SQL函数（行函数）的使用

SQL函数（行函数）的使用

不同DBMS的函数差异

功能	SQL Server函数	Oracle函数或语句	MySQL函数
获取字符串的某部分	SUBSTRING()	SUBSTR()	SUBSTRING()
获取当前日期	GETDATE()	SYSDATE	CURDATE()
转换数据类型	CONVERT()	有多个具体函数，如TO_DATE()将字符串转换为日期，TO_CHAR()将数字或日期转换为字符串。	CONVERT()

一、SQL Server的函数

1.1、类型转换函数

转换函数用于将具体DBMS的数值转换成其他数据类型或对其进行格式化，经常用到的转换是将日期和数字转换成指定的字符串格式，或者将字符串转换成有效的日期或数值。

在SQL Server中，使用CONVERT()和CAST()两个函数转换数据类型。

1.1.1、CONVERT()函数

CONVERT(datatype[(length)],expression,[style])

其中，datatype为数据类型，如果是CHAR、VARCHAR、BINARY或VARBINARY数据类型，则可以选择length参数设置长度；expression为表达式，如果要将日期型数据转换为字符型数据，则还可以使用style参数设置日期显示格式。sytle参数可以取两类值，如果从第一类取值，则返回日期的年份为2位；如果从第二类取值，则返回日期的年份为4位。

当把一个日期转换为字符串时，CONVERT()函数默认的输出格式是“mon dd yy hh:mi AM(或PM)”，即省略style参数。

CONVERT()函数的例子

功能	函数实现
字符到数字	CONVERT(numeric,’15’)
数字到字符	CONVERT(char,12)
字符到日期	CONVERT(datetime,’15-09-1977’)，CONVERT(datetime,’SEP 15,1977’)
日期到字符	CONVERT(char,GETDATE())，CONVERT(char,GETDATE(),102)    --102是ANSI标准
十六进制到二进制	CONVERT(binary,’3C’))
二进制到十六进制	CONVERT(char,二进制字段)
获取当前系统时间	CONVERT(char,GETDATE(),8)

• 示例（SQL Server）：从stu_info表中查询所有学生的姓名、出生日期，并将日期转换为德国标准日期格式的字符串显示，其中返回日期的年份为4位，要求查询结果按出生日期升序排序。

分析：将出生日期转换为字符串，应当使用CONVERT()函数；因为需要按照德国日期格式显示，查找德国标准的style值；又因为年份要求是4位，所以选择style参数的值为104

SELECT name AS 姓名,CONVERT(CHAR,birthday,104) AS 生日 FROM student ORDER BY birthday;

1.1.2、CAST()函数

CAST()函数是SQL92标准函数，使用CAST()函数也可以转换数据类型，但是在格式化日期时间数据方面不如CONVERT()函数方便。

CAST(expression AS datatype[(length)])

其中，expression为表达式；datatype为数据类型，如果是CHAR、VARCHAR、BINARY或VARBINARY数据类型，则可以选择length参数设置长度。

• 示例：从student表中查询所有学生的姓名、出生日期，并将日期转换为字符串显示，要求查询结果按出生日期升序排序。

SELECT name AS 姓名,CAST(birthday as char) AS 生日 FROM student ORDER BY birthday;

可以看出，出生日期已经被转换为字符串，如果只想要日期部分，不想要时间，则可以在类型后设置长度。

SELECT name AS 姓名,CAST(birthday as char(11)) AS 生日 FROM student ORDER BY birthday;

本例中，只给“出生日期”分配了11个字节的长度，所以只把前面的日期部分留下，后面的时间部分被自动截掉了。

CAST()函数不能改变原表字段的数据类型。

1.1.3、日期函数

1. GETDATE()函数

GETDATE()函数用于获取当前系统时间：GETDATE()

例如，在查询分析器中输入如下SELECT语句，运行后即可获得当前系统时间

SELECT GETDATE()

1. DATEADD()函数

DATEADD()函数用于在指定日期上增加年、月、日或者时间等，其返回值为日期型数据。

DATEADD(datepart,number,date)

--在当前时间的“年”上增加了10年，并返回10年后的日期 SELECT DATEADD(year,10,GETDATE())

--在当前时间的“月”上增加了10个月，并返回10个月后的日期 SELECT DATEADD(MONTH,10,GETDATE())

注：datepart参数值可以使用缩写，例如，DATEADD(mm,10,GETDATE())也是在当前时间上增加10个月。

• 示例：从student表中查询所有学生的姓名、出生日期、出生后的第10000天和出生后的第800个月，要求查询结果按出生日期升序排序。

