当前位置： 代码迷 >> 综合 >> datawhale-sql打卡之Task 3:复杂一点的查询
  详细解决方案

datawhale-sql打卡之Task 3:复杂一点的查询

热度：78   发布时间：2023-12-18 04:48:12.0

1 视图

视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的（会在下面具体介绍），所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。
在这里插入图片描述
图片来源：《sql基础教程第2版》

视图的优点

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

创建视图

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

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

修改视图

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

ALTER VIEW productSumASSELECT product_type, sale_priceFROM ProductWHERE regist_date > '2009-09-11';

更新视图

视图是一个虚拟表，所以对视图的操作就是对底层基础表的操作，所以在修改时只有满足底层基本表的定义才能成功修改。
对于一个视图来说，如果包含以下结构的任意一种都是不可以被更新的：

  • 聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
  • DISTINCT 关键字。
  • GROUP BY 子句。
  • HAVING 子句。
  • UNION 或 UNION ALL 运算符。
  • FROM 子句中包含多个表
UPDATE productsumSET sale_price = '5000'WHERE product_type = '办公用品';

在这里插入图片描述

注意：这里虽然修改成功了，但是并不推荐这种使用方式。而且我们在创建视图时也尽量使用限制不允许通过视图来修改表

删除视图

DROP VIEW productSum;

2 子查询

  • 子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1 开始引入，在 SELECT
    子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。
  • 子查询是一次性的，所以子查询不会像视图那样保存在存储介质中， 而是在 SELECT 语句执行之后就消失了。
SELECT stu_name
FROM (SELECT stu_name, COUNT(*) AS stu_cntFROM students_infoGROUP BY stu_age) AS studentSum;

嵌套子查询

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

先计算子查询，子查询结果作为外层另一个查询的过滤条件。所以最内层的子查询我们将其命名为productSum，这条语句根据product_type分组并查询个数，第二层查询中将个数为4的商品查询出来，最外层查询product_type和cnt_product两列。

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

标量子查询

所谓标量子查询，就是子查询会返回一个值，作为外层的参数使用。

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

上面的这条语句首先后半部分查询出product表中的平均售价，前面的sql语句在根据WHERE条件挑选出合适的商品。

由于标量子查询的特性，导致标量子查询不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用。 能够使用常数或者列名的地方，无论是 SELECT 子句、GROUP BY 子句、HAVING 子句，还是 ORDER BY 子句，几乎所有的地方都可以使用。

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

关联子查询

查询出销售单价高于平均销售单价的商品时可以这么写（其实就是标量子查询）：

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

那如果需求是选取出各商品种类中高于该商品种类的平均销售单价的商品时应该怎么写呢？（下半部分解释来自here）
在这里插入图片描述
按照我们正常的select语句执行逻辑，应该从WHERE字句中的括号内的子查询开始，子查询也是一个SELECT语句，应该这样开始执行：

1.FROM Product AS P2 ：指定从Product获取数据。
2.WHERE P1.product_type = P2.product_type
这里的P1和P2都是指向Product表，都是它的别名，所以P1.product_type 与 P2.product_type必然是相等的，所以这个WHERE字句是废话。
但是删除后就会报错，原因是sale_price是一行数据，子查询的结果是3行数据，无法进行比较。更无法得到我们想要的“选取出各商品种类中高于该商品种类的平均销售单价的商品。”这个结果。
实际上，‘WHERE P1.product_type = P2.product_type’这个语句是关联子查询，关联子查询的执行逻辑和正常的SELECT语句执行逻辑完全不同，这就是它的诡异之处了（不知道规定它的人是怎么想的，搞那么复杂的逻辑）。

  • 1.先执行主查询
SELECT product _type , product_name, sale_price
FROM Product AS P1

在这里插入图片描述

  • 2.从主查询的product _type先取第一个值=‘衣服’，通过WHERE P1.product_type = P2.product_type传入子查询，子查询变成
    在这里插入图片描述
  • 3.从子查询得到的结果AVG(sale_price)=2500，返回主查询：
SELECT product_type , product_name, sale_price
FROM Product AS P1
WHERE sale_price > 2500 AND product_type = ‘衣服’

第一次整个语句的结果：
在这里插入图片描述

  • 4.然后，product _type取第二个值，得到整个语句的第二结果，依次类推，把product _type全取值一遍，就得到了整个语句的结果集。结果如下：
    在这里插入图片描述
    事实上，所有关联子查询的执行过程都和上面的过程一样。

3 函数

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

算数函数

  • + - * /四则运算
  • ABS( 数值 ) – 绝对值函数
  • MOD( 被除数，除数 )– 求余数
  • ROUND( 对象数值，保留小数的位数 )– 四舍五入

