MySQL关键字union,limit,通用分页

关键字union,limit,通用分页

union合并查询结果集

• 案例：查询工作岗位是MANAGER和SALESMAN的员工

//写法一
select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';

+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| JONES  | MANAGER  |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)

//写法二
select ename,job from emp where job in( 'MANAGER','SALESMAN');

+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| JONES  | MANAGER  |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)

使用union

select ename,job from emp where job = 'MANAGER' 
union
select ename,job from emp where job = 'SALESMAN';

+--------+----------+
| ename  | job      |
+--------+----------+
| JONES  | MANAGER  |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)

union的效率要高一些，对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，成倍的翻。但是union可以减少匹配次数，在减少匹配次数的情况下，还可以完成两个结果集的拼接。
a 连接 b 连接 c
a 10 条记录
b 10条记录
c 10 条记录
匹配次数是：1000
a 连接 b 1010=100次
a 连接 c 1010=100次
使用union的话是：100+100=200次
union把乘法变成了加法

union使用注意事项
1.union在进行结果合并时，要求两个结果集的列数相同

select ename,job from emp where job = 'MANAGER' 
union
select enamefrom emp where job = 'SALESMAN';

ERROR 1222 (21000): The used SELECT statements have a different number of columns

2.MySQL可以，Oracle语法严格，不可以，要求结果集合并列时列和列的数据类型也要相同。

select ename,job from emp where job = 'MANAGER' 
union
select ename,sal from emp where job = 'SALESMAN';

+--------+---------+
| ename  | job     |
+--------+---------+
| JONES  | MANAGER |
| BLAKE  | MANAGER |
| CLARK  | MANAGER |
| ALLEN  | 1600    |
| WARD   | 1250    |
| MARTIN | 1250    |
| TURNER | 1500    |
+--------+---------+
7 rows in set (0.00 sec)

limit

• limit是将查询结果集的一部分取出来，通常用在分页查询中
  完整用法：limit startIndex , length
  startIndex是起始下标，默认从0开始， length是长度
  缺省用法：limit length ，取前length 个

分页的作用是为了提高用户的使用体验，一次显示全部结果，用户体验差。

limit如何使用？

• 案例：按照薪资降序，取出排名在前五的员工名字，要求显示员工名和工资

select
	ename,sal
from
	emp
order by
	sal desc
limit 5;

+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| FORD  | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.00 sec)

select
	ename,sal
from
	emp
order by
	sal desc
limit 0,5;

+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| FORD  | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.00 sec)

注意：limit在order by 之后执行！

• 案例：取出工资排名在[3-5]名的员工

select 
	ename,sal 
from 
	emp 
order by 
	sal desc 
limit 2,3;

+-------+---------+
| ename | sal     |
+-------+---------+
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
3 rows in set (0.00 sec)
2表示起始位置从下标2开始，就是第三条记录开始；3表示长度。

分页

每页显示3条记录
第1页：limit 0,3 [ 0 1 2 ]
第2页：limit 3,3 [ 3 4 5 ]
第3页：limit 6,3 [ 6 7 8 ]
第4页：limit 9,3 [ 9 10 11 ]
每页显示pageSize条记录，第pageNo页：limit (pageNo - 1)*pageSize,pageSize

关于DQL语句的总结

一条完整的select语句

select
	...
from
	...
where
	...
group by
	...
having
	...
order by
	...
limit
	...

执行顺序：
1.from
2.where
3.group by
4.having
5.select
6.order by
7.limit