一、外键
假设我们现在有一个员工信息表，其中包含以下字段：
[En]
Suppose we now have an employee information table with the following fields:
id # 主键
name # 姓名
age # 年龄
dep_name # 部门名称
dep_desc # 部门描述
仅就数据存储而言，这个表是可以的，但从程序开发的角度来看，这个表目前存在以下三个缺陷：
[En]
In terms of data storage alone, this table is fine, but from a program development point of view, this table currently has the following three defects:
- 表的重点不清晰：到底是员工表还是部门表（其实可以忽略）
- 表中字段会出现重复存储，如数据量较大，那么会存储多个相同部门的名称和描述（其实也可以忽略）
- 表的扩展性极差，如要修改某个部门名称信息，就会导致牵一发而动全身
（这一点不能忽略）
如何解决上述三个缺陷？
[En]
How to solve the above three defects?
其实我们可以将上述表拆分成两张表，一张员工表（emp），一张部门表（dep）：
emp表：
id name age
dep表：
id dep_name dep_desc
如此一来，上述三个缺陷都得到了解决，但也要发现一个小问题，那就是两个表之间的数据没有关系，那么如何解决表之间的关系呢？
[En]
In this way, all the above three defects have been solved, but you should also find a small problem, that is, there is no relationship between the data between the two tables, so how to solve the relationship between tables?
于是，MySQL给我们提供了 外键:foreign key这种约束方式来帮我们解决上述问题，
通过建立外键字段来标识表和表之间的数据关系，还可以简单地理解为该字段允许您在其他表中查找数据。
[En]
By establishing a foreign key field to identify the data relationship between the table and the table, it can also be simply understood that the field allows you to look for data in other tables.
让我们来看看如何以这种方式添加表之间的关系。
[En]
Let’s take a look at how to add relationships between tables in this way.
二、表与表之间建立关系
表之间有三种关系，如下所示：
[En]
There are three relationships between tables, as follows:
一对多多对多- *
一对一
其实我们日常所能接触到的最常见的关系反而不是这三种，而是 没有关系 。
当然， 没有关系 我们是比较容易判断的，那么对于上面三种关系，我们又该如何判断呢，这里我推荐一种判断表的方式，那就是 换位思考，接下来就来看看这种方式在三种关系判断中是如何体现的。
2.1 一对多
以员工和部门表为例：
- 以工作人员表为基础：
[En]
stand on the basis of the staff table:*
- 问：一个员工信息能否对应多个部门信息
- 答：不可以
- 站在部门表的基础上：
[En]
stand on the basis of the department table:*
- 问：一个部门信息能否对应多个员工信息
- 答：可以
结论：
一个可以，一个不可以，那么表关系就是
一对多；
针对一对多的表关系，外键字段建在多的一方；
表关系没有多对一一说，都是一对多；
在上面的示例中，Employee表是多个，而Department表是一个。
[En]
In the above example, the employee table is many and the department table is one.
SQL语句实现如下：
"""先创建不含外键字段的基本表，之后再添加外键字段"""
"""部门表"""
create table dep(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(254)
);
"""员工表"""
create table emp(
id int primary key auto_increment,
name varchar(32),
age int,
dep_id int,
foreign key(dep_id) references dep(id)
)
2.2 多对多
以书籍表和作者表为例：
- 以书单为依据：
[En]
stand on the basis of the list of books:*
- 问：一个书籍信息能否对应多个作者信息
- 答：可以
- 站在作者表的基础上：
[En]
stand on the basis of the author’s table:*
- 问：一个作者信息能否对应多个书籍信息
- 答：可以
结论：
两个都可以，那么表关系就是
多对多；
针对多对多表关系，需要单独开设第三张表存储两张表的关联数据（第三张表也可以不绑定关系）
SQL语句实现如下：
"""书籍表"""
create table book(
id int primary key auto_increment,
title varchar(32),
price float(6,2)
);
"""作者表"""
create table author(
id int primary key auto_increment,
name varchar(32),
age int
);
"""第三张关系表"""
create table book2author(
id int primary key auto_increment,
book_id int,
author_id int,
foreign key(book_id) references book(id)
on update cascade # 级联更新
on delete cascade, # 级联删除
foreign key(author_id) references author(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
2.3 一对一
以作者表和作者明细表为例：
[En]
Take the author table and the author details table as an example:
- 站在作者表的基础上：
[En]
stand on the basis of the author’s table:*
- 问：一个作者信息能否对应多个作者详情信息
- 答：不可以
- 以作者明细表为依据：
[En]
stand on the basis of the author’s details table:*
- 问：一个作者详情信息能否对应多个作者信息
- 答：不可以
结论：
两个都不可以，那么表关系可能是
一对一或者没有关系，这个视具体情况应该就能判断出来；
针对一对一表关系，外键字段建在任何一方都可以，但是推荐建在查询频率较高的表中；
SQL语句实现如下：
"""作者详情表"""
create table author_detail(
id int primary key auto_increment,
phone bigint,
address varchar(32)
);
"""作者表"""
create table author(
id int primary key auto_increment,
name varchar(32),
age int,
author_id int unique, # 要加上唯一约束条件，这是与一对多表关系唯一不同的地方
foreign key(author_id) references author_detail(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
2.4 外键约束
- 创建表时，需要创建关联表(不带外键字段的表)
[En]
when creating a table, you need to create an associated table (a table without foreign key fields)*
- 插入新数据时，应首先确保关联表中有数据
[En]
when inserting new data, you should first ensure that there is data in the associated table*
- 插入新数据时，外键字段只能填写关联表中已有的数据
[En]
when inserting new data, the foreign key field can only fill in the data that already exists in the associated table*
- 您不能直接修改和删除关联表中的数据。如果您想要自动修改和删除数据，则需要添加额外的配置，即上面使用的级联更新和级联删除：
[En]
you cannot directly modify and delete the data in the associated table. If you want to automatically modify and delete the data, you need to add additional configuration, that is, cascading updates and cascading deletions used above:*
on update cascade # 级联更新on delete cascade # 级联删除由于外键的实质性约束，当表很多时，外键的增加会增加耦合度。
[En]
Because of the substantial constraints of foreign keys, the increase of foreign keys will increase the degree of coupling when there are a lot of tables.
因此在实际开发项目中，有时候并不会直接使用外键创建表关系，而是通过SQL语句层面，建立逻辑意义上的表关系。
eg：操作员工表的SQL语句执行完毕之后，立刻跟着执行操作部门的SQL语句。
2.5 操作表的SQL语句补充
"""修改表名"""
ALTER TABLE 表名 RENAME 新表名;
"""增加字段"""
ALTER TABLE 表名 ADD 字段名 数据类型 [约束条件...],...;
ALTER TABLE 表名 ADD 字段名 数据类型 [约束条件...] FIRST;
ALTER TABLE 表名 ADD 字段名 数据类型 [约束条件...] AFTER 字段名;
"""删除字段"""
ALTER TABLE 表名 DROP 字段名;
"""修改字段"""
ALTER TABLE 表名 MODIFY 字段名 数据类型 [约束条件...];
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型 [约束条件...];
modify只能改字段数据类型和约束条件，不能改字段名，但是change可以！
三、SQL查询关键字
3.1 数据准备
"""数据准备"""
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, # 录用日期
department varchar(50), # 部门名称
department_comment varchar(100), # 岗位名称
salary double(15,2), # 薪资
office int, #办公室（同一部门在一件办公室）
depart_id int # 部门编号
);
"""插入记录"""
"""三个部门：教学，销售，运营"""
insert into emp(name,sex,age,hire_date,department,salary,office,depart_id) values
('jason','male',18,'20170301','teacher',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);
3.2 查询关键字之 select 与 from
- select 控制的是查询表里面的哪些字段；
select * from emp; # 查询所有字段
select name,age from emp; # 查询指定字段
- from控制的是查询哪张表；
3.3 查询关键字之 where 筛选
下面通过几道练习题来帮助我们体会 where关键字的用法及其他补充知识点：
"""1.查询id大于等于3小于等于6的数据"""
select * from emp where id >= 3 and id
补充知识点：
模糊查询：没有明确的过滤条件
[En]
Fuzzy query: there are no clear filter criteria
关键字： like
关键符号：
%：匹配任意个数任意字符_：匹配单个任意字符
示例：
show variables like '%mode%'; # 筛选出含有mode字符的结果
3.4 查询关键字之 group by 分组
也就是说，根据特定的条件将单个个体划分为一个整体。
[En]
That is, a single individual is divided into a whole according to a specified condition.
eg：按照男女将人分组、按照年龄分组、按照肤色分组……
分组之后默认只能获取到分组的依据，其他数据不能直接获取。
针对5.6版本需要自己设置sql_mode:
set global sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';
聚合函数：主要与分组一起使用。以下是五个常用的聚合函数：
[En]
Aggregate function: it is mainly used with grouping. Here are five commonly used aggregate functions:
max # 最大
min # 最小
sum # 求和
count # 计数
avg # 平均
可以应用数据分组的场景：每个部门的平均工资、性别比例等。
[En]
Scenarios to which data grouping can be applied: average salary in each department, gender ratio, etc.
下面通过几道练习题来帮助我们体会 group by关键字的用法：
"""1.按部门分组"""
select * from emp group by department; # 没设置sql_mode之前分组后取出的是每个组的第一条数据
select id,name,sex from emp group by department; # 验证
设置 sql_mode为 only_full_group_by后，意味着以后但凡分组，只能取到分组的依据，不应该再去取组里面的单个元素的值，那样的话分组就没有意义了，因为不分组就是对单个元素信息的随意获取。
set global sql_mode = "strict_trans_tables,only_full_group_by";
重新连接客户端
select * from emp group by department; # 报错
select id,name,sex from emp group by department; # 报错
select department from emp group by department; # 获取部门信息
强调:只要分了组，就不能够再”直接”查找到单个数据信息了，只能获取到组名
"""2.获取每个部门的最高工资"""
以组为单位统计组内数据>>>>聚合查询(聚集到一起合成为一个结果)
select department,max(salary) from emp group by department;
您还可以在显示字段时为其设置别名：
[En]
You can also alias the field when it is displayed:
select department as '部门',max(salary) as '最高工资' from emp group by department;
as也可以省略，但是不推荐，因为语义不明确。
"""每个部门的最低工资"""
select department,min(salary) from emp group by department;
"""每个部门的平均工资"""
select department,avg(salary) from emp group by department;
"""每个部门的工资总和"""
select department,sum(salary) from emp group by department;
"""每个部门的人数"""
select department,count(id) from emp group by department; # 统计的时候只要是非空字段,效果都是一致的
"""演示特殊情况:department_comment"""
select department,count(department_comment) from emp group by department;
补充说明：
group_concat：分组之后使用 如果真的需要获取分组以外的数据字段，可以使用group_concat()
"""每个部门的员工姓名"""
select department,group_concat(name) from emp group by department;
"""每个部门的员工姓名,性别（支持自定义连接符）"""
select department,group_concat(name,'|',sex) from emp group by department;
concat：未分组时使用
select concat(name,sex) from emp;
select concat(name,'|',sex) from emp;
3.5 查询关键字之 having 过滤
where与 having都是筛选功能，但是有区别：
where是在分组之前对数据进行筛选having是在分组之后对数据进行筛选
"""筛选出员工年龄在30岁以上，且平均薪资在10000以上的部门"""
select department,avg(salary) from emp where age>30 group by department having avg(salary)>10000;
注意：
一条SQL语句的结果也可以看成是一张全新的表。
3.6 查询关键字之 distinct 去重
"""对有重复的展示数据进行去重操作 一定要是重复的数据"""
select distinct department from emp; # 去重单个字段
select distinct id,age from emp; # 如去重多个字段必须满足去重的字段数据都重复才能去重，否则不去重
3.7 查询关键字之 order by 排序
select * from emp order by salary asc; # 也可不写asc，默认升序排
select * from emp order by salary desc; # 降序排
"""先按照age降序排，在年龄相同的情况下再按照薪资升序排"""
select * from emp order by age desc,salary asc;
"""统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行降序排序"""
select department,avg(salary) from emp where age>10 group by department having avg(salary)>1000 order by avg(salary) desc;
3.8 查询关键字之 limit 分页
"""限制展示条数"""
select * from emp limit 3;
"""查询工资最高的人的详细信息"""
select * from emp order by salary desc limit 1;
"""分页显示"""
select * from emp limit 0,5; # 第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置
select * from emp limit 5,5;
3.9 查询关键字之 regexp 正则
select * from emp where name regexp '^j.*(n|y)$';
3.10 查询关键字之 exists 存在
exists关键字表示是否存在。
在使用 exists关键字时，内层查询语句不返回查询的记录，而是返回一个真假值， True或 False。
- 当返回
True时，外层查询语句将进行查询； - 当返回值为
False时，外层查询语句不进行查询。
select id,name from emp where exists (select id from emp where id > 3); # 有结果
select id,name from emp where exists (select id from emp where id > 200); # 内层查询无结果，所以外层查询语句不执行
Original: https://www.cnblogs.com/JZjuechen/p/15921023.html
Author: JZEason
Title: MySQL之外键、表关系及SQL查询关键字
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/507810/
转载文章受原作者版权保护。转载请注明原作者出处！