Task 03 : 复杂一点的查询

1. 基础知识

3.1视图
3.1.1 什么是视图
3.1.2 视图与表有什么区别
3.1.3 为什么会存在视图
3.1.4 如何创建视图
3.1.5 如何修改视图结构
3.1.6 如何更新视图内容
3.1.7 如何删除视图

3.2 子查询
3.2.1 什么是子查询
3.2.2 子查询和视图的关系
3.2.3 嵌套子查询
3.2.4 标量子查询
3.2.5 标量子查询有什么用
3.2.6 关联子查询

3.3 各种各样的函数
3.3.1 算数函数
3.3.2 字符串函数
3.3.3 日期函数
3.3.4 转换函数

3.4 谓词
3.4.1 什么是谓词
3.4.2 LIKE谓词 – 用于字符串的部分一致查询
3.4.3 BETWEEN谓词 – 用于范围查询
3.4.4 IS NULL、 IS NOT NULL – 用于判断是否为NULL
3.4.5 IN谓词 – OR的简便用法
3.4.6 使用子查询作为IN谓词的参数
3.4.7 EXIST 谓词

3.5 CASE 表达式
3.5.1 什么是 CASE 表达式？
3.5.2 CASE表达式的使用方法

2. 练习题

3.1
创建出满足下述三个条件的视图（视图名称为 ViewPractice5_1）。使用 product（商品）表作为参照表，假设表中包含初始状态的 8 行数据。

条件 1：销售单价大于等于 1000 日元。
条件 2：登记日期是 2009 年 9 月 20 日。
条件 3：包含商品名称、销售单价和登记日期三列。
对该视图执行 SELECT 语句的结果如下所示。

SELECT * FROM ViewPractice5_1;

执行结果

product_name | sale_price | regist_date
--------------+------------+------------
T恤衫         | 　 1000    | 2009-09-20
菜刀          |    3000    | 2009-09-20

答：

CREATE VIEW ViewPractice5_1 AS
SELECT product_name, sale_price, regist_date
FROM Product
WHERE sale_price >= 1000
AND regist_date = '2009-09-20';

3.2
向习题一中创建的视图 ViewPractice5_1 中插入如下数据，会得到什么样的结果呢？

INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');

答：会错误，因为与product表的列不同

3.3
请根据如下结果编写 SELECT 语句，其中 sale_price_all 列为全部商品的平均销售单价。

product_id | product_name | product_type | sale_price | sale_price_all
------------+-------------+--------------+------------+---------------------
0001       | T恤衫         | 衣服         | 1000       | 2097.5000000000000000
0002       | 打孔器        | 办公用品      | 500        | 2097.5000000000000000
0003       | 运动T恤       | 衣服          | 4000      | 2097.5000000000000000
0004       | 菜刀          | 厨房用具      | 3000       | 2097.5000000000000000
0005       | 高压锅        | 厨房用具      | 6800       | 2097.5000000000000000
0006       | 叉子          | 厨房用具      | 500        | 2097.5000000000000000
0007       | 擦菜板        | 厨房用具       | 880       | 2097.5000000000000000
0008       | 圆珠笔        | 办公用品       | 100       | 2097.5000000000000000

答：

select product_id, product_name, product_type, sale_price, 
(select avg(sale_price) from product) as sale_price_all
from product;

3.4
请根据习题一中的条件编写一条 SQL 语句，创建一幅包含如下数据的视图（名称为AvgPriceByType）。

product_id | product_name | product_type | sale_price | avg_sale_price
------------+-------------+--------------+------------+---------------------
0001       | T恤衫         | 衣服         | 1000       |2500.0000000000000000
0002       | 打孔器         | 办公用品     | 500        | 300.0000000000000000
0003       | 运动T恤        | 衣服        | 4000        |2500.0000000000000000
0004       | 菜刀          | 厨房用具      | 3000        |2795.0000000000000000
0005       | 高压锅         | 厨房用具     | 6800        |2795.0000000000000000
0006       | 叉子          | 厨房用具      | 500         |2795.0000000000000000
0007       | 擦菜板         | 厨房用具     | 880         |2795.0000000000000000
0008       | 圆珠笔         | 办公用品     | 100         | 300.0000000000000000

提示：其中的关键是 avg_sale_price 列。与习题三不同，这里需要计算出的 是各商品种类的平均销售单价。这与使用关联子查询所得到的结果相同。 也就是说，该列可以使用关联子查询进行创建。问题就是应该在什么地方使用这个关联子查询。

答：

Create view avgpricebytype as
select product_id, product_name, product_type,sale_price,
(select avg(sale_price) from product P2
where p1.product_type = p2.product_type) # 使用关联子查询进行结算
as avg_sale_price
from product p1;

3.5
运算或者函数中含有 NULL 时，结果全都会变为NULL ？（判断题）

答：YES

3.6
对本章中使用的 product（商品）表执行如下 2 条 SELECT 语句，能够得到什么样的结果呢？

①

SELECT product_name, purchase_price
  FROM product
 WHERE purchase_price NOT IN (500, 2800, 5000);

答：

 product_name | purchase_price
----------+-----------+------------
  打孔器                320 
  切菜板                790

②

SELECT product_name, purchase_price
  FROM product
 WHERE purchase_price NOT IN (500, 2800, 5000, NULL);

答：Empty set (0.00 sec)

3.7
按照销售单价（ sale_price）对练习 3.6 中的 product（商品）表中的商品进行如下分类。

低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、 圆珠笔）
中档商品：销售单价在1001日元以上3000日元以下（菜刀）
高档商品：销售单价在3001日元以上（运动T恤、高压锅）
请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句，结果如下所示。

执行结果

low_price | mid_price | high_price
----------+-----------+------------
        5 |         1 |         2

答：

select
sum (case when sale_price <= 1000 then 1 else 0 end) as low_price,
sum (case when sale_price between 1001 and 3000 then 1 else 0 end) as mid_price,
sum (case when sale_price > 3000 then 1 else 0 end) as high_price,
from product;