天池SQL训练营（三）—— 复杂查询方法-视图、子查询、函数等

引言

本节首先介绍了视图的概念，视图可以理解为把Select搜索出的数据以文件的形式保存下来，视图是虚表，修改视图会影响原表。
之后介绍了子查询的概念，子查询可以理解为嵌套查询，在查询出的结果中查询，其中较难理解的是关联子查询。
然后介绍了一些常用的函数，如算数函数Count、Sum，字符串函数Concat、Substring、日期函数Current_Date、转换函数Cast
接着介绍一些谓词，谓词就是返回值为True或False的函数，如Like、Between、Is NOT、In、Exist等
最后介绍CASE表达式，用法类似于IF–ELSE

文本连接

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

视图

视图是虚拟的表，其存放的数据受制于原表中的真实数据，可以理解为是把Select语句搜索出来的结果以文件的形式保存下来，如下图中的Select结果。

创建视图

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

CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type
 ORDER BY product_type;

多表视图

视图的数据可以来源于多个表

CREATE VIEW view_shop_product(product_type, sale_price, shop_name)
AS
SELECT product_type, sale_price, shop_name
  FROM product,
       shop_product
 WHERE product.product_id = shop_product.product_id;

修改视图

ALTER VIEW <视图名> AS <SELECT语句>
类似于将视图删除重建

ALTER VIEW productSum
    AS
        SELECT product_type, sale_price
          FROM Product
         WHERE regist_date > '2009-09-11';

更新视图数据

由于视图数据来源于原表，因此更新视图数据会影响原表，并且，只有满足底层基本表的数据才能修改，如使用了Count()函数得到的数据不能修改，因为原表中没有。

UPDATE productsum
   SET sale_price = '5000'
 WHERE product_type = '办公用品';

删除视图

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

DROP VIEW productSum;

子查询

子查询可以理解为嵌套查询，在查询语句中查询。

SELECT stu_name
FROM (
         SELECT stu_name, COUNT(*) AS stu_cnt
          FROM students_info
          GROUP BY stu_age) AS studentSum;

标量子查询

标量是单一的意思，标量子查询就是单一子查询，意思是返回某一行中的某一列，即返回某个数值

SELECT AVG(sale_price) FROM product

这句话返回了商品中的平均售价，为一个数值

关联子查询

关联子查询是主查询和子查询之间有一定的关联，关联的方法通过分别对主查询和子查询命名，然后根据命名进行调用。

选取出各商品种类中高于该商品种类的平均销售单价的商品
SELECT product_type, product_name, sale_price
  FROM product ASp1
 WHERE sale_price > (SELECT AVG(sale_price)
   FROM product ASp2
                      WHERE p1.product_type =p2.product_type
   GROUP BY product_type);

关联子查询的执行过程比较特殊，可参考该博客

算数函数

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

字符串函数

CONCAT(str1, str2, str3) —— 拼接
LENGTH( 字符串 ) —— 字符串长度
LOWER – 小写转换
REPLACE( 对象字符串，替换前的字符串，替换后的字符串 ) —— 字符串的替换
SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）—— 字符串的截取
SUBSTRING_INDEX (原始字符串， 分隔符，n) —— 原始字符串按照分隔符分割后，第 n 个分隔符之前（或之后）的子字符串，支持正向和反向索引，索引起始值分别为 1 和 -1。

日期函数

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

转换函数

CAST（转换前的值 AS 想要转换的数据类型）—— 类型转换
COALESCE(数据1，数据2，数据3……) —— 返回可变参数 A 中左侧开始第 1个不是NULL的值。

谓词

谓词实际上是返回值为真值的函数。包括TRUE / FALSE / UNKNOWN。
LIKE谓词 – 用于字符串的部分一致查询

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

%是代表“零个或多个任意字符串”的特殊符号
_下划线匹配任意 1 个字符
BETWEEN谓词 – 用于范围查询
数值是闭区间

SELECT product_name, sale_price
FROM product
WHERE sale_price BETWEEN 100 AND 1000;

IS NULL、 IS NOT NULL – 用于判断是否为NULL
IN谓词 – OR的简便用法

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

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

SELECT product_name, sale_price
FROM product
WHERE product_id IN (SELECT product_id
  FROM shopproduct
                       WHERE shop_id = '000C');

EXIST 谓词
判断是否存在满足某种条件的记录，如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。

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);

由于 EXIST 只关心记录是否存在，因此返回哪些列都没有关系。

CASE表达式

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

SELECT  product_name,
        CASE WHEN product_type = '衣服' THEN CONCAT('A ： ',product_type)
             WHEN product_type = '办公用品'  THEN CONCAT('B ： ',product_type)
             WHEN product_type = '厨房用具'  THEN CONCAT('C ： ',product_type)
             ELSE NULL
        END AS abc_product_type
  FROM  product;

实现列方向上的聚合

SELECT SUM(CASE WHEN product_type = '衣服' THEN sale_price ELSE 0 END) AS sum_price_clothes,
       SUM(CASE WHEN product_type = '厨房用具' THEN sale_price ELSE 0 END) AS sum_price_kitchen,
       SUM(CASE WHEN product_type = '办公用品' THEN sale_price ELSE 0 END) AS sum_price_office
  FROM product;

实现行转列

SELECT name,
       SUM(CASE WHEN subject = '语文' THEN score ELSE null END) as chinese,
       SUM(CASE WHEN subject = '数学' THEN score ELSE null END) as math,
       SUM(CASE WHEN subject = '外语' THEN score ELSE null END) as english
  FROM score
 GROUP BY name;