MySQL学习12：修改数据表(二)

我们接着上一篇的添加约束的操作讲述。 三添加约束 (3)添加外键约束 ALTER TABLE table_name ADD [CONSTRAINT [symbol]] FOREIGN KEY[index_name] [index_type] (index_col_name,...) references_definition; 例子： ALTER TABLE users2 ADD FOREIGN KEY (pi

我们接着上一篇的添加约束的操作讲述。

三添加约束

(3)添加外键约束

ALTER TABLE table_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] [index_type]

(index_col_name,...) references_definition;

例子：

ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES provinces (id);

SHOW COLUMNS FROM users2;

(4)添加默认约束

ALTER TABLE table_name ALTER [COLUMN] col_name {SET DEFAULT literal} ;

例子：

首先添加一列要做为默认约束的字段：

ALTER TABLE users2 ADD age TINYINT UNSIGNED NOT NULL;

SHOW COLUMNS FROM users2;

添加默认约束age字段为默认值为15：

ALTER TABLE users2 ALTER age SET DEFAULT 15;

SHOW COLUMNS FROM users2;

四删除约束

(1)删除主键约束

删除主键约束语法格式(由于一张数据表只有一个主键，因此不用加上列明)：

ALTER TABLE table_name DROP PRIMARY KEY;

例子：

ALTER TABLE users2 DROP PRIMARY KEY;

SHOW COLUMNS FROM users2;

(2)删除唯一约束

删除唯一约束的语法格式(一张数据表可以有多个唯一约束)：

ALTER TABLE table_name DROP {INDEX|KEY} index_name;

例子：

先来查看有索引的字段名称，找到唯一约束字段：

SHOW INDEXES FROM users2\G;

ALTER TABLE users2 DROP INDEX username;

SHOW COLUMNS FROM users2;

(3)删除外键约束

ALTER TABLE table_name DROP FOREIGN KEY fk_symbol;

例子：

先来查看外键约束：

SHOW CREATE TABLE users2;

由此知道外键约束的名称为users2_ibfk_1，因此可以删除外键约束：

ALTER TABLE users2 DROP REIGN KEY users2_ibfk_1;

SHOW CREATE TABLE users2;

(4)删除默认约束

删除默认约束的语法格式为：

ALTER TABLE table_name ALTER [COLUMN] col_name {DROP DEFAULT};

例子：

ALTER TABLE users2 ALTER age DROP DEFAULT;

SHOW COLUMNS FROM users2;

五修改列定义和更名数据表

(1)修改列定义

修改列定义语法格式：

ALTER TABLE table_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name];

例子：

1)如果我们看到数据表的id字段并没有在第一列，要是改到第一列需要这样做：

SHOW COLUMNS FROM users2;

ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;

SHOW COLUMNS FROM users2;

2)我们也可以修改字段的数据类型：

SHOW COLUMNS FROM users2;

ALTER TABLE users2 MODIFY id TINYINT UNSIGNED NOT NULL FIRST;

SHOW COLUMNS FROM users2;

大数据类型改到小数据类型会造成数据的丢失。谨慎使用改变数据类型。因此这就需要我们在进行数据库设计的

时候一定要事先设计好字段的数据类型，避免不必要的麻烦。

(2)修改列名称

修改列名称的语法格式：

ALTER TABLE table_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST |

AFTER col_name];

例子：修改列名称和列的数据类型

SHOW COLUMNS FROM users2;
ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;

SHOW COLUMNS FROM users2;

(3)修改数据表名称

修改数据表名称的方式有两种：

方法1：

ALTER TABLE table_name RENAME [TO|AS] new_table_name;

方法2：

RENAME TABLE table_name TO new_table_name [,table_name2 To new_table_name2] ...;

例子：

SHOW TABLES;

ALTER TABLE users2 RENAME users3;

SHOW TABLES;

我们再来使用另一种方法修改数据表名称：

RENAME TABLE users3 TO users2;

SHOW TABLES;

我们应该尽量少使用修改列定义和数据表名称，可能导致视图或存储过程不能使用。

总结：

修改数据表

针对字段的操作：添加/删除字段、修改列定义，修改列名称等。

针对约束的操作：添加/删除各种约束。

针对数据表的操作：数据表更名(两种方式)。