《MySQL数据操作与查询》- 综合项目 – 航空售票系统数据库杨明金的博客-

Mysql & SqlServer综合项目需求

1、系统整体功能

系统需支持以下功能：

维护客户信息、航班信息和票务信息

支持客户按多种条件组合查询航班信息和票务信息

支持客户根据票务信息订购机票

支持民航管理部门和航空公司查询、统计航班信息以及票务信息

2、系统的信息需求

每一家航空公司可以经营多条航班，即某条航班仅能隶属于某一家航空公司

飞机舱位有三个等级，它们分别为头等舱、商务舱和普通舱

每位乘客可以订购有余票的航班

系统支持票务信息查询，这些信息包括：舱位、座位和剩余座位数

3、表结构

【乘客表】Passenger

【航空公司表】Aircompany

【舱位表】Cabin

【航班表】Flight

【机票销售表】TicketSell

（1）创建数据库

 CREATE database Airticket;

（2）创建数据库表结构

 -- ---------------------------- -- Table structure for `aircompany` -- ---------------------------- DROP TABLE IF EXISTS `aircompany`; CREATE TABLE `aircompany` (   `companyID` int(11) NOT NULL AUTO_INCREMENT,   `companyName` varchar(50) NOT NULL,   `address` varchar(50) DEFAULT NULL,   `hotLine` varchar(50) DEFAULT NULL,   PRIMARY KEY (`companyID`) ); -- ---------------------------- -- Table structure for `cabin` -- ---------------------------- DROP TABLE IF EXISTS `cabin`; CREATE TABLE `cabin` (   `cabinID` int(11) NOT NULL AUTO_INCREMENT,   `flightID` varchar(50) DEFAULT NULL,   `flightDate` date DEFAULT NULL,   `grade` varchar(20) DEFAULT NULL COMMENT '头等舱、商务舱、经济舱',   `seats` int(11) DEFAULT NULL,   `availableSeats` int(11) DEFAULT NULL,   `fullPrice` decimal(8,2) DEFAULT NULL,   PRIMARY KEY (`cabinID`) ); -- ---------------------------- -- Table structure for `flight` -- ---------------------------- DROP TABLE IF EXISTS `flight`; CREATE TABLE `flight` (   `flightID` varchar(50) NOT NULL,   `companyID` int(11) DEFAULT NULL,   `model` varchar(50) DEFAULT NULL,   `fromCity` varchar(50) DEFAULT NULL,   `toCity` varchar(50) DEFAULT NULL,   `mileAge` int(11) DEFAULT NULL,   `departureTime` time DEFAULT NULL,   PRIMARY KEY (`fiightID`) ); -- ---------------------------- -- Table structure for `passenger` -- ---------------------------- DROP TABLE IF EXISTS `passenger`; CREATE TABLE `passenger` (   `identityID` varchar(20) NOT NULL DEFAULT '',   `name` varchar(50) NOT NULL,   `gender` varchar(4) DEFAULT NULL,   `birthday` date DEFAULT NULL,   `tel` varchar(20) DEFAULT NULL,   PRIMARY KEY (`identityID`) ); -- ---------------------------- -- Table structure for `ticketsell` -- ---------------------------- DROP TABLE IF EXISTS `ticketsell`; CREATE TABLE `ticketsell` (   `ordersID` int(11) NOT NULL AUTO_INCREMENT,   `identityID` varchar(20) DEFAULT NULL,   `flightID` varchar(50) DEFAULT NULL,   `flightDate` date DEFAULT NULL,   `Grade` varchar(20) DEFAULT NULL,   `seat` varchar(20) DEFAULT NULL,   `price` decimal(8,2) DEFAULT NULL,   `gate` varchar(20) DEFAULT NULL,   PRIMARY KEY (`ordersID`) );  

