oracle高级查询

幕课oracle学习笔记

--！！！scott用户

--一.分组查询

--1.常用的分组函数：AVG(平均数)，SUM，MIN，MAX，COUNT，WM_CONCAT(行转列)

select avg(sal),sum(sal) from emp;--工资的平均值，工资的总和

select max(sal),min(sal) from emp;--工资的最大值，最小值

select count(*) from emp;--员工的总数

select count(distinct deptno) from emp;--根据部门号取重查询

select deptno 部门号,wm_concat(ename) 部门中的员工 from emp group by deptno;--行转列,查出各个部门的人员名字

--分组函数会自动过滤空值，使用NVL函数使分组函数无法忽略空值

select avg(comm) from emp;--平均奖金为550

select avg(nvl (comm,0)) from emp;--平均奖金为157.14

--2.分组数据:

--（1）GROUP BY子句:select a,b,c,组函数（x） from  table_name  group by a,b,c;在select列表中所有未包含在组函数中的列都应该包含在group by子句中

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

--多个列分组

select deptno,job,sum(sal) from emp group by deptno,job;--求出每个部门各个职位的工资综合

--（2）HAVING子句：过滤分组数据

select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;--求出平局工资大于2000的部门

--where子句中不能使用分组函数，可以在having几组中使用;

--从sql优化的角度，没有组函数.分组函数时where和having通用，尽量使用where（效率高）

select deptno,avg(sal) from emp group by deptno having deptno=10;--10号部门的平均工资

select deptno,avg(sal) from emp where deptno=10 group by deptno ;--10号部门的平均工资

--（3）order by子句：默认升序,降序+desc

select deptno,avg(sal) 平均工资 from emp group by deptno order by 平均工资;--各部门平均工资并升序排列

select deptno,avg(sal) 平均工资 from emp group by deptno order by 2;--根据select语句的第二列排序

select deptno,avg(sal) 平均工资 from emp group by deptno order by 2 desc;

--（4）分组函数的嵌套

select max(avg(sal)) from emp group by deptno;--平均工资最高的部门的平均工资

--（5）group by语句的增强:rollup();主要应用于报表

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);

--二.多表查询

--笛卡尔积：多张表的列数相加，行数相乘所得的新表。;为了避免笛卡尔积，可以在where加入有效的连接条件

--四种连接（等值连接，不等值连接，外连接，自连接）；

(1)等值连接

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

(2)不等值连接

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

或select e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where e.sal>=s.losal and  e.sal<=s.hisal;

(3)外连接：把对于连接条件不成立的记录，仍然包含在结果中;

--下列语句查询丢失了deptno=40的部门,因为40部门人数为0

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 d.deptno 部门号,d.dname 部门名称,count(e.empno) 部门人数 from emp e,dept d where e.deptno(+)=d.deptno group by d.deptno,d.dname;

--右外连接：连接条件不成立时，等号右边的仍然被包含；在连接条件右边加 ： (+)

(4)自连接(不适合操作大表):通过别名，将同一张表视为多张表

select e.ename,b.ename from emp e,emp b where e.mgr=b.empno;--获得员工的名字和他上级的名字

--层次查询(解决自连接操作大表的笛卡尔积问题):某种情况下可以替代自连接，本质上是一个单表查询;结果没有自连接直观

select level,empno,ename,sal,mgr from emp connect by prior empno=mgr start with mgr is null order by 1;

ps:connect by 上一层的员工号=老板号；start with 老板号为空，即子节点开始；level：伪列，代表树形结构等级；order by 1 表示按level第一层开始排序

--三.子查询(select语句的嵌套)

--(1)可以使用子查询的位置：where，select，having，from

select ename from  emp where sal>(select sal from emp where ename='SCOTT');--工资比scott高的员工

select empno,ename,sal,(select job from emp where empno=7839) from  emp;--所有员工的职位都是7839的职位

select deptno,avg(sal) from emp group by deptno having avg(sal)>(select max(sal) from emp where deptno=30);--部门平均工资大于30部门工资最大值的部门

select * from (select empno,ename,sal from emp);

--(2)不可以使用子查询的位置：group by

***错误提示：ORA-22818:这里不允许出现子查询表达式**

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

--(3)*from 后面的子查询

*******：在已知条件的基础上得到更多的已知条件

select * from (select empno,ename,sal from emp);

select * from (select empno,ename,sal 月薪,sal*12 年薪 from emp);

--(4)主查询，子查询可以不是同一张表,只要子查询的结果主查询可以使用就可以！

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';

--理论上尽量使用多表查询，只有一个from，只访问一次数据库.

--(5)一般不在子查询中使用排序，但在TOP-N分析问题中，必须对子查询排序

--TOP-N问题:例如薪水前三的员工

