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

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

 图片 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;

图片 2

 

图片 3

  视图正是保存好的 SELECT
语句。我们将反复的查询操作保存成视图,就能够进步效用了。

  

  (1)使用视图的查询

    在FROM 子句中利用视图的询问,经常有如下步骤:

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

      ②依照取得的结果,再履行在 FROM 子句中应用视图的 SELECT
子句。

    也就象征或者会师世“多重视图”,大家应尽量防止在视图的底蕴上成立视图,因为如此很或许会稳中有降
SQL 的特性。

  【备注】应制止在视图的底子上成立视图。

 

  (2)视图的界定 – 创制视图时不可能应用 OCR-VDE君越 BY 子句(但能够使用其他SELECT 子句)

图片 4

图:就算这是3个新创造的视图

 

  (3)视图的限制 – 更新视图

图片 5

图:假使上航海用体育场地去掉 OEscortDEQashqai BY 子句,在功成名就开创 ShohinSm
视图后,再进行插入时依然报错

  【备注】通过汇集获得的视图不可能举行革新。

 

  下边是一当中标创制视图并得以实施插入语句的示范。

图片 6

 成立视图并插入图片 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;    --记得加上别名哦

图片 8

图片 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
)

图片 10

图片 11

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

  可以使用常数或许列名的地方重重:SELECT 子句、GROUP BY 子句、HAVING
子句,或 OEnclaveDE福睿斯 BY 子句等。

--示例:在 SELECT 子句中使用标量子查询
SELECT shohin_id ,
       shohin_mei ,
       hanbai_tanka ,
       (
        SELECT AVG(hanbai_tanka)
        FROM dbo.Shohin
       ) AS avg_hanbai_tanka

FROM dbo.Shohin

图片 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
)

图片 13

  【注意】使用标量子查询时,该子查询无法回去多行结果。

图片 14

 

叁 、关联子查询

  为了直观展现字段名,今后将具有列名都改成中文。

图片 15图片 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

图片 17

 

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

  思考:接纳出各商品归类中高于该分类平均销售单价的货物?

图片 18

图片 19


因为内部的子查询不是前一节中的标量子查询(这里重回三行结果),所以报错

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

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

图片 20

  图

  关键效能的语句:在子查询中加上的 WHERE
子句的原则。该标准的情趣是:在同种商品中对个商品的销售单价和平均销售单价作相比较。

  

  2.整合条件一定要写在子查询 

图片 21

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

  那里提到到事关名称的功效域。子查询内部设定的涉及名称,只幸而该子查询内部使用。也能够那样说:“内部能够观望外部,而外部看不到里面”。

 

备注

  这里运用 MS SQL Server 实行求证,不保障拥有的 DBMS 执行结果正确。

 

《SQL 基础知识梳理》体系

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

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

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

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

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

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

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

 

 


【博主】反骨仔

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

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

相关文章