《复杂SQL语句》PPT课件.ppt

1、复杂SQL语句,1,2,3,4,较复杂查询,多表查询,分组及函数,子查询,目 录,使用选择限定记录,在上面的例子中，假定你想要显示部门10的所有员工，这种方式是基于WHERE子句的SQL命令。,使用where子句限定返回的记录,WHERE子句在FROM子句后面 Condition:由列名表达式，常量和比较操作符组成。,SELECT DISTINCT * , column alias , FROM table WHERE condition(s);,使用WHERE语句,上面例子中返回job=CLERK的所有员工的name , job和deptno 注意：字符的大小写是敏感的。,SQL SELEC

2、T ename, job , deptno FROM emp WHERE job =CLERK; ENAME JOB DEPTNO - - - KING CLERK 30 BLAKE CLERK 20 TURNER CLERK 10 14 rows selected。,字符串和日期,在WHERE子句中的字符串和日期必须用单引号括起来，所有的字符是大小写敏感的。 Oracle存储日期是以内定的格式存放，它们代表世纪、年、月、日、小时、分钟和秒，缺省显示的日期格式是DD-MON-YY，也可能是其他格式。 为了避免日期字段查询条件不同格式下可能造成的错误，通常用TO_DATE函数来进行转换。,日期类

3、型查询条件举例,上面第二个例子使用了TO_DATE函数，就不再受日期格式的影响了。否则同样的查询，同样的表数据，不同的系统日期格式会导致结果不正确。,SQL SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE=03-DEC-81; ENAME HIREDATE - - JAMES 03-DEC-81 FORD 03-DEC-81 SQL SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE=TO_DATE(19811203,YYYYMMDD); ENAME HIREDATE - - JAMES 03-DEC-81 F

4、ORD 03-DEC-81,常用比较运算符,使用BETWEEN,SQL SELECT ename , sal FROM emp WHERE sal BETWEEN 1000 AND 1500; ENAME SAL - - KING 1250 BLAKE 1500 CLERK 1250 JONES 1300,注意：BETWEEN后面要先写低值，后写高值,使用IN运算符,SQL SELECT empno, ename , sal, mgr FROM emp WHERE mgr IN(7902, 7566, 7788); EMPNO ENAME SAL MGR - - 7902 KING 1250

5、7566 7369 BLAKE 1500 7902 7788 CLARK 1250 7566 7876 JONES 1300 7788,使用LIKE运算符,使用LIKE运算符执行通配查询 查询条件可包含文字字符或数字 %可表示零或多个字符 _可表示一个字符,SQL SELECT ename FROM emp WHERE ename LIKE S%; ENAME - SMITH SCOTT,用LIKE和ESCAPE来查找包含特殊字符的数据,例如如果想查找表EMP中ENAME包含下划线_的数据，就需要用到ESCAPE选项，否则查询结果不准确。 ESCAPE后面单引号内只能有一个字符，表示前面的LI

6、KE条件中这个字符后面的第一个字符当作普通字符处理,SQL SELECT EMPNO,ENAME FROM EMP WHERE ENAME LIKE %_% ESCAPE ; EMPNO ENAME - - 9999 FOR_TEST,使用IS NULL,查询包含空值的记录,SQL SELECT ename , mgr FROM emp WHERE mgr IS NULL; ENAME MGR KING,逻辑运算符,优先级次序： 1 所有的比较运算 2 NOT 3 AND 4 OR 括号将跨越所有优先级,使用AND运算符,AND需要条件都满足,SQL SELECT empno,ename,jo

7、b,sal FROM emp WHERE sal= 1100 AND job=CLERK； EMPNO ENAME JOB SAL - - 7369 BLAKE CLERK 1300 7788 CLARK CLERK 1250,使用OR运算符,OR需要满足条件之一即可,SQL SELECT empno,ename,job,sal FROM emp WHERE sal= 1100 OR job=CLERK； EMPNO ENAME JOB SAL - - 7369 BLAKE CLERK 1300 7788 CLARK CLERK 1250 7839 KING PERSIDENT 5000 76

