MySQL多表查询

一、多表关系

• 概述：项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：
  1）一对多（多对一）
  2）多对多
  3）一对一
• 一对多
  案例：部门 与 员工 的关系
  关系：一个部门对应多个员工，一个员工对应一个部门
  实现：在多的一方建立外键，指向一的一方的主键
• 多对多
  案例：学生 与 课程 的关系
  关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
  实现：建立中间表，中间表至少包含两个外键，分别关联两方主键
• 一对一
  案例：用户 与 用户详细 的关系
  关系：多用于单表拆分，将一张表的基础字段放在一张表中，其他详细字段放在另一张表                   中，以提高操作效率
  实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(unique)

二、多表查询 

select * from 表名，表名 where 字段名=字段名（这两个字段设置了外键关联）；

三、多表查询分类

内连接

查询A、B交集部分数据

隐式内连接 select 字段列表 from 表1 [别名]，表2 [别名] where 条件 ......;
       例如：查询每一个员工的姓名，及关联的部门的名称
       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 字段列表 from 表1 [inner] join 表2 on 连接条件 .....;
        例如：查询每一个员工的姓名，及关联的部门的名称
        select e.name,d.name from emp e [inner] join dept d on e.dept_id=d.id;

外连接

左外连接：查询表1(左表)所有数据，以及两张表交集部分数据
select 字段列表 from 表1 left [outer] join 表2 on 条件......;
例如：查询emp表的所有数据，和对应的部门信息
           select e.*,d.name from emp e left  join dept d on e.dept_id=d.id; 

右外连接：查询表2(右表)所有数据，以及两张表交集部分数据
select 字段列表 from 表1 right [outer] join 表2 on 条件......;
例如：查询dept表的所有数据，和对应的员工信息
           select d.*,e.* from emp e right join dept d on e.dept_id=d.id;

自连接

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

select 字段列表 from 表A 别名A join 表A 别名B on 条件......;
例如：查询员工 及其 所属领导的名字
           select a.name,b.name emp a,emp b where a.managerid=b.id;
           查询所有员工 emp 及其领导的名字 emp，如果员工没有领导，也需要查询出来
           select a.name,b.name from emp a left join emp b on a.managerid=b.id; 

联合查询

把多次查询的结果合并起来，形成一个新的查询结果集
对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致

select 字段列表 from 表A ......
union[all](不加all，会将结果去重)
select 字段列表 from 表B ......;
例如：将薪资低于5000的员工，和 年龄大于50岁的员工全部查询出来
           select * from emp where salary<5000
           union
           select * from emp where age>50;

子查询

1）概念

SQL语句中嵌套select语句，称为嵌套查询，又称子查询
子查询外部的语句可以是insert/update/delete/select的任何一个

select * from 表名1 where 条件=(select * from 表2 where 条件);

2）根据子查询的位置，分为：where之后、from之后、select之后 

3）根据子查询结果的不同，分为：

• 标量子查询（子查询结果为单个值）
  常用的操作符：=   <>  >  >=  <  <= 
  例如：查询“销售部”的所有员工的信息
             select * from emp where dept_id=(select id from dept where name='销售部')
• 列子查询（子查询结果为一列）
  常用的操作符：in  not in   any   some  all

  操作符	描述
  in	在指定的集合范围之内，多选一
  not in	不在指定的集合范围之内
  any	子查询返回列表中，有任意一个满足即可
  some	与any等同，使用some的地方都可以使用any
  all	子查询返回列表的所有值都必须满足

  
  例如：查询 销售部 和 市场部 的所有员工信息
             select * from emp where dept_id in(select id from dept where name='销售部' or                       name='市场部');
             查询比 财务部 所有人工资都高的员工信息
             select * from emp where salary > all(select salary from emp where dept_id=(select id             from dept where name='财务部'));
             查询比 研发部 中任意一人工资高的员工信息
             select * from emp where salary > any(select salary from emp where dept_id=(select               id form dept where name='研发部'));
• 行子查询（子查询结果为一行）
  常用的操作符：=  <>  in  not in 
  例如：查询与 张三 的薪资及直属领导相同的员工信息
             select * from emp where (salary,managerid)=(select salary,managerid from emp                     where name='张三');
• 表子查询（子查询结果为多行多列）
  常用的操作符：in
  例如：查询与“李四”，“王五”的职位和薪资相同的员工
             select * from emp where (job,salary) in (select job,salary from emp where name='李               四’ or name='王五');
             查询入职日期是 2003-5-20 之后的员工信息，及其部门信息
             select e.*,d.* from (select * from emp where entrydate > '2003-5-20') e left join dept d             on e.dept_id=d.id;