多表查询

多表关系

1. 一对多（多对一）

• 案例：员工 <==> 部门
• 关系：一个员工只能属于一个部门，一个部门却可以包含多个员工
• 实现：在多的一方建立外键（此处为部门表），指向一的那方的主键

2. 多对多

• 案例：学生 <==> 课程
• 关系：一个学生可以选修门课程，一门课程可以被多个学生选修
• 实现：建立一张中间表，中间表至少包含两个外键，分别关联两方的主键

3. 一对一

• 案例：用户 <==> 用户详情
• 关系：一对一的关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放到另一张表中，以提升操作效率
• 实现：在任意一方加入外键，关联另一方的主键，并且设置外键为唯一的（unique）

多表查询介绍

注意：多表查询的时候，需要消除笛卡尔积，也就是 where 后面需要跟上关联关系条件， 例如：select * from emp,dept where emp.dept_id=dept.id;

小知识

笛卡尔积：指在数学中，两个集合的所有组合情况。

多表查询分类

1. 连接查询

• 内连接：查询两表交集部分
• 外连接
  • 左外连接：查询左表所有数据+两表交集部分数据
  • 右外连接：查询由表所有数据+两表交集部分数据
• 自连接：当前表与自身的连接查询，自连接必须使用表别名

2. 子查询

内连接查询

语法

• 隐式内连接 查询的是两表的交集部分

select 字段列表 from 表1,表2 where 条件;

• 显示外连接

select 字段列表 from 表1 inner join 表2 on 连接条件...;

inner关键字可以省略；

案例：

-- 查询每一个员工的姓名，及关联的部门的名称
-- 隐式内连接
select emp.name as empName,dept.name as deptName  from emp,dept where emp.dept_id = dept.id;
-- 表用别名
select e.name as empName,d.name as deptName  from emp e,dept d where e.dept_id = d.id;


-- 显示内连接
select emp.name as empName, dept.name as deptName from emp inner join dept on emp.dept_id=dept.id;
-- 表用别名
select e.name as empName, d.name as deptName from emp e inner join dept d on e.dept_id=d.id;

外连接查询

语法

关键字 outer 可以省略；

• 左外连接

select 字段列表 from 表1 left outer join 表2 on 条件;

包含表 1（左表） + 表 1 表 2 交集部分的数据

• 右外连接

select 字段列表 from 表1 right outer join 表2 on 条件;

包含表 2（右表） + 表 1 表 2 交集部分的数据

自连接查询

• 语法

-- 外联查询
select 字段列表 from 表A 别名A join 表A 别名B on 条件;

--内连查询
select 字段列表 from 表A 别名A, 表A 别名B where 条件;

提示

• 自连接查询，可以是内连接查询，也可以是外连接查询。
• 自连接查询，表必须起别名

• 示例： 员工表(emp)内有一列（managerid）是员工直属领导的 ID，

需求 1：查询员工信息以及自己直属领导的名字；

-- 查询员工信息以及自己直属领导的名字

-- 用外联
select e2.*,e1.name as managerName from emp e1 join emp e2 on e1.id=e2.managerid;
-- 用内联
select e2.*,e1.name as managerName from emp e1, emp e2 where e1.id=e2.managerid;

需求 2：查询所有员工极其领导的名字，若员工没有领导也要查询出来

此时，只能用外连接，因为内连接只能查询两表交集部分，只有外连接才能查某一张表的全部数据

select e1.name empName, e2.name managerName from emp e1 left outer join emp e2 on e1.managerid=e2.id;

联合查询

指的是将多次查询的结果合并起来，形成一个新的查询结果集；

• 关键字：union 或者 union all

• 语法

selsct 字段列表 from 表A ... union [all] select 字段列表 from biao B;

提示

1. union与union all的区别

• union all是直接将查询的结果合并起来
• union将结果合并后会进行去重

2. 联合查询，必须保证联合的两个查询语句字段列表数量和类型完全一致

子查询

• 概念：

  sql 语句中嵌套的 select 语句，称为嵌套查询，也叫子查询

• 示例：

select * from ti where column = (select column1 from t2);

上述 sql 语句中括号内的 select 语句就是子查询

提示

子查询可以出现在insert、update、delete、select 的任何一种 sql 语句中

• 子查询分类

1. 根据查询结果分：

• 标量子查询（子查询得到的结果为单个值）
• 列子查询（子查询得到的结果为一个列）
• 行子查询（子查询得到的结果为一行）
• 表子查询（子查询结果为多行多列）

2. 根据子查询的位置分：

• where 之后
• from 之后
• select 之后

- 标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式

常用的操作符：

操作符	说明
>	大于
<	小于
=	等于
>=	大于等于
<=	小于等于
<>	不等于

• 案例 1：查询“销售部”所有的员工信息

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

• 案例 2：查询在‘令狐冲’后面入职的员工信息

select * from emp where entrydate > (select entrydate from emp where name='令狐冲');

