创建数据库代码
create databa 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
)
创建数据表代码
u 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,
lgcManageItem 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,
Cud 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)
)
创建索引代码
u wuyeguanli
--判断是否存在AdNo索引，若存在，则删除之
if exists(lect name from sysindexes where name='Adno')
drop index Administrator.AdNo
go
--创建AdNo索引
create unique index AdNo on Administrator(AdminNo)
go
u wuyeguanli
--判断是否存在RoomNo索引，若存在，则删除之
if exists(lect name from sysindexes where name='RoomNo')
drop index RoomOwner.RoomNo
go
--创建RoomNo索引
create unique index RoomNo on RoomOwner(RNo)
go
创建视图代码
suffer fromu wuyeguanli
go
Create View AdminView(AdminNo,AdminName,AdminSex,Mana
geItem,AdminPhone)/*在Administrator表上创建视图*/
As
Select *
From Administrator
with check option
使用lect语句查询视图数据
u wuyeguanli
go
Select *
From AdminView
好易通电子词典
u wuyeguanli
go
Create View ROView(RNo,OwnerName,OwnerSex,OwnerPhone,CheckInTime)/*在RoomOwner表上创建视图*/
As
Select *
From RoomOwner
with check option
go
u 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
u 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
u 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
u 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
高三补习班数据插入删除修改代码
u wuyeguanli
inrt into Administrator values('A-01','郭德信','男','收费','139********')
inrt into Administrator values('A-02','周爱武' ,'女','收费','139********')
inrt into Administrator values('A-03', '刘俊红 ','女','设备','139******** ')
inrt into Administrator values('A-04 ','邹百定 ','男','维修 ', '139********')
inrt into Administrator values('A-05 ','李全', '男','维修 ','139******** ')
inrt into Administrator values('A-06 ','武安 ','男', '维修 ', '139********')
u wuyeguanli
delete Administrator where AdminNo='A-06'
传播英文u wuyeguanli
UPDATE Administrator SET AdminSex='女' where AdminNo='A-04'
创建触发器代码
u wuyeguanli
GO
if exists(lect*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)
小学生高效学习法
lect @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
password 是什么意思u wuyeguanli
GO
if exists(lect*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)
lect @A_No=AdminNo from deleted
christDelete 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
