lihong 20080424 1609.sql

使用java弄的,实现铁路售票的各种功能,供大家学习使用

--

DROP TABLE IF EXISTS `trainstation`;
CREATE TABLE `trainstation` (
  `trainno` char(10) NOT NULL default '',
  `stageno` int(10) unsigned NOT NULL default '0',
  `stagenameindex` varchar(45) NOT NULL default '',
  `arrivetime` char(10) NOT NULL default '',
  `exittime` char(10) NOT NULL default '',
  `runmileage` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`trainno`,`stageno`,`stagenameindex`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

--
-- Dumping data for table `trainticket`.`trainstation`
--

/*!40000 ALTER TABLE `trainstation` DISABLE KEYS */;
INSERT INTO `trainstation` (`trainno`,`stageno`,`stagenameindex`,`arrivetime`,`exittime`,`runmileage`) VALUES 
 ('5566',5566,'','2008-05-03','13',5566),
 ('A21',11,'','2008-05-03','20',1333),
 ('B21',13,'','2008-05-03','21',1222),
 ('C21',14,'','2008-05-03','22',1111),
 ('D21',15,'','2008-05-03','23',1212),
 ('E21',16,'','2008-05-03','24',1312),
 ('F21',17,'','2008-05-03','25',1213),
 ('G21',18,'','2008-05-03','26',1122),
 ('H21',19,'','2008-05-03','27',1133),
 ('I21',20,'','2008-05-03','28',1213),
 ('J21',32,'','2008-05-03','29',1312),
 ('k10',27,'','2008-05-03','30',1000),
 ('k12',12,'','2008-05-03','31',1200),
 ('K13',56,'','2008-05-03','32',2100),
 ('K21',73,'','2008-05-03','26',2000),
 ('L21',73,'','2008-05-03','32',2600),
 ('M20',90,'','2008-05-03','33',1234),
 ('M21',73,'','2008-05-03','12',1200),
 ('N21',60,'','2008-05-03','22',1234),
 ('O21',61,'','2008-05-03','22',1235),
 ('P21',62,'','2008-05-03','22',1236),
 ('Q21',63,'','2008-05-03','22',1237),
 ('R21',64,'','2008-05-03','22',1238),
 ('S21',65,'','2008-05-03','22',1239);
INSERT INTO `trainstation` (`trainno`,`stageno`,`stagenameindex`,`arrivetime`,`exittime`,`runmileage`) VALUES 
 ('T21',66,'','2008-05-03','22',1300),
 ('U21',67,'','2008-05-03','22',1400),
 ('V21',68,'','2008-05-03','22',1500),
 ('W21',69,'','2008-05-03','22',1600),
 ('X21',70,'','2008-05-03','22',1700),
 ('Y21',71,'','2008-05-03','22',1800),
 ('Z21',72,'','2008-05-03','22',1900);
/*!40000 ALTER TABLE `trainstation` ENABLE KEYS */;


--
-- Table structure for table `trainticket`.`user`
--

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `userno` int(10) unsigned NOT NULL default '0',
  `username` char(10) NOT NULL default '',
  `userpassword` char(10) NOT NULL default '',
  `userpopedom` char(10) NOT NULL default '',
  PRIMARY KEY  (`userno`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

--
-- Dumping data for table `trainticket`.`user`
--

/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` (`userno`,`username`,`userpassword`,`userpopedom`) VALUES 
 (1,'鸿哥','123','admin'),
 (2,'sain','123','admin'),
 (3,'user','123','user');
/*!40000 ALTER TABLE `user` ENABLE KEYS */;


--
-- Table structure for table `trainticket`.`usersinfo`
--

DROP TABLE IF EXISTS `usersinfo`;
CREATE TABLE `usersinfo` (
  `身份证号` varchar(18) NOT NULL default '',
  `姓名` varchar(45) NOT NULL default '',
  `性别` char(10) NOT NULL default '',
  `家庭住址` varchar(45) NOT NULL default '',
  `电话` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`身份证号`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

--
-- Dumping data for table `trainticket`.`usersinfo`
--

/*!40000 ALTER TABLE `usersinfo` DISABLE KEYS */;
/*!40000 ALTER TABLE `usersinfo` ENABLE KEYS */;


--
-- Procedure `trainticket`.`adduser`
--

DROP PROCEDURE IF EXISTS `adduser`;
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `adduser`(in param1 int,param2 varchar(50),param3 varchar(50),param4 varchar(50),out param5 int)
begin
insert into user (userno,username,userpassword,userpopedom)values(param1,param2,param3,param4);
select 1 into param5;
end $$

DELIMITER ;

--
-- Procedure `trainticket`.`AllTicket`
--

DROP PROCEDURE IF EXISTS `AllTicket`;
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `AllTicket`(out param int)
begin
select sum(useable) into param from railway;
end $$

DELIMITER ;

--
-- Procedure `trainticket`.`deluser`
--

DROP PROCEDURE IF EXISTS `deluser`;
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `deluser`(in param varchar(20),out param2 int)
begin
delete from user where userno = param;
select 1 into param2;
end $$

DELIMITER ;

--
-- Procedure `trainticket`.`getticket`
--

DROP PROCEDURE IF EXISTS `getticket`;
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getticket`(in param1 varchar(20),param2 varchar(20),out param3 int)
begin
update railway set useable=useable-param2 where trainno = param1;
select 1 into param3;
end $$

DELIMITER ;

--
-- Procedure `trainticket`.`query`
--

DROP PROCEDURE IF EXISTS `query`;
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `query`(in param1 varchar(50),param2 varchar(50),param3 varchar(50))
BEGIN
select * from user where username = param1 and userpassword = param2;
select * from salereturnticket where trainno = param3;
END $$

DELIMITER ;

--
-- Procedure `trainticket`.`returnday`
--

DROP PROCEDURE IF EXISTS `returnday`;
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `returnday`(in param varchar(10),out param1 date)
begin
select date(saletime) into param1 from salereturnticket where trainno = param;
end $$

DELIMITER ;

--
-- Procedure `trainticket`.`returnmonth`
--

DROP PROCEDURE IF EXISTS `returnmonth`;
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `returnmonth`(in param varchar(10),out param1 date)
begin
select date(saletime) into param1 from salereturnticket where trainno = param;
end $$

DELIMITER ;

--
-- Procedure `trainticket`.`returnyear`
--

DROP PROCEDURE IF EXISTS `returnyear`;
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `returnyear`(in param varchar(10),out param1 date)
begin
select date(saletime) into param1 from salereturnticket where trainno = param;
end $$

DELIMITER ;

--
-- Procedure `trainticket`.`updateuser`
--

DROP PROCEDURE IF EXISTS `updateuser`;
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `updateuser`(in param varchar(20),param1 varchar(20),param2 varchar(20),param3 varchar(20),out param4 int)
begin
update user set username = param1 where userno = param;
update user set userpassword = param2 where userno = param;
update user set userpopedom = param3 where userno = param;
select 1 into param4;
end $$

DELIMITER ;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;