Datawhale202210 Wonderful sql——Task03

课程地址：​GitHub - datawhalechina/wonderful-sql: Follow me，从 0 到 1 掌握 SQL，决胜秋招。

---

一、视图

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

       视图与表的关系：“`视图不是表，视图是虚表，视图依赖于表”。

• 创建视图的基本语法：

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

注意：在一般的DBMS中定义视图时不能使用ORDER BY语句。

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

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

也可以通过将当前视图删除然后重新创建的方式达到修改的效果。

• 删除视图的基本语法：

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

注意：需要有相应的权限才能成功删除。

二、子查询

       子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1开始引入，在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。
       子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中。而且由于子查询是一次性的，所以子查询不会像视图那样保存在存储介质中， 而是在 SELECT 语句执行之后就消失了。

嵌套子查询

       将一个SELECT语句嵌套在另一个SELECT语句中。
       例如：

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;

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

标量子查询

       执行的SQL语句只能返回一个值，也就是要返回表中具体的某一行的某一列。

关联子查询

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

三、练习题-第一部分

练习题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

代码如下：

-- 练习题3.1
USE shop;
CREATE VIEW ViewPractice5_1 (product_name,sale_price,regist_date)
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_id、product_name、product_type这三列要求填入的数据为 NOT NULL，而插入的该条数据的product_id和product_type为NULL，所以会出现错误。

练习题3.3

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

代码如下：

-- 练习题3.3
USE shop;
SELECT product_id,
       product_name,
		 product_type,
		 sale_price,
		 (SELECT AVG(sale_price) FROM product) AS sale_price_avg
  FROM product;

执行结果如下：

练习题3.4

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

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

代码如下：

-- 练习题3.4
USE shop;
CREATE VIEW AvgPriceByType(product_id,product_name,product_type,sale_price,sale_price_avg_type)
AS
SELECT product_id,
       product_name,
		 product_type,
		 sale_price,
		 (SELECT AVG(sale_price) AS sale_price_avg_type
		 FROM product AS p2
		 WHERE p1.product_type = p2.product_type
		 GROUP BY product_type)
FROM product AS p1;

执行结果：

四、SQL自带函数

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

算数函数

四则运算主要运算符：

含义	运算符
加法	+
减法	-
乘法	*
除法	/

• ABS – 绝对值
  语法：ABS( 数值 )

• MOD – 求余数
  语法：MOD( 被除数，除数 )

• ROUND – 四舍五入
  语法：ROUND( 对象数值，保留小数的位数 )

字符串函数

• CONCAT – 拼接
  语法：CONCAT(str1, str2, str3)

• LENGTH – 字符串长度
  语法：LENGTH( 字符串 )

• LOWER – 小写转换
  语法：LOWER( 字符串 )

• UPPER – 大写转换
  语法：UPPER( 字符串 )

• REPLACE – 字符串的替换
  语法：REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )

• SUBSTRING – 字符串的截取
  语法：SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）

• UBSTRING_INDEX – 字符串按索引截取
  语法：SUBSTRING_INDEX (原始字符串， 分隔符，n)

• REPEAT – 字符串按需重复多次
  语法：REPEAT(string, number)

日期函数

• CURRENT_DATE – 获取当前日期
• CURRENT_TIME – 获取当前时间
• CURRENT_TIMESTAMP – 获取当前日期和时间
• EXTRACT – 截取日期元素
  语法：EXTRACT(日期元素 FROM 日期)

转换函数

• CAST – 类型转换
  语法：CAST（转换前的值 AS 想要转换的数据类型）
• COALESCE – 将NULL转换为其他值
  语法：COALESCE(数据1，数据2，数据3……)

五、谓词

谓词就是返回值为真值的函数。包括TRUE / FALSE / UNKNOWN。

谓词主要有以下几个：

• LIKE（用于字符串的部分一致查询）
• BETWEEN（用于范围查询）
• IS NULL、IS NOT NULL（用于判断是否为NULL）
• IN（OR的简便用法）
• EXISTS（判断是否存在满足某种条件的记录）

六、CASE 表达式

    CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支。CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。

    语法：

CASE WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     .
     .
     .
ELSE <表达式>
END  

七、练习题-第二部分

练习题3.5

四则运算中含有 NULL 时（不进行特殊处理的情况下），运算结果是否必然会变为NULL ？

答：不是必然会变成NULL。

练习题3.6

    对本章中使用的 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);

答：

①返回purchase_price不为500、2800、5000的记录，如图所示：

②NULL不能使用比较运算符与其他数据进行比较，返回为空，如图所示：

练习题3.7

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

• 低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、 圆珠笔）
• 中档商品：销售单价在1001日元以上3000日元以下（菜刀）
• 高档商品：销售单价在3001日元以上（运动T恤、高压锅）

    请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句，结果如下所示：

low_price	mid_price	high_price
5	1	2

代码如下：

-- 练习题3.4
USE shop;
SELECT COUNT(CASE WHEN sale_price<=1000 THEN product_name ELSE NULL END) AS low_price,
	   COUNT(CASE WHEN (sale_price>=1001 AND sale_price<=3000) THEN product_name ELSE NULL END) AS mid_price,
	   COUNT(CASE WHEN sale_price>=3001 THEN product_name ELSE NULL END) AS high_price
  FROM product;

执行结果：