MYSQL事件使用日期函数

目录
1.日期格式转换函数
DATE_FORMAT()
STR_TO_DATE()
CURDATE() 与 NOW()
DATE_ADD() 与 DATE_SUB()
DATEDIFF() 与 TIMESTAMPDIFF()
2.字符串函数
CONCAT()
SUBSTRING()
REPLACE()
LENGTH() 与 CHAR_LENGTH()
UPPER() 与 LOWER()
3.聚合函数
COUNT()
SUM()
AVG()
MAX() 与 MIN()
GROUP_CONCAT()常见场景与示例总结

1. 日期格式转换函数

MySQL 提供了多种日期相关的函数，可以方便地对日期进行格式化、计算时间差以及加减时间。以下是常用的日期格式转换函数。

1.1 DATE_FORMAT()

DATE_FORMAT() 函数用于将日期格式化为指定的字符串格式。常见的日期格式化规则包括：

%Y: 4位的年份（例如：2024）
%m: 2位的月份（01-12）
%d: 2位的日期（01-31）
%H: 2位的小时（00-23）
%i: 2位的分钟（00-59）
%s: 2位的秒（00-59）
示例：

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

结果：

formatted_date
-------------------
2024-09-13 14:35:21

1.2 STR_TO_DATE()

STR_TO_DATE() 将字符串解析为日期格式，通常用于从字符串中提取日期。

示例：

SELECT STR_TO_DATE('2024-09-13 14:35:21', '%Y-%m-%d %H:%i:%s') AS date_value;

结果：

date_value
-------------------
2024-09-13 14:35:21

1.3 CURDATE()、CURTIME() 与 NOW()

CURDATE() 返回当前日期（不包含时间部分）。

CURTIME()返回当前时间
NOW() 返回当前的日期和时间。

SELECT CURDATE() AS current_date, NOW() AS current_datetime;

结果：

current_date | current_datetime
----------------|-------------------
2024-09-13 | 2024-09-13 14:35:21

1.4 DATE_ADD() 与 DATE_SUB()

函数形式：DATE_ADD(date,INTERVAL expr unit) —— DATE_SUB(date,INTERVAL expr unit)

date_sub参数参考date_add即可。

expr：表示时间间隔值

unit：单位，如下

MICROSECOND 间隔单位：毫秒
SECOND 间隔单位：秒
MINUTE 间隔单位：分钟
HOUR 间隔单位：小时
DAY 间隔单位：天
WEEK 间隔单位：星期
MONTH 间隔单位：月
QUARTER 间隔单位：季度
YEAR 间隔单位：年
SECOND_MICROSECOND 复合型，间隔单位：秒、毫秒，expr可以用两个值来分别指定秒和毫秒
MINUTE_MICROSECOND 复合型，间隔单位：分、毫秒
MINUTE_SECOND 复合型，间隔单位：分、秒
HOUR_MICROSECOND 复合型，间隔单位：小时、毫秒
HOUR_SECOND 复合型，间隔单位：小时、秒
HOUR_MINUTE 复合型，间隔单位：小时分
DAY_MICROSECOND 复合型，间隔单位：天、毫秒
DAY_SECOND 复合型，间隔单位：天、秒
DAY_MINUTE 复合型，间隔单位：天、分
DAY_HOUR 复合型，间隔单位：天、小时
YEAR_MONTH 复合型，间隔单位：年、月

DATE_ADD() 用于在日期上增加指定的时间间隔。
DATE_SUB() 用于在日期上减去指定的时间间隔。
SELECT DATE_ADD(CURDATE(), INTERVAL 7 DAY) AS next_week;
SELECT DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AS last_month;

结果：

next_week | last_month
----------------|------------
2024-09-20 | 2024-08-13

1.5 DATEDIFF() 与 TIMESTAMPDIFF()

DATEDIFF() 计算两个日期之间的天数差。
TIMESTAMPDIFF() 可计算任意单位的时间差（秒、分钟、小时、天等）。
SELECT DATEDIFF('2024-09-20', '2024-09-13') AS days_diff;
SELECT TIMESTAMPDIFF(HOUR, '2024-09-13 08:00:00', NOW()) AS hours_diff;

结果：

days_diff | hours_diff
------------|------------
7 | 6

1.6 转换日期

转换日期为对应格式

select day(now())；获取当前的日数

select month(now())；获取当前的月数

select year(now())；获取当前的年数

select hour(now())；获取当前的小时数

2. 字符串函数

字符串操作是数据库查询中常见的需求。MySQL 提供了丰富的字符串处理函数，用于连接、替换、截取和转换字符串。

2.1 CONCAT()

CONCAT() 函数用于将多个字符串连接成一个字符串。

SELECT CONCAT('Hello, ', 'World!') AS greeting;
使用字符串作为数据表名查询。

