七、多表联查

超、凢脫俗 2023-10-04 19:23 21阅读 0赞

### 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
>     
>