sql考试所需.docx
- 文档编号:11213986
- 上传时间:2023-05-29
- 格式:DOCX
- 页数:16
- 大小:19.84KB
sql考试所需.docx
《sql考试所需.docx》由会员分享,可在线阅读,更多相关《sql考试所需.docx(16页珍藏版)》请在冰点文库上搜索。
sql考试所需
主要是以教学管理项目数据库为例
一.建库、建表(数据完整性控制)略,详细见平时测验1
二.INSERT记录
以销售数据库员工表插入记录为例
INSERTINTO[employeet]([employeeid],[employeename],[employeebirth],[password])
VALUES('001','李欢','1980-1-2','123456')
三.SELECT/UPDATE/DELETE
INSERTINTOStuT
([stuid],[stuname],[specid],[classid],[stubirth],[stusex])
VALUES('20070801103','束红叶','0801','200708011','1987-1-1','F')
go
select*fromstut
whereclassid='200708011'
go
updatestut
setstubirth='1988-1-1'
wherestuid='20070801101'
go
Selectstut.stusexas性别,count(*)as人数
Fromstut
innerjoinclasstonclasst.classid=stut.classid
whereclasst.classname='07网络1'
Groupbystut.stusex
go
selectspect.specname,sum(courset.coursegrade)
fromcourset
innerjoinspecteachingplanonspecteachingplan.courseid=courset.courseid
wherespecteachingplan.specidlike'08%'
groupbyspecteachingplan.specid
go
四.附加语言元素的应用详细见触发器
五.视图(详细见平时测验2十三,十四题)
十三
createviewv131
as
selecttb_tempselectcourse.courseasignidas课程开设编号,tb_course.coursenameas课程,tb_teacher.teachernameas任课教师,tb_student.stunameas学生姓名,tb_tempselectcourse.selectdateas选课时间
fromtb_tempselectcourse
innerjointb_studentontb_student.stuid=tb_tempselectcourse.stuid
innerjointb_courseasignon
tb_courseasign.courseasignid=tb_tempselectcourse.courseasignid
innerjointb_courseontb_course.courseid=tb_courseasign.courseid
innerjointb_teacheron
tb_teacher.teacherid=tb_courseasign.teacherid
go
createviewv132
as
selecttb_tempselectcourse.courseasignidas课程开设编号
tb_course.coursenameas课程,tb_teacher.teachernameas任课教师,tb_tempselectcourse.stuidas学号,tb_student.stunameas学生姓名,tb_class.classnameas班级,tb_tempselectcourse.selectdateas选课时间
fromtb_tempselectcourse
innerjointb_studentontb_student.stuid=tb_tempselectcourse.stuid
innerjointb_classontb_class.classid=tb_student.classid
innerjointb_courseasignon
tb_courseasign.courseasignid=tb_tempselectcourse.courseasignid
innerjointb_courseontb_course.courseid=tb_courseasign.courseid
innerjointb_teacheron
tb_teacher.teacherid=tb_courseasign.teacherid
go
十四
createviewv142
as
selecttb_teachingevaluation.courseasignidas课程开设编号
tb_teacher.teachernameas任课教师,tb_student.stunameas学生姓名,tb_teachingevaluation.teachingattitudeas教学态度,tb_teachingevaluation.teachingcontentas教学内容,tb_teachingevaluation.teachingmethodas教学方法,tb_teachingevaluation.spareinstructionas作业布置
(teachingattitude+teachingcontent+teachingmethod+spareinstruction)/4as
平均
fromtb_teachingevaluation
innerjointb_studentontb_student.stuid=tb_teachingevaluation.stuid
innerjointb_teacheron
tb_teacher.teacherid=tb_teachingevaluation.teacherid
go
六.存储过程
1.添加学生
IFEXISTS(SELECTname
FROMsysobjects
WHEREname='jwinsertstup'
ANDtype='P')
DROPPROCEDUREjwinsertstup
GO
CREATEPROCEDUREjwinsertstup(@stuiddchar(11),@stunameechar(8),@speciddchar(4),@classiddchar(9),@stubirthhsmalldatetime,@stusexxchar
(1),@succhar
(1)output)
AS
ifnotexists(selectstuidfromstuTwherestuid=@stuidd)
begin
INSERTINTO[bf].[dbo].[StuT]([stuid],[stuname],[specid],[classid],[stubirth],[stusex])
VALUES(@stuidd,@stunamee,@specidd,@classidd,@stubirthh,@stusexx)
set@suc='1'
end
else
--print'此学生已存在不要重复添加学生'--print/select
set@suc='0'
GO
--=============================================
--exampletoexecutethestoreprocedure
--=============================================
declare@schar
(1)
EXECUTEjwinsertstup'20040803101','aa','0803','200408031','1980-7-7','M',@soutput
select@s
GO
七.触发器
1.--请编写触发器,实现:
当同学在选修某门开设课程时,自动更新“开设课程表”中的已选学生数和选满标志。
--=============================================
--CreatebasicInsteadOfTrigger
--=============================================
IFEXISTS(SELECTname
FROMsysobjects
WHEREname=N'tcourseselect_insert'
ANDtype='TR')
DROPTRIGGERtcourseselect_insert
GO
CREATETRIGGERtcourseselect_insert
ONforeselectt
forinsert
AS
begin
declare@stunumtinyint,@selectedstunumtinyint,@stuidchar(11),@coursesetidint
set@stuid=(selectstuidfrominserted)
set@coursesetid=(selectcoursesetidfrominserted)
select@stunum=stunum,@selectedstunum=selectedstunumfromcoursesett
wherecoursesetid=@coursesetid
end
if(@selectedstunum<@stunum-1)
updatecoursesettsetselectedstunum=selectedstunum+1
wherecoursesetid=@coursesetid
if(@selectedstunum=@stunum-1)
updatecoursesettsetselectedstunum=selectedstunum+1,fullflag='1'
wherecoursesetid=@coursesetid
if(@selectedstunum=@stunum)
deletefromforeselectt
wherecoursesetid=@coursesetidandstuid=@stuid
go
select*fromcoursesettwherecoursesetid=73
go
insertintoforeselectt(stuid,stuname,coursesetid,selectdate)values('2004080312','aa',73,getdate())
go
select*fromcoursesettwherecoursesetid=73
go
2.--请编写触发器,实现:
当某位教师在其任教的课程中,在为本学期某学生、某门课程输入平时成绩、
--期中成绩、期末成绩后,自动计算出该学生本门课程的总评成绩。
--=============================================
--CreatebasicInsteadOfTrigger
--=============================================
IFEXISTS(SELECTname
FROMsysobjects
WHEREname=N'tgradet'
ANDtype='TR')
DROPTRIGGERtgradet
GO
CREATETRIGGERtgradet
ONgradetforINSERT
AS
BEGIN
declare@kcxftinyint,@getxftinyint,@getcjreal,@ccjreal,@mcjreal,@tcjreal,
@ecjreal,@tparttinyint,@cparttinyint,@mparttinyint,@theparttinyint
select@kcxf=coursesett.coursegradefromcoursesettwherecoursesett.coursesetid=(selectcoursesetidfromdeleted)
select@tpart=coursesett.tpart,@cpart=coursesett.cpart,@mpart=coursesett.mpart,@thepart=coursesett.theorypartfrom
coursesettwherecoursesett.coursesetid=(selectcoursesetidfromdeleted)
select@ccj=inserted.cscore,@mcj=inserted.mscore,@tcj=inserted.tscore,@ecj=inserted.escorefrominserted
set@getcj=(@ccj*@cpart/100.0+@mcj*@mpart/100.0+@tcj*@tpart/100.0)*@thepart/100.0+@ecj*(100-@thepart)/100.0
if@getcj>60
set@getxf=@kcxf
else
set@getxf=0
updategradetsetcoursegrade=@kcxf,getgrade=@getxf,coursescore=@getcjwhere
stuid=(selectstuidfrominserted)andcoursesetid=(selectcoursesetidfrominserted)
END
GO
3.--请编写触发器,实现:
当为某系新增某个专业时,自动检查专业编号是否符合其编码规则。
--=============================================
--CreatebasicInsteadOfTrigger
--=============================================
IFEXISTS(SELECTname
FROMsysobjects
WHEREname=N'tinsertspec'
ANDtype='TR')
DROPTRIGGERtinsertspec
GO
CREATETRIGGERtinsertspec
ONspectinsteadofINSERT
AS
BEGIN
declare@specidchar(4),@deptidchar
(2),@specnamechar(20)
set@specid=(selectspecidfrominserted)
set@deptid=(selectdeptidfrominserted)
set@specname=(selectspecnamefrominserted)
begintran
insertintospect(specid,deptid,specname)values(@specid,@deptid,@specname)
ifsubstring(@specid,1,2)=@deptidandconvert(int,substring(@specid,3,2))<99andconvert(int,substring(@specid,3,2))>1
committran
else
rollbacktran
END
GO
insertintospect(specid,deptid,specname)values('0810','08','xx')
go
八.游标(sqlserver2000游标用法小例)
declarecursor定义transact-sql服务器游标的属性,例如游标的滚动行为和用于生成游标所操作的结果集的查询。
open语句填充结果集,fetch从结果集返回行。
close语句释放与游标关联的当前结果集。
deallocate语句释放游标所使用的资源。
declarecursor语句的第一种格式使用sql-92语法声明游标行为。
declarecursor的第二种格式使用transact-sql扩展插件,这些扩展插件允许您使用在odbc或ado的数据库api游标函数中所使用的相同游标类型来定义游标。
不能混淆这两种格式。
如果在cursor关键字的前面指定scroll或insensitive关键字,则不能在cursor和forselect_statement关键字之间使用任何关键字。
如果在cursor和forselect_statement关键字之间指定任何关键字,则不能在cursor关键字的前面指定scroll或insensitive。
如果使用transact-sql语法的declarecursor不指定read_only、optimistic或scroll_locks,则默认值如下:
如果select语句不支持更新(由于权限不够、访问的远程表不支持更新等等),则游标为read_only。
static和fast_forward游标默认为read_only。
dynamic和keyset游标默认为optimistic。
游标名称只能被其他transact-sql语句引用。
它们不能被数据库api函数引用。
例如,声明游标之后,不能通过oledb、odbc或ado函数或方法引用游标名称。
不能使用提取函数或api的方法来提取游标行;只能通过transact-sqlfetch语句提取这些行。
在声明游标后,可使用下列系统存储过程确定游标的特性。
系统存储过程说明
sp_cursor_list
返回当前在连接上可视的游标列表及其特性。
sp_describe_cursor
说明游标属性,例如是只前推的游标还是滚动游标。
sp_describe_cursor_columns
说明游标结果集中的列的属性。
sp_describe_cursor_tables
说明游标所访问的基表。
在声明游标的select_statement中可以使用变量。
游标变量值在声明游标后不发生更改。
在sqlserver版本6.5以及早期版本中,每次重新打开游标时都会重新刷新变量值。
权限
默认情况下,将declarecursor权限授予对游标中所使用的视图、表和列具有select权限的任何用户。
示例
a.使用简单游标和语法
在打开该游标时所生成的结果集包括表中的所有行和所有列。
可以更新该游标,对该游标所做的所有更新和删除均在提取中表现出来。
因为未指定scroll选项,所以fetchnext是唯一可用的提取选项。
declarevend_cursorcursor
forselect*frompurchasing.vendor
openvend_cursor
fetchnextfromvend_cursor
b.使用嵌套游标生成报表输出
以下示例显示如何嵌套游标以生成复杂的报表。
为每个供应商声明内部游标。
setnocounton
declare@vendor_idint,@vendor_namenvarchar(50),
@messagevarchar(80),@productnvarchar(50)
print’--------vendorproductsreport--------’
declarevendor_cursorcursorfor
selectvendorid,name
frompurchasing.vendor
wherepreferredvendorstatus=1
orderbyvendorid
openvendor_cursor
fetchnextfromvendor_cursor
into@vendor_id,@vendor_name
while@@fetch_status=0
begin
print’’
select@message=’-----productsfromvendor:
’+
@vendor_name
print@message
--declareaninnercursorbased
--onvendor_idfromtheoutercursor.
declareproduct_cursorcursorfor
selectv.name
frompurchasing.productvendorpv,production.productv
wherepv.productid=v.productidand
pv.vendorid=@vendor_id--variablevaluefromtheoutercursor
openproduct_cursor
fetchnextfromproduct_cursorinto@product
if@@fetch_status<>0
print’<
while@@fetch_status=0
begin
select@message=’’+@product
print@message
fetchnextfromproduct_cursorinto@product
end
closeproduct_cursor
deallocateproduct_cursor
--getthenextvendor.
fetchnextfromvendor_cursor
into@vendor_id,@vendor_name
end
closevendor_cursor
deallocatevendor_cursor
九.安全控制
--创建
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- sql 考试