SQL脚本-12-计算机考试-空麓网

SQL脚本-12

- - 1.基础操作
  - - 1.1插入数据
    - - 1.1.1插入记录（一）
      - 1.1.2插入记录（二）
      - 1.1.3插入记录（三）
    - 1.2更新记录
    - - 1.2.1更新记录（一）
      - 1.2.2更新记录（二）
    - 1.3删除记录
    - - 1.3.1删除记录（一）
      - 1.3.2删除记录（二）
      - 1.3.3删除记录（三）
    - 1.4表的增删改
    - - 1.4.1表的创建
      - 1.4.2修改表
      - 1.4.3删除表
    - 1.5索引的增删
    - - 1.5.1创建索引
      - 1.5.2删除索引
  - 2.提升操作
  - - 2.1多表关联
    - 2.2聚合分组
    - 2.3函数应用
    - - 2.3.1空值处理
      - 2.3.2字符串截取
      - 2.3.3日期与时间
      - 2.3.4窗口函数
  - 3.高级操作
  - - 3.1存储过程
    - - 3.1.1存储过程创建
      - 3.1.2存储过程与游标
    - 3.2事件与触发器
    - - 3.2.1事件
      - 3.2.2触发器

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 首先尝试插入数据到表中，

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

要注意的是：插入数据的表必须有主键或者是唯一索引！否则的话，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字符串截取

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

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事件

学习连接：

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

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

3.2.2触发器

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

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

SQL脚本-12

SQL脚本-12

1.基础操作

1.1插入数据

1.1.1插入记录（一）

1.1.2插入记录（二）

1.1.3插入记录（三）

1.2更新记录

1.2.1更新记录（一）

1.2.2更新记录（二）

1.3删除记录

1.3.1删除记录（一）

1.3.2删除记录（二）

1.3.3删除记录（三）

1.4表的增删改

1.4.1表的创建

1.4.2修改表

1.4.3删除表

1.5索引的增删

1.5.1创建索引

1.5.2删除索引

2.提升操作

2.1多表关联

2.2聚合分组

2.3函数应用

2.3.1空值处理

2.3.2字符串截取

2.3.3日期与时间

2.3.4窗口函数

3.高级操作

3.1存储过程

3.1.1存储过程创建

3.1.2存储过程与游标

3.2事件与触发器

3.2.1事件

3.2.2触发器

Java相关栏目本月热门文章