MySQL7 SQL执行顺序、SQL语句执行顺序、学生选课表练习

一、SQL执行顺序

1.系统（客户端）访问 MySQL 服务器前，做 的第一件事就是建立 TCP 连接。
2.Caches & Buffers： 查询缓存组件
3.SQL Interface: SQL接口 接收用户的SQL命 令，并且返回用户需要查询的结果。比如 SELECT … 4.FROM就是调用SQL Interface MySQL支持DML（数据操作语言）、DDL （数据定义语言）、存储过程、视图、触发器、 自定 义函数等多种SQL语言接口
5.Parser: 解析器:在解析器中对 SQL 语句进行 语法分析、语义分析。
6.Optimizer: 查询优化器
7.存储引擎
8.文件系统
9.日志系统

二、MySQL执行过程

MySQL整体的执行过程如下图所示：

连接器

连接器的主要职责就是：

1、负责与客户端的通信，是半双工模式，这就意味着某一固定时刻只能由客户端向服务器请求或者服务器向客户端发送数据，而不能同时进行，其中MySQL在与客户端连接TC/IP的。

2、验证请求用户的账户和密码是否正确，如果账户和密码错误，会报错：Access denied for user
‘root’@‘localhost’ (using password: YES)

3、如果用户的账户和密码验证通过，会在MySQL自带的权限表中查询当前用户的权限。

MySQL中存在4个控制权限的表，分别为user表，db表，tables_priv表，columns_priv表：

user表：存放用户账户信息以及全局级别（所有数据库）权限，决定了来自哪些主机的哪些用户可以访问数据库实例
db表：存放数据库级别的权限，决定了来自哪些主机的哪些用户可以访问此数据库
tables_priv表：存放表级别的权限，决定了来自哪些主机的哪些用户可以访问数据库的这个表
columns_priv表：存放列级别的权限，决定了来自哪些主机的哪些用户可以访问数据库表的这个字段 MySQL权限表的验证过程为：

1、 先从user表中的Host，User，Password这3个字段中判断连接的IP、用户名、密码是否存在，存在则通过验证。

2、通过身份认证后，进行权限分配，按照user，db，tables_priv，columns_priv的顺序进行验证。即先检查全局权限表user，如果user中对应的权限为Y，则此用户对所有数据库的权限都为Y，将不再检查db，tables_priv，columns_priv；如果为N，则到db表中检查此用户对应的具体数据库，并得到db中为Y的权限；如果db中为N，则检查tables_priv中此数据库对应的具体表，取得表中的权限Y，以此类推。

3、如果在任何一个过程中权限验证不通过，都会报错。

缓存

MySQL的缓存主要的作用是为了提升查询的效率，缓存以key和value的哈希表形式存储，key是具体的SQL语句，value是结果的集合。如果无法命中缓存，就继续走到分析器的这一步，如果命中缓存就直接返回给客户端。不过需要注意的是在MySQL的8.0版本以后，缓存被官方删除掉了。之所以删除掉，是因为查询缓存的失效非常频繁，如果在一个写多读少的环境中，缓存会频繁的新增和失效。对于某些更新压力大的数据库来说，查询缓存的命中率会非常低，MySQL为了维护缓存可能会出现一定的伸缩性的问题，目前在5.6的版本中已经默认关闭了，比较推荐的一种做法是将缓存放在客户端，性能大概会提升5倍左右。

分析器

分析器的主要作用是将客户端发过来的SQL语句进行分析，这将包括预处理与解析过程，在这个阶段会解析SQL语句的语义，并进行关键词和非关键词进行提取、解析，并组成一个解析树。具体的关键词包括不限定于以下：select/update/delete/or/in/where/group
by/having/count/limit等。如果分析到语法错误，会直接给客户端抛出异常：“ERROR:You have an error
in your SQL syntax.”。

比如：select * from user where userId =1234;

在分析器中就通过语义规则器将select from
where这些关键词提取和匹配出来，MySQL会自动判断关键词和非关键词，将用户的匹配字段和自定义语句识别出来。这个阶段也会做一些校验：比如校验当前数据库是否存在user表，同时假如user表中不存在userId这个字段同样会报错：“unknown
column in field list.”。