8、45 MARTIN MANAGER 1050,使用NOT运算符,SQL SELECT ename ,job FROM emp WHERE job NOT IN(CLERK,MANAGER,ANALYST); ENAME JOB - - KING PERSIDENT MARTIN SALESMAN WARD SALESMAN,ORDER BY 语句,在缺省情况下，查询返回的结果是没被排序的。使用ORDER BY子可将记录排序。ORDER BY 子句放在最后。ASC表示升序排序，DESC表示降序排序，缺省为ASC,SELECT expr FROM table WHERE condition ORD

9、ER BY column , expr ASC | DESC,降序排列,SQL select ename,hiredate from emp order by hiredate desc; ENAME HIREDATE - - FOR_TEST ADAMS 12-JAN-83 SCOTT 09-DEC-82 MILLER 23-JAN-82 JAMES 03-DEC-81 SMITH 17-DEC-80 15 rows selected.,使用列别名排序,SQL select empno,ename,sal*12 annual from emp order by annual; EMPNO E

10、NAME ANNUAL - - - 7369 SMITH 9600 7900 JAMES 11400 7876 ADAMS 13200 7521 WARD 15000 7654 MARTIN 15000 7934 MILLER 15600 7844 TURNER 18000 15 rows selected.,按照多个列排序,SQL SELECT ename,sal,deptno FROM EMP ORDER BY deptno,sal DESC; ENAME SAL DEPTNO - - - KING 5000 10 CLARK 2450 10 MILLER 1300 10 SCOTT 30

11、00 20 FORD 3000 20 JONES 2975 20 ADAMS 1100 20 . 15 rows selected.,1,2,3,4,较复杂查询,多表查询,分组及函数,子查询,目 录,从多个表中获取数据,有时候你需要从多个表中获得数据。在上面的例子中，报告显示的数据取自两个表。 EMPNO存在于EMP表中，DEPTNO在EMP和DEPT表中都有，LOC存在于DEPT表中为了生成上面的报告，你需要将表EMP和DEPT连起来，从两个中获取数据。,定义连接,当从两个以上的表中获取数据时，就要使用连接条件。一个表中的记录可以根据两个表的相同列和另一个表的记录相连接。两表中的相同列一般是

12、主键和外键列。 为了能显示两个或多个表中的数据，在WHERE子句中需要设简单的连接条件。语法如下： table.column 指定取数据的表和它的列 table.column1 = table2.column2 将表连接起来的条件 当写一个有连接的SELECT命令时，为了避免同样的不同表具有同样的列名，应该在列前加表的名字或者表的别名。 当在表中有相同的列名时，并且这个列作为显示内容或者查询条件，必须在列名前加表名或表的别名作为前缀。 如果要将n个表连起来，你必须指定n-1个连接条件。因此连接4个表需要有3个连接条件。如果你的表有组合主键，此规则可能不适用，此时多一条记录需要多个列唯一标识。,

13、笛卡尔结果,笛卡尔结果 笛卡尔结果形成于： -连接条件被省略 -连接条件无效 -第一个表的所有记录连接到第二个表的所有记录 一个笛卡尔结果趋于产生一个巨大的记录数 ，通常没有意义。为了避免笛卡尔结果我们要在WHERE子句中使用有效连接,笛卡尔结果举例,SELECT * FROM EMP,DEPT;,连接类型,有两种主要的连接条件 等值连接 非等值连接 其它的连接方式包括 多连接 自连接 定置运算符,什么是等值连接,就是两个表连接的WHERE条件是一个表的列等于另外一个表的列。通常情况下，这种连接是主键和外键的连接。,使用等值连接获取记录举例,因为DEPTNO列在EMP和DEPT中都存在，因此需

14、要在这个列前面加上表名，否则Oracle认为有歧义，语句无法执行,SQL SELECT ENAME,EMP.DEPTNO,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO; ENAME DEPTNO DNAME - - - MILLER 10 ACCOUNTING KING 10 ACCOUNTING CLARK 10 ACCOUNTING FORD 20 RESEARCH ADAMS 20 RESEARCH SCOTT 20 RESEARCH ,额外的条件,除了连接条件，可能还有额外的查询条件。例如，显示员工King的员工号、各字、部门号和部门

