MYSQL常用函数

时间、格式转换

1、获取当前时间（datetime格式）

SELECT NOW(),SYSDATE() 

2、获取当前时间（timestamp格式）

SELECT CURRENT_TIMESTAMP() 

3、获取当前日期（date格式）、当前时间（time格式）

SELECT CURRENT_DATE(), CURRENT_TIME() 

4、日期格式化

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'), TIME_FORMAT(NOW(), '%H:%i:%s') 

5、秒和时间格式转换

SELECT SEC_TO_TIME(100), TIME_TO_SEC('00:01:40') 

6、时间戳和时间格式转换

SELECT UNIX_TIMESTAMP(NOW()), FROM_UNIXTIME(1681884663);  

7、日期、时间拼凑

SELECT MAKEDATE(2023,109), MAKETIME(14,9,22)

8、获取年、季、月、星期、日

SELECT YEAR('20230419'), QUARTER(NOW()), MONTH(NOW()), WEEK(NOW()), DAY(NOW())

9、获取时、分、秒

SELECT HOUR(NOW()), MINUTE(NOW()), SECOND(NOW()) 

10、获取星期、日相对数量

SELECT WEEKOFYEAR(NOW()), DAYOFYEAR(NOW()), DAYOFMONTH(NOW()), 
DAYOFWEEK(NOW()), WEEKDAY(NOW())

#注：20230419为星期三
#DAYOFWEEK	星期天=1，星期一=2， ... 星期六=7
#WEEKDAY	星期一=0，星期二=1， ... 星期日=6

11、时间的加减

SELECT SUBTIME('17:19:36', '15:56:23')

12、日的加减

SELECT SUBDATE('20230419', 1), SUBDATE('20230419', 0), SUBDATE('20230419', -1)

13、月的加减

SELECT PERIOD_ADD('202304', 2), PERIOD_ADD('202304', -2)

14、间隔的月数量

SELECT PERIOD_DIFF('202304', '202309'), PERIOD_DIFF('202309', '202304')

15、年、季、月、星期、日加减

SELECT DATE_ADD(NOW(),INTERVAL 1 YEAR) '加1年', 
DATE_ADD(NOW(),INTERVAL 1 QUARTER) '加1季', 
DATE_ADD(NOW(),INTERVAL '1 4' YEAR_MONTH) '加1年4个月', 
DATE_ADD(NOW(),INTERVAL 1 MONTH) '加1月', 
DATE_ADD(NOW(),INTERVAL 1 WEEK) '加1星期', 
DATE_ADD(NOW(),INTERVAL -1 DAY) '减1日'

16、小时、分钟、秒加减

SELECT DATE_ADD(NOW(), INTERVAL 1 HOUR) '加1小时',
DATE_ADD(NOW(), INTERVAL 1 MINUTE) '加1分钟',
DATE_ADD(NOW(), INTERVAL 1 SECOND) '加1秒',
DATE_ADD(NOW(), INTERVAL 1 MICROSECOND) '加1毫秒'

拼接、截取、字符串相关

1、字符串拼接（存在null值则结果为null）

SELECT CONCAT('HELLO','WORLD','!')

SELECT CONCAT('HELLO',NULL)

2、固定分隔符拼接（null值不影响数据拼接）

SELECT CONCAT_WS('~','HELLO','WORLD','!',NULL,'HAHAHA')

3、多行转一行

SELECT YEA '年',COUNT(DISTINCT MON) '月份个数', COUNT(1) AS '日期个数', 
GROUP_CONCAT(DISTINCT DT ORDER BY DT SEPARATOR ',' ) AS '日期列表'
FROM T_TEST GROUP BY YEA

4、字符串截取

SELECT LEFT(STR, 3) '截取前3位', 
RIGHT(STR, 3) '截取后3位', 
SUBSTRING(STR, 3) '从3开始截取', 
SUBSTRING(STR, -3) '从-3开始截取', 
SUBSTRING(STR, 3, 4) '从3开始截4位',
SUBSTRING_INDEX(URL, '.', 2) '到第二个.结束', 
SUBSTRING_INDEX(URL, '.', -1) '最后一个.到结束' FROM 
(SELECT '1234567890' STR, 'WWW.MYSQL.COM' URL) A

5、字符串长度、替换、大小写、转置

SELECT LENGTH(LETTER) '字符串长度', 
INSERT(LETTER, 1, 3, 'XYZ') '按位置替换字符串',
REPLACE(LETTER, 'a', 'w') '按字符替换字符串',
UPPER(LETTER) '转大写', 
LOWER(LETTER) '转小写',
TRIM(LETTER) '删除前后空格',
REVERSE(LETTER) '字符串反转' FROM 
(SELECT ' aaaBcD ' LETTER) A

6、字符串判断

SELECT IF(123 > 321, 1, 0), 
IFNULL(NULL, 'yes'), 
IFNULL('aaa', 'yes'),
CASE WHEN 123 > 321 THEN 1 ELSE 0 END 

运算

1、运算函数

SELECT ABS(NUM1) '绝对值', 
SQRT(NUM3) '开根号', 
MOD(NUM2, NUM3) '求余数',
CEIL(NUM4) '正数向上取整',
FLOOR(NUM4) '正数向下取整',
CEIL(NUM5) '负数向上取整',
FLOOR(NUM5) '负数向下取整',
POW(NUM3, NUM6) '幂运算' FROM 
(SELECT -177 NUM1, 895 NUM2, 4 NUM3, 6.39 NUM4, -5.66 NUM5, 3 NUM6) A

2、随机数

SELECT RAND() '随机数', 
RAND(1) '固定的随机数1', -- 传入整数参数时，用来产生重复序列
RAND(1) '固定的随机数2',
SIGN(NUM2) '正数的符号', 
SIGN(NUM1) '负数的符号'
FROM 
(SELECT -177 NUM1, 895 NUM2, 4 NUM3, 6.39 NUM4, -5.66 NUM5, 3 NUM6) A

2、三角形函数

SELECT PI() '圆周率',
SIN(0.5*PI()) '正弦值',
ASIN(0.8414709848078965) '反正弦值', -- 若 x 不在 -1 到 1 的范围之内，则返回 NULL
COS(0) '余弦值',
ACOS(1) '反余弦值', -- x 值的范围必须在 -1 和 1 之间，否则返回 NULL
TAN(0) '正切值',
ATAN(0) '反正切值',
COT(1) '余切值'

系统

1、查询数据库表信息

SELECT TABLE_NAME,CREATE_TIME,TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'lenos'

2、查询数据库表信息

SELECT TABLE_SCHEMA, TABLE_NAME , TABLE_ROWS ,
TRUNCATE ( DATA_LENGTH / 1024 / 1024, 2 ) '数据内存(MB)',
TRUNCATE ( INDEX_LENGTH / 1024 / 1024, 2 ) '索引内容(MB)' 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'fq_dw'