Oracle-第一章-多表查询和其他

4多表关联查询

4.1表的别名

①在多表关联查询时，如果多个表之间存在同名的列，则必须用表名限定列的引用如dept.deptno,emp.deptno
②为使语句简洁，使用表别名，表别名在from子句中定义如 emp e
③表别名一经定义，在整个查询语句中就只能用表别名，而不能用表名 emp.ename × e.ename √
④表别名只在所定义的查询语句中有效

4.2内连接（直连-只显示关联到的结果，会遗失部分数据）

####
select d.dormno, s.name
from dorm d
[inner]join student s--默认是inner内查询，不用写
on d.id=s.dormid;
--用相同的ID来进行表之间连接####
select b.buildname,d.dormno,s.namefrom building bjoin dorm don b.id = d.buildidjoin student son d.id = s.dormid;
--三个及以上连接####
select d.dormno, s.namefrom dorm djoin student son d.id = s.dormidwhere s.gender = ;--条件语句放在后面####
内连接的另一种写法（了解即可，千万别用）
select e.ename,d.dname,e.salfrom emp e,dept d--省去了join on 语句where sal>=3000;--忘记写where子句会导致笛卡尔积任意关联，会导致非常严重的数据库崩溃--笛卡尔积：两个表做任意关联，无关联条件，导致数据暴增

4.3外连接（可以返回不相关的数据行，不必须有条件语句）

（1）左连接（以左边的为主表，显示所有行的结果，以右边的为从表，只显示关联的结果）

select e.ename,d.dname,e.comm
from dept d
left join emp e
on e.deptno=d.deptno;--此处应该在建表时用id而不用no####
select s.name,d.dormno 
from student s, dorm d 
where s.dormid=d.id(+);
--左连接另种写法，仅作了解，千万别用，会造成笛卡尔积数据库崩溃或数据丢失
####
▲如果左连接的从表添加where条件，则左连接失效
例：查询所有人的姓名，如果其他宿舍在2楼，显示宿舍号
select s.name,d.dormno
from student s
left join dorm d
on d.id=s.dormid
where d.floor=2;--此处where应改为and，把选择条件变成关联条件

（2）右连接

right用法和左连接一样

（3）全连接（特殊情况才用，一般不用，显示左右全部数据，如果关联则在同一行）

full同上

4.4自然连接(几乎不用)

自然连接指在检索多个表时
oracle会将个表中的列和第二个表中具有相同名称的列进行自动连接
语法： natural join
select empno,ename,job,dname
from emp 
natural join dept
where sal>2000;
--由于自然连接要求表之间必须有相同名称的列，这样容易在出现不可预知的错，故几乎不用

4.5自连接（一表两用）

####
select em2.ename 上层管理者,em1.ename 下属员工
from emp em1
join emp em2
on em1.mgr=em2.empno--关键在于找到对应关系
order by em1.mgr;####
如果左连接后使用自连接，则自连接会失效
例：
select d.dormno,s.name,s1.name
from student s
left join dorm d
on d.id=s.dormid
left join student s1
on d.headno=s1.stuno;
--仅作语法示例

4.6交叉连接(几乎不用，会造成笛卡尔积)

语法： cross join
select count(*) --计算查询结果的行数
from dept
cross join emp;
--执行结果是一个笛卡尔积等同于select count(*)
from dept,emp;
--执行结果是一个笛卡尔积

5子查询

又称嵌套查询在执行数据操作时
某个操作要依赖另一个select语句的查询结果
可以select语句嵌入该语句中注意：
①子查询子句必须用括号括起来
②子查询不能包括order by
③子查询允许多层（不超过255行）嵌套
④子查询比多表关联查询更灵活、功能更强大、更容易理解，但效率更低

5.1单行子查询

指子查询语句的返回结果只有一行
当在where子句中引用单行子查询时可以用单行比较运算符（= > < >= <= <>）①关联子查询在select语句（查询结果中）
select s.name,s.gender,s.groupno,(select avg(t.age)from student t)--此处的平均年龄只作为常量，不是聚合函数
from student s;
--查询每个人的姓名、年龄和班级平均年龄②关联子查询在条件语句中
select empno, ename, sal--外查询from empwhere sal > (select min(sal) from emp)--括号内是内查询and sal < (select max(sal) from emp);
--查询emp表中不是高或低工资的员工编号、姓名、工资

