SQL脚本-12

1.基础操作

1.1插入数据

插入记录的方式汇总：

• 普通插入（全字段）：INSERT INTO table_name VALUES (value1, value2, …)
• 普通插入（限定字段）：INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …)
• 多条一次性插入：INSERT INTO table_name (column1, column2, …) VALUES (value1_1, value1_2, …), (value2_1, value2_2, …), …
• 从另一个表导入：INSERT INTO table_name SELECT * FROM table_name2 [WHERE key=value]
• 替换插入（带有主键或唯一索引）：REPLACE INTO table_name VALUES (value1, value2, …)

1.1.1插入记录（一）

牛客后台会记录每个用户的试卷作答记录到exam_record表，现在有两个用户的作答记录详情如下：

用户1001在2021年9月1日晚上10点11分12秒开始作答试卷9001，并在50分钟后提交，得了90分；
用户1002在2021年9月4日上午7点1分2秒开始作答试卷9002，并在10分钟后退出了平台。
试卷作答记录表exam_record中，表已建好，其结构如下，请用一条语句将这两条记录插入表中。

【输入】

drop table if EXISTS exam_record;
CREATE TABLE IF NOT EXISTS exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
TRUNCATE exam_record;

【操作】

insert into exam_record
(uid,exam_id,start_time,submit_time,score) 
values(1001,9001,'2021-09-01 22:11:12','2021-09-01 23:01:12',90),
(1002,9002,'2021-09-04 07:01:02',null,null)
;

1.1.2插入记录（二）

现有一张试卷作答记录表exam_record，结构如下表，其中包含多年来的用户作答试卷记录，由于数据越来越多，维护难度越来越大，需要对数据表内容做精简，历史数据做备份。

我们已经创建了一张新表exam_record_before_2021用来备份2021年之前的试题作答记录，结构和exam_record表一致，请将2021年之前的已完成了的试题作答纪录导入到该表。
后台会通过执行"SELECT * FROM exam_record_before_2021;"语句来对比结果

【输入】

