JZTXT

创建数据库

发布时间 2023-12-13 01:46:18作者: yangsxuan

# 创建火车购票系统数据库
CREATE DATABASE `trainticketsystem` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT ENCRYPTION='N';

# 创建用户消息表
CREATE TABLE `users_message` (
  `user_id` int NOT NULL COMMENT '唯一用户',
  `user_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '用户名',
  `user_password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '密码',
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '真实姓名',
  `IDNumber` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '身份证号',
  `userStatus` tinyint DEFAULT NULL COMMENT '用户状态（学生0/儿童1/成人2）',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户消息表';

# 创建火车消息表
CREATE TABLE `train_message` (
  `train_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '唯一火车',
  `train_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '火车类型（G/C/D/Z/K）',
  `status` tinyint DEFAULT NULL COMMENT '火车状态（启动1/停止0）',
  PRIMARY KEY (`train_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='火车消息表';

# 创建火车座位表
CREATE TABLE `train_seat` (
  `train_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '唯一火车',
  `seat_id` int NOT NULL COMMENT '唯一同一车中的座位号',
  `carriage` int DEFAULT NULL COMMENT '车厢号',
  `seat_type` varchar(100) DEFAULT NULL COMMENT '座位等级',
  `seat_ocation` varchar(100) DEFAULT NULL COMMENT '座位位置',
  `ride_time` varchar(100) DEFAULT NULL COMMENT '乘车时间',
  `occupy` smallint DEFAULT NULL COMMENT '座位是否有人（是1/否0）',
  PRIMARY KEY (`train_id`,`seat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='火车座位表';

# 创建火车站点表
CREATE TABLE `train_station` (
  `station_id` int NOT NULL COMMENT '唯一火车站',
  `city` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '火车站位于的城市',
  `station_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '火车站名称',
  PRIMARY KEY (`station_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='火车站点表';

# 创建火车停站表
CREATE TABLE `stop_table` (
  `train_id` varchar(100) NOT NULL COMMENT '唯一表示火车',
  `station_id` varchar(100) NOT NULL COMMENT '唯一表示火车站',
  `arrive_time` datetime DEFAULT NULL COMMENT '表示到站时间',
  `stop_time` int DEFAULT NULL COMMENT '表示火车在车站经停时间',
  `mieage` int DEFAULT NULL COMMENT '表示火车已经行驶的路程',
  PRIMARY KEY (`train_id`,`station_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='火车停站表';

# 创建火车票表
CREATE TABLE `train_ticket` (
  `ticket_id` int NOT NULL COMMENT '唯一订单号',
  `user_id` int NOT NULL COMMENT '唯一用户',
  `train_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '唯一火车',
  `start_station_name` varchar(100) DEFAULT NULL COMMENT '起始火车站',
  `end_station_name` varchar(100) DEFAULT NULL COMMENT '到达火车站',
  `carriage` varchar(100) DEFAULT NULL COMMENT '车厢号',
  `seat_grade` varchar(100) DEFAULT NULL COMMENT '车座等级',
  `seat_ocation` varchar(100) DEFAULT NULL COMMENT '车座位置',
  `ride_time` varchar(100) DEFAULT NULL COMMENT '出行时间',
  `start_time` varchar(100) DEFAULT NULL COMMENT '起始时间',
  `end_time` varchar(100) DEFAULT NULL COMMENT '终止时间',
  `price` double DEFAULT NULL COMMENT '车票价格',
  `order_time` datetime DEFAULT NULL COMMENT '下单时间',
  PRIMARY KEY (`ticket_id`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='火车票表';

# 创建乘客乘车表
CREATE TABLE `ride_train` (
  `user_id` int DEFAULT NULL COMMENT '唯一用户',
  `train_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '唯一火车',
  `date` varchar(100) DEFAULT NULL COMMENT '乘车时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='乘客乘车表';