03 条件查询 WHERE ...
SELECT col_list FROM tb_name WHERE condition_list;
运算符 | 举个栗子 | |
比较运算符 | > | |
>= | ||
< | ||
<= | ||
= | ||
<> 或 != | id <> 2 | |
BETWEEN ... AND ...(含最小最大) | id between 3 and 10 | |
IN(...) | in(2, 3, 5, 6, 7) | |
LIKE 占位符(模糊匹配) | 匹配单个字符:like “a_” 匹配任意个字符:like “a%” | |
IS NULL | id is null | |
IS NOT NULL | id is not null | |
逻辑运算符 | AND 或 &&(同时成立) | id = 1 and name = “韩一” |
OR 或 ||(任意一个成立) | id = 1 or name = “韩一” | |
NOT 或 !(非) | id is not 1 |
举个栗子(MySQL)
数据库:world(自带的),表:countrylanguage。需要回答以下问题:
- 查询language为English的所有信息;
- 查询Percentage大于50的所有信息;
- 查询language不是English的所有信息;
- 查询没有CountryCode的所有信息;
- 查询Percentage在20到50之间的所有信息;
- 查询language为English且Percentage大于50的所有信息;
- 查询language为English或Percentage大于50的所有信息;
- 查询language以a开头的所有信息;
- 查询language为5个字的所有信息;
- 查询language以c结尾的所有信息;
比较简单,代码没错,大家可以在MySQL里运行一下看看结果:
use world;
show tables;
desc countrylanguage;
-- 1. 查询language为english的所有信息;
select * from countrylanguage where language = 'english';
-- 2. 查询percentage大于50的所有信息;
select * from countrylanguage where percentage > 50;
-- 3. 查询language不是english的所有信息;
select * from countrylanguage where language <> 'english';
-- 4. 查询没有countrycode的所有信息;
select * from countrylanguage where countrycode is null;
-- 5. 查询percentage在20到50之间的所有信息;
select * from countrylanguage where percentage >= 20 and percentage <= 50;
select * from countrylanguage where percentage between 20 and 50;
-- 6. 查询language为english且percentage大于50的所有信息;
select * from countrylanguage where language = 'english' and percentage > 50;
-- 7. 查询language为english或percentage大于50的所有信息;
select * from countrylanguage where language = 'english' or percentage > 50;
-- 8. 查询language以a开头的所有信息;
select * from countrylanguage where language like 'a%';
-- 9. 查询language为5个字的所有信息;
select * from countrylanguage where language like '_____';
-- 10. 查询language的倒数第二个字母为c的所有信息;
select * from countrylanguage where language like '%c_';
-- 11. 查询language在集合{english, spanish, dari}中的所有信息;
select * from countrylanguage where language in('english', 'spanish', 'dari');
不总结=白学
THE END