为了账号安全,请及时绑定邮箱和手机立即绑定

Oracle数据库学习笔记 (四 —— select 从入门到放弃 【下】)

标签:
Oracle

目录:

一、聚合函数查询
	1.1 聚合函数
	1.2 分组查询
		一、非单组函数
		二、where 后面不能跟 分组函数
		分组的原则
	小测验
	1.3 子查询
		一、子查询语法
		二、子查询类型
	2.1 单行子查询
		exists 操作符
		小总结
二、测试 (多题预警)
三、select 查询从入门到放弃

一、聚合函数查询

1.1 聚合函数

聚合函数的定义和作用

  • 聚合函数又称为分组函数 或 组函数,能对集合中的一组数据进行计算,并返回单个计算结果

聚合函数烈性:

  • count(): 求总数
  • max():求最大值,一般对数值型数据进行操作,也可以对日期进行操作
  • min():求最小值
  • avg():求平均值
  • sum(): 求综合

看例题

  1. 求 emp 表的记录数
select count(*) from emp;
  1. 求员工最高工资
select max(sal) from emp;
  1. 求员工最低工资
select min(sal) from emp;
  1. 求雇员的平均工资
select avg(sal) from emp;
  1. 求每个月要发放给雇员的基本工资总数
select sum(sal) from emp;
  1. 求 20部门的总工资
    注意: 工资 = 基本工资 + 奖金,sum(sal + nvl(comm,0))
select sum(sal + nvl(comm,0)) from emp where deptno = 20

-- nvl(comm,0) 值为空的时候显示0 ,否则显示本身,它可以在任何函数中使用

1.2 分组查询

  • group by 的含义和作用

group by 意为 “根据(by)” 一定的规则进行分组(group)。其作用是通过一定的规则将一个数据集划分为若干个小的区域,然后针对若干小区域进行统计汇总

  • group by子句的功能和使用场景
  1. 用于对查询结果的分组统计
  2. 常与聚合函数联合使用。

语法:

select *| 列名
	from 表名
	where 条件表达式
	group by 分组条件 (having 过滤条件)
	order by 排序列 asc|desc

eg1:求出每个部门雇员的数量,先分组再统计

select deptno,count(empno) from emp group by deptno;

eg2:求出每个部门的平均工资

select deptno,avg(sal) from emp group by deptno;

注意:

  1. 分组查询只能由两部份构成,一是 group by 中出现的列
  2. 另外是分组函数,除此之外,其他内容不能放在 select 后

找错误:

一、非单组函数
select deptno,count(empno) from emp;

这个会报错:ORA-00937:不是单组分组函数

原因如下:

  1. 如果程序使用了分组函数,则有两种可以使用的情况
    1.1 程序中 存在 group by,并指定列分组条件,这样可以将分组条件一起查询
    1.2 如果不使用分组,则只能单独的使用组函数
  2. 使用分组函数的时候,不能出现分组函数和分组条件以外的字段。
二、where 后面不能跟 分组函数

统计 平均工资 2000 以上的部门

SELECT deptno, AVG(sal) FROM emp WHERE AVG(sal) > 2000
GROUP BY deptno;
  • 原因
    分组函数只能在分组中使用,不允许出现在 where 语句之中
  • 解决方案如下
    使用 having,对分组后的数据进行过滤
-- **使用 having 统计平均工资 2000 以上的部门**
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

-- 求出每个部门雇员的数量,先分组在统计
select deptno,count(empno) from emp group by deptno

-- 求每个部门的平均工资
select deptno,avg(sal) from emp group by deptno

-- 显示部门名称,各部门员工数,平均工资
select d.dname,count(e.empno),avg(e.sal)
from emp e, dept d
where e.deptno = d.deptno
group by e.deptno

分组的原则

  • 只要一列上存在重复的内容才能考虑分组
  • select 后面出现查询列,要么是分组条件,要么是分组函数

分组函数只能出现在 select 语句的列里,或者 having、order by子句中,如果在 select 语句中同时包含有 group by、having、order by 子句,那么必须是 group by、再having 再 order by

比如下面的句子:

select avg(sal), max(sal), deptno
from emp
group by deptno
having avg(sal) < 6000
order by avg(sal);

  • 多字段分组原则
    使用 group by 可以根据多个字段进行分组
    分组层次从左到右,即先按第一个字段分组,然后再第一个字段值相同的记录中,再根据第二个字段的值进行分组

eg:获取同一个部门下,同一个上司下的人数

select deptno, mgr, count(*) from emp group by deptno, mgr;

小测验

-- 1 查询入职最早的员工日期
select min(hiredate) from emp;

-- 2 求每个部门员工数量
select count(empno),deptno from emp group by deptno;

-- 3 统计各个部门的员工数及平均工资
select d.dname,count(e.empno),avg(e.sal) from emp e,dept d
where e.deptno = d.deptno
group by d.deptno = d.dname

-- 4 显示非销售人员工作名称雇员的月工资综合,并且要满足从事同一工作的雇员的月工资合计大于 5000,输出结果按月合计升序排列
select job,sum(sal) sal_num
from emp
where job <> 'SALESMAN'
group by job
having sum(sal)>5000
order by sal_num;

-- 5 统计每年入职的人数、鱼粉、人数
select to_char(hiredate,'yyyy') 年份,count(empno) 人数 from emp group by to_char(hiredate,'yyyy');

-- 6 统计每年入职的人数:年份,人数(仅返回入职不少于2人的年份的数据)
select to_char(hiredate,'yyyy') 年份,count(empno) 人数
from emp
group by to_char(hiredate,'yyyy')
having count(empno)>=2

1.3 子查询

给一个场景,查询工资比 SCOTT 高的人员信息

select * from emp where sal>(select sal from emp where ename = 'SCOTT')

一般要将子查询放在括号内,将子查询放在比较条件的右侧

一、子查询语法

