多表查询
什么是多表查询
•DQL: 查询多张表,获取到需要的数据
•比如 我们要查询家电分类下 都有哪些商品,那么我们就需要查询分类与商品这两张表
数据准备
- 创建db3_2 数据库
-- 创建 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');
笛卡尔积
交叉连接查询,因为会产生笛卡尔积,所以 基本不会使用
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)}。
多表查询之内连接查询
内连接的特点: 通过指定的条件去匹配两张表中的数据, 匹配上就显示,匹配不上就不显示
比如通过: 从表的外键 = 主表的主键 方式去匹配
隐式内链接
form子句 后面直接写 多个表名 使用where指定连接条件的 这种连接方式是 隐式内连接.
使用where条件过滤无用的数据
语法格式
•SELECT 字段名 FROM 左表, 右表 WHERE 连接条件;
1)查询所有商品信息和对应的分类信息
-- 隐式内连接
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` = '格力空调';
显式内连接
使用 inner join …on 这种方式, 就是显式内连接
语法格式
•SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件
(inner可以省略)
1)查询所有商品信息和对应的分类信息
-- 显式内连接查询
SELECT * FROM products p INNER JOIN category c ON p.category_id = c.cid;
- 查询鞋服分类下,价格大于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 = '鞋服';
多表查询之外连接查询
左外连接
左外连接 , 使用 LEFT OUTER JOIN , OUTER 可以省略
左外连接的特点:
•以左表为基准, 匹配右边表中的数据,如果匹配的上,就展示匹配到的数据
•如果匹配不到, 左表中的数据正常展示, 右边的展示为null.
语法格式
SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件
SQL实现
-- 左外连接查询
SELECT * FROM category c LEFT JOIN products p ON c.`cid`= p.`category_id`;
左外连接, 查询每个分类下的商品个数
-- 查询每个分类下的商品个数
/*
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`;
各种连接方式的总结
•内连接: inner join , 只获取两张表中 交集部分的数据。
•左外连接: left join , 以左表为基准 ,查询左表的所有数据, 以及与右表有交集的部分。
•右外连接: right join , 以右表为基准,查询右表的所有的数据,以及与左表有交集的部分。
