七、多表联查

1.联合查询

并集
## 把多个select的语句的结果集联合起来
## union: 特征: 去重
## union all 特征: 不去重

/* 
  select 语句
    union [all]
  select 语句
    union [all]
  select 语句
  
  #要求: 结果集的列的个数一样, 列含义一样
*/
​

SELECT * FROM emp;
​
CREATE TABLE emp_job_CLERK AS 
    SELECT empno,ename,sal FROM emp WHERE job ='CLERK';
SELECT * FROM emp_job_CLERK;
​
CREATE TABLE emp_deptno_30 AS 
    SELECT empno,ename,sal FROM emp WHERE deptno = 30;
​
#查询岗位为CLERK的员工编号,姓名,薪水
#查询部门编号为30部门的员工编号,姓名,薪水
​
SELECT * FROM emp_job_CLERK;
SELECT * FROM emp_deptno_30;
#把多张不相干的表联合起来
#7369 7876 7900 7934
​
SELECT * FROM emp_job_CLERK
UNION
SELECT * FROM emp_deptno_30;
​
SELECT * FROM emp_job_CLERK
UNION ALL
SELECT * FROM emp_deptno_30;

2.表连接查询

1. 内连接
 2. 外连接
    a. 左外连接
    b. 右外连接
 3. 自然连接
 4. 自连接

## 自然连接: 把A表的数据,与B表的数据, 两两相连
## 没有连接条件
## 笛卡尔积  记录数 = A表记录数 * B记录数
## 56  emp:14  dept:4 
# 标准写法
SELECT * FROM emp JOIN dept;
# 非标准写法
SELECT * FROM emp , dept;
​

内连接

## 加连接条件:  内连接或者外连接
##内连接
## 特征: A表内连接B表, A表中的记录与B表有对应的,才会查询出来
##       没有对应, A表,B表的数据都不会出现
/*
标准语法: 
  select *|列名 from 表1 [inner] join 表2
  on 连接条件
​
n张表连接,需要最小的连接条件: (n-1)个
*/
#查询员工信息(emp)以及它的部门信息(dept)
#表连接查询,如果多张表有相同名字列, 使用 表名(表别名).列名区分

SELECT * FROM emp JOIN dept 
ON emp.deptno = dept.deptno;
​
SELECT * FROM emp e JOIN dept d 
ON e.deptno = d.deptno;
​
​
SELECT empno,ename,dname,e.deptno FROM emp e JOIN dept d 
ON e.deptno = d.deptno;
​
SELECT e.*,dname FROM emp e JOIN dept d 
ON e.deptno = d.deptno;

​
​

外连接

## 多表查询
#内连接: 符合连接条件出来
## 外连接: 符合连接条件的出来, + 不符合条件(某张表)
##  左外连接    A left [outer] join  B   join左边的表的数据全部显示
##  右外连接    A right [outer] join  B  join由边的表的数据全部显示
##内连接
##标准写法
SELECT * FROM emp e JOIN dept d
ON e.`deptno` = d.`deptno`;
##非标准写法
SELECT * FROM emp e , dept d
WHERE e.`deptno` = d.`deptno`;
​
##左外连接  对应右边的表使用null连接
SELECT * FROM emp e LEFT JOIN dept d
ON e.`deptno` = d.`deptno`;
​
SELECT * FROM dept d RIGHT JOIN emp e 
ON e.`deptno` = d.`deptno`;
​
##右外连接
SELECT * FROM emp e RIGHT JOIN dept d
ON e.`deptno` = d.`deptno`;

自连接

##自连接: 属于内连接或者外连接一种
## 自己连接自己
SELECT * FROM emp;
##查询员工的姓名以及上级领导的姓名
## emp 表示员工表,  也可以表示领导表
## emp e1 表示员工表     emp e2 领导表

​
SELECT e1.ename 员工姓名, e2.ename 上级领导姓名 
FROM emp e1 LEFT JOIN emp e2
ON e1.mgr = e2.empno;

​
##表连接: 不是只是两张表的连接,可以是n张表的连接
## 根据员工编号查询员工的姓名, 薪水,岗位,部门名称,薪水等级
## 1.找表 emp  dept grade
## 如果是多张表: 表连接查询, 连接条件 2
## 2.找列 姓名, 薪水,岗位,部门名称,薪水等级
## 3.找条件 员工编号=?
## emp 的sal     grade: lower high

SELECT e.ename, e.job, e.sal, d.dname ,g.grade
FROM  emp e JOIN dept d
ON  e.`deptno` = d.`deptno`
JOIN grede g
ON e.sal BETWEEN g.lower AND g.hige
WHERE e.empno = 7788;