--rownum  行号，oracle提供的伪列；永远按照默认的顺序生成（无任何条件的查询表时的顺序），只能使用<,<=;不能使用>,>=(分页查询时使用此特性)

--解决上述伪列默认顺序导致查询永远是前三个员工，而非工资前三的员工的问题

--不使用原表，而使用倒序排列后的新表作为查询表

select rownum,empno,ename,sal from (select  * from emp order by sal desc)  where rownum<=3;

--(6)一般先执行子查询，再执行主查询；但相关子查询例外

--查询员工标中薪水大于本部门平均水平的员工（相关子查询：主查询的中的值作为参数传给子查询,主查询必须起别名）

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 deptno=e.deptno);

--(7)单行子查询(返回单行记录)只能使用单行操作符(=,>,>=,<,<=,<>)

--职位与7566员工一样，薪水大于7782员工的薪水

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);

--查询最低工资大于20号部门最低工资的部门号和部门的最低工资

select  deptno,min(sal) from emp group by deptno having min(sal)>(select min(sal) from emp where deptno=20);

--多行子查询(返回多行记录)只能使用多行操作符(in:等于列表中的任何一个,any：和子查询返回的任意一个值比较,all：和子查询返回的所有值比较)

--in：查询部门名称是sales和accounting的员工信息

select * from emp where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING');--子查询

select e.* from emp e ,dept d where e.deptno=d.deptno and (d.dname='SALES' or d.dname='ACCOUNTING');--多表联查(加括号，必须先执行or，再执行and)

--any：查询工资比30号部门任意一个员工高的员工信息

select * from emp where sal>any(select sal from emp where deptno=30);

select * from emp where sal>(select min(sal) from emp where deptno=30);

--all:查询工资比30部门所有员工高的员工信息

select * from emp where sal>all(select sal from emp where deptno=30);

select * from emp where sal>(select max(sal) from emp where deptno=30);

--(8)子查询中的null值问题（主要是多行子查询中）

--查询不是老板的员工

select * from emp where empno not in(select mgr from emp where mgr is not null);--(子查询中不能有空值，否则就查不到任何数据)

ps：  a not in (10,20,null)就等同于 a！=10 and a!=20 and a!=null;因为a!=null永远为假，所有条件就永远为假，就查不出任何结果

--四.示例

(1)分页查询显示员工信息：显示员工号，姓名，月薪(每页显示四条；显示第二页的员工；按照月薪降序排列)

--rownum伪列，始终从1开始，无法使用大于(>)和大于等于(>=)

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;

(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 deptno=e.deptno);--相关子查询--相比多表查询更节省cpu资源

--------------------------------------------

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;--多表联查

(3)按部门统计员工，分别查出总人数，及1980，1981，1982，1987年入职的员工

select count(*) total,--使用函数的方式

sum(decode(to_char(hiredate,'YYYY'),'1980',1,0)) "1980",--to_char转换出入职日期中的年

sum(decode(to_char(hiredate,'YYYY'),'1981',1,0)) "1981",--并与四位年份比较

sum(decode(to_char(hiredate,'YYYY'),'1982',1,0)) "1982",--相同则返回1，不同返回0

sum(decode(to_char(hiredate,'YYYY'),'1987',1,0)) "1987"--sum算出1和0的总和，即为该年份入职的人数

from emp;

-------------------------------------------------

select --使用子查询+伪表dual的方式

(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;

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 203,324评论 5赞 476
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,303评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 150,192评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,555评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,569评论 5赞 365
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,566评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,927评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,583评论 0赞 257
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,827评论 1赞 297
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,590评论 2赞 320
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,669评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,365评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,941评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,928评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,159评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,880评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,399评论 2赞 342

推荐阅读更多精彩内容

  • 常见的99中sql语句
    1. select * from emp; 2. select empno, ename, job from em...
    海纳百川_4d26阅读 1,878评论 0赞 4
  • Oracle之查询详解
    查询是数据的一个重要操作。用户发送查询请求，经编译软件变异成二进制文件供服务器查询，后返回查询结果集给用户，查询会...
    产品小正阅读 1,358评论 0赞 2
  • Oracle数据库学习一
    1.简介 数据存储有哪些方式？电子表格，纸质文件，数据库。 那么究竟什么是关系型数据库？ 目前对数据库的分类主要是...
    乔震阅读 1,699评论 0赞 2
  • Oracle数据库学习二
    5.多表查询 多表查询 目的：从多张表获取数据 前提：进行连接的多张表中有共同的列 等连接 通过两个表具有相同意义...
    乔震阅读 1,194评论 0赞 0
  • 生活不可能没有痛苦
    我发现有很多人，都太脆弱，甚至有点矫情。他们没有受过什么巨大的痛苦，不知道真正的痛苦的滋味，往往还抱怨自己苦，还露...
    人生如梦z阅读 502评论 0赞 1