（3）新增和更新数据

 -- ---------------------------- -- Records of aircompany -- ---------------------------- INSERT INTO `aircompany` VALUES ('1', '中国南方航空', '广州市机场路278号95539', '95539'); INSERT INTO `aircompany` VALUES ('2', '中国东方航空', '上海市虹桥路2550号', '95530'); INSERT INTO `aircompany` VALUES ('3', '中国国际航空', '北京市顺义区天柱路30号', '95583'); INSERT INTO `aircompany` VALUES ('4', '海南航空', '海口市国兴大道7号', '950718'); INSERT INTO `aircompany` VALUES ('5', '深圳航空', '深圳宝安国际机场航站四路2033号', '95080'); INSERT INTO `aircompany` VALUES ('6', '厦门航空', '厦门市埭辽路22号', '95557'); INSERT INTO `aircompany` VALUES ('7', '吉祥航空', '上海市虹翔三路80号', '95520'); INSERT INTO `aircompany` VALUES ('8', '四川航空', '成都市武侯区二环路南四段六号', '400-830-0999'); INSERT INTO `aircompany` VALUES ('9', '山东航空', '济南二环东路5746号', '400-609-6777'); -- ---------------------------- -- Records of cabin -- ---------------------------- INSERT INTO `cabin` VALUES ('1', 'MU294', '2015-08-21', '头等舱', '4', '4', '2600.00'); INSERT INTO `cabin` VALUES ('2', 'MU294', '2015-08-21', '商务舱', '8', '8', '2200.00'); INSERT INTO `cabin` VALUES ('3', 'MU294', '2015-08-21', '经济舱', '172', '172', '1610.00'); INSERT INTO `cabin` VALUES ('6', 'MU2540', '2015-08-21', '头等舱', '2', '2', '2900.00'); INSERT INTO `cabin` VALUES ('7', 'MU2540', '2015-08-21', '商务舱', '6', '6', '2500.00'); INSERT INTO `cabin` VALUES ('8', 'MU2540', '2015-08-21', '经济舱', '154', '154', '1610.00'); INSERT INTO `cabin` VALUES ('9', 'MU2885', '2015-08-21', '头等舱', '2', '2', '2780.00'); INSERT INTO `cabin` VALUES ('10', 'MU2885', '2015-08-21', '商务舱', '6', '6', '2120.00'); INSERT INTO `cabin` VALUES ('11', 'MU2885', '2015-08-21', '经济舱', '162', '162', '1080.00'); INSERT INTO `cabin` VALUES ('12', 'CA1947', '2015-08-21', '头等舱', '8', '8', '2750.00'); INSERT INTO `cabin` VALUES ('13', 'CA1947', '2015-08-21', '商务舱', '12', '12', '2230.00'); INSERT INTO `cabin` VALUES ('14', 'MU2451', '2015-08-21', '经济舱', '270', '268', '1610.00'); INSERT INTO `cabin` VALUES ('15', 'MU2453', '2015-08-21', '商务舱', '16', null, '1580.00'); INSERT INTO `cabin` VALUES ('16', 'MU2453', '2015-08-21', '经济舱', '140', '138', '1080.00'); INSERT INTO `cabin` VALUES ('17', 'CZ3117', '2015-08-21', '头等舱', '4', null, '2100.00'); INSERT INTO `cabin` VALUES ('18', 'CZ3117', '2015-08-21', '商务舱', '12', null, '1620.00'); INSERT INTO `cabin` VALUES ('19', 'CZ3117', '2015-08-21', '经济舱', '142', '141', '1080.00'); INSERT INTO `cabin` VALUES ('21', 'MU2451', '2015-08-21', '商务舱', '16', null, '1480.00'); INSERT INTO `cabin` VALUES ('24', 'HU7188', '2015-08-21', '头等舱', '6', '5', '2320.00'); INSERT INTO `cabin` VALUES ('25', 'HU7188', '2015-08-21', '商务舱', '12', '11', '1730.00'); INSERT INTO `cabin` VALUES ('26', 'HU7188', '2015-08-21', '经济舱', '140', '137', '1080.00'); INSERT INTO `cabin` VALUES ('27', 'HU7291', '2015-08-21', '商务舱', '16', '16', '3420.00'); INSERT INTO `cabin` VALUES ('28', 'HU7291', '2015-08-21', '经济舱', '144', '144', '1740.00'); INSERT INTO `cabin` VALUES ('29', 'HU7309', '2015-08-21', '头等舱', '4', '4', '3830.00'); INSERT INTO `cabin` VALUES ('30', 'HU7309', '2015-08-21', '商务舱', '12', '12', '3290.00'); INSERT INTO `cabin` VALUES ('31', 'HU7309', '2015-08-21', '经济舱', '150', '150', '1740.00'); INSERT INTO `cabin` VALUES ('32', 'CZ6356', '2015-08-21', '头等舱', '6', '6', '3820.00'); INSERT INTO `cabin` VALUES ('33', 'CZ6356', '2015-08-21', '商务舱', '12', '12', '3180.00'); INSERT INTO `cabin` VALUES ('34', 'CZ6356', '2015-08-21', '经济舱', '139', '139', '1740.00'); INSERT INTO `cabin` VALUES ('35', 'ZH9438', '2015-08-21', '商务舱', '20', '20', '3260.00'); INSERT INTO `cabin` VALUES ('36', 'ZH9438', '2015-08-21', '经济舱', '156', '156', '1740.00'); INSERT INTO `cabin` VALUES ('37', 'ZH9516', '2015-08-21', '商务舱', '12', '12', '2880.00'); INSERT INTO `cabin` VALUES ('38', 'ZH9516', '2015-08-21', '经济舱', '170', '170', '1900.00'); INSERT INTO `cabin` VALUES ('39', 'ZH9602', '2015-08-21', '商务舱', '16', '16', '2760.00'); INSERT INTO `cabin` VALUES ('40', 'ZH9602', '2015-08-21', '经济舱', '158', '158', '1900.00'); INSERT INTO `cabin` VALUES ('41', 'MF8073', '2015-08-21', '商务舱', '9', '9', '2820.00'); INSERT INTO `cabin` VALUES ('42', 'MF8073', '2015-08-21', '经济舱', '151', '151', '1900.00'); INSERT INTO `cabin` VALUES ('43', 'MF8069', '2015-08-21', '头等舱', '6', '6', '3680.00'); INSERT INTO `cabin` VALUES ('44', 'MF8069', '2015-08-21', '商务舱', '16', '16', '2710.00'); INSERT INTO `cabin` VALUES ('45', 'MF8069', '2015-08-21', '经济舱', '140', '140', '1900.00'); INSERT INTO `cabin` VALUES ('46', 'SC1191', '2015-08-21', '商务舱', '12', '12', '3220.00'); INSERT INTO `cabin` VALUES ('47', 'SC1191', '2015-08-21', '经济舱', '146', '146', '1880.00'); INSERT INTO `cabin` VALUES ('51', 'SC4873', '2015-08-21', '商务舱', '9', '9', '3320.00'); INSERT INTO `cabin` VALUES ('52', 'SC4873', '2015-08-21', '经济舱', '148', '148', '1880.00'); INSERT INTO `cabin` VALUES ('53', '3U8948', '2015-08-21', '头等舱', '6', '6', '3880.00'); INSERT INTO `cabin` VALUES ('54', '3U8948', '2015-08-21', '商务舱', '12', '12', '3250.00'); INSERT INTO `cabin` VALUES ('55', '3U8948', '2015-08-21', '经济舱', '142', '142', '1880.00'); INSERT INTO `cabin` VALUES ('56', 'HO1252', '2015-08-21', '商务舱', '8', '8', '1680.00'); INSERT INTO `cabin` VALUES ('57', 'HO1252', '2015-08-21', '经济舱', '174', '174', '1130.00'); INSERT INTO `cabin` VALUES ('58', 'MU5102', '2015-08-21', '头等舱', '12', '12', '3890.00'); INSERT INTO `cabin` VALUES ('59', 'MU5102', '2015-08-21', '商务舱', '24', '24', '2680.00'); INSERT INTO `cabin` VALUES ('60', 'MU5102', '2015-08-21', '经济舱', '296', '296', '1130.00'); INSERT INTO `cabin` VALUES ('61', 'CZ3907', '2015-08-21', '头等舱', '9', '9', '3770.00'); INSERT INTO `cabin` VALUES ('62', 'CZ3907', '2015-08-21', '商务舱', '20', '20', '2630.00'); INSERT INTO `cabin` VALUES ('63', 'CZ3907', '2015-08-21', '经济舱', '302', '302', '1130.00'); INSERT INTO `cabin` VALUES ('64', 'HO1284', '2015-08-21', '商务舱', '12', '12', '2820.00'); INSERT INTO `cabin` VALUES ('65', 'HO1284', '2015-08-21', '经济舱', '170', '170', '1600.00'); INSERT INTO `cabin` VALUES ('66', 'CZ6553', '2015-08-21', '头等舱', '4', '4', '3890.00'); INSERT INTO `cabin` VALUES ('67', 'CZ6553', '2015-08-21', '商务舱', '16', '16', '2990.00'); INSERT INTO `cabin` VALUES ('68', 'CZ6553', '2015-08-21', '经济舱', '156', '156', '1600.00'); INSERT INTO `cabin` VALUES ('69', 'MU5680', '2015-08-21', '商务舱', '20', '20', '2780.00'); INSERT INTO `cabin` VALUES ('74', 'MU5680', '2015-08-21', '经济舱', '276', '276', '1600.00'); -- ---------------------------- -- Records of flight -- ---------------------------- INSERT INTO `flight` VALUES ('3U8948', '8', '空客320', '济南', '昆明', '2080', '21:30:00'); INSERT INTO `flight` VALUES ('3U8962', '8', '空客320', '上海', '成都', '2800', '11:35:00'); INSERT INTO `flight` VALUES ('CA1947', '3', '空客340', '上海', '成都', '2800', '08:03:00'); INSERT INTO `flight` VALUES ('CZ3117', '1', '波音738', '武汉', '北京', '1100', '08:06:00'); INSERT INTO `flight` VALUES ('CZ3907', '1', '空客333', '北京', '上海', '1130', '18:05:00'); INSERT INTO `flight` VALUES ('CZ6356', '1', '波音738', '海口', '郑州', '1873', '11:51:00'); INSERT INTO `flight` VALUES ('CZ6553', '1', '空客320', '长春', '上海', '1698', '16:30:00'); INSERT INTO `flight` VALUES ('HO1252', '7', '空客320', '北京', '上海', '1130', '06:40:00'); INSERT INTO `flight` VALUES ('HO1284', '7', '空客320', '长春', '上海', '1698', '14:45:00'); INSERT INTO `flight` VALUES ('HU7188', '4', '波音738', '武汉', '北京', '1100', '15:55:00'); INSERT INTO `flight` VALUES ('HU7291', '4', '波音738', '海口', '郑州', '1873', '13:05:00'); INSERT INTO `flight` VALUES ('HU7309', '4', '波音738', '海口', '郑州', '1873', '08:31:00'); INSERT INTO `flight` VALUES ('MF8069', '6', '波音738', '南宁', '沈阳', '2780', '08:03:00'); INSERT INTO `flight` VALUES ('MF8073', '6', '波音738', '厦门', '沈阳', '2242', '07:41:00'); INSERT INTO `flight` VALUES ('MU2451', '2', '波音738', '武汉', '北京', '1100', '09:04:00'); INSERT INTO `flight` VALUES ('MU2453', '2', '波音738', '武汉', '北京', '1100', '15:00:00'); INSERT INTO `flight` VALUES ('MU2540', '2', '波音738', '上海', '成都', '2800', '19:30:00'); INSERT INTO `flight` VALUES ('MU2885', '2', '空客320', '南京', '西安', '1104', '19:50:00'); INSERT INTO `flight` VALUES ('MU294', '2', '空客320', '上海', '成都', '2800', '11:15:00'); INSERT INTO `flight` VALUES ('MU5102', '2', '空客333', '北京', '上海', '1130', '08:39:00'); INSERT INTO `flight` VALUES ('MU5680', '2', '空客332', '长春', '上海', '1698', '13:50:00'); INSERT INTO `flight` VALUES ('SC1191', '9', '波音738', '济南', '昆明', '2080', '20:00:00'); INSERT INTO `flight` VALUES ('SC4873', '9', '波音738', '济南', '昆明', '2080', '08:45:00'); INSERT INTO `flight` VALUES ('ZH9438', '5', '空客320', '海口', '郑州', '1873', '17:00:00'); INSERT INTO `flight` VALUES ('ZH9516', '5', '空客320', '厦门', '沈阳', '2242', '16:20:00'); INSERT INTO `flight` VALUES ('ZH9602', '5', '空客320', '厦门', '沈阳', '2242', '14:55:00'); -- ---------------------------- -- Records of passenger -- ---------------------------- INSERT INTO `passenger` VALUES ('420105197610200916', '魏国兰', '女', '1983-09-12', '13827653456'); INSERT INTO `passenger` VALUES ('420106199208113738', '李慧娟', '女', '1992-08-11', '13967341256'); INSERT INTO `passenger` VALUES ('420107198304262089', '吕兰梦', '女', '1983-04-26', '13878292910'); INSERT INTO `passenger` VALUES ('420211197905122415', '郝嘉志', '男', '1979-05-12', '15945673421'); INSERT INTO `passenger` VALUES ('420506198304161675', '苏彦博', '男', '1983-04-16', '13867236598'); INSERT INTO `passenger` VALUES ('420712197303186681', '严雅可', '女', '1973-03-18', '15367212829'); INSERT INTO `passenger` VALUES ('421009197404104775', '傅明远', '男', '1974-04-10', '13578983521'); -- ---------------------------- -- Records of ticketsell -- ---------------------------- INSERT INTO `ticketsell` VALUES ('1', '420103198309125344', '3U8948', '2015-07-03', '经济舱', '8B', '780.00', '3'); INSERT INTO `ticketsell` VALUES ('2', '420103199806195830', 'CZ6356', '2015-09-12', '经济舱', '6E', '1050.00', '2'); INSERT INTO `ticketsell` VALUES ('3', '420104198703125881', 'HO1284', '2015-07-15', '商务舱', '2A', '2680.00', '6'); INSERT INTO `ticketsell` VALUES ('11', '420105197610200916', 'MF8069', '2015-07-18', '经济舱', '12E', '1250.00', '5'); INSERT INTO `ticketsell` VALUES ('12', '420106199208113738', 'MU2540', '2015-08-21', '经济舱', '9C', '760.00', '5'); INSERT INTO `ticketsell` VALUES ('13', '420106199208113738', 'MU5102', '2015-06-11', '商务舱', '3C', '2680.00', '3'); INSERT INTO `ticketsell` VALUES ('14', '420105197610200916', 'SC4873', '2015-07-22', '经济舱', '10F', '890.00', '4'); INSERT INTO `ticketsell` VALUES ('15', '420104198703125881', 'ZH9516', '2015-10-14', '商务舱', '2B', '2880.00', '4'); INSERT INTO `ticketsell` VALUES ('16', '420103199806195830', 'ZH9438', '2015-04-27', '经济舱', '13D', '890.00', '2'); INSERT INTO `ticketsell` VALUES ('17', '420103198309125344', 'MU5680', '2015-05-05', '经济舱', '7B', '920.00', '5'); INSERT INTO `ticketsell` VALUES ('18', '420103199806195830', 'MU2451', '2015-07-06', '经济舱', '9C', '460.00', '3'); INSERT INTO `ticketsell` VALUES ('19', '420106199208113738', 'MU2885', '2015-07-22', '经济舱', '8D', '620.00', '3'); INSERT INTO `ticketsell` VALUES ('20', '420105197610200916', 'HU7188', '2015-08-21', '经济舱', '13C', '560.00', '2'); INSERT INTO `ticketsell` VALUES ('21', '420103198309125344', 'MF8073', '2015-06-11', '经济舱', '5E', '1050.00', '5'); INSERT INTO `ticketsell` VALUES ('22', '420104198703125881', 'MU5102', '2015-07-22', '商务舱', '4B', '2880.00', '3'); INSERT INTO `ticketsell` VALUES ('23', '420104198703125881', 'HU7188', '2015-08-21', '经济舱', '8C', '560.00', '3'); INSERT INTO `ticketsell` VALUES ('24', '420103198309125344', 'HU7188', '2015-08-21', '经济舱', '9A', '560.00', '3'); INSERT INTO `ticketsell` VALUES ('25', '420104198703125881', 'MU2451', '2015-07-06', '经济舱', '6C', '460.00', '3'); INSERT INTO `ticketsell` VALUES ('26', '420105197610200916', 'MU2451', '2015-07-06', '经济舱', '6B', '460.00', '3'); INSERT INTO `ticketsell` VALUES ('27', '420103198309125344', 'MU2451', '2015-07-06', '经济舱', '5F', '460.00', '3'); INSERT INTO `ticketsell` VALUES ('28', '420106199208113738', 'CZ3117', '2015-08-29', '经济舱', '15C', '510.00', '5'); INSERT INTO `ticketsell` VALUES ('29', '420104198703125881', 'CZ3117', '2015-08-29', '经济舱', '12A', '510.00', '5'); INSERT INTO `ticketsell` VALUES ('30', '420103199806195830', 'MU2453', '2015-09-25', '经济舱', '7E', '420.00', '4'); INSERT INTO `ticketsell` VALUES ('31', '420103198309125344', 'MU2453', '2015-09-25', '经济舱', '8C', '420.00', '4'); INSERT INTO `ticketsell` VALUES ('32', '420103199806195830', 'HU7188', '2015-08-21', '头等舱', '1B', '2320.00', '3'); INSERT INTO `ticketsell` VALUES ('33', '420106199208113738', 'HU7188', '2015-08-21', '商务舱', '4A', '1730.00', '3'); INSERT INTO `ticketsell` VALUES ('34', '420211197905122415', 'MU2451', '2015-08-21', '经济舱', '8C', '470.00', '6'); INSERT INTO `ticketsell` VALUES ('35', '420506198304161675', 'MU2451', '2015-08-21', '经济舱', '13D', '470.00', '6'); INSERT INTO `ticketsell` VALUES ('36', '420107198304262089', 'CZ3117', '2015-08-21', '经济舱', '15E', '430.00', '3'); INSERT INTO `ticketsell` VALUES ('37', '420712197303186681', 'MU2453', '2015-08-21', '经济舱', '6F', '590.00', '4'); INSERT INTO `ticketsell` VALUES ('38', '421009197404104775', 'MU2453', '2015-08-21', '经济舱', '8A', '590.00', '4'); 

