java之学习记录 2 - 1 - Mysql多表关系&多表查询&数据库设计、规范

本文链接：https://blog.youkuaiyun.com/dear_zx/article/details/110919195

一：多表

1、多表简述

实际开发中，一个项目通常需要很多张表才能完成。
例如一个商城项目的数据库,需要有很多张表：用户表、分类表、商品表、订单表....

2、单表的缺点：

单表的问题：冗余, 同一个字段中出现大量的重复数据

3、解决方案：设计两张表

1) 员工表中有一个字段 dept_id 与部门表中的主键对应 , 员工表的这个字段就叫做外键

2) 拥有外键的员工表被称为从表 , 与外键对应的主键所在的表叫做主表

当我们在员工表的 dept_id 里面输入不存在的部门 id , 数据依然可以添加显然这是不合理的. 要保证员工表所添加的 dept_id , 必须在部门表中存在

解决方案：使用外键约束 ,约束 dept_id ,必须是部门表中存在的 id （ https://blog.youkuaiyun.com/dear_zx/article/details/110881249 中有外键约束介绍）

4、 级联删除操作

如果想实现删除主表数据的同时 , 也删除掉从表数据 , 可以使用级联删除操作

级联删除 关键字
ON DELETE CASCADE

创建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) 
    -- 添加级联删除 
    ON DELETE CASCADE 
);
往表中添加数据后  删除其中一条数据 测试
删除部门（department）编号为2 的记录 
DELETE FROM department WHERE id = 2;
下面截图效果：

员工表中外键值是 2 的记录 , 也被删除了

5、多表关系设计

实际开发中，一个项目通常需要很多张表才能完成。例如：一个商城项目就需要分类表(category)、商品表(products)、订单表(orders)等多张表。且这些表的数据之间存在一定的关系。

表与表之间的三种关系

一对多关系 : 最常见的关系 , 学生对班级 , 员工对部门

多对多关系 : 学生与课程 , 用户与角色

一对一关系 : 使用较少 , 因为一对一关系可以合成为一张表

5.1 一对多关系（常见）

一对多关系（1:n）
- 例如：班级和学生，部门和员工，客户和订单，分类和商品
一对多建表原则
- 在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键

5.2 多对多关系（常见）

多对多（ m:n ）
- 例如：学生和老师，学生和课程，用户与角色
n 多对多关系建表原则
- 需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。

5.3 一对一关系

一对一（ 1:1）：在实际的开发中应用不多 . 因为一对一可以创建成一张表。
一对一建表原则：外键唯一主表的主键和从表的外键（唯一），形成主外键关系，外键唯一 UNIQUE

二：多表查询

1、什么是多表查询

DQL: 查询多张表,获取到需要的数据
比如我们要查询家电分类下都有哪些商品,那么我们就需要查询分类与商品这两张表

2、准备数据：

-- 创建 db3_2 数据库,指定编码 
CREATE DATABASE db3_2 CHARACTER SET utf8;
-- 分类表 (一方 主表)
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('p002','格力空 调',3000,'1','c001'); 
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','美的冰 箱',4500,'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('p005','运动 裤',200,'1','c002'); 
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','T 恤',300,'1','c002'); 
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','冲锋 衣',2000,'1','c002'); 
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','神仙 水',800,'1','c003'); 
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','大 宝',200,'1','c003');

3、迪卡尔积

交叉连接查询,因为会产生笛卡尔积,所以基本不会使用

语法格式：
SELECT 字段名 FROM 表1, 表2;

使用交叉连接查询商品表与分类表
SELECT * FROM category , products;

观察查询结果 , 产生了笛卡尔积 ( 得到的结果是无法使用的 )

笛卡尔积详解：

假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

三：多表查询的分类：

1、内连接查询

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

2、隐式内连接

from子句后面直接写多个表名使用where指定连接条件的这种连接方式是隐式内连接. 使用where条件过滤无用的数据

语法格式：
SELECT 字段名 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` = 'p002';

3、显式内连接：

使用 inner join ...on 这种方式, 就是显式内连接
语法格式： inner 可以省略
SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件
-- 例子：查询所有商品信息和对应的分类信息
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 = '鞋服';

4、外连接查询

4.1 左外连接：

使用 LEFT OUTER JOIN , OUTER 可以省略
左外连接的特点
- 以左表为基准, 匹配右边表中的数据,如果匹配的上,就展示匹配到的数据
- 如果匹配不到, 左表中的数据正常展示, 右边的展示为null.

语法格式：
SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件
-- 左外连接查询 
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`;

4.2 右外连接

右外连接 , 使用 RIGHT OUTER JOIN , OUTER 可以省略
右外连接的特点
- 以右表为基准，匹配左边表中的数据，如果能匹配到，展示匹配到的数据
- 如果匹配不到，右表中的数据正常展示, 左边展示为null

语法格式：
SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件
-- 右外连接查询 
SELECT * FROM products p RIGHT JOIN category c ON p.`category_id` = c.`cid`;

5、各种连接方式的总结

内连接: inner join , 只获取两张表中交集部分的数据.
左外连接: left join , 以左表为基准 ,查询左表的所有数据, 以及与右表有交集的部分
右外连接: right join , 以右表为基准,查询右表的所有的数据,以及与左表有交集的部分

四：子查询(SubQuery)

1、什么是子查询

子查询概念
- 一条select 查询语句的结果, 作为另一条 select 语句的一部分
子查询的特点
- 子查询必须放在小括号中
- 子查询一般作为父查询的查询条件使用
子查询常见分类
- where型子查询: 将子查询的结果, 作为父查询的比较条件
- from型子查询 : 将子查询的结果, 作为一张表,提供给父层查询使用
- exists型子查询: 子查询的结果是单列多行, 类似一个数组, 父层查询使用 IN 函数 ,包含子查询的结果

语法格式：
SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;
-- 例如：通过子查询的方式, 查询价格最高的商品信息
SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products);

2、子查询的结果作为一张表

语法格式：
SELECT 查询字段 FROM （子查询）表别名 WHERE 条件;
-- 例如：查询商品中,价格大于500的商品信息,包括 商品名称 商品价格 商品所属分类名称
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;

注意：当子查询作为一张表的时候，需要起别名，否则无法访问表中的字段。

3、子查询结果是单列多行

子查询的结果类似一个数组, 父层查询使用 IN 函数 ,包含子查询的结果

语法格式：
SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;
-- 例如：查询价格小于两千的商品,来自于哪些分类(名称)
下面sql会报错：Subquery returns more than 1 row
子查询的结果 大于一行  所以下面的sql语句不正确
SELECT * FROM category 
WHERE cid = (SELECT DISTINCT category_id FROM products WHERE price < 2000);

解决方案 ： 使用in函数 in(c002,c003)
-- 子查询获取的是单列多行数据 
SELECT * FROM category 
WHERE cid IN (SELECT DISTINCT category_id FROM products WHERE price < 2000);

4、子查询总结：

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

五：数据库设计

1、数据库三范式（空间最省）：

概念: 三范式就是设计数据库的规则.
- 为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式
满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以此类推。一般说来，数据库只需满足第三范式(3NF）就行了