表视图存储过程触发器
表- 用户表
用户表的创建：
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.取消订票在系统中有应用，但三个合起来将增删改都实现了。
系列更新结束后会把源代码和相关的文档打包上传成资源。如果你觉得这篇文章对你有用的话，麻烦点赞，收藏，关注一下。
感谢您的阅读，您的支持是对我最大的鼓励~
