数据库系统原理与应用教程(041)—— MySQL 查询(三):设置查询条件
目录
数据库系统原理与应用教程(041)—— MySQL 查询(三):设置查询条件一、运算符1、比较运算符2、逻辑运算符二、比较运算符用法举例1、等于(=)2、大于(>)3、小于(<)4、大于或等于(>=)5、小于或等于(<=)6、不等于(!= 或 <>)三、逻辑运算符用法举例1、NOT(!)2、AND3、OR4、逻辑运算符的优先级四、几个特殊的运算符的用法1、BETWEEN … AND2、IN3、IS NULL查询中可以使用 WHERE 关键字来指定查询条件,从数据表中查询出满足某个条件的数据。
注意:由于 WHERE 关键字先于 SELECT 关键字执行,因此条件中不能使用关键字 SELECT 后面的别名,但可以使用表的别名。
一、运算符
可以使用比较运算符和逻辑运算符构造查询条件。
1、比较运算符
比较运算符可以对两个数据的值进行比较,常用的比较运算符如下:
2、逻辑运算符
逻辑运算符包括 NOT(!)、AND 和 OR,是对多个查询条件进行运算,用于构造复杂的查询条件。用法如下:
二、比较运算符用法举例
使用等于(=)、大于(>)、小于(<)、大于或等于(>=)、小于或等于(<=)、不等于(!= 或 <>)构造条件。
1、等于(=)
例如:查询地址在【新乡市】的学生信息。
mysql> select * from student where addr='新乡市';+-------+-----------+---------------------+-------------+-----------+| s_id | s_name | birth| phone | addr|+-------+-----------+---------------------+-------------+-----------+| S | 刘小青 | 1999-10-11 00:00:00 | 13603732255 | 新乡市 || S | 特朗普 | 1999-06-21 00:00:00 | 13343735588 | 新乡市 |+-------+-----------+---------------------+-------------+-----------+2 rows in set (0.00 sec)
2、大于(>)
例如:查询成绩超过 90 分的学生的学号,姓名以及所学课程的课程名及成绩。。
/*select s.s_id, s_name, c_name, scorefrom student s join score scon s.s_id = sc.s_idjoin course con sc.c_id = c.c_idwhere score > 90;*/mysql> select s.s_id, s_name, c_name, score-> from student s join score sc-> on s.s_id = sc.s_id-> join course c-> on sc.c_id = c.c_id-> where score > 90;+-------+-----------+--------------+-------+| s_id | s_name | c_name | score |+-------+-----------+--------------+-------+| S | 曹梦德 | 高等数学| 97 || S | 刘艳| 传染病学| 96 || S | 周华建 | 线性代数| 93 || S | 特朗普 | 经济法 | 91 || S2024 | 奥巴马 | 线性代数| 97 |+-------+-----------+--------------+-------+5 rows in set (0.01 sec)
3、小于(<)
例如:查询成绩不及格的学生的学号,姓名以及所学课程的课程名及成绩。
/*select s.s_id, s_name, c_name, scorefrom student s join score scon s.s_id = sc.s_idjoin course con sc.c_id = c.c_idwhere score < 60;*/mysql> select s.s_id, s_name, c_name, score-> from student s join score sc-> on s.s_id = sc.s_id-> join course c-> on sc.c_id = c.c_id-> where score < 60;+-------+-----------+-----------------+-------+| s_id | s_name | c_name| score |+-------+-----------+-----------------+-------+| S | 刘小青 | 高等数学 | 52 || S | 刘小青 | 线性代数 | 55 || S | 刘若非 | 教育学| 56 || S | 董雯花 | 传染病学 | 57 || S | 周华建 | 刑事诉讼法| 47 || S | 特朗普 | 刑事诉讼法| 56 || S2026 | 张学有 | 高等数学 | 59 || S2026 | 张学有 | 传染病学 | 48 |+-------+-----------+-----------------+-------+8 rows in set (0.00 sec)
4、大于或等于(>=)
例如:查询年龄超过 24 岁(含 24 岁)的学生信息
mysql> select * from student where year(now())-year(birth) >= 24;+-------+-----------+---------------------+-------------+-----------+| s_id | s_name | birth| phone | addr|+-------+-----------+---------------------+-------------+-----------+| S | 曹梦德 | 1998-02-13 00:00:00 | 13853735522 | 郑州市 || S | 刘艳| 1998-06-24 00:00:00 | 13623735335 | 郑州市 || S2026 | 张学有 | 1998-07-06 00:00:00 | 13743735566 | 郑州市 |+-------+-----------+---------------------+-------------+-----------+3 rows in set (0.00 sec)
5、小于或等于(<=)
例如:查询年龄低于 24 岁(含 24 岁)的学生信息
mysql> select * from student where year(now())-year(birth) <= 24;+-------+-----------+---------------------+-------------+-----------+| s_id | s_name | birth| phone | addr|+-------+-----------+---------------------+-------------+-----------+| S | 张晓刚 | 1999-12-03 00:00:00 | 13163735775 | 信阳市 || S | 刘小青 | 1999-10-11 00:00:00 | 13603732255 | 新乡市 || S | 曹梦德 | 1998-02-13 00:00:00 | 13853735522 | 郑州市 || S | 刘艳| 1998-06-24 00:00:00 | 13623735335 | 郑州市 || S | 刘岩| 1999-07-06 00:00:00 | 13813735225 | 信阳市 || S | 刘若非 | 2000-08-31 00:00:00 | 13683735533 | 开封市 || S | 董雯花 | 2000-07-30 00:00:00 | 13533735564 | 开封市 || S | 周华建 | 1999-05-25 00:00:00 | 13243735578 | 郑州市 || S | 特朗普 | 1999-06-21 00:00:00 | 13343735588 | 新乡市 || S2024 | 奥巴马 | 2000-10-17 00:00:00 | 13843735885 | 信阳市 || S2025 | 周健华 | 2000-08-22 00:00:00 | 13788736655 | 开封市 || S2026 | 张学有 | 1998-07-06 00:00:00 | 13743735566 | 郑州市 || S2031 | 李明博 | 1999-10-26 00:00:00 | 13643732222 | 郑州市 || S2032 | 达芬奇 | 1999-12-31 00:00:00 | 13043731234 | 郑州市 |+-------+-----------+---------------------+-------------+-----------+14 rows in set (0.00 sec)
6、不等于(!= 或 <>)
例如:查询地址不在【郑州市】的学生信息。
mysql> select * from student where addr <> '郑州市';+-------+-----------+---------------------+-------------+-----------+| s_id | s_name | birth| phone | addr|+-------+-----------+---------------------+-------------+-----------+| S | 张晓刚 | 1999-12-03 00:00:00 | 13163735775 | 信阳市 || S | 刘小青 | 1999-10-11 00:00:00 | 13603732255 | 新乡市 || S | 刘岩| 1999-07-06 00:00:00 | 13813735225 | 信阳市 || S | 刘若非 | 2000-08-31 00:00:00 | 13683735533 | 开封市 || S | 董雯花 | 2000-07-30 00:00:00 | 13533735564 | 开封市 || S | 特朗普 | 1999-06-21 00:00:00 | 13343735588 | 新乡市 || S2024 | 奥巴马 | 2000-10-17 00:00:00 | 13843735885 | 信阳市 || S2025 | 周健华 | 2000-08-22 00:00:00 | 13788736655 | 开封市 |+-------+-----------+---------------------+-------------+-----------+8 rows in set (0.00 sec)
三、逻辑运算符用法举例
1、NOT(!)
NOT 表示对一个条件求反,可以用感叹号代替 NOT。
例如:查询地址不在【郑州市】的学生信息。
mysql> select * from student where not addr = '郑州市';+-------+-----------+---------------------+-------------+-----------+| s_id | s_name | birth| phone | addr|+-------+-----------+---------------------+-------------+-----------+| S | 张晓刚 | 1999-12-03 00:00:00 | 13163735775 | 信阳市 || S | 刘小青 | 1999-10-11 00:00:00 | 13603732255 | 新乡市 || S | 刘岩| 1999-07-06 00:00:00 | 13813735225 | 信阳市 || S | 刘若非 | 2000-08-31 00:00:00 | 13683735533 | 开封市 || S | 董雯花 | 2000-07-30 00:00:00 | 13533735564 | 开封市 || S | 特朗普 | 1999-06-21 00:00:00 | 13343735588 | 新乡市 || S2024 | 奥巴马 | 2000-10-17 00:00:00 | 13843735885 | 信阳市 || S2025 | 周健华 | 2000-08-22 00:00:00 | 13788736655 | 开封市 |+-------+-----------+---------------------+-------------+-----------+8 rows in set (0.00 sec)
2、AND
AND 表示两个条件同时成立时结果为真。
例如:查询地址在【郑州市】并且姓【刘】的学生。
mysql> select * from student where addr='郑州市' and s_name like '刘%';+-------+--------+---------------------+-------------+-----------+| s_id | s_name | birth| phone | addr|+-------+--------+---------------------+-------------+-----------+| S | 刘艳 | 1998-06-24 00:00:00 | 13623735335 | 郑州市 |+-------+--------+---------------------+-------------+-----------+1 row in set (0.00 sec)
3、OR
OR 表示两个条件只要有一个成立结果就为真。
例如:查询地址为【郑州市】或者姓【刘】的学生信息。
mysql> select * from student where addr='郑州市' or s_name like '刘%';+-------+-----------+---------------------+-------------+-----------+| s_id | s_name | birth| phone | addr|+-------+-----------+---------------------+-------------+-----------+| S | 刘小青 | 1999-10-11 00:00:00 | 13603732255 | 新乡市 || S | 曹梦德 | 1998-02-13 00:00:00 | 13853735522 | 郑州市 || S | 刘艳| 1998-06-24 00:00:00 | 13623735335 | 郑州市 || S | 刘岩| 1999-07-06 00:00:00 | 13813735225 | 信阳市 || S | 刘若非 | 2000-08-31 00:00:00 | 13683735533 | 开封市 || S | 周华建 | 1999-05-25 00:00:00 | 13243735578 | 郑州市 || S2026 | 张学有 | 1998-07-06 00:00:00 | 13743735566 | 郑州市 || S2031 | 李明博 | 1999-10-26 00:00:00 | 13643732222 | 郑州市 || S2032 | 达芬奇 | 1999-12-31 00:00:00 | 13043731234 | 郑州市 |+-------+-----------+---------------------+-------------+-----------+9 rows in set (0.00 sec)
4、逻辑运算符的优先级
当 NOT、AND 和 OR 同时使用时,优先级顺序为 NOT → AND → OR。当然,如果记不清优先级,可以使用括号。
(1)查询地址不在【郑州市】并且姓【刘】的学生信息。
mysql> select * from student-> where s_name like '刘%' and not addr = '郑州市';+-------+-----------+---------------------+-------------+-----------+| s_id | s_name | birth| phone | addr|+-------+-----------+---------------------+-------------+-----------+| S | 刘小青 | 1999-10-11 00:00:00 | 13603732255 | 新乡市 || S | 刘岩| 1999-07-06 00:00:00 | 13813735225 | 信阳市 || S | 刘若非 | 2000-08-31 00:00:00 | 13683735533 | 开封市 |+-------+-----------+---------------------+-------------+-----------+3 rows in set (0.00 sec)
(2)查询地址【郑州市】或【信阳市】并且姓【刘】的学生信息。
-- 由于 and 的优先级大于 or,所以 or 两边的条件必须加括号-- 如果使用条件:s_name like '刘%' and addr = '郑州市' or addr = '信阳市'-- 表示查询地址在【郑州市】并且姓【刘】的学生以及地址在【信阳市】的学生,其中【信阳市】的学生不一定姓【刘】。mysql> select * from student where s_name like '刘%' and (addr = '郑州市' or addr = '信阳市');+-------+--------+---------------------+-------------+-----------+| s_id | s_name | birth| phone | addr|+-------+--------+---------------------+-------------+-----------+| S | 刘艳 | 1998-06-24 00:00:00 | 13623735335 | 郑州市 || S | 刘岩 | 1999-07-06 00:00:00 | 13813735225 | 信阳市 |+-------+--------+---------------------+-------------+-----------+2 rows in set (0.01 sec)-- 去掉括号mysql> select * from student where s_name like '刘%' and addr = '郑州市' or addr = '信阳市';+-------+-----------+---------------------+-------------+-----------+| s_id | s_name | birth| phone | addr|+-------+-----------+---------------------+-------------+-----------+| S | 张晓刚 | 1999-12-03 00:00:00 | 13163735775 | 信阳市 || S | 刘艳| 1998-06-24 00:00:00 | 13623735335 | 郑州市 || S | 刘岩| 1999-07-06 00:00:00 | 13813735225 | 信阳市 || S2024 | 奥巴马 | 2000-10-17 00:00:00 | 13843735885 | 信阳市 |+-------+-----------+---------------------+-------------+-----------+4 rows in set (0.00 sec)
四、几个特殊的运算符的用法
1、BETWEEN … AND
BETWEEN … AND 用来限定查找的信息在两个值(一个表示最小值,一个表示最大值,包含边界)之间。
语法格式如下:
where 列名 BETWEEN m AND n-- 等价于 列名 >= m and 列名 <= n-- 求不在某个范围之内的记录,格式如下:where 列名 NOT BETWEEN m AND n-- 或where NOT 列名 BETWEEN m AND n
例如:查询 2000-1-1(含2000-1-1) 到 2000-12-31(含2000-12-31) 之间出生的学生的信息。
mysql> select * from student where birth between '2000-1-1' and '2000-12-31';+-------+-----------+---------------------+-------------+-----------+| s_id | s_name | birth| phone | addr|+-------+-----------+---------------------+-------------+-----------+| S | 刘若非 | 2000-08-31 00:00:00 | 13683735533 | 开封市 || S | 董雯花 | 2000-07-30 00:00:00 | 13533735564 | 开封市 || S2024 | 奥巴马 | 2000-10-17 00:00:00 | 13843735885 | 信阳市 || S2025 | 周健华 | 2000-08-22 00:00:00 | 13788736655 | 开封市 |+-------+-----------+---------------------+-------------+-----------+4 rows in set (0.00 sec)-- 上面的条件等价于:birth >= '2000-1-1' and birth <='2000-12-31'mysql> select * from student where birth >= '2000-1-1' and birth <='2000-12-31';+-------+-----------+---------------------+-------------+-----------+| s_id | s_name | birth| phone | addr|+-------+-----------+---------------------+-------------+-----------+| S | 刘若非 | 2000-08-31 00:00:00 | 13683735533 | 开封市 || S | 董雯花 | 2000-07-30 00:00:00 | 13533735564 | 开封市 || S2024 | 奥巴马 | 2000-10-17 00:00:00 | 13843735885 | 信阳市 || S2025 | 周健华 | 2000-08-22 00:00:00 | 13788736655 | 开封市 |+-------+-----------+---------------------+-------------+-----------+4 rows in set (0.00 sec)
查询 1999-1-1(不含1999-1-1) 到 2000-12-31(不含2000-12-31) 之间出生的学生的信息。
mysql> select * from student where birth not between '1999-1-1' and '2000-12-31';+-------+-----------+---------------------+-------------+-----------+| s_id | s_name | birth| phone | addr|+-------+-----------+---------------------+-------------+-----------+| S | 曹梦德 | 1998-02-13 00:00:00 | 13853735522 | 郑州市 || S | 刘艳| 1998-06-24 00:00:00 | 13623735335 | 郑州市 || S2026 | 张学有 | 1998-07-06 00:00:00 | 13743735566 | 郑州市 |+-------+-----------+---------------------+-------------+-----------+3 rows in set (0.00 sec)mysql> select * from student where not birth between '1999-1-1' and '2000-12-31';+-------+-----------+---------------------+-------------+-----------+| s_id | s_name | birth| phone | addr|+-------+-----------+---------------------+-------------+-----------+| S | 曹梦德 | 1998-02-13 00:00:00 | 13853735522 | 郑州市 || S | 刘艳| 1998-06-24 00:00:00 | 13623735335 | 郑州市 || S2026 | 张学有 | 1998-07-06 00:00:00 | 13743735566 | 郑州市 |+-------+-----------+---------------------+-------------+-----------+3 rows in set (0.00 sec)
2、IN
使用 IN 操作符,表示要查询的数据在一个集合内,IN 操作符可以部分代替逻辑表达式 OR。
语法格式如下:
WHERE 列名 IN (数据1, 数据2, ...)-- 或WHERE 列名 IN (select 子查询)
例如:
(1)查询地址在【新乡市】和【信阳市】的学生信息。
-- 使用逻辑运算符 ormysql> select * from student where addr = '新乡市' or addr = '信阳市';+-------+-----------+---------------------+-------------+-----------+| s_id | s_name | birth| phone | addr|+-------+-----------+---------------------+-------------+-----------+| S | 张晓刚 | 1999-12-03 00:00:00 | 13163735775 | 信阳市 || S | 刘小青 | 1999-10-11 00:00:00 | 13603732255 | 新乡市 || S | 刘岩| 1999-07-06 00:00:00 | 13813735225 | 信阳市 || S | 特朗普 | 1999-06-21 00:00:00 | 13343735588 | 新乡市 || S2024 | 奥巴马 | 2000-10-17 00:00:00 | 13843735885 | 信阳市 |+-------+-----------+---------------------+-------------+-----------+5 rows in set (0.00 sec)-- 使用操作符 INmysql> select * from student where addr IN ('新乡市', '信阳市');+-------+-----------+---------------------+-------------+-----------+| s_id | s_name | birth| phone | addr|+-------+-----------+---------------------+-------------+-----------+| S | 张晓刚 | 1999-12-03 00:00:00 | 13163735775 | 信阳市 || S | 刘小青 | 1999-10-11 00:00:00 | 13603732255 | 新乡市 || S | 刘岩| 1999-07-06 00:00:00 | 13813735225 | 信阳市 || S | 特朗普 | 1999-06-21 00:00:00 | 13343735588 | 新乡市 || S2024 | 奥巴马 | 2000-10-17 00:00:00 | 13843735885 | 信阳市 |+-------+-----------+---------------------+-------------+-----------+5 rows in set (0.00 sec)
(2)查询和姓【周】的同学地址相同的学生的信息。
mysql> select s_name, addr from student where s_name like '周%';+-----------+-----------+| s_name | addr|+-----------+-----------+| 周华建 | 郑州市 || 周健华 | 开封市 |+-----------+-----------+2 rows in set (0.00 sec)-- 使用子查询,此时只能使用操作符 IN,查询结果其实就是地址为【郑州市】和【开封市】的学生信息。mysql> select * from student where addr IN-> (select addr from student where s_name like '周%');+-------+-----------+---------------------+-------------+-----------+| s_id | s_name | birth| phone | addr|+-------+-----------+---------------------+-------------+-----------+| S | 曹梦德 | 1998-02-13 00:00:00 | 13853735522 | 郑州市 || S | 刘艳| 1998-06-24 00:00:00 | 13623735335 | 郑州市 || S | 周华建 | 1999-05-25 00:00:00 | 13243735578 | 郑州市 || S2026 | 张学有 | 1998-07-06 00:00:00 | 13743735566 | 郑州市 || S2031 | 李明博 | 1999-10-26 00:00:00 | 13643732222 | 郑州市 || S2032 | 达芬奇 | 1999-12-31 00:00:00 | 13043731234 | 郑州市 || S | 刘若非 | 2000-08-31 00:00:00 | 13683735533 | 开封市 || S | 董雯花 | 2000-07-30 00:00:00 | 13533735564 | 开封市 || S2025 | 周健华 | 2000-08-22 00:00:00 | 13788736655 | 开封市 |+-------+-----------+---------------------+-------------+-----------+9 rows in set (0.00 sec)
3、IS NULL
使用 IS NULL 可以查询某个列为空的记录。查询某个字段为空值的记录不能使用等号(因为 NULL 和任何值用 = 比较结果为 NULL),需要使用 IS NULL。
语法格式如下:
-- 查询某个列为空的记录where 列名 IS NULL-- 查询某个列不为空的记录where 列名 IS NOT NULL-- 或 where NOT 列名 IS NULL
例如:有如下的表 t1。
mysql> create table t1(id int primary key,name char(20));Query OK, 0 rows affected (0.00 sec)mysql> insert into t1 values(1,'Jack'),(2,null),(3,'Black'),(4,null),(5,null);Query OK, 5 rows affected (0.00 sec)Records: 5 Duplicates: 0 Warnings: 0mysql> select * from t1;+----+-------+| id | name |+----+-------+| 1 | Jack || 2 | NULL || 3 | Black || 4 | NULL || 5 | NULL |+----+-------+5 rows in set (0.00 sec)
(1)查询姓名为 NULL 的记录。
mysql> select * from t1 where name is null;+----+------+| id | name |+----+------+| 2 | NULL || 4 | NULL || 5 | NULL |+----+------+3 rows in set (0.00 sec)-- 使用等号(=)没有结果mysql> select * from t1 where name = null;Empty set (0.00 sec)
(2)查询姓名不为 NULL 的记录。
mysql> select * from t1 where name is not null;+----+-------+| id | name |+----+-------+| 1 | Jack || 3 | Black |+----+-------+2 rows in set (0.00 sec)mysql> select * from t1 where not name is null;+----+-------+| id | name |+----+-------+| 1 | Jack || 3 | Black |+----+-------+2 rows in set (0.00 sec)