学习笔记与练习 | SQL入门（三）| 复杂一点的查询

---

视图

视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的（会在下面具体介绍），所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。

1. 视图不仅可以基于真实表，我们也可以在视图的基础上继续创建视图。

2. 虽然在视图上继续创建视图的语法没有错误，但是我们还是应该尽量避免这种操作。这是因为对多数 DBMS 来说， 多重视图会降低
   SQL的性能。

3. 需要注意的是在一般的DBMS中定义视图时不能使用ORDER BY语句。这是因为视图和表一样，数据行都是没有顺序的。

创建视图

基于单表：

CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type ;

基于多表：注意WHERE中的列名前面要带表名如product.

CREATE VIEW view_shop_product(product_type, sale_price, shop_name)
AS
SELECT product_type, sale_price, shop_name
  FROM product,
       shop_product
 WHERE product.product_id = shop_product.product_id;

修改视图

基本语句：ALTER VIEW <视图名> AS <SELECT语句>
其中的WHERE可以不是视图中的列名！

ALTER VIEW productSum
    AS
        SELECT product_type, sale_price
          FROM Product
         WHERE regist_date > '2009-09-11';

更新与删除视图

因为视图是一个虚拟表，所以对视图的操作就是对底层基础表的操作，所以在修改时只有满足底层基本表的定义才能成功修改。

对于一个视图来说，如果包含以下结构的任意一种都是不可以被更新的：

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

不建议通过视图修改表

--注意删除视图需要有权限
DROP VIEW productSum;

子查询

子查询不会像视图那样保存在存储介质中， 而是在 SELECT 语句执行之后就消失了。减少嵌套子查询

标量子查询

标量：返回一个值。
由于标量子查询的特性，导致标量子查询不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用。也就是说， 能够使用常数或者列名的地方，无论是 SELECT 子句、GROUP BY 子句、HAVING 子句，还是 ORDER BY 子句，几乎所有的地方都可以使用。

例子1：

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

例子2：

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

关联子查询

通过一些标志将内外两层的查询连接起来起到过滤数据的目的

例子：选取出各商品种类中高于该商品种类的平均销售单价的商品。

• 第二条SQL语句也就是关联子查询中我们将外面的product表标记为p1，将内部的product设置为p2，而且通过WHERE语句连接了两个查询。
• 首先执行不带WHERE的主查询
• 根据主查询结果匹配product_type，获取子查询结果
• 将子查询结果再与主查询结合执行完整的SQL语句

SELECT product_type, product_name, sale_price
  FROM product AS p1
 WHERE sale_price >