15、位置，这时在WHERE子句中需要设定一个额外的条件。,SQLSELECT empno, ename, emp.deptno, Loc FROM emp, dept WHERE emp.deptno=dept.deptno AND INITCAP(ename)=King; EMPNO ENAME DEPTNO LOC - - - - 7839 KING 10 NEW YORK,使用表的别名,表的别名 使用表名限定列名可能会很浪费时间，尤其是当表名特别长，这时你可以使用表的别名。使用表的别名会减少程序代码，因此占用较少的内存。注意，表的别名是在FROM子句中指定的。 表别名规则： 表的别名最长为3

16、0个字符，但通常以短字符为佳 表的别名最好有一定的含义 表的别名只在当前的SELECT语句有效 如果在FROM子句定义了表的别名，在SELECT子句中必须用它来替代表名。,SQLSELECT empno, ename, e.deptno, Loc FROM emp e, dept d WHERE e.deptno=d.deptno AND INITCAP(ename)=King; EMPNO ENAME DEPTNO LOC - - - - 7839 KING 10 NEW YORK,非等值连接,在EMP表和SALGRADE表中，没有直接的对应列，它们之间的关系是EMP的SAL列的值在SALG

17、RADE表的LOSAL和HISAL列之间，它们是不等值连接。,非等值连接举例,SQL SELECT e.sal,e.ename,s.grade FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal; SAL ENAME GRADE - - - 5000 KING 5 3000 SCOTT 4 3000 FORD 4 2975 JONES 4 2850 BLAKE 4 2450 CLARK 4 1600 ALLEN 3 1500 TURNER 3 1300 MILLER 2 .,外连接,使用外部连接，返回连接两边有一边为NUL

18、L的记录 外连接运算符是加号(+) 外连接运算符(+)可以加在左边, 也可以加在右边，但不能两边同时加外连接 有(+)的一边表示这边的值要么等于另外一边，要么为NULL 从9i开始，SQL支持ANSI SQL，也就是支持LEFT OUTER JOIN、RIGHT OUTER JOIN和FULL OUTER JOIN,SQL SELECT table.column,table.column FROM table1,table2 WHERE table1.column(+)=table2.column;,SQL SELECT table.column,table.column FROM table

19、1,table2 WHERE table1.column = table2.column(+);,外连接举例1：,这个例子(+)在e.deptno这边，意味着e.deptno可以是NULL,SQL SELECT ename,e.deptno E.DEPTNO,d.deptno D.DEPTNO,d.dname FROM EMP E,DEPT D WHERE E.DEPTNO(+)=D.DEPTNO; ENAME E.DEPTNO D.DEPTNO DNAME - - - - SMITH 20 20 RESEARCH ALLEN 30 30 SALES . FORD 20 20 RESEARCH

20、 MILLER 10 10 ACCOUNTING 40 OPERATIONS 15 rows selected.,外连接举例2,这个例子(+)在d.deptno这边，意味着d.deptno可以是NULL,SQL SELECT ename,e.deptno E.DEPTNO,d.deptno D.DEPTNO,d.dname FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO(+); ENAME E.DEPTNO D.DEPTNO DNAME - - - - MILLER 10 10 ACCOUNTING KING 10 10 ACCOUNTING . ALLEN

21、 30 30 SALES wzh 90 FOR_TEST 90 16 rows selected.,外连接举例3,从9i开始，可以用ANSI SQL语法来写外连接，这样也提供了一个以前的(+)不能实现的功能：全外连接,SQL SELECT E.ENAME,E.DEPTNO E.DEPTNO, D.DEPTNO D.DEPTNO,D.DNAME FROM EMP E FULL OUTER JOIN DEPT D ON (E.DEPTNO=D.DEPTNO); ENAME E.DEPTNO D.DEPTNO DNAME - - - - MILLER 10 10 ACCOUNTING KING 10

