SQL之高级查询

如果遇到没有创建的表影响运行使用

->点我进入表的创建与数据插入
或者评价反馈

子查询

子查询是一条SELECT语句，但它是嵌套其他SQL语句中的
为的是给该SQL提供数据以支持其执行操作

查看谁的工资高于CLARK？SELECT ename,salFROM emp_swmWHERE sal>(    SELECT sal    FROM emp_swm    WHERE ename='CLARK')查看与CLARK同职位的员工？  SELECT ename,job  FROM emp_swm  WHERE job=(SELECT job FROM emp_swm            WHERE ename='CLARK')查看与CLARK同部门的员工？  SELECT ename,deptno   FROM emp_swm   WHERE deptno=(SELECT deptno FROM emp_swm                WHERE ename='CLARK')

在DDL中使用子查询

可以根据子查询的结果集快速创建一张表
创建表employee，表中字段为：
empno,ename,job,sal,deptno,dname,loc
数据为现有表中emp与dept对应的数据

CREATE TABLE employee_swm  AS  SELECT e.empno id,e.ename name,e.job,e.sal salary,    e.deptno,d.dname,d.loc  FROM emp_swm e,dept_swm d  WHERE e.deptno=d.deptnoDESC employee_swmSELECT * FROM employee_swm

创建表时若子查询中的字段有别名，则该表
对应得字段就使用该别名作为其字段名。
当子查询中一个字段含有函数或表达式
那么该字段必须给别名。

删除表DROP TABLE employee_swm

DML 中使用子查询

将CLARK所在部门的所有员工删除  DELETE FROM employee_swm  WHERE deptno=(SELECT deptno FROM employee_swm                 WHERE name='CLARK')查询薪水比整个机构平均薪水高的员工？  SELECT name,salary  FROM employee_swm  WHERE (SELECT AVG(salary) FROM employee_swm)<salary 

子查询常用于SELECT语句中，子查询根据查询结果集的不同分为：

单行单列子查询:

常用于过滤条件，
可以配合=,>,>=,<,<=使用

多行单列子查询:

常用于过滤条件，由于查询出多个值
在判断=时要用IN，判断>,>=等操作配合ANY,ALL

多行多列子查询:

常当做一张表来看待

查询与SALESMAN同部门的其他职位员工：  SELECT ename，job，deptno  FROM emp_swm  WHERE deptno IN(SELECT deptno                     FROM emp_swm WHERE job='SALESMAN')        AND job<>'SALESMAN'查看比职位是CLARK和SALESMAN工资都高的员工？  SELECT ename,sal  FROM emp_swm  WHERE sal>ALL(SELECT sal FROM emp_swm           WHERE job IN('CLARK','SALESMAN'))

EXISTS 关键字

EXISTS后面跟一个子查询，当该子查询可以查询出
至少一条记录时，则EXISTS表达式成立并返回true

SELECT deptno,dname FROM dept_swm dWHERE NOT EXISTS(SELECT * FROM emp_swm e   WHERE d.deptno=e.deptno)查看每个部门的最低薪水是多少？前提是该部门的最低薪水都要高于30号部门的最低薪水SELECT deptno,MIN(sal)FROM emp_swmGROUP BY deptnoHAVING MIN(sal)>(SELECT MIN(sal) FROM emp_swm   WHERE deptno=30)

子查询在 FROM 子句中使用

当一个子查询是多列子查询，通常将该子查询
的结果集当做一张表看待并基于它进行二次查询。

查看比自己所在部门平均工资高的员工？  1:查看每个部门的平均工资  SELECT AVG(sal),deptno FROM emp_swm  GROUP BY deptno  SELECT e.ename,e.sal,e.deptno  FROM emp_swm e,(SELECT AVG(sal) avg_sal,deptno       FROM emp_swm GROUP BY deptno) t  WHERE e.deptno=t.deptno  AND e.sal>t.avg_sal

子查询在SELECT子句中使用

可以将查询的结果当做外层查询记录
中的一个字段值显示

SELECT e.ename,e.sal,(SELECT d.dname       FROM dept_swm d       WHERE d.deptno=e.deptno) deptno  FROM emp_swm e

