OracleSQL实用基础教程.ppt

《OracleSQL实用基础教程.ppt》由会员分享，可在线阅读，更多相关《OracleSQL实用基础教程.ppt（144页珍藏版）》请在三一办公上搜索。

1、王忠海2023/11/8,Oracle SQL 实用基础教程,SQL概述,历史SQL:Struceured Query Language1974年，由Boyce和Chamber提出1975-1979年，在System R上实现，由IBM的San Jose研究室研制，称为Sequel,SQL概述,标准化有关组织ANSI(American Natural Standard Institute)ISO(International Organization for Standardization)有关标准SQL-86“数据库语言SQL”SQL-89“具有完整性增强的数据库语言SQL”，增加了对完整性约

2、束的支持SQL-92“数据库语言SQL”，是SQL-89的超集，增加了许多新特性，如新的数据类型，更丰富的数据操作，更强的完整性、安全性支持等。SQL-99正在讨论中的新的标准，将增加对面向对象模型的支持,SQL概述,特点一体化集DDL，DML，DCL于一体单一的结构-关系，带来了数据操作符的统一面向集合的操作方式一次一集合高度非过程化用户只需提出“做什么”，无须告诉“怎么做”，不必了解存取路径两种使用方式，统一的语法结构SQL既是自含式语言（用户使用），又是嵌入式语言（程序员使用）语言简洁，易学易用,SQL概述,1 SQL命令基础,准备工作,用SQLPLUS来学习SQL连接到SQLPLUS，

3、创建学习用的用户和数据SCOTT用户在ORACLE805中默认已经创建，在8i和9i中需要手动运行rdbmsadminscott.sql。是ORACLE安装的主目录，在SQLPLUS中可以用?来代替。例如：SQL connect/as sysdbaConnected.SQL?rdbmsadminscott.sqlSQLconnect scott/tigerConnected.,基本的SELECT命令,SELECT命令用于从数据库中获得想要的信息。语法：SELECT*,column alias,FROM table;一个最简单的查询语句至少要包括SELECT子句和FROM子句：SELECT后面指

4、定要选择的列FROM后面指定从哪些表或视图中获取数据,SQL语句基本规则,SQL命令是大小写不敏感 SQL命令可写成一行或多行一个关键字不能跨多行或缩写子句通常位于独立行，以便编辑，并易读空格和缩进使程序易读关键字大写，其他小写,使用SELECT*可显示所有的列,SQL SELECT*FROM dept;DEPTNO DNAME LOC-10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON,选择指定的列,我们可以在SELECT后面指定要选择的列。,SQL SELECT deptno,loc

5、 FROM dept;DEPTNO LOC-10 NEW YORK 20 DALLAS 30 CHICAGO 40 BOSTON,在SQL*PLUS中查看表具有哪些列,在SQLPLUS中，用DESC TABLENAME命令可以查看表具有的列以及类型等,SQL desc dept 名称 是否为空?类型-DEPTNO NUMBER(2)DNAME VARCHAR2(14)LOC VARCHAR2(13),从数据字典中获取表的列信息,SQL SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,NULLABLE FROM USER_TAB_COLUM

6、NS WHERE TABLE_NAME=DEPT;TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH NULLABLE-DEPT DEPTNO NUMBER 22 YDEPT DNAME VARCHAR2 14 YDEPT LOC VARCHAR2 13 Y,使用算术运算,可以对数字类型的字段进行算术运算。运算的先后顺序是先乘除，后加减，括号优先。上面例子中，计算的是一年的报酬，月工资乘以12个月，再加上100,SQL SELECT ename,sal,12*sal+100 FROM emp;ENAME SAL 12*SAL+100-KING 5000 6

7、0100BLAKE 2850 34300CLERK 2450 29500JONES 2975 3580014 rows selected。,关于空值(NULL),空值是指不可用，不知道，不适用的值空值不等于零也不等于空格 对空值进行的任何运算仍然为空值,SQL SELECTename NAME,12*salcomm FROM emp WHERE ename KING;NAME 12*SAL+COMM-KING,定义列的别名,当显示查询结果时，SQL*PLUS通常使列名作为列头。在很多情况下，列名并非是此列的清晰描述。因此我们就可使用列的别名作为列头，。缺省情况下，列的别名是大写的。如果区别大小

