MySql常用函数说明及使用示例

1、字符串函数

1. CONCAT(str1,str2,…)：字符串拼接。
2. CHAR_LENGTH(str)/CHARACTER_LENGTH(str)：获取字符串的字符长度（1个数字、中文、英文等都为1个字符）。
3. LENGTH(str)：获取字符串的字节长度（utf8的一个汉字为3个字节，gbk的一个汉字为2个字节）。
4. SUBSTR/SUBSTRING(str,start[,length])：字符串截取，start：起始位置（下标从1开始），length（可选）：截取长度。
5. INSTR(str,substr)：如果字符串str中包含substr，就返回1，否则返回0。
6. ①、TRIM([remstr FROM] str)：去除字符串str前后的remstr字符串，remstr（可选）：默认为空格。
   ②、LTRIM(str)：去除字符串左边的空格。
   ③、RTRIM(str)：去除字符串右边的空格。
   ④、TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)：去除str字符串（前后|头部|尾部）的remstr字符，如果去除前后，BOTH可省略。
7. STRCMP(expr1,expr2)：字符串大小的比较（expr1和expr2相等返回0，expr1大于expr2返回1，expr1小于expr2返回-1）。
8. UPPER(str)/UCASE(str)：字母变大写。
9. LOWER(str)/LCASE(str)：字母变小写。
10. REPLACE(str,from_str,to_str)：字符串替换， 将str字符串中的from_str字符串替换为to_str字符串。
11. LPAD(str,len,padstr)：len是结果字符的长度，若str的长度大于len的长度，就保留len长度的str字符，若str的长度小于len的长度，则用padstr在str字符串的头部填充到len长度的新字符串。
12. RPAD(str,len,padstr)：len是结果字符的长度，若str的长度大于len的长度，就保留len长度的str字符，若str的长度小于len的长度，则用padstr在str字符串的尾部填充到len长度的新字符串。
13. LEFT(str,len)： 从str的左边开始取len个字符。
14. RIGHT(str,len)：从str的右边开始取len个字符。

使用示例：

-- 1、CONCAT(str1,str2,...)
SELECT CONCAT('字符串-', '拼接-', '在一起') 字符串拼接;
-- 2、CHAR_LENGTH(str)/CHARACTER_LENGTH(str)
SELECT CHAR_LENGTH('你好') 字符长度方式一, CHARACTER_LENGTH(' 你好 ') 字符长度方式二;
-- 3、LENGTH(str)
SELECT LENGTH('hello') en字节长度, LENGTH('你好') cn字节长度;
-- 4、SUBSTR/SUBSTRING(str,start[,length])
SELECT SUBSTR('string', 1, 3) substr截取字符串, SUBSTRING('string', 4, 3) substring截取字符串;
-- 5、INSTR(str,substr)
SELECT INSTR('hello', 'oo') hello是否包含oo;
SELECT IF(INSTR('hello', 'llo') > 0, '包含', '不包含') hello是否包含llo;
-- 6
-- ①、TRIM([remstr FROM] str)
SELECT TRIM(' str ') 去除字符串前后空格;
-- ②、LTRIM(str)
SELECT LTRIM(' str ') 去除字符串左边的空格;
-- ③、RTRIM(str)
SELECT RTRIM(' str ') 去除字符串右边的空格;
-- ④、TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
SELECT TRIM('str' FROM 'string str')          去除字符串前后的str字符,
       TRIM(BOTH 'str' FROM 'string str')     去除字符串前后的str字符,
       TRIM(LEADING 'str' FROM 'string str')  去除字符串头部的str字符,
       TRIM(TRAILING 'str' FROM 'string str') 去除字符串尾部的str字符;
-- 7、STRCMP(expr1,expr2)
SELECT STRCMP('2022-02', '2022-01') 时间比较;
SELECT IF(STRCMP('2022-02', '2022-04') >= 0, '大于等于', '小于') '2022-02与2022-01时间比较结果';
-- 8、UPPER(str)/UCASE(str)
SELECT UPPER('to upper') 字母变大写方式一,
       UCASE('to upper') 字母变大写方式二;
