oracle11学习资料

----------------------10.子查询--------------------------------1.1子查询在外部的SELECT、UPDATE或DELETE语句内部使用SELECT语句，这个内部SELECT语句称为子查询（Subquery）。使用子查询，主要是将子查询的结果作为外部主查询的查找条件。子查询在其他查询的基础上，提供一种进一步有效的方式来表示条件。子查询是一个SELECT语句，可以在外部SELECT语句的FROM、WHERE、HAVING等子句中使用，另外在UPDATE和DELETE语句中也可以使用子查询。在子查询中可以使用两种比较操作符：单行操作符和多行操作符。单行操作符例如=、、=、、=、、!=多行操作符例如ALL、ANY、IN、EXISTS可以把子查询分为两种类型：单行子查询和多行子查询。单行子查询向外部的SQL语句只返回一行数据，或者不返回任何内容。单行子查询可以放到SELECT语句的WHERE子句和HAVING子句中。多行子查询向外部的SQL语句返回多行。要处理返回多行记录的子查询，外部查询需要使用多行操作符。另外，子查询还有下面3种子类型，这3种子类型可以返回一行或多行查询结果:多列子查询向外部的SQL语句返回多列。关联子查询引用外部的SQL语句中的一列或多列。在关联子查询中，可以使用EXISTS和NOTEXISTS操作符。嵌套子查询在子查询中包含有子查询。指定子查询时，需要注意以下几点：子查询需要使用括号（()）括起来。子查询要放在比较操作符的右边。当子查询的返回值是一个集合而不是一个值时，不能使用单行操作符，而必须根据需要使用ANY、IN、ALL或EXISTS等操作符。A．内嵌视图当在FROM子句中使用子查询时，该子句会被作为视图对待，因此也被称为内嵌视图。使用子查询的一般用法形式如下：SELECTcolumn_listFROM(SELECTcolumn_nameFROMtable_nameWHEREconditionGROUPBYexpHAVINGhaving);例如：SELECTdeptnoFROM(SELECTdeptnoFROMscott.empGROUPBYdeptnoORDERBYCOUNT(deptno)ASCB．where在SELECT语句的WHERE子句中可以使用子查询，表示将子查询返回的结果作为外部的WHERE条件。SQLSELECTempno,ename,sal,deptnoFROMscott.empWHEREdeptno=（SELECTdeptnoFROMscott.empWHEREempno=7782);CHAVING在SELECT语句中使用HAVING子句，可以实现对数据进行分组过滤。在HAVING子句中，如果使用子查询，那么就可以实现对子查询返回的结果根据分组进行过滤。SQLSELECTdeptno,AVG(sal)FROMscott.empGROUPBYdeptno2HAVINGAVG(sal)(3SELECTAVG(sal)FROMscott.emp);1.2在UPDATE语句中使用子查询在UPDATE语句中使用子查询，可以将子查询返回的结果赋值给需要更新的列。【例】将员工编号为7839的员工的工资设置为平均工资，如下：SQLUPDATEscott.empSETsal=(2SELECTAVG(sal)FROMscott.emp)WHEREempno=7839;1.3DELETE语句中使用子查询1.4使用IN操作符实现指定匹配查询多行子查询可以向外部的SQL语句返回一行或多行记录。要处理返回多行记录的子查询，外部查询需要使用多行操作符。使用IN操作符，用来检查在一个值列表中是否包含指定的值。这个值列表可以是子查询的返回结果。例如SQLSELECTempno,ename,sal,deptnoFROMscott.emp2WHEREdeptnoIN(3SELECTdeptnoFROMscott.dept4WHEREdnameIN('ACCOUNTING','SALES'));1.4.1使用NOTIN操作符NOTIN操作符用来检查在一个值列表中是否不包含指定的值，NOTIN执行的操作正好与IN在逻辑上相反。例如：SQLSELECTempno,ename,sal,deptnoFROMscott.emp2WHEREdeptnoNOTIN(3SELECTdeptnoFROMscott.dept4WHEREdnameIN('ACCOUNTING','SALES'));1.5使用ANY和ALL操作符a.在进行多行子查询时，使用ANY操作符，用来将一个值与一个列表中的所有值进行比较，这个值只需要匹配列表中的一个值即可，然后将满足条件的数据返回。其中，值列表可以是子查询的返回结果。使用ANY操作符之前，必须使用一个单行操作符，例如=、、、=等。例如：SQLSELECTempno,ename,sal,deptnoFROMscott.emp2WHEREsalANY(3SELECTAVG(sal)FROMscott.empGROUPBYdeptno);B.在进行子查询时，使用ALL操作符，用来将一个值与一个列表中的所有值进行比较，这个值需要匹配列表中的所有值，然后将满足条件的数据返回。其中，值列表可以是子查询的返回结果。使用ALL操作符之前，必须使用一个单行操作符，例如=、、、=等。例如：SQLSELECTempno,ename,sal,deptnoFROMscott.emp2WHEREsalALL(3SELECTAVG(sal)FROMscott.empGROUPBYdeptno);----------------------9.函数--------------------------------1.1日期时间函数SQL语句主要通过日期时间函数操纵日期和时间数据。在Oracle系统中，默认的日期格式为DD-MON-YY。函数说明SYSDATE()获取系统当前的日期值current_timestamp()获取当前的日期和时间值add_months(date,count)在指定的日期date上增加count个月last_day(date)返回日期date所在月的最后一天months_between(date1,date2)返回date1和date2间隔多少个月New_time(date,'this','other')将时间从this时区转变为other时区Next_day(date,'day')返回指定日期之后下一个星期几的日期。这里的day表示星期几greatest(date1,date2,…)从日期列表中选出最早的日期1.2正则表达式正则表达式（RegularExpressions）是符号和元素的集合，这个集合允许用户匹配特定的文本模式。正则表达式的功能是非常强大的。在Oracle数据库中，可以通过正则表达式函数来使用正则表达式。正则表达式中包含了大量的元字符（Metacharacter），例如，需要获得2000年到2009年之间的年份（包括2000年和2009年），就可以使用下面的正则表达式实现这个功能。^200[0-9]$其中，^、[0-9]和$都是元字符，^可以匹配一个字符串的开头；[0-9]可以匹配0-9之间的数字；$可以匹配一个字符串的结尾。正则表达式中的元字符元字符说明元字符说明^匹配字符串的开头位置\d数字字符$匹配字符串的末尾位置\D非数字字符*匹配前面的字符0次或多次。如a*b可以匹配b、ab、aab等\w字母字符+匹配前面的字符1次或多次。如a+b可以匹配ab、aab，但不能匹配b\W非字母字符?匹配前面的字符0次或1次。如a?b可以匹配b、ab\s空白字符{n}匹配前面的内容n次，其中n是正整数。如a{3}b可以匹配aaab\S非空白字符元字符说明元字符说明{n,m}匹配前面的内容至少n次，至多m次，其中n和m都是正整数{n,}匹配前面的元素至少n次.匹配除null之外的任意单个字符\n对前一次捕获的一个反向引用，其中n是正整数。如\1则重复1次上次匹x|y匹配x或y，x和y是一个或多个字符\A只匹配字符串的开头位置[a-z]匹配指定范围的任意一个字符\Z值匹配字符串的末尾位置\表示要匹配的是一个字符、常量或者反向引用。例如\n匹配换行符；\\匹配\；\(匹配(*?匹配前面的元素0或多次??匹配前面的元素0次或1次+?匹配前面的元素1或多次1.2.1正则表达式函数函数说明REGEXP_LIKE(string,pattern[,match_option])从string中搜索pattern参数中定义的正则表达式，可以使用match_option修改默认匹配选项。match_option可以被设置为：c（匹配时区分大小写。默认值）；i（匹配时不区分大小写）；n（允许使用可以匹配任意字符的操作符）；m（将string作为一个可以包含多行的字符串，以^字符为字符串的开始，$字符表示字符串的结束）REGEXP_INSTR(string,pattern[,start[,occurrence[,return_option[,match_option]]]])在string中查找pattern，并返回pattern所在的位置。其中，start表示搜索起始位置，默认值为1；occurrence表示返回第几次出现pattern的位置，默认值为1，即pattern第一次在string中出现的位置；return_option说明应该返回什么整数，如果该参数为0，表示要返回的整数是string中的第一个字符的位置；若该参数为非0的整数，说明要返回的整数位string中出现在pattern之后的字符的位置；match_option的可选值有c、i、n和m函数说明REGEXP_REPLACE(string,pattern[,replace_str[,start[,occurrence[,match_option]]]])在string中查找pattern，并将其替换为replace_str，其他参数的含义与REGEXP_INSTR函数完全相同REGEXP_SUBSTR(string,pattern[,start[,occurrence[,match_option]]])返回string中可以匹配pattern的一个子字符串，其开始位置由start指定。其他参数的含义与REGEXP_INSTR函数完全相同REGEXP_COUNT(string,pattern[,start[,match_option]])这是OracleDatabase11g新增加的一个函数。表示在string中查找pattern，并返回pattern在string中出现的次数1.3聚合函数检索数据不仅仅是把现有的数据简单地从表中取出来，很多情况下，还需要对数据执行各种统计计算。函数说明AVG(x)返回对一个数字列或计算列求取的平均值SUM(x)返回一个对数字列或计算列的汇总和MAX(x)返回一个数字列或计算列中的最大值MIN(x)返回一个数字列或计算列中的最小值COUNT(x)返回记录的统计数量MEDIA(x)返回x的中间值1.4字符函数字符串函数是Oracle系统中比较常用的一种函数。在使用字符串函数时，可以接受字符参数，这些字符可以是一个任意有效的表达式，也可以来自于表中的一列。然后字符函数会按照某种方式处理输入参数，并返回一个结果。函数说明ASCII(string)返回给定ASCII字符string的十进制值chr(integer)返回给定整数integer所对应的ASCII字符concat(string1,string2)连接字符串string1和字符串string2initcap(string)字符串string第一个字母变为大写，其余字母不变函数说明instr(string1,string2[,start][,occu