https://hub.fastgit.org/datawhalechina/wonderful-sql/blob/main/ch03:%20%E5%A4%8D%E6%9D%82%E4%B8%80%E7%82%B9%E7%9A%84%E6%9F%A5%E8%AF%A2.md
3.1.视图
视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的，所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。
3.1.1.如何创建视图
创建视图的基本语法如下：
CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>
其中SELECT 语句需要书写在 AS 关键字之后。 SELECT 语句中列的排列顺序和视图中列的排列顺序相同， SELECT 语句中的第 1 列就是视图中的第 1 列， SELECT 语句中的第 2 列就是视图中的第 2 列，以此类推。而且视图的列名是在视图名称之后的列表中定义的。 需要注意的是视图名在数据库中需要是唯一的，不能与其他视图和表重名。
需要注意的是在一般的DBMS中定义视图时不能使用ORDER BY语句。因为视图和表一样，数据行都是没有顺序的。
3.1.2如何修改视图结构
ALTER VIEW <视图名> AS <SELECT语句>
3.1.3如何更新视图内容
对于一个视图来说，如果包含以下结构的任意一种都是不可以被更新的：
聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
DISTINCT 关键字。
GROUP BY 子句。
HAVING 子句。
UNION 或 UNION ALL 运算符。
FROM 子句中包含多个表。
3.1.4.如何删除视图
DROP VIEW <视图名1> [ , <视图名2> …]
3.2.子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1 开始引入，在 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;
其中最内层的子查询我们将其命名为productSum，这条语句根据product_type分组并查询个数，第二层查询中将个数为4的商品查询出来，最外层查询product_type和cnt_product两列。
关联查询的执行过程
首先执行不带WHERE的主查询
根据主查询讯结果匹配product_type，获取子查询结果
将子查询结果再与主查询结合执行完整的SQL语句
练习题
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 （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"
2.向习题一中创建的视图 ViewPractice5_1 中插入如下数据，会得到什么样的结果？为什么？
INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');
product_name | sale_price | regist_date
--------------+------------+------------
T恤衫 | 1000 | 2009-09-20
菜刀 | 3000 | 2009-09-20
刀子 | 300 |2009-11-20
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) FORM product) AS sale_price_all
FROM product;
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
CREATE VIEW AvgPriceByType (product_id ,product_name , product_type ,sale_price，avg_sale_price)
AS
(SELECT product_id ,product_name , product_type ,sale_price，
（SELECT avg(sale_price) FROM product AS p2 WHERE p1.product_type =p2.product_type) AS avg_sale_price
FROM product AS p1）
3.3.各种各样的函数
ABS -- 绝对值
语法：ABS( 数值 )
ABS 函数用于计算一个数字的绝对值，表示一个数到原点的距离。
当 ABS 函数的参数为NULL时，返回值也是NULL。
MOD -- 求余数
语法：MOD( 被除数，除数 )
MOD 是计算除法余数（求余）的函数，是 modulo 的缩写。小数没有余数的概念，只能对整数列求余数。
注意：主流的 DBMS 都支持 MOD 函数，只有SQL Server 不支持该函数，其使用%符号来计算余数。
ROUND -- 四舍五入
语法：ROUND( 对象数值，保留小数的位数 )
ROUND 函数用来进行四舍五入操作。
注意：当参数 保留小数的位数 为变量时，可能会遇到错误，请谨慎使用变量。
CONCAT -- 拼接
语法：CONCAT(str1, str2, str3)
MySQL中使用 CONCAT 函数进行拼接。
LENGTH -- 字符串长度
语法：LENGTH( 字符串 )
LOWER -- 小写转换
LOWER 函数只能针对英文字母使用，它会将参数中的字符串全都转换为小写。该函数不适用于英文字母以外的场合，不影响原本就是小写的字符。
类似的， UPPER 函数用于大写转换。
REPLACE -- 字符串的替换
语法：REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )
SUBSTRING -- 字符串的截取
语法：SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
使用 SUBSTRING 函数 可以截取出字符串中的一部分字符串。截取的起始位置从字符串最左侧开始计算，索引值起始为1。
SUBSTRING（"@!#$%" FROM 3 FOR 2)
得到的结果是#$
（扩展内容）SUBSTRING_INDEX -- 字符串按索引截取
语法：SUBSTRING_INDEX (原始字符串， 分隔符，n)
该函数用来获取原始字符串按照分隔符分割后，第 n 个分隔符之前（或之后）的子字符串，支持正向和反向索引，索引起始值分别为 1 和 -1。
SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
+------------------------------------------+
| SUBSTRING_INDEX('www.mysql.com', '.', 2) |
+------------------------------------------+
| www.mysql |
+------------------------------------------+
1 row in set (0.00 sec)
SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
+-------------------------------------------+
| SUBSTRING_INDEX('www.mysql.com', '.', -2) |
+-------------------------------------------+
| mysql.com |
+-------------------------------------------+
1 row in set (0.00 sec)
（扩展内容）REPEAT -- 字符串按需重复多次
语法：REPEAT(string, number)
该函数用来对特定字符实现按需重复。
CURRENT_DATE -- 获取当前日期
CURRENT_TIME -- 当前时间
CURRENT_TIMESTAMP -- 当前日期和时间
EXTRACT -- 截取日期元素
语法：EXTRACT(日期元素 FROM 日期)
SELECT CURRENT_TIMESTAMP as now,
EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year,
EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month,
EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day,
EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour,
EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS MINute,
EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;
+---------------------+------+-------+------+------+--------+--------+
| now | year | month | day | hour | MINute | second |
+---------------------+------+-------+------+------+--------+--------+
| 2020-08-08 17:34:38 | 2020 | 8 | 8 | 17 | 34 | 38 |
+---------------------+------+-------+------+------+--------+--------+
1 row in set (0.00 sec)
CAST -- 类型转换
语法：CAST（转换前的值 AS 想要转换的数据类型）
-- 将字符串类型转换为数值类型
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
+---------+
| int_col |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
-- 将字符串类型转换为日期类型
SELECT CAST('2009-12-14' AS DATE) AS date_col;
+------------+
| date_col |
+------------+
| 2009-12-14 |
+------------+
1 row in set (0.00 sec)
COALESCE -- 将NULL转换为其他值
语法：COALESCE(数据1，数据2，数据3……)
COALESCE 是 SQL 特有的函数。该函数会返回可变参数 A 中左侧开始第 1个不是NULL的值。参数个数是可变的，因此可以根据需要无限增加。
在 SQL 语句中将 NULL 转换为其他值时就会用到转换函数。
SELECT COALESCE(NULL, 11) AS col_1,
COALESCE(NULL, 'hello world', NULL) AS col_2,
COALESCE(NULL, NULL, '2020-11-01') AS col_3;
+-------+-------------+------------+
| col_1 | col_2 | col_3 |
+-------+-------------+------------+
| 11 | hello world | 2020-11-01 |
+-------+-------------+------------+
1 row in set (0.00 sec)
3.4谓词
谓词就是返回值为真值的函数。包括TRUE / FALSE / UNKNOWN。
谓词主要有以下几个：
LIKE
BETWEEN
IS NULL、IS NOT NULL
IN
EXISTS
LIKE 用于字符串的部分一致查询
%是代表“零个或多个任意字符串”的特殊符号
_下划线匹配任意 1 个字符
EXIST谓词的使用方法
谓词的作用就是 “判断是否存在满足某种条件的记录”。
SELECT product_name, sale_price
FROM product AS p
WHERE EXISTS (SELECT *
FROM shopproduct AS sp
WHERE sp.shop_id = '000C'
AND sp.product_id = p.product_id);
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| 运动T恤 | 4000 |
| 菜刀 | 3000 |
| 叉子 | 500 |
| 擦菜板 | 880 |
+--------------+------------+
4 rows in set (0.00 sec)
3.5 CASE 表达式
CASE 表达式是函数的一种。是 SQL 中数一数二的重要功能，有必要好好学习一下。
CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支。
CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。由于搜索CASE表达式包含简单CASE表达式的全部功能。
语法：
CASE WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
.
.
.
ELSE <表达式>
END
上述语句执行时，依次判断 when 表达式是否为真值，是则执行 THEN 后的语句，如果所有的 when 表达式均为假，则执行 ELSE 后的语句。 无论多么庞大的 CASE 表达式，最后也只会返回一个值。
练习题-第二部分
5 判断题
运算中含有 NULL 时，运算结果是否必然会变为NULL ？
在使用IN 和 NOT IN 时是无法选取出NULL数据的。 NULL 只能使用 IS NULL 和 IS NOT NULL 来进行判断。
6
对本章中使用的 product（商品）表执行如下 2 条 SELECT 语句，能够得到什么样的结果呢？
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000);
选出价格不是500或2800或5000或NULL的商品及商品价格
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000, NULL);
选出价格不是500或2800或5000或NULL的商品及商品价格
7
按照销售单价( sale_price )对练习 3.6 中的 product（商品）表中的商品进行如下分类。
低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、 圆珠笔）
中档商品：销售单价在1001日元以上3000日元以下（菜刀）
高档商品：销售单价在3001日元以上（运动T恤、高压锅）
请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句，结果如下所示。
执行结果
low_price | mid_price | high_price
----------+-----------+------------
5 | 1 | 2
SELECT
COUNT（CASE WHEN sale_price<=1000 THEN product_name ELSE 0 END) AS low_price,
COUNT（CASE WHEN sale_price BETWEEN10001 AND3000 THEN product_name ELSE 0 END) AS mid_price,
COUNT（CASE WHEN sale_price >=3001 THEN product_name ELSE 0 END) AS high_price
FROM product
