# 演示数据库 > 探索 Airlines 演示数据库：其模式、bookings、tickets、flights、routes，以及 PostgreSQL 18 示例中使用的数据。 --- LLMS 索引： [llms.txt](/llms.txt) --- ## 1. 基本信息 {#qpt-section-1-1} 要处理更复杂的查询，我们需要一个规模可观的数据库：其中有九张填充了逼真数据的表。我们选择航空旅行作为主题。这个数据库包含某个时间段内由一家虚构航空公司运营的航班信息。 数据库模式如下图所示。我们尽量让它既不过于复杂，也不过于简单：足够简单，可以避免不必要的细节；又足够丰富，可以支持有意义且有趣的查询。 网站上提供四种大小的数据库。本书关于查询优化的示例使用的是 2025 年 9 月 1 日的版本，大小约为 2.7 GB，其中包含六个月的航班数据。 可以使用下面的函数检查版本： ```sql SELECT bookings.version(); version ----------------------------------- PostgresPro 2025-09-01 (181 days) (1 row) ``` ![bookings、tickets、segments、flights、routes、airports、airplanes、seats 和 boarding_passes 之间的关系；图中标出了键列和必填字段。](/images/qpt/p007-01.svg) {#qpt-fig-1-1 num="1.1" caption="Airlines 数据库模式" .book-original-figure .d-flex .flex-column .align-items-center width=681 height=421} ## 2. 主要实体 {#qpt-section-1-2} 核心实体是一个**预订**（`bookings`）。 ```sql SELECT * FROM bookings b WHERE b.book_ref = '03RUKL'; book_ref | book_date | total_amount ----------+-------------------------------+-------------- 03RUKL | 2026-01-04 08:01:43.367932+03 | 84850.00 (1 row) ``` 这行数据给出了预订日期和总金额。按照真实时钟，距离预订的时间每天都会增加： ```sql SELECT now() - b.book_date FROM bookings b WHERE b.book_ref = '03RUKL'; ?column? -------------------------- 264 days 09:13:04.888436 (1 row) ``` 不过，演示数据库有一个固定的“当前”时间： ```sql SELECT bookings.now(); now ------------------------ 2026-03-01 03:00:00+03 (1 row) ``` 相对于这个时钟，距离预订的时间就短得多： ```sql SELECT bookings.now() - b.book_date FROM bookings b WHERE b.book_ref = '03RUKL'; ?column? ------------------------- 55 days 18:58:16.632068 (1 row) ``` 一笔预订可以包含多名乘客。每名乘客都会为去程行程获得一张单独的**机票**（`tickets`）；如果需要返程，还会再获得一张机票。 所选预订包含以下机票： ```sql SELECT t.ticket_no, t.passenger_id, t.passenger_name FROM bookings b JOIN tickets t ON t.book_ref = b.book_ref WHERE b.book_ref = '03RUKL'; ticket_no | passenger_id | passenger_name ---------------+------------------+-------------------- 0005435998767 | RU 3761090132061 | Evgeniy Kondratyev 0005435998770 | RU 3761090132061 | Evgeniy Kondratyev 0005435998774 | RU 5595520101762 | Oleg Golubev 0005435998776 | RU 5595520101762 | Oleg Golubev (4 rows) ``` 如果出发地和目的地之间有直达航班，一张机票只包含一个**航段**（`segments`）；如果旅程需要转机，则会包含多个航段。 我们的乘客经过哪些航段？只需查看一名乘客，因为同一笔预订中的所有乘客都遵循相同的行程： ```sql SELECT s.* FROM tickets t JOIN segments s ON s.ticket_no = t.ticket_no WHERE t.ticket_no = '0005435998767'; ticket_no | flight_id | fare_conditions | price ---------------+-----------+-----------------+---------- 0005435998767 | 17386 | Economy | 6750.00 0005435998767 | 17606 | Economy | 14500.00 (2 rows) ``` 这是去程机票。把它与返程机票进行比较： ```text ticket_no | flight_id | fare_conditions | price ---------------+-----------+-----------------+--------- 0005435998770 | 26693 | Comfort | 3575.00 0005435998770 | 27013 | Comfort | 5525.00 0005435998770 | 27375 | Economy | 2750.00 0005435998770 | 27537 | Economy | 8250.00 (4 rows) ``` 去程和返程的行程不必相同：返程时，Evgeniy 要转机三次，而且其中两趟**航班**（`flights`）属于 Comfort 舱等。 每趟航班都沿着一条**航线**（`routes`）从一个机场飞往另一个机场。航线号相同的航线具有相同的出发机场和到达机场，而单独的航班则由出发日期区分。 要找出所选航班对应的航线，必须考虑这两张表之间的时间关系。航班时刻表会随时间变化，因此连接条件不仅要匹配航线号，还要匹配航线的有效时间区间与航班出发时间。 ```sql SELECT r.route_no, f.scheduled_departure, f.status, r.departure_airport dep, r.arrival_airport arr FROM tickets t JOIN segments s ON s.ticket_no = t.ticket_no JOIN flights f ON f.flight_id = s.flight_id JOIN routes r ON r.route_no = f.route_no AND r.validity @> f.scheduled_departure WHERE t.ticket_no = '0005435998767' ORDER BY f.scheduled_departure; route_no | scheduled_departure | status | dep | arr ----------+------------------------+---------+-----+----- PG0177 | 2026-01-05 15:05:00+03 | Arrived | SVO | AER PG0587 | 2026-01-06 19:00:00+03 | Arrived | AER | LHR (2 rows) ``` **时刻表**（`timetable`）视图隐藏了这种复杂性。它像航班信息显示屏一样，在隐藏两张表之间连接的同时，展示我们需要的信息： ```sql SELECT tt.route_no, tt.scheduled_departure, tt.status, tt.departure_airport dep, tt.arrival_airport arr FROM tickets t JOIN segments s ON s.ticket_no = t.ticket_no JOIN timetable tt ON tt.flight_id = s.flight_id WHERE t.ticket_no = '0005435998770' ORDER BY tt.scheduled_departure; route_no | scheduled_departure | status | dep | arr ----------+------------------------+-----------+-----+----- PG0272 | 2026-02-27 06:25:00+03 | Arrived | LHR | CDG PG0781 | 2026-03-01 02:50:00+03 | Departed | CDG | MUC PG0358 | 2026-03-03 03:50:00+03 | Scheduled | MUC | VIE PG0165 | 2026-03-04 02:20:00+03 | Scheduled | VIE | SVO (4 rows) ``` 两趟去程航班和一趟返程航班已经抵达（状态为 `Arrived`）。按照演示数据库的当前时间，乘客正在从 CDG 飞往 MUC（状态为 `Departed`）。其余航班的值机尚未开放（状态为 `Scheduled`）。 **机场**（`airports`）使用标准的三字母机场代码作为键。下面是一座机场的完整记录： ```sql SELECT * FROM airports WHERE airport_code = 'CDG' \gx -[ RECORD 1 ]+------------------ airport_code | CDG airport_name | Charles de Gaulle city | Paris country | France coordinates | (2.55,49.0128) timezone | Europe/Paris ``` 下面是我们关心的航线： ```sql SELECT lower(r.validity) valid_from, upper(r.validity) valid_to, r.airplane_code, r.days_of_week, r.scheduled_time, r.duration FROM routes r WHERE r.route_no = 'PG0781' AND r.validity @> '2026-03-01 02:50:00+03'::timestamptz \gx -[ RECORD 1 ]--+----------------------- valid_from | 2026-02-01 03:00:00+03 valid_to | 2026-03-01 03:00:00+03 airplane_code | CR7 days_of_week | {1,2,3,4,5,6,7} scheduled_time | 00:50:00 duration | 01:25:00 ``` 在这个有效期内，航线每周运行七天，使用一架 CR7 **飞机**（`airplanes`）： ```sql SELECT * FROM airplanes WHERE airplane_code = 'CR7'; airplane_code | model | range | speed ---------------+-------------------+-------+------- CR7 | Bombardier CRJ700 | 3100 | 829 (1 row) ``` **座位**（`seats`）的数量及其在各票价舱等中的分布，取决于航班使用的飞机型号。在演示数据库中，同一型号的所有飞机都有相同的客舱配置。 例如，下面是第一排座位： ```sql SELECT s.* FROM airplanes a JOIN seats s ON s.airplane_code = a.airplane_code WHERE a.airplane_code = 'CR7' AND s.seat_no ~ '^1.$'; airplane_code | seat_no | fare_conditions ---------------+---------+----------------- CR7 | 1A | Business CR7 | 1C | Business CR7 | 1D | Business (3 rows) ``` 这些是 Business 舱座位。下面是这架飞机每个票价舱等中的座位总数： ```sql SELECT s.fare_conditions, count(*) FROM seats s WHERE s.airplane_code = 'CR7' GROUP BY s.fare_conditions; fare_conditions | count -----------------+------- Business | 6 Comfort | 12 Economy | 52 (3 rows) ``` 下面是所选航线上的一趟航班的完整记录。实际时间可能与时刻表不同：通常不会相差太多（本例中的出发时间延误了六分钟），但有时也可能相差数小时： ```sql SELECT * FROM flights f WHERE f.flight_id = 27013 \gx -[ RECORD 1 ]-------+---------------------------- flight_id | 27013 route_no | PG0781 status | Departed scheduled_departure | 2026-03-01 02:50:00+03 scheduled_arrival | 2026-03-01 04:15:00+03 actual_departure | 2026-03-01 02:56:02.8521+03 actual_arrival | ``` 当乘客为第一趟航班办理值机时，会为机票上的所有航班签发**登机牌**（`boarding_passes`）。登机牌会指定座位。航班和座位的组合是唯一的：两名乘客不能占用同一个座位。登机时，登机牌会记录乘客的登机顺序号以及其通过登机口的时间。 我们的乘客坐过、或将要坐哪些座位？ ```sql SELECT f.status, f.flight_id, bp.seat_no, bp.boarding_no FROM tickets t JOIN segments s ON s.ticket_no = t.ticket_no JOIN flights f ON f.flight_id = s.flight_id LEFT JOIN boarding_passes bp ON bp.ticket_no = s.ticket_no AND bp.flight_id = s.flight_id WHERE t.ticket_no IN ('0005435998767','0005435998770') ORDER BY f.scheduled_departure; status | flight_id | seat_no | boarding_no -----------+-----------+---------+------------- Arrived | 17386 | 50A | 41 Arrived | 17606 | 21D | 16 Arrived | 26693 | 11A | 176 Departed | 27013 | 7C | 49 Scheduled | 27375 | 45D | Scheduled | 27537 | 13B | (6 rows) ``` 演示数据库可以用不同语言显示机场、城市、国家和飞机名称。显示语言由数据库级别设置的配置参数控制： ```sql SHOW bookings.lang; bookings.lang --------------- en (1 row) ``` 把当前会话切换为俄语： ```sql SET bookings.lang = 'ru'; SELECT * FROM airports a WHERE a.airport_code = 'SVO' \gx -[ RECORD 1 ]+------------------ airport_code | SVO airport_name | Шереметьево city | Москва country | Россия coordinates | (37.4146,55.9726) timezone | Europe/Moscow ``` 实现方式是在以 JSON 存储译名的基表之上创建视图： ```sql SELECT ml.airport_name, ml.country, ml.city FROM airports_data ml WHERE ml.airport_code = 'SVO' \gx -[ RECORD 1 ]+-------------------------------------------- airport_name | {"en": "Sheremetyevo", "ru": "Шереметьево"} country | {"en": "Russia", "ru": "Россия"} city | {"en": "Moscow", "ru": "Москва"} ``` 最后总结一下，演示数据库中包含以下表和视图： ```text \dtv List of relations Schema | Name | Type | Owner ----------+-----------------+-------+---------- bookings | airplanes | view | postgres bookings | airplanes_data | table | postgres bookings | airports | view | postgres bookings | airports_data | table | postgres bookings | boarding_passes | table | postgres bookings | bookings | table | postgres bookings | flights | table | postgres bookings | routes | table | postgres bookings | seats | table | postgres bookings | segments | table | postgres bookings | tickets | table | postgres bookings | timetable | view | postgres (12 rows) ```