Task 04 复杂一点的查询

Content：

• 视图
• 子查询
• sql函数
• 谓词
• CASE表达式
• 练习

Reference：
[1]GitHub - datawhalechina/wonderful-sql: Follow me，从 0 到 1 掌握 SQL，决胜秋招。

[2]简单易懂教你学会SQL关联子查询 - 知乎 (zhihu.com)

1.视图

即视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。视图并不是数据库真实存储的数据表，它可以看作是一个窗口，通过这个窗口我们可以看到数据库表中真实存在的数据。

1.1 建立视图

-- 示例
CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句

相关说明

• 视图与表的区别---“是否保存了实际的数据”
• 视图不仅可以基于真实表（单表或多表），我们也可以在视图的基础上继续创建视图
• 视图名在数据库中需要是唯一的
• 在一般的 DBMS 中定义视图时不能使用 ORDER BY 语句，（但是MySQL若从特定视图进行选择，而该视图使用了
  自己的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略）这是因为视图和表一样，数据行都是没有顺序的。

1.2 一些命令

1. 对视图选取数据的修改

-- 示例
ALTER VIEW <视图名> AS <SELECT语句>

2. 视图里数据的更新

-- 示例
UPDATE productsum
SET sale_price = ’5000’
WHERE product_type = ’办公用品’;

相关说明

• 因为视图是一个虚拟表，所以对视图的操作就是对底层基础表的操作，所以在修改时只有满足底层基本表的定义才能成功修改：
  1） 拥有修改视图的权限
  2） 不包含以下结构的任意一种
  - 聚合函数 SUM()、MIN()、MAX()、COUNT() 等
  - DISTINCT 关键字
  - GROUP BY 子句
  - HAVING 子句
  - UNION 或 UNION ALL 运算符
  - FROM 子句中包含多个表
• 视图只是原表的一个窗口，所以它修改也只能修改透过窗口能看到的内容

3. 视图这个“窗口”的删除

DROP VIEW <视图名1> [ , <视图名2> …]

相关说明

• 删除视图也需要拥有修改视图的权限

2. 子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1 开始引入，在 SELECT子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。

SELECT stu_name
FROM (
SELECT stu_name, COUNT(*) AS stu_cnt
FROM students_info
GROUP BY stu_age) AS studentSum;

嵌套子查询：随着子查询嵌套的层数的叠加，SQL 语句不仅会难以理解而且执行效率也会很差，所以要尽量避免这样的使用

2.1 子查询与视图的区别

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

2.2 标量子查询和关联子查询

1. 标量子查询单一子查询：返回表中具体的某一行的某一列

-- 示例：查询出销售单价高于平均销售单价的商品
SELECT product_id, product_name, sale_price
FROM product
WHERE sale_price > (SELECT AVG(sale_price) FROM product);

2. 关联子查询：通过一些标志将内外两层的查询连接起来起到过滤数据的目的

-- 选取出各商品种类中高于该商品种类的平均销售单价的商品
SELECT product_type, product_name, sale_price
FROM product AS p1
WHERE sale_price > (SELECT AVG(sale_price)
FROM product ASp2
WHERE p1.product_type =p2.product_type
GROUP BY product_type);

相关说明

• 关联子查询的执行逻辑完全不同于正常的SELECT语句
• 关联子查询执行逻辑为：
  1）先从主查询的Product表中product _type列取出第一个值，进入子查询中，得到子查询结果，然后返回父查询，判断父查询的where子句条件，则返回整个语句的第1条结果
  2）重复上述操作，直到所有主查询中的Product表中product _type列记录取完为止。得出整个语句的结果集，就是最后的答案

3. sql函数

3.1 算数函数——进行数值计算的函数

1. 四则运算
2. ABS – 绝对值
   语法：‘ABS( 数值)‘
   ABS 函数用于计算一个数字的绝对值，表示一个数到原点的距离。
   当 ABS 函数的参数为 ‘NULL‘时，返回值也是 ‘NULL‘。
