商品表单MySQL mysql商品表设计思路

转载

definitely 2023-08-23 15:28:29

文章标签 商品表单MySQL mysql 子查询数据外键 文章分类 MySQL 数据库

前言

上个阶段进入数据库阶段学习MySQL阶段 mysql基础与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;

表关系分析

部门表与员工表的关系

商品表单MySQL mysql商品表设计思路_mysql

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

多表设计上的问题

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

-- 插入一条 不存在部门的数据 
INSERT INTO employee (ename,age,dept_id) VALUES('无名',35,3);

商品表单MySQL mysql商品表设计思路_数据_02

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

外键约束

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

创建外键约束

新建表时添加外键

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

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

-- 先删除 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) );

测试插入数据：

-- 正常添加数据 (从表外键 对应主表主键)
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); 

-- 插入一条错误的数据
-- 添加外键约束之后 就会产生一个强制的外键约束检查 保证数据的完整性和一致性
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;

-- 创建表之后添加外键
-- 语法格式 alter table 从表 add CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)

-- 简写 不写外键约束名 自动生成的外键约束 employee_ibfk_1
ALTER TABLE employee ADD FOREIGN KEY(dept_id) REFERENCES department(id)

外键约束的注意事项

从表的外键类型必须与主表的主键类型一致
添加数据时,应该先添加主表的数据

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

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

删除数据的时候要先删除从表中的数据

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

级联删除操作

如果想实现删除主表数据的同时,也删除掉从表数据,可以使用级联删除操作 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
);

-- 添加数据
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;

多表关系设计

实际开发中，一个项目通常需要很多张表才能完成。例如：一个商城项目就需要分类表(category)、
商品表(products)、订单表(orders)等多张表。且这些表的数据之间存在一定的关系，接下来我们一起
学习一下多表关系设计方面的知识

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

表与表之间的三种关系

一对多关系(1:n 常见): 班级和学生部门和员工
一对多建表原则:在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键
多对多关系(n:n 常见): 学生与课程演员和角色
多对多关系建表原则:需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。
一对一关系(1:1 了解): 身份证和人
一对一建表原则:外键唯一主表的主键和从表的外键（唯一），形成主外键关系，外键唯一 UNIQUE

多表查询

什么是多表查询

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

数据准备

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;

观察表查询结果,产生了笛卡尔积 (得到的结果是无法使用的)
笛卡尔积:假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

多表查询的分类

内连接查询

内连接的特点:
通过指定的条件去匹配两张表中的数据, 匹配上就显示,匹配不上就不显示
比如通过: 从表的外键 = 主表的主键方式去匹配
隐式内连接
from子句后面直接写多个表名使用where指定连接条件的这种连接方式是隐式内连接.
使用where条件过滤无用的数据

隐式内连接测试：

-- 隐式内连接
SELECT * FROM products , category WHERE category_id = cid;

-- 2.查询商品表的商品名称 和 价格,以及商品的分类信息
-- 多表查询中 可以使用给表起别名的方式 简化查询
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';

显示内连接测试：

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

-- 2.查询鞋服分类下,价格大于500的商品名称和价格
/*
	查询之前要确定几件事情
		1.查询几张表 products &  category
		2.表的连接条件 p.`category_id` = c.`cid`; 从表.外键 = 主表.主键
		3.查询所用到的字段  商品名称  价格
		4.查询的条件 分类 = 鞋服,  价格 > 500
*/

SELECT 
	p.`pname`,
	p.`price`
FROM products p 
INNER JOIN category c ON p.`category_id` = c.`cid`
WHERE p.`price` > 500 AND c.`cname` = '鞋服';

外连接查询

左外连接
语法格式关键字 left [outer] join
select 字段名 from 左表 left join 右表 on 连接条件
左外连接的特点
以左表为基准匹配右表中的数据如果能匹配上就显示
如果匹配不上, 左表中的数据正常显示,右表数据显示为null
右外连接
语法格式关键字 right [outer] join
select 字段名 from 左表 right join 右表 on 条件
右外连接的特点
以右表为基准匹配左表中的数据如果能够匹配上就显示
如果匹配不到右表中的数据就正常显示左表显示null

