现有一张试卷作答记录表exam_record,结构如下表,其中包含多年来的用户作答试卷记录,由于数据越来越多,维护难度越来越大,需要对数据表内容做精简,历史数据做备份。
表exam_record
注意新表会自动生成id序列,可以用null代替 或者insert into后面的表名括号中声名从哪里开始插入
– 用法:insert into 新表 (a ,b,)select (A,B)from 旧表 where
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 submit_time < '2021-01-01'
INSERT INTO exam_record_before_2021
SELECT null,uid,exam_id,start_time,submit_time,score FROM exam_record WHERE year(submit_time) <2021
insert into 表名(列名) VALUES (对应列数值)【,(对应列数值)】
注意:即使是Null也不能空着哦
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) 如果把null的地方空着会报错的
关键字NULL可以用DEFAULT替代。
掌握replace into···values的用法
replace into 跟 insert into功能类似,不同点在于:replace into 首先尝试插入数据到表中,
如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据,然后插入新的数据;
否则,直接插入新数据。
要注意的是:插入数据的表必须有主键或者是唯一索引!否则的话,replace into 会直接插入数据,这将导致表中出现重复的数据。
REPLACE INTO examination_info
VALUES(NULL,9003,'SQL','hard',90,'2021-01-01 00:00:00');
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
#注意set后面有多个对象用','而不是'and'连接
update examination_info set tag = 'Python' where tag = 'PYTHON'
现有一张试卷作答记录表exam_record,其中包含多年来的用户作答试卷记录,结构如下表:
请删除exam_record表中所有记录,并重置自增主键。
DROP TABLE, TRUNCATE TABLE, DELETE TABLE 三种删除语句的区别
1.DROP TABLE 清除数据并且销毁表,是一种数据库定义语言(DDL Data Definition Language), 执行后不能撤销,被删除表格的关系,索引,权限等等都会被永久删除。
2.TRUNCATE TABLE 只清除数据,保留表结构,列,权限,索引,视图,关系等等,相当于清零数据,是一种数据库定义语言(DDL Data Definition Language),执行后不能撤销。
3.DELETE TABLE 删除(符合某些条件的)数据,是一种数据操纵语言(DML Data Manipulation Language),执行后可以撤销。(还不太明白怎么撤销TT,在什么情况下可以撤销,求大神指点。
运行速度一般DROP最快,DELETE最慢,但是DELETE最安全。
详细可看 https://www.educba.com/sql-truncate/https://www.educba.com/sql-truncate/
truncate table exam_record
请删除exam_record表中未完成作答或作答时间小于5分钟整的记录中,开始作答时间最早的3条记录。
说明:
条件:
1.未完成作答或作答时间小于5分钟整
where timestampdiff(minute, start_time, submit_time) < 5
or submit_time is null
2.开始作答时间最早的3条记录。
order by start_time
limit 3
考察点:时间差函数的应用和分页的使用
mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15
//为了检索从某一个偏移量到记录集的结束所有的记录行,可以指定第二个参数为 -1:
mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.
//如果只给定一个参数,它表示返回最大的记录行数目:
mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行
//换句话说,LIMIT n 等价于 LIMIT 0,n。
delete from exam_record
where timestampdiff(minute, start_time, submit_time) < 5
or submit_time is null
order by start_time
limit 3
此题的难点是求出两个时间之间的时间差,注意是分钟
所以用时间差函数
timestampdiff(minute,start_time,submit_time)
TIMESTAMPDIFF函数返回begin-end的结果,其中begin和end是DATE或DATETIME表达式。
TIMESTAMPDIFF函数允许其参数具有混合类型,例如,begin是DATE值,end可以是DATETIME值。 如果使用DATE值,则TIMESTAMPDIFF函数将其视为时间部分为“00:00:00”的DATETIME值。
unit参数是确定(end-begin)的结果的单位,表示为整数。 以下是有效单位:
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
delete from exam_record
where timestampdiff(minute, start_time, submit_time) < 5
or submit_time is null
order by start_time
limit 3
create table user_info_vip
(
id int(11) not null primary key auto_increment comment '自增ID',
uid int(11) not null unique comment '用户ID',
nick_name varchar(64) comment '昵称',
achievement int(11) default 0 comment '成就值',
level int(11) comment '用户等级',
job varchar(32) comment '职业方向',
register datetime default current_timestamp comment '注册时间'
)default charset=utf8
CREATE TABLE `user_info_vip`(
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`uid` int(11) 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 '注册时间',
PRIMARY KEY (`id`),
UNIQUE KEY (`uid`)
)COLLATE = utf8_general_ci;
1、 添加列
Alter table 表名add column 列名 类型;(默认添加到表的最后一列)
【first|after 字段名】可以指定位置
Eg:alter table employees add column full_name varchar after last_name;
2、 修改列的类型或约束
Alter table 表名 modify column 列名 新类型【新约束】;
3、 修改列名
Alter table 表名 change column 旧列名 新列名 类型;
4、 删除列
Alter table 表名 drop column 列名;
5、 修改表名
Alter table 表名 rename 【to】新表名;
alter table user_info
add COLUMN school varchar(15) after `level`,
change COLUMN job profession varchar(10),
modify COLUMN achievement int(11) DEFAULT 0;
现有一张试卷作答记录表exam_record,其中包含多年来的用户作答试卷记录。一般每年都会为exam_record表建立一张备份表exam_record_{YEAR},{YEAR}为对应年份。
现在随着数据越来越多,存储告急,请你把很久前的(2011到2014年)备份表都删掉(如果存在的话)。
备注:后台会通过SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_NAME LIKE ‘exam_record_201_’ 来对比输出结果。
drop table if exists
exam_record_2011,exam_record_2012,exam_record_2013,exam_record_2014;
现有一张试卷信息表examination_info,其中包含各种类型试卷的信息。为了对表更方便快捷地查询,需要在examination_info表创建以下索引,规则如下:
在duration列创建普通索引idx_duration、在exam_id列创建唯一性索引uniq_idx_exam_id、在tag列创建全文索引full_idx_tag
create index idx_duration on examination_info(duration);
create unique index uniq_idx_exam_id on examination_info(exam_id);
create fulltext index full_idx_tag on examination_info(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);