T03 集合运算

复杂一点的查询

视图

视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的

SELECT product_name FROM view_product;

视图与表的区别—“是否保存了实际的数据”。所以视图并不是数据库真实存储的数据表，即视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。

1. 通过定义视图可以将频繁使用的SELECT语句保存以提高效率。
2. 通过定义视图可以使用户看到的数据更加清晰。
3. 通过定义视图可以不对外公开数据表全部字段，增强数据的保密性。
4. 通过定义视图可以降低数据的冗余。

创建视图：

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

视图名在数据库中需要是唯一的，不能与其他视图和表重名。

视图不仅可以基于真实表，我们也可以在视图的基础上继续创建视图。

在一般的DBMS中定义视图时不能使用ORDER BY语句。因为视图和表一样，数据行都是没有顺序的。在 MySQL中视图的定义是允许使用 ORDER BY 语句的，但是若从特定视图进行选择，而该视图使用了自己的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略。

eg：在product表的基础上创建一个视图

CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type ;

- 修改视图结构:

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

eg:

ALTER VIEW productSum
    AS
        SELECT product_type, sale_price
          FROM product
         WHERE regist_date > '2009-09-11';

- 更新视图内容:

因为视图是一个虚拟表，所以对视图的操作就是对底层基础表的操作，所以在修改时只有满足底层基本表的定义才能成功修改。

eg:

UPDATE productsum
   SET sale_price = '5000'
 WHERE product_type = '办公用品';

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

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

在创建视图时也尽量使用限制不允许通过视图来修改表

- 删除视图:

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

eg:

DROP VIEW productSum;

子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询。查询可以基于一个表或者多个表。

子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中。由于子查询是一次性的，所以子查询不会像视图那样保存在存储介质中， 而是在 SELECT 语句执行之后就消失了。

- 嵌套子查询

SELECT product_type, cnt_product
FROM (SELECT *
        FROM (SELECT product_type, 
                      COUNT(*) AS cnt_product
                FROM product 
               GROUP BY product_type) AS productsum
       WHERE cnt_product = 4) AS productsum2;

其中最内层的子查询我们将其命名为productSum，这条语句根据product_type分组并查询个数，第二层查询中将个数为4的商品查询出来，最外层查询product_type和cnt_product两列。

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

- 标量子查询

就是单一的子查询，也就是要返回表中具体的某一行的某一列。

eg: 通过标量子查询语句查询出销售单价高于平均销售单价的商品。

SELECT product_id, product_name, sale_price
  FROM product
 WHERE sale_price > (SELECT AVG(sale_price) FROM product);

- 关联子查询

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

eg: 选取出各商品种类中高于该商品种类的平均销售单价的商品。

SELECT product_type, product_name, sale_price
  FROM product AS p1
 WHERE sale_price > (SELECT AVG(sale_price)
   FROM product AS p2
                      WHERE p1.product_type =p2.product_type
   GROUP BY product_type);

练习题-第一部分

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

我的解答：

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

我的解答：

会报错，因为向视图插入数据会使对应表也插入数据，但原表一条记录中有的列不允许为空，所以无法插入。

3.3

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

我的解答：

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

3.4

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

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

我的解答：

CREATE VIEW AvgPriceByType (product_id, product_name, product_type, sale_price, sale_price_avg_type)
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_avg_type
FROM product AS p1;

各种各样的函数

算数函数

• ABS – 绝对值

语法：ABS( 数值 )

当 ABS 函数的参数为NULL时，返回值也是NULL。

• MOD – 求余数

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

小数没有余数的概念，只能对整数列求余数。

注意：主流的 DBMS 都支持 MOD 函数，只有SQL Server 不支持该函数，其使用%符号来计算余数。

• ROUND – 四舍五入

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

注意：当参数 保留小数的位数 为变量时，可能会遇到错误，请谨慎使用变量。

eg:

SELECT m,
    ABS(m)ASabs_col ,
    n, p,
    MOD(n, p) AS mod_col,
    ROUND(m,1) AS round_col
FROM samplemath;

字符串函数

• CONCAT – 拼接

语法：CONCAT(str1, str2, str3)

MySQL中使用 CONCAT 函数进行拼接。

• LENGTH – 字符串长度

语法：LENGTH( 字符串 )

• LOWER – 小写转换

LOWER 函数只能针对英文字母使用，它会将参数中的字符串全都转换为小写。

类似的， UPPER 函数用于大写转换。

