mysql多表查询

coding羊
161 阅读9分钟

本文已参与「新人创作礼」活动， 一起开启掘金创作之路。

多表操作

1 多表关系设计

表与表之间的三种关系

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

1.1 一对多关系(常见)

  • 一对多关系（1:n）
    • 班级和学生，部门和员工，客户和订单，分类和商品
  • 一对多建表原则
    • 在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键
-- 创建省表 主表 一的一方
USE test;

CREATE TABLE province(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	description VARCHAR(20)
);

-- 创建市表 从表中外键字段指向主表中的主键 多的一方
CREATE TABLE city(
	cid INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	description VARCHAR(20) COMMENT '描述',
	pid INT,
	
	-- 创建外键 添加外键约束
	FOREIGN KEY(pid) REFERENCES province(id)
)

image-20211110172942472

1.2 多对多（常见）

  • 多对多（m:n）
    • 老师和学生，学生和课程，用户和角色
  • n 多对多关系建表原则
    • 需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的 主 键。
-- 多对多关系 演员与角色

-- 演员表
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,
	
	-- aid 字段指向actor 的主键 外键字段一定要和主键字段类型一致
	aid INT,
	
	-- rid 字段指向role 的主键
	rid INT
);

-- 添加外键约束
ALTER TABLE actor_role ADD FOREIGN key(aid) REFERENCES actor(id);

ALTER TABLE actor_role ADD FOREIGN KEY(rid) REFERENCES role(id);

image-20211110173140806

1.3 一对一关系(不常见)

  • 一对一（1:1）
    • 在实际的开发中应用不多.因为一对一可以创建成一张表。
  • 一对一建表原则
    • 外键唯一 主表的主键和从表的外键（唯一），形成主外键关系，外键唯一 UNIQUE

2 多表查询

查询要领

	查询前要确定的几个要素
	1. 查询几张表
	2. 表连接条件
	3. 要查询的字段
	4. 查询的条件

多表查询的分类

  • 内连接
    • 显示内连接
    • 隐示内连接
  • 外连接
    • 左外连接
    • 右外连接
  • 子查询

2.1 数据准备

CREATE TABLE db1 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)
);
 -- 添加外键约束
ALTER TABLE products ADD 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');

2.2 笛卡尔积

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

-- 语法格式
SELECT 字段名    FROM  表1, 表2;

使用交叉连接查询 商品表与分类表

SELECT * FROM category , products;

image-20211110173634804

笛卡尔积

假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

2.3 内连接查询

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

隐式内连接

**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; # 显式内连接查询

2.4 外连接查询

左外连接

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

-- 左外连接, 查询每个分类下的商品个数
/*
	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`;

右外连接

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

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

2.5 各种连接可视化

image-20211110175140519

3 子查询 (SubQuery)

3.1 什么是子查询

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

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

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

-- 子连接 做为where 条件
SELECT * from category c LEFT JOIN products p on c.cid = p.category_id;

-- 查询每个分类下的产品个数
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 where price = (SELECT max(price) from products)

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

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

-- 查询商品中,价格大于500的商品信息,包括  商品名称  商品价格  商品所属分类名称
-- SELECT pname, price , cname from category c INNER JOIN products p on c.cid = p.category_id
-- 子连接 一张表
SELECT pname, price, cname from category c INNER JOIN (SELECT * from products) p on  c.cid = p.category_id
where p.price > 500

3.4 子查询结果是单列多行

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

-- 查询小于2000的商品来着哪些分类
SELECT * from category WHERE cid in (SELECT DISTINCT category_id from products where price < 2000);

-- 查询家电 与 鞋服类下面的全部商品信息
select cid from category where cname='家电' or cname='鞋服'
SELECT * from products where category_id in (select cid from category where cname='家电' or cname='鞋服')

子查询总结

1. 子查询是一个单列的就在where后面做条件
2. 子查询是多列就当作一张表使用一定要起别名
3. 子查询是一列多行一般都是和on一起使用

4 数据库三范式

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

4.1 第一范式 1NF

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

image-20211110175922762

  • 地址信息表中, contry这一列,还可以继续拆分,不符合第一范式

4.2 第二范式 2NF

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

image-20211110180008444

  • 学员信息表中其实在描述两个事物 , 一个是学员的信息,一个是课程信息
  • 如果放在一张表中,会导致数据的冗余,如果删除学员信息, 成绩的信息也被删除了

4.3 第三范式 3NF

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

image-20211110180112384

  • 通过number 与 price字段就可以计算出总金额,不要在表中再做记录(空间最省)
avatar
文章
阅读
粉丝