数据库系统原理与应用教程（039）—— MySQL 查询（一）：SELECT 命令的语法分析

一、SELECT 语句的语法结构

SELECT 语句的语法格式如下：

SELECT DISTINCT
    <列名或表达式列表>
FROM
    <表名> <连接类型>
JOIN <表名> ON <连接条件>
WHERE
    <筛选条件>
GROUP BY
    <分组字段列表>
HAVING
    <分组筛选条件>
ORDER BY
    <排序字段>
LIMIT <m,n>;

/*
参数说明：
（1）DISTINCT：消除重复行。
（2）<字段或表达式列表>：表示所要查询字段的名称，可以使用（*）表示所有字段，也可以使用列名构造表达式。
（3）<连接类型>：可以使用 inner join，left join，right join 分别表示内连接，左连接，右连接。
（4）<连接条件>：一般使用两个表中的相同列构造连接条件。进行连接运算的表通常具有一对多联系，连接条件通常使用子表的外键和父表的主键建立。
（5）<筛选条件>：限定查询数据必须满足该查询条件。
（6）<分组字段列表>：按照指定的字段分组。
（7）<分组筛选条件>：对分组进行筛选。
（8）<排序字段>：对查询结果进行排序，可以进行升序（ASC）和降序（DESC）排列，默认是升序。
（9）LIMIT ：分页显示数据。
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

二、准备查询所需的数据表

/*
CREATE TABLE student(
    s_id char(5) primary key,
    s_name char(20),
    birth datetime,
    phone char(20),
    addr varchar(100)
);

INSERT INTO student
VALUES('S2011','张晓刚','1999-12-3','13163735775','信阳市'),
('S2012','刘小青','1999-10-11','13603732255','新乡市'),
('S2013','曹梦德','1998-2-13','13853735522','郑州市'),
('S2014','刘艳','1998-6-24','13623735335','郑州市'),
('S2015','刘岩','1999-7-6','13813735225','信阳市'),
('S2016','刘若非','2000-8-31','13683735533','开封市'),
('S2021','董雯花','2000-7-30','13533735564','开封市'),
('S2022','周华建','1999-5-25','13243735578','郑州市'),
('S2023','特朗普','1999-6-21','13343735588','新乡市'),
('S2024','奥巴马','2000-10-17','13843735885','信阳市'),
('S2025','周健华','2000-8-22','13788736655','开封市'),
('S2026','张学有','1998-7-6','13743735566','郑州市'),
('S2031','李明博','1999-10-26','13643732222','郑州市'),
('S2032','达芬奇','1999-12-31','13043731234','郑州市');

CREATE TABLE teacher(
    t_id char(5) primary key,
    t_name char(20),
    job_title char(20),
    phone char(20)
);

INSERT INTO teacher
VALUES('T8001','欧阳修','教授','13703735666'),
('T8002','华罗庚','教授','13703735888'),
('T8003','钟南山','教授','13703735675'),
('T8004','钱学森','教授','13703735638'),
('T8005','李白','副教授','13703735828'),
('T8006','孔子','教授','13703735457'),
('T8007','王安石','副教授','13703735369');

CREATE TABLE course(
    c_id char(4) primary key,
    c_name char(20),
    t_id char(5)
);

INSERT INTO course
VALUES('C101','古代文学','T8001'),
('C102','高等数学','T8002'),
('C103','线性代数','T8002'),
('C104','临床医学','T8003'),
('C105','传染病学','T8003'),
('C106','大学物理','T8004'),
('C107','诗歌欣赏','T8005'),
('C108','教育学','T8006'),
('C109','刑事诉讼法','T8007'),
('C110','经济法','T8007');

CREATE TABLE score(
    s_id char(5),
    c_id char(4),
    score int,
    primary key(s_id, c_id)
);

INSERT INTO score
VALUES('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);
*/
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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202

三、SELECT 语句的书写顺序与执行顺序

1、SELECT 语句的书写顺序

SELECT DISTINCT <列名或表达式列表>
FROM <table_name> [INNER|LEFT|RIGHT] JOIN <table_name>
ON <连接条件>
WHERE <筛选条件>
GROUP BY <分组字段或表达式>
HAVING <分组筛选条件>
WITH ROLLUP
ORDER BY <排序字段或表达式>
LIMIT [m,]n
1
2
3
4
5
6
7
8
9

如果书写顺序不对会提示书写错误（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 1
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2、SELECT 语句的执行顺序

--（1）指定查询所使用的表
FROM <table_name> [INNER|LEFT|RIGHT] JOIN <table_name>
ON <连接条件>
--（2）指定筛选条件（WHERE 后面不能使用 SELECT 所指定的列的别名，因为此时 select 子句还没有执行）
WHERE <筛选条件>
--（3）对数据分组
GROUP BY <分组字段或表达式>
WITH ROLLUP
HAVING <分组筛选条件>
--（4）生成需要显示的列和表达式
SELECT <字段或表达式列表>
--（5）消除重复的数据行
DISTINCT
--（6）对查询结果排序
ORDER BY <排序字段或表达式>
LIMIT [m,]n
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

四、查询举例

1、基本用法

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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

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)
1
2
3
4
5
6
7
8

3、连接查询

/*
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;
*/
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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

相关阅读:
动态库.lib与.dll关系、visual studio工程库路径配置浅析
 如何利用ProcessOn 做资产管理流程图
 基于JavaSwing开发学生管理系统（登录增删改查）+论文报告课程设计大作业
 随机流-RandomAccessFile
Java面试题
 第十一章 GetAway服务网关详解
 解锁新姿势，揭秘如何通过公众号迁移方式给自己的爱号开通留言板功能
 加密excel（Python）
Linux python运维
 目标检测YOLO实战应用案例100讲-SAR图像多尺度舰船目标检测
原文地址：https://blog.csdn.net/weixin_44377973/article/details/125892166

数据库系统原理与应用教程（039）—— MySQL 查询（一）：SELECT 命令的语法分析

数据库系统原理与应用教程（039）—— MySQL 查询（一）：SELECT 命令的语法分析

目录

一、SELECT 语句的语法结构

二、准备查询所需的数据表

三、SELECT 语句的书写顺序与执行顺序

1、SELECT 语句的书写顺序

2、SELECT 语句的执行顺序

四、查询举例

1、基本用法

2、指定查询条件

3、连接查询

4、对查询结果进行排序

5、分组查询

6、分页查询