MySQL——函数
MySQL数据库提供了很多函数包括:
(1)数学函数
(2)字符串函数
(3)日期和时间函数
(4)条件判断函数
(5)系统信息函数
(6)加密函数
(7)格式化函数
一、数学函数
数学函数主要用于处理数字,包括整型、浮点数等。
函数 | 作用 |
ABS(x) | 返回x的绝对值 |
CEIL(x),CEILING(x) | 返回大于或等于x的最小整数 |
FLOOR(x) | 返回小于或等于x的最大整数 |
RAND() | 返回0-1之间的随机数 |
SIGN(x) | 返回x的符号,x是负数、0、正数分别返回-1、0、1 |
PI() | 返回圆周率 |
TRUNCATE(x,y) | 保留小数点后的几位小数,y是保留的位数 |
ROUND(x[,y]) | y不存在则四舍五入取整数,y存在则是要保留几位小数 |
POW(x,y),POWER(x,y) | 返回x的y次方 |
SQRT(x) | 返回x的平方根 |
EXP(x) | 返回e的x次方,e是以无理数为底数的对数 |
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) | 求反正切值(参数是弧度) |
COT(x) | 求余切值(参数是弧度) |
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> select ABS(-1) -> ; +---------+ | ABS(-1) | +---------+ | 1 | +---------+ 1 row in set (0.00 sec) mysql> select CEIL(1.5); +-----------+ | CEIL(1.5) | +-----------+ | 2 | +-----------+ 1 row in set (0.00 sec) mysql> select CEILING(1.5); +--------------+ | CEILING(1.5) | +--------------+ | 2 | +--------------+ 1 row in set (0.00 sec) mysql> select FLOOR(1.5); +------------+ | FLOOR(1.5) | +------------+ | 1 | +------------+ 1 row in set (0.00 sec) mysql> select RAND(); +--------------------+ | RAND() | +--------------------+ | 0.9863102249673663 | +--------------------+ 1 row in set (0.00 sec) mysql> select RAND(); +---------------------+ | RAND() | +---------------------+ | 0.13673951396414966 | +---------------------+ 1 row in set (0.00 sec) mysql> select RAND(2); +--------------------+ | RAND(2) | +--------------------+ | 0.6555866465490187 | +--------------------+ 1 row in set (0.00 sec) mysql> select RAND(1); +---------------------+ | RAND(1) | +---------------------+ | 0.40540353712197724 | +---------------------+ 1 row in set (0.00 sec) mysql> select SIGN(-10); +-----------+ | SIGN(-10) | +-----------+ | -1 | +-----------+ 1 row in set (0.00 sec) mysql> select SIGN(10); +----------+ | SIGN(10) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) mysql> select SIGN(0); +---------+ | SIGN(0) | +---------+ | 0 | +---------+ 1 row in set (0.00 sec) mysql> select PI(); +----------+ | PI() | +----------+ | 3.141593 | +----------+ 1 row in set (0.00 sec) mysql> select PI(10); ERROR 1582 (42000): Incorrect parameter count in the call to native function 'PI' mysql> select truncate(1.23456,3); +---------------------+ | truncate(1.23456,3) | +---------------------+ | 1.234 | +---------------------+ 1 row in set (0.00 sec) mysql> select ROUND(1.2345); +---------------+ | ROUND(1.2345) | +---------------+ | 1 | +---------------+ 1 row in set (0.00 sec) mysql> select ROUND(1.6345); +---------------+ | ROUND(1.6345) | +---------------+ | 2 | +---------------+ 1 row in set (0.00 sec) mysql> select ROUND(1.6345,3); +-----------------+ | ROUND(1.6345,3) | +-----------------+ | 1.635 | +-----------------+ 1 row in set (0.00 sec) mysql> select POW(3,2); +----------+ | POW(3,2) | +----------+ | 9 | +----------+ 1 row in set (0.00 sec) mysql> select POWER(3,2); +------------+ | POWER(3,2) | +------------+ | 9 | +------------+ 1 row in set (0.00 sec) mysql> select EXP(3); +--------------------+ | EXP(3) | +--------------------+ | 20.085536923187668 | +--------------------+ 1 row in set (0.00 sec) mysql> select MOD(5,2); +----------+ | MOD(5,2) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) mysql> select MOD(10,2); +-----------+ | MOD(10,2) | +-----------+ | 0 | +-----------+ 1 row in set (0.00 sec) mysql> select LOG(2); +--------------------+ | LOG(2) | +--------------------+ | 0.6931471805599453 | +--------------------+ 1 row in set (0.00 sec) mysql> select LOG(3); +--------------------+ | LOG(3) | +--------------------+ | 1.0986122886681098 | +--------------------+ 1 row in set (0.00 sec) mysql> select LOG10(100); +------------+ | LOG10(100) | +------------+ | 2 | +------------+ 1 row in set (0.00 sec) mysql> select RADIANS(100); +--------------------+ | RADIANS(100) | +--------------------+ | 1.7453292519943295 | +--------------------+ 1 row in set (0.00 sec) mysql> select RADIANS(180); +-------------------+ | RADIANS(180) | +-------------------+ | 3.141592653589793 | +-------------------+ 1 row in set (0.00 sec) mysql> select DEGREES(3.1415); +--------------------+ | DEGREES(3.1415) | +--------------------+ | 179.99469134034814 | +--------------------+ 1 row in set (0.00 sec)
二、字符串函数
字符串函数是MySQL中最常见的一类函数,字符串函数主要用于处理表中的字符串。
函数 | 说明 |
char_length(s) | 返回字符串的字符数 |
length(s) | 返回字符串的字节数 |
concat(s1,s2,....) |
将字符串s1和s2等多个字符串合并为一个字符串 |
concat_ws(x,s1,s2...) | 将多个字符串合并,中间用x连接 |
insert(s1,x,len,s2) | 将字符串s2替换s1的x位置开始长度为len的字符串 |
upper(s),ucaase(s) | 将字符串变为大写 |
lower(s),lcase(s) | 将字符串变为小写 |
left(s,n) | 返回字符串s的前n个字符 |
right(s,n) | 返回字符串s的后n个字符 |
lpad(s1,len,s2) | 字符串s2来填充s1的开始处,使长度达到len |
prad(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 |
substring(s,n,len) | 获取从字符串s中的第n个位置开始长度为len的字符串 |
mid(s,n,len) | 获取从字符串s中的第n个位置开始长度为len的字符串 |
locate(s1,s),position(s1 in s) | 从字符串s中获取s1的开始位置 |
instr(s,s1) | 从字符串s中获取s1的开始位置 |
reveres(s) | 将字符串s的顺序反过来 |
elt(n,s1,s2...) | 返回第n个字符串 |
export_set(x,s1,s2) |
返回一个字符串,在这里对于在“bits”中设定每一位,你得到一个“on”字符串, 并且对于每个复位(reset)的位,你得到一个 “off”字符串。每个字符串用“separator”分隔(缺省“,”), 并且只有“bits”的“number_of_bits” (缺省64)位被使用。 |
field(s,s1,s2) | 返回第一个与字符串s匹配的字符串位置 |
find_in_set(s1,s2) | 返回在字符串s2中与s1匹配的字符串的位置 |
make_set(x,s1,s2) | 返回一个集合 (包含由“,”
字符分隔的子串组成的一个 字符串),由相应的位在 |
substring_index |
返回从字符串str的第count个出现的分隔符delim之后的子串。 如果count是正数,返回第count个字符左边的字符串。 如果count是负数,返回第(count的绝对值(从右边数))个字符右边的字符串。 |
load_file(file_name) |
读入文件并且作为一个字符串返回文件内容。文件必须在服务器上,你必须指定到文件的完整路径名, 而且你必须有file权 限。文件必须所有内容都是可读的并且小于max_allowed_packet。 如果文件不存在或由于上面原因之一不能被读出,函数返回NULL |
三、日期和时间函数
MySQL的日期和时间函数主要用于处理日期时间
函数 | 说明 |
curdate(),current_date() | 返回当前时间 |
curtime(),current_date() | 返回当前时间 |
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中的月份值 |
monthname(d) | 返回日期当中的月份名称 |
dayname(d) | 返回日期d是星期几 |
dayofweek(d) | 日期d今天是星期几,1星期日,2星期一 |
weekday(d) | 日期d今天是星期几,0星期一,1星期二 |
week(d),weekofyear(d) | 计算日期d是本年的第几个星期 |
dayofyear(d) | 计算日期d是本年的第几天 |
dayofmonth(d) | 计算日期d是本月的第几天 |
quarter(d) | 返回日期d是第几季节 |
hour(t) | 返回t中的小时值 |
minute(t) | 返回t中的分钟值 |
second(t) | 返回t中的秒钟值 |
extract(type from d) | 从日期d中获取指定的值,type指定返回的值 |
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) | 同上 |
subdate(d,n) | 日期d减去n天后的日期 |
subdate(d,INTERVAL expr type) | 日期d减去一个时间段后的日期 |
addtime(t,n) | 时间t加上n秒的时间 |
subtime(t,n) | 时间t减去n秒的时间 |
date_format(t,f) | 按表达式f的要求显示日期d |
time_format(t,f) | 按表达式f的要求显示时间t |
get_format(type,s) | 获取国家地区时间格式函数 |
注意:d的格式可以是xxxx-xx-xx xx:xx:xx也可以是xxxx-xx-xx,根据需求来定
四、条件判断函数
1.IF(expr,v1,v2)函数:如果表达式expr成立,返回结果v1,否则,返回结果v2
mysql> select IF(1>0,'正确','错误'); +---------------------------+ | IF(1>0,'正确','错误') | +---------------------------+ | 正确 | +---------------------------+ 1 row in set (0.00 sec)
2.IFNULL(v1,v2)函数:如果v1的值不为NULL,则返回v1,否则返回v2。
mysql> select IFNULL(NULL,'前面NULL'); +---------------------------+ | IFNULL(NULL,'前面NULL') | +---------------------------+ | 前面NULL | +---------------------------+ 1 row in set (0.00 sec)
3.CASE函数
语法:
CASE WHEN e1 THEN v1 WHEN e2 THEN v2 ... ELSE vn END; #CASE表示函数开始,END表示函数结束。 #如果e1成立则返回v1,函数结束,否则执行e2,如果e2成立,则返回v2.. #如果上面所有的条件都不成立,则返回vn
演示:
mysql> select CASE -> when 1 > 0 -> then '1>0' -> when 2 > 0 -> then '2>0' -> else '3>0' -> end -> ; +-----------------------------------------------------------------------------------------+ | CASE when 1 > 0 then '1>0' when 2 > 0 then '2>0' else '3>0' end | +-----------------------------------------------------------------------------------------+ | 1>0 #结果 | +-----------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
还有另外一种写法:
CASE WHEN e1 THEN v1 WHEN e2 THEN v2 ... ELSE vn END;
五、系统信息函数
系统信息函数用来查询MySQL数据库的系统信息。
函数 | 作用 |
VERSION() | 返回数据库版本号 |
CONNECTION_ID() | 返回服务器的连接数 |
USER()、SYSTEM_USER()、SESSION_USER()、CURRENT_USER() | 返回当前用户 |
CHARSET(str) | 返回字符串str的字符集 |
COLLATION(str) | 返回字符串str的字符排列方式 |
LAST_INSERT_ID() | 返回最近生成的AUTO_INCREMENT值 |
DATABASES()、SCHEMA() | 返回当前数据库名 |
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#查询数据库的版本号 mysql> select VERSION(); +------------+ | VERSION() | +------------+ | 5.6.21-log | +------------+ 1 row in set (0.00 sec) #查看服务器的连接数 mysql> select CONNECTION_ID(); +-----------------+ | CONNECTION_ID() | +-----------------+ | 1 | +-----------------+ 1 row in set (0.00 sec) #查看当前数据库名 mysql> select DATABASE(); +--------------+ | DATABASE() | +--------------+ | 多表联合 | +--------------+ 1 row in set (0.00 sec) mysql> select SCHEMA(); +--------------+ | SCHEMA() | +--------------+ | 多表联合 | +--------------+ 1 row in set (0.00 sec) mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) #查看当前用户 mysql> select SYSTEM_USER(); +----------------+ | SYSTEM_USER() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> select SESSION_USER(); +----------------+ | SESSION_USER() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> select CURRENT_USER(); +----------------+ | CURRENT_USER() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) #查看该字符串的字符集 mysql> select CHARSET('fghjk') -> ; +------------------+ | CHARSET('fghjk') | +------------------+ | utf8 | +------------------+ 1 row in set (0.00 sec) #查看字符串的排序方式 mysql> select COLLATION('中国'); +---------------------+ | COLLATION('中国') | +---------------------+ | utf8_general_ci | +---------------------+ 1 row in set (0.00 sec) mysql> select LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 0 | +------------------+ 1 row in set (0.00 sec)
六、加密函数
1.PASSWORD(str):该函数可以对字符串str进行加密,PASSWORD(str)用于给用户的密码加密
mysql> select PASSWORD('1234'); +-------------------------------------------+ | PASSWORD('1234') | +-------------------------------------------+ | *A4B6157319038724E3560894F7F932C8886EBFCF | +-------------------------------------------+ 1 row in set (0.00 sec)
2.MD5(str):函数可以对字符串str进行散列,可以用于一些普通的不需要解密的数据加密
mysql> select MD5('1234'); +----------------------------------+ | MD5('1234') | +----------------------------------+ | 81dc9bdb52d04dc20036dbd8313ed055 | +----------------------------------+ 1 row in set (0.00 sec)
3.ENCODE(str,pswd_str)与DECODE(crypt_str,pswd_str)
ENCODE函数可以使用加密密码pswd_str来加密字符串,加密结果是二进制,需要使用BLOB类型的字段保存。该函数与DECODE是一对,需要同样的密码才能够解密。
mysql> select ENCODE('123','xxoo'); #xxoo可以视为加密方式 +----------------------+ | ENCODE('123','xxoo') | +----------------------+ | ;vx | +----------------------+ 1 row in set (0.00 sec) mysql> select DECODE(';vx','xxoo'); +----------------------+ | DECODE(';vx','xxoo') | +----------------------+ | 123 | +----------------------+ 1 row in set (0.00 sec)
七、其他函数
1.格式化函数FORMAT(x,n):将x进行格式化,将x保留到小数点后n位。
mysql> select FORMAT(3.1415626,3); +---------------------+ | FORMAT(3.1415626,3) | +---------------------+ | 3.142 | +---------------------+ 1 row in set (0.00 sec)
2.不同进制的数字进行转换
ASCII(s):返回字符串s的第一个字符的ASCII码;
BIN(x):返回x的二进制码;
HEX(x):返回x的十六进制码;
OCT(x):返回x的八进制码;
CONV(x,f1,f2):返回f1进制数变成f2进制数;
3.IP地址与数字相互转换的函数
INET_ATON(IP):可以讲IP地址转换为数字表示;IP需要加上引号
INET_NTOA(n):将数字n转换为IP形式
mysql> select INET_ATON('192.168.0.1'); +--------------------------+ | INET_ATON('192.168.0.1') | +--------------------------+ | 3232235521 | +--------------------------+ 1 row in set (0.00 sec) mysql> select INET_NTOA(3232235521); +-----------------------+ | INET_NTOA(3232235521) | +-----------------------+ | 192.168.0.1 | +-----------------------+ 1 row in set (0.00 sec)
4.加锁函数和解锁函数
GET_LOCK(name,time):定义一个名称为那么、持续时间长度为time秒的锁。如果锁定成功,则返回1;如果尝试超时,则返回0;如果遇到错误,返回NULL
RELEASE(name):解除名称为name的锁。如果解锁成功,则返回1;如果尝试超时,返回0,如果解锁失败,则返回NULL;
IS_FREE_LOCK(name):判断是否已使用名为那么的锁。如果使用返回0,否则返回1;
mysql> select GET_LOCK('mysql',10); #创建一把叫mysql的锁 +----------------------+ | GET_LOCK('mysql',10) | +----------------------+ | 1 | +----------------------+ 1 row in set (0.00 sec) mysql> select IS_FREE_LOCK('mysql'); +-----------------------+ | IS_FREE_LOCK('mysql') | +-----------------------+ | 0 | #锁存在 +-----------------------+ 1 row in set (0.00 sec) mysql> select RELEASE_LOCK('mysql'); +-----------------------+ | RELEASE_LOCK('mysql') | +-----------------------+ | 1 | #解锁成功 +-----------------------+ 1 row in set (0.00 sec)
5.改变字符集的函数
CONVERT(s USING cs):将字符串s的字符集变成cs。
mysql> select CHARSET('ABC'); +----------------+ | CHARSET('ABC') | +----------------+ | utf8 | +----------------+ 1 row in set (0.00 sec) mysql> select CHARSET(CONVERT('ABC' using gbk)); +-----------------------------------+ | CHARSET(CONVERT('ABC' using gbk)) | +-----------------------------------+ | gbk | +-----------------------------------+ 1 row in set (0.00 sec)