-- 9、LOWER(str)/LCASE(str)
SELECT LOWER('TO LOWER') 字母变小写方式一,
       LCASE('TO LOWER') 字母变小写方式二;
-- 10、REPLACE(str,from_str,to_str)
SELECT REPLACE('hello lcb', 'o', 'o -');
-- 11、LPAD(str,len,padstr)
SELECT LPAD('8', 2, '1');
-- 12、RPAD(str,len,padstr)
SELECT RPAD('8', 2, '1');
-- 13、LEFT(str,len)
select LEFT('hello',2);
-- 14、RIGHT(str,len)
select RIGHT('hello',2);

2、日期函数

1. NOW()、CURRENT_TIMESTAMP()、SYSDATE()：获取当前日期时间（yyyy-MM-dd HH:mm:ss NOW(),CURRENT_TIMESTAMP()作用一致，SYSDATE()的时间等于now()的时间加上延时时间）。
2. CURRENT_DATE()、DATE (NOW())：获取当前日期（yyyy-MM-dd）。
3. CURTIME()、CURRENT_TIME()：获取当前时间（HH:mm:ss）。
4. YEAR(date)、MONTH(date)、MONTHNAME(date)、DAYOFMONTH(date)、DAY(date)：日期中获取当前年、当前月、英文当前月、当前月几号DAYOFMONTH(date)/DAY(date)。
5. WEEKDAY(date)：日期中获取周(0-6代表周一到周日)。
6. DAYOFWEEK(date)：日期中的一周中的第几天(周日为1,周一为2)。
7. YEARWEEK(date)、YEARWEEK(date,mode)：日期中获取年+获取一年中的第几周(YEARWEEK(date,mode)：mode可有可无，没有就代表0也可简化为YEARWEEK(date)表示从周日开始计算，过了周日就加一周,mode为1表示从周一开始计算，过了周一就加一周)。
8. WEEK(date[,mode])：日期中获取一年中的第几周(WEEK(date[,mode]))：mode可有可无，没有就代表0也可简化为WEEK(date)表示从周日开始计算，过了周日就加一周,mode为1表示从周一开始计算，过了周一就加一周)。
9. WEEKOFYEAR(date)：日期中获取一年中的第几周,从周日开始计算，过了周日就加一周。
10. DATEDIFF(expr1,expr2)：两个时间表达式间隔时间（天）即两个时间差（天）。
11. TIMEDIFF(expr1,expr2)：两个时间差（相差多少个小时多少分钟多少秒钟）
12. YEAR(date)、MONTH(date)、DAY(date)、WEEK(date)、DAYOFWEEK(date)、DAYNAME(date)、HOUR(time)、MINUTE(time)、SECOND(time)：日期中的年、月、日、一年中过了几周(周日开始计算，周一加1周)、周中天(从周日开始为1，周一为2)、英文周中天、时、分、秒。
13. UNIX_TIMESTAMP()：返回1970-1-1到现在的秒数。
14. FROM_UNIXTIME(unix_timestamp[,format])：把一个unix_timestamp的日期转为指定（format）格式的日期（format不写的话，默认为（%Y-%m-%d %H:%i:%s））
15. STR_TO_DATE(str,format)：字符串转时间格式。
16. DATE_FORMAT(date,format)：日期转字符串。
17. DATE_ADD(date,INTERVAL expr unit)：向日期添加指定的时间间隔。
18. DATE_SUB(date,INTERVAL expr unit)：从日期减去指定的时间间隔，用法同DATE_ADD函数。

