火车票订票系统的数据库设计与实现（某某乐后端实习面试题）

时间：2024-02-29 16:40:13

目的：设计一个车票的数据库，完成一些基本的查询、增删功能。

数据表结构分析；
各个字段分析；
基本数据库、表的建立，数据录入；
复杂查询的实现简要分析。
 
 
1 数据库结构分析；
1.1 客户表
目的：查询客户的所有信息。
输出字段：身份证号（主键）、姓名、用户名、联系电话、籍贯、类型（学生还是普通）
 
1.2 订票单表
目的：查询某一客户订单票的信息
输入in：姓名
输出：订单编号，订票时间，乘车日期，订票数量
 
1.3车票表
输出：车次，出发站，目的站，座位类型，座位号，车票价格，发车时间，到站时间
 
 
二、表设计
create database ticketSystem;
use ticketSystem;
表1 客户表结构
create table users(
id_number varchar(18) primary key,
name varchar(10) not null,
tel char(11),
username varchar(16) not null,
hometown varchar(18),
type varchar(18)
);
insert into users values(\'522131199901016666\',\'张三\',\'13899998888\',\'zhangsan666\',\'贵州\',\'学生票\');
insert into users values(\'522131199901016667\',\'李四\',\'13899998889\',\'lisi666\',\'重庆\',\'成人票\');
 
表2 订票单表结构
create table orders(
order_number varchar(18) primary key,
order_time varchar(10) not null,
go_date timestamp,
order_counts int(3) not null,
id_number varchar(18) not null
);
 
alter table orders modify order_time timestamp not null;
 
insert into orders values(\'201904200001\',\'20190401060000\',\'20190501060000\',\'1\',\'522131199901016666\');
insert into orders values(\'201904200002\',\'20190402060000\',\'20190502120000\',\'2\',\'522131199901016667\');
insert into orders values(\'201904200003\',\'20190402060000\',\'20190502101010\',\'2\',\'522131199901016668\');
insert into orders values(\'201904200004\',current_time,\'20190502120000\',\'2\',\'522131199901016667\');
 
表3 车票表
create table tickets(
ticket_id varchar(18) primary key,
train_number varchar(10) not null,
start_station char(11) not null,
arrive_station varchar(16) not null,
seat_type varchar(18) not null,
price varchar(18),
go_time timestamp,
arrive_time timestamp,
type varchar(18),
order_number varchar(18)
);
insert into tickets values(\'ticket201904200001\',\'Z49\',\'上海\',\'杭州\',\'硬座\',\'20.00\',\'2019-04-20 21:01:00\',\'2019-04-20 22:00:00\',\'成人票\',\'201904200001\');
insert into tickets values(\'ticket201904200002\',\'Z50\',\'嘉兴\',\'杭州\',\'硬座\',\'10.00\',\'2019-04-02 21:00:00\',\'2019-04-02 22:00:00\',\'成人票\',\'201904020001\');
insert into tickets values(\'ticket201904200003\',\'Z50\',\'上海\',\'南京\',\'硬座\',\'10.00\',\'2019-04-02 21:00:00\',\'2019-04-02 22:00:00\',\'成人票\',\'201904020004\');
insert into tickets values(\'ticket201904200004\',\'Z50\',\'上海\',\'杭州\',\'硬卧\',\'10.00\',\'2019-04-02 21:00:00\',\'2019-04-02 22:00:00\',\'成人票\',\'201904020005\');
insert into tickets values(\'ticket201904200005\',\'G11\',\'上海\',\'武汉\',\'硬座\',\'10.00\',\'2019-04-02 21:00:00\',\'2019-04-02 22:00:00\',\'成人票\',\'201904020001\');
 
三、具体查询需求分析：
日期处理：arrive_time只需要日期+时分。
建表的时候有五种：
查询的时候：
需要格式化，DATE_FORMAT(tickets.arrive_time,\'%Y%m%d %H:%i:%s\'
select date_format(order_time,\'%Y-%m-%d %H:%m\') order_time from orders;

 

 
主要是车票表的查询：
（1）需求：查询所有从上海出发到杭州的火车的车次，起点，终点站，席位，价格，出发时间
select train_number as 车次,
start_station as 起点,
arrive_station as 终点,
seat_type as 席位,
price as 价格,
date_format(go_time,\'%Y-%m-%d %H:%m\') 出发时间
from tickets
where start_station = \'上海\'
and
arrive_station = \'杭州\';

 

type字段显示为All，没有用到索引。
 
（2）优化：为了加快查找速度，给始发站和终点站添加复合索引
alter table tickets add index idx_start_arrive(start_station,arrive_station);
type字段为ref，性能优化较好。
 
表结构参考了：