- 列子查询

子查询返回的结果是一列

常用操作符

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

• 案例 1：查询销售部和市场部所有员工信息

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

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

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

-- max 实现
select * from emp where salary > (select max(salary) from emp where dept_id = (select id from dept where name = '财务部'));

- 行子查询

子查询返回结果是一行

常用操作符：=、<>、in、not in

• 案例 1：查询与“张无忌”薪资及直属领导相同的员工信息

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

TIP

(salary, managerid ) = (12500,2)等价于salary=12500 and managerid=2

- 表子查询

子查询返回的结果有多行多列（可以看作返回的结果为一张表）

表子查询经常出现在from之后，将子查询的结果作为一张临时表，在与其他表一起做联查操作

常用操作符：in

• 案例 1：查询与‘宋江’ ‘林冲’ 职位和薪资相同的所有员工信息

SELECT
	*
FROM
	emp
WHERE
	( job, salary ) IN ( SELECT job, salary FROM emp WHERE NAME = '宋江' OR NAME = '林冲' );

TIP

(job, salary) in (select job,salary from emp where name = '宋江' or name = '林冲') 表示 job, salary两个值只需要满足后面 sql 语句的其中一条数据就能匹配上查询结果，因为 后面的 sql 语句查询出的结果为两条数据

• 案例 2：查询入职日期是‘2006-01-01’之后的员工信息，及其部门名称

SELECT
	e.*,
	d.`name` dept_name
FROM
	( SELECT * FROM emp WHERE entrydate > '2002-01-01' ) e
	LEFT JOIN dept d ON e.dept_id = d.id;

多表查询案例

1. 查询员工的姓名、年龄、职位、部门信息。

SELECT
	e.name username,
	e.age age,
	e.job job,
	e.dept_id dept_id,
	d.name dept_name
FROM
	emp e
	LEFT JOIN dept d ON e.dept_id = d.id;

2. 查询年龄小于 30 岁的员工姓名、年龄、职位、部门信息。

SELECT
	e.name username,
	e.age age,
	e.job job,
	e.dept_id dept_id,
	d.name dept_name
FROM
	emp e
	LEFT JOIN dept d ON e.dept_id = d.id
WHERE e.age < 30;

3. 查询拥有员工的部门 ID、部门名称。

SELECT
	*
FROM
	dept
WHERE
	id IN ( SELECT dept_id FROM emp GROUP BY dept_id );

-- 或者
select distinct d.id, d.name from emp e, dept d where e.dept_id = d.id;

4. 查询所有年龄大于 40 岁的员工，及其归属的部门名称；如果员工没有分配部门，也需要展示出来。

SELECT
	e.*,
	d.`name` dept_name
FROM
	emp e
	LEFT JOIN dept d ON e.dept_id = d.id
WHERE
	e.age > 40;

5. 查询所有员工的工资等级。

-- 涉及一张工资等级表grade，工资等级表拥有字段 level, low, high
SELECT
	e.*,
	g.LEVEL salaryLevel
FROM
	emp e,
	grade g
WHERE
	e.salary BETWEEN g.low
	AND g.high;

6. 查询“研发部”所有员工的信息及工资等级。

SELECT
	e.*,
	g.lenvl
FROM
	emp e,
	dept d,
	ggrade g
WHERE
	d.`name` = '研发部'
	AND ( e.salary BETWEEN g.low AND g.high )
	AND e.dept_id = d.id;

7. 查询“研发部”员工的平均工资。

SELECT
	AVG(e.salary) sum_salary
FROM
	emp e,
	dept d
WHERE
	e.dept_id = d.id
	AND d.`name` = '研发部';

8. 查询工资比“灭绝”高的员工信息。

SELECT *  FROM emp WHERE salary > (SELECT salary FROM emp WHER `name` = '灭绝');

9. 查询比平均薪资高的员工信息。

SELECT * FROM emp WHERE salary > (SELECT AVG(salary) FROM emp);

10. 查询工资比所在部门平均工资低的员工信息。

SELECT
	*
FROM
	emp AS e1
WHERE
	e1.salary < ( SELECT avg( e2.salary ) FROM emp AS e2 WHERE e2.dept_id = e1.dept_id );

11. 查询所有的部门信息，并统计部门的员工人数。

-- 展示了子查询在select后面的情况
SELECT
	d.id,
	d.`name`,
	( SELECT count(*) FROM emp e WHERE e.dept_id = d.id ) emp_count
FROM
	dept d;

12. 查询所有学生的选课情况，展示出学生名称，学号，课程名称（多对多）

/*
表：student、course、student_course
连接条件：
student.id = student_course.student_id
course.id = student_course.course_id
*/

select
	s.`name` student_name,
	s.`no` student_no,
	c.`name` course_name
from
	student s,
	course c,
	student_course sc
where
	s.id=sc.student_id
	and c.id=sc.course_id;