PostgreSQL 基础知识在线练习

PGExercises.com 是一个非常不错的 PostgreSQL 在线实践网站。该网站基于一个简单的数据集，设立各类题目，我们可以通过回答这些问题来复习 SQL 知识。

该站的题目涉及“简单查询及 WHERE 条件”，“连接及 CASE 语句”，“聚集函数，窗口函数及递归查询”等多个门类，是一个不错的测试所学知识的地方。

下面简单介绍一下该站用到的数据集。

该数据集针对的是一个刚成立的乡村俱乐部的：有一组会员，一组体育设施，及这些体育设施的预定记录。

先看一下members表：

有 ID，基础信息，推荐人 ID，及加入时间等。

CREATE TABLE cd.members (
    memid INTEGER NOT NULL,                     -- 会员ID
    surname CHARACTER VARYING(200) NOT NULL,    -- 姓
    firstname CHARACTER VARYING(200) NOT NULL,  -- 名
    address CHARACTER VARYING(300) NOT NULL,    -- 地址
    zipcode INTEGER NOT NULL,                   -- 邮政编码
    telephone CHARACTER VARYING(20) NOT NULL,   -- 电话
    recommendedby INTEGER,                      -- 推荐人
    joindate TIMESTAMP NOT NULL,                -- 加入时间
    CONSTRAINT members_pk PRIMARY KEY (memid),
    CONSTRAINT fk_members_recommendedby FOREIGN KEY (recommendedby)
        REFERENCES cd.members(memid) ON DELETE SET NULL
);

接下来，看一下facilities表：

该表列出可供预定的设施，包含设施 ID，设施名称，会员预定花销，游客预定花销等。

CREATE TABLE cd.facilities (
    facid integer NOT NULL,                 -- 设施ID
    name character varying(100) NOT NULL,   -- 设施名称
    membercost numeric NOT NULL,            -- 会员预定花销
    guestcost numeric NOT NULL,             -- 游客预定花销
    initialoutlay numeric NOT NULL,
    monthlymaintenance numeric NOT NULL,
    CONSTRAINT facilities_pk PRIMARY KEY (facid)
);

最后，看一下bookings表：

该表用于追踪各设施的预定情况，包含设施 ID，预定会员 ID，开始预定时间，及预定了多少个半小时的 slots 等。

CREATE TABLE cd.bookings (
    bookid integer NOT NULL,
    facid integer NOT NULL,        -- 设施ID
    memid integer NOT NULL,        -- 会员ID
    starttime timestamp NOT NULL,  -- 开始预定时间
    slots integer NOT NULL,        -- 预定了多少个半小时
    CONSTRAINT bookings_pk PRIMARY KEY (bookid),
    CONSTRAINT fk_bookings_facid FOREIGN KEY (facid) REFERENCES cd.facilities(facid),
    CONSTRAINT fk_bookings_memid FOREIGN KEY (memid) REFERENCES cd.members(memid)
);

这三张表的关系如下图所示。

介绍完数据集，下面就开始我们的练习吧。

1 简单 SQL 查询

该栏目考察 SQL 基础，题目涵盖 SELECT, WHERE, CASE, UNION 等。

1 控制取哪些行

问题描述：

生成一个设备列表，这些设备对会员收费，且所收的费用不足月度维护费用的 50 分之一。该列表返回设备的 ID，名称，会员费，月度维护费用。

问题答案：

SELECT facid, name, membercost, monthlymaintenance
FROM cd.facilities
WHERE membercost > 0
  AND membercost < monthlymaintenance/50;

2 将结果分类

问题描述：

生成一个设备列表，若月度维护费用大于 100 就标记为expensive，否则标记为cheap。返回相关设施的名称和月度维护情况。

问题答案：

SELECT name,
    CASE
        WHEN monthlymaintenance > 100
        THEN 'expensive'
        ELSE 'cheap'
    END AS cost