22、 10 ACCOUNTING . WARD 30 30 SALES ALLEN 30 30 SALES wzh 90 FOR_TEST 90 40 OPERATIONS 17 rows selected.,表的自连接,有的时候，需要对表进行自连接。例如上图所示，EMP表中的MGR列的代表员工的经理的员工编号，所以要想显示出每个员工的经理就要对EMP进行自连接，用MGR=EMPNO。实际上，自连接只是等连接（等外连接）的一个特例，同一张表用不同的别名，区别成了不同的表。,表自连接举例,上面这个例子相当一个等外连接。如果员工有经理，则显示出员工为谁工作（打工者），否则显示员工为自己干活（老板）,S

23、QL SELECT E.ENAME | works for | NVL(M.ENAME,himself) RELATIONS FROM EMP E,EMP M WHERE E.MGR=M.EMPNO(+); RELATIONS - FORD works for JONES . JONES works for KING SMITH works for FORD KING works for himself 14 rows selected.,1,2,3,4,较复杂查询,多表查询,分组及函数,子查询,目 录,产生数据组：GROUP BY子句,GROUP BY子句 使用GROUP BY 子句将一个表

24、分成许多小组，并对每一个小组返回一个计算值。 Group_by_expression：指定按什么列分组 规则： 在SELECT子句中，如果使用分组函数，不能对GROUP BY子句中指定的列使用分组函数。 使用WHERE子句，可预先排除某些记录 在GROUP BY 子句中必须有表中的列 在GROUP BY子句中不能使用列的别名 缺省情况下在GROUP BY子句中的列以升序排，你可以使用order by子句 改变它。,SELECT column, group_function FROM table WHEREcondition GROUP BY group_by_expression ORDER

25、BY column;,使用GROUP BY举例,注意，不是分组的列不能出现在SELECT后面。否则会提示：ORA-00979: not a GROUP BY expression。组函数也不能出现在WHERE子句中，否则会提示ORA-00934: group function is not allowed here,SQL SELECT DEPTNO, SUM(SAL),MAX(SAL),MIN(SAL),AVG(SAL) FROM EMP GROUP BY DEPTNO; DEPTNO SUM(SAL) MAX(SAL) MIN(SAL) AVG(SAL) - - - - - 10 8750

26、 5000 1300 2916.66667 20 10875 3000 800 2175 30 9400 2850 950 1566.66667,多列分组举例,有时你可能需要在组中再分组，上面例子中显示每个部门中不同头衔的工资和与平均工资。 此时EMP表首先以部门分组，然后按头衔分组,SQL SELECT DEPTNO,JOB,SUM(SAL),AVG(SAL) FROM EMP GROUP BY DEPTNO,JOB; DEPTNO JOB SUM(SAL) AVG(SAL) - - - - 10 CLERK 1300 1300 10 MANAGER 2450 2450 10 PRESIDE

27、NT 5000 5000 20 CLERK 1900 950 20 ANALYST 6000 3000 20 MANAGER 2975 2975 30 CLERK 950 950 30 MANAGER 2850 2850 30 SALESMAN 5600 1400,什么是分组函数,和单行函数不同，分组函数作用于一组记录，每一组返回一个结果。这些组可能是整个表，也可能是由GROUP BY子句将表分成的多个组。,主要的分组函数,COUNT(*/DISTINCE/ALLexpr) 返回记录数，这里expr赋非空值，*表示所有被选择的记录，包括重复记录和空值。 MAX(DISTNCT/ALLexpr)

28、：表达式的最大值，忽略空值 MIN(DISTNCT/ALLexpr)：表达式的最小值，忽略空值 AVG(DISTNCT/ALLexpr)：平均值，忽略空值 STDDEVDISTINCT/ALLX)：返回标准差，忽略空值 SUM(DIXNTICT/ALLn)：求和，忽略空值 VARIANCE(DISTINCT/ALLX)：返回统计方差 这些分组函数中,COUNT是不计算NULL值的，其它函数忽略NULL值。,使用分组函数举例1：,SQL SELECT SUM(SAL),MAX(SAL),MIN(SAL),AVG(SAL) FROM EMP; SUM(SAL) MAX(SAL) MIN(SAL)

