第07章简单函数

数据库操作与管理语言OracleSQL

第07章：简单函数本章目标单行函数字符函数数字函数转换函数日期函数正则表达式函数聚集函数分组单行函数单行函数只处理单个行，并且为每行返回一个结果。单行函数分为以下几种：字符函数处理字符串数字函数用于数学计算转换函数数据类型转换日期函数处理日期和时间正则表达式函数使用正则表达式搜索数据CONCAT()函数语法CONCAT(x,y)将x和y拼接起来，并返回新字符串例如，将customer表的first_name列和last_name列进行拼接SELECTCONCAT(first_name,last_name)FROMcustomers;查询结果如下图所示：INITCAP()函数语法INITCAP(x)将字母字符串转换为每个词首字母为大写，其他字母为小写例如，将products表的description列进行转换SELECTproduct_id,INITCAP(description)FROMproductsWHEREproduct_id<4;查询结果如下图所示：INSTR()函数-1语法INSTR(x,find_string[,start][,occurrence])返回指定字符串find_string在x中数字位置。可以指定开始搜索的位置start，并提供该字符串出现的次数occurrence。start和occurrence默认为1，表示从字符串开始的位置开始搜索，并返回第一次出现的位置例如，在products表的name列中查找‘Science‘出现的位置SELECTname,INSTR(name,'Science')FROMproductsWHEREproduct_id=1;查询结果如下图所示：INSTR()函数-2例如，在products表中的name列，从1位置开始查找第2个’e’出现的位置SELECTname,INSTR(name,'e',1,2)FROMproductsWHEREproduct_id=1;查询结果如下图所示：LENGTH()函数-1语法LENGTH(x)返回表达式中的字符数例如，计算products表中产品名的长度SELECTname,LENGTH(name)FROMproducts;查询结果如下图所示：LENGTH()函数-2同样可以计算数字、日期的长度例如，计算价格长度SELECTprice,LENGTH(price)FROMproducts;查询结果如右侧所示：例如，计算日期长度SELECTdob,LENGTH(dob)FROMcustomers;查询结果如右侧所示：LOWER()和UPPER()函数语法LOWER(column|expression)将字母字符值转换为小写UPPER(column|expression)将字母字符值转换为大写例如，分别将customers表中first_name列转换为大写，last_name列转换为小写SELECTUPPER(first_name),LOWER(last_name)FROMcustomers;查询结果如下图所示：LPAD()和RPAD()函数语法LPAD(x,width[,pad_string])将字符串值按右对齐排列，然后在左侧填充pad_string字符，以使总字符宽度为widthRPAD(x,width[,pad_string])将字符串值按左对齐排列，然后在右侧填充pad_string字符，以使总字符宽度为width例如SELECTRPAD(name,30,'.'),LPAD(price,8,'*+')FROMproductsWHEREproduct_id<4;LTRIM(),RTRIM()和TRIM()函数-1语法LTRIM(x[,trim_string])从x字符串左侧去除所有的trim_string字符串，如果没有指定trim_string字符串，则默认为去除左侧空白字符RTRIM(x[,trim_string])从x字符串右侧去除所有的trim_string字符串，如果没有指定trim_string字符串，则默认为去除右侧空白字符TRIM(trim_stringFROMx)从x字符串两侧去除trim_string字符串LTRIM(),RTRIM()和TRIM()函数-2例如SELECTLTRIM('HelloGailSeymour!'),RTRIM('HiDoreenOakley!abcabc','abc'),TRIM('0'FROM'000HeySteveButton!00000')FROMdual;查询结果如下图所示：NVL()函数语法NVL(x,value)用于将一个NULL值转换为另外一个值。如果x是NULL值的话返回value值，否则返回x值本身例如，查询customers表，如果phone列为NULL值，则显示UnknownPhoneNumberSELECTcustomer_id,NVL(phone,'UnknownPhoneNumber')FROMcustomers;查询结果如下图所示：NVL2()函数语法NVL2(x,value1,value2)如果x不为NULL值，返回value1，否则返回value2例如，查看customer表电话情况SELECTcustomer_id,NVL2(phone,'Known','Unknown')FROMcustomers;查询结果如下图所示：REPLACE()函数语法REPLACE(x,search_string,replace_string)从字符串x中搜索search_string字符串，并使用replace_string字符串替换。并不会修改数据库中原始值。例如，查找products表中name列，将‘Science’替换成'Physics'SELECTname,REPLACE(name,'Science','Physics')asnewnameFROMproductsWHEREproduct_id=1;查询结果如下图所示：SOUNDEX()函数语法SOUNDEX(x)返回代表x字符串的语音的表示形式，可用于查找一些语音相同但是拼写不同的单词例如SELECTlast_nameFROMcustomersWHERESOUNDEX(last_name)=SOUNDEX('whyte');查询结果如下图所示：SUBSTR()函数语法SUBSTR(x,start[,length])返回字符串中的指定的字符，这些字符从字符串的第start个位置开始，长度为length个字符；如果start是负数，则从x字符串的末尾开始算起；如果length省略，则将返回一直到字符串末尾的所有字符例如，从products表中提取name列从第2个字符开始，长度为7的字符串SELECTname,SUBSTR(name,2,7)FROMproductsWHEREproduct_id<4;查询结果如下图所示：数字函数数字函数接受数字输入，并以数字形式返回处理结果函数名说明举例ABS(value)返回value的绝对值SELECTABS(10),ABS(-10)FROMdual;返回：10和10CEIL(value)返回大于或等于value的最小整数SELECTCEIL(5.8),CEIL(-5.2)FROMdual;返回：6和-5FLOOR(value)返回小于或等于value的最大整数SELECTFLOOR(5.8),FLOOR(-5.2)FROMdual;返回：5和-6POWER(value,n)返回value的n次幂SELECTPOWER(2,1),POWER(2,3)FROMdual;返回：2和8MOD(m,n)返回m和n取余数的结果SELECTMOD(8,3),MOD(8,4)FROMdual;返回：2和0SQRT(value)对value进行开方SELECTSQRT(25),SQRT(5)FROMdual;返回：5和2.23606798TRUNC(value,n)对value进行截断。如果n>0，保留n位小数；n<0，则保留-n位整数位；n=0，则去掉小数部分SELECTTRUNC(5.75),TRUNC(5.75,1),TRUNC(5.75,-1)FROMdual;返回：5、5.7和0ROUND(value[,n])对value进行四舍五入，保存小数点右侧的n位。如果n省略的话，相当于n=0的情况。SELECTROUND(5.75),ROUND(5.75,1),ROUND(5.75,-1)FROMdual;返回：6、5.8和10转换函数将值从一种类型转换成另外一种类型，或者从一种格式转换为另外一种格式。主要介绍以下几种：TO_CHAR()TO_NUMBER()CAST()TO_CHAR()函数-1语法TO_CHAR(x[,format])将x转化为字符串。format为转换的格式，可以为数字格式或日期格式例如，将数字转换为字符串SELECTTO_CHAR(12345.67)FROMdual;查询结果如下图所示：TO_CHAR()函数-2将数字以指定格式输出例如，以指定99,999.99格式输出12345.67SELECTTO_CHAR(12345.67,'99,999.99')FROMdual;查询结果如下图所示：数字格式元素元素说明示例9数字位置(9的个数确定了显示的宽度)SELECTTO_CHAR(1234,’999999’)FROMdual;返回：__12340显示前导0SELECTTO_CHAR(1234,'099999')FROMdual;返回：001234$浮动的美元符号SELECTTO_CHAR(1234,'$999999')FROMdual;返回：$1234L浮动的当地货币符号SELECTTO_CHAR(1234,‘L999999')FROMdual;返回：￥1234.指定位置的小数点SELECTTO_CHAR(1234,'999999.99')FROMdual;返回：1234.00,指定位置的逗号SELECTTO_CHAR(1234,'999,999')FROMdual;返回：1,234MI右边的减号(负值)SELECTTO_CHAR(-1234,'999999MI')FROMdual;返回：1234-PR用括号括起负值SELECTTO_CHAR(-1234,'999999PR')FROMdual;返回：<1234>EEEE科学计数法(格式必须指定4个E)SELECTTO_CHAR(1234,'9999.99EEEE')FROMdual;返回：1.23E+03V乘以10的n次方(n=V之后的9的个数)SELECTTO_CHAR(1234,'9999V99')FROMdual;返回：123400B将0显示为空白，而不是0SELECTTO_CHAR(0.1234,'B99.9999')FROMdual;返回：.1234案例例如SELECTproduct_id,'Thepriceoftheproductis'||TO_CHAR(price,'$99.99')FROMproductsWHEREproduct_id<5;查询结果如下图所示：TO_NUMBER()函数语法TO_NUMBER(x[,format])将x转换为数字。可以指定format格式例如SELECTTO_NUMBER('970.13')+25.5FROMdual;SELECTTO_NUMBER('-$12,345.67','$99,999.99')FROMdual;查询结果如下图所示：CAST()函数语法CAST(xAStype)将x转换为指定的兼容的数据库类型。例如SELECTCAST(12345.67ASVARCHAR2(10)),CAST('9A4F'ASRAW(2)),CAST('05-7月-07'ASDATE),CAST(12345.678ASNUMBER(10,2))FROMdual;查询结果如下图所示：日期函数语法TO_DATE(x[,format])将x字符串转换为日期例如SELECTTO_DATE('2012-3-15','YYYY-MM-DD')FROMdual;查询结果如下图所示：日期格式元素年YYYY：4位数字的年，如：2008YY：2位数字的年，如：08月MM：两位数字的月份，如：09MONTH：月份的全称的大写形式MON：3位的月份天DD：月份中日的2位表示形式DAY：大写的星期几；Day：表示小写的星期几小时HH24：24小时进制HH：12小时进制分钟MI：2位的分钟数秒SS：2位的秒数正则表达式函数正则表达式函数允许搜索一个匹配模式字符串中的字符。例如，假设现有以下值1965196819711970如果想匹配1965到1968，那么可以使用以下正则表达式^196[5-8]$正则表达式中的元字符-1元字符含义举例\说明要匹配的字符是一个特殊字符、常量或者后者引用\n匹配换行符，\\匹配\，\(匹配(^匹配字符串的开头位置

如果A是字符串的第一个字符，^A匹配A

$匹配字符串的末尾位置

如果B是字符串的最后一个字符，$B匹配B

*匹配前面的字符0次或多次

ba*rk可以匹配brk、bark、baark等等

+匹配前面的字符1次或多次

ba+rk可以匹配bark、baark等等,但是不能匹配brk，也就是说，最少有以一次。

?匹配前面的字符0次或1次

ba?rk可以匹配bark、brk等等,但是不能匹配baark{n}匹配前面的字符恰好是n次，其中n是整数

hob{2}it可以匹配hobbit

{n,m}匹配前面的字符至少是n次，最多是m次，其中n，m都是整数

hob{2,3}it可以匹配hobbit或者hobbbit

.匹配除null以外的任意单个字符

hob.it中的.可以是任意的单个字符，如：hobsit等等

正则表达式中的元字符-2元字符含义举例(pattern)括号中pattern是一个子正则表达式，匹配指定pattern模式的一个子表达式aaa(x|y)可以匹配aaax或者aaay

x|y匹配x或yx|y可以匹配x或者y[abc]匹配中括号中的任意单个字符hello[abc]可以匹配helloa,hellob,helloc[a-z]匹配指定范围内的任意单个字符hell[a-z]可以匹配hello或者hellz

[::]指定一个字符类，可以匹配该类中的任何字符[:alphanum:]可以匹配字符0-9、A-Z、a-z

[:alpha:]可以匹配字符A-Z、a-z

[:blank:]可以匹配空格或tab键

[:digit:]可以匹配数字0-9

[:graph:]可以匹配非空字符

[:lower:]可以匹配小写字母a-z

[:print:]与[:graph:]类似，不同之处在于[:print:]包括空格字符

[:punct:]可以匹配标点符号.,""等等

[:space:]可以匹配所有的空字符

[:upper:]可以匹配大写字母A-Z

[:xdigit:]可以匹配十六进制数字0-9、A-F、a-f

\n这是对前一次匹配命中的一个后引用，其中n是一个正整数(.)\1可以匹配两个连续相同的非空字符。(.)可以匹配除null以外的任何单个字符，而\1则重复上一次匹配的内容，即再次匹配相同的字符，因此可以匹配两个连续相同的非空字符

REGEXP_LIKE()函数-1语法REGEXP_LIKE(x,pattern[,match_option])从x中搜索符合pattern模式的字符串match_option，有4种取值‘c’：指定区分大小写的匹配（这是默认设置）‘i’：指定不区分大小写的匹配‘n’：它允许你使用匹配任何字符的运算符‘m’：它把x作为一个多行REGEXP_LIKE()函数-2例如，从customers表中查找1965年到1968年出生的人SELECTcustomer_id,first_name,last_name,dobFROMcustomersWHEREREGEXP_LIKE(TO_CHAR(dob,'YYYY'),'^196[5-8]$');查询结果如下图所示：REGEXP_LIKE()函数-3例如，查找customers中first_name以J或j开始的客户SELECTcustomer_id,first_name,last_name,dobFROMcustomersWHEREREGEXP_LIKE(first_name,'^j','i');查询结果如下图所示：REGEXP_INSTR()函数-1语法REGEXP_INSTR(x,pattern[,start[,occurrence]])返回pattern在x中出现的位置例如，查找pattern第一次出现的位置SELECTREGEXP_INSTR('But,soft!Whatlightthroughyonderwindowbreaks?','l[[:alpha:]]{4}')ASresultFROMdual;查询结果如下图所示：REGEXP_INSTR()函数-2例如，搜索要匹配的pattern第2次出现的位置SELECTREGEXP_INSTR('But,soft!Whatlightthroughyonderwindowsoftlybreaks?','s[[:alpha:]]{3}',1,2)ASresultFROMdual;查询的结果如下图所示：REGEXP_INSTR()函数-3例如，从指定位置开始搜索，匹配第2次出现的o字母SELECTREGEXP_INSTR('But,soft!Whatlightthroughyonderwindowbreaks?','o',10,2)ASresultFROMdual;查询结果如下图所示：REGEXP_REPLACE()函数语法

REGEXP_REPLACE(x,pattern[,replace_string[,start[,occurrence[,match_option]]]])在x中搜索pattern，并使用replace_string字符串进行替换例如，搜索l[[:alpha:]]{4}并替换成‘sound’SELECTREGEXP_REPLACE('But,soft!Whatlightthroughyonderwindowbreaks?','l[[:alpha:]]{4}','sound')ASresultFROMdual;查询结果如下图所示：REGEXP_SUBSTR()函数语法REGEXP_SUBSTR(x,pattern[,start[,occurrence[,match_option]]])返回符合pattern模式的子字符串例如SELECTREGEXP_SUBSTR('But,soft!Whatlightthroughyonderwindowbreaks?','l[[:alpha:]]{4}')ASresultFROMdual;查询结果如下图所示：REGEXP_COUNT()函数语法

REGEXP_COUNT(x,pattern[,start[,match_option]])统计pattern在x字符串中出现的次数。Oracle11g新增函数。例如SELECTREGEXP_COUNT('But,soft!Whatlightthroughyonderwindowsoftlybreaks?','s[[:alpha:]]{3}')ASresultFROMdual;查询结果如下图所示：聚集函数聚集函数亦称分组函数、聚合函数。聚集函数可以对行集进行操作，并且为每组给出一个结果。聚集函数可以使用任何有效的表达式NULL值在聚集函数中将被忽略可以在聚集函数中使用DISTINCT关键字，排除重复值聚集函数的类型聚集函数分为以下几种AVG(x)：返回x的平均值COUNT(x)：返回统计的行数MAX(x)：返回x的最大值MEDIAN(x)：返回中间值MIN(x)：返回x的最小值STDDEV(x)：返回标准偏差SUM(x)：返回x的总计值VARIANCE(x)：返回x的方差AVG()函数-1语法AVG(x)获取x的平均值例如，统计products表商品的平均价格SELECTAVG(price)FROMproducts;查询结果如下图所示：AVG()函数-2例如，可以对每件商品加2后，对表达式求平均值SELECTAVG(price+2)FROMproducts;查询结果如下图所示：可以使用DISTINCT关键字排除重复项，进行求平均值SELECTAVG(DISTINCTprice)FROMproducts;查询结果如下图所示：COUNT()函数-1语法COUNT(x)统计查询返回的行数例如，统计products表的行数SELECTCOUNT(product_id)FROMproducts;查询结果如下图所示：COUNT()函数-2在统计时避免使用COUNT(*)，否则会使用更长的统计时间返回统计结果可以使用某列的列名或ROWID代替星号(*)例如，可以使用ROWID来统计products表的行数SELECTCOUNT(ROWID)FROMproducts;查询结果如下图所示：MAX()和MIN()函数-1语法

MAX(x)、MIN(x)分别统计x的最大值和最小值例如，统计products表price列的最大值和最小值SELECTMAX(price),MIN(price)FROMproducts;查询结果如下图所示：MAX()和MIN()函数-2MAX()和MIN()函数，也可以用来统计字符串和日期如果是用来统计字符串，那么将会对字母进行排序，最大值在最后底部，最小值在前面第一个。例如，Albert会在Zeb前面如果是日期的话，最大值会是最近的时间，最小值会是最早的时间例如，分别统计字符串列和日期列的最大值和最小值SELECTMAX(name),MIN(name)FROMproducts;SELECTMAX(dob),MIN(dob)FROMcustomers;查询结果如右图所示：STDDEV()函数语法STDDEV(x)返回标准偏差例如，获取products表price的标准偏差SELECTSTDDEV(price)FROMproducts;查询结果如下图所示：SUM()函数语法SUM(x)返回所有x中的值的总和例如，统计products表所有单价的总和SELECTSUM(price)FROMproducts;查询结果如下图所示：VARIANCE()函数语法VARIANCE(x)返回x的方差例如，统计products表price的方差SELECTVARIANCE(price)FROMproducts;查询结果如下图所示：分组有时需要对表中的行进行分组，然后统计每组的信息。例如，想统计products表中不同类型商品的平均价格，可以使用GROUPBY进行分组，然后再对每组进行统计例如，使用GROUPBY子句products表按product_type_id分组SELECTproduct_type_idFROMproductsGROUPBYproduct_type_id;查询结果如下图所示：按照多个列分组可以使用GROUPBY对多个列进行分组例如，对purchases表的product_id和customer_id分组SELECTproduct_id,customer_idFROMpurchasesGROUPBYproduct_id,customer_id;查询结果如下图所示：对分组行使用聚集函数-1可以对分组后的行使用聚集函数，聚集函数会统计每组中的值，对于每组分别统计后返回一个值例如，对product_type_id进行分组，然后再使用COUNT(ROWID)统计每组的数量SELECTproduct_type_id,COUNT(ROWID)FROMproductsGROUPBYproduct_type_idORDERBYproduct_type_id;查询结果如下图所示：对分组行使用聚集函数-2例如，计算products表中，每种类型商品的平均价格SELECTproduct_