Mysql之函数
1. 数学函数
1. 绝对值函数ABS()
select ABS(-109),ABS(39);
2. 圆周率函数PI()
select pi();
3. 平方根函数SQRT()
select SQRT(16);
4. 求余函数MOD(x,y)
x被y除后的余数
select mod(1,3), mod(31,8);
5. 获取整数的函数CEIL(x),CEILING(x)
返回不小于x的最小整数值,返回值转化为一个BIGINT
mysql> select ceil(-5.6),ceiling(5.6);
+------------+--------------+
| ceil(-5.6) | ceiling(5.6) |
+------------+--------------+
| -5 | 6 |
+------------+--------------+
6. FLOOR()
返回不大于x的最大整数,返回值转化为一个BIGINT
mysql> select floor(-5.6),floor(5.6);
+-------------+------------+
| floor(-5.6) | floor(5.6) |
+-------------+------------+
| -6 | 5 |
+-------------+------------+
7. 随机数函数
rand() 随机产生一个浮点数，范围在0到1之间
mysql> select rand(),rand(),rand();
+--------------------+--------------------+--------------------+
| rand() | rand() | rand() |
+--------------------+--------------------+--------------------+
| 0.4841434839033927 | 0.9747067540648456 | 0.4211033493476951 |
+--------------------+--------------------+--------------------+
rand(10)
mysql> select rand(10),rand(10),rand(11);
+--------------------+--------------------+-------------------+
| rand(10) | rand(10) | rand(11) |
+--------------------+--------------------+-------------------+
| 0.6570515219653505 | 0.6570515219653505 | 0.907234631392392 |
+--------------------+--------------------+-------------------+
指定x以后，x相同产生的随机数也相同；x不同产生不同的随机数
8. ROUND(x) 返回最接近x的整数，会进行四舍五入
mysql> select round(3.5),round(3.1);
+------------+------------+
| round(3.5) | round(3.1) |
+------------+------------+
| 4 | 3 |
+------------+------------+
9. ROUND(x,y)
返回最接近于x的数，其值保留到小数点后面y位，若y为负值，则将保留x值到小数点左边y位
mysql> select round(3.5,6),round(3.1,-2),round(123,-2),round(123.123,-1),round(29.99,-1);
+--------------+---------------+---------------+-------------------+-----------------+
| round(3.5,6) | round(3.1,-2) | round(123,-2) | round(123.123,-1) | round(29.99,-1) |
+--------------+---------------+---------------+-------------------+-----------------+
| 3.500000 | 0 | 100 | 120 | 30 |
+--------------+---------------+---------------+-------------------+-----------------+
y值为负数时，保留的小数点左边的相应位数直接保存为0
y值为0，则返回整数部分
10. TRUNCATE(x,y)
返回被舍去至小数点后y位的数字x。若y的值为0，则结果不带有小数点或不带有小数部分。若y设为负数，则截去x小数点左起第y位开始后面所有低位的值。
mysql> select truncate(1.91,1),truncate(19.99,-1),truncate(19.19,-1);
+------------------+--------------------+--------------------+
| truncate(1.91,1) | truncate(19.99,-1) | truncate(19.19,-1) |
+------------------+--------------------+--------------------+
| 1.9 | 10 | 10 |
+------------------+--------------------+--------------------+
round(x,y)会进行四舍五入；而truncate(x,y)不会进行四舍五入
11. 符号函数SIGN(x)
返回参数的符号，x的值为负，0，正时，分别对应-1, 0, 1。
12. 幂运算函数
POW(x,y) POWER(x,y)
返回x的y次方的结果
EXP(x)
13. 对数函数LOG(x) LOG10(x)
2. 字符串函数
1. 计算字符串的字符数 CHAR_LENGTH(str)
mysql> select CHAR_LENGTH('yangjianbo');
+---------------------------+
| CHAR_LENGTH('yangjianbo') |
+---------------------------+
| 10 |
+---------------------------+
2. 计算字符串的长度 LENGTH(str)
mysql> select LENGTH('杨建波');
+---------------------+
| LENGTH('杨建波') |
+---------------------+
| 9 |
+---------------------+
1 row in set (0.00 sec)
mysql> select LENGTH('yangjianbo');
+----------------------+
| LENGTH('yangjianbo') |
+----------------------+
| 10 |
+----------------------+
一个数字和一个字母占一个字节，一个汉字占3个字节
3. 合并字符串函数 CONCAT(s1,s2,...) CONCAT_WS(x,s1,s2,...)
mysql> select CONCAT('I am ','liudehua'),CONCAT('I am ',NULL);
+----------------------------+----------------------+
| CONCAT('I am ','liudehua') | CONCAT('I am ',NULL) |
+----------------------------+----------------------+
| I am liudehua | NULL |
+----------------------------+----------------------+
CONCAT()其中一个参数为NULL,结果就是NULL
CONCAT_ws(x,s1,...)
x参数是一个分隔符，使用分隔符后，会忽略NULL的值。
mysql> select CONCAT_ws('-','I am ','liudehua'),CONCAT_WS('*','I am ',NULL,'zhangxueyou');
+-----------------------------------+-------------------------------------------+
| CONCAT_ws('-','I am ','liudehua') | CONCAT_WS('*','I am ',NULL,'zhangxueyou') |
+-----------------------------------+-------------------------------------------+
| I am -liudehua | I am *zhangxueyou |
+-----------------------------------+-------------------------------------------+
4. 替换字符串的函数 INSERT(s1,x,len,s2)
x表示从s1的第几个字符开始
len表示要替换几个字符
mysql> select INSERT('yangjianbo',5,4,'jin');
+--------------------------------+
| INSERT('yangjianbo',5,4,'jin') |
+--------------------------------+
| yangjinbo |
+--------------------------------+
5. 字母大小写转换函数
LOWER(str) 大写转小写
UPPER(str) 小写转大写
6. 截取指定的字符函数
LEFT(str,n) 从左边第几个字符开始截取
RIGHT(str,n) 从右边第几个字符开始截取
7. 替换函数 REPLACE(str,s1,s2) str中s2替换s1
mysql> select replace('yangjianbo','a','b');
+-------------------------------+
| replace('yangjianbo','a','b') |
+-------------------------------+
| ybngjibnbo |
+-------------------------------+
8. 比较字符串大小的函数 STRCMP(s1,s2)
mysql> select STRCMP('abc','ab');
+--------------------+
| STRCMP('abc','ab') |
+--------------------+
| 1 |
+--------------------+
1 row in set (0.00 sec)
mysql> select STRCMP('abc','abcd');
+----------------------+
| STRCMP('abc','abcd') |
+----------------------+
| -1 |
+----------------------+
1 row in set (0.00 sec)
mysql> select STRCMP('abc','abc');
+---------------------+
| STRCMP('abc','abc') |
+---------------------+
| 0 |
+---------------------+
1 row in set (0.00 sec)
9. 获取子串的函数 SUBSTRING(str,n,len)
mysql> select substring('yangjianbo',-5,3);
+------------------------------+
| substring('yangjianbo',-5,3) |
+------------------------------+
| ian |
+------------------------------+
1 row in set (0.00 sec)
mysql> select substring('yangjianbo',-5);
+----------------------------+
| substring('yangjianbo',-5) |
+----------------------------+
| ianbo |
+----------------------------+
1 row in set (0.00 sec)
mysql> select substring('yangjianbo',5,5);
+-----------------------------+
| substring('yangjianbo',5,5) |
+-----------------------------+
| jianb |
+-----------------------------+
1 row in set (0.00 sec)
10. 字符串逆序的函数 REVERSE(str)
mysql> select reverse('abcdefg');
+--------------------+
| reverse('abcdefg') |
+--------------------+
| gfedcba |
+--------------------+
3. 日期和时间函数
1. 获取当前日期的函数
CURDATE() 返回格式：YYYY-MM-DD
CURRENT_DATE() 返回格式：YYYY-MM-DD
CURDATE()+0 返回格式：YYYYMMDD
2. 获取当前时间的函数
CURTIME() 返回格式：HH:MM:SS
CURRENT_TIME() 返回格式：HH:MM:SS
CURDATE()+0 返回格式：HHMMSS
3. 获取当前日期和时间的函数
CURRENT_TIMESTAMP()
LOCALTIME()
NOW()
SYSDATE()
YYYY-MM-DD HH:MM:SS
4. 条件判断函数
1. if
语法： IF(expr,v1,v2) 如果expr为TRUE,则返回v1,否则返回v2
mysql> select if(1>2,"yes","no");
+--------------------+
| if(1>2,"yes","no") |
+--------------------+
| no |
+--------------------+
2. ifnull
语法： IFNULL(v1,v2) 如果v1不为NULL，则返回v1;否则返回v2
mysql> select ifnull(1,2);
+-------------+
| ifnull(1,2) |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
mysql> select ifnull(null,2);
+----------------+
| ifnull(null,2) |
+----------------+
| 2 |
+----------------+
3. case
语法： CASE expr WHEN v1 THEN r1 WHEN v2 THEN r2 ELSE rn END 当expr值等于某个vn,则返回对应的THEN后面的结果，如果都不相等，则返回ELSE后面的结果。
mysql> select case 1 when 1 then "yes" when 2 then "no" else "fuck" end
-> ;
+-----------------------------------------------------------+
| case 1 when 1 then "yes" when 2 then "no" else "fuck" end |
+-----------------------------------------------------------+
| yes |
+-----------------------------------------------------------+
语法： CASE WHEN v1 THEN r1 WHEN v2 THEN r2 ELSE rn END 当某个vn值为TRUE时，返回对应位置THEN后面的结果，如果所有值不为TRUE，则返回ELSE后的rn。
mysql> select case when 1<2 then "yes" when 2 then "no" else "fuck" end;
+-----------------------------------------------------------+
| case when 1<2 then "yes" when 2 then "no" else "fuck" end |
+-----------------------------------------------------------+
| yes |
+-----------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select case when 1<0 then "yes" when 2 then "no" else "fuck" end;
+-----------------------------------------------------------+
| case when 1<0 then "yes" when 2 then "no" else "fuck" end |
+-----------------------------------------------------------+
| no |
+-----------------------------------------------------------+
5. 系统信息函数
1. 获取mysql版本号
select version();
2. 查看当前用户的连接数
select CONNECTION_ID();
3. 查看线程运行情况
show processlist; 只列出前100条
show full processlist; 列出所有线程运行情况
+-----+-------------+-----------+------------+---------+---------+--------------------------------------------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-------------+-----------+------------+---------+---------+--------------------------------------------------------+-----------------------+
| 1 | system user | | NULL | Connect | 2760551 | Waiting for master to send event | NULL |
| 2 | system user | | NULL | Connect | 5 | Slave has read all relay log; waiting for more updates | NULL |
| 235 | root | localhost | zp_product | Query | 0 | starting | show full processlist |
+-----+-------------+-----------+------------+---------+---------+--------------------------------------------------------+-----------------------+
id 用户登录mysql，系统分配的id
user 显示当前用户
host 显示从哪个IP的哪个端口发出的
db 显示连接的哪个数据库
command 显示当前连接执行的命令，一般取值为sleep,query,connect
time 显示这个状态持续的时间，单位为秒
state 显示当前连接的sql语句的状态
info 显示这个sql语句
4. 显示当前使用的数据库
select database(),schema();
5. 获取用户名的函数
select user(),current_user(),system_user();
6. 获取字符串的字符集和排序方式的函数
select charset('abc');
select charset(convert('abc' using latin1));
select collation('abc');
select collation(convert('abc' using latin1));
6. 加密解密函数
1. PASSWORD(str) mysql将password函数加密后的密码保存到用户权限表中
2. MD5(str)
3. ENCODE(str,pswd_str) 使用pswd_str作为密码，加密str
4. DECODE(crypt_str,pswd_str) 使用pswd_str作为密码，解密crypt_str
7. 其它函数
1. 改变字符集的函数
select charset(convert('abc' using latin1));
浙公网安备 33010602011771号