--建立用户信息表 create table users ( users_id varchar2(12) not null, users_name varchar2(12) not null, users_username varchar2(6) not null, users_userpassword varchar2(6) not null, users_phone number(11) not null, users_address varchar2(60) not null ) --添加注释 comment on column users.users_id is '用户编号（主键）'; comment on column users.users_name is '用户姓名'; comment on column users.users_username is '用户账户登录名'; comment on column users.users_userpassword is '用户账户登录密码'; comment on column users.users_phone is '用户联系方式'; comment on column users.users_address is '用户住址'; --添加主键约束 alter table users add constraint PK_users primary key (users_id) --给登录名添加唯一约束 alter table users add constraint UK_users unique (users_username); --添加三个信息 insert into users values('001','张三','zhangs','666666','18865472008','阳光小区15号楼1单元102'); insert into users values('002','李四','lisi01','666666','18865472009','阳光小区14号楼1单元301'); insert into users values('003','王五','wangwu','666666','18865472010','阳光小区8号楼1单元402'); --查看一下新建的users表 select * from users
--建立水费缴纳情况信息表 create table water ( users_id varchar2(12) not null, water_sfjf varchar2(3) not null, water_time date, water_count number(9,2) not null, water_price number(9,2) not null, water_money number(9,2) not null, water_before number(9,2) not null ) --添加注释 comment on column water.users_id is '用户编号（外键）'; comment on column water.water_sfjf is '上月是否缴纳水费'; comment on column water.water_time is '缴费时间'; comment on column water.water_count is '用水量（吨）'; comment on column water.water_price is '单价'; comment on column water.water_money is '本次应缴金额'; comment on column water.water_before is '截止上月共缴水费'; --添加外键 alter table WATER add constraint FK_WATER foreign key (USERS_ID) references USERS (USERS_ID); --添加几个水费缴纳情况的信息信息 insert into water values('001','是',sysdate,'12.3','0.5','6.15','275.5'); insert into water values('002','是',sysdate,'16','0.5','8','395.7'); insert into water values('003','否','','6','0.5','3','78.6'); --查看一下新建的admin表 select * from water
--建立电费缴纳情况信息表 create table power ( users_id varchar2(12) not null, power_sfjf varchar2(3) not null, power_time date, power_count number(9,2) not null, power_price number(9,2) not null, power_money number(9,2) not null, power_before number(9,2) not null ) --添加注释 comment on column power.users_id is '用户编号（外键）'; comment on column power.power_sfjf is '上月是否缴纳电费费'; comment on column power.power_time is '缴费时间'; comment on column power.power_count is '用电量（°）'; comment on column power.power_price is '单价'; comment on column power.power_money is '本次应缴金额'; comment on column power.power_before is '截止上月共缴电费'; --添加外键 alter table power add constraint FK_power foreign key (USERS_ID) references USERS (USERS_ID); --添加几个水费缴纳情况的信息信息 insert into power values('001','否','','12.3','0.5','6.15','275.5'); insert into power values('002','是',sysdate,'16','0.5','8','395.7'); insert into power values('003','否','','6','0.5','3','78.6'); --查看一下新建的admin表 select * from power
