回城传送–》《32天SQL筑基》
文章目录
零、前言
今天是学习 SQL 打卡的第 7 天，每天我会提供一篇文章供群成员阅读（ 不需要订阅付钱 ）。
希望大家先自己思考，如果实在没有想法，再看下面的解题思路，自己再实现一遍。在小虚竹JAVA社区 中对应的 【打卡贴】打卡，今天的任务就算完成了，养成每天学习打卡的好习惯。
虚竹哥会组织大家一起学习同一篇文章，所以有什么问题都可以在群里问，群里的小伙伴可以迅速地帮到你，一个人可以走得很快，一群人可以走得很远，有一起学习交流的战友，是多么幸运的事情。
我的学习策略很简单，题海策略+ 费曼学习法。如果能把这些题都认认真真自己实现一遍，那意味着 SQL 已经筑基成功了。后面的进阶学习，可以继续跟着我，一起走向架构师之路。
今天的学习内容是：SQL进阶-插入记录
一、练习题目
题目链接 | 难度 |
★☆☆☆☆ | |
★★☆☆☆ | |
★★☆☆☆ |
二、SQL思路
插入记录:SQL110 插入记录（一）
初始化数据
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;
解法
要求处理：
- 有两个用户的作答记录详情，试卷作答记录表exam_record中，表已建好，其结构如下，请用一条语句将这两条记录插入表中。
- 最后会通过执行SELECT uid, exam_id, start_time, submit_time, score FROM 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 7:01:02',null,null);
扩展
- INSERT INTO 语句可以有以下四种编写形式。
- 第一种形式：不需要指定表的列名，只要提供要插入的值，支持插入多行;
语法：
INSERT INTO table_name
VALUES
(value1,value2,value3,…),(value4,value5,value6,…);
insert into exam_record VALUES(null,1001,9001,'2021-09-01 22:11:12','2021-09-01 23:01:12',90),
(null,1002,9002,'2021-09-04 7:01:02',null,null);
- 第二种形式：不需要指定表的列名，只要提供要插入的值，支持插入一行;
语法：INSERT INTO table_name VALUE (value1,value2,value3,…);
insert into exam_record
VALUE(null,1001,9001,'2021-09-01 22:11:12','2021-09-01 23:01:12',90);
- 第三种形式：要指定表的列名，提供要插入的值，支持插入多行;
语法：INSERT INTO table_name (column1,column2,column3,…) VALUES (value1,value2,value3,…),(value4,value5,value6,…);
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 7:01:02',null,null);
- 第四种形式：要指定表的列名，提供要插入的值，支持插入一行;
语法：INSERT INTO table_name (column1,column2,column3,…) VALUE (value1,value2,value3,…);
insert into exam_record (uid,exam_id,start_time,submit_time,score)
VALUE
(1001,9001,'2021-09-01 22:11:12','2021-09-01 23:01:12',90);
插入记录:SQL111 插入记录（二）
初始化数据
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);
解法
要求统计：
- 已经创建了一张新表exam_record_before_2021用来备份2021年之前的试题作答记录，结构和exam_record表一致，请将2021年之前的已完成了的试题作答纪录导入到该表。
- 后台会通过执行"SELECT * FROM exam_record_before_2021;"语句来对比结果
分析：
- INSERT INTO 语句 第五种方式
语法：insert into table_name_new (column1,column2,column3,…)
select column1,column2,column3,…
from table_name
where …
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 year(submit_time) < 2021;
插入记录:SQL112 插入记录（三）
初始化数据
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');
解法
要求统计：
- 现在有一套ID为9003的高难度SQL试卷，时长为一个半小时，请你将 2021-01-01 00:00:00 作为发布时间插入到试题信息表examination_info（其表结构如下图），不管该ID试卷是否存在，都要插入成功，请尝试插入它。
- SELECT exam_id,tag,difficulty,duration,release_time FROM examination_info 语句来对比结果。
分析：
- 插入的第六种方式
语法：replace into table_name_new (column1,column2,column3,…)
value (value1,value2,value3,…)
replace into examination_info( exam_id, tag, difficulty, duration, release_time)
value(9003,'SQL','hard',90,'2021-01-01 00:00:00');
扩展
replace into的应用注意事项:
- 插入数据的表必须有主键或者是唯一索引,否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。
- 如果数据库里边有这条记录，则直接修改这条记录；如果没有则，则直接插入，在有外键的情况下，对主表进行这样操作时，因为如果主表存在一条记录，被从表所用时，直接使用replace into是会报错的，这和replace into的内部原理是相关（先删除然后再插入）
- 表中有一个自增的主键,带来的问题：
- replace操作在自增主键的情况下，遇到唯一键冲突时执行的是delete+insert,但是在记录binlog时,却记录成了update操作,update操作不会涉及到auto_increment的修改。备库应用了binlog之后，备库的表的auto_increment属性不变。如果主备库发生主从切换，备库变为原来的主库，写新的主库则有风险发生主键冲突。
- 频繁的REPLACE INTO 会造成新纪录的主键的值迅速增大。总有一天。达到最大值后就会因为数据太大溢出了。就没法再插入新纪录了。数据表满了，不是因为空间不够了，而是因为主键的值没法再增加了。
三、总结
sql插入的写法一共有6种：
- INSERT INTO 语句：不指定表的列名，插入多行；
INSERT INTO table_name VALUES (value1,value2,value3,…),(value4,value5,value6,…);
- INSERT INTO 语句：不指定表的列名，只插入一行；
INSERT INTO table_name VALUE (value1,value2,value3,…);
- INSERT INTO 语句：指定表的列名,插入多行；
INSERT INTO table_name (column1,column2,column3,…) VALUES (value1,value2,value3,…),(value4,value5,value6,…);
- INSERT INTO 语句：指定表的列名,只插入一行；
INSERT INTO table_name (column1,column2,column3,…) VALUE (value1,value2,value3,…);
- INSERT INTO 语句: 查其他表，拼凑要插入的表的数据；
insert into table_name_new (column1,column2,column3,…)
select column1,column2,column3,…
from table_name
where …
- 第6种：使用replace into 语句
- 如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。 否则，直接插入新数据
replace into table_name_new (column1,column2,column3,…)
value (value1,value2,value3,…)
参考
replace into详解
我是虚竹哥，我们明天见~