MySQL多表查询

多表关系

1. 一对多（多对一）：

关系：一个部门对应多个员工，一个员工对应一个部门。

实现：在多的一方建立外键，指向一的一方的主键

2. 多对多：

关系：一个学生可以选修多门课程，一门课程供多个学生选择

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

3. 一对一

关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表，其他字段放在另一张表

实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的

多表查询

1. 笛卡尔积：查询两个表（select * from 表1,表2）时出现两个表的数据相互乘积的现象，例如a表有A,B两条数据，b表有A1,B1两条数据，则结果为AA1,AB1,BA1,BB1

2. 消除笛卡尔积（只取自己要的那条数据）

select * from 表1,表2 where 表1.字段名=表2.字段名

多表查询分类

1. 内连接：查询两个表交集的那部分数据

① 隐式内连接：select 字段名 from 表1，表2 where 条件

eg：select emp.name,dept.name from emp,dept where emp.id=dept.id

eg：select e.name,d.name from emp e,dept d where e.id = d.id

② 显示内连接：select 字段名 from 表1 [inner] join 表2 on 连接条件

eg：select e.name,d.name from emp e inner join dept d on e.id = d.id

2. 左外连接：查询左表所有数据，以及两张表交集部分的数据：

select 字段 from 表1 left [outer] join 表2 on 条件

3. 右外连接：查询右表所有数据，以及两张表交集部分的数据：

select 字段 from 表1 right [outer] join 表2 on 条件

4、自连接：当前表与自身的连接查询，自连接必须使用表别名：

select 字段 from 表1 表1的别名 join 表1 表1的另一个别名 on 条件

给表起别名：select * from 表名 表别名

5. 联合查询：union，union all（把多次查询的结果合并起来形成一个新的查询结果集）：

select 字段 from 表1 union [all] select 字段 from 表2

注意：联合查询的多张表的列数必须保持一致，字段类型也需要保持一致，union all会将全部数据直接合并在一起，union会对合并之后的数据去重！！

6. 将两个表结合在一起形成新表：

select * from 表1 cross join 表2

7. 子查询（sql语句中嵌套select）：

列子查询（子查询结果为一列）

in	在指定的集合范围之内多选一
not in	不在指定的集合范围之内
any/some	子查询返回列表中，有任意一个满足即可
all	子查询返回列表的所有值都必须满足

例题1：查询销售部与市场部的所有员工信息

解：

步骤一：查询两个部门的id

select id from dept where name=’销售部’or name=’市场部’

步骤二：根据id查询员工

select * from emp where dept_id in（销售部id，市场部id）

步骤三：合并结果

select * from emp where dept_id in(select id from dept where name=’销售部’or name=’市场部’)

例题2：查询比财务部所有人工资都高的员工信息

解：

步骤一：查询财务部id

select id from dept where name=’财务部’

步骤二：查询财务部所有人的工资

select salary from emp where emp_id = 财务部id

步骤三：查询比财务部最高工资还高的员工信息

select * from emp where salary > all(财务部所有人的工资)

步骤四：合并结果

select * from emp where salary > all(select salary from emp where emp_id =（select id from dept where name=’财务部’）)

例题3：查询比研发部其中任意一人工资高的员工信息

解：

步骤一：查询研发部id

select id from dept where name=’研发部’

步骤二：查询研发部所有人的工资

select salary from emp where dept_id = 研发部id

步骤三：查询比研发部任意一人工资高的员工信息

select * from emp where salary > any(研发部所有人的工资)

步骤四：合并结果

select * from emp where salary > any(select salary from emp where dept_id = （select id from dept where name=’研发部’）)

行子查询（子查询结果为一行）

例题一：查询与小明的薪资及领导相同的员工信息

解：

步骤一：查询小明的薪资及领导

select salary managerid from emp where name = ‘小明’

步骤二：查询员工信息，条件是薪资与领导跟小明相同

select * from emp where (salary,manageid)=(薪资，领导id)

步骤三：合并结果

select * from emp where (salary,manageid)=( select salary managerid from emp where name = ‘小明’)

表子查询（子查询结果为多行多列）

例题一：查询与张三，李四职位和薪资都相同的员工信息

解：

步骤一：查询张三，李四的职位和薪资

select job salary from emp where name = ‘张三’ or name = ’ 李四’

步骤二：查询员工信息，条件是职位和薪资与张三，李四相同

select * from emp where (job,salary) in (张三，李四的职位和薪资表)

步骤三：合并结果

select * from emp where (job,salary) in (select job salary from emp where name = ‘张三’ or name = ’ 李四’)

例题二：查询入职日期是2006-01-01之后的员工信息及部门信息

解：

步骤一：查询日期是2006-01-01之后的员工信息

select * from emp where entrydate>’2006-01-01’

步骤二：查询这部分的员工的部门信息

select * from (查出来的员工信息表)

步骤三：合并结果

select * from (select * from emp where entrydate>’2006-01-01’)

扩展：如果员工信息表中的数据没有部门信息就需要用到左外连接

select e.* ,d.* from (select * from emp where entrydate>’2006-01-01’) e left join dept d on e.dept_id = d.id