SQL入门学习03-复杂一点的排序

参与Datawhale组队学习任务，开始从零学习SQL，repo地址： https://github.com/datawhalechina/wonderful-sql。

一、视图

视图是一个虚拟的表，通过select语句可以根据原有的表创建一张虚拟的表格，然后在这张虚拟表上做SQL操作。其不是**真正地保存了实际的数据，**只是构建了一个窗口是我们可以看到真实存在的数据。具有如下一个特性：

1. 数据不是真实存在于视图上的，创建视图可以使用select语句保存来提高效率。
   CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT 语句>;
2. 通过定义视图可以不公开数据表的全部字段，有助于保持数据的隐私性。
3. 修改视图可以更新原表中窗口对应的那一个部分，修改原表也会更新视图中间的数据。

UPDATE <视图名>
SET <语句>
WHERE<条件>;

4. 可以根据视图建立新的视图。

二、子查询

子查询是将一个查询嵌套到另一个查询的查询，不同于视图，子查询中的嵌套是一次性的，不像视图那样会被保存下来。

2.1 标量子查询

查询返回的是一个值（标量）的子查询叫做标量子查询如：

SELECT product_id, product_name, sale_price
 FROM product
WHERE sale_price > (SELECT AVG(sale_price) FROM product);

2.2 关联子查询

查询和子查询之间通过一些标志将两层连接起来，如：

SELECT product_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 算术函数

函数	公式	备注
绝对值	ABS(数值)	参数为NULL的时候返回也是NULL
求余	MOD(被除数，除数)	SQL Server不能使用
四舍五入	ROUND(数值，保留小数的位数)	参数是变量的时候可能会报错

3.2 字符串函数

函数	公式	备注
拼接	CONCAT(str1，str2....)	/
字符串长度	LENGTH(字符串)	/
小写转换	LOWER(字符串)	只能用于英文
字符串替换	REPLACE(对象字符串，替换前的字符串，替换后的字符串)	/
字符串截取	SUBSTRING(对象字符串 FROM 起始位置 FOR 截取字符数)	/
按索引截取	SUBSTRING_INDEX(字符串，分隔符，n)	/
重复	REPEAT(字符串，重复次数)	/

3.3 日期函数

函数	公式
获取当前日期	CURRENT_DATE
获取当前时间	CURRENT_TIME
截取日期元素	EXTRACT(日期元素 FROM 日期）

3.4 转换函数

函数	公式
类型转换	CAST(转换前的值 AS 想要转换的数据类型)
将NULL转换成其他值	COALESCE(数据1，数据2，...)

3.5 谓词

谓词	含义
LIKE	字符串的一部分查询
BETWEEN	进行范围查询，包含前后
IS NULL\IS NOT NULL	判断是不是NULL
IN	替换OR简便表达式
EXISTS	判断是否满足某种条件

3.5 CASE表达式

用在分支结构，作为条件分支。

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

作业部分

1. 创建出满足下述三个条件的视图（视图名称为 ViewPractice5_1）。使用 product（商品）表作为参照表，假设表中包含初始状态的 8 行数据。

条件 1：销售单价大于等于 1000 日元。
条件 2：登记日期是 2009 年 9 月 20 日。
条件 3：包含商品名称、销售单价和登记日期三列。
对该视图执行 SELECT 语句的结果如下所示。
答：

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

2. 向习题一中创建的视图ViewPractice5_1 中插入如下数据，会得到什么样的结果？为什么
答：无法插入数据，因为product表设置为不能有空。
3.请根据如下结果编写 SELECT 语句，其中 sale_price_avg 列为全部商品的平均销售单价。
答：

select product_id , product_name , product_type , sale_price ,
       (select avg(sale_price) from product) as sale_price_avg
from product
where product_id <= 8;

4.请根据习题一中的条件编写一条 SQL 语句，创建一幅包含如下数据的视图（名称为AvgPriceByType）。
答：

create view avgpricebytype(product_id , product_name, sale_price , sale_price_avg_type)
as
    select p1.product_id , p1.product_name, p1.sale_price , p2.avg_price
    from product as p1 ,
         (select product_type , avg(sale_price) as avg_price
          from product
          group by product_type
          ) as p2
    where p1.product_id <= 8 and p2.product_type = p1.product_type;

5. 判断题
四则运算中含有 NULL 时（不进行特殊处理的情况下），运算结果是否必然会变为NULL ？
答：是的
6. 对本章中使用的 product（商品）表执行如下 2 条 SELECT 语句，能够得到什么样的结果呢？
1 . 选出购买价格不在500，2800，5000的产品名字和购买价格。

2. 因为有null的存在，返回为空。
7.按照销售单价( sale_price )对练习 3.6 中的 product（商品）表中的商品进行如下分类。

低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、 圆珠笔）
中档商品：销售单价在1001日元以上3000日元以下（菜刀）
高档商品：销售单价在3001日元以上（运动T恤、高压锅）
请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句，结果如下所示。
答：

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 high_price
from product;