MySQL中SQL语句的执行顺序

【微|信|公|众|号：厦门微思网络】

一：SQL语句的执行顺序

　　作为一个开发人员，在开发中基本上每时每刻都要和数据库打交到；虽然写过无数的SQL语句，但是写好一个SQL可不是这么简单的，它涉及到各式各样的优化和书写方式；但下面我以MySQL中的SQL执行顺序来作为讲解，对其进行剖析。

1：SQL数据的准备

　　为了可以为下文做铺垫，这里我创建了一些数据，后面将以SQL案例的方式说明SQL语句执行的顺序

-- 创建数据库表
create database if not exists demo_order_school charset utf8;
-- 使用表
USE demo_order_school
-- 创建辅导员老师表
create table if not exists teacher(
    tid int primary key auto_increment COMMENT '编号', 
    tname varchar(5) not null COMMENT '姓名',          
    tsex enum('男','女') default '男' COMMENT '性别',   
    tage tinyint unsigned COMMENT '年龄',              
    tsalary decimal(6,1) default 0 COMMENT '工资 最高99999.9', 
    taddress varchar(10) COMMENT '住址'
)charset utf8;
-- 创建学生表
create table if not exists student (
    sid int(11) primary key auto_increment COMMENT '编号',
    sname varchar(5) not null COMMENT '姓名',
    ssex enum('男','女') default '男' COMMENT '性别', 
    sage tinyint(11) unsigned not null default 20 COMMENT '年龄', 
    saddress varchar(10) COMMENT '住址',
    senrol date default '2020-12-12' COMMENT '入学时间',
    tid int COMMENT '连接老师id'
)charset utf8;
-- 添加老师数据
insert into teacher (tid,tname,taddress)values
(1,'张老师','江苏南京'),(2,'李老师','江苏无锡'),
(3,'王老师','江苏常熟'),(4,'孙老师','江苏苏州');
-- 添加学生数据
insert into student(sid,sname,saddress)values
(1 ,"王生安","安徽六安"),(2 ,"李鑫灏","安徽合肥"),(3 ,"薛佛世","安徽蚌埠"),(4 ,"蔡壮保","安徽安庆"),
(5 ,"钱勤堃","安徽合肥"),(6 ,"潘恩依","安徽合肥"),(7 ,"陈国柏","安徽六安"),(8 ,"魏皑虎","安徽六安"),
(9 ,"周卓浩","安徽六安"),(10,"汤辟邦","安徽六安"),(11,"张顺谷","安徽淮南"),(12,"张悌斯","安徽淮南"),
(13,"张灶冲","安徽蚌埠"),(14,"章晞旺","安徽淮南"),(15,"易江维","安徽合肥"),(16,"孙生笙","安徽蚌埠"),
(17,"饶展林","安徽蚌埠"),(18,"岳列洋","安徽合肥"),(19,"时党舒","安徽六安"),(20,"周迟蒲",null),
(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,"张桥共",null),
(49,"莫征意","安徽合肥"),(50,"张棉党","安徽合肥"),(51,"葛星申","安徽六安"),(52,"张 婷","安徽六安"),
(53,"王 珊","安徽合肥"),(54,"王觉飘","安徽合肥"),(55,"魏爱來","安徽蚌埠"),(56,"池樵霆","安徽淮南"),
(57,"李澜才","安徽六安"),(58,"孙 熹","安徽六安"),(59,"黎宝安","安徽六安"),(60,"夏两吟","安徽六安"),
(61,"易 鲁","安徽六安"),(62,"路党拓","安徽合肥"),(63,"魏沁霭","安徽合肥"),(64,"鲍大莉","安徽六安");
-- 处理数据
update student set ssex=ceil(rand()*2),sage=ceil(rand()*5+20), 
senrol=concat(ceil(rand()*3+2017),'-' , ceil(rand()*12) , '-',ceil(rand()*20));
update student set tid=ceil(rand()*4);

1

2：一个简单案例SQL编写

