Task03：复杂查询方法-视图、子查询、函数等-天池龙珠计划SQL训练营

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

3.1视图

操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。
区别：是否保存了实际的数据
视图不是表，视图是虚表，视图依赖于表
视图的优势
通过定义视图可以将频繁使用的SELECT语句保存以提高效率。
通过定义视图可以使用户看到的数据更加清晰。
通过定义视图可以不对外公开数据表全部字段，增强数据的保密性。
通过定义视图可以降低数据的冗余。

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

其中SELECT 语句需要书写在 AS 关键字之后。 SELECT 语句中列的排列顺序和视图中列的排列顺序相同， SELECT 语句中的第 1 列就是视图中的第 1 列， SELECT 语句中的第 2 列就是视图中的第 2 列，以此类推。而且视图的列名是在视图名称之后的列表中定义的。
需要注意的是视图名在数据库中需要是唯一的，不能与其他视图和表重名。
修改视图结构

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

更新视图内容
因为视图是一个虚拟表，所以对视图的操作就是对底层基础表的操作，所以在修改时只有满足底层基本表的定义才能成功修改。

对于一个视图来说，如果包含以下结构的任意一种都是不可以被更新的：
聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
DISTINCT 关键字。
GROUP BY 子句。
HAVING 子句。
UNION 或 UNION ALL 运算符。
FROM 子句中包含多个表。

3.2子查询

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

嵌套子查询

标量子查询

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

关联子查询

3.3 各种函数

3.3.1 算数函数

ABS – 绝对值 —— ABS( 数值 )
MOD – 求余数 ——MOD( 被除数，除数 )
ROUND – 四舍五入——ROUND( 对象数值，保留小数的位数 )

3.3.2 字符串函数

CONCAT – 拼接 —— CONCAT(str1, str2, str3)
LENGTH – 字符串长度 —— LENGTH( 字符串 )
LOWER – 小写转换 —— LOWER()
REPLACE – 字符串的替换 —— REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )
SUBSTRING – 字符串的截取 —— SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）

3.3.3 日期函数

CURRENT_DATE – 获取当前日期
CURRENT_TIME – 当前时间
CURRENT_TIMESTAMP – 当前日期和时间
EXTRACT – 截取日期元素

3.3.4 转换函数

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

3.4谓词

谓词就是返回值为真值的函数。包括TRUE / FALSE / UNKNOWN
谓词主要有以下几个：

LIKE
BETWEEN
IS NULL、IS NOT NULL
IN
EXISTS

LIKE谓词 – 用于字符串的部分一致查询

当需要进行字符串的部分一致查询时需要使用该谓词。
部分一致大体可以分为前方一致、中间一致和后方一致三种类型。

前方一致：选取出“dddabc” —— ddd%
前方一致即作为查询条件的字符串（这里是“ddd”）与查询对象字符串起始部分相同。
其中的%是代表“零个或多个任意字符串”的特殊符号，本例中代表“以ddd开头的所有字符串”。

SELECT *
FROM samplelike
WHERE strcol LIKE 'ddd%';

中间一致：选取出“abcddd”,“dddabc”,“abdddc”

SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd%';

_下划线匹配任意 1 个字符

使用 _（下划线）来代替 %，与 % 不同的是，它代表了“任意 1 个字符”。

SELECT *
FROM samplelike
WHERE strcol LIKE 'abc__';
+--------+
| strcol |
+--------+
| abcdd  |
+--------+

3.4.3 BETWEEN谓词 – 用于范围查询

范围查询

WHERE sale_price BETWEEN 100 AND 1000;

BETWEEN 的特点就是结果中会包含 100 和 1000 这两个临界值，也就是闭区间。

3.4.4 IS NULL、 IS NOT NULL – 用于判断是否为NULL

3.4.5 IN谓词 – OR的简便用法

or方法

WHERE purchase_price = 320
OR purchase_price = 500
OR purchase_price = 5000;

in方法

SELECT product_name, purchase_price
FROM product
WHERE purchase_price IN (320, 500, 5000);

3.4.6 使用子查询作为IN谓词的参数

IN和子查询
N 谓词（NOT IN 谓词）具有其他谓词所没有的用法，那就是可以使用子查询作为其参数。子查询就是 SQL内部生成的表，因此也可以说“能够将表作为 IN 的参数”。同理，我们还可以说“能够将视图作为 IN 的参数”。

WHERE product_id IN (SELECT product_id
  FROM shopproduct
                       WHERE shop_id = '000C');

WHERE product_id IN ('0003', '0004', '0006', '0007');

子查询与IN的实际应用区别
①：实际生活中，某个门店的在售商品是不断变化的，使用 in 谓词就需要经常更新 sql 语句，降低了效率，提高了维护成本；

②：实际上，某个门店的在售商品可能有成百上千个，手工维护在售商品编号真是个大工程。

使用子查询即可保持 sql 语句不变，极大提高了程序的可维护性，这是系统开发中需要重点考虑的内容。

3.4.7 EXIST 谓词

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

如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。

EXIST（存在）谓词的主语是“记录”。

由于 EXIST 只关心记录是否存在，因此返回哪些列都没有关系。 EXIST 只会判断是否存在满足子查询中 WHERE 子句指定的条件

3.5 CASE 表达式

CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支。

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

上述语句执行时，依次判断 when 表达式是否为真值，是则执行 THEN 后的语句，如果所有的 when 表达式均为假，则执行 ELSE 后的语句。
无论多么庞大的 CASE 表达式，最后也只会返回一个值。

作者：Datawhale成员：王复振、杨煜、闫钟峰、杨梦迪、苏鹏