2021-08-08_小白的oracle 学习整理（六）表嵌套、表连接

oracle 学习整理（六）

1、 多表查询

1.1 子查询嵌套

查询SMITH他的上班地点在哪里？
--条件：名字=SMITH  --emp
--结果：上班的地点     --dept
--找两个表格的共同点：deptno
--先从结果表开始编写sql语句：
select * from scott.dept;
--使用共同点作为查询的条件：
select * from scott.dept where deptno=???;
--从条件表中，将需要的筛选条件用sql语句找出来：
select deptno from scott.emp where ename='SMITH';
--将条件表的sql语句，套入到结果表的sql语句之中。
select * from scott.dept where deptno=(select deptno from scott.emp where ename='SMITH');

查询出在NEW YORK上班的员工姓名：
select ename from scott.emp where deptno=(
select deptno from scott.dept where loc='NEW YORK'
);

查询和SMITH岗位相同的其他员工的信息：
select * from scott.emp where job=(
select job from scott.emp where ename='SMITH'
) and ename!='SMITH';

查询工资比公司平均工资高的员工的信息：
select * from scott.emp where sal>
(select avg(sal) from scott.emp);

查询工资比20号部门最高工资还要高的其他员工的信息：
select * from scott.emp where sal>
(select max(sal) from scott.emp where deptno=20);

查询工资比10号部门所有人工资都要低的其他员工的信息：
select * from scott.emp where sal<
(select min(sal) from scott.emp where deptno=10);

1.2 有时写可能会遇到ORA-00193:值过多错误

select * from scott.emp where ename in (select * from scott.emp )
原因是where 后面的是ename in 里面值的个数也应该是ename这一列
方法1、
select * from scott.emp where ename in (select ename from scott.emp )
方法2、
exists 可以解决
select * from scott.emp where exists (select * from scott.emp )

1.3和所有人的条件进行比较的方法：all()，大于最大的，小于最小的

select * from scott.emp where sal>
all(select sal from scott.emp where deptno=20);

select * from scott.emp where sal<
all(select sal from scott.emp where deptno=10);
 查询工资比10号部门任意一个人工资要高的其他员工的信息：

1.4 和范围内的任意一行信息进行比较：any()，大于最小的，小于最大的

select * from scott.emp where sal>
any(select sal from scott.emp where deptno=10)
and deptno!=10;

查询在部门表中，哪一个部门没有员工。
select * from scott.dept where deptno not in
(select deptno from scott.emp);

查询出同年同月入职的员工的名字：
select * from scott.emp where to_char(hiredate,'yyyymm') in
(select to_char(hiredate,'yyyymm') from scott.emp 
group by to_char(hiredate,'yyyymm')
having count(1)>=2);

1.5 exists()也可以进行嵌套子查询的操作代替in：

查询和SMITH岗位相同的员工的信息：
select * from scott.emp a where 
exists(select * from scott.emp b where b.ename='SMITH' and a.job=b.job)
and ename!='SMITH';

查询工资比10号部门所有人工资都要高的员工的信息：
select * from scott.emp a where 
exists(select * from (select max(sal) m from scott.emp where deptno=10) b where a.sal>m)
and deptno!=10;

查询在部门表中，哪一个部门没有员工。
select * from scott.dept a where
not exists(select * from scott.emp b where a.deptno=b.deptno);

2、表连接

2.1 概念:表连接是笛卡尔积

	1 表格的笛卡尔积：A表和B表所有数据的全部的数学上的组合，包含了所有的有效数据和无效数据
需要通过条件的筛选，去掉无效的数据。
	2 表连接查询，是将A表和B表有效的数据部分，拼接成一个新的大表。

2.2 表连接有四种常见的联合查询的方法,方式一：

将A表和B表共有的数据部分查询出来的方法：
1 内连接
select * from a, b where a.xxx=b.yyy;
select * from scott.emp a,scott.dept b where a.deptno=b.deptno;

将A表和B表共有的数据部分查询出来，然后再查询出A表独自拥有的数据部分：
2 左连接
select * from a, b where a.xxx=b.yyy(+);
select * from scott.dept a,scott.emp b where a.deptno=b.deptno(+);

将A表和B表共有的数据部分查询出来，然后再查询出B表独自拥有的数据部分：
3 右连接：和左连接是相同的概念，只是自己编写sql语句表的左右方向不同而已。
select * from a, b where a.xxx(+)=b.yyy;
select * from scott.emp a,scott.dept b where a.deptno(+)=b.deptno;

