Oracle支持的SQL.ppt

Oracle数据库实用教程 (第二版),唐远新 曲卫平 李晓峰等编著 中国水利水电出版社 2009.6,第8章 Oracle支持的SQL,8.1 Oracle的数据操纵语言,使用VALUES子句的INSERT语句 INSERT INTO table (column_1, column_2,. ) VALUES ( sql_expression_1 ,sql_expression_2,. ) ;,1.INSERT语句,此语法一次只能向表中插入一条记录,INSERT INTO 子句的table后可省略列名表 建议使用列名表明确要往哪些列插入数据,Values子句中，字符型、日期型数据要用单引号括起来 可以在列值处使用关键字DAFAULT表示该列取缺省值,插入空值 (1) 在INSERT INTO 子句的table后不指定该列名，默认情况下该列取空值 (2) 在VALUES子句中使用NULL作为列的值,1.INSERT语句,例8.1 向dept表插入一个新部门记录，但该部门的所在地尚未确定(值为空)。 EXA_08_01.SQL,使用子查询的INSERT语句 INSERT INTO (,.) SELECT ,. FROM ;,1.INSERT语句,例8.2 从ORCL数据库通过数据库链接向数据库CEMERP的表emp插入记录。 EXA_08_02.SQL,SELECT子句中列的数量与列类型应与INTO 子句中指定的列数量与列类型一致,常见的数据插入错误 (1) 没有对强制为非空(NOT NULL) 的列赋值 (2) 对唯一性索引列(包括主键) 插入重复值 (3) 违反参照完整性(插入了被参照表中主键列不存在的外键值) (4) 违反检查(CHECK) 约束 (5) 数据类型不匹配，且系统不能完成自动类型转换 (6) 值太大，超出列定义的表示范围,1.INSERT语句,UPDATE语句语法如图,2.UPDATE语句,最简单的UPDATE语句语法 UPDATE table SET column = value , column = value , . WHERE condition ;,指定被更新表的语法,2.UPDATE语句,设置更新值的语法,2.UPDATE语句,使用子查询从其他表获取数据作为列的新值 UPDATE SET (,.) = ( SELECT ,. FROM WHERE ) WHERE ( SELECT FROM WHERE );,2.UPDATE语句,SET子句中的SELECT子句只能返回一行数据,例8.3 将EMP表30部门的雇员佣金设为800。 EXA_08_03.SQL,例8.4 修改part_emp表sal_3000分区中的记录SAL列值为2500 EXA_08_04.SQL,例8.5 修改part_emp表，将7654号雇员调整为与7934号雇员一个部门。 EXA_08_05.SQL,DELETE语句语法 DELETE FROM schema . table PARTITION ( partition ) | SUBPARTITION ( subpartition ) | dblink | view | materialized view dblink WHERE condition ;,3.DELETE语句,例8.6 使用DELETE语句删除数据的不同方法。 EXA_08_06.SQL,例8.7 删除分区中信息。EXA_08_07.SQL,用于将一个表的数据合并到另一个表中,4.MERGE语句,根据指定的合并比对条件将源表中记录在目标表中进行查找 如找到对应记录，则执行UPDATE操作 如找不到满足条件的对应记录，则执行INSERT操作,MERGE是一条“确定性的语句”,4.MERGE语句,例8.8 将emp表中30部门的雇员记录合并到bonus表中。EXA_08_08.SQL,MERGE语句的语法,MERGE INTO table t_alias USING table | view | subquery t_alias ON ( condition ) WHEN MATCHED THEN UPDATE SET column = expr | DEFAULT . WHEN NOT MATCHED THEN INSERT ( column , column. ) VALUES ( expr , expr. ) ;,5.事务控制命令,COMMIT：提交 SAVEPOINT savepoint：设置保存点savepoint ROLLBACK WORK TO savepoint：回退,DML操作结果的提交 执行COMMIT命令 以EXIT命令退出SQL*Plus 执行DML之后发布了DDL命令,未提交的修改对操作者是生效的，提交后其他用户才可看到修改后的结果,SELECT语句完成如下运算：,8.2 Oracle支持的查询,投影(Projection) 选取指定的列(字段) 在SELECT子句中列出所需的列(字段)名,选择(Selection) 选取指定的行(记录) 无条件选取全部行或选WHERE子句条件所限定的行,连接(Joining) 将多个表的数据连接在一起 在FROM子句中列出多个表名然后在WHERE子句中指定连接条件 由Join指定参与连接的多个表，然后在ON子句中指定连接条件,1.SELECT语句,SELECT DISTINCT | ALL * | column | expression AS alias, . FROM schema . table PARTITION ( partition ) | SUBPARTITION ( subpartition ) sample_clause | sample_clause | dblink | view | materialized view dblink | (subquery subquery_restriction_clause ) WHERE condition CONNECT BY condition START WITH condition GROUP BY expn HAVING expn UNION ALL | INTERSECT | MINUS SELECT . ORDER BY expn ASC | DESC FOR UPDATE OF schema .table | view column ;,1.SELECT语句,一般将各子句单独成行书写，并采用缩进格式,别名(alias) 用于重命名列标题以改善查询结果的可读性 别名中使用到空格或需要区分大小写时，别名应用双引号括起来 两种定义别名方法 别名直接写在列名或列表达式之后 用AS 定义，将别名放在AS关键字之后 别名不可用于WHERE子句,选择表中所有列 *(星号)或在SELECT子句中列出所有的字段 消除重复出现的行：使用DISTINCT限定词,1.SELECT语句,字符串和日期常量需用单引号括起来 字符串常量区分大小写,在SELECT子句中可以使用运算符和SQL函数构造列表达式，在取出数据同时进行有关的运算,从何处取数据 表 表分区 视图 远程数据库,1.SELECT语句,数据分组 GROUP BY子句对记录分组并执行相应数据聚集运算 在SELECT 子句的列名表中的所有非分组函数计算列必须出现在GROUP BY子句中 使用HAVING子句对数据分组后的返回结果进行限制 HAVING子句应放在GROUP BY子句之后,升序用ASC，降序用DESC 默认排序方式为升序，记录以如下方式排列： (1) 数值型从小到大 (2) 日期型按年份从小到大 (3) 字符型按字母表顺序 (4) 空值排在最后,1.SELECT语句,CONNECT BY：层次树型查询 UNION：集合运算 FOR UPDATE：锁定选取记录,例8.9 DISTINCT限定词的应用。EXA_08_09.SQL,例8.10 别名与列标题的应用。EXA_08_10.SQL,例8.11 SELECT语句的综合例子。EXA_08_11.SQL,2.虚表与伪列,伪列 CURRVAL和NEXTVAL：序列当前值和下一个值 LEVEL：层次树型查询记录所对应的级 ROWID：记录的物理标识 ROWNUM：查询结果集中记录的行序号 Oracle针对闪回版本查询提供了一组伪列,例： SELECT sysdate FROM DUAL; SELECT user FROM DUAL;,虚表 DUAL,3.表的连接,在使用连接运算时，应注意如下几点： (1) 建议在列名之前使用表名前缀以改善运行性能 (2) 对表使用简短的别名可改善连接性能 (3) 使用准确的连接条件和WHERE子句条件可显著改善连接性能，不使用无条件的连接(即笛卡儿积) (4) 应对同名的列冠以表名前缀以明确告知Oracle数据库该列选取自哪个表,例8.12 查看工资高于2500的雇员及所在部门。EXA_08_12.SQL,等值连接指参与连接的多个表将连接条件列值相同的记录连接在一起作为查询结果记录返回 SELECT . FROM table1, table2 WHERE table1.column1 oper table2.column2 ; 等值连接，运算符oper为 = 非等值连接，oper可为!=、=等,3.表的连接,自连接将一个表看成两个副本，取不同的别名 用别名构造连接条件 SELECT . FROM table alias1, table alias2 WHERE alias1.column1=alias2.column2；,例8.13 查询出部门30中每个雇员的上司名字。EXA_08_13.SQL,3.表的连接,外连接 增加空行将不满足连接条件的记录也返回。 SELECT . FROM table alias1, table alias2 WHERE alias1.column1(+) = alias2.column2 | alias1.column1 = alias2.column2(+) ;,例8.14 查询出每个部门中雇员的名字。 EXA_08_14.SQL,在(+)端引入一空行与另一端匹配，保证无(+)端表的记录可全部显示出来 (+)在左端时称为左外连接 (+)在右端时称为右外连接,3.表的连接,ANSI/ISO SQL:1999标准中连接语法 SELECT table1.column , table2.column FROM table1 CROSS JOIN table2 | NATURE JOIN table2 | JOIN table2 USING ( column ) | JOIN table2 ON ( table1.column_name = table2.column_name ) | LEFT | RIGHT | FULL OUTER JOIN table2 ON (table1.col_name = table2.col_name ) ;,建议使用Oracle公司的连接语法,4.子查询,子查询是出现在SQL语句中的SELECT语法成分,子查询分类 简单子查询：用独立的条件返回值 相关子查询：子查询WHERE条件中用到了嵌套它的SQL语句的当前记录值 单行子查询：仅返回一条记录 多行子查询：返回多条记录,当子查询出现在WHERE子句中时 WHERE expr operator (SELECT select_list FROM table ); 单行比较运算符：、=、=等 多行比较运算符：IN、ANY、ALL、EXISTS,4.子查询,ANY：某一记录 ALL：所有记录,使用EXISTS的执行效率较高，只需返回一个布尔值,ANY/ALL与关系运算符一起使用的含义,例8.15 查询哪些雇员的工资高于他所在部门的平均工资。EXA_08_15.SQL,例8.16 ANY和ALL运算符的应用。EXA_08_16.SQL,例8.17 查询哪些员工没有下属，哪些员工有下属。EXA_08_17.SQL,4.子查询,例8.18 使用EXISTS的子查询。EXA_08_18.SQL,简单子查询,4.子查询,SELECT . FROM WHERE (SELECT FROM WHERE );,UPDATE SET (,.) = ( SELECT ,. FROM WHERE ) WHERE ( SELECT FROM WHERE );,DELETE FROM WHERE (SELECT FROM WHERE );,简单子查询,4.子查询,INSERT INTO (,.) SELECT ,. FROM WHERE ( SELECT FROM WHERE );,CREATE TABLE AS SELECT ,. FROM WHERE ( SELECT FROM WHERE );,相关子查询,4.子查询,SELECT FROM WHERE (SELECT FROM WHERE );,UPDATE SET (,.) = ( SELECT FROM WHERE ) WHERE ;,DELETE FROM WHERE = ( SELECT FROM WHERE );,SELECT语句中的集合运算包括 并(UNION) 、交(INTERSECT) 、差(MINUS) 所有集合运算符具有相同的优先级 按照从左(上) 至右(下) 的顺序执行 可使用括号改变运算的优先级,5.集合运算,各SELECT语句中的列数和字段类型必须相同 列的名字、列的顺序可不相同,查询结果列标题为第一个SELECT语句列标题,对集合运算结果的排序遵循以下规则 (1) ORDER BY子句只能出现在整个语句的最后，且只能用一次 (2) ORDER BY子句使用的列名或别名只能取自第一个SELECT语句 (3) ORDER BY子句可以使用列的位置序号表示列名 (4) 默认以第一个SELECT语句的第一列的升序显示,并运算符(UNION) UNION：返回多个查询中消除重复行以后的结果 UNION ALL：返回每个查询语句得到的结果行 不消除多个查询得到结果中的重复行 并运算符在重复数据检查时不忽略空值(NULL),例8.19 使用HR(人力资源)示例账户的表，查询各员工从事过的所有工作。EXA_08_19.SQL,5.集合运算,交运算符(INTERSECT) 返回所有查询语句得到的结果中的相同行 各查询语句返回结果中有相同行，并不代表各对应基表中就存在相同记录 交运算不忽略空值,例8.20 交运算的结果与SELECT语句选取的列相关。EXA_08_20.SQL,差运算符(MINUS) MINUS运算返回在第一个查询语句的结果集中却不在第二个查询语句的结果集中的所有记录行 差运算(MINUS) 不忽略空值,例8.21 集合运算中的空值测试。EXA_08_21.SQL,5.集合运算,列数量与类型相同问题的解决 用虚列(dummy column) 占位，使用类型转换函数满足其他SELECT语句的列对类型的要求,例： SELECT department_id, TO_NUMBER(null) location, hire_date FROM employees UNION SELECT department_id, location_id, TO_DATE(null) FROM departments;,SELECT . FROM table_name WHERE condition START WITH column=value CONNECT BY PRIOR 父主键=子外键;,6.层次树型查询,START WITH：标识层次树根行,CONNECT BY：定义层次树父子关系条件 PRIOR操作符表示父行,WHERE子句剪除满足条件的节点 CONNECT BY子句剪除满足条件的分支,LEVEL：返回记录在层次树型查询中的层级数 LEVEL为根节点返回1，根节点的子节点返回2,可用LPAD函数配合LEVEL伪列在查询结果的左边添加空格等字符，形成缩进结构的树形样式,例8.22 从表EMP中按照隶属关系显示所有雇员信息。EXA_08_22.SQL,6.层次树型查询,例8.23 从表EMP中按照等级关系显示层次小于等于2的所有雇员信息。EXA_08_23.SQL,例8.24 删除节点与分支。EXA_08_24.SQL,使用with子句定义内容需要重复使用的查询块,7.层次树型查询,例：查询哪些部门的总工资高于所有部门的平均工资。EXA_08_P164_with.SQL,当查询块的名称与基表名相同时，查询块优先 查询块自定义位置起到整个SQL语句代码段的结束处均有效 查询块不是数据库方案对象,首先是定义dept_costs计算出各部门的总工资 从dept_costs计算出所有部门的平均工资放到avg_cost中 SELECT语句在前面两步运算的基础上计算出部门的总工资高于所有部门的平均工资的部门,8.3 Oracle支持的SQL函数,根据函数返回结果是基于一行或多行，函数分为 单行函数：对查询的每一行返回一个结果值 分组函数：对一组查询行返回一个结果值,民族语言支持(National Language Support，NLS) 允许用户以本民族语言存储、处理和检索数据，并可以在错误信息显示、排序、日期、货币、数字及日历等方面自动转换适应本地平台和语言。,NLS对函数运算的影响 字节运算：函数名称后带字符“B” “民族语言支持”字符运算,SELECT substr(中华人民共和国的英文名称是China,3,4) FROM DUAL; SELECT substrb(中华人民共和国的英文名称是China,3,4) FROM DUAL; SELECT substr(Oracle中国公司,3,4) FROM DUAL; EXA_08_P164_nls.SQL,单行函数可以出现在SELECT语句的 SELECT子句 WHERE子句 ORDER BY子句 START WITH子句 CONNECT BY子句,1.单行函数,单行函数特点 (1) 基于单行返回结果 (2) 允许参数是其他单行函数产生的结果 (3) 对查询返回的各行分别进行计算 (4) 可以用常量、变量、列名以及表达式作为参数,数值函数 接受数值型输入数据，并返回数值型的结果,1.单行函数,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) SINH(n) SQRT(n) TAN(n) TANH(n) TRUNC(m,n),字符函数,1.单行函数,LOWER (char) UPPER (char) INITCAP (char) CONCAT (char1, char2) SUBSTR (char, 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),返回数值型的字符函数 ASCII INSTR INSTRB LENGTH LENGTHB NLSSORT,日期函数,1.单行函数,MONTHS_BETWEEN (d1,d2) ADD_MONTHS (d,n) NEXT_DAY (d,s) LAST_DAY (d) ROUND (date,fmt) TRUNC (date,fmt),1.单行函数,Oracle使用内部的数值格式表示日期和时间,默认的日期显示格式是DD-MON-RR (日-月-年) 可设定掩码指定日期型数据的格式,1.单行函数,RR格式表示的实际年份，与 当前年份(在本世纪中的位置) 给定的两位年份数 有关,例： 假设当前年份为2009。 EXA_08_P167_RR.SQL,SELECT to_char(to_date(79-01-31,YY-MM-DD),YYYY-MM-DD) FROM dual;,SELECT to_char(to_date(79-01-31,RR-MM-DD),RRRR-MM-DD) FROM dual;,2079-01-31,1979-01-31,1.单行函数,Oracle数据库中的类型转换有两种 自动类型转换：字符型和日期型、字符型和数值型间 强制类型转换：使用类型转换函数,常用的转换函数 TO_CHAR (NUMBER | date,format) TO_NUMBER (char , format) TO_DATE (char, format),常用的数值格式掩码 9-数字字符 0-数据有前导0 .-小数点 ,-千位分隔符 $-美元符号$ L-显示本地货币符号（人民币为￥）,1.单行函数,其他常用单行函数,空值(NULL)与其他数据运算的结果一般均为空值 根据需要，一般将空值转换为数值0或1，否则计算表达式将得不到正确的值,2.分组函数,分组函数对一组查询行返回一个结果值,除count(*)外，分组函数均忽略空值 如要包括空值，须使用空值转换函数,分组函数不可以用在WHERE子句中用以限定查询的结果，对分组查询结果的限定应使用HAVING子句,在分组函数中可有下面两个选项 (1) DISTINCT选项：使分组函数只考虑列表达式中的不同值 (2) ALL选项：使分组函数考虑全部值，其中包含重复值,2.分组函数,主要的分组函数,例8.25 分组函数综合应用。 EXA_08_25.SQL,3.DateTime函数,在DATE类型的基础上引入三种日期时间类型 TIMESTAMP TIMESTAMP WITH TIME ZONE (TSTZ) TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ),时区参数 数据库时区 DBTIMEZONE 会话时区 SESSIONTIMEZONE,连接会话参数TIME_ZONE设置会话时区与UTC时间的时间差 TIME_ZONE = + | - hh:mm +表示比UTC提前，-表示比UTC滞后,北京时间时区设置 ALTER SESSION SET TIME_ZONE = +8:00 ;,3.DateTime函数,DateTime函数,例8.26 DateTime函数应用 EXA_08_26.SQL,4.OLAP函数,Oracle提供了一组用于联机分析处理(OLAP)的SQL函数,例8.27 对各部门各职位的工资总额进行从高到低排序。EXA_08_27.SQL,SELECT department_id, job_id, SUM(salary), RANK() OVER (ORDER BY SUM(salary) DESC) AS 次序 FROM employees GROUP BY department_id, job_id ;,8.4 用于数据分析的SQL,多表插入可将一条源记录根据条件向多个表插入，以满足不同的数据观察要求,INSERT ALL FIRST WHEN condition1 THEN INTO table1 VALUES(column_val, .) WHEN conditionX THEN INTO tableX VALUES(column_val, .) ELSE INTO tableY VALUES(column_val, .) SELECT .;,ALL：对所有WHEN条件进行检查，只要满足条件就执行对应的插入操作 FIRST：找到第一个满足条件的WHEN子句，执行对应插入操作后即结束当前记录的处理 源数据由SELECT子句提供,1.多表插入与旋转插入,多表插入操作有如下限制 (1) 只能对基表执行多表插入，不能对视图和实体化视图执行多表插入操作 (2) 不能对远程表执行多表插入操作,例8.28 对雇员表中编号大于200的雇员的记录，将工资超过10000的雇员记录插入到sal_history表中，将其经理的雇员编号超过200的雇员记录插入到mgr_history表中。EXA_08_28.SQL,1.多表插入与旋转插入,旋转数据插入可将一个规范化程度不高的表中的数据转换到规范化的表中,例8.29 现有雇员销售记录表SALES_SOURCE_DATA，包含雇员编号EMPLOYEE_ID，星期编号WEEK_ID，周一销量SALES_MON，周二销量SALES_TUE，周三销量SALES_WED，周四销量SALES_THUR，周五销量SALES_FRI等列。EXA_08_29.SQL,2.Top-n查询,Top-n用于取某列数据中最大或最小的n个值,例8.30 按从高到低的次序显示工资最高的5个人。EXA_08_30.SQL,Top-n分析语法 SELECT column_list , ROWNUM