MySQL之数据表的修改

       在MySQL运维中，经常会用到alter这个命令来对数据表进行修改，ALTER tables允许修改表的结构，如增加/删除列，创建/删除索引，改变列的类型，对表中的列重新命名，重新命名数据表，修改表的注释等。不同于UPDATE命名，update主要用于修改字段的值。

数据表的修改操作包括数据的添加和删除以及数据的更改，下面一一讲解：

1、添加列

root@localhost test> ALTER TABLE table_name  ADD [COLUMN] col_name  column_defination [FIRST | AFTER] col_name

FIRST 和 AFTER 字段指定了新加入的列位于哪一列的之前或者哪一列的之后，如果省略该字段，则默认添加在表的最后

首先查看一下表 user1 的结构：

root@localhosttest>show columns from user1;

root@localhosttest>ALTER TABLE user1 ADD age TINYINT  NOT NULL DEFAULT 10;

root@localhosttest>show columns from user1;            查看表的结构

接着在uername之后添加一个 password 字段

root@localhosttest>ALTER TABLE user1 ADD password VARCHAR(32) NOT NULL AFTER username;

root@localhosttest>show columns from user1;

那如何添加多列呢，添加多列与添加一列有所不同，主要表现在：

1、添加多列不能指定位置关系，只能添加在数据表的末尾

2、添加多列时必须用小括号括起来

root@localhost test> ALTER TABLE table_name  ADD [COLUMN] (col_name  column_defination,...)

root@localhosttest>ALTER TABLE user1 ADD (col1 TINYINT,col2 TINYINT NOT NULL);

2、删除列

root@localhost test> ALTER TABLE table_name  DROP [COLUMN] col_name 

删除一列的情形：

root@localhosttest>ALTER TABLE user1 DROP age;         删除 age 这列

那如果想吧 col1 和 col2 这两列均删除怎么做呢?

root@localhost test>alter table user1 DROP col1,DROP col2;

3、修改数据表

修改数据表包括添加主键，修改约束等等，其中constraint前缀的含义(PK,UK,DF,CK,FK):

--主键约束：constraint PK_字段 PRIMARY KEY(字段)

--唯一约束：constraint UK_字段 UNIQUE KEY(字段)

--默认约束：constraint DF_字段 DEFAULT('默认值') for 字段

--检查约束：constraint CK_字段 CHECK KEY(约束，如len(字段)>1)

--外键约束：constraint FK_主表_从表 FOREIGN(外键字段) references 主表(主表主键字段)

1、添加主键约束

root@localhost test> ALTER TABLE table_name  ADD [CONSTRAINT [symbol]] PRIMAY KEY [index_type] (index_col_name,...) 

现在先创建一个没有主键的表 user2

root@localhosttest>create table user2(
    -> username varchar(20) not null,
    -> pid tinyint unsigned
    -> );
Query OK, 0 rows affected (0.04 sec)

创建完成之后进行查看：

之后再增加一列 id

root@localhosttest>alter table user2 add id smallint unsigned;

现在将 id 这一列设置为主键

root@localhosttest>alter table user2 add constraint PK_user2_id PRIMARY KEY (id);

constrant 这个关键词可以添加也可以不添加，添加了也可以为这个关键词起一个名字，这里起了 PK_user2_id的名字，其中 PK表示主键的意思；这里将主键跟在 PRIMARY KEY 之后。

2、添加唯一约束

root@localhost test> ALTER TABLE table_name  ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY][index_name] [index_type](index_col_name,...) 

添加主键约束时只能有一个，而唯一约束可以有多个。这里为 username 字段添加唯一约束，这里要注意，主键约束是确定了表中每条记录的唯一性，而唯一约束是确保了了每一列数据的唯一性，例如有一张表记录用户的姓名和身份证号码，定义了id为primary key auto_increment这样在表中id是一个唯一序列，通过id可以识别到用户，同时将username设置为唯一约束

root@localhosttest>alter table user2 add UNIQUE (username);

3、添加外键约束

root@localhost test> ALTER TABLE table_name  ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] [index_type](index_col_name,...) reference_defination

已经知道 province 表中的结构如下：

现在为 user2 中的 pid 去参照 province 表中的 id

root@localhosttest>alter table user2 add foreign key (pid) references (id);

发现添加失败了，这时考虑的添加外键约束的条件，数据类型必须一致，检查user2的数据类型

