文章目录数据库表的约束和表之间的关系1. 数据库表的约束1.1 主键约束1.1.1 创建主键1.1.2 删除主键1.1.3 主键自增1.1.3.1 修改自增长的默认值起始值1.1.3.2 delete 和 truncate对自增长的影响1.2 唯一约束1.3 非空约束1.2.1 非空约束默认值1.4 外键约束1.4.1 单表的缺点1.4.2 解决方案1.4.3 外键约束1.4.3.1 创建外键约束1.4.3.2 删除外键1.4.3.3 外键的级联1.5 数据约束小结2. 表与表之间的关系2.1 一对多2.2 多对多2.3 一对一数据库表的约束和表之间的关系1. 数据库表的约束约束的作用对表中的数据进行限制保证数据的正确性、有效性和完整性。一个表如果添加了约束不正确的数据将无法插入到表中。约束在创建表的时候添加比较合适。约束的种类约束名约束关键字主键primary key唯一unique非空not null外键foreign key检查约束check 注mysql 不支持1.1 主键约束用来唯一标识数据库中的每一条记录哪个字段应该作为表的主键通常不用业务字段作为主键单独给每张表设计一个 id 的字段把 id 作为主键。主键是给数据库和程序使用的不是给最终的客户使用的。所以主键有没有含义没有关系只要不重复非空就行。如身份证学号不建议做成主键1.1.1 创建主键创建主键关键字 primary key主键的特点非空 not null唯一 unique-- 创建主键的两种方式-- 1.在创建表的时候给字段添加主键字段名 字段类型primarykey-- 2.在已有表中添加主键altertable表名addprimarykey(字段名);-- 演示创建表学生表 student,包含字段(id,name,age)将 id做为主键createtablestudent(idintprimarykey,-- id为主键namevarchar(20),ageint)descstudent;-- 插入重复的主键值insertintostudentvalues(1,上官婉儿,18);-- 错误代码1062 Duplicate entry 1 for keyPRIMARYinsertintostudentvalues(1,上官,20);select*fromstudent;-- 插入 null的主键值, Column id cannot be nullinsertintost5values(null,上官婉儿,20);1.1.2 删除主键-- 删除student表的主键altertablestudentdropprimarykey;-- 添加主键altertablestudentaddprimarykey(id);1.1.3 主键自增主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值auto_increment表示自动增长(字段类型必须是整数类型)创建表学生表 student,包含字段(id,name,age)将 id做为主键createtablestudent(idintprimarykeyauto_increment,-- id为主键namevarchar(20),ageint)-- 插入数据insertintostudent(name,age)values(小乔,18);insertintostudent(name,age)values(大乔,20);-- 另一种写法insertintostudentvalues(null,周瑜,35);select*fromstudent;1.1.3.1 修改自增长的默认值起始值默认地 AUTO_INCREMENT 的开始值是 1如果希望修改起始值,请使用下列 SQL 语法创建表的时候修改自增长起始值createtable表名(列名intprimaryauto_increment)auto_increment起始值;-- 修改自增长的默认值起始值-- 指定起始值为 1000createtablestudent(idintprimarykeyauto_increment,namevarchar(20))auto_increment1000;insertintost4values(null,孔明);创建好表以后修改起始值altertable表名auto_increment起始值;altertablestudentauto_increment2000;insertintostudentvalues(null,刘备);1.1.3.2 delete 和 truncate对自增长的影响delete 删除所有的记录之后自增长没有影响。truncate 删除以后自增长又重新开始。1.2 唯一约束什么是唯一约束就是表中某一列不能出现重复的值。-- 唯一约束的基本语法格式字段名 字段类型unique;-- 创建学生表 student, 包含字段(id, name),name这一列设置唯一约束,不能出现同名的学生createtablestudent(idint,namevarchar(20)unique)-- 添加一个同名的学生insertintostudentvalues(1,张三);select*fromstudent;-- Duplicate entry 张三 for key nameinsertintostudentvalues(2,张三);-- 重复插入多个 null会怎样insertintostudentvalues(2,null);insertintostudentvalues(3,null);-- null没有数据不存在重复的问题-- null没有数据不存在重复的问题-- null没有数据不存在重复的问题1.3 非空约束什么是非空约束就是某一列不能为 null。-- 非空约束的基本语法格式字段名 字段类型notnull;-- 创建表学生表 student, 包含字段(id,name,gender)其中 name不能为 nullcreatetablestudent(idint,namevarchar(20)notnull,genderchar(1));-- 添加一条记录其中姓名不赋值insertintostudentvalues(1,张三疯,男);select*fromstudent;-- Column name cannot be nullinsertintostudentvalues(2,null,男);1.2.1 非空约束默认值什么是非空约束默认值字段名 字段类型 default 默认值;-- 创建一个学生表 student4包含字段(id,name,address)地址默认值是广州createtablestudent(idint,namevarchar(20),addressvarchar(20)default广州)-- 添加一条记录,使用默认地址insertintostudentvalues(1,李四,default);select*fromstudent;insertintostudent(id,name)values(2,李白);-- 添加一条记录,不使用默认地址insertintostudentvalues(3,李四光,深圳);如果一个字段设置了非空与唯一约束该字段与主键的区别?主键数在一个表中只能有一个。不能出现多个主键。主键可以单列也可以是多列。自增长只能用在主键上1.4 外键约束1.4.1 单表的缺点-- 创建一个员工表包含如下列(id, name, age, dep_name, dep_location),id 主键并自动增长,添加 6 条数据createtableemp(idintprimarykeyauto_increment,namevarchar(30),ageint,dep_namevarchar(30),dep_locationvarchar(30));-- 添加数据insertintoemp(name,age,dep_name,dep_location)values(张三,20,研发部,广州);insertintoemp(name,age,dep_name,dep_location)values(李四,21,研发部,广州);insertintoemp(name,age,dep_name,dep_location)values(王五,20,研发部,广州);insertintoemp(name,age,dep_name,dep_location)values(老王,20,销售部,深圳);insertintoemp(name,age,dep_name,dep_location)values(大王,22,销售部,深圳);insertintoemp(name,age,dep_name,dep_location)values(小王,18,销售部,深圳);以上数据表的缺点:数据冗余后期还会出现增删改的问题1.4.2 解决方案分成 2 张表设置主表和从表。-- 创建部门表(id,dep_name,dep_location)-- 一方主表createtabledepartment(idintprimarykeyauto_increment,dep_namevarchar(20),dep_locationvarchar(20));-- 创建员工表(id,name,age,dep_id)-- 多方从表createtableemployee(idintprimarykeyauto_increment,namevarchar(20),ageint,dep_idint-- 外键对应主表的主键)-- 添加 2 个部门insertintodepartmentvalues(null,研发部,广州),(null,销售部,深圳);select*fromdepartment;-- 添加员工,dep_id表示员工所在的部门insertintoemployee(name,age,dep_id)values(张三,20,1);insertintoemployee(name,age,dep_id)values(李四,21,1);insertintoemployee(name,age,dep_id)values(王五,20,1);insertintoemployee(name,age,dep_id)values(老王,20,2);insertintoemployee(name,age,dep_id)values(大王,22,2);insertintoemployee(name,age,dep_id)values(小王,18,2);select*fromemployee;当我们在 employee 的 dep_id 里面输入不存在的部门,数据依然可以添加.但是并没有对应的部门实际应用中不能出现这种情况。employee 的 dep_id 中的数据只能是 department 表中存在的 id1.4.3 外键约束什么是外键在从表中与主表主键对应的那一列如员工表中dep_id主表一方用来约束别人的表从表多方被别人约束的表1.4.3.1 创建外键约束新建表时增加外键[constraint][外键约束名称]foreignkey(外键字段名)references主表名(主键字段名);已有表增加外键altertable从表add[constraint][外键约束名称]foreignkey(外键字段名)references主表(主键字段名);-- 1.删除副表/从表 employeedroptableemployee;-- 2.创建从表 employee 并添加外键约束 emp_depid_fk-- 多方从表createtableemployee(idintprimarykeyauto_increment,namevarchar(20),ageint,dep_idint,-- 外键对应主表的主键constraintemp_depid_fkforeignkey(dep_id)referencesdepartment(id)-- 创建外键约束)-- 3.正常添加数据insertintoemployee(name,age,dep_id)values(张三,20,1);insertintoemployee(name,age,dep_id)values(李四,21,1);insertintoemployee(name,age,dep_id)values(王五,20,1);insertintoemployee(name,age,dep_id)values(老王,20,2);insertintoemployee(name,age,dep_id)values(大王,22,2);insertintoemployee(name,age,dep_id)values(小王,18,2);select*fromemployee;-- 4.部门错误的数据添加失败-- 插入不存在的部门-- Cannot add or update a child row: a foreign key constraint failsinsertintoemployee(name,age,dep_id)values(老张,18,6);1.4.3.2 删除外键alter table 从表 drop foreign key 外键名称;-- 删除 employee表的 emp_depid_fk外键altertableemployeedropforeignkeyemp_depid_fk;-- 在 employee表存在的情况下添加外键altertableemployeeaddconstraintemp_depid_fkforeignkey(dep_id)referencesdepartment(id);1.4.3.3 外键的级联什么是级联操作在修改和删除主表的主键时同时更新或删除副表的外键值称为级联操作-- 出现新的问题select*fromemployee;select*fromdepartment;/* 要把部门表中的 id值 2改成 5能不能直接更新呢会报出一下错误 Cannot delete or update a parent row: a foreign key constraint fails */updatedepartmentsetid5whereid2;/* 要删除部门 id等于 1的部门, 能不能直接删除呢会报出一下错误 Cannot delete or update a parent row: a foreign key constraint fails */deletefromdepartmentwhereid1;级联操作语法描述on update cascade级联更新只能是创建表的时候创建级联关系,更新主表中的主键从表中的外键列也自动同步更新on delete cascade级联删除-- 删除 employee表重新创建 employee表添加级联更新和级联删除droptableemployee;createtableemployee(idintprimarykeyauto_increment,namevarchar(20),ageint,dep_idint,-- 外键对应主表的主键constraintemp_depid_fkforeignkey(dep_id)referencesdepartment(id)onupdatecascadeondeletecascade-- 创建外键约束)-- 再次添加数据到员工表和部门表insertintoemployee(name,age,dep_id)values(张三,20,1);insertintoemployee(name,age,dep_id)values(李四,21,1);insertintoemployee(name,age,dep_id)values(王五,20,1);insertintoemployee(name,age,dep_id)values(老王,20,2);insertintoemployee(name,age,dep_id)values(大王,22,2);insertintoemployee(name,age,dep_id)values(小王,18,2);-- 删除部门表能不能直接删除droptabledepartment;-- 把部门表中 id等于 1的部门改成 id等于 10updatedepartmentsetid10whereid1;select*fromemployee;select*fromdepartment;-- 删除部门号是 2的部门deletefromdepartmentwhereid2;1.5 数据约束小结约束名关键字说明主键primary key唯一,非空默认default如果一列没有值使用默认值非空not null这一列必须有值唯一unique这一列不能有重复值外键foreign key主表中主键列在从表中外键列2. 表与表之间的关系概念现实生活中实体与实体之间肯定是有关系的比如爸爸和儿子部门和员工老师(喵喵)和学生(dd)等。那么我们在设计表的时候就应该体现出表与表之间的这种关系表与表之间的三种关系关系的维护一对多最常用的关系 部门和员工主外键的关系多对多学生选课表 和 学生表 一门课程可以有多个学生选择一个学生选择多门课程中间表两个一对多一对一相对使用比较少。员工表 简历表 公民表 护照表特殊一对多从表中的外键设置为唯一,从表中的主键又是外键2.1 一对多一对多1 : n例如班级和学生部门和员工客户和订单分类和商品一对多建表原则:​ 在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键2.2 多对多多对多m : n例如老师和学生学生和课程用户和角色多对多关系建表原则:​ 需要创建第三张表中间表中至少两个字段这两个字段分别作为外键指向各自一方的主键。2.3 一对一一对一1: 1在实际的开发中应用不多.因为一对一可以创建成一张表。两种建表原则一对一的建表原则说明外键唯一主表的主键和从表的外键唯一形成主外键关系外键唯一 unique外键是主键主表的主键和从表的主键形成主外键关系