SQL实现房屋租赁管理系统

-------------建立房屋信息-----
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