房屋出租管理系统用到的数据库(视图、存储过程、触发器)

--创建房屋出租管理系统数据库 CREATE DATABASE RoomRentManage --创建三个基本表，分别为： --出租人信息表(RenterInfo) --房屋信息表(RoomInfo) --承租客户表(CustomerInfo) USE RoomRentManage GO --RenterInfo表 --RenterID：出租人编号；RenterName：出租人姓名；RenterContact：出租人联系方式； --RenterContractID：合同编号；RenterRental：出租人租金；Remark：备注 CREATE TABLE RenterInfo ( RenterID INT PRIMARY KEY IDENTITY(1,1), RenterName NVARCHAR(30), RenterContact NVARCHAR(50), RenterContractID CHAR(8) NOT NULL, RenterRental MONEY NOT NULL, Remark VARCHAR(60) ) GO --RoomInfo表 --RoomID：房屋编号；RenterID：出租人编号；RoomType：房屋类型；RoomLocation：房屋地理位置 --RoomFloor：房屋处于第几层；RoomRatingNum：额定人数；RoomTrueNum：实际人数； --RoomArea：房屋面积；RoomPrice：价格；RoomDevice：家中设备；Remark：备注 CREATE TABLE RoomInfo ( RoomID INT PRIMARY KEY IDENTITY(1,1), RenterID INT, RoomType NVARCHAR(40), RoomLocation NVARCHAR(60), RoomFloor NVARCHAR(4), RoomRatingNum SMALLINT CHECK(RoomRatingNum>0), RoomTrueNum SMALLINT CHECK(RoomTrueNum>=0), RoomArea SMALLINT CHECK(RoomArea>0), RoomPrice MONEY NOT NULL, RoomDevice NVARCHAR(100), Remark NVARCHAR(100), FOREIGN KEY(RenterID) REFERENCES RenterInfo(RenterID) ) GO --CustomerInfo表 --CustomerID：客户编号；CustomerNAME：客户姓名；CustomerGender：客户性别；CustomerPlace：客户籍贯 --CustomerCardID：客户省份证号；RoomID：房屋编号；CustomerInDate：客户入住时间； --CustomerRentalDate：客户交租时间；RenterRental：月租金；Remark：备注 CREATE TABLE CustomerInfo ( CustomerID INT PRIMARY KEY IDENTITY(1,1), CustomerNAME NVARCHAR(30) NOT NULL, CustomerGender NCHAR(4) , CustomerPlace NVARCHAR(40), CustomerCardID CHAR(18) , RoomID INT, CustomerInDate DATETIME, CustomerRentalDate DATETIME, RenterContractID CHAR(8) NOT NULL, RenterRental MONEY NOT NULL, Remark NVARCHAR(50), FOREIGN KEY(RoomID) REFERENCES RoomInfo(RoomID) ) GO --创建RoomInfo表的视图 --RoomID：房屋编号；RenterID：出租人编号；RoomType：房屋类型；RoomLocation：房屋地理位置 --RoomFloor：房屋处于第几层；RoomRatingNum：额定人数；RoomTrueNum：实际人数； --RoomArea：房屋面积；RoomPrice：价格；RoomDevice：家中设备；Remark：备注 IF OBJECT_ID('VW_ROOMINFO') IS NOT NULL DROP VIEW VW_ROOMINFO GO CREATE VIEW VW_ROOMINFO AS SELECT RoomID AS 房屋编号,RenterID AS 出租人编号,RoomType AS 类型, RoomLocation AS 地理位置,RoomFloor AS 第几层,RoomRatingNum AS 额定人数, RoomTrueNum AS 实际人数,RoomArea AS 面积平米,RoomPrice AS 价格元,RoomDevice AS 家中设备, Remark AS 备注 FROM RoomInfo GO SELECT * FROM VW_ROOMINFO --视图：查找空房的视图 IF OBJECT_ID('VW_NULLROOM') IS NOT NULL DROP VIEW VW_NULLROOM GO CREATE VIEW VW_NULLROOM AS SELECT RoomID AS 房屋编号,RenterID AS 出租人编号,RoomType AS 类型, RoomLocation AS 地理位置,RoomFloor AS 第几层,RoomRatingNum AS 额定人数, RoomTrueNum AS 实际人数,RoomArea AS 面积平米,RoomPrice AS 价格元,RoomDevice AS 家中设备, Remark AS 备注 FROM RoomInfo WHERE RoomTrueNum=0 GO SELECT * FROM VW_NULLROOM --视图：查找房子未住满的视图 IF OBJECT_ID('VW_NOTFULLROOM') IS NOT NULL DROP VIEW VW_NOTFULLROOM GO CREATE VIEW VW_NOTFULLROOM AS SELECT RoomID AS 房屋编号,RenterID AS 出租人编号,RoomType AS 类型, RoomLocation AS 地理位置,RoomFloor AS 第几层,RoomRatingNum AS 额定人数, RoomTrueNum AS 实际人数,RoomArea AS 面积平米,RoomPrice AS 价格元,RoomDevice AS 家中设备, Remark AS 备注 FROM RoomInfo WHERE RoomRatingNum>RoomTrueNum GO SELECT * FROM VW_NOTFULLROOM --存储过程：根据出租人编号RenterID查找合同编号RenterContractID和租金RenterRental --RenterID：出租人编号；RenterName：出租人姓名；RenterContact：出租人联系方式； --RenterContractID：合同编号；RenterRental：出租人租金；Remark：备注 IF OBJECT_ID('PROC_RENTER') IS NOT NULL DROP PROC PROC_RENTER GO CREATE PROCEDURE PROC_RENTER @ID INT AS SELECT RenterContractID,RenterRental FROM RENTERINFO WHERE RenterID=@ID GO EXEC PROC_RENTER 1 select * from customerinfo --视图：CustomerInfo --CustomerID：客户编号；CustomerNAME：客户姓名；CustomerGender：客户性别；CustomerPlace：客户籍贯 --CustomerCardID：客户省份证号；RoomID：房屋编号；CustomerInDate：客户入住时间； --CustomerRentalDate：客户交租时间；RenterRental：月租金；Remark：备注 CREATE VIEW VW_CUSTOMERINFO AS SELECT CustomerID AS 客户编号,RoomID AS 房屋编号,CustomerNAME AS 姓名,CustomerGender AS 性别, CustomerPlace AS 籍贯,CustomerCardID AS 身份证号, CustomerInDate AS 入住时间,CustomerRentalDate AS 交租时间,RenterRental AS 月租金, Remark AS 备注 FROM CustomerInfo; --创建触发器：当客户解除合同时（即从客户表中删除一条记录）,则房屋信息表中的实住人数要加1 IF OBJECT_ID('TRIG_CUSINFO_DELETE') IS NOT NULL DROP TRIGGER TRIG_CUSINFO_DELETE GO CREATE TRIGGER TRIG_CUSINFO_DELETE ON CUSTOMERINFO FOR DELETE AS DECLARE @ID INT SELECT @ID=ROOMID FROM DELETED UPDATE ROOMINFO SET ROOMTRUENUM=ROOMTRUENUM-1 WHERE ROOMID=@ID GO