《Oracle数据库应用》课件9.ppt

1、第第9 9章章 OracleOracle支持的支持的SQLSQL查询查询虚表和伪列虚表和伪列1基本查询基本查询2Oracle支持的支持的SQL函数函数3高级查询高级查询4n 当今的数据处理可分成两大类：当今的数据处理可分成两大类：l联机事务处理联机事务处理OLTPOLTP(on-line transaction(on-line transaction processing)processing)OLTPOLTP是传统的关系型数据库的主要应用，主要是基本的、是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，如银行交易日常的事务处理，如银行交易l联机分析处理联机分析处理OLAPOLAP

2、(On-Line Analytical(On-Line Analytical Processing)Processing)OLAPOLAP是数据仓库系统的主要应用，支持复杂的分析操作，是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果侧重决策支持，并且提供直观易懂的查询结果OLTP OLTP OLAP OLAP 用户用户操作人员操作人员,低层管理人员低层管理人员决策人员决策人员,高级管理人员高级管理人员功能功能日常操作处理日常操作处理 分析决策分析决策DBDB设计设计面向应用面向应用面向主题面向主题数据数据当前的当前的,最新的细节的，最新的细节的，二维的分立

3、的二维的分立的 聚集的聚集的,多维的集成的多维的集成的,统一的统一的存取存取读读/写数十条记录写数十条记录读上百万条记录读上百万条记录工作单位工作单位简单的事务简单的事务 复杂的查询复杂的查询用户数用户数上千个上千个 上百个上百个DB DB 大小大小100MB-GB100MB-GB100GB-TB100GB-TBlOLTPOLTP与与OLAPOLAP之间的比较之间的比较：9.1 9.1 虚表和伪列虚表和伪列n OracleOracle的伪列和虚表的伪列和虚表l虚表虚表(伪表伪表)DUALDUALw 该表是为了保证在使用该表是为了保证在使用SELECTSELECT语句中的语句的完整性而提供语句中

4、的语句的完整性而提供的的l伪列伪列(Pseudo column)(Pseudo column)物理上并不存在，但却想查看的伪造的假列，即：只是在物理上并不存在，但却想查看的伪造的假列，即：只是在查询时才构造出来查询时才构造出来比如：比如：w CURRVAL/NEXTVAL CURRVAL/NEXTVAL 使用序列号的保留字使用序列号的保留字w LEVEL LEVEL 查询数据所对应的级查询数据所对应的级w ROWID ROWID 记录的唯一标识记录的唯一标识w ROWNUM ROWNUM 限制查询结果集的数量限制查询结果集的数量lSQL SERVERSQL SERVER不支持伪列不支持伪列/伪

5、表伪表9.2 9.2 基本查询基本查询n SELECTSELECT语句完成如下运算：语句完成如下运算：l投影投影(Projection)Projection)选取指定的列选取指定的列(字段字段)在在SELECTSELECT子句中列出所需的列子句中列出所需的列(字段字段)名名l选择选择(Selection)Selection)选取指定的行选取指定的行(记录记录)无条件选取全部行或选无条件选取全部行或选WHEREWHERE子句条件所限定的行子句条件所限定的行l连接连接(Joining)Joining)将多个表的数据连接在一起将多个表的数据连接在一起在在FROMFROM子句中列出多个表名然后在子句中

6、列出多个表名然后在WHEREWHERE子句中指定连接条子句中指定连接条件件由由JoinJoin指定参与连接的多个表，然后在指定参与连接的多个表，然后在ONON子句中指定连接子句中指定连接条件条件n SELECTSELECT语句基本查询语句基本查询l一般将各子句单独成行书写，并采用缩进格式一般将各子句单独成行书写，并采用缩进格式l选择表中所有列选择表中所有列*(星号星号)或在或在SELECTSELECT子句中列出所有子句中列出所有的字段的字段l消除重复出现的行：使用消除重复出现的行：使用DISTINCTDISTINCT限定词限定词l要善于使用别名要善于使用别名(alias)alias)可重命名表

