[大数据]第三章 待整理

1 视图

• 视图与表的区别：“是否保存了实际的数据”
• “视图不是表，视图是虚表，视图依赖于表”。
  

1.1 创建视图

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

-- 基于多表的创建视图，不用join
-- create的列名可省
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;

-- 基于上述视图的查询
SELECT sale_price, shop_name
  FROM view_shop_product
 WHERE product_type = '衣服';

• ELECT 语句中的第 1 列就是视图中的第 1 列， SELECT 语句中的第 2 列就是视图中的第 2 列
• 视图的列名是在视图名称之后的列表中定义
• 视图名在数据库中需要是唯一的，不能与其他视图和表重名
• 视图不仅可以基于真实表，我们也可以在视图的基础上继续创建视图，但对多数 DBMS 来说多重视图会降低 SQL 的性能
• 在一般的DBMS中定义视图时不能使用ORDER BY语句，因为视图和表一样，数据行都是没有顺序的
• 在 MySQL中视图的定义是允许使用 ORDER BY 语句的，但是若从特定视图进行选择，而该视图使用了自己的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略

1.2 修改视图结构

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

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

1.3 更新视图内容

-- 上述修改后的productsum不含以上限制
UPDATE productsum
   SET sale_price = '5000'
 WHERE product_type = '办公用品';

• 结果中只有一条数据被更新，因为视图只是原表的一个窗口，所以它只能修改透过窗口能看到的内容
• 不推荐用这种方式修改原表

1.4 删除视图

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

-- exam
DROP VIEW productSum;

2 子查询

2.1 嵌套子查询

SELECT product_type, cnt_product
FROM (SELECT *
        FROM (SELECT product_type, 
                      COUNT(*) AS cnt_product
                FROM product 
               GROUP BY product_type) AS productsum
       WHERE cnt_product = 4) AS productsum2;

• AS后可接子查询得到的表名
• 子查询是一次性的，不会像视图那样保存在存储介质中， 而是在 SELECT 语句执行之后就消失了
• 随着子查询嵌套的层数的叠加，SQL语句不仅会难以理解而且执行效率也会很差，所以要尽量避免这样的使用

2.2 标量子查询

• 标量子查询：执行的select语句只能返回一个值，也就是要返回表中具体的某一行的某一列
• 标量子查询在任何可以使用单一值的位置都可以使用

-- 多了一个avg_price列，值都一样
SELECT product_id,
       product_name,
       sale_price,
       (SELECT AVG(sale_price)
          FROM product) AS avg_price
  FROM product;

2.3 关联子查询

• 意味着查询与子查询之间存在着联系
• 下例中将外面的product表标记为p1，将内部的product设置为p2，而且通过WHERE语句连接了两个查询

-- 查出每个类别中，sale价格高于对应类别均价的物品
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执行顺序可知：
  • 首先from得到待筛选的数据
  • 执行子查询，其中p1.producty_type是父查询当前数据所对应的type

3 函数

3.1 算术函数