MySQL数据库函数

MySQL函数
一数学函数
1绝对值函数：abs(x)
abs(x)用于返回x的绝对值

2返回圆周率的函数：pi()
pi()返回圆周率值

3平方根函数：sqrt(x)
sqrt(x)返回非负数x的二次方根

4求余函数：mod(x,y)
mod(x,y)返回x被y除后的余数，mod(x,y)对于带有小数部分的数值也起作用，它返回除法运算后的精确余数

5获取整数的函数：ceil(x) 、ceiling(x) 、floor(x)
ceil(x) 用于返回不小于 x 的最小整数值

ceiling(x) 用于返回不小于 x 的最小整数

floor(x) 返回不大于 x 的最大整数值

6获取随机数的函数：rand() 、rand(x)
rand() 用于返回一个随机浮点值，范围在 0 ~ 1 之间

rand(x) 用于返回一个随机浮点值，范围在 0~1 之间，x 被用作种子值，用来产生重复序列

7四舍五入的函数：round(x) 、round (x,y)
round (x) 用于对 x 进行四舍五入

round(x,y) 用于对 x 进行四舍五入，并保留小数点后 y 位

8截取数值的函数：truncate(x,y)
truncate(x,y) 用于对 x 进行截取，结果保留小数点后 y 位

9符号函数：sign(x)
sign(x) 用于返回参数 x 的符号，当x为负数时返回-1，当x为正数时返回1，当x为零时返回0

10幂运算函数：pow(x,y)
pow(x,y) 用于返回x的y次方的结果

11对数运算函数：log(x) log10（x）
log(x) 返回x的自然对数

使用 log10(x) 计算以 10 为基数的对数

12角度与弧度相互转换的函数：radians (x) 、degrees（x）
radians(x) 用于将参数 x 由角度转化为弧度

degrees (x) 用于将参数 x 由弧度转化为角度

13正弦函数和反正弦函数：sin(x) 、asin(x)
sin(x) 用于返回 x 的正弦值，其中x为弧度值

asin(x) 用于返回x的反正弦，即正弦为x的值

14余弦函数和反余弦函数：cos(x) 、acos(x)
cos(x) 用于返回 x 的余弦，其中 x 为弧度值

acos(x) 用于返回 x 的反余弦，即余弦是 x 的值

15正切函数、反正切函数、余切函数：tan(x) 、atan(x) 、cot(x)
tan(x) 返回 x 的正切，其中 x 为给定的弧 度值

atan(x) 用于返回 x 的反正切，即正切为 x 的值

cot(x) 用于返回 x 的余切

二字符串函数
1计算字符串长度的函数：Char_length (str) 、length(str)
Char_length (str) 用于统计 str 的字符个数

length (str) 用于统计 str 的字节长度，使用 utf8 编码字符集时，一个汉字是3个字节，一个数字或字母是1个 字节

2合并字符串的函数：concat(s1,s2,…) 、concat _ws (x,s1,s2,…)
concat (s1,s2,…) 用于合并字符串 s1, s2, … ，如果有一个参数为 NULL，则结果为 NULL

concat _ws(x,s1,s2,…) 以 x 作为分隔符，将 s1, s2, … 合并，如果 分隔符为 NULL ，则结果为 NULL

