MySQL:表的设计原则和聚合函数

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 本文详细介绍了数据库表设计的原则与范式,包括从需求中找到实体及其属性,确定实体间关系,并使用SQL创建具体表。文章还深入探讨了一范式、二范式和三范式的要求及不满足这些范式时可能遇到的问题。此外,文中通过实例解释了一对一、一对多和多对多关系的表设计方法,并介绍了如何使用聚合函数如 COUNT()、SUM()、AVG()、MAX() 和 MIN() 进行数据统计和分析。最后,文章还展示了如何通过 SQL 语句实现数据的复制和插入操作。

💎1. 表的设计原则

1. 从需求中找到类,类对应到数据库中的实体,实体在数据库中表现为一张一张的表,类中的属性对应着表中的字段

2. 确定类与类的对应关系

3. 使用SQL去创建具体的表

范式:范式描述的是数据关系的模型(一对一关系,一对多关系,多对多关系)

分类:第一范式(1NF),第二范式(2NF),第三范式(3NF),BC范式(BCNF)

💎2. 三大范式

💎2.1 第一范式

规定:表中的数据不能再分,在定义表的时候,对照数据中的数据类型,每一个字段都可以用一个数据类型标识,那么当前这个表就满足第一范式

例如:定义一个学生表,其中的字段有:学号,姓名,年龄,班级名,学校名,学校地址,这就符合了第一范式,但是如果是:学号,姓名,年龄,班级名,学校,这就不符合第一范式,因为无法找到一个数据类型来表示学校这个对象

关系型数据库的一个最基本的要求,不满足第一范式就不能称为关系型数据库

💎2.2 第二范式

在满足第一范式的基础上,不存在非关键字段(非主键字段)对任意候选键(主键,外键,没有主键时的唯一键)的部分函数依赖(存在与复合主键的情况下),就满足第二范式,复合主键在上一篇文章中已经介绍过,一个表中不能有两个主键,但是一个主键中可以包含多个列,这时的主键就是复合主键

也就是说,如果这个表不含复合主键,那么这个表就满足第二范式

先来看一个表中存在复合主键的情况下,存在非关键字段对候选键的部分函数依赖的不符合第二范式的反例:

学号

姓名

年龄

课程名称

学分

成绩

202201

张三

19

MySQL

3

100

其中,年龄和姓名依赖学号(对应唯一学号),学分依赖课程名称,成绩通过学生和课程共同区分,也就是这个表中可以用学生和课程作为复合主键来确定学生当前的课程成绩,对与其他的,学分和学号,学生姓名等没有关系,学生的姓名和课程名等也没有关系

像这样的,对于由两个或多个关键字段共同决定一条记录(存在复合主键)的情况,如果一行数据中有些字段只与关键字段中的一个有关系,那么就称为只存在部分函数依赖,对于这样的情况就不满足第二范式

接下来看一个正面例子:

对于这样的设计,每张表都有非主键字段,都强依赖与主键,第三个表存在的复合主键,非主键依赖于两个主键的字段,不存在部分函数依赖,满足第二范式

不符合第二范式的时候的弊端:

学号

姓名

年龄

课程名称

学分

成绩

202201

张三

19

MySQL

3

100

202202

李四

19

MySQL

3

100

202203

王五

20

Java

2


95


202204

赵六

19

Java

2

96

1. 数据冗余

学生的年龄和学分大量出现,造成数据冗余

2. 更新异常

如果需要修改MySQL的学分,那么就需要修改表中所有关于MySQL的记录,如果说只有部分数据修改成功,剩余的还是原来的数据,就会出现数据不一致,造成数据混乱

3. 插入异常

当前表格在有学生录入成绩后才能查看课程的学分信息,例如:如果说这时学校加入一门新课,但学生都没有考过试,那么这门新课在数据库就就没有记录

4. 删除异常

同插入异常一样,如果需要删除学生成绩,例如,把选Java的两位同学成绩删除,那么此时在数据库中就又没有Java这门课程的学分信息了

💎2.3 第三范式

在第二范式的基础上,不存在非关键字段对任意候选键的传递依赖

