插入记录
插入记录的方式汇总:
普通插入(全字段):INSERT INTO table_name VALUES (value1, value2, …)
普通插入(限定字段):INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …)
多条一次性插入:INSERT INTO table_name (column1, column2, …) VALUES (value1_1, value1_2, …), (value2_1, value2_2, …), …
从另一个表导入:INSERT INTO table_name SELECT * FROM table_name2 [WHERE key=value]
题目1
https://www.nowcoder.com/practice/5d2a42bfaa134479afb9fffd9eee970c?tpId=240&tqId=2221797&ru=/ta/sql-advanced&qru=/ta/sql-advanced/question-ranking
法1:
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);
法2:
insert into
exam_record
-- 本法中,由于列id是自增主键(PRIMARY KEY),说明不需要手动填入,它会跟随表格行数进行自己增加(比如这样增加id值:1,2,3...n)。
-- 因此,若要按全表插入(法1是按指定列插入),就要把id的值设置为NULL或0,这样MySQL会自己处理这个自增的id列。
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);
题目2
https://www.nowcoder.com/practice/9681abf28745468c8adacb3b029a18ce?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0
法1: 限定字段从另一表导入
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 submit_time IS NOT NULL; # <"2021" 也对。
法2: 全字段从另一表导入。注意跟法1的区别:
第一列id为自增主键列,不能直接复制过去,若要全字段导入,就用null来凑。
insert into
exam_record_before_2021
select null,uid, exam_id, start_time, submit_time, score from exam_record
where year(submit_time) < 2021 and submit_time IS NOT NULL;
题目3
https://www.nowcoder.com/practice/978bcee6530a430fb0be716423d84082?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0
法1:使用repalce into
replace into···values的用法:
replace into 跟 insert into功能类似,不同点在于:replace into 首先尝试插入数据到表中,若发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据,然后插入新的数据;
否则,直接插入新数据。
要注意的是:插入数据的表必须有主键或者是唯一索引!(在本题中,就有唯一索引exam_id)否则的话,replace into 会直接插入数据,这将导致表中出现重复的数据。
replace into
examination_info
values(null, 9003, "SQL", "hard", 90, "2021-01-01 00:00:00");
法2:若有重复记录,先删再插入。相当于法1.
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.设置为新值:UPDATE table_name SET column_name=new_value [, column_name2=new_value2] [WHERE column_name3=value3]
2.根据已有值替换:UPDATE table_name SET key1=replace(key1, ‘查找内容’, ‘替换成内容’) [WHERE column_name3=value3]
题目1
https://www.nowcoder.com/practice/bfe8ad2bddc540fc911614aa648868b3?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0
法1:
update examination_info
set tag="Python"
where tag="PYTHON";
法2:用repalce
update examination_info
set tag=replace(tag, "PYTHON", "Python")
# where tag="PYTHON"; # 这里不写where..也ok
题目2
https://www.nowcoder.com/practice/0c2e81c6b62e4a0f848fa7693291defc?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0
update exam_record
set submit_time='2099-01-01 00:00:00', score=0
where start_time<'2021-09-01 00:00:00' and submit_time is null;
删除记录
删除记录的方式汇总:
- 1.根据条件删除:DELETE FROM tb_name [WHERE options] [ [ ORDER BY fields ] LIMIT n ]
- 2.全部删除(表清空,包含自增计数器重置):TRUNCATE [table] tb_name
题目1
https://www.nowcoder.com/practice/d331359c5ca04a3b87f06b97da42159c?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0
delete from exam_record
where timestampdiff(minute, start_time, submit_time)<5 and score<60;
关于时间差的拓展
时间差:
TIMESTAMPDIFF(interval, time_start, time_end)可计算time_start-time_end的时间差,单位以指定的interval为准,常用可选:
SECOND 秒
MINUTE 分钟(返回秒数差除以60的整数部分)
HOUR 小时(返回秒数差除以3600的整数部分)
DAY 天数(返回秒数差除以3600*24的整数部分)
MONTH 月数
YEAR 年数
题目2:
https://www.nowcoder.com/practice/964c9f7fffbb4ab18b507cfed4111b4a?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0
delete from exam_record
where TIMESTAMPDIFF(MINUTE, start_time, submit_time ) < 5
or submit_time is NULL
ORDER BY start_time
LIMIT 3;
题目3:
https://www.nowcoder.com/practice/3abefc6fc73e4f219dad0ab66e6b1e3f?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0
truncate table exam_record; # 加或不加table都ok.