SQL学习_WHERE 数据过滤
1.比较运算符
SQL:SELECT name, hp_max FROM heros WHERE hp_max > 6000
SQL:SELECT name, hp_max FROM heros WHERE hp_max BETWEEN 5399 AND 6811
SQL:SELECT name, hp_max FROM heros WHERE hp_max IS NULL
2.逻辑运算符
假设想要筛选最大生命值大于 6000,最大法力大于 1700的英雄,然后按照最大生命值和最大法力值之和从高到低进行排序
SQL:SELECT name, hp_max, mp_max FROM heros WHERE hp_max > 6000 AND mp_max > 1700 ORDER BY (hp_max+mp_max) DESC
SQL 会优先处理 AND 操作符,然后再处理 OR 操作符...
SQL:SELECT name, hp_max, mp_max FROM heros WHERE (hp_max+mp_max) > 8000 OR hp_max > 6000 AND mp_max > 1700 ORDER BY (hp_max+mp_max) DESC
如果我想要查询主要定位或者次要定位是法师或是射手的英雄,同时英雄的上线时间不在 2016-01-01 到 2017-01-01 之间。
SQL:
SELECT name, role_main, role_assist, hp_max, mp_max, birthdate
FROM heros
WHERE (role_main IN ('法师', '射手') OR role_assist IN ('法师', '射手'))
AND DATE(birthdate) NOT BETWEEN '2016-01-01' AND '2017-01-01'
ORDER BY (hp_max + mp_max) DESC
3. 使用通配符进行过滤
SQL:SELECT name FROM heros WHERE name LIKE '% 太 %'
比如我们想要查找英雄名除了第一个字以外,包含‘太’字的英雄有哪些
SQL:SELECT name FROM heros WHERE name LIKE '_% 太 %'