FROM cd.facilities;

3 日期处理

问题描述：

生成一个会员列表，返回 2012 年 9 月及之后加入的会员。返回会员的 memid，surname，firstname 及 joindate。

问题答案：

SELECT memid, surname, firstname, joindate
FROM cd.members
WHERE joindate >= '2012-09-01';

4 重复项移除及结果排序

问题描述：

生成一个排序后的前 10 位会员的姓氏列表，且不要有重复。

问题答案：

SELECT DISTINCT surname
FROM cd.members
ORDER BY surname LIMIT 10;

5 组合多个查询的结果

问题描述：

出于某种原因，您需要一个包含所有姓氏和所有设施名称的组合列表。请生成这个列表。

问题答案：

注意使用UNION会移除重复项，而UNION ALL并不会。

SELECT surname
FROM cd.members
UNION
SELECT name
FROM cd.facilities;

6 聚集函数使用

问题描述：

您想获取最后一个加入的会员的名字，姓氏，加入时间。该如何做？

问题答案：

使用子查询实现。

SELECT firstname, surname, joindate
FROM cd.members
WHERE joindate = (
        SELECT max(joindate)
        FROM cd.members);

2 连接及子查询

该栏目主要考察关系型数据库的基础——连接。题目涵盖内连接，外连接，自连接，子查询。

1 获取会员的预定开始时间

问题描述：

获取会员名字为“David Farrell”的预定开始时间。

问题答案：

有两种实现方式，一种采用内连接，一种采用子查询。内连接又有两种写法。

a）内连接实现

SELECT b.starttime
FROM cd.bookings b, cd.members m
WHERE b.memid = m.memid
  AND firstname = 'David'
  AND surname = 'Farrell';

-- 另一种写法
SELECT b.starttime
FROM cd.bookings b
  INNER JOIN cd.members m
  ON b.memid = m.memid
WHERE firstname = 'David'
  AND surname = 'Farrell';

b）子查询实现

SELECT starttime
FROM cd.bookings
WHERE memid IN (
      SELECT memid
      FROM cd.members
      WHERE firstname = 'David'
        AND surname = 'Farrell'
      );

2 获取网球场的预定开始时间

问题描述：

获取2012-09-21这一天预定“Tennis Court”的开始时间列表。返回开始时间及设备名称，按开始时间排序。

问题答案：

SELECT b.starttime, f.name
FROM cd.bookings b, cd.facilities f
WHERE b.facid = f.facid
  AND f.name LIKE 'Tennis Court%'
  AND date(b.starttime) = '2012-09-21'
ORDER BY b.starttime;

3 获取推荐过其他会员的所有会员列表

问题描述：

获取推荐过其他会员的所有会员列表，确保结果不含重复项，且结果以姓和名排序。

问题答案：

采用自连接实现，采用 DISTINCT 去重。

SELECT DISTINCT m2.firstname, m2.surname
FROM cd.members m1, cd.members m2
WHERE m1.recommendedby = m2.memid
ORDER BY m2.surname, m2.firstname;

4 获取所有会员及其推荐人

问题描述：

获取所有会员及其推荐人（如果有的话），确保结果以姓和名排序。

问题答案：

SELECT m1.firstname,
	m1.surname,
	m2.firstname,
	m2.surname
FROM cd.members m1
LEFT OUTER JOIN cd.members m2 ON m1.recommendedby = m2.memid
ORDER BY m1.surname,
	m1.firstname;

5 列出所有使用过网球场的会员

问题描述：

找出使用过网球场的所有会员的列表。输出包含网球场名，合为一列的会员姓名。确保没有重复数据，并按会员姓名后跟设施名称排序。

问题答案：

SELECT DISTINCT (m.firstname || ' ' || m.surname) AS member,
  f.name AS facility
FROM cd.bookings b,
  cd.members m,
  cd.facilities f