2.2使用字符串作为表名查询

SET @tablename =users ;
PREPARE StMt FROM 'SELECT * FROM ?';
SET @tablename =users
EXECUTE stmt USING @tablename;
DEALLOCATE PREPARE stmt;

2.3 SUBSTRING()

SUBSTRING() 函数用于截取字符串的子串。可以指定起始位置和长度。

SELECT SUBSTRING('abcdefg', 2, 3) AS sub_string;

结果：

sub_string
-----------
bcd

2.4REPLACE()

REPLACE() 函数用于将字符串中的指定子串替换为新的字符串。

SELECT REPLACE('Hello World', 'World', 'MySQL') AS replaced_string;

结果：

replaced_string
----------------
Hello MySQL

2.5 LENGTH() 与 CHAR_LENGTH()

LENGTH() 返回字符串的字节长度。
CHAR_LENGTH() 返回字符串的字符数（与字符编码相关）。
SELECT LENGTH('Hello') AS byte_length, CHAR_LENGTH('Hello') AS char_length;

结果：

byte_length | char_length
------------|------------
5 | 5

2.6 UPPER() 与 LOWER()

UPPER() 将字符串转换为大写，LOWER() 将字符串转换为小写。

SELECT UPPER('hello') AS upper_case, LOWER('HELLO') AS lower_case;

结果：

upper_case | lower_case
-----------|------------
HELLO | hello

3. 聚合函数

聚合函数用于对多行记录进行计算，常用于统计分析和报表生成。

3.1 COUNT()

COUNT() 用于计算查询结果的行数。

SELECT COUNT(*) AS total_rows FROM users;

结果：

total_rows
-----------
100

3.2 SUM()

SUM() 用于计算某列数值的总和。

SELECT SUM(salary) AS total_salary FROM employees;

结果：

total_salary
-------------
250000

3.3 AVG()

AVG() 用于计算某列数值的平均值。

SELECT AVG(salary) AS average_salary FROM employees;

结果：

average_salary
---------------
50000

3.4 MAX() 与 MIN()

MAX() 返回某列的最大值。
MIN() 返回某列的最小值。
SELECT MAX(salary) AS highest_salary, MIN(salary) AS lowest_salary FROM employees;

结果：

highest_salary | lowest_salary
---------------|---------------
100000 | 20000

3.5 GROUP_CONCAT()

GROUP_CONCAT() 用于将一组值连接成一个字符串，并以指定的分隔符分隔。

SELECT GROUP_CONCAT(username SEPARATOR ', ') AS user_list FROM users;

结果：

user_list
------------
alice, bob, charlie

4. 常见场景与示例

4.1 日期范围查询

假设你需要查询过去一个月内的订单：

SELECT * FROM orders
WHERE order_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND CURDATE();

4.2 统计用户数量

统计每个部门的员工数量：

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

4.3 合并用户姓名

假设你有一张用户表，想要将所有用户名拼接成一个字符串：

SELECT GROUP_CONCAT(username SEPARATOR ', ') AS all_usernames
FROM users;

5.定时任务事件语法

5.1开启事件功能

先检查是否开启事件功能：

show variables like 'event_scheduler';
如果’event_scheduler’的值为 'ON’表示调度器已开启，'OFF’为调度器未开启

开启事件：set global event_scheduler = on;
关闭事件：set global event_scheduler = off;

查看任务 SHOW EVENTS;
这个是查看现有任务，如果有任务只执行一次的话，执行之前是可以看到的，执行之后就没有了就查不到了。

5.2创建定时任务事件语法

CREATE
        [DEFINER = { user | CURRENT_USER }]
        EVENT
        [IF NOT EXISTS]
        event_name
        ON SCHEDULE schedule
        [ON COMPLETION [NOT] PRESERVE]
        [ENABLE | DISABLE | DISABLE ON SLAVE]
        [COMMENT 'comment']
        DO event_body;

时间有很多种的，

on schedule every 1 second         -- 每秒执行1次
on schedule every 2 minute         -- 每两分钟执行1次
on schedule every 3 day            -- 每3天执行1次
ON schedule every 1 day starts date_add(date_add(curdate(), interval 1 day), interval 1 hour)  -- 每天凌晨1点执行
ON schedule every 1 month starts date_add(date_add(date_sub(curdate(),interval day(curdate())-1 day),interval 1 month),interval 1 hour) -- 每个月的第一天凌晨1点执行

on schedule at current_timestamp()+interval 5 day     -- 5天后执行
on schedule at current_timestamp()+interval 10 minute -- 10分钟后执行
on schedule at '2016-10-01 21:50:00'                  -- 在2016年10月1日，晚上9点50执行
ON schedule EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + 1 WEEK -- 每 3 个月，从现在起一周后开始
ON schedule  EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK -- 每十二个小时，从现在起三十分钟后开始，并于现在起四个星期后结束

