数据库之修改表结构

语法：
1. 修改表名
      ALTER TABLE 表名 
                      RENAME 新表名;

2. 增加字段
      ALTER TABLE 表名
                      ADD 字段名  数据类型 [完整性约束条件…],
                      ADD 字段名  数据类型 [完整性约束条件…];
                            
3. 删除字段
      ALTER TABLE 表名 
                      DROP 字段名;

4. 修改字段
      ALTER TABLE 表名 
                      MODIFY  字段名 数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                      CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                      CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

5.修改字段排列顺序/在增加的时候指定字段位置
    ALTER TABLE 表名
                     ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
    ALTER TABLE 表名
                     ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
    ALTER TABLE 表名
                     CHANGE 字段名  旧字段名 新字段名 新数据类型 [完整性约束条件…]  FIRST;
    ALTER TABLE 表名
                     MODIFY 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;

 

alter操作非空和唯一（了解）

create table t(id int unique,name char(10) not null);

#去掉null约束
alter table t modify name char(10) null;
# 添加null约束
alter table t modify name char(10) not null;


# 去掉unique约束
alter table t drop index id;
# 添加unique约束
alter table t modify id int unique;

# 添加联合唯一
alter table t add unique index(aa,bb);

alter操作非空和唯一（了解）

alter操作主键（了解）

1、首先创建一个数据表table_test：
create table table_test(
`id` varchar(100) NOT NULL,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`name`)
); 
2、如果发现主键设置错了,应该是id是主键,但如今表里已经有好多数据了,不能删除表再重建了，仅仅能在这基础上改动表结构。
先删除主键
alter table table_test drop primary key;
然后再增加主键
alter table table_test add primary key(id);
注:在增加主键之前,必须先把反复的id删除掉。

alter操作主键（了解）

为表添加外键（了解）

创建press表
CREATE TABLE `press` (
  `id` int(11) NOT NULL,
  `name` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ；

创建book表
CREATE TABLE `book` (
  `id` int(11) DEFAULT NULL,
  `bk_name` char(12) DEFAULT NULL,
  `press_id` int(11) NOT NULL,
  KEY `press_id` (`press_id`)
) ；

为book表添加外键
alter table book add constraint fk_id foreign key(press_id) references press(id);

删除外键
alter table book drop foreign key fk_id;

为表添加外键（了解）

示例

 

mysql> desc staff_info;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| phone | bigint(11)            | YES  |     | NULL    |       |
| job   | varchar(11)           | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

# 表重命名
mysql> alter table staff_info rename staff;
Query OK, 0 rows affected (0.00 sec)

mysql> desc staff;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| phone | bigint(11)            | YES  |     | NULL    |       |
| job   | varchar(11)           | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

# 删除sex列
mysql> alter table staff drop sex;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc staff;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | int(3)      | YES  |     | NULL    |       |
| phone | bigint(11)  | YES  |     | NULL    |       |
| job   | varchar(11) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

# 添加列
mysql> alter table staff add sex enum('male','female');
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 修改id的宽度
mysql> alter table staff modify id int(4);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc staff;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(4)                | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
| phone | bigint(11)            | YES  |     | NULL    |       |
| job   | varchar(11)           | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

# 修改name列的字段名
mysql> alter table staff change name sname varchar(20);
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc staff;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(4)                | YES  |     | NULL    |       |
| sname | varchar(20)           | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
| phone | bigint(11)            | YES  |     | NULL    |       |
| job   | varchar(11)           | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

# 修改sex列的位置
mysql> alter table staff modify sex enum('male','female') after sname;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc staff;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(4)                | YES  |     | NULL    |       |
| sname | varchar(20)           | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
| phone | bigint(11)            | YES  |     | NULL    |       |
| job   | varchar(11)           | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

# 创建自增id主键
mysql> alter table staff modify id int(4) primary key auto_increment;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc staff;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(4)                | NO   | PRI | NULL    | auto_increment |
| sname | varchar(20)           | YES  |     | NULL    |                |
| sex   | enum('male','female') | YES  |     | NULL    |                |
| age   | int(3)                | YES  |     | NULL    |                |
| phone | bigint(11)            | YES  |     | NULL    |                |
| job   | varchar(11)           | YES  |     | NULL    |                |
+-------+-----------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

# 删除主键，可以看到删除一个自增主键会报错
mysql> alter table staff drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

# 需要先去掉主键的自增约束，然后再删除主键约束
mysql> alter table staff modify id int(11);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc staff;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | NO   | PRI | 0       |       |
| sname | varchar(20)           | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
| phone | bigint(11)            | YES  |     | NULL    |       |
| job   | varchar(11)           | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

mysql> alter table staff drop primary key;
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

# 添加联合主键
mysql> alter table staff add primary key (sname,age);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 删除主键
mysql> alter table staff drop primary key;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

# 创建主键id
mysql> alter table staff add primary key (id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc staff;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | NO   | PRI | 0       |       |
| sname | varchar(20)           | NO   |     |         |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| age   | int(3)                | NO   |     | 0       |       |
| phone | bigint(11)            | YES  |     | NULL    |       |
| job   | varchar(11)           | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

# 为主键添加自增属性
mysql> alter table staff modify id int(4) auto_increment;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc staff;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(4)                | NO   | PRI | NULL    | auto_increment |
| sname | varchar(20)           | NO   |     |         |                |
| sex   | enum('male','female') | YES  |     | NULL    |                |
| age   | int(3)                | NO   |     | 0       |                |
| phone | bigint(11)            | YES  |     | NULL    |                |
| job   | varchar(11)           | YES  |     | NULL    |                |
+-------+-----------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

示例