【Mysql】多表查询、隐式内链接、显式内连接、左外连接、右外连接

多表查询
什么是多表查询
•DQL: 查询多张表,获取到需要的数据
•比如 我们要查询家电分类下 都有哪些商品,那么我们就需要查询分类与商品这两张表
数据准备

1. 创建db3_2 数据库

-- 创建 db3_2 数据库,指定编码
CREATE DATABASE db3_2 CHARACTER SET utf8;
1
2

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)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

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');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

笛卡尔积
交叉连接查询,因为会产生笛卡尔积,所以 基本不会使用
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;
1
2

2. 查询商品表的商品名称 和 价格,以及商品的分类信息

-- 可以通过给表起别名的方式, 方便我们的查询(有提示)
SELECT 
    p.`pname`,
    p.`price`,
    c.`cname`
FROM products p , category c WHERE p.`category_id` = c.`cid`;
1
2
3
4
5
6

3. 查询 格力空调是属于哪一分类下的商品

-- 查询 格力空调是属于哪一分类下的商品 
SELECT p.`pname`,c.`cname` FROM products p , category c 
WHERE p.`category_id` = c.`cid` AND p.`pid` = '格力空调';
1
2
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;
1
2

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 = '鞋服';
1
2
3
4
5
6
7
8
9
10
11
12
13
14

多表查询之外连接查询
左外连接
左外连接 , 使用 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

左外连接, 查询每个分类下的商品个数

-- 查询每个分类下的商品个数
/*
    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`;
1
2
3
4
5
6
7
8
9
10
11

右外连接
右外连接 , 使用 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`;
1
2

各种连接方式的总结

•内连接: inner join , 只获取两张表中 交集部分的数据。
•左外连接: left join , 以左表为基准 ,查询左表的所有数据, 以及与右表有交集的部分。
•右外连接: right join , 以右表为基准,查询右表的所有的数据,以及与左表有交集的部分。