优化器

能够进入到优化器阶段表示SQL是符合MySQL的标准语义规则的并且可以执行的，此阶段主要是进行SQL语句的优化，会根据执行计划进行最优的选择，匹配合适的索引，选择最佳的执行方案。比如一个典型的例子是这样的：

表T，对A、B、C列建立联合索引，在进行查询的时候，当SQL查询到的结果是：select xx where B=x and A=x and
C=x，很多人会以为是用不到索引的，但其实会用到，虽然索引必须符合最左原则才能使用，但是本质上，优化器会自动将这条SQL优化为：where
A=x and B=x and
C=X，这种优化会为了底层能够匹配到索引，同时在这个阶段是自动按照执行计划进行预处理，MySQL会计算各个执行方法的最佳时间，最终确定一条执行的SQL交给最后的执行器。

执行器

在执行器的阶段，此时会调用存储引擎的API，API会调用存储引擎，主要有一下存储的引擎，不过常用的还是myisam和innodb：

引擎以前的名字叫做：表处理器（其实这个名字我觉得更能表达它存在的意义）负责对具体的数据文件进行操作，对SQL的语义比如select或者update进行分析，执行具体的操作。在执行完以后会将具体的操作记录到binlog中，需要注意的一点是：select不会记录到binlog中，只有update/delete/insert才会记录到binlog中。而update会采用两阶段提交的方式，记录都redolog中。
执行的状态
可以通过命令：show full processlist，展示所有的处理进程，主要包含了以下的状态，表示服务器处理客户端的状态，状态包含了从客户端发起请求到后台服务器处理的过程，包括加锁的过程、统计存储引擎的信息，排序数据、搜索中间表、发送数据等。囊括了所有的MySQL的所有状态,其中具体的含义如下图：

三、SQL执行顺序

事实上，SQL并不是按照我们的书写顺序来从前往后、左往右依次执行的，它是按照固定的顺序解析的，主要的作用就是从上一个阶段的执行返回结果来提供给下一阶段使用，SQL在执行的过程中会有不同的临时中间表，一般是按照如下顺序：

例子：
select distinct s.id from T t join S s on t.id=s.id where t.name=“Yrion” group by t.mobile having count(*)>2 order by s.create_time limit 5;
from
第一步就是选择出from关键词后面跟的表，这也是SQL执行的第一步：表示要从数据库中执行哪张表。
实例说明：在这个例子中就是首先从数据库中找到表T。
join on

join是表示要关联的表，on是连接的条件。通过from和join
on选择出需要执行的数据库表T和S，产生笛卡尔积，生成T和S合并的临时中间表Temp1。on：确定表的绑定关系，通过on产生临时中间表Temp2。

实例说明：找到表S，生成临时中间表Temp1，然后找到表T的id和S的id相同的部分组成成表Temp2，Temp2里面包含着T和Sid相等的所有数据。

where

where表示筛选，根据where后面的条件进行过滤，按照指定的字段的值（如果有and连接符会进行联合筛选）从临时中间表Temp2中筛选需要的数据，注意如果在此阶段找不到数据，会直接返回客户端，不会往下进行。这个过程会生成一个临时中间表Temp3。注意：在where中不可以使用聚合函数，聚合函数主要是（min\max\count\sum等函数）。

实例说明：在temp2临时表集合中找到T表的name="Yrion"的数据，找到数据后会成临时中间表Temp3，T。emp3里包含name列为"Yrion"的所有表数据。

group by

group
by是进行分组，对where条件过滤后的临时表Temp3按照固定的字段进行分组，产生临时中间表Temp4，这个过程只是数据的顺序发生改变，而数据总量不会变化，表中的数据以组的形式存在。

实例说明：在Temp3表数据中对mobile进行分组，查找出mobile一样的数据，然后放到一起，产生Temp4临时表。

Having

对临时中间表Temp4进行聚合，这里可以为count等计数，然后产生中间表Temp5，在此阶段可以使用select中的别名。

