MySQL SELECT条件查询

一、 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 |
+--------+---------+------+--------+