DQL 多表查询

1、多表关系

一对多（多对一）

案例: 部门 与 员工的关系
关系: 一个部门对应多个员工，一个员工对应一个部门
实现: 在从表的一方建立外键，指向主表一方的主键

多对多

案例: 学生 与 课程的关系
关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择
实现: 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

一对一

案例: 用户 与 用户详情的关系
关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

2、多表查询简介、笛卡尔积

多表查询就是指从多张表中查询数据。原来查询单表数据，执行的SQL形式为：select * from 表名;那么我们要执行多表查询，就只需要使用逗号分隔多张表即可，如： select * from 表1 , 表2; 在查询时我们看到查询结果中包含了表1所有的记录 与 表2所有记录的所有组合情况，这种现象称之为笛卡尔积。
笛卡尔积: 笛卡尔乘积是指在数学中，两个集合A集合 和 B集合的所有组合情况。
而在多表查询中，我们是需要消除无效的笛卡尔积的，只保留两张表关联部分的数据。
在SQL语句中，如何来去除无效的笛卡尔积呢？ 我们可以给多表查询加上连接查询的条件即可。

3、内连接

相当于查询A、B交集部分数据。内连接查询的是两张表交集部分的数据。(也就是绿色部分的数据）

隐式内连接

SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;

显示内连接

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;

案例：

A. 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)
 表结构: emp , dept
 连接条件: emp.dept_id = dept.id

select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ;
-- 为每一张表起别名,简化SQL编写
select e.name,d.name from emp e , dept d where e.dept_id = d.id;

B. 查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现) — INNER JOIN …ON …
 表结构: emp , dept
 连接条件: emp.dept_id = dept.id

select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;
-- 为每一张表起别名,简化SQL编写
select e.name, d.name from emp e join dept d on e.dept_id = d.id;

4、外连接

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

左外连接

-- 左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;

右外连接

-- 右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;

案例：

A. 查询emp表的所有数据, 和对应的部门信息。
由于需求中提到，要查询emp的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。表结构: emp, dept、连接条件: 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;

B. 查询dept表的所有数据, 和对应的员工信息(右外连接)
由于需求中提到，要查询dept表的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。表结构: emp, dept、连接条件: emp.dept_id = dept.id。

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

注意事项：
左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了。而我们在日常开发使用时，更偏向于左外连接。

5、自连接

自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。我们先来学习一下自连接的查询语法：

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

案例：

A. 查询员工 及其 所属领导的名字

select a.name , b.name from emp a , emp b where a.managerid = b.id;

B. 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来

select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid =b.id;

注意事项:
在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段。

6、子查询

SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );
-- 子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。

分类：

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

• A. 标量子查询（子查询结果为单个值）
• B. 列子查询(子查询结果为一列)
• C. 行子查询(子查询结果为一行)
• D. 表子查询(子查询结果为多行多列)

根据子查询位置，分为：

• A. WHERE之后
• B. FROM之后
• C. SELECT之后

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。
常用的操作符：= <> > >= < <=

案例:

A. 查询 “销售部” 的所有员工信息
①. 查询 “销售部” 部门ID

select id from dept where name = '销售部';

②. 根据 “销售部” 部门ID, 查询员工信息

select * from emp where dept_id = (select id from dept where name = '销售部');

B. 查询在 “方东白” 入职之后的员工信息
①. 查询 方东白 的入职日期

select entrydate from emp where name = '方东白';

②. 查询指定入职日期之后入职的员工信息

select * from emp where entrydate > (select entrydate from emp where name = '方东白');

列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL

案例:
A. 查询 “销售部” 和 “市场部” 的所有员工信息
①. 查询 “销售部” 和 “市场部” 的部门ID

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

②. 根据部门ID, 查询员工信息

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

B. 查询比 财务部 所有人工资都高的员工信息
①. 查询所有 财务部 人员工资

select id from dept where name = '财务部';
select salary from emp where dept_id = (select id from dept where name = '财务部');

②. 比 财务部 所有人工资都高的员工信息

select * from emp where salary > all ( select salary from emp where dept_id = (select id from dept where name = '财务部') );

C. 查询比研发部其中任意一人工资高的员工信息
①. 查询研发部所有人工资

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 from dept where name = '研发部') );

行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。
常用的操作符：= 、<> 、IN 、NOT IN

案例:

A. 查询与 “张无忌” 的薪资及直属领导相同的员工信息 ;
①. 查询 “张无忌” 的薪资及直属领导

select salary, managerid from emp where name = '张无忌';

②. 查询与 “张无忌” 的薪资及直属领导相同的员工信息 ;

select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');

表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。
常用的操作符：IN

案例:

A. 查询与 “鹿杖客” , “宋远桥” 的职位和薪资相同的员工信息
①. 查询 “鹿杖客” , “宋远桥” 的职位和薪资

select job, salary from emp where name = '鹿杖客' or name = '宋远桥';

②. 查询与 “鹿杖客” , “宋远桥” 的职位和薪资相同的员工信息

select * from emp where (job,salary) in ( select job, salary from emp where name = '鹿杖客' or name = '宋远桥' );

B. 查询入职日期是 “2006-01-01” 之后的员工信息 , 及其部门信息
①. 入职日期是 “2006-01-01” 之后的员工信息

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 ;

案例数据：

-- 创建dept表，并插入数据
create table dept
(id   int auto_increment comment 'ID' primary key,name varchar(50) not null comment '部门名称'
) comment '部门表';
INSERT INTO dept (id, name)
VALUES (1, '研发部'),(2, '市场部'),(3, '财务部'),(4, '销售部'),(5, '总经办'),(6, '人事部');-- 创建emp表，并插入数据
create table emp
(id        int auto_increment comment 'ID' primary key,name      varchar(50) not null comment '姓名',age       int comment '年龄',job       varchar(20) comment '职位',salary    int comment '薪资',entrydate date comment '入职时间',managerid int comment '直属领导ID',dept_id   int comment '部门ID'
) comment '员工表';
-- 添加外键
alter table empadd constraint fk_emp_dept_id foreign key (dept_id) referencesdept (id);INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id)
VALUES (1, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5),(2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1),(3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1),(4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),(5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1),(6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1),(7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3),(8, '周芷若', 19, '会计', 48000, '2006-06-02', 7, 3),(9, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3),(10, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2),(11, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2),(12, '鹤笔翁', 19, '职员', 3750, '2007-05-09', 10, 2),(13, '方东白', 19, '职员', 5500, '2009-02-12', 10, 2),(14, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4),(15, '俞莲舟', 38, '销售', 4600, '2004-10-12', 14, 4),(16, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4),(17, '陈友谅', 42, null, 2000, '2011-10-12', 1, null);