【MySQL多表&外键&数据库设计】

最新推荐文章于 2023-12-27 14:33:19 发布

远道学java

最新推荐文章于 2023-12-27 14:33:19 发布

阅读量389

点赞数 2

文章标签：数据库 mysql database

本文链接：https://blog.csdn.net/xmcxmc___/article/details/123315888

版权

多表

简述

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

单表的缺点

数据准备

# 创建一个数据库 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, '销售 部', '深圳');

单表的问题

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

解决方案

设计为两张表

多表方式设计
department 部门表 : id, dep_name, dep_location
employee 员工表: eid, ename, age, dep_id

-- 创建部门表 
-- 一方,主表 
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, '销售部', '深圳'); 

-- 添加员工,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);

表关系分析

在这里插入图片描述

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

多表设计上的问题

当我们在员工表的 dept_id 里面输入不存在的部门id ,数据依然可以添加显然这是不合理的.
实际上我们应该保证,员工表所添加的 dept_id , 必须在部门表中存在.
使用外键约束,约束 dept_id ,必须是部门表中存在的id

外键约束

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

创建外键约束

新建表时添加外键
[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
已有表添加外键
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);

-- 创建部门表 
-- 一方,主表 
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,
	CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
);

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

-- 添加员工,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);

-- 报错 Cannot add or update a child row: a foreign key constraint fails 
INSERT INTO employee (ename, age, dept_id) VALUES ('大玲子', 18, 3);

删除外键约束

语法格式
alter table 从表 drop foreign key 外键约束名称

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

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

外键约束的注意事项

从表外键类型必须与主表主键类型一致否则创建失败.
添加数据时, 应该先添加主表中的数据.
删除数据时,应该先删除从表中的数据.

级联删除操作(了解)

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

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

多表关系设计

一对多

-- 创建省表
CREATE TABLE province(
	id INT PRIMARY KEY AUTO_INCREMENT,
	pname VARCHAR(20),
	discription VARCHAR(20)
);

-- 创建市表
CREATE TABLE city(
	cid INT PRIMARY KEY AUTO_INCREMENT,
	cname VARCHAR(20),
	discription VARCHAR(20),
	pid INT,
	-- 创建外键约束
	CONSTRAINT pro_id_fk FOREIGN KEY(pid) REFERENCES province(id)
);

多对多

-- 创建演员表
CREATE TABLE actor(
	id INT PRIMARY KEY AUTO_INCREMENT,
	aname VARCHAR(10)
);

-- 创建角色表
CREATE TABLE role(
	id INT PRIMARY KEY AUTO_INCREMENT,
	rname VARCHAR(10)
);

-- 创建中间表
CREATE TABLE actor_role(
	id INT PRIMARY KEY AUTO_INCREMENT,
	aid INT,
	rid INT,
	-- 添加外键约束
	CONSTRAINT actor_id_fk FOREIGN KEY(aid) REFERENCES actor(id),
	CONSTRAINT role_id_fk FOREIGN KEY(rid) REFERENCES role(id)
);
-- 或者用下面的方式添加外键约束
ALTER TABLE actor_role ADD CONSTRAINT actor_id_fk FOREIGN KEY(aid) REFERENCES actor(id);
ALTER TABLE actor_role ADD CONSTRAINT role_id_fk FOREIGN KEY(rid) REFERENCES role(id);

多表查询

数据准备

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

笛卡尔积

在这里插入图片描述

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

语法格式
SELECT 字段名 FROM 表1, 表2;
使用交叉连接查询商品表与分类表
SELECT * FROM category , products;
观察查询结果,产生了笛卡尔积 (得到的结果是无法使用的)

多表查询的分类

内连接查询

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

隐式内连接

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

显式内连接

使用 inner join …on 这种方式, 就是显式内连接
语法格式：SELECT 字段名 FROM 左表 [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 c.cname = '鞋服' AND p.price > 500;

外连接查询

左外连接

左外连接 , 使用 LEFT OUTER JOIN , OUTER 可以省略
特点：
以左表为基准, 匹配右边表中的数据,如果匹配的上,就展示匹配到的数据
如果匹配不到, 左表中的数据正常展示, 右边的展示为null.
语法格式
SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件

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

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

在这里插入图片描述

右外连接

右外连接 , 使用 RIGHT OUTER JOIN , OUTER 可以省略
特点：
以右表为基准，匹配左边表中的数据，如果能匹配到，展示匹配到的数据
如果匹配不到，右表中的数据正常展示, 左边展示为null
语法格式
SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件

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

在这里插入图片描述

各种连接方式的总结

在这里插入图片描述

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

子查询 (SubQuery)

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

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

语法格式
SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;

# 通过子查询的方式, 查询价格最高的商品信息
SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products);

# 查询化妆品分类下的 商品名称 商品价格
SELECT
	pname,
	price
FROM products WHERE category_id = (SELECT cid FROM category WHERE cname = '化妆品');

# 查询小于平均价格的商品信息
SELECT * FROM products WHERE price < (SELECT AVG(price) FROM products);

子查询的结果作为一张表

# 查询商品中,价格大于500的商品信息,包括 商品名称 商品价格 商品所属分类名称
SELECT 
	p.pname,
	p.price,
	c.cname
FROM products p 
INNER JOIN (SELECT * FROM category) c ON p.category_id = c.cid
WHERE price > 500;

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

子查询结果是单列多行

子查询的结果类似一个数组, 父层查询使用 IN 函数 ,包含子查询的结果
语法格式：
SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;

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

# 查询家电类 与 鞋服类下面的全部商品信息
SELECT * FROM products 
WHERE category_id IN (SELECT cid FROM category WHERE cname IN ('家电', '鞋服'));

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

数据库设计

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

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

第一范式 1NF

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

第二范式 2NF

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

示例：
学员信息表中其实在描述两个事物 , 一个是学员的信息,一个是课程信息
如果放在一张表中,会导致数据的冗余,如果删除学员信息, 成绩的信息也被删除了
在这里插入图片描述

第三范式 3NF

消除传递依赖
表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放

示例：
通过number 与 price字段就可以计算出总金额,不要在表中再做记录(空间最省)
在这里插入图片描述

数据库反三范式

反范式化指的是通过增加冗余或重复的数据来提高数据库的读性能
浪费存储空间,节省查询时间 (以空间换时间)
冗余字段：设计数据库时，某一个字段属于一张表，但它同时出现在另一个或多个表，且完全等同于它在其本来所属表的意义表示，那么这个字段就是一个冗余字段
反三范式示例

当需要查询“订单表”所有数据并且只需要“用户表”的name字段时, 没有冗余字段就需要去join
连接用户表,假设表中数据量非常的大, 那么会这次连接查询就会非常大的消耗系统的性能.
这时候冗余的字段就可以派上用场了, 有冗余字段我们查一张表就可以了.

创建一个关系型数据库设计，我们有两种选择
1，尽量遵循范式理论的规约，尽可能少的冗余字段，让数据库设计看起来精致、优雅、让人心醉。
2，合理的加入冗余字段这个润滑剂，减少join，让数据库执行性能更高更快。

远道学java

关注

2
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【MySQL多表&外键&数据库设计】

多表简述实际开发中，一个项目通常需要很多张表才能完成。例如一个商城项目的数据库,需要有很多张表：用户表、分类表、商品表、订单表…单表的缺点数据准备# 创建一个数据库 db3CREATE DATABASE db3 CHARACTER SET utf8;USE db3;-- 创建emp表主键自增 CREATE TABLE emp( eid INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(20), age INT , dep_nam
复制链接

扫一扫