ch03复杂一点的查询

学习笔记

（1）视图

     创建视图 Create view <视图名> as <select语句>

     修改视图 Alter view <视图名> as <select 语句>

     删除视图 drop view <视图名>

（2）子查询

  子查询分为嵌套子查询、标量子查询（只返回一个值）、关联子查询（将嵌套子查询内外两个查询通过一些标志连接起来，比如外部循环表as p1，内部循环的表as p2，筛选条件  p1.name=p2.name）。子查询是一次性的，它没有保存在介质中。

（3）函数

算数函数：

     ABS(数值) ——绝对值 

     MOD(被除数,除数) ——求余数

     ROUND ——四舍五入

字符串函数：

     CONCAT(str1,str2,st3......) ——拼接

     LENGTH(字符串) ——字符串长度

     LOWER ——小写转换；UPPER——大写转换；【只针对英文字母】

     SUBSTRING(对象字符串 FROM 截取起始位置 FOR 截取字符数) ——字符串截取

       e.g. str1=taylor，substring ( str1 from 2 for 2 ) 运行结果为 ay

日期函数：

     CURRENT_DATE——截取当前日期；CURRENT_TIME——截取当前时间；

     CURRENT_TIMESTAMP——截取当前日期和时间

     EXTRACT——截取日期元素（返回值为数值类型）

转换函数：

     CAST(转换前的值 AS 想要转换的数据类型)——类型转换

     COALESCE(数据1,数据2,数据3......)——将NULL转化为其他值

（4）谓词

谓词：返回真值（TRUE/FALSE/UNKNOW）的函数

LIKE

BETWEEN(包含边界值)

IS NULL/IS NOT NULL

IN (OR的简便用法)

e.g.where age = 15 or age = 20 or age = 33 可变为

      where age in (15,20,33)

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

练习题

3.1

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

product_name | sale_price | regist_date
--------------------+--------------+-----------------
T恤衫                | 　 1000    | 2009-09-20
菜刀                  |      3000    | 2009-09-20

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-9-20'

执行结果：

 

3.2

向习题一中创建的视图 ViewPractice5_1 中插入如下数据，会得到什么样的结果？为什么？
INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02')

会报错。

 因为修改视图插入数据，原表里也会插入数据，但是原表不只有三列数据，并且规定了NOT NULL，所以会报错。

3.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

一开始我把as sale_price_avg放在了括号里面，发现得到的结果在均价一列的列名为一长串语句，思后把as sale_price_avg放在括号外，成功将列名改为sale_price_avg。

执行结果：

 3.4

请根据习题一中的条件编写一条 SQL 语句，创建一幅包含如下数据的视图（名称为AvgPriceByType）。
提示：其中的关键是 sale_price_avg_type 列。与习题三不同，这里需要计算出的 是各商品种类的平均销售单价。这与使用关联子查询所得到的结果相同。 也就是说，该列可以使用关联子查询进行创建。问题就是应该在什么地方使用这个关联子查询。

 

create view AvgPriceByType 
as
select product_id,
       product_name,
       product_type,
       sale_price,
       (select AVG(sale_price) 
       from product as p1
       where p1.product_type = p2.product_type
       group by p2.product_type)as sale_price_avg_type
from product as p2;

select * from AvgPriceByType

执行结果：

 

 3.5

运算中含有 NULL 时，运算结果是否必然会变为NULL ？

不对，对于ABS函数参数为NULL时会返回NULL，但是对于AVG、SUM等函数，NULL不影响。

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

① 返回purchase_price不为500，2800，5000的结果。

 ②返回为空

3.7

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

执行结果
low_price | mid_price | high_price
--------------+----------------+------------
               5 |                1 |               2

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

执行结果：