WHERE b.facid = f.facid
  AND b.memid = m.memid
  AND f.name LIKE 'Tennis Court%'
ORDER BY member, facility;

6 生成一份昂贵的预订清单

问题描述：

生成2012-09-14这一天会员或游客花费超过 30 元的预订清单。

注意：游客和会员的预定费用不同，且游客的 ID 始终为 0。输出中包括设施名称，会员姓名及预定费用。结果按费用降序排序，且不使用任何子查询。

问题答案：

SELECT (m.firstname || ' ' || m.surname) AS member,
  f.name AS facility,
  (CASE
    WHEN b.memid = 0
    THEN b.slots * f.guestcost
    ELSE b.slots * f.membercost END) AS cost
FROM cd.bookings b,
  cd.members m,
  cd.facilities f
WHERE b.memid = m.memid
  AND b.facid = f.facid
  AND date(b.starttime) = '2012-09-14'
  AND ((b.memid = 0 AND b.slots * f.guestcost > 30)
    OR (b.memid != 0 AND b.slots * f.membercost > 30))
ORDER BY cost DESC;

7 使用子查询生成一份昂贵的预订清单

问题描述：

对于上一个问题，实现的有点啰嗦：我们必须在 WHERE 子句和 CASE 语句中两次计算预订成本。尝试使用子查询简化此计算。

问题答案：

SELECT *
FROM (SELECT (m.firstname || ' ' || surname) AS member,
          f.name AS facility,
          (CASE
            WHEN b.memid = 0
            THEN b.slots * f.guestcost
            ELSE b.slots * f.membercost
          END) AS cost
      FROM cd.bookings b,
        cd.members m,
        cd.facilities f
      WHERE b.memid = m.memid
      AND b.facid = f.facid
      AND date(b.starttime) s '2012-09-14') AS t
WHERE t.cost > 30
ORDER BY t.cost DESC;

8 不使用连接生成所有成员及其推荐人列表

问题描述：

不使用任何连接的情况下输出所有成员的列表，包括其推荐人（如果有的话）。确保列表中没有重复项，且名字姓氏对被格式化为一列并有序。

问题答案：

SELECT DISTINCT (m.firstname || ' ' || m.surname) AS member,
  (SELECT (firstname || ' ' || surname) AS recommender
    FROM cd.members
    WHERE memid = m.recommendedby)
FROM cd.members m
ORDER BY member;

3 数据修改

本栏目涉及插入、更新和删除。像这样更改数据的操作统称为 DML（数据操作语言）。

1 单行插入

问题描述：

俱乐部正在增加一个新设施——SPA。我们需要将它添加到设施表中。值如下。

facid: 9, name: 'Spa', membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800

问题答案：

可以显示指定列名，也可以省略列名按建表字段顺序插入。

INSERT INTO cd.facilities (facid, name, membercost, guestcost, initialoutlay, monthlymaintenance)
    VALUES (9, 'Spa', 20, 30, 100000, 800);

-- 按照建表字段顺序插入
INSERT INTO cd.facilities VALUES (9, 'Spa', 20, 30, 100000, 800);

2 多行插入

问题描述：

使用一行命令一次加入多个设备。值如下。

facid: 9, name: 'Spa', membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800
facid: 10, name: 'Squash Court 2', membercost: 3.5, guestcost: 17.5, initialoutlay: 5000, monthlymaintenance: 80

问题答案：

INSERT INTO cd.facilities (facid, name, membercost, guestcost, initialoutlay, monthlymaintenance)
    VALUES (9, 'Spa', 20, 30, 100000, 800),
           (10, 'Squash Court 2', 3.5, 17.5, 5000, 80);

3 计算后的数据插入

问题描述：

这一次不再指定设备 ID，而是自动计算下一个 facid 值。其它字段值如下。

name: 'Spa', membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800.

问题答案：

