创建数据库代码
create database wuyeguanli
on(
name=物业管理,filename='F:\MYSQL\物业管理.mdf',
size=10MB,maxsize=100MB,FILEGROWTH=5MB
)
LOG ON(
name=物业管理日志,filename='F:\MYSQL\物业管理日志.ldf',
size=10MB,maxsize=50MB,FILEGROWTH=5MB
)
创建数据表代码
use wuyeguanli
Create table Administrator(
AdminNo char(20) primary key not null,
AdminName char(20) not null,
AdminSex char(2) check(AdminSex='男' or AdminSex='女') not null,
ManageItem char(20) not null,
AdminPhone char(20) not null
)
Create table RoomOwner(
RNo char(20) primary key not null,
OwnerName char(20) not null,
OwnerSex char(2) check(OwnerSex='男' or OwnerSex='女') not null,
OwnerPhone char(20) not null,
CheckInTime char(20) not null
)
Create table RepairManagement(
RepNum char(20) Primary key not null,
RTimes int check(RTimes>0 and RTimes<9999)not null,
RNo char(20) not null,
OwnerName char(20) not null,
AdminNo char(20) not null,
RTime datetime,
Rcost int not null,
Foreign key (Rno) References RoomOwner(RNo),
Foreign key (AdminNo) References Administrator(AdminNo)
)
Create table ParkManagement(
ParkNo char(20) Primary key not null,
RNo char(20) not null,
PStartTime datetime,
PEndTime datetime,
AdminNo char(20) not null,
PCharge int not null,
Foreign key (Rno) References RoomOwner(RNo),
Foreign key (AdminNo) References Administrator(AdminNo)
)
Create table Equipment(
EquNo char(20) primary key not null,
EquName char(20) not null,
ELendTime datetime not null,
ERetTime datetime not null,
AdminNo char(20) not null,
Rno char(20) not null,
Foreign key (Rno) References RoomOwner(RNo),
Foreign key (AdminNo) References Administrator(AdminNo)
)
Create table ChargeManagement(
Cno1 char(20) Primary key not null,
Cname char(20) not null,
CStartTime datetime,
CEndTime datetime,
Cused int not null,
CPrice int not null,
AdminNo char(20) not null,
Rno char(20) not null,
Foreign key (AdminNo) References Administrator(AdminNo),
Foreign key (Rno) References RoomOwner(RNo)
)
Create table Charge(
Cno2 char(20) Primary key not null,
Cname char(20) not null,
CCharge int not null,
CTime datetime not null,
CStatus char(2) check(CStatus = '是' or CStatus = '否') not null,
Rno char(20) not null,
Foreign key (Rno) References RoomOwner(RNo)
)
创建索引代码
use wuyeguanli
--判断是否存在AdNo索引，若存在，则删除之
if exists(select name from sysindexes where name='Adno')
drop index Administrator.AdNo
go
drop table if exists admin--创建AdNo索引
create unique index AdNo on Administrator(AdminNo)
go
use wuyeguanli
--判断是否存在RoomNo索引，若存在，则删除之
if exists(select name from sysindexes where name='RoomNo')
drop index RoomOwner.RoomNo
go
--创建RoomNo索引
create unique index RoomNo on RoomOwner(RNo)
go
创建视图代码
use wuyeguanli
go
Create View AdminView(AdminNo,AdminName,AdminSex,Mana
geItem,AdminPhone)/*在Administrator表上创建视图*/
As
Select *
From Administrator
with check option
使用select语句查询视图数据
use wuyeguanli
go
Select *
From AdminView
use wuyeguanli
go
Create View ROView(RNo,OwnerName,OwnerSex,OwnerPhone,CheckInTime)/*在RoomOwner表上创建视图*/
As
Select *
From RoomOwner
with check option
go
use wuyeguanli
go
Create View ParkView(ParkNo,Rno,PStartTime,PEndTime,AdminNo,PCharge)
As
Select
ParkNo,ParkManagement.Rno,PStartTime,PEndTime,ParkManagement.AdminNo,PCharge
From ParkManagement,Administrator,RoomOwner
Where AdminiStrator.AdminNo=ParkManagement.AdminNo and RoomOwner.RNo=ParkManagement.RNo
go
use wuyeguanli
go
Create View EquView(EquNo,EquName,ElendTime,ERetTime,AdminNo,Rno)
As
Select  EquNo,EquName,ElendTime,ERetTime,Equipment.AdminNo,Equipment.RNo
From Equipment,Administrator,RoomOwner
Where Administrator.AdminNo=Equipment.AdminNo and RoomOwner.Rno=Equipment.Rno
go
use wuyeguanli
go
Create View RepMView(RepNum,RTimes,RNo,OwnerName,AdminNo,RTime,Rcost)
As
Select  RepNum,RTimes,RepairManagement.RNo,RepairManagement.OwnerName,RepairManagement.AdminNo,RTime,Rcost
From RepairManagement,Administrator,RoomOwner
Where Administrator.AdminNo=RepairManagement.AdminNo and RoomOwner.Rno=RepairManagement.Rno
go
use wuyeguanli
go
Create View ChaView(Cno2,Cname,CCharge,CTime,CStatus,Rno)
As
Select  Cno2,Cname,CCharge,CTime,CStatus,Charge.Rno
From Charge,Administrator,RoomOwner
Where RoomOwner.Rno=Charge.Rno
go
数据插入删除修改代码
use wuyeguanli
insert into Administrator values('A-01','郭德信','男','收费','139********')
insert into Administrator values('A-02','周爱武' ,'女','收费','139********')
insert into Administrator values('A-03', '刘俊红 ','女','设备','139******** ')
insert into Administrator values('A-04 ','邹百定 ','男','维修 ', '139********')
insert into Administrator values('A-05 ','李全', '男','维修 ','139******** ')
insert into Administrator values('A-06 ','武安 ','男', '维修 ', '139********')
use wuyeguanli
delete Administrator where AdminNo='A-06'
use wuyeguanli
UPDATE  Administrator  SET  AdminSex='女' where AdminNo='A-04'
创建触发器代码
use wuyeguanli
GO
if exists(select*from sysobjects where name='Delete_T1' and type='TR')
DROP TRIGGER Delete_T1
GO
Create Trigger Delete_T1 --创建触发器Delete_T1
On RoomOwner
instead of Delete
As
declare @R_No char(20)
select @R_No=RNo  from deleted
Delete from RepairManagement  where RNo=@R_No
Delete  from ChargeManagement  where RNo=@R_No
Delete  from Charge  where RNo=@R_No
Delete  from Equipment  where RNo=@R_No
Delete  from ParkManagement  where RNo=@R_No
Delete  from RoomOwner  where RNo=@R_No
GO
use wuyeguanli
GO
if exists(select*from sysobjects where name='Delete_T2' and type='TR')
DROP TRIGGER Delete_T2
GO
--创建触发器D
elete_T2
Create Trigger Delete_T2
On Administrator  instead of Delete
As
declare @A_No char(20)
select @A_No=AdminNo  from deleted
Delete  from RepairManagement  where AdminNo=@A_No
Delete  from ChargeManagement  where AdminNo=@A_No
Delete  from Equipment where AdminNo=@A_No
Delete  from ParkManagement  where AdminNo=@A_No
Delete  from Administrator  where AdminNo=@A_No
GO

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。