心得技巧
从建表到关于sql和MySQL的查询语句执行顺序最新详解
发布时间：2022-07-01 发布网站：脚本宝典
脚本宝典收集整理的这篇文章主要介绍了从建表到关于sql和MySQL的查询语句执行顺序最新详解，脚本宝典觉得挺不错的，现在分享给大家，也给大家做个参考。
目录
- SELECT语句关键字的定义顺序
- SELECT语句关键字的执行顺序
- 建表和准备数据（DML）
- 新建测试数据库players
- 创建测试表 star, ballvalue, starworth
- 新增测试数据
- 准备SQL逻辑查询测试语句
- 执行顺序分析（重点）
- (1) 执行FROM语句
- (2) 执行ON过滤
- (3) 添加外部行
- (4) 执行WHERE过滤
- (5) 执行GROUP BY分组
- (6) 执行HAVING过滤
- (7) 执行DISTINCT子句
- (8) 执行ORDER BY子句
- (9) 执行LIMIT子句
SELECT语句关键字的定义顺序
序号为关键字执行顺序：
select distinct --------- 7777
...
from --------- 1111
...
join --------- 2222
...
on --------- 3333
...
where --------- 4444
...
group by --------- 5555
...
having --------- 6666
...
order by --------- 8888
...
limit --------- 9999
...
SELECT语句关键字的执行顺序
- from
- join
- on
- where
- group by
- having
- select
- distinct
- order by
- limit
建表和准备数据（DML）
新建测试数据库players
create database players;
通过查找数据库能看到新建的数据库：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hyqwsq |
| information_schema |
| mysql |
| performance_schema |
| players |
| sys |
| testdb |
+--------------------+
7 rows in set (0.00 sec)
创建测试表 star, ballvalue, starworth
创建表1，设置参数有 球星 (star)，球队 (team)，上场时间 (time)：
CREATE TABLE star (
star VARCHAR ( 10 ) NOT NULL,
team VARCHAR ( 10 ) NOT NULL,
time INT NOT NULL,
PRIMARY KEY ( star )) ENGINE = INNODB DEFAULT charset = utf8;
创建表2，设置参数有 进球数 (goal)，球星 (star)，得分 (score)：
CREATE TABLE ballvalue (
goal INT NOT NULL auto_increment,
star VARCHAR ( 10 ),
score INT NOT NULL,
PRIMARY KEY ( goal )) ENGINE = INNODB DEFAULT charset = utf8;
创建表3，设置参数有 上场时间 (time)，上场时间等级 (worth)：
CREATE TABLE starworth (
time INT NOT NULL,
worth INT NOT NULL,
PRIMARY KEY ( time )) ENGINE = INNODB DEFAULT charset = utf8;
新增测试数据
insert into star(star,team,time) values('James','Lakes',35);
insert into star(star,team,time) value('Kobe','Lakes',40);
insert into star value('alex','bull',10);
insert into star values('Frank','det',16);
insert into star values('Tyler','bull',15);
insert into star values('cory','det',22);
insert into star values('cooper','eagle',28);
insert into star values('hunter','eagle',32);
insert into star values('Gorden','hou',18);
insert into star values('Green','hou',30);
insert into ballvalue value(28,'Kobe',81);
insert into ballvalue value(30,'James',75);
insert into ballvalue value(20,'hunter',45);
insert into ballvalue value(3,'Tyler',9);
insert into ballvalue value(13,'Green',25);
insert into ballvalue value(8,'Frank',18);
insert into ballvalue value(6,'cory',12);
insert into ballvalue value(15,'cooper',30);
insert into ballvalue value(5,'alex',15);
insert into ballvalue value(4,'Gorden',8);
insert into starworth value(10,27);
insert into starworth value(28,60);
insert into starworth value(22,56);
insert into starworth value(16,40);
insert into starworth value(18,48);
insert into starworth value(30,75);
insert into starworth value(32,80);
insert into starworth value(35,95);
insert into starworth value(40,99);
insert into starworth value(15,32);
添加好数据后可以查看表：
mysql> select * from starworth;
+------+-------+
| time | worth |
+------+-------+
| 10 | 27 |
| 15 | 32 |
| 16 | 40 |
| 18 | 48 |
| 22 | 56 |
| 28 | 60 |
| 30 | 75 |
| 32 | 80 |
| 35 | 95 |
| 40 | 99 |
+------+-------+
10 rows in set (0.00 sec)
mysql> select * from star;
+--------+-------+------+
| star | team | time |
+--------+-------+------+
| alex | bull | 10 |
| cooper | eagle | 28 |
| cory | det | 22 |
| Frank | det | 16 |
| Gorden | hou | 18 |
| Green | hou | 30 |
| hunter | eagle | 32 |
| James | Lakes | 35 |
| Kobe | Lakes | 40 |
| Tyler | bull | 15 |
+--------+-------+------+
10 rows in set (0.00 sec)
mysql> select * from ballvalue;
+------+--------+-------+
| goal | star | score |
+------+--------+-------+
| 3 | Tyler | 9 |
| 4 | Gorden | 8 |
| 5 | alex | 15 |
| 6 | cory | 12 |
| 8 | Frank | 18 |
| 13 | Grean | 25 |
| 15 | cooper | 30 |
| 20 | hunter | 45 |
| 28 | Kobe | 81 |
| 30 | James | 75 |
+------+--------+-------+
10 rows in set (0.00 sec)
准备SQL逻辑查询测试语句
写一个查询测试语句： 查询出场时间等级总和大于90的球队中总得分超过60的球队
select
n.team,
sum( n.worth ),
sum( n.time ),
sum( b.score )
from
(select
s.star,
s.team,
sw.worth,
sw.time
from
star s
join starworth sw
on s.time = sw.time
where
s.time > 10
) n
join ballvalue b
on b.star = n.star
group by
n.team
having
sum( b.score ) > 60
order by
sum( b.score ) desc;
习惯看长句的同学看这里：
mysql> select n.team,sum(n.worth),sum(b.score)
-> from(select s.star, s.team, sw.worth, sw.time from star s join starworth sw on s.time = sw.time where s.time > 10) n
-> join ballvalue b on b.star = n.star
-> group by n.team having sum(b.score) > 60 order by sum(b.score) desc;
执行顺序分析（重点）
在这些SQL语句的执行过程中，每次执行都会产生一个虚拟表，用来保存SQL语句的执行结果（这是重点），跟踪这个虚拟表的变化，就能得到最终的查询结果的过程，用来分析整个SQL逻辑查询的执行顺序和过程。
(1) 执行FROM语句
通过观察最终的查询语句可以发现我写的是嵌套子查询，所以先分析子查询语句：
select
s.star, s.team, sw.worth, sw.time
from
star s
join starworth sw on s.time = sw.time
where
s.time > 10
第一步，执行 from 语句。from 就是告诉你最开始从哪个表开始查询，现在有 star as s 和 starworth as sw 两个表，而两表连接是通过 笛卡尔积 来连接
所谓笛卡尔积,通俗点说就是指包含两个集合中任意取出两个元素构成的组合的集合
经过 from 语句对两个表执行笛卡尔积，会得到一个虚拟表，先叫XB1 (虚拟表1)，内容如下：
mysql> select * from star join starworth;
+--------+-------+------+------+-------+
| star | team | time | time | worth |
+--------+-------+------+------+-------+
| Tyler | bull | 15 | 10 | 27 |
| Kobe | Lakes | 40 | 10 | 27 |
| James | Lakes | 35 | 10 | 27 |
| hunter | eagle | 32 | 10 | 27 |
| Green | hou | 30 | 10 | 27 |
| Gorden | hou | 18 | 10 | 27 |
| Frank | det | 16 | 10 | 27 |
...
...中间数据过多，我删了一部分，方便理解就好
...
| hunter | eagle | 32 | 40 | 99 |
| Green | hou | 30 | 40 | 99 |
| Gorden | hou | 18 | 40 | 99 |
| Frank | det | 16 | 40 | 99 |
| cory | det | 22 | 40 | 99 |
| cooper | eagle | 28 | 40 | 99 |
| alex | bull | 10 | 40 | 99 |
+--------+-------+------+------+-------+
100 rows in set (0.00 sec)
总共有100（star 的记录条数 * starworth 的记录条数）条记录。这就是 XB1 的结果，接下来的操作就在 XB1 的基础上进行。
(2) 执行ON过滤
执行完笛卡尔积以后，接着就进行 on s.time = sw.time 条件过滤，根据 on 中指定的条件，去掉那些不符合条件的数据，得到 XB2 表，内容如下：
mysql> select * from star s join starworth sw on s.time = sw.time;
+--------+-------+------+------+-------+
| star | team | time | time | worth |
+--------+-------+------+------+-------+
| alex | bull | 10 | 10 | 27 |
| cooper | eagle | 28 | 28 | 60 |
| cory | det | 22 | 22 | 56 |
| Frank | det | 16 | 16 | 40 |
| Gorden | hou | 18 | 18 | 48 |
| Green | hou | 30 | 30 | 75 |
| hunter | eagle | 32 | 32 | 80 |
| James | Lakes | 35 | 35 | 95 |
| Kobe | Lakes | 40 | 40 | 99 |
| Tyler | bull | 15 | 15 | 32 |
+--------+-------+------+------+-------+
10 rows in set (0.00 sec)
XB2 就是经过 on 条件筛选以后得到的有用数据，而接下来的操作将在 XB2 的基础上继续进行。
(3) 添加外部行
这一步只有在连接类型为 outer join 时才发生，如 left outer join, right outer join 和 full outer join。在大多数的时候，我们都是会省略掉 outer 关键字的，但 outer 表示的就是外部行的概念。
我创建的数据库的表里没有null值，所以这里用orcle的数据库来测试左外连接和右外连接：
left outer join 把左表记为主表，主表数据都被保留，得到的结果为：
mysql> select * from emp e left join dept d on e.deptno = d.deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
14 rows in set (0.00 sec)
right outer join 把右表记为主表，主表数据都被保留，得到的结果为：
mysql> select * from emp e right join dept d on e.deptno = d.deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 40 | OPERATIONS | BOSTON |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
15 rows in set (0.00 sec)
所以当右表有值但左表没有对应值符合 on 的条件，会保留主表的数据，给左表全都配上 NULL
full outer join 把左右表都作为保留表
总结：添加外部行的工作就是在 XB2 表的基础上添加保留表中被过滤条件过滤掉的数据，非保留表中的数据被赋予NULL值，最后生成虚拟表XB3
回到新建的数据库中，因为我的查询语句用的是 left join，所以 XB3 为：
mysql> select s.star,s.team,sw.worth,sw.time from star s join starworth sw on s.time = sw.time;
+--------+-------+-------+------+
| star | team | worth | time |
+--------+-------+-------+------+
| alex | bull | 27 | 10 |
| cooper | eagle | 60 | 28 |
| cory | det | 56 | 22 |
| Frank | det | 40 | 16 |
| Gorden | hou | 48 | 18 |
| Green | hou | 75 | 30 |
| hunter | eagle | 80 | 32 |
| James | Lakes | 95 | 35 |
| Kobe | Lakes | 99 | 40 |
| Tyler | bull | 32 | 15 |
+--------+-------+-------+------+
10 rows in set (0.00 sec)
(4) 执行WHERE过滤
对添加外部行得到的 XB3 进行 where 过滤，只有符合 <where_condition> 的记录才会输出到虚拟表 XB4中。当我们执行 where s.time > 10 的时候，就会得到以下内容，并存在虚拟表 XB4 中：
mysql> select s.star,s.team,sw.worth,sw.time from star s join starworth sw on s.time = sw.time where s.time > 10;
+--------+-------+-------+------+
| star | team | worth | time |
+--------+-------+-------+------+
| cooper | eagle | 60 | 28 |
| cory | det | 56 | 22 |
| Frank | det | 40 | 16 |
| Gorden | hou | 48 | 18 |
| Green | hou | 75 | 30 |
| hunter | eagle | 80 | 32 |
| James | Lakes | 95 | 35 |
| Kobe | Lakes | 99 | 40 |
| Tyler | bull | 32 | 15 |
+--------+-------+-------+------+
9 rows in set (0.00 sec)
可以发现 XB4 比 XB3 少了一条数据，是被 where s.time > 10 过滤掉的：
| star | team | worth | time |
+--------+-------+-------+------+
| alex | bull | 27 | 10 |
但是在使用 where 子句时，需要注意以下两点：
- 由于数据还没有分组，因此现在还不能在 where 过滤器后加分组函数（avg,sum,couunt…）这类对分组统计的过滤；
- 由于还没有进行列的选取操作，因此在 select 中使用列的别名也是不被允许的，如：select star as r from t where r = ‘Kobe’; 即 select 对象的别名不能出现在 where
- 总结：where 不能跟分组函数 和 select 后起的别名
到这里子查询的语句就分析完了：
select
s.star, s.team, sw.worth, sw.time
from
star s
join starworth sw on s.time = sw.time
where
s.time > 10
接着看嵌套子查询的语句：
select
n.team,
sum( n.worth ),
sum( n.time ),
sum( b.score )
from
(子查询语句) n ---查询出来的 XB4 起别名为 n
join ballvalue b
on b.star = n.star
group by
n.team
having
sum( b.score ) > 60
order by
sum( b.score ) desc;
按照上述顺序先执行 from 语句： 连接表 n 与 ballvalue 两个表，同样进行笛卡尔积连接：
mysql> select * from (select s.star,s.team,sw.worth,sw.time from star s join starworth sw on s.time = sw.time where s.time > 10) n join ballvalue b;
+--------+-------+-------+------+------+--------+-------+
| star | team | worth | time | goal | star | score |
+--------+-------+-------+------+------+--------+-------+
| Tyler | bull | 32 | 15 | 3 | Tyler | 9 |
| Kobe | Lakes | 99 | 40 | 3 | Tyler | 9 |
| James | Lakes | 95 | 35 | 3 | Tyler | 9 |
| hunter | eagle | 80 | 32 | 3 | Tyler | 9 |
...
... 同样数据太多，删掉中间部分方便观察
...
| Green | hou | 75 | 30 | 30 | James | 75 |
| Gorden | hou | 48 | 18 | 30 | James | 75 |
| Frank | det | 40 | 16 | 30 | James | 75 |
| cory | det | 56 | 22 | 30 | James | 75 |
| cooper | eagle | 60 | 28 | 30 | James | 75 |
+--------+-------+-------+------+------+--------+-------+
90 rows in set (0.00 sec)
一共90条记录，当作 XB5，接着在 XB5 的基础上执行 on 过滤语句，进行 on b.star = n.star 条件过滤，根据 on 中指定的条件，去掉那些不符合条件的数据，得到 XB6 表，内容如下：
mysql> select * from (select s.star,s.team,sw.worth,sw.time from star s join starworth sw on s.time = sw.time where s.time > 10) n join ballvalue b on b.star = n.star;
+--------+-------+-------+------+------+--------+-------+
| star | team | worth | time | goal | star | score |
+--------+-------+-------+------+------+--------+-------+
| Tyler | bull | 32 | 15 | 3 | Tyler | 9 |
| Gorden | hou | 48 | 18 | 4 | Gorden | 8 |
| cory | det | 56 | 22 | 6 | cory | 12 |
| Frank | det | 40 | 16 | 8 | Frank | 18 |
| Green | hou | 75 | 30 | 13 | Green | 25 |
| cooper | eagle | 60 | 28 | 15 | cooper | 30 |
| hunter | eagle | 80 | 32 | 20 | hunter | 45 |
| Kobe | Lakes | 99 | 40 | 28 | Kobe | 81 |
| James | Lakes | 95 | 35 | 30 | James | 75 |
+--------+-------+-------+------+------+--------+-------+
9 rows in set (0.00 sec)
接着在 XB6 的基础上执行左外连接：
mysql> select * from (select s.star,s.team,sw.worth,sw.time from star s join starworth sw on s.time = sw.time where s.time > 10) n left join ballvalue b on b.star = n.star;
+--------+-------+-------+------+------+--------+-------+
| star | team | worth | time | goal | star | score |
+--------+-------+-------+------+------+--------+-------+
| cooper | eagle | 60 | 28 | 15 | cooper | 30 |
| cory | det | 56 | 22 | 6 | cory | 12 |
| Frank | det | 40 | 16 | 8 | Frank | 18 |
| Gorden | hou | 48 | 18 | 4 | Gorden | 8 |
| Green | hou | 75 | 30 | 13 | Green | 25 |
| hunter | eagle | 80 | 32 | 20 | hunter | 45 |
| James | Lakes | 95 | 35 | 30 | James | 75 |
| Kobe | Lakes | 99 | 40 | 28 | Kobe | 81 |
| Tyler | bull | 32 | 15 | 3 | Tyler | 9 |
+--------+-------+-------+------+------+--------+-------+
9 rows in set (0.00 sec)
同样的顺序也会按照主表来执行，因为数据没变，所以这里依旧是 XB6 因为嵌套语句外面没有 where 语句，所以跳过
(5) 执行GROUP BY分组
group by 子句主要是对使用 where 子句得到的虚拟表进行分组操作。我们执行测试语句 group by n.team，得到以下内容，记作 XB7 (默认只显示组内第一条)：
mysql> select n.team,sum(n.worth),sum(n.time),sum(b.score) from(select s.star,s.team,sw.worth,sw.time from star s join starworth sw on s.time = sw.time where s.time > 10) n join ballvalue b on b.star = n.star group by n.team;
+-------+--------------+-------------+--------------+
| team | sum(n.worth) | sum(n.time) | sum(b.score) |
+-------+--------------+-------------+--------------+
| bull | 32 | 15 | 9 |
| hou | 123 | 48 | 33 |
| det | 96 | 38 | 30 |
| eagle | 140 | 60 | 75 |
| Lakes | 194 | 75 | 156 |
+-------+--------------+-------------+--------------+
5 rows in set (0.00 sec)
因为分组函数是跟着 group by 关键字执行，所以上表也列出了需要的分组函数
(6) 执行HAVING过滤
having 子句主要和 group by 子句配合使用，对分组得到的 XB7 虚拟表进行条件过滤。当我执行测试语句中的 having sum(b.score) > 60 时，将得到以下内容，得到 XB8 ：
mysql> select n.team,sum(n.worth),sum(n.time),sum(b.score) from(select s.star,s.team,sw.worth,sw.time from star s join starworth sw on s.time = sw.time where s.time > 10) n join ballvalue b on b.star = n.star group by n.team having sum(b.score) > 60;
+-------+--------------+-------------+--------------+
| team | sum(n.worth) | sum(n.time) | sum(b.score) |
+-------+--------------+-------------+--------------+
| eagle | 140 | 60 | 75 |
| Lakes | 194 | 75 | 156 |
+-------+--------------+-------------+--------------+
2 rows in set (0.00 sec)
(7) 执行DISTINCT子句
如果在查询中指定了 distinct 子句，则会创建一张内存临时表（如果内存放不下，就需要存放在硬盘）。这张临时表的表结构和上一步产生的虚拟表 XB8 是一样的，不同的是对进行 distinct 操作的列增加了一个唯一索引，以此来除重复数据。
我没用到 distinct ，所以没有虚拟表产生
(8) 执行ORDER BY子句
对虚拟表中的内容按照指定的列进行排序，然后返回一个新的虚拟表 XB9，我们执行测试SQL语句中的 order by sum(b.score) desc ，就会得到以下内容：
mysql> select n.team,sum(n.worth),sum(n.time),sum(b.score) from(select s.star,s.team,sw.worth,sw.time from star s join starworth sw on s.time = sw.time where s.time > 10) n join ballvalue b on b.star = n.star group by n.team having sum(b.score) > 60 order by sum(b.score) desc;
+-------+--------------+-------------+--------------+
| team | sum(n.worth) | sum(n.time) | sum(b.score) |
+-------+--------------+-------------+--------------+
| Lakes | 194 | 75 | 156 |
| eagle | 140 | 60 | 75 |
+-------+--------------+-------------+--------------+
2 rows in set (0.00 sec)
(9) 执行LIMIT子句
limit 子句从上一步得到的 XB9 虚拟表中选出从指定位置开始的指定行数据。对于没有应用 order by 的limit 子句，得到的结果同样是无序的，所以，很多时候，我们都会看到 limit 子句会和 order by 子句一起使用。
MySQL数据库的 limit 支持如下形式的选择：
LIMIT n, m
表示从第 n 条记录开始选择 m 条记录。而很多开发人员喜欢使用该语句来解决分页问题。对于小数据，使用 limit 子句没有任何问题，当数据量非常大的时候，使用 LIMIT n, m 是非常低效的。因为 limit 的机制是每次都是从头开始扫描，如果需要从第 60万行开始，读取 3 条数据，就需要先扫描定位到 60万行，然后再进行读取，而扫描的过程是一个非常低效的过程。所以，对于大数据处理时，是非常有必要在应用层建立一定的缓存机制（现在的大数据处理，大都使用缓存）
我写的sql语句没有 limit 关键字，所以不产生虚拟表，所以最后的结果为：
查询要求：查询出场时间等级总和大于90的球队中总得分超过60的球队
mysql> select n.team,sum(n.worth),sum(n.time),sum(b.score) from(select s.star,s.team,sw.worth,sw.time from star s join starworth sw on s.time = sw.time where s.time > 10) n join ballvalue b on b.star = n.star group by n.team having sum(b.score) > 60 order by sum(b.score) desc;
+-------+--------------+-------------+--------------+
| team | sum(n.worth) | sum(n.time) | sum(b.score) |
+-------+--------------+-------------+--------------+
| Lakes | 194 | 75 | 156 |
| eagle | 140 | 60 | 75 |
+-------+--------------+-------------+--------------+
2 rows in set (0.00 sec)
参考1：https://pig66.blog.csdn.net/. 完结，行动是焦虑和迷茫最好的特效药，共勉~
脚本宝典总结
以上是脚本宝典为你收集整理的从建表到关于sql和MySQL的查询语句执行顺序最新详解全部内容，希望文章能够帮你解决从建表到关于sql和MySQL的查询语句执行顺序最新详解所遇到的问题。
本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。
猜你在找的心得技巧相关文章
- clion结合vcpkg以及GTest的使用 2022-07-07
- EGF 2022-06-06
- ExtJS 布局-Column布局（Column layout） 2022-06-05
- 颜色之ARGB与RGB、RGBA的区别与介绍 2022-04-15
- rgba中的a是什么意思 CSS之RGBA颜色指南 2022-04-15
- rootfs -根文件系统制作 2022-07-07
- 网页简单布局之结构与表现原则分享 2022-04-15
- 小项目中怎么防止Vue的闪现画面效果 2022-04-15
- 隐藏 Web 中的元素方法及优缺点教程详解 2022-04-15
- 告别硬编码让你的前端表格自动计算的实例代码 2022-04-15
全站导航更多
html5HTML/XhtmlCSSXML/XSLTDreamweaver教程Frontpage教程心得技巧JavaScriptASP.NETPHP编程正则表达式AJAX相关ASP编程JSP编程编程10000问CSS/HTMLFlexvbsDOS/BAThtahtcpythonperl游戏相关VBA远程脚本ColdFusionMsSqlMysqlmariadboracleDB2mssql2008mssql2005SQLitePostgreSQLMongoDB星外虚拟主机华众虚拟主机Linuxwin服务器FTP服务器DNS服务器Tomcatnginxzabbix云和虚拟化bios系统安装系统系统进程Windows系列LINUXRedHat/CentosUbuntu/DebianFedoraSolaris红旗Linux建站经验微信营销网站优化网站策划网络赚钱网络创业站长故事alexa域名photoshop教程摄影教程Fireworks教程CorelDraw教程Illustrator教程Painter教程Freehand教程IndesignSketch笔记本主板内存CPU存储显卡显示器光存储鼠标键盘平板电脑安全教程杀毒防毒安全设置病毒查杀脚本攻防入侵防御工具使用业界动态Exploit漏洞分析
最新心得技巧教程