SQL笔记-第五章，函数

一.数学函数

二.字符串函数

 时间日期函数

三.类型转换

mysql

CAST(expression AS type)
CONVERT(expression,type)

 oracle

TO_CHAR(expression,format); 将时间日期类型或者数值类型的数据转换为字符串

TO_DATE (expression,format); 将字符串转换为时间类型

TO_NUMBER (expression,format); 将字符串转换为数值类型

HEXTORAW() 将十六进制格式的数据转换为原始值

RAWTOHEX() 将原始值转换为十六进制格式的数据。

TO_MULTI_BYTE() 将字符串中的半角字符转换为全角字符

TO_SINGLE_BYTE() 将字符串中的全角字符转换为半角字符

四.空值处理

COALESCE ( expression,value1,value2……,valuen)

COALESCE()函数将会返回包括expression在内的所有参数中的第一个非空表达式。如果expression不为空值则返回 expression；否则判断value1是否是空值，如果value1不为空值则返回value1；否则判断value2是否是空值，如果 value2不为空值则返回value3；……以此类推，如果所有的表达式都为空值，则返回NULL。

COALESCE()函数的简化版

MYSQL: IFNULL(expression,value)
Oracle: NVL(expression,value)

NULLIF ( expression1 , expression2 )

如果两个表达式不等价，则NULLIF 返回第一个expression1的值。如果两个表达式等价，则NULLIF 返回第一个expression1类型的空值。也就是返回类型与第一个expression相同。

CASE函数

SELECT
FName,
(CASE FName
WHEN ‘Tom’ THEN ‘GoodBoy’
WHEN ‘Lily’ THEN ‘GoodGirl’
WHEN ‘Sam’ THEN ‘BadBoy’
WHEN ‘Kerry’ THEN ‘BadGirl’
ELSE ‘Normal’
END) as isgood
FROM T_Person

SELECT
FName,
FWeight,
(CASE
WHEN FWeight<40 THEN ‘thin’
WHEN FWeight>50 THEN ‘fat’
ELSE ‘ok’
END) as isnormal
FROM T_Person

五.各数据库系统独有函数

1.MYSQL中的独有函数

IF()函数

SELECT FName, FWeight,
IF(FWeight>50,’太胖’,‘正常’) AS ISTooFat
FROM T_Person

CONV()函数用于对数字进行进制转换，比如将十进制的26转换为2进制显示，其参数格式如下：
CONV(N,from_base,to_base)

将每个人的体重四舍五入为整数，然后以二进制的形式显示它们：
SELECT FWeight,Round(FWeight),
CONV(Round(FWeight),10,2)
FROM T_Person

填充函数

LPAD(str,len,padstr)
RPAD(str,len,padstr)

SELECT FName,LPAD(FName,5,’*‘),RPAD(FName,5,’*‘) FROM T_Person

REPEAT()用来得到一个子字符串重复了若干次所组成的字符串

REPEAT(str,count)

字符串颠倒

REVERSE() 函数用来将一个字符串的顺序颠倒

字符串的集合操作

SELECT FName,
ELT(FLevel, ‘VIP客户’, ‘高级客户’, ‘普通客户’)
FROM T_Customer

SELECT FName,
FIELD(FCustomerTypeName, ‘VIP’, ‘会员’, ‘普通客户’)
FROM T_Customer

计算集合中的最大最小值

SELECT GREATEST(2,7,1,8,30,4,3,99,2,222,12),
LEAST(2,7,1,8,30,4,3,99,2,222,12)

辅助功能函数

DATABASE()函数返回当前数据库名；

VERSION()函数以一个字符串形式返回MySQL 服务器的版本；

USER()函数（这个函数还有SYSTEM_USER、SESSION_USER两个别名）返回当前MySQL 用户名。

ENCODE(str,pass_str)函数使用pass_str 做为密钥加密str，函数的返回结果是一个与string 一样长的二进制字符。如果希望将它保存到一个列中，需要使用BLOB列类型。
DECODE()函数使用pass_str 作为密钥解密经ENCODE加密后的字符串crypt_str。

MD5(string)、SHA1(string)两个函数就是分别用来使用MD5算法和SHA1算法来进行字符串的摘
要计算的函数

2.Oracle中的独有函数

填充函数

SELECT FName,
LPAD(FName,5,’*‘),
RPAD(FName,5,’#‘)
FROM T_Person

返回当月最后一天

SELECT FName,FBirthDay,
LAST_DAY(FBirthDay)
FROM T_Person
WHERE FBirthDay IS NOT NULL

计算最大最小值

SELECT GREATEST(2,7,1,8,30,4,5566,99,2,222,12),
LEAST(2,7,1,8,30,4,3,99,-2,222,12)
FROM DUAL

辅助功能函数

取得当前登录用户名，注意，不能用括号

SELECT USER
FROM DUAL

USERENV()函数用来取得当前登录用户相关的环境信息

SELECT USERENV(‘ISDBA’) AS ISDBA,
USERENV(‘LANGUAGE’) AS LANGUAGE,
USERENV(‘LANG’) AS LANG
FROM DUAL