Task03：复杂查询方法--视图、子查询、函数、谓词、case表达式

本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql

视图

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

视图与表有什么区别
视图并不是数据库真实存储的数据表，它可以看作是一个窗口。通过这个窗口我们可以看到数据库表中真实存在的数据。所以我们要区别视图和数据表的本质，即视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。
记为：视图不是表，视图是虚表，视图依赖于表

为什么会存在视图
那既然已经有数据表了，为什么还需要视图呢？主要有以下几点原因：

• 通过定义视图可以将频繁使用的SELECT语句保存以提高效率。
• 通过定义视图可以使用户看到的数据更加清晰。
• 通过定义视图可以不对外公开数据表全部字段，增强数据的保密性。
• 通过定义视图可以降低数据的冗余。

创建视图

create view 视图名称（列名1，列名2，...）as select语句

修改视图

alter view 视图名 as select语句

删除视图

drop view 视图名

注意

• SELECT 语句中列的排列顺序和视图中列的排列顺序相同， SELECT 语句中的第 1 列就是视图中的第 1 列， SELECT 语句中的第 2 列就是视图中的第 2 列
• 视图名在数据库中需要是唯一的，不能与其他视图和表重名
• 视图不仅可以基于真实表，我们也可以在视图的基础上继续创建视图，虽然在视图上继续创建视图的语法没有错误，但是我们还是应该尽量避免这种操作。这是因为对多数 DBMS 来说， 多重视图会降低 SQL 的性能。
• 在一般的DBMS中定义视图时不能使用ORDER BY语句,这是因为视图和表一样，数据行都是没有顺序的

更新视图内容
因为视图是一个虚拟表，所以对视图的操作就是对底层基础表的操作，所以在修改时只有满足底层基本表的定义才能成功修改。
视图归根结底还是从表派生出来的，因此，如果原表可以更新，那么 视图中的数据也可以更新。反之亦然，如果视图发生了改变，而原表没有进行相应更新的话，就无法保证数据的一致性了。
在更新视图时，原表也会被相应更新，同时，原表只会更新部分，就对应视图的那部分，不会全部更新。因此，我们在创建视图时也尽量使用限制不允许通过视图来修改表

子查询
什么是子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询

子查询和视图的关系
子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中
而且由于子查询是一次性的，所以子查询不会像视图那样保存在存储介质中， 而是在 SELECT 语句执行之后就消失了。

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

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

函数

所谓函数，类似一个黑盒子，你给它一个输入值，它便按照预设的程序定义给出返回值，输入值称为参数

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

算数函数
MOD – 求余数
MOD( 被除数，除数 )

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

字符串函数
CONCAT – 拼接
CONCAT(str1, str2, str3)

LENGTH – 字符串长度length
LENGTH( 字符串 )

LOWER – 小写转换lower
LOWER 函数只能针对英文字母使用，它会将参数中的字符串全都转换为小写，类似的， UPPER 函数用于大写转换。upper

REPLACE – 字符串的替换replace
REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )

SUBSTRING – 字符串的截取substring
SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）

SUBSTRING_INDEX – 字符串按索引截取
SUBSTRING_INDEX (原始字符串， 分隔符，n)
该函数用来获取原始字符串按照分隔符分割后，第 n 个分隔符之前（或之后）的子字符串，支持正向和反向索引

日期函数

CURRENT_DATE – 获取当前日期current_date
CURRENT_TIME – 当前时间current_time 几点几分
CURRENT_TIMESTAMP – 当前日期和时间 current_timrstamp

EXTRACT – 截取日期元素extract
EXTRACT(日期元素 FROM 日期)
使用 EXTRACT 函数可以截取出日期数据中的一部分，例如“年”,“月”，或者“小时”“秒”等。该函数的返回值并不是日期类型而是数值类型

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,

转换函数

CAST – 类型转换
CAST（转换前的值 AS 想要转换的数据类型）

COALESCE – 将NULL转换为其他值coalesce
COALESCE(数据1，数据2，数据3……)
该函数会返回可变参数 A 中左侧开始第 1个不是NULL的值。参数个数是可变的，因此可以根据需要无限增加。

谓词

什么是谓词
谓词就是返回值为真值的函数。包括TRUE / FALSE / UNKNOWN。
谓词主要有以下几个：
LIKE
BETWEEN
IS NULL、IS NOT NULL
IN
EXISTS

LIKE谓词 – 用于字符串的部分一致查询
%是代表“零个或多个任意字符串”的特殊符号
_下划线匹配任意 1 个字符

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

IS NULL、 IS NOT NULL – 用于判断是否为NULL
为了选取出某些值为 NULL 的列的数据，不能使用 =，而只能使用特定的谓词IS NULL。

IN谓词 – OR的简便用法
需要注意的是，在使用IN 和 NOT IN 时是无法选取出NULL数据的
not in 后面不能含有NULL，否则查询结果通常为空。
not in 结果中一般也不含有NULL的结果，因为谓词无法与NULL进行比较

EXIST 谓词
谓词的作用就是 “判断是否存在满足某种条件的记录”。

EXIST 的左侧并没有任何参数。 EXIST 是只有 1 个参数的谓词。 EXIST 只需要在右侧书写 1 个参数，该参数通常都会是一个子查询。

使用NOT EXIST替换NOT IN
NOT EXIST 与 EXIST 相反，当“不存在”满足子查询中指定条件的记录时返回真（TRUE）。

CASE 表达式

上述语句执行时，依次判断 when 表达式是否为真值，是则执行 THEN 后的语句，如果所有的 when 表达式均为假，则执行 ELSE 后的语句。
无论多么庞大的 CASE 表达式，最后也只会返回一个值。
ELSE 子句也可以省略不写，这时会被默认为 ELSE NULL
此外， CASE 表达式最后的“END”是不能省略的

应用场景1：根据不同分支得到不同列值

应用场景2：实现列方向上的聚合
实现行的方向上不同种类的聚合 —group by
在列的方向上展示不同种类额聚合值 ----聚合函数 + CASE WHEN

应用场景3：实现行转列
行转列 —聚合函数 + CASE WHEN 表达式即可实现该转换


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

习题

为啥不能直接group by 了求平均数呢，为啥还要嵌套一个子查询呢？？？

select sum(case when sale_price <=1000 then 1 else 0 then ) as low_price,
sum(case when sale_price between 1001 and 3000 then 1 else 0 then ) as mid_price,
sum(case when sale_price >=3001 then 1 else 0 then)
as high_price
from product;