## 子查询
子查询可以在的位置:
select
字段,(子查询)
from
(子查询内容可以看作一个临时表)
join
表
on
连接内容
where
(子查询)
1、把子查询内容看成一个字段
查询员工表名字对应的部门名称
mysql> select e.ename,(select d.dname from dept d where d.deptno=e.deptno) dname from emp e;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
2、在from后面添加子查询
找出每个部门平均的薪资等级
mysql> select e.*,s.grade from (select e.deptno,avg(e.sal) esal from emp e group by deptno) e join salgrade s on esal between s.losal and s.hisal;
+--------+--------------------+-------+
| deptno | esal | grade |
+--------+--------------------+-------+
| 10 | 2916.6666666666665 | 4 |
| 20 | 2175 | 4 |
| 30 | 1566.6666666666667 | 3 |
+--------+--------------------+-------+
3 rows in set (0.00 sec)
mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)
3.在where后面添加子查询
找出高于平均薪资的员工信息
mysql> select ename,sal from emp where sal>(select avg(sal) from emp);
+-------+------+
| ename | sal |
+-------+------+
| JONES | 2975 |
| BLAKE | 2850 |
| CLARK | 2450 |
| SCOTT | 3000 |
| KING | 5000 |
| FORD | 3000 |
+-------+------+
6 rows in set (0.00 sec)
## union
将查询结果相加(列数要一至)
一、找出工作岗位是salesman和manager的员工
第一种:mysql> select ename,job from emp where job='salesman' or job='manager' order by job;
+--------+----------+
| ename | job |
+--------+----------+
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)
第二种:mysql> select ename,job from emp where job in('salesman','manager') order by job;
+--------+----------+
| ename | job |
+--------+----------+
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)
第三种:mysql> select ename,job from emp where job='salesman'
-> union
-> select ename,job from emp where job='manager';
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
+--------+----------+
7 rows in set (0.00 sec)
有的时候不能用or或in只能用union比如两张不相干的表
例子:mysql> select ename from emp
-> union
-> select grade from salgrade;
+--------+
| ename |
+--------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| TURNER |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+--------+
19 rows in set (0.00 sec)
## 表的复制
create table 表名 as select语句
## 将查询结果插入到一张表中
insert into dept1 select *from dept;