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);2：一个简单案例SQL编写
t.tname ,s.ssex , (s.sid) teacher t student s t.tid (s.sid) (s.sid)
3：SQL执行顺序和书写顺序
查询字段 查询的（左）表名称 连接查询右边表 表连接的连接条件 数据查询条件 字段分组信息 CUBEROLLUP 超组统计 处理后的筛选条件 排序字段 LIMIT 查询数据长度
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子句
