完整性约束
介绍
约束条件与数据类型的宽度一样,都是可选参数
作用 : 用于保证数据的完整性和一致性
主要分为 :
# 标识该字段为该表的主键,可以唯一的标识记录 primary key (pk) # 标识该字段为该表的外键 foreign key (fk) # 标识该字段不能为空 not null # 标识该字段的值是唯一的 unique key (uk) # 标识该字段的值自动增长(整数类型,而且为主键) auto_increment # 为该字段设置默认值 default # 无符号 unsigned #使用0填充 zerofill
说明 :
#1 . 是否允许为空 , 默认null ,可以设置 not null ,字段不允许为空,必须赋值. #2 . 字段是否有默认值,缺省的默认值是 null ,如果插入记录时不给字段赋值,此字段使用默认值. sex enum("male","female") not null default 'name' # 必须为正值(无符号),不允许为空,默认是20. age int unsigned not null default 20 # 3 . 是否是key 主键 primary key 外键 foreign key 索引 (index,unique......)
一 . not null 和 default
是否可空 , null 表示空,非字符串
not null ---不可空
null --- 可空
默认值 : 创建列时可以指定默认值,当插入数据时如果未主动设置,则自动添加默认值.
create table tb1( nid int not null defalut 2, num int not null );
验证1 : 默认值为空
mysql> create table t11(id int);# id字段默认可以为空 Query OK, 0 rows affected (0.05 sec) mysql> desc t11; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ row in set (0.03 sec) mysql> insert into t11 values(); #给t11表插一个空的值 Query OK, 1 row affected (0.00 sec) #查询结果如下 mysql> select * from t11; +------+ | id | +------+ | NULL | +------+ row in set (0.00 sec) 默认值可以为空
验证2 : 设置not null ,插入时不能为空
mysql> create table t12(id int not null);#设置字段id不为空 Query OK, 0 rows affected (0.03 sec) mysql> desc t12; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.01 sec) mysql> insert into t12 values();#不能插入空 ERROR 1364 (HY000): Field 'id' doesn't have a default value
验证3 :设置id字段默认值后,则无论id字段是 null 还是 not null ,都可以插入空,插入空默认填入default指定的默认值.
# 第一种情况 mysql> create table t13(id int default 1); Query OK, 0 rows affected (0.03 sec) mysql> desc t13; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | YES | | 1 | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.01 sec) mysql> insert into t13 values(); Query OK, 1 row affected (0.00 sec) mysql> select * from t13; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) # 第二种情况 mysql> create table t14(id int not null default 2); Query OK, 0 rows affected (0.02 sec) mysql> desc t14; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | | 2 | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.01 sec) mysql> select * from t14; +----+ | id | +----+ | 2 | +----+ 1 row in set (0.00 sec)
练习 : 创建学生表student2 ,设置每个字段的约束条件.
mysql> create table student2( -> id int not null, -> name varchar(50) not null, -> age int(3) unsigned not null default 18, -> sex enum('male','female') default 'male', -> fav set('smoke','drink','tangtou') default 'drink,tangtou' -> ); Query OK, 0 rows affected (0.01 sec) # 只插入了not null约束条件的字段对应的值 mysql> insert into student2(id,name) values(1,'mjj'); Query OK, 1 row affected (0.00 sec) # 查询结果如下 mysql> select * from student2; +----+------+-----+------+---------------+ | id | name | age | sex | fav | +----+------+-----+------+---------------+ | 1 | mjj | 18 | male | drink,tangtou | +----+------+-----+------+---------------+ row in set (0.00 sec)
二 . unique
在mysql 中称为 单列唯一
比如 : 我们要创建一个公司,那么每个部门都是唯一的.
mysql> create table department( -> id int, -> name char(10) -> ); Query OK, 0 rows affected (0.01 sec) mysql> insert into department values(1,'IT'),(2,'IT'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from department; +------+------+ | id | name | +------+------+ | 1 | IT | | 2 | IT | +------+------+ rows in set (0.00 sec) # 发现： 同时插入两个IT部门也是可以的，但这是不合理的，所以我们要设置name字段为unique 解决这种不合理的现象。 验证之前重复插入记录的操作是可行的，但是不符合场景
使用约束条件 unique ,对公司的字段进行设置
#第一种创建unique的方式 #例子1： create table department( id int, name char(10) unique ); mysql> insert into department values(1,'it'),(2,'it'); ERROR 1062 (23000): Duplicate entry 'it' for key 'name' #例子2： create table department( id int unique, name char(10) unique ); insert into department values(1,'it'),(2,'sale'); #第二种创建unique的方式 create table department( id int, name char(10) , unique(id), unique(name) ); insert into department values(1,'it'),(2,'sale');
联合唯一 :(组合唯一) , 只要有一列不同,就可以插入数据
# 创建services表 mysql> create table services( -> id int, -> ip char(15), -> port int, -> unique(id), -> unique(ip,port) -> ); Query OK, 0 rows affected (0.05 sec) mysql> desc services; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | UNI | NULL | | | ip | char(15) | YES | MUL | NULL | | | port | int(11) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.01 sec) #联合唯一，只要两列记录，有一列不同，既符合联合唯一的约束 mysql> insert into services values -> (1,'192,168,11,23',80), -> (2,'192,168,11,23',81), -> (3,'192,168,11,25',80); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from services; +------+---------------+------+ | id | ip | port | +------+---------------+------+ | 1 | 192,168,11,23 | 80 | | 2 | 192,168,11,23 | 81 | | 3 | 192,168,11,25 | 80 | +------+---------------+------+ 3 rows in set (0.00 sec) mysql> insert into services values (4,'192,168,11,23',80); ERROR 1062 (23000): Duplicate entry '192,168,11,23-80' for key 'ip'
总结 :
单列唯一 create table dep(id int not null,name varchar(20) unique); insert into dep(id,name) values(1,'alex'); insert into dep(id,name) values(2,'alex'); 多列唯一 表示每一列都唯一 create table dep2(id int unique,name varchar(20) unique); insert into dep2(id,name) values(1,'alex'); # 不能插入值 insert into dep2(id,name) values(1,'alex2'); insert into dep2(id,name) values(2,'alex'); 组合唯一 （联合唯一） 只要有一列不同，就可以插入数据 create table dep3( id int, name varchar(20), unique(id,name) ); insert into dep3(id,name) values(1,'alex'); insert into dep3(id,name) values(1,'alex2'); insert into dep3(id,name) values(2,'alex'); # 不能插入的格式 insert into dep3(id,name) values(1,'alex');
三 . primary key
一个表中可以 :
单列做主键
多列做主键(复合主键)
约束 : 等价于 not null , unique ,字段的值不为空且唯一
存储引擎默认是( innodb ) : 对于 innodb 存储引擎来说,一张表必须有一个主键
在 sql 来说,一张表中只允许有一个主键,通常是 id cid nid sid
单列主键:
# 创建t14表，为id字段设置主键，唯一的不同的记录 create table t14( id int primary key, name char(16) ); insert into t14 values (1,'xiaoma'), (2,'xiaohong'); mysql> insert into t14 values(2,'wxxx'); ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY' # not null + unique的化学反应，相当于给id设置primary key create table t15( id int not null unique, name char(16) ); mysql> create table t15( -> id int not null unique, -> name char(16) -> ); Query OK, 0 rows affected (0.01 sec) mysql> desc t15; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | char(16) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ rows in set (0.02 sec)
复合主键 : 验证复合主键的使用
create table t16( ip char(15), port int, primary key(ip,port) ); insert into t16 values ('1.1.1.2',80), ('1.1.1.2',81);
四. auto_increment
约束 : 约束的字段为自动增长 , 约束的字段必须同时被 key 约束.
验证 : ************
不指定id , 则自动增长.
# 创建student create table student( id int primary key auto_increment, name varchar(20), sex enum('male','female') default 'male' ); mysql> desc student; +-------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | sex | enum('male','female') | YES | | male | | +-------+-----------------------+------+-----+---------+----------------+ 3 rows in set (0.17 sec) #插入记录 mysql> insert into student(name) values ('老白'),('小白'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from student; +----+--------+------+ | id | name | sex | +----+--------+------+ | 1 | 老白 | male | | 2 | 小白 | male | +----+--------+------+ 2 rows in set (0.00 sec)
mysql> insert into student values(4,'asb','female'); Query OK, 1 row affected (0.00 sec) mysql> insert into student values(7,'wsb','female'); Query OK, 1 row affected (0.01 sec) mysql> select * from student; +----+--------+--------+ | id | name | sex | +----+--------+--------+ | 1 | 老白 | male | | 2 | 小白 | male | | 4 | asb | female | | 7 | wsb | female | +----+--------+--------+ rows in set (0.00 sec) # 再次插入一条不指定id的记录，会在之前的最后一条记录继续增长 mysql> insert into student(name) values ('大白'); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +----+--------+--------+ | id | name | sex | +----+--------+--------+ | 1 | 老白 | male | | 2 | 小白 | male | | 4 | asb | female | | 7 | wsb | female | | 8 | 大白 | male | +----+--------+--------+ rows in set (0.00 sec) 也可以指定id
注意 : 对于自增长的字段,在用delete删除之后,在插入值 , 该字段仍按照删除前的位置继续增长.
mysql> delete from student; Query OK, 5 rows affected (0.00 sec) mysql> select * from student; Empty set (0.00 sec) mysql> select * from student; Empty set (0.00 sec) mysql> insert into student(name) values('ysb'); Query OK, 1 row affected (0.01 sec) mysql> select * from student; +----+------+------+ | id | name | sex | +----+------+------+ | 9 | ysb | male | +----+------+------+ row in set (0.00 sec) #应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它 mysql> truncate student; Query OK, 0 rows affected (0.03 sec) mysql> insert into student(name) values('xiaobai'); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +----+---------+------+ | id | name | sex | +----+---------+------+ | 1 | xiaobai | male | +----+---------+------+ row in set (0.00 sec) mysql> 对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
清空表 区分 delete 和 truncate 的区别 :
delete from t1; 如果有自增长id,新增的数据,仍然是以删除前的最后一样作为起始.
truncate table t1; 数据量大,删除的速度比上一条块,且直接从零开始.
六 . foreign key 外键
原来我们创建表的时候是在一张表中添加记录,有时候会出现重复信息,如图所示 :
这个时候,员工所在的部门和从事的工作就是属于重复存储的字段,人越多,部门名字越长,浪费!!!
这个时候就可以用到 foreign key ---关联
这样就会形成两张表 , 一张是 employee 表,简称emp表(关联表,也就是从表) . 一张是 department表,简称 dep表(被关联表 , 主表)
创建两张表的操作 :
#1.创建表时先创建被关联表，再创建关联表 # 先创建被关联表（dep表） create table dep( id int primary key, name varchar(20) not null, descripe varchar(20) not null ); #再创建关联表（emp表） create table emp( id int primary key, name varchar(20) not null, age int not null, dep_id int, constraint fk_dep foreign key(dep_id) references dep(id) ); #2.插入记录时，先往被关联表中插入记录，再往关联表中插入记录 insert into dep values (1,'IT','IT技术有限部门'), (2,'销售部','销售部门'), (3,'财务部','花钱太多部门'); insert into emp values (1,'zhangsan',18,1), (2,'lisi',19,1), (3,'egon',20,2), (4,'yuanhao',40,3), (5,'alex',18,2); 3.删除表 #按道理来说，删除了部门表中的某个部门，员工表的有关联的记录相继删除。 mysql> delete from dep where id=3; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db5`.`emp`, CONSTRAINT `fk_name` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`)) #但是先删除员工表的记录之后，再删除当前部门就没有任何问题 mysql> delete from emp where dep =3; Query OK, 1 row affected (0.00 sec) mysql> select * from emp; +----+----------+-----+--------+ | id | name | age | dep_id | +----+----------+-----+--------+ | 1 | zhangsan | 18 | 1 | | 2 | lisi | 18 | 1 | | 3 | egon | 20 | 2 | | 5 | alex | 18 | 2 | +----+----------+-----+--------+ rows in set (0.00 sec) mysql> delete from dep where id=3; Query OK, 1 row affected (0.00 sec) mysql> select * from dep; +----+-----------+----------------------+ | id | name | descripe | +----+-----------+----------------------+ | 1 | IT | IT技术有限部门 | | 2 | 销售部 | 销售部门 | +----+-----------+----------------------+ rows in set (0.00 sec)
注意 : 创建的时候要先创建主表,在创建从表去关联主表 .
插入记录时 : 先给主表插入记录,在往从表插入计入
按照上面的删除方法,要先删除从表的员工,才可以删除主表里的部门,这样不符合现实,也繁琐. 这个时候就有了 : 同步删除,同步更新.
要在关联表(从表)中加入 :
on delete cascade : 同步删除
on update cascade : 同步更新
修改 emp表 :
create table emp( id int primary key, name varchar(20) not null, age int not null, dep_id int, constraint fk_dep foreign key(dep_id) references dep(id)
# 关联 dep_id 主表的id在从表中的显示 dep(id)---主表的id on delete cascade #同步删除 on update cascade #同步更新 );
这样就可以正常的删除了 :
#再去删被关联表(dep)的记录，关联表(emp)中的记录也跟着删除 mysql> delete from dep where id=3; Query OK, 1 row affected (0.00 sec) mysql> select * from dep; +----+-----------+----------------------+ | id | name | descripe | +----+-----------+----------------------+ | 1 | IT | IT技术有限部门 | | 2 | 销售部 | 销售部门 | +----+-----------+----------------------+ 2 rows in set (0.00 sec) mysql> select * from emp; +----+----------+-----+--------+ | id | name | age | dep_id | +----+----------+-----+--------+ | 1 | zhangsan | 18 | 1 | | 2 | lisi | 19 | 1 | | 3 | egon | 20 | 2 | | 5 | alex | 18 | 2 | +----+----------+-----+--------+ 4 rows in set (0.00 sec) #再去更改被关联表(dep)的记录，关联表(emp)中的记录也跟着更改 mysql> update dep set id=222 where id=2; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 赶紧去查看一下两张表是否都被删除了，是否都被更改了 mysql> select * from dep; +-----+-----------+----------------------+ | id | name | descripe | +-----+-----------+----------------------+ | 1 | IT | IT技术有限部门 | | 222 | 销售部 | 销售部门 | +-----+-----------+----------------------+ 2 rows in set (0.00 sec) mysql> select * from emp; +----+----------+-----+--------+ | id | name | age | dep_id | +----+----------+-----+--------+ | 1 | zhangsan | 18 | 1 | | 2 | lisi | 19 | 1 | | 3 | egon | 20 | 222 | | 5 | alex | 18 | 222 | +----+----------+-----+--------+ 4 rows in set (0.00 sec)
总结 :
创建的时候 : 要先创建主表,在创建从表去关联主表 .
插入记录时 : 先给主表插入记录,在往从表插入计入
删除的时候 :
如果有 同步删除 同步更新. 就可以删除被关联dep的记录,关联表emp的记录也会删除.
没有 同步删除,同步更新 . 只能先删除关联表emp的记录, 在去删除dep记录.
# dep : 被关联表,主表 create table dep( id int primary key auto_increment, name varchar(20) not null, des varchar(30) not null ); create table dep2( id int primary key auto_increment, name varchar(20) not null, des varchar(30) not null ); # emp ： 关联表 从表 create table emp( eid int primary key auto_increment, name char(10) not null, age int not null, dep_id int not null, dep2_id int not null, constraint fk_dep foreign key(dep_id) references dep(id) on delete cascade # 同步删除 on update cascade, # 同步更新 constraint fk_dep2 foreign key(dep2_id) references dep2(id) on delete cascade # 同步删除 on update cascade # 同步更新 );
外键的变种 三种关系
因为有 foreign key 的约束 , 使得两张表形成了三种关系 :
- 多对一
- 多对多
- 一对一
两张表之间的关系 *******
分析步骤： #1、先站在左表的角度去找 是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id） #2、再站在右表的角度去找 是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id） #3、总结： #多对一： 如果只有步骤1成立，则是左表多对一右表 如果只有步骤2成立，则是右表多对一左表 #多对多 如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系 #一对一: 如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可
表的三种关系
- 一对多 (多对一)
比如 书和出版社 : 一个出版社可以出版很多书,但是一本书只可以有一个出版社
create table press( id int primary key auto_increment, name varchar(20) ); create table book( id int primary key auto_increment, name varchar(20), press_id int not null, constraint fk_book_press foreign key(press_id) references press(id) on delete cascade on update cascade ); # 先往被关联表中插入记录 insert into press(name) values ('北京工业地雷出版社'), ('人民音乐不好听出版社'), ('知识产权没有用出版社') ; # 再往关联表中插入记录 insert into book(name,press_id) values ('九阳神功',1), ('九阴真经',2), ('九阴白骨爪',2), ('独孤九剑',3), ('降龙十巴掌',2), ('葵花宝典',3) ; 查询结果： mysql> select * from book; +----+-----------------+----------+ | id | name | press_id | +----+-----------------+----------+ | 1 | 九阳神功 | 1 | | 2 | 九阴真经 | 2 | | 3 | 九阴白骨爪 | 2 | | 4 | 独孤九剑 | 3 | | 5 | 降龙十巴掌 | 2 | | 6 | 葵花宝典 | 3 | +----+-----------------+----------+ rows in set (0.00 sec) mysql> select * from press; +----+--------------------------------+ | id | name | +----+--------------------------------+ | 1 | 北京工业地雷出版社 | | 2 | 人民音乐不好听出版社 | | 3 | 知识产权没有用出版社 | +----+--------------------------------+ rows in set (0.00 sec) 书和出版社（多对一）
- 多对多
比如 作者和书籍 : 一个作者可以写很多书,一本书也可以有多个作者,双向的一对多就是多对多.
# 创建被关联表author表，之前的book表在讲多对一的关系已创建 create table author( id int primary key auto_increment, name varchar(20) ); #这张表就存放了author表和book表的关系，即查询二者的关系查这表就可以了 create table author2book( id int not null unique auto_increment, author_id int not null, book_id int not null, constraint fk_author foreign key(author_id) references author(id) on delete cascade on update cascade, constraint fk_book foreign key(book_id) references book(id) on delete cascade on update cascade, primary key(author_id,book_id) ); #插入四个作者，id依次排开 insert into author(name) values('egon'),('alex'),('wusir'),('yuanhao'); # 每个作者的代表作 egon: 九阳神功、九阴真经、九阴白骨爪、独孤九剑、降龙十巴掌、葵花宝典 alex: 九阳神功、葵花宝典 wusir:独孤九剑、降龙十巴掌、葵花宝典 yuanhao:九阳神功 # 在author2book表中插入相应的数据 insert into author2book(author_id,book_id) values (1,1), (1,2), (1,3), (1,4), (1,5), (1,6), (2,1), (2,6), (3,4), (3,5), (3,6), (4,1) ; # 现在就可以查author2book对应的作者和书的关系了 mysql> select * from author2book; +----+-----------+---------+ | id | author_id | book_id | +----+-----------+---------+ | 1 | 1 | 1 | | 2 | 1 | 2 | | 3 | 1 | 3 | | 4 | 1 | 4 | | 5 | 1 | 5 | | 6 | 1 | 6 | | 7 | 2 | 1 | | 8 | 2 | 6 | | 9 | 3 | 4 | | 10 | 3 | 5 | | 11 | 3 | 6 | | 12 | 4 | 1 | +----+-----------+---------+ rows in set (0.00 sec) 作者与书籍关系（多对多）
- 一对一
比如 : 一个用户只能注册一个博客,就是一对一
#例如： 一个用户只能注册一个博客 #两张表： 用户表 （user）和 博客表(blog) # 创建用户表 create table user( id int primary key auto_increment, name varchar(20) ); # 创建博客表 create table blog( id int primary key auto_increment, url varchar(100), user_id int unique, constraint fk_user foreign key(user_id) references user(id) on delete cascade on update cascade ); #插入用户表中的记录 insert into user(name) values ('alex'), ('wusir'), ('egon'), ('xiaoma') ; # 插入博客表的记录 insert into blog(url,user_id) values ('http://www.cnblog/alex',1), ('http://www.cnblog/wusir',2), ('http://www.cnblog/egon',3), ('http://www.cnblog/xiaoma',4) ; # 查询wusir的博客地址 select url from blog where user_id=2; 用户和博客（一对一）
