目录
3.1 本节知识整理
| 用法 | 相关说明 | ||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
视图 | 创建视图: 修改视图结构: 更新视图内容:
删除视图
|
| ||||||||||||||||||||||||||||||||||||||||
子查询
| 一个查询语句嵌套在另一个查询语句内部的查询
| 关联子查询执行过程详解: | ||||||||||||||||||||||||||||||||||||||||
函数 | 算术函数 (用来进行数值计算的函数):
字符串函数 (用来进行字符串操作的函数):
日期函数 (用来进行日期操作的函数):
转换函数 (用来转换数据类型和值的函数):
聚合函数 (用来进行数据聚合的函数):参考Task02: 基础排序与查询笔记 | NA | ||||||||||||||||||||||||||||||||||||||||
谓词 | 谓词就是返回值为真值的函数,包括
|
| ||||||||||||||||||||||||||||||||||||||||
CASE表达式 | 依次判断WHEN表达式是否为真值,是则执行THEN后的语句,如果所有的WHEN表达式均为假,则执行ELSE后的语句 |
|
3.2 练习题(macOS)
3.2.1 第一部分
1. 创建出满足下述三个条件的视图(视图名称为 ViewPractice5_1)。使用 product(商品)表作为参照表,假设表中包含初始状态的 8 行数据。
-
条件 1:销售单价大于等于 1000 日元。
-
条件 2:登记日期是 2009 年 9 月 20 日。
-
条件 3:包含商品名称、销售单价和登记日期三列。
对该视图执行 SELECT 语句的结果如下所示。
SELECT * FROM ViewPractice5_1;
执行结果
product_name | sale_price | regist_date
--------------+------------+------------
T恤衫 | 1000 | 2009-09-20
菜刀 | 3000 | 2009-09-20
只要写出对应的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. 向习题1中创建的视图 ViewPractice5_1 中插入如下数据,会得到什么样的结果呢?
INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');
说明:会报错。视图是一个虚拟表,对视图的操作就是对底层基础表的操作,所以在修改时只有满足底层基本表的定义才能成功修改;上述插入语句只包含了视图ViewPractice5_1所选出来的列名,并未包含地处基础表product的所有列名,特别是primary key不能为NULL。
3. 请根据如下结果编写 SELECT 语句,其中 sale_price_all 列为全部商品的平均销售单价。
product_id | product_name | product_type | sale_price | sale_price_all
------------+-------------+--------------+------------+---------------------
0001 | T恤衫 | 衣服 | 1000 | 2097.5000000000000000
0002 | 打孔器 | 办公用品 | 500 | 2097.5000000000000000
0003 | 运动T恤 | 衣服 | 4000 | 2097.5000000000000000
0004 | 菜刀 | 厨房用具 | 3000 | 2097.5000000000000000
0005 | 高压锅 | 厨房用具 | 6800 | 2097.5000000000000000
0006 | 叉子 | 厨房用具 | 500 | 2097.5000000000000000
0007 | 擦菜板 | 厨房用具 | 880 | 2097.5000000000000000
0008 | 圆珠笔 | 办公用品 | 100 | 2097.5000000000000000
利用标量子查询即可实现上述结果:
SELECT product_id,
product_name,
sale_price,
(SELECT AVG(sale_price)
FROM product) AS sale_price_all
FROM product;
4. 请根据习题一中的条件编写一条 SQL 语句,创建一幅包含如下数据的视图(名称为AvgPriceByType)。
product_id | product_name | product_type | sale_price | avg_sale_price
------------+-------------+--------------+------------+---------------------
0001 | T恤衫 | 衣服 | 1000 |2500.0000000000000000
0002 | 打孔器 | 办公用品 | 500 | 300.0000000000000000
0003 | 运动T恤 | 衣服 | 4000 |2500.0000000000000000
0004 | 菜刀 | 厨房用具 | 3000 |2795.0000000000000000
0005 | 高压锅 | 厨房用具 | 6800 |2795.0000000000000000
0006 | 叉子 | 厨房用具 | 500 |2795.0000000000000000
0007 | 擦菜板 | 厨房用具 | 880 |2795.0000000000000000
0008 | 圆珠笔 | 办公用品 | 100 | 300.0000000000000000
提示:其中的关键是 avg_sale_price 列。与习题3不同,这里需要计算出的是各商品种类的平均销售单价。这与使用关联子查询所得到的结果相同。 也就是说,该列可以使用关联子查询进行创建。问题就是应该在什么地方使用这个关联子查询。
利用关联子查询,将外层查询的product_type传递到内层查询中计算分类产品的平均销售单价,并返回作为新建列avg_sale_price的值:
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 product_type) AS avg_sale_price
FROM product AS p2;
3.2.2 第二部分
1. 运算或者函数中含有 NULL 时,结果全都会变为NULL ?(判断题)
正确☑️
说明:
-
运算中包含有NULL时结果为NULL参考Task02: 基础排序与查询-2.2.1练习题第一部分第2题
-
函数中含有NULL时,以函数ABS,MAX,SUM为例,示例代码和结果如下:
SELECT ABS(NULL), MAX(NULL), SUM(NULL);
结果:
+-----------+----------------------+-----------+
| ABS(NULL) | MAX(NULL) | SUM(NULL) |
+-----------+----------------------+-----------+
| NULL | 0x | NULL |
+-----------+----------------------+-----------+
1 row in set (0.00 sec)
2. 对本章中使用的 product(商品)表执行如下 2 条 SELECT 语句,能够得到什么样的结果呢?
注:product表建表过程请参考Task02: 基础排序与查询-2.2.1练习题第一部分准备工作
①
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000);
结果:返回purchase_price不为500,2800,5000的producet_name,purchase_price,且不包含purchase_price为空的行
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| 打孔器 | 320 |
| 擦菜板 | 790 |
+--------------+----------------+
2 rows in set (0.00 sec)
②
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000, NULL);
结果:返回为空,结合上一题运算或函数中含有NULL,结果都会变为NULL;如果要筛选NULL对应行,一定要用IS NULL或者IS NOT NULL
Empty set (0.00 sec)
3. 按照销售单价( sale_price)对练习 6.1 中的 product(商品)表中的商品进行如下分类。
-
低档商品:销售单价在1000日元以下(T恤衫、办公用品、叉子、擦菜板、 圆珠笔)
-
中档商品:销售单价在1001日元以上3000日元以下(菜刀)
-
高档商品:销售单价在3001日元以上(运动T恤、高压锅)
请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句,结果如下所示。
low_price | mid_price | high_price
----------+-----------+------------
5 | 1 | 2
观察结果,分别统计了不同分类标准下所包含的商品数量,利用聚合函数+CASE WHEN表达式实现:
SELECT SUM(CASE WHEN sale_price <= 1000 THEN 1 ELSE 0 END) AS low_price,
SUM(CASE WHEN sale_price > 1000 AND sale_price <= 3000 THEN 1 ELSE 0 END) AS mid_price,
SUM(CASE WHEN sale_price > 3000 THEN 1 ELSE 0 END) AS high_price
FROM product;