MySQL： 多表查询
1 什么是多表查询
DQL： 查询多张表， 获取到需要的数据
比如： 查询家电分类下， 都有哪些商品， 那么， 我们就需要查询分类与商品这两张表
2 数据准备
1） 创建 db3_2 数据库
-- 创建 db3_2 数据库,指定编码 CREATE DATABASE db3_2 CHARACTER SET utf8;
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) );
3) 插入数据
#分类数据 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 笛卡尔积
交叉连接查询,因为会产生笛卡尔积,所以基本不会使用
1) 语法格式
SELECT 字段名 FROM 表1, 表2;
2) 使用交叉连接查询 商品表与分类表
SELECT * FROM category , products;
3) 观察查询结果,产生了笛卡尔积 (得到的结果是无法使用的)
4） 笛卡尔积图解
假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。
4 多表查询 - 内连接查询
<1> 内连接的特点
通过指定的条件去匹配两张表中的数据, 匹配上就显示,匹配不上就不显示
比如通过: 从表的外键 = 主表的主键 的方式去匹配
<2> 隐式内连接
from子句后面直接写多个表名， 使用where指定连接条件。这种连接方式，是 隐式内连接。
（使用where条件过滤无用的数据）
语法格式
SELECT 字段名 FROM 左表, 右表 WHERE 连接条件;
代码示例：
1) 查询所有商品的所有信息，和对应的分类信息
# 隐式内连接 SELECT * FROM products,category WHERE category_id = cid;
2) 查询商品表的商品名称和价格,以及商品的分类信息
可以通过给表起别名的方式, 方便我们的查询(有提示)
3) 查询 格力空调是属于哪一分类下的商品
#查询 格力空调是属于哪一分类下的商品 SELECT p.`pname`,c.`cname` FROM products p , category c WHERE p.`category_id` = c.`cid` AND p.`pid` = 'p002';
<3> 显式内连接
使用 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 = '鞋服';
5 多表查询 - 外连接查询
<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`;
<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`;
6 各种连接方式的总结
内连接: inner join , 只获取两张表中 交集部分的数据.
左外连接: left join , 以左表为基准 ,查询左表的所有数据, 以及与右表有交集的部分
右外连接: right join , 以右表为基准,查询右表的所有的数据,以及与左表有交集的部分
