本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql；
1、视图
定义：视图是依据select语句并创建出的虚拟和临时表
查询语句：
select stu_name from view_students_info;
与表的本质区别：是否保存了实际的数据，视图并不是数据库真是存储的数据表，其数据来源均建立在真实表的基础上
视图与表的关系：视图不是表，视图是虚表，视图依赖于表
存在意义：
（1）保存频繁使用的select语句，提高效率
（2）让用户看到的数据更加清晰。
（3）不用对外公开数据表全部字段，增强数据的保密性。
（4）降低数据的冗余
2、创建视图
基本语法：
CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS
注意：1、视图名在数据库中需要是唯一的，不能与其他视图和表重名
2、可以在视图的基础上继续创建视图
3、 多重视图会降低 SQL 的性能
4、不能使用order by（特殊视图可以），B：数据行都是没有顺序
3、修改视图结构
基本语法：
alter view <视图名>as
注意：视图名在数据库中需要是唯一的，不能与其他视图和表重名
4、更新视图内容：
修改时只有满足底层基本表的定义才能成功修改
不能被更新的：
（1）聚合函数 SUM()、MIN()、MAX()、COUNT()
（2）DISTINCT 关键字。
（3）GROUP BY 子句。
（4）HAVINg 子句。
（5）UNIOn 或 UNIOn ALL 运算符。
（6）FROM 子句中包含多个表。
注意：即使修改成功了，但是并不推荐这种使用方式。而且我们在创建视图时也尽量使用限制不允许通过视图来修改表
5、删除视图
基本语法：
DROp VIEW <视图名1> [ , <视图名2> …]
注意：需要有相应的权限才能成功删除
6、子查询
定义：子查询指一个查询语句嵌套在另一个查询语句内部的查询
子查询和视图的关系：子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中
注意：由于子查询是一次性的，所以子查询不会像视图那样保存在存储介质中， 而是在 SELECt 语句执行之后就消失了。
7、嵌套子查询
嵌套子查询可以查询出结果，但是随着子查询嵌套的层数的叠加，SQL语句不仅会难以理解而且执行效率也会很差，所以要尽量避免这样的使用。
8、标量子查询
标量子查询不仅仅局限于 WHERe 子句中，通常任何可以使用单一值的位置都可以使用
能够使用常数或者列名的地方，无论是 SELECT 子句、GROUP BY 子句、HAVINg 子句，还是 ORDER BY 子句，几乎所有的地方都可以使用
9、关联子查询
- 首先执行不带WHERe的主查询
- 根据主查询讯结果匹配product_type，获取子查询结果
- 将子查询结果再与主查询结合执行完整的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
create view ViewPractice5_1(product_name,sale_price,regist_date) as select product_name,sale_price,regist_date
from shop.product
where sale_price>=1000 and regist_date="2009-9-20";
向习题一中创建的视图 ViewPractice5_1 中插入如下数据，会得到什么样的结果呢？
INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');
会发生错误，视图的更新归根结底是对原表进行更新，若成功反映到原表上的更新，才是对三视图合法的更新，由于语句中的第一、三、四列没有赋值，也没有使用默认值， 但是第一、三列都有not null约束，因此会发生错误
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
ORDER BY product_id ASC;
执行结果：
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 AvgPriceType (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;
执行结果：
10、函数
所谓函数，类似一个黑盒子，你给它一个输入值，它便按照预设的程序定义给出返回值，输入值称为参数。
（1）算术函数（用来进行数值计算的函数）
- ABS – 绝对值
语法：ABS( 数值 )
ABS 函数用于计算一个数字的绝对值，表示一个数到原点的距离。
当 ABS 函数的参数为NULL时，返回值也是NULL。
- MOD – 求余数
语法：MOD( 被除数，除数 )
MOD 是计算除法余数（求余）的函数，是 modulo 的缩写。小数没有余数的概念，只能对整数列求余数。
注意：主流的 DBMS 都支持 MOD 函数，只有SQL Server 不支持该函数，其使用%符号来计算余数。
- ROUND – 四舍五入
语法：ROUND( 对象数值，保留小数的位数 )
ROUND 函数用来进行四舍五入操作。
注意：当参数 保留小数的位数 为变量时，可能会遇到错误，请谨慎使用变量。
（2）字符串函数 （用来进行字符串操作的函数）
- CONCAT – 拼接
语法：CONCAt(str1, str2, str3)
MySQL中使用 CONCAT 函数进行拼接。
- LENGTH – 字符串长度
语法：LENGTH( 字符串 )
- LOWER – 小写转换
LOWER 函数只能针对英文字母使用，它会将参数中的字符串全都转换为小写。该函数不适用于英文字母以外的场合，不影响原本就是小写的字符。
类似的， UPPER 函数用于大写转换。
- REPLACe – 字符串的替换
语法：REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )
- SUBSTRING – 字符串的截取
语法：SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
使用 SUBSTRING 函数 可以截取出字符串中的一部分字符串。截取的起始位置从字符串最左侧开始计算，索引值起始为1。
- （扩展内容）SUBSTRING_INDEX – 字符串按索引截取
语法：SUBSTRING_INDEX (原始字符串， 分隔符，n)
该函数用来获取原始字符串按照分隔符分割后，第 n 个分隔符之前（或之后）的子字符串，支持正向和反向索引，索引起始值分别为 1 和 -1。
（3）日期函数 （用来进行日期操作的函数）
- CURRENT_TIMESTAMP – 当前日期和时间
- CURRENT_TIME – 当前时间
- CURRENT_DATE – 获取当前日期
- EXTRACT – 截取日期元素
语法：EXTRACT(日期元素 FROM 日期)
使用 EXTRACT 函数可以截取出日期数据中的一部分，例如“年”
“月”，或者“小时”“秒”等。该函数的返回值并不是日期类型而是数值类型
（4）转换函数 （用来转换数据类型和值的函数）
- CAST – 类型转换
语法：CAST（转换前的值 AS 想要转换的数据类型）
- COALESCE – 将NULL转换为其他值
语法：COALESCE(数据1，数据2，数据3……)
COALESCE 是 SQL 特有的函数。该函数会返回可变参数 A 中左侧开始第 1个不是NULL的值。参数个数是可变的，因此可以根据需要无限增加。
在 SQL 语句中将 NULL 转换为其他值时就会用到转换函数。
（5）聚合函数 （用来进行数据聚合的函数）
11、谓词：作用： “判断是否存在满足某种条件的记录
谓词就是返回值为真值的函数。包括true/false/unknown
谓词主要有：
like:用于字符串的部分一致查询，部分一致可以分为前方一致，中间一致，后方一致
注意：%是代表“零个或多个任意字符串”的特殊符号，_下划线匹配任意 1 个字符
between ：用于范围查询，使用了3个参数
注意：BETWEEN 的特点就是结果中会包含 100 和 1000 这两个临界值，也就是闭区间，如果不想让结果中包含临界值，那就必须使用 < 和 >。
is null & is not null ：用于判断是否为null
注意：选取出某些值为 NULL 的列的数据，不能使用 =，而只能使用特定的谓词IS NULL
in ：or 的简便用法
注意：多个查询条件取并集时可以选择使用or语句。
在使用IN 和 NOT IN 时是无法选取出NULL数据的
特别用法：使用子查询作为in谓词的参数
exists： 主语是“记录“
使用NOT EXIST替换NOT IN
就像 EXIST 可以用来替换 IN 一样， NOT IN 也可以用NOT EXIST来替换。
NOT EXIST 与 EXIST 相反，当“不存在”满足子查询中指定条件的记录时返回真（TRUE）。
12、case表达式
分为：简单CASE表达式和搜索CASE表达式（包含简单CASE表达式的全部功能）
搜索CASE表达式语法：
CASE WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
. . .
ELSE <表达式>
END
使用方法：
（1）根据不同的分支得到不同的列值
（2）实现列方向上的聚合
（3）实现行转列
总结：
- 当待转换列为数字时，可以使用SUM AVG MAX MIN等聚合函数；
- 当待转换列为文本时，可以使用MAX MIN等聚合函数
运算或者函数中含有 NULL 时，结果全都会变为NULL ？（判断题）T
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);
代码执⾏之前，你可能会认为该语句会返回和查询 ① 同样的结果，实际上它却返回了零条记录，
这是因为 NOT IN 的参数中不能包含 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;
执行结果：