3 替换字符串的函数：insert( (s1,x,len,s2)
(1) insert(s1,x,len,s2) 用于返回字符串 s1，其子字符串起始于 x 位 置和被字符串 s2 取代的 len 字符
(2) 如果 x 超过字符串长度，则返回值为原始字符串，如果 len 的长度大于其 他字符串的长度则从位置 x 开始替换
(3) 如果任何一个参数为 NULL ，则返回值为 NULL

4转换大小写的函数：lower(str) 、lcase(str) 、upper(str) 、ucase(str)
lower(str) 用于将字符串 str 中的 字母字符全部转换成小写字母

lcase(str) 用于将字符串 str 中的字母字符全部转换成小写字母

upper(str) 用于将 str 中的小写字母转换成大写字母

ucase(str) 用于将 str 中的小写字母转换成大写字母

5获取指定长度的字符串的函数：left(s,n) 、right(s,n)
left(s,n) 用于返回字符串 s 开始的最左边 n 个字符

right(s,n) 用于返回字符串 s 中右边 n 个字符

6填充字符串的函数：lpad(s1,len,s2) 、rpad(s1,len,s2)
lpad(s1,len,s2) 返回字符串 s1 ，其左边由字符串 s2 填充到 len 字符长度，如果 s1 的长度大于 len ，则返回值被缩短至 len 长度

rpad(s1,len,s2) 返回字符串 s1 ，其右边由字符串 s2 填充到 len 字符长度，如果 s1 的长度大于 len ，则返回值 被缩短至 len 长度

7删除空格的函数：ltrim(s) 、rtrim(s) 、trim(s)
ltrim (s) 用于删除字符串 s 左侧的空格

rtrim(s) 用于删除字符串 s 右侧的空格

trim(s) 用于删除字符串 s 两侧的空格

8删除指定字符串的函数：trim(s1 FROM s)
trim(s1 FROM s) 用于删除字符串 s 中两端所有的子字符串 s1 ， 如果没有指定 s1 ，则默认删除字符串 s 两侧的空格

9重复生成字符串的函数：repeat(s,n)
repeat(s,n) 用于重复字符串 s ，n 表示重复多少次

10空格函数：space(n)
space(n) 用于返回 n 个空格

11替换函数：replace(s,s1,s2)
replace(s,s1,s2) 表示使用字符串 s2 替换字符串 s 中所有的字符串 s1

12比较字符串大小的函数：strcmp(s1,s2)
strcmp(s1,s2) 用于比较字符串 s1 和 s2 的大小，若所有字符串相 同则返回 0 ，若第一个字符串大于第二个字符串则返回 1 ，若第一个字符串小于第二个字符串则返回 -1

13获取子字符串的函数：substring(s,n,len) 、mid(s,n,len)
substring (s,n,len) 用于获取指定位置的子字符串

mid (s,n,len) 用于获取指定位置的子字符串

14匹配子字符串开始位置的函数：locate (str1,str) 、position (str1 IN str) 、instr (str, str1)
locate(str1,str) 用于返回字符串 str1 在字符串 str 中的开始位置

position(str1 IN str) 用于返回字符串 str1 在字符串 str 中的开始位置

instr(str, str1) 用于返回子字符串 str1 在字符串 str 中的开始位置

15反转字符串的函数：reverse (s)
reverse(s) 用于将字符串 s 反转

16返回指定位置的字符串的函数：elt(n, s1, s2, s3, …)
elt(n, s1, s2, s3, …) 用于返回第 n 个字符串，如果 n 超出范围则返回 NULL

17返回指定字符串位置的函数：iwld (s, s1, s2, …)
fiwld(s, s1, s2, …) 用于返回字符串 s 在列表 s1, s2, … 中 的位置，如果不存在字符串 s 则返回 0 ，如果字符串 s 是 NULL 也返回 0

18返回子字符串位置的函数：Find_in_set (s1, s2)
Find_in_set(s1, s2) 用于返回字符串 s1 在字符串列表 s2 中的位置

时间和日期函数
1获取当前日期的函数：curdate () 、current_date ()
curdate() 用于获取系统当前日期

current_date() 用于系统获取当前日期

2获取当前时间的函数：curtime () current_time()
curtime() 用于获取系统当前时间

3获取当前日期和时间的函数：Current_timestamp () 、localtime () 、now() 、sysdate()
Current_timestamp() 用于获取系统当前日期和时间

localtime() 用于获取系统当前日期和时间

now() 用于获取系统当前日期和时间

sysdate用于获取系统当前日期和时间

4获取时间戳的函数：unix_timestamp()
Unix_timestamp() 用于获取 UNIX 格式的时间戳

5转换时间戳的函数：from_unixtime()
From_unixtime() 用于将 UNIX 格式的时间戳转换为普通格式的时 间

6获取 UTC 日期的函数：utc_date()
Utc_date() 用于获取当前 UTC (世界标准时间) 日期值

7获取 UTC 时间的函数：Utc_time ()
Utc_time() 用于获取当前 UTC (世界标准时间) 时间值

8获取月份的函数：month (date) 、monthname (date)
month(date) 用于返回 date 对应的月份

monthname(date) 用于返回 date 对应月份的英文全名

9获取星期的函数：dayname (date) 、dayofweek (date) 、weekday (date) 、week(date) 、 weekofyear(date)
dayname(date) 用于返回 date 对应的工作日的英文名称

dayofweek(date) 用于返回 date 对应的一周中的索引，1 表示周日，2 表示周一，… ，7 表示周六

weekday(date) 用于返回日期对应的工作日索引，0 表示周一，1 表示周二，… ，6 表示周日

week(date) 用于计算 date 是一年中的第几周，一年有 53 周

weekofyear(date) 用于计算日期 date 是一年中的第几周，一年有 53 周

10获取天数的函数：dayofyear(date) 、dayofmonth(date)
dayofyear(date) 用于返回 date 是一年中的 第几天，一年有 365 天

dayofmonth用于计算 date 是一个月中的第几天

11获取年份的函数：year(date)
year(date) 返回 date 对应的年份

12获取季度的函数：quarter(date)
quarter(date) 返回 date 对应的一年中的季度值

13获取分钟的函数：minute (time)
minute(time) 返回 time 对应的分钟值

14获取秒钟的函数：second (time)
second(time) 返回 time 对应的秒数

15获取日期的指定值的函数：extract (type FROM date)
extract(type FROM date) 用于获取指定的日期值

16时间和秒钟转换的函数：Time_to_sec (time) 、Sec_to_time (time)
Time_to_sec(time) 用于将 time 转换 为秒钟，公式为 " 小时3600 + 分钟60 + 秒 "

Sec_to_time(time) 用于将秒值转换为时间格式

17计算日期和时间的函数：date_add() 、adddate() 、date_sub() 、subdate() 、daatime() 、 subtime() 、datediff()
Date_add() 用于对日期进行加运算，格式为 DATE_ADD(date, INTERVAL expr type) expr 与 type 的关系

date_sub用于对日期进行减运算，格式为 DATE_SUB(date, INTERVAL expr type) ，expr 与 type 的关系

subdate用于对日期进行减运算，格式为 SUBDATE(date, INTERVAL expr type) ，expr 与 type 的关系

addtime用于对日期进行加运算，格式为 ADDTIME(date, expr)

subtime() 用于对日期进行减运算，格式为 SUBTIME(date, expr)

Datediff用于计算两个日期之间的间隔天数

18将日期和时间格式化的函数：Date_format (date, format) 、Time_format (time, format) 、 get_format(val_type, format_type)
Date_format(date, format) 用于格式化日期，即根据 format 指定的格式 显示 date 值，format 格式

Time_format(time, format) 用于格式化时间，即根据 format 指定的格式显示 time 值，format 格式

get_format我们指定值类型和格式化类型，然后会显示成格式字符串

%d该月日期，数字形式（00…31） %e该月日期，数字形式（0…31） %f微秒（000000…999999） %H以2位数表 示24小时（00…23） %h,%I 以2位数表示12小时（01…12） %i分钟，数字形式（00-59） %j一年中的天数（001- 366） %k以24小时（0-23） %l以12小时（0…12） %M月份名称（january…December） %m月份数字形式 （00…12） %p上午（AM）或下午（PM） %r时间，12小时制（小时hh：分钟mm：秒钟ss后面加AM或PM） %S,%s以2位数形式表示秒（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同时使用 %Y4位数形式表示年份 %y2 位数形式表示年份 %% “%”文字字符

条件判断函数
1 if()
if(expr, v1, v2) 如果表达式 expr 为 TRUE ，则返回值为 v1 ，否则返回 v2

2 ifnull()
ifnull(v1, v2) ，如果 v1 不为 NULL ，则返回值为 v1 ；如果 v1 为 NULL ，则返回值为 v2

3 ) case
case expr WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE rn] END 含义：如果 expr 等于某个 vn ，则返回对应位置 THEN 后面的结果，如果与所有值都不相等，则返回 ELSE 后面的 rn

