MySQL系统学习三：管理表语法之alter table

学习MySQL管理表语句之前先来学习几个查看表相关信息的SQL语句：

##表状态
SHOW TABLE STATUS LIKE 'tableName' 
##like语法  like的是字符串

##查看表结构的3种语法
DESC tableName
DESCRIBE tableName
show [full] columns from tableName 
##columns[ˈkɑ:ləm] 字段、列

MySQL的管理表语句alter table主要用于对MySQL表的结构管理，即如下几项管理操作：

1、列的增删改，即字段的新增、删除、修改；

2、列索引的增删改查，即字段的索引的新增、删除、修改；

3、修改表名和表属性，如修改表的存储引擎、字符集、排序集、表注释。

一、编辑表本身

1.1、修改表名

##语法一：alter与rename配合的语法
alter table old_table_name rename new_table_name
##语法二：rename to的语法
rename table old_table_name to new_table_name
##语法三：从一个库将表移动到另外一个库
---第一种
alter table database1.old_table_name rename database2.new_table_name
---第二种
rename table database1.old_table_name to database2.new_table_name
---其中old_table_name可以与new_table_name一样，实现数据库之间的表移动

修改表名有两种语法，其中alter table rename是标准SQL里的，以此语法为准。

1.2、修改表的字符集和校队集

##修改表的默认字符集和校队集（排序集）
ALTER TABLE table_name DEFAULT CHARACTER SET character_name collate collation_name
--此语法仅修改table_name表的默认字符集合排序集，并不会修改已有记录的字符集和排序集
ALTER TABLE table_name CONVERT TO CHARACTER SET character_name collate collation_name
--此语法会修改table_name的默认字符集和排序集，也会修改表中已有记录的字符集和排序集

修改表的字符集和校队集，涉及到表的默认字符集和校队集，以及已存在数据的字符集和校队集，第一种方法适用于表内数据为空，直接修改表的默认字符集和校队集，那么新插入的数据就将是修改后的字符集和校队集；第二种方法适用于表内已有数据时修改表的字符集和校队集，这种方法会将已有记录转换为目标字符集和校队集，然后将表的默认字符集和校队集修改。

二、编辑字段名和字段属性

2.1、增加字段

##增加字段结构语法
ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER exist_col_name]

其中column_definition与创建表时定义字段属性的语法一致，不再累述。

而可选的FIRST、AFTER语句则可以指定新增的字段的位置，即新增的字段相对于已存在的列的位置。其中FIRST后方无需跟随已存在的字段名，表示在表字段的第一列之前添加新字段，即新增的字段将作为第一列；而使用AFTER则后方需要跟随已存在的字段名，表示在这个已存在的字段名后方添加这个新字段。

---在表blog开头位置新增一个字段test
ALTER TABLE  `blog` ADD  `test` VARCHAR( 32 ) NULL DEFAULT  '1' COMMENT  '新增的字段' FIRST
--在表blog的字段title后新增一个字段contents
ALTER TABLE  `blog` ADD  `contents` TEXT NULL COMMENT  '在某个字段后新增一个字段' AFTER  `title`

注意SQL语句的注释写法，以及SQL语句中的关键字不区分大小写的特性。

2.2、删除字段

##删除字段语法结构，其中COLUMN是可选关键词
ALTER TABLE tbl_name DROP [COLUMN] col_name

删除字段的drop语句与删除数据库database、表的语法类似；只不过没有删除库和表的判断语法：if exists和if not exists ，来一个对比：

---删除某个字段
alter table test1 drop column title;
--删除库
drop database if exists test;
--删除表
drop talbe if exists test;

2.3、修改字段

修改字段的SQL语句结构定义为：

###修改表字段相关
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

使用modify和change对字段进行修改时MySQL会自动将已有记录对应的列值进行转换，这个转换可能会导致已有数据精度或结构的变化。

修改字段有多种情况：仅修改字段名、仅修改字段的属性、同时修改字段名和字段属性。修改字段名称和字段属性的关键字语句有两个：CHANGE、MODIFY

关于两则的区别，MySQL参考手册是这样说的：如果使用CHANGE仅修改字段的属性（也就是说字段名不变），那么上述语句结构中的old_col_name new_col_name column_definition任何一部分都不可缺少，即old_col_name与new_col_name是一摸一样的；而使用MODIFY关键字时new_col_name是不需要的。最关键的一点：MODIFY是MySQL兼容Oracle的语法规则，除了不用于修改字段名的功能之外，MODIFY的功能与CHANGE无异。