连接查询`teacher`表和`student`表，并对其分组，统计出每个老师手下的男女学生个数，并过滤出总是小于等于8的人数，降序排列
SELECT DISTINCT t.tname '老师姓名',s.ssex '学生性别', COUNT(s.sid) '学生总数'
    FROM teacher t LEFT JOIN student s ON t.tid = s.tid
    GROUP BY t.tname,s.ssex
    HAVING COUNT(s.sid) >=8 ORDER BY COUNT(s.sid) DESC LIMIT 3;
    +--------------+--------------+--------------+
    | 老师姓名      | 学生性别       | 学生总数      |
    +--------------+--------------+--------------+
    | 孙老师        | 男           |           11 |
    | 张老师        | 女           |            9 |
    | 王老师        | 女           |            9 |
    +--------------+--------------+--------------+
通过上面的一个案例可以发现，数据是查询出来了，但是具体的SQL执行顺序是什么呢？是先执行’WHERE‘子句还是先执行‘HAVING’子句呢？下面就一起说的透：

3：SQL执行顺序和书写顺序

执行顺序                     书写顺序                  
    8            SELECT
    9            DISTINCT                <查询字段>
    1            FROM                    <查询的（左）表名称>
    3            [LEFT|RIGHT|INNER] JOIN <连接查询右边表> 
    2            ON                      <表连接的连接条件>
    4            WHERE                   <数据查询条件>
    5            GROUP BY                <字段分组信息>
    6            WITH CUBE|ROLLUP        <超组统计>
    7            HAVING                  <处理后的筛选条件>
    10           ORDER BY [ASC|DESC]     <排序字段>
    11           LIMIT                   <查询数据长度>
 
说明：从这个顺序中我们不难发现，所有的查询语句都是从FROM子句开始执行的，在执行过程中，每个步骤都会为下一个步骤生成一个虚拟   表，这个虚拟表将作为下一个执行步骤的输入。
 
第一步：首先对FROM子句中的前两个表执行一个笛卡尔乘积，此时生成虚拟表 vt1（若有多表时选择相对小的表做基础表）
第二步：接下来使用ON筛选器，ON中的逻辑表达式将应用到 vt1 中的各个行，筛选出满足ON逻辑表达式的行，生成虚拟表 vt2
第三步：如果是OUTER JOIN那么这一步就将添加第二步被过滤的数据，LEFT OUTER JION就把左表在第二步中过滤的添加进来，
       如果是RIGHT OUTER JOIN那么就将右表在第二步中过滤掉的行添加进来，这样生成虚拟表 vt3
    注意：如果FROM子句中的表数目多余两个表，那么就将vt3和第三个表连接从而计算笛卡尔乘积，生成虚拟表，该过程就是一个重复1-3的步骤，最终得到一个新的虚拟表 vt3。
第四步：使用WHERE筛选器，对上一步生产的虚拟表vt3引用WHERE筛选器，筛选后生成虚拟表vt4；
第五步：GROUP BY子句将指定的列值组合成为一组，得到虚拟表vt5。如果应用了GROUP BY，那么后面的所有步骤都只能得到的vt5的列
       或者是聚合函数（COUNT、SUM、AVG等）。原因在于最终的结果集中只为每个组包含一行。这一点请牢记。
第六步：应用WITH CUBE（在MySQL中不支持）或WITH ROLLUP（支持）选项，为vt5生成超组，生成vt6
第七步：应用HAVING筛选器，生成vt7。HAVING筛选器是第一个也是为唯一一个可以使用到已分组数据的筛选器。
第八步：处理SELECT子句。将vt7虚拟表在SELECT中出现的列筛选出来。生成vt8。
第九步：应用DISTINCT子句，vt8中移除相同的行，生成vt9。事实上如果应用了GROUP BY子句那么DISTINCT是多余的；
       其实分组的时候是将列中相同的值分成一组，同时只为每一组返回一行记录，那么所有的记录都将是不相同的。
