MySQL多表查询

表连接查询

什么是多表查询

• 多表查询的作用：
  比如：我们想查询孙悟空的名字和他所在的部门的名字，则需要使用多表查询。
  如果一条 SQL 语句查询多张表，因为查询结果在多张不同的表中。每张表取 1 列或多列。

多表查询的分类：

笛卡尔积现象

什么是笛卡尔积现象

什么是笛卡尔积：

-- 需求：查询所有的员工和所有的部门
select * from emp,dept;

如何清除笛卡尔积现象的影响

我们发现不是所有的数据组合都是有用的，只有员工表.dept_id = 部门表.id 的数据才是有用的。所以需要通过条件过滤掉没用的数据。

内连接

用左边表的记录去匹配右边表的记录，如果符合条件的则显示。如：从表.外键=主表.主键

• 隐式内连接：看不到 JOIN 关键字，条件使用 WHERE 指定

SELECT 字段名 FROM 左表, 右表 WHERE 条件
select * from emp,dept where emp.`dept_id` = dept.`id`;

• 显式内连接

显示内连接：使用 INNER JOIN ... ON 语句, 可以省略 INNER
SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件;
select * from emp,dept where emp.`dept_id`=dept.`id`;

select * from emp join dept on emp.`dept_id`=dept.`id` where emp.`name`='唐僧';

左外连接

• 左外连接：使用 LEFT OUTER JOIN … ON，OUTER 可以省略

SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件

用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL
可以理解为：在内连接的基础上保证左表的数据全部显示(左表是部门，右表员工)

-- 使用内连接查询
select * from dept d inner join emp e on d.`id` = e.`dept_id`;
-- 使用左外连接查询
select * from dept d left join emp e on d.`id` = e.`dept_id`;

右外连接

• 右外连接：使用 RIGHT OUTER JOIN … ON，OUTER 可以省略

SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件

用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示 NULL
可以理解为：在内连接的基础上保证右表的数据全部显示

-- 使用内连接查询
select * from dept inner join emp on dept.`id` = emp.`dept_id`;
-- 使用右外连接查询
select * from dept right join emp on dept.`id` = emp.`dept_id`;

子查询

什么是子查询

-- 查询开发部中有哪些员工
select * from emp where dept_id = (select id from dept where name='市场部');

• 子查询的概念：

1. 一个查询的结果做为另一个查询的条件
2. 有查询的嵌套，内部的查询称为子查询
3. 子查询要使用括号

子查询结果的三种情况：

1. 子查询的结果是单行单列
2. 子查询的结果是多行单列
3. 子查询的结果是多行多列

子查询的结果是一个值的时候

• 子查询结果只要是单行单列，肯定在 WHERE 后面作为条件，父查询使用：比较运算符，如：> 、<、<>、=等

SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;

-- 根据最高工资到员工表查询到对应的员工信息
select * from emp where salary = (select max(salary) from emp);
-- 到员工表查询小于平均的员工信息
select * from emp where salary < (select avg(salary) from emp);

子查询结果是多行单列的时候

• 子查询结果是单例多行，结果集类似于一个数组，父查询使用 IN 运算符

SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;
-- 查询工资大于 5000 的员工，来自于哪些部门的名字
select name from dept where id = (select dept_id from emp where salary > 5000);
select name from dept where id in (select dept_id from emp where salary > 5000);

查询开发部与财务部所有的员工信息
select * from emp where dept_id in (select id from dept where name in('开发部','财务
部'));

子查询的结果是多行多列

• 子查询结果只要是多列，肯定在 FROM 后面作为表

SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件;

子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段

查询出 2011 年以后入职的员工信息，包括部门名称
select * from dept d, (select * from emp where join_date >='2011-1-1') e where
d.`id`= e.dept_id ;

-- 也可以使用表连接：
select * from emp inner join dept on emp.`dept_id` = dept.`id` where
join_date >='2011-1-1';
select * from emp inner join dept on emp.`dept_id` = dept.`id` and
join_date >='2011-1-1';

子查询小结

• 子查询结果只要是单列，则在 WHERE 后面作为条件
• 子查询结果只要是多列，则在 FROM 后面作为表进行二次查询