第4章SQL语言高级.ppt

1、第4章 SQL语言高级,本章内容,多列分组查询 累计统计查询 层次查询 多表插入 SQL函数,4.1 多列分组统计,ROLLUP CUBE GROUPING GROUPING SETS,使用ROLLUP选项，除了生成基于所有指定列的分组统计外，还对指定的分组列从左开始的每个子集进行统计。例如，GROUP BY ROLLUP(A,B,C)形成的统计包括： GROUP BY ()：不基于任何列的整个查询结果的统计。 GROUP BY A：基于A列的分组统计。 GROUP BY A,B：基于A、B两列的分组统计。 GROUP BY A,B,C：基于A、B、C三列的分组统计。,SELECT depar

2、tment_id,job_id,avg(salary) FROM employees WHERE department_id IN(4,20,30) GROUP BY ROLLUP(department_id,job_id),使用CUBE选项，除了生成基于所有指定列的分组统计外，还对指定分组列的所有子集进行统计。例如，GROUP BY CUBE(A,B,C)形成的统计包括： GROUP BY ()：不基于任何列的整个查询结果的统计。 GROUP BY A：基于A列的分组统计。 GROUP BY B：基于B列的分组统计。 GROUP BY C：基于C列的分组统计。 GROUP BY A,B：基于

3、A、B两列的分组统计。 GROUP BY A,C：基于A、C两列的分组统计。 GROUP BY B,C：基于B、C两列的分组统计。 GROUP BY A,B,C：基于A、B、C三列的分组统计。,SELECT department_id,job_id,avg(salary) FROM employees WHERE department_id IN(4,20,30) GROUP BY CUBE(department_id,job_id);,为了了解每个统计结果是基于哪些列统计而来的，可以使用GROUPING函数，函数返回值为1时，表示该列没有参与统计；函数返回值为0时，表示该列参与了统计。 SQ

4、LSELECT department_id,job_id,avg(salary), grouping(department_id) did,grouping(job_id) jid FROM hr.employees WHERE department_id IN(4,20,30) GROUP BY CUBE(department_id,job_id);,合并分组查询 使用GROUPING SETS可以将几个单独的分组查询合并成一个分组查询 SELECT department_id,job_id,avg(salary) FROM employees GROUP BY GROUPING SETS(

5、department_id,job_id);,GROUPING SETS语句的作用就是使用一个语句得到多个分组统计的结果集。要注意嵌套列与非嵌套列的区别。嵌套列与单个GROUP BY语句作用相同；非嵌套列相当于将多个单独的GROUP BY语句查询结果，采用UNION ALL方式的合并起来。例如： GROUP BY GROUPING SETS(A,B,C)：等价于GROUP BY A,B,C。 GROUP BY GROUPING SETS(A,B,C)：等价于GROUP BY A UNION ALL GROUP BY B UNION ALL GROUP BY C。 GROUP BY GROUPI