（4）添加外键约束

 -- ---------------------------- -- Table structure for `cabin` -- ---------------------------- CREATE TABLE `cabin` (   KEY `fk_flightID` (`flightID`),   CONSTRAINT `fk_flightID` FOREIGN KEY (`flightID`) REFERENCES `flight` (`flightID`) ); -- ---------------------------- -- Table structure for `flight` -- ---------------------------- CREATE TABLE `flight` (   KEY `companyID` (`companyID`),   CONSTRAINT `companyID` FOREIGN KEY (`companyID`) REFERENCES `aircompany` (`companyID`) ); -- ---------------------------- -- Table structure for `ticketsell` -- ---------------------------- CREATE TABLE `ticketsell` (   KEY `fk_identityID` (`identityID`) USING BTREE,   KEY `flightID` (`flightID`),   CONSTRAINT `fk_identityID` FOREIGN KEY (`identityID`) REFERENCES `passenger` (`identityID`),   CONSTRAINT `flightID` FOREIGN KEY (`flightID`) REFERENCES `flight` (`flightID`) ); 

4、义务数据查询应用

（1）查询从武汉飞往北京的航班信息，要求显示航班号、航空公司名称、机型、始发地、目的地、里程数和起飞时间，按照起飞时间升序显示

 SELECT flightid 航班号, companyname 航空公司名称, model 机型, fromcity 始发地, tocity 目的地, mileage 里程数, departuretime 起飞时间  FROM flight f INNER JOIN aircompany a ON f.companyid=a.companyid WHERE fromcity='武汉' AND tocity='北京' ORDER BY 起飞时间

