2023年7月6日发(作者：)

数据库课程设计——MySQL⽕车票售票系统数据库课程设计——⽕车票售票系统很烦数据库课程设计？阅读本篇⽂章会让你不那么烦躁~由于数据库部署在了我⾃⼰的服务器上，所以如果各位想要修改源代码，或者搭建⾃⼰的系统，请⾃⾏根据下⽂的表结构搭建数据库，并且修改中数据库连接的部分。其中train-tickets-back-end为后台⼯程⽂件。train-tickets-front-end为前端⼯程⽂件。系统开发平台：开发⼯具：eclipse，webstorm开发语⾔：Java，HTML，CSS，JavaScript数据库：MySQL中间件：tomcat 8.0后台框架：SpringMVC前端框架：React（前端只是做了⼀个展⽰，不是很重要）系统功能：在本系统中，前端和后台使⽤json进⾏数据交互，下⾯的每⼀个功能均对应着⼀个接⼝，括号中为接⼝的URL，具体实现可以参考源代码。⽤户层⾯：添加⽤户，即注册。(/register)根据⽤户名和密码获取⽤户的所有信息，即登录。 (/login)修改⽤户。 (/updateUser)订单层⾯：添加订单，在本系统中添加订单就意味着锁定座位。(/lockSeat)修改订单。(/updateOrder)删除订单。(/deleteOrder)根据⽤户ID获取订单信息。(/queryOrder)⽕车层⾯：根据车站获取列车ID。(/getTrainByStop)根据列车ID获取列车信息。(/getTrainByTrainId)列车换乘查询。(/getTransfer)查询列车座位剩余。(/getRemainSeats)查询所有通列车的城市。(/getAllCities)数据库设计（表结构加表数据）：⽤户表：⽕车表：⽕车经停车站表：⽕车状态表（是否停运）：⽕车座次表：订单表：难点SQL语句设计：以下SQL语句均基于上述表结构进⾏的设计，数据库名为12307//根据起⾄车站查询⽕车select _id, train_name, _id as start_index, _id as end_index, n_name as start, n_name as end, _time as start_time, _time as end_time, (e - e) as mileage

from `12307`.stop as a,`12307`.stop as b,`12307`.train as c

where _name = '沈阳' and _name = '济南' and _id < _id and _id = _id and _id = _id and _id not in ( select train_id from `12307`.status where date = '2019-12-12' and status = '停运')//根据起⾄车站查询换乘⽕车select _id as first_tid, _id as second_tid, _name as first_tname, _name as second_tname, n_name as transfer_stationfrom `12307`.stop as a, `12307`.stop as b, `12307`.stop as c, `12307`.stop as d, `12307`.train as e, `12307`.train as fwhere _name = '⼴州' and _name = '长春' and _id = _id and n_name = n_name and _id = _id and _id <> _id and _id < _id and _id < _id and _time < _timeand _id not in (select train_id from `12307`.status where date = '2019-10-10' and status = '停运')and _id not in (select train_id from `12307`.status where date = '2019-10-10' and status = '停运')and _id = _id and _id = _id//查询余票数量select _id, _type, count(_type)from `12307`.train as t, `12307`.seat as swhere _id = 5 and _id = _id and _id not in ( select seat_id from `12307`.order where train_id = 5 and date = '2019-01-02' and ((start_stop_id >= 2 and start_stop_id < 3) or (end_stop_id > 2 and end_stop_id <= 3)))group by _type//查询余票具体座位情况select _id, _id, ge, _type, _locationfrom `12307`.train as t, `12307`.seat as swhere _id = 1 and _id = _id and _id not in ( select seat_id from `12307`.order where train_id = 1 and date = '2019-01-01' and ((start_stop_id >= 0 and start_stop_id < 2) or (end_stop_id > 0 and end_stop_id <= 2)))//锁定座位（实际上就是插⼊⼀条订单）insert into `12307`.order (user_id, passenger_id, user_name, train_id, carriage, seat_type, seat_id, seat_location, start_stop_id, start_station_name, end_stop_id, end_station_name, date, create_at, status) values ('1', '000001', 'user1', '1', '1', '⼀等座', '0', '01A', '0', '沈阳北', 2, '深圳北', '2019-01-01', '2018-12-31 12:00:00', '未付款')重点后台接⼝及返回样例：注：所有请求的参数都为string类型，请求类型均为post请求，请求均可以跨域。1、/login请求参数：{ username,

