forked from Russell-A/database-course-design
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathair_data.sql
111 lines (104 loc) · 6.72 KB
/
air_data.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
USE air
GO
INSERT INTO 机场(机场代码,机场名,所在城市)
VALUES
('01','首都机场','北京'),
('02','双流机场','成都'),
('03','高崎机场','香港'),
('04','太平机场','哈尔滨'),
('05','江北机场','海南'),
('06','虹桥机场','上海'),
('07','龙嘉机场','长春'),
('08','中川机场','兰州'),
('09','白云机场','广州'),
('10','黄沙机场','长沙'),
('11','昌北机场','南昌'),
('12','南苑机场','北京')
INSERT INTO 用户表(用户名
,用户密码
,用户权限
,用户联系方式)
VALUES
('复旦大学','310226199',0,'1321231278'),
('上海市文化街','31022612873',0,'18888888888'),
('郑州绿博园','zzlby2010',0,'13543276895'),
('余夏','1274111',0,'18917260800'),
('地平线~','dpx123321',0,'1891739856'),
('铁柱','1274111',0,'18917260800'),
('乌托邦','seiclhiqcaa',0,'13876392037'),
('aa','asdfghjkl',0,'18217461800'),
('机场管理员a','kseh23Gke9',1,'13972654267'),
('机场管理员b','AShs195ose',1,'13232604267'),
('管理员a','123456',2,'13972397267')
/*其中飞机数据来自百度百科，载重量单位为吨，最大里程单位为千米*/
INSERT INTO 飞机表
VALUES
('空客319',0,8,120,68,7200),
('空客321',12,0,173,83,5600),
('空客330',20,16,255,230,7400),
('波音747',42,8,261,148,13890),
('波音777',54,15,299,178,12400)
INSERT INTO 飞机登记
VALUES
('001','空客319',2015-08-01,5,null),
('002','空客319',2015-08-01,5,null),
('003','空客319',2015-08-01,5,null),
('004','空客321',2016-09-01,4,null),
('005','空客321',2016-09-01,4,null),
('006','空客321',2016-09-01,4,null),
('007','空客330',2017-10-22,3,null),
('008','空客330',2017-10-22,3,null),
('009','空客330',2017-10-22,3,null),
('010','波音777',2014-01-01,6,null),
('011','波音777',2014-01-01,6,null),
('012','波音777',2014-01-01,6,null),
('013','波音747',2018-01-01,2,null),
('014','波音747' ,2018-01-01,2,null),
('015','波音747',2018-01-01,2,null)
INSERT INTO 航班(航班编号,出发机场代码,经停机场代码,到达机场代码,飞机编号)
VALUES
('0001','01',null,'02','001'),
('0002','01',null,'03','002'),
('0003','05','12','06','004'),
('0004','08',null,'09','005'),
('0005','08','12','10','007'),
('0006','12',null,'11','008'),
('0007','01',null,'02','009'),
('0008','01',null,'12','010'),
('0009','01',null,'11','011')
/*这里假设2019年1月1日为星期日，构建了2019年第一星期的飞行计划安排，可由此推至全年；这里为了配合订票表，已经对剩余票量做了处理*/
INSERT INTO 飞行计划安排(航班编号,计划出发时间,计划到达经停机场时间,计划从经停机场出发时间,计划到达时间,[经济舱（开始-到达）剩余座位],[商务舱（开始-到达）剩余座位],[头等舱（开始-到达）剩余座位],[经济舱（经停-到达）剩余座位],[商务舱（经停-到达）剩余座位],[头等舱（经停-到达）剩余座位],[经济舱（开始-经停）剩余座位],[商务舱（开始-经停）剩余座位],[头等舱（开始-经停）剩余座位],[票价（开始-到达，经济舱）],[票价（开始-经停，经济舱）],[票价（经停-到达，经济舱）],[票价（开始-到达，商务舱）],[票价（开始-经停，商务舱）],[票价（经停-到达，商务舱）],[票价（开始-到达，头等舱）],[票价（开始-经停，头等舱）],[票价（经停-到达，头等舱）])
VALUES
('0001','2019-01-01 08:00:00',null,null,'2019-01-01 10:30:00',118,0,8,null,null,null,null,null,null,500,null,null,800,null,null,1100,null,null),
('0001','2019-01-03 08:00:00',null,null,'2019-01-01 10:30:00',120,0,8,null,null,null,null,null,null,500,null,null,800,null,null,1100,null,null),
('0001','2019-01-05 08:00:00',null,null,'2019-01-01 10:30:00',120,0,8,null,null,null,null,null,null,500,null,null,800,null,null,1100,null,null),
('0002','2019-01-02 13:00:00',null,null,'2019-01-02 16:30:00',118,0,8,null,null,null,null,null,null,500,null,null,800,null,null,1100,null,null),
('0002','2019-01-06 13:00:00',null,null,'2019-01-06 16:30:00',120,0,7,null,null,null,null,null,null,500,null,null,800,null,null,1100,null,null),
('0003','2019-01-01 10:00:00','2019-01-01 12:00:00','2019-01-01 13:00:00','2019-01-01 14:30:00',172,12,0,171,12,0,172,12,0,500,300,200,800,600,500,1100,900,800),
('0003','2019-01-04 10:00:00','2019-01-04 12:00:00','2019-01-04 13:00:00','2019-01-04 14:30:00',173,12,0,173,12,0,173,12,0,500,300,200,800,600,500,1100,900,800),
('0003','2019-01-07 10:00:00','2019-01-07 12:00:00','2019-01-07 13:00:00','2019-01-07 14:30:00',173,12,0,173,12,0,173,12,0,500,300,200,800,600,500,1100,900,800),
('0004','2019-01-05 17:00:00',null,null,'2019-01-05 20:30:00',255,20,16,null,null,null,null,null,null,670,null,null,970,null,null,1270,null,null),
('0005','2019-01-01 15:00:00','2019-01-01 17:00:00','2019-01-01 18:00:00','2019-01-01 20:30:00',260,41,8,260,42,8,260,41,8,600,300,300,900,450,450,1200,600,600),
('0005','2019-01-04 15:00:00','2019-01-04 17:00:00','2019-01-04 18:00:00','2019-01-04 20:30:00',261,42,8,261,42,8,261,42,8,600,300,300,900,450,450,1200,600,600),
('0005','2019-01-07 15:00:00','2019-01-07 17:00:00','2019-01-07 18:00:00','2019-01-07 20:30:00',261,42,8,261,42,8,261,42,8,600,300,300,900,450,450,1200,600,600),
('0006','2019-01-02 08:00:00',null,null,'2019-01-02 13:30:00',255,20,16,null,null,null,null,null,null,670,null,null,1100,null,null,1570,null,null),
('0006','2019-01-06 08:00:00',null,null,'2019-01-06 13:30:00',255,20,16,null,null,null,null,null,null,670,null,null,1100,null,null,1570,null,null),
('0007','2019-01-03 09:30:00',null,null,'2019-01-03 13:00:00',299,54,15,null,null,null,null,null,null,670,null,null,970,null,null,1270,null,null),
('0008','2019-01-04 08:30:00',null,null,'2019-01-04 10:30:00',299,54,14,null,null,null,null,null,null,400,null,null,700,null,null,1000,null,null),
('0009','2019-01-06 11:30:00',null,null,'2019-01-03 15:00:00',297,54,15,null,null,null,null,null,null,570,null,null,870,null,null,1170,null,null)
GO
INSERT INTO 订票
VALUES
(1,'首都机场','双流机场','001','经济舱','余夏','李铁柱','男','老人','411002196402122371','13837439823'),
(1,'首都机场','双流机场','002','经济舱','地平线~','张三','男','残疾','411001199902122391','13837434523'),
(4,'首都机场','高崎机场','001','经济舱','余夏','李可可','女','小孩','311002201402122381','13823879823'),
(4,'首都机场','高崎机场','002','经济舱','余夏','李见','男','病人','311002197302122311','13823879823'),
(5,'首都机场','高崎机场','001','商务舱','余夏','朱欣','女','成人','311002197603132481','13928376273'),
(6,'太平机场','江北机场','001','经济舱','铁柱','李四','男','小孩','411001201702122371','13829386483'),
(6,'太平机场','黄沙机场','002','经济舱','铁柱','张光北','男','成人','321001198603022371','13922946483'),
(10,'虹桥机场','江北机场','001','经济舱','复旦大学','张武','男','病人','411001198412212371','15723126483'),
(10,'江北机场','中川机场','002','商务舱','复旦大学','李四','男','成人','411001198809212331','15629386483'),
(16,'首都机场','黄沙机场','001','头等舱','aa','温蒂','女','成人','312001199302122362','15702986483'),
(17,'首都机场','昌北机场','001','经济舱','郑州绿博园','王文倩','女','老人','411001196802222381','17827362817'),
(17,'首都机场','昌北机场','002','经济舱','上海市文化街','贺天成','男','成人','332001199409122372','13892836483')
GO