1 --建表

 2 --管理人员表 admin

 3 create table admin(

 4        admin_id      varchar2(3) not null,

 5        admin_loginname           varchar2(8) not null,

 6        admin_password            varchar2(6) not null,

 7        admin_username            varchar2(4) not null,

 8        constraint pk_admin primary key(admin_id)

 9 );

10 comment on column admin.admin_id is '管理员编号';

11 comment on column admin.admin_loginname is '管理员登录用户名';

12 comment on column admin.admin_password is '管理员登录密码';

13 comment on column admin.admin_username is '管理员姓名';

14 --用户基本信息表  user

15 create table users(

16        user_id    varchar2(10) not null,

17        user_loginname           varchar2(8) not null,

18        user_password            varchar2(6) not null,

19        user_username            varchar2(4) not null,

20        user_address             varchar2(20) not null,

21        user_phone               number(11) not null,

22        constraint pk_users primary key(user_id)

23 );

24 comment on column users.user_id is '用户编号';

25 comment on column users.user_loginname is '用户登录名';

26 comment on column users.user_password is '用户登录密码';

27 comment on column users.user_username is '用户姓名';

28 comment on column users.user_address is '用户地址';

29 comment on column users.user_phone is '用户电话';

30 --水表 water

31 create table water(

32        water_id   varchar2(10) not null,

33        userid     varchar2(10) not null,

34        water_count             number(3,2) not null,

35        water_time              date not null,

36        water_status            number(1) not null,

37        water_priceid           varchar2(10) not null,

38        water_beforecount       number(3,2) not null,

39        constraint pk_water primary key(water_id)

40 );

41 comment on column water.water_id is '水表编号';

42 comment on column water.userid is '用户编号';

43 comment on column water.water_count is '水表跑数';

44 comment on column water.water_time is '水表时间';

45 comment on column water.water_status is '水费缴费状态';

46 comment on column water.water_priceid is '水费价格编号';

47 comment on column water.water_beforecount is '上月水表跑数';

48 --水表 power

49 create table power(

50        power_id   varchar2(10) not null,

51        userid     varchar2(10) not null,

52        power_count             number(3,2) not null,

53        power_time              date not null,

54        power_status            number(1) not null,

55        power_priceid           varchar2(10) not null,

56        power_beforecount       number(3,2) not null,

57        constraint pk_power primary key(power_id)

58 );

59 comment on column power.power_id is '电表编号';

60 comment on column water.userid is '用户编号';

61 comment on column power.power_count is '电表跑数';

62 comment on column power.power_time is '电表时间';

63 comment on column power.power_status is '电费缴费状态';

64 comment on column power.power_priceid is '电费价格编号';

65 comment on column power.power_beforecount is '上月电表跑数';

66 --价格表 money

67 create table price(

68        price_id   varchar2(10) not null,

69        price_time date not null,

70        isactive   number(1) not null,

71        waterprice number(2,2) not null,

72        powerprice number(2,2) not null,

73        constraint pk_price primary key(price_id)

74 );

75 comment on column price.price_id is '价格编号';

76 comment on column price.price_time is '价格日期';

77 comment on column price.isactive is '价格状态';

78 comment on column price.waterprice is '水费价格';

79 comment on column price.powerprice is '电费价格';