当前位置:Gxlcms > 数据库问题 > Oracle学习笔记(七)

Oracle学习笔记(七)

时间:2021-07-01 10:21:17 帮助过:18人阅读

九、高级查询(分组,子查询)
查询升级版:
需要用到三张表
员工表:
desc emp
EMPNO 员工号
ENAME 员工姓名
JOB 员工职位
MGR 老板员工号
HIREDATE 员工入职日期
SAL 员工月薪
COMM 员工奖金
DEPTNO 员工所在部门部门号

查看当前用户:
show users;
select * from emp;
部门表:
desc dept
deptno 部门号
dname 部门名称
LoC 部门地点
select * from dept;
工资级别表:
desc salgrade
GRADE 级别
LOSAL 级别最低薪水
HISAL 级别最高薪水
select * from salgrade;

十、分组查询
1、分组函数的概念
分组函数作用于一组数据,并对一组数据返回一个值

2、分组函数的使用
常用的分组函数:AVG、SUM、MIN、MAX、COUNT、WM_CONCAT(行转列)
(1)求出员工的平均工资和工资总额
select avg(SAL) as 平均工资,sum(SAL) as 工资总额 from emp;
(2)求出员工工资的最大值和最小值
select max(SAL) as 工资的最大值,min(SAL) as 工资的最小值 from emp;
(3)求出员工的总人数
select COUNT(*) as 总人数 from emp;
(4)查询部门数(去除重复)
select COUNT(distinct deptno) as 部门数 from emp;
(5)行转列
设置行宽
set linesize 200
col 部门中员工的姓名 for a60
select deptno as 部门号,wm_concat(ename) as 部门中员工的姓名 from emp group by deptno;
(6)分组函数与空值
a、统计员工的平均工资(三种方法统计薪水)
select sum(sal)/count(*) 一,sum(sal)/count(sal) 二, avg(sal) 三 from emp;
b、统计员工的平均奖金(三个值不相同)
select sum(comm)/count(*) 一,sum(comm)/count(comm) 二, avg(comm) 三 from emp;

注意:
分组函数会自动忽略空值
(7)在分组函数中使用NVl函数
注意:NVL函数使分组函数无法忽略空值
改写b
所有员工都统计
select sum(comm)/count(*) 一,sum(comm)/count(nvl(comm,0)) 二, avg(nvl(comm,0)) 三 from emp;
非空员工的统计
select sum(comm)/count(comm) 二, avg(comm) 三 from emp;

3、使用GROUP BY 子句数据分组
(1)求出employee表中各部门的平均工资,并按照部门号分组
select deptno 部门号,avg(sal) 平均工资 from emp group by deptno;
注意:
在select列表中所有未包含在组函数中的列都应该包含在group by子句中
就是select 列名一,列名二 from emp group by 列名一;
因为列名一不是列名二的内容,所有要加在group by 后面
(2)求每个部门的平均工资,要求显示:部门的平均工资
select avg(sal) 平均工资 from emp group by deptno;

注意:
包含在group by 子句中的列不必包含在select列表中
(3)使用多个列分组
查询员工的薪水总额
select deptno 员工号,job 工作,sum(sal) 总薪水 from emp group by deptno,job order by deptno;
修改语句:
edit
编辑后保存
执行语句:
/
(4)非法使用组函数
所包含与select列表中,而未包含于组函数中的列都必须包含于group by 子句中
经典错误
select deptno,count(ename) from emp;
正确形式:
select deptno,count(ename) from emp group by deptno;

4、使用HAVING子句过滤分组结果集
(1)求平均工资大于2000的部门
select deptno 部门号,avg(sal) 平均工资 from emp group by deptno having avg(sal)>2000;

where与having的区别
a、不能在where子句中使用组函数
b、可以在having子句中使用组函数

