1、1实验二2.实验内容（1）CREATE DATABASE StuInfo ON PRIMARY (NAME = StuInfo _Data,，FILENAME = D: StuInfo.Mdf,SIZE =3MB,MAXSIZE = 10MB,FILEGROWTH =1 MB)LOG ON(NAME = StuInfo _Log ，FILENAME = D: StuInfo _Log.Ldf,SIZE = 1MB,MAXSIZE =5MB,FILEGROWTH = 1MB)GO（2）-创建学生表 T_StudentUSE StuinfoGOCREATE TABLE t_student(S_nu
2、mber char(8) NOT NULL PRIMARY KEY,S_name char(10) NOT NULL,Sex char(2) NULL CHECK(Sex=男 or Sex=女),Birthday datetime ,Nation nvarchar(10) ,Politics varchar(10) ,Department nvarchar(12) ,Address varchar(60) ,PostalCode nvarchar(10) ,Phone varchar(24) ,)-创建课程表 T_CourseUSE StuinfoGO2CREATE TABLE t_cours
3、e(C_number char(4) NOT NULL PRIMARY KEY,C_name char(10) NOT NULL,Teacher char(10) ,Hours int NOT NULL,Credit int ,Type nvarchar(12) ,) -创建课程表 T_ScoreUSE StuinfoGOCREATE TABLE t_score(S_number char(8) NOT NULL REFERENCES t_student (S_number),C_number char(4) NOT NULL REFERENCES t_course (C_number),Sc
4、ore numeric(5, 1) CHECK(Score=0 and Score20 and sex=女（5）USE stuinfogoSELECT t_student.s_number,s_name,sum(score) FROM t_student,t_scoreWHERE t_score.s_number=t_student.s_numberGROUP BY t_student.s_number,s_nameHAVING sex=男 4思考与练习（1） use stuinfogoSelect s_name,birthday, year(getdate()-year(birthday)
5、年龄 from t_student Where sex=男use stuinfogoSelect s_name,datename(year,birthday)+年+datename(month,birthday)+月 as 出生年月, year(getdate()-year(birthday) 年龄 from t_student Where sex=男（2） use stuinfogoselect *,count(sex) 人数 from t_studentgroup by s_number,birthday,sex,s_name having sex=女5compute sum(count(
6、sex)(3) use stuinfogoselect t_course.c_name 课程名称,t_score.score 成绩 from t_course,t_scorewhere t_course.c_number=t_score.c_number and c_name=SQL Servercompute avg(score)compute sum(score)goselect count(*) sql server及格学生人数 from t_score where score=60 and c_number=1select count(*) sql server不及格学生人数 from
7、 t_score where score=60 AND score=70 AND score=80 AND score=90 THEN 优秀ELSE 无成绩ENDFROM t_course JOIN t_score ON t_course.c_number=t_score.c_numberwhere c_name=电子商务（6）-声明游标DECLARE stu_cursor CURSORGLOBAL SCROLL DYNAMICFOR SELECT t_student.s_number,s_name,c_name,scoreFROM t_student JOIN t_score ON t_st
8、udent.s_number=t_score.s_numberjoin t_course on t_course.c_number=t_score.c_number-打开游标OPEN stu_cursor-第一次读取，得到结果集的首行记录FETCH NEXT FROM stu_cursor-循环读取结果集中剩余的数据行9WHILE FETCH_STATUS=0BEGINFETCH NEXT FROM stu_cursorEND-关闭游标 stu_cursorCLOSE stu_cursor-释放（删除）游标 stu_cursorDEALLOCATE stu_cursor（7）（8）WAITFO
9、R DELAY 00:00:05select * from t_coursewhere type=专业核心课实验七2.实验内容（1） USE STUINFOGO-创建存储过程 Proc_scoreCREATE PROC Proc_scoreWITH RECOMPILE,ENCRYPTIONASSELECT S_number,scoreFROM t_score WHERE C_number=2-执行存储过程 Proc_scoreEXEC Proc_scoreUSE STUINFOGO-创建存储过程 Proc_listCREATE PROC Proc_listcno char(4) ASSELEC
10、T TOP 5 WITH TIES S_number,score10FROM t_score WHERE C_number=cnoORDER BY score DESC,S_number ASC-执行存储过程 Proc_list，查询“1”号课程成绩排名前 5 位的学生成绩记录。EXEC Proc_list 1其中， “1”为要传递给存储过程 Proc_list 的输入参数。创建和执行带输入和输出参数的存储过程 ProcAvgScoreCREATE PROC ProcAvgScorecno char(4),maxscore real OUTPUT,minscore real OUTPUT,av
11、gscore numeric(5,2) OUTPUT ASSELECT maxscore=MAX(score),minscore=MIN(score),avgscore=AVG(score)FROM t_score WHERE C_number=cnoGROUP BY C_number/*执行存储过程 ProcAvgScore，查询选修“1”号课程学生成绩的最高分，最低分和平均分*/DECLARE maxscore real,minscore real,avgscore numeric(5,2) EXEC ProcAvgScore 1,maxscore OUTPUT,minscore OUTP
12、UT,avgscore OUTPUT PRINT 该门课程成绩最高分为：+CAST(maxscore AS char(6)PRINT 该门课程成绩最高分为：+CAST(minscore AS char(6)PRINT 该门课程成绩最高分为：+CAST(avgscore AS char(6)（2）使用 T-SQL 语句查看、修改和删除存储过程查看存储过程a.查看加密存储过程的定义EXEC sp_helptext Proc_score注意观察执行结果。能够查看到该存储过程的定义文本吗？b.查看未加密存储过程的定义EXEC sp_helptext Proc_listEXEC sp_helptext
13、ProcAvgScorec.查看有关存储过程的信息。EXEC sp_help Proc_score11EXEC sp_help Proc_listEXEC sp_help ProcAvgScore（2）修改存储过程 Proc_scoreUSE STUINFO GOALTER PROC Proc_scoreWITH RECOMPILEASSELECT S_number,scoreFROM t_score WHERE C_number=1（3）删除上述创建的存储过程DROP PROC Proc_score,Proc_list,ProcAvgScore思考与练习：1、USE STUINFOGOCRE
14、ATE PROC insert_tri Cnum Char (4),Cname char (16),Teacher char (10) =NULL,Hours int,Credit int =NULL,Type Nvarchar(12)ASIF Cnum IS NULL OR Cname IS NULL OR Hours IS NULLBEGINPRINT 你必须提供课程号，课程名，课程学时！PRINT (授课教师，学分，课程类型可以为空。)RETURNENDBEGIN TRANSACTIONINSERT t_course (C_number,C_name, Teacher, Hours, C
15、redit,Type)VALUES (Cnum,Cname,Teacher,Hours,Credit,Type)IF error =60BEGINUPDATE t_scoreSET credit=creditFROM t_score,insertedWHERE t_score.S_number=inserted.S_numberENDEND实验九2实验内容（1）USE StuinfoGOCREATE FUNCTION Fn_Grad(ClassNo Char(6)RETURNS Fn_Grad TABLE(课程编号 Char(4) PRIMARY KEY NOT NULL,平均成绩 Decim
16、al(6,2) NOT NULL)ASBEGININSERT Fn_GradSELECT C_Number AS 课程编号,AVG(score) AS 平均成绩FROM T_ScoreWHERE (LEFT(S_Number,6)=ClassNo)GROUP BY C_NumberRETURNEND-测试SELECT * FROM dbo.Fn_Grad(0401)17-返回结果是 0401 班的成绩统计结果。（2）Declare err IntUSE SalesBEGIN TRANSACTIONUPDATE T1 SET T1.数量= T1.数量+ GG.数量FROM T1,GG WHERE T1.编号= GG.编号SELECT err=errorIF err!=0-更新失败，回滚所有操作BEGINROLLBACK TRANSACTIONRETURNENDINSERT T1 SELECT * FROM GG WHERE NOT (编号 IN (SELECT 编号 FROM T1)SELECT err=errorIF err!=0-更新失败，回滚所有操作BEGINROLLBACK TRANSACTIONRETURNENDCOMMIT TRANSACTION实验十
