Mysql DBA 進階營運學習筆記-增刪表欄位更改表名刪除表實戰

標籤：utf8   位置   des   tables   營運學習   alter   ant   nta   empty   

9.12 增刪改表欄位

9.12.1 命令文法及預設添加用示範

1.命令文法： alter table 表名 add 欄位 類型 其他

2.測試表資料

mysql> show create table student\G*************************** 1. row ***************************   Table: studentCreate Table: CREATE TABLE `student` (  `id` int(4) NOT NULL,  `name` char(20) NOT NULL,  `age` tinyint(2) NOT NULL DEFAULT ‘0‘,  `dept` varchar(16) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)mysql> desc student;+-------+-------------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id| int(4)  | NO   | | NULL|   || name  | char(20)| NO   | | NULL|   || age   | tinyint(2)  | NO   | | 0   |   || dept  | varchar(16) | YES  | | NULL|   |+-------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec)

3.實踐

例如在表student中添加欄位sex，qq類型分別為char(4),varchar(15)

4.執行的命令示範

a.添加性別列，預設語句

mysql> desc student;+-------+-------------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id| int(4)  | NO   | | NULL|   || name  | char(20)| NO   | | NULL|   || age   | tinyint(2)  | NO   | | 0   |   || dept  | varchar(16) | YES  | | NULL|   |+-------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec)mysql> alter table student add sex char(4);Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc student;+-------+-------------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id| int(4)  | NO   | | NULL|   || name  | char(20)| NO   | | NULL|   || age   | tinyint(2)  | NO   | | 0   |   || dept  | varchar(16) | YES  | | NULL|   || sex   | char(4) | YES  | | NULL|   |+-------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec)

提示：預設添加列添加到所有欄位結尾

9.12.2 指定添加列在表裡的位置

b.指定添加qq到name列的後面

mysql> alter table student add qq varchar(15) after name;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc student;+-------+-------------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id| int(4)  | NO   | | NULL|   || name  | char(20)| NO   | | NULL|   || qq| varchar(15) | YES  | | NULL|   || age   | tinyint(2)  | NO   | | 0   |   || dept  | varchar(16) | YES  | | NULL|   || sex   | char(4) | YES  | | NULL|   |+-------+-------------+------+-----+---------+-------+6 rows in set (0.00 sec)

c.在第一列添加address列

mysql> alter table student add address char(20) first;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc student;+---------+-------------+------+-----+---------+-------+| Field   | Type| Null | Key | Default | Extra |+---------+-------------+------+-----+---------+-------+| address | char(20)| YES  | | NULL|   || id  | int(4)  | NO   | | NULL|   || name| char(20)| NO   | | NULL|   || qq  | varchar(15) | YES  | | NULL|   || age | tinyint(2)  | NO   | | 0   |   || dept| varchar(16) | YES  | | NULL|   || sex | char(4) | YES  | | NULL|   |+---------+-------------+------+-----+---------+-------+7 rows in set (0.00 sec)

9.13 更改表名

9.13.1 rename法

1.命令文法：rename table 原表名 to 新表名；

列如：表student名字更改為user

mysql> show tables;+---------------+| Tables_in_zbf |+---------------+| student   |+---------------+1 row in set (0.00 sec)mysql> rename table student to user;Query OK, 0 rows affected (0.00 sec)mysql> show tables;+---------------+| Tables_in_zbf |+---------------+| user  |+---------------+1 row in set (0.01 sec)

9.13.2 Alter方法

mysql> alter table user rename to student;Query OK, 0 rows affected (0.01 sec)mysql> show tables;+---------------+| Tables_in_zbf |+---------------+| student   |+---------------+1 row in set (0.00 sec)

9.14刪除表

命令文法：drop table<表名>

例如：刪除表名為student的表

mysql> drop table student;Query OK, 0 rows affected (0.01 sec)mysql> show tables;Empty set (0.01 sec)

Mysql DBA 進階營運學習筆記-增刪表欄位更改表名刪除表實戰