2.1.3 MySQL多表&外键&数据库设计 多表关系, 多表查询, 内外连接查询 子查询, 三范式, 反三范式

目录

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

1.1 多表简述

1.2 单表的缺点

1.2.1 数据准备

1.2.2 单表的问题

1.3 解决方案

1.3.1 设计为两张表

1.3.2 表关系分析

1.3.3 多表设计上的问题

1.4 外键约束

1.4.1 什么是外键

1.4.2 创建外键约束

1.4.3 删除外键约束

1.4.4 外键约束的注意事项

1.4.5 级联删除操作(了解)

2. 多表关系设计

2.1 一对多关系(常见)

2.2 多对多关系(常见)

2.3 一对一关系(了解)

2.4 设计 省&市表

2.5 设计 演员与角色表

3. 多表查询

3.1 什么是多表查询

3.2 笛卡尔积

3.4 多表查询的分类

3.4.1 内连接查询 (隐式/显式)

3.4.2 外连接查询  (左/右) 

3.4.3 各种连接方式的总结

4. 子查询 (SubQuery)

4.1 什么是子查询

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

4.3 子查询的结果作为一张表

4.4 子查询结果是单列多行

4.5 子查询总结

5. 数据库设计

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

5.2 数据库反三范式

5.3 总结


 

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操作, 让数据库执行的更快
*/

 

 

  • 1
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值