MySQL之多表查询
一、交叉连接查询(笛卡尔积)
1.什么是交叉连接查询(笛卡尔积)?
交叉连接(笛卡尔积)返回被连接的两个表所有数据行的笛卡尔积，返回结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。
简单的说就是左表的每条数据与右表的每条数据组合。
2.笛卡尔积例子
1.产生笛卡尔积：
查询所有的员工和所有的部门
select * from emp,dept;
2.消除笛卡尔积：通过条件过滤掉没用的数据
查询所有的员工和所有的部门
select * from emp,dept where emp.dept_id=dept.id;
二、内连接
1.内连接查询步骤
1) 确定查询哪些表
2) 确定表连接的条件
3) 确定查询的条件
4) 确定查询的字段
2.隐式内连接
1.什么是隐式内连接?
隐式内连接：看不到 JOIN 关键字，条件使用 WHERE 指定
2.隐式内连接语法与例子
1.语法:
SELECT 字段列表 FROM 左表, 右表 WHERE 条件
2.例子:
-- 查询所有员工信息和对应的部门信息
SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
-- 查询员工名称，性别 ,部门名称
SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
3.显式内连接
1.什么是显式内连接?
显示内连接：使用 INNER JOIN … ON 语句, 可以省略 INNER
2.显式内连接语法与例子
1.语法：
SELECT 字段列表 FROM 左表 [INNER] JOIN 右表 ON 条件
2.例子:
-- 查询所有员工信息和对应的部门信息
SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;
SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`;
-- 查询员工名称，性别。部门名称
SELECT emp.name,emp.gender,dept.name FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;
二、 外链接
1.左外连接
1.什么是左外连接?
用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL
2.左外连接语法与示例
1.语法：
select 字段列表 from 表1 left [outer] join 表2 on 条件；
2.例子:
-- 查询的是左表所有数据以及其交集部分。
SELECT * FROM emp LEFT JOIN dept ON emp.`dept_id` = dept.`id`;
2.右外连接
1.什么是右外连接?
用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示 NULL
2.右外连接语法与示例
1. 语法：
select 字段列表 from 表1 right [outer] join 表2 on 条件；
2.例子：
-- 查询的是右表所有数据以及其交集部分
SELECT * FROM dept RIGHT JOIN emp ON dept.`dept_id` = emp.`id`;
三、全连接
1.什么是全连接?
全连接查询：是在内连接的基础上增加 左右两边没有显示的数据。简单的说就是显示左右表中全部数据
2.全连接用法与示例
1. 语法：
select 字段列表 from 表1 leftjoin 表2 on 条件
union/union all
select 字段列表 from 表1 right join 表2 on 条件
2.例子
查询所有的员工和所有的部门
SELECT * FROM emp LEFT JOIN dept ON emp.id = dept.id
UNION / UNION ALL
SELECT * FROM emp RIGHT JOIN dept ON emp.id = dept.id;
3.注意：
1.union两张表的列数必须相同
2.合并表的列名称是以第一张 表列名为准
3.使用union 时，进行表链接后会筛选掉重复的记录，由于去除重复数据比较耗时,通常采用union all
4.union all 不会去除重复记录
四.子查询
1.什么是子查询?
1)一个查询的结果做为另一个查询的条件
2) 有查询的嵌套，内部的查询称为子查询
3) 子查询要使用括号
2.子查询结果的三种情况
1. 子查询的结果是单行单列
SELECT 查询字段 FROM 表 WHERE 字段= （子查询);
注意:
子查询结果只要是单行单列，肯定在 WHERE 后面作为条件，供父查询使用：使用运算符去判断，如：> 、<、<>、= 等
-- 查询员工工资小于平均工资的人
SELECT AVG(salary) FROM emp;
SELECT * FROM emp WHERE emp.salary < AVG(salary)
--子查询
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
2. 子查询的结果是多行单列的
注意:
子查询结果是单例多行，结果集类似于一个数组，父查询使用 IN 运算符
-- 查询'采购部'和'市场部'所有的员工信息
SELECT id FROM dept WHERE NAME = '采购部' OR NAME = '市场部';
SELECT * FROM emp WHERE dept_id = 1 OR dept_id = 2;
-- 子查询
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '采购部' OR NAME = '市场部');
3. 子查询的结果是多行多列的
注意：
1.子查询结果只要是多列，肯定在 FROM 后面作为一张虚拟表参与查询
2.子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段
例子：
--查询部门信息 人员信息 要求:人员年龄大于20
SELECT * FROM emp WHERE emp.age > 20
SELECT * FROM dept t1 ,子查询表 t2 WHERE t1.id = t2.dept_id;
--子查询
SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.age > 20) t2 WHERE t1.id = t2.dept_id;
-- 隐式内连接
SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.age > 20
