优享资讯 | MySQL数据库及表的设计

掘金后端 ( ) • 2024-04-16 11:33

MySQL数据库及表的设计

highlight: a11y-dark

什么是E-R图?

在数据库概念设计阶段会基于需求,进行实体关系的描述,描述的形式一般是一个实体关系图(E-R)，图中会描述实体、实体属性、实体与实体之间的一个关系。

什么是关系模型？

关系模型是数据库逻辑设计阶段将E-R图中的实体,实体属性,实体与实体之间的关系转换为的一种关系模式.

例如:

雇员(雇员编号,雇员姓名,雇员薪水,入职日期,....)

部门(部门编号,部门名称,部门地址,经理人id)

常见的数据库操作语句有哪些？

#创建数据库
create database if not exists jsd character set utf8mb4;
#查看数据库字符集
show variables like 'collation';
#创建数据库并指定字符集
create schema jht collate 'utf8mb4_general_ci';
#显示创建数据库语句
show create database jsd;
#删除数据库
drop database if exists jsd;
#打开数据库
use jsd;
#显示数据库中的表
show tables;

设计表是要考虑哪些因素？

表名及字段名【见名知意，多个单词时，单词之间用下划线连接】
表中字段的类型、约束、注释、字段数量【不能太多，越多维护成本就越高】
表的存储引擎【InnoDB,MyIsam】
表的设计范式【三大范式，反范式】

MySQL有哪些常用数据类型？

字符串类型 (char,varchar,text,...)
日期/时间类型(date,time,datetime,timestamp,...)
数值类型(tinyint,int,bigint,decimal,...)
二进制类型(blob,mediumblob,longblob,...)
其它(enum,set,json,...)

MySQL数据类型应用有哪些原则？

尽量选择简单数据类型(例如存储整数用int不用varchar)
尽量使用最小数据类型(例如能用tinyint不用int)
假如要存储小数可以考虑使用decimal类型。
尽量避免使用text、blob等大字段类型(假如需要使用则尽量放到一张表中)

MySQL表中常用字段约束有哪些？

非空约束(not null)：字段的值不允许为空。
主键约束(primary key)：字段值不允许为空并且唯一。
唯一约束(unique key)：字段值必须唯一。
检查约束(check)：字段值需要在指定范围(但是数据库之间的兼容不好)
外键约束(foreign key)：字段值需要参考引用表中的字段值。
字段默认值(Default Value): 建议允许为空的字段给一个默认值。

如何理解宽表和窄表的概念？

宽表和窄表的定义一般由企业内部开发规范进行定义(例如超出40个字段定义宽表)。

宽表就是表中字段比较多的表(字段越多维护越困难，甚至会影响查询效率)
窄表就是表中字段比较少的表(维护简单、太少可能会导致大量的表关联)

如何理解表设计时的三大范式？

范式是一种设计规范，一种关系模式，可以对表的设计起到一个指导性作用,常用的设计范式有如下三种：

第一范式(1NF):字段不可再分(原子性)。例如姓名可再分为姓和名，这属于可再分。
第二范式(2NF): 首先要满足1NF，然后不存在非主键字段对主键字段的部分依赖。
第三范式(3NF): 首先要满足1NF，然后不存在非主键字段对主键字段的传递依赖。

案例：判断如下表的设计是否满足第一范式?

create table t_student(
   id int auto_increment comment '自增id值'，
   name varchar(50) not null comment '学生姓名',
   primary key (id)
)engine=InnoDB character set utf8mb4;

不满足，表中的name字段可以分为姓和名，可以修改如下：

create table t_student(
   id int auto_increment comment '自增id值'，
   first_name varchar(50) not null comment '名字',
   last_name varchar(50) not null comment '姓氏',
   primary key (id)
)engine=InnoDB character set utf8mb4;

案例，判断如下表（成绩表）的设计是否满足第二范式?

create table t_score(
   sid int comment '学生id'，
   cid int comment '课程id'，
   cname varchar(50) not null comment '课程名',
   score int default 0 comment '成绩',
   primary key (sid,cid)
)engine=InnoDB character set utf8mb4;

不满足，这里的主键为sid和cid的组合，但是表中的cname依赖于cid，并不依赖于sid，所以，这个设计中存在非主键字段部分依赖于主键字段。假如希望这个设计要满足第二范式，可以将cname这个字段移除，添加课程表（t_course）中。

案例，判断如下表的设计是否满足第三范式?

create table t_teacher(
   id int comment '教师id'，
   first_name varchar(50) not null,
   last_name varchar(50) not null,
   school_name varchar(100) default ''comment '任教的大学',
   school_phone varchar(100) default ''comment '任教学校电话',
   primary key (id)
)engine=InnoDB character set utf8mb4;

不满足，因为这里存在非主键字段school_phone对非主键字段school_name依赖。假如要满足第三范式需要将school_name,school_phone放到学校表中，单独创建学校表，在t_teacher表中添加一个学校id即可。

create table t_teacher(
   id int comment '教师id'，
   first_name varchar(50) not null,
   last_name varchar(50) not null,
   school_id int,
   primary key (id)
)engine=InnoDB character set utf8mb4;

create table t_school(
   id int comment '学校id'，
   school_name varchar(100) default ''comment '任教的大学',
   school_phone varchar(100) default ''comment '任教大学的电话',
   primary key (id)
)engine=InnoDB character set utf8mb4;