[SQL] SQL 基础知识梳理(五) – 复杂查询

SQL 基础知识梳理(五) – 复杂查询

 SQL Server 1

目录

  • 视图
  • 子查询
  • 关联子查询

 

一、视图

  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;

SQL Server 2

 

SQL Server 3

  视图就是保存好之 SELECT
语句。我们以反复的查询操作保存成视图,就好提高效率了。

  

  (1)使用视图的询问

    在FROM 子句被使视图的查询,通常有如下步骤:

      ①推行定义视图的 SELECT 语句子;

      ②冲取得的结果,再实行于 FROM 子句被行使视图的 SELECT
子句。

    也就是象征可能会见产出“多厚图”,我们承诺尽量避免在视图的底子及创设视图,因为如此十分可能会见减低
SQL 的习性。

  【备注】应避免以视图的功底及缔造视图。

 

  (2)视图的界定 – 创建视图时无可知利用 ORDER BY 子词(但好采用外
SELECT 子句)

SQL Server 4

祈求:假而即是一个新创造的视图

 

  (3)视图的界定 – 更新视图

SQL Server 5

贪图:假设上图去丢 ORDER BY 子句,在成功开创 ShohinSm
视图后,再履行插入时依旧报错

  【备注】通过聚合得到的视图不可知开展创新。

 

  下面是一个打响开创视图并可执行插入语句的言传身教。

SQL Server 6

 创建视图并插入SQL Server 7

检测数据

  虽然好对视图实现创新操作,但咱通常不这样做。

 

  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;    --记得加上别名哦

SQL Server 8

SQL Server 9

  图中之 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
)

SQL Server 10

SQL Server 11

  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

SQL Server 12

  

--示例:在 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
)

SQL Server 13

  【注意】使用标量子查询时,该子查询不可知回去多实践结果。

SQL Server 14

 

老三、关联子查询

  为了直观显示字段名,现在拿持有列名都转移化汉语。

SQL Server 15SQL Server 16

CREATE TABLE Shohin
(  
    商品编号   CHAR(4)      NOT NULL,
    商品名称    VARCHAR(100) NOT NULL,
    商品分类 VARCHAR(32)  NOT NULL,
    销售单价  INTEGER ,
    进货单价  INTEGER ,
    登记日期      DATE ,
    PRIMARY KEY (商品编号)
 );

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

建表语句:CREATE TABLE Shohin

SQL Server 17

 

  1.普通子查询及关联子查询的界别

  思考:选取出各商品归类中逾该分类平均销售单价的货?

SQL Server 18

SQL Server 19


因为内部的子查询不是前方同省被之标量子查询(这里归三行结果),所以报错

  【备注】在 WHERE
子句被使用子查询时,该子查询的结果要也单一值。

--正确的语句:采用关联子查询
SELECT *
FROM dbo.Shohin s1
WHERE 销售单价 >
(
    SELECT AVG(s2.销售单价) AS avg_销售单价
    FROM dbo.Shohin s2
    WHERE s1.商品分类 = s2.商品分类
    GROUP BY s2.商品分类
) 

SQL Server 20

  图

  关键作用的讲话:在子查询中丰富的 WHERE
子句之条件。该标准的意是:在同种商品受到针对只商品之行销单价及平均销售单价作于。

  

  2.整合条件得要是描绘于子查询 

SQL Server 21

  图:把涉及条件移到子查询之外

  这里提到到关系名称的作用域。子查询内部设定的关系名称,只能于该子查询内部采用。也堪这么说:“内部可以见见外部,而外部看不到里面”。

 

备注

  这里以 MS SQL Server 进行认证,不保险有的 DBMS 执行结果对。

 

《SQL 基础知识梳理》系列

  《SQL 基础知识梳理(一) – 数据库和
SQL》

  《SQL 基础知识梳理(二) –
查询基础》

  《SQL 基础知识梳理(三) –
聚合和排序》

  《SQL 基础知识梳理(四) –
数据更新》

  《SQL 基础知识梳理(五) –
复杂查询》

  《SQL 基础知识梳理(六)- 函数、谓词、CASE
表达式》

  《SQL 基础知识梳理(七)-
集合运算》

 

 


【博主】反骨仔

【原文】http://www.cnblogs.com/liqingwen/p/5939796.html 

【参考】《SQL
ゼロからはじめるデータベース操作》

相关文章