8、写，可加双引号，如有特殊字符如$、#也必须使用双引号将其括起来。上面的例子中的AS可以省略,SQL SELECT ename AS name,sal salary FROM emp;NAME SALARY-,SQL SELECT ename Name,sal*12 Annual Salary FROM emp;NAME Annual Salary-,列连接操作,使用双竖条”|”操作符，可将列和运算表达式常量连起来显示，形成一个输出显示,SQL SELECT ENAME|s salary is|sal Employees Salary from emp;Employees Salary-SMIT

9、Hs salary is 800ALLENs salary is 1600WARDs salary is 1250JONESs salary is 2975.已选择14行。,去除重复记录,默认情况下，显示所有行，包括重复记录。如果想去掉重复记录，可以在DISTINCT关键字。如果在DISTINCT后面指定了多个列，则DISTINCT将对所有被选择的列有效，其结果是不同的列的组合。,SQL SELECT deptno FROM emp;DEPTNO-10 30 1014 rows selected。,SQL SELECT DISTINCT deptno FROM emp;DEPTNO-10 20

10、 30,SQL*PLUS程序介绍,SQL*PLUS是是Oracle自带的与Oracle交互的一个工具。你可在SQL*PLUS中做以下操作：执行SQL命令来修改、查询、增加、删除数据库中的数据格式化、计算、存储、数据于一定格式的报告中产生用于存储SQL命令的脚本，以便以后执行SQL*PLUS专用命令可被分为如下类别：环境：影响通常的SQL命令格式化：格式化查询结果文件处理：存储、调用、运行脚本文件编辑：修改SQL缓存中的SQL命令显示列的定义,登陆到SQL*PLUS,在命令行提示符下输入SQLPLUS/NOLOG然后在SQL提示符下输入CONNECT USERNAME/PASSWORDDATAB

11、ASE,C:Documents and SettingsAdministratorSQLPLUS/NOLOGSQL*Plus:Release 9.2.0.4.0-Production on 星期四 5月 24 09:51:42 2007Copyright(c)1982,2002,Oracle Corporation.All rights reserved.SQL connect system/managertlgaxz已连接。,SQL*PLUS的编辑命令,SQL*PLUS的命令每次只能键入一行，且不能存于SQL缓存中APPEND text 将text加到当前行的后面CHANGE/old/new

12、 将当前行的old改为newCHANGE/text/从当前行中删除textCLEAR BUFFER 从缓存中删除所有的行DEL 删除当前行 LIST：列 出 SQL 缓 存 中 的 所 有 行LIST n：列 出 一 行(由 n 指 出 列 出 的 行)RUN：显 示 并 运 行 SQL 缓 存 中 的 SQL 语 句,SQL*PLUS的编辑命令(续),GET filename.ext：把文件的内容写到SQL缓存 filename.ext：运行文件 EDIT：调用编辑器编辑当前缓存中的内容 EDIT filename.ext：调用编辑器编辑存的文件 SPOOL filename.ext：将查询

13、结果存于文件中 SPOOL OFF 结束结果内容输出 EXIT：退出SQL*PLUS,2限定和排序数据,目的,限制某一查询所取记录排序查询结果,使用选择限定记录,在上面的例子中，假定你想要显示部门10的所有员工，这种方式是基于WHERE子句的SQL命令。,使用where子句限定返回的记录,WHERE子句在FROM子句后面Condition:由列名表达式，常量和比较操作符组成。,SELECT DISTINCT*,column alias,FROM tableWHERE condition(s);,使用WHERE语句,上面例子中返回job=CLERK的所有员工的name,job和deptno注意：

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

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

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

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

18、 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 NOT3 AND4 OR括号将跨越所有优先级,使用AND运算符,AND需要条件都满足,SQL SELECT empno,ename,job,sal FROM emp WHERE sal=1100 AND job=CLERK；EMPNO EN

19、AME 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 7645 MARTIN MANAGER 1050,使用NOT运算符,SQL SELECT ename,job FROM emp WH

