从建表到关于sql和MySQL的查询语句执行顺序最新详解

大大源码 • 2023年3月9日 am12:57 • 其他

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;
	-- ENGINE = INNODB为存储引擎

创建表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),(30,'James',75),(20,'hunter',45),
						   (3,'Tyler',9),(13,'Green',25),(8,'Frank',18),
						   (6,'cory',12),(15,'cooper',30),(5,'alex',15),(4,'Gorden',8);

insert into starworth value(10,27),(28,60),(22,56),(22,56),
						   (22,56),(16,40),(30,75),(32,80),
						   (35,95),(40,99),(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 表示的就是外部行的概念。

相对应的 inner join 就是内连接，而内外链接的区别就是有无主副之分，内连接没有，外连接则有

我创建的数据库的表里没有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 只能出现在所有字段最前面，表示后面所有字段联合起来去重，只是对结果集进行处理。

我没用到 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万行，然后再进行读取，而扫描的过程是一个非常低效的过程。所以，对于大数据处理时，是非常有必要在应用层建立一定的缓存机制（现在的大数据处理，大都使用缓存）

每页显示 pageSize 条记录，第 pageNo 页（固定）：

LIMIT  (pageNo - 1) * pageSize, pageSize

我写的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)