三、约束以及修改数据表

3-1回顾和概述

• 创建数据表
• PRIMARY KEY 主键约束
• UNIQUE KEY 唯一约束
• DEFAULT 默认约束
• NOT NULL非空约束
• 记录的插入及简单查找

 

3-2外键约束的要求解析

• FOREIGN KEY：保持数据一致性，完整性；实现一对一或一对多关系。
• 要求：

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

• 在 MY.ini 文件中编辑默认的存储引擎：default-storage-engine=INNODB；
• 显示创建表的语句：SHOW CREATE TABLE table_name；
• 查看表是否有索引：SHOW INDEXS FROM table_name；
• 以网格查看表是否有索引：SHOW INDEXS FROM table_name\G；

 

• Eg:

CREATE TABLE table_name1(

id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(20) NOT NULL

) ;

CREATE TABLE table_name2(

id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(20) NOT NULL,

pid SMALLINT UNSIGNED,

FOREIGN KEY (pid) REFERENCES table_name1(id)

/* 外键 pid 参照 table_name1中的 id 字段 */

);

 

3-3外键约束的参照操作

• CASCADE:删除或更新父表中的数据相应的删除或更新子表中的相匹配的行；

SHOW CREATE TABLE tb_name;//显示数据表创建时的命令；

创建子表：users1;

>CREATE TABLE users(

>id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

>username VARCHAR(10) NOT NULL,

>pid(外键) SMALLINT(与参照键类型相同) UNSIGNED(与参照键有无符号类型也要相同),

//外键的作用体现在这：如果我们要添加一个省份的字段，需要添加个char型的，现在有了关系型数据库，我们只需要存储省份的id即可；

>FOREIGN KEY (pid) PREFERENCES provinces (id) ON DELETE CASCADE,

>);

INSERT provinces(pname) VALUES('A');

INSERT provinces(pname) VALUES('B');

INSERT provinces(pname) VALUES('C');

INSERT users1(username,pid) VALUES('Tom',1);

INSERT users1(username,pid) VALUES('Jerry',2);

INSERT users1(username,pid) VALUES('Lucy',3);

当删除provinces中的id=3的数据C时，相应的users1中的Lucy也会被删掉，更新的话同样如此；

• SET NULL://从父表删除或更新行，会设置子表中的外键列为NULL，如果使用该选项，必须保证子表列没有指定NOT NULL
• RESTRICT://拒绝对父表的删除或更新操作；
• NO ACTION：//标准的SQL语句，在MYSQL当中与RESTRICT相同
• 实际开发中，我们很少使用物理的外键约束，而是使用逻辑约束；无理的外键约束只有innoDB这种存储引擎才会支持，MYISAM这种引擎就不支持物理的外键约束。反过来说，当我们使用到的引擎为MYISAM时，只能使用逻辑外键（即两个表的设计的逻辑关系）。

 

3-4表记约束及列级约束

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

• 主键、外键、非空、唯一、default都是约束条件。

 

3-5修改数据表——添加/删除列

• 添加单列：如果指定FIRST，则在整个表的最前方，默认不写为整个表的最后方；如果指定AFTER col_name，则在col_name的后面。

ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST AFTER col_name]

• 添加多列：无法指定FIRST/AFTER、只能默认为最后方。

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

• 删除单列

ALTER TABLE tbl_name DROP [COLUMN] col_name

• 删除多列（Ps：删除一列的同时，新增一列。其间用逗号隔开）

ALTER TABLE tbl_name DROP [COLUMN] col_name, DROP [COLUMN] col_name,DROP [COLUMN] col_name

 

3-6修改数据表——添加约束

• 添加主键约束

CONSTRAINT_PK主键名字为PK

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

e.g:ALTER TABLE users ADD CONSTRAINT PK_users_id PRIMARY KEY (id);

• 添加唯一约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...)

e.g:ALTER TABLE users ADD UNIQUE (username);

• 添加外键约束

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

e.g:ALTER TABLE users ADD FOREIGN KEY (pid) REFERENCES provinces (id)

• 添加/删除默认约束 DEFAULT

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

e.g:

ALTER TABLE users ADD age TINYINT UNSIGNED NULL;

ALTER TABLE users ALTER age SET DEFAULT 15;

ALTER TABLE users ALTER age DROP DEFAULT;

 

3-7修改数据表——删除约束

• 约束的 index_name 可通过 SHOW INDEX FROM table_name\G; 查询
• 删除主键约束

ALTER TABLE table_name DROP PRIMARY KEY;

• 删除唯一约束

ALTER TABLE table_name DROP INDEX index_name;

• 删除外键约束

ALTER TABLE table_name DROP FOREIGN KEY （fk_symbol）通过查询系统默认给的名字;

• 外键约束的名字 fk_symbol 可通过 SHOW CREATE TABLE table_name; 查询

 

3-8修改数据表——修改列定义和更名数据表

• 修改列定义

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; //修改数据类型,需注意数据丢失的问题

• 修改列名称

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; //修改列名称

• 数据表更名

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] ...

尽量少使用数据表的列名及表名的更名。

 

小结