SELECT name AS 姓名,        birthday AS 出生日期,        DATEADD(DAY,10000,birthday) AS 出生后第10000天,        DATEADD(MONTH,800,birthday) AS 出生后第800月 FROM student ORDER BY birthday;

1. DATEDIFF()函数

DATEDIFF()函数用于获取两个日期间的差，并返回数值数据。

DATEDIFF(datepart,date1,date2)

datepart参数规定在日期的哪个部分（如年份、月份等）增加（减小）数值；date1和date2是日期或者日期格式的字符串。

• 示例：从student表中查询所有学生的姓名、出生日期和年龄，要求查询结果按出生日期降序排序。

SELECT name AS 姓名,        birthday AS 出生日期,        DATEDIFF(YEAR,birthday,GETDATE()) AS 年龄 FROM student ORDER BY birthday DESC;

返回的是当前日间和出生日期之间的年份差，即年龄。

1. DATENAME()函数

DATENAME()函数用于获取日期的一部分，并以字符串形式返回。

DATENAME(datepart,date)

datepart参数规定在日期的哪个部分（年、月）增加（减小）数值；date是日期或者日期格式的字符串；例如，假设当前日期为1月29日，则DATENAME(month,GETDATE())的结果为字符串‘01’，DATENAME(dd,GETDATE())的结果为字符串‘29’。

• 示例：从student表中查询每个月25日出生的所有学生，要求查询结果按出生日期降序排序。

SELECT * FROM student WHERE DATENAME(DAY,birthday)='25' ORDER BY birthday DESC;

1. DATEPART()函数

DATEPART()函数用于获取日期的一部分，并以整数值返回。

DATEPART(datepart,date)

DATEPART()函数返回的是数值，因为必须与条件表达式中的数值进行比较（例如date为25），数值不用引号。

SELECT * FROM student WHERE DATEPART(DAY,birthday)=25 ORDER BY birthday DESC;

在SQL Server中，还有YEAR()、MONTH()、DAY()3个函数，分别用于获取日期数据的年份、月份和日期部分，这3个函数的返回值都是数值型。

1.1.4、数学函数

数学函数允许操作数值数据。

• 示例：使用数学函数计算30度角的正弦值。

分析：首先应当使用RADIANS()函数计算30度角的弧度值，其次对弧度值使用SIN()函数求正弦值，最后应当对结果进行四舍五入的计算。

ROUND	(numeric_表达式，length)	返回数字表达式并四舍五入为指定的的长度或精度
SIN	(float_表达式)	返回给定float表达式的给给定角度（以弧度为单位）的三角正弦值（近似值）
RADIANS	(numeric_表达式)	对于在数字表达式中输入的度数值返回弧度值

SELECT ROUND(SIN(RADIANS(30.0)),1) AS "30度的正弦值";

1.1.5、字符函数

字符函数允许操作字符数据。

函数	参数	说明
ASCII	(char_表达式)	返回字符表达式结果最左边字符的ASCII码
RTRIM	(char_表达式)	删除字符串右边所有的空格
SOUNDEX	(char_表达式)	返回由4个字符组成的代码（SOUNDEX）以评估两个字符串的相似性
UPPER	(char_表达式)	将字符表达式中的所有小写字母全部转换成大写字母

• 示例：从foreign_teacher表中查询“Tom Green”老师的联系电话（tel）和电子邮件（email）。

分析：有时人们经常会忽视英文字母的大小写，如将“Tom Green”写为“tom green”；此时，如果数据库管理系统没有自动转换匹配的功能，则会将这两个字符看作是不同人的姓名，从而导致查询出错。为了解决这类问题，应当将数据库中的字符串的所有字母转换为大写（或小写）字母，然后与大写（或小写）字母的字符串进行比较。

SELECT tname,tel,email FROM foreign_teacher WHERE UPPER(tname)='TOM GREEN';

• 示例：从foreign_teacher表中查询所有教师的姓名（tname）、国家（country）、雇用日期（hiredate）和联系电话（tel），并将姓名和国家合并为一列显示。

SELECT tname+'('+country+')' AS "姓名（国家）",         hiredate AS 雇用日期,         tel 联系电话 FROM foreign_teacher;

将AS后的别名“姓名（国家）”放入双引号中的原因是别名中含有圆括号。

使用RTRIM()函数将姓名的尾随空格去掉，然后再拼接。

SELECT RTRIM(tname)+'('+country+')' AS "姓名（国家）",         hiredate AS 雇用日期,         tel 联系电话 FROM foreign_teacher;

• 示例：在查询数据时，记错了名称，从而查询出错；如果要要处理这类问题，就要用到SOUNDEX()函数，因为该函数能对字符串进行发音比较而不是字母比较。