分页查询

分页查询时将查询表中数据时，分段查询
而不是一次性将所有数据查询出来。
有时查询数据量非常庞大，这会导致系统资源
消耗大，相应速度长，数据冗余严重。
为此当遇到这种情况时一般使用分页查询解决。
数据库基本都支持分页，但是不同数据库
语法不同。

Oracle中的分页是基于伪列ROWNUM实现的。
ROWNUM 不存在于任何一张表中，但是所有的表
都可以查询该字段，该字段的值是随着查询自动
生成的，方法是:每当可以从表中查询一条记录时，
该字段的值即为该条记录的行号，从1开始，逐次递增

SELECT ROWNUM,empno,ename,sal,job  FROM emp_swm WHERE ROWNUM>5在使用ROWNUM对结果集进行编号的查询过程中，不能使用ROWNUM做>1以上的数字判断，否则查询不出任何数据。SELECT * FROM(SELECT ROWNUM rn,empno,  ename,sal,job  FROM emp_swm)WHERE rn BETWEEN 6 AND 10查看公司工资排名的6-10  SELECT * FROM    (SELECT ROWNUM rn,t.* FROM      (SELECT empno,ename,sal FROM emp_swm       ORDER BY sal DESC) t    )  WHERE rn BETWEEN 6 AND 10  SELECT * FROM     (SELECT ROWNUM rn,t.* FROM      (SELECT empno,ename,sal FROM emp_swm       ORDER BY sal DESC      ) t      WHERE ROWNUM<=10      )  WHERE rn>=6计算区间公式  pageSize:每页显示的条目数  page:页数  star:(page-1)*pageSize+1  end:pageSize*page  int start = (page-1)*pageSize+1;  int end = pageSize*page;  String sql= "SELECT * FROM "+    "(SELECT ROWNUM rn,t.* FROM"+      "(SELECT empno,ename,sal FROM emp_swm"+      " ORDER BY sal DESC"+     " ) t "+    " WHERE ROWNUM<="+end+   " )"+  "WHERE rn>="+start;

DECODE函数，可以实现分支效果的函数

SELECT ename,job,sal,    DECODE(job,           'MANAGER',sal*1.2,          'ANALYST',sal*1.1,          'SALESMAN',sal*1.05,          sal    ) bonus    FROM emp_swmDECODE 在GROUP BY 分组中的应用可以将字段不同的记录看做一组统计人数，将职位是'MANAGER','ANALYST'  看作一组，其余职业看做另外一组分别统计人数  SELECT COUNT(*),    DECODE(      job      ,'MANAGER','VIP'      ,'ANALYST','VIP'      ,'OTHER')  FROM emp_swm   GROUP BY     DECODE(      job      ,'MANAGER','VIP'      ,'ANALYST','VIP'      ,'OTHER'    )  SELECT deptno,dname,loc  FROM dept_swm  ORDER BY    DECODE(      dname      ,'OPERATIONS',1      ,'ACCOUNTING',2      ,'SALES',3    )

排序函数

排序函数允许对结果集按照指定的字段分组
在组内再按照指定的字段排序，最终生成组内编号。

ROW_NUMBER()函数

生成组内连续且唯一的数字：

PARTITION BY 根据什么分组ORDER BY 根据什么排序查看每个部门的工资排名？  SELECT ename,sal,deptno,    ROW_NUMBER()     OVER(      PARTITION BY deptno      ORDER BY sal DESC    ) rank    FROM emp_swm

RANK函数

生成组内不连续也不唯一的数字，同组内排序字段值一样的记录
生成的数字也一样。

SELECT ename,sal,deptno,    RANK()     OVER(      PARTITION BY deptno      ORDER BY sal DESC    )FROM emp_swm

DENSE_RANK函数

生成组内连续但不唯一的数字。

SELECT ename,sal,deptno    ,DENSE_RANK()    OVER(        PARTITION BY deptno        ORDER BY sal DESC    ) rankFROM emp_swm

集合操作

为了合并多个SELECT语句的结果，可以使用集合操作符，实现集合的并、交、差。
集合操作符包括UNION、UNION ALL、INTERSECT和MINUS。多条作集合操作的SELECT语句的列的个数和数据类型必须匹配。
ORDER BY子句只能放在最后的一个查询语句中。