5.2多行子查询

指子查询语句的返回结果不止一行
当在where子句中引用多行子查询时必须用多行比较符（in any all）

（1.1）in运算符

在多行子查询中使用in运算符时
外查询会尝试与子查询结果中任何一个结果进行匹配
只要有一个匹配成功则外查询会返回当前的检索记录
select empno,ename,job
from emp
where deptno in
(select deptno from dept where dname<>'SALES');
--查询emp表中不是销售部门的员工信息

（1.2）exists运算符

exists存在（前面可以加not）--查询有预备党员的小组
select s.groupnofrom student swhere s.groupno in select t.groupnofrom student twhere t.political = '预备党员');
select distinct s.groupnofrom student swhere exists (select t.groupnofrom student twhere t.political = '预备党员'and s.groupno = t.groupno);
--查询没有预备党员的小组
select distinct s.groupnofrom student swhere not exists (select t.groupnofrom student twhere t.political = '预备党员'and s.groupno = t.groupno);工作中常用 exists代替 in（速度更快）in关联子查询先子查询全部再主查询（速度慢，但容易想）exists：关联子查询先主查询一条一条查询再子查询（子查询不需要查询全部，速度更快）
①将原 SQL中 in改为 exists
②在子查询中添加条件（原子查询的结果=原主查询中 in的前面一致）

（2）any运算符

any运算符必须与单行操作符结合使用
并且返回行只要匹配子查询的任何一个结果即可
select deptno,ename,sal
from emp
where 
sal>any (select sal from emp where deptno=30)
and deptno<>30;--and与sal语句是并列的
--查询emp表中工资大于30号部门的任意一个员工工资的其他部门的员工信息
--实质上查询emp表中工资大于30号部门的低的一个员工工资的其他部门的员工信息

（3）all运算符

all运算符必须与单行操作符结合使用
并且返回行必须匹配子查询的所有结果
select deptno,ename,sal
from emp
where 
sal>all (select sal from emp where deptno=30);
----查询emp表中工资大于30号部门的所有员工工资的其他部门的员工信息
--与any相比较

5.3多列子查询

select *
from student s 
where (s.age,s.gender)=(select t.age,t.gender from student t where name='张简简');
--查询年龄和性别都和张简简相同的人
--注意s.age,s.gender和t.age,t.gender前后顺序要一致

5.4关联子查询

在单行或多行子查询中内查询和外查询是分开执行的
外查询仅仅使用内查询的终结果在一些特殊需求的子查询中内查询和外查询相互关联
被称为关联子查询①关联子查询在select语句（查询结果中）
select s.name,s.gender,s.groupno,(select avg(t.age)from student t where t.groupno=s.groupno)
from student s;
--使用了两个表别名
--不需要使用分组函数
--例 select avg(t.age) from student t where t.groupno*=1即可查询1组的平均年龄②关联子查询在条件语句中
select s.name,s.age
from student s
where s.age>(select avg(t.age) from student t where t.groupno=s.groupno);
--查询比小组平均年龄大的人

5.5子查询难点用法

--查询A型血人数比B型血人数多的宿舍
select ssaxx.dormno
from (select d.dormno,count(1) ssaxxrs from student s join dorm d on s.dormid=d.id where s.xo='A' group by d.dormno) ssaxx--把A血型的寝室和人数看作一个表
join (select d.dormno,count(1) ssbxxrs from student s join dorm d on s.dormid=d.id where s.xo='B' group by d.dormno) ssbxx--把B血型的寝室和人数看作一个表
on ssaxx.dormno=ssbxx.dormno
where ssaxx.ssaxxrs>ssbxx.ssbxxrs;
--当问题不清晰的时候拆解成多个表来解决问题即可

6 开窗函数

也称分析函数窗，就是范围在over子句所限定的范围内进行查询，速度优于子查询。

6.1 partition by

select s.name,s.groupno,s.age,max(s.age) over(partition by s.groupno) age1
from student s;
--查询所有同学姓名、组号、年龄和其最大年龄
--partition by 类似于 group by具有分组的作用
--【max(s.age) over(partition by s.groupno) age1】是一个整体使用多个聚合函数时每个要单独加over语句
--由于受开窗范围的影响别名（此处为age1）要放在 order by后

6.2 order by

