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

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

《SQL Server总复习Word文档格式.docx》由会员分享，可在线阅读，更多相关《SQL Server总复习Word文档格式.docx（28页珍藏版）》请在冰豆网上搜索。

+convert（char（10）,@sum）

Go

/*[例题3.32]对课程进行分类统计,要求显示课程类别、课程名称和报名人数，计算各类课程的平均报名人数。

查询结果要按照课程类别、报名人数升序排序。

要使用CASE语句来完成对课程种类的判断，如果Kind值是‘工程技术’类，则显示‘工科类课程’；

如果kind值是‘人文’类，则显示‘人文类课程’；

如果kind值是‘信息技术’类，则显示‘信息类课程’；

否则，则显示‘其他类课程’。

*/

usexk

selectkind,'

课程类别'

=

casekind

when'

工程技术'

then'

工科类课程'

人文'

人文类课程'

信息技术'

信息类课程'

else'

其他类课程'

end,'

课程名称'

=couname,'

报名人数'

=willnum

fromcourse

orderbykind,willnum

computeavg（willnum）bykind

--课本117页实训1,计算有多少个班级（假设为X），然后显示一条信息：

共有X个班级。

declare@classnoint

select@classno=count（distinctclassno）fromclass

共有'

+convert（char（3）,@classno）+'

个班级'

--课本117页实训2,编写计算n!

（n=20）的SQL语句,并显示计算结果.

--定义变量@iint,@productint

DECLARE@iint,@productfloat

--赋值

SET@i=1

SET@product=1

--当@i<

=10000时,执行循环语句:

@i+@sum->

@sum,@i+1->

@i

WHILE（@i<

=20）

BEGIN

SET@product=@i*@product

SET@i=@i+1

END

PRINT'

20!

='

+CONVERT（char（50）,@product）

《第5章数据库与事务日志》

--删除数据库

dropdatabasexk

--创建数据库

createdatabasexk

--显示当前xk数据库的信息

sp_helpdbxk

--显示服务器上已有数据库的信息

sp_helpdb

--查看数据库可以配置的选项

sp_dboption

--数据库选项的配置,将数据库xk配置为‘只读’

sp_dboption'

xk'

'

readonly'

true'

--修改数据库的名字

sp_renamedb'

原名'

新名'

/*[例题5.3]创建名字为Mydb的数据库，它有容量分别为MB、MB的两个数据文件mydb_data1.mdf和mydb_data2.ndf,

其中mydb_data1.mdf为主数据文件，mydb_data2.mdf是次数据文件，日志文件容量为MB，都存储在D:

\目录下。

数据

文件和日志文件的最大容量均为MB，文件增长量为MB。

createdatabasemydb

on

primary（name=mydb_data1,

filename='

d:

\mydb_data1.mdf'

size=10,

maxsize=20,

filegrowth=2）,

（name=mydb_data2,

\mydb_data1.ndf'

size=8,

filegrowth=2）

logon

（name=mydb_log1,

\mydb_log1.ldf'

size=7,

--[例题5.4]将选课数据库Xk的数据文件Xk.mdf的容量扩充到5MB，事务日志文件Xk_log.ldf的容量扩充为6MB。

alterdatabasexk

modifyfile（name=xk,size=5MB）

modifyfile（name=xk_log,size=6MB）

--[例题5.5]为xk数据库增加一个MB的次数据文件XK2，次数据文件后缀为.ndf。

usemaster

addfile

（name=Xk2,

c:

\xk2.ndf'

size=4MB,

maxsize=10MB,

filegrowth=1MB）

--缩小数据库和数据文件

--[例题5.12]将xk数据库的MB的数据文件Xk2收缩为MB。

dbccshrinkfile（xk2,2）

《第7章数据完整性》

--使用SQL语句给xk数据库的Student表添加主键。

altertableClass

addconstraintpk_Classprimarykey（Classno）

--给Student表的ClassNo列创建外键，它参照Class表的ClassNo列。

altertablestudent

addconstraintFk_student_ClassForeignkey（ClassNo）

referencesclass（ClassNo）

--给Student表的PWD列创建唯一约束。

addconstraintun_student_pwdunique（PWD）

--使用Check约束

--Course表中的Credit值只允许为下列值中的任意一个：

、.5、、.5、、.5、、.5、，不允许输入其他值。

altertableCourse

addconstraintCk_Creditcheck（Creditin（1,1.5,2,2.5,3,3.5,4,4.5,5））

--使用Default约束.

--StuCou表State（报名状态）在不输入时,SQLServer自动赋予为‘报名’。

altertableStuCou

addconstraintDF_StuCou_StateDefault（'

报名'

）forState

--删除约束

altertablestucou

dropconstraint约束名

《第8章索引》

--显示索引信息

SP_HelpindexCourse

--删除基于Student表的PWD列上的索引

dropindexStudent.IX_Student_PWD

--重新命名索引

sp_rename'

student.ix_student_stuname'

ix_student_stuname_new'

--[例题8.2]使用Transact-SQL语句创建索引。

用户需要按照课程名称查询信息，希望提高查询速度。

createunique

indexix_CounameonCourse（CouName）

--练习：

按照学生姓名查询学生信息，希望提高查询速度，请解决。

createindexIX_Student_StuName

onStudent（StuName）

--Student表的PWD列值唯一时，查询PWD时希望提高查询速度。

createuniqueindexIX_Student_PWD

onstudent（pwd）

--[例题8.8]在Xk数据库中的Student表上查询姓'

林'

学生的信息,并分析哪些索引被系统采用.

setshowplan_allon;

selectstuno,stunamefromstudentwherestunamelike'

林%'

setshowplan_alloff;

《第9章管理数据库其他对象》

--[例题9.1]创建用户自定义数据类型mydatatype,nvarchar（30）,'

notnull'

.

sp_addtypemydatatype,'

nvarchar（30）'

createtabletab1

（

namemydatatype,

addressmydatatype

）

--删除表tab1

droptabletab1

--删除用户自定义的数据类型

sp_droptypemydatatype

--问题:

Course表中如何查看课程剩余的选课名额？

限选人数limitnum-选中人数Choosenum

selectcouno,couname,'

剩余选课名额'

=limitnum-choosenum

/*[例题9.5]使用Transace-SQL语句,在XK数据库中创建名为CalcRemainNum的用户定义的函数,

它计算课程剩余的选课名额,并将用户定义函数绑定到Course表上.

<

1>

创建名字为CalcRemainNum的用户自定义函数.*/

