SQL刷题笔记day7——SQL进阶——增删改操作

目录

1插入记录

1.1插入两个用户的作答记录

1.2 记录导入新表

 1.3 替换插入（表中已有主键相同的数据）

 2 更新记录

2.1更新指定字段

2.2 更新满足多个条件下的数据

3 删除记录

3.1 删除某些条件下的数据 where

3.2 时间差函数的应用和分页的使用

3.3重置自增主键

删除所有记录并重置自增值：

1插入记录

1.1插入两个用户的作答记录

我的代码：insert into table(col1, col2,...,coln) values (value1, value2,...,valuen)  

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)

复盘：table后面对应最好也要写上列字段名，否则和默认的对应不上就要报错，values中类型要正确，''要正确使用。

标准：不写字段名 + 自增id + 系统算时间使用date_add

insert into 
    exam_record
values 
(default,1001,9001,'2021-09-01 22:11:12',
date_add('2021-09-01 22:11:12',interval 50 minute) ,90),

(default,1002,9002,'2021-09-04 07:01:02',default ,default)

1.2 记录导入新表

我的痴人说梦代码：

insert into exam_record_before_2021
from exam_record
where submit_time < ('2021%m%d')

复盘：

1. 如何取2021年以前的，就是年份<2021:year(submit_time)<'2021'
2. 已经完成了的，即有提交时间而且最好有成绩：score is not null
3. into .A.. from..B.不可以吗，逻辑是这样，但是不from B中select出来，into什么进A呢。

正确代码：应该给出字段名，这样才能把建表时的初始信息覆盖掉

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' and score is not null

 

因为本身exam_record_before_2021创建的时候已经有id=1，所以如果是插入数据就会从id=2开始，所以下面这个代码就会失效：

insert into exam_record_before_2021
select *
from exam_record
where year(submit_time) < '2021' and score is not null

 

 1.3 替换插入（表中已有主键相同的数据）

我的代码：

insert into
examination_info(exam_id,tag,difficulty,duration,release_time)
values(9003,'SQL','高难度',90,'2021-01-01 00:00:00')

报错：说明重复了

仔细读题：不管该ID试卷是否存在，都要插入成功，说明大概率已经存在9003，不能直接用insert 

正确代码：

replace into
examination_info(exam_id,tag,difficulty,duration,release_time)
values(9003,'SQL','hard',90,'2021-01-01 00:00:00')

掌握replace into···values的用法

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

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

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

 2 更新记录

2.1更新指定字段

我的代码

update examination_info
set tag = 'Python'
where tag='PYTHON'

update用法：MySQL 更新数据

update 表名 
set 列名1 = 值1 [,列名2=值2] 
[where 条件];

2.2 更新满足多个条件下的数据

我的代码：结果不对，有些地方没有满足条件

update exam_record
set submit_time='2099-01-01 00:00:00',score='0'
where start_time <'2021-09-01 00:00:00'

分析：要改的是2021年9月1日之前开始作答的未完成记录 ，即submit_time is null

正确完整答案：

update exam_record
set submit_time='2099-01-01 00:00:00',score='0'
where start_time <'2021-09-01'and submit_time is null

3 删除记录

3.1 删除某些条件下的数据 where

我的错误代码：不会计算时间差

delete from exam_record
where day(submit_time-start_time)<5 and score<60

删除：delete from 表名，where。

MySQL删除数据学习链接：https://blog.csdn.net/chengyj0505/article/details/128358817?spm=1001.2014.3001.5502

时间差函数：timestampdiff，如计算差多少分钟，timestampdiff(minute,时间1,时间2)，是时间2-时间1。

正确代码：

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

3.2 时间差函数的应用和分页的使用

我的正确代码：

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

3.3重置自增主键

我的代码：

truncate exam_record

删除所有记录并重置自增值：

truncate [table] 表名;

[注意]：truncate只能作用于表；truncate会清空表中的所有行，但表结构及其约束、索引等保持不变；truncate会重置表的自增值；使用truncate后会使表和索引所占用的空间会恢复到初始大小。