MySQL条件查询语句有哪些？-php教程-PHP中文網

MySQL条件查询语句有哪些？

PHPz

發布： 2020-09-04 15:09:32

原創

4478 人瀏覽過

MySQL 条件查询语句有哪些？

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

登入後複製

更多相关知识，请访问PHP中文网！