MySQL表级约束与列级约束、修改数据表

按功能划分：NOT NULL, PRIMARY KEY, UNIQUE KEY, DEFAULT,
按数据列的数目划分：

对一个数据列建立的约束，称为列级约束
对多个数据列建立的约束，称为表级约束
列级约束既可以在列定义时声明，也可以定义后声明
表级约束只能在列定义后声明

一般使用列级约束

NOT NULL和DEFAULT只有列级约束。
主键、唯一和外键三种约束都有。

【修改数据表】

针对字段的操作
针对约束的操作
针对数据表的操作

【添加单列】ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name];

省略first 和 after则添加到所有列的最下面。

ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10; // 添加到最后面
ALTER TABLE users1 ADD password VARCHAR(20) NOT NULL AFTER username; //添加到username后
ALTER TABLE users1 ADD truename VARCHAR(20) NOT NULL FIRST; //添加到最前面
SHOW COLUMNS FROM user1;

【添加多列】ALTER TABLE tbl_name ADD [COLUMN](col_name, col_definition,...)

不能指定位置，直接添加到最下面。

【删除列】ALTER TABLE tbl_name DROP [COLUMN] col_name;

ALTER TABLE users1 DROP truename;
ALTER TABLE users1 DROP password, DROP age, ADD ...; // 操作用逗号分开

【添加主键】ALTER TABLE tbl_name ADD [CONSTRAINT [symbol］ PRIMARY KEY [index_type](index_col_name,...)

例：
CREATE TABLE users2(
->username VARCHAR(20) NOT NULL,
->pid SMALLINT UNSIGNED
->);
ALTER TBALE users2 ADD id SMALLINT UNSIGNED;
ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY(id); //添加约束的名字为PK_users2_id

【添加唯一约束】ALTER TABLE tbl_name ADD [CONSTRAINT[symbol］ UNIQUE [INDEX|KEY] [index_type] (index_col_name,...)

唯一约束可以有多个
ALTER TABLE users2 ADD UNIQUE (username);

【添加外键约束】ALTER TABLE tbl_name ADD [CONSTRAINT [symbol］ FOREIGN KEY [index_name] (index_col_name,...) reference_definition

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

【添加删除默认约束】ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

ALTER TABLE users2 ADD age TINYINT UNSIGNED NOT NULL;
ALTER TABLE users2 ALTER age SET DEFAULT 15;
ALTER TABLE users2 ALTER age DROP DEFAULT;

【删除主键约束】

ALTER TABLE users2 DROP PRIMARY KEY; // 执行后id的主键约束没有，username的唯一约束变为主键约束

【删除唯一约束】ALTER TABLE tbl_name DROP {INDEX|KEY} index_name;

查看唯一约束约束名字：
SHOW INDEXES FROM users2\G;
ALTER TABLE users2 DROP INDEX username;

【删除外键约束】ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

查看外键约束名称：
SHOW CREATE TABLE users2;
ALTER TABLE users2 DROP FOREIGN KEY ....;
ALTER TABLE users2 DROP INDEX pid; //删除pid上的索引

【修改列定义】ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

SHOW COLUMNS FROM users2;
ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST; //id移动到顶端。

由大类型改成小类型可能会造成数据丢失

【修改列名称】CHANGE可修改列定义和列名称

ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL,
->CHANGE col_name2 col_name_new2 TINYINT UNSIGNED NOT NULL;

【数据表更名】

方法一：ALTER TABLE tbl_name RENAME [TO | AS] new_tbl_name;
方法二：RENAME TABLE tbl_name TO new_tbl_name, tbl_name2 TO new_tbl_name2,...;

减少使用更名，导致引用过的数据出错。