INSERT INTO cd.facilities (facid, name, membercost, guestcost, initialoutlay, monthlymaintenance)
    SELECT (SELECT max(facid)+1 FROM cd.facilities), 'Spa', 20, 30, 100000, 800;

4 根据现有内容作更新

问题描述：

我们想改变第二个网球场的价格，使其比第一个网球场贵 10%。尝试在不指定常量值的情况下执行此操作，以便我们可以根据需要重用该语句。

问题答案：

UPDATE cd.facilities
SET membercost = 1.1 * membercost, guestcost = 1.1 * guestcost
WHERE name = 'Tennis Court 2';

5 根据子查询作删除

问题描述：

删除所有从未预定过设施的成员。

问题答案：

DELETE FROM cd.members
WHERE memid
  NOT IN (
    SELECT DISTINCT memid
    FROM cd.bookings);

-- 另一种实现是使用相关子查询
DELETE FROM cd.members m
WHERE
  NOT EXISTS (
    SELECT 1
    FROM cd.bookings
    WHERE memid = m.memid);

4 聚合

聚合是一个让人能真正体会到关系型数据库强大能力的功能。该栏目深度覆盖聚合，使用标准分组以及最新的窗口函数来测试我们的掌握情况。

1 计算各成员的推荐数

问题描述：

生成各成员的推荐数列表，以成员 ID 排序。

问题答案：

SELECT recommendedby, count(*)
FROM cd.members
WHERE recommendedby IS NOT NULL
GROUP BY ecommendedby
ORDER BY recommendedby;

2 列出每个设施的预订总段数

问题描述：

生成每个设施的预订总段数。输出设施 ID 和预定总段数，按设施 ID 排序。

问题答案：

SELECT facid, sum(slots)
FROM cd.bookings
GROUP BY facid
ORDER BY facid;

3 列出给定月份每个设施的预订总段数

问题描述：

生成 2012 年 9 月每个设施的预订总段数。输出设施 ID 和预定总段数，按总段数排序。

问题答案：

SELECT facid, sum(slots) AS totalslots
FROM cd.bookings
WHERE starttime >= '2012-09-01'
  AND starttime < '2012-10-01'
GROUP BY facid
ORDER BY totalslots;

4 列出每个设施每月的预订总段数

问题描述：

生成 2012 年每个设施每月的预订总段数。输出设施 ID 和预定总段数，按设施 ID 和月份排序。

问题答案：

SELECT facid,
  extract(month from starttime) AS month,
  sum(slots)
FROM cd.bookings
WHERE starttime >= '2012-01-01'
  AND starttime < '2013-01-01'
GROUP BY facid, month
ORDER BY facid, month;

5 列出预订已超过 1000 个段的设施

问题描述：

生成预订已超过 1000 个段的设施列表。输出设施 ID 和预定总段数，按设施 ID 排序。

问题答案：

使用HAVING来过滤聚合后的结果。WHERE用于聚合前的数据筛选，而HAVING用于聚合后的数据筛选，这即是两者的区别。

SELECT facid, sum(slots)
FROM cd.bookings
GROUP BY facid
HAVING sum(slots) > 1000
ORDER BY facid;

6 列出每个设施的总收入

问题描述：

列出每个设施的总收入。输出应包括设施名和总收入，按总收入排序。记住，游客和会员的计费是不同的。

问题答案：

SELECT f.name,
    sum(CASE
          WHEN b.memid = 0
          THEN b.slots * f.guestcost
          ELSE b.slots * f.membercost
        END) AS revenue
FROM cd.bookings b, cd.facilities f
WHERE b.facid = f.facid
GROUP BY f.name
ORDER BY revenue;

7 列出总收入低于 1000 的设施

问题描述：

列出总收入小于 1000 的设施列表。输出包括设施名称和总收入，按收入排序。记住，游客和会员的计费是不同的。

问题答案：

