戚薇戚薇戚薇

让他夜夜
热液乳业人员

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

更新时间:2020-05-21 12:11:34点击:

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

学习MySQL管理表语句过去先来学习几个查看表关联信息的SQL语句:

##表状态SHOW TABLE STATUS LIKE 'tableName' ##like语法  like的是字符串 ##查看表结构的3种语法DESC tableNameDESCRIBE tableNameshow [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开头职位新增一个字段testALTER TABLE  `blog` ADD  `test` VARCHAR( 32 ) NULL DEFAULT  '1' COMMENT  '新增的字段' FIRST--在表blog的字段title后新增一个字段contentsALTER 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 existsif 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|KEYUNIQUE [INDEX|KEY]FULLTEXT [INDEX|KEY]PRIMARY KEYFOREIGN 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的结构

table for test

诚然alter table还有少少更高级、适用的诸如增进、编辑分区,启用禁用索引,没有列出来的基本是少少alter table的高阶用法,已经是抵达专业DBA的程度,到了需要用到的时候再说啊~~~而MySQL索引这块还需单独深入学习,这里仅列出了与alter table关联的索引编辑成果的语句结构。