initial.sql

软件工程实习项目：移动网上营业厅

CREATE DATABASE `mobile` /*!40100 DEFAULT CHARACTER SET utf8 */;

DROP TABLE IF EXISTS `mobile`.`avai`;
CREATE TABLE  `mobile`.`avai` (
  `AVAINUM` varchar(11) NOT NULL,
  `AREANO` int(11) NOT NULL,
  PRIMARY KEY  (`AVAINUM`),
  UNIQUE KEY `AVAINUM` (`AVAINUM`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `mobile`.`cal`;
CREATE TABLE  `mobile`.`cal` (
  `TNO1` varchar(11) NOT NULL default '',
  `TNO2` varchar(11) NOT NULL,
  `ACTI` char(10) NOT NULL default '',
  `ROAM` char(10) NOT NULL default '',
  `CALLTIME` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `TIM` time NOT NULL,
  PRIMARY KEY  (`CALLTIME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `mobile`.`chargeingcard`;
CREATE TABLE  `mobile`.`chargeingcard` (
  `CARDNUM` varchar(16) NOT NULL,
  `CARDCODE` varchar(16) default NULL,
  `MONEY` bigint(20) default NULL,
  PRIMARY KEY  (`CARDNUM`),
  UNIQUE KEY `CARDNUM` (`CARDNUM`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `mobile`.`clerk`;
CREATE TABLE  `mobile`.`clerk` (
  `KNO` varchar(11) NOT NULL,
  `KCODE` varchar(16) NOT NULL,
  `KNAME` char(10) NOT NULL,
  `KSEX` char(2) default NULL,
  `WTIME` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `AREANO` int(11) NOT NULL,
  `KPHO` varchar(16) default NULL,
  `GRADE` float default NULL,
  PRIMARY KEY  (`KNO`),
  UNIQUE KEY `KNO` (`KNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `mobile`.`clien`;
CREATE TABLE  `mobile`.`clien` (
  `TNO` varchar(11) NOT NULL,
  `TCODE` varchar(6) NOT NULL,
  `TNAME` varchar(10) NOT NULL,
  `TSEX` char(2) NOT NULL,
  `TIDNO` char(32) NOT NULL,
  `TEMAIL` char(32) default NULL,
  `TADD` char(255) default NULL,
  `AREANO` int(11) NOT NULL,
  `REGTIME` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`TNO`),
  UNIQUE KEY `TNO` (`TNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `mobile`.`countinfo`;
CREATE TABLE  `mobile`.`countinfo` (
  `TNO` varchar(11) NOT NULL,
  `MONEY` bigint(20) default NULL,
  `FREECALL` int(11) default NULL,
  `FREEMES` int(11) default NULL,
  `FREEGPRS` bigint(20) default NULL,
  `AVAILABLETIME` date default NULL,
  PRIMARY KEY  (`TNO`),
  UNIQUE KEY `TNO` (`TNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `mobile`.`gprs`;
CREATE TABLE  `mobile`.`gprs` (
  `TNO` varchar(11) NOT NULL default '',
  `TIME1` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `BYTES` bigint(20) default NULL,
  PRIMARY KEY  (`TIME1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `mobile`.`hotop`;
CREATE TABLE  `mobile`.`hotop` (
  `hopno` char(10) default NULL,
  `hopintro` char(100) default NULL,
  `hopcharge` int(5) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `mobile`.`manage`;
CREATE TABLE  `mobile`.`manage` (
  `MNO` varchar(11) NOT NULL,
  `MCODE` char(16) NOT NULL,
  `MNAME` char(10) NOT NULL,
  `MSEX` char(2) default NULL,
  `AREANO` int(11) NOT NULL,
  `MPHO` varchar(16) default NULL,
  PRIMARY KEY  (`MNO`),
  UNIQUE KEY `MNO` (`MNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `mobile`.`message`;
CREATE TABLE  `mobile`.`message` (
  `TNO1` varchar(11) NOT NULL default '',
  `TNO2` varchar(11) NOT NULL default '',
  `SENTTIME` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `MESSLEN` int(11) default NULL,
  PRIMARY KEY  (`SENTTIME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `mobile`.`mobileno`;
CREATE TABLE  `mobile`.`mobileno` (
  `no` varchar(20) NOT NULL default '',
  `place` varchar(40) default NULL,
  `cardtype` varchar(40) default NULL,
  PRIMARY KEY  (`no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `mobile`.`newclerk`;
CREATE TABLE  `mobile`.`newclerk` (
  `MNO` varchar(11) NOT NULL,
  `KNO` varchar(11) NOT NULL,
  `NKTIME` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`MNO`,`KNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `mobile`.`newclient`;
CREATE TABLE  `mobile`.`newclient` (
  `KNO` varchar(11) NOT NULL,
  `TNO` varchar(11) NOT NULL,
  `NTTIME` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`KNO`,`TNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `mobile`.`notes`;
CREATE TABLE  `mobile`.`notes` (
  `TNO` varchar(11) default NULL,
  `NNO` bigint(20) NOT NULL,
  `NOTE` char(255) default NULL,
  `DIS` char(255) default NULL,
  PRIMARY KEY  (`NNO`),
  UNIQUE KEY `NNO` (`NNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `mobile`.`operation`;
CREATE TABLE  `mobile`.`operation` (
  `opno` char(15) default NULL,
  `opintro` char(255) default NULL,
  `opcharge` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `mobile`.`operon`;
CREATE TABLE  `mobile`.`operon` (
  `tno` varchar(11) default NULL,
  `opno` char(10) default NULL,
  `opintro` char(100) default NULL,
  `opcharge` int(5) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `mobile`.`tvalue`;
CREATE TABLE  `mobile`.`tvalue` (
  `tno` char(20) NOT NULL,
  `value` int(11) NOT NULL,
  PRIMARY KEY  (`tno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/* Data for the `avai` table  (Records 1 - 101) */

INSERT INTO `mobile`.`avai` (`AVAINUM`, `AREANO`) VALUES 
  ('13917530850', 11),
  ('15813287851', 11),
  ('15988880000', 11),
  ('15988880002', 11),
  ('15988880003', 11),
  ('15988880004', 11),
  ('15988880005', 11),
  ('15988880006', 11),
  ('15988880007', 11),
  ('15988880008', 11),
  ('15988880009', 11),
  ('15988880010', 11),
  ('15988880011', 11),
  ('15988880012', 11),
  ('15988880013', 11),
  ('15988880014', 11),
  ('15988880015', 11),
  ('15988880016', 11),
  ('15988880017', 11),
  ('15988880018', 11),
  ('15988880019', 11),
  ('15988880020', 11),
  ('15988880021', 11),
  ('15988880022', 11),
  ('15988880023', 11),
  ('15988880024', 11),
  ('15988880025', 11),
  ('15988880026', 11),
  ('15988880027', 11),
  ('15988880028', 11),
  ('15988880029', 11),
  ('15988880030', 11),
  ('15988880031', 11),
  ('15988880032', 11),
  ('15988880033', 11),
  ('15988880034', 11),
  ('15988880035', 11),
  ('15988880036', 11),
  ('15988880037', 11),
  ('15988880038', 11),
  ('15988880039', 11),
  ('15988880040', 11),
  ('15988880041', 11),
  ('15988880042', 11),
  ('15988880043', 11),
  ('15988880044', 11),
  ('15988880045', 11),
  ('15988880046', 11),
  ('15988880047', 11),
  ('15988880048', 11),
  ('15988880049', 11),
  ('15988880050', 11),
  ('15988880051', 11),
  ('15988880052', 11),
  ('15988880053', 11),
  ('15988880054', 11),
  ('15988880055', 11),
  ('15988880056', 11),
  ('15988880057', 11),
  ('15988880058', 11),
  ('15988880059', 11),
  ('15988880060', 11),
  ('15988880061', 11),
  ('15988880062', 11),
  ('15988880063', 11),
  ('15988880064', 11),
  ('15988880065', 11),
  ('15988880066', 11),
  ('15988880067', 11),
  ('15988880068', 11),
  ('15988880069', 11),
  ('15988880070', 11),
  ('15988880071', 11),
  ('15988880072', 11),
  ('15988880073', 11),
  ('15988880074', 11),
  ('15988880075', 11),
  ('15988880076', 11),
  ('15988880077', 11),
  ('15988880078', 11),
  ('15988880079', 11),
  ('15988880080', 11),
  ('15988880081', 11),
  ('15988880082', 11),
  ('15988880083', 11),
  ('15988880084', 11),