表和表之间的关系
数据库设计方法:试凑法、规范化法和
CAD
法
数据库设计工具:
powerdesigner
在实际开发中,一个项目经常会涉及很多方面的数据。例如产品和类目
商品编号
|
商品名称
|
价格
|
类别名称
|
1 | 娃哈哈 | 2.5 |
饮料
|
2 | AD钙奶 | 5 |
饮料
|
3 | 平凡的世界 | 56 | 书籍 |
数据冗余和外键
数据冗余:类别信息重复
数据冗余导致的问题:空间浪费、增加异常、删除异常和修改异常
解决方案:引入外键约束
产品表
商品编号
|
商品名称
|
价格
|
类别名称
|
1 | 娃哈哈 | 2.5 |
饮料
|
2 | AD钙奶 | 5 |
饮料
|
3 | 平凡的世界 | 56 | 书籍 |
类目表
类别编号
|
类别名称
|
等级
|
1 |
饮料
| 3 |
2 |
书籍
| 2 |
通过引入新的表,同时使用外键约束保证取值的合理性,从而减少数据冗余
create table tb_catalog(
id bigint primary key auto_increment, -- 实体完整性
title varchar(32) not null
) comment '类别表';
create table tb_product(
id bigint primary key auto_increment comment '商品标号',
name varchar(32) not null,
price numeric(8,2) default 0,
-- 引入额外的列用于表示商品所属于的类别
catalog_id bigint, -- 外键列,允许为null
-- 外键表示该列的允许取的值必须在tb_catalog的id列中出现
foreign key(catalog_id) references tb_catalog(id)
) comment '商品表';
- 类目表中的id为主键,产品表中的列参照于类目表中的主键,所以一般类目表会被称为主表,产品表称为从表,产品表中的catalog_id称为外键
- 通过主表的主键和从表中的外键来描述的主外键关系,呈现的是一种一对多的关系
- 一个类目有多个商品
- 一个商品只能属于一个类目
- 在MySQL中innodb支持外键和事务,MyISAM不支持外键和事务
- 在具体开发中,为了提高性能,会故意删除外键约束,通过代码来控制数据的合理性
外键的特点
- 从表中的外键的值是对应主表中主键值的引用
- 从表中的外键数据类型必须和主表中的主键数据类型一致
基础语法
引入外键的目的在于保证数据的参照完整性
创建外键语法
1
:
create table tb_product(
id bigint primary key auto_increment comment '商品标号',
-- 引入额外的列用于表示商品所属于的类别
catalog_id bigint, -- 外键列,允许为null
-- 外键表示该列的允许取的值必须在tb_catalog的id列中出现
foreign key(catalog_id) references tb_catalog(id)
) comment '商品表';
创建外键语法
2
:
create table tb_product(
id bigint primary key auto_increment comment '商品标号',
catalog_id bigint -- 外键列,允许为null。是否允许为空取决于业务规则
)
-- alter table 从表名称 add [constraint fk_catalog外键约束名称] foreign key(从表中的外
键列名) references 主表名称(主表中的主键列名称)
alter table tb_product add constraint fk_catalog foreign key(catalog_id)
references tb_catalog(id);
删除外键约束的语法:
-- alter table 从表名称 drop foreign key 外键约束名称;
alter table tb_product drop foreign key fk_catalog;
需要记住
SELECT
查询时的两个顺序:
1
、关键字的顺序是不能颠倒的:
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ...
ORDER BY ... LIMIT...
2
、
SELECT
语句的执行顺序:
SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1
WHERE height > 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num > 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7
一个
SQL
语句的关键字顺序和执行顺序是
FROM
-
> WHERE
-
> GROUP BY
-
> HAVING
-
> SELECT
的字段-
> DISTINCT
-
> ORDER BY
-
> LIMIT
。在
SELECT
语句执行这些步骤的时候,每个步骤都会产生一个虚拟表 ,然后将这个虚拟表传入下一个步骤中作为输入。需要注意的是,这些步骤隐含在
SQL
的执行过程中,对于我们来说是不可见的。
SQL的执行原理
SELECT
是先执行
FROM
这一步的。在这个阶段,如果是多张表联查,还会经历下面的几个步骤:
1.
首先先通过
CROSS JOIN
求笛卡尔积,相当于得到虚拟表
vt
(
virtual table
)
1-1
;
2.
通过
ON
进行筛选,在虚拟表
vt1-1
的基础上进行筛选,得到虚拟表
vt1-2
;
3.
添加外部行。如果我们使用的是左连接、右链接或者全连接,就会涉及到外部行,也就是在虚拟表vt1-2
的基础上增加外部行,得到虚拟表
vt1-3
。
当然如果我们操作的是两张以上的表,还会重复上面的步骤,直到所有表都被处理完为止。这个过程得到是我们的原始数据。
然后进入第三步和第四步,也就是
GROUP
和
HAVING
阶段 。在这个阶段中,实际上是在虚拟表
vt2
的
基础上进行分组和分组过滤,得到中间的虚拟表
vt3
和
vt4
。
当我们完成了条件筛选部分之后,就可以筛选表中提取的字段,也就是进入到
SELECT
和
DISTINCT
阶段
。
- 首先在 SELECT 阶段会提取想要的字段,然后在 DISTINCT 阶段过滤掉重复的行,分别得到中间的虚拟表vt5-1 和 vt5-2 。
当我们提取了想要的字段数据之后,就可以按照指定的字段进行排序,也就是
ORDER BY
阶段 ,得到虚拟表
vt6
。
最后在
vt6
的基础上,取出指定行的记录,也就是
LIMIT
阶段 ,得到最终的结果,对应的是虚拟表
vt7。
当然我们在写
SELECT
语句的时候,不一定存在所有的关键字,相应的阶段就会省略。
同时因为
SQL
是一门类似英语的结构化查询语言,所以在写
SELECT
语句时还要注意相应的关键字顺序,所谓底层运行的原理,就是执行顺序。
范式NF
设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小。
目前关系数据库有
5+1
级范式:第一范式
(1NF)
、第二范式
(2NF)
、第三范式
(3NF)
、巴斯
-
科德范式
(BCNF)
、第四范式
(4NF)
和第五范式
(5NF
,又称完美范式
)
。满足最低要求的范式是第一范式
(1NF)
。在第一范式的基础上进一步满足更多规范要求的称为第二范式
(2NF)
,其余范式以次类推。如果不满足所要求
的范式,则将不满足范式要求的部分进行分表。一般说来,数据库只需满足第三范式
(3NF)
就行了。
数据库设计中的概念
- 实体:现实世界中客观存在并可以被区别的事物。比如“一个学生”、“一本书”、“一门课”等等。值得强调的是这里所说的“事物”不仅仅是看得见摸得着的“东西”,它也可以是虚拟的,不如说“老师与学校的关系”。
- 属性:教科书上解释为:“实体所具有的某一特性”,由此可见,属性一开始是个逻辑概念,比如说,“性别”是“人”的一个属性。在关系数据库中,属性又是个物理概念,属性可以看作是“表的一列”。
- 元组:表中的一行就是一个元组。
- 分量:元组的某个属性值。在一个关系数据库中,它是一个操作原子,即关系数据库在做任何操作的时候,属性是“不可分的”。否则就不是关系数据库了。
- 码:表中可以唯一确定一个元组的某个属性(或者属性组),如果这样的码有不止一个,那么叫候选码,从候选码中挑一个出来做老大,它就叫主码。
- 全码:如果一个码包含了所有的属性,这个码就是全码。
- 主属性:一个属性只要在任何一个候选码中都出现过,这个属性就是主属性。
- 非主属性:与上面相反,没有在任何候选码中出现过,这个属性就是非主属性。
- 外码:一个属性(或属性组),它不是码,但是它别的表的码,它就是外码。
- 候选码: 若关系中的某一属性或属性组的值能唯一的标识一个元组,而其任何真子集都不能再标识,则称该属性组为(超级码)候选码。
主键的定义
主键可分为
2
大类:自然主键和代理主键。一般建议使用代理主键
- 将表中的所有列的组合当作主键--候选码
- 去除其中某些列查看是否还能唯一标识一行数据
- 最后找到的所有候选码的真子集就是主码
最佳实践:可以在表中添加一个与业务无关的字段充当主键
id bigint primary key
auto_increment
NF1
所有列不可分,字段满足原子性
定义学生,学生
(
编号、班级编号、姓名、亲属
)
,这个亲属列是可分的,所以将亲属列划分到另外表中,从而使剩余的列满足
NF1
,最终结构选择为 学生
(
编号、班级编号、姓名
)
、学生亲属
(
姓名、关系、外码
)
NF2
消除对主键的部分依赖
定义学生,学生
(
编号、班级编号、姓名、宿舍楼号
)
,主键为复合主键
(
编号、班级编号
)
,这里会发现一旦班级编号确定则所属的系别就确定,系别确定则宿舍楼号确定。宿舍楼号部分依赖主键,不是依赖整
个主键。解决问题的方法为分表
学生
(
编号、班级编号、姓名
)
学生住宿
(
班级编号、宿舍楼号
)
NF3
消除对主键的传递依赖
定义学生,学生(学号
pk
、系别、宿舍楼号),主键为学号,所以自然满足
NF2
,但是一旦系别确定则宿舍楼号确定,所以宿舍楼号依赖于系别,不是依赖于学号。这里就是传递依赖:宿舍楼号
-->
系别
-->
学
号
pk
。解决问题的方法为分表
范式和反范式
应用范式可以减少数据冗余,但是范式级别越高,则创建表的数量越多,查询效率则越低。所以在具体开发中经常采用降低范式要求,采用合理冗余数据的方式以提高查询效率
考虑查询效率,所以一般只达到
NF3
即可,甚至有时会了提高查询效率会有意降低范式要求【反范式】
经典案例:电商网站,例如京东和淘宝
商品
(
商品编号
(pk)
、商品类别
)-->
商品(商品编号
pk
,类别编号)、类目
(
类目标号、类目名称
)
商品的数量非常庞大,而且类别要分为
3
个级别
10*10*10
实际应用:商品(编号、
1
级类别名称、
2
级类别名称、
3
级类别名称)
表和表之间关系
表和表【实体】之间的关系有
3
种:
- 一对一1:1,例如一个人只能有一个身份证,一个身份证只能属于一个人
- 一对多或者多对一1:m或者m:1,例如一个类目可以包含多个商品,一个商品只能属于一个类目
- 多对多n:m,例如一个学生可以选修多门课程,一个课程可以被多个学生选修
考察表【实体】之间关系的方法:中立
一对一
一对一实现方式有
2
种:共享主键或者唯一外键。例如人和身份证
共享主键
- 在tb_person从表中id列即是当前表的主键,又是外键
create table tb_card(
id bigint primary key auto_increment comment '不是身份证号码,仅仅是一个非业务含义
的编号',
name varchar(32) not null,
birth date
);
create table tb_person(
id bigint primary key, -- 这里的主键值来源于tb_card的主键值,主键约束非空唯一
-- 人的编号来源于card表种的编号值,而且on delete cascade级联删除,表示删除对应的身份证
信息时会自动删除对应的用户信息
foreign key(id) references tb_card(id) on delete cascade,
salary decimal(8,2)
);
唯一外键
create table tb_card(
id bigint primary key auto_increment comment '不是身份证号码,仅仅是一个非业务含义
的编号',
name varchar(32) not null,
birth date
);
create table tb_person(
id bigint primary key, -- 这里的主键值来源于tb_card的主键值,主键约束非空唯一
-- 人的编号来源于card表种的编号值,而且on delete cascade级联删除,表示删除对应的身份证
信息时会自动删除对应的用户信息
foreign key(id) references tb_card(id) on delete cascade,
salary decimal(8,2)
);
特殊实现方式
例如一夫一妻,如何在一个表中保存所有人的信息
编号
(PK)
|
姓名
|
性别
|
妻子编号
|
丈夫编号
|
1 | 张三 |
true
| 2 |
null
|
2 | 李四 |
false
|
null
| 1 |
3 | 王五 |
true
|
null
|
null
|
另外的实现方式
编号
(PK)
|
姓名
|
性别
|
配偶编号
|
1 | 张三 |
true
| 2 |
2 | 李四 |
false
| 1 |
3 | 王五 |
true
|
null
|
数据表定义
create table tb_person(
id bigint primary key auto_increment,
name varchar(10) not null,
sex boolean default 1,
pei_id bigint unique,
foreign key(pei_id) references tb_person(id)
);
一对多
实际上默认情况下
fk
外键参照主键
pk
则就是一对多关联,例如一个人有多辆车,一个车只能属于一个人
create table tb_person(
id bigint primary key,
name varchar(20)
);
create table tb_car(
id bigint primary key,
title varchar(32) not null,
person_id bigint not null,
foreign key(person_id) references tb_person(id)
);
多对多
实际上在关系型数据库中是不能直接表达多对多关系,必须引入中间表。例如学生选修课程
create table tb_student(
id bigint primary key auto_increment comment '学生编号',
name varchar(10) not null comment '学生姓名'
) comment '学生表';
create table tb_course(
id bigint primary key auto_increment comment '课程编号',
title varchar(32) not null comment '课程名称'
) comment '课程表';
create table tb_choice(
sid bigint comment '学生编号',
cid bigint comment '课程编号',
-- 不允许重复选修
primary key(sid,cid),
-- 不允许出现的学生信息错误
foreign key(sid) references tb_student(id) on delete cascade,
-- 不允许选修不存在的课程
foreign key(cid) references tb_course(id) on delete cascade
) comment '选课表,用于表示多对多关系';
PowerDesigner
Power Designer
是
Sybase
公司的
CASE
工具集,使用它可以方便地对管理信息系统进行分析设计,他几乎包括了数据库模型设计的全过程
计算机辅助软件工程 CASE ( Computer Aided ( or Assisted ) Software Engineering) 。原来指用来支持管理信息系统开发的、由各种计算机辅助软件和工具组成的大型综合性软件开发环境,随着 各种工具和软件技术的产生、发展、完善和不断集成,逐步由单纯的辅助开发工具环境转化为一种 相对独立的方法论
优点
- 不用在使用create table等语句创建表结构,数据库语句,可以自动生成
- 数据库设计人员只关注如何进行数据建模即可
基本设计流程
1
、创建
Conceptual Data Model
- stereotype套用模板
- mandatory强制不可为空的意思
- domain域可以理解为取值范围,定义最大值、最小值、约束。通过用户自定义后,新建实体时可以使用域对字段属性进行快速定义。需要Model-Domains先创建后使用
2、建立实体之间的联系
3
、检查
CDM
:菜单栏的
Tools
选项,选择
Check Model
,打开检查模型的界面
4
、将
CDM
转换成
PDM
:菜单栏的
Tools
选项,选择
Generate Physical Data Model
5
、由
PDM
生成
sql
文件:在
PDM
页面菜单栏中的
Database
点击
Generate Database