【数据库】多表查询：子查询｜关联查询 inner join 、left join、right joi ...

一、外键：

就是把一张表的主键拿到另一张表中作为一个平凡的字段
通过外键 可以把两张表毗连起来
比如，下面【部门】表里的主键 拿到【员工】表里做平凡字段（外键）
员工 部门
1员工，XXX，1部门 1部门，武当派
2员工，XXX，2部门 2部门，峨眉派
3员工，XXX，1部门 3部门，明教
4员工，XXX，2部门

 计划主外键关系的方案
1、图形化操作：（数据库管理工具）

2、写sql语句
constraint 束缚名 foreign key (外键列名) references 对方表(列表)

1. -- 创建部门表
2. create table dept(
3.         id int(10) not null primary key auto_increment,
4.         deptName varchar(50),
5.         deptLeader varchar(50)
6. )
8. -- 创建员工表（需要部门表的支持）
9. create table emp(
10.         id int(10) not null primary key auto_increment,
11.         name varchar(50),
12.         salary decimal(10,2),
13.         deptNo int(10),
14.         constraint foreign_key_deptNo foreign key (deptNo) references dept(id)
15. );

复制代码

二、多表查询

1、子查询 

举例：

• 根据： 查询 where （表A 外键 in 表B）
  

1. 题目1:通过部门的名称查询出该部门所有的员工信息——查询明教的员工信息
2. -- 分解
3. -- （1）查询明教的部门信息
4. select id from dept where deptName = '明教'
5. -- （2）根据ID查询员工信息
6. select * from emp where deptNo = 3
7. -- 结果
8. select * from emp where deptNo = (select id from dept where deptName = '明教')
9. -- ⚠️用in更适用 如果后面的查询结果不是唯一的 用 = 不合适
10. select * from emp where deptNo in (select id from dept where deptName = '明教')

复制代码

• 同时查询两张表 表A.字段 表B.字段 where（ 表A.外键字段 = 表B.主键字段）
  

1. 题目2: 查询各个部门的平均工资
2. -- 分解教学“各个部门的平均工资” 根据部门来区分，所以group by deptNo
3. select avg(salary) from emp group by deptNo
5. -- 但是⚠️希望展示部门 ：部门 平均工资
7. -- 把部门和员工放在一起进行查询
8. select dept.deptName,avg(emp.salary) from emp,dept where emp.deptNo = dept.id group by dept.deptName

复制代码

2、关联查询

• 语法规则
  
  
  
  • join：关联
    
  • on: 通过 ... 做关联
    
  • A xxjoin B on A.字段 = B.字段
    
  • ⚠️通常关联项 on 背面的就是 主键 外键
    
  
  
• 用的比较多的还是inner join 、left join 左联
  
  
  
  • inner join 内毗连
    
  • left join 左联
    
  • right join 右联 结果和左联也差不多
    
  
  

2.1、inner join 内毗连 筛选出两张表完全关联起来的数据

1. -- 通常关联项 on 后面的就是 主 外 键
2. SELECT * from dept INNER JOIN emp on dept.id = emp.deptNo
4. -- inner JOIN 改成 下面的 子查询 结果一样
5. SELECT * from dept,emp where emp.deptNo = dept.id

复制代码

2.2、left join 左联 一定会把左边表的数据全部查询出来

把左边这张表所有的数据都摆出来 根据关联项 把右边表的数据插入进去

1. -- left join  一定会把左边表的数据全部查询出来
2. SELECT * from emp left JOIN dept on dept.id = emp.deptNo
4. ⚠️应用场景非常多，比如
5. 查询所有员工的部门信息 ⚠️所有员工 以员工为基准 emp在左边
6. -- 员工名字 部门名字
7. select emp.name,dept.deptName from emp LEFT JOIN dept on dept.id = emp.deptNo

复制代码

结果如下：

 
2.3、right join 右联 一定会把右边表的数据全部查询出来

把右边这张表所有的数据都摆出来 根据关联项 把左边表的数据插入进去

1. -- 右联接 一定把右边的数据查询出来
2. SELECT * from emp RIGHT JOIN dept on emp.deptNo = dept.id

复制代码

结果如下： 

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。