第三章复杂一点的查询

学习者pn

已于 2022-06-20 20:42:00 修改

阅读量129

点赞数

文章标签：数据库 mysql sql

于 2022-06-19 23:49:55 首次发布

本文链接：https://blog.csdn.net/weixin_44785383/article/details/125364439

版权

本文详细介绍了数据库中的视图概念，包括其定义、区别、作用及创建、修改、删除方法。视图提供了一种抽象，可以简化数据查询并提高效率。同时，文章探讨了子查询的使用，如嵌套子查询、标量子查询和关联子查询，以及在数据操作中的重要性。此外，还涵盖了函数的应用，如算术、字符串和日期函数，以及在查询中常用的谓词和CASE表达式的用法。

摘要由CSDN通过智能技术生成

第三章复杂一点的查询

3.1 视图

3.1.1 定义

视图是一个虚拟的表，不同于直接操作数据表
语法：

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

3.1.2 视图与表有什么区别

用一句话非常凝练的概括了视图与表的区别—“是否保存了实际的数据”，视图并不是数据库真实存储的数据表，它可以看作是一个窗口，通过这个窗口我们可以看到数据库表中真实存在的数据。

通过对表进行select可产生视图。
“视图不是表，视图是虚表，视图依赖于表”。

3.1.3 视图作用

频繁使用的SELECT语句保存以提高效率
使用户看到的数据更加清晰
不对外公开数据表全部字段，增强数据的保密性
降低数据的冗余

3.1.4 如何创建视图

例如：

基于单表的视图

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

基于多表的视图

CREATE VIEW view_shop_product(product_type, sale_price, shop_name)
AS
SELECT product_type, sale_price, shop_name
 FROM product,
      shop_product
WHERE product.product_id = shop_product.product_id;

shop_product与product为两个不同的表
SELECT 语句需要书写在 AS 关键字之后。 SELECT 语句中列的排列顺序和视图中列的排列顺序相同
注意事项：

1.在一般的DBMS中定义视图时不能使用ORDER BY语句，这是因为视图和表一样，数据行都是没有顺序的！
select函数可以用是因为select是查询。

2.在 MySQL中视图的定义是允许使用 ORDER BY 语句的，但是若从特定视图进行选择，而该视图使用了自己的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略。

3.可以在视图的基础上继续创建视图

3.1.5 修改视图（改为另一张图）

语法：

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

例如：

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

3.1.6更新视图（需要更新原表）

例如：

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

则改变原表中的视图部分数据，视图外的部分则不发生改变。（修改也只能修改透过窗口能看到的内容。）尽量不使用这种方法！

3.1.7 删除视图

语法：

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

3.2 子查询

定义：子查询指一个查询语句嵌套在另一个查询语句内部的查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表

3.2.1 嵌套子查询

例如：

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两列提取查询。

应尽量避免这样嵌套多层！

3.2.4 标量子查询

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

与

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

执行结果相同
标量子查询指：要求我们执行的SQL语句只能返回一个值，也就是要返回表中具体的某一行的某一列。在上例中标量子查询就是查询得到均值。

3.2.5 关联子查询

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

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

如上所示p1.product_type = p2.product_type即为关联。
选取出各商品种类中高于该商品种类的平均销售单价的商品：

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

操作流程：

首先执行不带WHERE的主查询
根据主查询讯结果匹配product_type，获取子查询结果
将子查询结果再与主查询结合执行完整的SQL语句

3.3 各种各样的函数

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

3.3.1算术函数

ABS – 绝对值
语法：ABS( 数值 )
MOD – 求余数
语法：MOD( 被除数，除数 )
ROUND – 四舍五入
语法：ROUND( 对象数值，保留小数的位数 )

3.3.2 字符串函数

CONCAT – 拼接
语法：CONCAT(str1, str2, str3)
LENGTH – 字符串长度
语法：LENGTH( 字符串 )
LOWER – 小写转换
LOWER 函数只能针对英文字母使用，它会将参数中的字符串全都转换为小写。该函数不适用于英文字母以外的场合，不影响原本就是小写的字符。

类似的， UPPER 函数用于大写转换。
4. REPLACE – 字符串的替换
语法：REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )
5. SUBSTRING – 字符串的截取
语法：SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
截取的起始位置从字符串最左侧开始计算，索引值起始为1。

