MySql常见复合查询(重点)

红太狼 2024-03-31 08:04 62阅读 0赞

## 复合查询（重点） ##

### 多表查询 ###

> 实际开发中往往**数据来自不同的表**，所以需要**多表查询**。本节我们用一个简单的公司管理系统，有三张表 EMP,DEPT,SALGRADE来演示如何进行多表查询。

*  **显示雇员名、雇员工资以及所在部门的名字因为上面的数据来自EMP和DEPT表，因此要联合查询**

![在这里插入图片描述][376842a362374abbb77290f26b35bf42.png]

> from后面跟两张表，多表查询，实际上是产生两张表的笛卡尔积(**排列组合**),我们把所有相关的数据，聚合到了一张表,接下来的问题，就变成了一个单表查询!! !;
> 
> ![在这里插入图片描述][ab0fe3f821444217b688304e817c4b2a.png]  
> 其实我们**只要**emp .deptno = dept.deptno字段的记录;(**排列组合中的有效记录**)

select EMP.ename, EMP.sal, DEPT.dname from EMP, DEPT where EMP.deptno = DEPT.deptno;

*  **显示部门号为10的部门名，员工名和工资**

select dname,ename,sal from EMP, DEPT where EMP.deptno = DEPT.deptno and EMP=10;

*  **显示 各个 员工的 姓名，工资，及工资级别**

> 工资级别表格式 : SALGRADE :
> 
> 字段1 grade级别
> 
> 字段2,3 满足这个级别的区间losal , hisal;

select ename, sal, grade from EMP, SALGRADE where EMP.sal between losal and hisal;

### 自连接 ###

> 自连接是指在**同一张表连接查询** （同一张表 做笛卡尔积）

*  **显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号–empno**

-- 使用的子查询(select 嵌套,下面介绍)：
    select empno,ename from EMP where empno = (select mgr from EMP where ename='FORD');
    
    -- 使用多表查询（自查询）:
    select t2.empno,t2.ename from EMP t1,EMP t2 where t1.mgr=t2.empno and t1.ename='FORD';

### 子查询 ###

> 子查询是指嵌入在其他sql语句中的select语句，也叫**嵌套查询**

#### 单行子查询 ####

> 返回**一行记录**的子查询()

*  **显示SMITH同一部门的员工**

select * from EMP where deptno = (select deptno from EMP where ename='SMITH');

#### 多行子查询 ####

> 返回**多行记录**的子查询()

*  **in关键字； 查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10部门自己的**

select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=10) and deptno<>10; -- 嵌套进的子select，返回多行

*  **all关键字；显示工资比 部门30的 所有 员工的工资高的 员工的姓名、工资和部门号**

select ename,sal,deptno from EMP where sal > all(select sal from EMP where deptno=30);

*  **any关键字；显示工资比 部门30的 任意 员工的工资高的员工的姓名、工资和部门号（包含30自己部门的员工）**

select ename,sal,deptno from EMP where sal > any(select sal from EMP where deptno=30);

#### 多列子查询 ####

> 单行子查询 和 多行子查询，返回的都是1列(n行)， 多列子查询返回多列多行;

**案例：查询和 SMITH的部门和岗位完全相同 的所有雇员，不含SMITH本人**

select * from EMP where (deptno,job) = all(select depthno,job from EMP where ename = 'SMITH') and ename != 'SMITH'; -- 很明显，多列子查询的话，因为多列(多字段)，所以 = 的左边需要上(字段1，字段2) = (select *......);来匹配

#### 在from子句中使用子查询 ####

> 子查询语句出现在**from子句中**。这里要用到**数据查询的技巧**，**把一个子查询当做一个临时表 来进行 笛卡尔积 多表查询 使用**。
> 
> **我们可以理解为MySql每次查询的结果都是一个临时表 “表结构”！**，那么这个临时表也能当做一张表用于多表查询;

*  **显示每个高于 自己 部门平均工资 的员工的姓名、部门、工资、平均工资**

-- 下面这个查询出来的 每个部门平均工资 的临时表 可以当成一张表进行 笛卡尔积 多表查询;
    select depthno agv(sal) as agvs from EMP group by depthno; 
    
    
    select ename,depthno,sal,agvs from EMP , (select depthno agv(sal) as agvs from EMP group by depthno)as tmp where tmp.depthno = EMP.depthno and EMP.sal>tmp.agvs;

*  **查找 每个部门工资最高 的人的姓名、工资、部门、最高工资**

-- 下面这个查询出来的 每个部门max工资 的临时表 可以当成一张表进行 笛卡尔积 多表查询;
    select depthno max(sal) as msal from EMP group by depthno;
    
    
    select ename,sal,depthno,msal from EMP ,(select depthno max(sal) as msal from EMP group by depthno) as tmp where tmp.depthno = EMP.depthno and EMP.sal=tmp.msal;

