MySQL数据库（三）---约束、修改数据表

本篇的思维导图如下：

外键约束 FOREIGN KEY

保持数据的一致性和完整性
实现一对多的关系

要求

1. 父表和字表必须使用相同的存储引擎，并且禁止使用临时表
2. 数据表的存储引擎只能为InnoDB
3. 外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同。
4. 外键列和参照列必须创建索引，如果外键列不存在索引的话，MySQL将自动创建索引。

编辑数据表的默认存储引擎：
default-storage-engine=InnoDB

举例说明：

//下面创建一个省份列表
CREATE TABLE provinces
(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(20) NOT NULL
);

//创建另外一个数据表
CREATE TABLE users
(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED,                     
FOREIGN KEY (pid) REFERENCES provinces (id));

外键约束的参照操作

1. CASCADE: 从父表删除或更新 且自动删除或更新子表中匹配的行
2. SET NULL:从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL
3. RESTRICT：拒绝对父表的删除或更新操作
4. NO ACTION :标准SQL的关键字，在MySQL中与RESTRICT相同

例：

CREATE TABLE users1(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED,     
FOREIGN KEY (pid) REFERENCES provinces (id) ON DELETE CASCADE
);

INSERT provinces(pname) VALUE('A');
INSERT provinces(pname) VALUE('B');
INSERT provinces(pname) VALUE('C');
此时子表的内容自动跟着更新
INSERT users1(username,pid) VALUES ('Tom',3);

表级约束与列级约束

列级约束既可以在列定义的时候声明，也可以在列定义之后声明，
表级约束只能在列定义后声明

修改数据表：

添加单列：

ALTER TABLE tbl_name  ADD[COLUMN] col_name column_definition [FIRST|AFTER col_name]；
例：
ALTER TABLE users1  ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;

此时位于所有列的下面

ALTER TABLE users1  ADD password VARCHAR(32)  NOT NULL AFTER username;

此时在username列的后面

ALTER TABLE users1  ADD truename VARCHAR(32)  NOT NULL FIRST;

此时在最前面。

添加多列：（不能指定位置关系）

ALTER TABLE tbl_name ADD[COLUMN] (col_name column_definition,…);

删除列：

ALTER TABLE tbl_name DROP[COLUMN] col_name;
ALTER TABLE users1 DROP truename;
ALTER TABLE users1 DROP password,DROP age;  同时删除两列

添加/删除约束：

//先创建一个users2
CREATE TABLE users2(
username VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED
);

添加主键约束

alter table tbl_name ADD [CONSTRAINT[symbol]] PRIMARY KEY[index_type](index_col_name,...);
ALTER TABLE users2 ADD id smallint unsigned;  添加一个id列
alter table users2 add constraint PK_users2_id PRIMARY KEY(id); //添加主键约束

添加唯一约束：

alter table tbl_name ADD [CONSTRAINT[symbol]] unique[index|key] [index_name] [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 user2 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;  设置默认值为15
alter table users2 alter age drop default ;    删除默认约束

删除主键约束：

ALTER TABLE tbl_name DROP PRIMARY KEY；
alter table users2 drop primary key;

查看约束名字 以网格的形式：
SHOW INDEXES FROM users2\G

删除唯一约束

ALTER TABLE tbl_name DROP{INDEX|KEY} index_name;
alter table users2 drop index username;

删除外键约束：

//外键名称查看方法：
show create table users2;     //此时为 users2_ibfk_1
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symboy;
alter table users2 drop foreign key users2_ibfk_1;

修改列定义：

ALTER TABLE tbl_name MODIFY[COLUMN] col_name column_definition [FIRST|AFTER col_name];
alter table users2 modify id SMALLINT  unsigned not null first;
此时ID字段在最上面了

alter table users2 modify id tinyint  unsigned not null ;
此时则修改了id字段的数据类型

修改列名称：

ALTER TABLE tbl_name CHANGE[COLUMN] old_col_name new_col_name  column_definition [FIRST|AFTER col_name];
alter table users2  change pid p_i_d tinyint unsigned not null;

修改数据表的名字：

方法1：
ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name;
方法2：
RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2]…

alter table users2 rename users22;
rename table users22 to user2;