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

一、外键

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

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

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

  1. 表的重点不清晰:到底是员工表还是部门表(其实可以忽略)
  2. 表中字段会出现重复存储,如数据量较大,那么会存储多个相同部门的名称和描述(其实也可以忽略)
  3. 表的扩展性极差,如要修改某个部门名称信息,就会导致牵一发而动全身(这一点不能忽略)

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

其实我们可以将上述表拆分成两张表,一张员工表(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);

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

3.2 查询关键字之selectfrom

  • 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_modeonly_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过滤

wherehaving都是筛选功能,但是有区别:

  • 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)$';
上一篇:第一个Mybatis程序


下一篇:DataFlux:实时数据应用的新选择——企业级云原生数据库介绍