⭐️⭐️⭐️⭐️⭐️
完整数据详见 练习数据·免费
⭐️⭐️⭐️⭐️⭐️
六十二、格式化会员姓名
问题
输出所有会员的姓名，格式为"姓, 名"
预期结果
| name |
|---|
| GUEST, GUEST |
| Smith, Darren |
| Smith, Tracy |
| Rownam, Tim |
| Joplette, Janice |
| Butters, Gerald |
| Tracy, Burton |
| Dare, Nancy |
| Boothe, Tim |
| Stibbons, Ponder |
| Owen, Charles |
| Jones, David |
| Baker, Anne |
| Farrell, Jemima |
| Smith, Jack |
| Bader, Florence |
| Baker, Timothy |
| Pinker, David |
| Genting, Matthew |
| Mackenzie, Anna |
| Coplin, Joan |
| Sarwin, Ramnaresh |
| Jones, Douglas |
| Rumney, Henrietta |
| Farrell, David |
| Worthington-Smyth, Henry |
| Purview, Millicent |
| Tupperware, Hyacinth |
| Hunt, John |
| Crumpet, Erica |
| Smith, Darren |
[答案与讨论]
select surname || ', ' || firstname as name from cd.members
在 SQL 中构建字符串与其他语言类似，只是连接运算符有所不同：||。有些系统（如 SQL Server）使用 +，但 || 才是 SQL 标准。
六十三、按名称前缀查找设施
问题
查找所有名称以"Tennis"开头的设施。检索所有列。
预期结果
| facid | name | membercost | guestcost | initialoutlay | monthlymaintenance |
|---|---|---|---|---|---|
| 0 | Tennis Court 1 | 5 | 25 | 10000 | 200 |
| 1 | Tennis Court 2 | 5 | 25 | 8000 | 200 |
[答案与讨论]
select * from cd.facilities where name like 'Tennis%';
SQL 的 LIKE 运算符是一种使用基本匹配来搜索字符串的标准方法。% 字符匹配任意字符串，而 _ 匹配任意单个字符。
使用 LIKE 时需要注意的一点是它如何使用索引。如果你使用的是"C"区域设置，任何具有固定开头的 LIKE 字符串（如本例所示）都可以使用索引。如果使用其他区域设置，LIKE 默认不会使用任何索引。详见此处了解如何更改这一行为。
六十四、执行不区分大小写的搜索
问题
执行不区分大小写的搜索，查找所有名称以"tennis"开头的设施。检索所有列。
预期结果
| facid | name | membercost | guestcost | initialoutlay | monthlymaintenance |
|---|---|---|---|---|---|
| 0 | Tennis Court 1 | 5 | 25 | 10000 | 200 |
| 1 | Tennis Court 2 | 5 | 25 | 8000 | 200 |
[答案与讨论]
select * from cd.facilities where upper(name) like 'TENNIS%';
标准 SQL 中没有直接用于不区分大小写比较的运算符。幸运的是，我们可以借鉴许多其他语言的做法，在比较时将所有值强制转换为大写。这样大小写就不再重要，从而得到我们想要的结果。
另外，Postgres 还提供了 ILIKE 运算符，用于执行不区分大小写的搜索。虽然这不是标准 SQL，但可以说更加清晰明了。
需要注意的是，对列值运行 UPPER 这样的函数会阻止 Postgres 使用该列上的任何索引（ILIKE 也是如此）。幸运的是，Postgres 有解决办法：除了简单地在列上创建索引外，你还可以在表达式上创建索引。如果你在 UPPER(name) 上创建了索引，这个查询就可以很好地利用它。
六十五、查找包含括号的电话号码
问题
你注意到俱乐部的会员表中的电话号码格式非常不统一。你想找出所有包含括号的电话号码，返回会员 ID 和电话号码，并按会员 ID 排序。
预期结果
| memid | telephone |
|---|---|
| 0 | (000) 000-0000 |
| 3 | (844) 693-0723 |
| 4 | (833) 942-4710 |
| 5 | (844) 078-4130 |
| 6 | (822) 354-9973 |
| 7 | (833) 776-4001 |
| 8 | (811) 433-2547 |
| 9 | (833) 160-3900 |
| 10 | (855) 542-5251 |
| 11 | (844) 536-8036 |
| 13 | (855) 016-0163 |
| 14 | (822) 163-3254 |
| 15 | (833) 499-3527 |
| 20 | (811) 972-1377 |
| 21 | (822) 661-2898 |
| 22 | (822) 499-2232 |
| 24 | (822) 413-1470 |
| 27 | (822) 989-8876 |
| 28 | (855) 755-9876 |
| 29 | (855) 894-3758 |
| 30 | (855) 941-9786 |
| 33 | (822) 665-5327 |
| 35 | (899) 720-6978 |
| 36 | (811) 732-4816 |
| 37 | (822) 577-3541 |
[答案与讨论]
select memid, telephone from cd.members where telephone ~ '[()]';
我们选择使用正则表达式来解答这个问题，虽然 Postgres 也提供了其他字符串函数（如 POSITION），它们同样能胜任这项工作。Postgres 通过 ~ 运算符实现 POSIX 正则表达式匹配。如果你之前用过正则表达式，对这个运算符的功能会很熟悉。
作为替代方案，你可以使用 SQL 标准的 SIMILAR TO 运算符。它的正则表达式与 POSIX 标准有相似之处，但也存在很多差异。一些最显著的差异包括：
- 与 LIKE 运算符一样，SIMILAR TO 使用 ‘_’ 字符表示"任意字符"，使用 ‘%’ 字符表示"任意字符串"。
- SIMILAR TO 表达式必须匹配整个字符串，而不像 POSIX 正则表达式那样只匹配子串。这意味着你通常需要在表达式两端加上 ‘%’ 字符。
- 在 SIMILAR TO 正则表达式中，‘.’ 字符并不表示"任意字符"，它只是一个普通字符。
下面给出的是使用 SIMILAR TO 的等效写法：
select memid, telephone from cd.members where telephone similar to '%[()]%';
最后需要注意的是，正则表达式通常不会使用索引。一般来说，你不希望让正则表达式承担查询中的繁重工作，因为它会比较慢。如果你需要快速的模糊匹配，可以考虑评估一下全文搜索是否能满足你的需求。
六十六、用前导零填充邮政编码
问题
我们的示例数据集中的邮政编码由于存储为数值类型，前导零被移除了。从会员表中检索所有邮政编码，将任何少于 5 个字符的邮政编码用前导零填充到 5 位。按新的邮政编码排序。
预期结果
| zip |
|---|
| 00000 |
| 00234 |
| 00234 |
| 04321 |
| 04321 |
| 10383 |
| 11986 |
| 23423 |
| 28563 |
| 33862 |
| 34232 |
| 43532 |
| 43533 |
| 45678 |
| 52365 |
| 54333 |
| 56754 |
| 57392 |
| 58393 |
| 64577 |
| 65332 |
| 65464 |
| 66796 |
| 68666 |
| 69302 |
| 75655 |
| 78533 |
| 80743 |
| 84923 |
| 87630 |
| 97676 |
[答案与讨论]
select lpad(cast(zipcode as char(5)),5,'0') zip from cd.members order by zip
Postgres 的 LPAD 函数是本次的主角。它的功能正如你所料：让我们生成一个填充后的字符串。我们需要记得将邮政编码转换为字符串类型，以便 LPAD 函数能够接受它。
在接手旧数据库时，发现数据类型方面做出了一些奇怪的决定并不罕见。你可能想修复这类错误，但如果更改数据类型会导致大量代码出错。在这种情况下，一种可行的方案（取决于性能要求）是在表上创建一个视图，以修正后的方式呈现数据，然后逐步迁移。
六十七、统计姓氏以字母表中每个字母开头的会员数量
问题
你想统计姓氏以字母表中每个字母开头的会员数量。按字母排序，如果某个字母对应的数量为 0，无需显示该字母。
预期结果
| letter | count |
|---|---|
| B | 5 |
| C | 2 |
| D | 1 |
| F | 2 |
| G | 2 |
| H | 1 |
| J | 3 |
| M | 1 |
| O | 1 |
| P | 2 |
| R | 2 |
| S | 6 |
| T | 2 |
| W | 1 |
[答案与讨论]
select substr (mems.surname,1,1) as letter, count(*) as count
from cd.members mems
group by letter
order by letter
这个练习相当直接。你只需要提取会员姓氏的第一个字母，然后进行基本的聚合统计即可。这里我们使用了 SUBSTR 函数，但其实还有很多其他方法可以实现同样的效果。例如，LEFT 函数可以返回字符串左侧的前 n 个字符。另外，你也可以使用 SUBSTRING 函数，它允许你使用正则表达式来提取字符串的一部分。
值得注意的是：正如你所见，SQL 中的字符串函数是基于 1 的索引，而不是你可能习惯的基于 0 的索引。在适应之前，这可能会让你犯一两次错误 😃
六十八、清理电话号码
问题
数据库中的电话号码格式非常不统一。你想打印一份会员 ID 和电话号码的列表，其中已移除 ‘-’、‘(’、‘)’ 和 ’ ’ 字符。按会员 ID 排序。
预期结果
| memid | telephone |
|---|---|
| 0 | 0000000000 |
| 1 | 5555555555 |
| 2 | 5555555555 |
| 3 | 8446930723 |
| 4 | 8339424710 |
| 5 | 8440784130 |
| 6 | 8223549973 |
| 7 | 8337764001 |
| 8 | 8114332547 |
| 9 | 8331603900 |
| 10 | 8555425251 |
| 11 | 8445368036 |
| 12 | 8440765141 |
| 13 | 8550160163 |
| 14 | 8221633254 |
| 15 | 8334993527 |
| 16 | 8339410824 |
| 17 | 8114096734 |
| 20 | 8119721377 |
| 21 | 8226612898 |
| 22 | 8224992232 |
| 24 | 8224131470 |
| 26 | 8445368036 |
| 27 | 8229898876 |
| 28 | 8557559876 |
| 29 | 8558943758 |
| 30 | 8559419786 |
| 33 | 8226655327 |
| 35 | 8997206978 |
| 36 | 8117324816 |
| 37 | 8225773541 |
[答案与讨论]
select memid, translate(telephone, '-() ', '') as telephone
from cd.members
order by memid;
最直接的解决方案可能是使用 TRANSLATE 函数，它可以用来替换字符串中的字符。你需要传递三个参数：要修改的值、要替换的字符，以及用于替换的字符。在我们的例子中，我们希望删除所有指定的字符，因此第三个参数是空字符串。
与处理字符串时常见的做法一样，我们也可以使用正则表达式来解决这个问题。REGEXP_REPLACE 函数提供了我们所需的功能：我们只需传入一个匹配所有非数字字符的正则表达式，并将它们替换为空，如下所示。‘g’ 标志告诉函数尽可能多地替换所有匹配的模式。这种解决方案可能更加健壮，因为它能清除更多不良格式。
select memid, regexp_replace(telephone, '[^0-9]', '', 'g') as telephone
from cd.members
order by memid;
自动化使用自由格式的文本数据可能会很麻烦。理想情况下，你希望避免不断编写代码来清理数据，因此应该考虑让数据库为你强制执行正确的格式。你可以在列上使用 CHECK 约束来实现这一点，它可以拒绝任何格式不佳的条目。在应用层执行此类验证很有诱惑力，这当然也是一种有效的方法。一般来说，如果你的数据库被多个应用程序使用，建议将更多的检查下推到数据库中，以确保各应用之间的行为一致。
偶尔，添加约束并不可行。例如，你可能有两个不同的遗留应用程序，它们以不同的格式提交信息。如果你无法修改这些应用程序，有几个方案可以考虑。首先，你可以在表上定义一个触发器。这允许你在数据插入表之前（或之后）拦截它，并将其规范化为单一格式。另外，你也可以在表上构建一个视图，在读取数据时即时清理信息。较新的应用程序可以从视图中读取，从而受益于更可靠格式的信息。
1403
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