字符串函数

  • CONCAT – 拼接
    语法：CONCAT(str1, str2, str3)

  • LENGTH – 字符串长度
    语法：LENGTH( 字符串 )

  • LOWER – 小写转换

  • UPPER– 大写转换

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

  • SUBSTRING – 字符串的截取
    语法：SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数），索引值起始为1。

  • SUBSTRING_INDEX – 字符串按索引截取
    语法：SUBSTRING_INDEX (原始字符串， 分隔符，n)
    原始字符串按照分隔符分割后，第 n 个分隔符之前（或之后）的子字符串，支持正向和反向索引，索引起始值分别为 1 和 -1。
    在这里插入图片描述

日期函数

不同DBMS的日期函数语法各有不同，下面是一些被标准 SQL 承认的可以应用于绝大多数 DBMS 的函数。特定DBMS的日期函数查阅文档即可。

  • CURRENT_DATE – 获取当前日期
  • CURRENT_TIME – 当前时间
    在这里插入图片描述
  • CURRENT_TIMESTAMP – 当前日期和时间
    在这里插入图片描述
  • EXTRACT – 截取日期元素
    语法：EXTRACT(日期元素 FROM 日期)

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

“月”，或者“小时”“秒”等。该函数的返回值并不是日期类型而是数值类型。
在这里插入图片描述

转换函数

在 SQL 中主要有两层意思：
一是数据类型的转换，简称为类型转换，在英语中称为cast；
另一层意思是值的转换。

  • CAST – 类型转换
    语法：CAST（转换前的值 AS 想要转换的数据类型）
    在这里插入图片描述
  • COALESCE – 将NULL转换为其他值
    语法：COALESCE(数据1，数据2，数据3……)
    COALESCE 是 SQL 特有的函数。该函数会返回可变参数 A 中左侧开始第 1个不是NULL的值。在 SQL 语句中将 NULL 转换为其他值时就会用到转换函数
    在这里插入图片描述

4 谓语

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

谓词主要有以下几个

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

LIKE– 用于字符串匹配查询

通常与%和_组合使用

  • %是代表零个或多个任意字符串的特殊符号
    _下划线匹配任意 1 个字符
  • 代表查询条件是字符串起始部分是ddd。
SELECT *
FROM samplelike
WHERE strcol LIKE 'ddd%';

LIKE 'ddd%'代表以ddd开头，任意字符串结尾，也可以就ddd三个字符
LIKE '%ddd%代表以ddd在中间，两端是任意字符串，也可以就ddd三个字符
LIKE '%ddd代表以ddd在末尾，任意字符串开头，也可以就ddd三个字符
LIKE "ddd__"代表以ddd开头，必须2个字符串结尾（2个下划线）

BETWEEN – 用于范围查询

-- 选取销售单价为100～ 1000元的商品
SELECT product_name, sale_price
FROM product
WHERE sale_price BETWEEN 100 AND 1000;

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

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

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

SELECT product_name, purchase_price
FROM product
WHERE purchase_price IS NULL;

与此相反，想要选取 NULL 以外的数据时，需要使用IS NOT NULL。

SELECT product_name, purchase_price
FROM product
WHERE purchase_price IS NOT NULL;

IN– OR的集合用法

多个查询条件取并集时可以选择使用or语句。

-- 通过OR指定多个进货单价进行查询
SELECT product_name, purchase_price
FROM product
WHERE purchase_price = 320
OR purchase_price = 500
OR purchase_price = 5000;

虽然上述方法没有问题，但还是存在一点不足之处，那就是随着希望选取的对象越来越多， SQL 语句也会越来越长，这时， 我们就可以使用IN 谓词IN(值1, 值2, 值3, …)来替换上述 SQL 语句。

SELECT product_name, purchase_price
FROM product
WHERE purchase_price IN (320, 500, 5000);

反之，希望选取出“进货单价不是 320 元、 500 元、 5000 元”的商品时，可以使用否定形式NOT IN来实现。

SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (320, 500, 5000);

需要注意的是，在使用IN 和 NOT IN 时是无法选取出NULL数据的。只要in或者not in 碰到null，结果都是空集！
实际结果也是如此，上述两组结果中都不包含进货单价为 NULL 的叉子和圆珠笔。 NULL 只能使用 IS NULL 和 IS NOT NULL 来进行判断。

EXIST

作用就是判断是否存在满足某种条件的记录
如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。EXIST（存在）谓词的主语是“记录”。
EXISTS和NOT EXISTS的作用与IN和NOT IN很相似，但是EXISTS和NOT EXISTS只能在子查询中使用！

  • 使用 EXIST 选取出大阪门店在售商品的销售单价：
