目录

1. 多表

1.1 外键约束

1.1.1 什么是外键?

1.1.2 创建外键约束

1.1.3 删除外键约束

1.1.4 外键约束的注意事项

1.1.5 级联删除操作

2. 多表关系设计

2.1 一对多关系(1:n)

2.2 多对多关系(n:n)

2.3 一对一关系（1:1）

3. 多表查询

3.1 什么是多表查询?

3.2 多表查询的分类

3.2.1 内连接查询

3.2.2 外连接查询

3.2.3 各种连接方式的总结：

4. 子查询 (SubQuery)

4.1 什么是子查询

4.2 子查询常见分类

4.2.1 where 型子查询

4.2.2 from 型子查询

4.2.3 exists 型子查询

4.3 子查询总结

5. 数据库设计

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

5.1.1 第一范式 1NF

5.1.2 第二范式 2NF

5.1.3 第三范式 3NF

5.2 数据库反三范式

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 数据库反三范式

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

什么是冗余字段 ?

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

反三范式示例

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

总结

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

最后

以上就是朴实大碗为你收集整理的数据库技术二：（多表，外键，数据库设计）1. 多表2. 多表关系设计3. 多表查询4. 子查询 (SubQuery)5. 数据库设计的全部内容，希望文章能够帮你解决数据库技术二：（多表，外键，数据库设计）1. 多表2. 多表关系设计3. 多表查询4. 子查询 (SubQuery)5. 数据库设计所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。