一、 SELECT 条件查询
条件查询
条件查询就是在查询时给出 WHERE 子句， 在WHERE字句中可以使用如下运算符及关键字
- ==， !=, <>（不等于）, <, >, <= >, >=
- BETWEEN ... AND
- IN(set) : 几个固定值
- IS NULL , IS NOT NULL
- AND
- OR
- NOT： 非
二、 OR 条件查询
SELECT * FROM stu WHERE sid = "s_1001" OR sname="lisi";
三、 IN 条件查询
SELECT * FROM stu WHERE sid IN("s_1001", "s_1002", "s_1003"); // 查询符合条件的集合中
+--------+----------+------+--------+
| sid | sname | age | gender |
+--------+----------+------+--------+
| s_1001 | liuYing | 35 | male |
| s_1002 | chenger | 20 | male |
| s_1003 | zhangsan | 15 | female |
+--------+----------+------+--------+
四、 NOT IN 条件查询
SELECT * FROM stu WHERE sid NOT IN("s_1001", "s_1002", "s_1003"); // 查询符合条件不在集合中
查询结果
+--------+----------+------+--------+
| sid | sname | age | gender |
+--------+----------+------+--------+
| s_1004 | wangwu | 23 | male |
| s_1005 | liuYin | 35 | female |
| s_1006 | zhaoliu | 70 | male |
| s_1007 | sunQi | 15 | female |
| s_1008 | zhouBa | 43 | male |
| s_1009 | wuJiu | 85 | female |
| s_1010 | zhengShi | 5 | male |
+--------+----------+------+--------+
五、查询是否存在年龄为空 IS NULL
SELECT * FROM stu WHERE age IS NULL;
六、查询年龄不为空 IS NOT NULL
SELECT * FROM stu WHERE age IS NOT NULL;
七、使用 BETWEEN AND 或者 >= <= 查询 年龄 20 到 40 之间的数据
SELECT * FROM stu WHERE age BETWEEN 20 AND 40;
SELECT * FROM stu WHERE age >= 20 AND age <= 40;
查询结果
+--------+---------+------+--------+
| sid | sname | age | gender |
+--------+---------+------+--------+
| s_1001 | liuYing | 35 | male |
| s_1002 | chenger | 20 | male |
| s_1004 | wangwu | 23 | male |
| s_1005 | liuYin | 35 | female |
+--------+---------+------+--------+
