【SQL笔记】Task3

Task03：复杂查询方法-视图、子查询、函数等

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

– 3.1 视图

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

– 3.1.2 视图与表有什么区别
/视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。
视图不仅可以基于真实表，我们也可以在视图的基础上继续创建视图。(对多数 DBMS 来说， 多重视图会降低 SQL 的性能)
表中保存真实数据，视图中保存select语句/

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

– 注意：
/在一般的DBMS中定义视图时不能使用ORDER BY语句：因为视图和表一样，数据行都是没有顺序的。
在 MySQL中视图的定义是允许使用ORDER BY语句的，但是若从特定视图进行选择，而该视图使用了自己的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略。/

-- 3.1.4 如何创建视图：CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>
-- 基于单表
create view productsum (product_type, cnt_product)
as
select product_type, count(*)
from product
group 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;

-- 3.1.5 如何修改视图结构：ALTER VIEW <视图名> AS <SELECT语句>
alter view productsum
as
select product_type,sale_price
from product
where regist_date>'2009-09-11';

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

对于一个视图来说，如果包含以下结构的任意一种都是不可以被更新的：
1.聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
2.DISTINCT 关键字。
3.GROUP BY 子句。
4.HAVING 子句。
5. 或 UNION ALL 运算符。
6.FROM 子句中包含多个表。
视图归根结底还是从表派生出来的，因此，如果原表可以更新，那么 视图中的数据也可以更新。反之亦然，如果视图发生了改变，而原表没有进行相应更新的话，就无法保证数据的一致性了。
*/
update productsum
set sale_price='5000'
where product_type='办公用品'; #原表对于于视图中的行也被更新
#在创建视图时尽量使用限制不允许通过视图来修改表

-- 3.1.7 如何删除视图
drop view productsum;


-- 3.2 子查询

-- 3.2.1 什么是子查询
/*子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1 开始引入，在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。*/

-- 3.2.2 子查询和视图的关系
/*子查询是一次性的，所以子查询不会像视图那样保存在存储介质中， 而是在 SELECT 语句执行之后就消失了。*/

-- 3.2.3 嵌套子查询
select product_type, cnt_product
from (
	select * 
	from (
		select product_type, count(*) as cnt_product
		from product
		group by product_type
	) as productsum
	where cnt_product = 4
) as productsum2; #最外层这个没必要，本来就只剩这两列了

-- 3.2.4 标量子查询
#1.查询出销售单价高于平均销售单价的商品
select * 
from product
where sale_price>(select avg(sale_price) from product); 
#2.查询出注册日期最晚的那个商品
select * 
from product
where regist_date=(select max(regist_date) from product);

select * 
from product 
order by regist_date desc 
limit 0,1; #分页

-- 3.2.5 关联子查询
#查询出销售单价高于本类商品平均销售单价的商品
select poduct_type, product_name, sale_price
from product as p1
where sale_price>(
	select avg(sale_price) from product as p2
	where p1.product_type=p2.product_type
	group by product_type
);

-- 小结
-- 练习题-第一部分
-- 3.1：
create view ViewPractices5_1
as
select product_name, sale_price, regist_date
from product
where sale_price>=1000 and regist_date='2009-0-20';
-- 3.2：向习题一中创建的视图 ViewPractice5_1 中插入如下数据，会得到什么样的结果呢？
-- 3.3
select product_id, product_name, product_type, sale_price,
	(select avg(sale_price) from product) as sale_price_all 
from product;

-- 3.4
create view AvgPriceByType
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


-- 3.3 各种各样的函数

-- 3.3.1 算数函数
-- DDL ：创建表，演示计算
USE shop;
DROP TABLE IF EXISTS samplemath;
CREATE TABLE samplemath
(m float(10,3),
n INT,
p INT);
-- DML ：插入数据
START TRANSACTION; -- 开始事务
INSERT INTO samplemath(m, n, p) VALUES (500, 0, NULL);
INSERT INTO samplemath(m, n, p) VALUES (-180, 0, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, NULL, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 7, 3);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 5, 2);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 4, NULL);
INSERT INTO samplemath(m, n, p) VALUES (8, NULL, 3);
INSERT INTO samplemath(m, n, p) VALUES (2.27, 1, NULL);
INSERT INTO samplemath(m, n, p) VALUES (5.555,2, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 1, NULL);
INSERT INTO samplemath(m, n, p) VALUES (8.76, NULL, NULL);
COMMIT; -- 提交事务

