数据库系统原理与应用教程（068）—— MySQL 练习题：操作题 90-94（十二）：DML 语句练习

90、创建表

现有一张用户信息表（user_info），表结构如下：

【问题】，请创建一张 vip 用户信息表（user_info_vip），表结构和用户信息表一致。

解答：

/*
create table user_info_vip(
    id int primary key auto_increment comment '自增ID',
    uid int not null comment '用户ID',
    nick_name varchar(64) default 0 comment '昵称',
    achievement int comment '成就值',
    level int comment '用户等级',
    job varchar(32) comment '职业方向',
    register_time datetime default CURRENT_TIMESTAMP comment '注册时间',
    unique(uid)
) CHARACTER SET utf8 COLLATE utf8_general_ci;;
*/

mysql> mysql> SHOW FULL FIELDS FROM user_info_vip;
+---------------+-------------+-----------------+------+-----+-------------------+----------------+
| Field         | Type        | Collation       | Null | Key | Default           | Extra          | Privileges                      | Comment      |
+---------------+-------------+-----------------+------+-----+-------------------+----------------+
| id            | int(11)     | NULL            | NO   | PRI | NULL              | auto_increment | select,insert,update,references | 自增ID       |
| uid           | int(11)     | NULL            | NO   | UNI | NULL              |                | select,insert,update,references | 用户ID       |
| nick_name     | varchar(64) | utf8_general_ci | YES  |     | 0                 |                | select,insert,update,references | 昵称         |
| achievement   | int(11)     | NULL            | YES  |     | NULL              |                | select,insert,update,references | 成就值       |
| level         | int(11)     | NULL            | YES  |     | NULL              |                | select,insert,update,references | 用户等级     |
| job           | varchar(32) | utf8_general_ci | YES  |     | NULL              |                | select,insert,update,references | 职业方向     |
| register_time | datetime    | NULL            | YES  |     | CURRENT_TIMESTAMP |                | select,insert,update,references | 注册时间     |
+---------------+-------------+-----------------+------+-----+-------------------+----------------+
7 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

91、修改表结构

用户信息表user_info，表结构如下：

【问题】请在用户信息表 level 列的后面增加一列，列名为：school，最多可保存 15 个汉字；并将 job 列的列名修改为 profession，同时 varchar 字段长度变为 10；achievement 列的默认值设置为 0。修改之后的结果如下：

表结构如下：

/*
drop table if exists user_info;

CREATE TABLE IF NOT EXISTS user_info (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int UNIQUE NOT NULL COMMENT '用户ID',
`nick_name` varchar(64) COMMENT '昵称',
achievement int COMMENT '成就值',
level int COMMENT '用户等级',
job varchar(10) COMMENT '职业方向',
register_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
*/
1
2
3
4
5
6
7
8
9
10
11
12
13

解答：

/*
alter table user_info add school varchar(15) after level;
alter table user_info change job profession varchar(10);
alter table user_info modify achievement int default 0;
*/
mysql> alter table user_info add school varchar(15) after level;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table user_info change job profession varchar(10);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table user_info modify achievement int default 0;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user_info;
+---------------+-------------+------+-----+-------------------+----------------+
| Field         | Type        | Null | Key | Default           | Extra          |
+---------------+-------------+------+-----+-------------------+----------------+
| id            | int(11)     | NO   | PRI | NULL              | auto_increment |
| uid           | int(11)     | NO   | UNI | NULL              |                |
| nick_name     | varchar(64) | YES  |     | NULL              |                |
| achievement   | int(11)     | YES  |     | 0                 |                |
| level         | int(11)     | YES  |     | NULL              |                |
| school        | varchar(15) | YES  |     | NULL              |                |
| profession    | varchar(10) | YES  |     | NULL              |                |
| register_time | datetime    | YES  |     | CURRENT_TIMESTAMP |                |
+---------------+-------------+------+-----+-------------------+----------------+
8 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

92、删除表

现有一张试卷答题记录表：exam_record，一般每年都会为 exam_record 表建立一张备份表 exam_record_{YEAR}，{YEAR}为对应年份。请把很久前的（2011到2014年）备份表都删掉（如果存在的话）。

表结构及数据如下：

/*
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_2010 (LIKE exam_record); 
CREATE TABLE IF NOT EXISTS exam_record_2012 (LIKE exam_record); 
CREATE TABLE IF NOT EXISTS exam_record_2013 (LIKE exam_record); 
CREATE TABLE IF NOT EXISTS exam_record_2014 (LIKE exam_record); 
CREATE TABLE IF NOT EXISTS exam_record_2015 (LIKE exam_record); 
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

解答：

mysql> SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_NAME LIKE 'exam_record_201_';
+------------------+
| TABLE_NAME       |
+------------------+
| exam_record_2010 |
| exam_record_2012 |
| exam_record_2013 |
| exam_record_2014 |
| exam_record_2015 |
+------------------+
5 rows in set (0.05 sec)

/*
drop table if exists exam_record_2011;
drop table if exists exam_record_2012;
drop table if exists exam_record_2013;
drop table if exists exam_record_2014;
*/
mysql> drop table if exists exam_record_2011;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop table if exists exam_record_2012;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists exam_record_2013;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table if exists exam_record_2014;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_NAME LIKE 'exam_record_201_';
+------------------+
| TABLE_NAME       |
+------------------+
| exam_record_2010 |
| exam_record_2015 |
+------------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

93、创建索引

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

为了对表更方便快捷地查询，需要在 examination_info 表创建以下索引，规则如下：在 duration 列创建普通索引idx_duration，在 exam_id 列创建唯一性索引 uniq_idx_exam_id、在 tag 列创建全文索引 full_idx_tag。结果如下：

表结构及数据如下：

/*
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;
*/
1
2
3
4
5
6
7
8
9
10
11

解答：

mysql> SHOW INDEX FROM examination_info;
+------------------+------------+----------+--------------+-------------+-----------+-------------
| Table            | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality 
+------------------+------------+----------+--------------+-------------+-----------+-------------+
| examination_info |          0 | PRIMARY  |            1 | id          | A         |           0 |
| examination_info |          0 | exam_id  |            1 | exam_id     | A         |           0 |
+------------------+------------+----------+--------------+-------------+-----------+-------------+
2 rows in set (0.01 sec)

/*
create index dx_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);
*/
mysql> SHOW INDEX FROM examination_info;
+------------------+------------+------------------+--------------+-------------+-----------+
+| Table            | Non_unique | Key_name         | Seq_in_index | Column_name | Collation |
|+------------------+------------+------------------+--------------+-------------+-----------+
+| examination_info |          0 | PRIMARY          |            1 | id          | A         |
|| examination_info |          0 | exam_id          |            1 | exam_id     | A         |
|| examination_info |          0 | uniq_idx_exam_id |            1 | exam_id     | A         |
|| examination_info |          1 | dx_duration      |            1 | duration    | A         |
|| examination_info |          1 | full_idx_tag     |            1 | tag         | NULL      |
|+------------------+------------+------------------+--------------+-------------+-----------+
+5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

94、删除索引

【问题】请删除 examination_info 表上的唯一索引 uniq_idx_exam_id 和全文索引 full_idx_tag。

解答：

mysql> SHOW INDEX FROM examination_info;
+------------------+------------+------------------+--------------+-------------+-----------+
+| Table            | Non_unique | Key_name         | Seq_in_index | Column_name | Collation |
|+------------------+------------+------------------+--------------+-------------+-----------+
+| examination_info |          0 | PRIMARY          |            1 | id          | A         |
|| examination_info |          0 | exam_id          |            1 | exam_id     | A         |
|| examination_info |          0 | uniq_idx_exam_id |            1 | exam_id     | A         |
|| examination_info |          1 | dx_duration      |            1 | duration    | A         |
|| examination_info |          1 | full_idx_tag     |            1 | tag         | NULL      |
|+------------------+------------+------------------+--------------+-------------+-----------+
+5 rows in set (0.00 sec)

/*
drop index uniq_idx_exam_id on examination_info;
drop index full_idx_tag on examination_info;
*/
mysql> drop index uniq_idx_exam_id on examination_info;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop index full_idx_tag on examination_info;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM examination_info;
+------------------+------------+-------------+--------------+-------------+-----------+
| Table            | Non_unique | Key_name    | Seq_in_index | Column_name | Collation |
+------------------+------------+-------------+--------------+-------------+-----------+
| examination_info |          0 | PRIMARY     |            1 | id          | A         |
| examination_info |          0 | exam_id     |            1 | exam_id     | A         |
| examination_info |          1 | dx_duration |            1 | duration    | A         |
+------------------+------------+-------------+--------------+-------------+-----------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33