SQL上机练习加课后练习所有代码

if exists(select * from master..sysdatabases where name='bbsDB') drop database bbsDB exec xp_cmdshell 'mkdir D:\project' go create database bbsDB on ( name='bbsDB_data', filename='D:\project\bbsDB_data.mdf', size=10mb, filegrowth=20% ) log on ( name='bbsDB_log', filename='D:\project\bbsDB_log.ldf', size=3mb, maxsize=20mb, filegrowth=10% ) go use bbsDB if exists(select * from sysobjects where name='bbsUsers') drop table bbsUsers create table bbsUsers ( UID INT IDENTITY (1,1) not null, Uname VARCHAR (15) not null, Upassword VARCHAR (10), Uemail VARCHAR (20), Ubirthday datetime not null, Usex bit not null, Uclass int , Uremark varchar (20), UregDate datetime not null, Ustate int , Upoint int ) go select * from bbsUsers alter table bbsUsers add constraint PK_UID primary key (UID) alter table bbsUsers add constraint DF_Upassword default (888888)for Upassword alter table bbsUsers add constraint CK_Upassword check (len(Upassword)>=6) alter table bbsUsers add constraint CK_Uemail check (Uemail like '%@%') alter table bbsUsers add constraint DF_Usex default (1) for Usex alter table bbsUsers add constraint DF_Uclass default (1)for Uclass alter table bbsUsers add constraint DF_UregDate default (getdate())for UregDate alter table bbsUsers add constraint DF_Ustate default (0)for Ustate alter table bbsUsers add constraint DF_Upoint default (20)for Upoint go use bbsDB create table bbsSection ( SID int identity (1,1) not null, Sname varchar (32) not null, SmasterID int not null, Sprofile varchar (20) , SclickCount int , StopicCount int ) go alter table bbsSection add constraint PK_SID primary key (SID) alter table bbsSection add constraint FK_smasterID foreign key(SmasterID) references bbsUsers(UID) alter table bbsSection add constraint DF_SclikCount default (0) for SclickCount alter table bbsSection add constraint DF_StopicCount default (0) for StopicCount go create table bbsTopic ( TID int identity(1,1) not null, TsID int not null, TuID int not null, TreplyCount int , Tface int , Ttopic varchar (20) not null, Tcontents varchar (30) not null, Ttime datetime , TclickCount int , Tstate int not null, TlastReply datetime ) go alter table bbsTopic add constraint PK_TID primary key (TID) alter table bbsTopic add constraint FK_TsID foreign key(TsID) references bbsSection(SID) alter table bbsTopic add constraint FK_TuID foreign key(TuID) references bbsUsers(UID) alter table bbsTopic add constraint DF_TreplyCount default (0) for TreplyCount alter table bbsTopic add constraint CK_Tcontents check (len(Tcontents)>6) alter table bbsTopic add constraint DF_Ttime default (getdate()) for Ttime alter table bbsTopic add constraint DF_TclickCount default (0) for TclickCount alter table bbsTopic add constraint DF_Tstate default (1) for Tstate go create table bbsReply ( RID int identity (1,1) not null, RtID int not null, RsID int not null, RuID int not null, Rface int , Rcontents varchar (30) not null, Rtime datetime , RclickCount int ) go alter table bbsReply add constraint PK_RID primary key(RID) alter table bbsReply add constraint FK_RtID foreign key(RtID) references bbsTopic(TID) alter table bbsReply add constraint FK_RsID foreign key(RsID) references bbsSection(SID) alter table bbsReply add constraint FK_RuID foreign key(RuID) references bbsUsers(UID) alter table bbsReply add constraint CK_Rcontents check (len(Rcontents)>6) alter table bbsReply add constraint DF_Rtime default (getdate()) for Rtime go exec sp_addlogin 'adminMaster','theMaster' use bbsDB exec sp_grantdbaccess 'adminMaster' grant select,delete on bbsTopic to adminMaster grant select,delete on bbsReply to adminMaster grant update on bbsUsers to adminMaster INSERT bbsUsers(Uname,Upassword,Uemail,Ubirthday,Usex,Uclass,Uremark,UregDate,Ustate,Upoint) VALUES('可卡因','HYXS007','ss@hotmail.com','1978-07-09',1,1,'我要去公元',DEFAULT,1,200) INSERT bbsUsers(Uname,Upassword,Uemail,Ubirthday,Usex,Uclass,Uremark,UregDate,Ustate,Upoint) VALUES('心酸果冻',DEFAULT,'yy@hotmail.com','1978-07-09',0,2,'走便天涯',DEFAULT,2,600) INSERT bbsUsers(Uname,Upassword,Uemail,Ubirthday,Usex,Uclass,Uremark,UregDate,Ustate,Upoint) VALUES('冬篱儿','fangdong','bb@sohu.com','1978-07-09',1,3,'爱迷失在',DEFAULT,4,1200) INSERT bbsUsers(Uname,Upassword,Uemail,Ubirthday,Usex,Uclass,Uremark,UregDate,Ustate,Upoint) VALUES('Super','master','dd@p.com','1978-07-09',1,5,'BBS大斑竹',DEFAULT,1,5000) INSERT bbsSection (Sname,SmasterID,Sprofile,SclickCount,StopicCount) VALUES('Java技术',3,'讨论Java相关.',500,1) INSERT bbsSection (Sname,SmasterID,Sprofile,SclickCount,StopicCount) VALUES('NET',5,'讨论Web Service.',800,1) INSERT bbsSection (Sname,SmasterID,Sprofile,SclickCount,StopicCount) VALUES('Linux/Unix社区',5,'包含系统维护与',0,0) INSERT bbsTopic (TsID,TuID,TreplyCount,Tface,Ttopic,Tcontents,Ttime,TclickCount,Tstate,TlastReply) VALUES(1,3,2,1,'还是isp中','isp文件中读',DEFAULT,200,1,DEFAULT) INSERT bbsTopic (TsID,TuID,TreplyCount,Tface,Ttopic,Tcontents,Ttime,TclickCount,Tstate,TlastReply) VALUES(2,2,0,2,'部署...net中','项目中包括wi..',DEFAULT,0,1,DEFAULT) INSERT bbsReply (RtID,RsID,RuID,Rface,Rcontents,Rtime,RclickCount) VALUES(1,1,5,2,'数据库连接池在....',DEFAULT,100) INSERT bbsReply (RtID,RsID,RuID,Rface,Rcontents,Rtime,RclickCount) VALUES(1,1,4,4,'public static DataSo....',DEFAULT,200) INSERT bbsReply (RtID,RsID,RuID,Rface,Rcontents,Rtime,RclickCount) VALUES(2,2,2,3,'帮测试人元架ASP.NET环....',DEFAULT,0) --删除数据库 drop database bbsDB