SQL 复杂查询

发布于 2020-07-22 01:23:13   阅读量 56  点赞 0  

一、视图

 视图是数据的映射。视图与表的区别在于,视图保存的是SELECT语句,而没有保存实际的数据,可以将视图看作原表的一张子表。我们从视图中读取数据时,视图会在内部执行该SELECT语句并创建出一张临时表。

 视图的优点有以下两点:

  • 视图无需保存数据,因此可以节省存储设备的容量;

  • 可以将频繁使用的SELECT语句保存成视图,这样就不用每次重新书写了。而且,视图中的数据会随着原表的变化自动更新

应将经常使用的SELECT语句做成视图。


限制

① 定义视图时不能使用 ORDER BY 语句

 由于视图中的数据是没有顺序的,故使用ORDER BY的查询语句定义视图会产生错误。虽然在某些DBMS中是可以使用的(MYSQL可以 ),但这并不是通用的语法。

② 不能对视图进行更新

 视图在SELECT语句中能够像表一样使用,而对于更新表的DML语句来说,在某些情况下,也能够使用这些语句对视图进行更新。

 若在定义视图时使用的SELECT语句满足以下条件,则这个视图可以被更新:

  1. SELECT语句中未使用DISTINCT

  2. FROM子句中只有一张表

  3. 未使用GROUP BY子句

  4. 未使用HAVING子句

 其中条件大抵与聚合有关,使用视图来保存原表的汇总结果时,是无法判断如何将视图的更改反映到原表中的。通俗地将,无法将对聚合结果的更改,反推回对原数据的更改。

对于有些BDMS而言,视图被设置为只读的。若要修改视图,需要对数据库进行设置。


1. 创建视图

 创建视图需要使用CREATE VIEW语句:

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

SELECT语句中列的排列顺序应与视图中列的排列顺序相同。

定义视图时可以使用除了使用ORDER BY子句外的任何SELECT语句。


2. 使用视图

 视图与表一样,能够写在SELECT语句的FROM子句中:

SELECT <字段1>, ...
FROM <视图名>
...

由于视图保存了SELECT语句,故使用视图的查询通常需要执行两条以上的SELECT语句。而视图之间又可以嵌套,即创建视图时,从另一个视图中SELECT数据,形成多重视图。

我们应尽量避免在视图的基础上创建视图,因对于DBMS来说,多重视图会降低 SQL 的性能。


3. 删除视图

 删除视图使用DROP VIEW语句。

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



二、子查询

 视图并不是用来保存数据的,而是通过保存读取数据的SELECT语句来为用户提供便利。反之,子查询就是将用来定义视图的SELECT语句直接用于FROM子句当中。换句话说,子查询就是一次性视图

SELECT <字段1>,<字段2>
FROM (
    <SELECT 语句>
)
...;

 即子查询即为嵌套的SELECT结构,首先会执行FROM子句中的SELECT语句,然后才会执行外层的SELECT语句。

 理论上SELETC语句内部的嵌套层数是没有限制的,但随着子查询嵌套层数的增加,SQL 语句的易读性与性能会越来越差,故不建议使用太多层的嵌套SELECT结构。


1.标量子查询

 标量子查询指返回1行1列的结果,即返回表中某一行记录的某个字段值

 由于返回的是单一值,故标量子查询的返回值能够用在=<>等需要单一值的场景。

在 WHERE 子句中使用标量子查询

 在WHERE子句中不能使用聚合函数,于是可以使用标量子查询突破这个限制:

SELECT ...
FROM ...
WHERE <SELECT 语句>;

例如

SELECT product_id,product_name
FROM Product
WHERE sale_price > AVG(sale_price);

 以上 SQL 语句将会报错,但是使用标量子查询能够完成预期的功能:

SELECT product_id,product_name
FROM Product
WHERE sale_price > (
    SELECT AVG(sale_price)
    FROM Product
);


使用标量子查询的注意事项

 使用标量子查询的唯一注意事项为该子查询绝对不能返回多行结果。若子查询返回了多行结果,它就不再是标量子查询,而仅仅是一个普通的子查询,这时,在需要一个标量子查询的地方就会报错。



三、关联子查询

 若要在细分的组内进行比较,需要使用关联子查询。即各个组之间使用各自组的基准进行比较。(如组别A使用组别A的平均值,组别B使用组别B的平均值)。

 关联子查询的关键在于在外层查询中为查询起一个关联名称(使用关键字AS),并能够在子查询中通过名称.字段访问到外层查询的查询结果。

例:

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
                    );

 以上 SQL 语句查询出在不同产品种类中,售价大于相同种类均值的产品信息。

需要注意的是,关联子查询时起的关联名称有作用域,关联名称仅在其对应的子查询内有效。


Last Modified : 2020-07-23 04:49:12