实例说明：在Temp4临时表中找出条数大于2的数据，如果小于2直接被舍弃掉，然后生成临时中间表Temp5。

select

对分组聚合完的表挑选出需要查询的数据，如果为*会解析为所有数据，此时会产生中间表Temp6。

实例说明：在此阶段就是对Temp5临时聚合表中S表中的id进行筛选产生Temp6，此时Temp6就只包含有s表的id列数据，并且name=“Yrion”，通过mobile分组数量大于2的数据。

Distinct

Distinct对所有的数据进行去重，此时如果有min、max函数会执行字段函数计算，然后产生临时表Temp7。

实例说明：此阶段对Temp5中的数据进行去重，引擎API会调用去重函数进行数据的过滤，最终只保留id第一次出现的那条数据，然后产生临时中间表Temp7。

order by

会根据Temp7进行顺序排列或者逆序排列，然后插入临时中间表Temp8，这个过程比较耗费资源。

实例说明：这段会将所有Temp7临时表中的数据按照创建时间（create_time）进行排序，这个过程也不会有列或者行损失。

limit

limit对中间表Temp8进行分页，产生临时中间表Temp9，返回给客户端。

实例说明：在Temp7中排好序的数据，然后取前五条插入到Temp9这个临时表中，最终返回给客户端。

PS：实际上这个过程也并不是绝对这样的，中间MySQL会有部分的优化以达到最佳的优化效果，比如在select筛选出找到的数据集。

总结

MySQL的执行过程，以及SQL的执行顺序，理解这些有助于我们对SQL语句进行优化，以及明白MySQL中的SQL语句从写出来到最终执行的轨迹，有助于我们对SQL有比较深入和细致的理解，提高我们的数据库理解能力。同时，对于复杂SQL的执行过程、编写都会有一定程度的意义。

四、学生选课表练习

学生表：Student（编号sid，姓名sname，生日birthday，性别ssex，班级 classid)

课程表：Course（课程编号cid，课程名称cname，教师编号tid）

成绩表：Sc（学生编号sid，课程编号cid，成绩score）

教师表：Teacher（教师编号tid，姓名tname）

班级表：Class （班级编号 classid，班级名称 classname）

创建表

学生表 Student

create table Student(Sid int primary key, Sname varchar(10), birthday datetime, Ssex varchar(10), classid int);
insert into Student values('1' , '赵雷' , '1990-01-01' , '男', '1');
insert into Student values('2' , '钱电' , '1990-12-21' , '男', '2');
insert into Student values('3' , '孙风' , '1990-05-20' , '男', '1');
insert into Student values('4' , '李云' , '1990-08-06' , '男', '2');
insert into Student values('5' , '周梅' , '1991-12-01' , '女', '1');
insert into Student values('6' , '吴兰' , '1992-03-01' , '女', '2');
insert into Student values('7' , '郑竹' , '1989-07-01' , '女', '1');
insert into Student values('8' , '王菊' , '1990-01-20' , '女', '2');

成绩表SC

create table SC(Sid int, Cid int, score decimal(18,1));
insert into SC values('1' , '1' , 80);
insert into SC values('1' , '2' , 90);
insert into SC values('1' , '3' , 99);
insert into SC values('2' , '1' , 70);
insert into SC values('2' , '2' , 60);
insert into SC values('2' , '3' , 80);
insert into SC values('3' , '1' , 80);
insert into SC values('3' , '2' , 80);
insert into SC values('3' , '3' , 80);
insert into SC values('4' , '1' , 50);
insert into SC values('4' , '2' , 30);
insert into SC values('4' , '3' , 20);
insert into SC values('5' , '1' , 76);
insert into SC values('5' , '2' , 87);
insert into SC values('6' , '1' , 31);
insert into SC values('6' , '3' , 34);
insert into SC values('7' , '2' , 89);
insert into SC values('7' , '3' , 98);

课程表Course

create table Course(Cid int primary key,Cname varchar(10),Tid varchar(10));
insert into Course values('1' , '语文' , '2');
insert into Course values('2' , '数学' , '1');
insert into Course values('3' , '英语' , '3');

