十六、子查询与关联查询

素颜马尾好姑娘i 2022-04-14 06:15 328阅读 0赞

#                                      子查询与关联查询 #

## 1、子查询 ##

### 1.1、单行子查询 ###

select * from emp
    where sal = (select sal from emp where empno = 7876);

### 1.2、多行子查询 ###

多行子查询
    
    any：
    select * from emp where sal > any(select avg(sal) from emp group by deptno);
    注：any(collection) 函数.  只要大于collection中任意一个即满足>条件。与in用法类似
    
    all：
    select * from emp where sal > all(select avg(sal) from emp group by deptno);
    注:all(collection)函数，比子查询返回结果中的所有值都大才满足>条件。
    
    in：
    select * from emp where job in (select job from emp where ename = 'Smith' or ename = 'Halen');

## 2、关联查询 ##

### 2.1、内连接 ###

*  只选取符合连接条件的记录

SELECT e.ename, d.dname
        FROM emp e, dept d
        WHERE e.deptno = d.deptno

### 2.2、左外连接 ###

*  以左边表为主（左边表的记录都保留），右边表有的，左边表没有的不显示

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70]

SELECT e.ename, d.dname
        FROM emp e LEFT OUTER JOIN dept d
        ON e.deptno = d.deptno;

### 2.3、右外连接 ###

*  以右边表为主（右边表的记录都保留），左边表有的，右边表没有的不显示

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70 1]

SELECT e.ename, d.dname
        FROM emp e RIGHT OUTER JOIN dept d
        ON e.deptno = d.deptno;

### 2.5、全外连接 ###

*  保留所有字段  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70 2]

SELECT e.ename, d.dname
        FROM emp e FULL OUTER JOIN dept d
        ON e.deptno = d.deptno;

### 2.6、自连接 ###

*  关联的字段都在一张表，代表的例子就是多级部门表

SELECT worker.empnow_empno, worker.enamew_ename, manager.empnom_empno, manager.enamem_ename
    FROM emp worker join emp manager
    ON worker.mgr = manager.empno;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70_pic_center][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70]: /images/20220414/1b725c5e844641f6931473d218653669.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70 1]: /images/20220414/7e0d05ed91bc4e37943a3a70e24ca405.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70 2]: /images/20220414/e2a3541a8f3f42dcbc43490527f7e47b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70_pic_center]: /images/20220414/27e900988e1449df818dc15f17d8801d.png