MySQL-常用内置函数（字符串、数值、日期、流程）

字符串函数：

函数	作用
CONCAT(str1,str2,…)	拼接字符串，返回拼接后的字符串
LENGTH(str)	返回字符串字节长度，注意是字节，中文占3个
LEFT(str,len)	返回从最左边截取到len的字符串
RIGHT(str,len)	返回从最左边截取到len的字符串
SUBSTR(str,pos,len)	从str截取字符串，pos：起始索引，len：返回新的字符串长度
SUBSTRING(str,pos,len)	从str截取字符串，pos：起始索引，len：返回新的字符串长度
REPLACE(str,from_str,to_str)	替换字符串，将str中的from_str替换为to_str
LTRIM(str)	去掉字符串开头的空格
RTRIM(str)	去掉字符串结尾的空格
TRIM(str)	去掉字符串开头和结尾的空格
UPPER(str)	将字符串转换为大写
LOWER(str)	将字符串转换为小写
CHARSET(str)	查看字符串的字符集
INSTR(str,substr)	返回substr在str中首次出现的索引，没有则返回0
LPAD(str,len,padstr)	将padstr填充到最左边，使str的长度达到len，padstr为空则填充空格
RPAD(str,len,padstr)	将padstr填充到最右边，使str的长度达到len，padstr为空则填充空格
MD5(str)	使用MD5加密字符串并返回加密后的字符串

演示：

-- CONCAT()：拼接字符串，返回拼接后的字符串
SELECT CONCAT('Hello','World');   -- HelloWorld

-- LENGTH()：返回字符串字节长度，注意是字节，中文占3个
SELECT LENGTH('你好啊');   -- 9

-- LEFT()：返回从最左边截取到len的字符串
SELECT LEFT('HelloWorld',2);   -- He

-- RIGHT()：返回从最左边截取到len的字符串
SELECT RIGHT('HelloWorld',2);   -- ld

-- SUBSTR()、SUBSTRING()：从str截取字符串，pos：起始索引，len：返回新的字符串长度
SELECT SUBSTR('HelloWorld', 1, 5);   -- Hello
SELECT SUBSTRING('HelloWorld', 1, 5);   -- Hello

-- REPLACE()：将World替换为66666
SELECT REPLACE('HelloWorld', 'World', '66666');   -- Hello66666

-- LTRIM()、RTRIM()、TRIM()：分别去除字符串的前空格、后空格、前后空格
SELECT LTRIM('  HelloWorld ');   -- HelloWorld 
SELECT RTRIM('  HelloWorld ');   --   HelloWorld
SELECT TRIM('  HelloWorld ');   -- HelloWorld

-- UPPER() 和 LOWER()：分别将字符串转换为大写和小写
SELECT UPPER('hello');   -- HELLO
SELECT LOWER('HELLO');   -- hello

-- CHARSET()：获取字符串使用的字符集
SELECT CHARSET('Hello')   -- utf8mb3

-- INSTR()：返回l在Hello第一次出现的索引
SELECT INSTR('Hello','l');   -- 3

-- LPAD()：将0填充到最左边，使Hello的长度达到10为止
SELECT LPAD('Hello',10,'0');   -- 00000Hello

-- RPAD()：将0填充到最右边，使Hello的长度达到10为止
SELECT RPAD('Hello',10,'0');   -- Hello00000

-- 使用MD5加密字符串并返回加密后的字符串
SELECT MD5('嘻嘻');   -- aa068250325852a3478835e3acbd6ccd

数值函数：

函数	作用
ABS(X)	求绝对值
CEIL(X)	向上取整
FLOOR(X)	向下取整
MOD(N,M)	返回n/m的余数
RAND()	返回0~1之间的随机数
ROUND(X,Y)、FORMAT(X,D)	求x的四舍五入值，保留y位小数
POW(X,Y)、POWER(X,Y)	返回x的y次方
BIN(N)	十进制转二进制
HEX(N_or_S)	十进制转十六进制

演示：

-- ABS()：求绝对值
SELECT ABS(-3.14);   -- 3.14

