You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
284 lines
15 KiB
284 lines
15 KiB
# 航班表
|
|
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);
|