数据库技术二：（多表，外键，数据库设计）

YMeng_Zhang

已于 2022-07-08 13:37:38 修改

阅读量717

点赞数 1

分类专栏：数据库文章标签：数据库 java

于 2021-08-19 13:31:18 首次发布

本文链接：https://blog.csdn.net/bier_zm/article/details/119799832

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1. 多表

单表：有冗余问题，同一个字段中出现大量的重复数据。
实际开发中，一个项目通常需要很多张表才能完成。
例如一个商城项目的数据库,需要有很多张表：用户表、分类表、商品表、订单表....

-- dep_name 和 dep_location 数据会重复出现
CREATE TABLE emp(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    age INT ,
    dep_name VARCHAR(20),
    dep_location VARCHAR(20)
);

-- 删除emp表, 重新创建两张表
DROP TABLE emp;

-- 主表:部门表。字段 id 为主键
CREATE TABLE department(
     id INT PRIMARY KEY AUTO_INCREMENT,   
     dep_name VARCHAR(30),  
     dep_location VARCHAR(30)
);
-- 从表:员工表。外键 dept_id 与部门表中的主键对应
CREATE TABLE employee(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    age INT,
    dept_id INT
);

多表设计上的问题：
在员工表的 dept_id 里面输入不存在的部门 id，数据依然可以添加，显然这是不合理的。

解决方法：
使用外键约束，约束 dept_id 为部门表中存在的 id。

1.1 外键约束

1.1.1 什么是外键?

-- 外键指的是在从表中与主表的主键对应的那个字段,比如员工表的 dept_id,就是外键。使用外键约束可以让两张表之间产生一个对应关系,从而保证主从表的引用的完整性。

主表：主键 id 所在的表，约束别人的表；
从表：外键所在的表，被约束的表。

1.1.2 创建外键约束

添加外键约束，就会产生强制性的外键数据检查，从而保证了数据的完整性和一致性。

-- 已有表添加外键约束
-- 可以不写 CONSTRAINT emp_dept_fk
ALTER TABLE employee 
ADD CONSTRAINT emp_dept_fk 
    FOREIGN KEY (dept_id) 
    REFERENCES department(id);

-- 重新创建表，添加外键约束
-- 先删除 employee表
DROP TABLE employee;
-- 重新创建 employee 表，添加外键约束
CREATE TABLE employee(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    age INT,
    dept_id INT,
    -- 添加外键约束
    CONSTRAINT emp_dept_fk 
        FOREIGN KEY(dept_id) 
        REFERENCES department(id)
);

1.1.3 删除外键约束

-- 删除 employee 表中的外键约束
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;

-- 再将外键约束添加回来
-- 省略外键约束名称, 系统会自动生成一个约束名称
ALTER TABLE employee 
ADD FOREIGN KEY (dept_id) 
    REFERENCES department (id);

1.1.4 外键约束的注意事项

-- 从表外键类型必须与主表主键类型一致，否则外键约束创建失败。（Error: Cannot add foreign key constraint）

添加数据时，应该先添加主表中的数据。

-- 添加一个新的部门
INSERT INTO department(dep_name,dep_location) VALUES('市场部','广州');
-- 添加一个属于市场部的员工
INSERT INTO employee(ename,age,dept_id) VALUES('张三',24,1);

删除数据时，应该先删除从表中的数据。（Error: Cannot delete or update a parent row: a foreign key constraint fails）

-- 错误删除：直接删除主表数据
-- 不能删除主表的这条数据，因为在从表中有对这条数据的引用
-- DELETE FROM department WHERE id = 1;

-- 正确删除
-- 1.先删除从表的所有关联数据
DELETE FROM employee WHERE dept_id = 1;
-- 2.再删除主表的数据
DELETE FROM department WHERE id = 1;

1.1.5 级联删除操作

级联删除操作：实现删除主表数据的同时，也删除掉从表数据，可以使用级联删除操作。

-- 重新创建
CREATE TABLE employee(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    age INT,
    dept_id INT,
    CONSTRAINT emp_dept_fk 
        FOREIGN KEY(dept_id) 
        REFERENCES department(id) 
        ON DELETE CASCADE   -- 添加级联删除
);
-- 添加数据
...
-- 删除部门编号为 1 的记录，
-- 同时，员工表中外键值是 1 的记录也自动删除了
DELETE FROM department WHERE id = 1;

