第三章 复杂一点的查询

• 3.1 创建出满足下述三个条件的视图（视图名称为 ViewPractice5_1）。使用 product（商品）表作为参照表，假设表中包含初始状态的 8 行数据。
  • 条件 1：销售单价大于等于 1000 日元。
  • 条件 2：登记日期是 2009 年 9 月 20 日。
  • 条件 3：包含商品名称、销售单价和登记日期三列。
  对该视图执行 SELECT 语句的结果如下所示。

SELECT * FROM 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';

• 3.2 向习题一中创建的视图 ViewPractice5_1 中插入如下数据，会得到什么样的结果？为什么？

INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');

结果：报错:Field of view ‘shop.viewpractice5_1’ underlying table doesn’t have a default value, Time: 0.007000s
原因分析（个人见解）：从product表结构中，我们可以看出，product_id和product_type是不能为空的，我们在向视图中插入数据时，这两个字段可能默认为空值，从而我们无法给要求不能为空的product_id和product_type赋值。

• 3.3 请根据如下结果编写 SELECT 语句，其中 sale_price_all 列为全部商品的平均销售单价。
  

select
product_id,
product_name,
product_type,
sale_price,
(select avg(sale_price) FROM product) as sale_price_all
from product;

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

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
)
as sale_price_all
from product as p1;

select语句执行顺序（个人见解）：
第一步：执行主查询：

select
product_id,
product_name,
product_type,
sale_price,
sale_price_all -- 没有值，先占位
from product as p1;

第二步：

select avg(sale_price) 
from product as p2 
where p2.product_type = '衣服'

第三步：

select avg(sale_price) 
from product as p2 
where p2.product_type = '打孔器'

第四步：依次类推。

• 3.5 判断题
  运算中含有 NULL 时，运算结果是否必然会变为NULL ？
  不一定。concat(‘a’,null)是为空，做加减运算时null会转换为0
• 3.6
  对本章中使用的 product （商品）表执行如下 2 条 SELECT 语句，能够得到什么样的结果呢？
  ①

SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000);

②

SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000, NULL);

返回空，因为只有is null、is not null才可以筛选null

• 3.7
  按照销售单价( sale_price )对练习 3.6 中的 product （商品）表中的商品进行如下分类。
  • 低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、 圆珠笔）
  • 中档商品：销售单价在1001日元以上3000日元以下（菜刀）
  • 高档商品：销售单价在3001日元以上（运动T恤、高压锅）
  请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句，结果如下所示。

SELECT 
count(case when sale_price <= 1000 then '低档商品' else null end) as low_price,
count(case when sale_price > 1000 and 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;