1、字符串函数
- CONCAT(str1,str2,…):字符串拼接。
- CHAR_LENGTH(str)/CHARACTER_LENGTH(str):获取字符串的字符长度(1个数字、中文、英文等都为1个字符)。
- LENGTH(str):获取字符串的字节长度(utf8的一个汉字为3个字节,gbk的一个汉字为2个字节)。
- SUBSTR/SUBSTRING(str,start[,length]):字符串截取,start:起始位置(下标从1开始),length(可选):截取长度。
- INSTR(str,substr):如果字符串str中包含substr,就返回1,否则返回0。
- ①、TRIM([remstr FROM] str):去除字符串str前后的remstr字符串,remstr(可选):默认为空格。
②、LTRIM(str):去除字符串左边的空格。
③、RTRIM(str):去除字符串右边的空格。
④、TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str):去除str字符串(前后|头部|尾部)的remstr字符,如果去除前后,BOTH可省略。 - STRCMP(expr1,expr2):字符串大小的比较(expr1和expr2相等返回0,expr1大于expr2返回1,expr1小于expr2返回-1)。
- UPPER(str)/UCASE(str):字母变大写。
- LOWER(str)/LCASE(str):字母变小写。
- REPLACE(str,from_str,to_str):字符串替换, 将str字符串中的from_str字符串替换为to_str字符串。
- LPAD(str,len,padstr):len是结果字符的长度,若str的长度大于len的长度,就保留len长度的str字符,若str的长度小于len的长度,则用padstr在str字符串的头部填充到len长度的新字符串。
- RPAD(str,len,padstr):len是结果字符的长度,若str的长度大于len的长度,就保留len长度的str字符,若str的长度小于len的长度,则用padstr在str字符串的尾部填充到len长度的新字符串。
- LEFT(str,len): 从str的左边开始取len个字符。
- 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、日期函数
- NOW()、CURRENT_TIMESTAMP()、SYSDATE():获取当前日期时间(yyyy-MM-dd HH:mm:ss NOW(),CURRENT_TIMESTAMP()作用一致,SYSDATE()的时间等于now()的时间加上延时时间)。
- CURRENT_DATE()、DATE (NOW()):获取当前日期(yyyy-MM-dd)。
- CURTIME()、CURRENT_TIME():获取当前时间(HH:mm:ss)。
- YEAR(date)、MONTH(date)、MONTHNAME(date)、DAYOFMONTH(date)、DAY(date):日期中获取当前年、当前月、英文当前月、当前月几号DAYOFMONTH(date)/DAY(date)。
- WEEKDAY(date):日期中获取周(0-6代表周一到周日)。
- DAYOFWEEK(date):日期中的一周中的第几天(周日为1,周一为2)。
- YEARWEEK(date)、YEARWEEK(date,mode):日期中获取年+获取一年中的第几周(YEARWEEK(date,mode):mode可有可无,没有就代表0也可简化为YEARWEEK(date)表示从周日开始计算,过了周日就加一周,mode为1表示从周一开始计算,过了周一就加一周)。
- WEEK(date[,mode]):日期中获取一年中的第几周(WEEK(date[,mode])):mode可有可无,没有就代表0也可简化为WEEK(date)表示从周日开始计算,过了周日就加一周,mode为1表示从周一开始计算,过了周一就加一周)。
- WEEKOFYEAR(date):日期中获取一年中的第几周,从周日开始计算,过了周日就加一周。
- DATEDIFF(expr1,expr2):两个时间表达式间隔时间(天)即两个时间差(天)。
- TIMEDIFF(expr1,expr2):两个时间差(相差多少个小时多少分钟多少秒钟)
- YEAR(date)、MONTH(date)、DAY(date)、WEEK(date)、DAYOFWEEK(date)、DAYNAME(date)、HOUR(time)、MINUTE(time)、SECOND(time):日期中的年、月、日、一年中过了几周(周日开始计算,周一加1周)、周中天(从周日开始为1,周一为2)、英文周中天、时、分、秒。
- UNIX_TIMESTAMP():返回1970-1-1到现在的秒数。
- FROM_UNIXTIME(unix_timestamp[,format]):把一个unix_timestamp的日期转为指定(format)格式的日期(format不写的话,默认为(%Y-%m-%d %H:%i:%s))
- STR_TO_DATE(str,format):字符串转时间格式。
- DATE_FORMAT(date,format):日期转字符串。
- DATE_ADD(date,INTERVAL expr unit):向日期添加指定的时间间隔。
- 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、数字函数
- ROUND(X)、ROUND(X,D):四舍五入,X就是要四舍五入的数,D就是要保留的位数,D不写的话就默认为0,即保留整数位。
- CEIL(X)、CEILING(X):向上取整。
- FLOOR(X):向下取整。
- MOD(N,M):求余数(N%M)。
- ABS(X):绝对值。
- RAND():[0,1)之间的随机数。
- TRUNCATE(X,D):数值截取,X:被截取的数值,D:截取超过D长度的小数位。
- 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、条件控制函数
- IF(expr1,expr2,expr3):expr1为true,返回expr2,否则返回expr3。
- IFNULL(expr1,expr2):如果expr1为null,就返回expr2,否则返回expr1。
- NULLIF(expr1,expr2):如果expr1和expr2相等,就返回null 否则返回expr1。
- 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、聚合函数
- SUM([DISTINCT] expr):求和。
- AVG([DISTINCT] expr):求平均值。
- MAX(expr)、MAX([DISTINCT] expr):求最大值。
- MIN(expr)、MIN([DISTINCT] expr):求最小值。
- 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、其他函数
- VERSION():查看版本信息
- DATABASE():当前数据库名称
- USER():当前用户
使用示例
select VERSION() 版本信息, DATABASE() 数据库名称, USER() 当前用户;
执行结果: