mysql机票预订管理系统

### 关于使用 MySQL 实现机票预订管理系统

#### 数据库结构设计

为了构建一个高效且易于维护的机票预订管理系统，数据库的设计至关重要。以下是建议的数据表及其字段：

- **航班信息 (flights)**
存储所有可用航班的信息。

| 字段名 | 类型 | 描述 |
| -------------- | ------------- | ------------------ |
| flight_id | INT | 航班编号（主键） |
| airline | VARCHAR(50) | 承运航空公司名称 |
| departure_city | VARCHAR(100) | 出发城市 |
| arrival_city | VARCHAR(100) | 到达城市 |
| departure_time | DATETIME | 预定起飞时间 |
| arrival_time | DATETIME | 计划降落时间 |

CREATE TABLE flights (
  flight_id INT AUTO_INCREMENT PRIMARY KEY,
  airline VARCHAR(50),
  departure_city VARCHAR(100),
  arrival_city VARCHAR(100),
  departure_time DATETIME,
  arrival_time DATETIME
);

- **座位信息 (seats)**
维护每架飞机上的具体座位详情。

| 字段名 | 类型 | 描述 |
| ------------ | --------- | ---------------- |
| seat_id | INT | 座位唯一标识符 |
| flight_id | INT | 对应航班ID |
| row_num | TINYINT | 排号 |
| column_char | CHAR(1) | 列字母 |
| class_type | ENUM('E','B') |舱位等级(E经济/B商务)|

CREATE TABLE seats (
  seat_id INT AUTO_INCREMENT PRIMARY KEY,
  flight_id INT NOT NULL,
  row_num TINYINT UNSIGNED,
  column_char CHAR(1),
  class_type ENUM('E', 'B'),
  FOREIGN KEY (flight_id) REFERENCES flights(flight_id)
);

- **乘客信息 (passengers)**
收集并保存每位旅客的基本资料。

| 字段名 | 类型 | 描述 |
| ------------ | ----------------- | -------------------- |
| passenger_id | BIGINT | 用户账号/身份证号码 |
| name | VARCHAR(255) | 姓名 |
| gender | ENUM('M','F','O') | 性别(M男/F女/O其他)|
| contact_info | JSON | 联系方式(JSON格式) |

CREATE TABLE passengers (
  passenger_id BIGINT PRIMARY KEY,
  name VARCHAR(255),
  gender ENUM('M', 'F', 'O'),
  contact_info JSON
);

- **订单记录 (orders)**
追踪每一次成功的订票操作。

| 字段名 | 类型 | 描述 |
| -- | --------- | ------------------------ |
| order_id | INT | 订单序列号 |
| passenger_id | BIGINT | 下单者身份证明 |
| seat_id | INT | 已选座位置 |
| booking_date | TIMESTAMP | 创建日期 |
| status | ENUM(...) | 当前状态(C取消/P待支付/C完成)

CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  passenger_id BIGINT NOT NULL,
  seat_id INT UNIQUE NOT NULL,
  booking_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  status ENUM('C', 'P', 'D') COMMENT 'C=Cancelled, P=Pending Payment, D=Done',
  FOREIGN KEY (seat_id) REFERENCES seats(seat_id),
  FOREIGN KEY (passenger_id) REFERENCES passengers(passenger_id)
);

#### 查询优化策略

对于上述架构，在执行频繁读取或写入操作时需考虑性能因素。这里提供几个常见的SQL语句示例来展示如何有效地利用索引和其他技术手段提高效率:

##### 获取特定时间段内的热门航线排名

此查询可以帮助了解哪些路线最受欢迎，并据此调整营销计划或资源分配。

SELECT f.departure_city, f.arrival_city, COUNT(*) AS ticket_count
FROM flights f JOIN orders o ON f.flight_id = s.flight_id AND s.seat_id = o.seat_id
WHERE DATE(o.booking_date) BETWEEN 'start-date' AND 'end-date'
GROUP BY f.departure_city, f.arrival_city
ORDER BY ticket_count DESC LIMIT 10;

##### 查找某天某个机场出发的所有航班列表

当用户想要查看一天之内从某一地点出发的所有选项时可以使用此类查询。

SELECT * FROM flights WHERE DATE(departure_time) = CURDATE() AND departure_city = 'target-city';

##### 更新已售罄的状态标记

一旦发现有航班满员，则应及时通知前端界面停止销售相应席位。

UPDATE flights SET is_full = TRUE WHERE flight_id IN (
  SELECT DISTINCT flight_id FROM seats LEFT JOIN orders USING(seat_id)
  GROUP BY flight_id HAVING SUM(IF(status='D', 1, 0)) >= total_seats_limit
);

---