表的完整性约束
一.介绍
约束条件与数据类型的宽度一样，都是可选参数
作用：用于保证数据的完整性和一致性
主要分为：
PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK) 标识该字段为该表的外键
NOT NULL 标识该字段不能为空
UNIQUE KEY (UK) 标识该字段的值是唯一的
AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT 为该字段设置默认值
UNSIGNED 无符号
ZEROFILL 使用0填充
说明
1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值 2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值 sex enum('male','female') not null default 'male' age int unsigned NOT NULL default 20 必须为正值（无符号） 不允许为空 默认是20 3. 是否是key 主键 primary key 外键 foreign key 索引 (index,unique...)
二 .not null与default
是否可空，null表示空，非字符串 not null - 不可空 null - 可空 默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值 create table tb1( id int not null defalut 2, num int not null )
mysql> create table t15( -> id int, -> name char(16) not null, -> sex char(6) not null default "male" -> ); Query OK, 0 rows affected (0.03 sec) mysql> desc t15; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | char(16) | NO | | NULL | | | sex | char(6) | NO | | male | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.04 sec) mysql> insert into t15(id,name) values -> (1,'egon1'), -> (2,'egon2'), -> (3,'egon3'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t15; +------+-------+------+ | id | name | sex | +------+-------+------+ | 1 | egon1 | male | | 2 | egon2 | male | | 3 | egon3 | male | +------+-------+------+ 3 rows in set (0.00 sec) mysql> drop table t15; Query OK, 0 rows affected (0.02 sec) mysql> create table t15( -> id int, -> name char(16) not null, -> sex enum('male','female','other') not null default "male" -> ); Query OK, 0 rows affected (0.03 sec) mysql> insert into t15(id,name) values -> (1,'egon1'), -> (2,'egon2'), -> (3,'egon3'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t15; +------+-------+------+ | id | name | sex | +------+-------+------+ | 1 | egon1 | male | | 2 | egon2 | male | | 3 | egon3 | male | +------+-------+------+ 3 rows in set (0.00 sec)
三 .unique
#unique:限制字段的值唯一
#单列唯一
create table t16( id int unique, name char(16) );
# 联合唯一
create table server( id int unique, ip char(15), port int, unique(ip,port) );
mysql> create table t16( -> id int unique, -> name char(16) -> ); Query OK, 0 rows affected (0.04 sec) mysql> insert into t16 values(1,'egon'); Query OK, 1 row affected (0.01 sec) mysql> desc t16; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | UNI | NULL | | | name | char(16) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.03 sec) mysql> insert into t16 values(2,'egon'); Query OK, 1 row affected (0.00 sec) mysql> insert into t16 values(3,'wangmiaolu'); Query OK, 1 row affected (0.00 sec) mysql> select*from t16; +------+------------+ | id | name | +------+------------+ | 1 | egon | | 2 | egon | | 3 | wangmiaolu | +------+------------+ 3 rows in set (0.00 sec) mysql> create table server( -> id int unique, -> ip char(15), -> port int, -> unique(ip,port) -> ); Query OK, 0 rows affected (0.03 sec) mysql> insert into server values(1,'1.1.1.1',3306); Query OK, 1 row affected (0.01 sec) mysql> insert into server values(2,'1.1.1.1',3307); Query OK, 1 row affected (0.00 sec) mysql> select*from server; +------+---------+------+ | id | ip | port | +------+---------+------+ | 1 | 1.1.1.1 | 3306 | | 2 | 1.1.1.1 | 3307 | +------+---------+------+ 2 rows in set (0.00 sec)
四.primary key和auto_increment
#primary key:单单从约束角度去看，primary key就等同于not null unique 不为空且唯一 #强调（******） 1、一张表中必须有，并且只能有一个主键 innodb会以主键为依据来组织数据 2、一张表中都应该有一个id字段，而且应该把id字段做成主键 create table t17( id int primary key, name char(16), age int, sex char(6) )engine=innodb; #联合主键 create table t19( ip char(15), port int, primary key(ip,port) ); # primary key auto_increment create table t20( id int primary key auto_increment, name char(16) )engine=innodb; # auto_increment注意点： 1、通常与primary key连用，而且通常是给id字段加 2、auto_incremnt只能给被定义成key（可以是unique key，primary key）的字段加 foreign key不是用来加速查询的
五.foreign key
5.1多对一之间的关系(多个记录对一条记录)
什么是外键?
是用来建立表与表之间的硬性的关系，多对一，一对一，多对多
1、把所有数据都存放于一张表的弊端 1、表的组织结构复杂不清晰 2、浪费空间 3、扩展性极差 2、寻找表与表之间的关系的套路 举例：emp表(关联表) dep表(被关联表) 步骤一： part1： 1、先站在左表emp的角度 2、去找左表emp的多条记录能否对应右表dep的一条记录 3、翻译2的意义： 左表emp的多条记录==》多个员工 右表dep的一条记录==》一个部门 最终翻译结果：多个员工是否可以属于一个部门？ 如果是则需要进行part2的流程 part2： 1、站在右表dep的角度 2、去找右表dep的多条记录能否对应左表emp的一条记录 3、翻译2的意义： 右表dep的多条记录==》多个部门 左表emp的一条记录==》一个员工 最终翻译结果：多个部门是否可以包含同一个员工 如果不可以，则可以确定emp与dep的关系只一个单向的多对一 如何实现？ 在emp表中新增一个dep_id字段，该字段指向dep表的id字段
foreign key会带来什么样的效果？
1、约束1：在创建表时，先建被关联的表dep，才能建关联表emp
create table dep( id int primary key auto_increment, dep_name char(10), dep_comment char(60) ); create table emp( id int primary key auto_increment, name char(16), gender enum('male','female') not null default 'male', dep_id int, foreign key(dep_id) references dep(id) );
2、约束2：在插入记录时，必须先插被关联的表dep，才能插关联表emp
insert into dep(dep_name,dep_comment) values ('sb教学部','sb辅导学生学习，教授python课程'), ('外交部','老男孩上海校区驻张江形象大使'), ('nb技术部','nb技术能力有限部门'); insert into emp(name,gender,dep_id) values ('alex','male',1), ('egon','male',2), ('lxx','male',1), ('wxx','male',1), ('wenzhou','female',3);
补充清空表
delete from tb1; //delete是删除表当中某几条记录的 强调：上面的这条命令确实可以将表里的所有记录都删掉，但不会将id重置为0， 所以收该条命令根本不是用来清空表的，delete是用来删除表中某一些符合条件的记录 delete from tb1 where id > 10; 如果要清空表，使用truncate tb1； 作用：将整张表重置
3、约束3：更新与删除都需要考虑到关联与被关联的关系
解决方案： 1、先删除关联表emp，再删除被关联表dep，准备重建 mysql> drop table emp; Query OK, 0 rows affected (0.11 sec) mysql> drop table dep; Query OK, 0 rows affected (0.04 sec) 2、重建：新增功能，同步更新，同步删除 create table dep( id int primary key auto_increment, dep_name char(10), dep_comment char(60) ); create table emp( id int primary key auto_increment, name char(16), gender enum('male','female') not null default 'male', dep_id int, foreign key(dep_id) references dep(id) on update cascade on delete cascade ); insert into dep(dep_name,dep_comment) values ('sb教学部','sb辅导学生学习，教授python课程'), ('外交部','老男孩上海校区驻张江形象大使'), ('nb技术部','nb技术能力有限部门'); insert into emp(name,gender,dep_id) values ('alex','male',1), ('egon','male',2), ('lxx','male',1), ('wxx','male',1), ('wenzhou','female',3);
# 同步删除 mysql> select * from dep; +----+------------------+------------------------------------------------------------------------------------------+ | id | dep_name | dep_comment | +----+------------------+------------------------------------------------------------------------------------------+ | 1 | sb教学部 | sb辅导学生学习，教授python课程 | | 2 | 外交部 | 老男孩上海校区驻张江形象大使 | | 3 | nb技术部 | nb技术能力有限部门 | +----+------------------+------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> select * from emp; +----+------------------+--------+--------+ | id | name | gender | dep_id | +----+------------------+--------+--------+ | 1 | alex | male | 1 | | 2 | egon | male | 2 | | 3 | lxx | male | 1 | | 4 | wxx | male | 1 | | 5 | wenzhou | female | 3 | +----+------------------+--------+--------+ 5 rows in set (0.00 sec) mysql> delete from dep where id=1; Query OK, 1 row affected (0.02 sec) mysql> select * from dep; +----+------------------+------------------------------------------------------------------------------------------+ | id | dep_name | dep_comment | +----+------------------+------------------------------------------------------------------------------------------+ | 2 | 外交部 | 老男孩上海校区驻张江形象大使 | | 3 | nb技术部 | nb技术能力有限部门 | +----+------------------+------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> select * from emp; +----+------------------+--------+--------+ | id | name | gender | dep_id | +----+------------------+--------+--------+ | 2 | egon | male | 2 | | 5 | wenzhou | female | 3 | +----+------------------+--------+--------+ 2 rows in set (0.00 sec) #同步更新 mysql> select * from emp; +----+------------------+--------+--------+ | id | name | gender | dep_id | +----+------------------+--------+--------+ | 2 | egon | male | 2 | | 5 | wenzhou | female | 3 | +----+------------------+--------+--------+ 2 rows in set (0.00 sec) mysql> update dep set id=200 where id =2; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from dep; +-----+------------------+------------------------------------------------------------------------------------------+ | id | dep_name | dep_comment | +-----+------------------+------------------------------------------------------------------------------------------+ | 3 | nb技术部 | nb技术能力有限部门 | | 200 | 外交部 | 老男孩上海校区驻张江形象大使 | +-----+------------------+------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> select * from emp; +----+------------------+--------+--------+ | id | name | gender | dep_id | +----+------------------+--------+--------+ | 2 | egon | male | 200 | | 5 | wenzhou | female | 3 | +----+------------------+--------+--------+ 2 rows in set (0.00 sec)
5.2多对多之间的关系
1、什么是多对多
两张表之间是一个双向的多对一关系，称之为多对多
如何实现？
建立第三张表，该表中有一个字段fk左表的id，还有一个字段是fk右表的id
create table author( id int primary key auto_increment, name char(16) ); create table book( id int primary key auto_increment, bname char(16), price int ); insert into author(name) values ('egon'), ('alex'), ('wxx') ; insert into book(bname,price) values ('python从入门到入土',200), ('葵花宝典切割到精通',800), ('九阴真经',500), ('九阳神功',100) ; create table author2book( id int primary key auto_increment, author_id int, book_id int, foreign key(author_id) references author(id) on update cascade on delete cascade, foreign key(book_id) references book(id) on update cascade on delete cascade ); insert into author2book(author_id,book_id) values (1,3), (1,4), (2,2), (2,4), (3,1), (3,2), (3,3), (3,4);
5.3一对一之间的关系
#一对一： 左表的一条记录唯一对应右表的一条记录，反之也一样 create table customer( id int primary key auto_increment, name char(20) not null, qq char(10) not null, phone char(16) not null ); create table student( id int primary key auto_increment, class_name char(20) not null, customer_id int unique, #该字段一定要是唯一的 foreign key(customer_id) references customer(id) #外键的字段一定要保证unique on delete cascade on update cascade ); insert into customer(name,qq,phone) values ('李飞机','31811231',13811341220), ('王大炮','123123123',15213146809), ('守榴弹','283818181',1867141331), ('吴坦克','283818181',1851143312), ('赢火箭','888818181',1861243314), ('战地雷','112312312',18811431230) ; #增加学生 insert into student(class_name,customer_id) values ('脱产3班',3), ('周末19期',4), ('周末19期',5) ;
浙公网安备 33010602011771号