Mysql DBA 高级运维学习之路-增删表字段更改表名删除表实战-CSDN博客

1.增加表字段

命令语法： alter table 表名 add 字段类型其他

1.1 查看建表语句和表结构

mysql> show create table test\G
*************************** 1. row ***************************
   Table: test
Create Table: CREATE TABLE `test` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra  |
+-------+----------+------+-----+---------+----------------+
| id| int(4)   | NO   | PRI | NULL| auto_increment |
| name  | char(20) | NO   | | NULL||
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

1.2 添加默认列

例如在表student中添加字段sex，qq类型分别为char(4)，varchar(15)。在test表中添加性别列。

Mysql DBA 高级运维学习之路-增删表字段更改表名删除表实战

mysql> alter table test add sex char(4);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra  |
+-------+----------+------+-----+---------+----------------+
| id| int(4)   | NO   | PRI | NULL| auto_increment |
| name  | char(20) | NO   | | NULL||
| sex   | char(4)  | YES  | | NULL||
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

提示：默认添加列添加到所有字段结尾

1.3 指定添加列在表里的位置

(1)指定添加qq到name列的后面，添加到指定列前面就用before

mysql> alter table test add qq varchar(15) after name;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type| Null | Key | Default | Extra  |
+-------+-------------+------+-----+---------+----------------+
| id| int(4)  | NO   | PRI | NULL| auto_increment |
| name  | char(20)| NO   | | NULL||
| qq| varchar(15) | YES  | | NULL||
| sex   | char(4) | YES  | | NULL||
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

(2)在第一列添加address列

mysql> alter table test add address char(20) first;
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> 
mysql> desc test;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type| Null | Key | Default | Extra  |
+---------+-------------+------+-----+---------+----------------+
| address | char(20)| YES  | | NULL||
| id  | int(4)  | NO   | PRI | NULL| auto_increment |
| name| char(20)| NO   | | NULL||
| qq  | varchar(15) | YES  | | NULL||
| sex | char(4) | YES  | | NULL||
+---------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

2.更改表名

2.1 rename法

命令语法：rename table 原表名 to 新表名；

列如：将表名test更改为student

mysql> rename table test to student;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables; 
+------------------------+
| Tables_in_linzhongniao |
+------------------------+
| student|
+------------------------+
1 row in set (0.00 sec)

2.2 alter方法

将student表改成test

mysql> alter table student rename to test;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+------------------------+
| Tables_in_linzhongniao |
+------------------------+
| test   |
+------------------------+
1 row in set (0.00 sec)

3.删除表

命令语法：drop table<表名>

例如：删除表名为student的表

mysql> drop table test;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.01 sec)

转载于:https://blog.51cto.com/10642812/2065961