（2）统计每一家航空公司所经营航线的平均里程，按平均里程升序显示

 SELECT companyname 航空公司名称, mileage 里程数 FROM flight f,aircompany a WHERE f.companyid=a.companyid GROUP BY 航空公司名称 ORDER BY 里程数

（3）查询2015年8月21日各航班不同舱位等级的机票折扣信息，要求显示航班号、舱位等级、全价、票面价、折扣

 SELECT f.flightid 航班号, c.grade 舱位等级, fullprice 全价, price 票面价, TRUNCATE(price/fullprice,2) 折扣 FROM flight f,cabin c,ticketsell t WHERE f.flightid=c.flightid AND f.flightid=t.flightid GROUP BY 航班号,舱位等级

（4）查询旅客“李慧娟”所有的购票信息，要求输出航班号、出发城市、目的城市、机票价格、飞行日期和起飞时间

 SELECT f.flightid 航班号, tocity 出发城市, fromcity 目的城市, price 价格, flightdate 飞行日期, departuretime 起飞时间  FROM flight f,ticketsell t,passenger p WHERE f.flightid=t.flightid AND t.identityid=p.identityid AND name='李慧娟'

（5）统计各航班的乘客人数和机票销售额，按照乘客人数和机票销售额升序显示

 SELECT f.flightid 航班, COUNT(*) 乘客人数, sum(price) 机票销售额  FROM passenger p,ticketsell t,flight f WHERE t.identityid=p.identityid AND t.flightid=f.flightid GROUP BY 航班 ORDER BY 乘客人数,机票销售额

（6）查询飞往上海的所有航班信息。

 SELECT * FROM flight f WHERE tocity='上海'