学号

姓名

年龄

所在学院

学院地址

在这个表中,描述的主要对象是学生,所以学号可以作为主键,此时,姓名和年龄与学号是强相关的,学院地址与所在学院是强相关的,描述学生所在学院,只需要把学生和学院建立一个关联关系即可,这两个强相关关系存在传递现象 学号->所在学院->学院地址 ,这种传递关系就称为传递依赖,所以说这种设计不满足第三范式

根据学生与学院的关系,拆分为两张表就满足了第三范式:

学院编号

学院名称

学院地址

学号

姓名

年龄

学院编号

这样设计,两张表都依赖与自己表中的主键,学生表可以通过外键与学院之间建立关联关系

💎3. 三种关系

💎3.1 一对一关系

例如设计一个登录界面,输入用户名和密码登录成功之后,显示欢迎用户,这样的场景一般对应两个实体,用户和账号,并且一个用户只对应一个账号,就是一对一的关系

针对一对一关系设计表时有两种方式

第一种就是把两个实体所有的信息放在一张表中

use_id

name

phone_number

username

password

第二种就是设计两张表,分别记录用户信息和账号信息,再把两张表关联起来

1.第一种关联方式就是通过用户id进行关联,场景:当输入用户名和密码并校验成功之后,再通过用户id去查找用户的name

user_id

name

phone_number

account_id

username

password

user_id

2.第二种关联方式通过account_id进行关联

account_id

username

password

user_id

name

phone_number

account_id

💎3.2 一对多关系

一对多关系其实很常见,例如学生和班级的关系:一个班级中可以有多个学生

创建学生和班级表:

class_id

name

student_id

name

class_id

💎 3.3 多对多关系

例如学生进行选课,一个学生可以选多门课,一门课可以被多名学生选择

分别创建实体表:

course_id

name

1


MySQL


2

Java

student_id

name

age

202201

张三

19

202202

李四

20

创建关系表

id

student_id

course_id

1

202201

1

2

202202

1

3

202202

2

通过关系表,就可以记录每位同学选择的课程,并且符合第二范式,修改学生的年龄字段时也不会影响到关系表

最后把之前讲到的综合起来创建一张成绩表

-- 班级表
create table class
(
    class_id bigint primary key auto_increment,
    name     varchar(20) not null
);
-- 学生表
create table student
(
    student_id bigint primary key auto_increment,
    name       varchar(20) not null,
    age        bigint,
    class_id   bigint,
    -- 设置class_id为class表class_id的外键
    foreign key (class_id) references class (class_id)
);
-- 课程表
create table course
(
    course_id bigint primary key auto_increment,
    name      varchar(50) not null
);
-- 成绩表
create table score
(
    score_id   bigint primary key auto_increment,
    student_id bigint,
    course_id  bigint,
    score      decimal(5, 2),
    -- 设置student_id为student表student_id的外键
    foreign key (student_id) references student (student_id),
    -- 设置course_id为course表course_id的外键
    foreign key (course_id) references course (course_id)
);

💎4. 新增

需求:创建一个新表,把原来的表的数据内容复制到新表中

我们有以下几种解决方法:

1. 一条一条的插入,很明显,这种方法很麻烦,如果数据量很大就不好操作

2. 把原来的数据导出来,再把表名修改一下,再改入到目录表中

3. 使用 insert into select 语句

第二个方法就是在 navicat 中直接进行表的复制

下面来看使用 insert into select 语句的方法

-- 新建一张表,把旧表导入到新表中
create table new_student
(
    id   bigint primary key auto_increment,
    name varchar(50)
);
-- 把在原来的表中查到的数据插入到新的表中
insert into new_student
select id, name
from student;

需要注意的就是,查询到的列和要插入的列要匹配,不然就会报错

💎5. 聚合函数

函数

说明

COUNT([DISTINCT] expr)

返回查询到的数据的数量

SUM([DISTINCT] expr)

返回查询到的数据的总和

AVG([DISTINCT] expr)

返回查询到的数据的平均值

MAX([DISTINCT] expr)

返回查询到的数据的最大值

MIN([DISTINCT] expr)

