MySQL讲义第 53 讲——select 查询之 select 语句执行过程分析
文章目录
- MySQL讲义第 53 讲——select 查询之 select 语句执行过程分析
- 一、数据准备
- 二、SELECT 语句的书写顺序
- 三、SELECT 语句的执行顺序
- 四、SELECT 语句的执行过程分析及验证
- 1、FROM 子句指定查询所使用的表，整个查询过程中 FROM 子句只执行一次
- 2、SELECT 子句执行的次数取决于查询结果中的记录行数
- 3、由于 SELECT 子句的执行晚于 WHERE 子句，因此在 WHERE 子句中不能使用 SELECT 子句中定义的别名
- 4、GROUP BY 子句和 WHERE 子句同时使用
- 5、ORDER BY 子句最后执行，可以使用 SELECT 子句中定义的别名
SELECT 语句包含多个子句，SELECT 语句在书写时需要遵循一定的顺序，否则会提示语法错误。而在执行过程中，各子句又有相应的执行顺序，在使用字段别名时需要注意各子句的执行顺序。
一、数据准备
以下查询使用到四张表，分别是：student、teacher、course 和 electives，表结构如下：
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);
二、SELECT 语句的书写顺序
SELECT DISTINCT <字段或表达式列表>
FROM <table_name> [ INNER | LEFT | RIGHT ] JOIN <table_name>
ON <连接条件>
WHERE <筛选条件>
GROUP BY <分组字段或表达式>
HAVING <分组筛选条件>
WITH ROLLUP
ORDER BY <排序字段或表达式>
LIMIT [m,]n
三、SELECT 语句的执行顺序
--step1：指定查询所使用的表
FROM <table_name> [ INNER | LEFT | RIGHT ] JOIN <table_name>
ON <连接条件>
--step2：指定筛选条件
--注意：WHERE 后面不能使用 SELECT 所指定的字段别名
WHERE <筛选条件>
--step3：对数据分组
GROUP BY <分组字段或表达式>
WITH ROLLUP
HAVING <分组筛选条件>
--step4：生成需要显示的字段
SELECT <字段或表达式列表>
--step5：消除重复的数据行
DISTINCT
--step6：对查询结果排序
ORDER BY <排序字段或表达式>
LIMIT [m,]n
四、SELECT 语句的执行过程分析及验证
1、FROM 子句指定查询所使用的表，整个查询过程中 FROM 子句只执行一次
验证：在 FROM 子句中定义变量并赋值，代码如下：
SELECT s.*
FROM student s, (SELECT @var := 0) a
;
SELECT @var;mysql> SELECT s.*-> FROM student s, -> (SELECT @var := 0) a-> ;
+-------+-----------+---------------------+-------------+-----------+
| 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 @var;
+------+
| @var |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
2、SELECT 子句执行的次数取决于查询结果中的记录行数
验证方法一：在 SELECT 子句中添加表达式，代码如下：
SELECT s.*, 1
FROM student s
WHERE s.birth BETWEEN '1999-1-' AND '1999-12-31'
;
+-------+-----------+---------------------+-------------+-----------+---+
| s_id | s_name | birth | phone | addr | 1 |
+-------+-----------+---------------------+-------------+-----------+---+
| S2011 | 张晓刚 | 1999-12-03 00:00:00 | 13163735775 | 信阳市 | 1 |
| S2012 | 刘小青 | 1999-10-11 00:00:00 | 13603732255 | 新乡市 | 1 |
| S2013 | 曹梦德 | 1998-02-13 00:00:00 | 13853735522 | 郑州市 | 1 |
| S2014 | 刘艳 | 1998-06-24 00:00:00 | 13623735335 | 郑州市 | 1 |
| S2015 | 刘岩 | 1999-07-06 00:00:00 | 13813735225 | 信阳市 | 1 |
| S2022 | 周华建 | 1999-05-25 00:00:00 | 13243735578 | 郑州市 | 1 |
| S2023 | 特朗普 | 1999-06-21 00:00:00 | 13343735588 | 新乡市 | 1 |
| S2026 | 张学有 | 1998-07-06 00:00:00 | 13743735566 | 郑州市 | 1 |
| S2031 | 李明博 | 1999-10-26 00:00:00 | 13643732222 | 郑州市 | 1 |
| S2032 | 达芬奇 | 1999-12-31 00:00:00 | 13043731234 | 郑州市 | 1 |
+-------+-----------+---------------------+-------------+-----------+---+
10 rows in set, 1 warning (0.00 sec)
因此，要统计记录数量，可以使用 COUNT(*)，也可以使用COUNT(1)：
SELECT COUNT(*),COUNT(1)
FROM student s
WHERE s.birth BETWEEN '1999-1-' AND '1999-12-31'
+----------+----------+
| COUNT(*) | COUNT(1) |
+----------+----------+
| 10 | 10 |
+----------+----------+
1 row in set, 1 warning (0.01 sec)
验证方法二：在 SELECT 子句中使用变量并赋值，代码如下：
SELECT s.*, @var := @var + 1
FROM student s, (SELECT @var := 0) a
WHERE s.birth BETWEEN '1999-1-' AND '1999-12-31'
;
+-------+-----------+---------------------+-------------+-----------+------------------+
| s_id | s_name | birth | phone | addr | @var := @var + 1 |
+-------+-----------+---------------------+-------------+-----------+------------------+
| S2011 | 张晓刚 | 1999-12-03 00:00:00 | 13163735775 | 信阳市 | 1 |
| S2012 | 刘小青 | 1999-10-11 00:00:00 | 13603732255 | 新乡市 | 2 |
| S2013 | 曹梦德 | 1998-02-13 00:00:00 | 13853735522 | 郑州市 | 3 |
| S2014 | 刘艳 | 1998-06-24 00:00:00 | 13623735335 | 郑州市 | 4 |
| S2015 | 刘岩 | 1999-07-06 00:00:00 | 13813735225 | 信阳市 | 5 |
| S2022 | 周华建 | 1999-05-25 00:00:00 | 13243735578 | 郑州市 | 6 |
| S2023 | 特朗普 | 1999-06-21 00:00:00 | 13343735588 | 新乡市 | 7 |
| S2026 | 张学有 | 1998-07-06 00:00:00 | 13743735566 | 郑州市 | 8 |
| S2031 | 李明博 | 1999-10-26 00:00:00 | 13643732222 | 郑州市 | 9 |
| S2032 | 达芬奇 | 1999-12-31 00:00:00 | 13043731234 | 郑州市 | 10 |
+-------+-----------+---------------------+-------------+-----------+------------------+
10 rows in set, 1 warning (0.01 sec)
3、由于 SELECT 子句的执行晚于 WHERE 子句，因此在 WHERE 子句中不能使用 SELECT 子句中定义的别名
验证代码如下：
SELECT s_id,s_name,YEAR(NOW()) - YEAR(birth) AS age
FROM student
WHERE age > 20
;
--执行时出现以下错误：
ERROR 1054 (42S22): Unknown column 'age' in 'where clause'SELECT s_id,s_name,YEAR(NOW()) - YEAR(birth) AS age
FROM student
WHERE YEAR(NOW()) - YEAR(birth) > 20 +-------+-----------+------+
| s_id | s_name | age |
+-------+-----------+------+
| S2011 | 张晓刚 | 21 |
| S2012 | 刘小青 | 21 |
| S2013 | 曹梦德 | 22 |
| S2014 | 刘艳 | 22 |
| S2015 | 刘岩 | 21 |
| S2022 | 周华建 | 21 |
| S2023 | 特朗普 | 21 |
| S2026 | 张学有 | 22 |
| S2031 | 李明博 | 21 |
| S2032 | 达芬奇 | 21 |
+-------+-----------+------+
10 rows in set (0.01 sec)
4、GROUP BY 子句和 WHERE 子句同时使用
由于 WHERE 子句先执行，查询时先使用 WHERE 子句筛选记录，对满足查询条件的记录进行分组。在 GROUP BY 子句中可以使用 SELECT 子句中定义的别名。
代码验证如下：
SELECT YEAR(NOW()) - YEAR(birth) AS age, COUNT(*)
FROM student
WHERE ADDR = '郑州市'
GROUP BY age
;
+------+----------+
| age | COUNT(*) |
+------+----------+
| 21 | 3 |
| 22 | 3 |
+------+----------+
2 rows in set (0.01 sec)
5、ORDER BY 子句最后执行，可以使用 SELECT 子句中定义的别名
代码验证如下：
SELECT s_id,s_name,YEAR(NOW()) - YEAR(birth) AS age
FROM student
ORDER BY age
;
+-------+-----------+------+
| s_id | s_name | age |
+-------+-----------+------+
| S2016 | 刘若非 | 20 |
| S2021 | 董雯花 | 20 |
| S2024 | 奥巴马 | 20 |
| S2025 | 周健华 | 20 |
| S2011 | 张晓刚 | 21 |
| S2012 | 刘小青 | 21 |
| S2015 | 刘岩 | 21 |
| S2022 | 周华建 | 21 |
| S2023 | 特朗普 | 21 |
| S2031 | 李明博 | 21 |
| S2032 | 达芬奇 | 21 |
| S2013 | 曹梦德 | 22 |
| S2014 | 刘艳 | 22 |
| S2026 | 张学有 | 22 |
+-------+-----------+------+
14 rows in set (0.00 sec)
