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

最新推荐文章于 2025-03-31 05:43:21 发布

Somia8889

最新推荐文章于 2025-03-31 05:43:21 发布

阅读量4k

点赞数 6

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

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

版权

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

5 篇文章

订阅专栏

本文详细介绍了如何创建和使用数据库中的存储过程、触发器以及视图，包括用户表、车次表、余票表和已售车票表的创建和数据插入。此外，还展示了用于登录、注册、取消订票和更新购票信息的存储过程，并定义了几个关键的触发器，如购票和取消订票时更新余票数量。最后，构建了几个实用的视图，如出发城市、学生车票、乘车量以及车票余量和用户信息。这些数据库元素共同构成了一个完整的购票系统的基础架构。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

表

用户表

用户表的创建：

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 $