为了账号安全，请及时绑定邮箱和手机立即绑定
首页 手记 Oracle数据库学习笔记 （四...

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

标签：
Oracle

select * from emmm…【下】

目录：

一、聚合函数查询
	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|DESC
	）
	GROUP 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. 子查询与联接查询执行效率的比较：当子查询执行结果的行数较大，而主查询执行结果的行数较小时，子查询执行效率较高；而情况相反时，则联接查询执行效率较高。

二、测试 （多题预警）

Oracle 数据库查询专题 (select * from emmmm 80 T)

三、select 查询从入门到放弃

SELECT进阶语法

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

本文原创发布于慕课网 ，转载请注明出处，谢谢合作

举报
4人点赞

若觉得本文不错，就分享一下吧！

评论

作者其他优质文章

正在加载中

Gorit

学生
手记
篇
粉丝
55
获赞与收藏
243

关注作者，订阅最新文章

阅读免费教程

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

100积分直接送

付费专栏免费学

大额优惠券免费领

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

举报

0/150
提交
取消