sql之子查询
1 子查询
1.1 子查询
sql中查询是可以嵌套的。一个查询可以作为另外一个查询的条件、表。
SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table);
理解子查询的关键在于把子查询当作一张表来看待。外层的语句可以把内嵌的子查询返回的结果当成一张表使用。子查询可以作为一个虚表被使用。
子查询要用括号括起来
将子查询放在比较运算符的右边(增强可读性)
子查询根据其返回结果可以分为单行子查询和多行子查询。
1.1.1 单行子查询
当子查询有单行时，可以取单行中的一个字段形成单个值用于条件比较。
-- 查询雇员其薪资在雇员平均薪资以上 -- [1] 查询员工的平均薪资 select avg(e.sal) "AVGSAL" from emp e --[2] 查询满足条件的雇员 select * from emp e where e.sal > (select avg(e.sal) "AVGSAL" from emp e)
1.1.2 多行子查询
-- 查在雇员中有哪些人是管理者 --【1】查询管理者 select distinct e.mgr from emp e where e.mgr is not null --【2】查询指定列表的信息 in select e.* from emp e where e.empno in (select distinct e.mgr from emp e where e.mgr is not null)
多行子查询返回的结果可以作为 表 使用，通常结合in、some/any、all、exists。
1.1.3 From后的子查询
子查询可以作为一张续表用于from后。
-- 每个部门平均薪水的等级 --【1】部门的平均薪资 select e.deptno,avg(e.sal) "AVGSAL" from emp e group by e.deptno --【2】求等级 select vt0.deptno,vt0.avgsal,sg.grade from (select e.deptno,avg(e.sal) "AVGSAL" from emp e group by e.deptno) VT0,salgrade sg where vt0.avgsal between sg.losal and sg.hisal
-- 99 join on select vt0.deptno,vt0.avgsal,sg.grade from (select e.deptno,avg(e.sal) "AVGSAL" from emp e group by e.deptno) VT0 join salgrade sg on vt0.avgsal between sg.losal and sg.hisal
1.2 TOP-N(A)
把select得到的数据集提取前n条数。
rownum:表示对查询的数据集记录的编号，从1开始。
-- 查询前10名雇员 select e.*,rownum from emp e where rownum <= 10
rownum和order-by
-- 查询按照薪资降序，前10名雇员 select e.*,rownum from emp e where rownum <= 10 order by e.sal desc
总结
[1] order by 一定在整个结果集出现后才执行。
[2] rownum 在结果集出现后才有编号。
。。。-> select -> rownum -> order by
-- 查询按照薪资降序，前10名雇员 select vt0.*,rownum from (select e.* from emp e order by e.sal desc) VT0 where rownum <= 10
1.3 分页(A)
-- 求查询6-10号的雇员 select vt0.* from (select e.*,rownum "RN" from emp e where rownum <= 10) VT0 where vt0.rn >= 6
求page=n,pagesize=size的数据
=>[(n-1)*size+1,n*size]
select vt0.* from (select t.*, rownum “RN” from table t where rownum <= n*size) VT0 where vt0.rn >= (n-1)*size+1
1.4 行转列 (B)
4.1得到类似下面的结果
姓名 语文 数学 英语
张三 78 88 98
王五 89 56 89
select ts.name, sum(decode(ts.subject,'语文',ts.score)) "语文", sum(decode(ts.subject,'数学',ts.score)) "数学", sum(decode(ts.subject,'英语',ts.score)) "英语" from test_score ts group by ts.name
1.5 视图(B)
视图(view)，称为虚表，在数据库中不存在实体。
视图本质上是对物理表(基表)的一种数据保护。让开发者或者用户只能看到基本中的部分数据。
1.5.1 创建视图
创建视图的语法
create or replace view 视图名 as sub-query(子查询)
案例：
需求：查询雇员的基本信息，但是不要显示薪金和津贴
create or replace view v$empinfo as select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.deptno from emp e;
