Task03-天池龙珠计划SQL训练营

学习目标

• 复杂查询方法-视图、子查询、函数等

---

学习内容

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表达式的使用方法

---

学习产出

• 练习题-Part01

• 练习题一

创建出满足下述三个条件的视图（视图名称为 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

创建视图SQL：

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

• 练习题二

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

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

插入结果：

 解释：

视图插⼊数据时，原表也会插⼊数据，⽽原表数据插⼊时不满⾜约束条件，所以会报错。（因为

ViewPractice5_1 的原表有三个带有 NOT NULL 约束的字段）

• 练习题三

请根据如下结果编写 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;

• 练习题四

请根据习题一中的条件编写一条 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_

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 
GROUP BY p1.product_type ) AS avg_sale_price 
FROM
	product p1;

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

• 练习题-Part02

• 练习题一

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

否，当运算包含NULL但结果确定时结果不为NULL，当某些函数中包含NULL时会忽略NULL进行计算，结果同样不为NULL。

• 练习题二

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

①

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

②

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

运行结果：

①

②

 

查询条件中不可含NULL，不然返回空值。

 

• 练习题三

按照销售单价（ sale_price）对练习 6.1 中的 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 >= 3001 THEN 1 ELSE 0 END ) AS high_price 
FROM
	product;