一 介绍
约束条件与数据类型的宽度一样，都是可选参数
作用：用于保证数据的完整性和一致性
主要分为：
not null 非空约束 数据不能为空default默认值约束 可以指定字段的默认值unique唯一性约束 该字段的值不能重复，标识该字段的值是唯一的primary key 主键约束 可以唯一的标识记录 ，唯一的且不能为空auto_increment 自动增长，从1开始增长（整数类型，而且为主键）foreign key 外键约束unsigend 无符号zerofill 使用0填充1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值sex enum('male','female') not null default 'male'age int unsigned NOT NULL default 20 必须为正值（无符号） 不允许为空 默认是203. 是否是key主键 primary key外键 foreign key索引 (index,unique...)
#1、not null 非空约束 数据不能为空如：学生表的姓名字段，create table student (id ind,name char(10) not null);#2、default 默认值约束 可以指定字段的默认值如：create table user (id int,name char(10) not null,sex char(10) default "woman");#3、unique 唯一性约束 该字段的值不能重复 如表示身份证 手机号 学号等unique其实是一种索引，索引是一种数据结构 用于提高查询效率传值可以为空，一张表中可以有多个唯一约束1.单列唯一约束，create table t5(idcard char(18) unique);2.多列联合唯一约束,create table t6(idcard char(18),phonenumber char(11),unique(idcard,phonenumber));意思： 身份证相同 并且 手机号相同 那就叫相同#4、primary key 主键约束，用于唯一标识表中一条记录，从约束的角度来看主键约束和 非空 加 唯一 约束没有区别 主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引组织表，一张表中必须有且只有一个主键。有主键 和没有主键的区别？1.无法区分两个相同记录 比如班级里有两个人名字相同2.有主键则意味有这索引 效率更高3.可以建立关联关系语法：create table stu (stuid int primary key,name char(3));等价于create table t7(id int unique not null,name char(3));多列联合主键：create table t8(idcard char(18),phonenumber char(11),primary key(idcard,phonenumber));#5、auto_increment自动增长，通常搭配主键字段使用 可以自动为你的数据分配逐渐，只能用于整型语法:create table t9(id int primary key auto_increment,name char(3));如果主键是自动增长 你可以跳过这个字段 也可以为它插入null，也可以指定id修改自动增长的起始位置：alter table t9 auto_increment = 7;#6、foreign key外键约束
#primary key:单单从约束角度去看，primary key就等同于not null unique#强调（******）1、一张表中必须有，并且只能有一个主键2、一张表中都应该有一个id字段，而且应该把id字段做成主键create table t17(id int primary key,name char(16),age int,sex char(6))engine=innodb;#联合主键create table t25(ip char(15),port int,primary key(ip,port));# auto_increment注意点：1、通常与primary key连用，而且通常是给id字段加2、auto_incremnt只能给被定义成key（unique key，primary key）的字段加# primary key auto_incrementcreate table t26(id int primary key auto_increment,name char(16))engine=innodb;不用插id insert into t20(name) values('egon');
primary key
二 foreign key ==> 表与表之间的关系
1.数据冗余、效率低下、扩展性差 ==> 分表
2.mysql提供了 foreign key 专门用于为表和表之间 建立物理关联
3.两个表之间能产生的关系有哪些?
多对一、一对一、多对多
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，才能建关联表empcreate 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) 加一个约束条件，后面指向关联另一张表的id);#2、约束2：在插入记录时，必须先插被关联的表dep，才能插关联表empinsert 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);#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 cascadeon 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: 0mysql> 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)
表与表之间的关系多对一
delete from tb1;强调：上面的这条命令确实可以将表里的所有记录都删掉，但不会将id重置为0，所以收该条命令根本不是用来清空表的，delete是用来删除表中某一些符合条件的记录delete from tb1 where id > 10;如果要清空表，使用truncate tb1；作用：将整张表重置
假如有员工和部门两张表，在查找表之间的关系时 要分别站在 不同表去思考1. 从员工出发 员工对于部门来说时，多个员工对应一个部门2. 从部门出发 一个部门对应多个员工，如果两个得到的关系不同，则认为这种多对一关系是单向先创建部门表create table dept(id int primary key auto_increment,name char(10),manager char(10));在创建员工表create table emp(id int primary key auto_increment,name char(10),dept_id int,foreign key(dept_id) references dept(id));需求: 设计 学员表 和 班级表两个表多对一的关系 通过外键来进行关联外键加在谁身上? 加到从表上create table class(id int primary key auto_increment,name char(10));create table student(id int primary key auto_increment,name char(10),c_id int,foreign key(c_id) references class(id));总结: 外键的作用 表与表之间建立联系添加外键约束时产生的限制：1.被关联的表需要先被创建2.部门数据(主表)应该先插入 员工数据(从表)后插入3.在删除部门数据前(主表)前 要保证该部门的员工数据都删除了4.在更新部门编号前 要先保证没有员工关联到这个部门简单的说 外键指的是 另一张的主键外键加上以后 主表中的数据 删除 和更新时 都受到限制解决的方案是为 外键 添加 级联操作级联操作：指的是就是同步更新和删除语法:在创建外键时 在后面添加 on update cascade 同步更新on delete cascade 同步删除实例:create table class(id int primary key auto_increment,name char(10));create table stu(id int primary key auto_increment,name char(10),c_id int,foreign key(c_id) references class(id) on update cascade on delete cascade);insert into class value(null,'python');insert into stu value(null,'egon',1);对主表的id进行更新，以及删除某条主表记录 来验证效果update class set id=3 where id=1; mysql> select *from stu;+----+------+------+| id | name | c_id |+----+------+------+| 1 | egon | 3 |+----+------+------+1 row in set (0.00 sec)delete from class where id=3;mysql> select *from stu;Empty set (0.00 sec)
多对多关系的处理:建立一个第三方表 专门存储两个表之间的关系这个关系表 应该有两个字段 分别关联学生的id 和老师的id为了保证 数据的合法性 完整性 给这两个字段都添加外键约束实例: 见图2create table teacher (id int primary key auto_increment,name char(10));create table student (id int primary key auto_increment,name char(10));create table t_s (t_id int,s_id int,foreign key(t_id) references teacher(id),foreign key(s_id) references student(id));为了避免重复无用的关系数据 关系表加上关联的主键约束create table t_s (t_id int,s_id int,foreign key(t_id) references teacher(id),foreign key(s_id) references student(id),primary key(t_id,s_id));insert into student value(null,"lxx");insert into teacher value(null,"exx");insert into t_s value(1,1)
多对多关系整理
如果觉得《mysql表操作之完整性约束》对你有帮助，请点赞、收藏，并留下你的观点哦！
