北大青鸟s2 SQL上机答案

USE master GO /*---检查是否已存在bbsDB数据库：查询master数据库中的系统表sysdatabases---*/ IF EXISTS (SELECT * FROM sysdatabases WHERE name = 'bbsDB') DROP DATABASE bbsDB GO --EXEC xp_cmdshell 'mkdir D:\project' --调用DOS命令创建文件夹，后续章节将讲解 /*-----建库--------*/ CREATE DATABASE bbsDB ON ( /*----数据文件的具体描述--*/ NAME = 'bbsDB_data', --主数据文件的逻辑名 FILENAME = 'e:\project\bbsDB_data.mdf' , --主数据文件的物理名 SIZE = 10 MB, --主数据文件初始大小 FILEGROWTH = 20% --主数据文件的增长率 ) LOG ON ( /*----日志文件的具体描述，各参数含义同上--*/ NAME = 'bbsDB_log', FILENAME = 'e:\project\bbsDB_log.ldf' , SIZE = 1MB, MAXSIZE = 20MB, FILEGROWTH = 10% ) GO use BBSDB go --创建bbsusers表 if exists(select * from sysobjects where name='bbsUsers') drop table bbsusers go create table bbsUsers ( UID int identity(1,1), --自动编号，标识列，主键 Uname varchar(15) not null, --昵称 Upassword varchar(10) not null, --密码 Uemail varchar(20) not null, --邮件 Ubirthday datetime not null, --生日 Usex bit not null, --性别 Uclass int null, --级别（几星级） Uremark varchar(20) null, --备注 UregDate datetime not null, --注册日期 Ustate int null, --状态（是否禁言） Upoint int null, --积分（点数） ) go --编写bbsusers表中的约束 --bbsusers表中的主键约束 alter table bbsusers add constraint PK_UID primary key (UID) --密码的默认约束 alter table bbsusers add constraint DF_Upassword default(888888) for Upassword --密码的检查约束，必须大于6位数 alter table bbsusers add constraint CK_Upassword check(Upassword like '______%') --性别的默认约束 alter table bbsusers add constraint DF_Usex default(1) for Usex --邮件的@约束 alter table bbsusers add constraint CK_Uemail check(Uemail like '__%@__%.__%') --状态的默认约束 alter table bbsusers add constraint DF_Ustate default(0) for Ustate --积分的默认约束 alter table bbsusers add constraint DF_Upoint default(20) for Upoint --日期的默认约束 alter table bbsusers add constraint DF_UregDate default(Getdate()) for UregDate --级别的默认值 alter table bbsusers add constraint DF_Uclass default(1) for Uclass --在bbsusers表中插入测试用的数据 insert into bbsusers(uname,upassword,uemail,ubirthday,uclass,uremark,upoint) values ('可卡因','HYXS007','[email protected]','1978-7-9',default,'我去公安局自首',50) insert into bbsusers(uname,upassword,uemail,ubirthday,uclass,uremark,upoint) values ('心酸果冻',default,'[email protected]','1978-2-12',default,'走遍天涯海角',2200) insert into bbsusers(uname,upassword,uemail,ubirthday,uclass,uremark,upoint) values ('东篱儿','langdong','[email protected]','1987-3-15',default,'爱迷失在天堂',1111) insert into bbsusers(uname,upassword,uemail,ubirthday,uclass,uremark,upoint) values ('Super','master','[email protected]','1888-8-8',default,'我是超人',8888) insert into bbsusers(uname,upassword,uemail,ubirthday,uclass,uremark,upoint) values ('Kind','kkMM12','[email protected]','1954-11-11',default,'东北人都是活雷锋',20) go --查询bbsusers表 select * from bbsUsers go --判断是否存在bbsSection表 if exists(select * from sysobjects where name='bbsSection') drop table bbsSection go --创建bbsSection表 create table bbsSection ( SID int identity(1,1) primary key, --版块编号 Sname varchar(32) not null, --版块名称 SmasterID int not null, --版块用户 Sprofile varchar(200) null, --版面简介 ScilckCount int null default(0), --点击率 StopicCount int null default(0), --发帖数 ) go --SmasterID的外键约束 alter table bbsSection add constraint FK_SmasterID foreign key(SmasterID) references bbsUsers(UID) go --插入测试语句 insert into bbsSection values('java技术',3,'讨论java相关技术，包括J2EE、J2ME、J2SE',500,1) insert into bbsSection values('.NET',5,'讨论Web Service/XML、NET Remoting',800,1) insert into bbsSection values('Linux/Unix社区',5,'包含系统维护与使用区，程序开发区，内核及相关技术',0,0) --查询bbsSection表中的数据 select * from bbsSection --判断数据库中是否有bbsTopic表 if exists(select * from sysobjects where name='bbsTopic') drop table bbsTopic go --创建bbsTopic表 create table bbsTopic ( TID int identity(1,1) primary key, --帖子编号，标识列 TSID int not null, --版块编号 TUID int not null, --发贴人 TreplyCount int null default(0), --回复数量 Tface int null, --发帖表情 Ttopic varchar(50) not null, --标题 Tcontents varchar(50) not null, --正文 Ttime datetime null default(Getdate()), --发帖时间 TclickCount int null default(0), --点击数 Tstate int not null default(1), --状态 TlastReply datetime null, --最后回复时间 ) go --TUID的外键约束 alter table bbsTopic add constraint FK_TUID foreign key(TUID) references bbsUsers(UID) --TSID的外键约束 alter table bbsTopic add constraint FK_TSID foreign key(TSID) references bbsSection(SID) --Tcontents的检查约束 alter table bbsTopic add constraint CK_Tcontents check(Tcontents like '______%') --TlastReply的检查约束 alter table bbsTopic add constraint CK_TlastReply check(TlastReply>Ttime) go --在bbsTopic表中插入测试数据 insert into bbsTopic values(1,3,2,1,'还是JSP中的问题','JSP文件中读取数据','2007-10-10',200,1,'2007-10-11') insert into bbsTopic values(2,2,0,2,'部署.NET的问题','项目包括WINDOWs的基础数据','2007-10-11',200,1,'2007-10-12') --查询主贴表的数据 select * from bbsTopic go --判断是否存在bbsReply if exists(select * from sysobjects where name='bbsReply') drop table bbsReply go --创建bbsReply表 create table bbsReply ( RID int identity(1,1) primary key, --帖子编号 RTID int not null, --主贴ID RSID int not null, --版块ID RUID int not null, --回帖人ID Rface int null, --回帖表情 Rcontents varchar(30) not null, --正文 Rtime datetime null default(Getdate()), --回帖时间 RclickCount int null, --点击数 ) go --RTID的外键约束 alter table bbsReply add constraint FK_RTID foreign key(RTID) references bbsTopic(TID) --RUID的外键约束 alter table bbsReply add constraint FK_RUID foreign key(RUID) references bbsUsers(UID) --RSID的外键约束 alter table bbsReply add constraint FK_RSID foreign key(RSID) references bbsSection(SID) --Rcontents的检查约束 alter table bbsReply add constraint CK_Rcontents check(Rcontents like '______%') go --插入测试数据 insert into bbsReply values (1,1,5,2,'数据库连接池在哪里',2007-10-10,100) insert into bbsReply values (1,1,4,4,'public static DataTime',2007-10-10,200) insert into bbsReply values (2,2,2,3,'帮数据员架ASP',2007-10-10,0) --查询bbsReply表的数据 select * from bbsReply --创建SQL登录用户 use master go exec sp_addlogin 'adminMaster','theMaster' ----在BBs数据库中添加用户 use bbs go exec sp_grantdbaccess adminMaster ----授予权限 grant update on bbsUsers to adminMaster grant select,delete on bbsTopic to adminMaster grant select,delete on bbsReply to adminMaster -----第三章阶段一----------- set nocount on -----不显示影响的行数信息 print '' -----打印空行 print 'SQL Server 的版本：' + @@version print '服务器的名称：' + @@servername update bbsUsers set Upassword = '1234' where Uname = '可卡因' print '执行上条语句产生的错误号：' + convert(varchar(5),@@error) go --------------------版主查看可卡因的情况--------------------- set nocount on print '' print '' print '个人资料如下' select 昵称=Uname,等级=Uclass,个人说明=Uremark,积分=Upoint from bbsUsers where Uname = '可卡因' declare @userID int select @userID = UID from bbsUsers where Uname = '可卡因' print '可卡因发贴如下：' select 发贴时间=convert(varchar(20),Ttime,111),点击率=TclickCount,主题=Ttopic,内容=Tcontents from bbsTopic where TuID = @userID print '可卡因回贴如下：' select 回贴时间=convert(varchar(20),Rtime,111),点击率=RclickCount,内容=Rcontents from bbsReply where RuID = @userID declare @point int select @point = Upoint from bbsUsers where UID = @userID if(@point > 30) print '可卡因的权限：有权发贴' else print '可卡