将A表和B表共有的数据部分查询出来，然后查询A表的独有数据，然后再查询B表的独有数据：
4 全连接：
select * from a, b where a.xxx=b.yyy(+)
union
select * from a, b where a.xxx(+)=b.yyy;

select * from my_emp a,my_dept b where a.deptno=b.deptno(+)
union
select * from my_emp a,my_dept b where a.deptno(+)=b.deptno;

2.3 表连接有四种常见的联合查询的方法,方式二：

select * from a [ left | right | full ] join b on a.xxx=b.yyy;
1 内连接：
select * from my_emp a join my_dept b on a.deptno=b.deptno;
2 左连接：
select * from my_emp a left join my_dept b on a.deptno=b.deptno;
3 右连接：
select * from my_emp a right join my_dept b on a.deptno=b.deptno;
4 全连接：
select * from my_emp a full join my_dept b on a.deptno=b.deptno;

2.4 练习,相同的结果，用子查询嵌套的方法，速度更快(PL/SQL软件F5看cost开销)

查询在NEW YORK上班的人的名字：
select ename from my_emp a,my_dept b where a.deptno=b.deptno and loc='NEW YORK';
select ename from my_emp a join my_dept b on a.deptno=b.deptno where loc='NEW YORK';
select ename from my_emp where deptno=(select deptno from my_dept where loc='NEW YORK');

--查询每个员工的工资对应的等级是多少，查询出员工名字和等级。
select ename,grade from scott.salgrade a, scott.emp b where sal>=losal and sal<=hisal;
select ename,grade from scott.salgrade a join scott.emp b on sal>=losal and sal<=hisal;

查询SALESMAN这个工作岗位所在的部门名称叫什么名字？
select distinct dname from scott.emp a, scott.dept b where a.deptno=b.deptno
and job='SALESMAN';

查询出每个部门的员工人数，包括没有员工的部门。
select b.deptno,count(empno) from scott.emp a, scott.dept b where a.deptno(+)=b.deptno group by b.deptno;

3、综合练习题

3.1题目：

	请编写sql，请找出申请日期到受理日期超过10天的业务id（包括10天）

3.2 前置表和值

create table a(
t date,
status varchar2(10)--是否是工作日，Y是N不是
);--日历表
insert into a values(date'2020-6-1','Y');
insert into a values(date'2020-6-2','Y');
insert into a values(date'2020-6-3','Y');
insert into a values(date'2020-6-4','Y');
insert into a values(date'2020-6-5','Y');
insert into a values(date'2020-6-6','N');
insert into a values(date'2020-6-7','N');
insert into a values(date'2020-6-8','Y');
insert into a values(date'2020-6-9','Y');
insert into a values(date'2020-6-10','Y');
insert into a values(date'2020-6-11','Y');
insert into a values(date'2020-6-12','Y');
insert into a values(date'2020-6-13','N');
insert into a values(date'2020-6-14','N');
insert into a values(date'2020-6-15','Y');
insert into a values(date'2020-6-16','Y');
insert into a values(date'2020-6-17','Y');
insert into a values(date'2020-6-18','Y');
insert into a values(date'2020-6-19','Y');
insert into a values(date'2020-6-20','N');
insert into a values(date'2020-6-21','N');
insert into a values(date'2020-6-22','Y');
insert into a values(date'2020-6-23','Y');
insert into a values(date'2020-6-24','Y');
insert into a values(date'2020-6-25','N');
insert into a values(date'2020-6-26','N');
insert into a values(date'2020-6-27','N');
insert into a values(date'2020-6-28','Y');
insert into a values(date'2020-6-29','Y');
insert into a values(date'2020-6-30','Y');
commit;

create table b(
id varchar2(10),
sdate date,--业务开始日期
ldate date--业务结束日期
);
insert into b values('S001',date'2020-6-2',date'2020-6-8');
insert into b values('S002',date'2020-6-3',date'2020-6-26');
insert into b values('S003',date'2020-6-5',date'2020-6-15');
insert into b values('S004',date'2020-6-15',date'2020-6-30');
insert into b values('S005',date'2020-6-18',date'2020-6-27');
commit;

3.3 分析

--1、先将a,b表连接得到ab的笛卡尔积（意味着b的每一行都对应a的全部行）
--2、用where筛选日历表a的时间在，业务受理表b的开始和结束时间内容
--3、sid分组过滤，求和得到的就是天数，用having过滤得到结果

3.4 答案,为了方便我把days列写了出来

select * from a where status='Y'
select id,count(*) days from a,b where a.t between b.sdate and b.ldate 
and a.status='Y'
group by id 
having count(*)>=10