[SQL] SQL 基础知识梳理（五） - 复杂查询

2022-11-19 07:33:54

SQL 基础知识梳理（五） - 复杂查询

一、视图

　　1.视图和表

　　　　（1）视图：从 SQL 的角度来看视图就是一张表。

　　　　（2）视图和表的区别：是否保存了实际的数据。数据库中的数据实际上保存到计算机的存储设备（如硬盘）；视图实际上保存的是 SELECT 语句，从视图中读取数据时，视图会在内部执行该 SELETC 语句并创建出一张临时表。

　　　　（3）视图的优点：

　　　　　　①无需保存数据，节省存储设备的容量。

　　　　　　②保存频繁使用的 SELECT 语句，提高效率。

　　【总结】表中存储的是实际数据，视图保存的是 SELECT 语句。

　　【总结】应该将经常使用的 SELECT 语句做成视图。

　　2.创建视图

--语法

--CREATE VIEW 视图名称(<视图列名1>, <视图列名2>, ...)

--AS

--<SELECT 语句>

--示例：创建视图

CREATE VIEW ShohinSum(shohin_bunrui, cnt_shohin)

AS

SELECT shohin_bunrui, COUNT(*)

FROM dbo.Shohin

GROUP BY shohin_bunrui;

--示例：使用视图

SELECT *

FROM shohinSum;

　　视图就是保存好的 SELECT 语句。我们将频繁的查询操作保存成视图，就可以提高效率了。

　　（1）使用视图的查询

　　　　在FROM 子句中使用视图的查询，通常有如下步骤：

　　　　　　①执行定义视图的 SELECT 语句；

　　　　　　②根据得到的结果，再执行在 FROM 子句中使用视图的 SELECT 子句。

　　　　也就意味着可能会出现“多重视图”，我们应尽量避免在视图的基础上创建视图，因为这样很可能会降低 SQL 的性能。

　　【备注】应避免在视图的基础上创建视图。

　　（2）视图的限制 - 创建视图时不能使用 ORDER BY 子句（但可以使用其它 SELECT 子句）

图：假设这是一个新创建的视图

　　（3）视图的限制 - 更新视图

图：假设上图去掉 ORDER BY 子句，在成功创建 ShohinSm 视图后，再执行插入时仍然报错

　　【备注】通过聚合得到的视图不能进行更新。

　　下面是一个成功创建视图并可以执行插入语句的示例。

创建视图并插入

检测数据

　　虽然可以对视图实现更新操作，但我们通常不这样做。

　　3.删除视图

--语法

--DROP VIEW 视图名称(<视图列名1>, <视图列名2>, ...)

--示例

DROP VIEW shohinSum

二、子查询

　　1.子查询和视图：

　　　　（1）子查询：将用来定义视图的 SELECT 语句直接用于 FROM 子句当中。

--示例

SELECT shohin_bunrui, cnt_shohin

FROM

(

    SELECT shohin_bunrui, COUNT(*) AS cnt_shohin

    FROM Shohin

    GROUP BY shohin_bunrui

) AS ShohinSum;    --记得加上别名哦

　　图中的 AS ShohinSum 是子查询的名称，子查询（subquery）就是“次级（sub）”的“查询（query）”。先执行①的内层查询，再执行②的外层查询。

　　【备注】子查询作为内层查询会首先执行。

　　子查询的层数没有限制，出于性能和可读性考虑，应避免使用多层嵌套的子查询。

　　2.标量子查询

　　上面的子查询基本上都会返回多行结果（虽然偶尔只返回 1 行数据），而标量子查询，就是必须而且只能返回 1 行 1 列的结果。

　　【备注】标量子查询就是返回单一值的子查询。

--示例:查询出销售单价高于平均销售单价的商品，hanbai_tanka：销售单价

SELECT *

FROM dbo.Shohin

WHERE hanbai_tanka >

(

    SELECT AVG(hanbai_tanka)

    FROM dbo.Shohin

)

　　3.在其它地方书写标量子查询

　　能够使用常数或者列名的地方很多：SELECT 子句、GROUP BY 子句、HAVING 子句，或 ORDER BY 子句等。

--示例：在 SELECT 子句中使用标量子查询

SELECT shohin_id ,

       shohin_mei ,

       hanbai_tanka ,

       (

        SELECT AVG(hanbai_tanka)

        FROM dbo.Shohin

       ) AS avg_hanbai_tanka

FROM dbo.Shohin

--示例：在 HAVING 子句中使用标量子查询

SELECT shohin_bunrui,

    AVG(hanbai_tanka)

FROM dbo.Shohin

GROUP BY shohin_bunrui

HAVING AVG(hanbai_tanka) >

(

    SELECT AVG(hanbai_tanka)

    FROM dbo.Shohin

)

　　【注意】使用标量子查询时，该子查询不能返回多行结果。

三、关联子查询

　　为了直观显示字段名，现在将所有列名都改成中文。

CREATE TABLE Shohin

(

    商品编号   CHAR(4)      NOT NULL,

    商品名称    VARCHAR(100) NOT NULL,

    商品分类 VARCHAR(32)  NOT NULL,

    销售单价  INTEGER ,

    进货单价  INTEGER ,

    登记日期      DATE ,

    PRIMARY KEY (商品编号)

 );

INSERT INTO Shohin VALUES ('', 'T恤' ,'衣服', 1000, 500, '2009-09-20');

INSERT INTO Shohin VALUES ('', '打孔器', '办公用品', 500, 320, '2009-09-11');

INSERT INTO Shohin VALUES ('', '运动T恤', '衣服', 4000, 2800, NULL);

INSERT INTO Shohin VALUES ('', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');

INSERT INTO Shohin VALUES ('', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');

INSERT INTO Shohin VALUES ('', '叉子', '厨房用具', 500, NULL, '2009-09-20');

INSERT INTO Shohin VALUES ('', '擦菜板', '厨房用具', 880, 790, '2008-04-28');

INSERT INTO Shohin VALUES ('', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');

建表语句：CREATE TABLE Shohin

　　1.普通子查询和关联子查询的区别

　　思考：选取出各商品分类中高于该分类平均销售单价的商品？

图

图因为内部的子查询不是前一节中的标量子查询（这里返回三行结果），所以报错

　　【备注】在 WHERE 子句中使用子查询时，该子查询的结果必须为单一值。

--正确的语句：采用关联子查询

SELECT *

FROM dbo.Shohin s1

WHERE 销售单价 >

(

    SELECT AVG(s2.销售单价) AS avg_销售单价

    FROM dbo.Shohin s2

    WHERE s1.商品分类 = s2.商品分类

    GROUP BY s2.商品分类

)