---

1 题目链接

https://www.nowcoder.com/exam/oj?page=1&tab=SQL%E7%AF%87&topicId=240

2 牛客 - SQL进阶挑战 - 01 增删改操作

2.1 插入记录

SQL 110 插入记录（一）

INSERT INTO exam_record ( uid, exam_id, start_time, submit_time, score )
VALUES
	( 1001, 9001, '2021-9-1 22:11:12', '2021-9-1 23:01:12', 90 ),
	( 1002, 9002, '2021-9-4 07:01:02', NULL, NULL );
1
2
3
4

INSERT INTO exam_record
VALUES
	( NULL, 1001, 9001, '2021-9-1 22:11:12', '2021-9-1 23:01:12', 90 ),
	( NULL, 1002, 9002, '2021-9-4 07:01:02', NULL, NULL );
1
2
3
4

SQL 111 插入记录（二）

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; 
1
2
3
4

INSERT INTO exam_record_before_2021 
SELECT null, uid, exam_id, start_time, submit_time, score
FROM exam_record
WHERE YEAR(submit_time) < 2021; 
1
2
3
4

SQL 112 插入记录（三）

DELETE FROM examination_info WHERE exam_id = 9003;

INSERT INTO examination_info
VALUES (NULL, 9003, 'SQL', 'hard', 90, '2021-01-01 00:00:00');
1
2
3
4

REPLACE INTO表示插入替换数据，需求表中有 PRIMARY KEY ，或者 UNIQUE 索引，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和 INSERT INTO 一样

REPLACE INTO examination_info
VALUES (NULL, 9003, 'SQL', 'hard', 90, '2021-01-01 00:00:00');
1
2

2.2 更新记录

SQL 113 更新记录（一）

UPDATE examination_info
SET tag = 'Python'
WHERE tag = 'PYTHON'; 
1
2
3

SQL 114 更新记录（二）

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 00:00:00';
1
2
3

2.3 删除记录

SQL 115 删除记录（一）

DELETE
FROM
	exam_record 
WHERE
	TIMESTAMPDIFF( SECOND, start_time, submit_time ) < 300 
	AND score < 60;
1
2
3
4
5
6

DELETE
FROM
	exam_record
WHERE
	TIMESTAMPDIFF(MINUTE, start_time, submit_time) < 5
	AND score < 60;
1
2
3
4
5
6

SQL 116 删除记录（二）

DELETE
FROM
	exam_record 
WHERE
	submit_time IS NULL 
	OR TIMESTAMPDIFF( MINUTE, start_time, submit_time ) < 5 
ORDER BY
	start_time ASC 
LIMIT 
	3;
1
2
3
4
5
6
7
8
9
10

SQL 117 删除记录（三）

TRUNCATE TABLE exam_record;
1