您好,登錄后才能下訂單哦!
這篇文章主要講解了“SQL增刪改操作實例分析”,文中的講解內容簡單清晰,易于學習與理解,下面請大家跟著小編的思路慢慢深入,一起來研究和學習“SQL增刪改操作實例分析”吧!
表exam_record結構
題目描述 牛客后臺會記錄每個用戶的試卷作答記錄到exam_record表,現在有兩個用戶的作答記錄詳情如下: 用戶1001在2021年9月1日晚上10點11分12秒開始作答試卷9001,并在50分鐘后提交,得了90分; 用戶1002在2021年9月4日上午7點1分2秒開始作答試卷9002,并在10分鐘后退出了平臺。 試卷作答記錄表exam_record中,表已建好,其結構如下,請用一條語句將這兩條記錄插入表中。 該題最后會通過執行SELECT uid, exam_id, start_time, submit_time, score FROM exam_record; 來對比結果 建表語句 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; 答案 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);
表exam_record結構
題目描述 現有一張試卷作答記錄表exam_record,其中包含多年來的用戶作答試卷記錄, 由于數據越來越多,維護難度越來越大,需要對數據表內容做精簡,歷史數據做備份。 我們已經創建了一張新表exam_record_before_2021用來備份2021年之前的試題作答記錄, 結構和exam_record表一致,請將2021年之前的已完成了的試題作答紀錄導入到該表。 后臺會通過執行"SELECT * FROM exam_record_before_2021;"語句來對比結果 建表語句 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); 答案 INSERT INTO exam_record_before_2021 SELECT NULL,uid, exam_id, start_time, submit_time, score FROM exam_record WHERE submit_time < '2021-01-01 00:00:00';
試題信息表examination_info結構
題目描述 現在有一套ID為9003的高難度SQL試卷,時長為一個半小時, 請你將 2021-01-01 00:00:00 作為發布時間插入到試題信息表examination_info,不管該ID試卷是否存在,都要插入成功,請嘗試插入它。 后臺會通過執行 SELECT exam_id,tag,difficulty,duration,release_time FROM examination_info 語句來對比結果。 建表語句 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'); 答案 replace into examination_info (id,exam_id,tag,difficulty,duration,release_time) values(null,9003,'SQL','hard',90,'2021-01-01 00:00:00');
現有一張試卷信息表examination_info,表結構如下圖所示:
題目描述 請把examination_info表中tag為PYTHON的tag字段全部修改為Python。 后臺會通過執行'SELECT exam_id,tag,difficulty,duration,release_time FROM examination_info;'語句來對比結果。 建表語句 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, 'python', 'easy', 60, '2020-01-01 10:00:00'), (9003, 'Python', 'medium', 80, '2020-01-01 10:00:00'), (9004, 'PYTHON', 'hard', 80, '2020-01-01 10:00:00'); 答案 UPDATE examination_info set tag ='Python' WHERE tag='PYTHON';
作答記錄表exam_record表結構
題目描述 現有一張試卷作答記錄表exam_record,其中包含多年來的用戶作答試卷記錄,submit_time為 完成時間 請把exam_record表中2021年9月1日之前開始作答的未完成記錄全部改為被動完成, 即:將完成時間改為'2099-01-01 00:00:00',分數改為0。 建表語句 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; INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES (1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80), (1001, 9002, '2021-09-01 09:01:01', '2021-09-01 09:21:01', 90), (1002, 9001, '2021-08-02 19:01:01', null, null), (1002, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89), (1003, 9001, '2021-09-02 12:01:01', null, null), (1003, 9002, '2021-09-01 12:01:01', null, 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;
作答記錄表exam_record表結構,start_time是試卷開始時間,submit_time 是交卷,即結束時間
題目描述 現有一張試卷作答記錄表exam_record,其中包含多年來的用戶作答試卷記錄, start_time是試卷開始時間 submit_time 是交卷,即結束時間 請刪除exam_record表中作答時間小于5分鐘整且分數不及格(及格線為60分)的記錄; 后臺會執行您的SQL,然后通過 SELECT * FROM exam_record; 語句來篩選出剩下的數據,與正確數據進行對比。 建表語句 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; INSERT INTO exam_record(uid, exam_id, start_time, submit_time, score) VALUES (1001, 9001, '2020-01-01 22:11:12', '2020-01-01 23:16:12', 50), (1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:06:00', 58), (1002, 9001, '2021-05-02 10:01:01', '2021-05-02 10:05:58', 60), (1002, 9002, '2021-06-02 19:01:01', '2021-06-02 19:05:01', 54), (1003, 9001, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 49), (1003, 9001, '2021-09-05 19:01:01', '2021-09-05 19:15:01', 70), (1003, 9001, '2021-09-06 19:01:01', '2021-09-06 19:05:01', 80), (1003, 9002, '2021-09-09 07:01:02', null, null); 答案 delete from exam_record where timestampdiff(minute,start_time,submit_time) < 5 and score < 60;
作答記錄表exam_record結構如下:
題目描述 現有一張試卷作答記錄表exam_record,其中包含多年來的用戶作答試卷記錄,start_time是試卷開始時間 submit_time 是交卷時間,即結束時間,如果未完成的話,則為空 請刪除exam_record表中未完成作答或作答時間小于5分鐘整的記錄中,開始作答時間最早的3條記錄。 后臺會通過 SELECT * FROM exam_record 語句來對比結果。 建表語句 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; INSERT INTO exam_record(uid, exam_id, start_time, submit_time, score) VALUES (1001, 9001, '2020-01-01 22:11:12', '2020-01-01 23:16:12', 50), (1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:06:00', 58), (1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:05:01', 58), (1002, 9001, '2021-05-02 10:01:01', '2021-05-02 10:06:58', 60), (1002, 9002, '2021-06-02 19:01:01', null, null), (1003, 9001, '2021-09-05 19:01:01', null, null), (1003, 9001, '2021-09-05 19:01:01', null, null), (1003, 9002, '2021-09-09 07:01:02', null, null); 答案 delete from exam_record where timestampdiff(minute, start_time, submit_time) < 5 or submit_time is null order by start_time limit 3;
試卷作答記錄表exam_record表結構
題目描述 現有一張試卷作答記錄表exam_record,其中包含多年來的用戶作答試卷記錄, 請刪除exam_record表中所有記錄,并重置自增主鍵。 后臺會通過SELECT table_rows, auto_increment FROM information_schema.tables WHERE table_name='exam_record'語句來對比輸出結果 建表語句 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; INSERT INTO exam_record(uid, exam_id, start_time, submit_time, score) VALUES (1001, 9001, '2020-01-01 22:11:12', '2020-01-01 23:16:12', 50), (1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:06:00', 58); 答案 TRUNCATE table exam_record;
感謝各位的閱讀,以上就是“SQL增刪改操作實例分析”的內容了,經過本文的學習后,相信大家對SQL增刪改操作實例分析這一問題有了更深刻的體會,具體使用情況還需要大家實踐驗證。這里是億速云,小編將為大家推送更多相關知識點的文章,歡迎關注!
免責聲明:本站發布的內容(圖片、視頻和文字)以原創、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。