--资费信息表
DROP TABLE COST CASCADE CONSTRAINTS PURGE;
CREATE TABLE COST(
ID NUMBER(4) CONSTRAINT COST_ID_PK PRIMARY KEY,
NAME VARCHAR(50) NOT NULL,
BASE_DURATION NUMBER(11),
BASE_COST NUMBER(7,2),
UNIT_COST NUMBER(7,4),
STATUS CHAR(1) CONSTRAINT COST_STATUS_CK
CHECK (STATUS IN (0,1)),
DESCR VARCHAR2(100),
CREATIME DATE DEFAULT SYSDATE ,
STARTIME DATE,
COST_TYPE CHAR(1)
);
INSERT INTO COST VALUES (1,'5.9元套餐',20,5.9,0.4,0,'5.9元20小时/月,超出部分0.4元/时',DEFAULT,DEFAULT,NULL);
INSERT INTO COST VALUES (2,'6.9元套餐',40,6.9,0.3,0,'6.9元40小时/月,超出部分0.3元/时',DEFAULT,DEFAULT,NULL);
INSERT INTO COST VALUES (3,'8.5元套餐',100,8.5,0.2,0,'8.5元100小时/月,超出部分0.2元/时',DEFAULT,DEFAULT,NULL);
INSERT INTO COST VALUES (4,'10.5元套餐',200,10.5,0.1,0,'10.5元200小时/月,超出部分0.1元/时',DEFAULT,DEFAULT,NULL);
INSERT INTO COST VALUES (5,'计时收费',null,null,0.5,0,'0.5元/时,不使用不收费',DEFAULT,DEFAULT,NULL);
INSERT INTO COST VALUES (6,'包月',null,20,null,0,'每月20元,不限制使用时间',DEFAULT,DEFAULT,NULL);
COMMIT;
COLUMN NAME FORMAT a10
COLUMN BASE_COST FORMAT 99.99
COLUMN BASE_DURATION FORMAT 999
COLUMN UNIT_COST FORMAT 99.99
COLUMN ID FORMAT 9
select id,name,base_cost,base_duration,unit_cost from cost
--帐务信息表
DROP TABLE ACCOUNT CASCADE CONSTRAINTS PURGE;
CREATE TABLE ACCOUNT(
ID NUMBER(9) CONSTRAINT ACCOUNT_ID_PK PRIMARY KEY,
RECOMMENDER_ID NUMBER(9) CONSTRAINT ACCOUNT_RECOMMENDER_ID_FK
REFERENCES ACCOUNT(ID),
LOGIN_NAME VARCHAR2(30) NOT NULL
CONSTRAINT ACCOUNT_LOGIN_NAME_UK UNIQUE,
LOGIN_PASSWD VARCHAR2(30) NOT NULL,
STATUS CHAR(1) CONSTRAINT ACCOUNT_STATUS_CK
CHECK (STATUS IN (0,1,2)),
CREATE_DATE DATE DEFAULT SYSDATE,
PAUSE_DATE DATE,
CLOSE_DATE DATE,
REAL_NAME VARCHAR2(20) NOT NULL,
IDCARD_NO CHAR(18) NOT NULL
CONSTRAINT ACCOUNT_INCARD_NO UNIQUE,
BIRTHDATE DATE,
GENDER CHAR(1) CONSTRAINT ACCOUNT_GENDER_CK
CHECK (GENDER IN (0,1)),
OCCUPATION VARCHAR2(50),
TELEPHONE VARCHAR2(15) NOT NULL,
EMAIL VARCHAR2(50),
MAILADDRESS VARCHAR2(50),
ZIPCODE CHAR(6),
QQ VARCHAR2(15),
LAST_LOGIN_TIME DATE,
LAST_LOGIN_IP VARCHAR2(15)
);
ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy mm dd hh24:mi:ss';
INSERT INTO ACCOUNT(ID,RECOMMENDER_ID,LOGIN_NAME,LOGIN_PASSWD,STATUS,CREATE_DATE,
REAL_NAME,BIRTHDATE,IDCARD_NO,TELEPHONE)
VALUES(1005,NULL,'taiji001','256528',1,'2008 03 15','zhangsanfeng','19430225','410381194302256528',13669351234);
INSERT INTO ACCOUNT(ID,RECOMMENDER_ID,LOGIN_NAME,LOGIN_PASSWD,STATUS,CREATE_DATE,
REAL_NAME,BIRTHDATE,IDCARD_NO,TELEPHONE)
VALUES(1010,NULL,'xl18z60','190613',1,'2009 01 10','guojing','19690319','330682196903190613',13338924567);
INSERT INTO ACCOUNT(ID,RECOMMENDER_ID,LOGIN_NAME,LOGIN_PASSWD,STATUS,CREATE_DATE,
REAL_NAME,BIRTHDATE,IDCARD_NO,TELEPHONE)
VALUES(1011,1010,'dgbf70','270429',1,'2009 03 01','huangrong','19710827','330902197108270429',13637811357);
INSERT INTO ACCOUNT(ID,RECOMMENDER_ID,LOGIN_NAME,LOGIN_PASSWD,STATUS,CREATE_DATE,
REAL_NAME,BIRTHDATE,IDCARD_NO,TELEPHONE)
VALUES(1015,1005,'mjjzh64','041115',1,'2010 03 12','zhangwuji','19890604','610121198906041115',13572952468);
INSERT INTO ACCOUNT(ID,RECOMMENDER_ID,LOGIN_NAME,LOGIN_PASSWD,STATUS,CREATE_DATE,
REAL_NAME,BIRTHDATE,IDCARD_NO,TELEPHONE)
VALUES(1018,1011,'jmdxj00','010322',1,'2011 01 01','guofurong','199601010322','350581200201010322',18617832562);
INSERT INTO ACCOUNT(ID,RECOMMENDER_ID,LOGIN_NAME,LOGIN_PASSWD,STATUS,CREATE_DATE,
REAL_NAME,BIRTHDATE,IDCARD_NO,TELEPHONE)
VALUES(1019,1011,'ljxj90','310346',1,'2012 02 01','luwushuang','19930731','320211199307310346',13186454984);
INSERT INTO ACCOUNT(ID,RECOMMENDER_ID,LOGIN_NAME,LOGIN_PASSWD,STATUS,CREATE_DATE,
REAL_NAME,BIRTHDATE,IDCARD_NO,TELEPHONE)
VALUES(1020,NULL,'kxhxd20','012115',1,'2012 02 20','weixiaobao','20001001','321022200010012115',13953410078);
INSERT INTO ACCOUNT(ID,RECOMMENDER_ID,LOGIN_NAME,LOGIN_PASSWD,STATUS,CREATE_DATE,
REAL_NAME,BIRTHDATE,IDCARD_NO,TELEPHONE)
VALUES(1021,NULL,'kxhxd21','012116',1,'2012 02 20','zhangsan','20001002','321022200010012116',13953410079);
INSERT INTO ACCOUNT(ID,RECOMMENDER_ID,LOGIN_NAME,LOGIN_PASSWD,STATUS,CREATE_DATE,
REAL_NAME,BIRTHDATE,IDCARD_NO,TELEPHONE)
VALUES(1022,NULL,'kxhxd22','012117',1,'2012 02 20','lisi','20001003','321022200010012117',13953410080);
INSERT INTO ACCOUNT(ID,RECOMMENDER_ID,LOGIN_NAME,LOGIN_PASSWD,STATUS,CREATE_DATE,
REAL_NAME,BIRTHDATE,IDCARD_NO,TELEPHONE)
VALUES(1023,NULL,'kxhxd23','012118',1,'2012 02 20','wangwu','20001004','321022200010012118',13953410081);
INSERT INTO ACCOUNT(ID,RECOMMENDER_ID,LOGIN_NAME,LOGIN_PASSWD,STATUS,CREATE_DATE,
REAL_NAME,BIRTHDATE,IDCARD_NO,TELEPHONE)
VALUES(1024,NULL,'kxhxd24','012119',1,'2012 02 20','zhouliu','20001005','321022200010012119',13953410082);
INSERT INTO ACCOUNT(ID,RECOMMENDER_ID,LOGIN_NAME,LOGIN_PASSWD,STATUS,CREATE_DATE,
REAL_NAME,BIRTHDATE,IDCARD_NO,TELEPHONE)
VALUES(1025,NULL,'kxhxd25','012120',1,'2012 02 20','maqi','20001006','321022200010012120',13953410083);
COMMIT;
select id,recommender_id,login_name,login_passwd,status,create_date,real_name,idcard_no,telephone
from account;
column id format 9999
column recommender_id format 9999
column login_name format a10
column login_passwd format a10
column real_name format a10
--UNIX服务器信息表
DROP TABLE HOST CASCADE CONSTRAINT PURGE;
CREATE TABLE HOST
(ID VARCHAR2(15) CONSTRAINT HOST_ID_PK PRIMARY KEY,
NAME VARCHAR2(20),
LOCATION VARCHAR2(30)
);
INSERT INTO HOST VALUES ('192.168.0.26','sunv210','beijing');
INSERT INTO HOST VALUES('192.168.0.20','sun-server','beijing');
INSERT INTO HOST VALUES ('192.168.0.23','sun280','beijing');
INSERT INTO HOST VALUES ('192.168.0.200','ultra10','beijing');
COMMIT;
---年龄分段信息表
DROP TABLE AGE_SEGMENT CASCADE CONSTRAINT PURGE;
CREATE TABLE AGE_SEGMENT
(ID NUMBER(1) CONSTRAINT AGE_SEGMENT_ID_PK PRIMARY KEY,
NAME VARCHAR2(20) NOT NULL,
LOWAGE NUMBER(2) NOT NULL,
HIAGE NUMBER(2) NOT NULL
);
INSERT INTO AGE_SEGMENT VALUES (0,'少年逆反期',11,14);
INSERT INTO AGE_SEGMENT VALUES (1,'少年成长期',15,17);
INSERT INTO AGE_SEGMENT VALUES (2,'青年青春期',18,28);
INSERT INTO AGE_SEGMENT VALUES (3,'青年成熟期',29,40);
INSERT INTO AGE_SEGMENT VALUES (4,'中年壮实期',41,48);
INSERT INTO AGE_SEGMENT VALUES (5,'中年稳健期',49,55);
INSERT INTO AGE_SEGMENT VALUES (6,'中年稳健期',56,65);
INSERT INTO AGE_SEGMENT VALUES (7,'老年初老期',66,72);
COMMIT;
--业务信息表
DROP TABLE SERVICE CASCADE CONSTRAINTS PURGE;
CREATE TABLE SERVICE(
ID NUMBER(10) CONSTRAINT SERVICE_ID_PK PRIMARY KEY,
ACCOUNT_ID NUMBER(9) CONSTRAINT SERVICE_ACCOUNT_ID_FK
REFERENCES ACCOUNT(ID) NOT NULL,
UNIX_HOST VARCHAR2(15) ,
OS_USERNAME VARCHAR2(8) NOT NULL,
CONSTRAINT SERVICE_UNIXHOST_OSUSERNAME_UK
UNIQUE(UNIX_HOST,OS_USERNAME),
LOGIN_PASSWD VARCHAR2(8) NOT NULL,
STATUS CHAR(1) CONSTRAINT SERVICE_STATUS_CK
CHECK ( STATUS IN (0,1,2) ),
CREATE_DATE DATE DEFAULT SYSDATE,
PAUSE_DATE DATE,
CLOSE_DATE DATE,
COST_ID NUMBER(4) CONSTRAINT SERVICE_COST_ID_FK
REFERENCES COST(ID) NOT NULL
);
INSERT INTO SERVICE VALUES (2001,1010,'192.168.0.26','guojing','guo1234','0',sysdate,null,null,1);
INSERT INTO SERVICE VALUES (2002,1011,'192.168.0.26','huangr','huang234','0',sysdate,null,null,1);
INSERT INTO SERVICE VALUES (2003,1011,'192.168.0.20','huangr','huang234','0',sysdate,null,null,3);
INSERT INTO SERVICE VALUES (2004,1011,'192.168.0.23','huangr','huang234','0',sysdate,null,null,6);
INSERT INTO SERVICE VALUES (2005,1019,'192.168.0.26','luwsh','luwu2345','0',sysdate,null,null,4);
INSERT INTO SERVICE VALUES (2006,1019,'192.168.0.20','luwsh','luwu2345','0',sysdate,null,null,5);
INSERT INTO SERVICE VALUES (2007,1020,'192.168.0.20','weixb','wei12345','0',sysdate,null,null,6);
INSERT INTO SERVICE VALUES (2008,1010,'192.168.0.20','guojing','g