第十步：应用ORDER BY子句。按照ORDER_BY_CONDITION（指定排序的字段）排序vt9，此时返回的一个游标，而不是虚拟表。SQL是基于集合理论的，集合不会预先对它的行排序，它只是成员的逻辑集合，成员的顺序是无关紧要的。对表进行排序的查询可以返回一个对象，这个对象包含特定的物理顺序的逻辑组织。这个对象就叫游标。正因为返回值是游标，那么使用ORDER BY子句查询不能应用于表达式。排序是很需要成本的，除非你必须要排序，否则最好不要指定ORDER BY，最后，在这一步中是第一个也是唯一一个可以使用SELECT列表中别名的步骤。
第十一步：使用LIMIT制定需要返回的行数
补充说明：
    ①：对于包含OUTER JOIN子句的查询，到底是在ON筛选器里指定逻辑过滤条件还是用WHERE筛选器指定逻辑过滤条件呢？
      其实ON和WHERE的最大区别在于，如果在ON应用逻辑表达式那么在第三步OUTER JOIN中还可以把移除的行再次添加回来，而WHERE一旦被移除那是找不回来的，那就代表左外连接和右外连接特有数据就丢失了。
     简单来说就是，当有外关联表时（左连接和右连接），ON主要是针对外关联表进行筛选，保留主要的一方表信息；当不是外关联表时，两者的作用其实是一样的；例如左外连接时，首先执行ON，筛选掉外连表中不符合ON表达式的数据，而WHERE的筛选是对ON关联后的结果集进行筛选。
    假设有一个学生表（班级,姓名）和一个成绩表(姓名,成绩)，现在需要返回一个X班级的全体同学的成绩，但是这个班级有几个学生缺考，也就是说在成绩表中没有记录。为了得到我们预期的结果我们就需要在ON子句指定学生和成绩表的关系（学生.姓名=成绩.姓名）那么我们是否发现在执行第二步的时候，对于没有参加考试的学生记录就不会出现在vt2中，因为他们被ON的逻辑表达式过滤掉了,但是我们用LEFT OUTER JION就可以把左表（学生）中没有参加考试的学生找回来，因为我们想返回的是X班级的所有学生，如果在ON中应用学生.班级='n'的话，LEFT OUTER JION会把n班级的所有学生记录找回；若在WHERE中删选学生.班级='n'的话，只打印n班级学生，其它信息则会被永久过滤。

4：补充和案例SQL说明

MySQL查询语句的七大子句标准形式：
    一个标准的SELECT语句可以包含多个子句，SELECT语句的定义如下：
        <SELECT clause> [<FROM clause>] [<WHERE clause>] [<GROUP BY clause>] 
            [<HAVING clause>] [<ORDER BY clause>] [<LIMIT clause>]
    其中SELECT子句是必选项，其余的如WHERE子句、GROUP BY子句等等都是可有可无的；一个SELECT语句中，子句的顺序是严格固定的。
    例如GROUP BY子句不会位于WHERE子句的前面。 
 
SELECT语句的执行顺序：
    上面说了MySQL查询语句的标准形式，我们知道SQL查询语句的结构，但是这并不意味着每次一个SELECT语句都是从SELECT子句开始执行的，而标准的七大子句执行顺序如下（具体可以参考第3小节）：
        开始-> FROM子句->WHERE子句->GROUP BY子句->HAVING子句->ORDER BY子句->SELECT子句->LIMIT子句-> 最终结果 每个子句执行后都会产生一个中间结果（虚拟表），供接下来的子句使用，如果不存在某个子句，就跳过。
 
案例（参考第2小节的SQL语句阐述）：
    SELECT DISTINCT t.tname '老师姓名',s.ssex '学生性别', COUNT(s.sid) '学生总数'
    FROM teacher t LEFT JOIN student s ON t.tid = s.tid
    GROUP BY t.tname,s.ssex
    HAVING COUNT(s.sid) >=8 
    ORDER BY COUNT(s.sid) DESC 
    LIMIT 3;
    ①：首先会执行FROM子句把`teacher表`和`student表`连接在一起做笛卡儿积，然后就生成了vt1表，然后拿着vt1表来使用ON筛选器进行筛选，得到具体的vt2表
    ②：执行GROUP BY子句, 把`teacher表`的t.tname和`student表`的s.ssex进行分组
    ③：计算 COUNT() 聚集函数, 按 "学生ID" 求出学生总数
    ④：执行 HAVING 子句, 筛选'学生总数'大于等于8的
    ⑥：执行 ORDER BY 子句, 把最后的结果按学生人数降序排列
    ⑥：执行 LIMIT 子句限制查询条数为3条记录
    说明：其实上述语句我并未使用到WHERE子句