与 父表中的 id 的类型不同，先修改子表中的数据类型

root@localhosttest>alter table user2 modify column pid smallint(3) unsigned;

此时发现还是添加失败，因为是 id 和 pid 在设置 SET NULL 时不同，这样为了方便，这里不再修改，添加一个 uid 的列

root@localhosttest>alter table user2 add foreign key (uid) references province (id);

再次添加外键约束：

root@localhosttest>alter table user2 add foreign key (uid) references province (id);

4、添加/删除默认约束

root@localhost test> ALTER TABLE table_name  ALTER [COLUMN] col_name {SET DEFAULT literl | DROP DEFAULT}

接着再为表 user2 添加一个字段

root@localhosttest>alter table user2 add age TINYINT UNSIGNED NOT NULL;

现在为age 字段添加默认值为15，则可以进行如下操作：

root@localhosttest>alter table user2 alter age SET DEFAULT 18;

删除age字段的默认值

root@localhosttest>alter table user2 alter age DROP DEFAULT;

5、删除主键约束

root@localhost test> ALTER TABLE table_name DROP PRIMARY  KEY

这里删除表 user2 的主键约束，因为主键只有一个，因此不需要加索引

root@localhosttest>ALTER table user2 DROP PRIMARY KEY;

6、删除唯一约束

因为一个表中可以有多个唯一约束因此不同与删除主键约束，这里需要指定索引

root@localhost test> ALTER TABLE table_name DROP {INDEX | KEY} index_name

root@localhosttest>show indexes from user2\G;

删除唯一约束：

root@localhosttest>alter table user2 drop index username;

这里一定要和删除字段区分开来，查看字段仍然存在

但是约束不存在了

7、删除外键约束

root@localhost test> ALTER TABLE table_name DROP FOREIGN KEY fk_symbol

查看表中哪个是外键约束

其中 user2_ibfk_1 是系统赋予这张表的，这样就可以删除了

root@localhosttest>alter table user2 DROP FOREIGN KEY user2_ibfk_1;

发现外键已经不存在，如若想要删除 uid 的索引

root@localhosttest>alter table user2 drop index uid;

4、修改列操作

针对字段的操作在3中基本完成，现在来看一下如何修改列相关的，如更改定义和名称等

root@localhost test> ALTER TABLE table_name MODIFY [COLUMN] col_name col_defination [FIRST | AFTER colname]

在 表 user2 中，发现 id 不在第一位置，尽管表中的位置先后没有影响，但是习惯将 id 放置在第一列，因此可以：

只是更改位置，将其相应的字段定义按上述写进来就可以

root@localhosttest>alter table user2 MODIFY id smallint(5) unsigned NOT NULL FIRST;

现在将id 的类型进行修改为 TINYINT类型

root@localhosttest>alter table user2 MODIFY id TINYINT(5) unsigned NOT NULL;

修改类型的时候要注意，从高精度修改为低精度类型，可能会造成数据精度丢失的问题，也有可能造成数据丢失，如之前的ID 定义。为 smallint unsigned 为 0-65535，改成tinyint unsigned 之后为 0-255，这样如果有5000条记录会造成丢失

root@localhost test> ALTER TABLE table_name CHANGE [COLUMN] old_col_name new_col_name col_defination [FIRST | AFTER colname]

使用 change 不仅可以修改列定义，还可以修改列名称，其功能远大于modify

root@localhosttest>alter table user2 CHANGE pid p_id tinyint unsigned NOT NULL;

这里将id 更改为 p_id ，将其字段数据类型由smallint(5) unsigned 更改为 tinyint unsigend

5、修改数据表的名称

修改数据表的名称通常有两种方法，分别是使用关键字 ALTER 和 RENAME.

1、使用 ALTER 修改数据表名称

root@localhost test> ALTER TABLE table_name RENAME [TO|AS] new_table_name将

将数据表 user2 更名为 user2_new

root@localhosttest>alter table user2 RENAME user2_new;

2、使用 RENAME修改数据表名称

root@localhost test> RENAME TABLE table_name TO new_table_name [,table_name2 TO new_table_name2]...

root@localhosttest>RENAME table user2_new TO new 2;

注意尽量少用修改表名和列名的操作，如果表名或者列名被引用了之后，修改之后可能导致无法使用。