从foreign_teacher表中查询所有加拿大籍教师的姓名（tname）、出生日期（birth）和电子邮件（email）。

SELECT tname,birth,email FROM foreign_teacher WHERE SOUNDEX(country)=SOUNDEX('canda');

可见，即使拼错了加拿大的英文名称，使用SOUNDEX()函数还是能够查到正确的结果；SOUNDEX()函数并不支持汉字的读音比较。

二、MySQL的函数

2.1、类型转换函数

2.1.1、CONVERT()函数

CONVERT(value,type)

其中type为数据类型，转换的数据类型有限，可以是以下值中的一个：

1. 二进制：BINARY[(N)]
2. 字符型：CHAR[(N)]
3. 日期：DATE
4. 时间：TIME
5. 日期时间型：DATETIME
6. 浮点数：DECIMAL
7. 整数：SIGNED[INTEGER]
8. 无符号整数：UNSIGNED[INTEGER]

• 示例：使用CONVERT()函数将字符串转换成整数

SELECT CONVERT('12345',SIGNED);

2.1.2、CAST函数

CAST(value AS type)

其中，type为数据类型

• 示例：使用CAST()函数将字符串转换成整数

SELECT CAST('12345' AS signed);

2.1.3、日期函数

1. 获取当前日期、时间的函数

使用CURDATE()函数、CURTIME()函数可以获取当前日期、当前时间

SELECT CURDATE() AS 当前日期,CURTIME() AS 当前时间;

• CURRENT_DATE()：根据返回值所处上下文是字符串或数字，返回以‘YYYY-MM-DD’或YYYYMMDD格式表示的当前日期值。
• CURRENT_TIME()：根据返回值所处上下文是字符串或数字，返回以‘HH:MM:SS’或HHMMSS格式表示的当前时间值。
• NOW()\SYSDATE()\CURRENT_TIMESTAMP()\LOCALTIME()：根据返回值所处上下文是字符串或数字，返回以‘YYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS格式表示的当前日期时间。

1. 时间戳函数

• UNIS_TIMESTAMP()：返回一个UNIX时间戳（从‘1970-01-01 00:00:00’GMT开始的秒数，date默认值为当前时间）。
• FROM_UNIXTIME()：将时间戳转换为以‘YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS格式表示的值。

1. 取日期中部分值的函数

• YEAR(date)：返回date的年份（范围为1000~9999）。
• MONTH(date)：返回date的月份数。
• DAY(date)：返回date的天数。
• HOUR(date)：返回date的小时数（范围是0~23）。
• MINUTE(date)：返回date的分钟数（范围是0~59）。
• SECOND(date)：返回date的秒数（范围是0~59）。
• TO_DAYS(date)：返回从西元0年至date的天数（不计算1582年以前）。
• FROM_DAYS(N)：返回距西元0年N天的日期值（不计算1582年以前）。
• DAYOFWEEK(date)：返回date是星期几（1=星期天，2=星期一，……，7=星期六）。
• WEEKDAY(date)：返回date是星期几（0=星期一，1=星期二，……，6=星期天）。
• DAYOFMONTH(date)：返回date是一月中的第几日（在1~31范围内）。
• DAYOFYEAR(date)：返回date是一年中的第几日（在1~366范围内）。
• DAYNAME(date)：返回date是星期几（按英文名返回）。
• MONTHNAME(date)：返回date是几月（按英文名返回）。
• QUARTER(date)：返回date是一年中的第几个季度。
• WEEK(date,first)：返回date是一年中的第几周（first的默认值为0，first取值1表示周一是一周的开始，取值0表示从周日开始）。

1. 日期运算函数

日期运算函数用于对日期时间进行加减运算。

• DATE_ADD(date,INTERVAL expr type)
• DATE_SUB(date,INTERVAL expr type)
• ADDDATE(date,INTERVAL expr type)
• SUBDATE(date,INTERVAL expr type)

其中ADDDATE()和SUBDATE()是DATE_ADD和DATE_SUB()的同义词，也可以用运算符（+）和（-）进行运算。

在上面4个函数中，date是一个DATETIME或DATE值，expr是对date进行加减运算的一个表达式字符串，type指明表达式expr应该如何被解释。type可取以下值：

