Oracle高级查询-imooc

chenchenchen

发布于 2023-01-30 17:21:24

2K0

发布于 2023-01-30 17:21:24

文章被收录于专栏：chenchenchenchenchenchen

第1章课程概述

1-1 Oracle高级查询课程概述 (05:14)

第2章分组查询

本章介绍分组函数的概念和应用，以及GROUP BY子句和HAVING子句的使用。

第3章多表连接

本章介绍多表查询的概念，什么是笛卡尔集，等值连接、不等值连接、外连接、自连接和层次查询等多表连接查询的内容。

第4章子查询

本章介绍如何使用子查询以及子查询的类型。

第5章案例集锦

本章通过4个案例的介绍，巩固了所学知识，并能使你认识到本课程所学内容在实际中的应用。

==========================================================

格式：

SELECT column1[,column2…] FROM table_name WHERE conditions GROUP BY … HAVIND conditions ORDER BY …

以下所有例子，都是在oracle安装时的默认数据库中操作的，都可以运行成功。

分组查询：

分组函数：作用于一组数据，并对一组数据返回一个值。

1、常用的分组函数：AVG,SUM,MIN,MAX,COUNT,WM_CONCAT AVG:平均值。SUM:求和。MIN:最小值,MAX:最大值。COUNT:求个数。WM_CONCAT:字符串拼加，行转列

select avg(sal),sum(sal) from emp; select max(sal),min(sal) from emp; select count(empno) from emp; select count(distinct depno) from emp;//去除重复值，用于返回唯一不同的值

行转列：每个部门下的员工姓名

set linesize 200 //设置宽度 col 部门中员工的姓名a60 select deptno 部门号,wm_concat(ename) 部门中员工的姓名 from emp group by deptno;

2、预空函数NVL：

计算平均奖金 select sum(comm)/count(*) 一, sum(comm)/count(comm) 二, avg(comm) 三 from emp;

分组函数会自动过滤掉空值，所以执行结果不一样。

预空函数NVL(value,def_value):当value为空时，返回def_value。

select count(comm),count(nvl(comm,0)) from emp;

3、group by子句： select a,b,c,组函数(X)

from table

group by a,b,c; 先按a进行分组，a相同的看b，b相同的看c，如果都相同，则为一组。

注意：在select列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中;包含在GROUP BY子句中的列不必包含在SELECT列表中。

显示部门的平均工资：部门号，平均工资 select deptno,avg(sal) from emp group by deptno; 按部门不同的职位，统计员工的工资总额 select deptno,job,sum(sal) from emp group by deptno,job order by deptno;

非法使用组函数： select deptno,count(ename) from emp; 错误：所有包含于select列表中，而未包含于组函数中的列都必须包含在GROUP BY子句中。

4、过滤分组——having子句

平均工资大于2000的部门： select deptno,sum(sal) from emp group by deptno having sum(sal)>2000;

where和having的区别：不能在where子句中使用组函数，可以在having子句中使用组函数。

select deptno,avg(sal) from emp group by deptno having deptno=10; select deptno,avg(sal) from emp where deptno=10 group by deptno;

如果过滤条件中没有分组函数时，where与having通用，那么从sql优化的角度来讲，where的效率更高，因为having是先分组再过滤，而where是先过滤再分组，所以，同等条件下，尽量使用where。

5、使用order by子句进行排序 select deptno,avg(sal) from emp group by deptno order by 2 –select表达式的数目

–-a命令 append，追加到上一个命令后面。注意必须添加两个及两个以上的空格 a desc降序排列

6、嵌套分组函数 求出平均工资的最大值 select max(avg(sal)) from emp group by deptno;

7、group by语句增强–适用于报表

group by rollup(a,b)–先对a,b分组；在对a分组；最后不分组

select deptno,job,sum(sal) from emp group by rollup(deptno,job);

= select deptno,job,sum(sal) from emp group by deptno,job + select deptno,job,sum(sal) from emp group by deptno + select deptno,job,sum(sal) from emp

set pagesize 30 每页显示30条记录 break on deptno skip 2–相同的部门号只显示一次，不同的部门号空两行

8、SQL/PLUS报表功能 ttitle col 15 ‘我的报表’ col 35 sql.pno --col+num表示空多少列进行显示 col deptno heading 部门号 col job heading 职位 col sum(sal) heading 工资总额 break on deptno skip 1

将设置保存为.sql格式的文件，把它保存到一个目录下，然后我们可以在sqlplus中把这个文件用get语句加上路径读取进来，然后我们要执行的话就输入一个@然后加上路径，这样格式就设置好了，我们就可以执行sql语句了，执行sql语句后就会显示成我们设置的格式。

多表查询

多个表连接进行查询，数学理论——笛卡尔积。

1、等值连接 等号连接，等值连接,连接条件是‘=’号

select e.empno,e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno

2、不等值连接

between and 小值在前，大值在后

select e.empno,e.ename,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal

3、外连接 通过外连接，把对于连接条件不成立的记录，仍然包含在最好的结果中，分为左外连接和右外连接。左外连接：当条件不成立的时候，等号左边的表仍然被包含。右外连接：当条件不成立的时候，等号右边的表仍然被包含。

特别注意左外连接和右外连接的写法，位置与名字相反，符号用‘(+)’表示。左外连接where e.deptno=d.deptno(+); 右外连接where e.deptno(+)=d.deptno;

按部门统计员工人数

select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数 from emp e,dept d where e.deptno(+)=d.deptno group by d.deptno,d.dname

右边表仍包含，d.deptno有40，e.deptno无40，结果包含40

4、自连接 通过表的别名，将一张表视为多张表