3. MOD – 求余数
   语法：‘MOD( 被除数，除数)‘
   MOD 是计算除法余数（求余）的函数，是 modulo 的缩写。小数没有余数的概念，只能对整数列求余数。
   注意：主流的 DBMS 都支持 MOD 函数，只有 SQL Server 不支持该函数。
4. ROUND – 四舍五入
   语法：‘ROUND( 对象数值，保留小数的位数)‘
   ROUND 函数用来进行四舍五入操作。
   注意：当参数 保留小数的位数为变量 时，可能会遇到错误，请谨慎使用变量

3.2 字符串函数——进行字符串操作的函数

1. CONCAT – 拼接
   语法：‘CONCAT(str1, str2, str3)‘
   MySQL 中使用 CONCAT 函数进行拼接。
2. LENGTH – 字符串长度
   语法：‘LENGTH( 字符串)‘
3. LOWER – 小写转换
   LOWER 函数只能针对英文字母使用，它会将参数中的字符串全都转换为小写。该函数不适用于英文字母
   以外的场合，不影响原本就是小写的字符。
   类似的，UPPER 函数用于大写转换。
4. REPLACE – 字符串的替换
   语法：‘REPLACE( 对象字符串，替换前的字符串，替换后的字符串)‘
5. SUBSTRING – 字符串的截取
   语法：‘SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）‘
   使用 SUBSTRING 函数可以截取出字符串中的一部分字符串。截取的起始位置从字符串最左侧开始计算，
   索引值起始为 1。
6. SUBSTRING_INDEX – 字符串按索引截取
   语法：SUBSTRING_INDEX (原始字符串，分隔符，n)
   该函数用来获取原始字符串按照分隔符分割后，第 n 个分隔符之前（或之后）的子字符串，支持正向和反
   向索引，索引起始值分别为 1 和 -1。

3.3 日期函数——进行日期操作的函数

1. CURRENT_DATE – 获取当前日期
2. CURRENT_TIME – 当前时间
3. CURRENT_TIMESTAMP – 当前日期和时间
4. EXTRACT – 截取日期元素
5. EXTRACT – 截取日期元素
   语法：‘EXTRACT(日期元素 FROM 日期)‘
   使用 EXTRACT 函数可以截取出日期数据中的一部分，例如“年”“月”，或者“小时”“秒”等。该函数的返回值并不是日期类型而是数值类型

3.4 转换函数——转换数据类型和值的函数

转换在SQL中的含义：一是数据类型的转换，简称为类型转换，在英语中称为 ‘cast‘；另一层意思是值的转换。

1. CAST – 类型转换
   语法：‘CAST（转换前的值 AS 想要转换的数据类型）‘
2. COALESCE – 将 NULL 转换为其他值
   语法：‘COALESCE(数据 1，数据 2，数据 3……)‘
   COALESCE 是 SQL 特有的函数。该函数会返回可变参数 A 中左侧开始第 1 个不是 NULL 的值。参数个
   数是可变的，因此可以根据需要无限增加。
   在 SQL 语句中将 NULL 转换为其他值时就会用到转换函数。

3.5 聚合函数——进行数据聚合的函数

已在上一篇笔记中介绍过，这里不再赘述。

4. 谓词

谓词就是返回值为真值的函数。包括 ‘TRUE / FALSE / UNKNOWN‘。

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

1. 前方一致：作为查询条件的字符串与查询对象字符串起始部分相同。
2. 中间一致：查询对象字符串中含有作为查询条件的字符串，无论该字符串出现在对象字符串的最后还是中间都没有关系。
3. 作为查询条件的字符串与查询对象字符串的末尾部分相同。
4. 使用 _（下划线）来代替任意 1 个字符

4.2 BETWEEN 谓词 – 用于范围查询

1. 范围查询是闭区间

4.3 IS NULL、IS NOT NULL – 用于判断是否为 NULL

1. 在选取出某些值为 NULL 的列的数据时，不能使用 =，而只能使用特定的谓词 IS NULL，同理IS NOT NULL

4.4 IN 谓词 – OR 的简便用法

