训练营笔记——Task03：复杂查询方法-视图、子查询、函数等

#视图
视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的
视图与表的区别—“是否保存了实际的数据”。视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。
口诀：“视图不是表，视图是虚表，视图依赖于表”

创建视图：

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

视图名在数据库中需要是唯一的，不能与其他视图和表重名。
不仅可以基于真实表，我们也可以在视图的基础上继续创建视图。不过还是要避免多重视图

一般的DBMS中定义视图时不能使用ORDER BY语句视图和表数据行没有顺序

在 MySQL中视图的定义是允许使用 ORDER BY 语句的，但是若从特定视图进行选择，而该视图使用了自己的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略。

修改视图结构的基本语法

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

对于一个视图来说，如果包含以下结构的任意一种都是不可以被更新的：

• 聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
• DISTINCT 关键字。
• GROUP BY 子句。
• HAVING 子句。
• UNION 或 UNION ALL 运算符。
• FROM 子句中包含多个表。

删除视图的基本语法：
注意：需要有相应的权限才能成功删除。

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

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

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

标量子查询（单一子查询）
所谓单一就是要求我们执行的SQL语句只能返回一个值，也就是要返回表中具体的某一行的某一列。

关联查询的执行过程

1. 首先执行不带WHERE的主查
2. 根据主查询讯结果匹配product_type，获取子查询结果
3. 将子查询结果再与主查询结合执行完整的SQL语句

#函数
算术函数 （用来进行数值计算的函数）
ABS – 绝对值：当 ABS 函数的参数为NULL时，返回值也是NULL。
MOD – 求余数：小数没有余数的概念，只能对整数列求余数。
ROUND – 四舍五入：当参数 保留小数的位数 为变量时，可能会遇到错误

字符串函数 （用来进行字符串操作的函数）
CONCAT – 拼接
LENGTH – 字符串长度
LOWER – 小写转换
REPLACE – 字符串替换
SUBSTRING – 字符串的截取
SUBSTRING_INDEX – 字符串按索引截取

日期函数 （用来进行日期操作的函数）
CURRENT_DATE – 获取当前日期
CURRENT_TIME – 当前时间
CURRENT_TIMESTAMP – 当前日期和时间
EXTRACT – 截取日期元素（返回值为数值类型）

转换函数 （用来转换数据类型和值的函数）
CAST – 类型转换
COALESCE – 将NULL转换为其他值
（该函数会返回可变参数 A 中左侧开始第 1个不是NULL的值。参数个数是可变的，因此可以根据需要无限增加。）

聚合函数 （用来进行数据聚合的函数）

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

LIKE：用于字符串部分的一致查询
前方一致：选取出“dddabc”
中间一致：选取出“abcddd”,“dddabc”,“abdddc”
后方一致：选取出“abcddd“
_下划线匹配任意 1 个字符

BETWEEN：用于范围查询
BETWEEN 的特点就是结果中会包含 100 和 1000 这两个临界值，也就是闭区间。如果不想让结果中包含临界值，那就必须使用 < 和 >。

IS NULL、IS NOT NULL
为了选取出某些值为 NULL 的列的数据，不能使用 =，而只能使用特定的谓词IS NULL。
想要选取 NULL 以外的数据时，需要使用IS NOT NULL。

IN：OR的简便用法
IN可以使用子查询作为其参数。NOT IN 同样支持子查询作为参数，用法和 in 完全一样。

EXISTS：判断是否存在满足某种条件的记录
EXIST 只需要在右侧书写 1 个参数，该参数通常都会是一个子查询。
EXIST 可以用来替换 IN ， NOT IN 也可以用NOT EXIST来替换。

#CASE表达式
在区分情况时使用
CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。由于搜索CASE表达式包含简单CASE表达式的全部功能。
搜索表达式：

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

CASE 表达式最后的“END”是不能省略

使用场景：
应用场景1：根据不同分支得到不同列值
应用场景2：实现列方向上的聚合——聚合函数 + CASE WHEN 表达式即可实现该效果
应用场景3：实现行转列

• 当待转换列为数字时，可以使用SUM AVG MAX MIN等聚合函数；
• 当待转换列为文本时，可以使用MAX MIN等聚合函数