窥探SQL: 3.复杂查询

视图

视图定义

视图是一个虚拟的表。
视图是依据SELECT语句来创建的，所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。
即视图是根据SELECT语句创建视图然后在这个基础上再进行查询。

视图(View) vs 表(Table)

从SQL的角度来说操作视图与操作表看起来是完全相同的。
视图与表的区别：
根本区别：是否保存了实际的数据。
视图并不是数据库真实存储的数据表，它可以看作是一个窗口，通过这个窗口我们可以看到数据库表中真实存在的数据。
即两者区别的本质是：视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。

视图的意义

1. 通过定义视图可以将频繁使用的SELECT语句保存以提高效率。
2. 通过定义视图可以使用户看到的数据更加清晰。
3. 通过定义视图可以不对外公开数据表全部字段，增强数据的保密性。
4. 通过定义视图可以降低数据的冗余。

创建视图

创建视图的基本语法如下：

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

其中SELECT 语句需要书写在 AS 关键字之后。 SELECT 语句中列的排列顺序和视图中列的排列顺序相同。而且视图的列名是在视图名称之后的列表中定义的。
注意：

1. 视图名在数据库中需要是唯一的，不能与其他视图和表重名。
2. 在一般的DBMS中定义视图时不能使用ORDER BY语句。原因在于视图和表一样，数据行都是没有顺序的。

基于单表的视图和基于多表的视图

修改视图结构

修改视图结构的基本语法如下：

ALTER VIEW <视图名> AS <SELECT语句>

其中视图名在数据库中需要是唯一的，不能与其他视图和表重名。

更新视图内容

因为视图是一个虚拟表，所以对视图的操作就是对底层基础表的操作，所以在修改时只有满足底层基本表的定义才能成功修改。
如果一个视图来说包含以下结构的任意一种，那么这个视图是不可以被更新的：

• 聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
• DISTINCT 关键字。
• GROUP BY 子句。
• HAVING 子句。
• UNION 或 UNION ALL 运算符。
• FROM 子句中包含多个表。

视图归根结底还是从表派生出来的，因此，如果原表可以更新，那么视图中的数据也可以更新。反之亦然，如果视图发生了改变，而原表没有进行相应更新的话，就无法保证数据的一致性了。
注意：这里虽然修改成功了，但是并不推荐这种使用方式。而且我们在创建视图时也尽量使用限制不允许通过视图来修改表。

删除视图

删除视图的基本语法如下：

DROP VIEW <视图名1> [ , <视图名2> …]

子查询

子查询定义

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1 开始引入，在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。

子查询 vs 视图

子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中。其中AS studentSum可以看作是子查询的名称，而且由于子查询是一次性的，所以子查询不会像视图那样保存在存储介质中， 而是在 SELECT 语句执行之后就消失了。

嵌套子查询

在一个子查询中可以继续嵌套子查询。
不过，虽然嵌套子查询可以查询出结果，但是随着子查询嵌套的层数的叠加，SQL语句不仅会难以理解而且执行效率也会很差，所以要尽量避免这样的使用。

标量子查询

标量就是单一的意思，标量子查询就是单一子查询的意思。而所谓单一，就是要求我们执行的SQL语句只能返回一个值，也就是要返回表中具体的某一行的某一列。
让我们看如何通过标量子查询语句查询出销售单价高于平均销售单价的商品。

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

上面的这条语句首先后半部分查询出product表中的平均售价，前面的sql语句在根据WHERE条件挑选出合适的商品。
由于标量子查询的特性，导致标量子查询不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用。也就是说， 能够使用常数或者列名的地方，无论是 SELECT 子句、GROUP BY 子句、HAVING 子句，还是 ORDER BY 子句，几乎所有的地方都可以使用。
我们还可以这样使用标量子查询：

SELECT product_id,
       product_name,
       sale_price,
       (SELECT AVG(sale_price)
          FROM product) AS avg_price
  FROM product;

关联子查询

所谓关联子查询，既然包含关联两个字，那么一定意味着查询与子查询之间存在着联系。
关联子查询就是通过一些标志将内外两层的查询连接起来起到过滤数据的目的。
选取出各商品种类中高于该商品种类的平均销售单价的商品。SQL语句如下：

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语句也就是关联子查询中我们将外面的product表标记为p1，将内部的product设置为p2，而且通过WHERE语句连接了两个查询。
关于关联子查询的具体执行过程，可以参考文章：简单易懂教你学会SQL关联子查询，可以简要概括为：

• 首先执行不带WHERE的主查询
• 根据主查询结果匹配product_type，获取子查询结果
• 将子查询结果再与主查询结合执行完整的SQL语句

注意：在子查询中像标量子查询，嵌套子查询或者关联子查询可以看作是子查询的一种操作方式即可。

函数

sql自带了各种各样的函数，极大提高了sql语言的便利性。
所谓函数，有输入值（参数），就有返回值。
函数总个数超过200个，不需要完全记住，常用函数有 30~50 个，其他不常用的函数使用时查阅文档即可。
函数大致可分为如下五类：

1. 算术函数 （用来进行数值计算的函数）
2. 字符串函数 （用来进行字符串操作的函数）
3. 日期函数 （用来进行日期操作的函数）
4. 转换函数 （用来转换数据类型和值的函数）
5. 聚合函数 （用来进行数据聚合的函数）

待继续更新

谓词

CASE 表达式