7、名、列名以改善查询结果的可读性可重命名表名、列名以改善查询结果的可读性若别名中使用空格或需区分大小写时，应使用双引号若别名中使用空格或需区分大小写时，应使用双引号两种定义别名方法：两种定义别名方法：w 别名直接写在列名或列表达式之后别名直接写在列名或列表达式之后w 用用ASAS定义，将别名放在定义，将别名放在ASAS关键字之后关键字之后l字符串和日期常量需用单引号括起来字符串和日期常量需用单引号括起来l字符串常量区分大小写字符串常量区分大小写l在在SELECTSELECT子句中可使用运算符和子句中可使用运算符和SQLSQL函数构造列表达函数构造列表达式，在取出数据同时进行有关的运算式，在取出数

8、据同时进行有关的运算lFROMFROM后可以连接的数据对象，即从何处取数据后可以连接的数据对象，即从何处取数据表表表分区表分区视图视图远程数据库远程数据库结果集结果集l查询结果默认的排序方式为升序查询结果默认的排序方式为升序ASCASC数值型从小到大数值型从小到大日期型按年份从小到大日期型按年份从小到大字符型按字母表顺序字符型按字母表顺序空值排在最后空值排在最后l统计函数统计函数对一组查询行返回一个结果值对一组查询行返回一个结果值除除count(count(*)外，分组函数均忽略空值外，分组函数均忽略空值若要包括空值，须使用空值转换函数若要包括空值，须使用空值转换函数不可用在不可用在WHERE

9、WHERE子句中用以限定查询的结果，对分组查询结子句中用以限定查询的结果，对分组查询结果的限定应使用果的限定应使用HAVINGHAVING子句子句可有下面两个选项：可有下面两个选项：w(1)(1)DISTINCTDISTINCT选项：使分组函数只考虑列表达式中的不同值选项：使分组函数只考虑列表达式中的不同值w(2)(2)ALLALL选项：使分组函数考虑全部值，其中包含重复值选项：使分组函数考虑全部值，其中包含重复值主要的统计函数主要的统计函数l数据分组数据分组GROUP BYGROUP BY子句对记录分组并执行相应数据聚集运算子句对记录分组并执行相应数据聚集运算在在SELECT SELECT

10、子句的列名表中的所有非分组函数计算列必须出子句的列名表中的所有非分组函数计算列必须出现在现在GROUP BYGROUP BY子句中子句中使用使用HAVINGHAVING子句对数据分组后的返回结果进行限制子句对数据分组后的返回结果进行限制HAVINGHAVING子句应放在子句应放在GROUP BYGROUP BY子句之后子句之后group bygroup by子句子句w 需要将表中的数据按照某些字段值分组，然后对每组的数据需要将表中的数据按照某些字段值分组，然后对每组的数据进行统计，得到多个汇总结果进行统计，得到多个汇总结果w 用于分组的表达式里的字段，可以是用于分组的表达式里的字段，可以是1

11、1个也可是多个个也可是多个w 使用规则使用规则 每组只产生一个汇总结果，每组只返回一行每组只产生一个汇总结果，每组只返回一行 selectselect子句中只能有子句中只能有2 2种类型的表达式：一是出现在种类型的表达式：一是出现在group bygroup by子句中的字段或是它的非统计函数表达式；另一种是其他非分子句中的字段或是它的非统计函数表达式；另一种是其他非分组字段的统计函数组字段的统计函数havinghaving子句子句w 使使用用group bygroup by和统计函数对记录分组后，还可使用和统计函数对记录分组后，还可使用havinghaving对对分组后的结果进一步筛选分组后

12、的结果进一步筛选w 使使用规则用规则 当查询语句当查询语句selectselect中同时存在中同时存在where/group by/havingwhere/group by/having时，执时，执行顺序为行顺序为where-group by-havingwhere-group by-having。其写法如下：。其写法如下：select select from from wherewheregroup bygroup byhavinghavingl分页查询分页查询查询出来的数据按每页多少条进行显示查询出来的数据按每页多少条进行显示需要使用伪列需要使用伪列ROWNUMROWNUM使用子查询嵌套的

13、方式使用子查询嵌套的方式语法：语法：SELECT SELECT*FROM FROM(SELECT A.(SELECT A.*,ROWNUM RN,ROWNUM RN FROM FROM(SELECT(SELECT*FROM table)A FROM table)A WHERE ROWNUM=number_high WHERE ROWNUM=number_low;WHERE RN=number_low;例题：例题：P143-148P143-148n 字符串函数字符串函数LOWER(char)UPPER(char)INITCAP(char)CONCAT(char1,char2)SUBSTR(cha

14、r,m ,n)LENGTH(char)INSTR(char1,char2,m,n)LPAD(char1,n,char2)RPAD(char1,n,char2)TRIM(leading|trailing|both,trim_char FROM trim_source)REPLACE(char1,char2,char3)9.3 Oracle9.3 Oracle支持的支持的SQLSQL函数函数n 数学函数数学函数ABS(n)CEIL(n)COS(n)COSH(n)EXP(n)LN(n)LOG(m,n)MOD(m,n)POWER(m,n)FLOOR(n)ROUND(m,n)SIGN(n)SIN(n)S

15、INH(n)SQRT(n)TAN(n)TANH(n)TRUNC(m,n)n 日期函数日期函数lMONTHS_BETWEEN(d1,d2)MONTHS_BETWEEN(d1,d2)lADD_MONTHS(d,n)ADD_MONTHS(d,n)lNEXT_DAY(d,s)NEXT_DAY(d,s)lLAST_DAY(d)LAST_DAY(d)lROUND(date,fmt)ROUND(date,fmt)lTRUNC(date,fmt)TRUNC(date,fmt)n OracleOracle的类型转换的类型转换l自动类型转换：字符型和日期型、字符型和数值型自动类型转换：字符型和日期型、字符型和数值

16、型间间l强制类型转换：使用类型转换函数强制类型转换：使用类型转换函数n 常用的转换函数常用的转换函数lTO_CHAR(NUMBER|date,format)TO_CHAR(NUMBER|date,format)lTO_NUMBER(char,format)TO_NUMBER(char,format)lTO_DATE(char,format)TO_DATE(char,format)例题：例题：P149-151P149-151n 1.1.表的连接表的连接l等值连接与非等值连接等值连接与非等值连接SELECT.SELECT.FROM table1,table2 FROM table1,table2

17、WHERE table1.column1 WHERE table1.column1 operoper table2.column2;table2.column2;等值连接指参与连接的多个表将连接条件列值相同的记录等值连接指参与连接的多个表将连接条件列值相同的记录连接在一起作为查询结果记录返回连接在一起作为查询结果记录返回等值连接，运算符等值连接，运算符operoper为为=非等值连接，非等值连接，operoper可为可为!=!=、=等等例题：例题：P152P1529.4 9.4 高级查询高级查询l自连接（自身连接）自连接（自身连接）出于某种业务逻辑的需要，将一个表看成两个副本进行关出于某种业务

18、逻辑的需要，将一个表看成两个副本进行关联查询联查询将两个副本赋予不同的别名，使用别名构造连接条件将两个副本赋予不同的别名，使用别名构造连接条件l外连接外连接增加空行将不满足连接条件的记录也返回增加空行将不满足连接条件的记录也返回SELECT.SELECT.FROM table alias1,table alias2FROM table alias1,table alias2WHERE alias1.column1(+)=alias2.column2 WHERE alias1.column1(+)=alias2.column2|alias1.column1=alias2.column2(+)|a

19、lias1.column1=alias2.column2(+);(+)(+)所在位置的另一侧为连接的方向所在位置的另一侧为连接的方向左外连接左外连接alias1.column1=alias2.column2(+)alias1.column1=alias2.column2(+)说明等号左侧说明等号左侧的所有记录均会被显示的所有记录均会被显示右外连接右外连接alias1.column1(+)=alias2.column2alias1.column1(+)=alias2.column2说明等号右侧说明等号右侧的所有记录均会被显示的所有记录均会被显示SQL ServerSQL Server中用中用*表

20、示外连接表示外连接1.left outer join1.left outer join：左外连接：左外连接=左连接左连接select e.last_name,e.department_id,select e.last_name,e.department_id,d.department_named.department_namefrom employees e from employees e left outer joinleft outer join departments d departments donon(e.department_id=d.department_id);(e.dep

21、artment_id=d.department_id);等价于等价于select e.last_name,e.department_id,select e.last_name,e.department_id,d.department_named.department_namefrom employees e,departments dfrom employees e,departments dwhere e.department_id where e.department_id=d.department_idd.department_id(+)(+);结果为：所有员工及对应部门的记录，包括没有

22、对应部门编结果为：所有员工及对应部门的记录，包括没有对应部门编号号department_iddepartment_id的员工记录，即等号左侧的所有记录均的员工记录，即等号左侧的所有记录均会被显示会被显示2.right outer jion2.right outer jion：右外连接：右外连接=右连接右连接select e.last_name,e.department_id,select e.last_name,e.department_id,d.department_named.department_namefrom employees e from employees e right ou

23、ter joinright outer join departments d departments donon(e.department_id=d.department_id);(e.department_id=d.department_id);等价于等价于select e.last_name,e.department_id,select e.last_name,e.department_id,d.department_named.department_namefrom employees e,departments dfrom employees e,departments dwhere

24、e.department_id where e.department_id(+)=(+)=d.department_id;d.department_id;结果为：所有员工及对应部门的记录，包括没有任何员工的结果为：所有员工及对应部门的记录，包括没有任何员工的部门记录，即等号右侧的所有记录均会被显示部门记录，即等号右侧的所有记录均会被显示3.full outer join3.full outer join：全外连接：全外连接select e.last_name,e.department_id,select e.last_name,e.department_id,d.department_name

25、d.department_namefrom employees e from employees e full outer joinfull outer join departments d departments donon(e.department_id (e.department_id=d.department_id);d.department_id);结果为：所有员工及对应部门的记录，包括没有对应部门编结果为：所有员工及对应部门的记录，包括没有对应部门编号号department_iddepartment_id的员工记录和没有任何员工的部门记录的员工记录和没有任何员工的部门记录l使用连接

26、时，应注意以下几点：使用连接时，应注意以下几点：(1)(1)建议在列名之前使用表名前缀以改善运行性能建议在列名之前使用表名前缀以改善运行性能(2)(2)对表使用简短的别名可改善连接性能对表使用简短的别名可改善连接性能(3)(3)使用准确的连接条件和使用准确的连接条件和WHEREWHERE子句条件可显著改善连接子句条件可显著改善连接性能，性能，不使用无条件的连接不使用无条件的连接(即笛卡儿积即笛卡儿积)(4)(4)应对同名的列冠以表名前缀以告知数据库该列取自哪个应对同名的列冠以表名前缀以告知数据库该列取自哪个表表n 2.2.子查询子查询l嵌入在其他嵌入在其他SQLSQL语句中的语句中的SELEC

27、TSELECT语句语句l分类：分类：简单子查询：用独立的条件返回值简单子查询：用独立的条件返回值相关子查询：子查询相关子查询：子查询WHEREWHERE条件中用到了嵌套它的条件中用到了嵌套它的SQLSQL语句语句的当前记录值的当前记录值单行子查询：仅返回一条记录单行子查询：仅返回一条记录多行子查询：返回多条记录多行子查询：返回多条记录l当子查询出现在当子查询出现在WHEREWHERE子句中时子句中时WHERE expr WHERE expr operatoroperator (SELECT select_list (SELECT select_list FROM table FROM tabl

28、e ););l单行比较运算符：单行比较运算符：、=、=、=等等l多行比较运算符：多行比较运算符：ININ、ANYANY、ALLALL、EXISTSEXISTSANYANY：某一记录：某一记录ALLALL：所有记录：所有记录ANY/ALLANY/ALL与关系运算符一起使用的含义与关系运算符一起使用的含义注：使用注：使用EXISTSEXISTS的执行效率较高，只需返回一个布尔值的执行效率较高，只需返回一个布尔值l相关子查询相关子查询CREATE TABLE AS SELECT,.FROM WHERE (SELECT FROM WHERE );INSERT INTO (,.)SELECT,.FROM

29、 WHERE (SELECT FROM WHERE );UPDATE SET(,.)=(SELECT,.FROM WHERE )WHERE (SELECT FROM WHERE );DELETE FROM WHERE (SELECT FROM WHERE);SELECT.FROM WHERE (SELECT FROM WHERE);例题：例题：P154-155P154-155n 3.3.集合操作集合操作lSELECTSELECT语句中的集合运算包括语句中的集合运算包括并并UNION/UNION/交交INTERSECT/INTERSECT/差差MINUSMINUSl所有集合运算符具有相同的优先级

30、所有集合运算符具有相同的优先级l按照从左按照从左(上上)至右至右(下下)的顺序执行的顺序执行l可用括号改变运算的优先级可用括号改变运算的优先级l各各SELECTSELECT语句中的列数和字段类型必须相同语句中的列数和字段类型必须相同l作为作为DBADBA，要对数据的变化敏感，当查询结果与预期，要对数据的变化敏感，当查询结果与预期不符时，需查找问题所在不符时，需查找问题所在l对集合运算结果的排序应遵循以下规则：对集合运算结果的排序应遵循以下规则：(1)(1)ORDER BYORDER BY子句只能出现在整个语句的最后，且只能用一子句只能出现在整个语句的最后，且只能用一次次(2)(2)ORDER

31、BYORDER BY子句使用的列名或别名只能取自第一个子句使用的列名或别名只能取自第一个SELECTSELECT语句语句(3)(3)ORDER BYORDER BY子句可使用列的位置序号表示列名子句可使用列的位置序号表示列名(4)(4)默认以第一个默认以第一个SELECTSELECT语句的第一列的升序显示语句的第一列的升序显示l并并UNION UNION UNIONUNION：返回多个查询中消除重复行以后的结果返回多个查询中消除重复行以后的结果UNION ALLUNION ALL：返回每个查询语句得到的结果行返回每个查询语句得到的结果行 不消除多个查询得到结果中的重复行不消除多个查询得到结果中

32、的重复行在重复数据检查时不忽略空值在重复数据检查时不忽略空值(NULL)NULL)l交交INTERSECTINTERSECT返回所有查询语句得到的结果中的相同行返回所有查询语句得到的结果中的相同行各查询语句返回结果中有相同行，并不代表各对应基表中各查询语句返回结果中有相同行，并不代表各对应基表中就存在相同记录，只是与选取的列有关就存在相同记录，只是与选取的列有关交运算不忽略空值交运算不忽略空值l差差MINUSMINUS返回在第一个查询语句的结果集中却不在第二个查询语句返回在第一个查询语句的结果集中却不在第二个查询语句的结果集中的所有记录行的结果集中的所有记录行差运算不忽略空值差运算不忽略空值例

33、题：例题：P156P156UNIONUNIONALLINTERSECTMINUSl集合运算要求集合运算要求SELECTSELECT语句的列数与列类型要一致。语句的列数与列类型要一致。当不一致而又需保持一致时，可用虚列占位。当不一致而又需保持一致时，可用虚列占位。用虚列用虚列(dummy column)dummy column)占位，使用类型转换函数满足其他占位，使用类型转换函数满足其他SELECTSELECT语句的列对类型的要求语句的列对类型的要求例如：例如：SELECT department_id,SELECT department_id,TO_NUMBER(null)TO_NUMBER(n

34、ull)location,location,hire_datehire_date FROM employees FROM employees UNION UNION SELECT department_id,location_id,SELECT department_id,location_id,TO_DATE(null)TO_DATE(null)FROM departments;FROM departments;n 4.TOP-N4.TOP-N查询查询l用于获取某列数据中最大或最小的用于获取某列数据中最大或最小的n n个值个值l语法：语法：SELECT column_list,ROWNUMS

35、ELECT column_list,ROWNUMFROM FROM(SELECT column_list(SELECT column_list FROM table FROM table ORDER BY Top-N_column ASC|DESC)ORDER BY Top-N_column ASC|DESC)WHERE ROWNUM=N;WHERE ROWNUM=N;取最大的前取最大的前N N个值，个值，ORDER BYORDER BY需指明需指明DESCDESC取最小的前取最小的前N N个值，个值，ORDER BYORDER BY需指明需指明ASCASC用用ROWNUMROWNUM限制取得

36、的结果记录数限制取得的结果记录数n 5.5.树形层次查询树形层次查询l语法：语法：SELECT.SELECT.FROM table_nameFROM table_nameWHERE condition WHERE condition START WITH column=value START WITH column=value CONNECT BY PRIOR CONNECT BY PRIOR 父主键父主键=子外键子外键;START WITHSTART WITH：标识层次树的根行标识层次树的根行CONNECT BYCONNECT BY：定义层次树的父子关系条件定义层次树的父子关系条件PRIOR

37、PRIOR操作符表示父行操作符表示父行WHEREWHERE子句剪除满足条件的节点子句剪除满足条件的节点CONNECT BYCONNECT BY子句剪除满足条件的分支子句剪除满足条件的分支LEVEL(LEVEL(伪列伪列)：返回记录在层次树型查询中的层级数：返回记录在层次树型查询中的层级数LEVELLEVEL为根节点返回为根节点返回1 1，根节点的子节点返回，根节点的子节点返回2 2可用可用LPADLPAD函数配合函数配合LEVELLEVEL在查询结果的左边添加空格等字符在查询结果的左边添加空格等字符，形成缩进结构的树形样式，形成缩进结构的树形样式n 虚表和伪列虚表和伪列n 基本查询基本查询n

38、OracleOracle支持的支持的SQLSQL函数函数n 高级查询高级查询l连接查询连接查询l子查询子查询l集合操作集合操作lTOP-NTOP-N查询查询l树形层次查询树形层次查询本章小结本章小结n SelectSelect语句语句l掌握表本身的业务逻辑关系，才能写出正确的查询掌握表本身的业务逻辑关系，才能写出正确的查询语句语句l关键字大小写无关关键字大小写无关l字符串大小写有关字符串大小写有关l善于使用别名：表的别名、字段的别名善于使用别名：表的别名、字段的别名l多表连接查询：首要条件应是表之间的连接关系，多表连接查询：首要条件应是表之间的连接关系，避免出现笛卡尔连接避免出现笛卡尔连接lfromfrom后面不仅可以连接表，也可连接结果集后面不仅可以连接表，也可连接结果集lWhereWhere后面只能连接条件，条件之间的关系可为与、后面只能连接条件，条件之间的关系可为与、或、非、包含、不包含、子查询等或、非、包含、不包含、子查询等l查询语句不一定只有一种写法，运用不同的关键字查询语句不一定只有一种写法，运用不同的关键字可以达到一样的效果可以达到一样的效果