MySQL之外键、表关系及SQL查询关键字

一、外键

假设现在我们有一张员工信息表，表的字段如下：

id # 主键
name # 姓名
age # 年龄
dep_name # 部门名称
dep_desc # 部门描述

单从数据存储上来看，这个表是没有问题的，但是从程序开发角度来看的话，这张表目前有如下三个缺陷：

要如何解决上述的三个缺陷呢？

其实我们可以将上述表拆分成两张表，一张员工表（emp），一张部门表（dep）：

emp表：
id name age

dep表：
id dep_name dep_desc

这样一来，上述的三个缺陷便全部解决了，但是你应该也发现了一个小问题，那就是这两张表之间的数据没有关系了，那又如何解决表与表之间的关系呢？

于是，MySQL给我们提供了外键:foreign key这种约束方式来帮我们解决上述问题，

通过建立外键字段来标识表与表之间的数据关系，也可以简单的理解为该字段可以让你去到其他表中查找数据。

下面就让我们来看看如何使用这种方式给表与表之间添加关系。

二、表与表之间建立关系

表与表之间的关系总共就三种，如下：

• 一对多
• 多对多
• 一对一

其实我们日常所能接触到的最常见的关系反而不是这三种，而是 没有关系 。

当然，没有关系 我们是比较容易判断的，那么对于上面三种关系，我们又该如何判断呢，这里我推荐一种判断表的方式，那就是换位思考，接下来就来看看这种方式在三种关系判断中是如何体现的。

2.1 一对多

以员工和部门表为例：

• 先站在员工表的基础之上：
  • 问：一个员工信息能否对应多个部门信息
  • 答：不可以
• 再站在部门表的基础之上：
  • 问：一个部门信息能否对应多个员工信息
  • 答：可以

结论：

一个可以，一个不可以，那么表关系就是一对多；

针对一对多 的表关系，外键字段建在多的一方；

表关系没有 多对一 一说，都是 一对多；

在上述例子中，员工表是多，部门表是一。

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 多对多

以书籍表和作者表为例：

• 先站在书籍表的基础之上：
  • 问：一个书籍信息能否对应多个作者信息
  • 答：可以
• 再站在作者表的基础之上：
  • 问：一个作者信息能否对应多个书籍信息
  • 答：可以

结论：

两个都可以，那么表关系就是多对多；

针对多对多表关系，需要单独开设第三张表存储两张表的关联数据（第三张表也可以不绑定关系）

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 一对一

以作者表和作者详情表为例：

• 先站在作者表的基础之上：
  • 问：一个作者信息能否对应多个作者详情信息
  • 答：不可以
• 再站在作者详情表的基础之上：
  • 问：一个作者详情信息能否对应多个作者信息
  • 答：不可以

结论：

两个都不可以，那么表关系可能是一对一或者没有关系，这个视具体情况应该就能判断出来；

针对一对一表关系，外键字段建在任何一方都可以，但是推荐建在查询频率较高的表中；

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 外键约束

• 在创建表的时候，需要先创建被关联表（没有外键字段的表）；

• 在插入新数据的时候，应该先确保被关联表中有数据；

• 在插入新数据的时候，外键字段只能填写被关联表中已经存在的数据；

• 在修改和删除被关联表中的数据的时候，无法直接操作，如果想要数据之间自动修改和删除，需要添加额外的配置，即上面所使用到的级联更新，级联删除：

  on update cascade # 级联更新
  on delete cascade # 级联删除

由于外键有实质性的诸多约束，当表特别多的时候外键的增多反而会增加耦合程度。

因此在实际开发项目中，有时候并不会直接使用外键创建表关系，而是通过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 <= 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); # 简写，类似于Python中的成员运算

\"\"\"3.查询员工姓名中包含o字母的员工姓名与其薪资\"\"\"
select name,salary from emp where name like \'%o%\';

\"\"\"4.查询员工姓名是由四个字符组成的员工姓名与其薪资\"\"\"
select name,salary from emp where name like \'____\';
select name,salary from emp where char_length(name) = 4;

\"\"\"5.查询id小于3或者大于6的数据\"\"\"
select * from emp where id not between 3 and 6; # 取反

\"\"\"6.查询薪资不在20000，18000，17000范围的数据\"\"\"
select * from emp where salary not in (20000,18000,17000);

\"\"\"7.查询岗位描述为空的员工名与岗位名（针对null不能用等号，只能用is）\"\"\"
select name,department_comment from emp where department_comment = NULL; # 不报错，但查询结果为空！
select name,department_comment from emp where department_comment is NULL;

补充知识点：

模糊查询：没有明确的筛选条件

关键字：like

关键符号：

• %：匹配任意个数任意字符
• _：匹配单个任意字符

示例：

show variables like \'%mode%\'; # 筛选出含有mode字符的结果

3.4 查询关键字之group by分组

即按照某个指定的条件将单个单个的个体分成 一个个整体。

eg：按照男女将人分组、按照年龄分组、按照肤色分组......

分组之后默认只能获取到分组的依据，其他数据不能直接获取。

针对5.6版本需要自己设置sql_mode:

set global sql_mode = \'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH\';

聚合函数：主要就是配合分组一起使用，这里列出五个常用的聚合函数：

max # 最大
min # 最小
sum # 求和
count # 计数
avg # 平均

数据分组所能应用到的场景：每个部门的平均薪资，男女比例等。

下面通过几道练习题来帮助我们体会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;

在显示的时候还可以给字段取别名：

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); # 内层查询无结果，所以外层查询语句不执行

来源：https://www.cnblogs.com/JZjuechen/p/15921023.html
图文来源于网络，如有侵权请联系删除。