SELECT product_name, sale_priceFROM product AS pWHERE EXISTS (SELECT *FROM shopproduct AS spWHERE sp.shop_id = '000C'AND sp.product_id = p.product_id);

EXIST 只关心记录是否存在，因此返回哪些列都没有关系。 EXIST 只会判断是否存在满足子查询中 WHERE 子句指定的条件“商店编号（shop_id）为 ‘000C’，商品（product）表和商店。
商品（shopproduct）表中商品编号（product_id）相同”的记录，只有存在这样的记录时才返回真（TRUE）。因此，使用下面的查询语句，查询结果也不会发生变化

SELECT product_name, sale_priceFROM product AS pWHERE EXISTS (SELECT 1 -- 这里可以书写适当的常数FROM shopproduct AS spWHERE sp.shop_id = '000C'AND sp.product_id = p.product_id);

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

  • 不在大阪门店销售的商品的销售单价：
SELECT product_name, sale_priceFROM product AS pWHERE NOT EXISTS (SELECT *FROM shopproduct AS spWHERE sp.shop_id = '000A'AND sp.product_id = p.product_id);

5 CASE 表达式

CASE 表达式是函数的一种。是 SQL 中数一数二的重要功能。
语法：

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

上述语句执行时，依次判断 when 表达式是否为真值，是则执行 THEN 后的语句，如果所有的 when 表达式均为假，则执行 ELSE 后的语句，其实就是python里的 if elif ... else一样的功能和结构。

举例1：根据不同分支得到不同列值

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 NULLEND AS abc_product_typeFROM  product;

在这里插入图片描述
ELSE 子句也可以省略不写，这时会被默认为 ELSE NULL。但是END不能省略。

举例2： 实现列方向上的聚合

通常我们使用如下代码实现行的方向上不同种类的聚合（这里是 sum）

SELECT product_type,SUM(sale_price) AS sum_priceFROM productGROUP BY product_type;

在这里插入图片描述
假如要在列的方向上展示不同种类额聚合值，该如何写呢？
在这里插入图片描述

-- 对按照商品种类计算出的销售单价合计值进行行列转换
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_officeFROM product;

举例3：实现行转列

在这里插入图片描述

-- 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 englishFROM scoreGROUP BY name;

在这里插入图片描述
也可以实现文本列 subject 的行转列

-- 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 englishFROM scoreGROUP BY name;
  • 当待转换列为数字时，可以使用SUM AVG MAX MIN等聚合函数；
  • 当待转换列为文本时，可以使用MAX MIN等聚合函数

6 练习题

  • 3.1
    在这里插入图片描述
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';

在这里插入图片描述

  • 3.2
    在这里插入图片描述
    在这里插入图片描述
    会报错：Field of view 'datawhale.viewpractice5_1' underlying table doesn't have a default value
    但是update更新成功，视图和原表都发生了变化
    在这里插入图片描述
  • 3.3
    在这里插入图片描述
    使用标量子查询即可
SELECT p1.*,(SELECT AVG(sale_price) FROM product as p2) AS sale_price_all
FROM product AS p1;

在这里插入图片描述

  • 3.4
    在这里插入图片描述
CREATE VIEW AvgPriceByType (product_id,product_name,product_type,sale_price,avg_sale_price)
AS
SELECT p1.*, 
(SELECT AVG(p2.sale_price) 
FROM product AS p2
WHERE p1.product_type=p2.product_type) AS avg_sale_price
FROM product AS p1;
  • 3.5
    运算或者函数中含有 NULL 时，结果全都会变为NULL ？（判断题）
    不对，会将null排除在外
    用count计数时，直接count(列名)会忽略null所在行，而count(1)和count(*)会把包含null的行也计算进去
    在这里插入图片描述
  • 3.6
    在这里插入图片描述
    在这里插入图片描述
    第二条语句本意应该是提取不是500,2800,5000且purchase_price非空的数据，但结果为空集，跟预期的不一样
    在这里插入图片描述
    实际上，只要in或者not in 碰到null，结果都是空集
    使用IN 和 NOT IN 时无法选取出NULL数据，必须用is null 或者is not null
    在这里插入图片描述
  • 3.7
    在这里插入图片描述
SELECT 
SUM(CASE WHEN sale_price<1001 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;

在这里插入图片描述

Task 03:复杂一点的查询

  相关解决方案

代码迷 - 您身边的软件异常，代码异常，编程异常助手（发现，解决，分享）
Copyright © 2009-2013 DAIMAMI.COM. All rights reserved.