多表和外键

多表开发

实际开发中，一个项目通常需要很多张表才能完成

单表的缺点

冗余, 同一个字段中出现大量的重复数据

sql

# 创建一个数据库db3
CREATE DATABASE db3 CHARACTER SET utf8;

USE db3;

# 创建emp员工表	主键自增
CREATE TABLE emp (
  eid INT PRIMARY KEY AUTO_INCREMENT,
  ename VARCHAR (20),
  age INT,
  dep_name VARCHAR (20),
  dep_location VARCHAR (20)
);

# 添加数据
INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('张百万', 20, '研发 部', '广州'); 
INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('赵四', 21, '研发 部', '广州'); 
INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('广坤', 20, '研发 部', '广州'); 
INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('小斌', 20, '销售 部', '深圳'); 
INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('艳秋', 22, '销售 部', '深圳'); 
INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('大玲子', 18, '销售 部', '深圳');

解决方案

多表方案设计

sql

# 创建部门表 -- 一方,主表
CREATE TABLE department (
  id INT PRIMARY KEY AUTO_INCREMENT,
  dep_name VARCHAR (30),
  dep_location VARCHAR (30)
);

# 创建员工表 -- 多方 ,从表
CREATE TABLE employee (
  eid INT PRIMARY KEY AUTO_INCREMENT,
  ename VARCHAR (20),
  age INT,
  dept_id INT
);

# 添加2个部门
INSERT INTO department VALUES (NULL, '研发部', '广州'),(NULL, '销售部', '深圳');

SELECT * FROM department;


-- 添加员工,dep_id表示员工所在的部门 
INSERT INTO employee (ename, age, dept_id) VALUES ('张百万', 20, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('赵四', 21, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('广坤', 20, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('小斌', 20, 2); 
INSERT INTO employee (ename, age, dept_id) VALUES ('艳秋', 22, 2); 
INSERT INTO employee (ename, age, dept_id) VALUES ('大玲子', 18, 2); 

SELECT * FROM employee;

部门表与员工表的关系

员工表中有一个字段 dept_id 与部门表中的主键对应,员工表的这个字段就叫做外键
拥有外键的员工表被称为从表 , 与外键对应的主键所在的表叫做主表

多表设计上的问题

当我们在员工表的 dept_id 里面输入不存在的部门id ,数据依然可以添加显然这是不合理的。

实际上我们应该保证,员工表所添加的 dept_id , 必须在部门表中存在
使用外键约束,约束 dept_id ,必须是部门表中存在的 id

外键约束

外键指的是在 从表 中与 主表 的主键对应的那个字段

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

主表: 主键 id 所在的表, 约束别人的表
从表: 外键所在的表多, 被约束的表

创建外键约束

重新创建 employee 表, 添加外键约束

sql

# 创建部门表 -- 一方,主表
CREATE TABLE department (
  id INT PRIMARY KEY AUTO_INCREMENT,
  dep_name VARCHAR (30),
  dep_location VARCHAR (30)
);

-- 先删除 employee表 
DROP TABLE employee; 

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

-- 正常添加数据 (从表外键 对应主表主键) 
INSERT INTO employee (ename, age, dept_id) VALUES ('张百万', 20, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('赵四', 21, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('广坤', 20, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('小斌', 20, 2); 
INSERT INTO employee (ename, age, dept_id) VALUES ('艳秋', 22, 2); 
INSERT INTO employee (ename, age, dept_id) VALUES ('大玲子', 18, 2);

-- 插入一条有问题的数据 (部门id不存在) 
-- Cannot add or update a child row: a foreign key constraint fails 
INSERT INTO employee (ename, age, dept_id) VALUES ('错误', 18, 3);

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

删除或添加外键约束

sql

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

再将外键添加回来

sql

ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主 键字段名);

-- 可以省略外键名称, 系统会自动生成一个 
ALTER TABLE employee ADD FOREIGN KEY (dept_id) REFERENCES department (id);

外键约束的注意事项

从表外键类型必须与主表主键类型一致否则创建失败

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

sql

-- 添加一个新的部门 
INSERT INTO department(dep_name,dep_location) VALUES('市场部','北京'); 

-- 添加一个属于市场部的员工 
INSERT INTO employee(ename,age,dept_id) VALUES('老胡',24,3);

删除数据时,应该先删除从表中的数据

sql

-- 删除数据时 应该先删除从表中的数据 
-- 报错 Cannot delete or update a parent row: a foreign key constraint fails 
-- 报错原因 不能删除主表的这条数据,因为在从表中有对这条数据的引用 
DELETE FROM department WHERE id = 3;

先删除从表的数据，然后删除主表的数据

sql

-- 先删除从表的关联数据 
DELETE FROM employee WHERE dept_id = 3; 

-- 再删除主表的数据 
DELETE FROM department WHERE id = 3;

级联删除操作(了解)

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

sql

-- 级联删除 
ON DELETE CASCADE

删除 employee 表,重新创建,添加级联删除

删除部门表中的数据之后，员工表中对应的数据，也会被删除

sql

-- 重新创建添加级联操作 
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 
);

-- 添加数据 
INSERT INTO employee (ename, age, dept_id) VALUES ('张百万', 20, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('赵四', 21, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('广坤', 20, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('小斌', 20, 2); 
INSERT INTO employee (ename, age, dept_id) VALUES ('艳秋', 22, 2); 
INSERT INTO employee (ename, age, dept_id) VALUES ('大玲子', 18, 2);

-- 删除部门编号为2 的记录 ,员工表对应的数据也会删除
DELETE FROM department WHERE id = 2;

多表关系设计

表与表之间的三种关系

一对多关系: 最常见的关系, 学生对班级,员工对部门
多对多关系: 学生与课程, 用户与角色
一对一关系: 使用较少,因为一对一关系可以合成为一张表

一对多关系

例如部门表-员工表，见上面

分析: 省和市之间的关系是一对多关系,一个省包含多个市

sql

#创建省表 (主表,注意: 一定要添加主键约束) 
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) 
);

多对多关系

需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键

分析: 演员与角色是多对多关系, 一个演员可以饰演多个角色, 一个角色同样可以被不同的演员扮演

sql

#创建演员表 
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 
);

-- 为中间表的aid字段,添加外键约束 指向演员表的主键 
ALTER TABLE actor_role ADD FOREIGN KEY(aid) REFERENCES actor(id); 

-- 为中间表的rid字段, 添加外键约束 指向角色表的主键 
ALTER TABLE actor_role ADD FOREIGN KEY(rid) REFERENCES role(id);

一对一关系(了解)

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

多表查询

概念

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

准备数据

sql

-- 创建 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) 
);

插入数据

sql

#分类数据 
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');

多表查询的分类

内连接查询

内连接的特点:

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

隐式内连接

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

sql

SELECT * FROM category , products; 

# 隐式内连接  cid 是分类表的 id
SELECT * FROM products,category WHERE category_id = cid;

查询商品表的商品名称和价格,以及商品的分类信息。可以通过给表起别名的方式, 方便我们的查询(有提示)

sql

SELECT p.`pname`, p.`price`, c.`cname` FROM products p , category c WHERE p.`category_id` = c.`cid`;、

# 查询 格力空调是属于哪一分类下的商品   格力空调的pid为 p002
SELECT p.`pname`,c.`cname` FROM products p , category c WHERE p.`category_id` = c.`cid` AND p.`pid` = 'p002';

显式内连接

使用 inner join ...on 这种方式, 就是显式内连接 -- inner 可以省略

查询所有商品信息和对应的分类信息

sql

# 显式内连接查询 
SELECT * FROM products p INNER JOIN category c ON p.category_id = c.cid;

查询鞋服分类下,价格大于 500 的商品名称和价格

sql

# 查询鞋服分类下,价格大于500的商品名称和价格 
-- 我们需要确定的几件事 
-- 1.查询几张表 products & category 
-- 2.表的连接条件 从表.外键 = 主表的主键 
-- 3.查询的条件 cname = '鞋服' and price > 500 
-- 4.要查询的字段 pname price 
SELECT p.pname, p.price FROM products p INNER JOIN category c ON p.category_id = c.cid WHERE p.price > 500 AND cname = '鞋服';

外连接查询

左外连接

左外连接 , 使用 LEFT OUTER JOIN , OUTER 可以省略

左外连接的特点

以左表为基准, 匹配右边表中的数据,如果匹配的上,就展示匹配到的数据
如果匹配不到, 左表中的数据正常展示, 右边的展示为 null.

sql

-- 左外连接查询 
SELECT * FROM category c LEFT JOIN products p ON c.`cid`= p.`category_id`;

左外连接, 查询每个分类下的商品个数

sql

# 查询每个分类下的商品个数 
/* 
1.连接条件: 主表.主键 = 从表.外键 
2.查询条件: 每个分类 需要分组 
3.要查询的字段: 分类名称, 分类下商品个数 
*/

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

sql

-- 右外连接查询 
SELECT * FROM products p RIGHT JOIN category c ON p.`category_id` = c.`cid`;

各种连接方式的总结

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

子查询 (SubQuery)

一条 select 查询语句的结果, 作为另一条 select 语句的一部分
子查询必须放在小括号中
子查询一般作为父查询的查询条件使用

子查询常见分类

where 型子查询: 将子查询的结果, 作为父查询的比较条件

from 型子查询 : 将子查询的结果, 作为一张表,提供给父层查询使用

exists 型子查询: 子查询的结果是单列多行, 类似一个数组, 父层查询使用 IN 函数 ,包含子查询的结果

子查询的结果作为查询条件

通过子查询的方式, 查询价格最高的商品信息

sql

# 通过子查询的方式, 查询价格最高的商品信息 
-- 1.先查询出最高价格 
SELECT MAX(price) FROM products; 

-- 2.将最高价格作为条件,获取商品信息 
SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products);

查询化妆品分类下的商品名称商品价格

sql

#查询化妆品分类下的 商品名称 商品价格 
-- 先查出化妆品分类的 id ，根据名称查找到 cid
SELECT cid FROM category WHERE cname = '化妆品'; 

-- 根据分类 cid ,去商品表中查询对应的商品信息 
SELECT 
		p.`pname`, 
		p.`price` 
FROM products p WHERE p.`category_id` = (SELECT cid FROM category WHERE cname = '化妆品');

查询小于平均价格的商品信息

sql

-- 1.查询平均价格 
SELECT AVG(price) FROM products; -- 1866 

-- 2.查询小于平均价格的商品 
SELECT * FROM products WHERE price < (SELECT AVG(price) FROM products);

子查询的结果作为一张表

查询商品中,价格大于500的商品信息,包括商品名称商品价格商品所属分类名称

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

sql

-- 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;

子查询结果是单列多行

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

sql

SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;

查询价格小于两千的商品,来自于哪些分类(名称)

sql

# 查询价格小于两千的商品,来自于哪些分类(名称) 
-- 先查询价格小于2000 的商品的,分类ID 
SELECT DISTINCT category_id FROM products WHERE price < 2000; 

-- 在根据分类的id信息,查询分类名称 
-- 报错: Subquery returns more than 1 row 
-- 子查询的结果 大于一行 
SELECT * FROM category WHERE cid = (SELECT DISTINCT category_id FROM products WHERE price < 2000);

使用 in 函数, in ( c002, c003 )

sql

-- 子查询获取的是单列多行数据 
SELECT * FROM category WHERE cid IN (SELECT DISTINCT category_id FROM products WHERE price < 2000);

子查询总结

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

版本差异(MySQL 5.7 → 8.0/8.4)

特性	旧版（本文编写时，MySQL 5.7）	当前（MySQL 8.0/8.4 LTS）
默认字符集	utf8（需显式配置 utf8mb4）	utf8mb4（MySQL 8.0 起默认）
索引	普通 B+Tree	降序索引、隐藏索引、函数索引（8.0+）
SQL 能力	常规查询	递归 CTE、窗口函数（8.0+）
版本策略	5.7	8.0（主流）/ 8.4 LTS / 9.x（创新版）
Java 驱动	mysql-connector-java 5.x/8.0	mysql-connector-j 8.x/9.x

本文基于 MySQL 5.7 编写，核心概念（索引、事务、锁、MVCC、InnoDB）在 8.0/8.4 中依然适用；8.0 的默认字符集、隐藏索引与 SQL 增强（CTE/窗口函数）是升级后的主要差异。