# 航班表 use air_data; CREATE TABLE flights ( flight_id VARCHAR(20) PRIMARY KEY, # 航班号 airline_company VARCHAR(100) NOT NULL, # 航空公司 departure_location VARCHAR(100) NOT NULL, # 出发地点 destination_location VARCHAR(100) NOT NULL, # 目的地位置 departure_time DATETIME NOT NULL, # 出发时间 arrival_time DATETIME NOT NULL, # 到达时间 boarding_time DATETIME NOT NULL, # 登机时间 gate VARCHAR(20), # 登机口 delay_status ENUM ('on_time', 'delayed', 'cancelled') DEFAULT 'on_time', # 延迟状态 destination_image VARCHAR(255), # 目的地图片 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP # 创建时间 ); INSERT INTO flights (flight_id, airline_company, departure_location, destination_location, departure_time, arrival_time, boarding_time, gate, delay_status, destination_image) VALUES ('CA123', 'Air China', 'Beijing (PEK)', 'Tokyo (NRT)', '2024-03-20 08:30:00', '2024-03-20 12:45:00', '2024-03-20 07:45:00', 'A12', 'on_time', '/images/tokyo.jpg'), ('UA789', 'United Airlines', 'New York (JFK)', 'London (LHR)', '2024-03-20 14:15:00', '2024-03-20 23:30:00', '2024-03-20 13:30:00', 'B7', 'on_time', '/images/london.jpg'), ('EK521', 'Emirates', 'Dubai (DXB)', 'Singapore (SIN)', '2024-03-20 09:45:00', '2024-03-20 17:20:00', '2024-03-20 09:00:00', 'C15', 'delayed', '/images/singapore.jpg'), ('LH430', 'Lufthansa', 'Frankfurt (FRA)', 'New York (JFK)', '2024-03-20 11:20:00', '2024-03-20 14:35:00', '2024-03-20 10:35:00', 'D22', 'on_time', '/images/newyork.jpg'), ('SQ305', 'Singapore Airlines', 'Singapore (SIN)', 'Sydney (SYD)', '2024-03-20 16:40:00', '2024-03-20 22:15:00', '2024-03-20 15:55:00', 'E8', 'on_time', '/images/sydney.jpg'), ('BA117', 'British Airways', 'London (LHR)', 'Dubai (DXB)', '2024-03-20 13:10:00', '2024-03-20 22:45:00', '2024-03-20 12:25:00', 'F3', 'on_time', '/images/dubai.jpg'), ('AF385', 'Air France', 'Paris (CDG)', 'Tokyo (NRT)', '2024-03-20 10:50:00', '2024-03-21 06:20:00', '2024-03-20 10:05:00', 'G11', 'on_time', '/images/tokyo.jpg'), ('JL065', 'Japan Airlines', 'Tokyo (NRT)', 'Los Angeles (LAX)', '2024-03-20 15:30:00', '2024-03-20 09:15:00', '2024-03-20 14:45:00', 'H5', 'on_time', '/images/losangeles.jpg'), ('DL289', 'Delta Air Lines', 'Atlanta (ATL)', 'Paris (CDG)', '2024-03-20 18:25:00', '2024-03-21 08:10:00', '2024-03-20 17:40:00', 'A9', 'delayed', '/images/paris.jpg'), ('CX831', 'Cathay Pacific', 'Hong Kong (HKG)', 'Vancouver (YVR)', '2024-03-20 12:05:00', '2024-03-20 08:50:00', '2024-03-20 11:20:00', 'B14', 'on_time', '/images/vancouver.jpg'), ('QF2', 'Qantas', 'London (LHR)', 'Singapore (SIN)', '2024-03-20 19:40:00', '2024-03-21 17:25:00', '2024-03-20 18:55:00', 'C6', 'on_time', '/images/singapore.jpg'), ('KL887', 'KLM', 'Amsterdam (AMS)', 'New York (JFK)', '2024-03-20 14:55:00', '2024-03-20 17:20:00', '2024-03-20 14:10:00', 'D18', 'on_time', '/images/newyork.jpg'), ('EY451', 'Etihad Airways', 'Abu Dhabi (AUH)', 'Sydney (SYD)', '2024-03-20 21:15:00', '2024-03-21 17:40:00', '2024-03-20 20:30:00', 'E4', 'on_time', '/images/sydney.jpg'), ('TK15', 'Turkish Airlines', 'Istanbul (IST)', 'Tokyo (NRT)', '2024-03-20 08:10:00', '2024-03-20 23:45:00', '2024-03-20 07:25:00', 'F9', 'cancelled', '/images/tokyo.jpg'), ('AA125', 'American Airlines', 'Chicago (ORD)', 'London (LHR)', '2024-03-20 16:50:00', '2024-03-21 06:25:00', '2024-03-20 16:05:00', 'G13', 'on_time', '/images/london.jpg'), ('NZ5', 'Air New Zealand', 'Auckland (AKL)', 'Los Angeles (LAX)', '2024-03-20 13:35:00', '2024-03-20 05:20:00', '2024-03-20 12:50:00', 'H7', 'on_time', '/images/losangeles.jpg'), ('BR87', 'EVA Air', 'Taipei (TPE)', 'Vancouver (YVR)', '2024-03-20 11:45:00', '2024-03-20 06:30:00', '2024-03-20 11:00:00', 'A3', 'delayed', '/images/vancouver.jpg'), ('LH730', 'Lufthansa', 'Munich (MUC)', 'Dubai (DXB)', '2024-03-20 09:20:00', '2024-03-20 15:55:00', '2024-03-20 08:35:00', 'B10', 'on_time', '/images/dubai.jpg'), ('SQ25', 'Singapore Airlines', 'Frankfurt (FRA)', 'New York (JFK)', '2024-03-20 17:30:00', '2024-03-20 20:15:00', '2024-03-20 16:45:00', 'C8', 'on_time', '/images/newyork.jpg'), ('UA863', 'United Airlines', 'San Francisco (SFO)', 'Sydney (SYD)', '2024-03-20 22:05:00', '2024-03-22 06:40:00', '2024-03-20 21:20:00', 'D5', 'on_time', '/images/sydney.jpg'); # 乘客表 CREATE TABLE passengers ( id_card VARCHAR(18) PRIMARY KEY, #身份证号 name VARCHAR(100) NOT NULL, # 姓名 is_vip BOOLEAN DEFAULT FALSE, # 是否是VIP created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP # 创建时间 ); -- 插入测试数据到 passengers 表 INSERT INTO passengers (id_card, name, is_vip) VALUES ('110101199001011234', '张三', TRUE), ('110101199002022345', '李四', FALSE), ('110101199003033456', '王五', TRUE), ('110101199004044567', '赵六', FALSE), ('110101199005055678', '钱七', TRUE), ('110101199006066789', '孙八', FALSE), ('110101199007077890', '周九', TRUE), ('110101199008088901', '吴十', FALSE), ('110101199009099012', '郑十一', TRUE), ('110101199010101123', '王十二', FALSE); update passengers set name = 'ZHANG' where id_card = '110101199001011234'; # 管理员表 CREATE TABLE admins ( admin_id VARCHAR(18) PRIMARY KEY, # 管理员id username VARCHAR(50) UNIQUE NOT NULL, # 账号 password_hash VARCHAR(255) NOT NULL, # 密码 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP # 创建时间 ); INSERT INTO admins (admin_id, username, password_hash) VALUES ('100000000000000001', 'admin01', '100001'), ('100000000000000002', 'admin02', '100002'), ('100000000000000003', 'admin03', '100003'), ('100000000000000004', 'admin04', '100004'), ('100000000000000005', 'admin05', '100005'), ('100000000000000006', 'superadmin', '100006'), ('100000000000000007', 'sysadmin', '100007'), ('100000000000000008', 'webmaster', '100008'), ('100000000000000009', 'rootadmin', '100009'), ('100000000000000010', 'masteradmin', '100010'); # 飞机座位 CREATE TABLE flight_seats ( seat_id INT PRIMARY KEY AUTO_INCREMENT, # 座位号 flight_id VARCHAR(20) NOT NULL, # 航班号 seat_number VARCHAR(10) NOT NULL, # 座位 seat_row INT NOT NULL, # 行号 seat_column CHAR(1) NOT NULL, # 列号 seat_class ENUM ('economy', 'business', 'first') DEFAULT 'economy', # 座位类型 is_available BOOLEAN DEFAULT TRUE, # 是否可用 UNIQUE KEY unique_flight_seat (flight_id, seat_number), FOREIGN KEY (flight_id) REFERENCES flights (flight_id) ); delete from flight_seats; INSERT INTO flight_seats (flight_id, seat_number, seat_row, seat_column, seat_class, is_available) VALUES -- 航班 CA123 (Air China) - 假设 A330 布局 ('CA123', '1A', 1, 'A', 'first', TRUE), ('CA123', '1B', 1, 'B', 'first', TRUE), ('CA123', '1C', 1, 'C', 'first', TRUE), ('CA123', '1D', 1, 'D', 'first', TRUE), ('CA123', '2A', 2, 'A', 'first', TRUE), ('CA123', '2B', 2, 'B', 'first', TRUE), ('CA123', '2C', 2, 'C', 'first', TRUE), ('CA123', '2D', 2, 'D', 'first', TRUE), ('CA123', '5A', 5, 'A', 'business', TRUE), ('CA123', '5B', 5, 'B', 'business', TRUE), ('CA123', '5C', 5, 'C', 'business', TRUE), ('CA123', '5D', 5, 'D', 'business', TRUE), ('CA123', '5E', 5, 'E', 'business', TRUE), ('CA123', '5F', 5, 'F', 'business', TRUE), ('CA123', '10A', 10, 'A', 'economy', TRUE), ('CA123', '10B', 10, 'B', 'economy', TRUE), ('CA123', '10C', 10, 'C', 'economy', TRUE), ('CA123', '10D', 10, 'D', 'economy', TRUE), ('CA123', '10E', 10, 'E', 'economy', TRUE), ('CA123', '10F', 10, 'F', 'economy', TRUE), -- 航班 UA789 (United Airlines) - 假设 777 布局 ('UA789', '1A', 1, 'A', 'first', TRUE), ('UA789', '1B', 1, 'B', 'first', TRUE), ('UA789', '1C', 1, 'C', 'first', TRUE), ('UA789', '1D', 1, 'D', 'first', TRUE), ('UA789', '4A', 4, 'A', 'business', TRUE), ('UA789', '4B', 4, 'B', 'business', TRUE), ('UA789', '4C', 4, 'C', 'business', TRUE), ('UA789', '4D', 4, 'D', 'business', TRUE), ('UA789', '4E', 4, 'E', 'business', TRUE), ('UA789', '4F', 4, 'F', 'business', TRUE), ('UA789', '8A', 8, 'A', 'economy', TRUE), ('UA789', '8B', 8, 'B', 'economy', TRUE), ('UA789', '8C', 8, 'C', 'economy', TRUE), ('UA789', '8D', 8, 'D', 'economy', TRUE), ('UA789', '8E', 8, 'E', 'economy', TRUE), ('UA789', '8F', 8, 'F', 'economy', TRUE), -- 航班 EK521 (Emirates) - 假设 A380 布局 ('EK521', '1A', 1, 'A', 'first', TRUE), ('EK521', '1B', 1, 'B', 'first', TRUE), ('EK521', '2A', 2, 'A', 'first', TRUE), ('EK521', '2B', 2, 'B', 'first', TRUE), ('EK521', '5A', 5, 'A', 'business', TRUE), ('EK521', '5B', 5, 'B', 'business', TRUE), ('EK521', '5C', 5, 'C', 'business', TRUE), ('EK521', '5D', 5, 'D', 'business', TRUE), ('EK521', '6A', 6, 'A', 'business', TRUE), ('EK521', '6B', 6, 'B', 'business', TRUE), ('EK521', '6C', 6, 'C', 'business', TRUE), ('EK521', '6D', 6, 'D', 'business', TRUE), ('EK521', '15A', 15, 'A', 'economy', TRUE), ('EK521', '15B', 15, 'B', 'economy', TRUE), ('EK521', '15C', 15, 'C', 'economy', TRUE), ('EK521', '15D', 15, 'D', 'economy', TRUE), ('EK521', '15E', 15, 'E', 'economy', TRUE), ('EK521', '15F', 15, 'F', 'economy', TRUE), -- 继续为其他航班添加座位数据... -- 航班 LH430 (Lufthansa) ('LH430', '1A', 1, 'A', 'first', TRUE), ('LH430', '1B', 1, 'B', 'first', TRUE), ('LH430', '1C', 1, 'C', 'first', TRUE), ('LH430', '1D', 1, 'D', 'first', TRUE), ('LH430', '4A', 4, 'A', 'business', TRUE), ('LH430', '4B', 4, 'B', 'business', TRUE), ('LH430', '4C', 4, 'C', 'business', TRUE), ('LH430', '4D', 4, 'D', 'business', TRUE), ('LH430', '8A', 8, 'A', 'economy', TRUE), ('LH430', '8B', 8, 'B', 'economy', TRUE), ('LH430', '8C', 8, 'C', 'economy', TRUE), ('LH430', '8D', 8, 'D', 'economy', TRUE), ('LH430', '8E', 8, 'E', 'economy', TRUE), ('LH430', '8F', 8, 'F', 'economy', TRUE), -- 航班 SQ305 (Singapore Airlines) ('SQ305', '1A', 1, 'A', 'first', TRUE), ('SQ305', '1B', 1, 'B', 'first', TRUE), ('SQ305', '2A', 2, 'A', 'first', TRUE), ('SQ305', '2B', 2, 'B', 'first', TRUE), ('SQ305', '5A', 5, 'A', 'business', TRUE), ('SQ305', '5B', 5, 'B', 'business', TRUE), ('SQ305', '5C', 5, 'C', 'business', TRUE), ('SQ305', '5D', 5, 'D', 'business', TRUE), ('SQ305', '10A', 10, 'A', 'economy', TRUE), ('SQ305', '10B', 10, 'B', 'economy', TRUE), ('SQ305', '10C', 10, 'C', 'economy', TRUE), ('SQ305', '10D', 10, 'D', 'economy', TRUE), ('SQ305', '10E', 10, 'E', 'economy', TRUE), ('SQ305', '10F', 10, 'F', 'economy', TRUE); # 订单表 CREATE TABLE orders ( order_id VARCHAR(50) PRIMARY KEY, # 订单号 passenger_id VARCHAR(18) NOT NULL, # 乘客id flight_id VARCHAR(20) NOT NULL, # 航班号 seat_id INT NOT NULL, # 关联座位表 food_name VARCHAR(100), # 食物名称 notes TEXT, # 备注信息 boarding_status BOOLEAN DEFAULT FALSE, # 登机状态 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, # 创建时间 FOREIGN KEY (passenger_id) REFERENCES passengers (id_card), # 外键 FOREIGN KEY (flight_id) REFERENCES flights (flight_id), # 外键 FOREIGN KEY (seat_id) REFERENCES flight_seats (seat_id) # 外键 ); -- 初始化所有乘客均未登机 update orders set boarding_status = FALSE; -- 删除座位外键约束 alter table orders drop foreign key orders_ibfk_3; -- 修改座位可为null值 alter table orders modify seat_id int NULL; -- 重新添加外键 alter table orders add constraint fk_orders_seat foreign key (seat_id) references flight_seats(seat_id); update orders set seat_id = null; INSERT INTO orders (order_id, passenger_id, flight_id, seat_id, food_name, notes, boarding_status) VALUES -- 张三的订单 ('ORD20240320001', '110101199001011234', 'CA123', 1, '牛肉饭', '需要毛毯', TRUE), ('ORD20240320002', '110101199001011234', 'UA789', 2, '素食餐', '靠窗座位偏好', FALSE), -- 李四的订单 ('ORD20240320003', '110101199002022345', 'EK521', 3, '鸡肉面', '生日特别服务', FALSE), ('ORD20240320004', '110101199002022345', 'LH430', 4, '海鲜餐', '过敏：花生', TRUE), -- 王五的订单 ('ORD20240320005', '110101199003033456', 'SQ305', 5, '儿童餐', '带婴儿旅行', TRUE), ('ORD20240320006', '110101199003033456', 'BA117', 6, '糖尿病餐', '需要胰岛素冷藏', FALSE), -- 赵六的订单 ('ORD20240320007', '110101199004044567', 'AF385', 10, '普通餐', '第一次飞行', TRUE), ('ORD20240320008', '110101199004044567', 'JL065', 15, '牛肉饭', '无特殊要求', FALSE), -- 钱七的订单 ('ORD20240320009', '110101199005055678', 'DL289', 20, '素食餐', 'VIP服务', TRUE), ('ORD20240320010', '110101199005055678', 'CX831', 21, '低盐餐', '心脏病人', TRUE), -- 孙八的订单 ('ORD20240320011', '110101199006066789', 'QF2', 22, '海鲜餐', '庆祝纪念日', FALSE), ('ORD20240320012', '110101199006066789', 'KL887', 33, '鸡肉面', '中转航班', TRUE), -- 周九的订单 ('ORD20240320013', '110101199007077890', 'EY451', 43, '水果拼盘', '减肥餐', FALSE), ('ORD20240320014', '110101199007077890', 'TK15', 44, '牛肉饭', '商务会议材料', TRUE), -- 吴十的订单 ('ORD20240320015', '110101199008088901', 'AA125', 56, '普通餐', '无特殊要求', FALSE), ('ORD20240320016', '110101199008088901', 'NZ5', 57, '儿童餐', '带儿童旅行', TRUE), -- 郑十一的订单 ('ORD20240320017', '110101199009099012', 'BR87', 58, '糖尿病餐', '需要医疗协助', TRUE), ('ORD20240320018', '110101199009099012', 'LH730', 59, '素食餐', 'VIP通道', TRUE), -- 王十二的订单 ('ORD20240320019', '110101199010101123', 'SQ25', 60, '低脂餐', '健身饮食', FALSE), ('ORD20240320020', '110101199010101123', 'UA863', 61, '海鲜餐', '过敏：贝类', TRUE), -- 额外的订单，展示更多航班组合 ('ORD20240320021', '110101199001011234', 'EK521', 62, '牛肉饭', '回程航班', FALSE), ('ORD20240320022', '110101199003033456', 'UA789', 63, '普通餐', '公司出差', TRUE), ('ORD20240320023', '110101199005055678', 'CA123', 71, '素食餐', '环保旅行', TRUE), ('ORD20240320024', '110101199007077890', 'SQ305', 72, '糖尿病餐', '定期检查', FALSE), ('ORD20240320025', '110101199009099012', 'LH430', 73, '海鲜餐', '蜜月旅行', TRUE);