mysql积累

日期+时间

# 常用日期+时间关键字
SELECT CURDATE();         # 当前日期，年月日
SELECT CURRENT_DATE;      # 当前日期，年月日
SELECT now();             # 当前时间，包含时分秒
SELECT CURRENT_TIME;      # 当前时间，包含时分秒
SELECT CURRENT_TIMESTAMP; # 当前时间，包含时分秒
SELECT CURRENT_USER;      # 当前用户，没啥时间关系，哈哈

# 日期提取
SELECT DATE('2003-12-31 01:02:03');       # 提取年月日
SELECT YEAR('2018-02-22');                # 年份
SELECT MONTH('2018-02-22');               # 月份
SELECT MONTHNAME('2018-02-22');           # 月份英文名
SELECT QUARTER('2018-05-22');             # 第几季度
SELECT YEARWEEK('2003-01-31 01:02:03');   # 年份和周
SELECT WEEKOFYEAR('2003-01-31 01:02:03'); # 第几周
SELECT WEEK('2003-01-31 01:02:03');       # 和WEEKOFYEAR一样
SELECT DAY('2018-02-22 00:00:00');        # 返回月中第几天
SELECT DAYOFYEAR('2018-02-22 10:30:42');  # 一年中的第几天
SELECT DAYOFMONTH('2018-02-22 00:00:00'); # 对应月里面第几天，和DAY一样

# 时间提取
SELECT TIME('2018-02-22 10:30:42');       # 提取时分秒
SELECT HOUR('2018-02-22 10:30:42');       # 小时
SELECT MINUTE('2018-02-22 10:30:42');     # 分钟
SELECT SECOND('2018-02-22 10:30:42');     # 秒

# 提取公式：EXTRACT(unit FROM date) 从日期中提取特定单位的值
SELECT EXTRACT(YEAR FROM '2022-05-27');   # 年
SELECT EXTRACT(MONTH FROM '2022-05-27');  # 月
SELECT EXTRACT(DAY FROM '2022-05-27');    # 日
SELECT EXTRACT(HOUR FROM '2022-05-27');   # 时
SELECT EXTRACT(HOUR FROM now());          # 时
SELECT EXTRACT(MINUTE FROM now());        # 分
SELECT EXTRACT(SECOND FROM now());        # 秒

# 日期差值
# 公式DATEDIFF(expr1,expr2)，TIMEDIFF(expr1,expr2)
# DATEDIFF结果正数是expr1比expr2多几天，负数则反之
# TIMEDIFF只能expr1时间大于expr2，要不报错
SELECT DATEDIFF('2018-02-22','2018-03-22');
SELECT DATEDIFF('2010-11-30 13:59:59','2010-11-30 14:59:59');
SELECT DATEDIFF('2010-11-30 13:59:59','2010-11-30');
SELECT TIMEDIFF('2010-11-30 15:59:59','2010-11-30 14:59:59'); 


# 时间计算
# (1)第一个位置是符号位，默认是+
# (2)时分秒之间是:
# (3)ADDDATE和DATE_ADD一样,SUBDATE和DATE_SUB一样
# (4)DATE_ADD和DATE_SUB反向计算
# (5)公式DATE_ADD(date,INTERVAL expr unit)
# (6)公式ADDTIME(expr1,expr2)
# (7)unit左侧补齐补0，例如：unit=HOUR_SECOND，expr='2:3',补齐为'0:2:3'
SELECT ADDDATE(now(), INTERVAL 1 YEAR);                              # 加1年               
SELECT DATE_ADD(now(), INTERVAL 1 YEAR);                             # 加1年
SELECT DATE_ADD('2023-12-13', INTERVAL 1 MONTH);                     # 加1月
SELECT DATE_ADD('2023-12-13 13:19:29',INTERVAL 1 DAY);               # 加1天
SELECT DATE_ADD('2023-12-13 13:19:29',INTERVAL '1 1' YEAR_MONTH);    # 加1年、1月
SELECT DATE_ADD('2023-12-13 13:19:29',INTERVAL '1 10' DAY_HOUR);     # 加1天、10时
SELECT DATE_ADD('2023-12-13 13:19:29',INTERVAL '1 1:2' DAY_MINUTE);  # 加1天、1时、2分
SELECT DATE_ADD('2023-12-13 13:19:29',INTERVAL '1 1:2:3' DAY_SECOND);# 加1天、1时、2分、3秒
SELECT DATE_ADD('2023-12-13 13:19:29',INTERVAL '1:2' HOUR_MINUTE);    # 加1时、2分
SELECT DATE_ADD('2023-12-13 13:19:29',INTERVAL '1:2:3' HOUR_SECOND);  # 加1时、2分、3秒
SELECT DATE_ADD('2023-12-13 13:19:29',INTERVAL '2:3' MINUTE_SECOND);  # 加2分、3秒
SELECT DATE_ADD('2023-12-13 13:19:29',INTERVAL '-1 1:2:3' DAY_SECOND);# 减1天、1时、2分、3秒
SELECT DATE_SUB('2023-12-13 13:19:29',INTERVAL '1 1' DAY_HOUR);       # 减1天、1时
SELECT DATE_SUB('2023-12-13 13:19:29',INTERVAL '-1 +1' DAY_HOUR);     # 加1天、1时
SELECT SUBDATE('2023-12-13 13:19:29',INTERVAL '-1 +1' DAY_HOUR);      # 减1天、1时

