SQL学习笔记 | 09 多表查询

最新推荐文章于 2023-08-03 22:28:50 发布

月公子

最新推荐文章于 2023-08-03 22:28:50 发布

阅读量217

点赞数

分类专栏： SQL 文章标签： sql

本文链接：https://blog.csdn.net/weixin_44878159/article/details/91591664

版权

SQL 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

一、子查询

1、概念

2、种类

（1）、标量子查询（一个常数）

返回值：一个常数
放置位置：
SELECT 后插入一个常数列。如：SELECT m.prod_id, 15 FROM
WHERE 后的过滤条件中。如：WHERE m.sale_price > 15
HAVING 后的过滤条件中。如：HAVING COUNT(m.sale_price) > 2
Navicat代码

#	放在WHERE后（Result 1）
SELECT *
FROM milk_tea AS m1
WHERE m1.sale_price > ( 
                       SELECT m.sale_price 
					   FROM milk_tea AS m 
					   WHERE m.prod_name = '奶茶')			#	子查询结果为：15，下同


											
#	放在SELECT后（Result 2）											
SELECT m1.*, (
              SELECT m.sale_price 
			  FROM milk_tea AS m 
			  WHERE m.prod_name = '奶茶') '15'
FROM milk_tea AS m1;	



#	放在HAVING后（Result 3）
SELECT p.class, AVG(p.sale_price) 
FROM prod_info AS p
GROUP BY p.class
HAVING AVG(p.sale_price) > (
                            SELECT m.sale_price 
						    FROM milk_tea AS m 
						    WHERE m.prod_name = '奶茶');

在这里插入图片描述

（2）、关联子查询（一列值）

返回值：一列值
注：可以看作一列值，也可以看作一个n乘1的二维表
用法：
子查询SELECT的列和主查询中SELECT的列相同。
子查询的WHERE后，一般是子查询的某一列=主查询的某一列。
作用：
将多个相同结构的标量子查询组合在一起。
如；标量子查询只能查找大于日用品均值的日用品。而关联子查询能查出大于其均值的所有类别，日用品，饮料，零食。
Navicat代码

在这里插入图片描述

#	标量子查询，实现某个类别（日用品）的对比。Result 1
SELECT *
FROM prod_info AS p2
WHERE p2.class = '日用品'
  AND p2.sale_price > ( 
                       SELECT AVG(p1.sale_price)
					   FROM prod_info AS p1
					   WHERE p1.class = '日用品'
					   GROUP BY p1.class);


#	关联子查询，实现所有类别的对比。Result 2										 
SELECT *
FROM prod_info AS p2
WHERE  p2.sale_price > ( 
                        SELECT AVG(p1.sale_price)
						FROM prod_info AS p1
					    WHERE p1.class = p2.class
						GROUP BY p1.class);

在这里插入图片描述

（3）、普通子查询（一列值）

返回值：一列值
注：可以看作一列值，也可以看作一个n乘1的二维表
放置位置：
作为一个表，作为主查询的查询表
作为一列值，作为主查询WHERE IN （括号中的可选值）
Navicat代码

#	作为IN后面不连续的取值（Result 1）
SELECT *
FROM milk_tea AS m1
WHERE m1.prod_name IN (
                       SELECT m2.prod_name
					   FROM milk_tea AS m2
					   WHERE m2.sale_price = 15);



#	作为主查询的查询表（Result 2）											 
SELECT b.type 
FROM (
      SELECT p.prod_name, p.type, p.sale_price 
	  FROM prod_info AS p 
	  WHERE p.prod_name = '抽纸')	AS b 
WHERE	b.sale_price >26;

在这里插入图片描述

二、表联结（拼列）

1、概念

联结前提：两张表必须有相同的列。
联结不改变实际数据库中的表。

#	这其实是内联结的写法
SELECT * 
FROM prod_info AS p, supplier_info AS s 
WHERE p.supplier_id = s.supplier_id;

prod_info表（备用对比）：
在这里插入图片描述
supplier_info表（备用对比）：

内联结：

2、种类

（1）、内联结（显示重复行）

表之间用：逗号改为INNER JOIN 或 “=”
过滤条件“WHERE”改为“ON”
联结结果：显示共同列中重复的行！
ON后面除了联结字段相等，还可以再用AND加过滤条件

