06 SQL进阶 -- 复杂查询方法 -- 函数、谓词与CASE 表达式

置顶 已于 2024-07-02 10:04:57 修改
阅读量1.5k 收藏 8
点赞数 30
分类专栏： SQL必知必会 文章标签： sql java 数据库
于 2024-04-13 20:00:00 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/luorongxi123/article/details/137697988
版权

在这里插入图片描述

1. 各种各样的函数

sql自带了各种各样的函数，极大提高了sql语言的便利性。

所谓函数，类似一个黑盒子，你给它一个输入值，它便按照预设的程序定义给出返回值，输入值称为参数。

函数大致分为如下几类：

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

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

1.1 算术函数

+ - * /四则运算在之前的章节介绍过，此处不再赘述。

为了演示其他的几个算数函数，在此构造samplemath表

– DDL ：创建表

USE shop;
DROP TABLE IF EXISTS samplemath;
CREATE TABLE samplemath
(m float(10,3),
n INT,
p INT);
在这里插入图片描述
– DML ：插入数据

INSERT INTO samplemath(m, n, p) VALUES (500, 0, NULL);
INSERT INTO samplemath(m, n, p) VALUES (-180, 0, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, NULL, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 7, 3);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 5, 2);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 4, NULL);
INSERT INTO samplemath(m, n, p) VALUES (8, NULL, 3);
INSERT INTO samplemath(m, n, p) VALUES (2.27, 1, NULL);
INSERT INTO samplemath(m, n, p) VALUES (5.555,2, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 1, NULL);
INSERT INTO samplemath(m, n, p) VALUES (8.76, NULL, NULL);
在这里插入图片描述

– 查询表内容

SELECT * FROM samplemath;
在这里插入图片描述

ABS – 绝对值

语法：ABS( 数值 )

ABS 函数用于计算一个数字的绝对值，表示一个数到原点的距离。

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

MOD – 求余数

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

MOD 是计算除法余数（求余）的函数，是 modulo 的缩写。小数没有余数的概念，只能对整数列求余数。

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

ROUND – 四舍五入

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

ROUND 函数用来进行四舍五入操作。

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

SELECT m,
ABS(m) AS abs_col ,
n, p,
MOD(n, p) AS mod_col,
ROUND(m,1)AS round_colS
FROM samplemath;
在这里插入图片描述

1.2 字符串函数

字符串函数也经常被使用，为了学习字符串函数，在此我们构造samplestr表。

– DDL ：创建表

USE shop;
DROP TABLE IF EXISTS samplestr;
CREATE TABLE samplestr
(str1 VARCHAR (40),
str2 VARCHAR (40),
str3 VARCHAR (40)
);
在这里插入图片描述

– DML：插入数据