createfunctionCalcRemainNum

（@xdecimal（6,0）,@Ydecimal（6,0））

returnsdecimal（6,0）

as

begin

return（@X-@Y）

end

--<

2>

在Course表中新增名字为RemainNum的列，并与CalcRemainNum函数进行绑定。

addremainnumasdbo.CalcRemainNum（limitnum,choosenum）

3>

进行测试

select*fromCourse

《第10章视图》

--1、创建一个简单的视图,查看学生表的信息.

select*

fromstudent

--2、保存select作为一个视图，名字为V_Student

createviewV_Student

select*

fromstudent

--3、使用视图V_Student

fromV_Student

--让电子商务班的班主任TeacherLi只可以查看她自己班级同学的选课信息。

--1、查询‘电子商务’班同学的选课信息。

selectStudent.StuNo,Stuname,Course.CouNo,Couname,Credit,SchoolTime

fromstudent,Course,Stucou,Class

whereStudent.StuNo=Stucou.StuNOand

Course.CouNo=StuCou.counoand

class.classno=student.classnoand

classname='

00电子商务'

--2、保存为视图（创建视图）。

createviewV_TeacherLi

as

selectstudent.stuno,stuname,course.couno,couname,credit,schooltime

fromstudent,course,stucou,class

--3、测试使用该视图。

fromV_TeacherLi

--修改视图（加密视图的定义）

alterviewV_TeacherLi

withencryption

--显示视图的信息（定义）

sp_helptext'

V_TeacherLi'

--取消加密

--给视图V_TeacherLi加标题

alterviewV_TeacherLi（学号,姓名,课程编号,课程名称,学分,上课时间）

--测试查询

--显示视图的信息（来自哪些表）

sp_depends'

--删除视图

dropviwe'

视图名'

《第11章存储过程》

--查看‘电子商务’班级同学的选课信息，存储过程来完成。

createprocedureP_TeacherLi

--执行存储过程

P_TeacherLi

--根据用户输入的班级名字查看该班同学的选课信息，存储过程来完成。

alterprocedureP_TeacherLi

@InputclassNamenvarchar（20）

selectstudent.stuno,stuname,course.couno,couname,credit,schooltime

fromstudent,course,stucou,class

whereStudent.StuNo=Stucou.StuNOand

classname=@inputclassname

--按名字传送

execP_TeacherLi@inputclassname='

01建筑电气'

--按位置传送

execP_TeacherLi'

00多媒体'

01多媒体'

--加密

@inputclassnamenvarchar（20）

--查看存储过程信息

P_TeacherLi'

sP_depends'

--删除存储过程

dropprocedure'

存储过程名'

--重命名

old_name'

new_name'

--[例题11.17]执行下列Xp_cmdshell语句返回指定目录下的文件列表。

execxp_cmdshell'

dirc:

\*.exe'

《第12章触发器》

--[例题12.1]创建一个触发器，要求每当在Student表中修改数据时，向客户端显示一条’记录已修改‘的消息。

createtriggertri_update_student

onstudent

forupdate

print'

已修改了student表的数据行!

'

--在查询窗口执行如下SQL语句，测试触发器是否被触发：

updatestudentsetpwd='

11111111'

wherestuno='

00000001'

--[例题12.3]将例.1中Test1触发器中的ForUpdate修改为insetadofupdate并进行比较。

altertriggertri_update_student

insteadofupdate

记录没有修改!

--测试触发器，在查询窗口中执行如下SQL语句

22222222'

000000001'

--[例题12.4]创建一个触发器，实现当插入、更新或删除Stucou表的选课数据行时，能同时更新Course表中相应的报名人数。

--1、在查询窗口中执行如下SQL语句，创建后触发器：

createtriggersetwillnumonstucou

forinsert,update,delete

updatecoursesetwillnum=willnum+1wherecouno=（selectcounofrominserted）

updatecoursesetwillnum=willnum-1wherecouno=（selectcounofromdeleted）

--2、查看CouNo等于’‘和’‘的报名人数willnum:

select*fromcoursewherecouno='

002'

003'

--3、修改数据行，将一个原来报名CouNo为’‘的数据行改为’‘.

updatestucousetcouno='

wherestuno='

00000011'

andcouno='

--查看数据行修改后CouNo等于’‘和’‘的willnum值：

--删除触发器

droptrigger'

触发器名'

--修改触发器的名称

sp_renameoldname,newname

--使用Transace-SQL语句将触发器SetWillnum更名为UpdateWillnum。

sp_renamesetwillnum,updatewillnum

--禁用触发器（禁用Updatewillnum）

altertablestucoudisabletriggerupdatewillnum

--恢复使用触发器（恢复Updatewillnum）

altertablestucouenabletriggerUpdatewillnum

--使用Transact-SQL语句查询XK数据库中有哪些触发器。

select*fromsysobjectswheretype='

tr'

《第13章