SELECT *
FROM (SELECT f.name,
        sum(CASE
              WHEN b.memid = 0
              THEN b.slots * f.guestcost
              ELSE b.slots * f.membercost
            END) AS revenue
      FROM cd.bookings b, cd.facilities f
      WHERE b.facid = f.facid
      GROUP BY f.name) AS t
WHERE t.revenue < 1000
ORDER BY revenue;

注意如下写法是错误的：

SELECT f.name,
  sum(
    CASE
      WHEN b.memid = 0
      THEN b.slots * f.guestcost
      ELSE b.slots * f.membercost
    END) AS revenue
FROM cd.bookings b, cd.facilities f
WHERE b.facid = f.facid
GROUP BY f.name
HAVING revenue < 1000 -- PostgreSQL不允许在HAVING中直接使用列名
ORDER BY revenue;

8 输出预订段数最多的设施 ID

问题描述：

输出预订段数最多的设施 ID。尝试不使用LIMIT来实现（看起来可能会乱一点）。

问题答案：

第一种写法，看起来笨一些。

SELECT facid, sum(slots)
FROM cd.bookings
GROUP BY facid
HAVING sum(slots) = (
  SELECT max(totalslots)
  FROM (SELECT facid, sum(slots) AS totalslots
        FROM cd.bookings
        GROUP BY facid) AS t);

第二种写法，使用WITH表达式提取出公用部分，更紧凑。

WITH t AS (
    SELECT facid, sum(slots) AS totalslots
    FROM cd.bookings
    GROUP BY facid)

SELECT *
FROM t
WHERE totalslots = (
    SELECT max(totalslots)
    FROM t);

9 输出每个设施的预订总小时数

问题描述：

输出每个设施的预订总小时数，注意一个时段为半小时。输出应包含设施 ID、设施名称和预订小时数，按设施 ID 排序。尝试将小时数格式化为两位小数。

问题答案：

PostgreSQL 默认是整除的，若需采用浮点除法，需要显式指定一下。

SELECT b.facid, f.name,
  round(sum(b.slots)::numeric/2::numeric, 2)
FROM cd.bookings b, cd.facilities f
WHERE b.facid = f.facid
GROUP BY b.facid, f.name
ORDER BY b.facid;

10 列出每位会员在 2012 年 9 月 1 日之后的首次预订

问题描述：

列出每位会员的姓名、ID 和他们在 2012 年 9 月 1 日之后的第一次设施预订时间。按会员 ID 排序。

问题答案：

SELECT m.surname,
  m.firstname,
  b.memid,
  min(b.starttime)
FROM cd.bookings b, cd.members m
WHERE b.memid = m.memid
  AND b.starttime >= '2012-09-01'
GROUP BY m.surname,
  m.firstname,
  b.memid
ORDER BY b.memid;

11 生成会员名称列表，每行包含会员总数

问题描述：

生成会员（包括游客）名称列表，每行包含会员总数。按加入日期排序。

问题答案：

使用窗口函数实现。

SELECT count(*) over (),
  firstname,
  surname
FROM cd.members
ORDER BY joindate;

12 生成一份带编号的会员名单

问题描述：

生成一份会员（包括游客）的单调递增编号列表，按加入日期排序。注意，不保证会员 ID 是连续的。

问题答案：

使用窗口函数实现。

SELECT row_number() OVER (ORDER BY joindate),
  firstname,
  surname
FROM cd.members
ORDER BY joindate;

13 查找前三大创收设施

问题描述：

列出前三个创收设施（包含排名相同的）。输出设施名称和排名，按排名和设施名称排序。

问题答案：

SELECT *
FROM (SELECT
        f.name,
        rank() OVER (ORDER BY sum(
            CASE
                WHEN b.memid = 0
                THEN b.slots * f.guestcost
                ELSE b.slots * f.membercost
            END) DESC) AS rank
      FROM cd.bookings b, cd.facilities f
      WHERE b.facid = f.facid
      GROUP BY f.name) AS t
