MySQL常用函数
数学函数
CEIL()
进一取整
SELECT CEIL(1.2);
+-----------+
| CEIL(1.2) |
+-----------+
| 2 |
+-----------+
FLOOR()
舍掉小数部分
SELECT FLOOR(1.9);
+------------+
| FLOOR(1.9) |
+------------+
| 1 |
+------------+
ROUND()
四舍五入
-- 最后的一个参数表示四舍五入到小数点后几位,不写即为0
SELECT ROUND(3.56789,3);
+------------------+
| ROUND(3.56789,3) |
+------------------+
| 3.568 |
+------------------+
TRUNCATE()
截取小数点后几位
-- 第二个参数不可以不写
SELECT TRUNCATE(3.456789,3);
+----------------------+
| TRUNCATE(3.456789,3) |
+----------------------+
| 3.456 |
+----------------------+
MOD()
取余数
-- 5 % 2
SELECT MOD(5,2);
+----------+
| MOD(5,2) |
+----------+
| 1 |
+----------+
ABS()
取绝对值
SELECT ABS(-12);
+----------+
| ABS(-12) |
+----------+
| 12 |
+----------+
POWER()
幂运算
-- 2^3
SELECT POWER(2,3);
+------------+
| POWER(2,3) |
+------------+
| 8 |
+------------+
PI()
圆周率
-- 这还没有小学背的精度高3.1415926~3.1415927
SELECT PI();
+----------+
| PI() |
+----------+
| 3.141593 |
+----------+
RAND()或者RAND(X)
0~1之间的随机数
-- 产生随机数
SELECT RAND();
+--------------------+
| RAND() |
+--------------------+
| 0.7419394308160426 |
+--------------------+
-- 给予一个随机数种子 种子相同则产生的随机数相同
SELECT RAND(1);
+---------------------+
| RAND(1) |
+---------------------+
| 0.40540353712197724 |
+---------------------+
SIGN(X)
得到数字符号。正数返回1,负数返回-1,0返回0。
SELECT SIGN(-12);
+-----------+
| SIGN(-12) |
+-----------+
| -1 |
+-----------+
SELECT SIGN(12);
+----------+
| SIGN(12) |
+----------+
| 1 |
+----------+
EXP()
计算e的x次方
SELECT EXP(2);
+------------------+
| EXP(2) |
+------------------+
| 7.38905609893065 |
+------------------+
字符串函数
CHAR_LENGTH()
得到字符串的字符数
SELECT CHAR_LENGTH('ABC');
+--------------------+
| CHAR_LENGTH('ABC') |
+--------------------+
| 3 |
+--------------------+
SELECT CHAR_LENGTH('你好啊');
+-----------------------+
| CHAR_LENGTH('你好啊') |
+-----------------------+
| 3 |
+-----------------------+
LENGTH()
得到字符串的长度
SELECT LENGTH('ABC');
+---------------+
| LENGTH('ABC') |
+---------------+
| 3 |
+---------------+
-- 一个中文字符在utf8下为3个长度
SELECT LENGTH('你好啊');
+------------------+
| LENGTH('你好啊') |
+------------------+
| 9 |
+------------------+
CONCAT(s1,s2,…)
将若干个字符串合并成一个字符串
SELECT CONCAT('A','B','C');
+---------------------+
| CONCAT('A','B','C') |
+---------------------+
| ABC |
+---------------------+
-- 如果拼接的字符串里有一个null,那么结果就为null
SELECT CONCAT('A','B','C',NULL);
+--------------------------+
| CONCAT('A','B','C',NULL) |
+--------------------------+
| NULL |
+--------------------------+
CONCAT_WS(x,s1,s2,s3,…)
以指定的分隔符x拼接字符串
SELECT CONCAT_WS('-','A','B','C');
+----------------------------+
| CONCAT_WS('-','A','B','C') |
+----------------------------+
| A-B-C |
+----------------------------+
SELECT CONCAT_WS('-','A','B','C',NULL);
+---------------------------------+
| CONCAT_WS('-','A','B','C',NULL) |
+---------------------------------+
| A-B-C |
+---------------------------------+
-- 如果分割符为null 结果为null
SELECT CONCAT_WS(NULL,'A','B','C');
+-----------------------------+
| CONCAT_WS(NULL,'A','B','C') |
+-----------------------------+
| NULL |
+-----------------------------+
转换大小写
-
转换为大写
UPPER(); UCASE();
-
转换为小写
LOWER(); LCASE();
SELECT UPPER('hello king'),UCASE('hello imooc'),LOWER('HELLO ADMIN'),LCASE('HELLO EVERYBODY');
+---------------------+----------------------+----------------------+--------------------------+
| UPPER('hello king') | UCASE('hello imooc') | LOWER('HELLO ADMIN') | LCASE('HELLO EVERYBODY') |
+---------------------+----------------------+----------------------+--------------------------+
| HELLO KING | HELLO IMOOC | hello admin | hello everybody |
+---------------------+----------------------+----------------------+--------------------------+
REVERSE()
SELECT REVERSE('ABC');
+----------------+
| REVERSE('ABC') |
+----------------+
| CBA |
+----------------+
LEFT()与RIGHT()
- **LEFT():**返回字符串的前几个字符
- **RIGHT():**返回字符串的后几个字符
SELECT LEFT('HELLO',2),RIGHT('HELLO',2);
+-----------------+------------------+
| LEFT('HELLO',2) | RIGHT('HELLO',2) |
+-----------------+------------------+
| HE | LO |
+-----------------+------------------+
-- 超出字符串字符数就会全取
SELECT LEFT('HELLO',10),RIGHT('HELLO',10);
+------------------+-------------------+
| LEFT('HELLO',10) | RIGHT('HELLO',10) |
+------------------+-------------------+
| HELLO | HELLO |
+------------------+-------------------+
LPAD()与RPAD()
- **LPAD():**在字符串左端填充到指定长度
- **RPAD():**在字符串右端填充到指定长度
SELECT LPAD('ABC',10,'?'),RPAD('ABC',10,'?');
+--------------------+--------------------+
| LPAD('ABC',10,'?') | RPAD('ABC',10,'?') |
+--------------------+--------------------+
| ???????ABC | ABC??????? |
+--------------------+--------------------+
TRIM() | LTRIM() | RTRIM()
- **TRIM():**去掉字符串两端的空格
- **LTRIM():**去掉字符串左侧的空格
- **RTRIM():**去掉字符串左侧的空格
-- 为了使操作结果更加的明显 我们用 CONCAT 在字符串两端加上星号
SELECT CONCAT('*',TRIM(' ABC '),'*'),CONCAT('*',LTRIM(' ABC '),'*'),CONCAT('*',RTRIM(' ABC '),'*');
+-------------------------------+--------------------------------+--------------------------------+
| CONCAT('*',TRIM(' ABC '),'*') | CONCAT('*',LTRIM(' ABC '),'*') | CONCAT('*',RTRIM(' ABC '),'*') |
+-------------------------------+--------------------------------+--------------------------------+
| *ABC* | *ABC * | * ABC* |
+-------------------------------+--------------------------------+--------------------------------+
REPEAT()
重复指定的次数
SELECT REPEAT('HELLO',3);
+-------------------+
| REPEAT('HELLO',3) |
+-------------------+
| HELLOHELLOHELLO |
+-------------------+
REPLACE()
字符串替换
-- 将HELLO KING 中的 KING替换成 QUEEN
SELECT REPLACE('HELLO KING','KING','QUEEN');
+--------------------------------------+
| REPLACE('HELLO KING','KING','QUEEN') |
+--------------------------------------+
| HELLO QUEEN |
+--------------------------------------+
SUBSTRING()
截取字符串
-- 从1开始截取3位
SELECT SUBSTRING('ABCDEFG',1,3);
+--------------------------+
| SUBSTRING('ABCDEFG',1,3) |
+--------------------------+
| ABC |
+--------------------------+
STRCMP()
比较字符串的ASCII码
-- 第一个小于第二个
SELECT STRCMP('A','B');
+-----------------+
| STRCMP('A','B') |
+-----------------+
| -1 |
+-----------------+
-- 第一个大于第二个
SELECT STRCMP('z','a');
+-----------------+
| STRCMP('z','a') |
+-----------------+
| 1 |
+-----------------+
-- 第一个等于第二个
SELECT STRCMP('a','a');
+-----------------+
| STRCMP('a','a') |
+-----------------+
| 0 |
+-----------------+
LOCATE()
-- 返回子串在主串中第一次出现的位置
SELECT LOCATE('LL','HELLO');
+----------------------+
| LOCATE('LL','HELLO') |
+----------------------+
| 3 |
+----------------------+
1 row in set (0.00 sec)
-- 返回子串在主串的第4个位置之后第一次出现的位置
mysql> SELECT LOCATE('LL','HELLOHELLO',4);
+-----------------------------+
| LOCATE('LL','HELLOHELLO',4) |
+-----------------------------+
| 8 |
+-----------------------------+
日期时间函数
CURDATE() | CURRENT_DATE()
-- 返回当前的日期
SELECT CURDATE(),CURRENT_DATE();
+------------+----------------+
| CURDATE() | CURRENT_DATE() |
+------------+----------------+
| 2020-10-04 | 2020-10-04 |
+------------+----------------+
CURTIME() | CURRENT_TIME()
-- 返回当前时间
SELECT CURTIME(),CURRENT_TIME();
+-----------+----------------+
| CURTIME() | CURRENT_TIME() |
+-----------+----------------+
| 14:10:13 | 14:10:13 |
+-----------+----------------+
NOW() | CURRENT_TIMESTAMP() | SYSDATE()
-- 返回当前的日期时间
SELECT NOW(),CURRENT_TIMESTAMP(),SYSDATE();
+---------------------+---------------------+---------------------+
| NOW() | CURRENT_TIMESTAMP() | SYSDATE() |
+---------------------+---------------------+---------------------+
| 2020-10-04 14:12:04 | 2020-10-04 14:12:04 | 2020-10-04 14:12:04 |
+---------------------+---------------------+---------------------+
MONTH() | MONTHNAME()
- **MONTH():**返回日期中的月份
- **MONTHNAME():**返回日期中月份的名称
-- 返回日期中的月份
SELECT MONTH('2017-2-19');
+--------------------+
| MONTH('2017-2-19') |
+--------------------+
| 2 |
+--------------------+
SELECT MONTH(CURDATE()),MONTHNAME(CURDATE());
+------------------+----------------------+
| MONTH(CURDATE()) | MONTHNAME(CURDATE()) |
+------------------+----------------------+
| 10 | October |
+------------------+----------------------+
DAYNAME()
- **DAYNAME():**返回日期是星期几
SELECT DAYNAME(NOW());
+----------------+
| DAYNAME(NOW()) |
+----------------+
| Sunday |
+----------------+
DAYOFWEEK()
- **DAYOFWEEK():**返回日期是一周内的第几天(星期天是第一天)
SELECT DAYOFWEEK(NOW());
+------------------+
| DAYOFWEEK(NOW()) |
+------------------+
| 1 |
+------------------+
WEEK()
- **WEEK():**返回日期是一年中的第几个星期
SELECT WEEK(NOW());
+-------------+
| WEEK(NOW()) |
+-------------+
| 40 |
+-------------+
YEAR() | MONTH() | DAY() | HOUR() | MINUTE() | SECOND()
-- 也就是把时间2020-10-4-14:29:40给拆开了,真是一家子整整齐齐^(* ̄(oo) ̄)^
SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW());
+-------------+--------------+------------+-------------+---------------+---------------+
| YEAR(NOW()) | MONTH(NOW()) | DAY(NOW()) | HOUR(NOW()) | MINUTE(NOW()) | SECOND(NOW()) |
+-------------+--------------+------------+-------------+---------------+---------------+
| 2020 | 10 | 4 | 14 | 29 | 40 |
+-------------+--------------+------------+-------------+---------------+---------------+
DATEDIFF()
- **DATEDIFF():**计算两个日期相差的天数
SELECT DATEDIFF('2020-10-1','2020-10-8');
+-----------------------------------+
| DATEDIFF('2020-10-1','2020-10-8') |
+-----------------------------------+
| -7 |
+-----------------------------------+
SELECT DATEDIFF('2020-10-8','2020-10-1');
+-----------------------------------+
| DATEDIFF('2020-10-8','2020-10-1') |
+-----------------------------------+
| 7 |
+-----------------------------------+
其它常用函数
VAERSION()
- 获得版本号
SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.17 |
+-----------+
CONNECTION_ID()
- 得到当前服务器的连接数
SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
| 78 |
+-----------------+
DATABASE() | SCHEMA()
- 返回当前选择的数据库的名字
SELECT DATABASE(),SCHEMA();
+------------+----------+
| DATABASE() | SCHEMA() |
+------------+----------+
| king | king |
+------------+----------+
USER() | CURRENT_USER() | SYSTEM_USER() | SESSION_USER()
- 返回当前登录的用户
SELECT USER(),CURRENT_USER(),SYSTEM_USER(),SESSION_USER();
+----------------+----------------+----------------+----------------+
| USER() | CURRENT_USER() | SYSTEM_USER() | SESSION_USER() |
+----------------+----------------+----------------+----------------+
| root@localhost | root@localhost | root@localhost | root@localhost |
+----------------+----------------+----------------+----------------+
LAST_INSERT_ID()
- 返回上一次插入操作的AUTO_INCREMENT的值
MD5()
- 使用MD5加密,返回一个32个字符的字符串
SELECT MD5('KING');
+----------------------------------+
| MD5('KING') |
+----------------------------------+
| 123b28961dd0f97d91bcb55d7a3b7c1c |
+----------------------------------+
PASSWORD()
- MySQL默认的密码加密算法
不知道为什么老是说我语法错误,就没有演示了。反正就是一个加密。