drop table if EXISTS exam_record;
CREATE TABLE IF NOT EXISTS exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE IF NOT EXISTS exam_record_before_2021 (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
TRUNCATE exam_record;
TRUNCATE exam_record_before_2021;
INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-01 09:00:01', null, null),
(1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 70),
(1001, 9002, '2020-09-02 09:00:01', null, null),
(1002, 9001, '2021-05-02 10:01:01', '2021-05-02 10:30:01', 81),
(1002, 9002, '2021-09-02 12:01:01', null, null);

【操作】

insert into exam_record_before_2021 
(uid, exam_id, start_time, submit_time, score)
select 
uid, exam_id, start_time, submit_time, score
from exam_record
where substring(submit_time,1,4) < '2021'
-- YEAR(submit_time) < '2021'
;

1.1.3插入记录（三）

现在有一套ID为9003的高难度SQL试卷，时长为一个半小时，请你将 2021-01-01 00:00:00 作为发布时间插入到试题信息表examination_info（其表结构如下图），不管该ID试卷是否存在，都要插入成功，请尝试插入它。

【输入】

drop table if EXISTS examination_info;
CREATE TABLE IF NOT EXISTS examination_info (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
tag varchar(32) COMMENT '类别标签',
difficulty varchar(8) COMMENT '难度',
duration int NOT NULL COMMENT '时长(分钟数)',
release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_bin;
TRUNCATE examination_info;
INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
(9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'),
(9002, '算法', 'easy', 60, '2020-01-01 10:00:00'),
(9003, 'SQL', 'medium', 60, '2020-01-02 10:00:00'),
(9004, '算法', 'hard', 80, '2020-01-01 10:00:00');

【操作】

replace INTO examination_info
VALUES (null,9003,'SQL','hard',90,'2021-01-01 00:00:00')
;

replace into 跟 insert into功能类似，不同点在于：replace into 首先尝试插入数据到表中，

1. 如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据；
2. 否则，直接插入新数据。

要注意的是：插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。

1.2更新记录

修改记录的方式汇总：

• 根据指定条件更新：利用where条件一行一行查找列col种等于str1的字符替换成str2

  update table set col = str2 where col = str1
  

• 替换指定值：使用replace默认表col列所有满足要求的str1都替换为str2

  update table set col = replace(col,str1,str2)
  

1.2.1更新记录（一）

【输入】

drop table if EXISTS examination_info;
CREATE TABLE IF NOT EXISTS examination_info (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
tag varchar(32) COMMENT '类别标签',
difficulty varchar(8) COMMENT '难度',
duration int NOT NULL COMMENT '时长',
release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_bin;
TRUNCATE examination_info;
INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
(9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'),
(9002, 'python', 'easy', 60, '2020-01-01 10:00:00'),
(9003, 'Python', 'medium', 80, '2020-01-01 10:00:00'),
(9004, 'PYTHON', 'hard', 80, '2020-01-01 10:00:00');

【操作】

-- 语句一
update examination_info
set tag = 'Python'
where tag = 'PYTHON'
;
-- 语句二
update examination_info
set tag = replace（tag,'PYTHON','Python')
;

1.2.2更新记录（二）

【输入】

drop table if EXISTS exam_record;
CREATE TABLE IF NOT EXISTS exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80),
(1001, 9002, '2021-09-01 09:01:01', '2021-09-01 09:21:01', 90),
(1002, 9001, '2021-08-02 19:01:01', null, null),
(1002, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
(1003, 9001, '2021-09-02 12:01:01', null, null),
(1003, 9002, '2021-09-01 12:01:01', null, null);

【操作】

update exam_record
set submit_time = '2099-01-01 00:00:00',
    score = '0'
where substring(start_time,1,10) < '2021-09-01'
and submit_time is null;

1.3删除记录

删除记录的方式汇总：

• 根据条件删除：DELETE FROM tb_name [WHERE options][ [ ORDER BY fields ] LIMIT n ]
• 全部删除（表清空，包含自增计数器重置）：TRUNCATE tb_name

时间差：

• TIMESTAMPDIFF(interval, time_start, time_end)可计算time_start-time_end的时间差，单位以指定的interval为准，常用可选：
  • SECOND 秒
  • MINUTE 分钟（返回秒数差除以60的整数部分）
  • HOUR 小时（返回秒数差除以3600的整数部分）
  • DAY 天数（返回秒数差除以3600*24的整数部分）
  • MONTH 月数
  • YEAR 年数

1.3.1删除记录（一）

【输入】

drop table if EXISTS exam_record;
CREATE TABLE IF NOT EXISTS exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
TRUNCATE exam_record;
INSERT INTO exam_record(uid, exam_id, start_time, submit_time, score) VALUES
(1001, 9001, '2020-01-01 22:11:12', '2020-01-01 23:16:12', 50),
(1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:06:00', 58),
(1002, 9001, '2021-05-02 10:01:01', '2021-05-02 10:05:58', 60),
(1002, 9002, '2021-06-02 19:01:01', '2021-06-02 19:05:01', 54),
(1003, 9001, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 49),
(1003, 9001, '2021-09-05 19:01:01', '2021-09-05 19:15:01', 70),
(1003, 9001, '2021-09-06 19:01:01', '2021-09-06 19:05:01', 80),
(1003, 9002, '2021-09-09 07:01:02', null, null);

【操作】

delete from exam_record
where timestampdiff(minute,start_time,submit_time) <5
and score < 60
;

TIMESTAMPDIFF(interval, time_start, time_end)可计算time_start-time_end的时间差，单位以指定的interval为准，常用可选：

- SECOND 秒
- MINUTE 分钟（返回秒数差除以60的整数部分）
- HOUR 小时（返回秒数差除以3600的整数部分）
- DAY 天数（返回秒数差除以3600*24的整数部分）
- MONTH 月数
- YEAR 年数

1.3.2删除记录（二）

【输入】

drop table if EXISTS exam_record;
CREATE TABLE IF NOT EXISTS exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
TRUNCATE exam_record;
INSERT INTO exam_record(uid, exam_id, start_time, submit_time, score) VALUES
(1001, 9001, '2020-01-01 22:11:12', '2020-01-01 23:16:12', 50),
(1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:06:00', 58),
(1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:05:01', 58),
(1002, 9001, '2021-05-02 10:01:01', '2021-05-02 10:06:58', 60),
(1002, 9002, '2021-06-02 19:01:01', null, null),
(1003, 9001, '2021-09-05 19:01:01', null, null),
(1003, 9001, '2021-09-05 19:01:01', null, null),
(1003, 9002, '2021-09-09 07:01:02', null, null);

【操作】

delete from exam_record
where submit_time is null
    or timestampdiff(minute,start_time,submit_time) < 5
order by start_time 
limit 3
;

1.3.3删除记录（三）

【操作】

truncate table exam_record;

1.4表的增删改

1.4.1表的创建

• 直接创建表：

  CREATE TABLE
  [IF NOT EXISTS] tb_name -- 不存在才创建，存在就跳过
  (column_name1 data_type1 -- 列名和类型必选
    [ PRIMARY KEY -- 可选的约束，主键
     | FOREIGN KEY -- 外键，引用其他表的键值
     | AUTO_INCREMENT -- 自增ID
     | COMMENT comment -- 列注释（评论）
     | DEFAULT default_value -- 默认值
     | UNIQUE -- 唯一性约束，不允许两条记录该列值相同
     | NOT NULL -- 该列非空
    ], ...
  ) [CHARACTER SET charset] -- 字符集编码
  [COLLATE collate_value] -- 列排序和比较时的规则（是否区分大小写等）
  

• 从另一张表复制表结构创建表： CREATE TABLE tb_name LIKE tb_name_old

• 从另一张表的查询结果创建表： CREATE TABLE tb_name AS SELECT * FROM tb_name_old WHERE options

现有一张用户信息表，其中包含多年来在平台注册过的用户信息，随着牛客平台的不断壮大，用户量飞速增长，为了高效地为高活跃用户提供服务，现需要将部分用户拆分出一张新表。
原来的用户信息表：

【输出】

id|int|None|NO|PRI|None|auto_increment|select,insert,update,references|自增ID
uid|int|None|NO|UNI|None||select,insert,update,references|用户ID
nick_name|varchar(64)|utf8_general_ci|YES||None||select,insert,update,references|昵称
achievement|int|None|YES||0||select,insert,update,references|成就值
level|int|None|YES||None||select,insert,update,references|用户等级
job|varchar(32)|utf8_general_ci|YES||None||select,insert,update,references|职业方向
register_time|datetime|None|YES||CURRENT_TIMESTAMP|DEFAULT_GENERATED|select,insert,update,references|注册时间

【操作】

CREATE TABLE IF NOT EXISTS user_info_vip (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int UNIQUE NOT NULL COMMENT '用户ID',
    nick_name varchar(64) COMMENT '昵称',
    achievement int DEFAULT 0 COMMENT '成就值',
    `level` int COMMENT '用户等级',
    job varchar(32) COMMENT '职业方向',
    register_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间'
) CHARACTER SET utf8 COLLATE utf8_general_ci;

1.4.2修改表

- 请在用户信息表，字段level的后面增加一列最多可保存15个汉字的字段school；
- 并将表中job列名改为profession，同时varchar字段长度变为10；
- achievement的默认值设置为0。

【输入】

drop table if exists user_info;
CREATE TABLE IF NOT EXISTS user_info (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int UNIQUE NOT NULL COMMENT '用户ID',
`nick_name` varchar(64) COMMENT '昵称',
achievement int COMMENT '成就值',
level int COMMENT '用户等级',
job varchar(10) COMMENT '职业方向',
register_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

【操作】

alter table user_info add school varchar(15) after level;
增加列在某列之后
alter table 增加的表格 add 增加列的名称 数据类型 位置(after level 在level 之后)

alter table user_info change job profession varchar(10);
更换列的名称及数据类型
alter table user_info change 原列名 修改列名 修改数据类型

alter table user_info modify achievement int(11) default 0;
更改数据类型
alter table 表名 modify 修改列名称 数据类型 默认值等

1.4.3删除表

现有一张试卷作答记录表exam_record，其中包含多年来的用户作答试卷记录。一般每年都会为exam_record表建立一张备份表exam_record_{YEAR}，{YEAR}为对应年份。

现在随着数据越来越多，存储告急，请你把很久前的（2011到2014年）备份表都删掉（如果存在的话）。


备注:后台会通过SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_NAME LIKE \'exam\_record\_201_\'  来对比输出结果。

【输入】

drop table if EXISTS exam_record;
CREATE TABLE IF NOT EXISTS exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE IF NOT EXISTS exam_record_2010 (LIKE exam_record); 
CREATE TABLE IF NOT EXISTS exam_record_2012 (LIKE exam_record); 
CREATE TABLE IF NOT EXISTS exam_record_2013 (LIKE exam_record); 
CREATE TABLE IF NOT EXISTS exam_record_2014 (LIKE exam_record); 
CREATE TABLE IF NOT EXISTS exam_record_2015 (LIKE exam_record); 

【操作】

-- 方式一
SELECT 
concat('drop table if exists ',GROUP_CONCAT('',' ',TABLE_NAME),';') AS sqltext into @sqltext 
FROM 
information_schema.TABLES 
WHERE TABLE_NAME LIKE 'exam_record_201_' 
    and substr(TABLE_NAME,13,4) between 2011 and 2014;
PREPARE stmt FROM @sqltext;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt;
-- 方式二
drop table if exists exam_record_2011;
drop table if exists exam_record_2012;
drop table if exists exam_record_2013;
drop table if exists exam_record_2014;

1.5索引的增删

• create方式创建索引：

CREATE 
  [UNIQUE -- 唯一索引
  | FULLTEXT -- 全文索引
  ] INDEX index_name ON table_name -- 不指定唯一或全文时默认普通索引
  (column1[(length) [DESC|ASC]] [,column2,...]) -- 可以对多列建立组合索引  

• alter方式创建索引：ALTER TABLE tb_name ADD [UNIQUE | FULLTEXT] [INDEX] index_content(content)
• drop方式删除索引：DROP INDEX <索引名> ON <表名>
• alter方式删除索引：ALTER TABLE <表名> DROP INDEX <索引名>

1.5.1创建索引

现有一张试卷信息表examination_info，其中包含各种类型试卷的信息。为了对表更方便快捷地查询，需要在examination_info表创建以下索引，规则如下：
在duration列创建普通索引idx_duration、在exam_id列创建唯一性索引uniq_idx_exam_id、在tag列创建全文索引full_idx_tag。

【输入】

drop table if exists examination_info;
CREATE TABLE IF NOT EXISTS examination_info (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
tag varchar(32) COMMENT '类别标签',
difficulty varchar(8) COMMENT '难度',
duration int NOT NULL COMMENT '时长',
release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_bin;

【操作】

CREATE INDEX idx_duration ON examination_info(duration);
CREATE UNIQUE INDEX uniq_idx_exam_id ON examination_info(exam_id);
CREATE FULLTEXT INDEX full_idx_tag ON examination_info(tag);

1.5.2删除索引

请删除examination_info表上的唯一索引uniq_idx_exam_id和全文索引full_idx_tag。

后台会通过 SHOW INDEX FROM examination_info 来对比输出结果。

【操作】

-- 方式一
DROP INDEX uniq_idx_exam_id ON examination_info;
DROP INDEX full_idx_tag ON examination_info;
-- 方式二
alter table examination_info drop index uniq_idx_exam_id;
alter table examination_info drop index full_idx_tag;

2.提升操作

2.1多表关联

2.2聚合分组

2.3函数应用

2.3.1空值处理

• 空值处理：

2.3.2字符串截取

1)截取字符串
substring(str,1,end_length)

-- 截取日期字符串'2022-12-15 13:41:01'
select substring(str,1,10);
-- 截取结果：'2022-12-15'

LEFT函数 | RIGHT函数
LEFT函数用于从给定字符串的左侧提取指定数量的字符
RIGHT函数用于从给定字符串的右侧提取指定数量的字符

LEFT(str,len)
str: 给定的字符串，将从其左侧提取字符
len: 要提取的字符数，如果此参数大于字符串中的字符数，则此函数将返回实际的字符串

-- abc
SELECT LEFT('abcdefg',3);
-- Null
SELECT LEFT('abcd',NULL);
-- LEFT函数可以截取数字
-- 12
SELECT LEFT(123456,2);

RIGHT(str,len)
str: 给定的字符串，将从其右侧提取字符
len: 要提取的字符数，如果此参数大于字符串中的字符数，则此函数将返回实际的字符串

-- efg
SELECT RIGHT('abcdefg',3);
-- Null
SELECT RIGHT('abcd',NULL);
-- RIGHT函数可以截取数字
-- 56
SELECT RIGHT(123456,2);

2)分隔符分割字符串
substring_index(str,‘分隔符’,取值索引)

-- 把字符列ssq_red('07,08,13,29,30,33,03')切分
select 
    SUBSTRING_INDEX(ssq_red,',',1) as r1,
	SUBSTRING_INDEX(SUBSTRING_INDEX(ssq_red,',',2),',',-1) as r2,
	SUBSTRING_INDEX(SUBSTRING_INDEX(ssq_red,',',3),',',-1) as r3,
	SUBSTRING_INDEX(SUBSTRING_INDEX(ssq_red,',',4),',',-1) as r4,
	SUBSTRING_INDEX(SUBSTRING_INDEX(ssq_red,',',5),',',-1) as r5,
	SUBSTRING_INDEX(ssq_red,',',-1) as r6
from 
appssq_info_ssq
-- 切分结果为：07	08	13	29	30	33	03

2.3.3日期与时间

学习连接：

(1条消息) MySQL日期函数_假女吖☌的博客-CSDN博客_mysql 日期函数

(1条消息) MySQL 日期时间加减_普通网友的博客-CSDN博客_mysql日期加减函数

1)日期与时间的获取

• 获取年月日与时间：DATE()、TIME()、TIMESTAMP()

-- 返回 日期。格式：YYYY-MM-DD
SELECT DATE(NOW()); -- 2022-04-04
-- 返回 日期。格式：HH-mm-ss
SELECT TIME(NOW()); -- 16:25:09
-- 返回 日期时间。格式：YYYY-MM-DD HH-mm-ss
SELECT TIMESTAMP(NOW()); -- 2022-04-04 16:31:12
-- 其他
select NOW(),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP();
-- 2022-04-04 16:40:03	2022-04-04 16:40:03	2022-04-04 16:40:03

• 将字符串转成对应的日期(类型) ：STR_TO_DATE()

-- 例1、
SELECT STR_TO_DATE('2022-04-04 22:50:17','%Y-%m-%d');    -- 2022-04-04
-- 没有时分秒时这里就忽略显示了

-- 例2、
SELECT STR_TO_DATE('2022-04-04 22:50:17','%Y-%m-%d %H'); -- 2022-04-04 22:00:00

• 日期转为对应的字符串 ：DATE_FORMAT()

-- 例1、
SELECT DATE_FORMAT('2022-04-04 22:50:17','%Y-%m-%d'); 	  -- 2022-04-04

-- 例2、
SELECT DATE_FORMAT('2022-04-04 22:50:17','%Y-%m-%d %H');  -- 2022-04-04 22

• 每月天数获取：DAY(last_day(date_need)) 可获取每日当月的天数，date_need的获取：
  • NOW()、CURRENT_TIMESTAMP()、SYSDATE()（返回：2022-12-14 15:38:05）
  • CURRENT_TIMESTAMP 当前时间 （返回：2022-12-14 15:38:54）

select DAY(last_day(now()));  -- 31

• 时间差：TIMESTAMPDIFF(interval, time_start, time_end) 可计算time_start-time_end的时间差，单位以指定的interval为准，常用可选：
  • SECOND 秒
  • MINUTE 分钟（返回秒数差除以60的整数部分）
  • HOUR 小时（返回秒数差除以3600的整数部分）
  • DAY 天数（返回秒数差除以3600*24的整数部分）
  • MONTH 月数
  • YEAR 年数

-- 筛选作答时间5分钟内容且不及格的数据信息
select * from exam_record
where timestampdiff(minute,start_time,submit_time) <5
and score < 60
;

• 日期的加减：datediff(date1,date2) 、date_add(‘某个日期时间’,interval 1 时间种类名)、date_sub()

-- datediff(date1,date2)示例：
select datediff(now(),date_add(now(), interval -1 day)); // 1

-- date_add()示例：

select date_add(@dt, interval 1 year); //加1年
select date_add(@dt, interval 1 month); //加1月

-- quarter:季，week:周，day:天，hour:小时，minute:分钟，second:秒，microsecond:毫秒
-- 注：也可以不用变量，直接加减某个时间，如：select date_add(‘1998-01-01’, interval 1 day);

2.3.4窗口函数

3.高级操作

3.1存储过程

3.1.1存储过程创建

学习连接：(1条消息) MySQL中的存储过程（详细篇）_星辰与晨曦的博客-CSDN博客_mysql存储过程

3.1.2存储过程与游标

学习连接：(1条消息) 【mysql】游标的基本使用_兮动人的博客-CSDN博客_数据库游标的使用

【问题描述】

实例说明：编写两个表 sys_user和 user, 编写存储过程，当 两个表的 id 相同时 将 user 表中的 name 更新为 sys_user 中的 user_name，如下图：

【建表】

-- sys_user 表的创建
CREATE TABLE `sys_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(128) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
 
-- user 表的创建
CREATE TABLE `user` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

【创建存储过程】

DELIMITER $$
CREATE PROCEDURE user_test()
BEGIN
  -- 定义变量
  DECLARE sys_user_id BIGINT;
  DECLARE sys_user_name VARCHAR(11);
  DECLARE done INT;
  -- 创建游标，并存储数据
  DECLARE cur_test CURSOR FOR 
      SELECT id AS user_id,user_name AS sys_user_name  FROM `sys_user`;
  -- 游标中的内容执行完后将 done 设置为 1 
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
  -- 打开游标
  OPEN cur_test;
  -- 执行循环
  posLoop:LOOP
  -- 判断是否结束循环
        IF done=1 THEN
        LEAVE posLoop;
        END IF;
  -- 取游标中的值
  FETCH cur_test INTO sys_user_id,sys_user_name;
  -- 执行更新操作
    UPDATE `user` SET NAME=sys_user_name WHERE id=sys_user_id;
  END LOOP posLoop;
  -- 释放游标 
  CLOSE cur_test; 
END $$
DELIMITER ;
 
DROP PROCEDURE user_test;
-- 调用存储过程
CALL user_test;

3.2事件与触发器

3.2.1事件与定时任务

-- 创建一个定时事件，12h执行一次
-- drop event myweekJob;
create event if not exists myweekJob
-- on schedule every 12 HOUR STARTS NOW()  -- 从现在开始，每12h执行一次
on schedule  EVERY 1 DAY STARTS '2022-09-23 13:00:00' 
on completion PRESERVE
do call myweek();                       -- 回调 myweek() 存储过程

-- 启动定时器 （已经启动）
-- SET GLOBAL event_scheduler = 1;
-- 启动事件 eventJob
ALTER EVENT myweekJob ON COMPLETION PRESERVE ENABLE;

-- 查看事件
SELECT * FROM information_schema.events; 
ALTER EVENT warnning_reform_out_time ENABLE;

学习连接：

(1条消息) MySQL事件（定时任务）_pan_junbiao的博客-CSDN博客_mysql 定时事件

(1条消息) MySql的函数和事件（navicat界面操作）_唐 城的博客-CSDN博客_mysql 写函数之后 启动 函数事件

3.2.2触发器

操作实例：

delimiter ##  -- 切换自定义结束符号
CREATE DEFINER=`root`@`localhost` TRIGGER `insert_user_from_sys` BEFORE UPDATE ON `user` 
FOR EACH ROW begin
	declare var varchar(255);
	set var = (select user_name from sys_user where id = OLD.id);
	set NEW.tmp = var;
end;
delimiter ;

学习连接：
(1条消息) MySQL的触发器_莱维贝贝、的博客-CSDN博客_mysql触发器

(1条消息) MySql的函数和事件（navicat界面操作）_唐 城的博客-CSDN博客_mysql 写函数之后 启动 函数事件