MySQL笔记--多表查询

1--多表关系

多表关系基本上分为三种：

        一对多（多对一）；

        多对多；

        一对一；

1-1--多对一

在多的一方建立外键，指向一的一方的主键；

1-2--多对多

建立第三张中间表，中间表至少包含两个外键，分别关联两方主键；

1-3--一对一

在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）；

2--多表查询分类

多表查询存在笛卡尔乘积的问题，可借助where语句实现多表查询；

# 借助 where 语句查询 emp 表和 dept 表
select * from emp, dept where emp.dept_id = dept.id;

多表查询分类：

        连接查询：内连接、外连接、自连接；

        子查询

2-1--内连接

内连接查询语法：隐式内连接和显式内连接；

内连接查询的是两张表交集的部分；

# 隐式内连接语法
SELECT 字段列表 FROM 表1, 表2 WHERE 条件...;

# 显式内连接语法
SELECT 字段列表 FROM 表1 [INNER] JOIN ON 连接条件...;

# 内连接案例
1. 查询每一个员工的姓名，以及关联的部门的名称，表结构为 emp 和 dept，连接条件是 emp.dept_id = dept.id;

# 隐式实例
select emp.name, dept.name from emp, dept where emp.dept_id = dept.id;
# 使用表别名
select e.name, d.name from emp e, dept d where e.dept_id = d.id;

# 显式实例
select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;
# 省略 inner
select e.name, d.name from emp e join dept d on e.dept_id = d.id;

2-2--外连接

外连接可分为：左外连接和右外连接；

左外连接查询左表的数据，右外连接查询右表的数据；

左外连接更常用；

# 外连接查询语法
# 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 ...;
# 相当于查询表1（左表）的所有数据（包含表1和表2交集部分的数据）

# 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件 ...;
# 相当于查询表2（右表）的所有数据（包含表1和表2交集部分的数据）

# 左外连接案例
1. 查询emp表的所有数据，以及对应部分信息
2. 表结构为：emp表和dept表
3. 连接条件为：emp.dept_id = dept.id

select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
select e.*, d.name from emp e left join dept d on e.dept_id = d.id;

# 右外连接案例
1. 查询dept表的所有数据，以及对应员工信息（右外连接）
select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;
2. 上述需求，改写成左外连接
select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;

2-3--自连接

自连接：查询一个表格的关联数据；

自连接查询可以是：内连接查询和外连接查询；

# 自连接案例1
1. 查询员工及其所属领导的名字
2. 表结构为emp表，字段id表示员工的id，字段managerid表示所属领导的id

# 内连接实现，将emp表分别起别名为a表和b表
select a.name, b.name from emp a, emp b where a.managerid = b.id;

# 自连接案例2
1. 查询所有员工及其所属领导的名字，若员工没有领导，也需查询
2. 表结构为emp表

# 因为所有员工都需要查询，需使用外连接实现
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id;

2-4--联合查询

联合查询：将多次查询结果合并，形成一个新的查询结果集。

对于联合查询，多张查询表的列数必须一致，字段类型也要保持一致。

# 联合查询语法
SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...;

# 联合查询案例
1. 将薪资低于5000的员工，以及年龄大于50岁的员工全部查询

select * from emp where salary < 5000
union all
select * from emp where age > 50;

2. 对上述结果去重
select * from emp where salary < 5000
union
select * from emp where age > 50;