SQL Server总复习Word文档格式.docx

1、+convert（char（10）,sum）Go/*例题3.32对课程进行分类统计,要求显示课程类别、课程名称和报名人数，计算各类课程的平均报名人数。查询结果要按照课程类别、报名人数升序排序。要使用CASE语句来完成对课程种类的判断，如果Kind值是工程技术类，则显示工科类课程；如果kind值是人文类，则显示人文类课程；如果kind值是信息技术类，则显示信息类课程；否则，则显示其他类课程。*/use xkselect kind,课程类别= case kind when工程技术then工科类课程人文人文类课程信息技术信息类课程 else其他类课程 end,课程名称=couname,报名人数=wi

2、llnumfrom courseorder by kind,willnumcompute avg（willnum） by kind-课本117页实训1,计算有多少个班级（假设为X），然后显示一条信息：共有X个班级。declare classno intselect classno=count（distinct classno） from class共有+convert（char（3）,classno）+个班级-课本117页实训2,编写计算n!（n=20）的SQL语句,并显示计算结果.-定义变量i int,product intDECLARE i int,product float-赋值SET

3、i=1SET product=1-当isum,i+1-iWHILE （i=20） BEGIN SET product=i*product SET i=i+1ENDPRINT 20!=+CONVERT（char（50）,product）第5章 数据库与事务日志-删除数据库drop database xk-创建数据库create database xk-显示当前xk数据库的信息sp_helpdb xk-显示服务器上已有数据库的信息sp_helpdb-查看数据库可以配置的选项sp_dboption-数据库选项的配置,将数据库xk配置为只读sp_dboptionxk,read onlytrue-修改数

4、据库的名字sp_renamedb原名新名/*例题5.3创建名字为Mydb的数据库，它有容量分别为MB、MB的两个数据文件mydb_data1.mdf和mydb_data2.ndf,其中mydb_data1.mdf为主数据文件，mydb_data2.mdf是次数据文件，日志文件容量为MB，都存储在D:目录下。数据文件和日志文件的最大容量均为MB，文件增长量为MB。create database mydbonprimary（name=mydb_data1,filename=d:mydb_data1.mdf,size=10,maxsize=20,filegrowth=2）,（name=mydb_da

5、ta2,mydb_data1.ndfsize=8,filegrowth=2）log on（name=mydb_log1,mydb_log1.ldfsize=7,-例题5.4将选课数据库Xk的数据文件Xk.mdf的容量扩充到5MB，事务日志文件Xk_log.ldf的容量扩充为6MB。alter database xkmodify file（name=xk,size=5MB）modify file（name=xk_log,size=6MB）-例题5.5为xk数据库增加一个MB的次数据文件XK2，次数据文件后缀为.ndf。use masteradd file（name=Xk2,c:xk2.ndfsi

6、ze=4MB,maxsize=10MB,filegrowth=1MB）-缩小数据库和数据文件-例题5.12将xk数据库的MB的数据文件Xk2收缩为MB。dbcc shrinkfile（xk2,2）第7章 数据完整性-使用SQL语句给xk数据库的Student表添加主键。alter table Classadd constraint pk_Class primary key（Classno）-给Student表的ClassNo列创建外键，它参照Class表的ClassNo列。alter table studentadd constraint Fk_student_Class Foreign ke

7、y（ClassNo）references class（ClassNo）-给Student表的PWD列创建唯一约束。add constraint un_student_pwd unique（PWD）-使用Check约束-Course表中的Credit值只允许为下列值中的任意一个：、.5、.5、.5、.5、，不允许输入其他值。alter table Courseadd constraint Ck_Credit check（Credit in（1,1.5,2,2.5,3,3.5,4,4.5,5）-使用Default约束.-StuCou表State（报名状态）在不输入时,SQL Server自动赋予为

8、报名。alter table StuCouadd constraint DF_StuCou_State Default（报名）for State-删除约束alter table stucoudrop constraint 约束名第8章 索引-显示索引信息SP_Helpindex Course-删除基于Student表的PWD列上的索引drop index Student.IX_Student_PWD-重新命名索引sp_renamestudent.ix_student_stunameix_student_stuname_new-例题8.2使用Transact-SQL语句创建索引。用户需要按照课程

9、名称查询信息，希望提高查询速度。create uniqueindex ix_Couname on Course（CouName）-练习：按照学生姓名查询学生信息，希望提高查询速度，请解决。create index IX_Student_StuNameon Student（StuName）-Student表的PWD列值唯一时，查询PWD时希望提高查询速度。create unique index IX_Student_PWDon student（pwd）-例题8.8在Xk数据库中的Student表上查询姓林学生的信息,并分析哪些索引被系统采用.set showplan_all on; select

10、 stuno,stuname from student where stuname like 林%set showplan_all off;第9章 管理数据库其他对象-例题9.1创建用户自定义数据类型mydatatype,nvarchar（30）,not null.sp_addtype mydatatype,nvarchar（30）create table tab1（name mydatatype,address mydatatype）-删除表tab1drop table tab1-删除用户自定义的数据类型sp_droptype mydatatype-问题:Course表中如何查看课程剩余的选

11、课名额？限选人数limitnum-选中人数Choosenumselect couno,couname,剩余选课名额=limitnum-choosenum/*例题9.5使用Transace-SQL语句,在XK数据库中创建名为CalcRemainNum的用户定义的函数,它计算课程剩余的选课名额,并将用户定义函数绑定到Course表上.创建名字为CalcRemainNum的用户自定义函数.*/create function CalcRemainNum（x decimal（6,0）,Y decimal（6,0）returns decimal（6,0）as begin return（X-Y） end-在

