关键字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