-- Create table
create table ADMIN
(
  gno       VARCHAR2(4) not null,
  gpassword VARCHAR2(8) not null,
  gname     VARCHAR2(9) not null,
  gphone    VARCHAR2(11)
);
-- Add comments to the columns 
comment on column ADMIN.gno
  is '管理员编号';
comment on column ADMIN.gpassword
  is '管理员密码';
comment on column ADMIN.gname
  is '管理员姓名';
comment on column ADMIN.gphone
  is '管理员电话';

-- Create/Recreate primary, unique and foreign key constraints 
alter table ADMIN
  add constraint PK_GNO primary key (GNO);

-- Create table
create table USERZ
(
  uno       VARCHAR2(4) not null,
  uname     VARCHAR2(9) not null,
  upassword VARCHAR2(8) not null,
  uphone    VARCHAR2(11),
  usfje     NUMBER(8,2) not null,
  udfje     NUMBER(8,2) not null,
  uzje      NUMBER(8,2) not null,
  udate     DATE not null
);

-- Add comments to the columns 
comment on column USERZ.uno
  is '用户编号（主键）';
comment on column USERZ.uname
  is '用户姓名';
comment on column USERZ.upassword
  is '用户密码';
comment on column USERZ.uphone
  is '用户电话';
comment on column USERZ.usfje
  is '水费金额';
comment on column USERZ.udfje
  is '电费金额';
comment on column USERZ.uzje
  is '水电费总金额';
comment on column USERZ.udate
  is '缴费日期';

-- Create/Recreate primary, unique and foreign key constraints 
alter table USERZ
  add constraint PK_UNO primary key (UNO);

-- Create table   水费表
create table WATER
(
  uno    VARCHAR2(4) not null,
  wmonth CHAR(6) not null,
  wyl    NUMBER(8,2) not null,
  usfje  NUMBER(6,2) not null,
  wsfjf  CHAR(6) not null
);
-- Add comments to the columns 
comment on column WATER.uno
  is '用户编号（外键）';
comment on column WATER.wmonth
  is '月份';
comment on column WATER.wyl
  is '本月用水量';
comment on column WATER.usfje
  is '水费金额';
comment on column WATER.wsfjf
  is '是否缴费';
-- Create/Recreate primary, unique and foreign key constraints 
alter table WATER
  add constraint FK_W_UNO foreign key (UNO)
  references USERZ (UNO);

-- Create table  电费单
create table POWER
(
  uno    VARCHAR2(4) not null,
  pmonth CHAR(5) not null,
  pyl    NUMBER(8,2) not null,
  udfje  NUMBER(8,2),
  psfjf  CHAR(6) not null
);
-- Add comments to the columns 
comment on column POWER.uno
  is '用户编号（外键）';
comment on column POWER.pmonth
  is '电表月份';
comment on column POWER.pyl
  is '用电量';
comment on column POWER.udfje
  is '电费金额';
comment on column POWER.psfjf
  is '是否缴费';
-- Create/Recreate primary, unique and foreign key constraints 
alter table POWER
  add constraint FK_P_UNO foreign key (UNO)
  references USERZ (UNO);

-- Create table
create table MONEY
(
  mmonth CHAR(5) not null,
  mwater NUMBER(4,2) not null,
  mpower NUMBER(4,2) not null
);
-- Add comments to the columns 
comment on column MONEY.mmonth
  is '价格变更月份';
comment on column MONEY.mwater
  is '水费价格';
comment on column MONEY.mpower
  is '电费价格';