SQL：复杂查询

最新推荐文章于 2024-08-05 17:42:28 发布

ISWW

最新推荐文章于 2024-08-05 17:42:28 发布

阅读量370

点赞数

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

本文链接：https://blog.csdn.net/abbeylasi/article/details/105662120

版权

MySQL 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

一、视图

1.视图和表

在SQL语句中，视图就是一张表。
视图和表的区别：是否保存了实际的数据，表中存储的是实际数据，视图中保存的是从表中取出数据所使用的SELECT语句。

在创建表时，通过INSERT语句将数据保存到数据库中，而数据库被保存在计算机的存储设备中。因此，通过SELECT语句查询数据时，是从存储设备中读取数据，进行计算后，再将结果返回给用户。但是，视图保存的是SELECT语句，不会将数据保存到任何地方。因而从视图中读取数据时，视图会在内部执行SELECT语句，并创建一张临时表。

视图和表的区别
视图的2大优点：
1.视图无需保存数据，可以节省存储设备的容量。
2.可以将频繁使用的SELECT 语句保存成视图，便于复用。

视图中的数据会随着原表的变化自动更新。

2.创建视图

CREATE VIEW 视图名称(<视图列名1>, <视图列名2>, ……)
AS <SELECT语句>

视图的列名在视图名称之后的列表中定义。
SELECT语句需要书写在AS关键字之后。
SELECT 语句中列的排列顺序和视图中列的排列顺序相同，SELECT 语句中的第1 列就是视图中的第1列，SELECT语句中的第2列就是视图中的第2列，以此类推。

-- 更新Product表中的数据
-- 1.删除Product表中的数据，将表清空
DELETE FROM Product;
DROP Product;

--2.将数据插入到空表Product中
CREATE TABLE Product(
	product_id      CHAR(4)      NOT NULL,
 	product_name    VARCHAR(100) NOT NULL,
 	product_type    VARCHAR(32)  NOT NULL,
 	sale_price      INTEGER ,
 	purchase_price  INTEGER ,
 	regist_date     DATE ,
 	PRIMARY KEY (product_id)
 );

INSERT INTO Product VALUES ('0001', 'T恤' ,'衣服', 1000, 500, '2009-09-20');
INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');

--3.查询更新结果
SELECT * FROM Product;

定义视图时可以使用任何SELECT语句，可以使用WHERE、GROUPBY、HAVING，也可以通过SELECT *来指定全部列。

-- 1.创建视图
-- ProductSum视图
CREATE VIEW ProductSum (product_type, cnt_product)
AS 
	SELECT product_type, COUNT(*)
		FROM Product 
			GROUP BY product_type;
--2.使用视图
--在FROM子句中使用视图来代替表
SELECT product_type, cnt_product
	FROM ProductSum;

在FROM 子句中使用视图的查询，有如下两个步骤：
1.先执行定义视图的SELECT语句；
2.根据得到的结果，再执行在FROM 子句中使用视图的SELECT语句。

使用视图的查询通常需要执行2 条以上的SELECT 语句，“2 条以上”，指可以出现以视图为基础创建视图的多重视图。

-- 视图ProductSumJim
CREATE VIEW ProductSumJim (product_type, cnt_product)
AS
	SELECT product_type, cnt_product
		FROM ProductSum
			WHERE product_type = '办公用品';

-- 确认创建好的视图
SELECT product_type, cnt_product
	FROM ProductSumJim;

建议：尽量避免在视图的基础上创建视图，因为多重视图会降低SQL的性能。

3.视图的限制

视图的限制1：定义试图时不能使用ORDER BY语句

-- 错误用法
CREATE VIEW ProductSum (product_type, cnt_product)
AS
	SELECT product_type, COUNT(*)
		FROM Product
			GROUP BY product_type
			ORDER BY product_type;

为什么不能使用ORDER BY 子句？
因为视图和表一样，数据行没有顺序。

视图的限制2：对视图进行更新

标准SQL中有规定：如果定义视图的SELECT语句能够满足某些条件，那这个视图就可以被更新。

SELECT 子句中未使用DISTINCT
FROM 子句中只有一张表
未使用GROUP BY 子句
未使用HAVING 子句

使用视图来保存原表的汇总结果时，无法判断如何将视图的更改反映到原表中。因此，通过汇总得到的视图不能进行更新。

视图是从表派生出来的，如果原表可以更新，那视图中的数据就可以更新。反之亦然，如果视图发生了改变，而原表没有进行相应更新的话，就无法保证数据的一致性。所以，视图和表需要同时进行更新。

举例，对ProductSum 视图执行INSERT INTO ProductSum VALUES ('电器制品', 5);会报错。

不是通过汇总得到的视图就可以进行更新。

CREATE VIEW ProductJim (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
AS
	SELECT * FROM Product 
		WHERE product_type = '办公用品';

INSERT INTO ProductJim VALUES ('0009', '印章', '办公用品', 95, 10,'2009-11-30');

-- 确认数据是否已经添加到视图中
SELECT * FROM ProductJim;

-- 确认数据是否已经添加到原表中
SELECT * FROM Product;

4.删除视图

--语法
DROP VIEW 视图名称(<视图列名1>, <视图列名2>, ……)

--例子
DROP VIEW ProductSum;

二、子查询

1.子查询和视图

子查询是将用来定义视图的SELECT语句直接用于FROM子句中，查询的结果是一张一次性视图。
子查询必须设定名称，使用AS关键字进行命名，但有时可以省略。

-- 删除商品编号为0009的数据
DELETE FROM Product WHERE product_id = '0009';

-- 1.视图
-- 根据商品种类统计商品数量的视图
CREATE VIEW ProductSum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*) 
		FROM Product
	GROUP BY product_type;

