目录
MySQL多表&外键&数据库设计
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 设计为两张表
-- 创建部门表
-- 一方,主表
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;
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 外键约束
1.4.1 什么是外键
/*
外键约束
作用: 外键约束可以让两张表之间产生一个对应的关系, 从而保证主从表引用的完整性
外键: 指的是在从表中与主表对应的字段
主表和从表
主表: 主键id所在的表, 一的一方
从表: 外键字段所在的表, 多的一方
*/
1.4.2 创建外键约束
/*
添加外键约束的语法格式
1, 创建表的时候添加外键
create table 表名 (
字段...
[constraint] [外键约束名] foreign key(外键字段名) references 主表(主键字段)
);
2, 已有表中添加外键
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);
*/
-- 创建员工表 添加外键
CREATE TABLE employee(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARBINARY(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);
-- 添加外键约束之后, 就会产生一个强制的外键约束检查 保证数据的完整性和一致性
-- 插入一条有问题的数据 (部门id不存在)
-- Cannot add or update a child row: a foreign key constraint fails
INSERT INTO employee (ename, age, dept_id) VALUES ('错误', 18, 3);
1.4.3 删除外键约束
/*
删除外键约束
语法格式
alter table 从表 drop foreign key 外键约束的名称
*/
-- 删除employee表中的外键
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;
-- 创建表之后,添加外键
-- 语法格式: alter table 表名 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主 键字段名);
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dept_id) REF ERENCEs department(id);
-- 简写, 不写外键约束名, 自动生成 employee_ibfk_1 外键约束名
ALTER TABLE employee ADD FOREIGN KEY (dept_id) REFERENCES department(id);
1.4.4 外键约束的注意事项
/*
外键约束的注意事项
1, 从表的外键类型, 必须与主表的主键类型一致,否则创建失败
2, 添加数据时,应该先添加主表的数据,再添加从表的数据
3, 删除数据时,先删除从表中的数据
*/
-- 添加一个新的部门
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;
1.4.5 级联删除操作(了解)
- 如果想实现删除主表数据的同时,也删除掉从表数据,可以使用级联删除操作
/*
级联删除
指的是, 在删除主表的数据的同时, 可以删除与之相关的从表中的数据
实现语句: 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 的记录
-- employee中的数据 也会同步删除
DELETE FROM department WHERE id = 2;
2. 多表关系设计
/*
表与表之间的三种关系
1, 一对多关系(1vN 常见): 班级和学生 部门和员工
2, 多对多关系(NvN 常见): 学生与课程 演员和角色
3, 一对一关系(1v1 了解): 身份证和个人
*/
2.1 一对多关系(常见)
2.2 多对多关系(常见)
2.3 一对一关系(了解)
- 一对一(1:1) 在实际的开发中应用不多.因为一对一可以创建成一张表。
- 一对一建表原则 外键唯一 主表的主键和从表的外键(唯一),形成主外键关系,外键唯一 UNIQUE
2.4 设计 省&市表
-- 创建省表 主表, 1的一方
USE db3;
CREATE TABLE province(
id INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(20),
pdesc VARCHAR(20)
);
-- 创建市表, 外键指向id
CREATE TABLE city(
id INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(20),
cdesc VARCHAR(20),
pid INT,
-- 添加外键约束
CONSTRAINT pro_city_fk FOREIGN KEY(pid) REFERENCES province(id)
);
ALTER TABLE city CHANGE id cid INT;
SELECT * FROM city;
2.5 设计 演员与角色表
-- 多对多关系, 演员与角色表
-- 创建演员表
CREATE TABLE actor(
a_id INT PRIMARY KEY AUTO_INCREMENT,
a_name VARCHAR(20)
);
-- 创建角色表
CREATE TABLE role(
r_id INT PRIMARY KEY AUTO_INCREMENT,
r_name VARCHAR(20)
);
-- 创建中间表
CREATE TABLE media(
-- 中间表的主键
m_id INT PRIMARY KEY AUTO_INCREMENT,
-- 指向 actor的主键
aid INT,
-- 指向 role的主键
rid INT,
CONSTRAINT a_m_fk FOREIGN KEY(aid) REFERENCES actor(a_id),
CONSTRAINT r_m_fk FOREIGN KEY(rid) REFERENCES role(r_id)
);
-- 重命名中间表
RENAME TABLE media TO actor_role;
-- 删除中间表外键 约束
ALTER TABLE actor_role DROP FOREIGN KEY a_m_fk;
ALTER TABLE actor_role DROP FOREIGN KEY r_m_fk;
-- 添加中间表外键约束
ALTER TABLE actor_role ADD CONSTRAINT a_m_fk FOREIGN KEY(aid) REFERENCES actor(a_id);
ALTER TABLE actor_role ADD CONSTRAINT r_m_fk FOREIGN KEY(rid) REFERENCES role(r_id);
3. 多表查询
3.1 什么是多表查询
/*
多表查询的语法
select 字列表段 from 表名列表;
*/
-- 创建 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 products, category;
3.2 笛卡尔积
/*
笛卡尔积
*/
-- 多表查询 交叉连接查询 的查询结果会产生笛卡尔积, 是无法正常使用的
SELECT * FROM products, category;
3.4 多表查询的分类
3.4.1 内连接查询 (隐式/显式)
/*
内连接查询
特点: 通过制定的条件, 去匹配两张表中的内容, 匹配不上的就不显示
隐式内连接
语法格式: select 字段名... from 左表,右表 where 连接条件
显式内连接
语法格式: select 字段名... from 左表 [innner] join 右表 on 连接条件
*/
-- 隐式内连接
-- 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`;
-- 查询格力空调是属于哪一分类下的商品
SELECT
c.`cname`,
p.`pname`
FROM products p, category c WHERE p.`category_id` = c.`cid` AND p.`pname` = '格力空调';
-- 显式内连接
-- 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` = '鞋服';
3.4.2 外连接查询 (左/右)
/*
外连接查询
左外连接
语法格式: 关键字 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`;
3.4.3 各种连接方式的总结
- 内连接: inner join , 只获取两张表中 交集部分的数据.
- 左外连接: left join , 以左表为基准 ,查询左表的所有数据, 以及与右表有交集的部分
- 右外连接: right join , 以右表为基准,查询右表的所有的数据,以及与左表有交集的部分
4. 子查询 (SubQuery)
4.1 什么是子查询
/*
子查询
一条select语句的结果, 作为另一条select语句的一部分
特点: 子查询必须放在小括号中
作为父查询的条件使用(多数的情况)
*/
-- 查询价格最高的商品信息
-- 1.查询出最高的价格
SELECT MAX(price) FROM products; -- 5000
-- 2.根据最高价格查出商品信息
SELECT * FROM products WHERE price = 5000;
-- 使用一条SQL完成 子查询方式
SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products );
4.2 子查询的结果作为查询条件
/*
子查询的分类
where型子查询: 将子查询的结果, 作为父查询的 比较条件使用
from型子查询: 将子查询的结果作为一张表使用
exists型子查询: 查询结果是单列多行的情况, 可以将子查询的结果作为父查询的 in函数中的条件使用
*/
-- 子查询作为查询条件
-- 1.查询化妆品分类下的 商品名称 商品价格
-- 查询出化妆品分类的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 = '化妆品');
-- 2.查询小于平均价格的商品信息
-- 求出平均价格
SELECT AVG(price) FROM products;
-- 获取小于平均价格的商品信息
SELECT
*
FROM products
WHERE price < (SELECT AVG(price) FROM products);
4.3 子查询的结果作为一张表
-- 3.查询商品中,价格大于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;
4.4 子查询结果是单列多行
/*
子查询的结果是单列多行, 作为父查询的in函数的条件使用
语法格式:
select 字段名 from 表名 where 字段 in (子查询);
*/
-- 4.查询价格小于两千的商品,来自于哪些分类(名称)
-- 查询小于2000的商品的 分类id 单列多行
SELECT DISTINCT category_id FROM products WHERE price < 2000;
-- 根据分类的id 查询分类的信息
SELECT
*
FROM category
WHERE cid IN
(SELECT DISTINCT category_id FROM products WHERE price < 2000);
-- 5.查询家电类 与 鞋服类下面的全部商品信息
-- 首先要获取家电类和鞋服类的分类id
SELECT cid FROM category WHERE cname IN ('家电','鞋服');
-- 根据分类id 查找商品的信息
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.2 数据库反三范式
/*
反三范式
通过增加冗余或者重复数据, 来提高数据库的读性能
浪费存储空间,节省查询时间(以空间换时间)
冗余字段
某一个字段,属于一张表,但又在多张表中出现
*/
5.3 总结
/*
总结
1, 尽量根据三范式的规则设计数据库
2, 可以合理的增加冗余字段, 减少join操作, 让数据库执行的更快
*/