系统信息函数
1获取 MySQL 版本号的函数：version()
version() 用于获取 MySQL 版本号

2查看当前用户的连接数的ID函数：connection_id()
Connection_id() 用于查看当前用户的连接数

1. Id ：用户登录 MySQL 时，系统分配的连接 id
2. User ：当前连接的用户
3. Host ：显示这个语句是从哪个 IP 的哪个端口上发出的，可以用来追踪出现问题语句的用户
4. db ：显示这个进程目前连接的是哪个数据库
5. Command ：显示当前连接执行的命令，一般取值为休眠(Sleep)、查询(Query)、连接(Connect)
6. Time ：显示这个状态持续的时间，单位是秒
7. State ：显示使用当前连接的 SQL 语句的状态
8. Info ：显示这个 SQL 语句
   

3查看当前使用的数据库的函数：Database () 、schema ()
Database() 用于查看当前使用的数据库

schema() 用于查看当前使用的数据库

4查看当前登录的用户名的函数：user() 、current_user () 、system_user ()
user() 返回当前登录的用户 及主机名

current_user() 用于返回当前登录的用户及主机名

system_user() 用于返回当前登录的用户及主机名

5查看指定字符串的字符集的函数：charset(str)
charset(str) 用于查看字符串 str 的字符集

6查看指定字符串的排列方式的函数：collation(str)
collation(str) 用于查看字符串 str 的字符排列方式