• REPLACE – 字符串的替换

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

• SUBSTRING – 字符串的截取

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

截取的起始位置从字符串最左侧开始计算，索引值起始为1。

• （扩展内容）SUBSTRING_INDEX – 字符串按索引截取

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

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

eg:

SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
-- 得到结果：
-- www.mysql   
SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
-- 得到结果：
-- mysql.com

• （扩展内容）REPEAT – 字符串按需重复多次

语法：REPEAT(string, number重读次数)

该函数用来对特定字符实现按需重复。

日期函数

• CURRENT_DATE – 获取当前日期

• CURRENT_TIME – 当前时间

• CURRENT_TIMESTAMP – 当前日期和时间

• EXTRACT – 截取日期元素

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

使用 EXTRACT 函数可以截取出日期数据中的一部分，例如“年”

“月”，或者“小时”“秒”等。该函数的返回值并不是日期类型而是数值类型

eg:

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;

转换函数

• CAST – 类型转换

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

eg:

-- 将字符串类型转换为数值类型
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
-- 结果： 1
-- 将字符串类型转换为日期类型
SELECT CAST('2009-12-14' AS DATE) AS date_col;
-- 结果： 2009-12-14 

• COALESCE – 将NULL转换为其他值

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

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

eg:

SELECT COALESCE(NULL, 11) AS col_1,
COALESCE(NULL, 'hello world', NULL) AS col_2,
COALESCE(NULL, NULL, '2020-11-01') AS col_3;
+-------+-------------+------------+
| col_1 | col_2       | col_3      |
+-------+-------------+------------+
|    11 | hello world | 2020-11-01 |
+-------+-------------+------------+

谓词

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

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

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

创建表：

CREATE TABLE samplelike
( strcol VARCHAR(6) NOT NULL,
  PRIMARY KEY (strcol)
);
START TRANSACTION; -- 开始事务
INSERT INTO samplelike (strcol) VALUES ('abcddd');
INSERT INTO samplelike (strcol) VALUES ('dddabc');
INSERT INTO samplelike (strcol) VALUES ('abdddc');
INSERT INTO samplelike (strcol) VALUES ('abcdd');
INSERT INTO samplelike (strcol) VALUES ('ddabc');
INSERT INTO samplelike (strcol) VALUES ('abddc');
COMMIT; -- 提交事务
SELECT * FROM samplelike;
+--------+
| strcol |
+--------+
| abcdd  |
| abcddd |
| abddc  |
| abdddc |
| ddabc  |
| dddabc |
+--------+

• 前方一致：选取出“dddabc”

前方一致即作为查询条件的字符串（这里是“ddd”）与查询对象字符串起始部分相同。

SELECT *
FROM samplelike
WHERE strcol LIKE 'ddd%';
-- 其中的%是代表“零个或多个任意字符串”的特殊符号，本例中代表“以ddd开头的所有字符串”。

• 中间一致：选取出“abcddd”“dddabc”“abdddc”

中间一致即查询对象字符串中含有作为查询条件的字符串，无论该字符串出现在对象字符串的最后还是中间都没有关系。

SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd%';

• 后方一致：选取出“abcddd“

后方一致即作为查询条件的字符串（这里是“ddd”）与查询对象字符串的末尾部分相同。

SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd';

• _下划线匹配任意 1 个字符：选取出“abcdd”

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

SELECT *
FROM samplelike
WHERE strcol LIKE 'abc__';

BETWEEN谓词 – 用于范围查询

SELECT product_name, sale_price
FROM product
WHERE sale_price BETWEEN 100 AND 1000;

BETWEEN 的特点就是结果中会包含 100 和 1000 这两个临界值，也就是闭区间。如果不想让结果中包含临界值，那就必须使用 < 和 >。

ELECT product_name, sale_price
FROM product
WHERE sale_price > 100
AND sale_price < 1000;

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

为了选取出某些值为 NULL 的列的数据，不能使用 =，而只能使用特定的谓词IS NULL。

SELECT product_name, purchase_price
FROM product
WHERE purchase_price IS NULL;
-- 或者：WHERE purchase_price IS NOT NULL;

IN谓词 – OR的简便用法

-- 通过OR指定多个进货单价进行查询需要多条语句
SELECT product_name, purchase_price
FROM product
WHERE purchase_price = 320
OR purchase_price = 500
OR purchase_price = 5000;
-- 用IN代替会省略很多
WHERE purchase_price IN (320, 500, 5000);
-- 否定时可直接加NOT
WHERE purchase_price NOT IN (320, 500, 5000);

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