SELECT ADDTIME('2023-12-13 13:19:29','1:2:3');                        # 加1时、2分、3秒
SELECT ADDTIME('2023-12-13 13:19:29','-1:2:3');                       # 减1时、2分、3秒
SELECT ADDTIME('2023-12-13 13:19:29','0:0:3');                        # 加3秒
SELECT ADDTIME('2023-12-13 13:19:29','3');                            # 加3秒
SELECT ADDTIME('2023-12-13 13:19:29','2:3');                          # 2分、3秒
SELECT SUBTIME('2023-12-13 13:19:29','1:2:3');                        # 减1时、2分、3秒


# 公式：格式化DATE_FORMAT(date,format)
SELECT DATE_FORMAT('2023-12-13 22:23:00', '%Y/%m/%d');  # 输出格式：2023/12/13
SELECT DATE_FORMAT('2023-12-13 22:23:00', '%H:%i:%s');  # 输出格式：22:23:00
 

截取+拼接

# 公式SUBSTRING(column_name, start_position, length)，截取column_name值，从第start_position开始，截取length长度的字符串
# 公式LEFT(string, length)，截取string，从左截取length长度的字符串
# 公式CONCAT_WS(separator,string1,string2,…)
# 公式GROUP_CONCAT(）
SELECT LEFT('2023-04-08', 7);
SELECT RIGHT('2023-04-08', 7);
SELECT SUBSTRING('2023-04-08', 7);
SELECT SUBSTRING('2023-04-08', 3, 3);
SELECT CONCAT('Hello', ' ', 'World');
SELECT CONCAT_WS( '#','Hello', 'World','!');
SELECT is_valid, GROUP_CONCAT(cust_key SEPARATOR '#')
FROM dt_phone_blacklist
WHERE id >275
GROUP BY is_valid;

distinct

# distinct 多个字段
SELECT DISTINCT task_status,task_status_desc FROM dt_risk_account_apply

case...when

公式：case...when...when...else...

# 字段A不为空，不=2的情况下加和值字段B
SELECT
  SUM(
    CASE
      WHEN 字段A != '2' THEN 字段B
      WHEN 字段A IS NULL THEN 字段B
      ELSE 0
    END
  ) AS 字段C
FROM
  表名

count(1)

工作中发现的bug

修改后：

rand

SELECT RAND();                   # 0-1之间随机数
SELECT FLOOR(RAND());            # 向下取整
SELECT CEIL(rand());             # 向上取整
SELECT CEILING(3.44);            # 向上取整
SELECT ROUND(3.44);              # 精度限制=0           
SELECT ROUND(3.4455667788,3);    # 精度限制=3   
SELECT MD5(RAND() * 10000)       # md5值   

工作遇到的问题：

1、int(1)和int(10)

提工单修改字段语句

ALTER TABLE `bs_org`
    ADD COLUMN `new_col` int(1) DEFAULT 0 NOT NULL COMMENT 'int(1)和int(10)区别';

这里的int(1)是不是不够用呢？？？？？？

知识点：mysql中的int(1)和int(10)的区别-CSDN博客

mysql中int占4个字节（32位），最小值：-2147483648（-2的31次方），最大值：2147483647（2的31次方-1），只有和