软件1414-201421122099-张毅军-实验二.doc

集美大学数据库系统原理课程实验报告课程名称：数据库系统原理班级：软件1414实验成绩：指导教师：吴晓晖姓名：张毅军实验项目名称：实验二：SQL SERVER-运用T-SQL实现表查询、删除、添加及修改学号：201421122099上机实践日期：实验目的：熟悉运用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表l 设计实验掌握SELECT单表查询。包括*，distinct ,between,in,like,is null等应用；扩展应用TOP与TOP n percent；表达式，别名应用。n 例如从XSCJ.stu表查询学生的学号，年龄只显示年龄最大的两名同学: SELECT top 2 S.SNAME,YEAR(GETDATE()-YEAR(BIRDATE) AS AGE FROM STU as S order by age desc 。l 设计实验掌握SELECT单表分组查询及聚集函数应用，包括group by, having应用,聚集函数应用。l 设计实验掌握SELECT多表连接查询。包括WHERE 连接条件；用INNOR JION，full outer join,left outer join, right outer join（注：可查询帮助中select:from子句）l 设计实验掌握SELECT的嵌套查询及相关子查询l Select into子句应用，where的some,all应用实验步骤：0. 查询自已的所有信息。select * from STU where SNAME=张毅军注：结果需要截图1 查询全学院所有学生的信息select * from STU2 查询所有学生的学号与姓名select SNO,SNAME from STU order by SNO3 查询女生的学号与姓名select SNO,SNAME from STU where SEX=04 查询女生且年龄19以上学生信息USE xscjGOselect * from STU where SEX=0 and year(GETDATE()-year(BIRDATE) 195 查询年龄18-20的学生信息USE xscjGOselect * from STU where year(GETDATE()-year(BIRDATE) 18and year(GETDATE()-year(BIRDATE) 7511.查询C001课程未登记成绩的学生学号结果为空select SNO from SC where GRADE is null and CNO= C00112查询选修C语言课程的学生的学号1）采用连接查询USE xscjGOselect SNO from COU,SC where COU.CNO=SC.CNO and COU.CNAME=C语言2）采用嵌套查询USE xscjGOselect SNO from SC where CNO in(select CNO from COU where CNAME=C语言)3）采用EXIST查询USE xscjGOselect SNO from SC where exists(select * from COU where SC.CNO=COU.CNO and COU.CNAME=C语言)13查询未选修C语言课程的学生的学号（not exist实现）USE xscjGOselect STU.SNO from STUwhere not exists(select * from COU,SC where SC.SNO=STU.SNO and COU.CNAME=C语言 and COU.CNO=SC.CNO)15查询与张三在同一个专业的学生信息USE xscjGOselect * from STU where MNO in(select MNO from STU where SNAME=张三)16.按各位同学自已的姓名查询选修了哪几门课程，已获得的学分。注：至少两种命令，并且需截图。（1）嵌套查询USE xscjGOselect CNO,CNAME from COU where CNO in(select CNO from SC where SNO in(select SNO from STU where SNAME=张毅军)select sum(CREDIT) as 当前学分 from COU where CNO in(select CNO from SC where SNO in(select SNO from STU where SNAME=张毅军)（2）连接查询USE xscjGOselect COU.CNO,COU.CNAME from COU,SC,STU where STU.SNAME=张毅军 and STU.SNO=SC.SNO and SC.CNO=COU.CNOselect sum(CREDIT) as 当前学分 from COU,SC,STU where STU.SNAME=张毅军 and STU.SNO=SC.SNO and SC.CNO=COU.CNO17查询至少选修的C001与C002课程的学生学号1）使用SC表的自连接完成USE xscjGOselect A.SNO from SC as A,SC as Bwhere A.CNO=C001 and B.CNO=C002 and A.SNO=B.SNO2）使用INTERSECT（交）完成USE xscjGOselect SNO from SC where CNO=C001intersectselect SNO from SC where CNO=C00218查询S001学号选修而S003学号未选修的课程号（提示：使用EXCEPT）USE xscjGOselect CNO from SC where SNO=S001exceptselect CNO from SC where SNO=S00319. 查询S001学号、S003学号都选修了哪些课程（试验：UNION）USE xscjGOselect CNO from SC where SNO=S001unionselect CNO from SC where SNO=S00320查询每个同学超过他选修的平均成绩的课程名。1）用相关子查询实现USE xscjGOselect sno,cname from sc x,cou where grade (select avg(grade) from sc y where x.sno = y.sno) and o = o2）使用派生表实现。USE xscjGOselect SNO AS 学号,AVG(GRADE) AS 平均分into MYGRADEfrom SC GROUP BY SNOMYGRADE表：USE xscjGOselect SC.SNO,COU.CNAME from SC,MYGRADE,COUwhere SC.GRADEMYGRADE.平均分 and SC.SNO=MYGRADE.学号 and SC.CNO=COU.CNO21查询平均分高于80分的学生姓名USE xscjGOselect STU.SNAME from STU where SNO in(select SNO from SC group by SNO having avg(GRADE)80)22查询平均分高于60分的课程的课程名。USE xscjGOselect COU.CNAME from COU where CNO in(select CNO from SC group by CNO having avg(GRADE)60)23.查询C语言课程成绩最高的前三名同学USE xscjGOselect top(3) SC.SNO,STU.SNAME from SC,STU where SC.CNO in(select CNO from COU where CNAME=C语言) and SC.SNO=STU.SNO order by GRADE DESC24.查询平均成绩最高的前3名同学的学号，姓名，性别及年龄。USE xscjGOselect SNO,SNAME,SEX,year(getdate()-year(BIRDATE) as age from STU where SNO in(select top(3) SNO from SC group by SNO order by avg(GRADE) DESC)25检索C003课程成绩最高二人学号，姓名与成绩。并将结果保存于max_C003临时表中注：结果截图USE xscjGOselect top(2) SC.SNO,STU.SNAME,SC.GRADE into max_C003from SC,STU where SC.CNO=C003 and SC.SNO=STU.SNO order by GRADE DESCmax_C003表：26查询选修了所有课程的学生。USE xscjGOselect SNO,SNAME from STU where not exists (select * from COU where not exists(select * from SC where SNO=STU.SNO and CNO=COU.CNO)（二）添加1.对学生表添加一条记录，记录(SO12,周强，女)等USE xscjGOinsert into STU(SNO,SNAME,SEX,MNO,BIRDATE,MEMO) values(S012,周强,0,02,1996-08-13 00:00:00,NULL)2.为上述学生添加二条选课记录。USE xscjGOinsert into SC(SNO,CNO,GRADE) values(S012,C001,85.00)insert into SC(SNO,CNO,GRADE) values(S012,C002,89.00)3为软件专业创建一个学生简表，用于点名。USE xscjGOselect *into SOFTWAREfrom STU where MNO=02 order by SNO4检索所授每门课程平均成绩均大于70分的教师姓名，并将检索的值送往另一个已在的表faculty(tname)insert into faculty(tname) select tname from TEACHER where tno in (select tno from COU where CNO in ( select CNO from SC group by CNO having avg(GRADE) 70 )5.创建表totalcredit(sno,totalcredit),为该表插入各同学当前获得总学分USE xscjGOselect STU.SNO as sno,sum(CREDIT) as totalcreditinto totalcreditfrom SC,COU,STU WHERE SC.SNO=STU.SNO and SC.CNO=COU.CNO group by STU.SNO （三）删除(1) 在SC中删除尚无成绩的选课元组USE xscjGOdelete from SC where SC.GRADE is NULL(2) 把选修C语言课程的女同学选课元组全部删除USE xscjGOdelete from SC where CNO in(select CNO from COU where CNAME=C语言) and SNO in(select SNO from STU where SEX=0)(3) 删除周强的所有信息USE xscjGOcreate procedure p_delete SNAME char(8)asbeginbegin transactiondelete from SC where SNO in(select SNO from STU where SNAME=SNAME)delete from STU where SNAME=SNAMEcommit transactionenddelete from SC where SNO in(select SNO from STU where SNAME=周强)delete from STU where SNAME=周强（四）修改1.将高数课不及格的成绩全改为60分USE xscjGOupdate SC set GRADE=60 where GRADE 60 and CNO in(select CNO from COU where CNAME=高等数学)2.把低于所有课程总平均成绩的女同学成绩提高5%;USE xscjGOupdate SC set GRADE=GRADE*1.05where SNO in(select SNO from STU where SEX=0) and GRADE 70update SC set GRADE=GRADE*1.05 where CNO=C004 and GRADE 70 then GRADE*1.04when GRADE 70 then GRADE*1.05end)from SC where CNO=C0044.为SC表添加一个字段RANK。将各同学按60分以下为E,60-69为D,70-79为C,80-89为B,90及以上为AUSE xscjGOalter table SCadd RANK char(1)USE xscjGOupdate SC set RANK=(case when GRADE 60 then Ewhen GRADE 70 then Dwhen GRADE 80 then Cwhen GRADE 90 then Belse Aend)from SC实验小结：1 什么是SQL入侵，在软件设计中如何避免。SQL入侵也称作SQL注入攻击，是黑客对数据库进行攻击的常用手段之一。随着B/S模式应用开发的发展，使用这种模式编写应用程序的程序员也越来越多。但是由于程序员的水平及经验也参差不齐，相当大一部分程序员在编写代码的时候，没有对用户输入数据的合法性进行判断，使应用程序存在安全隐患。用户可以提交一段数据库查询代码，根据程序返回的结果，获得某些他想得知的数据，这就是所谓的SQL Injection，即SQL注入。SQL注入是从正常的WWW端口访问，而且表面看起来跟一般的Web页面访问没什么区别，所以目前市面的防火墙都不会对SQL注入发出警报，如果管理员没查看IIS日志的习惯，可能被入侵很长时间都不会发觉。但是，SQL注入的手法相当灵活，在注入的时候会碰到很多意外的情况，需要构造巧妙的SQL语句，从而成功获取想要的数据。避免和防范SQL入侵：1