多表查询,指从多张表查询数据
- 连接查询
- 内连接: 相当于查询A和B交集部分数据
- 外连接
- 左外连接: 查询左表所有的数据(包括两张表交集部分的数据)
- 有外连接: 查询右表所有的数据(包括两张表交集部分的数据)
- 子查询
内连接查询
隐式内连接查询
select 字段列表 from 表1,表2 where 条件...;
示例
//查询用户(tb_user)以及对应部门(tb_dept)
select tb_user.name,tb_dept.name from tb_user,tb_dept where tb_user.dept_id = tb_dept.id;
起别名示例
如果遇到表名比较长的情况,书写sql语句不方便,这个时候我们可以定义别名来进行书写sql
//查询用户(tb_user)以及对应部门(tb_dept)
select user.name,dept.name from tb_user user,tb_dept dept where user.dept_id = dept.id;
显示内连接查询
select 字段列表 from 表1[inner] join 表2 on 连接条件...;
示例
//查询用户(tb_user)以及对应部门(tb_dept)
select tb_user.name,tb_dept.name from tb_user inner join tb_dept on tb_user.dept_id = tb_dept.id;
外连接查询
//左外连接
select 字段列表 from 表1 left [outer] join 表2 on 连接条件...;
如果左表有19条数据,但是有条数据连接条件是空的,也会展示出来
//右外连接
select 字段列表 from 表1 right [outer] join 表2 on 连接掉件...;
如果右表有19条数据,但是有条数据并不在连接条件中,右侧表数据也会展示出来
子查询
- 介绍: SQL语句中嵌套select语句,称为嵌套查询,又称子查询
- 形式: select * from t1 where colum1=(select colum1 from t2…);
- 子查询 外部语句可以是insert/update/delete/select的任何一个,最常见的是select.
分类
- 标量子查询: 子查询返回的结果是单个值
- 列子查询: 子查询返回的结果是一列
- 行子查询: 子查询返回的结果为一行
- 表子查询: 子查询返回的结果为多行多列
标量子查询
- 子查询返回的结果是单个值(数字,字符串,日期等),最简单的形式
- 常用的操作符: = <> > >= < <=
//查询教育部下的所有员工
//先查询教育部的部门id
select id from tb_dept where name='教育部';
//在查询教育部门id下的所有用户
select * from tb_user where dept_id=2;
//合并后为sql语句为
select * from tb_user where dept_id=(select id from tb_dept where name='教育部');
列子查询
- 子查询返回的结果是一列(可以是多行)
- 常用的操作符,in not in 等
//查询教育部和后勤部的所有员工
//先查询教育部和后勤部的id
select id from tb_dept where name='教育部' or name='后勤部';
//再根据查询到的部门id 查询对应的用户列表
select * from tb_user where dept_id in(2,3);
//合并后的sql语句
select * from tb_user where dept_id in(select id from tb_dept where name='教育部' or name='后勤部');
行子查询
- 子查询返回的是一行(可以是多列)
- 常用操作符:= ,<> ,in ,not ,in等
//查询张三丰的入职日期,并查出与其相同的入职日期和相同部门的员工
//先查询张三丰的入职日期
select join_time,dept_id from tb_user where name='张三丰';
//再查出入职日期相同的员工
select * from tb_user where join_time='2020-01-01' and dept_id=3;
//合并后的sql语句
select * from tb_user where (join_time,dept_id)=(select join_time from tb_user where name='张三丰');
表子查询
- 子查询返回的结果是多行多列,常作为临时表
- 常用的操作符 in
//查询入职日期'2022-01-01'之后的员工,及其部门名称
//先查询入职日期是'2022-01-01'之后的员工
select * from tb_user where join_time>'2022-01-01';
//再查询对应的员工信息和部门
select user.*,dept.name from (select * from tb_user where join_time>'2022-01-01') user, tb_dept dept where user.dept_id=dept.id;