29、AVG(SAL) - - - - 29025 5000 800 2073.21429,使用分组函数举例2：,SQL SELECT COUNT(*) FROM EMP; COUNT(*) - 16 SQL SELECT COUNT(EMPNO) FROM EMP; COUNT(EMPNO) - 16 SQL SELECT COUNT(MGR) FROM EMP; COUNT(MGR) - 13,使用HAVING子句限定分组函数结果值,前面我们讲过，分组函数不能写在WHERE子句中，如果要对分组函数结果值进行限定，可以用HAVING子句,SQL SELECT DEPTNO,JOB,SUM(SAL)

30、,AVG(SAL) FROM EMP GROUP BY DEPTNO,JOB HAVING AVG(SAL)2000; DEPTNO JOB SUM(SAL) AVG(SAL) - - - - 10 MANAGER 2450 2450 10 PRESIDENT 5000 5000 20 ANALYST 6000 3000 20 MANAGER 2975 2975 30 MANAGER 2850 2850,SQL函数,SQL函数 有两种不同的SQL函数 单行函数 多行函数 单行函数 这些函数仅作用于单行记录，并对每行记录返回一个值，有许多不同类型的单行函数，常用的类型有： 字符函数 数字函数 日

31、期函数 转换函数 多行函数 这些函数作用于记录组，每组记录返回一个结果。,单行函数,单行函数 单行函数操作数据项，它们接收一个或多个参数，并对查询出的每一条记录返回一个值。参数可以是： 用户提供的常量 一个列名 一个表达式 单行函数的特性 它们作用于查询的每一条记录 每条记录返回一个结果 它们可返回一个不同于它所参照的数据类型 它们可嵌入到SELECT ，WHERE 和ORDER BY子句。,字符串函数,字符函数被分为： 大小写转换函数 字符处理函数 LOWER(column | expression):将字符转换为小写 UPPER(column | expression):将字符转换不大写

32、INITCAP(column | expression):将每一个单词的第一个字母大写其它小写 CONCAT(column expression):返回第一个串接上第二个串,它的作用和| 运算是相同的 SUBSTR(column expression,):返回从字母m开始,有n个字符 长的字符串。 LENGTH(column expression):返回字符串长度 INSTR(column 1 expression.mn):返回字符串中字符的位置 LPAD(column 1 cxpression,n,string):在字符串前填补字符,使其长度达到n。 RPAD(column 1 cxpres

33、sion,n,string):在字符串后填补字符,使其长度达到n。,字符串函数举例,上面的例子将ename列的首字母大写，其余字母小写,SQL SELECT INITCAP(ename) FROM emp; INITCAP(EN - Smith Allen Ward ,字符串处理函数举例,SQLSELECT ENAME,SUBSTR(ENAME,1,3) SUBSTR,RPAD(ENAME,10,*) RPAD,LENGTH(ENAME) LENGTH FROM EMP; ENAME SUBSTR RPAD LENGTH - - - - SMITH SMI SMITH* 5 ALLEN ALL

34、 ALLEN* 5 WARD WAR WARD* 4 JONES JON JONES* 5 MARTIN MAR MARTIN* 6 ,数字函数,数字函数接收数字输入返回数字值 ROUND(column | expression, n):返回舍入到小数点右边n位的值 TRUNC(column | expression, n):返回截断到n位的值 MOD(m,n):返回m和n相除后的余数,使用ROUND函数,上面的例子分别显示45.923到小数点后两位，个位，十位,SQL SELECT ROUND(45.923,2),ROUND(45.923,0), ROUND(45.923,-1) FROM

35、DUAL; ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1) - - - 45.92 46 50,使用TRUNC函数,显示45.923到小数点后两位，个位，十位,SQL SELECT TRUNC(45.923,2),TRUNC(45.923,0), TRUNC(45.923,-1) FROM DUAL; TRUNC(45.923,2) TRUNC(45.923,0) TRUNC(45.923,-1) - - - 45.92 45 40,使用MOD函数,这个例子计算工资除以奖金后的余数,SQL SELECT ename,sal ,comm,MOD(

36、sal,comm) FROM emp WHERE job = SALESMAN; ENAME SAL COMM MOD(SAL,COMM) - - - - ALLEN 1600 300 100 WARD 1250 500 250 MARTIN 1250 1400 1250 TURNER 1500 0 1500,使用日期函数,Oracle的日期函数 Oracle使用内部的数字化格式存储日期，它们代表世纪、年、月、日、小时、分钟和秒. 缺省显示的日期格式为DD-MON-YY，有效的日期在公元前4712年1月1日到公元后9999年12月31日 SYSDATE SYSDATE是一个返回当前日期和时间的