-- 获取当前日期时间 yyyy-MM-dd HH:mm:ss NOW(),CURRENT_TIMESTAMP()作用一致，SYSDATE()的时间等于now()的时间加上延时时间
select NOW(), CURRENT_TIMESTAMP(), SYSDATE();
-- 获取当前日期 yyyy-MM-dd
select CURRENT_DATE(), DATE (NOW());
-- 获取当前时间 HH:mm:ss
select CURTIME(), CURRENT_TIME();
-- 日期中获取当前年、当前月、英文当前月、当前月几号DAYOFMONTH(date)/DAY(date)
select YEAR (NOW()), MONTH (NOW()), MONTHNAME(NOW()), DAYOFMONTH(NOW()), DAY (NOW());
-- 日期中获取周(0-6代表周一到周日)
SELECT WEEKDAY(NOW()), WEEKDAY(STR_TO_DATE('2022-07-09', '%Y-%m-%d'));
-- 日期中的一周中的第几天(周日为1,周一为2)
select DAYOFWEEK('2022-07-11');
-- 日期中获取年+获取一年中的第几周(YEARWEEK(date,mode)：mode可有可无，没有就代表0也可简化为YEARWEEK(date)表示从周日开始计算，过了周日就加一周,mode为1表示从周一开始计算，过了周一就加一周)、
-- 日期中获取一年中的第几周(WEEK(date[,mode]))：mode可有可无，没有就代表0也可简化为WEEK(date)表示从周日开始计算，过了周日就加一周,mode为1表示从周一开始计算，过了周一就加一周)
-- 日期中获取一年中的第几周,从周日开始计算，过了周日就加一周
select YEARWEEK('2022-07-10'),
       YEARWEEK('2022-07-10', 0),
       YEARWEEK('2022-07-10', 1),
       WEEK('2022-07-10'),
       WEEK('2022-07-10', 0),
       WEEK('2022-07-10', 1),
       WEEKOFYEAR('2022-07-11'),
       WEEKOFYEAR('2022-07-10');
-- 两个时间差（天）
select DATEDIFF('2022-07-12', '2022-07-09');
-- 两个时间差（时分秒）
select TIMEDIFF('12:11:22', '09:10:11');
-- 日期中的年、月、日、一年中过了几周(周日开始计算，周一加1周)、周中天(从周日开始为1，周一为2)、英文周中天、时、分、秒
select YEAR (NOW()), MONTH (NOW()), DAY (NOW()), WEEK(NOW()), DAYOFWEEK(NOW()), DAYNAME(NOW()), HOUR (NOW()), MINUTE (NOW()), SECOND (NOW());
-- 1970-1-1到现在的秒数
select UNIX_TIMESTAMP();
-- 把一个unix_timestamp的日期转为指定（format）格式的日期（format不写的话，默认为（%Y-%m-%d %H:%i:%s））
select FROM_UNIXTIME(1675739986), FROM_UNIXTIME(1675739986, '%Y/%m/%d %H:%i:%s');
-- 字符串转时间格式 STR_TO_DATE(str,format) Y:4位，y：2位，%m：01，%c：1
select STR_TO_DATE('2022-01-11', '%Y-%m-%d'), STR_TO_DATE('2022-07-11 10:10:10', '%Y-%m-%d %H:%i:%s');
-- 日期转字符串 DATE_FORMAT(date,format)
select DATE_FORMAT('2022-07-11', '%Y年%c月%d日');
--  DATE_ADD(date,INTERVAL expr unit)：向日期添加指定的时间间隔 DATE_SUB(date,INTERVAL expr unit)：从日期减去指定的时间间隔，用法同DATE_ADD函数
-- DATE_ADD 减一天、加一天、加一天一个小时、加一天一个小时十分钟十秒钟
select DATE_ADD(NOW(), INTERVAL -1 DAY),
       DATE_ADD(NOW(), INTERVAL +1 DAY),
       DATE_ADD(NOW(), INTERVAL '1 1' DAY_HOUR),
       DATE_ADD(NOW(), INTERVAL +'1 1:10:10' DAY_SECOND);

3、数字函数

1. ROUND(X)、ROUND(X,D)：四舍五入，X就是要四舍五入的数，D就是要保留的位数，D不写的话就默认为0，即保留整数位。
2. CEIL(X)、CEILING(X)：向上取整。
3. FLOOR(X)：向下取整。
4. MOD(N,M)：求余数(N%M)。
5. ABS(X)：绝对值。
6. RAND()：[0,1)之间的随机数。
7. TRUNCATE(X,D)：数值截取，X：被截取的数值，D：截取超过D长度的小数位。
8. FORMAT(X,D)：保留小数位数，X的小数位数超过D位，就进行四舍五入保留D位小数。

