MySQL函数
日期函数
获取\提取日期
-
获取当前日期时间
NOW(); DATE(); CURDATE(); CURTIME(); CURRENT_DATE(); CURRENT_TIME(); CURRENT_TIMESTAMP(); # 获取utc时间 select utc_timestamp(); select utc_date(); select utc_time();
-
分别提取其年、月、日、时、分、秒
# date中提取 SELECT EXTRACT(YEAR FROM NOW()); -- 2022 SELECT EXTRACT(QUARTER FROM NOW()); -- 2 SELECT EXTRACT(MONTH FROM NOW()); -- 5 SELECT EXTRACT(WEEK FROM NOW()); -- 20 SELECT EXTRACT(DAY FROM NOW()); -- 17 SELECT EXTRACT(HOUR FROM NOW()); -- 14 SELECT EXTRACT(MINUTE FROM NOW()); -- 25 SELECT EXTRACT(SECOND FROM NOW()); -- 0 SELECT EXTRACT(MICROSECOND FROM NOW()); -- 0 SELECT EXTRACT(YEAR_MONTH FROM NOW()); -- 202205 SELECT EXTRACT(DAY_HOUR FROM NOW()); -- 14 SELECT EXTRACT(DAY_MINUTE FROM NOW()); -- 1426 SELECT EXTRACT(DAY_SECOND FROM NOW()); -- 142646 SELECT EXTRACT(DAY_MICROSECOND FROM NOW()); -- 142714000000 SELECT EXTRACT(HOUR_MINUTE FROM NOW()); -- 1427 SELECT EXTRACT(HOUR_SECOND FROM NOW()); -- 142746 SELECT EXTRACT(HOUR_MICROSECOND FROM NOW()); -- 142755000000 SELECT EXTRACT(MINUTE_SECOND FROM NOW()); -- 2808 SELECT EXTRACT(MINUTE_MICROSECOND FROM NOW()); -- 2814000000 SELECT EXTRACT(SECOND_MICROSECOND FROM NOW()); -- 52000000 # 或者从日期格式字符串中提取取 SELECT EXTRACT(SECOND FROM '2022-03-06 11:12:13.123456');
-
其他提取函数
SELECT DAYOFWEEK(NOW()); -- 周几 SELECT DAYOFMONTH(NOW()); -- 几月 SELECT DAYOFYEAR(NOW()); -- 某天 #获取本月最后一天 select last_day('2022-05-31 14:15:20'); -- 2022-05-31 select last_day(now()); # WEEK SELECT WEEK(NOW()); -- 第几周 SELECT WEEK(NOW(), 3); -- 三天后是第几周 SELECT WEEKOFYEAR(NOW()); -- 一年中的第几周 SELECT DAYOFWEEK(NOW()); -- “某天”在一周中的位置 SELECT WEEKDAY(NOW()); -- 3 SELECT YEARWEEK(NOW()); -- 那年的第几周 202043 #返回星期和月份名称函数 SELECT DAYNAME(NOW()); -- Tuesday SELECT MONTHNAME(NOW()); -- May
日期转换
TIMESTAMP(‘date_str’)
SELECT TIMESTAMP('2022-03-01 01:00:00'); -- date to timestamp
SELECT TIMESTAMP('2022-03-01 01:00:00', '01:12:12');-- 2022-03-01 02:12:12 日期 + 时间
STR_TO_DATE(date_str,format)
# 字符串转日期
SELECT STR_TO_DATE('06.01.2022 18:22:30', '%m.%d.%Y %H:%i:%s'); -- 2022-06-01 18:22:30
SELECT STR_TO_DATE('06/01/2022', '%m/%d/%Y'); -- 2022-06-01
SELECT STR_TO_DATE('2022/12/3','%Y/%m/%d'); -- 2022-12-03
SELECT STR_TO_DATE('20:09:30', '%h:%i:%s'); -- NULL(超过12时的小时用小写h,得到的结果为NULL)
date_format(date,format)
# 日期格式化输出
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
#输出 2022-05-17 14:02:17,%Y四位数年份,%m两位数月份,%d两位数月中天数,01,07,11等等,
# %H 两位数字形式的小时,24 小时(00,01, …, 23)
SELECT TIME_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');#这个只能转换时间,输出0000-00-00 15:20:23
format模型转换格式符号含义。
- %S, %s 两位数字形式的秒( 00,01, …, 59)
- %I, %i 两位数字形式的分( 00,01, …, 59)
- %H 两位数字形式的小时,24 小时(00,01, …, 23)
- %h 两位数字形式的小时,12 小时(01,02, …, 12)
- %k 数字形式的小时,24 小时(0,1, …, 23)
- %l 数字形式的小时,12 小时(1, 2, …, 12)
- %T 24 小时的时间形式(hh:mm:ss)
- %r 12 小时的时间形式(hh:mm:ss AM 或hh:mm:ss PM)
- %p AM或PM
- %W 一周中每一天的名称(Sunday, Monday, …, Saturday)
- %a 一周中每一天名称的缩写(Sun, Mon, …, Sat)
- %d 两位数字表示月中的天数(00, 01,…, 31)
- %e 数字形式表示月中的天数(1, 2, …, 31)
- %D 英文后缀表示月中的天数(1st, 2nd, 3rd,…)
- %w 以数字形式表示周中的天数( 0 = Sunday, 1=Monday, …, 6=Saturday)
- %j 以三位数字表示年中的天数( 001, 002, …, 366)
- %U 周(0, 1, 52),其中Sunday 为周中的第一天
- %u 周(0, 1, 52),其中Monday 为周中的第一天
- %M 月名(January, February, …, December)
- %b 缩写的月名( January, February,…, December)
- %m 两位数字表示的月份(01, 02, …, 12)
- %c 数字表示的月份(1, 2, …, 12)
- %Y 四位数字表示的年份
- %y 两位数字表示的年份
- %% 直接值“%”
日期计算
date_add
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY); -- 加一天
SELECT DATE_ADD(NOW(), INTERVAL 1 HOUR); -- 加一小时
SELECT DATE_ADD(NOW(), INTERVAL 1 MINUTE); -- 加一分
SELECT DATE_ADD(NOW(), INTERVAL 1 SECOND); -- 加一秒
SELECT DATE_ADD(NOW(), INTERVAL 1 MICROSECOND); -- 加一毫秒
SELECT DATE_ADD(NOW(), INTERVAL 1 WEEK); -- 加一周
SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH); -- 加一月
SELECT DATE_ADD(NOW(), INTERVAL 1 QUARTER); -- 加一季度
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR); -- 加一年
# 参数可以为-,为负值时,加变成减
SELECT DATE_ADD(NOW(), INTERVAL -1 DAY); -- 减一天
SELECT DATE_ADD(NOW(), INTERVAL '02:30:30' HOUR_SECOND); -- 加02:30:30
SELECT DATE_ADD(NOW(), INTERVAL '1 02:30:30' DAY_SECOND); -- 加1天02:30:30
date_sub()
SELECT DATE_SUB(NOW(), INTERVAL '1 1:1:1' DAY_SECOND);-- 减去1天1:1:1
TIMESTAMPDIFF(),
#计算两个日期的差值,返回参数 1 减去参数 2 的值
SELECT TIMESTAMPDIFF(SECOND , '2022-05-17 14:59:00', NOW());
YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
MICROSECOND
DATEDIFF()
select DATEDIFF('1997-12-31 23:01:59','1997-12-30'); -- 结果:1
SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31'); -- 结果:-31
timediff()
SELECT TIMEDIFF('2000:01:01 00:00:00', '2000:01:01 00:00:00.000001');
-- '-00:00:00.000001'
SELECT TIMEDIFF('1997-12-31 23:59:59.000001', '1997-12-30 01:01:01.000002');
-- '46:58:57.999999'
日期函数表
字符串函数
SELECT LENGTH('s'); -- 计算字符串长度函数,返回字符串的字节长度
SELECT CONCAT('s1', 's2', 'sn'); -- 合并字符串函数,如果其中一个为 null,返回null
SELECT INSERT('123456', 1, 3, 'instr'); -- 将字符串str从第x位置开始,y个字符长的子串替换为字符串instr,返回结果
SELECT LOWER('STR'); -- 将字符串中的字母转换为小写
SELECT UPPER('str'); -- 将字符串中的字母转换为大写
SELECT LEFT('12345678', 3); -- 返回字符串str中最左边的x个字符
SELECT RIGHT('87654321', 3); -- 返回字符串str中最右边的x个字符
SELECT TRIM(' str '); -- 删除字符串左右两侧的空格
SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');-- 删除字符串左边字符
SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); -- 删除字符串两边字符
SELECT TRIM(TRAILING 'x' FROM 'xxxbarxxx'); -- 删除字符串右边字符
SELECT REPLACE('source_str', 'so','替换so后:'); -- 字符串替换函数,返回替换后的新字符串
SELECT SUBSTRING('123456789', 5, 2); -- 截取字符串,返回从指定位置开始到指定长度的字符
SELECT REVERSE('123456'); -- 返回颠倒字符串str的结果
流程控制函数
- IF(test,t,f) 如果test是真,返回t;否则返回f
- IFNULL(arg1,arg2) 如果arg1不是空,返回arg1,否则返回arg2
- NULLIF(arg1,arg2) 如果arg1=arg2返回NULL;否则返回arg1
- CASE WHEN[test1] THEN [result1]…ELSE [default] END 如果testN是真,则返回resultN,否则返回default
- CASE [test] WHEN[val1] THEN [result]…ELSE [default]END 如果test和valN相等,则返回resultN,否则返回default
加密函数
- ENCRYPT(str,salt) 使用UNIXcrypt()函数,用关键词salt(一个可以惟一确定口令的字符串,就像钥匙一样)加密字符串str
- ENCODE(str,key) 使用key作为密钥加密字符串str,调用ENCODE()的结果是一个二进制字符串,它以BLOB类型存储
- MD5() 计算字符串str的MD5校验和
- PASSWORD(str) 返回字符串str的加密版本,这个加密过程是不可逆转的,和UNIX密码加密过程使用不同的算法。
- SHA() 计算字符串str的安全散列算法(SHA)校验和
聚合函数
- COUNT(col) 统计查询结果的行数
- MIN(col) 查询指定列的最小值
- MAX(col) 查询指定列的最大值
- SUM(col) 求和,返回指定列的总和
- AVG(col) 求平均值,返回指定列数据的平均值
数值函数
-
计算函数
SELECT ABS(1); -- 返回x的绝对值 SELECT BIN(2); -- 返回x的二进制 SELECT CEILING(3); -- 返回大于x的最小整数值 SELECT EXP(2); -- 返回值e(自然对数的底)的x次方 SELECT FLOOR(3); -- 返回小于x的最大整数值 SELECT GREATEST(1,2,8,3); -- 返回集合中最大的值 SELECT LEAST(5,1,9,3); -- 返回集合中最小的值 SELECT LN(4); -- 返回x的自然对数 SELECT LOG(9,3); -- 返回x的以y为底的对数 SELECT MOD(8,3); -- 返回x/y的模(余数) SELECT PI(); -- 返回pi的值(圆周率) SELECT RAND(); -- 返回0到1内的随机值,可以通过提供一个参数(种子)-- 使RAND()-- 随机数生成器生成一个指定的值
-
保留小数
SELECT ROUND(5.5555,2); -- 返回参数x的四舍五入的有y位小数的值 SELECT TRUNCATE(5.5555,2); -- 返回数字x截短为y位小数的结果 SELECT FORMAT(20.123456, 2); -- 20.13 四舍五入,保留2位小数。