【Java+JSP+MySql】12306购票系统（二）数据库

最新推荐文章于 2024-05-02 21:44:24 发布

Somia8889

最新推荐文章于 2024-05-02 21:44:24 发布

阅读量3.2k

点赞数 6

分类专栏：【MySQL+Java】简易12306系统文章标签：数据库 java mysql

本文链接：https://blog.csdn.net/weixin_46068120/article/details/122894970

版权

【MySQL+Java】简易12306系统专栏收录该内容

5 篇文章 23 订阅

订阅专栏

文章目录

表
视图
存储过程
触发器

表

用户表

用户表的创建：

CREATE TABLE user_info(
id CHAR(18) NOT NULL,
username VARCHAR(10) NOT NULL,
telephone CHAR(11) NOT NULL,
upassword VARCHAR(20) NOT NULL,
PRIMARY KEY(id),
CONSTRAINT CU1 CHECK(telephone LIKE '1%')
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

插入数据：

INSERT INTO `user_info` VALUES
('110108200001011111','赵倩','18310710700','password'),
('110108200002022222','张三','18310710711','123456'),
('110108200003033333','李四','18310710722','654321');

车次表

车次表的创建：

CREATE TABLE train(
trainnum CHAR(5) NOT NULL,
origin VARCHAR(10) NOT NULL,
destination VARCHAR(10) NOT NULL,
stime CHAR(5),
prize00 INT,
prize01 INT,
prize02 INT,
PRIMARY KEY(trainnum),
CONSTRAINT CU2 CHECK(stime LIKE '__:__')
);

插入数据：

INSERT INTO `train` VALUES
('G1101','北京','天津','09:00',89,59,39),
('G1323','北京','天津','13:30',89,59,39),
('G1434','北京','天津','17:05',89,59,39),
('G2141','天津','北京','09:40',89,59,39),
('G2363','天津','北京','18:00',89,59,39),
('C3444','北京','上海','10:50',319,289,259),
('C3555','北京','上海','16:30',319,289,259),
('C3666','北京','上海','19:50',319,289,259),
('C4566','上海','北京','11:05',319,289,259),
('C4788','上海','北京','18:05',319,289,259),
('C5077','上海','天津','15:25',319,289,259),
('C6098','天津','上海','16:30',319,289,259);

余票表

余票表的创建：

CREATE TABLE `left_tickets`(
`sdate` DATE,
`trainnum` CHAR(5),
`leftnum00` INT,
`leftnum01` INT,
`leftnum02` INT,
PRIMARY KEY(`sdate`,`trainnum`),
FOREIGN KEY(`trainnum`) REFERENCES train(`trainnum`),
CONSTRAINT CU3 CHECK(`leftnum00`>=0 AND `leftnum01`>=0 AND `leftnum02`>=0)
);

插入数据：

INSERT INTO `left_tickets` VALUES
('2021-12-31','G1101',12,20,36),
('2021-12-31','G1323',12,20,36),
('2021-12-31','G1434',12,20,36),
('2021-12-31','G2141',12,20,36),
('2021-12-31','G2363',12,20,36),
('2021-12-31','C3444',12,20,34),
('2021-12-31','C3555',12,20,36),
('2021-12-31','C3666',12,20,36),
('2021-12-31','C4566',12,20,36),
('2021-12-31','C4788',12,20,36),
('2021-12-31','C5077',12,20,36),
('2021-12-31','C6098',12,20,36),
('2022-01-01','G1101',12,19,36),
('2022-01-01','G1323',12,20,36),
('2022-01-01','G1434',12,20,36),
('2022-01-01','G2141',12,20,36),
('2022-01-01','G2363',12,20,36),
('2022-01-01','C3444',12,20,36),
('2022-01-01','C3555',12,20,36),
('2022-01-01','C3666',12,20,36),
('2022-01-01','C4566',12,20,36),
('2022-01-01','C4788',12,20,36),
('2022-01-01','C5077',12,20,36),
('2022-01-01','C6098',12,20,36),
('2022-01-02','G1101',12,20,36),
('2022-01-02','G1323',12,20,36),
('2022-01-02','G1434',12,20,36),
('2022-01-02','G2141',11,20,36),
('2022-01-02','G2363',12,20,36),
('2022-01-02','C3444',12,20,36),
('2022-01-02','C3555',12,20,36),
('2022-01-02','C3666',12,20,36),
('2022-01-02','C4566',12,20,36),
('2022-01-02','C4788',12,20,36),
('2022-01-02','C5077',12,20,36),
('2022-01-02','C6098',12,20,36),
('2022-01-03','G1101',12,20,36),
('2022-01-03','G1323',12,20,36),
('2022-01-03','G1434',12,20,36),
('2022-01-03','G2141',12,20,36),
('2022-01-03','G2363',12,20,36),
('2022-01-03','C3444',12,20,36),
('2022-01-03','C3555',12,20,36),
('2022-01-03','C3666',12,20,36),
('2022-01-03','C4566',12,20,36),
('2022-01-03','C4788',12,20,34),
('2022-01-03','C5077',12,20,36),
('2022-01-03','C6098',12,20,36);

已售车票表

已售车票表的创建

CREATE TABLE `sold_tickets`(
`sdate` DATE,
`trainnum` CHAR(5),
`seat` VARCHAR(20),
`userid` CHAR(18) NOT NULL,
PRIMARY KEY(`sdate`,`trainnum`,`seat`),
FOREIGN KEY(`sdate`,`trainnum`) REFERENCES `left_tickets`(`sdate`,`trainnum`),
FOREIGN KEY(`userid`) REFERENCES `user_info`(`id`)
);

插入数据：

INSERT INTO `sold_tickets` VALUES
('2021-12-31','C3444','02-1A','110108200001011111'),
('2022-01-03','C4788','02-4D','110108200001011111'),
('2022-01-01','G1101','01-5C','110108200002022222'),
('2022-01-02','G2141','00-2B','110108200002022222'),
('2021-12-31','C3444','02-8C','110108200003033333'),
('2022-01-03','C4788','02-9A','110108200003033333');

视图

前面3个视图是几种常见的分类，后面2个视图是系统中所需要的。

出发城市

#行列子视图
CREATE VIEW 出发城市 AS
SELECT DISTINCT origin
FROM train;

学生车票

#带表达式视图
CREATE VIEW 学生车票(出发城市,到达城市,二等座价格) AS
SELECT origin,destination,`prize02`*0.5
FROM train;

乘车量

#分组视图
CREATE VIEW 乘车量(日期,车次号,乘客数) AS
SELECT sdate,trainnum,COUNT(*)
FROM sold_tickets
GROUP BY sdate,trainnum;

车票余量及用户完整信息

#完整系统需要的视图
#车票余量完整信息视图
CREATE VIEW train_info(start_date,origin,destination,trainnum,stime,num00,num01,num02) AS
SELECT left_tickets.`sdate`,train.`origin`,train.`destination`,left_tickets.`trainnum`,
train.`stime`,left_tickets.`leftnum00`,left_tickets.`leftnum01`,left_tickets.`leftnum02`
FROM train,left_tickets
WHERE train.`trainnum`=left_tickets.`trainnum`;

已购车票及用户完整信息

#用户购买车票完整信息视图
CREATE VIEW user_tickets_info(userid,origin,destination,sdate,stime,trainnum,seat) AS
SELECT sold_tickets.`userid`,train.`origin`,train.`destination`,sold_tickets.`sdate`,
train.`stime`,sold_tickets.`trainnum`,sold_tickets.`seat`
FROM train,sold_tickets
WHERE train.`trainnum`=sold_tickets.`trainnum`;

所有视图的截图在这个专栏的第一个文章里包含了，可以看到。这里也放出链接：
12306购票系统（一）项目介绍

存储过程

DELIMITER $
CREATE PROCEDURE login(IN telenum CHAR(11),IN pwd VARCHAR(20))
BEGIN
	DECLARE result INT DEFAULT 0;
	SELECT COUNT(*) INTO result
	FROM `user_info`
	WHERE `user_info`.`telephone`=telenum
	AND `user_info`.`upassword`=pwd;
	SELECT result;
END $

DELIMITER $
CREATE PROCEDURE sign_in(IN id CHAR(18),IN nm VARCHAR(10),IN telenum CHAR(11),IN pwd VARCHAR(20))
BEGIN
	INSERT INTO `user_info` VALUES(id,nm,telenum,pwd);
	SELECT ('注册成功');
END $

取消订票

DELIMITER $
CREATE PROCEDURE cancel_ticket(IN stdate DATE, IN trainnum CHAR(5),IN userid CHAR(18))
BEGIN
	DELETE FROM `sold_tickets`
	WHERE `sold_tickets`.`sdate`=stdate AND
	`sold_tickets`.`trainnum`=trainnum AND
	`sold_tickets`.`userid`=userid;
END $

更新购票信息

DELIMITER $
CREATE PROCEDURE buy_update(IN stdate DATE,IN trainnum CHAR(5),IN sw_newnum INT,IN yd_newnum INT,IN ed_newnum INT)
BEGIN
	UPDATE `left_tickets` SET `left_tickets`.`leftnum00`=sw_newnum,
	`left_tickets`.`leftnum01`=yd_newnum,`left_tickets`.`leftnum02`=ed_newnum
	WHERE `left_tickets`.`sdate`=stdate AND `left_tickets`.`trainnum`=trainnum;
END $

以上存储过程中，只有1. 登录和2.注册在购票系统中有应用。但是这四个触发器把增删改查都应用到了。

触发器

购票
根据所购车票等级，余票量减一

DELIMITER $
CREATE TRIGGER buy_tickets BEFORE INSERT ON sold_tickets FOR EACH ROW
BEGIN
	IF new.`seat` LIKE '02%' THEN
	UPDATE `left_tickets` SET `leftnum02` =`leftnum02`-1
	WHERE `sdate`=new.`sdate` AND `trainnum`=new.`trainnum`;
	ELSEIF new.`seat` LIKE '01%' THEN
	UPDATE `left_tickets` SET `leftnum01` =`leftnum01`-1
	WHERE `sdate`=new.`sdate` AND `trainnum`=new.`trainnum`;
	ELSE
	UPDATE `left_tickets` SET `leftnum00` =`leftnum00`-1
	WHERE `sdate`=new.`sdate` AND `trainnum`=new.`trainnum`;
	END IF;
END $

取消订票
根据取消的车票等级，相应余票加一

DELIMITER $
CREATE TRIGGER cancel_t AFTER DELETE ON sold_tickets FOR EACH ROW
BEGIN
	IF old.`seat` LIKE '02%' THEN
	UPDATE `left_tickets` SET `leftnum02` =`leftnum02`+1
	WHERE `sdate`=old.`sdate` AND `trainnum`=old.`trainnum`;
	ELSEIF old.`seat` LIKE '01%' THEN
	UPDATE `left_tickets` SET `leftnum01` =`leftnum01`+1
	WHERE `sdate`=old.`sdate` AND `trainnum`=old.`trainnum`;
	ELSE
	UPDATE `left_tickets` SET `leftnum00` =`leftnum00`+1
	WHERE `sdate`=old.`sdate` AND `trainnum`=old.`trainnum`;
	END IF;
END $

修改座位等级

DELIMITER $
CREATE TRIGGER update_seat AFTER UPDATE ON sold_tickets FOR EACH ROW
BEGIN
	IF new.`seat` LIKE '02%' THEN
	UPDATE `left_tickets` SET `leftnum02` =`leftnum02`-1
	WHERE `sdate`=new.`sdate` AND `trainnum`=new.`trainnum`;
	ELSEIF new.`seat` LIKE '01%' THEN
	UPDATE `left_tickets` SET `leftnum01` =`leftnum01`-1
	WHERE `sdate`=new.`sdate` AND `trainnum`=new.`trainnum`;
	ELSE
	UPDATE `left_tickets` SET `leftnum00` =`leftnum00`-1
	WHERE `sdate`=new.`sdate` AND `trainnum`=new.`trainnum`;
	END IF;
	
	IF old.`seat` LIKE '02%' THEN
	UPDATE `left_tickets` SET `leftnum02` =`leftnum02`+1
	WHERE `sdate`=old.`sdate` AND `trainnum`=old.`trainnum`;
	ELSEIF old.`seat` LIKE '01%' THEN
	UPDATE `left_tickets` SET `leftnum01` =`leftnum01`+1
	WHERE `sdate`=old.`sdate` AND `trainnum`=old.`trainnum`;
	ELSE
	UPDATE `left_tickets` SET `leftnum00` =`leftnum00`+1
	WHERE `sdate`=old.`sdate` AND `trainnum`=old.`trainnum`;
	END IF;
END $

触发器前两个1. 购票和2.取消订票在系统中有应用，但三个合起来将增删改都实现了。

系列更新结束后会把源代码和相关的文档打包上传成资源。
如果你觉得这篇文章对你有用的话，麻烦点赞，收藏，关注一下。
感谢您的阅读，您的支持是对我最大的鼓励~

Somia8889

关注

6
点赞
踩
35

收藏

觉得还不错? 一键收藏
2
评论
【Java+JSP+MySql】12306购票系统（二）数据库

文章目录表视图存储过程触发器表用户表用户表的创建：CREATE TABLE user_info(id CHAR(18) NOT NULL,username VARCHAR(10) NOT NULL,telephone CHAR(11) NOT NULL,upassword VARCHAR(20) NOT NULL,PRIMARY KEY(id),CONSTRAINT CU1 CHECK(telephone LIKE '1%'))ENGINE=INNODB DEFAULT CHARS
复制链接

扫一扫