使用示例

-- ROUND(X)、ROUND(X,D)：四舍五入，X就是要四舍五入的数，D就是要保留的位数，D不写的话就默认为0，即保留整数位
select ROUND(10.19), ROUND(10.19, 1);
-- CEIL(X)、CEILING(X)：向上取整
select CEIL(10.333), CEILING(10.333);
-- FLOOR(X)：向下取整
select FLOOR(10.9);
-- MOD(N,M)：求余数(N%M)
SELECT MOD(10, 3);
-- ABS(X)：绝对值
select ABS(-6);
-- RAND()：[0,1)之间的随机数
select RAND();
-- TRUNCATE(X,D)：数值截取，X：被截取的数值，D：截取超过D长度的小数位
select TRUNCATE(1055, 1), TRUNCATE(1055.99, 1);
-- FORMAT(X,D)：保留小数位数，X的小数位数超过D位，就进行四舍五入保留D位小数
select FORMAT(1055.91, 1), FORMAT(1055.99, 1);

4、条件控制函数

1. IF(expr1,expr2,expr3)：expr1为true，返回expr2，否则返回expr3。
2. IFNULL(expr1,expr2)：如果expr1为null，就返回expr2，否则返回expr1。
3. NULLIF(expr1,expr2)：如果expr1和expr2相等，就返回null 否则返回expr1。
4. CASE WHEN THEN ELSE END;：类似于switch case功能。

使用示例

-- IF(expr1,expr2,expr3)：expr1为true，返回expr2，否则返回expr3
select IF(TRUE, 1, 0), IF(FALSE, 1, 0);
-- IFNULL(expr1,expr2)：如果expr1为null，就返回expr2，否则返回expr1
select IFNULL(1, 2), IFNULL(null, 2);
-- NULLIF(expr1,expr2)：如果expr1和expr2相等，就返回null 否则返回expr1
select NULLIF(1, 2), NULLIF(2, 2);
-- IFNULL(expr1,expr2)、NULLIF(expr1,expr2)的综合使用
select IFNULL(NULLIF(1, 2), '空'), IFNULL(NULLIF(2, 2), '空');
-- CASE WHEN THEN ELSE END; 使用
-- 方式一
select CASE
           WHEN false THEN
               'this is false'
           WHEN 100 = 100 THEN
               'this is true'
           ELSE
               'default result'
           END;
-- 方式二
select CASE 1
           WHEN 0 THEN
               'ZERO'
           ELSE
               'ONE'
           END;

5、聚合函数

1. SUM([DISTINCT] expr)：求和。
2. AVG([DISTINCT] expr)：求平均值。
3. MAX(expr)、MAX([DISTINCT] expr)：求最大值。
4. MIN(expr)、MIN([DISTINCT] expr)：求最小值。
5. COUNT(expr)、COUNT(DISTINCT expr,[expr…])：统计。

数据准备

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '名字',
  `score` double NULL DEFAULT NULL COMMENT '分数',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '张三', 95);
INSERT INTO `student` VALUES (2, '李四', 98);
INSERT INTO `student` VALUES (3, '王五', 93);
INSERT INTO `student` VALUES (4, '赵六', 99);
INSERT INTO `student` VALUES (5, '贾七', 93);
INSERT INTO `student` VALUES (6, '张三', 95);

使用示例

select SUM(score)                  求和,
       AVG(score)                  求平均值,
       MAX(score)                  求最大值,
       MAX(DISTINCT score)         去重求最大值,
       MIN(score)                  求最小值,
       MIN(DISTINCT score)         去重求最小值,
       COUNT(score)                统计个数,
       COUNT(DISTINCT name, score) 根据名字、分数去重统计个数
from student;

执行结果：

6、其他函数

1. VERSION()：查看版本信息
2. DATABASE()：当前数据库名称
3. USER()：当前用户

使用示例

select VERSION() 版本信息, DATABASE() 数据库名称, USER() 当前用户;

执行结果：