7获取最后一个自动生成的 ID 值得函数：last_insert_id()
Last_insert_id() 用于获取最后一个自动生成的 ID 值

加密解密函数
1加密函数：pasword(str) 、md5 (str) 、encode (str, pswd_str)
password(str) 从明文密码 str 计算并返 回加密后的密码字符串，当参数为 NULL 时，返回 NULL

md5(str) 为字符串 str 算出一个 MD5 128 比特校验和

encode(str, pswd_str) 使用 pswd_str 作为密码，加密 str

2解密函数：decode(crypt_str, pswd_str)
decode(crypt_str, pswd_str) 使用 pswd_str 作为密码，解密加密字 符串 crypt_str

其他函数
1格式化函数：format(x, n)
format(x, n) 将数字 x 格式化，并以四舍五入的方式保留小数点后 n 位，结果 以字符串的形式返回

2不同进制的数字进行转换的函数：conv()
conv() 用于不同进制数之间的转换

3IP 地址与数字互相转换的函数：Inet_aton (expr) 、Inet_ntoa(expr)
Inet_aton(expr) 用于将网络地址转 换为一个代表该地址数值的整数

4加锁函数和解锁函数：Get_lock (str, timeout) 、Release_locak (str) 、 Is_free_lock (str) 、 Is_used_lock (str)
Get_lock(str, timeout) 使用字符串 str 来得到一个锁，持续时间 timeout 秒

1. 若成功得到锁，则返回 1
2. 若操作超时，则返回 0
3. 若发生错误，则返回 NULL
   

Release_locak(str) 用于解开被 GET_LOCK() 获取的，用字符串 str 所命名的锁

1. 若锁被解开，则返回 1
2. 若该线程尚未创建锁，则返回 0
3. 若命名的锁不存在，则返回 NULL
4. 若该锁从未被 GET_LOCK() 的调用获取，或锁已经被提前解开，则该锁不存在
   

Is_free_lock(str) 检查名为 str 的锁是否可以使用

1. 若锁可以使用，则返回 1
2. 若锁正在被使用，则返回 0
3. 若出现错误，则返回 NULL
   

Is_used_lock(str) 用于检查名为 str 的锁是否正在被使用，若被封锁，则返回使用该锁的客户端的连接标识 符，否则返回 NULL

5重复执行指定操作的函数：benchmark (count, expr)
benchmark(count, expr) 用于重复 count 次执行表 达式 expr

1. 可以用于计算 MySQL 处理表达式的速度
2. 可以在 MySQL 客户端内部报告语句执行的时间
   

6改变字符集的函数：convert (… USING …)
convert (… USING …) 用于改变字符串的默认字符集

7改变数据类型的函数：cast (x, AS type) 、conver (x, type)
cast(x, AS type) 用于将一个数据类型的值转换 为另一个数据类型的值 CONVERT(x, type) 用于将一个数据类型的值转换为另一个数据类型的值

convert(x, type) 用于将一个数据类型的值转换为另一个数据类型的值