INSERT INTO samplestr (str1, str2, str3) VALUES (‘opx’, ‘rt’, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES (‘abc’, ‘def’, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES (‘太阳’, ‘月亮’, ‘火星’);
INSERT INTO samplestr (str1, str2, str3) VALUES (‘aaa’, NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES (NULL, ‘xyz’, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES (‘ABC’, NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES (‘aBC’, NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES (‘abc哈哈’, ‘abc’, ‘ABC’);
INSERT INTO samplestr (str1, str2, str3) VALUES (‘abcdefabc’, ‘abc’, ‘ABC’);
INSERT INTO samplestr (str1, str2, str3) VALUES (‘micmic’, ‘i’, ‘I’);
在这里插入图片描述

– 确认表中的内容

SELECT * FROM samplestr;
在这里插入图片描述

CONCAT – 拼接

语法：CONCAT(str1, str2, str3)

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

LENGTH – 字符串长度

语法：LENGTH( 字符串 )

LOWER – 小写转换

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

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

REPLACE – 字符串的替换

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

SUBSTRING – 字符串的截取

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

使用 SUBSTRING 函数 可以截取出字符串中的一部分字符串。截取的起始位置从字符串最左侧开始计算，索引值起始为1。
在这里插入图片描述

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

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

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

SELECT SUBSTRING_INDEX(‘www.mysql.com’, ‘.’, 2);
在这里插入图片描述

SELECT SUBSTRING_INDEX(‘www.mysql.com’, ‘.’, -2);
在这里插入图片描述

获取第1个元素比较容易，获取第2个元素/第n个元素可以采用二次拆分的写法。

SELECT SUBSTRING_INDEX(‘www.mysql.com’, ‘.’, 1);
在这里插入图片描述

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(‘www.mysql.com’, ‘.’, 2), ‘.’, -1);
在这里插入图片描述

1.3 日期函数

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

CURRENT_DATE – 获取当前日期

SELECT CURRENT_DATE;
在这里插入图片描述

CURRENT_TIME – 当前时间

SELECT CURRENT_TIME;
在这里插入图片描述

CURRENT_TIMESTAMP – 当前日期和时间
在这里插入图片描述

EXTRACT – 截取日期元素

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

使用 EXTRACT 函数可以截取出日期数据中的一部分，例如“年”，“月”，或者“小时”“秒”等。该函数的返回值并不是日期类型而是数值类型

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

1.4 转换函数

“转换”这个词的含义非常广泛，在 SQL 中主要有两层意思：一是数据类型的转换，简称为类型转换，在英语中称为cast；另一层意思是值的转换。

CAST – 类型转换

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

– 将字符串类型转换为数值类型

SELECT CAST(‘0001’ AS SIGNED INTEGER) AS int_col;
在这里插入图片描述

– 将字符串类型转换为日期类型

SELECT CAST(‘2009-12-14’ AS DATE) AS date_col;
在这里插入图片描述

COALESCE – 将NULL转换为其他值

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

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

在 SQL 语句中将 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;
在这里插入图片描述

2. 谓词

2.1 什么是谓词

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

谓词主要有以下几个：

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

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

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

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

首先我们来创建一张表

– DDL ：创建表

CREATE TABLE samplelike
(strcol VARCHAR(6) NOT NULL,
PRIMARY KEY (strcol));
在这里插入图片描述

– DML ：插入数据

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’);
在这里插入图片描述

SELECT * FROM samplelike;
在这里插入图片描述

前方一致： 选取出“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 个字符

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

SELECT *
FROM samplelike
WHERE strcol LIKE ‘abc__’;
在这里插入图片描述

2.3 BETWEEN 谓词 – 用于范围查询

使用 BETWEEN 可以进行范围查询。该谓词与其他谓词或者函数的不同之处在于它使用了 3 个参数。

– 选取销售单价为100～ 1000元的商品

SELECT product_name, sale_price
FROM product
WHERE sale_price BETWEEN 100 AND 1000;
在这里插入图片描述

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

SELECT product_name, sale_price
FROM product
WHERE sale_price > 100
AND sale_price < 1000;
在这里插入图片描述

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

2.5 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 语句。
在这里插入图片描述

上述语句简洁了很多，可读性大幅提高。

反之，希望选取出“进货单价不是 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 只能使用 IS NULL 和 IS NOT NULL 来进行判断。

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

IN和子查询

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

在此，我们创建一张新表shopproduct显示出哪些商店销售哪些商品。

– DDL ：创建表

DROP TABLE IF EXISTS shopproduct;
CREATE TABLE shopproduct
( shop_id CHAR(4) NOT NULL,
shop_name VARCHAR(200) NOT NULL,
product_id CHAR(4) NOT NULL,
quantity INTEGER NOT NULL,
PRIMARY KEY (shop_id, product_id)
);
在这里插入图片描述

– DML ：插入数据

INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES (‘000A’, ‘北京’, ‘0001’, 30);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES (‘000A’, ‘北京’, ‘0002’, 50);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES (‘000A’, ‘北京’, ‘0003’, 15);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES (‘000B’, ‘天津’, ‘0002’, 30);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES (‘000B’, ‘天津’, ‘0003’, 120);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES (‘000B’, ‘天津’, ‘0004’, 20);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES (‘000B’, ‘上海’, ‘0006’, 10);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES (‘000B’, ‘上海’, ‘0007’, 40);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES (‘000C’, ‘广州’, ‘0003’, 20);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES (‘000C’, ‘广州’, ‘0004’, 50);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES (‘000C’, ‘广州’, ‘0006’, 90);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES (‘000C’, ‘重庆’, ‘0007’, 70);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES (‘000D’, ‘重庆’, ‘0001’, 100);
在这里插入图片描述

SELECT * FROM shopproduct;
在这里插入图片描述

由于单独使用商店编号（shop_id）或者商品编号（product_id）不能区分表中每一行数据
，因此指定了 2 列作为主键（primary key）对商店和商品进行组合，用来唯一确定每一行数据。

假设我么需要取出广州在售商品的销售单价，该如何实现呢？

– 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’);
在这里插入图片描述

子查询是从最内层开始执行的（由内而外），因此，上述语句的子查询执行之后，sql 展开成下面的语句

– 子查询展开后的结果

SELECT product_name, sale_price
FROM product
WHERE product_id IN (‘0003’, ‘0004’, ‘0006’, ‘0007’);
在这里插入图片描述

可以看到，子查询转换之后变为 in 谓词用法，你理解了吗？

或者，你会疑惑既然 in 谓词也能实现，那为什么还要使用子查询呢？这里给出两点原因：

①：实际生活中，某个门店的在售商品是不断变化的，使用 in 谓词就需要经常更新 sql 语句，降低了效率，提高了维护成本；

②：实际上，某个门店的在售商品可能有成百上千个，手工维护在售商品编号真是个大工程。

使用子查询即可保持 sql 语句不变，极大提高了程序的可维护性，这是系统开发中需要重点考虑的内容。

NOT IN和子查询

NOT IN 同样支持子查询作为参数，用法和 in 完全一样。

– NOT IN 使用子查询作为参数，取出未在广州门店销售的商品的销售单价

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

在这里插入图片描述

2.7 EXIST 谓词

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

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

② 语法理解起来比较困难

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

这么说的话，还有学习 EXIST 谓词的必要吗？答案是肯定的，因为一旦能够熟练使用 EXIST 谓词，就能体会到它极大的便利性。

不过，你不用过于担心，本课程介绍一些基本用法，日后学习时可以多多留意 EXIST 谓词的用法，以期能够在达到 SQL 中级水平时掌握此用法。

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

EXIST的参数

之前我们学过的谓词，基本上都是像“列 LIKE 字符串”或者“ 列 BETWEEN 值 1 AND 值 2”这样需要指定 2 个以上的参数，而 EXIST 的左侧并没有任何参数。因为 EXIST 是只有 1 个参数的谓词。 所以，EXIST 只需要在右侧书写 1 个参数，该参数通常都会是一个子查询。

上面这样的子查询就是唯一的参数。确切地说，由于通过条件“sp.product_id = p.product_id”将 product 表和 shopproduct表进行了联接，因此作为参数的是关联子查询。 EXIST 通常会使用关联子查询作为参数。

子查询中的SELECT *

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

因此，使用下面的查询语句，查询结果也不会发生变化。

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

大家可以把在 EXIST 的子查询中书写 SELECT * 当作 SQL 的一种习惯。

使用NOT EXIST替换NOT IN

就像 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);
在这里插入图片描述

NOT EXIST 与 EXIST 相反，当“不存在”满足子查询中指定条件的记录时返回真（TRUE）。

3. CASE 表达式

3.1 什么是 CASE 表达式

CASE 表达式是函数的一种。是 SQL 中数一数二的重要功能，有必要好好学习一下。

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

CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。由于搜索CASE表达式包含简单CASE表达式的全部功能。本课程将重点介绍搜索CASE表达式。

语法：

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

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

无论多么庞大的 CASE 表达式，最后也只会返回一个值。

3.2 CASE 表达式的使用方法

假设现在 要实现如下结果：

A ：衣服
B ：办公用品
C ：厨房用具

因为表中的记录并不包含“A ： ”或者“B ： ”这样的字符串，所以需要在 SQL 中进行添加。并将“A ： ”“B ： ”“C ： ”与记录结合起来。

应用场景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：实现列方向上的聚合

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

SELECT product_type,
SUM(sale_price) AS sum_price
FROM product
GROUP BY product_type;
在这里插入图片描述

假如要在列的方向上展示不同种类额聚合值，该如何写呢？

sum_price_clothes | sum_price_kitchen | sum_price_office
------------------±------------------±----------------
5000 | 11180 | 600

聚合函数 + CASE WHEN 表达式即可实现该效果

– 对按照商品种类计算出的销售单价合计值进行行列转换

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：实现行转列

假设有如下图表的结构
在这里插入图片描述

– 建表语句
DROP TABLE IF EXISTS score;
CREATE TABLE score
(name VARCHAR(64) NOT NULL,
subject VARCHAR(64) NOT NULL,
score INTEGER NOT NULL
);
在这里插入图片描述

计划得到如下的图表结构
在这里插入图片描述

INSERT INTO score (name, subject, score) VALUES (‘张三’, ‘语文’, 93);
INSERT INTO score (name, subject, score) VALUES (‘张三’, ‘数学’, 88);
INSERT INTO score (name, subject, score) VALUES (‘张三’, ‘外语’, 91);
INSERT INTO score (name, subject, score) VALUES (‘李四’, ‘语文’, 87);
INSERT INTO score (name, subject, score) VALUES (‘李四’, ‘数学’, 90);
INSERT INTO score (name, subject, score) VALUES (‘李四’, ‘外语’, 77);
在这里插入图片描述

聚合函数 + 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;
在这里插入图片描述

上述代码实现了数字列 score 的行转列，也可以实现文本列 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 english
FROM score
GROUP BY name;
在这里插入图片描述

总结：

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

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

4. 练习题

4.1 练习题1

运算或者函数中含有 NULL 时，结果全都会变为NULL ？（判断题）

答案： 在 MySQL 中，这种说法是正确的。

4.2 练习题2

对本章中使用的 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);
在这里插入图片描述

4.3 练习题3

按照销售单价（ sale_price）对练习 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 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;
在这里插入图片描述

腾飞开源
关注
  • 30
    点赞
  • 踩
  • 8
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
SQL进阶-复杂查询方法-视图、子查询、函数等
一些技术文字
10-19 569
视图 SELECT stu_name FROM view_students_info; 单从表面上看起来这个语句是和正常的从数据表中查询数据是完全相同的，但其实我们操作的是一个视图。所以从SQL的角度来说操作视图与操作表看起来是完全相同的，那么为什么还会有视图的存在呢？视图到底是什么？视图与表有什么不同呢？ 什么是视图 视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的，所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。 视图与表有
SQL训练营：SQL进阶-复杂查询方法
GIVEN777的博客
10-14 361
本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql Part1：学习知识点概要 本笔记主要记录SQL语言的复杂查询方法，包括试图、子查询、函数、谓词等内容。 Part2：学习内容 一、视图 视图并不是数据库真实存储的数据表，它可以看作是一个窗口，通过这个窗口我们可以看到数据库表中真实存在的数据。视图存在的原因：1、保存SELECT语句提高效率；2、使用户看到的数据更清晰；3、不对.
SQL基本功（五）--函数、谓词、CASE表达式
2401_83704192的博客
04-21 983
*ROUND 函数：**ROUND( 对象数值，保留小数的位数 **)**ROUND 函数用来进行四舍五入操作。**MOD 函数：**MOD( 被除数，除数 )，MOD 是计算除法余数（求余）的函数，是 modulo 的缩写。**REPLACE函数：**REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )使用 REPLACE 函数，可以将字符串的一部分替换为其他的字符串。**CURRENT_TIME函数：**CURRENT_TIME 函数能够取得 SQL 执行的时间，也就是该函数执。
SQL进阶：复杂查询方法-视图、子查询、函数等
wawa66的博客
06-01 112
本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql；CASE 表达式是函数的一种。是 SQL 中数一数二的重要功能，有必要好好学习一下。CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支。CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。由于搜索CASE表达式包含简单CASE表达式的全部功能。本课程将重点介绍搜索CASE表达式。语法：EXIST
精通SQL--结构化查询语言详解
12-11
7.3.4 使用case表达式 124 7.4 函数 126 7.4.1 有关函数的说明 126 7.4.2 字符处理函数 126 7.4.3 算术运算函数 131 7.4.4 日期时间函数 133 7.4.5 convert()函数转换日期、时间 136 第8章 聚合分析与分组 ...
初级教程sql-tutorial【程序员VIP专用】.zip
05-08
【教程目录】 （1）Sample ├── Ch01 //数据库和SQL ...├── Ch06 //函数、谓词、CASE表达式 ├── Ch07 //集合运算 ├── Ch08 //SQL高级处理 └── CreateTable //创建的表 （2）answer--每章节课后练习
(第二卷)Microsoft SQL Server 2008技术内幕：T-SQL语言基础
10-22
2.3　CASE表达式 2.4　NULL值 2.5　同时操作（ALL-AT-ONCE OPERATION） 2.6　处理字符数据 2.7　处理日期和时间数据 2.8　查询元数据 2.9　总结 2.10　练习 2.11　解决方案 第3章　联接查询 3.1　交叉联接...
(第一卷)Microsoft.SQL.Server.2008技术内幕：T-SQL语言基础
10-22
2.3　CASE表达式 2.4　NULL值 2.5　同时操作（ALL-AT-ONCE OPERATION） 2.6　处理字符数据 2.7　处理日期和时间数据 2.8　查询元数据 2.9　总结 2.10　练习 2.11　解决方案 第3章　联接查询 3.1　交叉联接 3.2　内联...
SQL进阶知识笔记1
08-03
本文主要涵盖了SQL的一些进阶知识，包括CASE表达式、自连接、三值逻辑和NULL处理、HAVING子句、外连接的用法以及集合运算。 首先，CASE表达式在SQL中允许我们根据条件动态地设置值，它可以在GROUP BY子句中使用，...
PostgreSQL 中如何解决因大量并发删除和插入操作导致的索引抖动？
技术笔记
07-17 845
索引抖动是 PostgreSQL 中一个常见的问题，它会对数据库的性能产生严重的影响。通过采用批量操作、分区表、索引优化和调整数据库参数等方法，我们可以有效地解决索引抖动问题，提高数据库的性能和稳定性。在实际应用中，我们应该根据具体情况选择合适的解决方案，并不断地进行优化和调整，以满足业务的需求。解决索引抖动问题就像是一场战斗，我们需要根据敌人（问题）的特点和弱点，选择合适的武器（解决方案），并灵活运用战术（优化方法），才能取得最终的胜利。
接口开发：Orcal数据库的批量新增sql
qq_65032048的博客
07-17 783
场景：在日常的CURD中一定会用到批量新增。在我们的项目中，使用的数据库是Orcal，由于之前基本都是使用Mysql的，使用的sql语句也基本都是用mysql的。但是在这次的接口编写时用mysql的批量新增出了问题，刚开始我还以为是写的动态sql有问题，在后续的问题修改也主要是对动态sql修改，因此也浪费了大量的时间，在后续把sql搬到数据库运行一下后才发现是sql的问题，同时也了解到了mysql和orcal 也是有很多地方是不同的。
SQL执行流程、SQL执行计划、SQL优化
风中的默默
07-19 296
返回两个表中的匹配行。返回左表中的所有记录以及右表中的匹配记录。返回右表中的所有记录以及左表中的匹配记录。返回左侧或右侧表中有匹配的所有记录。
SQL每日一题：查找重复的电子邮箱
xiongxiaoxuan的博客
07-17 362
编写解决方案来报告所有重复的电子邮件。请注意，可以保证电子邮件字段不为 NULL。以 任意顺序 返回结果表。按照email分组计数，筛选出数量>1的email。(需注意分组计数时一定要记得用group by)此表的每一行都包含一封电子邮件。电子邮件不包含大写字母。id 是该表的主键（具有唯一值的列）。
SQL Server报告服务的艺术：在SSRS中打造专业报告
最新发布
2401_85760095的博客
07-20 637
SQL Server Reporting Services是一个全面的报告解决方案，它允许从各种数据源生成复杂的交互式和移动友好的报告。SSRS包括一个集成的开发环境，用于设计报告，以及一个部署环境，用于发布和共享报告。
sqlalchemy定期保持mysql连接活跃
ithongchou的博客
07-18 1446
数据库时，确保保持活跃连接是很重要的，特别是在长时间不使用数据库连接时。使用连接池管理数据库连接。通过配置合适的连接池参数可以有效地保持活跃连接。，以保持连接的活跃状态。这可以通过任务调度库如。（超出连接池大小时允许创建的额外连接数）和。（连接在被放回连接池前的最大生存时间）等。查询，保持数据库连接的活跃状态。可以定期执行简单的查询，例如。这个例子会每隔一段时间执行。
【PostgreSQL】Windows 上安装 PostgreSQL 16版本
No8g攻城狮的博客
07-17 1559
✌全网粉丝20W+，CSDN博客专家、Java领域优质创作者，掘金/华为云/阿里云/InfoQ等平台优质作者、专注于Java技术领域✌。
SQL Server的视图
weixin_49668551的博客
07-16 1028
SQL 视图（Views）是一种虚拟表，是基于 SQL 查询结果生成的。这些虚拟表可以包含来自一个或多个表的数据，并且可以像表一样查询；视图是一个表中的数据经过某种筛选后的显示方式，或者多个表中的数据经过连接筛选后的显示方式。使用视图可以简化复杂查询，提高查询的可读性和可维护性，并提供数据的不同视图。视图由一个预定义的查询（SELECT语句）组成，可以像基本表一样用于SELECT语句中；
在 PostgreSQL 里如何实现数据的分布式事务的事务传播和协调？
技术笔记
07-18 874
在分布式系统中，一个事务可能涉及到多个不同的节点或数据库。分布式事务就是要确保在这些多个操作中，要么所有的操作都成功提交，要么所有的操作都回滚，以保持数据的一致性。打个比方，这就好比一个团队合作完成一个项目，如果其中一个环节出了问题，整个项目都可能受到影响，所以需要确保每个环节都能顺利完成，或者在出现问题时能够及时回滚，避免出现部分完成、部分失败的情况。在本文中，我们深入探讨了在 PostgreSQL 中如何实现数据的分布式事务的事务传播和协调。
SAP HANA数据库SQL参考：函数、公式与计算
文档内容包括SQL语法、数据类型、谓词、操作符、表达式以及一系列的SQL函数，旨在帮助用户更好地在Web Intelligence报告中进行复杂的数据分析和计算。" 在SAP BusinessObjects Web Intelligence中，使用函数、公式...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

腾飞开源

你的鼓励将是我创作的最大动力！

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值