UNION、UNION ALL

**UNION和UNION ALL用来获取两个或两个以上结果集的并集：
UNION操作符会自动去掉合并后的重复记录。
UNION ALL返回两个结果集中的所有行，包括重复的行。
UNION操作符对查询结果排序，UNION ALL不排序。**

合并职位是’MANAGER’的员工和薪水大于2500的员工集合，查看两种方法的结果差别

SELECT ename,job,salFROM emp_swmWHERE job='MANAGER'    UNIONSELECT ename,job,salFROM emp_swmWHERE sal>2500SELECT ename,job,salFROM emp_swmWHERE job='MANAGER'    UNION ALLSELECT ename,job,salFROM emp_swmWHERE sal>2500

INTERSECT

INTERSECT函数获得两个结果集的交集，只有同时存在于两个结果集中的数据，才被显示输出。使用INTERSECT操作符后的结果集会以第一列的数据作升序排列。

SELECT ename,job,sal FROM emp_swmWHERE job='MANAGER'    INTERSECTSELECT ename,job,sal FROM emp_swmWHERE sal>2500;

MINUS

MINUS函数获取两个结果集的差集。只有在第一个结果集中存在，在第二个结果集中不存在的数据，才能够被显示出来。也就是结果集一减去结果集二的结果。

列出职位是MANAGER但薪水低于2500的员工记录：

SELECT ename, job, sal FROM empWHERE job = 'MANAGER'MINUSSELECT ename, job, sal FROM empWHERE sal> 2500;

创建练习表 sales_tab_swm

CREATE TABLE sales_tab_swm(    year_id NUMBER NOT NULL,    month_id NUMBER NOT NULL,    day_id NUMBER NOT NULL,    sales_value NUMBER(10,2) NOT NULL);

随机插入1000条数据

INSERT INTO sales_tabSELECT TRUNC(DBMS_RANDOM.value(2010,2012)) AS year_id,    TRUNC(DBMS_RANDOM.value(1,13)) AS month_id,    TRUNC(DBMS_RANDOM.value(1,32)) AS day_id,    TRUNC(DBMS_RANDOM.value(1,100)) AS sales_valueFROM dualCONNECT BY level<=1000;COMMIT

查看每天的营业额？

SELECT year_id,month_id,day_id,SUM(sales_value)FROM sales_tab_swmGROUP BY year_id,month_id,day_idORDER BY year_id,month_id,day_id

每月的营业额？

SELECT year_id,month_id,SUM(sales_value)FROM sales_tab_swmGROUP BY year_id,month_idORDER BY year_id,month_id

每年的营业额？

SELECT year_id,SUM(sales_value)FROM sales_tab_swmGROUP BY year_idORDER BY year_id

总共的营业额？

SELECT SUM(sales_value)FROM sales_tab_swm

高级分组函数

高级分组函数用在GROUP BY 子句中，每个高级分组函数都有一套分组策略。

ROLLUP():

分组原则，参数逐次递减，一直到所有参数都不要，每一种分组都统计一次结果并且在一个结果集显示。

GROUP BY ROLLUP(a,b,c)等价于:GROUP BY a,b,cUNION ALLGROUP BY a,bUNION ALLGROUP BY aUNION ALL全表

查看每天，每月，每年以及总共的营业额？

SELECT year_id,month_id,day_id,SUM(sales_value)FROM sales_tab_swmGROUP BYROLLUP(year_id,month_id,day_id)

CUBE()

每种组合分一组。分组次数：2的参数个数次方

SELECT year_id,month_id,day_id,SUM(sales_value)FROM sales_tab_swmGROUP BYCUBE(year_id,month_id,day_id)ORDR BY year_id,month_id,day_id

GOUPING SETS

每个参数是一种分组方式，然后将这些分组统计后，并在一个结果集显示。

仅查看每天和每月营业额？

SELECT year_id,month_id,day_id,SUM(sales_value)FROM sales_tab_swmGROUP BYGROUPING SETS((year_id,month_id,day_id),    (year_id,month_id))