查看SMITH的上司在那个城市工作?
SELECT e.ename,m.ename,d.loc
FROM emp e,emp m,dept d
WHERE e.mgr=m.empno
AND m.deptno=d.deptno
AND e.ename='SMITH'
SELECT e.ename,m.ename,d.loc
FROM emp e JOIN emp m
ON e.mgr=m.empno
JOIN dept d
ON m.deptno=d.deptno
WHERE e.ename='SMITH'
子查询
子查询是一条查询语句，它是嵌套在
其他SQL语句之中的，目的是为外层的
SQL语句提供数据。
DDL,DML,DQL都可以使用子查询。
查看谁的工资高于CLARK？
SELECT ename,sal
FROM emp
WHERE sal>(SELECT sal FROM emp
WHERE ename='CLARK')
查看与SMITH同部门的员工?
SELECT ename,deptno
FROM emp
WHERE deptno=(SELECT deptno
FROM emp
WHERE ename='SMITH')
查看谁的工资高于公司平均工资?
SELECT ename,sal
FROM emp
WHERE sal>(SELECT AVG(sal) FROM emp)
DDL中使用子查询，可以根据子查询的结果集
当作表快速创建出来。
创建一张表，包含员工的工号，名字，工资
职位，部门号，部门名以及所在地
该表中的数据来自emp,dept
CREATE TABLE employee
AS
SELECT e.empno,e.ename,e.sal,
e.job,e.deptno,d.dname,
d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno(+)
DESC employee
SELECT * FROM employee
DML中使用子查询
将SMITH所在部门的员工工资提高10%
UPDATE emp
SET sal=sal*1.1
WHERE deptno=(SELECT deptno
FROM emp
WHERE ename='SMITH')
SELECT * FROM emp
查看与MANAGER同部门的其他职位员工
SELECT ename,job,deptno
FROM emp
WHERE deptno IN(SELECT deptno FROM emp
WHERE job='MANAGER')
AND job<>'MANAGER'
查看比20和30号部门员工工资都高的员工信息?
SELECT ename,sal
FROM emp
WHERE sal>ALL(SELECT sal FROM emp
WHERE deptno IN(20,30))
EXISTS关键字
EXISTS用在过滤条件中，其后跟一个子查询，
只要该子查询可以查询出至少一条记录，那么
EXISTS就认为满足条件。
SELECT deptno, dname FROM dept d
WHERE EXISTS
(SELECT * FROM emp e
WHERE d.deptno = e.deptno)
HAVING中使用子查询
查看部门的最低薪水，前提是该部门最低薪水
要高于30号部门的最低薪水。
SELECT MIN(sal),deptno
FROM emp
GROUP BY deptno
HAVING MIN(sal)>(SELECT MIN(sal)
FROM emp
WHERE deptno=30)
FROM子句中使用子查询
通常一个多列子查询是当作一张表看待出现在
FROM子句中的。
查看谁的工资高于其所在部门的平均工资?
SELECT e.ename,e.sal,e.deptno
FROM emp e,(SELECT AVG(sal) avg_sal,
deptno
FROM emp
GROUP BY deptno) t
WHERE e.deptno=t.deptno
AND e.sal>t.avg_sal
SELECT
e.ename, e.sal,
(SELECT d.dname FROM dept d
WHERE d.deptno = e.deptno) dname
FROM 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 10
SELECT *
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+1
end:pageSize*page
DECODE函数
SELECT
ename, job, sal,
DECODE(job,
'MANAGER',sal*1.2,
'ANALYST',sal*1.1,
'SALESMAN',sal*1.05,
sal) bonus
FROM emp
将职位是MANAGER与ANALYST看作一组，
其他职位看作另一组，分别统计人数。
SELECT COUNT(*),job
FROM emp
GROUP BY job
SELECT COUNT(*),
DECODE(job,
'MANAGER','VIP',
'ANALYST','VIP',
'OTHER')
FROM emp
GROUP BY DECODE(job,
'MANAGER','VIP',
'ANALYST','VIP',
'OTHER')
排序函数
排序函数可以按照给定字段对结果集分组
然后在组内排序，并生成组内编号。
ROW_NUMBER生成组内连续且唯一的数字
查看每个部门的工资排名
SELECT
ename,sal,deptno,
ROW_NUMBER() OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM emp
RANK函数:生成组内不连续也不唯一的数字
SELECT ename,sal,deptno,
RANK() OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM emp
DENSE_RANK:生成组内连续但不唯一的数字
SELECT ename,sal,deptno,
DENSE_RANK() OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM emp
SELECT year_id,month_id,day_id,sales_value
FROM sales_tab
ORDER BY year_id,month_id,day_id
每天营业额?
SELECT year_id,month_id,day_id,SUM(sales_value)
FROM sales_tab
GROUP BY year_id,month_id,day_id
ORDER BY year_id,month_id,day_id
每月营业额?
SELECT year_id,month_id,SUM(sales_value)
FROM sales_tab
GROUP BY year_id,month_id
ORDER BY year_id,month_id
每年营业额?
SELECT year_id,SUM(sales_value)
FROM sales_tab
GROUP BY year_id
ORDER BY year_id
总共营业额?
SELECT SUM(sales_value)
FROM sales_tab
查看每天，每月，每年以及所有营业额?
SELECT year_id,month_id,day_id,SUM(sales_value)
FROM sales_tab
GROUP BY year_id,month_id,day_id
UNION ALL
SELECT year_id,month_id,NULL,SUM(sales_value)
FROM sales_tab
GROUP BY year_id,month_id
UNION ALL
SELECT year_id,NULL,NULL,SUM(sales_value)
FROM sales_tab
GROUP BY year_id
UNION ALL
SELECT 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_tab
GROUP 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_tab
GROUP 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_tab
GROUP BY
GROUPING SETS(
(year_id,month_id,day_id),
(year_id,month_id)
)
ORDER BY year_id,month_id,day_id