还有FIRST和AFTER col_name的待选语法，这个与新建字段时指定新建的字段位置功能类似，只不过与CHANGE或MODIFY搭配时含义就变成了移动现有字段的排序结构，当然在移动现有字段的列顺序时依然可以对该字段进行名称和属性的修改（注意change与modify的区别）。

2.3.1、仅修改字段名

---注意下面这个语句是会出错的，因为没有显式定义字段属性
alter table test change title_1 title_2;
---这个才是正解
alter table test change title_1 title_2 char(64) not null;

modify关键字是无法修改字段名的，使用change修改字段名时依然要显式的定义该字段属性。

2.3.2、仅修改字段属性

此时modify和change两个关键字都可以使用。

---仅修改字段属性
alter table test modify title_2 char(64) not null default '1';
alter table test modify column title_2 char(64) not null default '1';
---
alter table test change title_2 title_2 char(64) not null default '1';
alter table test change column title_2 title_2 char(32) not null default '1';

注意上述语句中的可省略关键字的差异，以及modify和change关键字的差异；经过测试仅变动字段属性时若定义的新的属性与原先属性无差异，也正常执行。

2.3.3、同时修改字段名称和属性

仅能使用change语句，modify语句无法修改字段名。

---下方这个语句是出错的，modify关键字无法修改字段名
alter table test modify column title_2 title_3 char(32) not null default '2';
---下方这个语句正常执行
alter table test change title_2 title_3 char(32) not null default '2';
---或
alter table test change column title_2 title_3 char(32) not null default '2';

2.3.4、移动字段

将某个字段移动到第一列：

---将title_3字段移动到第一列（注意行与列的区别）
alter table test change column title_3 title_3 char(32) not null default '2' first;
---或
alter table test modify column title_3 char(32) not null default '2' first;

经过测试，移动字段时上述change语句必须显式定义被移动字段的属性；modify语句中也是如此，省略掉定义字段属性的语句将会报错。

将某个字段移动到指定字段列之后：

---将title_3字段移动到id字段之后的列
alter table test change column title_3 title_3 char(32) not null default '2' after id;
---或
alter table test modify column title_3 char(32) not null default '2' after id;
###注意column是可省略的关键字

----

当然，上述将修改字段、修改字段属性以及移动字段进行了分割区分，实际应用中无需分割使用，可以同时修改字段名、字段属性以及同时修改字段排序，如下：

---title_3被修改成名为title_1，从char类型变成varchar类型，且移动到列首位置
alter table test change column title_3 title_1 varchar(128) not null default 'sb' comment '咧个是标题' first;
---title_1再次被修改成title_2，并且类型变成char，位置回到id之后
alter table test change title_1 title_2 char(32) CHARACTER SET utf8 COLLATE utf8_general_ci not null default 'sb' comment '咧个是标题字段' after id;

三、编辑索引

alter table语句用于新增、修改、删除索引的语句与编辑字段名和字段属性的功能差不多；也是配合ADD、DROP关键字，依据MySQL的索引类型INDEX|KEY、UNIQUE [INDEX|KEY]、FULLTEXT [INDEX|KEY]、PRIMARY KEY、FOREIGN KEY进行编辑。

编辑修改MySQL的索引除了alter table语句外，还可以使用create index index_name index_type on tbl_name (index_col_name,...)用于创建索引，和DROP INDEX index_name ON tbl_name用于删除索引。

需要特别说明的是：MySQL的索引修改并没有专门的语句，而且MySQL索引在数据库架构设计之初一般都会做详尽的设计。而实现MySQL的索引修改，一般是变相实现：先删除原先的索引，再建立新的索引。

新增索引

---第一种方式，依据索引类型不同，该结构也会有适当变化
ALTER TABLE tbl_name ADD UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_option]
---第二种方式
CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,... [index_option]

其中index_type一般为：USING {BTREE | HASH}

删除索引

---删除主键索引
ALTER TABLE tbl_name DROP PRIMARY KEY
---删除普通key|index索引
ALTER TABLE tbl_name DROP {INDEX|KEY} index_name
---删除外键索引
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol

###另外一种语法
DROP INDEX index_name ON tbl_name

做实验的表test的结构