MySQL外键
外键简介
# 外键（foreign key）的概念
'用来记录表与表之间的关系'
是用于建立和加强两个表数据之间的链接的一列或多列。外键表示一个表中的一个字段被另一个表中的一个字段引用。外键对相关表中的数据造成了限制，使MySQL能够保持参照完整性
# 外键的作用
外键约束主要用来维护两个表之间数据的一致性。简言之，表的外键就是另一表的主键，外键将两表联系起来。一般情况下，要删除一张表中的主键必须首先要确保其它表中的没有相同外键（即该表中的主键没有一个外键和它相关联）
外键案例
定义一个员工表
# 员工表信息
id name age dep_name dep_desc
1 jason 18 外交部 外交
2 kevin 28 教学部 教书
3 tony 38 教学部 教书
4 oscar 48 安保部 保安
# 缺陷
1.表的重点不清晰(可以忽略)
既可以说是员工表也可以说是部门表
2.表中某些字段对应的数据一直在重复(可以忽略)
浪费存储空间
3.表的扩展性极差 牵一发而动全身(不能忽略)
耦合度太高 不利于维护
拆分员工表
# 员工表
id name age
1 jason 18
2 kevin 28
3 tony 38
4 oscar 48
# 部门表
id dep_name dep_desc
1 外交部 外交
2 教学部 教书
3 安保部 保安
4 财务部 发工资
# 拆表之后上述的三个缺陷解决
但是迎来了一个致命问题 >>> 员工与部门之间没有了绑定关系
在员工表中添加一个部门编号字段与部门表中的主键字段对应
"""
该字段其实就是外键字段
外键字段就是用来记录表与表之间数据的关系
"""
外键关系
# 总共四种关系
1.一对多
2.多对多
3.一对一
4.没有关系
"""
关系的判断记住四字口诀:换位思考
"""
# 一对多
1.以员工表与部门表为例
先站在员工表的角度
问:一个员工能否对应多个部门
答:不可以
再站在部门表的角度
问:一个部门能否对应多个员工
答:可以
结论:换位思考之后得出的答案是一个可以一个不可以
所以关系是"一对多" 部门是'一'员工是'多'
'''关系表达只能用一对多 不能用多对一'''
一对多关系 外键字段建在"多"的一方(员工表)
一对多关系
删除或修改被关联表问题
如果要修改或者删除被关联表的字段的话，那么关联表的字段也要同样被修改或删除才合理
'该方法提供处理 被关联字段的删除与修改'
关键字(级联更新 级联删除):
on update cascade
on delete cascade
'on updata cascade与on delete cascade不要加逗号因同属于一条命令'
外键的约束效果
# 在创建表的时候需要先创建被关联表(没有外键的表)
因为没有被关联表的话 无法识别到被关联表的时候就会导致报错
# 在写入数据的时候也需要先写入被关联表(没有外键的表)
在存储数据的时候，没有被关联表的外键字段，关联表也无法关联，会导致报错
# 被关联表里面的数据无法直接删除和修改关联字段的操作
如果被关联表被删除了，那么关联表的外键字段就不知道指向谁了，会导致数据混乱
创建员工表和部门表
create table emp(
id int primary key auto_increment comment '编号',
name varchar(50) comment '姓名',
age int comment '年龄',
dep_id int comment '部门编号',
foreign key(dep_id) references dep(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
create table dep(
id int primary key auto_increment comment '编号',
dep_name varchar(50) comment '部门名称',
dep_desc varchar(50) comment '部门描述'
);
"""
在实际工作中外键也可能不会使用 因为外键会消耗额外的资源
并且会增加表的复杂度
表很多的情况下 我们也可以通过SQL语句的形式建立逻辑意义上的表关系
"""
插入数据
# 被关联表(先写入)
insert into dep(dep_name,dep_desc) values ('外交部','外交'),('教学部','教学'),('保安部','保安'),('财务部','发工资');
# 关联表
insert into emp(name,age,dep_id) values('jason',18,1),('kevin',28,2),('tony',38,3),('tom',48,4);
多对多关系
#以图书与作者表为例
1.先站在图书表的角度
问:一本书籍能否对应多名作者
答:可以
2.再站在作者表的角度
问:一名作者能否对应多本书籍
答:可以
'结论:换位思考之后两边都可以 那么就是"多对多"关系'
错误的创建表方式
# 创建图书表
create table book(
id int primary key auto_increment,
title varchar(32),
price float(10,2),
author_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade
);
# 创建作者表
create table author(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female','others'),
book_id int,
foreign key(book_id) references book(id)
on update cascade
on delete cascade
);
'''
ERROR 1215 (HY000): Cannot add foreign key constraint
以上为错误的方式:
两张表都是关联表，无法进行关联，所以报错
'''
正确的创建表方式(第三方关联表)
# 被关联表---图书表
create table book(
id int primary key auto_increment,
title varchar(32),
price float(10,2)
);
# 被关联表---作者表
create table author(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female','others')
);
# 第三方关联表
create table book_author(
id int primary key auto_increment,
book_id int,
foreign key(book_id) references book(id)
on update cascade
on delete cascade,
author_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade
);
"""
针对多对多关系 需要单独开设第三张表专门存储关系
"""
插入数据
# 图书表
insert into book(title,price) values ('python',100),('java',200),('linux',300);
# 作者表
insert into author(name,gender) values('jason1','male'),('jason2','female'),('jason3','male'),('jason4','others');
# 第三方关联表
insert into book_author(book_id,author_id) values (1,1),(2,1);
insert into book_author(book_id,author_id) values (2,2),(2,3);
一对一关系
# 举例说明：
针对qq用户表 其实里面的数据可以分成两类
热数据:经常需要使用的数据
eg:qq号码 座右铭 个人简介 爱好
冷数据:不怎么经常需要使用的数据
eg:邮箱 电话 学校 ...
'''
为了节省资源并降低数据库压力 会将表一分为二
用户表
存使用频率较高的数据字段
用户详情表
存使用频率较低的数据字段
'''
1.先站在用户表的角度
问:一个用户数据能否对应多个用户详情数据
答:不可以
2.再站在用户详情表的角度
问:一个用户详情数据能否对应多个用户数据
答:不可以
# 结论:
换位思考之后两边都不可以 那么关系可能有两种
1.没有关系:很好判断
2.一对一关系
'''
针对'一对一关系'外键字段建在任意一方都可以,但是推荐建在查询频率较高的较好的一方
'''
创建个人信息表
# 被关联表---基本信息表
create table User(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female','others'),
user_detail_id int unique,
foreign key(user_detail_id) references UserDetail(id)
on update cascade
on delete cascade
);
# 唯一关联表---详细信息
create table UserDetail(
id int primary key auto_increment,
phone bigint,
age int
);
插入数据
# 用户详细信息
insert into UserDetail(phone,age) values('1561651','18');
insert into UserDetail(phone,age) values('257751','28');
# 用户基本信息
insert into User(name,gender,user_detail_id) values('jason1','male',1),('jason2','female',2);
表查询关键字
前期准备
建表
create table emp(
id int primary key auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null, # 雇佣日期
post varchar(50), # 职业
post_comment varchar(100), # 员工信息
salary double(15,2), # 工资
office int, #一个部门一个屋子
depart_id int # 编号
);
插入数据
#三个部门：教学，销售，运营
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','张江第一帅形象代言',7300.33,401,1), #以下是教学部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);
查询关键字之select from
# select
控制查询表中的哪些字段对应的数据
# from
控制查询的表
# 代码示例：
select * from emp; # 查询emp表的全部信息
select id,name from emp; # 查询emp表的id和name信息
查询关键字之 where 筛选
# 1.查询id大于等于3 小于等于6的数据
select * from emp where id>=3 and id<=6;
select * from emp where id between 3 and 6;
# 2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary=20000 or salary=18000 or salary=17000;
select * from emp where salary in (20000,18000,17000);
# 3.查询id小于3或者大于6的数据
select * from emp where id not between 3 and 6;
# 4.查询薪资不在20000,18000,17000的数据
select * from emp where salary not in (20000,18000,17000);
# 5.查询岗位描述为空的数据
select * from emp where post_comment=null; # 不可以
'''针对null只能用is不能用等号'''
select * from emp where post_comment is null; # 可以
# 6.查询员工姓名中包含字母o的员工姓名和薪资
"""
查询条件如果不是很明确的情况下 我们统一称之为'模糊查询'
关键字
like:开启模糊查询的关键字
关键符号
%:匹配任意个数的任意字符
_:匹配单个个数的任意字符
"""
select name,salary from emp where name like '%o%';
# 7.查询员工姓名是由四个字符组成的数据
select * from emp where name like '____';
select * from emp where char_length(name)=4;
查询关键字之group by分组
# 分组的概念
按照指定的条件将单个单个的个体组织成一个个整体
eg:按照性别分组 按照部门分组 按照年龄分组 按照国家分组...
# 分组的应用场景
分组的好处在于可以快速统计出某些数据
eg:最大薪资 平均年龄 最小年龄 总人数
# 如何分组
关键字 group by
'''按照部分分组'''
select * from emp group by post;
实现分组
分组之后不再以单个个体为研究对象 也无法直接再获取单个个体的数据
研究对象应该是分组的整体
解析:
分组之后获取是(部门整体)而不是(个体)
分组之后默认只能直接获取到分组的依据 其他字段数据无法直接获取
解析:
使用(post/部门)进行分组的，使用select只能以post来做分组
#mysql5.7及以上版本
默认自带sql_mode=only_full_group_by
该模式要求分组之后默认只可以直接获取分组的依据不能直接获取其他字段
'''原因是分组的目的就是按照分组的条件来管理诸多数据 最小单位应该是分组的依据而不是单个单个的数据'''
#MySQL5.6及以下版本
需要自己手动添加
set global sql_mode='only_full_group_by';
修改完后重新登录MySQL
exit
聚合函数
# 专门用于分组之后的数据统计
max 统计最大值
min 统计最小值
sum 统计求和
count 统计计数
avg 统计平均值
分组案例练习
# 1.统计每个部门的最高薪资
select post,max(salary) from emp group by post;
# 2.统计每个部门的平均薪资
select post,avg(salary) from emp group by post;
# 3.统计每个部门的员工人数
select post,count(id) from emp group by post;
# 4.统计每个部门的月工资开销
select post,sum(salary) from emp group by post;
# 5.统计每个部门最小的年龄数
select post,min(age) from emp group by post;
"""间接获取分组以外其他字段的数据"""
# 统计每个部门下所有员工的姓名
select post,group_concat(name) from emp group by post;
# 统计每个部门下所有员工的姓名和年龄
select post,group_concat(name,age) from emp group by post;
select post,group_concat(name,'|',age) from emp group by post;
"""字段起别名"""
select post,group_concat(name) as '姓名' from emp group by post;
select id as '序号',name as '姓名' from emp;
# as关键字也可以不写 但是语义不明确 建议加上
sele2
浙公网安备 33010602011771号