北大青鸟5.0第二学期SQL2005上机和理论原代码

第2章 理论 use master go if exists(select * from sysdatabases where name='stuDB') drop database stuDB go create database stuDB on ( name='stuDB_data', filename='h:\project\stuDB_data.mdf', size=3, filegrowth=15% ) log on ( name='stuDB_log', filename='h:\project\stuDB_data.ldf' ) use stuDB go if exists(select * from sysobjects where name='stuInfo') drop table stuInfo go create table stuInfo ( stuname varchar(20) not null, stuno varchar(10) primary key check (stuno like 's253%'), stusex varchar(2) not null check (stusex = '男' or stusex ='女'), stuage int not null check (stuage between 15 and 40), stuseat int identity (1,1) check (stuseat between 1 and 30), stuaddress varchar(30) default ('地址不详') ) go select * from stuInfo go insert into stuInfo values ('张秋丽','s25301','男',18,1,'北京海淀') insert into stuInfo values ('李斯文','s25303','女',22,2,'河南洛阳') insert into stuInfo values ('李文才','s25302','男',31,3,'地址不详') insert into stuInfo values ('欧阳俊雄','s25304','男',28,4,'新疆威武哈') go exec sp_addlogin 'banzhuren','111111' exec sp_addlogin 'jiaoyuan','123456' go use stuDB go exec sp_grantdbaccess 'banzhuren' exec sp_grantdbaccess 'jiaoyuan' go grant select,update on stuInfo to banzhuren grant select on stuInfo to jiaoyuan 上机 use master go if exists (select * from sysdatabases where name='bbsDB') drop database bbsDB go create database bbsDB on ( name='bbsDB_data', FILENAME='h:\project\bbsDB_data.mdf', size=10, filegrowth=20% ) log on ( name='bbsDB_log', FILENAME='h:\project\bbsDB_data.ldf', size=3, maxsize=20, filegrowth=10% ) go use bbsDB go if exists (select * from sysobjects where name='bbsUsers') drop table bbsUsers go 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 null, upoint int null ) go select * from bbsUsers go 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 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 alter table bbsUsers add constraint ck_uemail check (uemail like '%@%') alter table bbsUsers add constraint ck_upassword check (len(upassword)>=6) go insert into bbsUsers values('可卡因','HYS007','ss@hotmail.com','1978-07-09','true','1','我要去公园','2007-10-10 11:05:57','1','600') insert into bbsUsers values('心酸果冻','888888','yy@hotmail.com','1980-02-12','false','2','走遍天涯海角','2007-10-10 11:05:57','2','2200') insert into bbsUsers values('冬篱儿','fangdong','bb@sohu.com','1976-10-03','true','3','爱迷失在天堂','2007-10-10 11:05:57','4','1200') insert into bbsUsers values('Super','master','dd@p.com','1977-05-16','true','5','BBS大斑竹','2007-10-10 11:05:57','1','5000') go use bbsDB go if exists (select * from sysobjects where name='bbsSection') drop table bbsSection go create table bbsSection ( sid int identity (1,1) primary key, sname varchar(32) not null , smasterid int not null , sprofile varchar(50), sclickcount int default (0), stopiccount int default (0) ) go select * from bbsSection insert into bbsSection values ('Java技术','3','讨论Java相关技术，包括J2EE、J2ME、J2SE、……','500','1') insert into bbsSection values ('.NET','5','讨论Web Service/XML、NET Remoting、Duwami……','800','1') insert into bbsSection values ('Linux/Unix社区','5','包含系统维护与使用区，程序开发区，内核及……','0','0') go use bbsDB if exists(select * from sysobjects where name='bbsTopic') drop table bbsTopic create table bbsTopic ( tid int primary key identity (1,1), tsid int not null foreign key references bbsSection (sid), tuid int not null foreign key references bbsUsers (uid), treplycount int default (0), tface int, ttopic varchar(20) not null, tcontents varchar (30) not null check (len(tcontents)>=6), ttime datetime default (getdate()), tclickcount int default (0), tstate int not null default (1), tlastreply datetime check ('ttime'>'tlastreply') ) go select * from bbsTopic insert into bbsTopic values ('1','2','2','1','还是jsp中的问题','jsp文件中读取……','2007-10-10','200','1','2007-10-11') insert into bbsTopic values ('2','1','0','2','部署.net中的问题','项目包括windows','2007-10-11','0','1','2007-10-12') use bbsDB if exists(select * from sysobjects where name='bbsReply') drop table bbsReply go create table bbsReply ( rid int primary key identity(1,1), rtid int not null foreign key references bbsTopic(tid), rsid int not null foreign key references bbsSection(sid), ruid int not null foreign key references bbsUsers(uid), rface int , rcontents varchar(30) not null check (len(rcontents)>=6), rtime datetime default (getdate()), rclickcount int ) go select * from bbsReply insert into bbsReply values (1,1,4,2,'数据库连接池在……','2007-10-10',100) insert into bbsReply values (1,1,3,4,'public static Data……','2007-10-10',200) insert into bbsReply values (2,2,1,3,'帮测试人员架AS……','2007-10-10',0) go exec sp_addlogin 'adminMaster','theMaster' go use bbsDB go exec sp_grantdbaccess 'adminMaster' go grant select,delete on bbsTopic to adminMaster grant select,delete on bbsReply to adminMaster grant select,update on bbsUsers to adminMaster 第3章 理论 use stuDB if exists(select * from sysobjects where name='stuMarks') drop table stuMarks go create table stuMarks ( ExamNo varchar(10) primary key check (ExamNo like 's2718%'), stuNo varchar(10) not null check (stuNo like 's253%'), writtenExam int not null , LabExam int not null ) go insert into stuMarks values ('s271811','s25303',80,58) insert into stuMarks values ('s271813','s25302',50,90) insert into stuMarks values ('s271815','s25302',65,0) insert into stuMarks values ('s271816','s25301',80,58) select * from stuMarks go use stuDB update stuMarks set labexam=labexam+2 declare @count int while(1=1) begin select @count=count(*) from stuMarks where labexam<60 if(@count>0) begin update stuMarks set labexam=labexam+2 update stuMarks set labexam=100 where labexam+2>100 end else break end print '加分后的成绩如下：' select stuNo as 学号, case when labexam<60 then 'E' when labexam between 60 and 69 then 'D' when labexam between 70 and 79 then 'C' when labexam between 80 and 89 then 'B' else 'A' end as 成绩 from stuMarks 上机 --阶段1 use bbsDB go select * from bbsUsers select * from bbsTopic select * from bbsReply select * from bbsSection go print 'SQL Server的版本：'+@@version print '服务器的名称：'+@@servername update bbsUsers set upassword='1234' where uname='可卡因' print '执行上条语句产生的错误号：'+convert(varchar(5),@@error) go set nocount on print ' ' print '个人资料如下' select uname as 昵称, uclass as 等级, uremark as 个人说明,