学习大数据DAY05 SQL基础语法5

目录

子查询、联合查询

子查询

单行子查询

多行子查询

作业 1

联合查询

连接查询（JOIN）

作业 2

---

SQL 基础语法（五）

子查询、联合查询

子查询

什么是子查询？简单理解，子查询就是嵌套在查询中的查询。

为什么要使用子查询？……很多时候，我们对数据的查询处理不是一个查询语句就

可以完

成的；

不能完成的原因，可能是查询条件比较复杂，也可能是受困于 SQL 自身语法的限制；

这时候，子查询作为查询条件的重要组成部分，用在 WHERE 子句以及 HAVING 子句中，

可以帮助我们快速灵活的完成查询操作……

单行子查询

单行子查询是指返回一行数据的子查询语句。当 where 子句引用单行子查询时，可

以使用

比较运算符（= 、>、<等）。

--查询结果表现形式：单行单列 多行单列 多行多列 单行多列

--单行单列，一个值 一个列 一个表

select avg(sal) from emp;

select * from emp where sal>(select avg(sal) from emp);

select deptno,avg(sal) from emp group by deptno

having avg(sal)>(select avg(sal) from emp);

select ename,sal, (select avg(sal) from emp) from emp;

select pjf from (select avg(sal) pjf from emp)

多行子查询

单行子查询是指返回多行数据的子查询语句。当 where 子句引用多行子查询时，必

须使用

多行比较符（in、any、all、exist 等）

--多行单列 一个列 一个表

select deptno from dept where deptno>10; select * from emp where deptno in (select deptno from dept where deptno>10);

--例子：多条件查询，大多数可以使用开窗代替

--查看每个部门工资最高的员工姓名和他的工资

--开窗的做法

select * from

(

select ename,sal,deptno,rank()over(partition by deptno order by sal desc)

r from emp

)

where r=1;

--多条件多行操作

select ename,sal,deptno from emp

where (deptno,sal) in (select deptno,max(sal) from emp group by deptno)

--多行多列 单行多列 一个表

select ename,sal,rank()over(order by sal desc) from emp

select *

from

(

select ename,sal,rank()over(order by sal desc) paiming from emp

)

where paiming<=3;

作业 1

--1.查询工资比 20 号部门工资都大的员工信息
select * from emp
where sal>(select max(sal) from emp where deptno=20);
--2.查询工资比 20 号部门工资任意一个大的员工信息
select * from emp
where sal>(select min(sal) from emp where deptno=20);
--3.求最高工资员工的姓名
select ename from emp
where sal=(select max(sal) from emp);
--4.查询员工工资和工作都和 20 号部门同时一样的员工信息
select * from empwhere (sal,job) in(select sal,job from emp where deptno=20) and dept!=20;
--5.查询部门平均工资大于整个公司平均工资的部门
select deptno,avg(sal)
from emp
group by deptno
having avg(sal)>
(
select avg(sal) from emp
);
--6.求工资前五名的员工信息
select * from emp
where sal in
(select sal from
(select sal,row_number()over(order by sal desc) as 排名 from emp )
where 排名<=5);
--7.查询 emp 表中所有数据的第 2 条到第 5 条
select * from
(
select rownum as 序号,emp.* from emp
)
where 序号 between 2 and 5 ;
--8.求每个部门工资最高的员工信息
select * from emp
where (deptno,sal) in
(
select deptno,max(sal) from emp
group by deptno
);

联合查询

SQL 联合查询主要是涉及两个表或者多个表（自身表多次）的查询。

主要分为连接查询和集合查询两大类，其中，连接查询分：

• 内连接（inner Join 或 Join）

• 左外连接（left outer Join 或 left Join）

• 右外连接（right outer Join 或 right Join） • 全外连接（full outer Join 或 full Join）

集合查询分为

• minus（差集）

• intersect（交集）

• union、union all（并集）

连接查询（JOIN）

inner join （等值连接或者叫内连接）：只返回两个表中连接字段相等的行。

left join （左连接，左外连接）：返回包括左表中的所有记录和右表中连接字段

相等的记录，如果没有匹配上，以 null 值代表右边表的列。

