第十三天、范式与关系，连表查询和子查询

空尽欢
503 阅读9分钟

一、范式与关系

假设有一个名为employee的员工表，它有九个属性：id(员工编号)、name(员工名称)、mobile(电话)、zip(邮编)、province(省份)、city(城市)、district(区县)、deptNo(所属部门编号)、deptName(所属部门名称)、表总数据如下：

idnamemobilezipprovincecitydistrictdeptNodeptName
101张三13910000001 13910000002100001北京北京海淀区D1部门1
101张三13910000001 13910000002100001北京北京海淀区D2部门2
102李四13910000003200001上海上海静安区D3部门3
103王五13910000004510001广东省广州白云区D4部门4
103王五13910000004510001广东省广州白云区D5部门 5

由于此员工表是非规范化的，我们将面对如下的问题。

  • 修改异常：上表中张三有两条记录，因为他隶属于两个部门。如果我们要修改张三的地址，必修修改两行记录。假如一个部门得到了张三的新地址并进行了更新，而另一个部门没有，那么此时张三在表中会存在两个不同的地址，导致了数据不一致
  • 新增异常： 假如一个新员工假如公司，他正处于入职培训阶段，还没有被正式分配到某个部门，如果deptNo字段不允许为空，我们就无法向employee表中新增该员工的数据。
  • 删除异常： 假设公司撤销了D3部门，那么在删除deptNo为D3的行时，会将李四的信息也一并删除。因为他隶属于D3这一部门。

第一范式(1NF)

表中的列只能含有原子性(不可再分)的值。

表中的张三有两个手机号存储在mobile列中，违反了 1NF 规则。为了使表满足 1NF，数据应该修改如下：

idnamemobilezipprovincecitydistrictdeptNodeptName
101张三13910000001100001北京北京海淀区D1部门1
101张三13910000002100001北京北京海淀区D1部门1
101张三13910000001100001北京北京海淀区D2部门2
101张三13910000002100001北京北京海淀区D2部门2
102李四13910000003200001上海上海静安区D3部门3
103王五13910000004510001广东省广州白云区D4部门4
103王五13910000004510001广东省广州白云区D5部门 5

第二范式(2NF)

第二范式要同时满足下面两个条件

  • 满足第一范式
  • 没有部分依赖

例如，员工表的一个候选键是{id，mobile，deptNo}，而deptName依赖于deptNo，同样 name 依赖于 id，因此不是 2NF的。为了满足第二范式的条件，需要将这个表拆分成employee、dept、employee_dept、employee_mobile四个表。如下：

员工表 employee

idnamezipprovincecitydistrict
101张三100001北京北京海淀区
102李四200001上海上海静安区
103王五510001广东省广州白云区

部门表 dept

deptNodeptName
D1部门1
D2部门2
D3部门3
D4部门4
D5部门5

员工部门关系表 employee_dept

iddeptNo
101D1
101D2
102D3
103D4
104D5

员工电话表 employee_mobile

idmobile
10113910000001
10113910000002
10213910000003
10313910000004

第三范式(3NF)

第三范式要同时满足下面两个条件

  • 满足第二范式
  • 没有传递依赖

例如，员工表的province、city、district依赖于zip，而zip依赖于id，换句话说，province、city、district传递依赖于id，违反了 3NF 规则。为了满足第三范式的条件，可以将这个表拆分成employee和zip两个表，如下

employee

idnamezip
101张三100001
102李四200001
103王五510001

地区表area

zipprovincecitydistrict
100001北京北京海淀区
200001上海上海静安区
51000广东省广州白云区

在关系数据库模型设计中，一般需要满足第三范式的要求。如果一个表具有良好的主外键设计，就应该是满足3NF的表。规范化带来的好处是通过减少数据冗余提高更新数据的效率，同时保证数据完整性。然而，我们在实际应用中也要防止过度规范化的问题。规范化程度越高，划分的表就越多，在查询数据时越有可能使用表连接操作。而如果连接的表过多，会影响查询性能。关键的问题是要依据业务需求，仔细权衡数据查询和数据更新关系，指定最合适的规范化程度。不要为了遵循严格的规范化规则而修改业务需求

数据库一对一、一对多、多对多设计

数据库实体间有三种对应关系：一对一、一对多、多对多

一对一关系示例：

一个学生对应一个学生档案材料 每个人都有唯一的身份证号

一对多关系示例：

一个学生只属于一个班，但这个班有多名学生

多对多关系示例：

一个学生可以选择多门课，一门课也可以有多名学生
一个人可以有多个角色，一个角色可以有多个人