12、Course表中新增名字为RemainNum的列，并与CalcRemainNum函数进行绑定。add remainnum as dbo.CalcRemainNum（limitnum,choosenum）3进行测试select * from Course第10章 视图-1、创建一个简单的视图,查看学生表的信息.select *from student-2、保存select作为一个视图，名字为V_Studentcreate view V_Student select * from student-3、使用视图V_Studentfrom V_Student-让电子商务班的班主任TeacherLi只可

13、以查看她自己班级同学的选课信息。-1、查询电子商务班同学的选课信息。select Student.StuNo,Stuname,Course.CouNo,Couname,Credit,SchoolTimefrom student,Course,Stucou,Classwhere Student.StuNo=Stucou.StuNO and Course.CouNo=StuCou.couno and class.classno=student.classno and classname=00电子商务-2、保存为视图（创建视图）。create view V_TeacherLiasselect stu

14、dent.stuno,stuname,course.couno,couname,credit,schooltimefrom student,course,stucou,class-3、测试使用该视图。from V_TeacherLi-修改视图（加密视图的定义）alter view V_TeacherLiwith encryption-显示视图的信息（定义）sp_helptextV_TeacherLi-取消加密-给视图V_TeacherLi加标题alter view V_TeacherLi（学号,姓名,课程编号,课程名称,学分,上课时间）-测试查询-显示视图的信息（来自哪些表）sp_depend

15、s-删除视图drop viwe 视图名第11章 存储过程-查看电子商务班级同学的选课信息，存储过程来完成。create procedure P_TeacherLi-执行存储过程P_TeacherLi-根据用户输入的班级名字查看该班同学的选课信息，存储过程来完成。alter procedure P_TeacherLi InputclassName nvarchar（20） select student.stuno,stuname,course.couno,couname,credit,schooltime from student,course,stucou,class where Studen

16、t.StuNo=Stucou.StuNO and classname=inputclassname-按名字传送exec P_TeacherLi inputclassname=01建筑电气-按位置传送exec P_TeacherLi00多媒体01多媒体-加密 inputclassname nvarchar（20）-查看存储过程信息P_TeacherLisP_depends-删除存储过程drop procedure 存储过程名-重命名old_namenew_name-例题11.17执行下列Xp_cmdshell语句返回指定目录下的文件列表。exec xp_cmdshelldir c:*.exe第1

17、2章 触发器-例题12.1创建一个触发器，要求每当在Student表中修改数据时，向客户端显示一条记录已修改的消息。create trigger tri_update_studenton studentfor update print已修改了student表的数据行!-在查询窗口执行如下SQL语句，测试触发器是否被触发：update student set pwd=11111111where stuno=00000001-例题12.3将例.1中Test1触发器中的For Update修改为insetad of update并进行比较。alter trigger tri_update_stude

18、ntinstead of update记录没有修改!-测试触发器，在查询窗口中执行如下SQL语句22222222000000001-例题12.4创建一个触发器，实现当插入、更新或删除Stucou表的选课数据行时，能同时更新Course表中相应的报名人数。-1、在查询窗口中执行如下SQL语句，创建后触发器：create trigger setwillnum on stucoufor insert,update,deleteupdate course set willnum=willnum+1 where couno=（select couno from inserted）update cours

19、e set willnum=willnum-1 where couno=（select couno from deleted）-2、查看CouNo等于和的报名人数willnum:select * from course where couno=002003-3、修改数据行，将一个原来报名CouNo为的数据行改为.update stucou set couno= where stuno=00000011and couno=-查看数据行修改后CouNo等于和的willnum值：-删除触发器drop trigger 触发器名-修改触发器的名称sp_rename oldname,newname-使用Transace-SQL语句将触发器SetWillnum更名为UpdateWillnum。sp_rename setwillnum,updatewillnum-禁用触发器（禁用Updatewillnum）alter table stucou disable trigger updatewillnum-恢复使用触发器（恢复Updatewillnum）alter table stucou enable trigger Updatewillnum-使用Transact-SQL语句查询XK数据库中有哪些触发器。select * from sysobjects where type=tr第13章