MySQL 修改表alter语法

修改表结构ALTER TABLE

Alter TABLE 数据表名 alter_spec[,alter_spec]…

ALTER TABLE tbl_name

ADD [COLUMN] create_definition [FIRST | AFTER column_name ] 增加字段名

or ADD INDEX [index_name] (index_col_name,…) 增加普通索引

or ADD [CONSTRAINT [symbol]] PRIMARY KEY (index_col_name,…) 增加主键索引

or ADD UNIQUE [index_name] (index_col_name,…) 增加唯一索引

or ADD FULLTEXT [INDEX|KEY] [index_name] (index_col_name,…) 增加全文索引

or ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,…) reference_definition 增加外键索引

or ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} 设置/删除字段的默认值

or CHANGE [COLUMN] old_col_name create_definition 修改字段类型

or MODIFY [COLUMN] create_definition 修改字段类型

or DROP [COLUMN] col_name 删除字段

or DROP PRIMARY KEY 删除主键

or DROP FOREIGH KEY fk_symbol 删除外键

or DROP INDEX index_name 删除普通索引、唯一索引、全文索引

or RENAME [AS] new_tbl_name 更改表名

? create table 查询协助语句

以上所有[COLUMN]是固定的可选语句, 不是指表中列的变量

ALTER TABLE语句允许指定多个动作, 其动作间使用逗号分隔, 每个动作表明对表的一个修改。

ALTER TABLE 表名 ADD 字段名 <建表语句> [FIRST | AFTER 列名]

如果没指定FIRST和AFTER,则在列尾添加一列

ALTER TABLE users ADD email VARCHAR(30) NOT NULL;

ALTER TABLE users ADD name VARCHAR(30) NOT NULL FIRST;

ALTER TABLE users ADD height DOUBLE NOT NULL DEFAULT '0.00' AFTER userPass;

实例:

ALTER TABLE `cmf_anchor` ADD `near_channel_endtime` int(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '最近直播结束的时间' AFTER `near_channel_time`;

删除字段

ALTER TABLE `draws` drop COLUMN `prev_balance`;

增加主键索引

ALTER TABLE tbl_name ADD PRIMARY KEY (index_col_name,…)

alter table categories add primary key (categoryId);

括号前可留可不留空格；如：alter table categories add primary key(categoryId);也是正确的

增加唯一索引

ALTER TABLE tbl_name ADD UNIQUE [index_name] (index_col_name,…)

alter table books add unique (bookName);

增加普通索引

ALTER TABLE tbl_name ADD INDEX [index_name] (index_col_name,…)

alter table carts add index ind (userId,bookId);

增加全文索引

ALTER TABLE tbl_name ADD FULLTEXT (index_col_name,…)

alter table books add fulltext (detail);

添加外键索引

alter table 表名1 add foreign key (字段1,[,字段名2,…]) references 表名2(字段1,[,字段名2,…]);

alter table books add foreign key (userId) references bookstype(id);

alter table books add constraint id_check foreign key(id) references bookstype(id) on delete cascade on update cascade;

constraint 是固定的可选语句 id_check 是外键索引名

设置/删除字段的默认值

ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

alter table film alter column val_tpl set default 5;

alter table film alter val_tpl set default 5;

alter table film alter column val_tpl drop default;

alter table film alter val_tpl drop default;

ALTER TABLE 表名 CHANGE(MODIFY) 列表 <建表语句>

此语句也可以修改数据字段属性如UNSIGNED、ZEROFILL、AUTO_INCREMENT、NULL和NOT NULL、DEFAULT

ALTER TABLE users MODIFY telNo INT UNSIGNED DEFAULT '0';

ALTER TABLE users CHANGE telNo telNo INT UNSIGNED DEFAULT '0';

CHANGE除了更改类型外还能更改列名, 而MODIFY不能实现这个功能

ALTER TABLE users CHANGE 旧表名 新表名

删除主键：

alter table 表名 drop primary key; #每一个表最多只能有一个主键

alter table books drop primary key;

删除外键：

alter table 表名 drop foreign key 外键名;

注意：外键名(symbol)不等于索引名(index_name);

外键名(symbol)查询：show create table 表名

mysql> show create table books

……

CONSTRAINT `books_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`)

……

其中：books_ibfk_1为外键名, 外键名(索引名)是定义在子表上

索引名(index_name)查询： show index from 表名

此语句只能查询普通索引、唯一索引、全文索引的索引名(index_name)

alter table books drop foreign key userId;

删除索引：(用于删除普通索引、唯一索引、全文索引)

alter table 表名 drop index 索引名;

alter table books drop index ind;

删除索引必须先要知道索引名

查询索引名的方法有两种：

show create table 表名;

show index from 表名;

删除索引另一种方法

DROP INDEX index_name ON tal_name

INDEX：是固定格式, 不能省略, 除了可以删除普通索引,还可删除唯一索引、全文索引

index_name: 索引名

tab_name: 表名

注意: 在建表时如何未指定索引名时, 默认情况下索引名等于字段名

查询索引名的方法：show index from tab_name;

drop index detail on books #可用于删除普通索引、唯一索引、全文索引(其中detail是索引名)

实际上, 此语句同样被映射为一条”alter table”的删除索引语句。

删除主键索引和外键索引只能采用alter table tab_name drop语句

alter table books drop primary key; (如果该字段名有auto_increment属性,则删除主键失败)

alter table books drop foreign key 外键名;(外键名通过”show create table 表名”查询)

当然也可以通过查询索引名后, 再去删除索引

show index from mytable; //mytable 是表名

alter table mytable drop index key_name; //key_name是上表查出的索引名

drop index key_name on mytable //删除键名的另一种方法

如果主键索引有auto_increment属性,应先通过alter table tbl_name modify语句修改；

如alter table books modify bookId int(10) not null;

然后才能删除主键索引:alter table books drop primary key;

修改表名

ALTER TABLE 旧表名 RENAME AS 新表名

alter table books rename as book;

修改表选项：

alter table 表名 选项名1=选项值1 选项名2=选项值2,…;

表选项:

engine = 存储引擎名, 默认是InnoDB(可以不写)

auto_increment = 自增长值的起始值, 默认是1(可以不写), 表中已经删除的记录行, 可以通过此参数恢复起始值。

charset = 字符集名称(编码名称), 默认是数据库上设定的编码(可以不写)

comment = '表的说明', 可以不写；

alter table books charset=utf8 collate=utf8_general_ci;

alter table books AUTO_INCREMENT=0; 修改自增长的起始值

修改数据库字符集和校对规则:

alter database 数据库名 [charset =新的要使用的编码] [collate = 新的要使用的排序规则];

alter database shop charset=utf8 collate=utf8_general_ci;

删除表

DROP TABLE [IF EXISTS] 表名

drop table if exists bookes;

修改表结构(ALTER TABLE语句), 修改字段类型, 存在的列的数据不会丢失, 而新添加的字段值为NULL;

实际在开发中, 数据表是设计好的, 数据是无价的, 我们需要做的就只是添加而已;

mysql> alter table sc_student_info add primary key (id);

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

错误解析：数据表中有重复的数据, 需要删除数据后, 才能添加索引;