WHERE t.rank <= 3;

14 按营收额对设施进行分类

问题描述：

根据营收额将设施等分为高、中和低三类。按分类和设施名称排序。

问题答案：

主要考察ntile窗口函数的使用，其会将值尽可能的等分为指定的分组数。

SELECT name, (
    CASE
      WHEN class = 1
      THEN 'high'
      WHEN class = 2
      THEN 'average'
      ELSE 'low'
    END) AS revenue
FROM (SELECT f.name,
        ntile(3) over(ORDER BY sum(
          CASE
            WHEN b.memid = 0
            THEN b.slots * f.guestcost
            ELSE b.slots * f.membercost
          END) DESC) AS class
      FROM cd.bookings b, cd.facilities f
      WHERE b.facid = f.facid
      GROUP BY f.name) AS t
ORDER BY class, name;

5 日期处理

本栏目涉及日期处理，详情请参阅PostgreSQL 日期时间函数文档。

1 生成 2012 年 8 月 31 日凌晨 1 点的时间戳

问题描述：

生成 2012 年 8 月 31 日凌晨 1 点这个时间的时间戳。

问题答案：

有三种写法，前两种是 PostgreSQL 的语法，最后一种是 SQL 标准语法。

-- 第一种写法
SELECT TIMESTAMP '2012-08-31 01:00:00';

-- 第二种写法
SELECT '2012-08-31 01:00:00'::TIMESTAMP;

-- 第三种写法
SELECT CAST('2012-08-31 01:00:00' AS TIMESTAMP);

2 时间戳相减

问题描述：

计算时间戳2012-08-31 01:00:00减去时间戳2012-07-30 01:00:00的结果。

问题答案：

SELECT TIMESTAMP '2012-08-31 01:00:00' - TIMESTAMP '2012-07-30 01:00:00';

3 生成 2012 年 10 月的所有日期

问题描述：

生成 2012 年 10 月的所有日期。可以输出为时间戳（时间部分为00:00:00）或日期。

问题答案：

使用 PostgreSQL 的generate_series函数来生成时间序列。

SELECT generate_series(TIMESTAMP '2012-10-01', TIMESTAMP '2012-10-31', INTERVAL '1 day');

4 从时间戳获取其属于月份中的哪一天

问题描述：

从时间戳2012-08-31中获取其属于月份中的第几天。

问题答案：

使用date_part或extract函数实现。

-- 写法一
SELECT date_part('day', TIMESTAMP '2012-08-31');

-- 写法二
SELECT extract(day FROM TIMESTAMP '2012-08-31');

5 计算时间戳之间的秒数

问题描述：

计算时间戳2012-08-31 01:00:00和2012-09-02 00:00:00之间的秒数。

问题答案：

-- 手动实现方式
SELECT extract(day FROM t.int) * 24 * 60 * 60
  + extract(hour FROM t.int) * 60 * 60
  + extract(minute FROM t.int) * 60
  + extract(second FROM t.int)
FROM (SELECT age(TIMESTAMP '2012-09-02 00:00:00', TIMESTAMP '2012-08-31 01:00:00') AS int) AS t;

-- 使用PostgreSQL函数
SELECT extract(epoch FROM age(TIMESTAMP '2012-09-02 00:00:00', TIMESTAMP '2012-08-31 01:00:00'));

6 输出 2012 年每个月的天数

问题描述：

输出 2012 年的每个月及该月的天数。

问题答案：

SELECT extract(month FROM t.month) AS month,
  (t.month + INTERVAL '1 month') - t.month AS length
FROM (SELECT generate_series(DATE '2012-01-01', DATE '2012-12-31', interval '1 month') AS month) AS t;

7 计算给定月的剩余天数

问题描述：

给定时间戳2012-02-11 01:00:00，计算其对应月的剩余天数（不论给定的时间戳是几点，都应算作剩余的一整天）。

问题答案：

