【初入DW之复杂一点的查询2】

于 2022-05-23 10:57:40 发布
阅读量294 收藏
点赞数
文章标签： sql 学习
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_58311911/article/details/124916626
版权
这篇博客详细介绍了SQL中的函数、谓词和CASE表达式。函数部分涵盖算数、字符串、日期和转换函数，如ABS、MOD、ROUND等；谓词部分讲解了LIKE、BETWEEN、IS NULL、IN和EXISTS的用法；CASE表达式则展示了如何在不同场景下进行条件分支。此外，还提供了多个示例和练习题帮助读者理解。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

文章目录

一、各种各样的函数

SQL自带了各种各样的函数，极大地提高了SQL语言的便利性。
所谓函数，就是类似于一个黑匣子，我们输入一个值，它便按照预设的程序定义给出返回值，这个输入的值就称为参数。

函数大致分为如下几类：

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

函数总个数超过200个，不需要完全记住，常用函数有 30~50 个，其他不常用的函数使用时查阅文档即可。

1.算数函数

为在下面学习过程中更好地演示其他几个算数函数，在此构造samplemath表如下：
在这里插入图片描述

  • ABS- - 绝对值
    语法：ABS(数值)
    ABS 函数用于计算一个数字的绝对值，表示一个数到原点的距离。
    当 ABS 函数的参数为NULL时，返回值也是NULL。

  • MOD- - 求余数
    语法：MOD（被除数，除数）
    MOD 是计算除法余数（求余）的函数，是 modulo 的缩写。小数没有余数的概念，只能对整数列求余数。
    注意：主流的 DBMS 都支持 MOD 函数，只有SQL Server 不支持该函数，其使用%符号来计算余数。

  • ROUND- -四舍五入
    语法：ROUND(对象数值，保留小数的位数)
    注意：当参数 保留小数的位数 为变量时，可能会遇到错误，请谨慎使用变量。

执行代码:

SELECT m,
    ABS(m)ASabs_col , /*取绝对值*/
    n, p,
    MOD(n, p) AS mod_col, /*取余*/
    ROUND(m,1) AS round_col /*四舍五入*/
FROM samplemath;

运行结果：
在这里插入图片描述

2.字符串函数

同样的，为更好地演示下面的字符串函数，我们构造samplestr表如下：
在这里插入图片描述

  • CONCAT – 拼接
    语法：CONCAT(str1, str2, str3)
    MySQL中使用 CONCAT 函数进行拼接。

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

  • LOWER – 小写转换
    LOWER 函数只能针对英文字母使用，它会将参数中的字符串全都转换为小写。该函数不适用于英文字母以外的场合，不影响原本就是小写的字符。
    类似的， UPPER 函数用于大写转换。

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

  • SUBSTRING – 字符串的截取
    语法：SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
    使用 SUBSTRING 函数 可以截取出字符串中的一部分字符串。截取的起始位置从字符串最左侧开始计算，索引（类似下角标）值起始为1。

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

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

执行结果（也就是说以 . 为分隔符，代表第2个分隔符之前的字符串，这里支持正负索引，如果时-2则代表后两个子字符串）：
在这里插入图片描述
获取第一个元素比较容易，如果想要获取第二（n）个元素可以采用二次拆分的写法。

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

执行结果（也就是在原来拆分的基础上再进行一次拆分。）：
在这里插入图片描述

  • 扩展内容）REPEAT – 字符串按需重复多次
    语法：REPEAT(string, number)
    该函数用来对特定字符实现按需重复。
    举个例子:
    在这里插入图片描述

3.日期函数

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

SELECT CURRENT_DATE;--获取当前日期
SELECT CURRENT_TIME;--获取当前时间
SELECT CURRENT_TIMESTAMP;-- 获取当前日期和时间

截取日期元素
语法：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;

运行结果：
在这里插入图片描述

4.转换函数

4.1 CAST 类型转换

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

SELECT CAST('2009.12.14' AS DATE) AS date_col;--将字符串转换成日期类型 
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;--将字符串类型转换成数值类型

4.2 COALESCE 将NULL转换成其他值

语法：COALESCE(数据1，数据2，数据3……)
COALESCE 是 SQL 特有的函数。该函数会返回可变参数 A 中左侧开始第 1个不是NULL的值。参数个数是可变的，因此可以根据需要无限增加。
举个例子：

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

运行结果（返回第一个不是null的值）：
在这里插入图片描述

二、谓词

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

谓词主要有以下几个：

LIKE
BETWEEN
IS NULL、IS NOT NULL
IN
EXISTS

1.LIKE谓语–用于字符串的部分一致查询

部分一致查询可以分为前方一致、中间一致、后方一致三种类型。
同样的为了方便说明来创建一个表samplelike如下：
在这里插入图片描述
前方一致：即作为查询条件的字符串（这里时“ddd”）与查询对象字符串起始部分相同。

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

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

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

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

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

综合如上三种类型的查询可以看出，查询条件最宽松，也就是能够取得最多记录的是中间一致。这是因为它同时包含前方一致和后方一致的查询结果。

SELECT *
FROM samplelike
WHERE strcol LIKE 'abc__';--与%不同的是下划线代表“任意一个字符”

2.BETWEEN谓词–用于范围查询

举个例子：

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

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

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

3. IS NULL、IS NOT NULL --用于判断是否为NULL