查询员工姓名和员工的老板姓名 select e.ename 员工姓名,b.ename 员工老板 from emp e,emp b where e.mgr = b.empno;

问题：不适合操作大表，原因是自连接至少有两张表参与，并进行笛卡尔全集，连接之后的记录数就是单张表记录数的平方（笛卡尔积行数是两张表行数的乘积）————解决办法：层次查询。

层次查询：可以替代自连接，本质是一个单表查询，不产生笛卡尔积，但是得到的结果不够直观。

select level,e.empno,e.ename,e.sal,e.mgr --leval是伪列表示树的深度 from emp e connect by prior empno=mgr -- 父类的员工号=当前子类的老板号 start with mgr is null --老板号为空表示根节点 || start with empno=7839 --表示从当前条件向下遍历 order by 1; --根据树深度排序

子查询

子查询的十个要点：

1、将子查询语句用“()”括起来。

2、书写风格；子查询应该有缩进，方便阅读。

3、在where,select,having,from中使用； select后只能接单行子查询。【语句只返回一条记录就是单行子查询，返回多条记录就是多行子查询】

select empno,ename,sal,(select job from emp) from emp; -- ORA-01427: 单行子查询返回多个行 select empno,ename,sal,(select job from emp where ename='KING') a from emp;

4、不可以在group by中使用；

5、from后面的子查询；将子查询的结果作为一个表。 select * from (select empno,ename,sal from emp);

6、主查询和子查询可以不是同一张表； select * from emp where deptno = (select deptno from dept where dname=’SALES’);–子查询

select e.* from emp e,dept d where e.deptno=d.deptno and d.dname=’SALES’;–多表查询

理论上讲，推荐用多表查询，因为只访问一次数据库。但是实际上多表查询会产生笛卡尔积。

7、获取top-N需要先对数据进行排序，rownum行号是默认排序，要使用子查询先排序在选出，行号只能使用<,<=，不能使用>,>=.

找到员工表中工资最高的前三名： select * from (select * from emp order by sal desc ) where rownum<=3;

8、一般先进行子查询再执行主查询，但相关子查询例外；相关子查询：外表起别名传递给子查询。

查找员工表中薪水大于本部门平均薪水的员工 select e.empno,e.ename,e.sal,(select avg(sal) from emp where deptno=e.deptno) avg_sal from emp e where e.sal > (select avg(sal) from emp where deptno=e.deptno);

9、单行子查询只能使用单行操作符，多行子查询只能使用多行操作符；单行子查询操作符：=,<,>,<=,>=,<>

select * from emp where job=(select job from emp where empno=7566)and sal>(select sal from emp where empno=7782)

select * from emp where sal = (select min(sal) from emp);

多行子查询操作符：IN,ANY,ALL 查找部门为sales或者accounting的员工信息： select * from emp where deptno in (select deptno from dept where dname=’SALES’ or dname=’ACCOUNTING’);

查找工资比30号部门任意一个员工工资都高的员工信息 select * from emp where sal > any(select sal from emp where deptno=30);

查找工资比30号部门所有员工工资都高的员工信息 select * from emp where sal > all(select sal from emp where deptno=30);

10、注意子查询中null值问题。 a not in(10,20,null)相当于a!=10 and a!=20 and a!=null,然而a!=null永远为假， oracle中空值都比较特殊，不能直接用"="或"<>"号来比较，空值既不在等于的集内，也不在不等于的集内。所以要排除空值，判断是否是null值，只能用is or is not而不能用= 或者！=。

查询不是老板的员工： select * from emp where empno not in (select mgr from emp where mgr is not null);

高级查询实例

1、分页显示员工信息：显示员工号，姓名，月薪 每页显示四条记录，显示第二页的员工，按照月薪降序排序 select r,empno,ename,sal from(select rownum r,empno,ename,sal from(select rownum,empno,ename,sal from emp order by sal desc) e1 where rownum<=8) e2 where r>=5

排序后rownum为乱序

rownum不能用大于号，重新赋予伪列rownum

通过嵌套子查询，再把排序后的伪列变成“实列”

不进行嵌套子查询的结果是错误的，筛选出的是排序后未重新定义的乱序rownum

2、找到员工表中薪水大于本部门平均薪水的员工

相关子查询 select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal from emp e where sal>(select avg(sal) from emp where e.deptno=deptno)

多表查询 select e.empno,e.ename,e.sal,d.avgsal from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d where e.deptno=d.deptno and e.sal>d.avgsal

explain plan for sql --生成执行计划 --查询语句 select * from tables(dbms_xplan.display) --查看执行计划，显示耗时cpu资源

相关子查询比多表查询效率要高。

3、按入职时间统计员工人数，按格式输出

使用函数的方式： select count(*) total,sum(decode(to_char(hiredate,’yyyy’),’1980’,1,0)) “1980”,sum(decode(to_char(hiredate,’yyyy’),’1981’,1,0)) “1981”,sum(decode(to_char(hiredate,’yyyy’),’1981’,1,0)) “1981”,sum(decode(to_char(hiredate,’yyyy’),’1982’,1,0)) “1982”,sum(decode(to_char(hiredate,’yyyy’),’1987’,1,0)) “1987” from emp

使用子查询的方式，dual是伪表，查出所列信息： select (select count(*) from emp ) total, (select count(*) from emp where to_char(hiredate,’yyyy’) = 1980) “1980”, (select count(*) from emp where to_char(hiredate,’yyyy’) = 1981) “1981”, (select count(*) from emp where to_char(hiredate,’yyyy’) = 1982) “1982”, (select count(*) from emp where to_char(hiredate,’yyyy’) = 1987) “1987” from dual;

4、练习，查询出选了每门课的学生姓名