1. OR：用于查询条件取并集
2. IN：简化多条OR语句，同理NOT　IN
3. IN谓词（NOT IN 谓词）具有其他谓词所没有的用法，那就是可以使用子查询、视图（可以理解为SQL内部生成的表）作为其参数

4.5　EXIST 谓词－判断是否存在满足某种条件的记录

• 如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE），EXIST（存在）谓词的主语是“记录”。
• EXIST 通常会使用关联子查询作为参数
• EXIST 的子查询中写 SELECT * 是 SQL 的一种习惯
• 同理NOT EXIST

5. CASE表达式－区分情况的条件分支

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

依次判断 when 表达式是否为真值，是则执行 THEN 后的语句，如果所有的 when 表达式均为假，则执行 ELSE 后的语句。注意：CASE表达式最后只会返回一个值。

-- 示例
 SELECT product_name,
- CASE WHEN product_type = ’衣服’ THEN CONCAT(’A ： ’,product_type)
- WHEN product_type = ’办公用品’ THEN CONCAT(’B ： ’,product_type)
- WHEN product_type = ’厨房用具’ THEN CONCAT(’C ： ’,product_type)
- ELSE NULL
- END AS abc_product_type
- FROM product;

相关说明

• ELSE 子句也可以省略不写，这时会被默认为 ELSE NULL
• CASE 表达式最后的 END 是不能省略的

6. 练习

6.1 第一部分

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 not sale_price<1000
AND regist_date='2009-9-20';

SELECT * FROM ViewPractice5_1;

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

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

出现如下错误：

Error Code: 1423. Field of view ‘shop.viewpractice5_1’ underlying table doesn’t have a default value

这是因为：当向视图中插入数据时，同时也会向原表插入数据 ，而原表(product)中的多个字段不允许为空，所以无法插入。因此若想达到插入的目的，将这些不允许为空的字段修改为允许为空即可。

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

SELECT product_id,product_name,product_type,sale_price,
(SELECT AVG(sale_price) FROM product) AS sale_price_avg
FROM product;

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

CREATE VIEW AvgPriceByType(product_id,product_name,product_type,sale_price,sale_price_avg_type)
AS SELECT product_id,product_name,product_type,
  (SELECT AVG(sale_price) 
  FROM product AS p2 
  WHERE p1.product_type = p2.product_type
  GROUP BY product_type) AS sale_price_avg_type
FROM product AS p2;

SELECT * FROM AvgPriceByType

5. 运算或者函数中含有 NULL 时，结果全都会变为 NULL ？（判断题）
   ANS：不一定，例如用COALESCE将NULL 转换为其他值

6. 对本章中使用的 product（商品）表执行如下 2 条 SELECT 语句，能够得到什么样的结果呢？

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

(1) IN 可以看作多个OR的并集，NOT IN 可以看作多个AND的交集。因此执行第一条语句将得到两列：product_name和purchase_price，其中purchase_price不包含500，2800，5000和NULL。（因为NULL 与任何比较运算符做运算得到的结果均为NULL,因此含有NULL记录的行无法被查询出来）

(2) 为空。在使用 IN 和 NOT IN 时是无法选取出 NULL 数据的。NULL 只能使用 IS NULL 和 IS NOT NULL 来进行判断。

7. 按照销售单价（sale_price）对练习 6.1 中的 product（商品）表中的商品进行如下分类。
   • 低档商品：销售单价在 1000 日元以下（T 恤衫、办公用品、叉子、擦菜板、圆珠笔）
   • 中档商品：销售单价在 1001 日元以上 3000 日元以下（菜刀）
   • 高档商品：销售单价在 3001 日元以上（运动 T 恤、高压锅）
   

   请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句，结果如下所示。
   7.png

SELECT 
  COUNT(CASE WHEN sale_price <= 1000 THEN TRUE ELSE NULL END) AS low_price,
  COUNT(CASE WHEN sale_price > 1000 AND sale_price < =3000 THEN TRUE ELSE NULL END) AS mid_price,
  COUNT(CASE WHEN sale_price > 3000 THEN TRUE ELSE NULL END) AS high_price
FROM product;