password}返回结果：{content: "{"idcard":"1","password":"123","status":"正常","telephone":"12","userId":"1","userName":"user1"}", discription: "successful", statusCode: "1000"}2、/register请求参数：{ username,

password, idcard, telephone}返回结果：{content: "{"idcard":"4","password":"123","status":"正常","telephone":"4","userId":"303","userName":"user4"}", discription: "successful", statusCode: "1000"}3、/updateUser请求参数：{ username,

password, idcard, telephone}返回结果：{content: "{"idcard":"4","password":"123","status":"正常","telephone":"4","userId":"303","userName":"user4"}", discription: "successful", statusCode: "1000"}4、/queryOrder请求参数：{ idcard}返回结果：{content: "[{"carriage":"1","createAt":"2018-12-31","date":"2…trainName":"G5","userId":"1","userName":"user1"}]", discription: "successful", statusCode: "1000"}5、/deleteOrder请求参数：{ orderId}返回结果：{content: "修改成功", discription: "successful", statusCode: "1000"}6、/getTrainByStop请求参数：{ fromCity, toCity, date}返回结果：{content: "[{"end":"沈阳北","endIndex":"4","endTime":"17:00:00",…"trainId":"5","trainName":"G5","trainType":"⾼铁"}]", discription: "successful", statusCode:

"1000"}7、/getTransfer请求参数：{ fromCity, toCity, date}返回结果：{content: "[{"firstTid":"5","firstTname":"G5","secondTid":"3","secondTname":"G3","transferStation":"沈阳北"}]", discription: "successful", statusCode: "1000"}8、/getRemainSeats请求参数：{ trainId, date, fromStopId, toStopId}返回结果：{content: "[{"carriage":"1","seatId":"0","seatLocation":"01A"…tId":"17","seatLocation":"02F","seatType":"⼆等座"}]", discription: "successful", statusCode: "1000"}9、/lockSeat请求参数： { userId, passengerId, userName, trainId, trainName, carriage, seatType, seatId, seatLocation, startTime, startStopId, startStationName, endStopId, endStationName, date, createAt, status}返回结果：{content: "{"carriage":"2","createAt":"2018-12-31","date":"20…"trainName":"G1","userId":"1","userName":"user1"}", discription: "successful", statusCode: "1000"}项⽬结构：controller中的三个类分别对应着：user相关的接⼝，order相关的接⼝和train相关的接⼝。，，分别对应着：订单类实现及相关数据查询⽅法，⽕车类实现及相关数据查询⽅法和⽤户类实现及相关数据查询⽅法。test中的SQLTest类是测试所有sql语句的⽅法。tools中的两个类包含了对HTTP请求进⾏处理的⽅法。包含了spring框架和jdbc的基本配置。WebContent中的是⽤来调试接⼝的界⾯。部署须知：前端页⾯的开发需要安装之后进⼊train-tickets-front-end⽂件夹，输⼊npm install, 然后输⼊npm run start访问localhost:3000即可进⾏开发。前端页⾯部署需要先输⼊npm run build打包前端页⾯。然后将打包出的build⽂件夹中的全部⽂件放到train-tickets-back-end/WebContent⽂件夹中。最后进⾏后台的部署，将train-tickets-back-end项⽬打包成war⽂件后，直接丢到tomcat⾥。向localhost:8080/TrainTickets/xxx (xxx为对应的接⼝)发起post请求就可以了。项⽬截图