SQL：
--建表
--管理人员表 admin
--drop table WPADMIN cascade constraints; /*==============================================================*/
/* Table: ADMIN */
/*==============================================================*/
create table WPADMIN
(
ADMIN_LOGINNAME varchar2(10) not null,
ADMIN_PASSWORD varchar2(10) not null,
ADMIN_NAME varchar(4) not null,
constraint PK_ADMIN primary key (ADMIN_LOGINNAME)
); comment on table WPADMIN is
'管理员表'; comment on column WPADMIN.ADMIN_LOGINNAME is
'管理员用户名'; comment on column WPADMIN.ADMIN_PASSWORD is
'管理员密码'; comment on column WPADMIN.ADMIN_NAME is
'管理员姓名'; --用户基本信息表 user
--drop table USERS cascade constraints; /*==============================================================*/
/* Table: USERS */
/*==============================================================*/
create table USERS
(
USER_LOGINNAME varchar2(10) not null,
USER_PASSWORD varchar2(10) not null,
USER_NAME varchar2(4) not null,
USER_ADRESS varchar2(20) not null,
USER_PHONE number(11) not null,
constraint PK_USERS primary key (USER_LOGINNAME)
); comment on table USERS is
'用户表'; comment on column USERS.USER_LOGINNAME is
'用户用户名'; comment on column USERS.USER_PASSWORD is
'用户密码'; comment on column USERS.USER_NAME is
'用户姓名'; comment on column USERS.USER_ADRESS is
'用户住址'; comment on column USERS.USER_PHONE is
'用户联系方式'; --水表 water
--drop table WATER cascade constraints; /*==============================================================*/
/* Table: WATER */
/*==============================================================*/
create table WATER
(
WATER_ID varchar2(10) not null,
USERID varchar2(10) not null,
WATER_TIME date not null,
WATER_COUNT number(4,2) not null,
WATER_BEFORECOUNT number(4,2) not null,
WATER_USE number(4,2) not null,
WATER_PRICE number(3,2) not null,
STATUS number(1) not null,
constraint PK_WATER primary key (WATER_ID)
); comment on table WATER is
'水费表'; comment on column WATER.WATER_ID is
'水费单id'; comment on column WATER.USERID is
'用户id(用户用户名)'; comment on column WATER.WATER_TIME is
'水费时间'; comment on column WATER.WATER_COUNT is
'当月水表数'; comment on column WATER.WATER_BEFORECOUNT is
'上月水表数'; comment on column WATER.WATER_USE is
'当月用水量=当月水表数-上月水表数
water_use=water_count-water_beforecount'; comment on column WATER.WATER_PRICE is
'当月水费'; comment on column WATER.STATUS is
'缴费状态:0:未缴费 1:已缴费
'; --电表 power
--drop table POWER cascade constraints; /*==============================================================*/
/* Table: POWER */
/*==============================================================*/
create table POWER
(
POWER_ID varchar2(10) not null,
USERID varchar2(10) not null,
POWER_TIME date not null,
POWER_COUNT number(4,2) not null,
POWER_BEFORECOUNT number(4,2) not null,
POWER_USE number(4,2) not null,
POWER_PRICE number(3,2) not null,
STATUS number(1) not null,
constraint PK_POWER primary key (POWER_ID)
); comment on table POWER is
'电费表'; comment on column POWER.POWER_ID is
'电费单id'; comment on column POWER.USERID is
'用户id(用户用户名)'; comment on column POWER.POWER_TIME is
'电费时间'; comment on column POWER.POWER_COUNT is
'当月电表数'; comment on column POWER.POWER_BEFORECOUNT is
'上月电表数'; comment on column POWER.POWER_USE is
'当月用电量=当月电表数-上月电表数
power_use=power_count-power_beforecount'; comment on column POWER.POWER_PRICE is
'当月电费'; comment on column POWER.STATUS is
'缴费状态:0:未缴费 1:已缴费
'; --价格表 money
--drop table PRICE cascade constraints; /*==============================================================*/
/* Table: PRICE */
/*==============================================================*/
create table PRICE
(
PRICEID varchar2(10) not null,
"DATE" date not null,
WATER_PRICE number(3,2) not null,
POWER_PRICE number(3,2) not null,
ISACTIVE number(1) not null,
constraint PK_PRICE primary key (PRICEID)
); comment on table PRICE is
'价格表'; comment on column PRICE.PRICEID is
'价格id'; comment on column PRICE."DATE" is
'当前价格时间'; comment on column PRICE.WATER_PRICE is
'水费单价'; comment on column PRICE.POWER_PRICE is
'电费单价'; comment on column PRICE.ISACTIVE is
'价格状态:0:当前价格失效
1:当前价格有效';