on schedule every 1 day starts current_timestamp()+interval 5 day ends current_timestamp()+interval 1 month -- 5天后开始每天都执行执行到下个月底
on schedule every 1 day ends current_timestamp()+interval 5 day -- 从现在起每天执行，执行5天

举例：

创建一个1分钟后清空student_point表数据的事件 
CREATE EVENT IF NOT EXISTS dsq1
on schedule at current_timestamp()+interval 1 minute -- 1分钟后执行
DO 
TRUNCATE TABLE student_point;

创建一个每天凌晨1点执行的任务
CREATE EVENT my_event
ON SCHEDULE
EVERY 1 DAY
STARTS date_add(date_add(curdate(), interval 1 day), interval 1 hour)
ON COMPLETION PRESERVE DO -- 这里是任务的具体操作，可以是SQL语句或存储过程 INSERT INTO my_table (column1, column2) VALUES ('value1', 'value2');

创建一个每隔3秒往test表中插入一条数据的事件 
CREATE EVENT IF NOT EXISTS e_test_1 
ON SCHEDULE 
EVERY 3 SECOND
ON COMPLETION PRESERVE
DO 
INSERT INTO test(id,t1) VALUES(NULL,NOW());

5.3查看全部事件

SHOW EVENTS;

5.4删除事件

DROP EVENT [IF EXISTS] event_name(事件名称);
例如：DROP EVENT event_name;

5.4使用事件实例　

#查看事件
show events;
#检查是否开启事件功能
show variables like 'event_scheduler';
#开启事件调度器
SET GLOBAL event_scheduler = ON;
#每3分钟定时删除202309_log表
DELIMITER $$
CREATE EVENT IF NOT EXISTS dellog_event
ON schedule every 3 minute starts date_add(now(),interval 3 minute) 
ON COMPLETION PRESERVE
DO BEGIN
SET @sql = CONCAT('drop table ',DATE_FORMAT(date_sub(curdate(),interval 1 month),'%Y%m'),'_log');
PREPARE stmt from @sql;
execute stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

参考：https://blog.csdn.net/fudaihb/article/details/142211537

https://www.cnblogs.com/qi-yuan-008/p/12782677.html

https://www.cnblogs.com/chaishengblog/p/18138557

posted @ 2024-09-18 19:34 风中追风h 阅读(110) 评论(0) 收藏举报

刷新页面返回顶部

我作为冒险家的一生:D

立刻行动！

MYSQL事件使用日期函数

1. 日期格式转换函数

1.1 DATE_FORMAT()

1.2 STR_TO_DATE()

1.3 CURDATE()、CURTIME() 与 NOW()

1.4 DATE_ADD() 与 DATE_SUB()

1.5 DATEDIFF() 与 TIMESTAMPDIFF()

1.6 转换日期

2. 字符串函数

2.1 CONCAT()

2.2使用字符串作为表名查询

2.3 SUBSTRING()

2.4REPLACE()

2.5 LENGTH() 与 CHAR_LENGTH()

2.6 UPPER() 与 LOWER()

3. 聚合函数

3.1 COUNT()

3.2 SUM()

3.3 AVG()

3.4 MAX() 与 MIN()

3.5 GROUP_CONCAT()

4. 常见场景与示例

4.1 日期范围查询

4.2 统计用户数量

4.3 合并用户姓名

5.定时任务事件语法

5.1开启事件功能

5.2创建定时任务事件语法

5.3查看全部事件

5.4删除事件

5.4使用事件实例

公告

我作为冒险家的一生:D

立刻行动！

MYSQL事件使用 日期函数

1. 日期格式转换函数

1.1 DATE_FORMAT()

1.2 STR_TO_DATE()

1.3 CURDATE()、CURTIME() 与 NOW()

1.4 DATE_ADD() 与 DATE_SUB()

1.5 DATEDIFF() 与 TIMESTAMPDIFF()

1.6 转换日期

2. 字符串函数

2.1 CONCAT()

2.2使用字符串作为表名查询

2.3 SUBSTRING()

2.4REPLACE()

2.5 LENGTH() 与 CHAR_LENGTH()

2.6 UPPER() 与 LOWER()

3. 聚合函数

3.1 COUNT()

3.2 SUM()

3.3 AVG()

3.4 MAX() 与 MIN()

3.5 GROUP_CONCAT()

4. 常见场景与示例

4.1 日期范围查询

4.2 统计用户数量

4.3 合并用户姓名

5.定时任务事件语法

5.1开启事件功能

5.2创建定时任务事件语法

5.3查看全部事件

5.4删除事件

5.4使用事件实例

公告

MYSQL事件使用日期函数

5.4使用事件实例　