• SECOND：表示按秒运算，expr为整数值。
• MINUTE：表示按分钟运算，expr为整数值。
• HOUR：表示按小时运算，expr为整数值。
• DAY：表示按天运算，expr为整数值。
• MONTH：表示按月运算，expr为整数值。
• YEAR：表示按年运算，expr为整数值。
• MINUTE_SECOND：表示按分钟和秒进行运算，expr为字符串，如“MINUTES:SECONDS”。
• HOUR_MINUTE：表示按小时和分钟进行运算，expr为字符串，如“HOURS:MINUTES”。
• DAY_HOUR：表示按天和小时进行运算，expr为字符串，如“DAYS HOURS”。
• YEAR_MONTH：表示按年和月进行运算，expr为字符串，如“YEARS-MONTHS”。
• HOUR_SECOND：表示按小时和分钟进行运算，expr为字符串，如“HOURS:MINUTES:SECONDS”。
• DAY_MINUTE：表示按天、小时、分钟进行运算，expr为字符串，如“DAYS HOURS:MINUTES”。
• DAY_SECOND：表示按天、小时、分钟、秒进行运算，expr为字符串，如“DAYS HOURS:MINUTES:SECONDS”。

• 示例：使用时间函数计算后天的这个时间再往后推2小时的值。

后天（即当前日期增加2天）再往后推2小时，其运算字符串“2 2”，使用DAY_HOUR类型进行运算。

SELECT NOW() AS 当前日期,DATE_ADD(NOW(),INTERVAL "2 2" DAY_HOUR) AS 运算结果;

在日期运算函数中，expr中允许使用任何标点做分隔符，如果所有date都是DATE值，结果是另一个DATE值，否则结果是一个DATETIME值。

另外，如果type关键词不完整，则MySQL从右端取值；例如，DAY_SECOND因为缺少小时分钟，等于MINUTE_SECOND；如果增加MONTH、YEAR_MONTH或YEAR，天数大于结果月份的最大天数，则使用最大天数。

三、将NULL更改为其他值的函数

3.1、SQL Server的ISNULL()函数

SQL Server中的ISNULL()函数可以将NULL值更改为其他值

ISNULL(check_expression,replacement_value)

• check_expression：将检查是否为NULL值的表达式，可以是任何类型的。
• replacement_value：当check_expression为NULL值时将返回该表达式，replacement_value必须与check_expression具有相同的数据类型。

• 示例：假设有一个数据表testnull

c1	c2
10	NULL
20	200
NULL	NULL

其创建语句和插入语句分别如下：

CREATE TABLE testnull (     c1 int,     c2 int ); INSERT INTO testnull VALUES(10,NULL),(20,200),(NULL,NULL);

下面的语句将c2字段所有的NULL值显示为0

SELECT c1,ISNULL(c2,0) as c2 FROM testnull;

上面的查询语句并不能将c2字段的NULL值更改为0，而只是将NULL值显示为0。

3.2、MySQL的IFNULL()函数

MySQL中对应SQL Server的ISNULL()函数是IFNULL()函数。

• 将MySQL中的IFNULL()函数，c2字段的所有NULL值显示为0。

SELECT c1,IFNULL(c2,0) FROM testnull;

四、IF…ELSE逻辑函数

IF…ELSE逻辑函数指的是根据判断条件返回不同结果的函数Oracle中的DECODE()函数和SQL Server中的CASE()函数就是这种函数。

4.1、CASE()函数

CASE    WHEN 条件表达式1 THEN 返回值 1    WHEN 条件表达式2 THEN 返回值 2    ……    WHEN 条件表达式n THEN 返回值 n    ELSE   返回值 n+1 END

• 当“条件表达式1”成立时，CASE()函数的返回值为“返回值1”；而当“条件表达式2”成立时，CASE()函数的返回值为“返回值2”，以此类推。
• 如果条件表达式1~n都不成立，则CASE()函数的返回值为“返回值n+1”。

• 示例：查询foreign_teacher表中美国藉外教的姓名和性别，并使用CASE()函数将性别（sex）字段的值“m”显示为“男”，将“f”显示为“女”。

SELECT tname 外教姓名,         性别 =         CASE         WHEN sex = 'm' THEN '男'         WHEN sex = 'f' THEN '女'         ELSE '错误数据'         END FROM foreign_teacher WHERE country='USA' ORDER BY sex;

CASE()函数实际上还有一种形式：

SELECT tname 外教姓名,         性别 =         CASE sex         WHEN  'm' THEN '男'         WHEN  'f' THEN '女'         ELSE '错误数据'         END FROM foreign_teacher WHERE country='USA' ORDER BY sex;

• CASE：这是表达式的开头。
• WHEN sex = 'm' THEN '男'：这是一个条件。如果“”列中的值为“m”，则结果为“男”（中文意为“男”）。
• WHEN sex = 'f' THEN '女'：这是另一个条件。如果“”列中的值为“f”，则结果为“女”（在中文中意为“女性”）。
• ELSE '错误数据'：这是默认条件。如果“”列中的值与上述任何条件（“m”或“f”）不匹配，则结果为“错误数据”（中文意为“错误数据”）。
• END：这标志着表达式的结束。