【SQL5天进阶挑战】day1.增删改操作

https://www.nowcoder.com/exam/oj?page=1&tab=SQL%E7%AF%87&topicId=240
💖牛客题目， SQL必知必会。不断熟悉，不断进步，加油！👍 💬 ⭐️

一、插入记录

1.直接插入

insert into table values(‘xx’,‘xxx’,‘xxxx’) # 参数按顺序排列

insert into table(name,age,class) values(‘xiaoming’,23,‘classone’) # 自增或没写的参数插入null

2.插入多条数据：

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 07:01:02', null, null)

3.从其他表中查询记录，然后插入

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 submit_time is not null and YEAR  (submit_time) < 2021

4. 替换插入 REPLACE INTO

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

二、更新记录

UPDATE examination_info set tag = 'Python' WHERE tag = 'PYTHON'

UPDATE exam_record set submit_time = '2099-01-01 00:00:00', score = 0 
WHERE submit_time is null AND start_time <'2021-09-01'

三、 删除记录

1. 按条件删除

DELETE FROM exam_record
WHERE score < 60 AND TIMESTAMPDIFF(MINUTE, start_time, submit_time) < 5

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. 删除所有记录

# 删除表所有记录，并且重置自增主键
TRUNCATE exam_record

# 删除所有记录，不重置自增主键
DELETE FROM exam_record

# 删除整张表
DROP TABLE exam_record

日期操作

直接比较

submit_time = '2099-01-01 00:00:00'

比较差值

TIMESTAMPDIFF(MINUTE, start_time, submit_time) < 5

日期转化为字符串

DATE_FORMAT(order_date, '%Y%m') = '202001'