数据库10.12水费管理系统相关表格设计…..修改

-- Create table
create table ADMIN

  gno       VARCHAR24) not null,
  gpassword VARCHAR28) not null,
  gname     VARCHAR29) not null,
  gphone    VARCHAR211)
);
-- 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       VARCHAR24) not null,
  uname     VARCHAR29) not null,
  upassword VARCHAR28) not null,
  uphone    VARCHAR211),
  usfje     NUMBER8,2) not null,
  udfje     NUMBER8,2) not null,
  uzje      NUMBER8,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    VARCHAR24) not null,
  wmonth CHAR6) not null,
  wyl    NUMBER8,2) not null,
  usfje  NUMBER6,2) not null,
  wsfjf  CHAR6) 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    VARCHAR24) not null,
  pmonth CHAR5) not null,
  pyl    NUMBER8,2) not null,
  udfje  NUMBER8,2),
  psfjf  CHAR6) 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 CHAR5) not null,
  mwater NUMBER4,2) not null,
  mpower NUMBER4,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 '电费价格';