一、外键：

就是把一张表的主键拿到另一张表中作为一个普通的字段

通过外键 可以把两张表连接起来

比如，下面【部门】表里的主键 拿到【员工】表里做普通字段（外键）

员工 部门

1员工，XXX，1部门 1部门，武当派

2员工，XXX，2部门 2部门，峨眉派

3员工，XXX，1部门 3部门，明教

4员工，XXX，2部门

 设计主外键关系的方案

1、图形化操作：（数据库管理工具）

2、写sql语句

constraint 约束名 foreign key (外键列名) references 对方表(列表)

-- 创建部门表
create table dept(
	id int(10) not null primary key auto_increment,
	deptName varchar(50),
	deptLeader varchar(50)
)

-- 创建员工表（需要部门表的支持）
create table emp(
	id int(10) not null primary key auto_increment,
	name varchar(50),
	salary decimal(10,2),
	deptNo int(10),
	constraint foreign_key_deptNo foreign key (deptNo) references dept(id)
);

二、多表查询

1、子查询 

举例：

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

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

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

题目2: 查询各个部门的平均工资
-- 分解教学“各个部门的平均工资” 根据部门来区分，所以group by deptNo
select avg(salary) from emp group by deptNo

-- 但是⚠️希望展示部门 ：部门 平均工资

-- 把部门和员工放在一起进行查询
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 内连接 筛选出两张表完全关联起来的数据

-- 通常关联项 on 后面的就是 主 外 键
SELECT * from dept INNER JOIN emp on dept.id = emp.deptNo

-- inner JOIN 改成 下面的 子查询 结果一样
SELECT * from dept,emp where emp.deptNo = dept.id

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

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

-- left join  一定会把左边表的数据全部查询出来
SELECT * from emp left JOIN dept on dept.id = emp.deptNo

⚠️应用场景非常多，比如
查询所有员工的部门信息 ⚠️所有员工 以员工为基准 emp在左边
-- 员工名字 部门名字
select emp.name,dept.deptName from emp LEFT JOIN dept on dept.id = emp.deptNo

结果如下：

 

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

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

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

结果如下：