【Kay】SQL增删改操作、表的创建与修改

一、增加行

1.增加两行

牛客后台会记录每个用户的试卷作答记录到exam_record表，现在有两个用户的作答记录详情如下：

• 用户1001在2021年9月1日晚上10点11分12秒开始作答试卷9001，并在50分钟后提交，得了90分；
• 用户1002在2021年9月4日上午7点1分2秒开始作答试卷9002，并在10分钟后退出了平台。

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

 

2. 备份/导入新表

我们已经创建了一张新表exam_record_before_2021用来备份试题作答记录，结构和exam_record表一致，请将2021年之前的已完成了的试题作答记录导入到该表

#因为主键id是自增的，新表会自动生成id序列，所以不需要赋值id这一列
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

3.强制插入数据

有一套ID为9003的难度为‘hard'的SQL试卷，时长为一个半小时，请你将 2021-01-01 00:00:00 作为发布时间插入到试题信息表examination_info（其表结构如下图），不管该ID试卷是否存在，都要插入成功

REPLACE INTO examination_info(exam_id,tag,difficulty,duration,release_time)
VALUES(9003,'SQL','hard',90,"2021-01-01 00:00:00")

insert into 最普遍的插入，如果表中存在主键相同的数据，执行会报错

replace into 是强制插入，如果表中存在主键相同的数据则根据主键修改当前主键的数据

以上是限定字段插入。试卷ID列有唯一性索引，自增主键也可直接设置为NULL或0或DEFAULT:

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

 

二、删除某行

1.删除某行

有一张试卷作答记录表exam_record，其中包含多年来的用户作答试卷记录

请删除exam_record表中作答时间小于5分钟整且分数不及格（及格线为60分）的记录

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

2.删除表、重置自增列

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

truncate table exam_record

三、更改某行

1.更新某行

现有一张试卷信息表examination_info，表结构如下图所示：

 把表中tag为PYTHON的字段全部修改为Python

UPDATE examination_info
SET tag = "Python"
WHERE tag = "PYTHON"

2.更新某行

现有一张试卷作答记录表exam_record，其中包含多年来用户作答试卷记录，结构如下表：

请把表中2021年9月1日之前开始作答的未完成记录全部改为被动完成，即：将完成时间改为'2099-01-01 00:00:00'，分数改为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

*插入记录的方式汇总：

• 普通插入（全字段）：INSERT INTO john VALUES (value1, value2, ...)
• 普通插入（限定字段）：INSERT INTO john(column1, column2, ...) VALUES (value1, value2, ...)
• 多条一次性插入：INSERT INTO john(column1, column2, ...) VALUES (value1_1, value1_2, ...), (value2_1, value2_2, ...), ...
• 从另一个表导入：INSERT INTO john SELECT **** FROM john2  [WHERE key=value]

*删除记录的方式汇总：

• 根据条件删除：DELETE FROM john [WHERE *******] [ [ ORDER BY ****** ] LIMIT n ]
• 全部删除（表清空，包含自增计数器重置）：TRUNCATE tb_name

*修改记录的方式汇总：

• 设置为新值：UPDATE john  SET column_name=new_value WHERE column_name=value2

*计算时间差

• TIMESTAMPDIFF(interval, time_start, time_end)可计算time_start-time_end的时间差，单位以指定的interval为准，常用可选：
  • SECOND 秒
  • MINUTE 分钟（返回秒数差除以60的整数部分）
  • HOUR 小时（返回秒数差除以3600的整数部分）
  • DAY 天数（返回秒数差除以3600*24的整数部分）
  • MONTH 月数
  • YEAR 年数

 附：表的创建、修改与删除

• 1.1 直接创建表：

1 2 3 4 5 6 7 8 9 10 11 12 13

CREATE TABLE john (column_name1 data_type1 -- 列名和类型必选   [ PRIMARY KEY -- 可选的约束，主键    | FOREIGN KEY -- 外键，引用其他表的键值    | AUTO_INCREMENT -- 自增ID    | COMMENT comment -- 列注释（评论）    | DEFAULT default_value -- 默认值    | UNIQUE -- 唯一性约束，不允许两条记录该列值相同    | NOT NULL -- 该列非空   ],    ...) [CHARACTER SET charset] -- 字符集编码 [COLLATE collate_value] -- 列排序和比较时的规则（是否区分大小写等）

• 1.2 从另一张表复制表结构创建表： CREATE TABLE john LIKE alice

• 1.3 从另一张表的查询结果创建表： CREATE TABLE john AS SELECT * FROM alice WHERE options

• 2.1 修改表：ALTER TABLE 表名 修改选项 。选项集合：

复制代码

1 2 3 4 5 6 7 8

{ ADD COLUMN <列名> <类型>  -- 增加列  | CHANGE COLUMN <旧列名> <新列名> <新列类型> -- 修改列名或类型  | ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT } -- 修改/删除 列的默认值  | MODIFY COLUMN <列名> <类型> -- 修改列类型  | DROP COLUMN <列名> -- 删除列  | RENAME TO <新表名> -- 修改表名  | CHARACTER SET <字符集名> -- 修改字符集  | COLLATE <校对规则名> } -- 修改校对规则（比较和排序时用到）

• 3.1 删除表：DROP TABLE john

1.请创建一张优质用户信息表user_info_vip，表结构和用户信息表一致

CREATE TABLE user_info_vip(
    id int(11) primary key auto_increment comment "自增ID",
    uid int(11) unique not null comment "用户ID",
    nick_name varchar(64) comment "昵称",
    achievement int(11) default 0 comment "成就值",
    level int(11) comment "用户等级",
    job varchar(32) comment "职业方向",
    register_time datetime default current_timestamp comment "注册时间"
)DEFAULT CHARSET=UTF8;
-- 字符编码默认是utf8mb4，需要将默认编码改成UTF8才能通过。

2.把很久前的（2011到2013年）备份表都删掉（如果存在的话）

DROP TABLE if EXISTS exam_record_2011;
DROP TABLE if EXISTS exam_record_2012;
DROP TABLE if EXISTS exam_record_2013;

3.现有一张试卷信息表examination_info，其中包含各种类型试卷的信息。为了对表更方便快捷地查询，需要在examination_info表创建以下索引，规则如下：

在duration列创建普通索引idx_duration、在exam_id列创建唯一性索引uniq_idx_exam_id、在tag列创建全文索引full_idx_tag。

-- 唯一索引
ALTER TABLE examination_info
ADD UNIQUE INDEX uniq_idx_exam_id(exam_id);
 
-- 全文索引
ALTER TABLE examination_info
ADD FULLTEXT INDEX full_idx_tag(tag);
 
-- 普通索引
ALTER TABLE examination_info
ADD INDEX idx_duration(duration);

4.请删除examination_info表上的唯一索引uniq_idx_exam_id和全文索引full_idx_tag

ALTER TABLE examination_info
DROP INDEX uniq_idx_exam_id;
 
ALTER TABLE examination_info
DROP INDEX full_idx_tag;