约束

• 约束保证数据的完整性与一致性
• 约束分为表级约束和列级约束
• 约束类型包括– NOT NULL (非空约束)– PRIMARY KEY (主键约束)– UNIQUE KEY (唯一约束)– DEFAULT (默认约束)– FOREIGN KEY (外键约束)

外键约束的要求解析

FOREIGN KEY保证数据的一致性,完整性.实现一对一或一对多关系

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

显示数据表的创建数据

SHOW CREATE TABLE tbl_name; 

显示数据表索引

SHOW INDEXES FROM tbl_name; 

创建两个表并且进行外键约束

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) 
   ); 

外键与主键数据类型必须的一直 比如 pname SMALLINT 对应的 pid SMALLINT 而且符号位也必须相同有外键的表称为子表 子表参照的表称为附表参照列自动创建了索引

外键约束的参照操作

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

外键约束仅仅支持innodb引擎

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

参照操作需要开启ON DELETE后再加上参数

添加数据

INSERT provinces(pname) VALUES("A"); 
INSERT provinces(pname) VALUES("B"); 
INSERT provinces(pname) VALUES("C"); 

//插入用户数据 

INSERT user1(username,pid) VALUES("Tom",3); 

插入用户数据只需要对应设置外键的id即可.

删除记录

删除provinces id为3的记录

DELETE FROM provinces WHERE id = 3; 

那么相应的子表中外键id为3的也会删除.

表级约束与列级约束

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

修改数据表

添加单列

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

FIRST 指的是插入的这一列会在最前面.AFTER col_name则是在某一列后面

添加单列数据

SHOW COLUMNS FROM user1; 
ALTER TABLE user1 ADD age INT UNSIGNED NOT NULL DEFAULT 10; 
ALTER TABLE user1 ADD password VARCHAR(32) NOT NULL AFTER username;//插入某列后面 
ALTER TABLE user1 ADD truename VARCHAR(20) NOT NULL FIRST; 

DEFAULT:未明确表明数据的时候.默认指定的数据UNSIGNED:针对数值型类型 是否有符号

添加多列

添加多列无法指定位置

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

给user1添加多列数据

ALTER TABLE user1 ADD( 
       num INT UNSIGNED NOT NULL DEFAULT 1, 
       sex ENUM("男","女") NOT NULL DEFAULT "男" 
   ); 

删除列

ALTER TABLE user1 DROP truename; 

删除多列

ALTER TABLE user1 DROP truename,DROP password; 

修改列的时候可以删除的时候再添加.中间通过逗号分隔.

删除的同时再添加列

ALTER TABLE user1 DROP num, ADD pm FLOAT UNSIGNED DEFAULT 15; 

添加主键约束

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

范例一

CREATE TABLE user2( 
       username VARCHAR(20) NOT NULL, 
       pid SMALLINT UNSIGNED 
   ); 
//增加主键 
ALTER TABLE user2 ADD id SMALLINT UNSIGNED; 
//增加主键约束 
ALTER TABLE user2 ADD CONSTRAINT PK_user2_id PRIMARY KEY (id); 

CONSTRAINT:用来起别名

添加唯一约束

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

范例一

ALTER TABLE user2 ADD UNIQUE (username); 

添加外键约束

范例一

user2中pid参照provinces;

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 user2 ADD age TINYINT UNSIGNED NOT NULL; 
ALTER TABLE user2 ALTER age SET DEFAULT 15; 

给age添加默认约束

删除主键约束

任何一个表有且只有一个主键

ALTER TABLE user2 DROP PRIMARY KEY; 

删除主键索引

ALTER TABLE user2 DROP INDEX username; 

删除的仅仅是索引

显示索引列表名称

SHOW INDEXes FROM user2; 

删除外键约束

删除约束是删除约束的名称

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbok 

范例

//查询外键名称 

SHOW CREATE TABLE user2; 
//找到pid的外键名称为 user2_ibfk_1;然后进行删除 

ALTER TABLE user2 DROP FOREIGN KEY user2_ibfk_1; 

虽然外键不在了,但是索引还是存在的 如果不想要索引则删除即可

ALTER TABLE user2 DROP INDEX pid; 

修改列定义和更名数据表

修改列定义

ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST |AFTER col_name] 

修改列顺序

ALTER TABLE user2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST; 

column_definition指的是列定义 不变

修改列定义-范例

ALTER TABLE user2 MODIFY id TINYINT UNSIGNED NOT NULL; 

修改列定义的时候有可能会造成数据的丢失.

修改列名称

ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name  new col_name column_definition [FIRST |AFTER col_name] 

修改id的类型与名称

ALTER TABLE user2 CHANGE pid p_id 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]; 

范例

ALTER TABLE user2 RENAME users2; 

RENAME TABLE users2 TO user2; 

总结

约束

• 按功能化为:NOT NULL,PRIMARY KEY, UNIQUE KEY, DEFAULT,FOREIGN KEY
• 按数据列的数目化为:表级约束,列级约束

修改数据表

• 针对字段的操作:添加/删除字段,修改列定义,修改列名称等
• 针对约束的操作:添加/删除各种约束.
• 针对数据表的操作:数据表更名(两种方式).

列级约束：只能应用于一列上。表级约束：可以应用于一列上，也可以应用在一个表中的多个列上。

默认约束(DEFAULT)与非空约束(NOT NULL)不存在表级约束