SQL: 筛选数据的方式

内容来自 极客时间 课程: SQL必知必会的阅读笔记.

比较运算符

像下面的这些,就属于比较运算符,一般用来比较大小,或者是否为空等:

SELECT name, hp_max FROM heros WHERE hp_max > 6000; -- 大于

SELECT name, hp_max FROM heros WHERE hp_max BETWEEN 5399 AND 6811; -- 范围

SELECT name, hp_max FROM heros WHERE hp_max IS NULL; -- 为空,如果是WHERE hp_max IS NOT NULL 则查询hp_max不为空的数据

MySQL数据库图标
image-2896

逻辑运算符

SELECT name, hp_max, mp_max FROM heros WHERE hp_max > 6000 AND mp_max > 1700 ORDER BY (hp_max+mp_max) DESC; -- AND 与,和Java中的&&相同

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; -- OR 或 和Java中的||相同.

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; -- 可以综合使用

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; -- 综合到一起使用.

SELECT name FROM heros WHERE name LIKE '%太%'; -- 模糊匹配

SELECT name FROM heros WHERE name LIKE '_%太%';