介绍
编程语言通常具有 conditional statements,这些命令执行特定的操作,直到满足一定条件。
1if condition=true
2
3 then action A
4
5 else action B
这个陈述的逻辑翻译成以下语言:`如果条件是真的,那么执行操作A;否则(else),执行操作B。
CASE表达式是结构化查询语言(SQL)中的一个功能,允许您将类似的逻辑应用到数据库查询中,并为您想要返回或显示结果集中的值设置条件。
在本教程中,您将学习如何使用CASE表达式来设置数据的条件,使用WHEN,THEN,ELSE和END关键字。
前提条件
要完成本教程,您将需要:
- 运行 Ubuntu 20.04 的服务器,具有
sudo管理权限和启用防火墙的非 root 用户。 遵循我们的 Ubuntu 20.04 初始服务器设置以启动 - MySQL 安装并在服务器上安全。 遵循我们的 如何在 Ubuntu 20.04 上安装 MySQL指南来设置此功能。 本指南假定您也已设置了非 root MySQL 用户,如本指南的 步骤 3所述。
<$>[注] 注: 请注意,许多关系数据库管理系统使用自己的独特的SQL实现,尽管本教程中描述的命令将在大多数RDBMS上工作,但如果您在MySQL以外的系统上测试它们,精确的语法或输出可能会有所不同。
要练习使用本教程中的CASE表达式,你需要一个数据库和表加载样本数据. 如果你没有一个准备好插入,你可以阅读以下 连接到MySQL和设置样本数据库部分,学习如何创建数据库和表。
连接到MySQL并设置样本数据库
如果您的 SQL 数据库在远程服务器上运行,则从本地计算机输入 SSH 到您的服务器:
1[environment local]
2ssh sammy@your_server_ip
接下来,打开MySQL提示,用您的MySQL用户帐户信息代替sammy:
1mysql -u sammy -p
创建一个名为caseDB的数据库:
1CREATE DATABASE caseDB;
如果数据库创建成功,您将收到以下输出:
1[secondary_label Output]
2Query OK, 1 row affected (0.01 sec)
要选择caseDB数据库,运行以下USE语句:
1USE caseDB;
1[secondary_label Output]
2Database changed
选择数据库后,在其中创建一个表. 对于本教程的示例,我们将创建一个表,其中包含所有时间最畅销的十张专辑的数据。
music_id:显示了int数据类型的值,并将用作表的 primary key,这意味着本列中的每个值将作为其相应行的唯一标识符artist_name:使用varchar数据类型存储每个艺术家的名称,最多使用 30 个字符album_name: 使用varchar数据类型,最多包含 30 个字符release_date: 跟踪每张专辑的发布日期使用DATE数据类型 使用YYYYY-MM-DD日期格式(
创建一个名为top_albums的表,其中包含这些列中的每一个,运行以下命令CREATE TABLE:
1CREATE TABLE top_albums (
2music_id int,
3artist_name varchar(30),
4album_name varchar(30),
5release_date DATE,
6genre_type varchar(25),
7copies_sold decimal(4,1),
8PRIMARY KEY (music_id)
9);
接下来,将一些样本数据插入空表中:
1INSERT INTO top_albums
2(music_id, artist_name, album_name, release_date, genre_type, copies_sold)
3VALUES
4(1, 'Michael Jackson', 'Thriller', '1982-11-30', 'Pop', 49.2),
5(2, 'Eagles', 'Hotel California', '1976-12-08', 'Soft Rock', 31.5),
6(3, 'Pink Floyd', 'The Dark Side of the Moon', '1973-03-01', 'Progressive Rock', 21.7),
7(4, 'Shania Twain', 'Come On Over', '1997-11-04', 'Country', 29.6),
8(5, 'AC/DC', 'Back in Black', '1980-07-25', 'Hard Rock', 29.5),
9(6, 'Whitney Houston', 'The Bodyguard', '1992-11-25', 'R&B', 32.4),
10(7, 'Fleetwood Mac', 'Rumours', '1977-02-04', 'Soft Rock', 27.9),
11(8, 'Meat Loaf', 'Bat Out of Hell', '1977-10-11', 'Hard Rock', 21.7),
12(9, 'Eagles', 'Their Greatest Hits 1971-1975', '1976-02-17', 'Country Rock', 41.2),
13(10, 'Bee Gees', 'Saturday Night Fever', '1977-11-15', 'Disco', 21.6);
1[secondary_label Output]
2Query OK, 10 rows affected (0.01 sec)
3Records: 10 Duplicates: 0 Warnings: 0
一旦您输入了数据,您就可以开始在 SQL 中使用CASE表达式。
理解案例语法语法
CASE表达式允许您为数据设置条件,并使用类似于如果表达式的逻辑来搜索数据,比较值,并评估它们是否与您设置的条件匹配为真。
1[label CASE expression syntax]
2. . .
3CASE
4 WHEN condition_1 THEN outcome_1
5 WHEN condition_2 THEN outcome_2
6 WHEN condition_3 THEN outcome_3
7 ELSE else_outcome
8END
9. . .
根据您想要为数据设置多少条件,您还会在一个CASE表达式中包含以下关键字:
WHEN:此关键字对您在表中的数据值进行评估和比较,与您设置的条件或标准相比。WHEN与典型的if-then-else语句中的if可比。 _)*THEN:此关键字通过您可能设置的每个条件过滤,如果特定值不符合标准ELSE:如果数据值在通过每个WHEN和THEN语句后不符合您设置的任何条件,那么此关键字可以用来指定最终条件它可以分类为END: 要成功运行CASE表达式并设置您的条件,您必须用 `END 关键字(MBRK1)结束。
有了这种对CASE表达结构和语法的理解,您已经准备好开始练习样本数据。
使用案例表达式
想象一下,你是一名DJ,为你奇异的卡罗尔姨妈的65岁生日庆祝准备了一张专辑名单,你知道她的口味很难辨别,所以你决定对所有时间的十大销售专辑进行一些研究,以便告知你的一些音乐决策。
首先,通过运行SELECT和*符号来检查您在top_albums表中编译的列表,以查看每个列的所有数据:
1SELECT * FROM top_albums;
1[secondary_label Output]
2+----------+-----------------+-------------------------------+--------------+------------------+-------------+
3| music_id | artist_name | album_name | release_date | genre_type | copies_sold |
4+----------+-----------------+-------------------------------+--------------+------------------+-------------+
5| 1 | Michael Jackson | Thriller | 1982-11-30 | Pop | 49.2 |
6| 2 | Eagles | Hotel California | 1976-12-08 | Soft Rock | 31.5 |
7| 3 | Pink Floyd | The Dark Side of the Moon | 1973-03-01 | Progressive Rock | 21.7 |
8| 4 | Shania Twain | Come On Over | 1997-11-04 | Country | 29.6 |
9| 5 | AC/DC | Back in Black | 1980-07-25 | Hard Rock | 29.5 |
10| 6 | Whitney Houston | The Bodyguard | 1992-11-25 | R&B | 32.4 |
11| 7 | Fleetwood Mac | Rumours | 1977-02-04 | Soft Rock | 27.9 |
12| 8 | Meat Loaf | Bat Out of Hell | 1977-10-11 | Hard Rock | 21.7 |
13| 9 | Eagles | Their Greatest Hits 1971-1975 | 1976-02-17 | Country Rock | 41.2 |
14| 10 | Bee Gees | Saturday Night Fever | 1977-11-15 | Disco | 21.6 |
15+----------+-----------------+-------------------------------+--------------+------------------+-------------+
1610 rows in set (0.00 sec)
自从卡罗尔阿姨出生于1957年以来,她在她年轻时享受了七十年代和八十年代的许多歌曲,你知道她是一个巨大的流行,软摇滚和迪斯科的粉丝,所以你想把它们列为你的首要任务。
您可以通过使用CASE表达式来为这些特定类型设置High Priority条件,通过在genre_type列下查询这些数据值。 以下查询会这样做,并为由CASE表达式创建的结果列创建一个代名词,命名为priority。 此查询还包括artist_name,album_name和release_date以获得更多背景。
1SELECT artist_name, album_name, release_date,
2CASE WHEN genre_type = 'Pop' THEN 'High Priority'
3WHEN genre_type = 'Soft Rock' THEN 'High Priority'
4WHEN genre_type = 'Disco' THEN 'High Priority'
5END AS priority
6FROM top_albums;
1[secondary_label Output]
2+-----------------+-------------------------------+--------------+---------------+
3| artist_name | album_name | release_date | priority |
4+-----------------+-------------------------------+--------------+---------------+
5| Michael Jackson | Thriller | 1982-11-30 | High Priority |
6| Eagles | Hotel California | 1976-12-08 | High Priority |
7| Pink Floyd | The Dark Side of the Moon | 1973-03-01 | NULL |
8| Shania Twain | Come On Over | 1997-11-04 | NULL |
9| AC/DC | Back in Black | 1980-07-25 | NULL |
10| Whitney Houston | The Bodyguard | 1992-11-25 | NULL |
11| Fleetwood Mac | Rumours | 1977-02-04 | High Priority |
12| Meat Loaf | Bat Out of Hell | 1977-10-11 | NULL |
13| Eagles | Their Greatest Hits 1971-1975 | 1976-02-17 | NULL |
14| Bee Gees | Saturday Night Fever | 1977-11-15 | High Priority |
15+-----------------+-------------------------------+--------------+---------------+
1610 rows in set (0.00 sec)
即使此输出反映了您为这些高优先级类型设置的条件,因为您错过了ELSE关键字,这会导致未知或缺少的数据值被称为NULL值。
对于下一个查询,键入相同的CASE表达式,但这一次设置一个条件与ELSE关键字. 在下面的示例中,ELSE参数将genre_type的任何非高优先级数据值标记为Maybe:
1SELECT artist_name, album_name, release_date,
2CASE WHEN genre_type = 'Pop' THEN 'High Priority'
3WHEN genre_type = 'Soft Rock' THEN 'High Priority'
4WHEN genre_type = 'Disco' THEN 'High Priority'
5ELSE 'Maybe'
6END AS priority
7FROM top_albums;
1[sceondary_label Output]
2+-----------------+-------------------------------+--------------+---------------+
3| artist_name | album_name | release_date | priority |
4+-----------------+-------------------------------+--------------+---------------+
5| Michael Jackson | Thriller | 1982-11-30 | High Priority |
6| Eagles | Hotel California | 1976-12-08 | High Priority |
7| Pink Floyd | The Dark Side of the Moon | 1973-03-01 | Maybe |
8| Shania Twain | Come On Over | 1997-11-04 | Maybe |
9| AC/DC | Back in Black | 1980-07-25 | Maybe |
10| Whitney Houston | The Bodyguard | 1992-11-25 | Maybe |
11| Fleetwood Mac | Rumours | 1977-02-04 | High Priority |
12| Meat Loaf | Bat Out of Hell | 1977-10-11 | Maybe |
13| Eagles | Their Greatest Hits 1971-1975 | 1976-02-17 | Maybe |
14| Bee Gees | Saturday Night Fever | 1977-11-15 | High Priority |
15+-----------------+-------------------------------+--------------+---------------+
1610 rows in set (0.00 sec)
虽然这有助于优先考虑前四张专辑(‘Thriller’、‘Hotel California’、‘Rumours’和‘Saturday Night Fever’),但你也必须说服卡罗尔姨妈这样做。
你决定进行一个小实验,并要求卡罗尔姨妈扩大她的音乐范围,并聆听剩余的专辑. 你不提供任何背景的专辑,并指示她以真诚的分数为Mellow,Fun,或Boring。一旦她完成,她给你一个手写的名单与她的成绩。
1SELECT artist_name, album_name, release_date,
2CASE WHEN genre_type = 'Hard Rock' THEN 'Boring'
3WHEN genre_type = 'Country Rock' THEN 'Mellow'
4WHEN genre_type = 'Progressive Rock' THEN 'Fun'
5WHEN genre_type = 'Country' THEN 'Fun'
6WHEN genre_type = 'R&B' THEN 'Boring'
7ELSE 'High Priority'
8END AS score
9FROM top_albums;
1[secondary_label Output]
2
3+-----------------+-------------------------------+--------------+---------------+
4| artist_name | album_name | release_date | score |
5+-----------------+-------------------------------+--------------+---------------+
6| Michael Jackson | Thriller | 1982-11-30 | High Priority |
7| Eagles | Hotel California | 1976-12-08 | High Priority |
8| Pink Floyd | The Dark Side of the Moon | 1973-03-01 | Fun |
9| Shania Twain | Come On Over | 1997-11-04 | Fun |
10| AC/DC | Back in Black | 1980-07-25 | Boring |
11| Whitney Houston | The Bodyguard | 1992-11-25 | Boring |
12| Fleetwood Mac | Rumours | 1977-02-04 | High Priority |
13| Meat Loaf | Bat Out of Hell | 1977-10-11 | Boring |
14| Eagles | Their Greatest Hits 1971-1975 | 1976-02-17 | Mellow |
15| Bee Gees | Saturday Night Fever | 1977-11-15 | High Priority |
16+-----------------+-------------------------------+--------------+---------------+
1710 rows in set (0.00 sec)
基于这个输出,卡罗尔姨妈似乎对新的声音开放,你对她对Pink Floyd的成绩感到惊讶,但你对她对AC/DC、Meeat Loaf和惠特尼休斯顿的优秀曲目缺乏兴趣感到失望。
卡罗尔姨妈可能会更灵活,如果你可以向她展示一些专辑的客观上比其他人更受欢迎,所以你决定输入一些数字来决定。事实是,这些专辑是十大专辑,因为他们在几十年来向粉丝出售了数百万份。
您将使用CASE表达式来设置销售至少3500万张专辑为最佳的条件,有2500万张专辑为伟大,有2000万张专辑为好的条件,以及以下示例中所示的中等的条件:
1SELECT artist_name, album_name, release_date, CASE WHEN copies_sold >35.0 THEN 'best'
2WHEN copies_sold >25.0 THEN 'great'
3WHEN copies_sold >20.0 THEN 'good'
4ELSE 'mediocre' END AS score FROM top_albums;
1[secondary_label Output]
2+-----------------+-------------------------------+--------------+-------+
3| artist_name | album_name | release_date | score |
4+-----------------+-------------------------------+--------------+-------+
5| Michael Jackson | Thriller | 1982-11-30 | best |
6| Eagles | Hotel California | 1976-12-08 | great |
7| Pink Floyd | The Dark Side of the Moon | 1973-03-01 | good |
8| Shania Twain | Come On Over | 1997-11-04 | great |
9| AC/DC | Back in Black | 1980-07-25 | great |
10| Whitney Houston | The Bodyguard | 1992-11-25 | great |
11| Fleetwood Mac | Rumours | 1977-02-04 | great |
12| Meat Loaf | Bat Out of Hell | 1977-10-11 | good |
13| Eagles | Their Greatest Hits 1971-1975 | 1976-02-17 | best |
14| Bee Gees | Saturday Night Fever | 1977-11-15 | good |
15+-----------------+-------------------------------+--------------+-------+
1610 rows in set (0.00 sec)
根据这一结果,没有一张专辑被评为中等级,因为他们每张都卖出了超过2000万张专辑,然而,有一些专辑根据得分而脱颖而出,现在你可以提供AC/DC或惠特尼休斯顿演奏的坚实证据,因为他们的专辑卖出了超过2500万张专辑,使它们成为了最伟大的音乐作品。
现在您了解如何使用CASE表达式为各种目的和字符和数字数据值设置条件,以及如何使用If-then逻辑来比较这些值,并根据您想要的条件生成响应。
结论
了解如何使用CASE表达式可以帮助将数据缩小到您设置的任何条件,无论您想要为某些值设定不同的优先级,还是根据常见观点或数字的标准评分它们,它对您的需求具有灵活性。