MySQL 约束和修改数据表

1.     FOREIGN KEY（外键约束）:保持数据的一致性，完整性。实现数据表的一对一，一对多的关系。

a)     父表（子表所参照的表）和子表（具有外键列的表）必须使用相同的存储引擎，而且禁止使用临时表。

b)     数据表的存储引擎只能为InnoDB（可在my.ini查看修改。5.7版本my.ini地址：C:\ProgramData\MySQL\MySQL Server 5.7\my.ini）。

c)      外键列（曾经加过foreign关键词的那一列）和参照列（外键列所参照的那列）必须具有相似的数据类型(字符，整型，日期时间等）。其中数字的长度或是否有符号位（如整型有无符号(unsigned)和有符号(signed)两种类型;）必须相同；而字符的长度则可以不同。比如说父表里面有一个参数id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,子表里面就要写作pid SMALLINT UNSIGNED(符号位和数据类型要相似)

d)     外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将自动创建索引。

 

2.     外键约束的参照操作

CASCADE：四个选项指在进行外键约束的创建以后，在更新表的时候子表是否也进行相应操作。

例如：ON DELECT+CASCADE/SET NULL/RESTRICT/NO ACTION 在删除的时候进行什么样的操作

SELECT*FROM tb_name(查看插入的记录是否存在）

删除某条记录 DELETE*FROM tb_name Where id/username(属性）=xx;

CASCADE:在父表删除/更新记录时子表的记录也被删除/更新；

SET NULL://从父表删除或更新行，会设置子表中的外键列为NULL，如果使用该选项，必须保证子表列没有指定NOT NULL

RESTRICT://拒绝对父表的删除或更新操作；

NO ACTION：//标准的SQL语句，在MYSQL当中与RESTRICT相同

实际开发中，我们很少使用物理的外键约束，而是使用逻辑约束；物理的外键约束只有innoDB这种存储引擎才会支持，MYISAM这种引擎就不支持物理的外键约束。反过来说，当我们使用到的引擎为MYISAM时，只能使用逻辑外键（即两个表的设计的逻辑关系）。

3.     表级约束和列级约束

对于一个列所创建的约束，称之为列级约束，而对于两个或两个以上的列所创建的约束，我们称之为表级约束，列级约束在使用的时候，既可以在列定义的时候声明，也可以在列定义以后声明，而表级的约束只能在列定义以后来声明，在实际开发中，用列级约束比较多，表级约束很少用，除此之外，在所有的约束中，并不是说每种约束都存在着表级或列级约束，其中，NOT NULL 非空约束，DEFAULT约束这两种约束就不存在表级约束，它们只有列级约束，而对于其他的三种，像主键，唯一，外键，它们都可以存在表级和列级约束。

4.     修改数据表

1)     添加、删除列

删除列：ALTER TABLE tb1_name DROP[COLUMN] col_name;

添加多列：ALTER TABLE tb1_name ADD[COLUMN] (col_name column_definition,...);

添加单列：ALTER TABLE tb!_name ADD[COLUMN] col_name column_definition [FIRST|AFTER col_name];

多个操作可以同时操作，用逗号分开

2)     修改数据表—添加约束

添加主键约束（只能添加一个）

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol自定义的约束的名称]] PRIMARY KEY [index_type] (index_col_name,...)

例如：ALTER TABLE 表格名 ADD CONSTRAINT PE_表格名_id PRIMARY KEY (id);

添加唯一约束（可以添加多个）

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [index_type] (index_col_name,...)

例如：ALTER TABLE 表格名 ADD UNIQUE (id);

添加外键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_type] (index_col_name,...) reference_definition 

例：ALTER TABLE t3 ADD FOREIGN KEY (age)REFERENCES t2(age);

添加/删除默认约束

ALTER TABLE tbl_name ALTER [COLUMN] col_name SET DAFAULT literal（比如age里面可设置为10，20 |DROP DEFAULT

例：ALTER TABLE user2 ALTER age SET DEFAULT 15;

ALTER TABLE user2 ALTER age DROP DEFAULT;

3)     修改数据表—删除约束

删除主键约束

ALTER TABLE 数据表名 DROP PRIMARY KEY;

为什么数据表后没指定名字：任何数据表有且只有一个主键约束

删除唯一约束

ALTER TABLE 数据表名 DROP INDEX id;

删除外键约束

ALTER TABLE 数据表名 DROP FOREIGN KEY users2_ibfk_1（可用show方法查看带下划线名）;

4)     修改列定义

1.用关键字ALTER TABLE..MODIFY

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字段的位置提到第一列

SHOW COLUMNS FROM users2;

ALTER TABLE users2 MODIFY id TINYINT UNSIGNED NOT NULL; //修改数据类型,需注意数据丢失的问题

2.修改列名称,用CHANGE方法可以同时修改列名称和列定义.

用关键字ALTER..CHANGE

ALTER TABLE tbl_name CHANGE [COLUMN] col_name new_col_name column_definition [FIRST|AFTER col_name];

ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED; //修改列名称

3.更换数据表名

用关键字ALTER..RENAME

修改一个表的表名(方法1)

ALTER TABLE users2 RENAME person;

修改一个表的表名(方法2)

用关键字RENAME..TO

RENAME TABLE users5 TO users2;

一次修改多个表的表名,操作间用逗号隔开.

RENAME TABLE users5 TO users2,users3 TO users4;

应该少使用数据表的列名及表名的更名。