表結構的調整與索引
實驗環境：使用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
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: 0
mysql>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;
| 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)
然後再清除主鍵屬性就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; ????????????????????????????????????????//确認設定結果
| 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;
mysql>alter table tea6 add address varchar(48); //為表添加一個字段，添加後（預設作為最後一 個字段）：
ysql> DESC tea6;
+---------+-------------+------+-----+---------+-------+
| address | varchar(48) | YES | | NULL | |
+---------+-------------+------+-----+---------
2）在tea6表的age列之後添加一個gender字段
mysql> alter table tea6 add gender enun('boy','girl') after age; //表中某位置添加新字段
确認添加結果：
+---------+--------------------+------+-----+---------+-------+
| gender | enum('boy','girl') | 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)
确認修改結果：
| sex | enum('boy','girl') | NO | | 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)
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; //變成了新表wwuser
3．設定外鍵
1）建立yg表，用來記錄員工工号、姓名
其中yg_id列作為主鍵，并設定自增屬性
mysql> CREATE TABLE yg( //建立員工表
-> yg_id int(4) AUTO_INCREMENT,
-> name char(16) NOT NULL,
-> PRIMARY KEY(yg_id)
2）建立gz表，用來記錄員工的工資資訊
其中gz_id需要參考員工工号，即gz表的gz_id字段設為外鍵，将yg表的yg_id字段作為參考鍵：
mysql> CREATE TABLE gz( //建立工資表
-> gz_id int(4) 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)
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; // 确認修改結果：
| 1234 | Jerry |
2 rows in set (0.00 sec)：
mysql> SELECT * FROM gz; //同時也會發現，gz表中Jerry使用者的gz_id也跟着變了
| 1234 | Jerry | 12000.00 |
6）驗證表記錄的DELETE删除關聯
mysql>delete from yg where name='Jerry';
Query OK, 1 row affected (0.00 sec)
确認删除結果：
mysql> SELECT * FROM yg;
+-------+------+
1 row in set (0.00 sec)
檢視gz表中的變化（Jerry的記錄也沒了）：
mysql> SELECT * FROM gz;
+-------+------+---------+
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
其中gz_ibfk_1即删除外鍵限制時要用到的名稱。
mysql> ALTER TABLE gz DROP FOREIGN KEY gz_ibfk_1; //删除操作：
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW CREATE TABLE gz\G 确認删除結果：
實驗總結：
1、主鍵的設定用alter table 其他索引的設定直接建立 、删除等操作、
2、删除主鍵的時候，主鍵如果有auto_increment 自增屬性，會出現錯誤提示，必須先删除自增屬性：
alter table tea6 modify id int(4) not null;
本文轉自Jx戰壕 51CTO部落格，原文連結：，http://blog.51cto.com/xujpxm/1377291如需轉載請自行聯系原作者
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)