（7）查询飞往上海的航班数、最长航线里程数、最短航线里程数、平均航线里程数以及航线总里程数。

 SELECT COUNT(*) 航班数, mileAge 最长航线里程数, MAX(mileAge) 最短航线里程数, AVG(mileage) 平均航线里程数,SUM(mileage) 航线总里程数 FROM flight WHERE tocity='上海'

（8）统计每一家航空公司的平均航线里程数。

 SELECT companyname 航空公司, AVG(mileage) 平均航线里程数 FROM flight f,aircompany a WHERE a.companyid=f.companyid GROUP BY 航空公司 ORDER BY 平均航线里程数

（9）按航空公司显示所属航线的平均里程数大于800公里的分组信息。

 SELECT companyname 航空公司, flightid 航班, AVG(mileage) 平均里程  FROM flight f,aircompany a WHERE a.companyid=f.companyid GROUP BY 航空公司,航班 HAVING 平均里程>800

（10）查询乘客“郝琼琼”飞往上海的所有票务信息。要求输出航班号、出发城市、目的城市、机票价格、出行日期和起飞时间。

 SELECT name 乘客, f.flightid 航班号, fromcity 出发城市, tocity 目的城市, price 机票价格, flightdate 出行日期, departuretime 起飞时间  FROM passenger p,flight f,ticketsell t WHERE p.identityid=t.identityid AND t.flightid=f.flightid AND name='郝琼琼'

（11）统计某天所有飞往上海的票务信息。要求输出每个航班、不同舱位等级的机票销售信息，显示信息包括：航班号、舱位等级、总票数、已售票数和剩余票数。

 SELECT f.flightid 航班号, c.grade 舱位等级, SUM(seats) 总票数,SUM(seats-availableseats) 已售票数, SUM(availableseats) 剩余票数  FROM ticketsell t,flight f,cabin c WHERE t.flightid=f.flightid AND f.flightid=c.flightid GROUP BY 航班号,舱位等级

（12）统计中国东方航空公司所属航线的乘客总人数和机票销售总额，按乘客数升序显示。

 SELECT companyname 航空公司,f.flightid 航线, COUNT(*) 乘客总数, SUM(price) 机票销售总额 FROM flight f,passenger p,ticketsell t,aircompany a WHERE f.flightid=t.flightid AND t.identityid=p.identityid AND f.companyid=a.companyid AND companyname='中国东方航空' GROUP BY 航空公司,航线 ORDER BY 乘客总数

（13）按天统计每一家航空公司所属航线的乘客总人数和机票销售总额，按乘客数、机票销售总额升序显示。

 SELECT companyname 航空公司,f.flightid 航线, COUNT(*) 乘客总数, SUM(price) 机票销售总额 FROM flight f,passenger p,ticketsell t,aircompany a WHERE f.flightid=t.flightid AND t.identityid=p.identityid AND f.companyid=a.companyid AND companyname='中国东方航空' GROUP BY 航空公司,航线 ORDER BY 乘客总数,机票销售总额

