SQL训练营复杂查询方法学习笔记

本学习笔记为阿里云天池龙珠计划SQL训练营的学习内容，学习链接为Task03：复杂查询方法-视图、子查询、函数等-天池龙珠计划SQL训练营-天池技术圈-天池技术讨论区
 

一、学习知识点概要

3.1 视图
3.1.1 什么是视图
3.1.2 视图与表有什么区别
3.1.3 为什么会存在视图
3.1.4 如何创建视图
3.1.5 如何修改视图结构
3.1.6 如何更新视图内容
3.1.7 如何删除视图
3.2 子查询
3.2.1 什么是子查询
3.2.2 子查询和视图的关系
3.2.3 嵌套子查询
3.2.4 标量子查询
3.2.5 标量子查询有什么用
3.2.6 关联子查询
小结
练习题-第一部分
3.1
3.2
3.3
3.4
3.3 各种各样的函数
3.3.1 算数函数
3.3.2 字符串函数
3.3.3 日期函数
3.3.4 转换函数
3.4 谓词
3.4.1 什么是谓词
3.4.2 LIKE谓词 – 用于字符串的部分一致查询
3.4.3 BETWEEN谓词 – 用于范围查询
3.4.4 IS NULL、 IS NOT NULL – 用于判断是否为NULL
3.4.5 IN谓词 – OR的简便用法
3.4.6 使用子查询作为IN谓词的参数
3.4.7 EXIST 谓词
3.5 CASE 表达式
3.5.1 什么是 CASE 表达式？
3.5.2 CASE表达式的使用方法
练习题-第二部分
3.5
3.6
3.7

二、学习内容

3.1 视图

3.1.4 如何创建视图

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

• 注意事项

需要注意的是在一般的DBMS中定义视图时不能使用ORDER BY语句，这是因为视图和表一样，数据行都是没有顺序的。

3.1.5 如何修改视图结构

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

Oracle数据库：CREATE OR REPLACE VIEW <视图名> AS <SELECT语句>

3.1.6 如何更新视图内容

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

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

因为对这些视图的更新不能唯一有意义的转化为对相应基本表的更新

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

3.1.7 如何删除视图

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

3.3 各种各样的函数

ABS( )

MOD( 被除数，除数 )

ROUND( 对象数值，保留小数的位数 )

CONCAT(str1, str2, str3)//含null变null

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

SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）//索引从1开始

SUBSTRING_INDEX (原始字符串， 分隔符，n)

//该函数用来获取原始字符串按照分隔符分割后，第 n 个分隔符之前（或之后）的子字符串，支持正向和反向索引，索引起始值分别为 1 和 -1。//

CURRENT_DATE – 获取当前日期

CURRENT_TIME – 当前时间

CURRENT_TIMESTAMP – 当前日期和时间

EXTRACT(日期元素 FROM 日期)//日期元素:YEAR/MONTH/DAY/HOUR/MINUTE/SECOND

CAST（转换前的值 AS 想要转换的数据类型）//CAST('0001' AS SIGNED INTEGER)

COALESCE(数据1，数据2，数据3……)//函数会返回可变参数 A 中左侧开始第 1个不是NULL的值

3.4 谓词

LIKE %任意个   _一个

BETWEEN 闭区间

IS NULL

IS NOT NULL

IN

NOT IN (不包括null）

EXISTS

NOT EXISTS

3.5 CASE 表达式

-- 简单 CASE表达式
CASE 列(或表达式)
     WHEN <匹配值1> THEN <表达式>
     WHEN <匹配值2> THEN <表达式>
     ......
     ELSE <表达式>
END

-- 搜索 CASE表达式
CASE WHEN <判断表达式> THEN <表达式>
     WHEN <判断表达式> THEN <表达式>
     WHEN <判断表达式> THEN <表达式>
     ......
     ELSE <表达式>
END
//ELSE子句不写默认为ELSE NULL

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

三、学习问题与解答

练习题-第一部分

--3.1
CREATE VIEW ViewPractice5_1-- (product_name, sale_price, regist_date)
AS
SELECT product_name, SALE_PRICE , REGIST_DATE FROM product
WHERE REGIST_DATE=TO_date('2009-09-20','YY-MM-DD')
AND  SALE_PRICE >= 1000

--3.2
//无效

--3.3
SELECT 
product_id,
product_name,
product_type,
sale_price,
(SELECT avg(sale_price) FROM product)AS sale_price_all 
FROM PRODUCT p 

--3.4
CREATE OR REPLACE VIEW AvgPriceByType
AS
SELECT 
product_id,
product_name,
product_type,
sale_price,
(SELECT avg(SALE_PRICE) FROM product p2
WHERE p1.product_type = p2.PRODUCT_TYPE 
GROUP BY PRODUCT_TYPE) AS sale_price_all
FROM PRODUCT p1

SELECT * FROM AVGPRICEBYTYPE a

--3.6
SELECT 
	MAX(CASE 商品类型 WHEN '低档商品' THEN NM END ) AS low_price,
	MAX(CASE 商品类型 WHEN '中档商品' THEN NM END ) AS mid_price,
	MAX(CASE 商品类型 WHEN '高档商品' THEN NM END ) AS hig_price
FROM (
SELECT 商品类型,count(*) AS NM
FROM (SELECT 
	(CASE WHEN sale_price <= 1000 THEN '低档商品'
		 WHEN SALE_PRICE BETWEEN 1001 AND 3000 THEN '中档商品'
		 WHEN SALE_PRICE >= 3001 THEN '高档商品'
	END)AS 商品类型
FROM PRODUCT)T
GROUP BY 商品类型
)

---简单的：
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 hig_price
	FROM PRODUCT p2 

四、学习思考与总结

相关子查询脑子有点钝没反应过来，我感觉这里说的还是少了一点要自己再去补充知识；

行转列这题我当时写完是3×3的结构，有许多为NULL的值，就用MAX函数进行了修改得到目标结果。

NOT IN 的参数不能包含NULL，谓词无法与NULL进行比较