mysql常见函数使用

-- 时间操作
-- 时间单位 unit
-- MICROSECOND  SECOND  MINUTE  HOUR  DAY WEEK MONTH  QUARTER  YEAR
-- SECOND_MICROSECOND  MINUTE_MICROSECOND MINUTE_SECOND   HOUR_MICROSECOND
-- HOUR_SECOND  HOUR_MINUTE  DAY_MICROSECOND  DAY_SECOND
-- DAY_MINUTE  DAY_HOUR YEAR_MONTH

-- 返回两个日期之间的天数。
SELECT DATEDIFF('2008-12-30','2009-12-29') 
-- 返回2个时间的 单位差 
SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');  -- 返回 3

SELECT DATE(NOW())

-- 返回日期/时间的单独部分，比如年、月、日、小时、分钟等等
SELECT EXTRACT(YEAR FROM NOW())  -- 返回 2020
SELECT YEAR(NOW()) 
SELECT EXTRACT(DAY_HOUR FROM NOW())  -- 当前时间 如：15
SELECT HOUR(NOW())

SELECT EXTRACT(YEAR_MONTH FROM NOW())  -- 返回202012
SELECT MONTH(NOW())  -- 返回12
SELECT YEARWEEK(NOW())  -- 202048  年份星期
SELECT WEEK(NOW())  -- 返回48

SELECT DAY(NOW()) --  3  day of month
SELECT DAYOFYEAR(NOW())
SELECT DAYOFMONTH(NOW()) 


-- 函数 DATE_ADD(date,INTERVAL expr type)
SELECT DATE_ADD(NOW(),INTERVAL 2 DAY)  -- 返回 后天这个时候
SELECT DATE_SUB(NOW(),INTERVAL 2 DAY) -- 返回大前天
SELECT DATE_SUB(NOW(),INTERVAL -2 DAY) -- 返回后天这个时候

-- 时间格式化  DATE_FORMAT  https://www.w3school.com.cn/sql/func_date_format.asp
-- 请注意 英文的大小写 YmdHis   这个格式是最常见的
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')  -- 返回2020-12-03 15:43:02

-- 日期转时间戳  注意是十位的时间戳 
SELECT UNIX_TIMESTAMP(NOW());  //1606981613
-- 时间戳转日期  注意返回的时间格式
SELECT FROM_UNIXTIME(1606981613); 


-- 实战
-- 返回 今年第一天
SELECT DATE_SUB(CURDATE(),INTERVAL DAYOFYEAR(NOW())-1 DAY);  
SELECT DATE_SUB(NOW(),INTERVAL DAYOFYEAR(NOW())-1 DAY);   -- 带时间

-- 返回 今年最后一天
SELECT CONCAT(YEAR(NOW()),'-12-31');



-- 返回 当前月 第 一天  2个 格式不一样 
SELECT CONCAT(EXTRACT(YEAR_MONTH FROM NOW()),'01');

SELECT CONCAT(DATE_FORMAT(LAST_DAY(NOW()),'%Y-%m-'),'01');


--  返回 当前月最后一天
SELECT LAST_DAY(NOW())
SELECT LAST_DAY('2003-02-05');

#前一month的第一天：  
SELECT CONCAT(DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH),'%Y-%m-'),'01');

#前一month的最后一天：  
SELECT LAST_DAY(NOW() - INTERVAL 1 MONTH);


# 月环比 上个月同期 
SELECT DATE_ADD(NOW(),INTERVAL -1 MONTH)
SELECT DATE_ADD('2020-02-28',INTERVAL -1 MONTH)   -- 2月28日 环比 是1月28日 还是 1月31日 

# 同比 去年同期 
SELECT DATE_ADD(NOW(),INTERVAL -1 YEAR)



-- --------------------------------------  字符串相关操作
-- 字符串连接函数    注意有一个为空全为空 ，在开发的时候要注意
SELECT CONCAT('中','国','人')
SELECT CONCAT('1',NULL,'2') 


-- 用字符进行连接  注意 为 null 情况，不连接该字符串，而不是返回null
SELECT CONCAT_WS('-', '2020','10','20')
SELECT CONCAT_WS('-', '2020',NULL ,'20')


-- FIELD(s,s1,s2...) 返回第一个字符串 s 在字符串列表(s1,s2...)中的位置
SELECT FIELD('abc','a','b','abc','abcd')  --返回  3
-- FIND_IN_SET(s1,s2)  返回在字符串s2中与s1匹配的字符串的位置
SELECT FIND_IN_SET("c", "a,b,c,d,e");