-- 确认创建好的视图
SELECT product_type, cnt_product
	FROM ProductSum;

-- 2.子查询
-- 在FROM子句中直接书写定义视图的SELECT语句
SELECT product_type, cnt_product
	FROM ( SELECT product_type, COUNT(*) AS cnt_product
				FROM Product
			GROUP BY product_type 
	) AS ProductSum;

上述子查询中，SELECT语句的执行顺序如下，即子查询作为内层查询会先执行。

-- 1.先执行FROM子句中的SELECT语句
SELECT product_type, COUNT(*) AS cnt_product
		FROM Product
	GROUP BY product_type;

-- 2.再执行外层的SELECT语句
SELECT product_type, cnt_product
	FROM ProductSum;

子查询的层数原则上没有限制，可以嵌套使用FROM子句。

SELECT product_type, cnt_product
	FROM (SELECT * 
				FROM (SELECT product_type, COUNT(*) AS cnt_product
							FROM Product
						GROUP BY product_type) AS ProductSum
			WHERE cnt_product = 4) AS ProductSum2;

建议：为了便于阅读，尽量避免使用多层嵌套的子查询。

2.标量子查询

标量，即单一。
子查询有时候会查询不到结果，标量子查询要求必须返回且只能返回1行1列的结果。因此，标量子查询指返回单一值的子查询。

标量子查询的优点：标量子查询的返回值可以用在=或者<>这样需要单一值的比较运算符中。

在WHERE子句中使用标量子查询，比如，如何查询出销售单价高于平均销售单价的商品？

-- 错误用法：在WHERE子句中使用聚合函数
SELECT product_id, product＿name, sale_price
FROM Product
WHERE sale_price > AVG(sale_price);

-- 正确用法：子查询
-- 先用子查询计算平均销售单价，再选取出销售单价高于全部商品的平均单价的商品
SELECT product_id, product_name, sale_price FROM Product 
WHERE sale_price > (SELECT AVG(sale_price) FROM Product);

能够使用常数或列名的地方，无论是SELECT子句、GROUP BY子句、HAVING子句，还是ORDER BY子句，只要是能使用单一值的位置都可以使用标量子查询。

-- 在SELECT子句中使用标量子查询
SELECT product_id,product_name,sale_price,
		(SELECT AVG(sale_price) FROM Product) AS avg_price
FROM Product;

-- 在HAVING子句中使用标量子查询
SELECT product_type, AVG(sale_price) 
FROM Product 
GROUP BY product_type 
HAVING AVG(sale_price) > (SELECT AVG(sale_price) FROM Product);

注意，子查询不能返回多行结果。

-- 错误用法
SELECT product_id,product_name,sale_price, 
		(SELECT AVG(sale_price) FROM Product 
			GROUP BY product_type) AS avg_price 
FROM Product;

三、关联子查询

1.普通子查询 vs 关联子查询

关联子查询适用于对表中某一部分记录的集合进行比较。
例如，以细分的组为基础，对组内商品的平均价格和各商品的销售单价进行比较，选取出各商品种类中高于该商品种类的平均销售单价的商品。

-- 按照商品种类计算平均价格
SELECT AVG(sale_price)
FROM Product
GROUP BY product_type;

-- WHERE + 聚合函数的子查询错误用法
-- 错误原因：在WHERE子句中使用子查询时，该子查询的结果必须是单一的。
SELECT product_id, product_name, sale_price
FROM Product
WHERE sale_price > (SELECT AVG(sale_price) FROM Product GROUP BY product_type);

-- 使用关联子查询解决上述问题
SELECT product_type, product_name, sale_price  
FROM Product AS P1 
WHERE sale_price > (SELECT AVG(sale_price) 
					FROM Product AS P2 
					WHERE P1.product_type = P2.product_type GROUP BY product_type);

上述关联子查询中的关键语句是，在子查询中添加的WHERE子句的条件。该条件表示在同一商品种类中对各商品的销售单价和平均单价进行比较。

此外，比较对象都是Product表，为了进行区别，分别使用P1和P2别名。因此，在使用关联子查询时，需要在表所对应的列名之前加上表的别名，以< 表名>.< 列名>的形式表示。

2.关联子查询用于切分集合

GROUP BY对集合进行切分：
根据商品种类对表进行切分
关联子查询对集合进行切分：

先计算各个商品种类中商品的平均销售单价，该单价会用来和商品表中的各条记录进行比较，关联子查询只能返回1行结果。关联子查询执行时，DBMS内的执行情况如下图。

3.结合条件要写在子查询中

子查询内设定的关联名称，只能在该子查询内使用。

-- 错误的关联子查询用法
-- 将关联条件写在子查询之外的外层查询中
SELECT product_type, product_name, sale_price
FROM Product AS P1
WHERE P1.product_type = P2.product_type
	AND sale_price > (SELECT AVG(sale_price) 
						FROM Product AS P2
						GROUP BY product_type);
-- 正确用法
SELECT product_type, product_name, sale_price
FROM Product AS P1
WHERE sale_price>(SELECT AVG(sale_price)
					FROM Product AS P2
					WHERE P1.product_type=P2.product_type
					GROUP BY product_type);

ISWW

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL：复杂查询

一、视图1.视图和表在SQL语句中，视图就是一张表。视图和表的区别：是否保存了实际的数据，表中存储的是实际数据，视图中保存的是从表中取出数据所使用的SELECT语句。在创建表时，通过INSERT语句将数据保存到数据库中，而数据库被保存在计算机的存储设备中。因此，通过SELECT语句查询数据时，是从存储设备中读取数据，进行计算后，再将结果返回给用户。但是，视图保存的是SELECT语句，不会将...
复制链接

扫一扫

专栏目录