《Oracle支持的》PPT课件.ppt

Oracle数据库实用教程(第二版),唐远新曲卫平李晓峰等编著中国水利水电出版社2009.6,第8章Oracle支持的SQL,Oracle的DML包括4条语句INSERTUPDATEDELETEMERGE,8.1Oracle的数据操纵语言,使用VALUES子句的INSERT语句INSERTINTOtable(column_1,column_2,.)VALUES(sql_expression_1,sql_expression_2,.);,1.INSERT语句,此语法一次只能向表中插入一条记录,INSERTINTO子句的table后可省略列名表建议使用列名表明确要往哪些列插入数据,Values子句中，字符型、日期型数据要用单引号括起来可以在列值处使用关键字DAFAULT表示该列取缺省值,插入空值(1)在INSERTINTO子句的table后不指定该列名，默认情况下该列取空值(2)在VALUES子句中使用NULL作为列的值,1.INSERT语句,例8.1向dept表插入一个新部门记录，但该部门的所在地尚未确定(值为空)。EXA_08_01.SQL,使用子查询的INSERT语句INSERTINTO(,.)SELECT,.FROM;,1.INSERT语句,例8.2从ORCL数据库通过数据库链接向数据库CEMERP的表emp插入记录。EXA_08_02.SQL,SELECT子句中列的数量与列类型应与INTO子句中指定的列数量与列类型一致,常见的数据插入错误(1)没有对强制为非空(NOTNULL)的列赋值(2)对唯一性索引列(包括主键)插入重复值(3)违反参照完整性(插入了被参照表中主键列不存在的外键值)(4)违反检查(CHECK)约束(5)数据类型不匹配，且系统不能完成自动类型转换(6)值太大，超出列定义的表示范围,1.INSERT语句,UPDATE语句语法如图,2.UPDATE语句,最简单的UPDATE语句语法UPDATEtableSETcolumn=value,column=value,.WHEREcondition;,指定被更新表的语法,2.UPDATE语句,设置更新值的语法,2.UPDATE语句,使用子查询从其他表获取数据作为列的新值UPDATESET(,.)=(SELECT,.FROMWHERE)WHERE(SELECTFROMWHERE);,2.UPDATE语句,SET子句中的SELECT子句只能返回一行数据,例8.3将EMP表30部门的雇员佣金设为800。EXA_08_03.SQL,例8.4修改part_emp表sal_3000分区中的记录SAL列值为2500EXA_08_04.SQL,例8.5修改part_emp表，将7654号雇员调整为与7934号雇员一个部门。EXA_08_05.SQL,DELETE语句语法DELETEFROMschema.tablePARTITION(partition)|SUBPARTITION(subpartition)|dblink|view|materializedviewdblinkWHEREcondition;,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语句的语法,MERGEINTOtablet_aliasUSINGtable|view|subqueryt_aliasON(condition)WHENMATCHEDTHENUPDATESETcolumn=expr|DEFAULT.WHENNOTMATCHEDTHENINSERT(column,column.)VALUES(expr,expr.);,5.事务控制命令,COMMIT：提交SAVEPOINTsavepoint：设置保存点savepointROLLBACKWORKTOsavepoint：回退,DML操作结果的提交执行COMMIT命令以EXIT命令退出SQL*Plus执行DML之后发布了DDL命令,未提交的修改对操作者是生效的，提交后其他用户才可看到修改后的结果,SELECT语句完成如下运算：,8.2Oracle支持的查询,投影(Projection)选取指定的列(字段)在SELECT子句中列出所需的列(字段)名,选择(Selection)选取指定的行(记录)无条件选取全部行或选WHERE子句条件所限定的行,连接(Joining)将多个表的数据连接在一起在FROM子句中列出多个表名然后在WHERE子句中指定连接条件由Join指定参与连接的多个表，然后在ON子句中指定连接条件,1.SELECT语句,SELECTDISTINCT|ALL*|column|expressionASalias,.FROMschema.tablePARTITION(partition)|SUBPARTITION(subpartition)sample_clause|sample_clause|dblink|view|materializedviewdblink|(subquerysubquery_restriction_clause)WHEREconditionCONNECTBYconditionSTARTWITHconditionGROUPBYexpnHAVINGexpnUNIONALL|INTERSECT|MINUSSELECT.ORDERBYexpnASC|DESCFORUPDATEOFschema.table|viewcolumn;,1.SELECT语句,一般将各子句单独成行书写，并采用缩进格式,别名(alias)用于重命名列标题以改善查询结果的可读性别名中使用到空格或需要区分大小写时，别名应用双引号括起来两种定义别名方法别名直接写在列名或列表达式之后用AS定义，将别名放在AS关键字之后别名不可用于WHERE子句,选择表中所有列*(星号)或在SELECT子句中列出所有的字段消除重复出现的行：使用DISTINCT限定词,1.SELECT语句,字符串和日期常量需用单引号括起来字符串常量区分大小写,在SELECT子句中可以使用运算符和SQL函数构造列表达式，在取出数据同时进行有关的运算,从何处取数据表表分区视图远程数据库,1.SELECT语句,数据分组GROUPBY子句对记录分组并执行相应数据聚集运算在SELECT子句的列名表中的所有非分组函数计算列必须出现在GROUPBY子句中使用HAVING子句对数据分组后的返回结果进行限制HAVING子句应放在GROUPBY子句之后,升序用ASC，降序用DESC默认排序方式为升序，记录以如下方式排列：(1)数值型从小到大(2)日期型按年份从小到大(3)字符型按字母表顺序(4)空值排在最后,1.SELECT语句,CONNECTBY：层次树型查询UNION：集合运算FORUPDATE：锁定选取记录,例8.9DISTINCT限定词的应用。EXA_08_09.SQL,例8.10别名与列标题的应用。EXA_08_10.SQL,例8.11SELECT语句的综合例子。EXA_08_11.SQL,2.虚表与伪列,伪列CURRVAL和NEXTVAL：序列当前值和下一个值LEVEL：层次树型查询记录所对应的级ROWID：记录的物理标识ROWNUM：查询结果集中记录的行序号Oracle针对闪回版本查询提供了一组伪列,例：SELECTsysdateFROMDUAL;SELECTuserFROMDUAL;,虚表DUAL,3.表的连接,在使用连接运算时，应注意如下几点：(1)建议在列名之前使用表名前缀以改善运行性能(2)对表使用简短的别名可改善连接性能(3)使用准确的连接条件和WHERE子句条件可显著改善连接性能，不使用无条件的连接(即笛卡儿积)(4)应对同名的列冠以表名前缀以明确告知Oracle数据库该列选取自哪个表,例8.12查看工资高于2500的雇员及所在部门。EXA_08_12.SQL,等值连接指参与连接的多个表将连接条件列值相同的记录连接在一起作为查询结果记录返回SELECT.FROMtable1,table2WHEREtable1.column1opertable2.column2;等值连接，运算符oper为=非等值连接，oper可为!=、=等,3.表的连接,自连接将一个表看成两个副本，取不同的别名用别名构造连接条件SELECT.FROMtablealias1,tablealias2WHEREalias1.column1=alias2.column2；,例8.13查询出部门30中每个雇员的上司名字。EXA_08_13.SQL,3.表的连接,外连接增加空行将不满足连接条件的记录也返回。SELECT.FROMtablealias1,tablealias2WHEREalias1.column1(+)=alias2.column2|alias1.column1=alias2.column2(+);,例8.14查询出每个部门中雇员的名字。EXA_08_14.SQL,在(+)端引入一空行与另一端匹配，保证无(+)端表的记录可全部显示出来(+)在左端时称为左外连接(+)在右端时称为右外连接,3.表的连接,ANSI/ISOSQL:1999标准中连接语法SELECTtable1.column,table2.columnFROMtable1CROSSJOINtable2|NATUREJOINtable2|JOINtable2USING(column)|JOINtable2ON(table1.column_name=table2.column_name)|LEFT|RIGHT|FULLOUTERJOINtable2ON(table1.col_name=table2.col_name);,建议使用Oracle公司的连接语法,4.子查询,子查询是出现在SQL语句中的SELECT语法成分,子查询分类简单子查询：用独立的条件返回值相关子查询：子查询WHERE条件中用到了嵌套它的SQL语句的当前记录值单行子查询：仅返回一条记录多行子查询：返回多条记录,当子查询出现在WHERE子句中时WHEREexproperator(SELECTselect_listFROMtable);单行比较运算符：、=、=等多行比较运算符：IN、ANY、ALL、EXISTS,4.子查询,ANY：某一记录ALL：所有记录,使用EXISTS的执行效率较高，只需返回一个布尔值,ANY/ALL与关系运算符一起使用的含义,例8.15查询哪些雇员的工资高于他所在部门的平均工资。EXA_08_15.SQL,例8.16ANY和ALL运算符的应用。EXA_08_16.SQL,例8.17查询哪些员工没有下属，哪些员工有下属。EXA_08_17.SQL,4.子查询,例8.18使用EXISTS的子查询。EXA_08_18.SQL,简单子查询,4.子查询,SELECT.FROMWHERE(SELECTFROMWHERE);,UPDATESET(,.)=(SELECT,.FROMWHERE)WHERE(SELECTFROMWHERE);,DELETEFROMWHERE(SELECTFROMWHERE);,简单子查询,4.子查询,INSERTINTO(,.)SELECT,.FROMWHERE(SELECTFROMWHERE);,CREATETABLEASSELECT,.FROMWHERE(SELECTFROMWHERE);,相关子查询,4.子查询,SELECTFROMWHERE(SELECTFROMWHERE);,UPDATESET(,.)=(SELECTFROMWHERE)WHERE;,DELETEFROMWHERE=(SELECTFROMWHERE);,SELECT语句中的集合运算包括并(UNION)、交(INTERSECT)、差(MINUS)所有集合运算符具有相同的优先级按照从左(上)至右(下)的顺序执行可使用括号改变运算的优先级,5.集合运算,各SELECT语句中的列数和字段类型必须相同列的名字、列的顺序可不相同,查询结果列标题为第一个SELECT语句列标题,对集合运算结果的排序遵循以下规则(1)ORDERBY子句只能出现在整个语句的最后，且只能用一次(2)ORDERBY子句使用的列名或别名只能取自第一个SELECT语句(3)ORDERBY子句可以使用列的位置序号表示列名(4)默认以第一个SELECT语句的第一列的升序显示,并运算符(UNION)UNION：返回多个查询中消除重复行以后的结果UNIONALL：返回每个查询语句得到的结果行不消除多个查询得到结果中的重复行并运算符在重复数据检查时不忽略空值(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.集合运算,列数量与类型相同问题的解决用虚列(dummycolumn)占位，使用类型转换函数满足其他SELECT语句的列对类型的要求,例：SELECTdepartment_id,TO_NUMBER(null)location,hire_dateFROMemployeesUNIONSELECTdepartment_id,location_id,TO_DATE(null)FROMdepartments;,SELECT.FROMtable_nameWHEREconditionSTARTWITHcolumn=valueCONNECTBYPRIOR父主键=子外键;,6.层次树型查询,STARTWITH：标识层次树根行,CONNECTBY：定义层次树父子关系条件PRIOR操作符表示父行,WHERE子句剪除满足条件的节点CONNECTBY子句剪除满足条件的分支,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.3Oracle支持的SQL函数,根据函数返回结果是基于一行或多行，函数分为单行函数：对查询的每一行返回一个结果值分组函数：对一组查询行返回一个结果值,民族语言支持(NationalLanguageSupport，NLS)允许用户以本民族语言存储、处理和检索数据，并可以在错误信息显示、排序、日期、货币、数字及日历等方面自动转换适应本地平台和语言。,NLS对函数运算的影响字节运算：函数名称后带字符“B”“民族语言支持”字符运算,SELECTsubstr(中华人民共和国的英文名称是China,3,4)FROMDUAL;SELECTsubstrb(中华人民共和国的英文名称是China,3,4)FROMDUAL;SELECTsubstr(Oracle中国公司,3,4)FROMDUAL;EXA_08_P164_nls.SQL,单行函数可以出现在SELECT语句的SELECT子句WHERE子句ORDERBY子句STARTWITH子句CONNECTBY子句,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_charFROMtrim_source)REPLACE(char1,char2,char3),返回数值型的字符函数ASCIIINSTRINSTRBLENGTHLENGTHBNLSSORT,日期函数,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,SELECTto_char(to_date(79-01-31,YY-MM-DD),YYYY-MM-DD)FROMdual;,SELECTto_char(to_date(79-01-31,RR-MM-DD),RRRR-MM-DD)FROMdual;,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类型的基础上引入三种日期时间类型TIMESTAMPTIMESTAMPWITHTIMEZONE(TSTZ)TIMESTAMPWITHLOCALTIMEZONE(TSLTZ),时区参数数据库时区DBTIMEZONE会话时区SESSIONTIMEZONE,连接会话参数TIME_ZONE设置会话时区与UTC时间的时间差TIME_ZONE=+|-hh:mm+表示比UTC提前，-表示比UTC滞后,北京时间时区设置ALTERSESSIONSETTIME_ZONE=+8:00;,3.DateTime函数,DateTime函数,例8.26DateTime函数应用EXA_08_26.SQL,4.OLAP函数,Oracle提供了一组用于联机分析处理(OLAP)的SQL函数,例8.27对各部门各职位的工资总额进行从高到低排序。EXA_08_27.SQL,SELECTdepartment_id,job_id,SUM(salary),RANK()OVER(ORDERBYSUM(salary)DESC)AS次序FROMemployeesGROUPBYdepartment_id,job_id;,8.4用于数据分析的SQL,多表插入可将一条源记录根据条件向多个表插入，以满足不同的数据观察要求,INSERTALLFIRSTWHENcondition1THENINTOtable1VALUES(column_val,.)WHENconditionXTHENINTOtableXVALUES(column_val,.)ELSEINTOtableYVALUES(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分析语法SELECTcolumn_list,ROWNUMFROM(SELECTcolumn_listFROMtableORDERBYTop-N_columnASC|DESC)WHEREROWNUM=N;,取最大的前N个值，ORDERBY子句需指明DESC取最小的前N个值，ORDERBY子句需指明ASC用ROWNUM限制取得的结果记录数,3.ROLLUP与CUBE,例8.31从最细的数据颗粒(同时考虑部门、职位和受聘时间3个因素)到相对高的数据层次(只考虑部门和职位两个因素)，再到更高的数据层次(仅考虑部门)和整个单位(不考虑任何因素)4个不同数据层次来观察和分析公司的工资情况。EXA_08_31.SQL,GROUPBY之后的列(各维)是有层次的，最右边的为最低层，最左边的为最高层ROLLUP对GROUPBY定义的维分层计算各个层次汇总值CUBE的结果包括ROLLUP产生的行和交叉表行(维组合的聚集值),数据聚集操作(ROLLUP)ROLLUP从右至左计算GROUPBY定义的维分组的小计并累计该值至最终的合计。若给定n维分组，ROLLUP将产生n+1层汇总数据,3.ROLLUP与CUBE,例8.32CUBE应用。EXA_08_32.SQL,CUBE操作提供了从数据的不同侧面了解其内涵的方法，把CUBE称作数据立方体操作,CUBE将GROUPBY子句中的各维进行组合(两两组合、三个组合，以此类推)，形成交叉表，并计算各维组合的聚集值,如果GROUPBY子句有n个列名(也称n维)，则CUBE操作将产生2n个分组组合,3.ROLLUP与CUBE,例8.33GROUPING函数的应用。EXA_08_33.SQL,GROUPING函数用于标识某个列是否参与了聚集值的计算返回值0，表示对应的参数列参与了聚集值的计算返回值1，表示对应的参数列未参与聚集值的计算,例8.343个维分组，明确地告知系统只从这3个角度观察数据EXA_08_34.SQL,对分组的集合操作(GROUPINGSETS子句)在一个SELECT语句中定义多个维分组各维分组用()括起来，用逗号分隔各维分组分别计算每个维分组的值，然后执行UNIONALL操作,3.ROLLUP与CUBE,例8.35使用复合列，跳过某些数据层次。EXA_08_35.SQL,复合列(CompositeColumns)复合列用括号括起来，将它们当作一个整体看待使用复合列可跳过某些聚集层次，只处理用户关心的数据层次和组合关系,例8.36连接分组。EXA_08_36.SQL,连接分组(ConcatenatedGroups)连接分组提供了一种组合分组的简便方法，它将分组集合中的各分组进行连接，可产生大量的分组组合,