1、修改记录的方式汇总:
- 设置为新值:UPDATE table_name SET column_name=new_value [, column_name2=new_value2] [WHERE column_name3=value3]
- 根据已有值替换:UPDATE table_name SET key1=replace(key1, '查找内容', '替换成内容') [WHERE column_name3=value3]
2、案例
(1)现有一张试卷信息表examination_info,表结构如下图所示:
Filed | Type | Null | Key | Extra | Default | Comment |
id | int(11) | NO | PRI | auto_increment | (NULL) | 自增ID |
exam_id | int(11) | NO | UNI | (NULL) | 试卷ID | |
tag | char(32) | YES | (NULL) | 类别标签 | ||
difficulty | char(8) | YES | (NULL) | 难度 | ||
duration | int(11) | NO | (NULL) | 时长 | ||
release_time | datetime | YES | (NULL) | 发布时间 |
--t1:
update examination_info
set tag = replace(tag,'PYTHON','Python')
where tag = 'PYTHON';
--t2:
update examination_info
set tag = 'Python'
where tag = 'PYTHON';
请把examination_info表中tag为PYTHON的tag字段全部修改为Python。
(2)现有一张试卷作答记录表exam_record,其中包含多年来的用户作答试卷记录,结构如下表:
作答记录表exam_record:
submit_time为 完成时间
Filed | Type | Null | Key | Extra | Default | Comment |
id | int(11) | NO | PRI | auto_increment | (NULL) | 自增ID |
uid | int(11) | NO | (NULL) | 用户ID | ||
exam_id | int(11) | NO | (NULL) | 试卷ID | ||
start_time | datetime | NO | (NULL) | 开始时间 | ||
submit_time | datetime | YES | (NULL) | 提交时间 | ||
score | tinyint(4) | YES | (NULL) | 得分 |
update exam_record
set score = 0,submit_time = '2099-01-01 00:00:00'
where start_time < '2021-09-01 00:00:00' and score is null;
请把exam_record表中2021年9月1日之前开始作答的未完成记录全部改为被动完成,即:将完成时间改为'2099-01-01 00:00:00',分数改为0。