函数(FUNCTION)
与数学中的函数概念类似,SQL语言中的函数也是指接收一个或多个参数,并按一定的关系返回预期的值。这里,根据一定的关系加工出来的预期的值,就是函数提供的功能。
MySQL数据库提供了众多功能强大、方便易用的系统函数。比如:数值函数、字符函数、日期和时间函数、条件判断函数、系统信息函数、数据类型转换函数、窗口函数、加密函数等。
通过这个链接,可以查看所有MySQL提供的系统函数:MySQL函数官方地址
数值函数
函数名称 | 作 用 | 示例 |
---|---|---|
ABS(X) | 求绝对值 | ABS(-3.14):3.14 |
PI() | 返回π的值 | PI():3.141593 |
SQRT(X) | 求算数平方根 | SQRT(9):3 |
MOD(N,M) | 求余数 | MOD(9,5):4 |
CEIL(X) 、 CEILING(X) | 两个函数功能相同,都是返回不小于参数的最小整数,即向上取整 | CEIL(6.2):7 CEIL(-6.7):-6 |
FLOOR(X) | 向下取整,返回值转化为一个BIGINT | FLOOR(3.6):3 FLOOR(-3.6):4 |
RAND() | 生成一个0~1之间的随机数,传入整数参数是,用来产生重复序列 | RAND():0.7176419149317238 |
RAND(X) | 使用X作为种子值,产生重复的随机序列 | RAND(2):0.6555866465490187 多次执行RAND(2),返回相同的值 |
ROUND(X)、 ROUND(X,D) | 对所传参数进行四舍五入 | ROUND(3.556):3.5 ROUND(3.556,2):3.56 保留2位小数 |
TRUNCATE(X,Y) | 返回舍弃小数点后y位的数字x | TRUNCATE(1.99,1):1.9 |
SIGN | 返回参数的符号,0是0,负数是-1,正数是1 | SIGN(0):0 SIGN(3):1 SIGN(-3):-1 |
POW(X,Y)、 POWER(X,Y) | 返回X的Y次方。两个函数的功能相同 | POW(2,3):8 POWER(2,3):8 |
EXP(X) | 返回e的X方 | exp(4):54.598150033144236 |
LOG(X) | 返回X的自然对数(相对于e的对数) | LOG(4):1.3862943611198906 |
LOG10(X) | 返回X的基数为10的对数 | LOG10(100):2 |
RADIANS(X) | 将X由角度转化为弧度 | RADIANS(180):3.141592653589793 |
DEGREES(X) | 将X由弧度转化为角度 | DEGREES(PI()):180 |
SIN(X) | 返回 x 的正弦值, x 为弧度值(不是角度值。30度=π/6) | sin(PI()/6):0.49999999999999994 |
ASIN(x) | 返回反正弦值,与函数 SIN 互为反函数。 若 x 不在 -1 到 1 的范围之内,则返回 NULL | asin(0.49999999999999994):0.5235987755982988=π/6 |
COS(x) | 返回 x 的余弦值,x 为弧度值 | cos(PI()/6):0.8660254037844387 |
ACOS(x) | 返回反余弦值,与函数 COS 互为反函数。 x 值的范围必须在 -1 和 1 之间,否则返回 NULL | acos(0.8660254037844387):0.5235987755982987 |
TAN(x) | 返回 x 的正切值,x 为给定的弧度值 | tan(PI()/6):0.5773502691896257 |
ATAN(x) | 返回 x 的反正切值,与函数 TAN 互为反函数 | atan(0.5773502691896257):0.5235987755982988 |
COT(x) | 返回 x 的余切值,x 是给定的弧度值 | cot(PI()/6):1.7320508075688774 |
字符函数
函数名称 | 作 用 | 示例 |
---|---|---|
ASCII(str) | 返回字符串str的最左面字符的ASCII代码值。如果str是空字符串,返回0。如果str是NULL,返回NULL | ASCII(‘2’):50 |
CHAR(X) | 将ASCII 码X转换为字符。如果没有输入0 ~ 255 之间的ASCII 码值,CHAR() 返回NULL。CHAR函数返回的数据类型为BLOB,需要使用CONVERT函数转换成字符。 | convert(char(50) using utf8mb4):’2′ convert(char(115,113,108,46,119,97,110,103) using utf8mb4):’sql.wang’ |
LENGTH(str) | 返回字符串str的字节长度。一个汉字占3个字节 | LENGTH(‘sql.wang’):8 LENGTH(‘SQL网’):6 |
CHAR_LENGTH(str) | 返回字符串str的字符个数。一个汉字算1个字符 | CHAR_LENGTH(‘SQL网’):4 |
CONCAT(str1,str2,…) | 合并字符串函数,返回结果为连接参数产生的字符串,参数可以使一个或多个 | CONCAT(‘sql.’,‘wang’):’sql.wang’ |
CONCAT_WS(x,str1,str2,…) | 使用x作为分隔符将str1、str2…拼接起来 | CONCAT_WS(‘-‘,’sql’,’.’,’wang’):’sql-.-wang’ |
LOCATE(substr,str) | 返回子串substr在字符串str第一次出现的位置,如果substr不在str里面,返回0。位置从1开始 | LOCATE(‘wang’,’sql.wang’):5 |
INSTR(str,substr) | 返回子串substr在字符串str第一次出现的位置,如果substr不在str里面,返回0。位置从1开始 | INSTR(‘sql.wang’,’wang’):5 |
POSITION(substr IN str) | 返回子串substr在字符串str第一次出现的位置,如果substr不在str里面,返回0。位置从1开始 | POSITION(‘wang’ IN ‘sql.wang’):5 |
ELT(n,str1,str2,…) | 返回第n个位置的字符串。如n=2,则返回str2。若n小于1或大于字符串的数目,则返回NULL | ELT(2,’sql’,’wang’):’wang’ |
FIELD(str,str1,str2,…) | 返回指定字符串str在列表str1、str2…中第一次出现的位置。若str不存在,则返回0 | FIELD(‘wang’,’sql’,’wang’):2 |
FIND_IN_SET(str1,str2) | 返回str1在str2中第一次出现的位置。str2为逗号分隔的字符串列表 | FIND_IN_SET(‘an’,’sq,lw,an,g’):3 |
MAKE_SET(x,str1,str2,…) | 根据x的二进制数从str1、str2…中选取字符串。x的二进制数从右往左数的位置上若为1,则选择对应位置的字符串。结果以逗号拼接 | MAKE_SET(3,’sql’,’.’,’wang’):’sql,.’ |
INSERT(str,pos,len,newStr) | 将str 中指定 pos 位置开始 len 长度的内容替换为 newStr | INSERT(‘sql.wang’,5,4,‘com’):‘sql.com’ |
LOWER(str)、LCASE(str) | 将字符串中的字母转换为小写 | LOWER(‘SQL.wang’):‘sql.wang’ |
UPPER(str)、UCASE(str) | 将字符串中的字母转换为大写 | UPPER(‘SQL.wang’):‘SQL.WANG’ |
LEFT(str,len) | 从左侧字截取字符串,返回字符串左边的len个字符 | LEFT(‘sql.wang’,3):‘sql’ |
RIGHT(str,len) | 从右侧字截取字符串,返回字符串右边的len个字符 | RIGHT(‘sql.wang’,4):‘wang’ |
LTRIM(str) | 删除字符串左侧的空格 | LTRIM(’ sql wang ‘):‘sql wang ’ |
RTRIM(str) | 删除字符串右侧的空格 | RTRIM(’ sql wang ‘):‘ sql wang’ |
TRIM(str) | 删除字符串左右两侧的空格 | TRIM(’ sql wang ‘):‘sql wang’ |
LPAD(str1,len,str2) | 将str1左边以str2填充,直到长度达到len | LPAD(‘sql’,’#’,5):’##sql’ |
RPAD(str1,len,str2) | 将str1右边以str2填充,直到长度达到len | RPAD(‘sql’,’#’,5):’sql##’ |
REPLACE(str,s1,s2) | 字符串替换函数,返回s2替换str中s1后的新字符串 | REPLACE(‘sql.wang’,’wang’,’com’):‘sql.com’ |
REPEAT(str,count) | 对str重复count次 | REPEAT(‘sql’,3):’sqlsqlsql’ |
SPACE(n) | 返回n个空格组成的字符串 | SPACE(5):’ ‘ |
SUBSTRING(str,num,len) MID(str,num,len) | 截取字符串,返回从指定num位置开始的指定len长度的字符串。num为负数时,表示从右往左的位置 | SUBSTRING(‘sql.wang’,5,2):‘wa’ SUBSTRING(‘sql.wang’,-5,2):‘.w’ |
REVERSE(str) | 字符串反转(逆序)函数,返回与原始字符串顺序相反的字符串 | REVERSE(‘sql.wang’):‘gnaw.lqs’ |
STRCMP(str1,str2) | 如果str1>str2返回1,str1=str2反回0,str1<str2返回-1 | STRCMP(‘sql’, ‘sql.wang’):-1 |
日期和时间函数
函数名称 | 作 用 | 示例 |
---|---|---|
CURDATE() 、 CURRENT_DATE | 两个函数作用相同,返回当前系统的日期值 | CURDATE():2021-10-09 |
CURTIME() 、 CURRENT_TIME | 两个函数作用相同,返回当前系统的时间值 | CURTIME():16:13:10 |
CURRENT_TIMESTAMP()、LOCALTIME()、NOW() 、 SYSDATE() | 两个函数作用相同,返回当前系统的日期和时间值 | CURRENT_TIMESTAMP():2021-10-09 16:13:10 |
UNIX_TIMESTAMP() | 获取UNIX时间戳函数,返回一个以 UNIX 时间戳为基础的无符号整数 | UNIX_TIMESTAMP():1655303190 |
FROM_UNIXTIME(X) | 将 UNIX 时间戳转换为时间格式,与UNIX_TIMESTAMP互为反函数 | FROM_UNIXTIME(1651302734):2022-04-30 15:12:14 |
YEAR(date) | 获取年份,返回值范围是 1970?2069 | YEAR(now()):2021 |
QUARTER(date) | 获取指定日期对应的一年中的季度,返回值范围是1~4 | QUARTER(now()):4 |
MONTH(date) | 获取指定日期中的月份 | MONTH(now()):10 |
MONTHNAME(date) | 获取指定日期中的月份英文名称 | MONTHNAME(now()):October |
WEEK(date,mode) | 获取指定日期是一年中的第几周,返回值的范围是否为 0?52 或 1?53。根据mode的值来认定一周的第一天,以及一年的第一周 | WEEK(now(),3):40 |
WEEKOFYEAR(date) | 获取指定日期是一年中的第几周,返回值的范围是否为 1?53。等价于WEEK(date,3) | WEEKOFYEAR(now()):40 |
WEEKDAY(date) | 获取指定日期在一周内的对应的工作日索引 | WEEKDAY(now()):6 |
DAYNAME(date) | 获取指定曰期对应的星期几的英文名称 | DAYNAME(now()):Sunday |
DAYOFWEEK(date) | 获取指定日期对应的一周的索引位置值 | DAYOFWEEK(now()):1 |
DAYOFYEAR(date) | 获取指定曰期是一年中的第几天,返回值范围是1~366 | DAYOFYEAR(now()):282 |
DAYOFMONTH(date) | 获取指定日期是一个月中是第几天,返回值范围是1~31 | DAYOFMONTH(now()):9 |
HOUR(time) | 获取指定时间的小时值,返回值范围是0~23 | HOUR(CURTIME()):16 |
MINUTE(time) | 获取指定时间的分钟值,返回值范围是0~59 | MINUTE(CURTIME()):13 |
SECOND(time) | 获取指定时间的秒数,返回值范围是0~59 | SECOND(CURTIME()):10 |
EXTRACT(type FROM date) | 根据type,从指定日期中提取一部分返回 | EXTRACT(YEAR FROM now()):2021 |
TIME_TO_SEC(time) | 将时间参数转换为秒数 | TIME_TO_SEC(CURTIME()):58390 |
SEC_TO_TIME(seconds) | 将秒数转换为时间,与TIME_TO_SEC 互为反函数 | SEC_TO_TIME(54):00:00:54 |
DATE_ADD、ADDDATE | 两个函数功能相同,都是向日期添加指定的时间间隔 | DATE_ADD(now(),interval 1 day):2021-10-10 16:13:10 |
DATE_SUB、SUBDATE | 两个函数功能相同,都是向日期减去指定的时间间隔 | DATE_SUB(now(),interval 1 day):2021-10-08 16:13:10 |
ADDTIME(expr1,expr2) | 时间加法运算,在原始时间上添加指定的时间 | ADDTIME(‘2018-10-31 23:59:59′,’0:1:1’):2018-11-01 00:01:00 |
SUBTIME(expr1,expr2) | 时间减法运算,在原始时间上减去指定的时间 | SUBTIME(‘2018-10-31 23:59:59′,’0:1:1’):2018-10-31 23:58:58 |
DATEDIFF(expr1,expr2) | 获取两个日期之间间隔,返回参数 1 减去参数 2 的值 | DATEDIFF(‘2021-04-30′,’2021-04-29’):1 |
DATE_FORMAT(date,format) | 格式化指定的日期,根据参数返回指定格式的值 | DATE_FORMAT(‘2021-04-30′,’%Y/%m/%d’):2021/04/30 |
WEEK函数中mode参数的取值:
模式 | 一周的第一天 | 范围 | 第一周是怎么计算的 |
---|---|---|---|
0 | 星期日 | 0-53 | 从本年的第一个星期日开始,是第一周。前面的计算为第0周 |
1 | 星期一 | 0-53 | 假如1月1日到第一个周一的天数超过3天,则计算为本年的第一周。否则为第0周 |
2 | 星期日 | 1-53 | 从本年的第一个星期日开始,是第一周。前面的计算为上年度的第5x周 |
3 | 星期一 | 1-53 | 假如1月1日到第一个周日天数超过3天,则计算为本年的第一周。否则为上年度的第5x周 |
4 | 星期日 | 0-53 | 假如1月1日到第一个周日的天数超过3天,则计算为本年的第一周。否则为第0周 |
5 | 星期一 | 0-53 | 从本年的第一个星期一开始,是第一周。前面的计算为第0周。 |
6 | 星期日 | 1-53 | 假如1月1日到第一个周日的天数超过3天,则计算为本年的第一周。否则为上年度的第5x周 |
7 | 星期一 | 1-53 | 从本年的第一个星期一开始,是第一周。前面的计算为上年度的第5x周 |
EXTRACT函数中type参数的取值:
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 | 返回年和月 |
条件判断函数
函数名称 | 作 用 | 示例 |
---|---|---|
ISNULL(v1) | 判断v1的值是否为NULL。如果为NULL,返回1;否则,返回0 | ISNULL(NULL):1 ISNULL(10):0 |
IF(expr,v1,v2) | 根据表达式 expr 得到不同的结果,当 expr 为真是返回 v1 的值,否则返回 v2 | if(age=10,1,2):如果字段age的值为10,则返回1;否则返回2; |
IFNULL(v1,v2) | 判断值是否为空。 如果 v1 不为 NULL,则 IFNULL 函数返回 v1; 否则返回 v2 的结果 | ifnull(age,0):如果字段age的值为NULL,则返回0;否则返回字段age本身的值; |
NULLIF(v1,v2) | 如果v1=v2,则返回null; 否则返回v1的值 | nullif(1,2):1 nullif(1,1):NULL |
CASE | 根据条件表达式得到不同的结果 | 写法一: select case age when 10 then 1 when 20 then 2 else 3 end from student; 写法二: select case when age <= 10 then 1 when age > 10 and age <=20 then 2 else 3 end from student; |
系统信息函数
函数名称 | 作 用 | 示例 |
---|---|---|
BENCHMARK(count,expr) | 执行表达式expr,并重复count次。常用于MySQL的基准测试。 | benchmark(10000, md5(‘sql.wang’ )) |
CHARSET(str) | 返回字符串参数的字符集 | CHARSET(‘sql.wang’):’utf8′ |
COLLATION(str) | 返回字符串参数的排序规则 | COLLATION(‘sql.wang’):’utf8_general_ci’ |
CONNECTION_ID() | 返回连接的连接ID(线程ID) | CONNECTION_ID():23876 |
CURRENT_ROLE() | 返回当前的活动角色 | CURRENT_ROLE():root @% |
CURRENT_USER(),CURRENT_USER | 经过身份验证的用户名和主机名 | CURRENT_USER():root@localhost |
DATABASE() | 返回默认(当前)数据库名称 | DATABASE():’school’ |
FOUND_ROWS() | 返回上一条语句返回的条数 | FOUND_ROWS():219 |
LAST_INSERT_ID() | 最后一个INSERT的AUTOINCREMENT列的值 | LAST_INSERT_ID():983 |
ROW_COUNT() | 更新的行数 | ROW_COUNT():2 |
SCHEMA() | DATABASE()的同义词 | SCHEMA():school |
SESSION_USER() | USER()的同义词 | SESSION_USER():root@localhost |
SYSTEM_USER() | USER()的同义词 | SYSTEM_USER():root@localhost |
USER() | 客户端提供的用户名和主机名 | USER():root@localhost |
VERSION() | 返回MySQL服务器版本 | VERSION():8.0.28 |
本站所有内容均为原创,本站保留所有权利。仅允许非商业用途的转载,但必须注明来源网站、作者、来源链接!否则,由此造成的一切后果,由转载方承担!
干货分享、技术提升、面试笔试、学习交流,欢迎关注公众号:xuesql。QQ学习交流群:209942678。