约束

  • 约束保证数据的完整性与一致性
  • 约束分为表级约束和列级约束
  • 约束类型包括 -- 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)不存在表级约束