一步导入完整代码：

 /* Navicat MySQL Data Transfer  Source Server         : test Source Server Version : 50646 Source Host           : localhost:3306 Source Database       : ats  Target Server Type    : MYSQL Target Server Version : 50646 File Encoding         : 65001  Date: 2020-04-19 16:59:57 */  SET FOREIGN_KEY_CHECKS=0;  -- ---------------------------- -- Table structure for `aircompany` -- ---------------------------- DROP TABLE IF EXISTS `aircompany`; CREATE TABLE `aircompany` (   `companyID` int(11) NOT NULL AUTO_INCREMENT,   `companyName` varchar(50) NOT NULL,   `address` varchar(50) DEFAULT NULL,   `hotLine` varchar(50) DEFAULT NULL,   PRIMARY KEY (`companyID`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;  -- ---------------------------- -- Records of aircompany -- ---------------------------- INSERT INTO `aircompany` VALUES ('1', '中国南方航空', '广州市机场路278号95539', '95539'); INSERT INTO `aircompany` VALUES ('2', '中国东方航空', '上海市虹桥路2550号', '95530'); INSERT INTO `aircompany` VALUES ('3', '中国国际航空', '北京市顺义区天柱路30号', '95583'); INSERT INTO `aircompany` VALUES ('4', '海南航空', '海口市国兴大道7号', '950718'); INSERT INTO `aircompany` VALUES ('5', '深圳航空', '深圳宝安国际机场航站四路2033号', '95080'); INSERT INTO `aircompany` VALUES ('6', '厦门航空', '厦门市埭辽路22号', '95557'); INSERT INTO `aircompany` VALUES ('7', '吉祥航空', '上海市虹翔三路80号', '95520'); INSERT INTO `aircompany` VALUES ('8', '四川航空', '成都市武侯区二环路南四段六号', '400-830-0999'); INSERT INTO `aircompany` VALUES ('9', '山东航空', '济南二环东路5746号', '400-609-6777');  -- ---------------------------- -- Table structure for `cabin` -- ---------------------------- DROP TABLE IF EXISTS `cabin`; CREATE TABLE `cabin` (   `cabinID` int(11) NOT NULL AUTO_INCREMENT,   `flightID` varchar(50) DEFAULT NULL,   `flightDate` date DEFAULT NULL,   `grade` varchar(20) DEFAULT NULL COMMENT '头等舱、商务舱、经济舱',   `seats` int(11) DEFAULT NULL,   `availableSeats` int(11) DEFAULT NULL,   `fullPrice` decimal(8,2) DEFAULT NULL,   PRIMARY KEY (`cabinID`),   KEY `fk_flightID` (`flightID`),   CONSTRAINT `fk_flightID` FOREIGN KEY (`flightID`) REFERENCES `flight` (`flightID`) ) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8;  -- ---------------------------- -- Records of cabin -- ---------------------------- INSERT INTO `cabin` VALUES ('1', 'MU294', '2015-08-21', '头等舱', '4', '4', '2600.00'); INSERT INTO `cabin` VALUES ('2', 'MU294', '2015-08-21', '商务舱', '8', '8', '2200.00'); INSERT INTO `cabin` VALUES ('3', 'MU294', '2015-08-21', '经济舱', '172', '172', '1610.00'); INSERT INTO `cabin` VALUES ('6', 'MU2540', '2015-08-21', '头等舱', '2', '2', '2900.00'); INSERT INTO `cabin` VALUES ('7', 'MU2540', '2015-08-21', '商务舱', '6', '6', '2500.00'); INSERT INTO `cabin` VALUES ('8', 'MU2540', '2015-08-21', '经济舱', '154', '154', '1610.00'); INSERT INTO `cabin` VALUES ('9', 'MU2885', '2015-08-21', '头等舱', '2', '2', '2780.00'); INSERT INTO `cabin` VALUES ('10', 'MU2885', '2015-08-21', '商务舱', '6', '6', '2120.00'); INSERT INTO `cabin` VALUES ('11', 'MU2885', '2015-08-21', '经济舱', '162', '162', '1080.00'); INSERT INTO `cabin` VALUES ('12', 'CA1947', '2015-08-21', '头等舱', '8', '8', '2750.00'); INSERT INTO `cabin` VALUES ('13', 'CA1947', '2015-08-21', '商务舱', '12', '12', '2230.00'); INSERT INTO `cabin` VALUES ('14', 'MU2451', '2015-08-21', '经济舱', '270', '268', '1610.00'); INSERT INTO `cabin` VALUES ('15', 'MU2453', '2015-08-21', '商务舱', '16', null, '1580.00'); INSERT INTO `cabin` VALUES ('16', 'MU2453', '2015-08-21', '经济舱', '140', '138', '1080.00'); INSERT INTO `cabin` VALUES ('17', 'CZ3117', '2015-08-21', '头等舱', '4', null, '2100.00'); INSERT INTO `cabin` VALUES ('18', 'CZ3117', '2015-08-21', '商务舱', '12', null, '1620.00'); INSERT INTO `cabin` VALUES ('19', 'CZ3117', '2015-08-21', '经济舱', '142', '141', '1080.00'); INSERT INTO `cabin` VALUES ('21', 'MU2451', '2015-08-21', '商务舱', '16', null, '1480.00'); INSERT INTO `cabin` VALUES ('24', 'HU7188', '2015-08-21', '头等舱', '6', '5', '2320.00'); INSERT INTO `cabin` VALUES ('25', 'HU7188', '2015-08-21', '商务舱', '12', '11', '1730.00'); INSERT INTO `cabin` VALUES ('26', 'HU7188', '2015-08-21', '经济舱', '140', '137', '1080.00'); INSERT INTO `cabin` VALUES ('27', 'HU7291', '2015-08-21', '商务舱', '16', '16', '3420.00'); INSERT INTO `cabin` VALUES ('28', 'HU7291', '2015-08-21', '经济舱', '144', '144', '1740.00'); INSERT INTO `cabin` VALUES ('29', 'HU7309', '2015-08-21', '头等舱', '4', '4', '3830.00'); INSERT INTO `cabin` VALUES ('30', 'HU7309', '2015-08-21', '商务舱', '12', '12', '3290.00'); INSERT INTO `cabin` VALUES ('31', 'HU7309', '2015-08-21', '经济舱', '150', '150', '1740.00'); INSERT INTO `cabin` VALUES ('32', 'CZ6356', '2015-08-21', '头等舱', '6', '6', '3820.00'); INSERT INTO `cabin` VALUES ('33', 'CZ6356', '2015-08-21', '商务舱', '12', '12', '3180.00'); INSERT INTO `cabin` VALUES ('34', 'CZ6356', '2015-08-21', '经济舱', '139', '139', '1740.00'); INSERT INTO `cabin` VALUES ('35', 'ZH9438', '2015-08-21', '商务舱', '20', '20', '3260.00'); INSERT INTO `cabin` VALUES ('36', 'ZH9438', '2015-08-21', '经济舱', '156', '156', '1740.00'); INSERT INTO `cabin` VALUES ('37', 'ZH9516', '2015-08-21', '商务舱', '12', '12', '2880.00'); INSERT INTO `cabin` VALUES ('38', 'ZH9516', '2015-08-21', '经济舱', '170', '170', '1900.00'); INSERT INTO `cabin` VALUES ('39', 'ZH9602', '2015-08-21', '商务舱', '16', '16', '2760.00'); INSERT INTO `cabin` VALUES ('40', 'ZH9602', '2015-08-21', '经济舱', '158', '158', '1900.00'); INSERT INTO `cabin` VALUES ('41', 'MF8073', '2015-08-21', '商务舱', '9', '9', '2820.00'); INSERT INTO `cabin` VALUES ('42', 'MF8073', '2015-08-21', '经济舱', '151', '151', '1900.00'); INSERT INTO `cabin` VALUES ('43', 'MF8069', '2015-08-21', '头等舱', '6', '6', '3680.00'); INSERT INTO `cabin` VALUES ('44', 'MF8069', '2015-08-21', '商务舱', '16', '16', '2710.00'); INSERT INTO `cabin` VALUES ('45', 'MF8069', '2015-08-21', '经济舱', '140', '140', '1900.00'); INSERT INTO `cabin` VALUES ('46', 'SC1191', '2015-08-21', '商务舱', '12', '12', '3220.00'); INSERT INTO `cabin` VALUES ('47', 'SC1191', '2015-08-21', '经济舱', '146', '146', '1880.00'); INSERT INTO `cabin` VALUES ('51', 'SC4873', '2015-08-21', '商务舱', '9', '9', '3320.00'); INSERT INTO `cabin` VALUES ('52', 'SC4873', '2015-08-21', '经济舱', '148', '148', '1880.00'); INSERT INTO `cabin` VALUES ('53', '3U8948', '2015-08-21', '头等舱', '6', '6', '3880.00'); INSERT INTO `cabin` VALUES ('54', '3U8948', '2015-08-21', '商务舱', '12', '12', '3250.00'); INSERT INTO `cabin` VALUES ('55', '3U8948', '2015-08-21', '经济舱', '142', '142', '1880.00'); INSERT INTO `cabin` VALUES ('56', 'HO1252', '2015-08-21', '商务舱', '8', '8', '1680.00'); INSERT INTO `cabin` VALUES ('57', 'HO1252', '2015-08-21', '经济舱', '174', '174', '1130.00'); INSERT INTO `cabin` VALUES ('58', 'MU5102', '2015-08-21', '头等舱', '12', '12', '3890.00'); INSERT INTO `cabin` VALUES ('59', 'MU5102', '2015-08-21', '商务舱', '24', '24', '2680.00'); INSERT INTO `cabin` VALUES ('60', 'MU5102', '2015-08-21', '经济舱', '296', '296', '1130.00'); INSERT INTO `cabin` VALUES ('61', 'CZ3907', '2015-08-21', '头等舱', '9', '9', '3770.00'); INSERT INTO `cabin` VALUES ('62', 'CZ3907', '2015-08-21', '商务舱', '20', '20', '2630.00'); INSERT INTO `cabin` VALUES ('63', 'CZ3907', '2015-08-21', '经济舱', '302', '302', '1130.00'); INSERT INTO `cabin` VALUES ('64', 'HO1284', '2015-08-21', '商务舱', '12', '12', '2820.00'); INSERT INTO `cabin` VALUES ('65', 'HO1284', '2015-08-21', '经济舱', '170', '170', '1600.00'); INSERT INTO `cabin` VALUES ('66', 'CZ6553', '2015-08-21', '头等舱', '4', '4', '3890.00'); INSERT INTO `cabin` VALUES ('67', 'CZ6553', '2015-08-21', '商务舱', '16', '16', '2990.00'); INSERT INTO `cabin` VALUES ('68', 'CZ6553', '2015-08-21', '经济舱', '156', '156', '1600.00'); INSERT INTO `cabin` VALUES ('69', 'MU5680', '2015-08-21', '商务舱', '20', '20', '2780.00'); INSERT INTO `cabin` VALUES ('74', 'MU5680', '2015-08-21', '经济舱', '276', '276', '1600.00');  -- ---------------------------- -- Table structure for `flight` -- ---------------------------- DROP TABLE IF EXISTS `flight`; CREATE TABLE `flight` (   `flightID` varchar(50) NOT NULL,   `companyID` int(11) DEFAULT NULL,   `model` varchar(50) DEFAULT NULL,   `fromCity` varchar(50) DEFAULT NULL,   `toCity` varchar(50) DEFAULT NULL,   `mileAge` int(11) DEFAULT NULL,   `departureTime` time DEFAULT NULL,   PRIMARY KEY (`flightID`),   KEY `companyID` (`companyID`),   CONSTRAINT `companyID` FOREIGN KEY (`companyID`) REFERENCES `aircompany` (`companyID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  -- ---------------------------- -- Records of flight -- ---------------------------- INSERT INTO `flight` VALUES ('3U8948', '8', '空客320', '济南', '昆明', '2080', '21:30:00'); INSERT INTO `flight` VALUES ('3U8962', '8', '空客320', '上海', '成都', '2800', '11:35:00'); INSERT INTO `flight` VALUES ('CA1947', '3', '空客340', '上海', '成都', '2800', '08:03:00'); INSERT INTO `flight` VALUES ('CZ3117', '1', '波音738', '武汉', '北京', '1100', '08:06:00'); INSERT INTO `flight` VALUES ('CZ3907', '1', '空客333', '北京', '上海', '1130', '18:05:00'); INSERT INTO `flight` VALUES ('CZ6356', '1', '波音738', '海口', '郑州', '1873', '11:51:00'); INSERT INTO `flight` VALUES ('CZ6553', '1', '空客320', '长春', '上海', '1698', '16:30:00'); INSERT INTO `flight` VALUES ('HO1252', '7', '空客320', '北京', '上海', '1130', '06:40:00'); INSERT INTO `flight` VALUES ('HO1284', '7', '空客320', '长春', '上海', '1698', '14:45:00'); INSERT INTO `flight` VALUES ('HU7188', '4', '波音738', '武汉', '北京', '1100', '15:55:00'); INSERT INTO `flight` VALUES ('HU7291', '4', '波音738', '海口', '郑州', '1873', '13:05:00'); INSERT INTO `flight` VALUES ('HU7309', '4', '波音738', '海口', '郑州', '1873', '08:31:00'); INSERT INTO `flight` VALUES ('MF8069', '6', '波音738', '南宁', '沈阳', '2780', '08:03:00'); INSERT INTO `flight` VALUES ('MF8073', '6', '波音738', '厦门', '沈阳', '2242', '07:41:00'); INSERT INTO `flight` VALUES ('MU2451', '2', '波音738', '武汉', '北京', '1100', '09:04:00'); INSERT INTO `flight` VALUES ('MU2453', '2', '波音738', '武汉', '北京', '1100', '15:00:00'); INSERT INTO `flight` VALUES ('MU2540', '2', '波音738', '上海', '成都', '2800', '19:30:00'); INSERT INTO `flight` VALUES ('MU2885', '2', '空客320', '南京', '西安', '1104', '19:50:00'); INSERT INTO `flight` VALUES ('MU294', '2', '空客320', '上海', '成都', '2800', '11:15:00'); INSERT INTO `flight` VALUES ('MU5102', '2', '空客333', '北京', '上海', '1130', '08:39:00'); INSERT INTO `flight` VALUES ('MU5680', '2', '空客332', '长春', '上海', '1698', '13:50:00'); INSERT INTO `flight` VALUES ('SC1191', '9', '波音738', '济南', '昆明', '2080', '20:00:00'); INSERT INTO `flight` VALUES ('SC4873', '9', '波音738', '济南', '昆明', '2080', '08:45:00'); INSERT INTO `flight` VALUES ('ZH9438', '5', '空客320', '海口', '郑州', '1873', '17:00:00'); INSERT INTO `flight` VALUES ('ZH9516', '5', '空客320', '厦门', '沈阳', '2242', '16:20:00'); INSERT INTO `flight` VALUES ('ZH9602', '5', '空客320', '厦门', '沈阳', '2242', '14:55:00');  -- ---------------------------- -- Table structure for `passenger` -- ---------------------------- DROP TABLE IF EXISTS `passenger`; CREATE TABLE `passenger` (   `identityID` varchar(20) NOT NULL DEFAULT '',   `name` varchar(50) NOT NULL,   `gender` varchar(4) DEFAULT NULL,   `birthday` date DEFAULT NULL,   `tel` varchar(20) DEFAULT NULL,   PRIMARY KEY (`identityID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  -- ---------------------------- -- Records of passenger -- ---------------------------- INSERT INTO `passenger` VALUES ('420103198309125344', '刘亚蒙', '男', '1976-10-20', '13565232345'); INSERT INTO `passenger` VALUES ('420103199806195830', '郝琼琼', '女', '1987-03-12', '15334564321'); INSERT INTO `passenger` VALUES ('420104198703125881', '雷亚波', '男', '1988-06-19', '13686035678'); INSERT INTO `passenger` VALUES ('420105197610200916', '魏国兰', '女', '1983-09-12', '13827653456'); INSERT INTO `passenger` VALUES ('420106199208113738', '李慧娟', '女', '1992-08-11', '13967341256'); INSERT INTO `passenger` VALUES ('420107198304262089', '吕兰梦', '女', '1983-04-26', '13878292910'); INSERT INTO `passenger` VALUES ('420211197905122415', '郝嘉志', '男', '1979-05-12', '15945673421'); INSERT INTO `passenger` VALUES ('420506198304161675', '苏彦博', '男', '1983-04-16', '13867236598'); INSERT INTO `passenger` VALUES ('420712197303186681', '严雅可', '女', '1973-03-18', '15367212829'); INSERT INTO `passenger` VALUES ('421009197404104775', '傅明远', '男', '1974-04-10', '13578983521');  -- ---------------------------- -- Table structure for `ticketsell` -- ---------------------------- DROP TABLE IF EXISTS `ticketsell`; CREATE TABLE `ticketsell` (   `ordersID` int(11) NOT NULL AUTO_INCREMENT,   `identityID` varchar(20) DEFAULT NULL,   `flightID` varchar(50) DEFAULT NULL,   `flightDate` date DEFAULT NULL,   `Grade` varchar(20) DEFAULT NULL,   `seat` varchar(20) DEFAULT NULL,   `price` decimal(8,2) DEFAULT NULL,   `gate` varchar(20) DEFAULT NULL,   PRIMARY KEY (`ordersID`),   KEY `fk_identityID` (`identityID`) USING BTREE,   KEY `flightID` (`flightID`),   CONSTRAINT `fk_identityID` FOREIGN KEY (`identityID`) REFERENCES `passenger` (`identityID`),   CONSTRAINT `flightID` FOREIGN KEY (`flightID`) REFERENCES `flight` (`flightID`) ) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8;  -- ---------------------------- -- Records of ticketsell -- ---------------------------- INSERT INTO `ticketsell` VALUES ('1', '420103198309125344', '3U8948', '2015-07-03', '经济舱', '8B', '780.00', '3'); INSERT INTO `ticketsell` VALUES ('2', '420103199806195830', 'CZ6356', '2015-09-12', '经济舱', '6E', '1050.00', '2'); INSERT INTO `ticketsell` VALUES ('3', '420104198703125881', 'HO1284', '2015-07-15', '商务舱', '2A', '2680.00', '6'); INSERT INTO `ticketsell` VALUES ('11', '420105197610200916', 'MF8069', '2015-07-18', '经济舱', '12E', '1250.00', '5'); INSERT INTO `ticketsell` VALUES ('12', '420106199208113738', 'MU2540', '2015-08-21', '经济舱', '9C', '760.00', '5'); INSERT INTO `ticketsell` VALUES ('13', '420106199208113738', 'MU5102', '2015-06-11', '商务舱', '3C', '2680.00', '3'); INSERT INTO `ticketsell` VALUES ('14', '420105197610200916', 'SC4873', '2015-07-22', '经济舱', '10F', '890.00', '4'); INSERT INTO `ticketsell` VALUES ('15', '420104198703125881', 'ZH9516', '2015-10-14', '商务舱', '2B', '2880.00', '4'); INSERT INTO `ticketsell` VALUES ('16', '420103199806195830', 'ZH9438', '2015-04-27', '经济舱', '13D', '890.00', '2'); INSERT INTO `ticketsell` VALUES ('17', '420103198309125344', 'MU5680', '2015-05-05', '经济舱', '7B', '920.00', '5'); INSERT INTO `ticketsell` VALUES ('18', '420103199806195830', 'MU2451', '2015-07-06', '经济舱', '9C', '460.00', '3'); INSERT INTO `ticketsell` VALUES ('19', '420106199208113738', 'MU2885', '2015-07-22', '经济舱', '8D', '620.00', '3'); INSERT INTO `ticketsell` VALUES ('20', '420105197610200916', 'HU7188', '2015-08-21', '经济舱', '13C', '560.00', '2'); INSERT INTO `ticketsell` VALUES ('21', '420103198309125344', 'MF8073', '2015-06-11', '经济舱', '5E', '1050.00', '5'); INSERT INTO `ticketsell` VALUES ('22', '420104198703125881', 'MU5102', '2015-07-22', '商务舱', '4B', '2880.00', '3'); INSERT INTO `ticketsell` VALUES ('23', '420104198703125881', 'HU7188', '2015-08-21', '经济舱', '8C', '560.00', '3'); INSERT INTO `ticketsell` VALUES ('24', '420103198309125344', 'HU7188', '2015-08-21', '经济舱', '9A', '560.00', '3'); INSERT INTO `ticketsell` VALUES ('25', '420104198703125881', 'MU2451', '2015-07-06', '经济舱', '6C', '460.00', '3'); INSERT INTO `ticketsell` VALUES ('26', '420105197610200916', 'MU2451', '2015-07-06', '经济舱', '6B', '460.00', '3'); INSERT INTO `ticketsell` VALUES ('27', '420103198309125344', 'MU2451', '2015-07-06', '经济舱', '5F', '460.00', '3'); INSERT INTO `ticketsell` VALUES ('28', '420106199208113738', 'CZ3117', '2015-08-29', '经济舱', '15C', '510.00', '5'); INSERT INTO `ticketsell` VALUES ('29', '420104198703125881', 'CZ3117', '2015-08-29', '经济舱', '12A', '510.00', '5'); INSERT INTO `ticketsell` VALUES ('30', '420103199806195830', 'MU2453', '2015-09-25', '经济舱', '7E', '420.00', '4'); INSERT INTO `ticketsell` VALUES ('31', '420103198309125344', 'MU2453', '2015-09-25', '经济舱', '8C', '420.00', '4'); INSERT INTO `ticketsell` VALUES ('32', '420103199806195830', 'HU7188', '2015-08-21', '头等舱', '1B', '2320.00', '3'); INSERT INTO `ticketsell` VALUES ('33', '420106199208113738', 'HU7188', '2015-08-21', '商务舱', '4A', '1730.00', '3'); INSERT INTO `ticketsell` VALUES ('34', '420211197905122415', 'MU2451', '2015-08-21', '经济舱', '8C', '470.00', '6'); INSERT INTO `ticketsell` VALUES ('35', '420506198304161675', 'MU2451', '2015-08-21', '经济舱', '13D', '470.00', '6'); INSERT INTO `ticketsell` VALUES ('36', '420107198304262089', 'CZ3117', '2015-08-21', '经济舱', '15E', '430.00', '3'); INSERT INTO `ticketsell` VALUES ('37', '420712197303186681', 'MU2453', '2015-08-21', '经济舱', '6F', '590.00', '4'); INSERT INTO `ticketsell` VALUES ('38', '421009197404104775', 'MU2453', '2015-08-21', '经济舱', '8A', '590.00', '4');