5.oracle_高级查询
分组查询
常用的分组函数：AVG、sum、min、max、count、wm_concat
①select count(distinct deptno) from emp;
②select deptno,wm_concat(ename) from emp group by deptno;
小技巧：set linesize 200;--设置显示宽度
算平均工资有3种方式
一、sun(sal)/count(*)
二、sun(sal)/count(sal)
三、avg(sal)
注意：nvl函数使分组函数无法忽略空值，将空值换为0。
分组数据 group by子句的使用
语法
|
Select column,group_function(column) From table [where condition] [group by group_by_expression] [order by column]; |
可以使用group by子句将表中的数据分成若干组。
示例：求每个部门的平均工资，要求显示：部门号，部门的平均工资。
Select deptno,avg(sal) From emp Group by deptno;
抽象：Select a,b,c,a组函数(X) From table Group by a,b,c;
结论：①在select列表中所有未包含在组函数中的列都应该包含在Group by子句中。
②包含在Group by 子句中的列不必包含在select列表中
示例：求每个部门的平均工资，要求显示部门：部门的平均工资。
Select avg(sal) from emp group by deptno;
多个列分组
示例：按部门、不同职位，统计员工的工资总额
Select deptno,job,sum(sal) from emp group by deptno,job order by deptno;
非法使用组函数
所用包含于select列表中，而未包含于组函数中的列都必须包含于group by子句中。
|
Select deptno,count(ename) from emp; |
Having子句的使用：过滤分组数据
语法：
|
Select column,group_function From table [where condition] [group by graoup_by_expression] [having group_condition] [order by column]; |
示例：
求平均工资大于2000的部门，要求显示：部门号，平均工资
Select avg(sal) from emp group by deptno having avg(sal)>2000;
Where 与having的区别
①不能在where子句中使用组函数。
②可以在HAVING子句中使用组函数。
错误示例：
|
Select deptno,avg(sal) From emp Where avg(sal)>2000 Group by deptno; |
Where与having可以通用的情况
示例：查询10号部门的平均工资
|
Select deptno,avg(sal) From emp Group by deptno Having deptno=10; |
|
Select deptno,avg(sal) From emp Where deptno Group by deptno; |
尽量使用Where 效率高
在分组查询中使用order函数
示例：求每个部门的平均工资，要求显示部门号。部门的平均工资，并且按照工资升序排列。
可以按照：列、列名、表达式、序号进行排序。
|
Select deptno,avg(sal) 平均工资 --可以用别名 From emp Group by deptno Order by avg(sal) --可以用别名、可以用列序号 |
--a命令 append 表示在上一条sql语句后面加上... a desc /
分组函数的嵌套
示例：求部门平均工资的最大值
|
Select max(avg(sal)) From Emp Group by deptno |
Group by 语句的增强
分析：
Select deptno,job,sum(sal) from emp group by deptno,job
Select deptno,sum(sal) from emp group by deptno
Select sum(sal) from emp
|
Select deptno,job,sum(sal) from emp group by rollup(deptno,job); |
语法：
Group by rollup(a,b)
等价于：
Group by a,b
+
Group by a
+
Group by null
Break on deptno skip 2 相同部门号的数字只显示一次
Set pagesize 30 设置显示30行
SQL*Plus的报表功能
报表包括：标题、页码、别名等。
SQL*Plus的报表功能
|
Ttitle col 15 ‘我的报表’ col 35 sql.pno Col deptno heading 部门号 Col job heading职位 Col sum(sal) heading 工资总额 Break on deptno skip 1 |
Get d:\temp\report.sql //读取文件，设置格式
多表查询（笛卡尔集）
为了避免笛卡尔集，可以在where加入有效的连接条件，在实际运行环境下，应避免使用笛卡尔全集。
等值连接
示例：查询员工信息，要求显示：员工号，姓名，月薪，部门名称
|
Select e.empno,e.ename,e.sal,d.dname from emp e,dept d where e.deptno d.deptno; |
不等值连接
示例：查询员工信息，要求显示：员工号，姓名，月薪，薪水的级别
Select * from salgrade;--查询工资级别表
|
Select e.empno,e.ename,e.sal,s.grade From emp e,salgrade s Where e.sal beteen s.losal and s.hisal; |
外连接
核心：通过外连接，把对于连接条件不成立的记录，仍然包含在最后的结果中。
左外链接：当连接条件不成立的时候，等号左边的表仍然被包含
右外链接：当连接条件不成立的时候，等号右边的表仍然被包含
示例：按部门统计员工人数，要求显示：部门号，部门名称，人数
|
Select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数 From emp e,dept d Where e.deptno(+)=d.deptno --(+)表示左外链接 Group by d.deptno,d.dname; |
自连接
核心：通过别名，将同一张表视为多张表
示例：查询员工姓名和员工的老板姓名
|
Select e.ename 员工姓名,b.ename 老板姓名 From emp e,emp b Where e.mgr=b.empno; |
自连接存在的问题
不适合操作大表
解决办法：层次查询
某些情况下，可以替代自连接
本质上，是一个单表查询、
|
Select level,empno,ename,sal,mgr --level伪列 From emp Connect by prior empno=mgr -- Connect by连接 条件：上一层的员工号=这一层的老板号 Start with empno=7839; --从empno遍历这个树，等价Start with mgr is null;mgr上级部门号 Order by 1; |
子查询
子查询概述
单行子查询：返回1条记录
多行子查询：返回多条记录
子查询的使用
语法：
|
Select select_list From table Where expr operator ( Select select_list From table ); |
示例：查询工资比scott高的员工信息
|
Select *from emp where sal>( Select sal from emp where ename=‘SCOTT’); |
子查询注意的10个问题：
l 子查询语法中的小括号
l 子查询的书写风格
l 可以使用子查询的位置：where，select，having，from
l 不可以使用的子查询的位置：group by后面不能使用子查询
l 强调：from后面的子查询
l 主查询和子查询可以不是同一张表
多表查询比子查询好
l 一般不在子查询中，使用排序；但在Top-N分析问题中，必须对子查询排序
--rownum行号 伪列。行号永远按照默认的顺序生存,不随排序而变化，行号只能使用<,<=;不能使用>,>=。
l 一般先执行子查询，再执行主查询；但相关子查询例外
示例：找到员工表中薪水大于本部门平均薪水的员工
|
Select empno,ename,sal,(select vag(sal) from emp where deptno=e.deptno) avgsal From emp e Where sal>(select avg(sal)from emp where deptno=e.deptno); |
l 单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
示例：查询员工信息，要求：职位与7566员工一样，薪水大于7782员工的薪水
Select * from emp where deptno in
(select deptno from dept where dname=’sales’ or dname=’accounting’)
也可以用多表查询。
Select * from emp where sal>any (select sal from emp where deptno=30);
Select * from emp where sal>all (select sal from emp where deptno=30);
l 注意：子查询中是null值问题
主要问题是注意子查询中的结果不能为空
多表查询比子查询好
子查询的类型
综合示例
案例1
分页查询显示员工信息：显示员工号、姓名、月薪
-每页显示4条记录
显示第二页的员工
-按照月薪降序排列
提示：rownum
|
Select rownum, 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; |
案例二
找到员工表中薪水大于本部门平均薪水的员工。
|
Explain plan for --得到下面语句的执行计划 Select empno,ename,sal,(select avg(sal) from emp where deptnoe.deptno)avgsal From emp e Where sal>(select avg(sal) from emp where deptno=e.deptno);
Select * from table (dbms_xplan.display);--查看刚刚的执行计划 |
以上代码和下面的查询结果一样
|
Select e.empno,e.ename,e.sal,d.avgsal From emp e,(select deptno,avg(sal) avgsal from emp group by deptno) Where e.deptno=d.deptno and e.sal>d.avgsal; |
上部分代码占用资源少。
案例三
按部门统计员工人数，按照如下格式输出：
注意：员工的入职年份已知
|
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’),’1982’,1,0))”1982”, Sum(decode(to_char(hiredate,’YYYY’),’1987’,1,0))”1987”, From emp; |
Host cls 清除文字
用子查询方式
|
select (select count(*)from emp)Total, (select count(*)from emp where to_char(hiredate,’yyyy’)=’1980’) (select count(*)from emp where to_char(hiredate,’yyyy’)=’1981’) (select count(*)from emp where to_char(hiredate,’yyyy’)=’1982’) (select count(*)from emp where to_char(hiredate,’yyyy’)=’1987’) From dual; |
案例四
|
select cstu.ci_id, wm_concat(stu.stu_name) stu_name from (select c.ci_id, stu.stu_name from PM_CI c, PM_STU stu where instr(c.stu_ids, stu.stu_id) != 0) cstu group by cstu.ci_id |
在Oracle中 可以使用instr函数对某个字符串进行判断，判断其是否含有指定的字符。
浙公网安备 33010602011771号