- 数据库系统原理与应用教程（039）—— MySQL 查询（一）：SELECT 命令的语法分析
- 一、SELECT 语句的语法结构
- 二、准备查询所需的数据表
- 三、SELECT 语句的书写顺序与执行顺序
- 1、SELECT 语句的书写顺序
- 2、SELECT 语句的执行顺序
- 四、查询举例
- 1、基本用法
- 2、指定查询条件
- 3、连接查询
- 4、对查询结果进行排序
- 5、分组查询
- 6、分页查询
MySQL 使用 SELECT 语句查询所需的数据。查询是指根据使用需求，使用不同的查询方式从数据库中获取所需的数据， 查询是 MySQL 最重要的操作。
一、SELECT 语句的语法结构SELECT 语句的语法格式如下：
SELECT DISTINCT
<列名或表达式列表>
FROM
<表名> <连接类型>
JOIN <表名> ON <连接条件>
WHERe
<筛选条件>
GROUP BY
<分组字段列表>
HAVINg
<分组筛选条件>
ORDER BY
<排序字段>
LIMIT ;
二、准备查询所需的数据表
mysql> select * from student; +-------+-----------+---------------------+-------------+-----------+ | s_id | s_name | birth | phone | addr | +-------+-----------+---------------------+-------------+-----------+ | S2011 | 张晓刚 | 1999-12-03 00:00:00 | 13163735775 | 信阳市 | | S2012 | 刘小青 | 1999-10-11 00:00:00 | 13603732255 | 新乡市 | | S2013 | 曹梦德 | 1998-02-13 00:00:00 | 13853735522 | 郑州市 | | S2014 | 刘艳 | 1998-06-24 00:00:00 | 13623735335 | 郑州市 | | S2015 | 刘岩 | 1999-07-06 00:00:00 | 13813735225 | 信阳市 | | S2016 | 刘若非 | 2000-08-31 00:00:00 | 13683735533 | 开封市 | | S2021 | 董雯花 | 2000-07-30 00:00:00 | 13533735564 | 开封市 | | S2022 | 周华建 | 1999-05-25 00:00:00 | 13243735578 | 郑州市 | | S2023 | 特朗普 | 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) mysql> select * from teacher; +-------+-----------+-----------+-------------+ | t_id | t_name | job_title | phone | +-------+-----------+-----------+-------------+ | T8001 | 欧阳修 | 教授 | 13703735666 | | T8002 | 华罗庚 | 教授 | 13703735888 | | T8003 | 钟南山 | 教授 | 13703735675 | | T8004 | 钱学森 | 教授 | 13703735638 | | T8005 | 李白 | 副教授 | 13703735828 | | T8006 | 孔子 | 教授 | 13703735457 | | T8007 | 王安石 | 副教授 | 13703735369 | +-------+-----------+-----------+-------------+ 7 rows in set (0.00 sec) mysql> select * from course; +------+-----------------+-------+ | c_id | c_name | t_id | +------+-----------------+-------+ | C101 | 古代文学 | T8001 | | C102 | 高等数学 | T8002 | | C103 | 线性代数 | T8002 | | C104 | 临床医学 | T8003 | | C105 | 传染病学 | T8003 | | C106 | 大学物理 | T8004 | | C107 | 诗歌欣赏 | T8005 | | C108 | 教育学 | T8006 | | C109 | 刑事诉讼法 | T8007 | | C110 | 经济法 | T8007 | +------+-----------------+-------+ 10 rows in set (0.00 sec) mysql> select * from score; +-------+------+-------+ | s_id | c_id | score | +-------+------+-------+ | S2011 | C102 | 84 | | S2011 | C105 | 90 | | S2011 | C106 | 79 | | S2011 | C109 | 65 | | S2012 | C101 | 67 | | S2012 | C102 | 52 | | S2012 | C103 | 55 | | S2012 | C104 | 86 | | S2012 | C105 | 87 | | S2012 | C106 | 64 | | S2012 | C107 | 62 | | S2012 | C108 | 73 | | S2012 | C109 | 78 | | S2012 | C110 | 89 | | S2013 | C102 | 97 | | S2013 | C103 | 68 | | S2013 | C104 | 66 | | S2013 | C105 | 68 | | S2014 | C102 | 90 | | S2014 | C103 | 85 | | S2014 | C104 | 77 | | S2014 | C105 | 96 | | S2015 | C101 | 69 | | S2015 | C102 | 66 | | S2015 | C103 | 88 | | S2015 | C104 | 69 | | S2015 | C105 | 66 | | S2015 | C106 | 88 | | S2015 | C107 | 69 | | S2015 | C108 | 66 | | S2015 | C109 | 88 | | S2015 | C110 | 69 | | S2016 | C101 | 65 | | S2016 | C102 | 69 | | S2016 | C107 | 82 | | S2016 | C108 | 56 | | S2021 | C102 | 72 | | S2021 | C103 | 90 | | S2021 | C104 | 90 | | S2021 | C105 | 57 | | S2022 | C102 | 88 | | S2022 | C103 | 93 | | S2022 | C109 | 47 | | S2022 | C110 | 62 | | S2023 | C102 | 68 | | S2023 | C103 | 86 | | S2023 | C109 | 56 | | S2023 | C110 | 91 | | S2024 | C102 | 87 | | S2024 | C103 | 97 | | S2024 | C109 | 80 | | S2024 | C110 | 81 | | S2025 | C102 | 61 | | S2025 | C105 | 62 | | S2025 | C106 | 87 | | S2025 | C109 | 82 | | S2026 | C102 | 59 | | S2026 | C105 | 48 | | S2026 | C106 | 90 | | S2026 | C109 | 73 | +-------+------+-------+ 60 rows in set (0.00 sec)三、SELECt 语句的书写顺序与执行顺序 1、SELECT 语句的书写顺序
SELECT DISTINCT <列名或表达式列表> FROM[INNER|LEFT|RIGHT] JOIN ON <连接条件> WHERe <筛选条件> GROUP BY <分组字段或表达式> HAVINg <分组筛选条件> WITH ROLLUP ORDER BY <排序字段或表达式> LIMIT [m,]n
如果书写顺序不对会提示书写错误（1064）：
-- order by 和 where 顺序不对 mysql> select * from student order by s_name where addr='郑州市'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where addr='郑州 市'' at line 1 -- where 和 from 顺序不对 mysql> select * where addr='郑州市' from student; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where addr='郑州 市' from student' at line 1 -- where 和 group 顺序不对 mysql> select addr,count(*) from student group by addr where s_name like '张%'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where s_name like '张%'' at line 12、SELECt 语句的执行顺序
--（1）指定查询所使用的表 FROM四、查询举例 1、基本用法[INNER|LEFT|RIGHT] JOIN ON <连接条件> --（2）指定筛选条件（WHERe 后面不能使用 SELECt 所指定的列的别名，因为此时 select 子句还没有执行） WHERe <筛选条件> --（3）对数据分组 GROUP BY <分组字段或表达式> WITH ROLLUP HAVINg <分组筛选条件> --（4）生成需要显示的列和表达式 SELECT <字段或表达式列表> --（5）消除重复的数据行 DISTINCT --（6）对查询结果排序 ORDER BY <排序字段或表达式> LIMIT [m,]n
mysql> select s_id,s_name,year(now())-year(birth) age from student; +-------+-----------+------+ | s_id | s_name | age | +-------+-----------+------+ | S2011 | 张晓刚 | 23 | | S2012 | 刘小青 | 23 | | S2013 | 曹梦德 | 24 | | S2014 | 刘艳 | 24 | | S2015 | 刘岩 | 23 | | S2016 | 刘若非 | 22 | | S2021 | 董雯花 | 22 | | S2022 | 周华建 | 23 | | S2023 | 特朗普 | 23 | | S2024 | 奥巴马 | 22 | | S2025 | 周健华 | 22 | | S2026 | 张学有 | 24 | | S2031 | 李明博 | 23 | | S2032 | 达芬奇 | 23 | +-------+-----------+------+ 14 rows in set (0.03 sec)2、指定查询条件
mysql> select * from student where s_name like '张%'; +-------+-----------+---------------------+-------------+-----------+ | s_id | s_name | birth | phone | addr | +-------+-----------+---------------------+-------------+-----------+ | S2011 | 张晓刚 | 1999-12-03 00:00:00 | 13163735775 | 信阳市 | | S2026 | 张学有 | 1998-07-06 00:00:00 | 13743735566 | 郑州市 | +-------+-----------+---------------------+-------------+-----------+ 2 rows in set (0.02 sec)3、连接查询
mysql> select s.s_id, s_name, c.c_id, 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_id | c_name | score |
+-------+-----------+------+--------------+-------+
| S2013 | 曹梦德 | C102 | 高等数学 | 97 |
| S2014 | 刘艳 | C105 | 传染病学 | 96 |
| S2022 | 周华建 | C103 | 线性代数 | 93 |
| S2023 | 特朗普 | C110 | 经济法 | 91 |
| S2024 | 奥巴马 | C103 | 线性代数 | 97 |
+-------+-----------+------+--------------+-------+
5 rows in set (0.00 sec)
4、对查询结果进行排序
mysql> select * from student order by birth desc; +-------+-----------+---------------------+-------------+-----------+ | s_id | s_name | birth | phone | addr | +-------+-----------+---------------------+-------------+-----------+ | S2024 | 奥巴马 | 2000-10-17 00:00:00 | 13843735885 | 信阳市 | | S2016 | 刘若非 | 2000-08-31 00:00:00 | 13683735533 | 开封市 | | S2025 | 周健华 | 2000-08-22 00:00:00 | 13788736655 | 开封市 | | S2021 | 董雯花 | 2000-07-30 00:00:00 | 13533735564 | 开封市 | | S2032 | 达芬奇 | 1999-12-31 00:00:00 | 13043731234 | 郑州市 | | S2011 | 张晓刚 | 1999-12-03 00:00:00 | 13163735775 | 信阳市 | | S2031 | 李明博 | 1999-10-26 00:00:00 | 13643732222 | 郑州市 | | S2012 | 刘小青 | 1999-10-11 00:00:00 | 13603732255 | 新乡市 | | S2015 | 刘岩 | 1999-07-06 00:00:00 | 13813735225 | 信阳市 | | S2023 | 特朗普 | 1999-06-21 00:00:00 | 13343735588 | 新乡市 | | S2022 | 周华建 | 1999-05-25 00:00:00 | 13243735578 | 郑州市 | | S2026 | 张学有 | 1998-07-06 00:00:00 | 13743735566 | 郑州市 | | S2014 | 刘艳 | 1998-06-24 00:00:00 | 13623735335 | 郑州市 | | S2013 | 曹梦德 | 1998-02-13 00:00:00 | 13853735522 | 郑州市 | +-------+-----------+---------------------+-------------+-----------+ 14 rows in set (0.00 sec)5、分组查询
mysql> select addr,count(*) cnt from student group by addr; +-----------+-----+ | addr | cnt | +-----------+-----+ | 信阳市 | 3 | | 开封市 | 3 | | 新乡市 | 2 | | 郑州市 | 6 | +-----------+-----+ 4 rows in set (0.00 sec) -- 对分组进行筛选 mysql> select addr,count(*) cnt from student group by addr having cnt>2; +-----------+-----+ | addr | cnt | +-----------+-----+ | 信阳市 | 3 | | 开封市 | 3 | | 郑州市 | 6 | +-----------+-----+ 3 rows in set (0.00 sec)6、分页查询
-- 取前三条记录 mysql> select * from student order by birth limit 3; +-------+-----------+---------------------+-------------+-----------+ | s_id | s_name | birth | phone | addr | +-------+-----------+---------------------+-------------+-----------+ | S2013 | 曹梦德 | 1998-02-13 00:00:00 | 13853735522 | 郑州市 | | S2014 | 刘艳 | 1998-06-24 00:00:00 | 13623735335 | 郑州市 | | S2026 | 张学有 | 1998-07-06 00:00:00 | 13743735566 | 郑州市 | +-------+-----------+---------------------+-------------+-----------+ 3 rows in set (0.00 sec) -- 从第 4 条开始，取 2 条记录 mysql> select * from student order by birth limit 3,2; +-------+-----------+---------------------+-------------+-----------+ | s_id | s_name | birth | phone | addr | +-------+-----------+---------------------+-------------+-----------+ | S2022 | 周华建 | 1999-05-25 00:00:00 | 13243735578 | 郑州市 | | S2023 | 特朗普 | 1999-06-21 00:00:00 | 13343735588 | 新乡市 | +-------+-----------+---------------------+-------------+-----------+ 2 rows in set (0.00 sec)
