mysql与gauss建表 mysql建立表与表之间的关系

转载

锦绣前程未央 2024-04-26 14:43:03

文章标签 mysql与gauss建表 1024程序员节 mysql sql 学习 文章分类 MySQL 数据库

表和表之间的关系

数据库设计方法：试凑法、规范化法和 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) 。原来指用来支持管理信息系统开发的、由各种计算机辅助软件和工具组成的大型综合性软件开发环境，随着各种工具和软件技术的产生、发展、完善和不断集成，逐步由单纯的辅助开发工具环境转化为一种相对独立的方法论

优点