2. 多表关系设计

2.1 一对多关系(1:n)

最常见的关系：班级对学生，部门对员工，客户对订单，商品对分类。

建表原则：主表（一方）的主键为从表（多方）的外键。在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键

-- 省和市表：一个省包含多个市

-- 创建省表。主表，要添加主键约束
CREATE TABLE province(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    description VARCHAR(20)
);
-- 创建市表。从表，外键类型要与主表主键一致
CREATE TABLE city(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    description VARCHAR(20),
    pid INT,
    CONSTRAINT pro_city_fk 
        FOREIGN KEY (pid) 
        REFERENCES province(id)
);

2.2 多对多关系(n:n)

学生对课程，学生对老师，用户对角色。

建表原则：需要借助一张中间表，中间表中至少保存两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。

-- 演员与角色表：多演员对多角色(一个演员可演多个角色，一个角色可被多个演员演)

-- 创建演员表
CREATE TABLE actor(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);
-- 创建角色表
CREATE TABLE role(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);
-- 创建中间表
CREATE TABLE actor_role(
    -- 中间表自己的主键
    id INT PRIMARY KEY AUTO_INCREMENT,
    -- 指向 actor 表的外键
    aid INT,
    -- 指向 role 表的外键
    rid INT
);
-- 添加外键约束指向演员表的主键
ALTER TABLE actor_role 
ADD FOREIGN KEY(aid) REFERENCES actor(id);
-- 添加外键约束指向角色表的主键
ALTER TABLE actor_role 
ADD FOREIGN KEY(rid) REFERENCES role(id);

2.3 一对一关系（1:1）

在实际的开发中使用较少，因为一对一关系可以合成为一张表。

建表原则：可以在任意一方添加一个外键，指向另一方的主键。给外键设置唯一约束。

3. 多表查询

3.1 什么是多表查询?

查询多张表，获取到需要的数据。例如，要查询家电分类下都有哪些商品，那么我们就需要查询分类表与商品表。

-- 创建 db1 数据库，指定编码
CREATE DATABASE db1 CHARACTER SET utf8;
use db1;

-- 创建分类表与商品表

-- 分类表：一方，主表
CREATE TABLE category (
    cid VARCHAR(32) PRIMARY KEY ,
    cname VARCHAR(50)
);
-- 商品表：多方，从表
CREATE TABLE products(
    pid VARCHAR(32) PRIMARY KEY,
    pname VARCHAR(50),
    price INT,
    flag VARCHAR(2), #是否上架标记为：1表示上架、0表示下架
    category_id VARCHAR(32),
    FOREIGN KEY (category_id) REFERENCES category (cid)
);

-- 插入数据
-- 分类数据
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','鞋服');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
INSERT INTO category(cid,cname) VALUES('c004','汽车');
-- 商品数据
INSERT INTO products(pid,pname,price,flag,category_id) 
VALUES('p001','小米电视',5000,'1','c001');
...
INSERT INTO products(pid,pname,price,flag,category_id) 
VALUES('p004','篮球',800,'1','c002');
...
INSERT INTO products(pid,pname,price,flag,category_id) 
VALUES('p009','饮料',200,'1','c003');

3.2 多表查询的分类

3.2.1 内连接查询

内连接：通过指定的条件去匹配两张表中的数据，匹配上就显示，匹配不上就不显示。比如，通过从表的外键 = 主表的主键的方式去匹配。

隐式内连接

隐式内连接：在 from 子句后面直接写多个表名，并使用 where 子句指定连接条件来过滤无用的数据。

-- 隐式内连接查询所有商品和对应的分类信息
SELECT * 
FROM products, category 
WHERE category_id = cid;

-- 隐式内连接通过给表起别名的方式查询
SELECT 
    p.`pname`,
    p.`price`,
    c.`cname`
FROM products p, category c 
WHERE p.`category_id` = c.`cid`;

-- 查询小米电视是属于哪一分类下的商品
SELECT p.`pname`,c.`cname` 
FROM products p , category c 
WHERE p.`category_id` = c.`cid` 
    AND p.`pid` = 'p001';

显式内连接

显式内连接：使用 Inner Join ... On 这种方式，Inner 可以省略。

-- 显式内连接查询所有商品信息和对应的分类信息
SELECT * FROM products p 
INNER JOIN category c 
    ON p.category_id = c.cid;

-- 查询鞋服分类下，价格大于 500 的商品名称和价格
SELECT
    p.pname,
    p.price
FROM products p 
INNER JOIN category c 
    ON p.category_id = c.cid
WHERE p.price > 500 
    AND cname = '鞋服';

3.2.2 外连接查询

左外连接

左外连接：使用 Left Outer Join , Outer 可以省略。以左表为基准，匹配右边表中的数据；如果匹配的上，就展示匹配到的数据；如果匹配不到，左表中的数据正常展示，右边的展示为 null。

-- 左外连接查询所有分类下的商品信息
-- 若分类下没有商品信息，数据显示为空
SELECT * 
FROM category c 
LEFT JOIN products p 
    ON c.`cid`= p.`category_id`;

-- 左外连接查询每个分类下的商品个数
SELECT 
    c.`cname` AS '分类名称',
    COUNT(p.`pid`) AS '商品个数' 
FROM category c 
LEFT JOIN products p 
    ON c.`cid` = p.`category_id`
GROUP BY c.`cname`;

右外连接

右外连接：使用 Right Outer Join，Outer 可以省略。以右表为基准，匹配左边表中的数据；如果能匹配到，展示匹配到的数据；如果匹配不到，右表中的数据正常展示，左边展示为 null。

-- 右外连接查询所有分类下的商品信息
-- 若分类下没有商品信息，数据显示为空
SELECT * 
FROM products p 
RIGHT JOIN category c 
    ON p.`category_id` = c.`cid`;

3.2.3 各种连接方式的总结：

内连接：只获取两张表中交集部分的数据。
左外连接：以左表为基准，查询左表的所有数据，以及与右表有交集的部分。
右外连接：以右表为基准，查询右表的所有数据，以及与左表有交集的部分。

4. 子查询 (SubQuery)

4.1 什么是子查询

子查询概念：一条 select 查询语句的结果，作为另一条 select 语句的一部分。

子查询的特点：子查询必须放在小括号中；子查询一般作为父查询的查询条件使用。

4.2 子查询常见分类

where型子查询: 将子查询的结果, 作为父查询的比较条件
from型子查询 : 将子查询的结果, 作为一张表,提供给父层查询使用
exists型子查询: 子查询的结果是单列多行, 类似一个数组, 父层查询使用 IN 函数 ,包含子查询的结果

4.2.1 where 型子查询

将子查询的结果作为父查询的筛选条件。

-- 需求一：通过子查询的方式，查询价格最高的商品信息
--1.先查询出最高价格
SELECT MAX(price) 
FROM products;
--2.将最高价格作为条件，获取商品信息
SELECT * 
FROM products 
WHERE 
price = (
        SELECT MAX(price) 
        FROM products
    );


-- 需求二：查询化妆品分类下的商品名称和商品价格
--1.先查出化妆品分类的 id
SELECT cid 
FROM category 
WHERE cname = '化妆品';
--2.根据分类 id，查询商品表对应的商品信息
SELECT 
    p.`pname`,
    p.`price`
FROM products p 
WHERE 
    p.`category_id` = (
        SELECT cid 
        FROM category 
        WHERE cname = '化妆品'
    );


-- 需求三：查询小于平均价格的商品信息
-- 1.先查询平均价格
SELECT AVG(price) 
FROM products;
-- 2.再查询小于平均价格的商品
SELECT * 
FROM products 
WHERE 
price < (
        SELECT AVG(price) 
        FROM products
    );

4.2.2 from 型子查询

将子查询的结果作为一张表提供给父层查询使用。另外需要给这张表起别名，否则无法访问表中的字段。

-- 需求：查询商品中，价格大于 500 的商品信息
-- 1.先查询分类表的数据
SELECT * 
FROM category;
-- 2.将上面的查询语句作为一张表使用
SELECT 
    p.`pname`,
    p.`price`,
    c.cname
FROM products p 
INNER JOIN 
(
        SELECT * 
        FROM category
    ) c 
    ON p.`category_id` = c.cid 
WHERE p.`price` > 500;

4.2.3 exists 型子查询