(2)查询10号部门的平均工资(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;

注意:
从SQL优化的角度看,尽量使用where,where使得分组记录数大大降低,从而提高效率

5、在分组查询中使用order by子句
(1)求每个部门的平均工资,要求显示:部门号,部门的平均工资,并且按照工资升序排列(可以按照:列、别名、表达式、序号进行排序)
select deptno 部门号,avg(sal) 平均工资 from emp group by deptno order by avg(sal);
写法二:
select deptno 部门号,avg(sal) 平均工资 from emp group by deptno order by 2;
追加降序命令:
a(后接两个或以上空格)
a desc
/
select deptno 部门号,avg(sal) 平均工资 from emp group by deptno order by 2 desc;

(2)分组函数的嵌套
求部门平均工资的最大值
思路:(a)通过avg函数求出每个部门的平均工资
(b)嵌套max函数求出部门平均工资的最大值
select max(avg(sal)) 平均工资 from emp group by deptno;

6、group by语句的增强
(1)按部门、不同的职位、统计工资总额,
按部门,统计工资总额
统计工资总额
select deptno 员工号,job 工作,sum(sal) 总薪水 from emp group by deptno,job order by deptno;
select deptno 员工号 sum(sal) 总薪水 from emp group by deptno
select sum(sal) 总薪水 from emp
上三句相加得下边一句话
break on deptno skip 2
set pagesize 30
select deptno,job,sum(sal) from emp group by rollup(deptno,job);(不可设置别名)

7、SQL*PLus的报表功能
报表包括:标题/页码/别名等
ttitle col 15 ‘我的报表‘ col 35 sql.pno 空15个列显示我的报表这个标题,空35个列
col deptno heading 部门号 标题设置为不猛号
col job heading 职位
col sum(sal) heading 工资总额
break on deptno skip 1
select deptno,job,sum(sal) from emp group by rollup(deptno,job);
保存一个目录中,get 文件路径
@路径执行

8、多表查询
(1)什么是多表查询?
从多个表中获取数据,就是多表查询
(2)迪卡尔集
例子演示:
create table dept_disk(
deptno number(5,0) primary key,
dname varchar2(20)
);
insert into dept_disk values(1,‘开发部‘);
insert into dept_disk values(2,‘行政部‘);
create table emp_disk(
emptno number(5,0),
ename varchar2(20),
sal number(5,0),
deptno number(5,0)
);
insert into emp_disk values(1,‘开发部‘);
insert into emp_disk values(2,‘行政部‘);
两包张表按照如下规则合并成一张表,叫做迪卡尔集表
(1)迪卡尔集表的列数,等于每张表的列数相加,迪卡尔集表的行数,等于每张表的行数相乘.得到笛卡尔全集
(2)多表查询的核心是连接条件.连接条件的个数,表的个数减一,两张表至少有一个连接条件.
注意:
(a)为了避免迪卡尔集,可以在where加入有效的连接条件.
(b)在实际运行环境下,应避免使用笛卡尔全集.
连接类型:
(3)等值连接
连接条件是个等号,就是等值连接
例子:查询员工信息,要求显示:员工号,姓名,月薪,部门名称
select e.empno as 员工号,e.ename as 姓名,e.sal as 月薪,d.dname as 部门名称 from emp e, dept d where e.deptno=d.deptno;
(4)不等值连接
例子:查询员工信息,要求显示:员工号,姓名,月薪,薪水等级(用between...and需要小值在前,大值在后)
select e.empno as 员工号,e.ename as 姓名,e.sal as 月薪, s.grade as 薪水等级 from emp e,salgrade s where e.sal between s.losal and s.hisal;
(5)外连接
例子:按部门统计员工人数,要求显示:部门号,部门名称,人数
select d.deptno as 部门号,d.dname as 部门名称,count(e.empno) as 人数 from dept d, emp e where e.deptno(+)=d.deptno group by d.deptno,d.dname;

注意:
(a)核心:通过外连接,把对于连接条件不成立的记录,仍然包含在最后的结果中
(b)左外连接:当条件不成立的时候,等号左边的表仍然被包含(等号右边加(+))
(c)右外连接:当连接条件不成立的时候,等号右边的表仍然被包含(等号左边加(+))

(6)自连接
例子:查询员工姓名和员工的老板姓名
连接条件员工的老板号等于老板的员工号
select e.ename as 员工姓名,b.ename as 老板姓名 from emp e,emp b where e.mgr=b.empno;

注意:
(a)核心:通过别名,将同一张表视为多张表

自连接存在的问题:
(1)自连接不做任何操作,得到的就是笛卡尔乘积
select count(*) from emp e,emp b;
(a)问题一:自连接不是和操作大表(因为自乘会有很大的数据)
解决方法:层次查询
说明:某些情况下,可以替代自连接,本质上是一个单表查询
(7)层次查询
原理:关系成成树状图
select level as 层次,empno as 员工号,ename as 员工姓名,sal as 月薪,mgr as 老板号 from emp connect by prior empno=mgr start with mgr is null order by 1;
优点:单表查询
缺点:没有自连接直观

Oracle学习笔记(七)

标签:min   报表   arch   信息   count   单表查询   优化   不能   cat   

人气教程排行