三、内连接
内连接查询的是两张表交集的部分
内连接查询语法:
隐式内连接
select 字段列表 from 表1,表2 where 条件 ... ;
显式内连接
select 字段列表 from 表1[ inner ] join 表2 on 连接条件 ... ;
例:
-- 内连接演示
-- 1.查询每一个员工的姓名,及关联的部门的名称(隐式内连接实现)
-- 表结构:emp dept
-- 连接条件 emp.dept_id = dept.id
select emp.name,dept.name from emp,dept where emp.dept_id = dept.id;
-- 2.查询每一个员工的姓名,及关联的部门的名称(显式内连接实现)
-- inner join ... on ...
-- 表结构:emp dept
-- 连接条件 emp.dept_id = dept.id
select emp.name,dept.name from emp inner join dept on emp.dept_id = dept.id;
四、外连接
外连接查询语法:
左外连接:
select 字段列表 from 表1 left [ outer ] join 表2 on 条件 ...;
相当于查询表1(左表)的所有数据【包含表1和表2交集部分的数据】
右外连接:
select 字段列表 from 表1 right [ outer ] join 表2 on 条件 ...;
相当于查询表2(右表)的所有数据【包含表1和表2交集部分的数据】
例:
-- 外连接演示
-- 1.查询emp表的所有数据,和对应的部门信息(左外连接)
-- 表结构:emp dept
-- 连接条件 emp.dept_id = dept.id
select emp.*,dept.name from emp left outer join dept on emp.dept_id = dept.id;
-- 2.查询dept表的所有数据,和对应的员工信息(右外连接)
-- 表结构:emp dept
-- 连接条件 emp.dept_id = dept.id
select dept.*,emp.* from emp right outer join dept on emp.dept_id = dept.id;
五、自连接
自连接查询语法
select 字段列表 from 表A 别名A join 表A 别名B on 条件 ... ;
自连接查询,可以是内连接查询,也可以是外连接查询。
例:
-- 自连接
-- 1.查询员工及其所属领导的名字
-- 表结构:emp
select a.name,b.name from emp a,emp b where a.managerid = b.managerid;
-- 2.查询所有员工 emp 及其领导的名字 emp ,如果员工没有领导,也需要查出来(使用外连接)
-- 完全包含员工数据,需要使用左外连接
-- 表结构:emp
select a.name,b.name from emp a left outer join emp b on a.managerid = b.managerid;
六、联合查询-union,union all
对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果。
select 字段列表 from 表A ...
union [ all ]
select 字段列表 from 表B ...;
例:
-- union , union all
-- 1.将薪资低于 5000 的员工,和年龄大于 50 的员工全部查询出来表结构
-- 表结构 emp
select * from emp where salary < 5000
union all
select * from emp where age > 50;
注意:
- 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
- union all 会将全部的数据直接合并在一起,union会对合并之后的数据去重。
七、子查询
概念:SQL语句中嵌套 select 语句,称为嵌套查询,又称子查询。
select * from t1 where column 1 = ( select column 1 from t2);
子查询外部的语句可以是 insert / update / delete / select 的任何一个。
根据子查询结果不同,分为:
- 标量子查询(子查询结果为单个值)
- 列子查询(子查询结果为一列)
- 行子查询(子查询结果为一行)
- 表子查询(子查询结果为多行多列)
根据子查询位置,分为:where 之后、from 之后、select 之后。
7.1标量子查询(子查询结果为单个值)
子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式。
常用的操作符:= <> > >= < <=
例:
-- 标量子查询
-- 查询‘销售部’的所有员工信息
-- a.查询销售部部门ID
select id from dept where name = '销售部';
-- b.根据销售部部门ID 查询员工信息
select * from emp where dept_id = 4;
-- 汇总即可得到
select * from emp where dept_id = (select id from dept where name = '销售部');
7.2列子查询(子查询结果为一列)
子查询返回的结果是一列(可以是多行)。
常用的操作符:in 、not in 、any 、some 、all
操作符 | 描述 |
in | 在指定的集合范围之内,多选一 |
not in | 不在指定的集合范围之内 |
any | 子查询返回列表中,有任意一个满足即可 |
some | 与ANY等同,使用SOME的地方都可以使用ANY |
all | 子查询返回列表的所有值都必须满足 |
例:
-- 列子查询
-- 1.查询‘销售部’ 和 ‘市场部’ 的所有员工信息
select * from emp where dept_id = (select id from dept where name = '销售部') || dept_id = (select id from dept where name = '市场部');
-- 简略:
select * from emp where dept_id in(select id from dept where name = '销售部' || name = '市场部');
-- 2.查询比财务部所有人工资都高的员工信息
select salary from emp where dept_id = (select id from dept where name = '财务部');
select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name = '财务部')) ;
-- 3.查询比研发部其中任意一人工资高的员工信息
select salary from emp where dept_id = (select id from dept where name = '研发部');
select * from emp where salary > any (select salary from emp where dept_id = (select id from dept where name = '研发部'));
7.3行子查询(子查询结果为一行)
子查询返回的结果是一行(可以是多列)。
常用的操作符:= 、<> 、in 、not in
例:
-- 行子查询
-- 1.查询与‘张无忌’的薪资以及直属领导相同的员工信息
select id from emp where name = '张无忌';
select salary from emp where id = (select id from emp where name = '张无忌');
select managerid from emp where id = (select id from emp where name = '张无忌');
select * from emp where
salary = (select salary from emp where id = (select id from emp where name = '张无忌'))
&& managerid = (select managerid from emp where id = (select id from emp where name = '张无忌'));
-- 简略
select salary,managerid from emp where name = '张无忌';
select * from emp where (salary,managerid)=(select salary,managerid from emp where name = '张无忌');
7.4表子查询(子查询结果为多行多列)
子查询返回的结果是多行多列。
常用的操作符: in
例:
-- 表子查询
-- 1.查询与‘鹿杖客’,‘宋远桥’ 的职位和薪资相同的员工信息
select job,salary from emp where name = '鹿杖客' || name = '宋远桥';
select * from emp where (job,salary) in (select job,salary from emp where name = '鹿杖客' || name = '宋远桥');
-- 2.查询入职日期是‘2006-01-01’ 之后的员工信息,及其部门信息
select * from emp where entrydate > '2006-01-01';
select * from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id;
八、多表查询案例
根据需求,完成SOL语句的编写
1. 查询员工的姓名、年龄、职位、部门信息
2. 查询年龄小于30岁的员工姓名、年龄、职位、部门信息。
3. 查询拥有员工的部门ID、部门名称。
4. 查询所有年龄大于40岁的员工,及其归属的部门名称;如果员工没有分配部门,也需要展示出来。
5. 查询所有员工的工资等级。
6. 查询“ 研发部 ”所有员工的信息及工资等级。
7. 查询“ 研发部 ”员工的平均工资。
8. 查询工资比“ 灭绝 ”高的员工信息。
9. 查询比平均薪资高的员工信息。
10. 查询低于本部门平均工资的员工信息。
11. 查询所有的部门信息,并统计部门的员工人数。
12. 查询所有学生的选课情况,展示出学生名称,学号,课程名称
这个题目大家下去可以自行联系,我会在评论区公布答案!
本期分享就到此结束啦。目前为止,多表查询的知识点都分享结束啦,我们下次再见!