SELECT m,
ABS(m) AS abs_col , #绝对值，参数为NULL时，返回值也是NULL
n, p,
MOD(n, p) AS mod_col, #MOD(被除数,除数)取余，小数没有余数的概念，只能对整数列求余数。（SQL Server不支持该函数，其使用%符号来计算） 参数为NULL时，返回值也是NULL
ROUND(m,1) AS round_colS #ROUND(对象数值,保留小数的位数)四舍五入（注意：当参数 保留小数的位数 为变量时，可能会遇到错误，请谨慎使用变量。）
FROM samplemath;

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

-- 3.3.3 日期函数
/*current_date-当前日期，current_tme-当前时间，current_timestamp-当前时间戳
extract(日期元素 FROM 日期)-截取日期元素*/

-- 3.3.4 转换函数
#CAST（转换前的值 AS 想要转换的数据类型）
select cast('0001' as integer) as int_col;
select cast('2009-01-10' as date) as date_col;

#COALESCE(数据1，数据2，数据3……)：返回可变参数 A 中左侧开始第 1个不是NULL的值
COALESCE(NULL, NULL, '2020-11-01') AS col_3;


-- 3.4 谓词

-- 3.4.1 什么是谓词：谓词就是返回值为真值的函数。包括TRUE / FALSE / UNKNOWN。

-- 3.4.2 LIKE谓词 – 用于字符串的部分一致查询
CREATE TABLE samplelike
( strcol VARCHAR(6) NOT NULL,
PRIMARY KEY (strcol)
samplelike);
-- DML ：插入数据
START TRANSACTION; -- 开始事务
INSERT INTO samplelike (strcol) VALUES ('abcddd');
INSERT INTO samplelike (strcol) VALUES ('dddabc');
INSERT INTO samplelike (strcol) VALUES ('abdddc');
INSERT INTO samplelike (strcol) VALUES ('abcdd');
INSERT INTO samplelike (strcol) VALUES ('ddabc');
INSERT INTO samplelike (strcol) VALUES ('abddc');
COMMIT; -- 提交事务

select * from samplelike where strcol like 'ddd%'; #以ddd开头的
select * from samplelike where strcol like '%ddd%'; #包含ddd的

select * from salmplelike where strcol like 'abc_'; #_代表1个字符

-- 3.4.3 BETWEEN谓词 – 用于范围查询
-- 选取销售单价为100～ 1000元的商品：闭区间
SELECT product_name, sale_price
FROM product
WHERE sale_price BETWEEN 100 AND 1000; 

-- 3.4.4 IS NULL、 IS NOT NULL – 用于判断是否为NULL
#为了选取出某些值为 NULL 的列的数据，不能使用 =，而只能使用特定的谓词IS NULL。
SELECT product_name, purchase_price
FROM product
WHERE purchase_price IS NULL;

-- 3.4.5 IN谓词 – OR的简便用法
SELECT product_name, purchase_price
FROM product
WHERE purchase_price IN (320, 500, 5000);

-- 3.4.6 使用子查询作为IN谓词的参数
SELECT product_name, sale_price
FROM product
WHERE product_id IN (
	SELECT product_id
  FROM shopproduct
  WHERE shop_id = '000C');

-- 3.4.7 EXIST 谓词
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);


-- 3.5 CASE 表达式

-- 3.5.1 什么是 CASE 表达式？
CASE WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     .
     .
     .
ELSE <表达式> 
END;

-- 3.5.2 CASE表达式的使用方法
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 #ELSE 子句也可以省略不写，这时会被默认为 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;

-- 行列转换：name-subject-score ———> name-c-m-e
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;

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

-- 练习题-第二部分
-- 3.5：运算或者函数中含有 NULL 时，结果全都会变为NULL.

-- 3.6：对本章中使用的 product（商品）表执行如下 2 条 SELECT 语句，能够得到什么样的结果呢？
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000); #结果包含NULL

SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000, NULL); #参数包含NULL，结果为空

-- 3.7：
/*按照销售单价（ sale_price）对练习 6.1 中的 product（商品）表中的商品进行如下分类。
低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、 圆珠笔）
中档商品：销售单价在1001日元以上3000日元以下（菜刀）
高档商品：销售单价在3001日元以上（运动T恤、高压锅）*/
select 
	count(case when sale_price<=1000 then '低档商品' else null end) as low_price,
	count(case when sale_price>1000 & sale_price<=3000 then '中档商品' else null end) as mid_price,
	count(case when sale_price>3000 then '高档商品' else null end) as high_price
from product;