使用子查询作为IN谓词的参数

IN 谓词（NOT IN 谓词）具有其他谓词所没有的用法，那就是可以使用子查询作为其参数。

SELECT product_name, sale_price
FROM product
WHERE product_id IN (SELECT product_id
  FROM shopproduct
                       WHERE shop_id = '000C');

EXIST 谓词

谓词的作用就是 “判断是否存在满足某种条件的记录”。如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。EXIST（存在）谓词的主语是“记录”。

SELECT product_name, sale_price
  FROM product AS p
 WHERE EXISTS (SELECT *
                 FROM shopproduct AS sp
                WHERE sp.shop_id = '000C'
                  AND sp.product_id = p.product_id);

EXIST 是只有 1 个参数的谓词。 所以，EXIST 只需要在右侧书写 1 个参数，该参数通常都会是一个子查询。

由于 EXIST 只关心记录是否存在，因此返回哪些列都没有关系。可以把在 EXIST 的子查询中书写 SELECT * 当作 SQL 的一种习惯。

就像 EXIST 可以用来替换 IN 一样， NOT IN 也可以用NOT EXIST来替换。

SELECT product_name, sale_price
  FROM product AS p
 WHERE NOT EXISTS (SELECT *
                     FROM shopproduct AS sp
                    WHERE sp.shop_id = '000A'
                      AND sp.product_id = p.product_id);

CASE 表达式

CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。

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

依次判断 when 表达式是否为真值，是则执行 THEN 后的语句，如果所有的 when 表达式均为假，则执行 ELSE 后的语句。

• 根据不同分支得到不同列值

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;
  -- CONCAT是拼接函数

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

• 实现列方向上的聚合

-- 实现不同类型的聚合
SELECT product_type,
       SUM(sale_price) AS sum_price
  FROM product
 GROUP BY product_type; 
 -- 运用CASE对按照商品种类计算出的销售单价合计值进行行列转换
SELECT SUM(CASE WHEN product_type = '衣服' THEN sale_price ELSE 0 END) AS sum_price_clothes,
       SUM(CASE WHEN product_type = '厨房用具' THEN sale_price ELSE 0 END) AS sum_price_kitchen,
       SUM(CASE WHEN product_type = '办公用品' THEN sale_price ELSE 0 END) AS sum_price_office
  FROM product;

• 实现行转列

假设有如下图表的结构

计划得到如下的图表结构

聚合函数 + CASE WHEN 表达式即可实现该转换

-- CASE WHEN 实现数字列 score 行转列
SELECT name,
       SUM(CASE WHEN subject = '语文' THEN score ELSE null END) as chinese,
       SUM(CASE WHEN subject = '数学' THEN score ELSE null END) as math,
       SUM(CASE WHEN subject = '外语' THEN score ELSE null END) as english
  FROM score
 GROUP BY name;
 +------+---------+------+---------+
| name | chinese | math | english |
+------+---------+------+---------+
| 张三 |      93 |   88 |      91 |
| 李四 |      87 |   90 |      77 |
+------+---------+------+---------+
-- CASE WHEN 实现文本列 subject 行转列
SELECT name,
       MAX(CASE WHEN subject = '语文' THEN subject ELSE null END) as chinese,
       MAX(CASE WHEN subject = '数学' THEN subject ELSE null END) as math,
       MIN(CASE WHEN subject = '外语' THEN subject ELSE null END) as english
  FROM score
 GROUP BY name;
+------+---------+------+---------+
| name | chinese | math | english |
+------+---------+------+---------+
| 张三 | 语文    | 数学 | 外语    |
| 李四 | 语文    | 数学 | 外语    |
+------+---------+------+---------+

总结：

• 当待转换列为数字时，可以使用SUM AVG MAX MIN等聚合函数；
• 当待转换列为文本时，可以使用MAX MIN等聚合函数

练习题-第二部分

3.5 判断题

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

我的解答：

正确。NULL与任何值运算的结果都为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);

我的解答：

① 购价不为500、2800、5000的商品
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
|     打孔器    |      320       |
|     擦菜板    |      790       | 
+--------------+----------------+
② 无法取出任何结果

3.7

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

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

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

执行结果

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

我的解答：

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 >= 3001 THEN 1
		 ELSE 0
	END
) AS high_price
FROM product;

OK啦，本章结束~