在基本查询部分的练习题已经有一个关于NULL的练习题，只有用特定的谓词IS NULL和IS NOT NULL才能选取出是否为NULL的数据，而不能使用‘=’或者‘！=’

4.IN谓词–OR的简便用法

多个查询条件取并集时可以选择使用or语句。
举个例子（相同的过滤结果用or和in）：

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

--通过IN谓词来替换上述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数据的。 实际结果也是如此，上述两组结果中都不包含进货单价为 NULL 的叉子和圆珠笔（实际售价字段是包含有NULL的，不过使用IN谓词不能够进行选取）。 NULL 只能使用 IS NULL 和 IS NOT NULL 来进行判断。

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

IN和子查询
IN 谓词（NOT IN 谓词）具有其他谓词所没有的用法，那就是可以使用子查询作为其参数。我们已经在 5-2 节中学习过了，子查询就是 SQL内部生成的表，因此也可以说“能够将表作为 IN 的参数”。同理，我们还可以说“能够将视图作为 IN 的参数”。

同样的为了方便说明我们使用了两张表如下：
（shopproduct表）
在这里插入图片描述
（表product）
在这里插入图片描述
假设我们要去除大阪在售商品的销售单价，该如何实现呢？
第一步：去除大阪门店的在售商品product_id
第二步：依据第一步取出在售商品的销售单价sale_price

-- step1：取出大阪门店的在售商品 `product_id`
SELECT product_id
FROM shopproduct
WHERE shop_id = '000C';

-- step2：取出大阪门店在售商品的销售单价 `sale_price`
SELECT product_name, sale_price
FROM product
WHERE product_id IN (SELECT product_id
                     FROM shopproduct
                     WHERE shop_id = '000C');

执行结果：
在这里插入图片描述
这里将对应的product_id列出来（‘0003’ ‘0004’ ‘0006’ ‘0007’）也可以仅仅使用IN谓词就可以实现相同的效果，但为什么还要使用子查询呢，这是因为在实际的应用中，人工标号往往是不可取的，商品数量庞大只能依靠电脑自动查询。

NOT IN和其用法相同，将上述代码进行修改：

-- NOT IN 使用子查询作为参数，取出未在大阪门店销售的商品的销售单价
SELECT product_name, sale_price
  FROM product
 WHERE product_id NOT IN (SELECT product_id
                            FROM shopproduct
                           WHERE shop_id = '000A');

6.EXISTS谓词

EXISTS 谓词的用法理解起来有些难度。

① EXISTS 的使用方法与之前的都不相同

② 语法理解起来比较困难

③ 实际上即使不使用 EXISTS，基本上也都可以使用 IN（或者 NOT IN）来代替

这么说的话，还有学习 EXISTS 谓词的必要吗？答案是肯定的，因为一旦能够熟练使用 EXISTS 谓词，就能体会到它极大的便利性。
EXIST谓词的使用方法
谓词的作用就是 “判断是否存在满足某种条件的记录”。
如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。

EXIST（存在）谓词的主语是“记录”。
我们继续以 IN和子查询 中的示例，使用 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);

这里用到了关联子查询，通常EXISTS是只有一个参数的谓词，在右侧书写一个参数，而这个参数通常会是一个子查询。
子查询中的SELECT
由于EXISTS只关心记录是否存在，因此返回哪些列都没有关系。

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

即便这样书写查询语句，查询结果也不会发生变化。
同样NOT EXISTS与其用法相同：
下面的代码示例取出，不在大阪门店销售的商品的销售单价。

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 表达式是函数的一种。是 SQL 中数一数二的重要功能，有必要好好学习一下。
CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支。
CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。由于搜索CASE表达式包含简单CASE表达式的全部功能。本课程将重点介绍搜索CASE表达式。
语法：

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

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

1.CASE表达式的使用方法

应用场景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 NULL
        END AS abc_product_type
  FROM  product;

执行结果：
在这里插入图片描述
ELSE 子句也可以省略不写，这时会被默认为 ELSE NULL。但为了防止有人漏读，还是希望大家能够显式地写出 ELSE 子句。 此外， CASE 表达式最后的“END”是不能省略的，请大家特别注意不要遗漏。忘记书写 END 会发生语法错误，这也是初学时最容易犯的错误。

应用场景2（实现列方向上不同种类的聚合）

--在行的方向上实现不同种类的聚合
SELECT product_type,
       SUM(sale_price) AS sum_price
  FROM product
 GROUP 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_office
  FROM 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 english
  FROM score
 GROUP 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,
       MAX(CASE WHEN subject = '外语' THEN subject ELSE null END) as english
  FROM score
 GROUP BY name;

执行结果：
在这里插入图片描述
总结：
当待转换列为数字时，可以使用SUM AVG MAX MIN等聚合函数；
当待转换列为文本时，可以使用MAX MIN等聚合函数

四、练习题

在这里插入图片描述
product表：
在这里插入图片描述
运行结果（不含500，2800，5000）：
在这里插入图片描述
（带null的筛选不出结果）
在这里插入图片描述
在这里插入图片描述
代码如下（纯属个人理解，如有错误欢迎各位大佬指正）：

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_pricen,
       SUM(CASE WHEN sale_price > 3000 THEN 1 ELSE 0 END) AS high_price
FROM product;

运行结果：
在这里插入图片描述

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值