SELECT *|列名 FROM 表名1 别名1,表名2  别名2,。。。
		(
			SELECT *|列名
			FROM 表名
			WHERE 条件表达式
			GROUP BY  分组条件
			ORDER BY 排序列 ASC|DESC
		 )别名,…
	WHERE 列 运算符
     (
			SELECT *|列名
			FROM 表名
			WHERE 条件表达式
			GROUP BY  分组条件
			ORDER BY 排序列 ASC|DESCGROUP BY  分组条件
	ORDER BY 排序列 ASC|DESC

二、子查询类型

  • 子查询可以分为三类
    • 单列子查询:返回结果是一列中的一个内容,出现几率最高
    • 单行子查询:返回多个列,有可能是一条完整的记录
    • 多行子查询:返回多条记录
2.1 单行子查询
-- 查询工资比7654高,同时与7788从事相同工作的全部雇员信息
SELECT *
   FROM emp
  WHERE sal > (SELECT sal FROM emp WHERE empno = 7654)
    AND job = (SELECT job FROM emp WHERE empno=7788)

-- 要求查询工资最低的雇员姓名,工作,工资
select ename,job,sal from emp where sal = (select min(sal) from emp);

-- 查询部门名称,部门员工数,部门平均工资,部门的最低收入雇员的姓名
-- 分析:程序关联的两张表 emp dept
-- 1.如果要求各个部门的员工数及平均工资,一定要分组统计,对emp按deptno 分组。
SELECT deptno, COUNT(*), AVG(sal) FROM emp GROUP BY deptno;
-- 2.如果想查部门名称,则要与dept表关联,用子查询。
SELECT d.dname, em.c, em.a
  from dept d,
       (SELECT deptno, COUNT(*) c, AVG(sal) a FROM emp GROUP BY deptno) em
 WHERE d.deptno = em. deptno;

-- 查询最低收入的雇员姓名
select min(sal) from emp group by deptno
-- 合并后
SELECT d.dname, em.c, em.a, e.ename
  from dept d,
       (SELECT deptno, COUNT(*) c, AVG(sal) a, MIN(sal) m
          FROM emp
         GROUP BY deptno) em,
       emp e
 WHERE d.deptno = em. deptno
   and em.m = e.sal;

-- in 操作符:指定一个查询范围的集合
-- 求出各部门最低收入的员工信息
select * from emp where sal in
(select min(sal) from emp group by deptno)

-- 查询工资UI 10 号部门中的任意一人相等即可
select * from emp where sal in (select sal from emp where deptno =10)

-- any 操作符:任意一个
-- = any:与 in 操作符的功能完全一样
-- >ANY: 大于任意一个,即比最小值都要大
-- <ANY: 小于任意一个,比最大的值小
-- 查询工资小于 10号部门中的任意一个人即可
select * from emp where sal<any(seect sal from emp where deptno = 10);

-- all 操作符:去阿奴
-- >all:大于全部,比最大值还大
-- <all,小于全部,比最小的值还要削
-- 查询工资小于全部 10 好部门的员工信息
select * from emp where sal < all (select sal from emp where deptno = 10);
-- 

##### 分页查询

```sql
-- 查询工资最高的前五名员工
-- 方法 1 
select rownum e.* from emp e where rownum <= 5 order by sal desc

-- 方法 2 使用子查询
select *
	from (select rownum rn,e.* from employees e order by salary desc)
	where rn <= 5;

-- 查询工资最高的6 - 12 条员工
-- 方法 1
SELECT *
   FROM (SELECT rownum row_top, tt.*
           FROM (SELECT e.* FROM emp e ORDER BY sal DESC) tt)
  WHERE row_top BETWEEN 6 AND 12;

-- 方法 2
SELECT *
  FROM (SELECT rownum row_top, tt.*
          FROM (SELECT e.* FROM emp e ORDER BY sal DESC) tt)
 WHERE row_top <= 12
   AND row_top >= 6;

-- 方法 3
SELECT *
  FROM (SELECT rownum rn, tt.*
          FROM (SELECT * FROM emp ORDER BY sal DESC) tt
         WHERE rownum <= 12)
 WHERE rn >= 6;
exists 操作符
  • EXISTS 操作符检查在子查询中是否存在满足条件的行
    如果在子查询中存在满足条件的行则条件返回TRUE
    如果在子查询中不存在满足条件的行则条件返回FALSE
-- 查询所有是部门经理的员工
-- exists 方法 (效率更高)
SELECT *
  FROM employees e
 WHERE EXISTS
 (SELECT 'X' FROM departments d WHERE e.employee_id = d.manager_id);

-- in 方法
SELECT *
FROM   employees e
WHERE  employee_id in (SELECT  manager_id from departments);

-- 查询不是部门经理的员工
SELECT *
  FROM employees e
 WHERE NOT EXISTS
 (SELECT 'X' FROM departments d WHERE e.employee_id = d.manager_id);


小总结

  1. 子查询通常用于SELECT语句的Form/WHERE子句中,且可以嵌套。
  2. 编写复杂的子查询的解决思路是:逐层分解查询。即从最内层的子查询开始分解,将嵌套的SQL语句拆分为一个个独立的SQL语句。
  3. 子查询的执行过程遵循“由里及外”原则,即先执行最内层的子查询语句,然后将执行结果与外层的语句进行合并,依次逐层向外扩展并最终形成完整的SQL语句。
  4. 一般情况下,联接查询可改为子查询实现;但子查询却不一定可改为联接查询实现。
  5. 子查询与联接查询执行效率的比较:当子查询执行结果的行数较大,而主查询执行结果的行数较小时,子查询执行效率较高;而情况相反时,则联接查询执行效率较高。

二、测试 (多题预警)

三、select 查询从入门到放弃

SELECT进阶语法

  • 联接查询:交叉联接、内连接、外连接(左外连接、右外连接)
  • 子查询:单列、单行、多行子查询及分页查询
  • 聚合函数:count,sum,avg,max,min
  • 分组查询使用关键字group by ,对分组之后的数据进行筛选使用having关键字。
  • 当一个select 语句中包含 where,group by,having ,order by 关键字时,它们的顺序依次为:
    where->group by->having->order by
点击查看更多内容
4人点赞

若觉得本文不错,就分享一下吧!

评论

作者其他优质文章

正在加载中
感谢您的支持,我会继续努力的~
扫码打赏,你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫,即可进行扫码打赏哦
今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与 放弃机会
意见反馈 帮助中心 APP下载
官方微信

举报

0/150
提交
取消