目录
多表查询分类总览:
1、内连接
语法:
# 隐式内连接
select 字段列表 from 表1,表2 where 条件···;
# 显示内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件···;
2、外连接
描述:外连接又分为左外连接和右外连接;查询范围如下图;
左外连接:左外连接查询的是左表的全部数据(下图浅蓝色区域)以及与右表的交集部分(下图浅绿色区域);
右外连接:右外连接查询的是右表的全部数据(下图浅黄色区域)以及与左表的交集部分(下图浅绿色区域);
语法:
# 左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件;
# 右外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件;
案例演示:
# 查询emp表的所有数据,和对应的部门信息(左外连接)
select emp.*, dept.* from emp left outer join dept on emp.dept_id = dept.id;
# 查询dept表的所有数据,和对应的员工信息(右外连接)
select dept.*, emp.* from emp right outer join dept on emp.dept_id = dept.id;
3,自连接
语法:
select 字段列表 from 表a 别名a join 表a 别名b on 条件;
案例演示:
4,联合查询:
注意点:
1,对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致,;
2,union关键字默认去重,如果使用union all 可以包含重复项;
应用场景:要查询的结果来自多个表,且多个表之间没有直接的连接关系,但查询的信息一致时候;
SELECT * from 表1 LEFT JOIN 表2 on 条件
UNION [all]
SELECT * from 表1 RIGHT JOIN 表2 on 条件;
案例演示:
子查询概念及分类:
对子查询的理解:
子查询实际就是查询语句嵌套查询语句,然后通过子查询语句的返回值不同可以将子查询分为不同种类;以往我们在sql语句中的where条件中的条件都是固定的值,有了子查询语句我们就可以实现将查询语句放到where条件中去;
1,标量子查询:
描述:子查询的返回结果为单个的值(数字、字符串、日期等):
案例一:
案例二:
2,列子查询:
描述:子查询返回的结果是一列(可以是多行);
常用操作符:IN ,NOT IN ,ALL ,SOME ,ANY ;
案例一:
# 列子查询
# 查询销售部和市场部的所有员工信息
# 查询销售部和市场部的id
select id from dept where name='销售部' or name='市场部'; #id为2 4
# 查询两个部门的所有员工
select * from emp where dept_id in (2,4);
# 合并
select * from emp where dept_id in (select id from dept where name='销售部' or name='市场部');
案例二:
案例三:
3,行子查询:
描述:子查询的返回结果是一行(可以是多列);
案例:
# 查询与张无忌的薪资及直属领导相同的员工信息
# 查询张无忌的薪资和直属领导
select salary, managerid from emp where name='张无忌';
# 查询与张无忌的薪资及直属领导相同的员工信息
select * from emp where (salary,managerid)=(select salary, managerid from emp where name='张无忌');
4,表子查询:
描述:子查询的返回值是多行多列,也就是返回一个表格;
案例一:
# 查询与鹿杖客和宋远桥的职位和薪资相同的员工信息
select * from emp where (job, salary) in ( select job, salary from emp where name in ('鹿杖客', '宋远桥'));
案例二:
# 查询入职日期是’2006-01-01‘之后的员工信息和部门信息
# 先查询出入职在’2006-01-01‘之后员工的所有信息
# 与部门表左连接
select e.*, dept.* from (select * from emp where entrydate>'2006-01-01') e left outer join dept on e.dept_id=dept.id;