表结构的调整与索引
实验环境：使用64位的RHEL 6.4操作系统，MySQL数据库的版本是5.6.15。 一、练习普通索引、唯一索引、主键索引的创建、删除1）创建表的时候指定INDEX索引字段：index
允许有多个INDEX索引字段。比如，以下操作在test库中创建了tea4表，将其中的id、name作为索引字段：
1. mysql> USE test; 2. Reading table information for completion of table and column names 3. You can turn off this feature to get a quicker startup with -A 4. 5. Database changed 6. mysql> CREATE TABLE tea4( 7. -> id char(6) NOT NULL, 8. -> name varchar(4) NOT NULL, 9. -> age int(3) NOT NULL, 10. -> gender ENUM('boy','girl') DEFAULT 'boy', 11. -> INDEX(id), INDEX(name) 12. -> ); 13. Query OK, 0 rows affected (0.22 sec) 查看新建tea4表的字段结构，可以发现两个非空索引字段的KEY标志为MUL：1. mysql> DESC tea4; 2. +--------+--------------------+------+-----+---------+-------+ 3. | Field | Type | Null | Key | Default | Extra | 4. +--------+--------------------+------+-----+---------+-------+ 5. | id | char(6) | NO | MUL | NULL | | 6. | name | varchar(4) | NO | MUL | NULL | | 7. | age | int(3) | NO | | NULL | | 8. | gender | enum('boy','girl') | YES | | boy | | 9. +--------+--------------------+------+-----+---------+-------+ 10. 4 rows in set (0.32 sec)2）删除现有表的某个INDEX索引字段比如，删除tea4表中名称为named的INDEX索引字段：
1. mysql> DROP INDEX name ON tea4; //删除name字段的索引
2. Query OK, 0 rows affected (0.08 sec) 3. Records: 0 Duplicates: 0 Warnings: 0 4. 5. mysql> DESC tea4; //确认删除结果 6. +--------+--------------------+------+-----+---------+-------+ 7. | Field | Type | Null | Key | Default | Extra | 8. +--------+--------------------+------+-----+---------+-------+ 9. | id | char(6) | NO | MUL | NULL | | 10. | name | varchar(4) | NO | | NULL | | 11. | age | int(3) | NO | | NULL | | 12. | gender | enum('boy','girl') | YES | | boy | | 13. +--------+--------------------+------+-----+---------+-------+3）在已有的某个表中设置INDEX索引字段：
比如，针对tea4表的age字段建立索引，名称为 nianling：
1. mysql> CREATE INDEX nianling ON tea4(age); //针对指定字段创建索引 2. Query OK, 0 rows affected (0.09 sec)mysql> DESC tea4; //确认创建结果
4）查看指定表的索引信息
mysql> SHOW INDEX FROM tea4\G //查看指定表的索引信息（以列表形式显示）
5）创建表的时候指定UNIQUE索引字段：unique
UNIQUE表示唯一性的意思，同一个表中可以有多个字段具有唯一性。
比如，创建tea5表，将id、name字段建立设置UNIQUE索引，age字段设置INDEX索引：
1. mysql> CREATE TABLE tea5( 2. -> id char(6), 3. -> name varchar(4) NOT NULL, 4. -> age int(3) NOT NULL, 5. -> UNIQUE(id),UNIQUE(name),INDEX(age) 6. -> ); 7. Query OK, 0 rows affected (0.06 sec) 查看新建tea5表的字段结构，可发现UNIQUE字段的KEY标志为UNI；另外，由于字段name必须满足“NOT NULL”的非空约束，所以将其设置为UNIQUE后会自动变成了PRIMARY KEY主键字段：mysql> DESC tea5； //确认设置结果
+-------+------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+------------+------+-----+---------+-------+| id | char(6) | YES | UNI | NULL | || name | varchar(4) | NO | PRI | NULL | || age | int(3) | NO | MUL | NULL | |+-------+------------+------+-----+---------+-------+6）删除UNIQUE索引、在已有的表中设置UNIQUE索引字段
先删除tea5表name字段的唯一索引（与删除INDEX索引的方法相同）：mysql> drop index name on tea5;
Query OK, 0 rows affected (0.27 sec) Records: 0 Duplicates: 0 Warnings: 0mysql>create unique index name on tea5(name); //重新为tea5表的name字段建立UNIQUE索引
mysql>desc tea5;
//确认结果+-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | id | char(6) | YES | UNI | NULL | | | name | varchar(4) | NO | PRI | NULL | | | age | int(3) | NO | MUL | NULL | | +-------+------------+----7）建表时设置PRIMARY KEY主键索引 主键索引实际上在前面已经接触过了，建表的时候可以直接指定。如果表内一开始没有主键字段，则新设置的非空UNIQUE字段相当于具有PRIMARY KEY主键约束。 每个表中的主键字段只能有一个。 建表的时候，可以直接在某个字段的“约束条件”部分指定PRIMARY KEY；也可以在最后指定PRIMARY KEY(某个字段名)。比如：mysql>create table biao01(
->id int(4) primary key, //直接在字段定义时约束主键
->name varchar(8),->);或者
mysql>create table biao02(
-> id int(4),-> name varchar(8),-> primary key (id) //所有字段定义完，最后指定主键-> );在建表的时候，如果主键字段为int类型，还可以为其设置AUTO_INCREMENT自增属性，这样当添加新的表记录时，此字段的值会自动从1开始逐个增加，无需手动指定。比如，新建一个tea6表，将id列作为自增的主键字段：
mysql> CREATE TABLE tea6(
-> id int(4) auto increment, -> name varchar(4) NOT NULL, -> age int(2) NOT NULL, -> PRIMARY KEY(id) -> ); Query OK, 0 rows affected (0.26 sec)8）删除现有表的PRIMARY KEY主键索引
如果要移除某个表的PRIMARY KEY约束，需要通过ALTER TABLE指令修改。比如，以下操作将清除biao01表的主键索引。mysql>alter table biao01 drop primary key ; //清除表的主键用
Query OK, 0 rows affected (6.35 sec)清除后（无主键）
mysql> DESC biao01;
+-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | id | int(4) | NO | | NULL | | | name | varchar(8) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ 2 rows in set (0.12 sec)当尝试删除tea6表的主键时，会出现异常：
mysql> ALTER TABLE tea6 DROP PRIMARY KEY;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key 这是因为tea6表的主键字段id具有AUTO_INCREMNET自增属性，提示这种字段必须作为主键存在，因此若要清除此主键必须先清除自增属性——修改id列的字段定义：mysql> ALTER TABLE tea6 MODIFY id int(4) NOT NULL; //清除之前先清除自增属性
Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0
然后再清除主键属性就OK了：
mysql> ALTER TABLE tea6 DROP PRIMARY KEY; ????????????????//清除主键mysql> DESC tea6; ??9）为现有表添加PRIMARY KEY主键索引
重新为tea6表指定主键字段，仍然使用id列： mysql>alter table tea6 add primarty key(id); //在已有的表中插入主键Query OK, 0 rows affected (0.15 sec)mysql> DESC tea6; ????????????????????????????????????????//确认设置结果
+-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | id | int(4) | NO | PRI | NULL | | | name | varchar(4) | NO | | NULL | | | age | int(2) | NO | | NULL | | +-------+------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)二、表结构的调整
1．修改现有表的字段 1）为tea6表添加一个address字段 添加前：mysql> DESC tea6;
+-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | id | int(4) | NO | PRI | NULL | | | name | varchar(4) | NO | | NULL | | | age | int(2) | NO | | NULL | | +-------+------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)mysql>alter table tea6 add address varchar(48); //为表添加一个字段，添加后（默认作为最后一 个字段）：
ysql> DESC tea6;
+---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id | int(4) | NO | PRI | NULL | | | name | varchar(4) | NO | | NULL | | | age | int(2) | NO | | NULL | | | address | varchar(48) | YES | | NULL | | +---------+-------------+------+-----+---------2）在tea6表的age列之后添加一个gender字段
mysql> alter table tea6 add gender enun('boy','girl') after age; //表中某位置添加新字段
Query OK, 0 rows affected (0.10 sec) 确认添加结果：mysql> DESC tea6;
+---------+--------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------------+------+-----+---------+-------+ | id | int(4) | NO | PRI | NULL | | | name | varchar(4) | NO | | NULL | | | age | int(2) | NO | | NULL | | | gender | enum('boy','girl') | YES | | NULL | | | address | varchar(48) | YES | | NULL | | +---------+--------------------+------+-----+---------+-------+ 3）将tea6表的gender字段改名为sex，并添加非空约束 修改操作：mysql>alter table tea6 change gender sex enum('boy','girl') not null; //修改表中某字段名Query OK, 0 rows affected (0.08 sec) 确认修改结果：mysql> DESC tea6;
+---------+--------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------------+------+-----+---------+-------+ | id | int(4) | NO | PRI | NULL | | | name | varchar(4) | NO | | NULL | | | age | int(2) | NO | | NULL | | | sex | enum('boy','girl') | NO | | NULL | | | address | varchar(48) | YES | | NULL | | +---------+--------------------+------+-----+---------+-------+ 5 rows in set (0.01 sec)??????4）删除tea6表中名为sex的字段
删除操作：mysql> ALTER TABLE tea6 DROP sex;
Query OK, 0 rows affected (0.07 sec)mysql> DESC tea6; // 确认删除结果：
?? 2．表的复制及改名1）将mysql库的user表复制到当前库，作为新表yyuser
这个实际上是先获取源表的所有数据（SELECT * 可选择所有字段，未加条件则包括所有记录），然后在用来建立新表的：mysql> CREATE TABLE yyuser SELECT * FROM mysql.user; //复制表，包括表结构和表中数据Query OK, 4 rows affected (0.22 sec) Records: 4 Duplicates: 0 Warnings: 0 2）将mysql库的user表复制为当前库的新表zzuser，仅包括部分字段 只选择源表的user,host,password这三个字段，存入新表：mysql> CREATE TABLE zzuser
-> SELECT user,host,password FROM mysql.user; //只选择表中的某字段存入新表Query OK, 4 rows affected (0.03 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM zzuser; //输出zzuser表的所有记录进行确认： 3）复制表的时候，只复制表结构，不含数据 这个可以在获取源表数据时指定条件“WHERE FALSE”，于是对任何一条表记录条件都不成立，从而满足只复制表结构而不含任何记录的要求。 比如，复制mysql库的user表的三个字段（user，host，password）结构，保存为vvuser表，不含任何表记录：mysql>create table vvuser select user,host,password from mysql.user
->where 1=2; //复制表中字段结构mysql> SELECT * FROM vvuser; //使用SELECT查询vvuser表，可发现无任何记录：
mysql> DESC vvuser; //使用DESC查看vvuser表结构，可确认已成功复制了三个 4）将vvuser表改名为wwuser 改名操作：mysql> ALTER TABLE vvuser RENAME TO wwuser; //表改名 rename to
Query OK, 0 rows affected (0.06 sec) 确认修改结果：mysql> DESC vvuser; //原来的vvuser已经没有了
ERROR 1146 (42S02): Table 'test.vvuser' doesn't exist mysql> DESC wwuser; //变成了新表wwuser3．设置外键
1）创建yg表，用来记录员工工号、姓名 其中yg_id列作为主键，并设置自增属性mysql> CREATE TABLE yg( //创建员工表
-> yg_id int(4) AUTO_INCREMENT, -> name char(16) NOT NULL, -> PRIMARY KEY(yg_id) -> ); Query OK, 0 rows affected (0.06 sec)
2）创建gz表，用来记录员工的工资信息 其中gz_id需要参考员工工号，即gz表的gz_id字段设为外键，将yg表的yg_id字段作为参考键：
mysql> CREATE TABLE gz( //创建工资表
-> gz_id int(4) NOT NULL, -> name char(16) NOT NULL, -> gz float(7,2) NOT NULL DEFAULT 0, -> INDEX(name), -> FOREIGN KEY(gz_id) REFERENCES yg(yg_id) //设置外键 gz_id ,参考建yg(yg_id)-> ON UPDATE CASCADE ON DELETE CASCADE -> ); Query OK, 0 rows affected (0.10 sec) 3）为yg表添加2条员工信息记录mysql> INSERT INTO yg(name) VALUES('Jerry'),('Tom'); //添加员工信息
Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM yg; //确认yg表的数据记录：+-------+-------+
| yg_id | name | +-------+-------+ | 1 | Jerry | | 2 | Tom | +-------+-------+ 2 rows in set (0.00 sec)4）为gz表添加2条工资信息记录
同上，数据参考图-2，插入相应的工资记录（gz_id字段未指定默认值，也未设置自增属性，所以需要手动赋值）：mysql> INSERT INTO gz(gz_id,name,gz)
-> VALUES(1,'Jerry',12000),(2,'Tom',8000) -> ; Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM gz; //确认gz表的数据记录：
+-------+-------+----------+ | gz_id | name | gz | +-------+-------+----------+ | 1 | Jerry | 12000.00 | | 2 | Tom | 8000.00 | +-------+-------+----------+ 2 rows in set (0.05 sec)5）验证表记录的UPDATE更新联动
将yg表中Jerry用户的yg_id修改为1234：mysql>update yg set yg_id=1234 where name='Jerry'; //更新表中信息Query OK, 1 row affected (0.08 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM yg; // 确认修改结果： +-------+-------+ | yg_id | name | +-------+-------+ | 2 | Tom | | 1234 | Jerry | +-------+-------+ 2 rows in set (0.00 sec)：mysql> SELECT * FROM gz; //同时也会发现，gz表中Jerry用户的gz_id也跟着变了
+-------+-------+----------+ | gz_id | name | gz | +-------+-------+----------+ | 1234 | Jerry | 12000.00 | | 2 | Tom | 8000.00 | +-------+-------+----------+ 2 rows in set (0.00 sec)6）验证表记录的DELETE删除联动
mysql>delete from yg where name='Jerry';Query OK, 1 row affected (0.00 sec) 确认删除结果：mysql> SELECT * FROM yg;
+-------+------+ | yg_id | name | +-------+------+ | 2 | Tom | +-------+------+ 1 row in set (0.00 sec) 查看gz表中的变化（Jerry的记录也没了）：mysql> SELECT * FROM gz;
+-------+------+---------+ | gz_id | name | gz | +-------+------+---------+ | 2 | Tom | 8000.00 | +-------+------+---------+ 1 row in set (0.00 sec) 7）删除指定表的外键约束 先通过SHOW指令获取表格的外键约束名称：mysql> SHOW CREATE TABLE gz\G
*************************** 1. row *************************** Table: gz Create Table: CREATE TABLE `gz` ( `gz_id` int(4) NOT NULL, `name` char(16) NOT NULL, `gz` float(7,2) NOT NULL DEFAULT '0.00', KEY `name` (`name`), KEY `gz_id` (`gz_id`), CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) 其中gz_ibfk_1即删除外键约束时要用到的名称。mysql> ALTER TABLE gz DROP FOREIGN KEY gz_ibfk_1; //删除操作：
Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE gz\G 确认删除结果：
实验总结： 1、主键的设置用alter table 其他索引的设置直接创建 、删除等操作、2、删除主键的时候，主键如果有auto_increment 自增属性，会出现错误提示，必须先删除自增属性： alter table tea6 modify id int(4) not null;