DDL中drop-alter table

一、DROP TABLE語句：用于删除資料表

　　DROP TABLE removes one or more tables.

　　You must have the DROP privilege for each table.

　　All table data and the table definition are removed, so be careful with this statement!

基本格式：

　　drop table <table_name>

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

TEMPORARY：用于删除臨時表(推薦針對性使用)，删除臨時表不會結束目前的事務

IF EXISTS：用于在表不存在時，阻止錯誤消息的顯示

[RESTRICT | CASCADE]：現在什麼也不做

其實删表文法如上，還是挺簡單的，但是當一個表被删除時，所有依賴于該表的對象也被删除(例如限制、索引、視圖和權限等)

是以，删除表之前(注意)：

　　①确認這個表沒有人在使用，确實是一個廢除的表

　　②看好确認是這個資料庫的表

順帶提一下，如何重命名表：

RENAME  TABLE  tbl_name TO  new_tbl_name   
　　　　　　  [, tbl_name2 TO new_tbl_name2] …

當然，可以使用ALTER TABLE語句替換該語句，如下……

二、ALTER TABLE語句：添加、修改和删除列

基本格式：

　　1.修改表名：alter table 原表名 rename to 新表名;

　　2.新增列：alter table 表名 add [column] 列名 varchar(20) ;

　　3.删除列：alter table 表名 drop [column] 列名;

　　4.修改列名： alter table 表名 change 原列名新列名 varchar(20) ;

　　5.修改列屬性：alter table 表名 modify 列名 varchar(20) ;

ALTER [IGNORE]  TABLE  tbl_name 
　　 ADD [COLUMN] col_name  column_definition 
　　　　[FIRST | AFTER col_name ] 
　　|ADD [COLUMN] (col_name  column_definition,...)
　　|MODIFY [COLUMN] col_name column_definition
　　　　[FIRST | AFTER col_name] 
　　|DROP [COLUMN] col_name 
　　|CHANGE [COLUMN] old_col_name  new_col_name column_definition 
　　[FIRST|AFTER col_name]

FIRST：表示表頭行

AFTER col_name：表示在col_name的後面，即下一行(沒有before選項)

##Copy一張TEAMS表用作示例

mysql> create table teams_copy

　-> as

　-> select * from TEAMS;

1、添加列

　　①新列自動成為表中的最後一個列，除非指定了FIRST或AFTER選項(表中已有的行在新列上自動得到NULL值或預設值)

例1：添加type列給teams_copy表中

mysql> alter table teams_copy
    -> add type1 char(1);    #添加的新列歸位最後一個列

mysql> alter table teams_copy
    -> add type2 char(1) default'1';    #添加列的同時設定預設值

mysql> alter table teams_copy
    -> add type3 char(1) after PLAYERNO;    #指定新列的位置

mysql> select * from teams_copy;
+--------+----------+-------+----------+-------+-------+
| TEAMNO | PLAYERNO | type3 | DIVISION | type1 | type2 |
+--------+----------+-------+----------+-------+-------+
|      1 |        6 | NULL  | first    | NULL  | 1     |
|      2 |       27 | NULL  | second   | NULL  | 1     |
+--------+----------+-------+----------+-------+-------+

　　②如果添加新列時指定了NOT NULL限制

則：

　　字元串列自動得到空字元串；

　　數字列自動得到0；

　　日期類型自動得到0日期；

　　時間類型自動得到00:00:00

mysql> alter table teams_copy
    -> add (
    ->   type4 char(1) NOT NULL,
    ->   type5 INTEGER NOT NULL,
    ->   type6 DATE NOT NULL,
    ->   tpye7 time not null );

mysql> select * from teams_copy;　　#截的一部分圖

2、删除列

　　列值是否有資料都可以删除，依賴于該列的其它資料庫對象，如索引、權限也将被删除

mysql> alter table teams_copy
    -> drop type6;

mysql> alter table teams_copy drop type2;
mysql> alter table teams_copy drop type4;
mysql> alter table teams_copy drop type5;    #隻能一列一列的删

mysql> select * from teams_copy;
+--------+----------+-------+----------+-------+----------+
| TEAMNO | PLAYERNO | type3 | DIVISION | type1 | tpye7    |
+--------+----------+-------+----------+-------+----------+
|      1 |        6 | NULL  | first    | NULL  | 00:00:00 |
|      2 |       27 | NULL  | second   | NULL  | 00:00:00 |
+--------+----------+-------+----------+-------+----------+

3、修改列---經常用到

　　alter table <表名> modify <列名> 資料類型(……) 選項；

1）修改列的寬度：

　　如果是增加列寬，都無所謂

　　如果是減少列寬，就必須保證原有的值要能放得下，否則出錯

2）改變資料類型時，列中的值必須能轉變為新的類型

mysql> desc student;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | NO   | PRI | NULL    |       |
| name  | varchar(20)   | NO   |     | NULL    |       |
| sex   | enum('M','F') | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from student;
+----+--------+------+
| id | name   | sex  |
+----+--------+------+
|  1 | 張三   | M    |
|  2 | 李四   | F    |
|  5 | 王五   | NULL |
+----+--------+------+
3 rows in set (0.00 sec)

mysql> alter table student
    -> modify id smallint not null;
Query OK, 3 rows affected (0.29 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> alter table student
    -> modify name int not null;
ERROR 1366 (HY000): Incorrect integer value: '張三' for column 'name' at row 1

解析：student表中id列值可轉換為新的資料類型，但是name列的值無法轉變，是以modify失敗。

3）修改列名(不要加引号)

mysql> alter table student
    -> change id 學号 int;

mysql> select * from student;
+--------+--------+------+
| 學号   | name    | sex  |
+--------+--------+------+
|      1 | 張三    | M    |
|      2 | 李四    | F    |
|      5 | 王五    | NULL |
+--------+--------+------+

4、修改限制

　　可以添加或删除限制，但是不能修改一個已有的限制。

ALTER [IGNORE] TABLE tbl_name
　　 ADD [CONSTRAINT [symbol]] PRIMARY KEY(col_name,...)
　　|ADD [CONSTRAINT [symbol]] UNIQUE (col_name,...)
　　|ADD [CONSTRAINT [symbol]] FOREIGN KEY (col_name,...)
　　　　References parent_table(col_name)
　　|DROP PRIMARY KEY
　　|DROP FOREIGN KEY fk_symbo
　　|DROP {INDEX|KEY} index_name

示例：定義兩張表t1和t2

mysql> create table t1(
    -> 　　a int not null primary key,
    -> 　　b int not null);

mysql> create table t2(
    -> 　　a int not null primary key,
    -> 　　b int not null,
    -> 　　constraint c1 unique(b),
    -> 　　constraint fk1 foreign key(a) references t1(a)
    -> );

1）删除唯一性限制：删除對應的索引(index)即可
mysql> alter table t2
    -> drop index c1;

2）給t1添加外鍵限制，取名fk2
mysql> alter table t1
    -> add constraint fk2 foreign key(a) references t2(a);

3）删除外鍵fk2
mysql> alter table t1
    -> drop foreign key fk2;

4）删除t1表的主鍵
mysql> alter table t1
    -> drop primary key;

@author：http://www.cnblogs.com/geaozhang/