SQL-task3

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

3.1 视图

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

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

在一般的DBMS中定义视图时不能使用ORDER BY语句。

在 MySQL中视图的定义是允许使用 ORDER BY 语句的

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

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

3.2 子查询

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

标量子查询 

关联子查询 

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

练习题-第一部分
3.1

CREATE VIEW ViewPractice5_1(product_name,sale_price,regist_date) AS
SELECT product_name,sale_price,regist_date FROM product
WHERE sale_price >= 1000
AND  regist_date = "2009-09-20";

SELECT * FROM ViewPractice5_1;

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
ORDER BY product_id;

3.4

CREATE VIEW AvgPriceByType(product_id,product_name,product_type,sale_price,avg_sale_price) AS
SELECT product_id,product_name,product_type,sale_price,
(SELECT AVG(sale_price) FROM product as p2 
where p1.product_type = p2.product_type 
group by product_type) 
AS avg_sale_price
from product as p1;
select * from AvgPriceByType;

3.3 各种各样的函数

• ABS – 绝对值
• MOD – 求余数

主流的 DBMS 都支持 MOD 函数，只有SQL Server 不支持该函数，其使用%符号来计算余数。

• ROUND – 四舍五入
• CONCAT – 拼接
• LENGTH – 字符串长度
• LOWER – 小写转换
• REPLACE – 字符串的替换
• SUBSTRING – 字符串的截取
• SUBSTRING_INDEX – 字符串按索引截取
• CURRENT_DATE – 获取当前日期
• CURRENT_TIME – 当前时间
• CURRENT_TIMESTAMP – 当前日期和时间
• EXTRACT – 截取日期元素
• CAST – 类型转换
• COALESCE – 将NULL转换为其他值

3.4 谓词

• LIKE  

%是代表“零个或多个任意字符串”的特殊符号

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

• BETWEEN

闭区间。如果不想让结果中包含临界值，那就必须使用 < 和 >。

• IS NULL、IS NOT NULL

为了选取出某些值为 NULL 的列的数据，不能使用 =，而只能使用特定的谓词IS NULL。

• IN

多个查询条件取并集时可以选择使用or语句。

在使用IN 和 NOT IN 时是无法选取出NULL数据的。NULL 只能使用 IS NULL 和 IS NOT NULL 来进行判断。

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

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

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

• EXISTS

实际上即使不使用 EXIST，基本上也都可以使用 IN（或者 NOT IN）来代替

EXIST 通常会使用关联子查询作为参数。

3.5 CASE 表达式

CASE

WHEN <求值表达式> THEN <表达式>

WHEN <求值表达式> THEN <表达式>

WHEN <求值表达式> THEN <表达式>

. . .

ELSE <表达式> END

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

在列的方向上展示不同种类额聚合值

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;

实现行转列

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

练习题-第二部分
3.5

正确
3.6

 该语句仅仅取出了购买价格不在括号范围内的商品名和价格，不包含购买价格为null的商品

未返回记录，参数中不能有null
3.7

 select
  count(distinct case when sale_price<=1000 then product_name else null end) as low_price,
  count(distinct case when sale_price between 1001 and 3000 then product_name else null end) as mid_price,
  count(distinct case when sale_price > 3000 then product_name else null end) as high_price
  from product;