开发工具:
文件大小: 3mb
下载次数: 0
上传时间: 2009-10-24
详细说明： SQLservice 数据库 use master go xp_cmdshell 'mkdir d:\project', NO_OUTPUT --创建文件夹project,xp_cmdshell为系统存储过程 --检验数据库是否存在，如果为真，删除此数据库-- IF EXISTS(SELECT NAME FROM master.dbo.SYSDATABASES WHERE NAME=N'stuDB') DROP DATABASE stuDB GO --创建数据库-- CREATE DATABASE stuDB O N (NAME=N'stuDB', USE stuDB --判断此表是否存在，如果存在，删除此表-- IF EXISTS(SELECT NAME FROM stuDB.dbo.SYSOBJECTS WHERE NAME=N'stuMarks') DROP TABLE stuMarks GO --创建从表stuMarks-- CREATE TABLE stuMarks (ExamNo CHAR(7) NOT NULL, stuNo NCHAR(6) NOT NULL, writtenExam SMALLINT NOT NULL, LabExam SMALLINT NOT NULL) GO --为从表stuMarks创建约束-- ALTER TABLE stuMarks ADD CONSTRAINT PK_ExamNo PRIMARY KEY(ExamNo), CONSTRAINT CK_ExamNo CHECK(ExamNo LIKE 'S2718[0-9][0-9]'), CONSTRAINT FK_stuNo FOREIGN KEY(stuNo) REFERENCES stuInfo(stuNo), CONSTRAINT CK_writtenExam CHECK(writtenExam BETWEEN 0 AND 100), CONSTRAINT DF_writtenExam DEFAULT 0 FOR writtenExam, CONSTRAINT CK_LabExam CHECK(LabExam BETWEEN 0 AND 100), CONSTRAINT DF_LabExam DEFAULT 0 FOR LabExam GO --为stuInfo插入数据-- INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress)VALUES('张秋丽','s25301','男',18,'北京海淀') INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress) VALUES('李斯文','s25303','女',22,'河南洛阳') INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge) VALUES('李文才','s25302','男',31) INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress) VALUES('欧阳俊雄','s25304','男',28,'新疆威武哈') GO --为stuMarks插入数据-- INSERT INTO stuMarks(ExamNo,stuNo,writtenExam,LabExam) VALUES('S271811','s25303',80,58) INSERT INTO stuMarks(ExamNo,stuNo,writtenExam,LabExam) VALUES('S271813','s25302',50,90) INSERT INTO stuMarks(ExamNo,stuNo,writtenExam,LabExam) VALUES('S271816','s25301',77,82) GO --数据查询-- select * from stumarks where 1=2 select * from stuinfo where 1=12 insert into stuinfo values('梅超风','S25318','女',22,'南京') insert into stumarks values('S271818','S25318',60 ,50) select * into s1 from stuinfo select * into s2 from stumarks ----显示缺考 declare @p1 int,@p2 int select @p1=count(*) from stuinfo select @p2=count(*) from stumarks select 实到人数=@p1, 考试人数=@p2, 缺考人数=@p1-@p2 ----循环加分 declare @avgW float,@avgL float declare @subject varchar(6) declare @addedValue int set @addedvalue=0 select @avgW=avg(writtenexam) ,@avgL=avg(labExam) from stumarks if(@avgW<@avgL) begin while(1=1) begin set @subject='笔试' set @addedvalue=@addedvalue+1 update stumarks set writtenexam=writtenexam+1 if(select max(writtenexam) from stumarks)>=97 break end end else begin while(1=1) begin set @subject='机试' set @addedvalue=@addedvalue+1 update stumarks set labexam=labexam+1 if(select max(labexam) from stumarks)>=97 break end end ----显示加分的情况 select 加分科目=@subject, 加分值=@addedValue ----显示加分后的情况 select 姓名=stuname, 学号=si.stuNo, 笔试=case when writtenexam is null then '缺考' else cast(writtenexam as varchar(5)) end, 机试=case when labexam is null then '缺考' else cast(labexam as varchar(5)) end, 是否通过=case when writtenexam>=60 and labExam>=60 then '是' else '否' end from stuinfo si left join stumarks sm on si.stuno=sm.stuno ----显示通过率 declare @p3 int select @p3=count(*) from stumarks where writtenexam>=60 and labExam>=60 select 总人数=@p1, 通过人数=@p3, 通过率=(cast(@p3*100/@p1 as varchar(10))+'%') ...展开收缩
(系统自动生成,下载前可以参看下载内容)
下载文件列表
相关说明
- 本站资源为会员上传分享交流与学习,如有侵犯您的权益,请联系我们删除.
- 本站是交换下载平台，提供交流渠道，下载内容来自于网络，除下载问题外，其它问题请自行百度。
- 本站已设置防盗链，请勿用迅雷、QQ旋风等多线程下载软件下载资源，下载后用WinRAR最新版进行解压.
- 如果您发现内容无法下载，请稍后再次尝试；或者到消费记录里找到下载记录反馈给我们.
- 下载后发现下载的内容跟说明不相乎，请到消费记录里找到下载记录反馈给我们，经确认后退回积分.
- 如下载前有疑问，可以通过点击"提供者"的名字，查看对方的联系方式，联系对方咨询.