（扩展内容）SUBSTRING_INDEX – 字符串按索引截取
语法：SUBSTRING_INDEX (原始字符串，分隔符，n)
其中分隔符是原始字符串中已有的
例如：

SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
+-------------------------------------------+
| SUBSTRING_INDEX('www.mysql.com', '.', -2) |
+-------------------------------------------+
| mysql.com                                 |

该函数用来获取原始字符串按照分隔符分割后，第 n 个分隔符之前（或之后）的子字符串，支持正向和反向索引，索引起始值分别为 1 和 -1。获取第2个元素/第n个元素可以采用二次拆分的写法。
例如:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1);

REPEAT – 字符串按需重复多次

语法：REPEAT(string, number)

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

3.3.3 日期函数

函数	功能
CURRENT_DATE	获取当前日期
CURRENT_TIME	当前时间
CURRENT_TIMESTAMP	当前日期和时间
EXTRACT	截取日期元素

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

3.3.4 转换函数

函数	功能	语法
CAST	类型转换	语法：CAST（转换前的值 AS 想要转换的数据类型）
COALESCE	将NULL转换为其他值	语法：COALESCE(数据1，数据2，数据3……)

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

3.4 谓词

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

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

3.4.2 BETWEEN谓词 – 用于范围查询

使用 BETWEEN 可以进行范围查询。
注：BETWEEN结果会包含两个临界值，如果不想让结果中包含临界值，那就必须使用 < 和 >。

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

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

SELECT product_name, sale_price
FROM product
WHERE product_id IN ('0003', '0004', '0006', '0007');

其中’0003’, ‘0004’, ‘0006’, '0007’其实是通过子查询得到的结果，但这种方法效率低，需要维护。
可以用以下方法：

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

3.4.5 EXIST 谓词

作用：判断是否存在满足某种条件的记录
EXIST 只需要在右侧书写 1 个参数，该参数通常都会是一个子查询

3.5 CASE 表达式

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

语法：

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

例如：

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;

应用场景2：实现列方向上的聚合

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;

可以实现将sum函数的结果用第一二三列的形式呈现。

（扩展内容）应用场景3：实现行转列
类似于场景2，例：

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;

总结：

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

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

练习题：

3.1

创建出满足下述三个条件的视图（视图名称为 ViewPractice5_1）。使用 product（商品）表作为参照表，假设表中包含初始状态的 8 行数据。

条件 1：销售单价大于等于 1000 日元。
条件 2：登记日期是 2009 年 9 月 20 日。
条件 3：包含商品名称、销售单价和登记日期三列。
对该视图执行 SELECT 语句的结果如下所示。

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

答：

CREATE VIEW ViewPractice5_1 AS
SELECT product_name, sale_price, regist_date
    FROM product
WHERE sale_price >= 1000
      AND regist_date = '2009-09-20';

3.2（错）

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

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

答：
插入时将会报错。
视图插入数据时，原表也会插入数据，而原表数据插入时不满足约束条件，所以会报错。（因为 ViewPractice5_1 的原表有三个带有 NOT NULL 约束的字段）

3.3

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

3.4

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

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

答：3.3与3.4

3.3

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

3.4

-- 创建视图的语句
CREATE VIEW AvgPriceByType AS
SELECT product_id,
       product_name,
       product_type,
       sale_price,
       (SELECT AVG(sale_price)
            FROM product p2
        WHERE p1.product_type = p2.product_type
        GROUP BY p1.product_type) AS sale_price_avg_type
 FROM product p1;

-- 确认视图内容
SELECT * FROM AvgPriceByType;

3.5 判断题

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

答：正确

3.6

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

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

答：
前者结果显然。后者带有NULL，实际上它却返回了零条记录，这是因为 NOT IN 的参数中不能包含 NULL，否则，查询结果通常为空。

3.7

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

低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、圆珠笔）

中档商品：销售单价在1001日元以上3000日元以下（菜刀）

高档商品：销售单价在3001日元以上（运动T恤、高压锅）

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

执行结果

low_price	mid_price	high_price
5	1	2

答：
显然用case语句

SELECT 
    SUM(CASE WHEN sale_price <= 1000 THEN 1 ELSE 0 END)               AS low_price,
    SUM(CASE WHEN sale_price BETWEEN 1001 AND 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;