Postgresql基础实践教程（七）

⭐️⭐️⭐️⭐️⭐️
完整数据详见 练习数据·免费
⭐️⭐️⭐️⭐️⭐️

六十二、格式化会员姓名

问题

输出所有会员的姓名，格式为"姓, 名"

预期结果

[答案与讨论]

select surname || ', ' || firstname as name from cd.members

在 SQL 中构建字符串与其他语言类似，只是连接运算符有所不同：||。有些系统（如 SQL Server）使用 +，但 || 才是 SQL 标准。

六十三、按名称前缀查找设施

问题

查找所有名称以"Tennis"开头的设施。检索所有列。

预期结果

[答案与讨论]

select * from cd.facilities where name like 'Tennis%';

SQL 的 LIKE 运算符是一种使用基本匹配来搜索字符串的标准方法。% 字符匹配任意字符串，而 _ 匹配任意单个字符。

使用 LIKE 时需要注意的一点是它如何使用索引。如果你使用的是"C"区域设置，任何具有固定开头的 LIKE 字符串（如本例所示）都可以使用索引。如果使用其他区域设置，LIKE 默认不会使用任何索引。详见此处了解如何更改这一行为。

六十四、执行不区分大小写的搜索

问题

执行不区分大小写的搜索，查找所有名称以"tennis"开头的设施。检索所有列。

预期结果

[答案与讨论]

select * from cd.facilities where upper(name) like 'TENNIS%';

标准 SQL 中没有直接用于不区分大小写比较的运算符。幸运的是，我们可以借鉴许多其他语言的做法，在比较时将所有值强制转换为大写。这样大小写就不再重要，从而得到我们想要的结果。

另外，Postgres 还提供了 ILIKE 运算符，用于执行不区分大小写的搜索。虽然这不是标准 SQL，但可以说更加清晰明了。

需要注意的是，对列值运行 UPPER 这样的函数会阻止 Postgres 使用该列上的任何索引（ILIKE 也是如此）。幸运的是，Postgres 有解决办法：除了简单地在列上创建索引外，你还可以在表达式上创建索引。如果你在 UPPER(name) 上创建了索引，这个查询就可以很好地利用它。

六十五、查找包含括号的电话号码

问题

你注意到俱乐部的会员表中的电话号码格式非常不统一。你想找出所有包含括号的电话号码，返回会员 ID 和电话号码，并按会员 ID 排序。

预期结果

[答案与讨论]

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 位。按新的邮政编码排序。

预期结果

[答案与讨论]

select lpad(cast(zipcode as char(5)),5,'0') zip from cd.members order by zip

Postgres 的 LPAD 函数是本次的主角。它的功能正如你所料：让我们生成一个填充后的字符串。我们需要记得将邮政编码转换为字符串类型，以便 LPAD 函数能够接受它。

在接手旧数据库时，发现数据类型方面做出了一些奇怪的决定并不罕见。你可能想修复这类错误，但如果更改数据类型会导致大量代码出错。在这种情况下，一种可行的方案（取决于性能要求）是在表上创建一个视图，以修正后的方式呈现数据，然后逐步迁移。

六十七、统计姓氏以字母表中每个字母开头的会员数量

问题

你想统计姓氏以字母表中每个字母开头的会员数量。按字母排序，如果某个字母对应的数量为 0，无需显示该字母。

预期结果

[答案与讨论]

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 排序。

预期结果

[答案与讨论]

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 约束来实现这一点，它可以拒绝任何格式不佳的条目。在应用层执行此类验证很有诱惑力，这当然也是一种有效的方法。一般来说，如果你的数据库被多个应用程序使用，建议将更多的检查下推到数据库中，以确保各应用之间的行为一致。

偶尔，添加约束并不可行。例如，你可能有两个不同的遗留应用程序，它们以不同的格式提交信息。如果你无法修改这些应用程序，有几个方案可以考虑。首先，你可以在表上定义一个触发器。这允许你在数据插入表之前（或之后）拦截它，并将其规范化为单一格式。另外，你也可以在表上构建一个视图，在读取数据时即时清理信息。较新的应用程序可以从视图中读取，从而受益于更可靠格式的信息。