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个字符。
使用示例:
SELECT CONCAT( '字符串-' , '拼接-' , '在一起' ) 字符串拼接;
SELECT CHAR_LENGTH( '你好' ) 字符长度方式一, CHARACTER_LENGTH( ' 你好 ' ) 字符长度方式二;
SELECT LENGTH( 'hello' ) en字节长度, LENGTH( '你好' ) cn字节长度;
SELECT SUBSTR( 'string' , 1 , 3 ) substr截取字符串, SUBSTRING( 'string' , 4 , 3 ) substring截取字符串;
SELECT INSTR( 'hello' , 'oo' ) hello是否包含oo;
SELECT IF ( INSTR( 'hello' , 'llo' ) > 0 , '包含' , '不包含' ) hello是否包含llo;
SELECT TRIM( ' str ' ) 去除字符串前后空格;
SELECT LTRIM( ' str ' ) 去除字符串左边的空格;
SELECT RTRIM( ' 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字符;
SELECT STRCMP( '2022-02' , '2022-01' ) 时间比较;
SELECT IF ( STRCMP( '2022-02' , '2022-04' ) >= 0 , '大于等于' , '小于' ) '2022-02与2022-01时间比较结果' ;
SELECT UPPER( 'to upper' ) 字母变大写方式一,
UCASE ( 'to upper' ) 字母变大写方式二;
SELECT LOWER( 'TO LOWER' ) 字母变小写方式一,
LCASE ( 'TO LOWER' ) 字母变小写方式二;
SELECT REPLACE ( 'hello lcb' , 'o' , 'o -' ) ;
SELECT LPAD( '8' , 2 , '1' ) ;
SELECT RPAD( '8' , 2 , '1' ) ;
select LEFT ( 'hello' , 2 ) ;
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函数。
select NOW ( ) , CURRENT_TIMESTAMP ( ) , SYSDATE( ) ;
select CURRENT_DATE ( ) , DATE ( NOW ( ) ) ;
select CURTIME( ) , CURRENT_TIME ( ) ;
select YEAR ( NOW ( ) ) , MONTH ( NOW ( ) ) , MONTHNAME( NOW ( ) ) , DAYOFMONTH( NOW ( ) ) , DAY ( NOW ( ) ) ;
SELECT WEEKDAY( NOW ( ) ) , WEEKDAY( STR_TO_DATE( '2022-07-09' , '%Y-%m-%d' ) ) ;
select DAYOFWEEK( '2022-07-11' ) ;
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' ) ;
select YEAR ( NOW ( ) ) , MONTH ( NOW ( ) ) , DAY ( NOW ( ) ) , WEEK( NOW ( ) ) , DAYOFWEEK( NOW ( ) ) , DAYNAME( NOW ( ) ) , HOUR ( NOW ( ) ) , MINUTE ( NOW ( ) ) , SECOND ( NOW ( ) ) ;
select UNIX_TIMESTAMP( ) ;
select FROM_UNIXTIME( 1675739986 ) , FROM_UNIXTIME( 1675739986 , '%Y/%m/%d %H:%i:%s' ) ;
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' ) ;
select DATE_FORMAT( '2022-07-11' , '%Y年%c月%d日' ) ;
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位小数。
使用示例
select ROUND ( 10.19 ) , ROUND ( 10.19 , 1 ) ;
select CEIL( 10.333 ) , CEILING( 10.333 ) ;
select FLOOR( 10.9 ) ;
SELECT MOD ( 10 , 3 ) ;
select ABS( - 6 ) ;
select RAND( ) ;
select TRUNCATE ( 1055 , 1 ) , TRUNCATE ( 1055.99 , 1 ) ;
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功能。
使用示例
select IF ( TRUE , 1 , 0 ) , IF ( FALSE , 1 , 0 ) ;
select IFNULL( 1 , 2 ) , IFNULL( null , 2 ) ;
select NULLIF ( 1 , 2 ) , NULLIF ( 2 , 2 ) ;
select IFNULL( NULLIF ( 1 , 2 ) , '空' ) , IFNULL( NULLIF ( 2 , 2 ) , '空' ) ;
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;
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 ( ) 当前用户;
执行结果: