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

90、创建表

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

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

解答：

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)

91、修改表结构

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

【问题】请在用户信息表 level 列的后面增加一列，列名为：school，最多可保存 15 个汉字；并将 job 列的列名修改为 profession，同时 varchar 字段长度变为 10；achievement 列的默认值设置为 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)

92、删除表

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

表结构及数据如下：

解答：

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)


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)

93、创建索引

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

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

表结构及数据如下：

解答：

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)


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)

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)


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)