- 约束条件之外键 - 查询关键字
2022.5.5 约束条件之外键及查询关键字
- 约束条件之外键
- 查询关键字
一、约束条件之外键
1、引入
# 创建一张员工表
id name age dep_name dep_desc
缺陷>>>:
1.表重点不清晰，可以说是员工表也可以是部门表
2.表中某些字段对应数据重复，浪费存储空间
3.表的扩展性差，牵一发动全身，耦合度太高不利维护
解决>>>:
将这张表分成两张表：
员工表>>>:
id name age
部门表>>>:
id dep_name dep_desc
解决后缺陷>>>:
致命缺陷：员工与部门没有绑定关系
再次优化>>>:
在员工表中添加一个‘部门编号’字段dep_id，与部门表的主键字段对应
# 引入知识
添加的部门编号字段其实就是外键字段>>>:
记录表与表之间关系的数据2、外键关系（4种）
- 一对多
- 多对多
- 一对一
- 无关系
判断表关系四字口诀>>>: 换位思考
（1）一对多
# 换位思考
以员工表与部门表为例
1.先站在员工表的角度
问:一个员工能否对应多个部门
答:不可以
2.再站在部门表的角度
问:一个部门能否对应多个员工
答:可以
结论:换位思考之后得出的答案是一个可以一个不可以
（1）所以关系是"一对多"，部门是'一'员工是'多'
（2）关系表达只能用一对多，不能用多对一
（3）一对多关系，外键字段建在"多"的一方(员工表)
# 外键sql语句
foreign key(dep_id) references dep(id)
（1）创建表的时候，应该先创建被关联表(没有外键字段的表)
（2）插入数据的时候，应该先插入被关联表(没有外键字段的表)；因为外键字段填入的值只能是被关联表中已经存在的主键值
问题：修改、删除被关联表数据都会出现障碍，且报错
解决办法>>>: 级联更新、级联删除
# 级联更新、级联删除
on update cascade # 级联更新，外键关联的表主键更新，另一个表对应数据也更新
on delete cascade # 级联删除，外键关联的表主键删除，另一个表对应数据删除
# 添加外键sql语句总结：
create table 员工表(
id int;
name vrchar(32);
...
foreign key(部门表_id) references 部门表(id) on update cascade on delete cascade # 外键+级联更新、删除
)
"""
在实际工作中外键也可能不会使用 因为外键会消耗额外的资源
并且会增加表的复杂度
表很多的情况下 我们也可以通过SQL语句的形式建立逻辑意义上的表关系
"""一对一关系表>>>外键sql语句代码操作：
# 在创建表字段的时候也可以给每个字段添加中文注释>>> comment '注释内容'
员工表>>>:
create table emp(
id int primary key auto_increment comment '编号',
name varchar(32) 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(32) comment '部门名称',
dep_desc varchar(32) comment '部门描述'
);这样，只要部门表主键修改或者删除某行数据，员工表对应数据也会随之修改和删除；
关系如下图：
（2）多对多关系
以图书表和作者表为例
# 换位思考
1.先站在图书表的角度
问:一本书籍能否对应多名作者
答:可以
2.再站在作者表的角度
问:一名作者能否对应多本书籍
答:可以
结论:换位思考之后两边都可以 那么就是"多对多"关系那么思考以下，针对多对多关系的表，是否可以将两者的外键都设置成对方的主键呢，这样不就可以关联了？
但是这样有个致命的问题，修改或者删除其中一个表的主键、数据，另一个表的数据也会随之修改、删除，那么就没办法实现多对多的效果了，那么应该怎么办呢？
# 针对多对多关系，需要单独开设第三张表专门存储两张表的关系，这两张表叫“基表”
图书表>>>:
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 book2author(
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 # 级联删除
);关系图如下：
（3）一对一关系
# 数据分析
1.以QQ用户表为例，分析里面的数据分为两类>>>:
热数据：经常使用的数据 >>> qq号码、座右铭、个人简介、爱好等
冷数据：不经常使用的数据 >>> 邮箱、电话、学校等
2.为节省资源并降低数据库压力，将表一分为二>>>:
用户表：存放使用频率较高的数据字段
用户详情表：存放使用频率较低的数据字段
# 换位思考
先站在用户表的角度
问:一个用户数据能否对应多个用户详情数据
答:不可以
再站在用户详情表的角度
问:一个用户详情数据能否对应多个用户数据
答:不可以
结论:换位思考之后两边都不可以，那么关系可能有两种
（1）'没有关系'
（2）'一对一关系'
针对'一对一关系'外键字段建在任意一方都可以,但是推荐建在查询频率较高的一方
# sql语句代码
用户表>>>:
create table user(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female','others'),
user_detail_id int unique, # 加unique是因为数据是一对一的，要保证不能重复
foreign key(user_detail_id) reference userdetail(id) on update cascade on delete cascade
)
用户详情表>>>:
create table userdetail(
id int primary key auto_increment,
phone bigint,
age int
);关系如下图：
这样，针对用户基本数据及用户的详细信息的数据就分好了；
二、表查询关键字
先创建一个表格便于操作关键字:
# 数据准备
创建员工表>>>:
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, # 年龄 非空 默认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);1、查询关键字之select与from
select # 用于指定查询的字段
from # 用于指定查询的表
select id,name from mysql.user; # 查询user中的id和name字段
'''查询关键字其实有先后执行顺序 但是无需过多在意!!!'''2、查询关键字之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; # char_length获取字符长度也可以3、查询关键字之group by分组
（1）什么是分组？
按照指定的条件将单个单个的个体组织成一个个整体
eg：按照性别分组 按照部门分组 按照年龄分组 按照国家分组...
（2）为什么需要分组？
分组的好处在于可以快速统计出某些数据
eg：最大薪资、平均年龄、最小年龄、总人数等
（3）如何分组？
按照部分分组
# 按照岗位分组并查询所有数据
select * from emp group by post;
如果是MySQL5.6及以下版本>>>:
有几个组就显示几行数据，且会显示其他字段的信息，但是只显示一条，每条数据默认显示该组的第一条数据
mysql5.7及以上版本>>>:
默认自带sql_mode=only_full_group_by
输入上面代码就会报错！
因为该模式要求分组之后默认只可以直接获取分组的依据，不能直接获取其他字段；
原因是分组的目的就是按照分组的条件来管理诸多数据 最小单位应该是分组的依据而不是单个单个的数据；
select post from emp group by post; # 这样才不会报错，且展示的数据仅限分组后的post的数据（单个不重复）
# 结论：如果是MySQL5.6及以下版本,需要自己手动添加>>>: sql_mode=only_full_group_by（4）聚合函数
专门用于分组之后的数据统计
# 聚合函数分类
max 统计最大值
min 统计最小值
sum 统计求和
count 统计计数
avg 统计平均值
函数括号里是字段类型，用以筛选分组后或者未分组的数据；
ps:是否需要分组，我们可以在题目或者需求中发现；
# 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;
"""间接获取分组以外其他字段的数据"""
group_concat(字段类型)，
（1）可以展示分组后的其他某字段的所有数据集中展示；
（2）括号里可以对字段展示的格式进行拼接字符串格式化输出
# 1.统计每个部门下所有员工的姓名
select post,group_concat(name) from emp group by post;
# 2.统计每个部门下所有员工的姓名和年龄
select post,group_concat(name,age) from emp group by post; # 获取按照post分组后的所有name和age
select post,group_concat(name,'|',age) from emp group by post; # 拼接字符串后展示（5）字段起别名
select post,group_concat(name) as '姓名' from emp group by post; # as加别名
select id as '序号',name as '姓名' from emp;
# as关键字也可以不写 但是语义不明确 建议加上
select id '序号',name '姓名' from emp;
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