教师表Teacher

create table Teacher(
	Tid int primary key auto_increment,
	Tname varchar(10),
	Tsex TINYINT default 1,
	Tbirthday date,
	Taddress varchar(255),
	Temail varchar(255),
	Tmoney DECIMAL(20,2)
 
);
insert into Teacher values('1' , '张三',1,'1988-1-15','陕西咸阳','zhangsan@qq.com',3000.00);
insert into Teacher values('2' , '李四',0,'1992-5-9','陕西宝鸡','lisi@qq.com',4000.00);
insert into Teacher values('3' , '王五',1,'1977-7-1','山西太原','wangwu@qq.com',5000.00);

班级表Class

create table Class(classid int primary key, classname varchar(20));
insert into Class values('1', '一班');
insert into Class values('2', '二班');

1. 查询” 01 “课程比” 02 “课程成绩高的学生的信息及课程分数

💡想法：

-- 1.	查询” 01 “课程比” 02 “课程成绩高的学生的信息及课程分数
SELECT * FROM student 
INNER JOIN 
(SELECT * FROM sc WHERE cid = 1)t1 ON student.sid = t1.sid
INNER JOIN 
(SELECT * FROM sc WHERE cid = 2)t2 ON student.sid = t2.sid
WHERE t1.score > t2.score;


SELECT s.*,t1.score
FROM student s,
	(SELECT sid,cid,score FROM sc WHERE cid = 1)t1,
	(SELECT sid,cid,score FROM sc WHERE cid = 2)t2
WHERE s.sid=t1.sid AND t1.sid=t2.sid AND t1.score>t2.score;

答案：

-- 答案1:
SELECT s.*,t1.score,t2.score
FROM student s,
	(SELECT sid,cid,score FROM sc WHERE cid = 1)t1,
	(SELECT sid,cid,score FROM sc WHERE cid = 2)t2
WHERE s.sid=t1.sid AND t1.sid=t2.sid AND t1.score>t2.score;
-- 答案2:
SELECT s.*,t1.score,t2.score
FROM student s,sc t1,sc t2
WHERE s.Sid=t1.Sid AND t1.Sid=t2.Sid AND (t1.Cid=1 AND t2.Cid=2)
		AND t1.score>t2.score;

2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

💡想法：

SELECT sc.sid,sname,AVG(score) AS num
FROM sc JOIN student ON sc.sid=student.sid
GROUP BY sc.sid 
HAVING num>=60;

答案：

-- 答案1
SELECT *
FROM student s,(
	SELECT sid,AVG(score) avg_score
	FROM sc 
	GROUP BY sid) t2
WHERE s.sid=t2.sid AND t2.avg_score>=60; 

-- 答案2
SELECT student.`*`,AVG(score) avg_score
FROM student LEFT JOIN sc ON student.Sid=sc.Sid
GROUP BY student.Sid
HAVING avg_score>=60;

3. 查询在 SC 表存在成绩的学生信息

💡想法：

SELECT student.*
FROM sc LEFT JOIN student
ON sc.Sid=student.Sid
GROUP BY sc.Sid;

答案：

SELECT * 
FROM student 
WHERE sid IN (
	SELECT DISTINCT  sid 
	from sc 
	WHERE  score IS NOT NULL);

4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

💡想法：

SELECT student.sid,sname,COUNT(cid),SUM(score)
FROM student LEFT JOIN sc
ON student.sid = sc.sid
GROUP BY sid;

答案：

SELECT * 
FROM student 
LEFT JOIN  (SELECT sid,COUNT(*),SUM(score) FROM sc GROUP BY sid) t1
ON  student.sid = t1.sid;

5. 查询「李」姓老师的数量

答案：

SELECT COUNT(*)
FROM teacher
WHERE tname LIKE '李%';

6. 查询学过「张三」老师授课的同学的信息

💡想法：

-- step1:查询张三老师教的课
SELECT tid FROM teacher WHERE tname='张三';

-- step2:查询课程表,老师带的哪个班
SELECT cid FROM course WHERE tid=1;

-- step3:查询cid为2的sid
SELECT sid 
FROM sc 
WHERE cid=2;

