Task03 复杂一点的查询

 参考：GitHub - datawhalechina/wonderful-sql: Follow me，从 0 到 1 掌握 SQL。

1. 视图

1.1 视图是什么

视图是一个虚拟的表，它具有表的结构，但是没有数据，它的数据来自基表。

1.2 视图的作用

1. 保证了基表的安全性，用户只能视图中的数据进行操作；
2. 提高了效率，用户可以只关注特定的数据；

1.3如何创建视图

创建视图的基本语法如下：

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

1.4 如何修改视图

修改视图的基本语法如下：

ALTER VIEW <视图名> AS <SELECT语句>

1.5 如何更新视图

对于一个视图来说，如果包含以下结构的任意一种都是不可以被更新的：

• 聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
• DISTINCT 关键字。
• GROUP BY 子句。
• HAVING 子句。
• UNION 或 UNION ALL 运算符。
• FROM 子句中包含多个表。

视图中的数据要和基表保持一致。

1.6 如何删除视图

删除视图的基本语法如下：

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

2. 子查询

2.1 什么是子查询

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

2.2 子查询的作用

很多查询需要从另一个查询的结果中获取数据，子查询可以增强SELECT的查询能力。

2.3 子查询的基本使用

• 子查询会在主查询之前完成；
• 子查询的结果被主查询使用。

2.4 标量子查询

标量子查询要求我们执行的SQL语句只能返回一个值，也就是要返回表中具体的某一行的某一列。

2.5 关联子查询

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

3. 各种各样的函数

函数大致分为如下几类：

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

3.1 算术函数

3.1.1 ABS 求绝对值

语法： ABS( 数值 )

3.1.2 MOD 求余数

语法： MOD( 被除数，除数 )

3.1.3  ROUND 四舍五入

语法： ROUND( 对象数值，保留小数的位数 )

3.2 字符串函数

3.2.1 CONCAT 拼接

语法： CONCAT( str1, str2, str3 )

将多个字符串连接成一个字符串，连接符用' '包起来。

3.2.2 LENGTH 字符串长度

语法： LENGTH( 字符串 )

3.2.3 LOWER 小写转换

语法： LOWER( input_string )

在此语法中, input_string 可以是文字字符串，变量，字符串表达式或表列。它会将参数中的字符串全都转换为小写。

3.2.4 REPLACE 字符串的替换

语法： REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )

3.2.5 SUBSTRING 字符串的截取

语法： SUBSTRING( 对象字符串 FROM 截取的起始位置 FOR 截取的字符数 )

索引值起始为1。

3.2.6 SUBSTRING_INDEX 字符串按索引截取

语法： SUBSTRING_INDEX( 原始字符串， 分隔符，n )

该函数用来获取原始字符串按照分隔符分割后，第 n 个分隔符之前（或之后）的子字符串，支持正向和反向索引，索引起始值分别为 1 和 -1。

3.2.7 REPEAT 字符串按需重复多次

语法： REPEAT( string, number )

3.3 日期函数

3.3.1 CURRENT_DATE 获取当前日期

SELECT CURRENT_DATE;

3.3.2 CURRENT_TIME 当前时间

SELECT CURRENT_TIME;

3.3.3 CURRENT_TIMESTAMP 当前日期和时间

SELECT CURRENT_TIMESTAMP;

3.3.4 EXTRACT 截取日期元素

语法：EXTRACT( 日期元素 FROM 日期 )

SELECT CURRENT_TIMESTAMP as now,
EXTRACT(YEAR   FROM CURRENT_TIMESTAMP) AS year,
EXTRACT(MONTH  FROM CURRENT_TIMESTAMP) AS month,
EXTRACT(DAY    FROM CURRENT_TIMESTAMP) AS day,
EXTRACT(HOUR   FROM CURRENT_TIMESTAMP) AS hour,
EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS MINute,
EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;

3.4 转换函数

3.4.1 CAST 类型转换

语法：CAST( 转换前的值 AS 想要转换的数据类型 )

需要特别注意的是，当要转换为整型时，需要指定为 SIGNED（有符号） 或者 UNSIGNED（无符号）。

3.4.2 COALESCE 将NULL转换为其他值

语法：COALESCE(数据1，数据2，数据3……)

COALESCE 是 SQL 特有的函数。该函数会返回可变参数 A 中左侧开始第 1个不是NULL的值。参数个数是可变的，因此可以根据需要无限增加。

4. 谓词

4.1 什么是谓词

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

谓词主要有以下几个：

• LIKE
• BETWEEN
• IS NULL、IS NOT NULL
• IN
• EXISTS

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

当需要进行字符串的部分一致查询时需要使用该谓词。

部分一致大体可以分为前方一致、中间一致和后方一致三种类型。

• 前方一致  select * from user where username like 'aaa%';
• 中间一致  select * from user where username like '%aaa%';
• 后方一致  select * from user where username like '%aaa';

另外，_下划线匹配任意1个字符，使用 _（下划线）来代替 %，与 % 不同的是，它代表了“任意 1 个字符”。

4.3 BETWEEN谓词 用于范围查询

BETWEEN () AND ()

BETWEEN的特点是结果中会包含临界值。

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

可以用于选出某些值为NULL或者是NULL以外的列的数据。

4.5 IN谓词 OR的简便用法

多个查询条件取并集时可以选择使用 IN 谓词，反之，可以用 NOT IN。

需要注意的是，在使用IN 和 NOT IN 时是无法选取出NULL数据的。 

5. CASE表达式

5.1 什么是CASE表达式

CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支。

语法：

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

上述语句执行时，依次判断 when 表达式是否为真值，是则执行 THEN 后的语句，如果所有的 when 表达式均为假，则执行 ELSE 后的语句。 无论多么庞大的 CASE 表达式，最后也只会返回一个值。

练习题

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

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

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

会报错。因为原表中的 product_id 和 product type 的约束条件是 NOT NULL。

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

 

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

提示：其中的关键是 sale_price_avg_type 列。与习题三不同，这里需要计算出的 是各商品种类的平均销售单价。这与使用关联子查询所得到的结果相同。 也就是说，该列可以使用关联子查询进行创建。问题就是应该在什么地方使用这个关联子查询。

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

是。

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

1.

2.

7.按照销售单价( sale_price )对练习 3.6 中的 product（商品）表中的商品进行如下分类。

• 低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、 圆珠笔）
• 中档商品：销售单价在1001日元以上3000日元以下（菜刀）
• 高档商品：销售单价在3001日元以上（运动T恤、高压锅）

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

执行结果

low_price | mid_price | high_price
-------------+--------------+------------
             5 |               1 |         2