首页 > 数据库 >【第7天】SQL进阶-插入记录(SQL 小虚竹)

【第7天】SQL进阶-插入记录(SQL 小虚竹)

时间:2022-10-21 14:02:52浏览次数:76  
标签:COMMENT 01 exam 2021 time 插入 虚竹 SQL 进阶


回城传送–》《32天SQL筑基》

文章目录

零、前言

今天是学习 SQL 打卡的第 7 天,每天我会提供一篇文章供群成员阅读( 不需要订阅付钱 )。

希望大家先自己思考,如果实在没有想法,再看下面的解题思路,自己再实现一遍。在小虚竹JAVA社区 中对应的 【打卡贴】打卡,今天的任务就算完成了,养成每天学习打卡的好习惯。

虚竹哥会组织大家一起学习同一篇文章,所以有什么问题都可以在群里问,群里的小伙伴可以迅速地帮到你,一个人可以走得很快,一群人可以走得很远,有一起学习交流的战友,是多么幸运的事情。

我的学习策略很简单,题海策略+ 费曼学习法。如果能把这些题都认认真真自己实现一遍,那意味着 SQL 已经筑基成功了。后面的进阶学习,可以继续跟着我,一起走向架构师之路。

今天的学习内容是:SQL进阶-插入记录

一、练习题目

题目链接

难度

​插入记录:SQL110 插入记录(一)​

★☆☆☆☆

​插入记录:SQL111 插入记录(二)​

★★☆☆☆

​插入记录:SQL112 插入记录(三)​

★★☆☆☆

二、SQL思路

插入记录:SQL110 插入记录(一)

【第7天】SQL进阶-插入记录(SQL 小虚竹)_数据库


【第7天】SQL进阶-插入记录(SQL 小虚竹)_数据库_02

初始化数据

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);

【第7天】SQL进阶-插入记录(SQL 小虚竹)_数据_03

扩展

  • 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);

【第7天】SQL进阶-插入记录(SQL 小虚竹)_主键_04


【第7天】SQL进阶-插入记录(SQL 小虚竹)_数据_05

  • 第二种形式:不需要指定表的列名,只要提供要插入的值,支持插入一行;

语法: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);

【第7天】SQL进阶-插入记录(SQL 小虚竹)_数据_06


【第7天】SQL进阶-插入记录(SQL 小虚竹)_主键_07

  • 第三种形式:要指定表的列名,提供要插入的值,支持插入多行;

语法: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);

【第7天】SQL进阶-插入记录(SQL 小虚竹)_数据库_08


【第7天】SQL进阶-插入记录(SQL 小虚竹)_数据_09

  • 第四种形式:要指定表的列名,提供要插入的值,支持插入一行;

语法: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);

【第7天】SQL进阶-插入记录(SQL 小虚竹)_sql_10


【第7天】SQL进阶-插入记录(SQL 小虚竹)_数据_11

插入记录:SQL111 插入记录(二)

【第7天】SQL进阶-插入记录(SQL 小虚竹)_数据_12

【第7天】SQL进阶-插入记录(SQL 小虚竹)_主键_13

初始化数据

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;

【第7天】SQL进阶-插入记录(SQL 小虚竹)_数据库_14


【第7天】SQL进阶-插入记录(SQL 小虚竹)_sql_15

插入记录:SQL112 插入记录(三)

【第7天】SQL进阶-插入记录(SQL 小虚竹)_数据库_16


【第7天】SQL进阶-插入记录(SQL 小虚竹)_数据_17

初始化数据

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');

【第7天】SQL进阶-插入记录(SQL 小虚竹)_数据库_18

扩展

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详解

我是虚竹哥,我们明天见~


标签:COMMENT,01,exam,2021,time,插入,虚竹,SQL,进阶
From: https://blog.51cto.com/u_14122613/5782608

相关文章