-- step4:查询sid对应的学生信息
SELECT * 
FROM student,sc 
WHERE student.Sid=sc.Sid AND cid=2

-- 总的
SELECT * 
FROM student,sc 
WHERE student.Sid=sc.Sid AND 
cid=(SELECT cid FROM course WHERE 
	tid=(SELECT tid FROM teacher WHERE tname='张三')
);

答案：

SELECT  * 
FROM student
WHERE EXISTS (
    SELECT * 
    FROM sc
    WHERE cid IN (
	    SELECT cid
	    FROM teacher t1,course t2
	    WHERE tname = '张三' AND t1.tid = t2.tid
	 ) AND student.sid = sc.sid
)

7. 查询没有学全所有课程的同学的信息

💡想法：

SELECT student.*,COUNT(*) course_num
FROM student
LEFT JOIN sc ON student.sid = sc.Sid
GROUP BY student.sid
HAVING course_num<(
	select COUNT(*) FROM course
);

答案：

SELECT student.* 
FROM  student 
LEFT  JOIN  sc ON  student.sid = sc.sid
GROUP  BY  student.sid 
HAVING  count(cid) < (SELECT  count(*) FROM   course);

8. 查询至少有一门课与学号为” 01 “的同学所学相同的同学的信息

💡想法：

-- 赵雷学了科目内容1 2 3
-- 至少1,2,3 1,2 1,2,4 1,4
SELECT student.* 
FROM student INNER JOIN sc ON student.Sid=sc.Sid
WHERE student.Sid NOT IN(
	SELECT sid FROM sc WHERE cid NOT IN(
		SELECT cid FROM sc WHERE sid=1
	)
)
GROUP BY student.Sid;

答案：

SELECT  DISTINCT student.*
FROM student,(SELECT * 
FROM sc
WHERE  cid IN(
	SELECT cid
	FROM sc 
	WHERE sid = 7))t1
WHERE student.sid = t1.sid

9. 查询和” 01 “号的同学学习的课程完全相同的其他同学的信息

💡想法：

-- step1:查询01号同学学习的课程
SELECT cid FROM sc WHERE sid =1
-- step2:查询学习1,2,3课程的人的名字
SELECT s.*,t1.*,t2.*
FROM student s,
(SELECT sid,cid,score FROM sc WHERE cid=1)t1,
(SELECT sid,cid,score FROM sc WHERE cid=2)t2,
(SELECT sid,cid,score FROM sc WHERE cid=3)t3
WHERE s.Sid=t1.sid AND t1.sid=t2.sid AND t2.sid = t3.sid

-- 总的【错误的,查询不是动态的】
SELECT s.*
FROM student s,
     (SELECT sid, cid, score FROM sc WHERE cid = 1) t1,
     (SELECT sid, cid, score FROM sc WHERE cid = 2) t2,
     (SELECT sid, cid, score FROM sc WHERE cid = 3) t3
WHERE s.sid = t1.sid
  AND t1.sid = t2.sid
  AND t2.sid = t3.sid;

答案：

-- step1:内连接
SELECT *
FROM student
INNER JOIN sc ON student.sid = sc.sid
-- step2:查询课程在都01的学生选课的信息中的学生
SELECT student.sid,student.Sname,COUNT(*)
FROM student
INNER JOIN sc ON student.sid = sc.sid
WHERE 
student.sid NOT IN(
	SELECT sid
	FROM sc
	WHERE cid NOT IN(
		SELECT cid FROM sc WHERE sid='01'
	)
)
GROUP BY student.sid,student.sname
HAVING COUNT(cid) = (SELECT COUNT(*) FROM sc WHERE sid=1);

10. 查询没学过”张三”老师讲授的任一门课程的学生姓名

💡想法：

SELECT Sname 
FROM student 
WHERE Sid NOT IN(
  SELECT DISTINCT Sid FROM sc WHERE Cid=(
    SELECT Cid FROM course WHERE Tid=(
			SELECT Tid FROM teacher WHERE Tname='张三'
		)
	)
);

答案：