关系

  • 创建成绩表scores，结构如下

    • id
    • 学生
    • 科目
    • 成绩

 

思考：学生列应该存什么信息呢？

答：学生列的数据不是在这里新建的，而应该从学生表引用过来，关系也是一条数据；根据范式要求应该存储学生的编号，而不是学生的姓名等其它信息

同理，科目表也是关系列，引用科目表中的数据

创建表的语句如下

create table scores(
id int primary key auto_increment,
stuid int,
subid int,
score decimal(5,2)
);

外键

思考：怎么保证关系列数据的有效性呢？任何整数都可以吗？ 答：必须是学生表中id列存在的数据，可以通过外键约束进行数据的有效性验证 为stuid添加外键约束

alter table scores add constraint stu_sco foreign key(stuid) references students(id);

此时插入或者修改数据时，如果stuid的值在students表中不存在则会报错
在创建表时可以直接创建约束

create table scores(
id int primary key auto_increment,
stuid int,
subid int,
score decimal(5,2),
foreign key(stuid) references students(id),
foreign key(subid) references subjects(id)
);

注：外键的添加会比较消耗性能

外键的级联操作

  • 在删除students表的数据时，如果这个id值在scores中已经存在，则会抛异常
  • 推荐使用逻辑删除，还可以解决这个问题
  • 可以创建表时指定级联操作，也可以在创建表后再修改外键的级联操作
  • 语法
alter table scores add constraint stu_sco foreign key(stuid) references students(id) on delete cascade;

级联操作的类型包括：

  • restrict（限制）：默认值，抛异常（常用）
  • cascade（级联）：如果主表的记录删掉，则从表中相关联的记录都将被删除（一般不使用）
  • set null：将外键设置为空（一般不使用）
  • no action：什么都不做（一般不使用）

二、连表查询

  • 连接查询分类如下：

    • 表A inner join 表B：表A与表B匹配的行会出现在结果中
    • 表A left join 表B：表A与表B匹配的行会出现在结果中，外加表A中独有的数据，未对应的数据使用null填充
    • 表A right join 表B：表A与表B匹配的行会出现在结果中，外加表B中独有的数据，未对应的数据使用null填充

  • 在查询或条件中推荐使用“表名.列名”的语法

  • 如果多个表中列名不重复可以省略“表名.”部分

  • 如果表的名称太长，可以在表名后面使用' as 简写名'或' 简写名'，为表起个临时的简写名称

练习

  • 查询学生的姓名、平均分
select students.sname,avg(scores.score)
from scores
inner join students on scores.stuid=students.id
group by students.sname;
  • 查询男生的姓名、总分
select students.sname,avg(scores.score)
from scores
inner join students on scores.stuid=students.id
where students.gender=1
group by students.sname;
  • 查询科目的名称、平均分
select subjects.stitle,avg(scores.score)
from scores
inner join subjects on scores.subid=subjects.id
group by subjects.stitle;
  • 查询未删除科目的名称、最高分、平均分
select subjects.stitle,avg(scores.score),max(scores.score)
from scores
inner join subjects on scores.subid=subjects.id
where subjects.isdelete=0
group by subjects.stitle;

三、子查询

  • 查询支持嵌套使用
  • 查询各学生的语文、数学、英语的成绩

什么是子查询

当一个查询是另一个查询的条件时,这个查询称之为子查询(内层查询)

什么时候用？

当查询需求比较复杂，一次性查询无法得到结果，需要多次查询时，

例如：给出一个部门名称，需要获得该部门所有的员工信息

分析：

1.需要先确定部门的id

2.然后才能通过id确定员工

解决问题的方式是把一个复杂的问题拆分为若干个简单的问题

2. 如何使用？

首先明确子查询就是一个普通的查询,当一个查询需要作为子查询使用时,用括号包裹即可

3. 需要注意

in中的子查询只能包含一个列

例如：查询财务部有哪些人

正确的写法：select name from emp where dept_id in (select id from dept where name = "财务");

错误的写法：select name from emp where dept_id in (select * from dept where name = "财务");

关键字：exists

exists后跟子查询，子查询有结果是为True，没有结果时为False。为True时外层执行，为False外层不执行

如何使用？

select from emp where exists (select from emp where salary > 1000);

前面 exists 后面 如果 后面 查询有结果时，前面 才会执行

今天特别累！

日期：2021/11/25

学习参考视频：*www.bilibili.com/video/BV1i7…

学习参考文档参考部分相关视频文案和课件，仅供个人学习和记录

avatar
文章
阅读
粉丝