-- CEIL()：向上取整
SELECT CEIL(3.14);   -- 4

-- FLOOR()：向下取整 
SELECT FLOOR(3.14);   -- 3

-- MOD()：求3/2的余数
SELECT MOD(3,2);   -- 1

-- RAND()：返回0~1之间的随机数
SELECT RAND();   -- 0.7683602997738498

-- ROUND()、FORMAT()：求x的四舍五入值，保留y位小数
SELECT ROUND(3.8989,3);   -- 3.899
SELECT FORMAT(3.8989,3);   -- 3.899

-- POW()、POWER()：返回x的y次方
SELECT POW(2,2);   -- 4
SELECT POWER(2,2);   -- 4

-- BIN()：十进制转二进制
SELECT BIN(2);   -- 10

-- HEX()：十进制转十六进制
SELECT HEX(2);   -- 2

生成验证码案例：

-- 6位数的验证码，所以*1000000变成大于0的小数
SELECT RAND() * 1000000; -- 586816.6224908239

SELECT ROUND(RAND() * 1000000,0); -- 586816

-- 但会出现0.012311这种小数，就会变成不满足6位数了，所以不满6位时要在前面或后面补0

SELECT LPAD(ROUND(RAND() * 1000000,0),6,'0'); -- 586816

日期函数：

函数	作用
NOW()	返回当前日期和时间
CURDATE()、CURRENT_DATE()	返回当前日期
CURTIME()、CURRENT_TIME()	返回当前时间
YEAR()	返回指定时间的年份
MONTH()	返回指定时间的月份
DAY()	返回指定时间的日期
DATE_ADD(date,INTERVAL expr unit)	返回加上expr后的日期/时间
DATEDIFF(expr1,expr2)	返回expr1 - expr2的天数

演示：

-- 返回当前日期和时间
SELECT NOW();   -- 2023-03-24 21:29:12

-- 返回当前日期
SELECT CURDATE();   -- 2023-03-24
SELECT CURRENT_DATE();   -- 2023-03-24

-- 返回当前时间
SELECT CURTIME();   -- 21:30:54
SELECT CURRENT_TIME();   -- 21:30:54

-- 返回指定时间的年、月、日
SELECT YEAR(NOW());   -- 2023
SELECT MONTH(NOW());   -- 3
SELECT DAY(NOW());   -- 24

-- 返回加上expr后的日期/时间
SELECT date_add(NOW(),INTERVAL 70 DAY);   -- 2023-06-02 21:48:37

-- 返回expr1 - expr2的天数
select DATEDIFF('2023-03-24','2033-03-24');   -- -3653

流程函数：

函数	作用
IF(value, true_value, false_value)	如果value为true，返回true_value，否则返回false_value
IFNULL(value1,value2)	如果value1不为空，返回value1，否则返回value2
CASE expr WHEN value1 THEN result1 WHEN value2 THEN result2 … ELSE default_value END	如果 expr 的值等于 value1，则返回 result1，如果等于 value2，则返回 result2，依此类推；如果没有匹配的值，则返回 default_value。
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 … ELSE default_value END	如果condition1成立，则返回 result1，如果条件condition2成立，则返回 result2，依此类推；如果没有条件成立，则返回 default_value

演示：

SELECT IF(1 > 0, '大于', '小于'); -- 大于

SELECT IFNULL('我不为空','不为空就返回我'); -- 我不为空
SELECT IFNULL('','不为空就返回我'); -- 还是返回空字符串   空字符 != NULL
SELECT IFNULL(NULL,'不为空就返回我'); -- 不为空就返回我

-- 每个WHEN都会和address对比，满足就会执行THEN，否则就执行ELSE
SELECT
	id,name,
    CASE address
        WHEN '打野' THEN '野王'
        WHEN '中单' THEN '法王'
        ELSE '啥也不是'
    END 
FROM Student;



-- 每个WHEN的条件满足后则执行THEN
SELECT
	id,name,
    CASE
        WHEN age >= 20 THEN '青年'
        WHEN age >= 30 THEN '中年'
        ELSE '老年'
    END AS age_group
FROM Student;