1 多表
1.1 多表描述
实际开发中,一个项目通常需要很多张表才能完成
例如一个商场项目的数据库,需要有很多张表:用户表、分类表、商品表、订单表…
1.2 单表的缺点
1.2.1 建表
CREATE DATABASE db3 CHARACTER SET utf8;
-- 创建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, '销售部', '深圳');
1.2.2 单表的问题
冗余: 同一个字段出现大量的重复数据
1.3 解决方案
1.3.1 设计为两张表
1 多表设计方式
department 部门表:id,dep_name,dep_location
employee 员工表:eid,ename,age,dep_id
2 建表
-- 创建部门表
-- 一方,主表
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
)
-- 添加部门表数据 添加两个部门
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;
1.3.2 表关系分析
部门表与员工表的关系:
1 员工表中有一个字段dept_id与部门表中的主键对应,员工表的这个字段就叫做外键
2 拥有外键的员工表被称为从表,与外键对应的主键所在的表叫做主表
1.3.3 多表设计上的问题
当我们在员工表的dept_id里面输入不存在的部门id,数据依然可以添加 这显然是不合理的
-- 插入一条不存在部门的数据
INSERT INTO employee (ename,age,dept_id) VALUES ('无名',35,3);
实际上我们应该保证员工表所添加的dept_id,必须在部门表中存在。
解决方案:使用外键约束,约束dept_id必须是部门表中存在的id
1.4 外键约束
创建外键约束:
/*
外键约束
作用:外键约束可以让两张表之间产生有一个对应的关系,从而保证了主从表引用的完整性
外键
外键指的是在从表中与主表的主键对应的字段
主表和从表
主表:主键id所在的表,一的一方
从表:外键字段所在的表,多的一方
添加外键约束的语法格式
1.创建表的时候添加外键
create table 表名(
字段...
[constraint] [外键约束名] foreign key(外键字段名) references 主表(主键字段)
);
*/
-- 创建员工表 添加外键
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);
1.4.3 删除外键约束
语法格式:
alter table 从表 drop foreign key 外键约束名称;
(1)删除外键约束
-- 删除employee表中的外键约束,外键约束名 emp_dept_fk
ALTER TABLE enployee DROP FOREIGN KEY emp_dept_fk;
(2)再将外键 添加回来
语法格式
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);
可以省略外键名称,系统会自动生成一个
ALTER TABLE employee ADD FOREIGN KEY (dept_id) REFERENCE department(id);
1.4.4 外键约束的注意事项
(1)从表外键类型必须与主表主键类型一致 否则创建失败
(2)添加数据时,应该先添加主表中的数据
-- 添加一个新的部门
INSERT INTO department(dep_name,dep_location) VALUES('市场部','北京');
-- 添加一个属于市场部的员工
INSERT INTO employee(ename,age,dept_id) VALUES('老胡',24,3);
(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;
1.4.5 级联删除操作(了解)
如果想实现删除主表数据的同时,也删除掉从表数据,可以使用级联删除操作
级联删除
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
);
-- 添加数据
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;
员工表中外键值是2的记录也被删除了
2 多表关系设计
实际开发中,一个项目通常需要很多张表才能完成,例如:一个商场项目就需要分类表(category)、商品表(products)、订单表(orders)等多张表,且这些表的数据之间存在一定的关系,接下来我们一起学习一下多表关系设计方面的知识
表与表之间的三种关系 |
---|
一对多关系:最常见的关系,学生对班级,员工对部门 |
多对多关系:学生与课程,用户与角色 |
一对一关系:使用较少,因对一对一关系可以合成为一张表 |
2.1 一对多关系(常见)
一对多关系(1:n)
=》例如:班级和学生,部门和员工,客户和订单,分类和商品
一对多建表原则
=》在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键
2.2 多对多关系(常见)
多对多(m:n)
=》例如:老师和学生,学生和课程,用户和角色
多对多建表原则
=》需要创建第三张表,中间表中至少两个字段,这两个字段分别作为外键指向各自一方的主键
2.3 一对一关系(了解)
一对一(1:1)
=》在实际的开发中应用不多,因为一对一可以创建成一张表
一对一建表原则
=》外键唯一 主表的主键和从表的外键(唯一),形成主外键关系,外键唯一 UNIQUE
2.4 设计 省&市表
(1)分析:省和市之间的关系是一对多关系,一个省包含多个市
(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 province_city_fk FOREIGN KEY (pid) REFERENCES province (id)
);
(3)查看表关系
2.5 设计 演员与角色表
(1)分析:演员与角色是多对多关系,一个演员可以饰演多个角色,一个角色同样可以被不同的演员扮演
(2)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 (aid) REFERENCES role (id);
(3)查看表关系
3 多表查询
3.1 什么是多表查询
DQL:查询多张表,获取到需要的数据
比如 我们要查询家电分类下 都有哪些商品,那么我们就需要查询分类与商品这两张表
3.2 数据准备
-- 创建db3_2数据库 指定编码
CREATE DATABASE db3_2 CHARACTER SET utf8;
USE db3_2;
#分类表(一方 主表)
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.3 笛卡尔积
交叉连接查询,因为会产生笛卡尔积,所以基本不会使用
(1)语法格式
SELECT 字段名 FROM 表1,表2;
(2)使用交叉连接查询 商品表与分类表
SELECT * FROM category,products;
(3)观察查询结果,产生了笛卡尔积(得到的结果是无法使用的)
笛卡尔积:
假设集合A={a,b},集合B={0,1,2},则两个集合的笛卡尔积为{(a.0),(a,1),(a,2),(b,0),(b,1),(b,2)。
3.4 多表查询的分类
3.4.1 内连接查询
内连接的特点:通过指定的条件去匹配两张表中的数据,匹配上就显示,匹配不上就不显示
比如通过:从表的外键=主表的主键 方式去匹配
3.4.1.1 隐式内连接
from子句 后面直接写 多个表名 使用where指定连接条件的 这种连接方式是 隐式内连接
使用where条件过滤无用的数据
语法格式:
SELECT 字段名 FROM 左表,右表 WHERE 连接条件;
(1)查询所有商品信息和对应的分类信息
#隐式内连接
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`;
-- 引号``是自动加上的,不用管
(3)查询格力空调是属于哪一分类下的产品
#查询 格力空调是属于哪一分类下的商品
SELECT p.`pname`,c.`cname` FROM products p,category c WHERE P.`category_id`=c.`cid` AND p.`pid`=`p002`;
3.4.1.2 显式内连接
使用 inner join…on 这种方式,就是显式内连接
语法格式
SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件;
-- inner可以省略
(1)查询所有商品信息和对应的分类信息
# 显式内连接查询
SELECT * FROM products p INNER JOIN category c ON p.category_id = c.cid;
(2)查询鞋服分类下,价格大于500的商品名称和价格
# 查询鞋服分类下,价格大于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 = '鞋服';
输出结果为:
3.4.2 外连接查询
3.4.2.1 左外连接
左外连接,使用LEFT OUTER JOIN,OUTER可以省略
左外连接的特点:
以左表为基准,匹配右边表中的数据,如果匹配的上,就展示匹配到的数据;
如果匹配不到,左表中的数据正常展示,右边的展示为null;
(1)语法格式
SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件;
-- 左外连接查询
SELECT * FROM category c LEFT JOIN products p ON c.`cid`=p.`category_id`;
(2)左外连接,查询每个分类下的商品个数
# 查询每个分类下的商品个数
/*
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`;
3.4.2.2 右外连接
右外连接,使用RIGHT OUTER JOIN,OUTER 可以省略
右外连接的特点:
以右表为基准,匹配左边表中的数据,如果能匹配到,展示匹配到的数据
如果匹配不到,右表的数据正常展示,左表展示为null
(1)语法格式
SELECT 字段名 FROM 左表 RIGHT [OUTER] JOIN 右表 ON 条件;
SELECT * FROM products p RIGHT JOIN category c ON p.`category_id` = c.`cid`;
内连接:inner join,只获取两张表中 交集部分的数据
左外连接:left join,以左表为基准,查询左表的所有数据,以及与右表有交集的部分
右外连接:right join,以右表为基准,查询右表的所有数据,以及与左表有交集的部分
4 子查询(SubQuery)
4.1 什么是子查询
子查询概念:一条select查询语句的结果,作为另一条select语句的一部分
子查询的特点:
=》子查询必须放在小括号中
=》子查询一般作为父查询的查询条件使用
子查询常见分类
=》where型子查询: 将子查询的结果,作为父查询的比较条件
=》from型子查询: 将子查询的结果,作为一张表,提供给父层查询使用
=》exists型子查: 子查询的结果是单列多行,类似一个数组,父层查询使用IN函数,包含子查询的结果
4.2 子查询的结果作为查询条件
语法格式:
SELECT 查询字段 FROM 表 WHERE 字段 = (子查询) ;
(1)通过子查询的方式,查询价格最高的商品信息
# 通过子查询的方式,查询价格最高的商品信息
-- 1.先查询出最高价格
SELECT MAX(price) FROM products;
-- 2.将最高价格作为条件,获取商品信息
SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products);
(2)查询化妆品分类下的商品名称和商品价格
# 查询化妆品分类下的商品名称 商品价格
-- 先查出化妆品分类的 id
SELECT cid FROM category WHERE cname = '化妆品';
-- 根据分类id,去商品表中查询对应的商品信息
SELECT
p.`pname`,
p.`price`
FROM products p
WHERE p.`category_id` = (SELECT cid FROM category WHERE cname = '化妆品');
3 查询小于平均价格的商品信息
-- 1.查询平均价格
SELECT AVG(price) FROM products;
-- 2.查询小于平均价格的商品
SELECT * FROM products WHERE price < (SELECT AVG(price) FROM products);
4.3 子查询的结果作为一张表
语法格式:
SELECT 查询字段 FROM (子查询) 表别名 WHERE 条件;
(1)查询商品中,价格大于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.4 子查询结果是单列多行
子查询的结果类似一个数组,父层查询使用IN函数,包含子查询的结果
语法格式
SELECT 查询字段 FROM 表 WHERE 字段 IN (子查询);
(1)查询价格小于两千的商品,来自于哪些分类(名称)
#查询价格小于两千的商品,来自于哪些分类(名称)
-- 先查询价格小于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)
-- 子查询获取的是单列多行数据
SELECT * FROM category
WHERE cid IN (SELECT DISTINCT category_id FROM products WHERE price < 2000);
(2)查询家电类与鞋服类下面的全部商品信息
#查询家电类与鞋服类下面的全部商品信息
-- 先查询出家电与鞋服类的分类ID
SELECT cid FROM category WHERE cname IN ('家电','鞋服');
-- 根据cid查询分类下的商品信息
SELECT * FROM products
WHERE category_id IN (SELECT cid FROM category WHERE cname IN ('家电','鞋服'));
4.5 子查询总结
1 子查询如果查出的是一个字段(单列),那就在where后面作为条件使用
2 子查询如果查询出的是多个字段(多列),就当做一张表使用(要起别名)
5 数据库设计
5.1 数据库三范式(空间最省)
概念:三范式就是设计数据库的原则
=》为了建立冗余较小、结构合理的数据库,设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库,必须满足一定的范式;
=》满足最低要求的范式是第一范式(1NF),在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF),其余范式以此类推,一般来说,数据库只需满足第三范式(3NF)就行了;
5.1.1 第一范式 1NF
概念:
=》原子性,做到列不可拆分
=》第一范式是最基本的范式,数据库表里面字段都是单一属性的,不可再分,如果数据表中每个字段都是不可再分的最小数据单元,则满足第一范式。
实例:地址信息表中,country这一列还可以继续拆分,不符合第一范式
5.1.2 第二范式 2NF
概念:
=》在第一范式的基础上更进一步,目标是确保表中的每列都和主键相关
=》一张表只能描述一件事
实例:
=》学员信息表中其实在描述两个事物,一个是学员的信息,一个是课程信息
=》如果放在一张表中,会导致数据的冗余,如果删除学员信息,成绩的信息也被删除了
5.1.3 第三范式3NF
概念:
=》消除传递依赖
=》表的信息,如果能够被推导出来,就不应该单独的设计一个字段来存放
示例:通过number与price字段就可以计算出总金额,不要在表中再做记录(空间最省)
5.2 数据库反三范式
5.2.1 概念
反范式化指的是通过增加冗余或重复的数据来提高数据库的读性能
浪费存储空间,节省查询时间(以空间换时间)
5.2.2 什么是冗余字段?
设计数据库,某一个字段属于一张表,但它同时出现在另一个或多个表,且完全等同于它在其本来所属表的意义表示,那么这个字段就是一个冗余字段
5.2.3 反三范式实例
两张表,用户表、订单表,用户表中有字段name,而订单表中也存在字段name
使用场景:
=》当需要查询"订单表""所有数据并且只需要"用户表"的name字段时,没有冗余字段,就需要去join连接用户表,假设表中数据量非常的大,那么这次连接查询就会非常大的消耗系统的性能;
=》这时候冗余的字段就可以派上用场了,有冗余字段我们差一张表就可以了
5.2.4 总结
创建一个关系型数据库设计,我们有两种选择
1 尽量遵循范式理论的规约,尽可能的少的冗余字段,让数据库设计看起来精致、优雅、让人心醉
2 合理的加入冗余字段这个润滑剂,减少join,让数据库执行性能更高更快