20、ERE job NOT IN(CLERK,MANAGER,ANALYST);ENAME JOB-KING PERSIDENTMARTIN SALESMANWARD SALESMAN,ORDER BY 语句,在缺省情况下，查询返回的结果是没被排序的。使用ORDER BY子可将记录排序。ORDER BY 子句放在最后。ASC表示升序排序，DESC表示降序排序，缺省为ASC,SELECT expr FROM table WHERE condition ORDER BY column,expr ASC|DESC,降序排列,SQL select ename,hiredate from emp order

21、 by hiredate desc;ENAME HIREDATE-FOR_TESTADAMS 12-JAN-83SCOTT 09-DEC-82MILLER 23-JAN-82JAMES 03-DEC-81SMITH 17-DEC-8015 rows selected.,使用列别名排序,SQL select empno,ename,sal*12 annual from emp order by annual;EMPNO ENAME ANNUAL-7369 SMITH 9600 7900 JAMES 11400 7876 ADAMS 13200 7521 WARD 15000 7654 MARTI

22、N 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 10CLARK 2450 10MILLER 1300 10SCOTT 3000 20FORD 3000 20JONES 2975 20ADAMS 1100 20.15 rows selected.,3 单行函数,SQL函数,SQL函数有两种不同的SQL函数单行函数多行函数单行函数

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

24、换为小写UPPER(column|expression):将字符转换不大写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):在字符串前填补字符,使

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