6、NG SETS(A,(B,C)：等价于GROUP BY A UNION ALL GROUP BY B,C。,4.2 累计查询,在执行统计查询时，可以将聚集函数与OVER函数相结合，进行总体累计统计查询或分组累计统计查询 总体累计统计 分组累计统计,总体累计统计的每一个统计结果都是针对之前的所有记录进行的，在OVER函数中使用ORDER BY语句指定统计的顺序，如果不指定ORDER BY语句，则不进行累计统计。 SELECT employee_id, sum(salary) OVER(ORDER BY employee_id) sal,count(*) OVER(ORDER BY employe

7、e_id) num,sum(salary) over() total_sal,count(*) over() total_num FROM hr.employees;,如果在进行累计统计时，需要按组进行，则需要使用PARTITION子句指定累计统计的分组列。 SQLSELECT department_id,employee_id, sum(salary) OVER( PARTITION BY department_id ORDER BY employee_id) sal, count(*) OVER(PARTITION BY department_id ORDER BY employee_id

8、) num FROM hr.employees;,4.3 层次查询,层次查询（hierarchical_query），又称树形查询，能够将一个表中的数据按照记录之间的联系以树状结构的形式显示出来。 层次查询的语法为 SELECT LEVEL, column,expression. FROM table WHERE condition START WITH column = value CONNECT BY condition;,参数说明， LEVEL：伪列，表示记录的层次； WHERE：记录（节点）选择条件； START WITH：层次查询的起始记录（起始节点） CONNECT BY：指定父记

9、录与子记录之间的关系及 分支选择条件。必须使用PRIOR引用父记录，形式为 PRIOR column1=column2或 column1=PRIOR column2。,利用分级查询显示emp表中员工与领导之间的关系（从高到低）。 SELECT empno,ename,mgr FROM emp START WITH empno=7839 CONNECT BY PRIOR empno=mgr;,EMPNO ENAME MGR - 7839 KING 7566 JONES 7839 7788 SCOTT 7566 7876 ADAMS 7788 7902 FORD 7566 7369 SMITH 7

10、902 7698 BLAKE 7839 7499 ALLEN 7698 7521 WARD 7698 7654 MARTIN 7698 7844 TURNER 7698 7900 JAMES 7698 7782 CLARK 7839 7934 MILLER 7782,查询显示工资大于2000且最高领导为JONES的员工信息。 SELECT empno,ename,mgr,sal FROM emp WHERE sal2000 START WITH ename=JONES CONNECT BY PRIOR empno=mgr; EMPNO ENAME MGR SAL - 7566 JONES 78

11、39 3075 7788 SCOTT 7566 340 7902 FORD 7566 340,查询员工信息，不包括以7698号员工为最高领导的员工 SELECT empno,ename,mgr FROM emp START WITH empno=7839 CONNECT BY PRIOR empno=mgr AND empno!=7698; EMPNO ENAME MGR - 7839 KING 7566 JONES 7839 7788 SCOTT 7566 7876 ADAMS 7788 7902 FORD 7566 7369 SMITH 7902 7782 CLARK 7839 7934

12、MILLER 7782,SELECT lpad( ,5*LEVEL-1)|empno EMPNO, lpad( ,5*LEVEL-1)|ename ENAME FROM emp START WITH empno=7839 CONNECT BY PRIOR empno=mgr;,4.4向多个表中插入数据,在Oracle 4g中，可以使用INSERT语句同时向多个表中插入数据。 根据数据插入的条件不同，分为： 无条件插入：将数据插入所有指定的表中 有条件插入：将数据插入符合条件的表中。,无条件多表插入的基本语法为 INSERT ALL INTO table1 VALUES(column1,colu

13、mn2,) INTO table2 VALUES(column1,column2,) subquery; 利用无条件多表插入，将emp表中工资高于2000的员工信息查询后分别插入emp_sal和emp_mgr表。 INSERT ALL INTO emp_sal VALUES(empno,hiredate,sal) INTO emp_mgr VALUES(empno,mgr,sal) SELECT empno,hiredate,mgr,sal FROM emp WHERE sal2000；,有条件多表插入语法为 INSERT ALL|FIRST WHEN condition1 THEN INTO

14、 table1(column1,) WHEN condition2 THEN INTO table2(column1,) ELSE INTO tablen(column1,) subquery; 参数说明 ALL：表示一条记录可以同时插入多个满足条件的表中； FIRST：表示一条记录只插入第一个满足条件的表中。,将emp表中的员工信息按不同部门号分别复制到emp4，emp20，emp30和emp_other表中。 INSERT FIRST WHEN deptno=4 THEN INTO emp4 WHEN deptno=20 THEN INTO emp20 WHEN deptno=30 THE

15、N INTO emp30 ELSE INTO emp_other SELECT * FROM emp;,将emp表中员工信息按照不同部门号分别复制到emp4，emp20，emp30和emp_other表中。同时，将工资低于2000的员工信息复制到lowsal表中，将工资高于4000的员工信息复制到highsal表中，将工资在20003000之间的员工信息复制到middlesal表中。 INSERT ALL WHEN deptno=4 THEN INTO emp4 WHEN deptno=20 THEN INTO emp20 WHEN deptno=30 THEN INTO emp30 WHEN

16、 deptno=40 THEN INTO emp_other WHEN sal4000 THEN INTO highsal ELSE INTO middlesal SELECT * FROM emp;,INSERT ALL WHEN SAL 4000 THEN INTO sal_history VALUES(EMPID,HIREDATE,SAL) WHEN MGR 200 THEN INTO mgr_history VALUES(EMPID,MGR,SAL) SELECT employee_id EMPID,hire_date HIREDATE, salary SAL, manager_id

17、MGR FROM employees WHERE employee_id 200;,多表插入的应用 利用多表插入技术可以实现不同数据源之间的数据转换，可以将非关系数据库的一条记录转换为关系数据库中的多条记录。,将SALES_SOURCE_DATA表中的记录转换为SALES_INFO表中的记录。 CREATE TABLE sale_source_data( emp_id NUMBER(6), week_id NUMBER(2), sale_MON NUMBER(8,2), sale_TUE NUMBER(8,2), sale_WED NUMBER(8,2), sale_THUR NUMBER(8

18、,2), sale_FRI NUMBER(8,2); INSERT INTO sale_source_data VALUES(7844,1,40,200,300,400,500);,CREATE TABLE sale_info( emp_id NUMBER(6), week NUMBER(2), sale NUMBER(8,2); INSERT ALL INTO sale_info VALUES (emp_id,week_id,sale_MON) INTO sale_info VALUES (emp_id,week_id,sale_TUE) INTO sale_info VALUES (emp

19、_id,week_id,sale_WED) INTO sale_info VALUES (emp_id,week_id,sale_THUR) INTO sale_info VALUES (emp_id,week_id, sale_FRI) SELECT * FROM sale_source_data;,4.5 SQL函数,SQL函数分类 数值函数 字符函数 日期函数 转换函数 其他函数,4.5.1 SQL函数分类,根据参数作用行数的不同，可以分为： 单行函数 多行函数 根据参数类型不同，可以分为： 数值函数 字符函数 日期函数 转换函数 聚集函数,4.5.2 数值函数,SELECT salar

20、y/22 daysal,round(salary/22,1), trunc(salary/22,1), round(salary/22,-1),trunc(salary/22,-1) FROM employees; SELECT salary,width_bucket(salary,400,4000,4) FROM employees WHERE department_id=30;,SELECT floor(3.5),ceil(3.5),mod(5,3), remainder(5,3), mod(4,3),remainder(4,3) FROM dual; FLOOR(3.5) CEIL(3.

21、5) MOD(5,3) REMAINDER(5,3) MOD(4,3) REMAINDER(4,3) - 3 4 2 -1 1 1,4.5.3 字符函数,SELECT lpad(abc,5, #) leftpad,rpad(abc,5, #) rightpad, ltrim(abcd, a) lefttrim,rtrim(abcde, e) righttrim, substr(abcd,2,3) substring FROM dual; LEFTPAD RIGHTPAD LEFTTRIM RIGHTTRIM SUBSTRING - - - - - #abc abc# bcd abcd bcd,

22、SELECT employee_id, concat(concat(first_name, ),last_name) employee_name FROM employees WHERE employee_id=48; SELECT instr(abcde,b) position,replace(oracle9i,9i,4g) newstring,soundex(hello) sound FROM dual; POSITION NEWSTRING SOUND - 2 oracle4g H400,4.5.4 日期函数,日期函数是指对日期进行处理的函数，函数输入为DATE或TIMESTAMP类型的

23、数据，输出为DATE类型的数据（除MONTH_BETWEEN函数返回整数以外）。 Oracle数据库中日期的默认格式为DD-MON-YY。可以通过设置NLS_DATE_FORMAT参数设置当前会话的日期格式，通过NLS_LANGUAGE参数设置表示日期的字符集。例如： ALTER SESSION SET NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS; ALTER SESSION SET NLS_LANGUAGE=AMERICAN;,SELECT SYSDATE,add_months(sysdate,2) ADDM, next_day(sysdate,2) NEXT

24、D,Last_day(sysdate) LASTD, round(sysdate, MONTH) ROUNDM, trunc(sysdate, MONTH) TRUNCM FROM DUAL; SYSDATE ADDM NEXTD LASTD ROUNDM TRUNCM - - - - - - 2009-03-27 2009-05-27 2009-03-30 2009-03-31 2009-04-01 2009-03-01,SELECT extract(YEAR FROM SYSDATE) YEAR, extract(DAY FROM SYSDATE) DAY , extract(HOUR FROM SYSTIMESTAMP) HOUR,extract