SELECT  * 
FROM student
WHERE NOT  EXISTS (
    SELECT * 
    FROM sc
    WHERE cid IN (
	    SELECT cid
	    FROM teacher t1,course t2
	    WHERE tname = '张三' AND t1.tid = t2.tid
	 ) AND student.sid = sc.sid
);

11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

答案：

SELECT sc.Sid,sname,AVG(score)
FROM sc,student
WHERE score<60 AND sc.Sid=student.Sid
GROUP BY sc.Sid
HAVING COUNT(*)>=2;

12. 检索” 01 “课程分数小于 60，按分数降序排列的学生信息

💡想法：

SELECT student.*
FROM student
INNER JOIN sc ON student.Sid=sc.Sid
WHERE score < 60 AND cid = 1
ORDER BY score DESC;

答案：

SELECT * 
FROM student,sc 
WHERE student.sid = sc.sid AND cid = '01' AND sc.score<60
ORDER BY score DESC;

13. 查询各科成绩最高分、最低分和平均分，以如下形式显示：

课程 ID，课程 name，最高分，最低分，平均分，
及格率，中等率，优良率，优秀率(及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90）。

答案1

SELECT sc.Cid AS '课程ID',cname '课程',
MAX(score) AS '最高分',
MIN(score) AS '最低分',
AVG(score) AS '平均分',
(
	(SELECT COUNT(sid) FROM sc WHERE score>=60 AND sc.Cid=course.Cid)/
	(SELECT COUNT(sid) FROM sc WHERE sc.Cid=course.Cid)
)AS '及格率',
(
	(SELECT COUNT(sid) FROM sc WHERE score>=70 AND score<80 AND sc.Cid=course.Cid)/
	(SELECT COUNT(sid) FROM sc WHERE sc.Cid=course.Cid)
)AS '中等率',
(
	(SELECT COUNT(sid) FROM sc WHERE score>=80 AND score<90 AND sc.Cid=course.Cid)/
	(SELECT COUNT(sid) FROM sc WHERE sc.Cid=course.Cid)
)AS '优良率',
(
	(SELECT COUNT(sid) FROM sc WHERE score>=90 AND sc.Cid=course.Cid)/
	(SELECT COUNT(sid) FROM sc WHERE sc.Cid=course.Cid)
)AS '优秀率'
FROM sc
INNER JOIN course ON sc.cid = course.cid
GROUP BY course.Cid;

答案2

SELECT 
	sc.Cid AS '课程ID',
	course.cname AS '课程',
	MAX(sc.score) AS '最高分',
	MIN(sc.score) AS '最低分',
	AVG(sc.score) AS '平均分',
	SUM(CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END) / COUNT(sc.sid) AS '及格率',
	SUM(CASE WHEN sc.score >= 70 AND sc.score < 80 THEN 1 ELSE 0 END) / COUNT(sc.sid) AS '中等率',
	SUM(CASE WHEN sc.score >= 80 AND sc.score < 90 THEN 1 ELSE 0 END) / COUNT(sc.sid) AS '优良率',
	SUM(CASE WHEN sc.score >= 90 THEN 1 ELSE 0 END) / COUNT(sc.sid) AS '优秀率'
FROM 
	sc
INNER JOIN 
	course ON sc.cid = course.cid
GROUP BY 
	sc.Cid, course.cname;

答案3：

SELECT 
	sc.Cid AS '课程ID',
	course.cname AS '课程',
	MAX(sc.score) AS '最高分',
	MIN(sc.score) AS '最低分',
	AVG(sc.score) AS '平均分',
	COUNT(case when sc.score>=60 then score END)/COUNT(*) AS '及格率',
	COUNT(case when sc.score>=70 AND sc.score<80 then score END)/COUNT(*) AS '中等率',
	COUNT(case when sc.score>=80 AND sc.score<90 then score END)/COUNT(*) AS '优良率',
	COUNT(case when sc.score>=90 then score END )/COUNT(*) AS '优秀率'
FROM 
   sc
INNER JOIN 
   course ON sc.cid = course.cid
GROUP BY 
   sc.Cid, course.cname;