right join （右连接，右外连接）：返回包括右表中的所有记录和左表中连接字段

相等的

记录，如果没有匹配，以 null 值代表左边表的列

full join （全外连接）：返回左右表中所有的记录和左右表中连接字段相等的记

录，没有

匹配上，以 null 值代表左右边表的列。

笛卡尔连接(交叉连接)：把笛卡尔积所有的结果给显示出来了

A cross join B

select * from emp cross join dept;

select * from emp,dept;

--应用举例，两个球队之间交叉比赛，循环赛

自然连接：一种特殊的内连接

没有链接条件 on，如果两个表里面有一列相等，

系统默认把这一列作为链接条件，并且把这两列合成一列放到表的最前面,

适用于知道表里面有这样一列，并且要用这一列的时候

natural join

select * from emp natural join dept

using:跟自然连接作用相同，不同在于它，当有多个列相同时，可以指定用哪一列

来做链接

select * from emp join dept using(deptno)

--内连接

select * from emp,dept where emp.deptno=dept.deptno

select * from emp inner join dept on emp.deptno=dept.deptno

--自连接 --查询出每个员工的上级领导(查询内容：员工编号、员工姓名、领导编号、领导姓

名)

select yg.empno,yg.ename,ld.empno,ld.ename

from emp yg inner join emp ld on yg.mgr=ld.empno

不等值连接：（过滤条件的符号不是等号）

--查询员工的工资级别

select ename,grade,sal from emp

inner join salgrade on emp.sal between losal and hisal

多表连接

select yg.empno,yg.ename,ld.empno,ld.ename,dname

from emp yg inner join emp ld on yg.mgr=ld.empno

inner join dept on ld.deptno=dept.deptno

--外连接

select * from emp left outer join dept on emp.deptno=dept.deptno

select * from dept right outer join emp on emp.deptno=dept.deptno

select * from emp right outer join dept on emp.deptno=dept.deptno

select * from dept left outer join emp on emp.deptno=dept.deptno

select * from emp full outer join dept on emp.deptno=dept.deptno

--筛选出来 emp 表中不满足条件的数据

select * from emp left join dept on emp.deptno=dept.deptno

where dept.deptno is null

--筛选出 DEPT 表中不满足条件数据

select * from emp e right join dept d on e.deptno=d.deptno

where e.deptno is null

--筛选出 emp 和 dept 表中不满足条件的数据

select * from emp e full join dept d on e.deptno=d.deptno

where d.deptno is null or e.deptno is null

作业 2

--1.查询所有员工的名字和部门名称
select ename,dname
from emp left join dept on emp.deptno=dept.deptno;
--2.查询所有部门名称及部门的所有员工列表
select dname,ename
from dept left join emp on emp.deptno=dept.deptno;
--方便查看，获取一份 emp 表
select * from emp;
--3.求所有员工姓名,工资和其领导的姓名,工资select E.ename,E.sal,L.ename,L.sal
from emp E inner join emp L
on L.empno=E.mgr;
--4.求所有员工工资大于其领导工资的员工姓名和其上级领导姓名，上级领导工资及
员工工资
select E.ename,L.ename,E.sal,L.sal
from emp E inner join emp L
on L.empno=E.mgr
where E.sal>L.sal;
--5.在上题的基础上查询员工所对应的部门
select E.ename,E.deptno,L.ename,E.sal,L.sal
from emp E inner join emp L
on L.empno=E.mgr
where E.sal>L.sal;
--6.查询出不是领导的员工
select ename from emp
where ename not in
(
select L.ename
from emp E inner join emp L
on L.empno=E.mgr
);
--获取一份 salgrade 表方便查看
select * from salgrade;
--7.查询出每个员工编号，姓名，部门名称，工资等级和他的上级领导的姓名，工资
等级
select E.empno,E.ename,D.dname,S.grade,M.ename,MS.grade
from (((emp E left outer join dept D on E.deptno=D.deptno)
left outer join salgrade S on E.sal between S.losal and S.hisal)
left outer join emp M on E.mgr=M.empno)
left outer join salgrade MS on M.sal between MS.losal and MS.hisal;