37、日期函数 DUAL DUAL是一个SYS用户所拥有的表，所有的用户都可以访问。它包括一个列DUMMY和一条记录值为X。 例子：显示当前的日期 SQLSELECT SYSDATE FROM SYS.DUAL; SYSDATE - 24-MAY-07,日期运算,日期+数字=日期 加天数 日期-数字=日期 减天数 日期-日期=数字 两日期间的天数 日期+number/24=日期 加小时 注意：两个日期类型字段不能相加,日期运算举例,SQL SELECT ENAME,SYSDATE-HIREDATE FROM EMP; ENAME SYSDATE-HIREDATE - - SMITH 9654.549

38、54 ALLEN 9589.54954 WARD 9587.54954 JONES 9548.54954 MARTIN 9369.54954 ,常见的日期函数,日期函数使用举例,SQL SELECT SYSDATE,ADD_MONTHS(SYSDATE,12), LAST_DAY(SYSDATE),NEXT_DAY(SYSDATE,FRIDAY) FROM DUAL; SYSDATE ADD_MONTHS LAST_DAY(S NEXT_DAY(S - - - - 2007/05/24 2008/05/24 2007/05/31 2007/05/25,转换函数,转换函数用于数据类型之间的转换。

39、SQL尽可能地自动进行转换，它会隐含地调用转换函数。但是你无法对隐含调用中使用的格式指定符进行控制，并且这会使得你的代码很难理解。因此使用显式转换函数而不依赖于隐式转换是一个很好的程序设计风格。,转换函数,Oracle提供了3个转换函数 TO_CHAR(number | date, fmt) :将数字或日期按格式转换成字符 TO_NUMBER(char) :将字符串转换成数字，此字符串必须是数字 TO_DATE(CHAR, fmt) ：将字符串按指定的格式转换成日期,使用TO_CHAR操作日期函数举例,上面的例子中：YYYY代表4位的年份，MM代表月份，HH24代表24小时，MI代表分钟，SS

40、代表秒。,SQL SELECT SYSDATE,TO_CHAR(SYSDATE,YYYY/MM/DD HH24:MI:SS) DETAILDATE FROM DUAL; SYSDATE DETAILDATE - - 24-MAY-07 2007/05/24 13:47:32,使用TO_DATE函数举例,上面的例子，如果不用TO_DATE进行转换，会怎么样呢？,SQL SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE=TO_DATE(19820101,YYYYMMDD); ENAME HIREDATE - - SCOTT 09-DEC-82 ADAMS

41、12-JAN-83 MILLER 23-JAN-82,SQL SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE=19820101; SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE=19820101 * ERROR at line 1: ORA-01861: literal does not match format string,因为19820101不是采用默认日期格式写的，Oracle无法进行隐含转换，导致报错。因此可以看出，对于可能发生转换的语句，应该显式指定转换,NVL函数,将空值转换为实际的值 数据格

42、式可以是日期，字符，数字 数据类型必须匹配,NVL(comm,0)：如果comm为空，则转换为0 NVL(hiredate,01-JAN-97)：如果hiredate为空，则转换为01-JAN-97 NVL(job,No Job Yet) ：如果job为空，则转换为No Job Yet,使用NVL,SQL SELECT ename , comm, sal , sal+300,(sal*12)+NVL(comm,0) FROM emp; ENAME COMM SAL SAL+300 (SAL*12)+NVL(COMM,0) - - - - - SMITH 800 1100 9600 ALLEN 300 1600 1900 19500 WARD 500 1250 1550 15500 JONES 2975 3275 35700 MARTIN 1400 1250 1550 16400 BLAKE 2850 3150 34200 CLARK 2450 2750 29400 SCOTT 3000 3300 36000 ,1,2,3,4,较复杂查询,多表查询,分组及函数,子查询,目 录,使用子查询解决问题,假设想知道谁的工资