子查询

## select 中嵌套其他的select
##子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。
/*
子查询出现的位置：
- where后，作为条件的一部分；
- from后，作为被查询的一条表；
- SELECT之后 ,作为被查询的一列;
*/
##根据子查询的结果:
## 1. 单行单列   作为条件
## 2. 多行多列   作为表
## 3. 单行多列   作为表, 作为条件
## 4. 多行单列   作为条件
​
##配合子查询的关键字, 作为条件
##  1. all  所有
##  2. any  任何一个
​
# 工资高于ALLEN的员工
​
SELECT * FROM emp;
#1. 通过ALLEN查询他的工资
SELECT sal FROM emp WHERE ename = 'ALLEN';
​
#2. 把第一步的结果作为第二步的条件
SELECT * FROM emp WHERE sal > 
    (SELECT sal FROM emp WHERE ename = 'ALLEN');
​
##查询 "SALES" 和 "ACCOUNTING" 的所有员工信息
#表连接查询
SELECT e.* FROM emp e JOIN dept d
ON e.`deptno` = d.`deptno`
WHERE dname IN('SALES','ACCOUNTING');
​
#子查询
#通过部门名称查询部门编号
SELECT deptno FROM dept WHERE dname IN('SALES','ACCOUNTING');
​
# 根据部门编号查询该部门的下所有的员工
# 比较运算符: = > < <>... 只能与单个值比较
# 如果与多个值比较:  = -->  in    <> != --> not in
#                    >    < 结合 any all
SELECT * FROM emp WHERE deptno IN(
      SELECT deptno FROM dept WHERE dname IN('SALES','ACCOUNTING')
    );
​
#查询员工信息,他的岗位和薪水与MARTIN一样
​
#查询MARTIN的岗位
SELECT job FROM emp WHERE ename = 'MARTIN';
SELECT sal FROM emp WHERE ename = 'MARTIN';
​
SELECT job, sal FROM emp WHERE ename = 'MARTIN';
​
#可以使用 多列in  
# 要求:
#1. 多列作为整体,必须使用括号引起
#2. 子查询的结果的列个数与in前面的列个数一样,顺序一样 
SELECT * FROM emp 
WHERE ( job,sal) IN (SELECT job, sal FROM emp WHERE ename = 'MARTIN')
AND ename <> 'MARTIN';
​
#工资高于30部门所有人的员工信息
# 查询30部门最高工资
SELECT MAX(sal) FROM emp WHERE deptno = 30;
SELECT * FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);
​
#查询30部门所有的薪水
SELECT sal FROM emp WHERE deptno = 30;
#  > all()  大于所有  大于最大值   <all() 小于所有 小于最小值
SELECT * FROM emp WHERE sal > ALL(
  SELECT sal FROM emp WHERE deptno = 30
);
#工资比30部门任意一个低的员工信息
# 比30部门任意一个低  < 最大值
# 小于任意一个   <最大值   大于任意一个  >最小值
SELECT * FROM emp WHERE sal 
 < (SELECT MAX(sal) FROM emp WHERE deptno = 30);
​
​
SELECT * FROM emp WHERE sal 
 < ANY(SELECT sal FROM emp WHERE deptno = 30);
​
​
​

## 子查询
## 多行多列 作为表
#查询员工编号为7369的员工名称、员工工资、部门名称、部门地址
# 表连接方式
​
SELECT e.`ename`,e.sal,d.`dname`,d.`loc`  FROM  emp e
JOIN  dept d
ON e.`deptno` = d.`deptno`
WHERE e.`empno` = 7369;
​
##把部门信息查询
SELECT deptno dno, dname,loc FROM dept;
​
#子查询作为表使用 一定要给子查询 设置别名
SELECT e.`ename`,e.sal,d.`dname`,d.`loc`  FROM
emp e JOIN 
(SELECT deptno dno, dname,loc FROM dept)d
ON e.`deptno` = d.dno
WHERE e.empno = 7369;
​
#查询员工编号为7369的员工名称、员工工资、
#部门名称、部门地址, 以及部门人数(emp)
SELECT e.`ename`,e.sal,d.`dname`,d.`loc`,t.num FROM 
emp e LEFT JOIN dept d
ON e.`deptno` = d.`deptno`
LEFT JOIN(
  SELECT deptno , COUNT(1) num FROM emp GROUP BY deptno
 ) t
 ON e.`deptno` = t.deptno
​
​