MySQL常用函数大全
一、数字函数
函数 | 作用 |
---|
ABS(x) | 返回x的绝对值 |
BIN(x) | 返回x的二进制(OCT返回八进制,HEX返回十六进制) |
EXP(x) | 返回值e(自然对数的底)的x次方 |
GREATEST(x1,x2,…,xn) | 返回集合中最大的值 |
LEAST(x1,x2,…,xn) | 返回集合中最小的值 |
LN(x) | 返回x的自然对数 |
LOG(x,y) | 返回x的以y为底的对数 |
MOD(x,y) | 返回x/y的模(余数) |
PI() | 返回pi的值(圆周率) |
RAND() | 返回0到1内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。 |
FLOOR(x) | 返回小于x的最大整数值,(去掉小数取整) |
CEILING(x) | 返回大于x的最小整数值,(进一取整) |
ROUND(x,y) | 返回参数x的四舍五入的有y位小数的值,(四舍五入) |
TRUNCATE(x,y) | 返回数字x截短为y位小数的结果 |
SIGN(x) | 返回代表数字x的符号的值(正数返回1,负数返回-1,0返回0) |
SQRT(x) | 返回一个数的平方根 |
1.1 ABS(x) 绝对值
SELECT ABS(-1);
1.2 AVG(expression) 平均值
1.3 CEIL(x)/CEILING(x) 向上取整
SELECT CEIL(1.5);
SELECT CEILING(1.5);
1.4 FLOOR(x)向下取整
SELECT FLOOR(1.5);
1.5 EXP(x) x 次方
SELECT EXP(3);
1.6 GREATEST(expr1, expr2, …) 返回最大值
SELECT GREATEST(3, 12, 34, 8, 25);
1.7 LEAST(expr1, expr2, …) 返回最小值
SELECT LEAST(3, 12, 34, 8, 25);
1.8 LN 返回数字的自然对数
SELECT LN(2);
1.9 LOG(x) 返回自然对数(以 e 为底的对数)
SELECT LOG(20.085536923188);
1.10 MAX(expression) 最大值
SELECT MAX(age) AS maxAge FROM Student;
1.11 MIN(expression) 最小值
SELECT MIN(age) AS minAge FROM Student;
1.12 POW(x,y)/POWER(x,y)返回 x 的 y 次方
SELECT POW(2,3);
SELECT POWER(2,3);
1.13 RAND()返回 0 到 1 的随机数
SELECT RAND();
1.14 ROUND(x)返回离 x 最近的整数
SELECT ROUND(1.23456);
1.15 SIGN(x)返回 x 的符号,x 是负数、0、正数分别返回 -1、0 和 1
SELECT SIGN(-10);
1.16 SQRT(x)返回x的平方根
SELECT SQRT(25);
1.17 SUM(expression)返回指定字段的总和
SELECT SUM(age) AS totalAage FROM Student;
1.18 TRUNCATE(x,y)返回数值 x 保留到小数点后 y 位的值(与 ROUND 最大的区别是不会进行四舍五入)
SELECT TRUNCATE(1.23456,3);
二、字符串函数
函数 | 作用 |
---|
ASCII(char) | 返回字符的ASCII码值 |
BIT_LENGTH(str) | 返回字符串的比特长度 |
CONCAT(s1,s2…,sn) | 将s1,s2…,sn连接成字符串 |
CONCAT_WS(sep,s1,s2…,sn) | 将s1,s2…,sn连接成字符串,并用sep字符间隔 |
INSERT(str,x,y,instr) | 将字符串str从第x位置开始,y个字符长的子串替换为字符串instr,返回结果 |
FIND_IN_SET(str,list) | 分析逗号分隔的list列表,如果发现str,返回str在list中的位置 |
LCASE(str)或LOWER(str) | 返回将字符串str中所有字符改变为小写后的结果 |
UCASE(str)或UPPER(str) | 返回将字符串str中所有字符转变为大写后的结果 |
LEFT(str,x) | 返回字符串str中最左边的x个字符 |
RIGHT(str,x) | 返回字符串str中最右边的x个字符 |
LENGTH(str) | 返回字符串str中的字符数 |
POSITION(substr,str) | 返回子串substr在字符串str中第一次出现的位置 |
QUOTE(str) | 用反斜杠转义str中的单引号 |
REPEAT(str,srchstr,rplcstr) | 返回字符串str重复x次的结果 |
REVERSE(str) | 返回颠倒字符串str的结果 |
LTRIM(str) | 去掉字符串str开头的空格 |
RTRIM(str) | 去掉字符串str尾部的空格 |
TRIM(str) | 去除字符串首部和尾部的所有空格 |
2.1 返回字符串 s 的第一个字符的 ASCII 码
SELECT ASCII('AB');
2.2 LENGTH/CHAR_LENGTH(s)/CHARACTER_LENGTH(s)返回字符串 s 的字符数
SELECT LENGTH('1234');
2.3 多个字符串合并为一个字符串
学生成绩表 t
id | name | score |
---|
1 | maryleo | 60 |
2 | nancysun | 80 |
3 | 小明 | 72 |
4 | 小明 | 93 |
5 | 小明 | 81 |
6 | 小王 | 76 |
7 | 小王 | 80 |
8 | 小丽 | 88 |
**CONCAT(s1,s2…sn)字符串 s1,s2 **
功能:将多个字符串连接成一个字符串。
语法:concat(str1, str2,…)
SELECT CONCAT('hel','llo');
SELECT CONCAT(ID,NAME,SCORE) stuInfo from t where id=4
concat_ws() 和concat() 一样,将多个字符串连接成一个字符串,但是可以一次性指定分隔符
语法:concat_ws(separator, str1, str2, …)
SELECT concat_ws(',',ID,NAME,SCORE) stuInfo from t where id=4
GROUP_CONCAT(s1,s2…sn) GROUP BY一起用 在select里如果使用了limit是不起作用的
- 使用group_concat()和group by显示相同名字的人的id号:
SELECT name,group_concat(id) from t group by name;
name | group_concat(id) |
---|
maryleo | 1 |
nancysun | 2 |
小丽 | 8 |
小明 | 3_4_5 |
小王 | 6_7 |
- 将上面的id号从大到小排序,且用’_'作为分隔符
SELECT name,group_concat(id order by id desc separator '_') from t group by name;
name | group_concat(id) |
---|
maryleo | 1 |
nancysun | 2 |
小丽 | 8 |
小明 | 3,4,5 |
小王 | 6,7 |
- 上面的查询中显示了以name分组的每组中所有的id。接下来我们要查询以name分组的所有组的id和score
SELECT name,group_concat(concat_ws('-',id,score)order by id) from t group by name;
name | group_concat(id) |
---|
maryleo | 1-60 |
nancysun | 2-80 |
小丽 | 8-88 |
小明 | 3-72,4-93,5-81 |
小王 | 6-80,7-88 |
2.4 FIND_IN_SET(s1,s2)返回在字符串s2中与s1匹配的字符串的位置
SELECT FIND_IN_SET("c", "a,b,c,d,e");
2.5 FORMAT(x,n)函数可以将数字 x 进行格式化 “#,###.##”, 将 x 保留到小数点后 n 位,最后一位四舍五入
SELECT FORMAT(250500.5634, 2);
2.6 INSERT(s1,x,len,s2)字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串
SELECT INSERT("google.com", 1, 6, "runnob");
2.7 LOCATE(s1,s)从字符串 s 中获取 s1 的开始位置
SELECT LOCATE('st','myteststring');
2.8 LCASE(s)/LOWER(s)将字符串 s 的所有字母变成小写字母
SELECT LOWER('RUNOOB');
2.9 UCASE(s)/UPPER(s)将字符串 s 的所有字母变成大写字母
SELECT UCASE('runoob');
2.10 TRIM(s)去掉字符串 s 开始和结尾处的空格
SELECT TRIM(' RUNOOB ');
2.11 LTRIM(s)去掉字符串 s 开始处的空格
SELECT LTRIM(' RUNOOB ');
2.12 RTRIM(s)去掉字符串 s 结尾处的空格
SELECT RTRIM(' RUNOOB ');
2.13 SUBSTR(s, start, length)从字符串 s 的 start 位置截取长度为 length 的子字符串
SELECT SUBSTR("RUNOOB", 2, 3) AS ExtractString;
2.14 SUBSTR/SUBSTRING(s, start, length)/MID(s,start,length)从字符串 s 的 start 位置截取长度为 length 的子字符串
SELECT SUBSTR/SUBSTRING("RUNOOB", 2, 3);
SELECT MID('您好,欢迎访问pan_junbiao的博客',8,14);
2.15 POSITION(s1 IN s)从字符串 s 中获取 s1 的开始位置
SELECT POSITION('b' in 'abc');
2.16 REPEAT(s,n)将字符串 s 重复 n 次
SELECT REPEAT('runoob',3);
2.17 REVERSE(s)将字符串s的顺序反过来
SELECT REVERSE('abc');
2.18 STRCMP(s1,s2)比较字符串 s1 和 s2,如果 s1 与 s2 相等返回 0 ,如果 s1>s2 返回 1,如果 s1<s2 返回 -1
SELECT STRCMP("runoob", "runoob");
2.19 REPLACE(s,s1,s2)函数
返回一个字符串,用字符串s2替代字符串s中所有的字符串s1。
SELECT REPLACE('您好,欢迎访问阿标的博客','阿标','pan_junbiao');
三、日期函数
函数 | 说明 |
---|
CURDATE()、CURRENT_DATE() | 返回当前日期,格式:yyyy-MM-dd。 |
CURTIME()、CURRENT_TIME() | 返回当前时间,格式:HH:mm:ss。 |
NOW()、CURRENT_TIMESTAMP()、LOCALTIME()、SYSDATE()、LOCALTIMESTAMP() | 返回当前日期和时间,格式:yyyy-MM-dd HH:mm:ss。 |
UNIX_TIMESTAMP() | 返回一个格林尼治标准时间1970-01-01 00:00:00到现在的秒数。 |
UNIX_TIMESTAMP(date) | 返回一个格林尼治标准时间1970-01-01 00:00:00到指定时间的秒数。 |
FROM_UNIXTIME(date) | 和UNIX_TIMESTAMP互为反函数,把UNIX时间戳转换为普通格式的时间。 |
UTC_DATE() | 返回当前UTC(世界标准时间)日期值,其格式为"YYYY-MM-DD"或"YYYYMMDD"。 |
UTC_TIME() | 返回当前UTC时间值,其格式为"YYYY-MM-DD"或"YYYYMMDD"。具体使用哪种取决于函数用在字符串还是数字语境中 |
MONTH(d) | 返回日期d中的月份值,范围是1~12。 |
MONTHNAME(d) | 返回日期d中的月份名称,如:January、February等。 |
DAYNAME(d) | 返回日期d是星期几,如:Monday、Tuesday等。 |
DAYOFWEEK(d) | 返回日期d是星期几,如:1表示星期日,2表示星期一等。 |
WEEKDAY(d) | 返回日期d是星期几,如:0表示星期一,1表示星期二等。 |
WEEK(d) | 计算日期d是本年的第几个星期,范围是0~53。 |
WEEKOFYEAR(d) | 计算日期d是本年的第几个星期,范围是1~53。 |
DAYOFYEAR(d) | 计算日期d是本年的第几天。 |
DAYOFMONTH(d) | 计算日期d是本月的第几天。 |
YEAR(d) | 返回日期d中的年份值。 |
QUARTER(d) | 返回日期d是第几季度,范围是1~4。 |
HOUR(t) | 返回时间t中的小时值。 |
MINUTE(t) | 返回时间t中的分钟值。 |
SECOND(t) | 返回时间t中的秒钟值。 |
EXTRACT(type FROM date) | 从日期中提取一部分,type可以是YEAR、YEAR_MONTH、DAY_HOUR、DAY_MICROSECOND、DAY_MINUTE、DAY_SECOND |
TIME_TO_SEC(t) | 将时间t转换为秒。 |
SEC_TO_TIME(s) | 将以秒为单位的时间s转换为时分秒的格式。 |
TO_DAYS(d) | 计算日期d至0000年1月1日的天数。 |
FROM_DAYS(n) | 计算从0000年1月1日开始n天后的日期。 |
DATEDIFF(d1,d2) | 计算日期d1与d2之间相隔的天数。 |
ADDDATE(d,n) | 计算起始日期d加上n天的日期。 |
ADDDATE(d,INTERVAL expr type) | 计算起始日期d加上一个时间段后的日期。 |
DATE_ADD(d,INTERVAL expr type) | 同ADDDATE(d,INTERVAL expr type) |
SUBDATE(d,n) | 计算起始日期d减去n天的日期。 |
SUBDATE(d,INTERVAL expr type) | 计算起始日期d减去一个时间段后的日期。 |
ADDTIME(t,n) | 计算起始时间t加上n秒的时间。 |
SUBTIME(t,n) | 计算起始时间t减去n秒的时间。 |
DATE_FORMAT(d,f) | 按照表达式 f 的要求显示日期d。 |
TIME_FORMAT(t,f) | 按照表达式 f 的要求显示时间t。 |
GET_FORMAT(type, s) | 根据字符串s获取type类型数据的显示格式。 |
3.1 CURDATE()/CURRENT_DATE()返回当前日期
SELECT CURDATE();
SELECT CURRENT_DATE();
3.2 CURRENT_TIME()/CURTIME()返回当前时间
SELECT CURRENT_TIME();
3.3 CURRENT_TIMESTAMP()返回当前日期和时间
SELECT CURRENT_TIMESTAMP();
3.4 ADDDATE(d,n)计算起始日期 d 加上 n 天的日期
SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY);
3.5 ADDTIME(t,n)时间 t 加上 n 秒的时间
SELECT ADDTIME('2011-11-11 11:11:11', 5);
3.6 DATE()从日期或日期时间表达式中提取日期值
SELECT DATE("2017-06-15 11:11:16");
3.7 DAY(d)返回日期值 d 的日期部分
SELECT DAY("2017-06-15");
3.8 DATEDIFF(d1,d2)计算日期 d1->d2 之间相隔的天数
SELECT DATEDIFF('2001-01-01','2001-02-02');
3.9 DATE_FORMAT按表达式 f的要求显示日期 d
SELECT DATE_FORMAT('2011.11.11 11:11:11','%Y-%m-%d %r');
3.10 DAYNAME(d)返回日期 d 是星期几,如 Monday,Tuesday
SELECT DAYNAME('2011-11-11 11:11:11');
3.11 DAYOFMONTH(d)计算日期 d 是本月的第几天
SELECT DAYOFMONTH('2011-11-11 11:11:11');
3.12 DAYOFWEEK(d)日期 d 今天是星期几,1 星期日,2 星期一,以此类推
SELECT DAYOFWEEK('2011-11-11 11:11:11');
3.13 DAYOFYEAR(d)计算日期 d 是本年的第几天
SELECT DAYOFYEAR('2011-11-11 11:11:11');
3.14 EXTRACT(type FROM d)从日期 d 中获取指定的值,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
SELECT EXTRACT(MINUTE FROM '2011-12-13 14:15:16');
3.15 DAYOFWEEK(d)日期 d 今天是星期几,1 星期日,2 星期一,以此类推
SELECT DAYOFWEEK('2011-11-11 11:11:11');
3.16 UNIX_TIMESTAMP()得到时间戳
SELECT UNIX_TIMESTAMP('2019-2-19');
SELECT UNIX_TIMESTAMP(expression);
3.17 FROM_UNIXTIME()时间戳转日期
SELECT FROM_UNIXTIME(1550505600);
SELECT FROM_UNIXTIME(1550505600, '%Y-%m-%d');
四、MySQL高级函数
函数 | 说明 |
---|
VERSION() | 获取数据库的版本号。 |
CONNECTION_ID() | 获取服务器的连接数。 |
DATABASE()、SCHEMA() | 获取当前数据库名。 |
USER()、SYSTEM_USER()、SESSION_USER() | 获取当前用户名。 |
CURRENT_USER()、CURRENT_USER | 获取当前用户名。 |
CHARSET(str) | 获取字符串str的字符集。 |
COLLATION(str) | 获取字符串str的字符排序方法。 |
LAST_INSERT_ID() | 获取最近生成的AUTO_INCREMENT值。 |
函数 | 说明 |
---|
PASSWORD(str) | 对字符串str进行加密。经此函数加密后的数据是不可逆的。其经常用于对普通数据进行加密。 |
MD5(str) | 对字符串str进行MD5加密。经常用于对普通数据进行加密。 |
ENCODE(str,pass_str) | 使用字符串pass_str来加密字符串str。加密后的结果是一个二进制数,必须使用BLOB类型的字段来保存它。 |
DECODE(crypt_str,pass_str) | 使用字符串pass_str来为crypt_str解密。 |
函数 | 说明 |
---|
FORMAT(X,D) | 将数字X格式化,将X保留到小数点后D位,截断时要进行四舍五入。 |
CONV(N,from_base,to_base) | 不同进制数之间的转换,返回值为数值N的字符串表示,由from_base进制转换为to_base进制。 |
INET_ATON(expr) | 给出一个作为字符串的网络地址的点地址表示,返回一个代表该地址数值的整数,地址可以使4或8比特。 |
INET_NTOA(expr) | 给定一个数字网络地址(4或8比特),返回作为字符串的该地址的点地址表示。 |
BENCHMARK(count,expr) | 重复执行count次表达式expr,它可以用于计算MySQL处理表达式的速度,结果值通常是0(0只是表示很快,并不是没有速度)。另一个作用是用它在MySQL客户端内部报告语句执行的时间。 |
CONVERT(str USING charset) | 使用字符集charset表示字符串str。 |
4.1 IF(expr,v1,v2)如果表达式 expr 成立,返回结果 v1;否则,返回结果 v2
SELECT IF(1>0,'yes','no');
4.2 CONV(x,f1,f2)返回 f1 进制数变成 f2 进制数
SELECT CONV(13,10,2);
4.3 返回当前用户
SELECT CURRENT_USER();
SELECT SESSION_USER();
SELECT SYSTEM_USER();
SELECT USER();
4.4 返回当前数据库名
SELECT DATABASE();
4.5 返回数据库的版本号
SELECT VERSION();