26、EN ALL ALLEN*5WARD WAR WARD*4JONES JON JONES*5MARTIN 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 DUAL;ROUND(

27、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(sal,comm)FROM emp WHERE job=SALE

28、SMAN;ENAME SAL COMM MOD(SAL,COMM)-ALLEN 1600 300 100WARD 1250 500 250MARTIN 1250 1400 1250TURNER 1500 0 1500,使用日期函数,Oracle的日期函数 Oracle使用内部的数字化格式存储日期，它们代表世纪、年、月、日、小时、分钟和秒.缺省显示的日期格式为DD-MON-YY，有效的日期在公元前4712年1月1日到公元后9999年12月31日 SYSDATE SYSDATE是一个返回当前日期和时间的日期函数 DUAL DUAL是一个SYS用户所拥有的表，所有的用户都可以访问。它包括一个列DUM

29、MY和一条记录值为X。例子：显示当前的日期 SQLSELECT SYSDATE FROM SYS.DUAL;SYSDATE-24-MAY-07,日期运算,日期+数字=日期 加天数日期-数字=日期 减天数日期-日期=数字 两日期间的天数日期+number/24=日期 加小时 注意：两个日期类型字段不能相加,日期运算举例,SQL SELECT ENAME,SYSDATE-HIREDATE FROM EMP;ENAME SYSDATE-HIREDATE-SMITH 9654.54954ALLEN 9589.54954WARD 9587.54954JONES 9548.54954MARTIN 9369

30、.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,转换函数,转换函数用于数据类型之间的转换。SQL尽可能地自动进行转换，它会隐含地调用转换函数。但是你无法对隐含调用中使用的格式指定符进行控制，并且这会使得你的代码很难理解。因此使用显式转换函数而

31、不依赖于隐式转换是一个很好的程序设计风格。,转换函数,Oracle提供了3个转换函数 TO_CHAR(number|date,fmt):将数字或日期按格式转换成字符 TO_NUMBER(char):将字符串转换成数字，此字符串必须是数字 TO_DATE(CHAR,fmt)：将字符串按指定的格式转换成日期,使用TO_CHAR操作日期函数举例,上面的例子中：YYYY代表4位的年份，MM代表月份，HH24代表24小时，MI代表分钟，SS代表秒。,SQL SELECT SYSDATE,TO_CHAR(SYSDATE,YYYY/MM/DD HH24:MI:SS)DETAILDATE FROM DUAL;

32、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-82ADAMS 12-JAN-83MILLER 23-JAN-82,SQL SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE=19820101;SELECT ENA

33、ME,HIREDATE FROM EMP WHERE HIREDATE=19820101*ERROR at line 1:ORA-01861:literal does not match format string,因为19820101不是采用默认日期格式写的，Oracle无法进行隐含转换，导致报错。因此可以看出，对于可能发生转换的语句，应该显式指定转换,NVL函数,将空值转换为实际的值数据格式可以是日期，字符，数字数据类型必须匹配,NVL(comm,0)：如果comm为空，则转换为0NVL(hiredate,01-JAN-97)：如果hiredate为空，则转换为01-JAN-97NVL(j

34、ob,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 9600ALLEN 300 1600 1900 19500WARD 500 1250 1550 15500JONES 2975 3275 35700MARTIN 1400 1250 1550 16400BLAKE 2850 3150 34200CLARK 2450 27

35、50 29400SCOTT 3000 3300 36000,4从多个表中选择数据,从多个表中获取数据,有时候你需要从多个表中获得数据。在上面的例子中，报告显示的数据取自两个表。EMPNO存在于EMP表中，DEPTNO在EMP和DEPT表中都有，LOC存在于DEPT表中为了生成上面的报告，你需要将表EMP和DEPT连起来，从两个中获取数据。,定义连接,当从两个以上的表中获取数据时，就要使用连接条件。一个表中的记录可以根据两个表的相同列和另一个表的记录相连接。两表中的相同列一般是主键和外键列。为了能显示两个或多个表中的数据，在WHERE子句中需要设简单的连接条件。语法如下：table.column

36、 指定取数据的表和它的列 table.column1=table2.column2 将表连接起来的条件当写一个有连接的SELECT命令时，为了避免同样的不同表具有同样的列名，应该在列前加表的名字或者表的别名。当在表中有相同的列名时，并且这个列作为显示内容或者查询条件，必须在列名前加表名或表的别名作为前缀。如果要将n个表连起来，你必须指定n-1个连接条件。因此连接4个表需要有3个连接条件。如果你的表有组合主键，此规则可能不适用，此时多一条记录需要多个列唯一标识。,笛卡尔结果,笛卡尔结果笛卡尔结果形成于：-连接条件被省略-连接条件无效-第一个表的所有记录连接到第二个表的所有记录一个笛卡尔结果趋于产

37、生一个巨大的记录数，通常没有意义。为了避免笛卡尔结果我们要在WHERE子句中使用有效连接,笛卡尔结果举例,SELECT*FROM EMP,DEPT;,连接类型,有两种主要的连接条件 等值连接 非等值连接其它的连接方式包括 多连接 自连接 定置运算符,什么是等值连接,就是两个表连接的WHERE条件是一个表的列等于另外一个表的列。通常情况下，这种连接是主键和外键的连接。,使用等值连接获取记录举例,因为DEPTNO列在EMP和DEPT中都存在，因此需要在这个列前面加上表名，否则Oracle认为有歧义，语句无法执行,SQL SELECT ENAME,EMP.DEPTNO,DNAME FROM EMP,

38、DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO;ENAME DEPTNO DNAME-MILLER 10 ACCOUNTINGKING 10 ACCOUNTINGCLARK 10 ACCOUNTINGFORD 20 RESEARCHADAMS 20 RESEARCHSCOTT 20 RESEARCH,额外的条件,除了连接条件，可能还有额外的查询条件。例如，显示员工King的员工号、各字、部门号和部门位置，这时在WHERE子句中需要设定一个额外的条件。,SQLSELECT empno,ename,emp.deptno,Loc FROM emp,dept WHERE emp.d

39、eptno=dept.deptno AND INITCAP(ename)=King;EMPNO ENAME DEPTNO LOC-7839 KING 10 NEW YORK,使用表的别名,表的别名 使用表名限定列名可能会很浪费时间，尤其是当表名特别长，这时你可以使用表的别名。使用表的别名会减少程序代码，因此占用较少的内存。注意，表的别名是在FROM子句中指定的。表别名规则：表的别名最长为30个字符，但通常以短字符为佳 表的别名最好有一定的含义 表的别名只在当前的SELECT语句有效 如果在FROM子句定义了表的别名，在SELECT子句中必须用它来替代表名。,SQLSELECT empno,en

40、ame,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列的值在SALGRADE表的LOSAL和HISAL列之间，它们是不等值连接。,非等值连接举例,SQL SELECT e.sal,e.ename,s.grade FROM emp e,salgrade s WHERE e.sal BETWEEN s.l

41、osal 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.,外连接,使用外部连接，返回连接两边有一边为NULL的记录外连接运算符是加号(+)外连接运算符(+)可以加在左边,也可以加在右边，但不能两边同时加外连接 有(+)的一边表示这边的值要么等于另外一边，要么为NULL从9i开始，SQL支持ANSI SQL，也就是支持LEFT OUTER JOIN、RIG

42、HT 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 table1,table2 WHERE table1.column=table2.column(+);,外连接举例1：,这个例子(+)在e.deptno这边，意味着e.deptno可以是NULL,SQL SELECT ename,e.deptno E.DEPTNO,d

43、.deptno D.DEPTNO,d.dname FROM EMP E,DEPT D WHERE E.DEPTNO(+)=D.DEPTNO;ENAME E.DEPTNO D.DEPTNO DNAME-SMITH 20 20 RESEARCHALLEN 30 30 SALES.FORD 20 20 RESEARCHMILLER 10 10 ACCOUNTING 40 OPERATIONS15 rows selected.,外连接举例2,这个例子(+)在d.deptno这边，意味着d.deptno可以是NULL,SQL SELECT ename,e.deptno E.DEPTNO,d.deptno

44、 D.DEPTNO,d.dname FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO(+);ENAME E.DEPTNO D.DEPTNO DNAME-MILLER 10 10 ACCOUNTINGKING 10 10 ACCOUNTING.ALLEN 30 30 SALESwzh 90FOR_TEST 9016 rows selected.,外连接举例3,从9i开始，可以用ANSI SQL语法来写外连接，这样也提供了一个以前的(+)不能实现的功能：全外连接,SQL SELECT E.ENAME,E.DEPTNO E.DEPTNO,D.DEPTNO D.DEP

45、TNO,D.DNAME FROM EMP E FULL OUTER JOIN DEPT D ON(E.DEPTNO=D.DEPTNO);ENAME E.DEPTNO D.DEPTNO DNAME-MILLER 10 10 ACCOUNTINGKING 10 10 ACCOUNTING.WARD 30 30 SALESALLEN 30 30 SALESwzh 90FOR_TEST 90 40 OPERATIONS17 rows selected.,表的自连接,有的时候，需要对表进行自连接。例如上图所示，EMP表中的MGR列的代表员工的经理的员工编号，所以要想显示出每个员工的经理就要对EMP进行自

46、连接，用MGR=EMPNO。实际上，自连接只是等连接（等外连接）的一个特例，同一张表用不同的别名，区别成了不同的表。,表自连接举例,上面这个例子相当一个等外连接。如果员工有经理，则显示出员工为谁工作（打工者），否则显示员工为自己干活（老板）,SQL 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 KINGSMITH works for FORDKING work

47、s for himself14 rows selected.,5使用分组函数,什么是分组函数,和单行函数不同，分组函数作用于一组记录，每一组返回一个结果。这些组可能是整个表，也可能是由GROUP BY子句将表分成的多个组。,主要的分组函数,COUNT(*/DISTINCE/ALLexpr)返回记录数，这里expr赋非空值，*表示所有被选择的记录，包括重复记录和空值。MAX(DISTNCT/ALLexpr)：表达式的最大值，忽略空值MIN(DISTNCT/ALLexpr)：表达式的最小值，忽略空值AVG(DISTNCT/ALLexpr)：平均值，忽略空值STDDEVDISTINCT/ALLX)：

48、返回标准差，忽略空值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)AVG(SAL)-29025 5000 800 2073.21429,使用分组函数举例2：,SQL SELECT COUNT(*)FROM EMP;COUNT(*)-16SQL SELECT COUNT(EMPNO

49、)FROM EMP;COUNT(EMPNO)-16SQL SELECT COUNT(MGR)FROM EMP;COUNT(MGR)-13,产生数据组：GROUP BY子句,GROUP BY子句使用GROUP BY 子句将一个表分成许多小组，并对每一个小组返回一个计算值。Group_by_expression：指定按什么列分组规则：在SELECT子句中，如果使用分组函数，不能对GROUP BY子句中指定的列使用分组函数。使用WHERE子句，可预先排除某些记录在GROUP BY 子句中必须有表中的列在GROUP BY子句中不能使用列的别名缺省情况下在GROUP BY子句中的列以升序排，你可以使用o

50、rder by子句改变它。,SELECT column,group_functionFROM tableWHEREconditionGROUP BY group_by_expressionORDER 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