⭐️⭐️⭐️⭐️⭐️完整数据详见 练习数据·免费⭐️⭐️⭐️⭐️⭐️六十二、格式化会员姓名问题输出所有会员的姓名格式为姓, 名预期结果nameGUEST, GUESTSmith, DarrenSmith, TracyRownam, TimJoplette, JaniceButters, GeraldTracy, BurtonDare, NancyBoothe, TimStibbons, PonderOwen, CharlesJones, DavidBaker, AnneFarrell, JemimaSmith, JackBader, FlorenceBaker, TimothyPinker, DavidGenting, MatthewMackenzie, AnnaCoplin, JoanSarwin, RamnareshJones, DouglasRumney, HenriettaFarrell, DavidWorthington-Smyth, HenryPurview, MillicentTupperware, HyacinthHunt, JohnCrumpet, EricaSmith, Darren[答案与讨论]select surname || , || firstname as name from cd.members在 SQL 中构建字符串与其他语言类似只是连接运算符有所不同||。有些系统如 SQL Server使用 但 || 才是 SQL 标准。六十三、按名称前缀查找设施问题查找所有名称以Tennis开头的设施。检索所有列。预期结果facidnamemembercostguestcostinitialoutlaymonthlymaintenance0Tennis Court 1525100002001Tennis Court 25258000200[答案与讨论]select * from cd.facilities where name like Tennis%;SQL 的 LIKE 运算符是一种使用基本匹配来搜索字符串的标准方法。% 字符匹配任意字符串而 _ 匹配任意单个字符。使用 LIKE 时需要注意的一点是它如何使用索引。如果你使用的是C区域设置任何具有固定开头的 LIKE 字符串如本例所示都可以使用索引。如果使用其他区域设置LIKE 默认不会使用任何索引。详见此处了解如何更改这一行为。六十四、执行不区分大小写的搜索问题执行不区分大小写的搜索查找所有名称以tennis开头的设施。检索所有列。预期结果facidnamemembercostguestcostinitialoutlaymonthlymaintenance0Tennis Court 1525100002001Tennis Court 25258000200[答案与讨论]select * from cd.facilities where upper(name) like TENNIS%;标准 SQL 中没有直接用于不区分大小写比较的运算符。幸运的是我们可以借鉴许多其他语言的做法在比较时将所有值强制转换为大写。这样大小写就不再重要从而得到我们想要的结果。另外Postgres 还提供了 ILIKE 运算符用于执行不区分大小写的搜索。虽然这不是标准 SQL但可以说更加清晰明了。需要注意的是对列值运行 UPPER 这样的函数会阻止 Postgres 使用该列上的任何索引ILIKE 也是如此。幸运的是Postgres 有解决办法除了简单地在列上创建索引外你还可以在表达式上创建索引。如果你在 UPPER(name) 上创建了索引这个查询就可以很好地利用它。六十五、查找包含括号的电话号码问题你注意到俱乐部的会员表中的电话号码格式非常不统一。你想找出所有包含括号的电话号码返回会员 ID 和电话号码并按会员 ID 排序。预期结果memidtelephone0(000) 000-00003(844) 693-07234(833) 942-47105(844) 078-41306(822) 354-99737(833) 776-40018(811) 433-25479(833) 160-390010(855) 542-525111(844) 536-803613(855) 016-016314(822) 163-325415(833) 499-352720(811) 972-137721(822) 661-289822(822) 499-223224(822) 413-147027(822) 989-887628(855) 755-987629(855) 894-375830(855) 941-978633(822) 665-532735(899) 720-697836(811) 732-481637(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 位。按新的邮政编码排序。预期结果zip00000002340023404321043211038311986234232856333862342324353243533456785236554333567545739258393645776533265464667966866669302756557853380743849238763097676[答案与讨论]select lpad(cast(zipcode as char(5)),5,0) zip from cd.members order by zipPostgres 的 LPAD 函数是本次的主角。它的功能正如你所料让我们生成一个填充后的字符串。我们需要记得将邮政编码转换为字符串类型以便 LPAD 函数能够接受它。在接手旧数据库时发现数据类型方面做出了一些奇怪的决定并不罕见。你可能想修复这类错误但如果更改数据类型会导致大量代码出错。在这种情况下一种可行的方案取决于性能要求是在表上创建一个视图以修正后的方式呈现数据然后逐步迁移。六十七、统计姓氏以字母表中每个字母开头的会员数量问题你想统计姓氏以字母表中每个字母开头的会员数量。按字母排序如果某个字母对应的数量为 0无需显示该字母。预期结果lettercountB5C2D1F2G2H1J3M1O1P2R2S6T2W1[答案与讨论]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 排序。预期结果memidtelephone00000000000155555555552555555555538446930723483394247105844078413068223549973783377640018811433254798331603900108555425251118445368036128440765141138550160163148221633254158334993527168339410824178114096734208119721377218226612898228224992232248224131470268445368036278229898876288557559876298558943758308559419786338226655327358997206978368117324816378225773541[答案与讨论]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 约束来实现这一点它可以拒绝任何格式不佳的条目。在应用层执行此类验证很有诱惑力这当然也是一种有效的方法。一般来说如果你的数据库被多个应用程序使用建议将更多的检查下推到数据库中以确保各应用之间的行为一致。偶尔添加约束并不可行。例如你可能有两个不同的遗留应用程序它们以不同的格式提交信息。如果你无法修改这些应用程序有几个方案可以考虑。首先你可以在表上定义一个触发器。这允许你在数据插入表之前或之后拦截它并将其规范化为单一格式。另外你也可以在表上构建一个视图在读取数据时即时清理信息。较新的应用程序可以从视图中读取从而受益于更可靠格式的信息。
Postgresql基础实践教程（七）
⭐️⭐️⭐️⭐️⭐️完整数据详见 练习数据·免费⭐️⭐️⭐️⭐️⭐️六十二、格式化会员姓名问题输出所有会员的姓名格式为姓, 名预期结果nameGUEST, GUESTSmith, DarrenSmith, TracyRownam, TimJoplette, JaniceButters, GeraldTracy, BurtonDare, NancyBoothe, TimStibbons, PonderOwen, CharlesJones, DavidBaker, AnneFarrell, JemimaSmith, JackBader, FlorenceBaker, TimothyPinker, DavidGenting, MatthewMackenzie, AnnaCoplin, JoanSarwin, RamnareshJones, DouglasRumney, HenriettaFarrell, DavidWorthington-Smyth, HenryPurview, MillicentTupperware, HyacinthHunt, JohnCrumpet, EricaSmith, Darren[答案与讨论]select surname || , || firstname as name from cd.members在 SQL 中构建字符串与其他语言类似只是连接运算符有所不同||。有些系统如 SQL Server使用 但 || 才是 SQL 标准。六十三、按名称前缀查找设施问题查找所有名称以Tennis开头的设施。检索所有列。预期结果facidnamemembercostguestcostinitialoutlaymonthlymaintenance0Tennis Court 1525100002001Tennis Court 25258000200[答案与讨论]select * from cd.facilities where name like Tennis%;SQL 的 LIKE 运算符是一种使用基本匹配来搜索字符串的标准方法。% 字符匹配任意字符串而 _ 匹配任意单个字符。使用 LIKE 时需要注意的一点是它如何使用索引。如果你使用的是C区域设置任何具有固定开头的 LIKE 字符串如本例所示都可以使用索引。如果使用其他区域设置LIKE 默认不会使用任何索引。详见此处了解如何更改这一行为。六十四、执行不区分大小写的搜索问题执行不区分大小写的搜索查找所有名称以tennis开头的设施。检索所有列。预期结果facidnamemembercostguestcostinitialoutlaymonthlymaintenance0Tennis Court 1525100002001Tennis Court 25258000200[答案与讨论]select * from cd.facilities where upper(name) like TENNIS%;标准 SQL 中没有直接用于不区分大小写比较的运算符。幸运的是我们可以借鉴许多其他语言的做法在比较时将所有值强制转换为大写。这样大小写就不再重要从而得到我们想要的结果。另外Postgres 还提供了 ILIKE 运算符用于执行不区分大小写的搜索。虽然这不是标准 SQL但可以说更加清晰明了。需要注意的是对列值运行 UPPER 这样的函数会阻止 Postgres 使用该列上的任何索引ILIKE 也是如此。幸运的是Postgres 有解决办法除了简单地在列上创建索引外你还可以在表达式上创建索引。如果你在 UPPER(name) 上创建了索引这个查询就可以很好地利用它。六十五、查找包含括号的电话号码问题你注意到俱乐部的会员表中的电话号码格式非常不统一。你想找出所有包含括号的电话号码返回会员 ID 和电话号码并按会员 ID 排序。预期结果memidtelephone0(000) 000-00003(844) 693-07234(833) 942-47105(844) 078-41306(822) 354-99737(833) 776-40018(811) 433-25479(833) 160-390010(855) 542-525111(844) 536-803613(855) 016-016314(822) 163-325415(833) 499-352720(811) 972-137721(822) 661-289822(822) 499-223224(822) 413-147027(822) 989-887628(855) 755-987629(855) 894-375830(855) 941-978633(822) 665-532735(899) 720-697836(811) 732-481637(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 位。按新的邮政编码排序。预期结果zip00000002340023404321043211038311986234232856333862342324353243533456785236554333567545739258393645776533265464667966866669302756557853380743849238763097676[答案与讨论]select lpad(cast(zipcode as char(5)),5,0) zip from cd.members order by zipPostgres 的 LPAD 函数是本次的主角。它的功能正如你所料让我们生成一个填充后的字符串。我们需要记得将邮政编码转换为字符串类型以便 LPAD 函数能够接受它。在接手旧数据库时发现数据类型方面做出了一些奇怪的决定并不罕见。你可能想修复这类错误但如果更改数据类型会导致大量代码出错。在这种情况下一种可行的方案取决于性能要求是在表上创建一个视图以修正后的方式呈现数据然后逐步迁移。六十七、统计姓氏以字母表中每个字母开头的会员数量问题你想统计姓氏以字母表中每个字母开头的会员数量。按字母排序如果某个字母对应的数量为 0无需显示该字母。预期结果lettercountB5C2D1F2G2H1J3M1O1P2R2S6T2W1[答案与讨论]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 排序。预期结果memidtelephone00000000000155555555552555555555538446930723483394247105844078413068223549973783377640018811433254798331603900108555425251118445368036128440765141138550160163148221633254158334993527168339410824178114096734208119721377218226612898228224992232248224131470268445368036278229898876288557559876298558943758308559419786338226655327358997206978368117324816378225773541[答案与讨论]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 约束来实现这一点它可以拒绝任何格式不佳的条目。在应用层执行此类验证很有诱惑力这当然也是一种有效的方法。一般来说如果你的数据库被多个应用程序使用建议将更多的检查下推到数据库中以确保各应用之间的行为一致。偶尔添加约束并不可行。例如你可能有两个不同的遗留应用程序它们以不同的格式提交信息。如果你无法修改这些应用程序有几个方案可以考虑。首先你可以在表上定义一个触发器。这允许你在数据插入表之前或之后拦截它并将其规范化为单一格式。另外你也可以在表上构建一个视图在读取数据时即时清理信息。较新的应用程序可以从视图中读取从而受益于更可靠格式的信息。
