Mysql 内置函数
mysql数据库中提供了很丰富的函数,包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等。
简单介绍几类函数的使用范围：
数学函数：这类函数只要用于处理数字。这类函数包括绝对值函数、正弦函数、余弦函数、获取随机数函数等。
字符串函数：这类函数主要用于处理字符串。其中包括字符串连接函数、字符串比较函数、将字符串的字母变成小写或大写字母的函数、获取子串的函数等。
日期和时间函数：这类函数主要用于处理日期和时间。其中包括取当前时间的函数、获取当前日期的函数、返回年份的函数、返回日期的函数等。
流程函数：这类函数主要用于在SQL语句中控制条件选择。其中包括IF语句、CASE语句、WHEN语句等。
系统信息函数：这类函数主要用于获取mysql数据库的系统信息。其中包括获取数据库名的函数、获取当前用户的函数、获取数据库版本的函数等。
加密函数：这类函数主要用于对字符串进行加密解密。其中包括字符串加密函数、字符串解密函数等。
集合函数:对列求和、求平均值、最大值、最小值等
其他函数：包括格式化函数、锁函数等。
1.数学函数
|
函数 |
作用 |
|
ABS(x) |
返回x的绝对值 |
|
CEIL(x),CEILING(x) |
返回大于或等于x的最小整数（向上取整） |
|
FLOOR(x) |
返回小于或等于x的最大整数（向下取整） |
|
RAND() |
返回0~1的随机数 |
|
RAND(x) |
返回0~1的随机数，x值相同时返回的随机数相同 |
|
SIGN(x) |
返回x的符号，x是负数、0、正数分别返回-1、0、1 |
|
PI() |
返回圆周率 |
|
TRUNCATE(x,y) |
返回数值x保留到小数点后y位的值 |
|
ROUND(x) |
返回离x最近的整数（四舍五入） |
|
ROUND(x,y) |
保留x小数点后y位的值，但截断时要四舍五入 |
|
POW(x,y),POWER(x,y) |
返回x的y次方 |
|
SQRT(x) |
返回x的平方根 |
|
EXP(x) |
返回e的x次方 |
|
MOD(x,y) |
返回x 除以y以后的余数 |
|
LOG(x) |
返回自然对数（以e为底的对数） |
|
LOG10(x) |
返回以10为底的对数 |
|
RADIANS(x) |
讲角度转换为弧度 |
|
DEGREES(x) |
讲弧度转换为角度 |
|
SIN(x) |
求正弦值 |
|
ASIN(x) |
求反正弦值 |
|
COS(x) |
求余弦值 |
|
ACOS(x) |
求反余弦值 |
|
TAN(x) |
求正切值 |
|
ATAN(x),ATAN(x,y) |
求反正切值 |
|
COT(x) |
求余切值 |
2.字符串函数
|
函数 |
作用 |
|
CHAR_LENGTH(s) |
返回字符串s的字符数 |
|
LENGTH(s) |
返回字符串s的长度 |
|
CONCAT(s1,s2,.....) |
将字符串s1,s2等多个字符串合并为一个字符串 |
|
CONCAT_WS(x,s1,s2,....) |
同COUCAT(s1,s2,.....)，但是每个字符串之间要加上x |
|
INSERT(s1,x,len,s2) |
将字符串s2替换s1的x位置开始长度为len的字符串 |
|
UPPER(s),UCASE(s) |
讲字符串s的所有字符都变成大写字母 |
|
LOWER(s),LCASE(s) |
讲字符串s的所有字符都变成小写字母 |
|
LEFT(s,n) |
返回字符串s的前n个字符 |
|
RIGHT(s,n) |
返回字符串s的后n个字符 |
|
LPAD(s1,len,s2) |
字符串s2来填充s1的开始处，使字符串长度达到len |
|
RPAD(s1,len,s2) |
字符串s2来填充s1的结尾处，使字符串长度达到len |
|
LTRIM(s) |
去掉字符串s开始处的空格 |
|
RTRIM(s) |
去掉字符串s结尾处的空格 |
|
TRIM(s) |
去掉字符串s开始处和结尾处的空格 |
|
TRIM(s1 FROM s) |
去掉字符串s中开始处和结尾处的字符串s1 |
|
REPEAT(s,n) |
将字符串s重复n次 |
|
SPACE(n) |
返回n个空格 |
|
REPLACE(s,s1,s2) |
用字符串s2代替字符串s中的字符串s1 |
|
STRCMP(s1,s2) |
比较字符串s1和s2 |
|
SUBSTRING(s,n,len) |
获取从字符串s中的第n个位置开始长度为len的字符串 |
|
MID(s,n,len) |
同SUBSTRING(s,n,len) |
|
ATE(s1,s),POSTTION(s1 IN s) |
从字符串s中获取s1的开始位置 |
|
INSTR(s,s1) |
从字符串s中获取s1的开始位置 |
|
REVERSE(s) |
将字符串s的顺序反过来 |
|
ELT(n,s1,s2...) |
返回第n个字符串 |
|
FIELD(s,s1,s2...) |
返回第一个与字符串s匹配的字符串的位置 |
|
FIND_IN_SET(s1,s2) |
返回在字符串s2中与s1匹配的字符串的位置 |
|
MAKE_SET(x,s1,s2...) |
按x的二进制数从s1,s2......sn中选取字符串 |
3.日期和时间函数
|
函数 |
作用 |
|
CURDATE(),CURRENT_DATE() |
返回当前日期 |
|
CURTIME(),CURRENT_TIME() |
返回当前时间 |
|
NOW(),CURRENT_TIMESTAMP() LOCALTIME(),SYSDATE() LOCALTIMESTAMP() |
返回当前日期和时间 |
|
UNIX_TIMESTAMP() |
以UNIX时间戳的形式返回当前时间 |
|
UNIX_TIMESTAMP(d) |
将时间d以UNIX时间戳的形式返回 |
|
FROM_UNIXTIME(d) |
把UNIX时间戳的时间转换为普通格式的时间 |
|
UTC_DATE() |
返回UTC(国际协调时间)日期 |
|
UTC_TIME() |
返回UTC时间 |
|
MONTH(d) |
返回日期d中的月份值，范围是1~12 |
|
MONTHNAME(d) |
返回日期d中的月份名称，如january |
|
DAYNAME(d) |
返回日期d是星期几，如Monday |
|
DAYOFWEEK(d) |
返回日期d是星期几，1表示星期日，2表示星期2 |
|
WEEKDAY(d) |
返回日期d是星期几，0表示星期一,1表示星期2 |
|
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 d) |
从日期d中获取指定的值，type指定返回的值，如YEAR,HOUR等 |
|
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加上一个时间段后的日期 |
|
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类型数据的显示格式 |
4.系统信息函数
系统信息函数用来查询mysql数据库的系统信息。例如查询数据库的版本，查询数据的当前用户等。
|
函数 |
作用 |
|
VERSION() |
返回数据库的版本号 |
|
CONNECTION_ID() |
返回服务器的连接数，也就是到现在为止mysql服务的连接次数 |
|
DATABASE(),SCHEMA() |
返回当前数据库名 |
|
USER() |
返回当前用户的名称 |
|
CHARSET(str) |
返回字符串str的字符集 |
|
COLLATION(str) |
返回字符串str的字符排列方式 |
|
LAST_INSERT_ID() |
返回最后生成的auto_increment值 |
5.加密解密函数
加密函数是mysql中用来对数据进行加密的函数。
|
函数 |
作用 |
|
PASSWORD(str) |
对字符串str进行加密 |
|
MD5(str) |
对字符串str进行加密 |
|
ENCODE(str,pswd_str) |
使用字符串pswd_str来加密字符串str,加密结果是一个二进制数，必须使用BLOB类型来保持它 |
|
DECODE(crypt_str,pswd_str) |
解密函数，使用字符串pswd_str来为crypt_str解密 |
6.集合函数
| 函数 | 作用 |
| AVG() | 返回平均值 |
| COUNT() | 返回行数 |
| FIRST() | 返回第一个记录的值 |
| LAST() | 返回最后一个记录的值 |
| MAX() | 返回最大值 |
| MIN() | 返回最小值 |
| SUM() | 返回总和 |
7.其他函数
|
函数 |
作用 |
|
FORMAT(x,n) |
格式化函数，可以讲数字x进行格式化，将x保留到小数点后n位，这个过程需要进行四舍五入。 |
|
ASCII(s) |
返回字符串s的第一个字符的ASSCII码 |
|
BIN(x) |
返回x的二进制编码 |
|
HEX(x) |
返回x的十六进制编码 |
|
OCT(x) |
返回x的八进制编码 |
|
CONV(x,f1,f2) |
将x从f1进制数变成f2进制数 |
|
INET_ATON(IP) |
将IP地址转换为数字表示，IP值需要加上引号 |
|
INET_NTOA(n) |
可以将数字n转换成IP的形式 |
|
GET_LOCT(name,time) |
加锁函数，定义一个名称为name、持续时间长度为time秒的锁，如果锁定成功，返回1，如果尝试超时，返回0，如果遇到错误，返回NULL. |
|
RELEASE_LOCK(name) |
解除名称为name的锁，如果解锁成功，返回1，如果尝试超时，返回0，如果解锁失败，返回NULL。 |
|
IS_FREE_LOCK(name) |
判断是否使用名为name的锁，如果使用，返回0，否则返回1. |
|
CONVERT(s USING cs) |
将字符串s的字符集变成cs |
|
CAST(x AS type),CONVERT(x,type) |
这两个函数将x变成type类型，这两个函数只对BINARY,CHAR,DATE,DATETIME,TIME,SIGNED INTEGER,UNSIGNED INTEGER这些类型起作用，但这两种方法只是改变了输出值得数据类型，并没有改变表中字段的类型。 |
8.条件判断函数
条件判断函数用来在SQL语句中进行条件判断。更加是否满足判断条件，SQL语句执行不同的分支。
8.1 IF(expr,v1,v2)函数
IF(expr,v1,v2)函数中，如果表达式expr成立，返回结果v1,否则，返回结果v2。
mysql> select name,if(price>2,'OK','ON') from food;
+--------------+-----------------------+
| name | if(price>2,'OK','ON') |
+--------------+-----------------------+
| 光磊饼干 | OK |
| 宪政牛奶 | OK |
| 兴周果冻 | ON |
| GG咖啡 | OK |
| XX奶糖 | OK |
+--------------+-----------------------+
5 rows in set (0.00sec)
8.2CASE函数
CASEWHEN expr1 THEN v1 [WHEN expr2 THEN v2...][ELSE vn] END
mysql> selectname,case when price =1.5 then 'OK' when price >3.5 then 'on my god' endfrom food;
+--------------+---------------------------------------------------------------------+
| name | case when price =1.5 then 'OK' whenprice >3.5 then 'on my god' end |
+--------------+---------------------------------------------------------------------+
| 光磊饼干 | NULL |
| 宪政牛奶 | NULL |
| 兴周果冻 | OK |
| GG咖啡 | on my god |
| XX奶糖 | on my god |
+--------------+---------------------------------------------------------------------+
5 rows in set (0.00sec)
使用举例：
SELECT VERSION(); SELECT COUNT(*) FROM tuser; SELECT SUM(score) FROM student; SELECT POW(2, 3); SELECT NOW(); SELECT PASSWORD('123456'); SELECT MD5('123456')
