MySQL之约束条件
一、约束条件之主键
# 1.primary key 主键 1.单从约束角度上来看主键等价于非空且唯一 not null unique create table t1( id int primary key, name varcahr(32) ); # 这样id的字段就被我们设置为主键了 写入数据的时候就不能为空而且唯一 2.Innodb存储引擎规定一张表必须有且只有一个主键 2.1我们之前创建表没有设置主键也没有报错这是为什么呢? 因为这是Innodb引擎的一个特性 如果我们创建一个表的时候如果我们没有设置主键 那么Innodb就会帮我们创建一个隐藏的字段然后设置为主键(主键能够快速查找数据:相当于新华字典的目录) 2.2如果创建表的时候没有主键 但时候有非空且唯一的字段 那么Innodb就会自动将该字段设置为主键 create table t2( nid not null unique, tid not null uniqeu, name varchar(32) ) # 这个时候innodb会把nid这个字段自动设置为主键 3.我们创建表的时候应该要创建一个'id'字段然后把这个字段设置为主键 ''' uid tid nid sid pid... ''' 补充: uid int primary key # 单列主键 sid int, nid int, primary key(sid,nid) # 联合主键 # 但是我们设置主键了主键有是非空且唯一 我们有时候写入数据的时候不知道写哪的时候不知道该写什么数据进去怎么办? # 2.auto_increment 自增 1.该约束条件不能单独使用 必须跟在键后面使用(主要搭配主键使用) # 如果单独使用会报错 create table t3( id int auto increment ); # 会报错 there can be only one auto column and it must be defined as a key # 要搭配键使用 create table t3( id int primary key auto_increment, name = varchar(32) ); # 这个时候id字段就被我们设置为主键了 并且还可以自增 ''' 我们在写入数据的时候可以不用写id的数据 insert into t3(name) value('jason'),('kevin'); 这样也不会报错主键会从1开始然后自增 ''' 自增补充: 当我们中途删除数据的时候自增是不会回退或则重置的 之后我们填写数据的时候数字的自增是按照删除的时候数字自增 # 如果非要重置只能表格格式化 truncate # 删除表数据并重置主键值
二、约束条件之外键
1.外键前戏
现在我们要创建一个员工表
| id | emp_name | emp_salary | dep_name | dep_desc | | ---- | -------- | --------- - | -------- | ------------ | | 1 | jason | 100000 | 外交部 | 搞外交 | | 2 | egon | 10000000 | 讲师部 | 教书育人 | | 3 | kevin | 100 | 讲师部 | 教书育人 | | 4 | tony | 159 | 讲师部 | 教书育人 | | 5 | jack | 233 | 教师部 | 教书育人 | | 6 | kevin | 99999 | 技术部 | 技术能力部门 | # 我们发现这个员工表的结构不明确 不知道是员工表还是部门表 # 浪费存储空间 可以购买硬盘解决 # 表的扩展性差 # 我们可以把一个表分成两个表 # 员工表: | id | emp_name | emp_salary | | ---- | -------- | ---------- | | 1 | jason | 100000 | | 2 | egon | 10000000 | | 3 | kevin | 100 | | 4 | tony | 159 | | 5 | jack | 233 | | 6 | kevin | 99999 |
# 部门表: | id | dep_name | dep_desc | | ---- | -------- | ----------- | | 1 | 外交部 | 搞外交 | | 2 | 讲师部 | 教书育人 | | 3 | 技术部 | 计数能力部门 |
# 这个时候我们解决了上述的上问题 但是这两个表就没有关系了 我们可以在员工表后面加一个字段: | id | emp_name | emp_salary | dep_id | | ---- | -------- | ---------- | ------ | | 1 | jason | 100000 | 1 | | 2 | egon | 10000000 | 2 | | 3 | kevin | 100 | 2 | | 4 | tony | 159 | 2 | | 5 | jack | 233 | 2 | | 6 | kevin | 99999 | 3 | ''' 这样我们就可以通过这个字段把两个表链接起来了 我们把这种字段称之为外键 外键就是用来建立表与表之间的关系的 '''
2.表与表之间的关系
2.1一对多关系
# 一对多关系 外键是用来记录表与表之间的关系的 而表与表之间的关系主要分为四种 一对多关系 (在MySQL数据库中多对一就是一对多关系) 多对多关系 一对一关系 没有关系 # 2.针对表之间的关系判断: '换位思考' 以员工表与部门表为例: # 先站在员工表角度 问:一条员工数据能对应多条部门数据吗? (其实就是一个员工能否属于多个部门) 答:不可以 # 再站在部门表的角度 问:一条部门数据能对应多条员工数据吗? (其实就是一个不部门能否有多个员工) 答:绝对可以 再完成换位思考之后我们可以知道两个表有一个可以一个不可以 那么表的关系就是"一对多" 员工是多 部门是一 针对'一对多'的关系 外键一般放在多的地方
2.2多对多关系
''' 我们一书籍表和作者表为例 一个书籍表中有许多的书籍 一个作者表中有许多的作者 ''' # 我们还是用换位思考解决 # 我们现在站在书籍表角度 问:一条书籍数据能否对应多条作者数据? (就是问一本书能否有多个作者) 答: 是可以的 (有些书的作者是有很多的) # 然后我们在站在作者的角度 问:一条作者数据能否对应多条书籍数据? (就是问一个作者能否写多本书籍) 答:那肯定也可以 ''' 然后我们在完成换位思考哦之后我们可以知道两个表都可以 那么这两个表就是"多对多"关系 而多对多关系的表 外键就是要重新创建一个表来存储 '''
2.3 一对一关系
''' 我们以用户表和用户详情表位列 用户表中有用户的基本信息 查询这些信息足以 用户详情表中就放着一些用户不怎么用的信息 但是必须有 ''' # 我们还是用换位思考 # 我们先站在用户表角度 问:一条用户数据能否对应多条用户详情数据 ? (就是在问一个用户能否拥有多条用户数据) 答:不可以 (一个用户只能拥有自己的数据 不能拥有其他用户数据) # 然后我们再站在用户详情表角度 问:一条用户详情数据能否对应多条用户数据? (就是在问一个用户详情数据能否被多个用户拥有) 答:肯定不能 一个用户详情数据肯定只有一个用户拥有 # 所以我们可以得知 两个表都不可以 首先我们先看两个表有没有关系 如果有那肯定就是 '一对一'关系 # 而一对一关系的主键最好创建在查询多的表中 再加上unique
三、代码实现
1.一对多
# 代码实现外键就需要关键字:foreign key ''' 1.我们先写普通字段 2.然后在写外键字段 ''' #################一对多#################### create table emp( id int primary key auto_increment, name varcahr(32), gender enum('male','female','others') default 'male', dep_id int, # 当所有的普通字段写完时 然后再写外键也可以 foreign key(dep_id) references dep(id) ); create table dep( dep_id int primary key auto_increment, dep_name varchar(32), dep_des varcahr(32) ); ''' 这样外键字段就写好了 1.创建表的时候需要先创建被关联的表(没有外键) 然后再是关联表(有外键) 2.插入表数据的时候 针对外键字段只能填写被关联表字段已经出现过的数据值 3.被关联字段无法修改和删除 有点不太好 操作限制性太强 怎么可以改了部门表的id 员工表的id也能修改? ''' # 这个时候我们就可以用到级联更新和级联修改 被关联的数据发生改变 那么关联数据同步改变 create table emp1( in int primary key auto_increment, name varchar(32), gender enum('male','female','others') default 'male', dep_id int, foreign key(dep_id) references dep1(id) on update cascade on delete cascade ); create table dep1( dep_id int primary key auto_increment, dep_name varchar(32), dep_desc varchar(32) ); # 这个时候被关联的表修改数据 那么关联的表就会同步修改 """ 扩展: 在实际工作中 很多时候可能并不会使用外键 因为外键增加了表之间的耦合度 不便于单独操作 资源消耗增加 我们为了能够描述出表数据的关系 又不想使用外键 自己通过写SQL 建立代码层面的关系 """
2.多对多
create table book( id int primary key auto_increment, name varcahr(32) ) create table author( id int primary key auto_increment, name varchar(32) ) # 我们需要开设第三张表来存储两个表的外键 create table book2author( id int primary key auto_increment, book_id int, foreign key(book_id) references book(id) on update cascade on delete cascade id int primary key auto_increment, author_id int, foreign key(author_id) references author(id) )
3.一对一
create table user( id int primary key auto_increment, name varchar(32), info_id int unique, # 一对一一定要在外键字段加上unique约束条件不加的话跟一对多就没区别了 foreign key(info_id) references userinfo(id) on update cascade on delete cascade ); create table userinfo( id int primary key auto_incerment, phon bigint );
四、作业
判断下列表数据关系 并自定义创建出表
ps:有些表数据关系不是确定 根据具体业务可能有变化
1.服务器表与应用程序表
2.课程表与班级表
3.学生表与班级表
4.老师表与课程表
5.书籍表与出版社表
1.服务器表与应用程序表
''' 首先一个服务器上可以有多个应用程序 然后一个应用程序只能在一个服务器上跑 所以这个两个表是一对多关系 ''' create table server( id int primary key auto_increment, name varcahr(32)
app_id int,
foreign key(app_id) references app(id)
on update cascade
on delete cascade
); create table app( id int primary key auto_increment, name varchar(32) );
2.课程表与班级表
''' 首先一个班级可以拥有多个课程 然后一个课程多个班级都可以上 所以这两个表也是多对多关系 ''' create table class( id int primary key auto_increment, name varchar(32) ); create table course( id int primary key auto_increment, name varchar(32) ); create table class2course( id int primary key auto_increment, class_id int, foreign key(class_id) references class(id) on update cascade on delete cascade, course_id int, foreign key(course_id) references course(id) on update cascade on delete cascade );
3.学生表与班级表
''' 首先一个班级可以有多个学生 而一个学生只能选择一个班级 所以两个表的关系是一对多关系 ''' create table class( id int primary key auto_increment, name varchar(32), student_id int, foreign key(student_id) references student(id) on update cascade on delete cascade ); create table student( id int primary key auto_increment, name varcahr(32) );
4.老师表与课程表
''' 一个老师可以教授多门课程 一个课程也可以被多门老师教授 所以是多对多关系 ''' create table teacher( id int primary key auto_increment, name varcahr(32) ); create table course( id int primary key auto_increment, name varcahr(32) ); create table teacher2course( id int primary key auto_increment, course_id int, foreign key(course_id) references course(id) on update cascade on delete cascade, teacher_id int, foreign key(teacher_id) references teacher(id) on update cascade on delete cascade );
5.书籍表与出版社表
''' 一个出版社可以出版多本书籍 而一本书籍只能是一个出版社出版 所以是一对多关系 ''' create table book( id int primary key auto_increment, name varchar(32), press_id int, foreign key(press_id) referenes press(id) on update cascade on delete cascade ); create table press( id int primary key auto_increment, name varcahr(32) );
浙公网安备 33010602011771号