火车查询系统

功能清单

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QBv0smwK-1639064931297)(C:\Users\ww\AppData\Roaming\Typora\typora-user-images\image-20211209184923749.png)]

只分管理员和用户。

管理员能进行车站管理、车次管理

用户能查询某个区间的车次

购票。

数据库设计

用户表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QIjLY17C-1639064931302)(C:\Users\ww\AppData\Roaming\Typora\typora-user-images\image-20211209185152628.png)]

车站表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nS0Xmd3J-1639064931304)(C:\Users\ww\AppData\Roaming\Typora\typora-user-images\image-20211209185319494.png)]

距离表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0R3i2bZD-1639064931305)(C:\Users\ww\AppData\Roaming\Typora\typora-user-images\image-20211209185407992.png)]

某两个站点之间的距离，和其之间的距离，

车次表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3OFiunUU-1639064931307)(C:\Users\ww\AppData\Roaming\Typora\typora-user-images\image-20211209185717086.png)]

车次站点表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kcpiEPN4-1639064931308)(C:\Users\ww\AppData\Roaming\Typora\typora-user-images\image-20211209185927812.png)]

sql脚本

create database huoche;

use huoche;
//建立用户表
create table user(
telephone varchar(15) not null,
username varchar(20) not null,
pwd varchar(20) not null,
stat int(1) not null,
primary key(telephone)
)engine = innodb,charset = utf8;



use huoche;
//建立站点表
create table station(
sta_id int(11) not null auto_increment,
staname varchar(20) not null unique,
primary key(sta_id)
)engine = innodb,charset = utf8;

//插入数据
use huoche;
insert into station
values(2,'杭州东');
insert into station
values(2,'绍兴东');
insert into station
values(3,'余姚北');
insert into station
values(4,'宁波');
insert into station
values(5,'台州西');
insert into station
values(6,'温岭');
insert into station
values(7,'乐清东');
insert into station
values(8,'温州南');
insert into station
values(9,'平阳');
insert into station
values(10,'福鼎');
insert into station
values(11,'宁德');
insert into station
values(12,'福州');
insert into station
values(13,'永泰');


//创建乘客表
create table passenage(
passenage_phone varchar(15) not null,
    passenage_id varchar(20) not null,
    passenage_name varchar(10) not null,
    primary key(passenage_id)
)engine = innodb,charset = utf8;


//创建座位类型表

create table seat_type(
seat_id int(11) not null auto_increment,
    seat_name varchar(15) not null,
    pricePerKm double not null,
    primary key(seat_id)
)engine = innodb,charset = utf8;

//插入座位数据

insert into seat_type
values(1,'商务座',4.0);
insert into seat_type
values(2,'一等座',2.0);
insert into seat_type
values(3,'二等座',1.5);
insert into seat_type
values(4,'硬座',1.0);


//创建站点距离表

create table sta_dis(
id int(11) not null primary key,
    src_id int(11) not null,
    to_id int(11) not null,
    dist double not null
)engine = innodb,charset = utf8;
 
 //添加外键
 ALTER TABLE sta_dis
 ADD FOREIGN KEY (src_id) REFERENCES station(sta_id);
 
  ALTER TABLE sta_dis
 ADD FOREIGN KEY (to_id) REFERENCES station(sta_id);


//建立车次表

create table car_time(
car_id int(11) primary key,
    out_time DateTime,
    arrive_time DateTime
)engine = innodb,charset = utf8;

//创建车次站点表

create table car_sta(
car_sta_id int(11) primary key,
  car_id int(11) not null,
    seat_id int(11) not null,
    seat_num int(11) not null,
    arrive_time DateTime,
    out_time DateTime,
    car_order int(11) not null,
    price double,
    dis_id int(11) not null
)engine = innodb,charset = utf8;


//添加外键


 ALTER TABLE car_sta
 ADD FOREIGN KEY (car_id) REFERENCES car_time(car_id);
 
  ALTER TABLE car_sta
 ADD FOREIGN KEY (seat_id) REFERENCES seat_type(seat_id);
 
  ALTER TABLE car_sta
 ADD FOREIGN KEY (dis_id) REFERENCES sta_dis(id);

CES car_time(car_id);

ALTER TABLE car_sta
ADD FOREIGN KEY (seat_id) REFERENCES seat_type(seat_id);

ALTER TABLE car_sta
ADD FOREIGN KEY (dis_id) REFERENCES sta_dis(id);