mysql之多表查询详细笔记

需求1: 内连接

-- 需求1: 查询所有存在商品分类的商品信息
-- 查询商品数据
select * from goods;
select * from category;
-- 在查询中, 需要将两张表存在对应关系的数据全部显示出来时, 需要使用连接查询-内连接
-- 内连接 : 显示两张表中存在对应关系的数据, 无对应关系的数据不显示
-- 语法 : 表1 inner join 表2 on 表1.字段 = 表2.字段
select * from goods inner join category on goods.typeId = category.typeId;

连接查询共通知识点

-- 连接查询共用知识点
-- 对连接后的表进行字段显示限制
-- 注意: 需要由对应的 表名.字段 的方式实现(防止表与表之间存在相同的字段名)
select goods.goodsName, category.cateName from goods inner join category on goods.typeId = category.typeId;
-- 表名.* : 代表显示该表的所有字段
select goods.*, category.cateName from goods inner join category on goods.typeId = category.typeId;

-- 连接查询中, 往往会给表名起别名, 目的: 1) 缩短表名 2) 给表单独创建空间(了解) 
select go.goodsName, ca.cateName from goods go inner join category ca on go.typeId = ca.typeId;

需求2: 左连接

-- 需求2: 查询所有商品信息,包含商品分类
-- 查询所有商品
select * from goods;
select * from category;
-- 左连接 : 以 left join 关键为界, 关键字左侧的表的信息要全部显示出来, 而关键字右侧表的信息, 有对应的部分显示, 无对应的部分以 null 占位填充即可
-- 语法 : 表1 left join 表2 on 表1.字段 = 表2.字段
select * from goods go left join category ca on go.typeId = ca.typeId;

-- 需求3: 查询所有商品分类及其对应的商品的信息
-- 左连接
select * from category ca left join goods go on ca.typeId = go.typeId;

需求2: 右连接

-- 需求2: 查询所有商品信息,包含商品分类
-- 查询所有商品
select * from goods;
select * from category;
-- 注意 :  右连接主要解决 3 张及以上表进行连接查询时常见
-- 右连接 : 以 right join 关键为界, 关键字右侧的表的信息要全部显示出来, 而关键字左侧表的信息, 有对应的部分显示, 无对应的部分以 null 占位填充即可
-- 语法 : 表1 right join 表2 on 表1.字段 = 表2.字段
select * from category ca right join goods go on ca.typeId = go.typeId;

-- 需求3: 查询所有商品分类及其对应的商品的信息
-- 右连接
select * from goods go right join category ca on go.typeId = ca.typeId;

内连接和左右连接的小结

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iCWSlTTZ-1650791400932)(Linux 和数据库 day05 随堂笔记.assets/image-20200529112619531.png)]

连接查询的实质

-- 需求 : 显示所有价格低于 120 的商品信息, 包含其对应商品分类
select * from goods;
select * from category;
-- 注意 : 
-- 1. 连接操作实质: 只是将分布于多张表的数据, 通过连接的方式进行整理, 形成数据源
-- 2. 连接操作完成后, 之前的所有的查询语法, 都可以继续使用
-- 3. 具体连接方法的选择, 要以具体需求为准
-- 内连接 : 如果有商品没有分类对应信息, 不会被显示出来
-- 注意 : 如果使用内连接, 会造成数据缺失, 查询结果错误
select * from goods go 
inner join category ca on go.typeId = ca.typeId
where go.price < 120;

-- 左连接
select * from goods go 
left join category ca on go.typeId = ca.typeId
where go.price < 120;

自关联的介绍

特征 : 只有 1 张表, 表中最少存在两列字段具备对应关系;
原理 : 通过起别名的方式, 将 1 张表变为 2 张表, 通过对应字段的对应关系, 进行条件比对, 实现连接查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ccDPMsL2-1650791400933)(Linux 和数据库 day05 随堂笔记.assets/image-20200529115856450.png)]

需求4: 自关联

-- 需求4: 查询河南省所有的市
-- 查询所有数据
select * from areas;
-- 自关联 : 
-- 1) 通过起别名的方式, 将一表变俩表
-- 2) 通过 表1.字段 = 表2.字段 实现表间关联
select * from areas a1 
inner join areas a2 on a1.aid = a2.pid
where a1.atitle = '河南省';

需求5 : 自关联

-- 需求5: 查询河南省的所有的市和区
-- 自关联
select * from areas;
-- 
select * from areas a1 
inner join areas a2 on a1.aid = a2.pid
left join areas a3 on a2.aid = a3.pid
-- inner join areas a3 on a2.aid = a3.pid
where a1.atitle = '河南省';

子查询的介绍

定义 : 在一条查询语句当中, 利用另一条语句作为条件或数据源, 充当条件或数据源的语句称之为子查询语句.
注意 : 子查询语句在使用时, 务必使用括号括起来 

需求6 : 子查询-充当条件

-- 需求6: 查询价格高于平均价的商品信息
-- 查询所有数据
select * from goods;
-- 查询平均价格
select avg(price) from goods; -- 68.33
-- 需求实现
select * from goods where price > 68.33;
-- 优化实现 - 子查询 : 在 where 关键字后边的子查询语句是充当条件的
select * from goods where price > (select avg(price) from goods);

需求7 : 子查询-充当数据源

-- 需求7: 查询所有来自并夕夕的商品信息, 包含商品分类
-- 查询所有数据
select * from goods;
select * from category;
-- 并夕夕商品信息
select * from goods where company = '并夕夕';
-- 需求实现 - 子查询 : 在 from 和连接查询关键字后边的子查询语句时充当数据源的
select * from category ca 
inner join (select * from goods where company = '并夕夕') a on ca.typeId = a.typeId;

扩展1 : 子查询的分类

根据子查询语句返回的结果形式体现, 子查询共分为 4 类:
子查询返回的结果是一个值(一行一列), 称之为: 标量子查询
子查询返回的结果是一列数据(一列多行), 称之为: 列子查询
子查询返回的结果是一行数据(一行多列), 称之为: 行子查询
子查询返回的结果是多行多列(相当于一张表), 称之为: 表级子查询

扩展2 : 子查询关键字

-- 需求 : 查询在25-100之间的商品的价格
select price from goods where price between 25 and 100;
-- in : 范围
-- select * from goods where price in (25, 30, 77, 30, 72,25);
select * from goods where price in (select price from goods where price between 25 and 100);
-- some/any : 任意一个
select * from goods where price = some(select price from goods where price between 25 and 100);
select * from goods where price = any(select price from goods where price between 25 and 100);
-- all : 全部(= all : 等于所有; != 或 <> all : 不等于所有)
select * from goods where price = all(select price from goods where price between 25 and 100);
select * from goods where price != all(select price from goods where price between 25 and 100);
select * from goods where price <> all(select price from goods where price between 25 and 100);

• from goods where price != all(select price from goods where price between 25 and 100);
  select * from goods where price <> all(select price from goods where price between 25 and 100);