select s.name,s.groupno,s.age,max(s.age)over(partition by s.groupno order by s.age)
from student s;
--查询所有同学姓名、组号年龄和窗口内大年龄
--order by不仅仅具有排序的作用只有 order by 没有 partition by 则仅有排序功能 会使窗口发生变化窗口变化为从 partition by选定的窗口的行数据开始到与被查询主体 order by后的列名（此处为s.age）相同值的所有数据行为止（不明白就运行代码试试）

6.3 排序类开窗函数 row_number()、 rank()、dense_rank() 

在窗口范围内对 order by 后指定的数据进行排序select s.name,s.groupno,s.age,row_number() over(partition by s.groupno order by s.age)
from student s;
--相同年龄也分先后顺序如：排序 1  2  3  4  5年龄 18 19 19 19 20
select s.name,s.groupno,s.age,rank() over(partition by s.groupno order by s.age)
from student s;
--并列第二然后第五如：排序 1  2  2  2  5年龄 18 19 19 19 20
select s.name,s.groupno,s.age,dense_rank() over(partition by s.groupno order by s.age)
from student s;
--并列第二然后第三如：排序 1  2  2  2  3年龄 18 19 19 19 20

6.4 偏移类开窗函数 lag() lead()

select s.name,s.groupno,s.age,lag(s.age) over(partition by s.groupno order by s.age)
from student s;
--lad(参数1，参数2，参数3)参数1（列名）所处位置的数据往上偏移参数2个位置如果偏移后数据为空则用参数3的数据填充
--lead(参数1，参数2，参数3)往下偏移

6.5 了解部分

（1）first_value() last_value()

--不可和聚合函数同用
select s.name,s.groupno,s.age,first_value(s.age) over(partition by s.groupno order by s.age),last_value(s.name) over(partition by s.groupno order by s.age)
from student s;    
--注意要写逗号
--first_value(col_name)返回该窗口中某列的个值
--last_value(col_name)返回该窗口中某列的后一个值

（2）影响开窗范围的参数(range between 参数1 and 参数2)（可以有聚合函数，必须有order by）

range between 参数1 and 参数2
在原来的窗口范围内再进行选定select q.realname,q.groupno,q.age,max(q.age) over(partition by q.groupno order by q.agerange between 参数1 and 参数2) 
from qqinfo q;参数可以替换为以下：
unbounded preceding 组内首行
current row 当前行
unbounded following 组内末行
1 preceding 组内当前行前面1行
1 following 组内当前行后面1行
range 值比较--不了解
rows 行比较--不了解

7 其他

7.1 查询中的集合操作

两个集合间 交 并 差
（1）并 union （自带去重效果）（去重必带排序）（oracle中去重和排序都非常慢）union all （不去除重复内容）（执行速度更快）
例：select s.name from student sunionselect e.name from emp e
此外要注意多列同时运算的情况：对应列如s.age和e.age的属性和类型要一致select s.name，s.age from student sunionselect e.name,e.age from emp e
（2）交（ intersect ）
（3）差 （ minus ）

7.2 case when

查询结果根据类别不同，查询方式随之不同
select s.name，--逗号不能少case--开始when s.gender=1 then '男生'when s.gender= then '女生'--也可以只有一个 when then语句 else '其他'end--结束
from student s;需要注意的是 case when 是顺序执行的
如果前面的条件包含了后面的条件
则后面的条件
如：select s.name,casewhen s.score>60 then '及格'when s.score>80 then ''else '不及格'endfrom student s;--则60分以上的都是及格，即使是100也是及格而不是当 case when 中的条件是确定值（即用等号=）时可等价于decode如：select s.name,--逗号不能少decode(s.gendere,1,'男生',,'女生','其他')from student s;

7.3 行列相互转换

（1）行转列【常面】

将同一列内容分成多列
① group by
② case when
③ 聚合函数例（查询班内全部人数，男生数量，女生数量）：select count(1),count(case when s.gender=1 then '是' end) nans,count(case when s.gender= then '是' end) nvsfrom student s;
例（查询各组全部人数，男生数量，女生数量）：select s.groupno，count(1),count(case when s.gender=1 then '是' end) nans,count(case when s.gender= then '是' end) nvsfrom student sgroup by s.groupno;

（2）列转行【常面】

将不同列的内容汇总到同一列
用 union[all]（并-操作）select name,'男' gender from stul where nans=1
union
select name,'女' from stu1 where nvs=1;