【MySQL】多表查询

电玩女神 2022-05-22 06:06 446阅读 0赞

首先，为了方便说明问题，创建两个表emp（雇员信息）和dept（雇员部门信息），其数据如下：  
![这里写图片描述][70]  
在之前的博客中，我们分享了[单表查询的方法][Link 1]，但是在实际应用中，我们要查的数据很可能不在同一个表中，而是来自于不同的表。多表查询如果不加任何条件，得到的结果称为笛卡尔积。  
例如，查找雇员名、雇员工资以及部门所在的名字。  
![这里写图片描述][70 1]  
可以发现，结果是这样的，从第一个表中选出第一条记录，和第二个表中的所有所有记录进行组合，然后再从第一个表中取出第二条记录，和第二张表的所有记录进行组合，这样的结果是没有实际意义的。我们需要的是`emp.deptno = dept.deptno`字段的记录。  
![这里写图片描述][70 2]

### 自连接 ###

**自连接是指在同一张表连接查询**

1.  显示员工FORD的上级领导的名字  
    ![这里写图片描述][70 3]

### 子查询 ###

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

##### 单行子查询：子查询的查询结果只有一行 #####

1.  显示和SMITH同一部门的员工  
    ![这里写图片描述][70 4]

##### 多行子查询(in ,all, any)：返回多条记录的子查询 #####

1.  查询和10好部门的工作相同的官员的名字、工作、工资、部门号，但是不包括10号自己的信息  
    ![这里写图片描述][70 5]
2.  显示工资比部门编号为30的所有员工的工资高的员工的姓名、工资和部门号  
    ![这里写图片描述][70 6]
3.  显示工资比部门编号为30的任意员工的工资高的员工的姓名、工资和部门号  
    ![这里写图片描述][70 7]

##### 多列子查询：查询返回多个列数据的子查询语句 #####

1.  查询和SMITH的部门和岗位完全相同的雇员，不含SMITH本人  
    ![这里写图片描述][70 8]

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

1.  显示高于自己部门的平均工资的员工信息  
    ![这里写图片描述][70 9]
2.  查找每个部门工资最高的人的ename, job, sal  
    ![这里写图片描述][70 10]  
    3.显示每个部门的信息（部门名、编号、地址）和人员数量  
    ![这里写图片描述][70 11]  
    这里有两种方法可以使用，但是经验证，当数据非常多的时候from子查询的效率是高于多表查询的。

##### 删除表中的重复记录 #####

已知一个表tt中有重复的数据

1.  创建一张空表tmp\_tt，空表的结构与表tt的结构相同；`create table tmp_tt like tt;`
2.  将tt表进行distinct，将数据导入空表中；`insert into tmp_tt select distinct * from tt;`
3.  删除原表tt`drop table tt;`
4.  将tmp\_tt改名为tt`alter table tmp_tt rename tt;`

##### 合并查询 #####

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

1.  union操作符用于取得两个结果的并集，并再自动去掉重复行  
    查找工资大于2500和职位Manger的人  
    ![这里写图片描述][70 12]  
    **2. union all** 与union类似，但是不会自动去重  
    例如：与or类似  
    ![这里写图片描述][70 13]

### 外键 ###

外键定义主表和从表的关系，外键约束主要是定义在从表上，主表必须是有主键或者唯一键。**当定义外键后，要求外键列数据必须在主表列存在或者为NULL。**  
　　  
　　例如：创建一个主表class，从表stu  
　　![这里写图片描述][70 14]  
　　![这里写图片描述][70 15]  
　　由上图，我们可以知道**主键不能为null，但是外键可以为null，同时不能存在外键有的数据而主表中不存在。**

[70]: /images/20220522/47eda191a77c4d50990caf5730ccc489.png
[Link 1]: https://blog.csdn.net/wei_cheng18/article/details/80379067
[70 1]: /images/20220522/447c7b8937634105aa02e9601dc4cdc9.png
[70 2]: /images/20220522/9a8333709ce54e14916792116689cc7a.png
[70 3]: /images/20220522/2007e94a71ec41a99d17b0b5a4307095.png
[70 4]: /images/20220522/ac4b871642b8463b857b4c35172132e8.png
[70 5]: /images/20220522/d700b6ddc81344f29e3843df37df09f4.png
[70 6]: /images/20220522/e1eb7c5752d645d0b44254ac8bb17c90.png
[70 7]: /images/20220522/089e7e87a1424093b8761a53b86291c2.png
[70 8]: /images/20220522/d79808b7c0ed45e8988bbe2755d2d6f1.png
[70 9]: /images/20220522/90de9f5d1c494d0cb7ee5e20cd36ccce.png
[70 10]: /images/20220522/f598d4ce5c774d169c0a5a7c92d3b11a.png
[70 11]: /images/20220522/596d965a61524232b8cf4e0581c240a0.png
[70 12]: /images/20220522/12d03a6e82334426942e9c49e739faf0.png
[70 13]: /images/20220522/db82d1c6009240ae91baa10b9fa1113f.png
[70 14]: /images/20220522/7c40ecec1644494e812fbb576f7e4bde.png
[70 15]: /images/20220522/7637501b4f72464ead8c2ff9efa06d39.png