（转）sql函数总结（更新中... ...）
原文：https://www.imooc.com/article/305617
SQL中包含以下七种类型的函数：
聚合函数:返回汇总值。
转型函数:将一种数据类型转换为另外一种。
日期函数:处理日期和时间。
数学函数:执行算术运算。
字符串函数:对字符串、二进制数据或表达式执行操作。
系统函数:从数据库返回在SQLSERVER中的值、对象或设置的特殊信息。
文本和图像函数:对文本和图像数据执行操作。
（注：下面所有的例子均为mysql写法）
1、聚合函数
1 AVG(表达式) 返回表达式中所有的平均值。仅用于数字列并自动忽略NULL值。
--例：计算user表中age字段的平均值
|
1
|
select AVG(age) age from user |
2 COUNT(表达式) 返回表达式中非NULL值的数量。可用于数字和字符列。
--例：查询user表中age为非空值的数量
|
1
|
select COUNT(age) num from user |
3 COUNT(*) 返回表中的行数(包括有NULL值的列)。
--例：查询user表中数据的数量
|
1
|
select COUNT(*) num from user |
-- 注：一般查询表中数据的数量时用select count(1) from user 查询速度比较快
4 MAX(表达式) 返回表达式中的最大值,忽略NULL值。可用于数字、字符和日期时间列。
-- 例：查询user表中age的最大值
|
1
|
select MAX(age) maxage from user |
5 MIN(表达式) 返回表达式中的最小值,忽略NULL值。可用于数字、字符和日期时间列。
--例：查询user表中age的最小值
|
1
|
select MIN(age) minage from user |
6 SUM(表达式) 返回表达式中所有的总和,忽略NULL值。仅用于数字列。
--例：查询user表中所有数据的age的总和
|
1
|
select SUM(age) sumage from user |
2、转换函数
1 CAST(x AS type)
2 CONVERT(x,type)
这两个函数只对BINARY、CHAR、DATE、DATETIME、TIME、SIGNED INTEGER、UNSIGNED INTEGER。
CONVERT(data_type[(length)], expression [, style])
例:
Select convert(varchar(10) ,stuno) as stuno,stuname from student
CAST( expression AS data_type )
例:
Select cast(stuno as varchar(10)) as stuno,stuname from student
|
1
2
3
4
5
6
7
8
|
SELECT CAST('123' AS UNSIGNED INTEGER) + 1 ->124 SELECT '123' + 1 ->124 -- 其实MySQL能默认转换 SELECT CAST(NOW() AS DATE) ->2014-12-18 |
数据库中user表中 magicstr字段是16进制字符 转换成10进制 再被sha1编码 update sigma_account_us_user set `password`= SHA1(CONVERT( magicstr, SIGNED )) where id=467450
对于字段排序：
|
1
|
select * from user order by cast(age as UNSIGNED) asc; |
Cast和Convert的区别：
Cast 和Convert都是用来将一种数据类型的表达式转换为另一种数据类型的表达式。CAST 和 CONVERT 提供相似的功能，只是语法不同。在时间转化中一般用到convert,因为它比cast多加了一个style,可以转化成不同时间的格式。
3、日期函数
1 获取当前日期和时间：NOW()
|
1
2
|
select NOW()-- 结果为：2020-06-09 09:55:06 |
2 获取当前日期：CURDATE()
|
1
2
|
select CURDATE()-- 结果为：2020-06-09 |
3 获取当前时间：CURTIME()
|
1
2
|
select CURTIME()-- 结果为：09:56:42 |
4 获取时间或日期的日期部分：DATE()
（date 参数是合法的日期表达式）
|
1
2
3
4
|
select DATE('2020-06-09 12:00:00') -- 获取固定时间的日期部分-- 结果为：2020-06-09select DATE(now()) -- 获取当前时间的日期部分-- 结果为：2020-06-09 |
5 获取返回日期/时间的单独部分，比如年、月、日、小时、分钟等等：EXTRACT
语法：EXTRACT(unit FROM date)
date 参数是合法的日期表达式。
unit 参数可以是下列的值：MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR、SECOND_MICROSECOND、MINUTE_MICROSECOND、MINUTE_SECOND、HOUR_MICROSECOND、HOUR_SECOND、HOUR_MINUTE、DAY_MICROSECOND、DAY_SECOND、DAY_MINUTE、DAY_HOUR、YEAR_MONTH
|
1
2
3
4
|
select EXTRACT(YEAR_MONTH FROM now()) -- 获取当前年月-- 输出结果为：202006SELECT EXTRACT(DAY FROM time) AS DATE FROM test -- 查询test表的time字段中的日部分 |
6 将日期添加指定的时间间隔：DATE_ADD
语法：DATE_ADD(date,INTERVAL expr type)
date 参数是合法的日期表达式。expr 参数是添加的时间间隔。
type 参数可以是下列值：MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR、SECOND_MICROSECOND、MINUTE_MICROSECOND、MINUTE_SECOND、HOUR_MICROSECOND、HOUR_SECOND、HOUR_MINUTE、DAY_MICROSECOND、DAY_SECOND、DAY_MINUTE、DAY_HOUR、YEAR_MONTH
|
1
2
|
select DATE_ADD(now(),INTERVAL 2 DAY) -- 获取两天后的时间-- 输出结果为：2020-06-11 10:49:18 |
7 将日期减去指定的时间间隔：DATE_SUB
语法：DATE_SUB(date,INTERVAL expr type)
date 参数是合法的日期表达式。expr 参数是添加的时间间隔。
type 参数可以是下列值：MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR、SECOND_MICROSECOND、MINUTE_MICROSECOND、MINUTE_SECOND、HOUR_MICROSECOND、HOUR_SECOND、HOUR_MINUTE、DAY_MICROSECOND、DAY_SECOND、DAY_MINUTE、DAY_HOUR、YEAR_MONTH
|
1
2
|
select DATE_SUB(now(),INTERVAL 2 DAY); -- 获取两天前的时间-- 输出结果为：2020-06-07 10:50:51 |
8 获取两个日期之间的天数。（只有日期部分参与计算）：DATEDIFF
语法：DATEDIFF(date1,date2)
date1 和 date2 参数是合法的日期或日期/时间表达式。
|
1
2
|
SELECT DATEDIFF(NOW(),'2019-06-09') -- 2019年6月9号在当前时间的多少天之前-- 输出结果：366 |
9 格式化日期时间：DATEFORMAT
语法：DATE_FORMAT(date,format)
date 参数是合法的日期。format 规定日期/时间的输出格式。
格式：描述
%a：缩写星期名
%b：缩写月名
%c：月，数值
%D：带有英文前缀的月中的天
%d：月的天，数值(00-31)
%e：月的天，数值(0-31)
%f：微秒
%H：小时 (00-23)
%h：小时 (01-12)
%I：小时 (01-12)
%i：分钟，数值(00-59)
%j：年的天 (001-366)
%k：小时 (0-23)
%l：小时 (1-12)
%M：月名
%m：月，数值(00-12)
%p：AM 或 PM
%r：时间，12-小时（hh:mm:ss AM 或 PM）
%S：秒(00-59)
%s：秒(00-59)
%T：时间, 24-小时 (hh:mm:ss)
%U：周 (00-53) 星期日是一周的第一天
%u：周 (00-53) 星期一是一周的第一天
%V：周 (01-53) 星期日是一周的第一天，与 %X 使用
%v：周 (01-53) 星期一是一周的第一天，与 %x 使用
%W：星期名
%w：周的天 （0=星期日, 6=星期六）
%X：年，其中的星期日是周的第一天，4 位，与 %V 使用
%x：年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y：年，4 位
%y：年，2 位
|
1
2
|
SELECT DATE_FORMAT(now(),'%W') -- 输出当前时间是周几-- 输出结果为：Tuesday |
10 获取时间的年、月、日：YEAR、MONTH、DAY
|
1
2
3
4
5
6
|
select YEAR(NOW()) -- 获取当前时间的年份-- 输出结果为：2020select MONTH(NOW()) -- 获取当前时间的月份-- 输出结果为：6select DAY(NOW()) -- 获取当前时间的日-- 输出结果为：9 |
注：当显示日期列的内容时如果只显示年月日部分,可以使用CONVERT转换函数对日期列进行转换
CONVERT(VARCHAR(10),日期字段名,120) --120 为日期格式YYYY-MM-DD
SELECT CONVERT(VARCHAR(10),盘点日期,120) AS 盘点日期 FROM 原材料盘点日期明细表
4、数字函数
ABS(num_expr) 返回数值表达式的绝对值。
|
1
2
3
4
|
select ABS(-123)-- 输出结果为：123select ABS(123)-- 输出结果为：123 |
ACOS(float_expr) 返回float_expr的反余弦，float_expr的范围必须在-1和1之间，否则返回 NULL。
|
1
2
|
select ACOS(0.06)-- 输出结果为：1.5107602683496182 |
ASIN(float_expr) 返回float_expr的反正弦，float_expr的范围必须在-1和1之间，否则返回 NULL。
|
1
2
|
select ASIN(0.06)-- 输出结果为：0.06003605844527842 |
ATAN(float_expr) 返回 float_expr 的反正切值
|
1
2
|
select ATAN(5)-- 输出结果为：1.373400766945016 |
ATAN2(float_expr1, float_expr2) 返回两个参数：float_expr1和float_expr2的反正切。它类似于Y/X的反正切，所不同的是两者的符号用于查找结果的象限。
|
1
2
|
select ATAN2(3,6)-- 输出结果为：0.4636476090008061 |
COS(float_expr) 返回 float_expr的余弦值，float_expr为弧度值。
|
1
2
|
select COS(0.5)-- 输出结果为：0.8775825618903728 |
COT(float_expr) 返回 float_expr的余切值，float_expr是给定的弧度值。
|
1
2
|
select COT(0.5)-- 输出结果为：1.830487721712452 |
SIN(float_expr) 返回 x 的正弦值，其中 x 为弧度值。
SELECT SIN(1),SIN(0.5*PI()) -- 输出结果：0.8414709848078965 1
DEGREES(num_expr)返回弧度值对应的度值。
|
1
2
|
select DEGREES(0.5)--输出结果为：28.64788975654116 |
CEILING(num_expr) 返回大于或等于数值表达式的最小整数。
|
1
2
|
select CEILING(30.4636)-- 输出结果为：31 |
EXP(float_expr) 返回e(自然对数的底)到X次方的值此函数返回e(自然对数的底)的X次方的值。
|
1
2
|
SELECT EXP(3)-- 输出结果为为e的3次方：20.085536923187668 |
FLOOR(num_expr) 返回小于或等于数值表达式的最大整数
|
1
2
|
select FLOOR(3.1415926)-- 输出结果为：3 |
LOG(float_expr) 返回float_expr的自然对数值。
|
1
2
|
select LOG(3)输出结果为：1.0986122886681098 |
LOG(float_expr1,float_expr2)返回float_expr2的对数的任意基数float_expr1
|
1
2
|
SELECT LOG(2,65536)-- 输出结果为：16 |
LOG10(float_expr) 返回以10为底的对数
|
1
2
|
SELECT LOG10(2)--输出结果：0.3010299956639812 |
PI() 返回圆周率PI：3.141593
|
1
2
|
SELECT PI()-- 输出结果为：3.141593 |
POWER(x,y) 和POW(x,y) 返回x的y次方。
|
1
2
3
4
|
SELECT POWER(2,3)-- 输出结果为：8SELECT POW(2,3)-- 输出结果为：8 |
RADIANS(num_expr) 返回num_expr的弧度值，从度转换为弧度。
|
1
2
|
SELECT RADIANS(90)-- 输出结果为：1.5707963267948966 |
RAND([seed]) 可以产生一个在 0 和 1 之间的随机数。
|
1
2
3
4
5
6
7
8
9
|
SELECT RAND(),RAND(),RAND()-- 输出结果为：0.27418810992891723 0.4770721629979761 0.5627965159367737--由运行结果可知，每次调用 RAND() 函数，都会随机生成一个 0~1 之间的随机数-- 当使用整数作为参数调用时，RAND() 使用该值作为随机数的种子发生器。每次种子使用给定值生成，RAND() 将产生一个可重复的系列数字SELECT RAND(1),RAND(),RAND(1)-- 输出结果为：0.40540353712197724 0.38276612142358546 0.40540353712197724由运行结果可知，当向 RAND() 函数中传入一个整数作为参数是，RAND() 函数产生的随机数可以重复。 |
ROUND(x,d) 对数值表达式截取指定的整数长度,返回四舍五入后的值，x指要处理的数，d是指保留几位小数。
round(x) ,其实就是round(x,0),也就是默认d为0
|
1
2
3
4
5
6
7
8
9
10
11
12
|
select round(1123.26723,2);-- 输出结果：1123.27select round(1123.26723,1);-- 输出结果: 1123.3select round(1123.26723,0);-- 输出结果：1123select round(1123.26723,-1);-- 输出结果: 1120select round(1123.26723,-2);-- 输出结果：1100select round(1123.26723);-- 输出结果：1123 |
SIGN(X) 返回X的符号(负数，零或正)对应-1，0或1。
SELECT SIGN(-4.65),SIGN(0),SIGN(4.65) -- 输出结果为：-1 0 1
SQRT(float_expr) 返回非负平方根。
|
1
2
|
SELECT SQRT(4)-- 输出结果为：2 |
5、字符串函数
|
1
|
可用于binary 和varbinary数据类型列,但主要用于char和varchar数据类型。 |
Expr1+expr2 返回两个表达式的组合形式的字符串。
ASCII(char_expr) 返回表达式最左边字符的ASCⅡ代码值。
CHAR(int_expr) 返回到之间的整数表达式的ASCⅡ字符值。如果输入的值不在有效范围内,则返回NULL。
CHARINDEX('pattern',char_expr) 返回字符表达式中指定模式的起始位置。
DIFFERENCE(char_expr1,char_expr2) 根据比较两个字符表达式的相似度,返回到之间的值。表示匹配度最佳。
LEN(char_expr) 返回字符表达式的长度。
LOWER(char_expr) 将字符表达式全部转换为小写。
LTRIM(char_expr) 返回删除掉前面空格的字符表达式。
PATINDEX('%pattern%',expr) 返回表达式中模式第一次出现的起始位置。返回表示不存在模式形式。
REPLICATE(char_expr,int_expr) 返回重复指定次数的字符表达式产生的字符串。
REVERSE(char_expr) 反转字符表达式。
RIGHT(char_expr,int_expr) 返回从字符表达式最右端起根据指定的字符个数得到的字符。
RTRIM(char_expr) 返回删除掉其后空格的字符表达式。
SOUNDEX(char_expr) 评估两个字符串的相似度后得到的位代码。
SPACE(int_expr) 返回包含指定空格数的字符串。
STR(float_expr[,length[,decimal]]) 返回浮点表达式的字符串表示法。
STUFF(char_expr1,start,length,char_expr2) 使用字符表达式替换字符表达式的一部分字符,从指定的位置开始替换指定的长度。
SUBSTRING(char_expr,start,length) 返回从字符表达式的指定位置开始,截取指定长度得到的字符集。
UPPER(char_expr) 将字符表达式全部转换为大写。
6、系统函数
|
1
|
用于返回元数据或配置设置。 |
COALESCE(expr1,expr2, xprN) 返回第一个非NULL表达式。
COL_LENGTH('table_name','column_name') 返回列的长度。
COL_NAME(table_id,column_id) 返回指定的表中的列名。
DATALENGTH('expr') 返回任何数据类型的实际长度。
DB_ID([‘database_name']) 返回数据库的标识号。
DB_NAME([database_id]) 返回数据库的名称。
GETANSINULL([‘database_name']) 返回数据库的默认空性(Nullability)。
HOST_ID() 返回工作站的标识号。
HOST_NAME() 返回工作站的名称。
IDENT_INCR('table_or_view') 有新的记录添加入到表中时计数加。
IDENT_SEED('table_or_view') 返回标识列的起始编号。
INDEX_COL('table_name',index_id,key_id) 返回索引的列名。
ISNULL(expr,value) 使用指定的值替换的NULL表达式。
NULLIF(expr1,expr2) Expr1与Expr2相等时,返回Null。
OBJECT_ID('obj_name') 返回数据库对象标识号。
OBJECT_NAME('object_id') 返回数据库对象名。
STATS_DATE(table_id,index_id) 返回上次更新指定索引的统计的日期。
SUSER_SID([‘login_name']) 返回用户的登录标识号。
SUSER_ID([‘login_name']) 返回用户的登录标识号。这个函数类似于SUSER_SID()函数,并且保留了向后的兼容性。
SUSER_SNAME([server_user_id]) 返回用户的登录标识号。
SUSER_NAME([server_user_id]) 返回用户的登录标识号。这个函数类似于SUSER_SNAME()函数,并且保留了向后的兼容性。
USER_ID('user_name') 返回用户的数据库标识号。
USER_NAME(['user_id']) 返回用户的数据库名称。
7、文本和图像函数
|
1
|
通常返回有关文本和图像数据所需的信息。文本和图像数据是以二进制格式的形式进行存储的。 |
TEXTPTR(col_name) 返回varbinary格式的文本指针值。对文本指针进行检查以确保它指向第一个文本页。
TEXTVALID('table_name.col_name',text_ptr)检查给定的文本指针是否有效。返回表示有效,返回表示指针无效。
作者：冒充大白
链接：https://www.imooc.com/article/305617
来源：慕课网