SELECT date_trunc('month', t.ts) + INTERVAL '1 month' - date_trunc('day', t.ts)
FROM (SELECT TIMESTAMP '2012-02-11 01:00:00' AS ts) AS t;

8 计算预订的结束时间

问题描述：

在系统中返回最近 10 个预订的开始和结束时间，先按结束时间排序，然后按开始时间排序。

问题答案：

SELECT starttime,
  starttime + slots * (interval '0.5 hour') AS endtime
FROM cd.bookings
ORDER BY endtime DESC,
  starttime DESC
LIMIT 10;

9 返回每个月的预订数

问题描述：

返回每个月的预订数，结果按月排序。

问题答案：

SELECT date_trunc('month', starttime) AS month,
  count(*)
FROM cd.bookings
GROUP BY month
ORDER BY month;

10 按月计算每个设施的利用率

问题描述：

按月计算每个设施的利用率，按名称和月份排序，四舍五入到小数点后一位。开门时间是早上 8 点，关门时间是晚上 8:30。您可以将每个月视为整月，无论俱乐部是否有某些日期未开放。

问题答案：

每天开门的时间是12.5 * 2个半小时，所以每个设备当月的预定总段数除以这个数就是当月的利用率。

SELECT
    name,
    month,
    round((totalslots / (extract(day FROM (month + interval '1 month') - month) * 12.5 * 2) * 100)::NUMERIC, 1) AS utilization
FROM (SELECT
        f.name,
        date_trunc('month', b.starttime) AS month,
        sum(b.slots) AS totalslots
      FROM cd.bookings b, cd.facilities f
      WHERE b.facid = f.facid
      GROUP BY f.name, MONTH) AS t
ORDER BY name, month;

6 字符串操作

本栏目涉及基础字符串操作，LIKE使用，正则表达式使用。详情请参阅PostgreSQL 正则匹配文档。

1 格式化会员名称

问题描述：

输出所有会员的名字，格式为Surname, Firstname。

问题答案：

SELECT surname || ', ' || firstname
FROM cd.members;

2 按名称前缀查找设施

问题描述：

查找名称以Tennis开头的所有设施。输出所有列。

问题答案：

LIKE中%用于匹配任何字符串，而_用于匹配任何单个字符。

SELECT *
FROM cd.facilities
WHERE name LIKE 'Tennis%';

3 执行不区分大小写的搜索

问题描述：

不区分大小写以查找名称以tennis开头的所有设施。输出所有列。

问题答案：

-- SQL标准写法
SELECT *
FROM cd.facilities
WHERE LOWER(name) LIKE 'tennis%';

-- PostgreSQL独有，使用ILIKE
SELECT *
FROM cd.facilities
WHERE name ILIKE 'tennis%';

4 查找带括号的电话号码

问题描述：

您可能已经注意到俱乐部的会员表中的电话号码格式很不一致。查找所有包含括号的电话号码，返回会员 ID 和电话号码，按会员 ID 排序。

问题答案：

PostgreSQL 有三种字符串匹配方法：LIKE，SIMILAR TO，及 POSIX 正则表达式。

SIMILAR TO与LIKE类似，只是其采用 SQL 正则表达式，是一种 LIKE 与 POSIX 正则表达式的结合体。SIMILAR TO不像常规正则表达式一样可以匹配子字符串，其与LIKE一样，想匹配成成功，必须匹配整个字符串。SIMILAR TO与LIKE一样，分别使用_及%表示任意单个字符及任意字符串，而.在SIMILAR TO中不表示任意单个字符。

-- 使用LIKE
SELECT memid, telephone
FROM cd.members
WHERE telephone LIKE '(%)%';

-- ~~与LIKE等价
SELECT memid, telephone
FROM cd.members
WHERE telephone ~~ '(%)%';

-- 使用SIMILAR TO
SELECT memid, telephone
FROM cd.members
WHERE telephone SIMILAR TO '\(%\)%';

