10、MYSQL函数
Mysql函数
运算函数
1、绝对值函数
ABS(x)：用于给x求绝对值，一个负数的绝对值等于它的相反数，一个正数的绝对值等于它本身
例：
mysql> SELECT ABS(10.5);
+-----------+
| ABS(10.5) |
+-----------+
| 10.5 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT ABS(-10.5);
+------------+
| ABS(-10.5) |
+------------+
| 10.5 |
+------------+
1 row in set (0.00 sec)2、圆周率函数
例：
mysql> SELECT PI();
+----------+
| PI() |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)3、平方根函数
SORT(x)：计算括号内容数值的开平方值，换句话说就是求几的平方等于x
例：
mysql> SELECT SQRT(9); /*3^2=9*/，不信的可以去尝试2^2是不是等于4，将4开平方
+---------+
| SQRT(9) |
+---------+
| 3 |
+---------+
1 row in set (0.00 sec)4、取整函数
具体用法以及详细参考==索引==文章的==索引函数==部分
CEIL(x)：取最接近x且不小于x的整数
CEILING(x)：同上
FLOOR(x)：取最接近x且不大于x的整数
5、随机数函数
RAND(x)：返回0-1之间的随机小数，大于0小于1
详情参考==索引==文章的==索引函数==
6、四舍五入函数
ROUND(x)：对x进行四舍五入，结果保留整数
mysql> SELECT ROUND(10.1);
+-------------+
| ROUND(10.1) |
+-------------+
| 10 |
+-------------+
1 row in set (0.00 sec)
mysql> SELECT ROUND(10.9);
+-------------+
| ROUND(10.9) |
+-------------+
| 11 |
+-------------+
1 row in set (0.00 sec)ROUND(x,y)：对x进行四舍五入，结果保留y位小数，四舍五入是对于第y位以及后一位小数处开始
mysql> SELECT ROUND(10.9,2); /*小数点只有一位，保留2位，所以0.9不会被四舍五入*/
+---------------+
| ROUND(10.9,2) |
+---------------+
| 10.90 |
+---------------+
1 row in set (0.00 sec)
mysql> SELECT ROUND(10.946,2); /*小数点有三位，保留2位，使用第三位进行四舍五入*/
+-----------------+
| ROUND(10.946,2) |
+-----------------+
| 10.95 |
+-----------------+
1 row in set (0.00 sec)7、截取数值的函数
TRUNCATE(x,y)：x为取值数，y为保留数位，y为0时，保留个位数，小于0时表示保留的小数位数，大于0时表示保留的整数位数
例：
mysql> SELECT TRUNCATE(123.456,2);
+---------------------+
| TRUNCATE(123.456,2) |
+---------------------+
| 123.45 |
+---------------------+
mysql> SELECT TRUNCATE(123.456,0);
+---------------------+
| TRUNCATE(123.456,0) |
+---------------------+
| 123 |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT TRUNCATE(123.456,-2); /*这里可能不太好理解，-2表示保留到百位，后面0补齐，所以是100*/
/*如果将y改为-1，结果是120*/
+----------------------+
| TRUNCATE(123.456,-2) |
+----------------------+
| 100 |
+----------------------+
1 row in set (0.00 sec)8、符号函数
SIGN(x)：如果x大于0，返回1，如果小于0，返回-1，等于0返回0
例：
mysql> SELECT SIGN(10),SIGN(0),SIGN(-5);
+----------+---------+----------+
| SIGN(10) | SIGN(0) | SIGN(-5) |
+----------+---------+----------+
| 1 | 0 | -1 |
+----------+---------+----------+9、幂运算函数
POW(x,y)：对于x进行幂运算，x的y次幂，也就是计算x的y次方
POWER(x,y)：同上
EXP(y)：计算e（自然对数的底数）的y次幂，e是自然对数的底数，这个函数是计算e的y次方，e的值为2.71开头的无限不循环小数，以下例子算的是2.71…的3次方，经过实地计算验证，确实没错
例：
mysql> SELECT POW(5,2),POWER(3,2),EXP(3);
+----------+------------+--------------------+
| POW(5,2) | POWER(3,2) | EXP(3) |
+----------+------------+--------------------+
| 25 | 9 | 20.085536923187668 |
+----------+------------+--------------------+10、对数函数
LOG(x)：表示以e为底x的对数
x本身就是e的几次方的一个数，这个函数就是计算x是e的几次方
LOG10(x)：表示以10为底x的对数
计算x是10的几次方
例：
mysql> SELECT LOG(20.085536923187668),LOG10(100);
+-------------------------+------------+
| LOG(20.085536923187668) | LOG10(100) |
+-------------------------+------------+
| 3 | 2 |
+-------------------------+------------+字符串函数
11、计算字符串长度的函数
CHAR_LENGTH(s)：计算字符个数，s为所要计算的字符串或者字段
LENGTH(s)：计算字符串字节长度，s为所要计算的字符串或者字段
例：
mysql> SELECT CHAR_LENGTH('nihao'),LENGTH('你好'); /*nihao，一共5个字符，你好，一共占用6字节*/
+----------------------+------------------+
| CHAR_LENGTH('nihao') | LENGTH('你好') |
+----------------------+------------------+
| 5 | 6 |
+----------------------+------------------+
1 row in set (0.00 sec)12、连接函数
CONCAT(s1,s2…)：将连接函数中的字符串合并显示
CONCAT_WS(x,s1,s2…)：将x作为分隔符把s1，s2合并进行显示
例：
mysql> SELECT CONCAT('My','sql','5.5'),CONCAT_WS('-','Mysql','5','5');
/*实际应用中将字符串换成字段名即可*/
+--------------------------+--------------------------------+
| CONCAT('My','sql','5.5') | CONCAT_WS('-','Mysql','5','5') |
+--------------------------+--------------------------------+
| Mysql5.5 | Mysql-5-5 |
+--------------------------+--------------------------------+13、空格函数
SPACE(n)：生成n个空格
感觉无意义的一个函数，以后碰到在更新吧
14、替换字符串的函数
INSERT(s1,x,length,s2)：返回字符串s1，从x的位置进行替换使用s2替换length
如果x超过了字符串长度，返回原始字符串，如果length长度大于字符串长度，从x开始替换一直到s2结尾，如果参数中有NULL，返回NULL
mysql> SELECT INSERT('aaa',2,4,'bbb')a,INSERT('aaa',100,4,'bbb')b,INSERT('aaa',3,100,'bbb')c;
+------+------+-------+
| a | b | c |
+------+------+-------+
| abbb | aaa | aabbb |
+------+------+-------+
1 row in set (0.00 sec)
INSERT('aaa',2,4,'bbb')a
/*字符串aaa，从第二位开始替换bbb，因为length长度大于字符串长度，所以使用bbb从第二位替换到结尾*/
INSERT('aaa',100,4,'bbb')b
/*字符串aaa，从第一百位开始替换bbb，因为替换起始位置x大于字符串长度，所以返回原值*/
INSERT('aaa',3,100,'bbb')c
/*字符串aaa，从第三位开始替换bbb，因为替换长度length大于字符串长度，从第三位开始替换至结尾*/15、获取指定长度的字符串的函数
LEFT(s,n)：获取字符串s左侧的n个字符
RIGHT(s,n)：获取字符串s右侧的n个字符
mysql> SELECT LEFT('mupei',3),RIGHT('mupeifeiyi',5);
+-----------------+-----------------------+
| LEFT('mupei',3) | RIGHT('mupeifeiyi',5) |
+-----------------+-----------------------+
| mup | feiyi |
+-----------------+-----------------------+16、补充字符串函数
LPAD(s1,len,s2)：返回字符串s1，在s1左侧使用s2补充到len长度
RPAD(s1,len,s2)：返回字符串s1，在s1右侧使用s2补充到len长度
mysql> CREATE TABLE user(id BIGINT,name VARCHAR(30));
mysql> INSERT INTO user VALUES
(100000001,'a'),
(100000002,'b'),
(101000001,'c'),
(101000002,'d'),
(100000003,'e');
mysql> SELECT RIGHT(id,6),name FROM user WHERE id LIKE '100%';
/*查看100开头的id号的后6位*/
+-------------+------+
| RIGHT(id,6) | name |
+-------------+------+
| 000001 | a |
| 000002 | b |
| 000003 | e |
+-------------+------+
mysql> SELECT LPAD('hello',4,'?'),LPAD('hello',10,'?');
将hello补充到4个字节长度，因为hello有五个，要变成4个必须少一个，使用少掉了o，但是可以不使用？也能补充到4个字节长度
将hello补充到10个字节长度，因为hello有10个，要变成10就还需要补充5个？号
+---------------------+----------------------+
| LPAD('hello',4,'?') | LPAD('hello',10,'?') |
+---------------------+----------------------+
| hell | ?????hello |
+---------------------+----------------------+
mysql> UPDATE user SET id=LPAD(id,12,500) WHERE id LIKE '100%';
mysql> UPDATE user SET id=LPAD(id,12,400) WHERE id LIKE '101%';
为不同开头的id添加不同的数字头
mysql> SELECT * FROM user;
+--------------+------+
| id | name |
+--------------+------+
| 500100000001 | a |
| 500100000002 | b |
| 400101000001 | c |
| 400101000002 | d |
| 500100000003 | e |
+--------------+------+17、删除空格的函数
LTRIM(s)：删除字符串s左边空格
RTRIM(s)：删除字符串s右边的空格
TRIM(s)：删除s两边的空格
mysql> SELECT LTRIM(' abc ')a;
+--------+
| a |
+--------+
| abc |
+--------+
mysql> SELECT RTRIM(' abc ')a;
+-------+
| a |
+-------+
| abc |
+-------+
mysql> SELECT TRIM(' abc ')a;
+------+
| a |
+------+
| abc |
+------+18、删除指定字符串的函数
TRIM(s1 FROM s)：删除字符串s两侧的s1
mysql> SELECT TRIM('xy' FROM 'xyzabczyx');
+-----------------------------+
| TRIM('xy' FROM 'xyzabczyx') |
+-----------------------------+
| zabczyx |
+-----------------------------+19、重复生成字符串的函数
REPEAT(s,n)：将s字符串重复生成n遍
mysql> SELECT REPEAT('cyj',5);
+-----------------+
| REPEAT('cyj',5) |
+-----------------+
| cyjcyjcyjcyjcyj |
+-----------------+
实例：
mysql> SELECT * FROM user;
+--------------+------+
| id | name |
+--------------+------+
| 500100000001 | a |
| 500100000002 | b |
| 400101000001 | c |
| 400101000002 | d |
| 500100000003 | e |
+--------------+------+
mysql> INSERT INTO user VALUES (REPEAT(1,10),'z');
mysql> SELECT * FROM user;
+--------------+------+
| id | name |
+--------------+------+
| 500100000001 | a |
| 500100000002 | b |
| 400101000001 | c |
| 400101000002 | d |
| 500100000003 | e |
| 1111111111 | z |
+--------------+------+20、替换函数
REPLACE(s,s1,s2)：将字符串s中的所有的s1使用s2替换
mysql> SELECT REPLACE('mupeioeiyi','o','f');
+-------------------------------+
| REPLACE('mupeioeiyi','o','f') |
+-------------------------------+
| mupeifeiyi |
+-------------------------------+
实例 ：
mysql> CREATE TABLE yeji(id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,customers VARCHAR(30));
mysql> INSERT INTO yeji(name,customers) VALUES
('zs','a'),('ls','b'),('zs','c'),('ww','d'),('zs','e'),('ls','f');
mysql> SELECT * FROM yeji;
+----+------+-----------+
| id | name | customers |
+----+------+-----------+
| 1 | zs | a |
| 2 | ls | b |
| 3 | zs | c |
| 4 | ww | d |
| 5 | zs | e |
| 6 | ls | f |
+----+------+-----------+
mysql> UPDATE yeji SET name=REPLACE(name,'zs','ww');
/*将表中的所有zs替换为ww*/
mysql> SELECT * FROM yeji;
+----+------+-----------+
| id | name | customers |
+----+------+-----------+
| 1 | ww | a |
| 2 | ls | b |
| 3 | ww | c |
| 4 | ww | d |
| 5 | ww | e |
| 6 | ls | f |
+----+------+-----------+
mysql> UPDATE yeji SET name=REPLACE(name,'ls','zs') WHERE id=2;
/*将yeji表中id为2的name中的ls替换为zs*/
mysql> SELECT * FROM yeji;
+----+------+-----------+
| id | name | customers |
+----+------+-----------+
| 1 | ww | a |
| 2 | zs | b |
| 3 | ww | c |
| 4 | ww | d |
| 5 | ww | e |
| 6 | ls | f |
+----+------+-----------+21、比较字符串大小的函数
STRCMP(s1,s2)：比较s1和s2的大小，如果s1大，返回1，如果s2大，返回-1，如果相等返回0
mysql> SELECT STRCMP('cyj','CYJ'),STRCMP('pjf2','pjf1'),STRCMP('pjf10','pjf2');
不区分大小写，cyj=CYJ，pjf2和pjf1对位比较2比1大，pjf10和pjf2对位比较1对着的2，使用pjf2大
+---------------------+-----------------------+------------------------+
| STRCMP('cyj','CYJ') | STRCMP('pjf2','pjf1') | STRCMP('pjf10','pjf2') |
+---------------------+-----------------------+------------------------+
| 0 | 1 | -1 |
+---------------------+-----------------------+------------------------+
实际应用
mysql> CREATE TABLE user1(id INT);
mysql> CREATE TABLE user2(id INT);
mysql> CREATE TABLE user10(id INT);
mysql> show tables; /*可以看到表名和库名的排列也是这样比较大小的排序*/
+-----------------+
| Tables_in_user1 |
+-----------------+
| user |
| user1 |
| user10 |
| user2 |
| yeji |
+-----------------+
22、获取子字符串函数
SUBSTRING(s,n,len)：用于获取指定位置的字符串，从n位开始获取s的len处的字符串
MID(s,n,len)：同上
如果省略len将会从n为获取直到字符串结尾，如果n为附属将倒序进行获取
mysql> SELECT SUBSTRING('mupeifeiyi',4)a, /*获取mupeifeiyi中从第四位开始到结尾的字符串*/
-> SUBSTRING('mupeifeiyi',2,3)b, /*获取mupeifeiyi中从第二位开始，一共取3位字符串*/
-> SUBSTRING('mupeifeiyi',-3)c, /*获取mupeifeiyi中从倒序第三位开始到结尾*/
-> SUBSTRING('mupeifeiyi',-3,2)d; /*获取mupeifeiyi中从倒序第三位，一共取2位字符串*/
+---------+------+------+------+
| a | b | c | d |
+---------+------+------+------+
| eifeiyi | upe | iyi | iy |
+---------+------+------+------+23、匹配子字符串开始位置的函数
LOCATE(s1,s)：s1在s中的第几位开始
POSITION(s1 IN s)：同上
INSTR(s,s1)：同上
mysql> SELECT LOCATE('mu','mupeifeiyi'), /*mu在mupeifeiyi中的第几位开始的*/
POSITION('ei' IN 'mupeifeiyi'), /*ei在mupeifeiyi中的第几位开始的*/
INSTR('mupeifeiyi','p'); /*p在mupeifeiyi中的第几位开始*/
+---------------------------+--------------------------------+-------------------------+
| LOCATE('mu','mupeifeiyi') | POSITION('ei' IN 'mupeifeiyi') | INSTR('mupeifeiyi','p') |
+---------------------------+--------------------------------+-------------------------+
| 1 | 4 | 3 |
+---------------------------+--------------------------------+-------------------------+24、反转字符串函数
REVERSE(s)：将字符串s进行反转，即头变尾，尾变头
mysql> SELECT REVERSE('mupeifeiyi');
+-----------------------+
| REVERSE('mupeifeiyi') |
+-----------------------+
| iyiefiepum |
+-----------------------+25、返回指定未知的字符串的函数
ELT(n,s1,s2,…)：用于返回第n个字符串
mysql> SELECT ELT(5,'a','b','c','d','e','f');
+--------------------------------+
| ELT(5,'a','b','c','d','e','f') |
+--------------------------------+
| e |
+--------------------------------+26、返回指定字符串位置的函数
FIELD(s,s1,s2,…)：
mysql> SELECT FIELD('hi','hello','world','mysql','hi','cyj');
/*hi在后面几个字符串的位置*/
+------------------------------------------------+
| FIELD('hi','hello','world','mysql','hi','cyj') |
+------------------------------------------------+
| 4 |
+------------------------------------------------+27、返回子字符串位置的函数
FIND_IN_SET(s1,s2)：
mysql> SELECT FIND_IN_SET('chang','tiao,chang,rap,lanqiu');
/*chang位于tiao,chang,rap,lanqiu中的第几个*/
+----------------------------------------------+
| FIND_IN_SET('chang','tiao,chang,rap,lanqiu') |
+----------------------------------------------+
| 2 |
+----------------------------------------------+日期函数
28、获取当前日期的函数
CURDATE(),CURRENT_DATE()
29、获取当前时间的函数
CURTIME(),CURRENT_TIME()
mysql> SELECT CURDATE(),CURRENT_DATE(),CURTIME(),CURRENT_TIME();
+------------+----------------+-----------+----------------+
| CURDATE() | CURRENT_DATE() | CURTIME() | CURRENT_TIME() |
+------------+----------------+-----------+----------------+
| 2019-12-09 | 2019-12-09 | 15:05:29 | 15:05:29 |
+------------+----------------+-----------+----------------+30、显示当前日期或者时间的函数
DATE(date) TIME(time)
mysql> SELECT DATE(NOW()),TIME(NOW());
+-------------+-------------+
| DATE(NOW()) | TIME(NOW()) |
+-------------+-------------+
| 2019-12-09 | 15:06:59 |
+-------------+-------------+31、获取当前日期和时间的函数
CURRENT_TIMESTAMP()
LOCALTIME()
NOW()
SYSDATE()
mysql> SELECT CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE();
+---------------------+---------------------+---------------------+---------------------+
| CURRENT_TIMESTAMP() | LOCALTIME() | NOW() | SYSDATE() |
+---------------------+---------------------+---------------------+---------------------+
| 2019-12-09 15:09:11 | 2019-12-09 15:09:11 | 2019-12-09 15:09:11 | 2019-12-09 15:09:11 |
+---------------------+---------------------+---------------------+---------------------+32、获取时间戳的函数
UNIX_TIMESTAMP()：从1970年01月01日08（中国时区）点至今的秒数
mysql> SELECT UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
| 1575875662 |
+------------------+
mysql> SELECT UNIX_TIMESTAMP(20191010010101);
+--------------------------------+
| UNIX_TIMESTAMP(20191010010101) |
+--------------------------------+
| 1570640461 |
+--------------------------------+
mysql> SELECT UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(19960723);
+------------------------------------------------+
| UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(19960723) |
+------------------------------------------------+
| 737826403 |
+------------------------------------------------+33、转换时间戳的函数
FROM_UNIXTIME()：可以将以上获取到的时间戳转换为时间
mysql> SELECT FROM_UNIXTIME(1575820800-737826403);
+-------------------------------------+
| FROM_UNIXTIME(1575820800-737826403) |
+-------------------------------------+
| 1996-07-22 08:13:17 |
+-------------------------------------+
34、获取UTC日期和时间的函数（UTC世界标准时间）
UTC_DATE
UTC_TIME
mysql> SELECT UTC_DATE(),UTC_TIME();
/*英国子午线时间*/
+------------+------------+
| UTC_DATE() | UTC_TIME() |
+------------+------------+
| 2019-12-09 | 07:53:32 |
+------------+------------+35、获取月份的函数
MONTH(date)： 获取月份对应的序号
MONTHNAME(date)： 获取对应月份的英文名
mysql> SELECT MONTH(NOW()),MONTHNAME(NOW());
+--------------+------------------+
| MONTH(NOW()) | MONTHNAME(NOW()) |
+--------------+------------------+
| 12 | December |
+--------------+------------------+36、获取星期的函数
DAYNAME(date)：返回日期对应的周几的英文名
mysql> SELECT DAYNAME(NOW());
+----------------+
| DAYNAME(NOW()) |
+----------------+
| Monday |
+----------------+
mysql> SELECT DAYNAME(20191211);
+-------------------+
| DAYNAME(20191211) |
+-------------------+
| Wednesday |
+-------------------+DAYOFWEEK(date)：返回日期的这天是一周的第几天（按照周日为第一天算）
mysql> SELECT DAYOFWEEK(20191211);
+---------------------+
| DAYOFWEEK(20191211) |
+---------------------+
| 4 |
+---------------------+WEEKDAY(date)：返回日期对应的一周中的位置，按照周一为第一天，用0表示，以此类推
mysql> SELECT WEEKDAY(20191209);
+-------------------+
| WEEKDAY(20191209) |
+-------------------+
| 0 |
+-------------------+WEEK(date)：返回日期在一年中是第多少周，从0记为第一周
mysql> SELECT WEEK(20190101);
+----------------+
| WEEK(20190101) |
+----------------+
| 0 |
+----------------+
mysql> SELECT WEEK(20191231);
+----------------+
| WEEK(20191231) |
+----------------+
| 52 |
+----------------+WEEKOFYEAR(date)：同上，从1即为第一周
mysql> SELECT WEEKOFYEAR(20190110);
+----------------------+
| WEEKOFYEAR(20190110) |
+----------------------+
| 2 |
+----------------------+37、获取天数的函数
DAYOFYEAR()：返回日期是一年中的第几天
mysql> SELECT DAYOFYEAR(NOW());
+------------------+
| DAYOFYEAR(NOW()) |
+------------------+
| 343 |
+------------------+DAYOFMONTH(date)：返回指定日期在改月中的第几天
mysql> SELECT DAYOFMONTH(NOW());
+-------------------+
| DAYOFMONTH(NOW()) |
+-------------------+
| 9 |
+-------------------+38、获取年份的函数
YEAR(date)：返回指定日期的年份
mysql> SELECT YEAR(19960723);
+----------------+
| YEAR(19960723) |
+----------------+
| 1996 |
+----------------+39、获取季度的函数
QUARTER(date)：返回指定日期所在一年中的第几个季度
mysql> SELECT QUARTER(20190723);
+-------------------+
| QUARTER(20190723) |
+-------------------+
| 3 |
+-------------------+40、获取小时，分钟，秒钟的函数
HOUR(time)：
MINUTE(time)：
SECOND(time)：
mysql> SELECT HOUR(NOW()),MINUTE(NOW()),SECOND(NOW());
+-------------+---------------+---------------+
| HOUR(NOW()) | MINUTE(NOW()) | SECOND(NOW()) |
+-------------+---------------+---------------+
| 16 | 18 | 54 |
+-------------+---------------+---------------+41、获取日期的指定值的函数
EXTRACT(type from date)：
例：
ysql> SELECT EXTRACT(MONTH FROM NOW())a,
EXTRACT(DAY FROM NOW())b,
EXTRACT(YEAR FROM NOW())c;
------+------+------+
| a | b | c |
+------+------+------+
| 12 | 10 | 2019 |
+------+------+------+
1 row in set (0.00 sec)42、时间和秒钟互相转换的函数
TIME_TO_SEC(time)：将时间转换为秒数
SEC_TO_TIME(time)：将秒数转换为时间
ysql> SELECT TIME_TO_SEC(NOW());
+--------------------+
| TIME_TO_SEC(NOW()) |
+--------------------+
| 49105 |
+--------------------+
ysql> SELECT TIME_TO_SEC(240000); /*24个小时的秒数*/
+---------------------+
| TIME_TO_SEC(240000) |
+---------------------+
| 86400 |
+---------------------+ysql> SELECT SEC_TO_TIME (54321); /*一天中的54321秒是几时*/
+---------------------+
| SEC_TO_TIME (54321) |
+---------------------+
| 15:05:21 |
+---------------------+43、对日期和时间进行操作的函数
DATE_ADD(date,INTERVAL expr type)：对日期进行加运算，date要准备进行运算的日期，INTERNAL，固定值，可以理解为加，expr，要加的数值，type，要加的数值的单位
ADDDATE(同上)：同上
mysql> SELECT DATE_ADD('2019-12-31 23:59:59',INTERVAL 1 SECOND);
/*2019-12-31 23:59:59加1秒*/
+---------------------------------------------------+
| DATE_ADD('2019-12-31 23:59:59',INTERVAL 1 SECOND) |
+---------------------------------------------------+
| 2020-01-01 00:00:00 |
+---------------------------------------------------+
Mysql> SELECT DATE_ADD('2019-12-31 23:59:59',INTERVAL '1:1:1' HOUR_SECOND);
/*2019-12-31 23:59:59加1时1分1秒*/
+--------------------------------------------------------------+
| DATE_ADD('2019-12-31 23:59:59',INTERVAL '1:1:1' HOUR_SECOND) |
+--------------------------------------------------------------+
| 2020-01-01 01:01:00 |
+--------------------------------------------------------------+
mysql> SELECT DATE_ADD('2019-12-31 23:59:59',INTERVAL '-1:1:1' HOUR_SECOND);
/*2019-12-31 23:59:59减1时1分1秒*/
+---------------------------------------------------------------+
| DATE_ADD('2019-12-31 23:59:59',INTERVAL '-1:1:1' HOUR_SECOND) |
+---------------------------------------------------------------+
| 2019-12-31 22:58:58 |
+---------------------------------------------------------------+DATE_SUB(date,INTERVAL expr type)：对日期进行减运算
SUBDATE(同上)：同上
ADDTIME(date,expr)：对时间进行加运算
mysql> SELECT ADDTIME(NOW(),'1:1:1');
/*将当前时间加1时1分1秒*/
+------------------------+
| ADDTIME(NOW(),'1:1:1') |
+------------------------+
| 2019-12-10 14:51:53 |
+------------------------+
mysql> SELECT ADDTIME(NOW(),'1 1:1');
/*当前时间加一天，1时1分*/
+------------------------+
| ADDTIME(NOW(),'1 1:1') |
+------------------------+
| 2019-12-11 14:52:43 |
+------------------------+SUBTIME(同上)：对时间进行减运算
DATEDIFF(date1,date2)：计算两个日期之间的间隔
如果date1大返回正值，如果date2大，返回负值，不影响两个日期之间的间隔判断
mysql> SELECT DATEDIFF('2020-01-01',NOW());
/*现在距离20年元旦还有22天*/
+------------------------------+
| DATEDIFF('2020-01-01',NOW()) |
+------------------------------+
| 22 |
+------------------------------+44、对日期或者时间进行格式化
DATE_FORMAT(date,format)：将指定的日期进行格式化，date为指定的日期，format为日期格式化后的单位
mysql> SELECT DATE_FORMAT(NOW(),'%W %M %Y');
+-------------------------------+
| DATE_FORMAT(NOW(),'%W %M %Y') |
+-------------------------------+
| Tuesday December 2019 |
+-------------------------------+%W：工作日对应的英文名
%w：工作日对应的序号
%M：月份对应的英文名
%m：月份对应的序号
%Y：年份（四位）
%y：年份（两位）
TIME_FORMAT(date,format)：将指定的时间进行格式化
mysql> SELECT TIME_FORMAT('16:00:00','%H %k %I');
+------------------------------------+
| TIME_FORMAT('16:00:00','%H %k %I') |
+------------------------------------+
| 16 16 04 |
+------------------------------------+
mysql> SELECT TIME_FORMAT('08:00:00','%H %k %I');
+------------------------------------+
| TIME_FORMAT('08:00:00','%H %k %I') |
+------------------------------------+
| 08 8 08 |
+------------------------------------+
%H：以两位数形式显示24小时制
%k：以一位数（不满10）形式显示24小时制
%I：以两位数形式显示12小时制
GET_FORMAT(value_type,format_type)：将数据格式化成想要的类型
mysql> SELECT DATE_FORMAT(NOW(),GET_FORMAT(date,'USA'));
/*以美国的显示日期格式显示当前日期*/
+-------------------------------------------+
| DATE_FORMAT(NOW(),GET_FORMAT(date,'USA')) |
+-------------------------------------------+
| 12.10.2019 |
+-------------------------------------------+
mysql> SELECT DATE_FORMAT(NOW(),GET_FORMAT(date,'INTERNAL'));
/*国际时间格式*/
+------------------------------------------------+
| DATE_FORMAT(NOW(),GET_FORMAT(date,'INTERNAL')) |
+------------------------------------------------+
| 20191210 |
+------------------------------------------------+
mysql> SELECT DATE_FORMAT(NOW(),GET_FORMAT(date,'JIS'));
/*日本日期格式*/
+-------------------------------------------+
| DATE_FORMAT(NOW(),GET_FORMAT(date,'JIS')) |
+-------------------------------------------+
| 2019-12-10 |
+-------------------------------------------+
mysql> SELECT DATE_FORMAT(NOW(),GET_FORMAT(date,'ISO'));
/*国际标准*/
+-------------------------------------------+
| DATE_FORMAT(NOW(),GET_FORMAT(date,'ISO')) |
+-------------------------------------------+
| 2019-12-10 |
+-------------------------------------------+
判断函数
45、条件判断函数
IF(expr,v1,v2)：如果expr为true返回v1，否则返回v2
mysql> SELECT IF(1+1>2,'OK','zz');
+---------------------+
| IF(1+1>2,'OK','zz') |
+---------------------+
| zz |
+---------------------+结果和v1，v2中的数据没有直接关系
46、判定是否为空值
IFNULL(v1，v2)：如果v1不为NULL，返回v1，如果为空，返回v2
mysql> SELECT IFNULL(NULL,1);
+----------------+
| IFNULL(NULL,1) |
+----------------+
| 1 |
+----------------+
mysql> SELECT IFNULL(10,1);
+--------------+
| IFNULL(10,1) |
+--------------+
| 10 |
+--------------+47、CASE
CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2….] [ELSE rn] END
对于expr的结果进行判断，如果符合v1，返回r1，如果符合v2，返回r2，如果都不满足返回rn
mysql> SELECT CASE 1+1 WHEN 1 THEN 'ZZ' WHEN 2 THEN 'RIGHT' ELSE 'error' END A;
+-------+
| A |
+-------+
| RIGHT |
+-------+实际应用
mysql> CREATE TABLE class(
-> id INT PRIMARY KEY,
-> name VARCHAR(30) NOT NULL,
-> sex CHAR(4));
mysql> INSERT INTO class VALUES(1,'ljj',3),(2,'ckx',0),(3,'bg',1);
mysql> SELECT * FROM class;
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | ljj | 3 |
| 2 | ckx | 0 |
| 3 | bg | 1 |
+----+------+------+
/*case*/
mysql> SELECT CASE sex WHEN 1 THEN '男' WHEN 0 THEN '女' ELSE '其它' END FROM class;
+----------------------------------------------------------------+
| CASE sex WHEN 1 THEN '男' WHEN 0 THEN '女' ELSE '其它' END |
+----------------------------------------------------------------+
| 其它 |
| 女 |
| 男 |
+----------------------------------------------------------------+
mysql> SELECT *,CASE sex WHEN 1 THEN '男' WHEN 0 THEN '女' ELSE '其它' END as sex FROM class;
+----+------+------+--------+
| id | name | sex | sex |
+----+------+------+--------+
| 1 | ljj | 3 | 其它 |
| 2 | ckx | 0 | 女 |
| 3 | bg | 1 | 男 |
+----+------+------+--------+系统信息函数
48、系统版本号函数
VERSION()
mysql> SELECT VERSION(); /*MYSQL版本号*/
+-----------+
| VERSION() |
+-----------+
| 5.6.33 |
+-----------+49、查看当前用户连接ID的函数
CONNECTION_ID()：
mysql> SELECT CONNECTION_ID(); /*表示连接mysql的次数，做了主从复制数值会更大,重启服务会归1*/
+-----------------+
| CONNECTION_ID() |
+-----------------+
| 8 |
+-----------------+50、查看用户连接信息的函数
SHOW PROCESSLIST：
mysql> show processlist;
+----+------+-----------+--------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+--------+---------+------+-------+------------------+
| 1 | root | localhost | mysql1 | Sleep | 156 | | NULL |
| 2 | root | localhost | mysql | Query | 0 | init | show processlist |
+----+------+-----------+--------+---------+------+-------+------------------+
此处的id为connection_id查看到的登录次数
User：登录用户
Host：登录主机
db：正在使用的数据库
conmmand：对数据库的描述
time：登录到的时间，单位s
state：状态
info：使用的命令
使用kill加id号可以将用户的数据库连接状态断掉51、查看当前使用的数据库的函数
DATABASE()
SCHEMA()
mysql> SELECT DATABASE(),SCHEMA();
+------------+----------+
| DATABASE() | SCHEMA() |
+------------+----------+
| mysql1 | mysql1 |
+------------+----------+
mysql> USE test ;
mysql> SELECT DATABASE(),SCHEMA();
+------------+----------+
| DATABASE() | SCHEMA() |
+------------+----------+
| test | test |
+------------+----------+
52、查看当前用户名的函数
USER()
CURRENT_USER()
SYSTEM_USER()
mysql> SELECT USER(),CURRENT_USER(),SYSTEM_USER();
+----------------+----------------+----------------+
| USER() | CURRENT_USER() | SYSTEM_USER() |
+----------------+----------------+----------------+
| root@localhost | root@localhost | root@localhost |
+----------------+----------------+----------------+53、查看字符集的函数
CHARSET()：
54、查看排序规则的函数
COLLATION(str)：
mysql> SELECT CHARSET('abc'),COLLATION('abc');
+----------------+------------------+
| CHARSET('abc') | COLLATION('abc') |
+----------------+------------------+
| utf8 | utf8_general_ci |
+----------------+------------------+关于密码和IP
55、加密解密函数
PASSWORD(str)：将str进行hash加密
mysql> SELECT PASSWORD('123.com');
+-------------------------------------------+
| PASSWORD('123.com') |
+-------------------------------------------+
| *AC241830FFDDC8943AB31CBD47D758E79F7953EA |
+-------------------------------------------+MD5(str)：将str进行md5进行加密，变成32位十六进制字符串
mysql> SELECT MD5('123.com');
+----------------------------------+
| MD5('123.com') |
+----------------------------------+
| cbff36039c3d0212b3e34c23dcde1456 |
+----------------------------------+ENCODE(str,pswd_str)：使用psswd_str作为密码，加密str，变成二进制数据
mysql> SELECT ENCODE('pjf','123.com');
+-------------------------+
| ENCODE('pjf','123.com') |
+-------------------------+
| 6잠 |
+-------------------------+DECODE(crypt_str,pswd_str)：使用pswd_str解密之前吉阿米果的crypt_str
mysql> SELECT DECODE(ENCODE('pjf','123.com'),'123.com');
+-------------------------------------------+
| DECODE(ENCODE('pjf','123.com'),'123.com') |
+-------------------------------------------+
| pjf |
+-------------------------------------------+加密解密的实际应用
mysql> CREATE TABLE jm(
event1 VARCHAR(255),
pswd BLOB);
mysql> INSERT INTO jm VALUES(1,ENCODE('jintianhenkaixin','123.com'));
/*使用123.com加密‘jintianhenkaixin’*/
mysql> SELECT * FROM jm;
+--------+------------------+
| event1 | pswd |
+--------+------------------+
| 1 | ʼwA ¼ǂ4e |
+--------+------------------+
mysql> SELECT DECODE(pswd,'123.com') FROM jm; /*解密*/
+------------------------+
| DECODE(pswd,'123.com') |
+------------------------+
| jintianhenkaixin |
+------------------------+
56、格式化函数
FORMAT(x,n)：将x进行格式化，结果以四舍五入的形式保留小数点后面n为，结果以字符串形式返回
mysql> SELECT FORMAT(1.2345,3),FORMAT(1.3456,2),FORMAT(1.2,3);
+------------------+------------------+---------------+
| FORMAT(1.2345,3) | FORMAT(1.3456,2) | FORMAT(1.2,3) |
+------------------+------------------+---------------+
| 1.235 | 1.35 | 1.200 |
+------------------+------------------+---------------+57、不同进制的数据进行相互转换的函数
CONV(要转换的数据,该数据的进制,转换成的进制)：
mysql> SELECT CONV(100,10,2); /*将10进制的100，转换成2进制*/
+----------------+
| CONV(100,10,2) |
+----------------+
| 1100100 |
+----------------+
mysql> SELECT CONV('1a',16,10); /*将16进制的1a转换成10进制*/
+------------------+
| CONV('1a',16,10) |
+------------------+
| 26 |
+------------------+58、IP地址和数值互相转换的函数
INET_ATON(expr)：将ip地址转换为数值
INET_NTOA(expr)：将数值转换为ip地址
mysql> SELECT INET_ATON('1.1.1.1');
/*相当于1*256^3+1*256^2+1*256^1+1*256^0*/
+----------------------+
| INET_ATON('1.1.1.1') |
+----------------------+
| 16843009 |
+----------------------+
mysql> SELECT INET_NTOA(16843009);
+-----------------------+
| INET_NTOA(16843009) |
+-----------------------+
| 1.1.1.1 |
+-----------------------+59、加锁函数，解锁函数
GET_LOCK(str,timeout)：使用str得到一个锁，超时时间为timeout秒
（1）如果成功得到锁，返回1
（2）如果操作超时，返回0
（3）如果出现错误，返回NULL
mysql> SELECT GET_LOCK('feiyi',10);
/*如果给feiyi成功设置锁之后*/
+----------------------+
| GET_LOCK('feiyi',10) |
+----------------------+
| 1 |
+----------------------+
mysql> SELECT GET_LOCK('feiyi',10); /*在另一个mysql终端中，同样给feiyi进行加锁操作，会出现超时*/
+----------------------+
| GET_LOCK('feiyi',10) |
+----------------------+
| 0 |
+----------------------+==不能给已经加锁的字符串进行二次加锁==
RELEASE_LOCK(str)：给字符串进行解锁
（1）如果解锁成功，返回1
（2）如果该线程尚未创建锁，返回0
（3）如果锁不存在，返回NULL，
mysql> SELECT RELEASE_LOCK('feiyi');
/*在上面例子汇总加锁失败的终端，对feiyi进行解锁，发现返回0,*/
+-----------------------+
| RELEASE_LOCK('feiyi') |
+-----------------------+
| 0 |
+-----------------------+
/*切换到给feiyi字符串加锁的终端，进行解锁，返回1，成功解锁*/
mysql> SELECT RELEASE_LOCK('feiyi');
+-----------------------+
| RELEASE_LOCK('feiyi') |
+-----------------------+
| 1 |
+-----------------------+==必须加锁解锁都在同一终端==
IS_FREE_LOCK(str)：检查名为str的锁是否可用
（1）如果可用返回1
（2）如果正被使用，返回0
（3）如果参数出错返回NULL
mysql> SELECT IS_FREE_LOCK('mupei'); /*因为在其他终端中mupei已经被加锁并使用*/
+-----------------------+
| IS_FREE_LOCK('mupei') |
+-----------------------+
| 0 |
+-----------------------+
1 row in set (0.00 sec)
mysql> SELECT IS_USED_LOCK('mupei'); /*表示connnecting_id为9的用户正在使用*/
+-----------------------+
| IS_USED_LOCK('mupei') |
+-----------------------+
| 9 |
+-----------------------+
mysql> kill 9; /*将该id杀掉，再次使用函数检测*/
mysql> SELECT IS_USED_LOCK('mupei'); /*没有人在使用*/
+-----------------------+
| IS_USED_LOCK('mupei') |
+-----------------------+
| NULL |
+-----------------------+
mysql> SELECT IS_FREE_LOCK('mupei'); /*表示该字符串可用*/
+-----------------------+
| IS_FREE_LOCK('mupei') |
+-----------------------+
| 1 |
+-----------------------+
IS_USED_LOCK(str)：判断名为str的锁是否正在被使用，如果被使用，返回该用户的标识符，如果没被使用返回NULL
60、改变字符集的函数
CONVERT(字符串 USING 要改变的字符集)：
mysql> SELECT CONVERT('你好' USING latin1); /*latin1该字符集不支持中文*/
+--------------------------------+
| CONVERT('你好' USING latin1) |
+--------------------------------+
| ?? |
+--------------------------------+61、改变数据类型的函数
CAST(x AS type)：将x以指定的type类型进行显示
CONVERT(x,type)：同上
mysql> SELECT CAST(500 AS CHAR(2));
/*因为只允许存储2个字节，结果是50，但它不是数字类型，是文本类型的50*/
+----------------------+
| CAST(500 AS CHAR(2)) |
+----------------------+
| 50 |
+----------------------+
mysql> SELECT CAST(500 AS CHAR(2));
+----------------------+
| CAST(500 AS CHAR(2)) |
+----------------------+
| 50 |
+----------------------+
mysql> SELECT CAST(010101 AS DATE);
+----------------------+
| CAST(010101 AS DATE) |
+----------------------+
| 2001-01-01 |
+----------------------+
62、重复执行指定操作的函数
BENCHMARK(count,expr)：用于计算服务器性能的函数，检测速度，做的操作耗时越短，效率越高
mysql> SELECT BENCHMARK(500000,PASSWORD('123.com'));
/*返回值0不需要知道，可以看到给123.com转换为hash值，转换50万次，用了0.14s*/
+---------------------------------------+
| BENCHMARK(500000,PASSWORD('123.com')) |
+---------------------------------------+
| 0 |
+---------------------------------------+
1 row in set (0.14 sec)博客内容遵循 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 协议
本文永久链接是：https://www.feiyiblog.com/2019/12/09/10%E3%80%81%E5%87%BD%E6%95%B0/