如果子查询的结果是单列多行类似一个数组，那么父层查询可以使用 IN 函数来包含子查询的结果。

-- 需求一：查询价格小于两千的商品，来自于哪些分类（名称）
--1. 先查询价格小于 2000 的商品的分类 ID
SELECT DISTINCT category_id 
FROM products 
WHERE price < 2000;
--2.基于以上的数据进行子查询
SELECT * 
FROM category 
WHERE cid IN (
    SELECT DISTINCT category_id 
    FROM products 
    WHERE price < 2000
);

-- 需求二：查询家电类与鞋服类下面的全部商品信息
-- 1.先查询出家电与鞋服类的分类 ID
SELECT cid 
FROM category 
WHERE cname IN ('家电','鞋服');
--2.根据 cid 查询分类下的商品信息
SELECT * 
FROM products 
WHERE category_id IN (
    SELECT cid 
    FROM category 
    WHERE cname IN ('家电','鞋服')
);

4.3 子查询总结

子查询如果查出的是一个字段（单列），那就在 where 后面作为条件使用。
子查询如果查询出的是多个字段（多列），就当做一张表使用（要起别名）。

5. 数据库设计

5.1 数据库三范式(空间最省)

范式是设计数据库的规则。

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。

满足最低要求的范式是第一范式（1 NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2 NF），其余范式以此类推。一般说来，数据库只需满足第三范式（3 NF）就行了。

5.1.1 第一范式 1NF

第一范式：原子性，做到列不可拆分，是最基本的范式。数据库表里面字段都是单一属性的，不可再分。如果数据表中每个字段都是不可再分的最小数据单元，则满足第一范式。

比如，如果把国家和城市都放到一个字段中，那么这个字段就不符合第一范式，所以需要被拆分为两个字段才行。

5.1.2 第二范式 2NF

第二范式：在第一范式的基础上更进一步，目标是确保表中的每列都和主键相关。一张表只能描述一件事。

比如，如果把学员的信息和课程信息放在一张表中，会导致数据的冗余，如果删除学员信息，课程的信息也被删除了，所以需要拆分为学员和课程两张表。

5.1.3 第三范式 3NF

第三范式：非主键字段不能相互依赖（消除传递依赖）。如果能够推导表的信息出来，就不应该单独的设计一个字段来存放。空间最省原则。

比如，一个表中有三个字段，分别为数量、单价、总价格，因为总价格=数量*单价，所以这里的字段之间存在依赖关系，可以通过数量和单价推导出总价格，这时候就可以省略总价格这个字段。

5.2 数据库反三范式

数据库反范式化：通过增加冗余或重复的数据来提高数据库的读性能，以空间换时间。

什么是冗余字段 ?

设计数据库时，某一个字段属于一张表，但它同时出现在另一个或多个表，且完全等同于它在其本来所属表的意义表示，那么这个字段就是一个冗余字段

反三范式示例

设计”用户表“和”订单表“：”用户表“中有”名字“字段，而”订单表“中也存在”名字“字段；当需要查询“订单表”所有数据并且只需要“用户表”的”名字“字段时，此时如果没有冗余字段，就需要去内连接”订单表“和”用户表“，进一步假设表中数据量非常的大，那这次内连接查询就会消耗巨大的系统性能；这种情况下，冗余的字段就可以派上用场了，如果有了冗余字段，那么我们只查询”订单表“就可以了。

总结

尽量遵循范式理论的规约，尽可能减少冗余字段。
某些情况下，可以合理的加入冗余字段以减少表与表的连接操作，从而让数据库的执行效率更高。

YMeng_Zhang

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
数据库技术二：（多表，外键，数据库设计）

目录1. 多表外键约束什么是外键?创建外键约束删除外键约束外键约束的注意事项级联删除操作2. 多表关系设计一对多关系(1:n)多对多关系(n:n)一对一关系（1:1）3. 多表查询什么是多表查询?多表查询的分类内连接查询外连接查询各种连接方式的总结4. 子查询 (SubQuery)什么是子查询子查询常见分类where 型子查询from 型子查询exists 型子查询子查询总结5. 数据库设计数据库三范式(空间最省)第一范式 1NF第二范式 2NF第三范式 3NF数据库反三范式单表：有冗余问题，同一个字段中出
复制链接

扫一扫