-- 采用POSIX正则表达式
SELECT memid, telephone
FROM cd.members
WHERE telephone ~ '^\(\d*\)\s\d{3}-\d{4}$';

5 用前导零填充邮政编码

问题描述：

由于存储时zipcode为数值类型，我们示例数据集中的邮政编码已经从它们中删除了前导零。从成员表中检索所有邮政编码，用前导零填充任何少于 5 个字符的邮政编码。

问题答案：

-- 使用lpad函数
SELECT lpad(cast(zipcode as char(5)), 5, '0')
FROM cd.members;

-- 使用tochar
SELECT to_char(zipcode, 'FM09999')
FROM cd.members;

6 计算姓氏以每个字母开头的会员数量

问题描述：

计算会员姓氏分别以各字母开头的数量。按字母排序，如果计数为 0，就不要打印这个字母。

问题答案：

-- 使用substr
SELECT
  substr(surname, 1, 1) AS firstletter,
  count(*)
FROM cd.members
GROUP BY firstletter
ORDER BY firstletter;

-- 使用left
SELECT
  left(surname, 1) AS firstletter,
  count(*)
FROM cd.members
GROUP BY firstletter
ORDER BY firstletter;

-- 使用substring
SELECT
  substring(surname FROM '#"_#"%' FOR '#') AS firstletter,
  count(*)
FROM cd.members
GROUP BY firstletter
ORDER BY firstletter;

7 清理电话号码

问题描述：

数据库中的电话号码格式非常不一致。您想打印会员 ID 和删除’-’、’(’、’)’，及’ ‘字符后的号码。按会员 ID 排序。

问题答案：

使用 regexp_replace 函数实现。

SELECT
  memid,
  regexp_replace(telephone, '[\s\-\(\)]', '', 'g')
FROM cd.members
ORDER BY memid;

7 递归查询

本栏目涉及递归查询。在 PostgreSQL，可以使用WITH RECURSIVE进行递归查询。这对处理树和图结构数据非常实用。详情请参阅WITH Queries。

1 追溯会员的上游推荐链

问题描述：

寻找会员 ID 为 27 的上游推荐链：即寻找会员 ID 为 27 的推荐人，会员 ID 为 27 的推荐人的推荐人，以此类推。返回会员 ID、名字和姓氏。

问题答案：

使用WITH RECURSIVE表达式实现。

WITH RECURSIVE recommenders(id) AS (
  SELECT recommendedby FROM cd.members WHERE memid = 27
  UNION ALL
  SELECT recommendedby
  FROM cd.members m, recommenders r
  WHERE m.memid = r.id
)

SELECT r.id, m.firstname, m.surname
FROM recommenders r, cd.members m
WHERE r.id = m.memid;

2 追溯会员的下游推荐链

问题描述：

寻找会员 ID 为 1 的下游推荐链：即寻找 ID 为 1 的会员推荐了哪些人，ID 为 1 的会员推荐的这些人又推荐了哪些人，以此类推。返回会员 ID、名字和姓氏，按会员 ID 排序。

问题答案：

使用WITH RECURSIVE表达式实现。

WITH RECURSIVE recommendeds(id) AS (
  SELECT memid FROM cd.members WHERE recommendedby = 1
  UNION ALL
  SELECT m.memid
  FROM cd.members m, recommendeds r
  WHERE m.recommendedby = r.id
)

SELECT r.id, m.firstname, m.surname
FROM recommendeds r, cd.members m
WHERE r.id = m.memid
ORDER BY id;

参考资料
[1]postgresql exercises
[2]postgresql date/time functions and operators
[3]postgresql pattern matching
[4]postgresql with queries (common table expressions)

PostgreSQL 基础知识在线练习

1 简单 SQL 查询

2 连接及子查询

3 数据修改

4 聚合

5 日期处理

6 字符串操作

7 递归查询

相关文章

评论