*  **显示 每个部门的信息（部门名，编号，地址）和 人员数量**

-- 方法1：使用多表
    select DEPT.dname,DEPT.depthno,DEPT.loc,count(*) from DEPT,EMP
    where EMP.depthno=DEPT.depthno;
    group by DEPT.dname,DEPT.depthno,DEPT.loc; -- 细节:一般group by之后的字段，在select中除了聚合函数以外，需要一一匹配，否则出现歧义，报错
    
    -- 方法1：使用子查询
    -- 将下面的 统计每个部门的人数 表看成临时表;
    select count(*) from EMP group by depthno;
    
    select dname,DEPT.depthno,loc , cnt from DEPT,(select count(*) as cnt from EMP group by depthno)as tmp
    where tmp.depthno=DEPT.depthno;

> 总结一下，复合查询还是比一般的查询要复杂，但是也是最重要，最常用的，而且套路很深！

### 合并查询 ###

> 在实际应用中，为了**合并多个select的执行结果**，可以使用集合操作符 **union**，**union all**

#### union ####

> 该操作符用于取得两个结果集的并集。当使用该操作符时，**会自动去掉结果集中的重复行**。取交集

*  **案例：将工资大于2500 或 职位是MANAGER的人找出来**

select ename, sal, job from EMP where sal>2500 union
        select ename, sal, job from EMP where jos='MANAGER';
    
        -- 结果去掉了重复记录; 
        -- 等价于 select ename, sal, job from EMP where sal>2500 OR jos='MANAGER';
        +-------+---------+-----------+
        | ename | sal | job |
        +-------+---------+-----------+
        | JONES | 2975.00 | MANAGER |
        | BLAKE | 2850.00 | MANAGER |
        | SCOTT | 3000.00 | ANALYST |
        | KING | 5000.00 | PRESIDENT |
        | FORD | 3000.00 | ANALYST |
        | CLARK | 2450.00 | MANAGER |
        +-------+---------+-----------+

#### union all ####

> 该操作符用于取得两个结果集的并集。当使用该操作符时，**不会去掉结果集中的重复行**。取并集

*  **案例：将工资大于2500 或 职位是MANAGER的人找出来**

## 表的内连和外连（重点） ##

### 内连接 ###

> **内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选（筛选出有意义的记录）**，下面要介绍的**join on语法**就是简化了我们的使用;
> 
> 我们前面学习的查询都是内连接，也是在开发过程中使用的最多的连接查询。

语法：

select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件；

**案例：显示SMITH的名字和部门名称**

-- 多表 + where写法
    select ename,dname from EMP,DEPT where EMP.depthno=DEPT.depthno and ename='SMITH';
    
    -- 用标准的内连接写法
    select ename,dname from EMP inner join DEPT on EMP.depthno=DEPT.depthno and ename='SMITH';

### 外连接 ###

#### 左外连接 ####

> 如果**联合查询，左侧的表完全显示**我们就说是左外连接。

语法:

select 字段名 from 表名1 left join 表名2 on 连接条件

原表结构:

![在这里插入图片描述][a10580136150488cb248081299d00bb9.png]

*  **查询所有学生的成绩，!!!如果这个学生没有成绩，也 要 将 学生的个人信息显示出来!!!**

-- 当左边表和右边表没有匹配时，也会显示左边表的数据
    
    select * from stu left join exam on stu.id=exam.id;

（左外连接）左侧的信息保留全

![在这里插入图片描述][71746988e2d846f38701cc708c44406c.png]

#### 右外连接 ####

> 如果**联合查询，右侧的表完全显示**我们就说是左外连接。

语法:

select 字段名 from 表名1 right join 表名2 on 连接条件

原表结构:  
![在这里插入图片描述][703e335a196b4a6a96dc1040b06c3a77.png]

*  **对stu表和exam表联合查询，!!!把所有的成绩都显示出来，即使这个成绩没有学生与它对应，也要显示出来!!!**

-- 当右边表和左边表没有匹配时，也会显示左边表的数据
    select * from stu right join exam on stu.id=exam.id;

![在这里插入图片描述][e0c1147c39344101924a86192b7ab643.png]

[376842a362374abbb77290f26b35bf42.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/f924b8e5f8114a0299f30f8248f87de7.png
[ab0fe3f821444217b688304e817c4b2a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/d4e9d5f83e5942ab88828e8d6f882f0f.png
[a10580136150488cb248081299d00bb9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/a94cdc680be84a2fbf39d76e2f4d589b.png
[71746988e2d846f38701cc708c44406c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/7e70adfd0e75494792e96dbdbc855b2a.png
[703e335a196b4a6a96dc1040b06c3a77.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/77b2579aa4694dd89ea6342e7bb393c7.png
[e0c1147c39344101924a86192b7ab643.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/22099c91efa9457f84dc17c6efd7ba79.png