在这里插入图片描述

#	写法1（表联结概念中展示的）
SELECT p.*,o.*
FROM prod_info AS p, order_list AS o
WHERE p.prod_id = o.prod_id
AND o.order_id = '20190403001';					#	两种写法结果完全一样

#	写法2（推荐用，意义更明确）
SELECT p.*,o.*
FROM prod_info AS p INNER JOIN order_list AS o
ON p.prod_id = o.prod_id
AND o.order_id = '20190403001';

在这里插入图片描述

（2）、自联结

特殊的内联结
两张联结表相同

（3）、外联结（显示重复行、无关联信息）

联结结果：不仅包含相同列中的重复行（内联结的功能），还包含相关表中没有关联的行。例如：客户信息表（500个客户）和超市某天的流水帐表（100条记录，同一个人可能有两条记录）。外联结的结果，不仅同一个人的两条记录会在不同行显示，而且其余在今天没有买东西的客户信息也会显示
要想清，显示哪张表的所有信息
外联结的种类：

#	要显示所有客户在4月7号的账目，有的人没买东西计成NULL
SELECT c.*, o.*
FROM cust_info AS c LEFT OUTER JOIN order_list AS o
ON c.cust_id = o.cust_id
AND o.order_id LIKE '20190407%';

结果如下：
cust_info表（备用对比）：
在这里插入图片描述
order_list表（备用对比）：
外联结：

3、应用

将联结后的新表作为一个查询表，对其进行分组聚合。
在这里插入图片描述

#	对外联结结果进行分组聚合，FROM里面的是外联结的表（推荐写法）
SELECT  c2.cust_id, COUNT(c2.prod_id)
FROM (SELECT c.cust_id, c.cust_name, prod_id,o.prodname, o.order_id 
      FROM cust_info AS c LEFT OUTER JOIN order_list AS o
      ON c.cust_id = o.cust_id
			AND o.order_id LIKE '20190401%') c2
GROUP BY c2.cust_id;											#两种写法结果相同



#	也可以换一种写法，这种写法不如上面的写法明确（不推荐）
SELECT c.cust_id, COUNT(o.prod_id) 
FROM cust_info AS c LEFT OUTER JOIN order_list AS o
ON c.cust_id = o.cust_id
AND o.order_id LIKE '20190401%'
GROUP BY c.cust_id;

在这里插入图片描述

三、组合查询（拼行）

1、概念

在这里插入图片描述

组合查询是将查询结果（多条完整的SELECT语句）进行连接，相比表联结来说比较简单
每两条SELECT语句就要UNION一次
SELECT的列字段必须都相同（可以是列、表达式、聚合函数，但内容必须相同），且数据类型要相同（允许隐式转换，如文本型‘123’与数字型123兼容）。但FROM的表不必相同。
最终表的列名由第一条SELECT语句的列字段决定。
ORDER BY是针对最终拼接结果的。

2、种类

（1）UNION（去重）

（2）UNION ALL（不去重）

3、Navicat代码

这里是对同一张表UNION的，其实实际意义不大。如果有order_list 1和order_list 2，不同时间的两张订单表，这时候进行相同字段的拼接比较好。

#	UNION，去重（Result 1）
SELECT * FROM order_list AS o WHERE o.order_id LIKE '20190407%'
UNION  
SELECT * FROM order_list AS o WHERE o.order_id LIKE '20190407%';



#UNION ALL，不去重，用的相对较少（Result 2）
SELECT * FROM order_list AS o WHERE o.order_id LIKE '20190407%'
UNION ALL 
SELECT * FROM order_list AS o WHERE o.order_id LIKE '20190407%';

在这里插入图片描述

月公子

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
SQL学习笔记 | 09 多表查询

SQL学习笔记 | 09 多表查询一、子查询1、概念2、种类（1）、标量子查询（一个常数）（2）、关联子查询（一列值）（3）、普通子查询（一列值）二、表联结（拼列）1、概念2、种类（1）、内联结（显示重复行）（2）、自联结（3）、外联结（显示重复行、无关联信息）3、应用三、组合查询（拼行）1、概念2、种类（1）UNION（去重）（2）UNION ALL（不去重）3、Navicat代码一、子查询...
复制链接

扫一扫