软件14142112张毅军实验二.docx
- 文档编号:10727287
- 上传时间:2023-02-22
- 格式:DOCX
- 页数:31
- 大小:1.34MB
软件14142112张毅军实验二.docx
《软件14142112张毅军实验二.docx》由会员分享,可在线阅读,更多相关《软件14142112张毅军实验二.docx(31页珍藏版)》请在冰豆网上搜索。
软件14142112张毅军实验二
集美大学数据库系统原理课程实验报告
课程名称:
数据库系统原理
班级:
软件1414
实验成绩:
指导教师:
吴晓晖
:
毅军
实验项目名称:
实验二:
SQLSERVER---运用T-SQL实现表查询、删除、添加及修改
学号:
9
上机实践日期:
实验目的:
熟悉运用SQL92标准实现表的查询、统计、添、删除、及修改。
实验容:
学习T-SQL语法完成单表查询、多表连接查询、嵌套查询、分组查询
学习T-SQL语法完成表数据的统计
学习T-SQL语法完成表记录的添、删及改
实验要求:
要求每个学生独立完成实验工作,编写实验报告
参考xscj库:
Major(mno,mname)
Stu(sno,sname,sex,birdate,mno,memo)
Cou(cno,cname,credit,ptime)
Sc(sno,cno,grade)
注:
可从xscjdb2008.rar解压后先,使用“附加”功能,按提示完成。
对stu表添加一条记录(用界面方式)该记录与自已的信息相符合(学号顺延即可。
但名字,性别,出生年月等登记自已信息),并在SC添加自已若干选课记录
Major表
Stu表
Cou表
Sc表
●设计实验掌握SELECT单表查询。
包括*,distinct,between,in,like,isnull等应用;扩展应用TOP与TOPn[percent];表达式,别名应用。
⏹例如从XSCJ.stu表查询学生的学号,年龄只显示年龄最大的两名同学:
SELECTtop2S.SNAME,YEAR(GETDATE())-YEAR(BIRDATE)ASAGEFROMSTUasSorderbyagedesc。
●设计实验掌握SELECT单表分组查询及聚集函数应用,包括groupby,having应用,聚集函数应用。
●设计实验掌握SELECT多表连接查询。
包括WHERE连接条件;用INNORJION,fullouterjoin,leftouterjoin,rightouterjoin(注:
可查询帮助中select:
from子句)
●设计实验掌握SELECT的嵌套查询及相关子查询
●Selectinto子句应用,where的some,all应用
实验步骤:
1.查询自已的所有信息。
select*fromSTUwhereSNAME='毅军'
注:
结果需要截图
1.查询全学院所有学生的信息
select*fromSTU
2.查询所有学生的学号与
selectSNO,SNAMEfromSTUorderbySNO
3.查询女生的学号与
selectSNO,SNAMEfromSTUwhereSEX='0'
4.查询女生且年龄19以上学生信息
USExscj
GO
select*fromSTUwhereSEX='0'andyear(GETDATE())-year(BIRDATE)>19
5.查询年龄18-20的学生信息
USExscj
GO
select*fromSTUwhereyear(GETDATE())-year(BIRDATE)>18
andyear(GETDATE())-year(BIRDATE)<20
6.查询所有姓的学生信息
无结果,查询结果为空
select*fromSTUwhereLEFT(SNAME,1)=''
7.查询计算机、软件专业的学生信息
select*fromstuwheremnoin(selectmnofrommajorwheremnamein('软件工程','计算机工程'))
8.查询全学院的学生成绩平均分
selectavg(GRADE)as全学院平均分fromSC
9.查询各专业的学生成绩平均分(注:
有的学生可能没有选修课程)
selectavg(GRADE)as各专业平均分fromSC,STUWHERESC.SNO=STU.SNOgroupbySTU.MNO
10.查询平均分多于75分的学生学号
selectSNOfromSCgroupbySNOhavingavg(GRADE)>75
11.查询‘C001’课程未登记成绩的学生学号
结果为空
selectSNOfromSCwhereGRADEisnullandCNO='C001'
12.查询选修‘C语言’课程的学生的学号
1)采用连接查询
USExscj
GO
selectSNOfromCOU,SC
whereCOU.CNO=SC.CNOandCOU.CNAME='C语言'
2)采用嵌套查询
USExscj
GO
selectSNOfromSC
whereCNOin(selectCNOfromCOUwhereCNAME='C语言')
3)采用EXIST查询
USExscj
GO
selectSNOfromSC
whereexists(select*fromCOUwhereSC.CNO=COU.CNOandCOU.CNAME='C语言')
13.查询未选修‘C语言’课程的学生的学号(notexist实现)
USExscj
GO
selectSTU.SNOfromSTU
wherenotexists(select*fromCOU,SC
whereSC.SNO=STU.SNO
andCOU.CNAME='C语言'
andCOU.CNO=SC.CNO)
15.查询与‘三’在同一个专业的学生信息
USExscj
GO
select*fromSTU
whereMNOin(selectMNOfromSTUwhereSNAME='三')
16.按各位同学自已的查询选修了哪几门课程,已获得的学分。
注:
至少两种命令,并且需截图。
(1)嵌套查询
USExscj
GO
selectCNO,CNAMEfromCOUwhereCNOin
(selectCNOfromSCwhereSNOin(selectSNOfromSTUwhereSNAME='毅军'))
selectsum(CREDIT)as当前学分fromCOUwhereCNOin
(selectCNOfromSCwhereSNOin(selectSNOfromSTUwhereSNAME='毅军'))
(2)连接查询
USExscj
GO
selectCOU.CNO,COU.CNAMEfromCOU,SC,STU
whereSTU.SNAME='毅军'andSTU.SNO=SC.SNOandSC.CNO=COU.CNO
selectsum(CREDIT)as当前学分fromCOU,SC,STU
whereSTU.SNAME='毅军'andSTU.SNO=SC.SNOandSC.CNO=COU.CNO
17.查询至少选修的’C001’与’C002’课程的学生学号
1)使用SC表的自连接完成
USExscj
GO
selectA.SNOfromSCasA,SCasB
whereA.CNO='C001'andB.CNO='C002'andA.SNO=B.SNO
2)使用INTERSECT(交)完成
USExscj
GO
selectSNOfromSCwhereCNO='C001'
intersect
selectSNOfromSCwhereCNO='C002'
18.查询S001学号选修而S003学号未选修的课程号
(提示:
使用EXCEPT)
USExscj
GO
selectCNOfromSCwhereSNO='S001'
except
selectCNOfromSCwhereSNO='S003'
19.查询S001学号、S003学号都选修了哪些课程
(试验:
UNION)
USExscj
GO
selectCNOfromSCwhereSNO='S001'
union
selectCNOfromSCwhereSNO='S003'
20.查询每个同学超过他选修的平均成绩的课程名。
1)用相关子查询实现
USExscj
GO
selectsno,cnamefromscx,cou
wheregrade>(selectavg(grade)fromscywherex.sno=y.sno)
o=o
2)使用派生表实现。
USExscj
GO
selectSNOAS学号,AVG(GRADE)AS平均分
intoMYGRADE
fromSCGROUPBYSNO
MYGRADE表:
USExscj
GO
selectSC.SNO,COU.CNAMEfromSC,MYGRADE,COU
whereSC.GRADE>MYGRADE.平均分andSC.SNO=MYGRADE.学号andSC.CNO=COU.CNO
21.查询平均分高于80分的学生
USExscj
GO
selectSTU.SNAMEfromSTUwhereSNOin
(selectSNOfromSCgroupbySNOhavingavg(GRADE)>80)
22.查询平均分高于60分的课程的课程名。
USExscj
GO
selectCOU.CNAMEfromCOUwhereCNOin
(selectCNOfromSCgroupbyCNOhavingavg(GRADE)>60)
23.查询‘C语言’课程成绩最高的前三名同学
USExscj
GO
selecttop(3)SC.SNO,STU.SNAMEfromSC,STUwhereSC.CNOin
(selectCNOfromCOUwhereCNAME='C语言')
andSC.SNO=STU.SNOorderbyGRADEDESC
24.查询平均成绩最高的前3名同学的学号,,性别及年龄。
USExscj
GO
selectSNO,SNAME,SEX,year(getdate())-year(BIRDATE)asage
fromSTU
whereSNOin
(selecttop(3)SNOfromSCgroupbySNOorderbyavg(GRADE)DESC)
25.检索C003课程成绩最高二人学号,与成绩。
并将结果保存于max_C003临时表中
注:
结果截图
USExscj
GO
selecttop
(2)SC.SNO,STU.SNAME,SC.GRADE
intomax_C003
fromSC,STU
whereSC.CNO='C003'andSC.SNO=STU.SNOorderbyGRADEDESC
max_C003表:
26.查询选修了所有课程的学生。
USExscj
GO
selectSNO,SNAMEfromSTU
wherenotexists(
select*fromCOUwherenotexists
(
select*fromSCwhereSNO=STU.SNOandCNO=COU.CNO
)
)
(二)添加
1.对学生表添加一条记录,记录(SO12,周强,女)等
USExscj
GO
insertintoSTU(SNO,SNAME,SEX,MNO,BIRDATE,MEMO)
values('S012','周强','0','02','1996-08-1300:
00:
00','NULL')
2.为上述学生添加二条选课记录。
USExscj
GO
insertintoSC(SNO,CNO,GRADE)
values('S012','C001','85.00')
insertintoSC(SNO,CNO,GRADE)
values('S012','C002','89.00')
3.为软件专业创建一个学生简表,用于点名。
USExscj
GO
select*
intoSOFTWARE
fromSTU
whereMNO='02'orderbySNO
4.检索所授每门课程平均成绩均大于70分的教师,并将检索的值送往另一个已在的表faculty(tname)
insertintofaculty(tname)selecttnamefromTEACHERwheretnoin(selecttnofromCOUwhereCNOin(selectCNOfromSCgroupbyCNOhavingavg(GRADE)>70))
5.创建表totalcredit(sno,totalcredit),为该表插入各同学当前获得总学分
USExscj
GO
selectSTU.SNOassno,sum(CREDIT)astotalcredit
intototalcredit
fromSC,COU,STU
WHERESC.SNO=STU.SNOandSC.CNO=COU.CNO
groupbySTU.SNO
(三)删除
(1)在SC中删除尚无成绩的选课元组
USExscj
GO
deletefromSCwhereSC.GRADEisNULL
(2)把选修’C语言’课程的女同学选课元组全部删除
USExscj
GO
deletefromSC
whereCNOin(selectCNOfromCOUwhereCNAME='C语言')
andSNOin(selectSNOfromSTUwhereSEX='0')
(3)删除周强的所有信息
USExscj
GO
createprocedurep_deleteSNAMEchar(8)
as
begin
begintransaction
deletefromSCwhereSNOin(selectSNOfromSTUwhereSNAME=SNAME)
deletefromSTUwhereSNAME=SNAME
committransaction
end
deletefromSCwhereSNOin(selectSNOfromSTUwhereSNAME='周强')
deletefromSTUwhereSNAME='周强'
(四)修改
1.将高数课不及格的成绩全改为60分
USExscj
GO
updateSCsetGRADE='60'
whereGRADE<60andCNOin(selectCNOfromCOUwhereCNAME='高等数学')
2.把低于所有课程总平均成绩的女同学成绩提高5%;
USExscj
GO
updateSCsetGRADE=GRADE*1.05
whereSNOin(selectSNOfromSTUwhereSEX='0')
andGRADE<(selectavg(GRADE)fromSC)
3.在SC中修改C004课程的成绩,若成绩小于70分则提高5%,若成绩大于70分则提高4%(要求用两种方法实现,一种方法是用两个UPDATE语句实现。
另一种方法是用CASE操作的一条UPDATE语句实现)
方法一:
USExscj
GO
updateSCsetGRADE=GRADE*1.04whereCNO='C004'andGRADE>70
updateSCsetGRADE=GRADE*1.05whereCNO='C004'andGRADE<70
方法二:
USExscj
GO
updateSCsetGRADE=
(
case
whenGRADE>70thenGRADE*1.04
whenGRADE<70thenGRADE*1.05
end
)
fromSCwhereCNO='C004'
4.为SC表添加一个字段RANK。
将各同学按60分以下为E,60-69为D,70-79为C,80-89为B,90及以上为A
USExscj
GO
altertableSC
addRANKchar
(1)
USExscj
GO
updateSCsetRANK=
(
case
whenGRADE<60then'E'
whenGRADE<70then'D'
whenGRADE<80then'C'
whenGRADE<90then'B'
else'A'
end
)
fromSC
实验小结:
1.什么是SQL入侵,在软件设计中如何避免。
SQL入侵也称作SQL注入攻击,是黑客对数据库进行攻击的常用手段之一。
随着B/S模式应用开发的发展,使用这种模式编写应用程序的程序员也越来越多。
但是由于程序员的水平及经验也参差不齐,相当大一部分程序员在编写代码的时候,没有对用户输入数据的合法性进行判断,使应用程序存在安全隐患。
用户可以提交一段数据库查询代码,根据程序返回的结果,获得某些他想得知的数据,这就是所谓的SQLInjection,即SQL注入。
SQL注入是从正常的WWW端口访问,而且表面看起来跟一般的Web页面访问没什么区别,所以目前市面的防火墙都不会对SQL注入发出警报,如果管理员没查看IIS日志的习惯,可能被入侵很长时间都不会发觉。
但是,SQL注入的手法相当灵活,在注入的时候会碰到很多意外的情况,需要构造巧妙的SQL语句,从而成功获取想要的数据。
避免和防SQL入侵:
1.普通用户与系统管理员用户的权限要有严格的区分。
2.加强对用户输入的验证。
3.编写防止执行性SQL脚本,对提交数据库容进行过滤操作。
4.多多使用SQLServer数据库自带的安全参数。
5.必要的情况下使用专业的漏洞扫描工具来寻找可能被攻击的点。
2.在软件设计中当记录数很多,需要进行分页显示,对于stu表每页显示两条记录的sql语句。
select*fromSTUorderbySNOoffset0rowfetchnext2rowsonly
3.数据库插入中需注意什么完整性?
对于major,stu表在插入记录时哪个表必须注意参数完整性。
举例说明
用户定义完整性、实体完整性、参照完整性
对于major,stu表在插入记录时stu表必须注意参数完整性,不能插入major表中没有的字段。
比如现在major表有一个专业号的字段,这个字段有两条记录,两个记录的专业号为01、02,在插入stu表时,如果stu.mno赋值为03就会出错,因为03不在major表里面,这里违背了参数完整性。
4.数据库记录删除时需注意什么完整性?
对于major,stu表在删除记录时哪个表必须注意参数完整性。
举例说明
参照完整性,也叫引用完整性。
就是外键约束。
意思就是说外键表引用的外键必须事先存在,删除主键表里的主键时,所有引用此键的外键表里的相关行必须先删掉。
所以stu表必须注意参数完整性,必须先删除stu表的记录再删除major表的记录,因为stu表的mno是引用major表的mno。
比如major表有两个专业号01、02,现在想要删除02号专业的所有信息,必须先删除stu表02号专业的学生记录,然后再删除major表的02号专业。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 软件 14142112 张毅军 实验