外连接测试

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

--  查询每个分类下的商品个数
/*
	1.查询的表
	2.查询的条件 分组 统计
	3.查询的字段 分类 分类下商品个数信息
	4.表的连接条件
*/

SELECT 
	c.`cname`,
	COUNT(p.`pid`)
FROM
-- 表连接
category c  LEFT JOIN products p ON c.`cid` = p.`category_id`
-- 分组
GROUP BY c.`cname`;


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

各种连接方式的总结

商品表单MySQL mysql商品表设计思路_子查询_03

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

子查询 (SubQuery)

什么是子查询

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

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

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

-- 子查询作为查询条件

-- 1. 查询化妆品分类下的 商品名称 商品价格
-- 查询出化妆品分类的 id
SELECT cid FROM category WHERE cname = '化妆品'; -- c003

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

-- 2.获取小于平均价格的商品信息
SELECT 
* 
FROM products
WHERE price < (SELECT AVG(price) FROM products);

子查询的结果作为一张表

语法格式：SELECT 查询字段 FROM （子查询）表别名 WHERE 条件;

-- from型子查询方式 

-- 查询商品中,价格大于500的商品信息,包括 商品名称 商品价格 商品所属分类名称
SELECT * FROM category;

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 函数 ,包含子查询的结果
语法格式：SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;

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

-- 1.查询小于两千的商品的 分类id
SELECT DISTINCT category_id FROM products WHERE price < 2000;

-- 2.根据分类的id 查询 分类的信息,使用in函数, in( c002, c003 )
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.根据 分类id 查找商品信息
SELECT 
	* 
FROM products WHERE category_id IN
(SELECT cid FROM category WHERE cname IN('家电','鞋服'));

子查询的总结

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

数据库设计

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

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

第一范式 1NF

概念:
原子性, 做到列不可拆分
第一范式是最基本的范式。数据库表里面字段都是单一属性的，不可再分, 如果数据表中每个
字段都是不可再分的最小数据单元，则满足第一范式。
示例:
地址信息表中, contry这一列,还可以继续拆分,不符合第一范式

第二范式 2NF

概念:
在第一范式的基础上更进一步，目标是确保表中的每列都和主键相关。
一张表只能描述一件事。
示例:
学员信息表中其实在描述两个事物 , 一个是学员的信息,一个是课程信息
如果放在一张表中,会导致数据的冗余,如果删除学员信息, 成绩的信息也被删除了

商品表单MySQL mysql商品表设计思路_mysql_04

第三范式 3NF

概念:
消除传递依赖
表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放
示例
通过number 与 price字段就可以计算出总金额,不要在表中再做记录(空间最省)

数据库反三范式

概念

反范式化指的是通过增加冗余或重复的数据来提高数据库的读性能
浪费存储空间,节省查询时间 (以空间换时间)

什么是冗余字段 ?

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

反三范式示例

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

总结

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

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：python全局变量实现自加1 python 全局变量声明

下一篇：java数据库大小监控 java数据库调优

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

商品表单MySQL mysql商品表设计思路

商品表单MySQL mysql商品表设计思路

前言

上个阶段 进入数据库阶段 学习MySQL阶段 mysql基础与sql语句

多表

多表简述

单表的问题

解决方案

表关系分析

多表设计上的问题

外键约束

创建外键约束

删除外键约束

外键约束的注意事项

级联删除操作

多表关系设计

表与表之间的三种关系

多表查询

什么是多表查询

数据准备

笛卡尔积

多表查询的分类

内连接查询

外连接查询

各种连接方式的总结

子查询 (SubQuery)

什么是子查询

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

子查询的结果作为一张表

子查询结果是单列多行

子查询的总结

数据库设计

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

第一范式 1NF

第二范式 2NF

第三范式 3NF

数据库反三范式

概念

什么是冗余字段 ?

反三范式示例

总结

51CTO博客

上个阶段进入数据库阶段学习MySQL阶段 mysql基础与sql语句