-- LOCATE(s1,s)  从字符串 s 中获取 s1 的开始位置
SELECT LOCATE('my','myteststring');   -- 1  注意是从1 开始
SELECT LOCATE('abc','myteststring');   --  不存在 返回  0   不是-1 

-- 右补足
SELECT RPAD('abc',8,'xzzaaa');
-- 左补足
SELECT LPAD('abc',8,'xzzaXY');
SELECT LPAD('abc',8,'?');

-- 字符转小写
SELECT LCASE('aDFD位置D') 
SELECT LOWER('aDFD位置D')
-- 字符大写
SELECT UCASE('aDFD位置D') 
SELECT UPPER('aDFD位置D')


--  去掉左侧空格
SELECT LTRIM('  abd c ')
-- 去掉右侧空格
SELECT RTRIM('  abd c ')
-- 去掉2侧字符
SELECT TRIM('  abd c ')

-- 字符翻转
SELECT REVERSE('abcde ')

-- 返回 截取前几个字符
SELECT LEFT('abcdedf',2)
-- 返回 截取后几个字符
SELECT RIGHT('中国人开始字符',2)

-- SUBSTR(S) 截取 从 2  开始的3个字符   SUBSTR 和 SUBSTRING 用法一样
SELECT SUBSTR("abcdedfh", 2, 3)  -- bcd    从第一位开始是1 
SELECT SUBSTR("abcdedfh", -3, 3)  -- dfh  注意负号
SELECT SUBSTRING("abcdedfh", 2, 3)  -- bcd

-- 替换
SELECT REPLACE('abc','a','x') --xbc


--  -------------------------------- 条件判断
-- COALESCE(expr1, expr2, ...., expr_n)  expr_n)	返回参数中的第一个非空表达式（从左向右）
SELECT COALESCE(NULL, NULL, NULL, 'runoob.com', NULL, 'google.com');

-- IF(expr,v1,v2) 如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。
SELECT IF(1 > 0,'正确','错误')

SELECT 

-- IFNULL(v1,v2) 如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。
SELECT IFNULL(NULL,1)

-- 判断是否为空
SELECT ISNULL(NULL)   -- 返回1 

-- case when
SELECT CASE WHEN 1>0 THEN  '正确' ELSE '错误' END 

-- ------------------------------- 数字相关
--  取绝对值
 SELECT  ABS('-123.0')  -- 返回 123  注意小数点不见了
 
-- CEIL 向上取整
SELECT CEIL(1.5)  
-- 向下取整
SELECT FLOOR(1.7)
-- 四舍五入， 保留几位小数
SELECT ROUND(1.5)   
SELECT ROUND(1.5,0)  -- 等同上
SELECT ROUND(1.5,2)   -- 1.50
-- 不会四舍五入 
SELECT TRUNCATE(1.23456,3)

-- 数字格式化 ##,###.### 的格式化  带千分位符号 ，用来展示
SELECT FORMAT(12332.123456, 4);  -- 12,332.1235  
SELECT FORMAT(12332.1, 4);  -- 12,332.1000  补0


-- 返回最大值 ，注意不能为空  否则为空
SELECT GREATEST(3, 12, 34.2, 8, -1.0);
-- 返回最小值
SELECT LEAST(3, 12, 34.2, 8, -1.0);

-- 求模 整除  3/5
SELECT 6 DIV 5   -- 返回1
SELECT FLOOR(6/5) 

-- 取余
SELECT MOD(5.233,2)
SELECT 5.233%2

-- 0 - 1的随机数 
SELECT RAND()
SELECT RAND(1) -- 重复的随机数
SELECT RAND(1) FROM t_user  -- 每条记录 都是不一样的  t_user  是随便一张测试表

-- 判断是正数 负数 0
SELECT SIGN(1)
SELECT SIGN(0)
SELECT SIGN(-1)

-- rand() 使用场景  -- 这个查询效率低 特别数据数据量大的表 ，要小心，数据量少的没关系  一般随机取数的 可以在应用程序上除了
SELECT * FROM t_user  ORDER BY RAND() LIMIT 10  
SELECT * FROM (SELECT * FROM t_user LIMIT 10 ) a ORDER BY RAND()