SQL函数的说明
SQLServer的函数
Oracle的函数
将NULL更改为其他值的函数
IF…ELSE逻辑函数
除了在SQL查询中使用正常的表达式外，在具体的数据库管理系统中，还可以使
用任意的内置函数或者用户编写的存储数据库函数。用户可以使用函数来执行计算或基
于输入参数的其他操作，也可以将某种数据转换成其他数据类型或显示格式。本章将通
过具体的例子介绍如何使用这些函数。
SQL函数的使用
第章
SQL技术与网络数据库开发详解
·112·
9.1SQL函数的说明
在介绍函数的使用之前，先应知道SQL函数是不通用的。SQL函数与前面所讲的SQL语句不同，
它们在不同的数据库管理系统中不能通用，因为每一个数据库管理系统都有一套自己的SQL函数，只
有很少的函数在大多数DBMS中都能使用。为了让读者感受这一点，表9.1列出了一些例子。
表9.1不同DBMS的函数差异
功能Access函数SQLServer函数Oracle函数或语句MySQL函数
获取字符串的某部分MID()SUBSTRING()SUBSTR()SUBSTRING()
获取当前日期NOW()GETDATE()SYSDATECURDATE()
转换数据类型
有多个具体函数，例如
CDATE()用于将字符
转换为日期，CSTR()
将数字转换为字符
CONVERT()
有多个具体函数，例如
TO_DATE()用于将字符串
转换为日期，TO_CHAR()
将数字或日期转换为字符串
CONVERT()
由于SQL函数不通用，因此，如果在数据库应用程序的开发中使用了数据库函数，则其通用性和
移植性就会变得很差，所以在软件开发时应尽量采用程序设计语言中的函数，避免使用数据库函数。
9.2SQLServer的函数
本节将介绍SQLServer中的类型转换函数、日期函数、数值函数和字符函数，并对较经常使用的
函数举例说明。
9.2.1类型转换函数
转换函数将具体DBMS的数据值转换成其他数据类型或对其进行格式化。经常用到的转换是，将
日期和数字转换成指定的字符串格式，或者将字符串转换成有效的日期或数值。
在SQLServer中，使用CONVERT()和CAST()两个函数转换数据类型。
1．CONVERT()函数
CONVERT()函数的语法格式为：
CONVERT(datatype[(length)],expression,[style])
其中，datatype为数据类型，如果是CHAR、VARCHAR、BINARY或VARBINARY数据类型，
则可以选择length参数设置长度；expression为表达式，如果要将日期型数据转换为字符型数据，还可
以使用style参数设置日期显示格式。style参数的取值与日期显示格式如表9.2所示。
第9章SQL函数的使用
·113·
表9.2style参数取值及对应日期格式
style值（返回yy）style值（返回yyyy）标准显示格式
0（或者100）默认标准monddyyhh:miAM（或PM）
1101美国mm/dd/yy
2102ANSIyy.mm.dd
3103英国/法国dd/mm/yy
4104德国dd.mm.yy
5105意大利dd-mm-yy
6106ddmonyy
7107mondd,yy
8108hh:mi:ss
9（或者109）默认标准+毫秒mondd,yyyyhh:mi:ss:msAM（或PM）
10110美国mm-dd-yy
11111日本yy/mm/dd
12112ISOYymmdd
13（或者113）欧洲默认+毫秒ddmonyyyyhh:mi:ss:ms（24小时）
14114hh:mi:ss:ms（24小时）
说明：style参数可以取两类值，如果从第一类取值，则返回日期的年份为2位；如果从第二类取值，
则返回日期年份为4位。
当把一个日期转换为字符串时，CONVERT()函数默认的输出格式是“monddyyhh:miAM(或
PM)”。从表中可见，CONVERT()函数将日期转换为字符串时提供了大量的日期时间显示格式，这给
用户提供了很大的方便。
表9.3列出了几个使用CONVERT()函数转换数据类型的例子，供读者参考。
表9.3CONVERT()函数的例子
功能函数实现
字符到数字CONVERT(numeric,''15'')
数字到字符CONVERT(char,12)
字符到日期CONVERT(datetime,''15-09-1977'')，CONVERT(datetime,''SEP15,1977'')
日期到字符CONVERT(char,GETDATE())，CONVERT(char,GETDATE(),102)
十六进制到二进制CONVERT(binary,''3C''))
二进制到十六进制CONVERT(char,二进制字段)
获取当前系统时间CONVERT(char,GETDATE(),8)
【例9.1】从Student表中，查询所有学生的姓名、出生日期，并将日期转换为德国标准日期格式
的字符串显示，其中年份为4位。要求查询结果按出生日期升序排序。
分析：将出生日期转换为字符串，应当使用CONVERT()函数，因为需要按照德国日期格式显示，所
以从表9.2中查找设置德国标准的style值，又因为年份要求是4位，所以选择style参数的值为
104。下面是具体的SELECT语句。
SQL技术与网络数据库开发详解
·114·
SELECT姓名,CONVERT(CHAR,出生日期,104)AS生日
FROMstudent
ORDERBY出生日期
运行结果如图9.1所示。
图9.1例9.1运行结果
提示：如果SQL运行环境为Oracle，则设置别名时不能使用AS关键字，将SELECT中的AS去掉
即可。
2．CAST()函数
CAST()函数是SQL92标准函数。使用CAST()函数也可以转换数据类型，但是在格式化日期时间
数据方面不如CONVERT()函数方便。CAST()函数的语法格式为：
CAST(expressionASdatatype[(length)])
其中，expression为表达式，datatype为数据类型。如果是CHAR、VARCHAR、BINARY或
VARBINARY数据类型，则可以选择length参数设置长度。
【例9.2】从Student表中，查询所有学生的姓名、出生日期，并将日期转换为字符串显示。要求
查询结果按出生日期升序排序。
SELECT姓名,CAST(出生日期ASchar)AS生日
FROMstudent
ORDERBY出生日期
运行结果如图9.2（a）所示。
从图中可以看出，出生日期已经被转换为字符串。如果只想要日期部分而不想要时间，则可以在
类型后设置长度。上面的SELECT语句可以写为如下形式。
SELECT姓名,CAST(出生日期ASchar(10))AS生日
FROMstudent
ORDERBY出生日期
运行结果如图9.2（b）所示。
本例中，因为只给“出生日期”分配了10个字节的长度，所以只把前面的日期部分留下，而后面
的时间部分就被自动截掉了。
注意：CAST()函数不能改变原表字段的数据类型。
第9章SQL函数的使用
·115·
（a）（b）
图9.2例9.2运行结果
9.2.2日期函数
日期函数允许操作日期时间值。SQLServer支持的日期函数有GETDATE、DATEADD、DATEDIFF、
DATENAME和DATEPART等。
1．GETDATE函数
GETDATE函数用于获取当前系统时间。其格式为：
GETDATE()
例如，在查询分析器中输入如下SELECT语句并运行后，即可获得当前系统时间。
SELECTGETDATE()
运行结果如图9.3所示。
图9.3使用GETDATE获取当前时间
2．DATEADD函数
DATEADD函数用于在指定日期上增加年、月、日或者时间等，其返回值为日期型数据。其格
式为：
DATEADD(datepart,number,date)
其中，datepart参数规定在日期的哪个部分（如年份、月份等）增加数值。表9.4列出了datepart
参数的可用值。
表9.4datepart参数的可用值
datepart参数值参数值可用缩写参数值范围
Yearyy,yyyy1753～9999
quarterqq,q1～4
Monthmm,m1～12
Dayofyeardy,y1～366
SQL技术与网络数据库开发详解
·116·
续表
datepart参数值参数值可用缩写参数值范围
Daydd,d1～31
Weekwk,ww0～51
Weekdaydw1～7（1为星期日）
Hourhh0～23
minutemi,n0～59
secondss,s0～59
millisecondms0～999
了解datepart参数的可用值后，就可以控制在日期的哪个部分增加值。例如：
DATEADD(year,10,GETDATE())
是在当前时间的“年”上增加了10年，并返回10年后的日期。而
DATEADD(month,10,GETDATE())
是在当前时间的“月”上增加了10个月，并返回10个月后的日期。
说明：datepart参数值也可以使用缩写。例如，DATEADD(mm,10,GETDATE())也是在当前时间上增加
10个月。
【例9.3】从Student表中，查询所有学生的姓名、出生日期、出生后的第10000天和出生后的第
800个月，要求查询结果按出生日期升序排序。
SELECT姓名,
出生日期,
DATEADD(DAY,10000,出生日期)AS出生后第10000天,
DATEADD(MONTH,800,出生日期)AS出生后第800月
FROMstudent
ORDERBY出生日期
运行结果如图9.4所示。
图9.4例9.3运行结果
3．DATEDIFF函数
DATEDIFF函数用于获取两个日期间的差，并返回数值数据。其格式为：
DATEDIFF(datepart,date1,date2)
第9章SQL函数的使用
·117·
其中，datepart参数的说明同上，date1和date2是日期或者日期格式的字符串。
【例9.4】从Student表中，查询所有学生的姓名、出生日期和年龄。要求查询结果按出生日期降
序排序。
SELECT姓名,
出生日期,
DATEDIFF(year,出生日期,GETDATE())AS年龄
FROMstudent
ORDERBY出生日期DESC
运行结果如图9.5所示。
图9.5例9.4运行结果
查询语句中的DATEDIFF函数如下：
DATEDIFF(year,出生日期,GETDATE())
返回的是当前时间和出生日期之间的年份的差距，即年龄。如果写成如下形式：
DATEDIFF(month,出生日期,GETDATE())
则返回的是当前时间和出生日期之间的月份的差距，即返回相差多少个月。
4．DATENAME函数
DATENAME函数用于获取日期的一部分，并以字符串形式返回。其格式为：
DATENAME(datepart,date)
其中，datepart参数的说明同上，date是日期或者日期格式的字符串。例如，假设当前日期为2008
年3月25日，则DATENAME(month,GETDATE())的结果为字符串''03''，DATENAME(dd,GETDATE())
的结果为字符串''25''。
注意：假设当前日期为2008年3月5日，则DATENAME(dd,GETDATE())返回的结果为字符串''5''，
而并非是''05''。
【例9.5】从Student表中，查询每个月1号出生的所有学生。要求查询结果按出生日期降序排序。
SELECT
FROMstudent
WHEREDATENAME(day,出生日期)=''1''
ORDERBY出生日期DESC
SQL技术与网络数据库开发详解
·118·
注意：DATENAME函数返回的是字符串，因此必须与字符串（''1''）比较。
运行结果如图9.6所示。
图9.6例9.5运行结果
5．DATEPART函数
DATEPART函数用于获取日期的一部分，并以整数值返回。其格式为：
DATEPART(datepart,date)
其中，datepart参数的说明同上，date是日期或者日期格式的字符串。例如，假设当前日期为2008
年3月25日，则DATEPART(month,GETDATE())的结果为数值3，DATEPART(dd,GETDATE())的
结果为数值25。
【例9.6】从Student表中查询每个月1号出生的所有学生。要求查询结果按出生日期降序排序。
SELECT
FROMstudent
WHEREDATEPART(day,出生日期)=1
ORDERBY出生日期DESC
注意：DATEPART函数返回的是数值，因此必须与数值（1）比较。
运行结果如图9.7所示。
图9.7例9.6运行结果
SQLServer中除上述日期时间函数以外，还有YEAR、MONTH、DAY等3个函数，分别用于获
取日期数据的年份、月份和日期部分，这3个函数的返回值都是数值型。
9.2.3数学函数
数学函数允许操作数值数据。表9.5中列出了常用的SQLServer数学函数及其说明供读者参考。
表9.5数学函数及其说明
函数参数说明
ABS（numeric_表达式）绝对值
ACOS（float_表达式）
返回以弧度表示的角度值。该角度值的余弦为给定的float表达式，
本函数亦称反余弦
ASIN（float_表达式）
返回以弧度表示的角度值。该角度值的正弦为给定的float表达式，
亦称反正弦
第9章SQL函数的使用
·119·
续表
函数参数说明
ATAN（float_表达式）
返回以弧度表示的角度值。该角度值的正切为给定的float表达式，
亦称反正切
ATN2（float_表达式，float_表达式）
返回以弧度表示的角度值。该角度值的正切介于两个给定的float表
达式之间，亦称反正切
COS（float_表达式）返回给定表达式中给定角度（以弧度为单位）的三角余弦值
SIN（float_表达式）返回给定角度（以弧度为单位）的三角正弦值（近似值）
COT（float_表达式）返回给定float表达式中指定角度（以弧度为单位）的三角余切值
TAN（float_表达式）返回float表达式的正切值
CEILING（numeric_表达式）返回大于或等于所给数字表达式的最小整数
DEGREES（numeric_表达式）当给出以弧度为单位的角度时，返回相应的以度数为单位的角度
EXP（float_表达式）返回所给的float表达式的指数值
FLOOR（numeric_表达式）返回小于或等于所给数字表达式的最大整数
LOG（float_表达式）返回给定float表达式的自然对数
LOG10（float_表达式）返回给定float表达式的以10为底的对数
PI()返回π的常量值
POWER（numeric_表达式,y）返回给定数字表达式的y次方
RADIANS（numeric_表达式）对于在数字表达式中输入的度数值返回弧度值
RAND（[seed]）返回0～1的随机float值
ROUND（numeric_表达式,length）返回数字表达式并四舍五入为指定的长度或精度
SIGN（numeric_表达式）返回给定表达式的正（+1）、零（0）或负（-1）号
SQRT（float_表达式）返回给定表达式的平方根
【例9.7】使用数学函数，计算30°角的正弦值。
分析：首先使用RADIANS函数计算30°的弧度值，其次对弧度值使用SIN函数求正弦值，最后对结
果进行四舍五入计算。在查询分析器中输入如下SELECT语句并运行。
SELECTROUND(SIN(RADIANS(30.0)),1)AS"30°的正弦值"
说明：将AS后的别名（30°的正弦值）放入双引号的原因是别名中有数字。
运行结果如图9.8所示。
图9.8例9.7运行结果
9.2.4字符函数
字符函数允许操作字符数据。表9.6中列出了常用的SQLServer字符函数及其说明供读者参考。
SQL技术与网络数据库开发详解
·120·
表9.6字符函数及其说明
函数参数说明
ASCII（char_表达式）返回字符表达式结果的最左边字符的ASCII码
CHAR（integer_表达式）返回ASCII码为指定整数的字符
CHARINDEX（char_表达式1，char_表达式2[，start]）
返回字符表达式1在字符表达式2中的起始位置。start
参数指定从字符表达式2的哪个位置开始向后寻找
DIFFERENCE（char_表达式，char_表达式）
比较两个字符串的相似性，返回从0～4的值，值为4
时是最好的匹配
LEFT（char_表达式，integer_表达式）返回字符串左面的指定个数的字符
LOWER（char_表达式）将字符串表达式中的所有大写字母全部转换成小写
字母
LTRIM（char_表达式）删除字符串左边所有的空格
REPLICATE（char_表达式，integer_表达式）以指定的次数重复字符表达式
REVERSE（char_表达式）返回字符表达式的逆序
RIGHT（char_表达式，integer_表达式）返回字符串右面的指定个数的字符
RTRIM（char_表达式）删除字符串右边所有的空格
SOUNDEX（char_表达式）
返回由4个字符组成的代码（SOUNDEX）以评估两
个字符串的相似性
SPACE（integer_表达式）
返回一个由重复空格组成的字符串。空格数等于
，若整数表达式为负数，则返回一个
空字符串
STR（float_expression[,length[,decimal]]）
由数字数据转换来的字符数据。length是总长度，包
括小数点、符号、数字或空格，默认值为10。decimal
是小数点右边的位数
STUFF（char_表达式，start，length，char_表
达式）
删除指定长度的字符并在指定的起始点插入另一组
字符
SUBSTRING（表达式，start，length）
返回表达式中start位置开始的length长度的子串，该
子串可能是字符串，也可能是二进制字符串
UPPER（char_表达式）
将字符串表达式中的所有小写字母全部转换成大写
字母
为了让读者感受使用字符函数查询数据的方便，在此使用一个Foreign_teacher表（外籍教师表）
举例说明，如表9.7所示。
表9.7Foreign_teacher表
tidtnamesexcountrybirthhiredatetelemail
0001TomGreenmUSA1967-1-212003-8-1513722112908tomcat@yahoo.com.cn
0002JackWhitemUK1972-5-12006-3-1013722112903jack111@sina.com
0003MarryYangfCanada1977-12-302006-3-1013722112905marry_771230@yahoo.com.cn
0004SiqinbatermMongolia1981-9-142008-2-2013722112906brjdsiqin@yahoo.com.cn
第9章SQL函数的使用
·121·
续表
tidtnamesexcountrybirthhiredatetelemail
0005NapoleonmFrance1961-10-122005-6-3013722111840NULL
0006GadamerenmGermany1968-4-62001-2-1013722115566NULL
0007WulanqiqigefUSA1979-9-302007-12-113722119999wulan@163.com
创建Foreign_teacher表结构的SQL语句和插入记录的SQL语句如下所示。
CREATETABLEforeign_teacher
(
tidchar(6)NOTNULL,
tnamechar(20)NOTNULL,
sexchar(1)NOTNULL,
countryvarchar(30)NOTNULL,
birthsmalldatetime,
hiredatesmalldatetime,
telchar(15),
emailvarchar(80)
)
INSERTINTOforeign_teacher(tid,
tname,
sex,
country,
birth,
hiredate,
tel,
email)
VALUES(''0001'',
''TomGreen'',
''m'',
''USA'',
''1967-1-21'',
''2003-8-15'',
''13722112908'',
''tomcat@yahoo.com.cn'')
……
【例9.8】从Foreign_teacher表中，查询tomgreen老师的联系电话（tel）和电子邮件（email）。
分析：人们经常会忽视英文字母的大小写，例如，将TomGreen写为tomgreen等，此时，如果数据库
管理系统没有自动转换匹配的功能，则会将这两个字符串看作是不同人的姓名，从而导致查询
出错。为了解决这类问题，应当将数据库中的字符串的所有字母转换为大写（或小写）字母，
然后与大写（小写）字母的字符串比较。例如，可以使用下面的SELECT语句。
SELECTtname,tel,email
FROMforeign_teacher
WHEREUPPER(tname)=''TOMGREEN''
SQL技术与网络数据库开发详解
·122·
说明：SQLServer2000可自动转换大小写字母进行匹配，但为了保险起见，查询英文字符串时建议使
用上述方法进行查询。
运行结果如图9.9所示。
图9.9例9.8运行结果
【例9.9】从Foreign_teacher表中查询所有教师的姓名（tname）、国家（country）、雇佣日期（hiredate）
和联系电话（tel），并将姓名和国家合并为一列显示。
SELECTtname+''(''+country+'')''AS"姓名（国家）",
hiredateAS雇佣日期,
telAS联系电话
FROMforeign_teacher
说明：将AS后的别名（姓名（国家））放入双引号的原因是别名中含有圆括号。
运行结果如图9.10所示。
图9.10例9.9运行结果1
从图9.10中看到姓名和国家之间的距离较大，这种问题由姓名（tname）字段后的尾随空格引起，
处理的方法是使用RTRIM函数将姓名的尾随空格去掉，然后再拼接，如下所示。
SELECTRTRIM(tname)+''(''+country+'')''AS"姓名（国家）",
hiredateAS雇佣日期,
telAS联系电话
FROMforeign_teacher
运行结果如图9.11所示。
图9.11例9.9运行结果2
在查询数据时，会遇到这样一种问题——要查询所有德国籍教师时，记错了德国的英文名字，将
Germany错记为Germeny，从而查询出错。要处理这类问题，可以使用SOUNDEX函数，因为该函数
能对字符串进行发音比较而不是字母比较。
第9章SQL函数的使用
·123·
【例9.10】从Foreign_teacher表中，查询所有德国籍教师的姓名（tname）、出生日期（birth）和
电子邮件（email）。
SELECTtname,birth,email
FROMforeign_teacher
WHERESOUNDEX(country)=SOUNDEX(''Germeny'')
运行结果如图9.12所示。
图9.12例9.10运行结果
可见，即使拼错了德国的英文名字，使用SOUNDEX函数还是能够查到正确的结果。但是，使用
SOUNDEX函数查找汉字就不行了，如下面的例题所示。
【例9.11】从Student表中查询名叫“张三”的学生的所有信息，将“张三”故意写错为读音相似
的“张叁”，以便测试SOUNDEX函数对汉字的支持。
SELECT
FROMstudent
WHERESOUNDEX(姓名)=SOUNDEX(''张叁'')
运行结果如图9.13所示。
图9.13例9.11运行结果
从运行结果可以看出SOUNDEX函数并不支持汉字的读音比较。
9.3Oracle的函数
本节将介绍Oracle中的类型转换函数、日期函数、数值函数和字符函数，并对较经常使用的函数
举例说明。
9.3.1类型转换函数
Oracle中的类型转换函数要比SQLServer的多一些。表9.8列出了Oracle的类型转换函数及其简
SQL技术与网络数据库开发详解
·124·
单说明供读者参考。
表9.8Oracle的类型转换函数
函数参数说明
CHARTOROWID（string）
将一个AAAAAAAA.BBBB.CCCC格式的字符串转换为
ROWID类型
CONVERT（string，目标字符集，源字符集）将源字符集的字符串转换为目标字符集的字符串
HEXTORAW（string）将一个用字符串表示的16进制数转换成其字节值
RAWTOHEXT（raw_value）将一个原始列值转换成16进制字符串
ROWIDTOCHAR（rowid）将ROWID伪列的值转换为可显示字符串
TO_CHAR（number[，format]）将一个数值转换成字符串
TO_CHAR（date，format）按照format格式，将一个日期转换为字符串
TO_DATE（string，format）按照format格式，将一个字符串转换为日期
TO_LABEL（string，format）将字符串转换成MLSLABEL数据类型
TO_MULTI_BYTE（string）
将一个单字节字符串转换成支持多字节字符集语言中的
多字节字符串
TO_NUMBER（string[，format]）将一个数字字符串转换为相应数值
TO_SINGLE_BYTE（string）将多字节字符转换成相应的单字节字符
下面详细介绍经常使用的TO_CHAR和TO_DATE函数。
1．TO_CHAR函数
TO_CHAR函数可以将一个数值或者日期转换为指定格式的字符串。
（1）将数值转换为字符串
使用TO_CHAR函数将数值转换为字符串的语法格式如下所示。
TO_CHAR（number[，format]）
如果不指定格式（format），Oracle将会把number转换成最简单的字符串形式，如果是负数则在
前面加一个减号（-）。不过在多数情况下，用户还是想以特定的格式显示number，因此，需要设置
format参数。表9.9列出了绝大多数Oracle可用的数值格式，并通过例子说明一个给定数字的结果字
符串形式。
表9.9Oracle的数值格式模型
元素说明示例值结果
9
返回指定位数的数值，前导0
显示为空格
9999
128
-256
1234567
456.655
''128''
''-256''
''####''
''457''
9插入小数点9999.99
128
-256
1234567
456.655
''128.00''
''-256.00''
''#######''
''456.66''
第9章SQL函数的使用
·125·
续表
元素说明示例值结果
9
在结果字符串的指定位置插入
逗号
9,999,999
128
1234567
''0.68''
''128''
''1,234,567''
''1''
$返回值前面加一个美元符号$99,999128''$128''
B
结果的整数部分，如果是0就显
示成空格
B9999.9
128
-256
0.44
''128.0''
''-256.0''
.4
MI返回末尾带减号的负数9999MI
128
-256
''128''
''256-''
S9999
128
-256
''+128''
''-256''
S返回带有正负号的数值
9999S
128
-256
''128+''
''256-''
PR用尖扩号包围负数9999PR
128
-256
''128''
''<256>''
D在指定位置插入小数点9999D99
128
-256
76.238
''128.00''
''-256.00''
''76.24''
G在当前位置插入分组符9G999
128
-256
-1234
''128''
''-256''
''-1,234''
C在指定位置返回ISO货币符号C999128USD128
L在指定位置返回国家货币符号L9,9991234$1,234
EEEE以科学计数法表示数值9.9EEEE
27
128
0.078
2.7E+01
1.3E+02
7.8E-02
（2）将日期转换为字符串
使用TO_CHAR函数将日期转换为字符串的语法格式如下所示。
TO_CHAR（date，format）
TO_CHAR函数按format参数指定的格式将日期转换成相应的字符串形式。表9.10列出了Oracle
的日期格式化元素。
表9.10Oracle的日期格式化元素
元素说明
AD（或A.D.）AD（或A.D.）指示符
AM（或A.M.)AM（或A.M.）指示符
SQL技术与网络数据库开发详解
·126·
续表
元素说明
BC（或B.C.）BC（或B.C.）指示符
CC日期的世纪部分
D星期几（1～7）
DAY星期中每一天的名字
DD月中的天数（1～31）
DDD年中的天数（1～365）
DY星期几的缩写（SUN～SAT）
IWISO标准的年中的星期
IYY,IYIISO年的最后三、二、一位
IYYYISO年
HH（或HH12）小时（1～12）
HH24小时（0～23）
MI分钟（0～59）
MM月份（1～12）
MONTH月份名
MON月份名的缩写
RM月份的罗马数字表示（I～XII）
RR年的最后两位
Q年的季度
SS秒（0～59）
SSSSS从午夜计算的秒数（0～86399）
W月中的星期数（1～5）
WW年中的星期数（1～53）
Y年份的最后1位
YY年份的最后2位
YYY年份的最后3位
YYYY年份的最后4位
【例9.12】从Foreign_teacher表中，查询所有2007年以前雇佣的外籍教师的教师编号（tid）、姓
名（tname）和国家（country）。
SELECTtid教师编号,
tname姓名,
country国家
FROMForeign_teacher
WHERETO_NUMBER(TO_CHAR(hiredate,''YYYY''))<2007
注意：在Oracle中给字段取别名时，不能使用“AS”关键字，直接用空格隔开即可。
运行结果如下所示。
第9章SQL函数的使用
·127·
教师编号姓名国家
-------------------------------------------------
0001TomGreenUSA
0002JackWhiteUK
0003MarryYangCanada
0005NapoleonFrance
0006GadamerenGermany
本例中首先使用TO_CHAR函数提取hiredate的年份并转换为字符串，然后使用TO_NUMBER函
数将得到的字符串转换为数值与2007比较。
2．TO_DATE函数
TO_DATE函数根据给定的格式将一个字符串转换成日期值，其语法格式如下所示。
TO_DATE（string，format）
表9.10中的掩码元素同样适合format参数。
【例9.13】从Foreign_teacher表中，查询所有60年代出生的教师姓名、出生日期和国家，并按生
日升序排序。
SELECTtname姓名,
birth生日,
country国家
FROMForeign_teacher
WHEREbirthBETWEENTO_DATE(''1960-1-1'',''yyyy-mm-dd'')ANDTO_DATE(''1969-12-31'',''yyyy-mm-dd
'')
ORDERBYbirth
运行结果如下所示。
姓名生日国家
-------------------------------------------------------
Napoleon12-10月-61France
TomGreen121-1月-67USA
Gadameren06-4月-6800:00:00Germany
本例中使用了TO_DATE函数将日期格式的字符串转换成日期型数值与birth进行了比较。
9.3.2日期函数
Oracle有ADD_MONTHS、LAST_DAY、MONTHS_BETWEEN、NEW_TIME、NEXT_DAY、
ROUND、TRUNC等日期函数，下面详细介绍这些函数的内容。
1．ADD_MONTHS函数
ADD_MONTHS函数的格式为：
ADD_MONTHS(date,number)
该函数用于在参数date上加上number个月返回一个新月值。如果number为负数，则返回值为date
SQL技术与网络数据库开发详解
·128·
之前几个月的日期。下面的例子返回2008年3月27日的6个月以后的日期。
SELECTADD_MONTHS(TO_DATE(''2008-3-27'',''yyyy-mm-dd''),6)
FROMdual
又例如，下面的例子返回2008年3月27日3个月以前的日期。
SELECTADD_MONTHS(TO_DATE(''2008-3-27'',''yyyy-mm-dd''),-3)
FROMdual
2．LAST_DAY函数
LAST_DAY函数的格式为：
LAST_DAY(date)
该函数用于获取date所在月份最后一天的日期。下面的例子返回2015年2月份的最后一天的日期。
SELECTLAST_DAY(TO_DATE(''2015-02-01'',''yyyy-mm-dd''))
FROMdual
3．MONTHS_BETWEEN函数
MONTHS_BETWEEN函数的格式为：
MONTHS_BETWEEN(date1,date2)
该函数用于获取两个日期date1和date2之间的月份。如果两个日期月份内的天数相同，例如两个
都是某月的20日，则该函数会返回一个整数；否则，返回一个带有小数的数值，就是以每天为1/31
月来计算月中剩余的天数。如果date2比date1早（date1>date2），则返回负数。
【例9.14】从Foreign_teacher表中查询所有至少工作3年的教师姓名、雇佣日期和国家，并按雇
佣日期升序排序。
SELECTtname姓名,
hiredate雇佣日期,
country国家
FROMForeign_teacher
WHEREMONTHS_BETWEEN(hiredate,SYSDATE)>=123
ORDERBYhiredate
说明：Oracle中使用SYSDATE获取当前系统时间。
运行结果如下所示。
姓名雇佣日期国家
--------------------------------------------------------
Gadameren10-2月-01Germany
TomGreen15-8月-03USA
如果SQL运行环境为SQLServer，则上面SELECT语句的条件表达式
MONTHS_BETWEEN(hiredate,SYSDATE)>=123
被替换为
DATEDIFF(month,hiredate,GETDATE())>=123
第9章SQL函数的使用
·129·
即可。
4．NEW_TIME函数
NEW_TIME函数的格式为：
NEW_TIME(date,zone1,zone2)
该函数用于将zone1时区的日期时间date转换成zone2时区的日期时间。表9.11列出了Oracle所
有有效的时区供读者参考。
表9.11Oracle有效时区
代码描述
ADT大西洋夏时制时间
AST大西洋标准时间
BDT白令海夏时制时间
BST白令海标准时间
CDT中部夏时制时间
CST中部标准时间
EDT东部夏时制时间
EST东部标准时间
GMT格林威治标准时间
HDT阿拉斯加/夏威夷夏时制时间
HST阿拉斯加/夏威夷标准时间
MDT山区夏时制时间
MST山区标准时间
NST纽芬兰标准时间
PDT太平洋夏时制时间
PST太平洋标准时间
YDT育空夏时制时间
YST育空标准时间
5．NEXT_DAY函数
NEXT_DAY函数的格式为：
NEXT_DAY(date,day)
该函数返回离指定日期（date）最近的星期（day）的日期。例如，下面的例子返回离2010年5月
4日最近的星期一的日期。
SELECTNEXT_DAY(TO_DATE(''2010-05-04'',''yyyy-mm-dd''),2)
FROMdual
运行结果为：
NEXT_DAY(TO_DA
------------------------
03-5月-10
SQL技术与网络数据库开发详解
·130·
说明：在Oracle中，星期日属于每个星期的第一天，所以星期一为2。
6．ROUND函数
ROUND函数的格式为：
ROUND(date,format)
该函数能够把date四舍五入到最接近格式元素指定的形式。例如，如果想把当前时间（2007-3-27
3:22:32）四舍五入到最近的小时，可以用如下查询语句。
SELECTROUND(SYSDATE,''HH'')
FROMdual
运行结果如下所示。
ROUND(SYSDATE,''HH'')
---------------------------------
2007-03-273:00:00
说明：Oracle的日期格式默认为“DD-MON-YY”，如果想改为“yyyy-mm-ddhh24:mi:ss”，则应当使
用如下语句更改会话。
ALTERSESSIONSETNLS_DATE_FORMAT=''yyyy-mm-ddhh24:mi:ss'';
9.3.3数值函数
表9.12中列出了常用的Oracle数值函数及其说明供读者参考。
表9.12常用Oracle数值函数及其说明
函数参数说明
ABS（number）返回绝对值
CEIL（number)返回与给定参数相等或比给定参数大的最小整数
COS、SIN、TAN（number）返回给定角度（以弧度为单位）的三角余弦值、正弦值和正切值
COSH、SINH、TANH（number）返回给定角度的反余弦值、反正弦值和反正切值
EXP（number）返回所给值的指数值
FLOOR（number）返回与给定参数相等或比给定参数小的最大整数
LN（number）返回给定参数的自然对数
LOG（base,number）返回给定数值的以base为底的对数
MOD（n,m）返回n除m的模
POWER（x,y）返回x的y次方
ROUND（number,length）返回number，并四舍五入为指定的长度或精度
SIGN（number）返回给定数值的正（+1）、零（0）或负（-1）号
SQRT（number）返回给定数值的平方根
TRUNC（number,decimal-pluces）返回值为按decimal-pluces截断的给定数值
第9章SQL函数的使用
·131·
【例9.15】使用POWER函数求9的6次方。
SELECTPOWER(9,6)FROMdual;
运行结果如下所示。
POWER(9,6)
-------------------
531441
9.3.4字符函数
表9.13中列出了常用的Oracle字符函数及其说明供读者参考。
表9.13常用Oracle字符函数及其说明
函数参数说明
CHR（number）返回与所给数值参数相等的字符
CONCAT（string1,string2）返回字符串连接结果
INITCAP（string）
该函数将参数的第一个字母变为大写，其他的字母则转换
成小写
INSTR（input_string,search_string[,n[,m]]）
从输入字符串的第n个字符开始查找搜索字符串的第m次
出现
LENGTH（string)返回输入字符串的字符数
LOWER（string）将输入字符串全部转换为小写字母
LPAD（string,n[,pad_chars]）
在输入字符串的左边填充上pad_chars指定的字符，将其拉
伸至n个字符长
LTRIM（string)从输入字符串中删除所有前导空格，即左边的空格
NLSSORT（string）对输入字符串的各个字符进行排序
REPLACE（string,search_string[,replace_string]）
将输入字符串中出现的所有search_string都替换为
replace_string，如果不指定replace_string，则删除全部
search_string
RPAD（string,n[,pad_chars]）
在输入字符串的右边填充上pad_chars指定的字符，将其拉
伸至n个字符长
RTRIM（string）从输入字符串中删除右边的所有空格
SOUNDEX（string）返回所有在发音上与输入字符串相似的字符串
SUBSTR（string,start[,length]）返回输入字符串中从第start位开始length长的一部分
UPPER（string）将输入字符串全部转换成大写字母
【例9.16】使用SUBSTR函数截取身份证号码中的生日信息。
SELECTSUBSTR(''150102197709142019'',7,6)FROMdual;
运行结果如下所示。
SUBSTR(''
SQL技术与网络数据库开发详解
·132·
--------------
19770914
9.4将NULL更改为其他值的函数
数据库操作中，有时需要将表中某字段的NULL值全部更改为其他值，这样有利于进行各种运算
和统计。对于这种功能，DBMS给用户提供了相应的函数，只是在不同的DBMS中函数的名称和用法
稍有不同。
9.4.1SQLServer的ISNULL函数
SQLServer中的ISNULL函数可以将NULL值更改为其他值，其语法如下所示。
ISNULL(check_expression,replacement_value)
具体说明如下：
nullcheck_expression：将被检查是否为NULL值的表达式。check_expression可以是任何类型的。
nullreplacement_value：当check_expression为NULL值时将返回该表达式。replacement_value必
须与check_expresssion具有相同的数据类型。
【例9.17】试验SQLServer中的ISNULL函数。假设有一个数据表TestNull，如表9.14所示。
表9.14TestNull表内容
C1C2
10NULL
20200
NULLNULL
其创建语句和插入语句分别如下所示。
CREATETABLEtestnull
(
c1int,
c2int
);
INSERTINTOtestnull
VALUES(10,NULL);
INSERTINTOtestnull
VALUES(20,200);
INSERTINTOtestnull
VALUES(NULL,NULL);
第9章SQL函数的使用
·133·
下面的语句将C2字段的所有NULL值显示为0。
SELECTc1,ISNULL(c2,0)
FROMtestnull;
运行结果如图9.14所示。
图9.14查询TestNull表的结果
注意：上面的查询语句，并不能将C2字段的NULL值更改为0，而只是将NULL值显示为0。
说明：Access中相对应ISNULL函数的是NZ函数。
9.4.2Oracle的NVL函数
Oracle中对应SQLServer的ISNULL函数的是NVL函数。下面通过一个例题说明其用法。
【例9.18】试验Oracle中的NVL函数，将C2字段的所有NULL值显示为1000。
SELECTc1,nvl(c2,1000)
FROMtestnull;
运行结果如下所示。
C1NVL(C2,1000)
--------------------------------
101000
20200
301000
9.5IF…ELSE逻辑函数
IF…ELSE逻辑函数指的是根据判断条件返回不同结果的函数。Oracle中的DECODE和SQLServer
中的CASE就是这种函数。
9.5.1DECODE函数
Oracle中的DECODE是一个比较重要的函数。可以使用该函数翻译数据，也可以动态地使查询以
一种特殊的方式执行。下面是DECODE函数的基本语法。
DECODE(表达式，值1，返回值1，值2，返回值2，…，值n，返回值n，默认返回值)
SQL技术与网络数据库开发详解
·134·
具体说明如下：
null当“表达式=值1”，则DECODE函数的返回值为“返回值1”；当“表达式=值2”，则DECODE
函数的返回值为“返回值2”，依此类推。
null如果表达式不与任何值相等，则DECODE函数的返回值为“默认返回值”。
DECODE函数最明显的用途是将查询到的值翻译成一种更具描述性的值。
【例9.19】查询foreign_teacher表中美国籍外教的姓名和性别，并使用DECODE函数，将性别
（sex）字段的值“m”显示为“男”，将“f”显示为“女”。
SELECTtname外教姓名,
DECODE(sex,''m'',''男'',''f'',''女'',''错误数据'')性别
FROMforeign_teacher
WHEREcountry=''USA''
ORDERBYsex;
运行结果如下所示。
外教姓名性别
--------------------------
TomGreen男
Wulanqiqige女
说明：目前Oracle的新版本中也加入了CASE函数，其功能与第10章SQLServer的CASE函数的用
法相同。
9.5.2CASE函数
SQLServer中对应DECODE函数的是CASE函数，其语法如下所示。
CASE
WHEN条件表达式1THEN返回值1
WHEN条件表达式2THEN返回值2
……
WHEN条件表达式nTHEN返回值n
ELSE返回值n+1
END
具体说明如下：
null当“条件表达式1”成立时，CASE函数的返回值为“返回值1”；当“条件表达式2”成立时，
CASE函数的返回值为“返回值2”，依此类推。
null如果条件表达式1～n都不成立，则CASE函数的返回值为“返回值n+1”。
【例9.20】查询foreign_teacher表中美国籍外教的姓名和性别，并使用CASE函数，将性别（sex）
字段的值“m”显示为“男”，将“f”显示为“女”。
SELECTtname外教姓名,
性别=
CASE
第9章SQL函数的使用
·135·
WHENsex=''m''THEN''男''
WHENsex=''f''THEN''女''
ELSE''错误数据''
END
FROMforeign_teacher
WHEREcountry=''USA''
ORDERBYsex;
运行结果如图9.15所示。
图9.15例9.20运行结果
CASE函数实际上还有一种形式，例如，上面的SELECT语句还可以写为如下形式。
SELECTtname外教姓名,
性别=
CASEsex
WHEN''m''THEN''男''
WHEN''f''THEN''女''
ELSE''错误数据''
END
FROMforeign_teacher
WHEREcountry=''USA''
ORDERBYsex;
|
|
