4、union (可以将查询结果集相加)
案例:找出工作岗位是SALESMAN和MANAGER的员工?
第一种:select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
第二种: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 |
+--------+----------+
第三种: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 |
+--------+----------+
两张不相干的表中的数据拼接在一起显示?
select ename from emp
union
select dname from dept;
+------------+
| ename |
+------------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| TURNER |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
| ACCOUNTING |
| RESEARCH |
| SALES |
| OPERATIONS |
+------------+
mysql> select ename,sal from emp
-> union
-> select dname from dept;
ERROR 1222 (21000): The used SELECT statements have a different number of columns
5、limit (重点中的重点,以后分页查询全靠它了。)
5.1、limit是mysql特有的,其他数据库中没有,不通用。(Oracle中有一个相同的机制,叫做rownum)
5.2、limit取结果集中的部分数据,这时它的作用。
5.3、语法机制:
limit startIndex, length
startIndex表示起始位置,从0开始,0表示第一条数据。
length表示取几个
案例:取出工资前5名的员工(思路:降序取前5个)
select ename,sal from emp order by sal desc;
取前5个:
select ename,sal from emp order by sal desc limit 0, 5;
select ename,sal from emp order by sal desc limit 5;
5.4、limit是sql语句最后执行的一个环节:
select 5
...
from 1
...
where 2
...
group by 3
...
having 4
...
order by 6
...
limit 7
...;
5.5、案例:找出工资排名在第4到第9名的员工?
select ename,sal from emp order by sal desc limit 3,6;
+--------+---------+
| ename | sal