ORACLE Unit04 SQL(高级查询)
来源:互联网 发布:知乎 方向盘套 编辑:程序博客网 时间:2024/04/30 08:02
查看SMITH的上司在那个城市工作?
SELECT e.ename,m.ename,d.locFROM emp e,emp m,dept dWHERE e.mgr=m.empnoAND m.deptno=d.deptnoAND e.ename='SMITH'SELECT e.ename,m.ename,d.locFROM emp e JOIN emp mON e.mgr=m.empnoJOIN dept dON m.deptno=d.deptnoWHERE e.ename='SMITH'
子查询
子查询是一条查询语句,它是嵌套在其他SQL语句之中的,目的是为外层的SQL语句提供数据。
DDL,DML,DQL都可以使用子查询。
查看谁的工资高于CLARK?
SELECT ename,salFROM empWHERE sal>(SELECT sal FROM emp WHERE ename='CLARK')
查看与SMITH同部门的员工?
SELECT ename,deptnoFROM empWHERE deptno=(SELECT deptno FROM emp WHERE ename='SMITH')
查看谁的工资高于公司平均工资?
SELECT ename,salFROM empWHERE sal>(SELECT AVG(sal) FROM emp)
DDL中使用子查询,可以根据子查询的结果集当作表快速创建出来。
创建一张表,包含员工的工号,名字,工资,职位,部门号,部门名以及所在地
该表中的数据来自emp,dept
CREATE TABLE employeeASSELECT e.empno,e.ename,e.sal, e.job,e.deptno,d.dname, d.locFROM emp e,dept dWHERE e.deptno=d.deptno(+)DESC employeeSELECT * FROM employee
DML中使用子查询
将SMITH所在部门的员工工资提高10%
UPDATE empSET sal=sal*1.1WHERE deptno=(SELECT deptno FROM emp WHERE ename='SMITH')SELECT * FROM emp
查看与MANAGER同部门的其他职位员工
SELECT ename,job,deptnoFROM empWHERE deptno IN(SELECT deptno FROM emp WHERE job='MANAGER')AND job<>'MANAGER'
查看比20和30号部门员工工资都高的员工信息?
SELECT ename,salFROM empWHERE sal>ALL(SELECT sal FROM emp WHERE deptno IN(20,30))
EXISTS关键字
EXISTS用在过滤条件中,其后跟一个子查询,只要该子查询可以查询出至少一条记录,那么EXISTS就认为满足条件。
SELECT deptno, dname FROM dept dWHERE EXISTS (SELECT * FROM emp e WHERE d.deptno = e.deptno)
HAVING中使用子查询
查看部门的最低薪水,前提是该部门最低薪水要高于30号部门的最低薪水。
SELECT MIN(sal),deptnoFROM empGROUP BY deptnoHAVING MIN(sal)>(SELECT MIN(sal) FROM emp WHERE deptno=30)
FROM子句中使用子查询
通常一个多列子查询是当作一张表看待出现在FROM子句中的。
查看谁的工资高于其所在部门的平均工资?
SELECT e.ename,e.sal,e.deptnoFROM emp e,(SELECT AVG(sal) avg_sal, deptno FROM emp GROUP BY deptno) tWHERE e.deptno=t.deptnoAND e.sal>t.avg_salSELECT e.ename, e.sal, (SELECT d.dname FROM dept d WHERE d.deptno = e.deptno) dnameFROM emp e
分页查询
当查询的数据量非常大的时候,会导致系统资源消耗大,传输速度慢,用户实际也无需这么多数据量。为此,通常的操作是”分段查询”即分页。
由于标准SQL中没有对分页语法进行规定,所以分页查询是”方言”,不同数据库分页查询的语句不同。
ROWNUM:伪列
ROWNUM不存在于任何表,但是任何表都可以查询该字段,该字段的值是查询结果集中每条记录的行号。
ROWNUM的值是动态生成的,在查询过程中进行,每当可以从表中查询出一条记录,ROWNUM字段的值就是该记录的行号,从1开始,逐次递增。
在使用ROWNUM对结果集编行号的过程中不要使用ROWNUM做>1以上数字的判断,否则将得不到任何结果。
取6-10的员工?
SELECT *FROM (SELECT ROWNUM rn,ename,sal, job,deptno FROM emp )WHERE rn BETWEEN 6 AND 10
查看公司中工资排名的6-10
SELECT *FROM(SELECT ROWNUM rn,t.* FROM(SELECT ename,sal,deptno FROM emp ORDER BY sal DESC) t)WHERE rn BETWEEN 6 AND 10SELECT *FROM(SELECT ROWNUM rn,t.* FROM(SELECT ename,sal,deptno FROM emp ORDER BY sal DESC) t WHERE ROWNUM<=10)WHERE rn>=6
page:页码
pageSize:每页的条目数
start:(page-1)*pageSize+1end:pageSize*page
DECODE函数
SELECT ename, job, sal, DECODE(job, 'MANAGER',sal*1.2, 'ANALYST',sal*1.1, 'SALESMAN',sal*1.05, sal) bonusFROM emp
将职位是MANAGER与ANALYST看作一组,其他职位看作另一组,分别统计人数。
SELECT COUNT(*),jobFROM empGROUP BY jobSELECT COUNT(*), DECODE(job, 'MANAGER','VIP', 'ANALYST','VIP', 'OTHER')FROM empGROUP BY DECODE(job, 'MANAGER','VIP', 'ANALYST','VIP', 'OTHER')
排序函数
排序函数可以按照给定字段对结果集分组然后在组内排序,并生成组内编号。
ROW_NUMBER生成组内连续且唯一的数字
查看每个部门的工资排名
SELECT ename,sal,deptno, ROW_NUMBER() OVER( PARTITION BY deptno ORDER BY sal DESC ) rankFROM emp
RANK函数:生成组内不连续也不唯一的数字
SELECT ename,sal,deptno, RANK() OVER( PARTITION BY deptno ORDER BY sal DESC ) rankFROM emp
DENSE_RANK:生成组内连续但不唯一的数字
SELECT ename,sal,deptno, DENSE_RANK() OVER( PARTITION BY deptno ORDER BY sal DESC ) rankFROM empSELECT year_id,month_id,day_id,sales_valueFROM sales_tabORDER BY year_id,month_id,day_id
每天营业额?
SELECT year_id,month_id,day_id,SUM(sales_value)FROM sales_tabGROUP BY year_id,month_id,day_idORDER BY year_id,month_id,day_id
每月营业额?
SELECT year_id,month_id,SUM(sales_value)FROM sales_tabGROUP BY year_id,month_idORDER BY year_id,month_id
每年营业额?
SELECT year_id,SUM(sales_value)FROM sales_tabGROUP BY year_idORDER BY year_id
总共营业额?
SELECT SUM(sales_value)FROM sales_tab
查看每天,每月,每年以及所有营业额?
SELECT year_id,month_id,day_id,SUM(sales_value)FROM sales_tabGROUP BY year_id,month_id,day_idUNION ALLSELECT year_id,month_id,NULL,SUM(sales_value)FROM sales_tabGROUP BY year_id,month_idUNION ALLSELECT year_id,NULL,NULL,SUM(sales_value)FROM sales_tabGROUP BY year_idUNION ALLSELECT NULL,NULL,NULL,SUM(sales_value)FROM sales_tab
高级分组函数
GROUP BY ROLLUP(a,b,c)
等同于:
GROUP BY a,b,c
UNION ALL
GROUP BY a,b
UNION ALL
GROUP BY a
UNION ALL
全表
SELECT year_id,month_id,day_id, SUM(sales_value)FROM sales_tabGROUP BY ROLLUP(year_id,month_id,day_id)
CUBE()函数
每种参数组合都进行一次分组,并在一个结果集
显示
分组次数为2的参数个数次方
GROUP BY CUBE(a,b,c)
abc
ab
ac
bc
a
b
c
全表
SELECT year_id,month_id,day_id, SUM(sales_value)FROM sales_tabGROUP BY CUBE(year_id,month_id,day_id)ORDER BY year_id,month_id,day_id
GROUPING SETS()
可以指定分组方式,然后将这些分组统计的结果并在一个结果集里显示
查看每天与每月的营业额?
SELECT year_id,month_id,day_id, SUM(sales_value)FROM sales_tabGROUP BY GROUPING SETS( (year_id,month_id,day_id), (year_id,month_id) )ORDER BY year_id,month_id,day_id
- ORACLE Unit04 SQL(高级查询)
- oracle 高级SQL查询
- oracle 高级SQL查询
- oracle 高级SQL查询笔记12-01
- ORACLE SQL 高级编程--查询转换
- Oracle数据库之SQL(高级查询)详解
- oracle 高级查询(一)
- oracle高级查询(二)
- oracle--高级查询(一)
- oracle---高级查询(二)
- Oracle高级查询(一)
- SQL 高级查询 (转载)
- Oracle与Mysql的高级查询与难点sql
- 笔记:Oracle SQL 高级查询简介 (2) 分析函数
- Oracle 高级查询sql DECODE函数 分组函数
- SQl Server与Oracle的高级模糊查询
- Oracle高级技巧,高级查询
- SQL SERVER 2005 高级查询(子查询查询)
- 9. 同构词分组
- windows网络编程(七)——重叠I/O模型中的问题——WSARecv函数出现WSAEFAULT错误应该怎么解决?
- Thinking in java-18 this 关键字
- 49. NAT 服务器
- 10. 消息加密
- ORACLE Unit04 SQL(高级查询)
- 11. 千字文
- 剑指offer 面试题56 链表中环的入口结点
- 人工智能资料
- SaltStack 的基本概念与工作原理 架构设计
- 关于tornado-web开发密码加密的使用
- 12. 正则表达式
- Last non-zero Digit in N!
- Computer Networking A Top-Down Approach 总结