三、复杂一点的查询

 笔记来源：DATAWHALE - 一个热爱学习的社区 (linklearner.com)

目录

1.视图 

视图与表的区别： 

2.子查询

子查询和视图的关系：

关联子查询：

​​3.函数

4.谓词

 LIKE谓词 -- 用于字符串的部分一致查询

BETWEEN谓词 -- 用于范围查询

IS NULL、 IS NOT NULL -- 用于判断是否为NULL

5.Case表达式

 6.作业

---

1.视图 

视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的。

​

视图与表的区别： 

“是否保存了实际的数据”。

即视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。

注意：

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

这是因为视图和表一样，数据行都是没有顺序的。

2.子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询。

子查询和视图的关系：

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

关联子查询：

类似滑动窗口，不同类别之间进行运算，查出每一行的数据。

选取出各商品种类中高于该商品种类的平均销售单价的商品！！！

​​3.函数

• 算术函数 （用来进行数值计算的函数）
• 字符串函数 （用来进行字符串操作的函数）
• 日期函数 （用来进行日期操作的函数）
• 转换函数 （用来转换数据类型和值的函数）
• 聚合函数 （用来进行数据聚合的函数）

具体用法见顶部链接

4.谓词

 LIKE谓词 -- 用于字符串的部分一致查询

%匹配零个或多个字符串，_仅匹配一个字符

BETWEEN谓词 -- 用于范围查询

注意：闭区间！

IS NULL、 IS NOT NULL -- 用于判断是否为NULL

注意：不能用=号，只能用is或者is not判断是不是null

5.Case表达式

case类似if else判断，对每一行数据进行判断标注。

​

 6.作业

-- 3.1 创建出满足下述三个条件的视图（视图名称为 ViewPractice5_1）。
-- 使用 product（商品）表作为参照表，假设表中包含初始状态的 8 行数据。
drop view ViewPractice5_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 向习题一中创建的视图 ViewPractice5_1 中插入如下数据，会得到什么样的结果？为什么？
INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');
-- 报错，因为视图不是真实存在的表，不能进行插入操作

-- 3.3 请根据如下结果编写 SELECT 语句，其中 sale_price_avg 列为全部商品的平均销售单价。
select product_id,product_name,product_type,sale_price,(select avg(sale_price) from product) sale_price_avg
from product;

-- 3.4 请根据习题一中的条件编写一条 SQL 语句，创建一幅包含如下数据的视图（名称为AvgPriceByType）。
drop view AvgPriceByType;
create view AvgPriceByType(product_id,product_name,product_type,sale_price,sale_price_avg_type)
    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) sale_price_avg_type
    from product p1;

select * from AvgPriceByType;

-- 3.5
-- 四则运算中含有 NULL 时（不进行特殊处理的情况下），运算结果是否必然会变为NULL ？
-- 不会

-- 3.6
SELECT product_name, purchase_price
  FROM product
 WHERE purchase_price IN (500, 2800, 5000,null);


SELECT product_name, purchase_price
  FROM product
 WHERE purchase_price NOT IN (500, 2800, 5000, NULL);
 -- 为什么什么也没返回，因为任何行都不满足null

select * from product;
select product_name from product where purchase_price in (null);
-- 什么也不返回

-- 3.7
select sum(case when sale_price <=1000 then 1 else 0 end) low_price,
       sum(case when sale_price between 1001 and 3000 then 1 else 0 end) mid_price,
       sum(case when sale_price >3000 then 1 else 0 end) hight_price
from product;