返回查询到的数据的最小值

💎5.1 COUNT() 统计所有行

-- 统计表中的行数
select count(*)
from student;
-- 也可以传入常量 1
select count(1)
from student;

星号(*)并不直接表示表中的任意一列,而是作为一个特殊的指示符,告诉数据库管理系统(DBMS)计算表中的行数,而不关心表中的列内容或是否有NULL值。

还可以指定某一列进行统计:

-- 指定列统计
select count(id)
from student;
select count(name)
from student;

💎5.2 SUM() 求和

创建一张成绩表,计算语文的总成绩

create table exam
(
    id      bigint primary key auto_increment,
    name    varchar(20),
    chinese decimal(5, 2),
    math    decimal(5, 2)
);
insert into exam(id, name, chinese, math)
values (1, '张三', 98, 95),
       (2, '李四', 97, 99),
       (3, '王五', 96, 98),
       (4, '赵六', 97, 94);
-- 计算语文总成绩
select *
from exam;
select sum(chinese)
from exam;

查询到的结果存储在了临时表中,不受字段中长度的约束(decimal(5, 2)

如果说求和的那一列存在null的话,会是像之前表达式相加时,null加上任何值都是null的情况吗?

insert into exam values (5,'钱七',96,null);
select sum(math)
from exam;

可以看出,最终的值并没有加上null ,并且,如果是非数值类型求和是没有意义的

💎5.3 AVG() 求平均值

-- 求平均值
select avg(math)
from exam;
-- 参数里边可以包含表达式,结果可以使用别名
select avg(math + chinese) as 总分平均值
from exam;

💎5.4 MAX()和MIN()

求指定列中的最大值和最小值

-- 求最大值和最小值
select max(chinese) as 语文最大值,
       min(math) as 数学最小值
from exam;

可以多个聚合函数使用,同时也可以使用别名

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
21天前
|
存储 自然语言处理 关系型数据库
MySQL高级篇——索引的创建与设计原则
索引的分类与使用、MySQL8.0索引新特性、适合创建索引的情况、不适合创建索引的情况
MySQL高级篇——索引的创建与设计原则
|
5月前
|
SQL 关系型数据库 MySQL
mysql子查询、聚合函数
mysql子查询、聚合函数
|
5月前
|
关系型数据库 MySQL 数据挖掘
轻松入门MySQL:深入理解MySQL聚合函数,实战进销存项目中的应用与技巧(8)
轻松入门MySQL:深入理解MySQL聚合函数,实战进销存项目中的应用与技巧(8)
|
5月前
|
关系型数据库 MySQL 数据库
MySQL查询聚合函数与分组查询
MySQL查询聚合函数与分组查询
|
SQL 存储 关系型数据库
第08章 索引的创建与设计原则【2.索引及调优篇】【MySQL高级】3
第08章 索引的创建与设计原则【2.索引及调优篇】【MySQL高级】3
110 0
|
3月前
|
关系型数据库 MySQL 数据挖掘
MySQL 聚合函数案例解析:深入实践与应用
MySQL 聚合函数案例解析:深入实践与应用
|
3月前
|
SQL 关系型数据库 MySQL
MySQL 聚合函数深入讲解与实战演练
MySQL 聚合函数深入讲解与实战演练
|
5月前
|
SQL 关系型数据库 MySQL
【MySQL进阶之路 | 基础篇】MySQL之聚合函数与应用
【MySQL进阶之路 | 基础篇】MySQL之聚合函数与应用
【MySQL进阶之路 | 基础篇】MySQL之聚合函数与应用
|
3月前
|
存储 关系型数据库 MySQL
MySQL索引设计原则与优化策略
MySQL索引设计原则与优化策略
|
4月前
|
SQL 关系型数据库 MySQL
MySQL数据库——索引(6)-索引使用(覆盖索引与回表查询,前缀索引,单列索引与联合索引 )、索引设计原则、索引总结
MySQL数据库——索引(6)-索引使用(覆盖索引与回表查询,前缀索引,单列索引与联合索引 )、索引设计原则、索引总结
76 1
下一篇
无影云桌面