-------------建立房屋信息-----
CREATE TABLE roominf(
rid varchar(10) constraint rnum_notn NOT NULL,
raddr varchar(30) constraint raddr_notn NOT NULL,
rstyle varchar(30) constraint rstyle_notn NOT NULL,
chaoxiang varchar(30) constraint chaoxiang_notn NOT NULL,
rarea varchar(40) constraint rarea_notn NOT NULL,
rmoney int(40) constraint rmoney_notn NOT NULL,
picture varchar(40) constraint rpic_notn NOT NULL,
PRIMARY KEY (rid)
)
------创建租户信息-----
CREATE TABLE userinf (
userid varchar(30) constraint userid_notn NOT NULL,
username varchar(30) constraint username_notn NOT NULL,
usex varchar(30) constraint usex_notn NOT NULL,
unum varchar(30) constraint unum_notn NOT NULL,
PRIMARY KEY (userid)
)
ALTER TABLE userinf
ADD umoney VARCHAR(255)
----创建租聘信息表----------
CREATE TABLE roomrent(
rid varchar(10) constraint rnum_notn NOT NULL,
userid varchar(30) constraint unum_notn NOT NULL,
starttime varchar(30) constraint unum_notn NOT NULL,
monthnum varchar(30) constraint unum_notn NOT NULL,
PRIMARY KEY (rid ,userid),
)
---添加租聘信息表starttime列
ALTER TABLE roomrent
ALTER COLUMN starttime Date NOT NULL;
alter table userinf
add rmoney int
---租聘信息表添加月租价格列
ALTER TABLE roomrent
add rmoney int
-----查看北京地区的租房信息
CREATE VIEW beijin AS
SELECT
rstyle,
rarea,
rmoney,
starttime,
monthnum,
raddr
FROM
roominf,
roomrent
WHERE
roominf.rid = roomrent.rid AND
raddr = '北京';
-------------------上海平均房价---------------
create view shanghai_avg
as select avg(rmoney)as'平均房租'
from roominf
where raddr='上海'
select * from shanghai_avg
drop view shanghai_avg
------未租出去的房子------
create view emptyroom
as select roominf.rid,raddr,rstyle,chaoxiang,rarea,picture,rmoney from roominf
where rid not in(select roomrent.rid from roominf,roomrent where roominf.rid=roomrent.rid)
select * from emptyroom
drop view emptyroom
---------已经交房租低于1500的上海房子信息-----
create view shanghai5500
as select roominf.rid,raddr,rstyle,chaoxiang,rarea,picture,rmoney as"已经支付月租" from roominf,roomrent
where roominf.rid=roomrent.rid and hmoney<=5500 and raddr='上海'
GROUP BY
roominf.rid,
raddr,
rstyle,
chaoxiang,
rarea,
rmoney,
picture;
select * from shanghai5500
drop view shanghai5500
---加唯一建rid
alter table roominf
add constraint uni_rid unique(rid)
---加唯一健uid
alter table userinf
add constraint uni_userid unique(userid)
------朝向为北并且是上海的房子情况
create view beimianshanghai
as select roominf.rid,raddr,rstyle,chaoxiang,rarea,picture,rmoney from roominf,roomrent
where roominf.rid=roomrent.rid and chaoxiang like'%北%' and raddr='上海'
select * from beimianshanghai
drop view beimianshanghai
-----hmoney默认值为0
alter table roomrent
ADD CONSTRAINT hmoney_def DEFAULT 0 for hmoney
------
alter table userinf
alter column umoney int not null
-------
alter table roomrent
add constraint uni_rent unique(rid)
--------------存储过程--------------------------------------------------------------------------------------------
---存储过程1出租----
create proc 出租
(
@rid varchar(10),
@userid varchar(30),
@username varchar(30),
@usex varchar(30),
@unum varchar(30),
@umoney int,
@starttime date,
@lasttime date
)
as
begin
IF EXISTS (SELECT * FROM emptyroom WHERE rid = @rid)
begin
declare @newrmoney int
--declare @newrid int
if not exists(select * from userinf where @userid=userid)
begin
insert into userinf (userid,username,usex,unum,umoney)
values (@userid,@username,@usex,@unum,@umoney);
end