实验三数据查询实验.docx
《实验三数据查询实验.docx》由会员分享,可在线阅读,更多相关《实验三数据查询实验.docx(9页珍藏版)》请在冰点文库上搜索。
实验三数据查询实验
实验三:
数据查询实验
、实验目的
SQLServerQuery
从弹出的快捷菜单中
了解在企业管理器或查询分析器中执行数据查询的方法;掌握
Analyzer中简单查询、连接查询、嵌套查询操作方法。
二、实验内容
1、使用企业管理器进行查询(验证性,了解部分)
(1)查询数据表的全部数据
在企业管理器中,选择需要查询数据的表,用鼠标右键单击该表,选择“打开表”命令,这时将显示该表的全部数据。
2)使用SELECT语句查询数据表的数据
在企业管理器中,选择需要查询数据的表,用鼠标右键单击该表,选择“打开表”命令,在打开返回表内容的子窗口中,单击工具栏按钮“显示格”,把子窗口分为上下两部分,上面部分能输入不同的SQL命令。
执行时单击工具栏按钮
运行”即可。
(3)学习和使用QBE查询
在企业管理器中,选择需要查询数据的表,用鼠标右键单击该表,从弹出的快捷菜单中选择“打开表”命令,在打开返回表内容的子窗口中,单击工具栏按钮“显示/隐藏条件窗
格”,把子窗口分为上下两部分,上面部分能选择QBE查询条件,执行时单击工具栏按钮“运行”即可。
若单击工具栏按钮“显示/隐藏SQL窗格”,则在SQL窗格中会显示自动生成的对应SQL语句。
2、使用查询分析器进行查询(验证性)
(1)查询Student表中所有学生的学号、姓名和性别。
SELECTSno,Sname,Ssex
FROMStudent
2)可以用“*”来选取数据表的全部列例如:
查询Student表中所有学生的基本情况。
SELECT*
FROMStudent
3)在查询结果中增加计算列,还可修改数据列的显示名称。
例如:
查询Student表中所有学生的学号、姓名、性别和出生年份。
SELECTSno学号,Sname姓名,Ssex性别,Year(GetDate())-Sage出生年份FROMStudent
4)使用WHERE子句,可以选择满足条件的部分记录
例如:
查询成绩在85〜90分之间的学生情况。
SELECTSno,Cno,Grade
FROMSC
WHEREGrade>=85ANDGrade<=90
(5)使用DISTINCT关键字,可以消除重复记录
例如:
查询有成绩的学生的学号。
SELECTDISTINCTSno
FROMSC
(6)使用IN关键字,选择不连续条件的记录
例如:
查询学生成绩为80或85的学生的学号。
SELECT*
FROMSC
WHEREGradeIN(80,85)
(7)使用谓词LIKE和通配符“%”或“_”,实现模糊查询
例如:
查询姓“张”的学生的基本情况。
SELECT*
FROMStudent
WHERESnameLIKE'张%'
注意:
“%”代表0个或多个字符,“_”代表一个字符。
有的书上说,一个汉字占两个字符,但这里一个汉字只占一个字符位置,这与系统的设置有关。
(8)使用ORDERBY子句,对查询结果进行排序
例如:
查询所有学生的2号课的成绩,并按成绩由高向低排序。
SELECT*
FROMSC
WHERECno='2'
ORDERBYGradeDESC
(9)使用TOP关键字,选择查询结果的前几条记录
例如:
查询2号课成绩最高的学生记录。
SELECTTOP1*
FROMSC
WHERECno='2'
ORDERBYGradeDESC
注意:
如果2号课成绩存在并列最高,则使用下面的SQL语句。
SELECTTOP1WITHTIES*
FROMSC
WHERECno='2'
ORDERBYGradeDESC
(10)用WHERE子句指定连接条件
例如:
查询所有有2号课程成绩的学生的学号、姓名和成绩。
SELECTStudent.Sno,Sname,Grade
FROMStudent,SC
WHEREStudent.Sno=SC.SnoANDCno='2'(11)将查询结果存储到表中
例如:
将总分在200分以上的学生的情况(学号、姓名、性别、总成绩)存储到数据表GradeList中,假设数据表GradeList不存在。
SELECTA.Sno,Sname,Ssex,SUM(Grade)ASsumGrade
INTOGradeList
FROMStudentA,SC
WHEREA.Sno=SC.Sno
GROUPBYA.Sno,Sname,Ssex
HAVING(SUM(SC.Grade)>=200)该语句执行成功后,在企业管理器中可以看到已经创建的数据表GradeList,并将查询结果是存储在该表中了。
(12)将查询结果保存到变量中
例如:
查询学号为200215121的学生1号课的成绩,将其保存到变量Grade中。
DECLARE@GradeINT--定义变量
SELECT@Grade=Grade--给变量赋值
FROMSC
WHERESno='200215121'
PRINT@Grade--显示变量的值注意:
要将查询结果保存到变量中,只能将查询结果集中第一条记录的值赋给变量。
3、分析设计部分在学生选课库中实现下列数据查询操作,并写出相应的SQL脚本。
(1)求计算机系学生的学号和姓名
(2)求选修3号课程的学生学号和成绩,结果按成绩降序排列,如成绩同按学号升序排列
(3)求选修课程2成绩在80-90之间的学生学号和成绩,并将成绩乘以0.8输出
(4)求数学或计算机系姓'张'的学生的信息
(5)求缺少了成绩的学生的学号和课程号
(6)查询各门课程及相应的选课人数。
(7)查询总成绩在200分以上的学生的学号、总成绩和平均成绩
(8)在FROM子句中用INNERJOIN连接符指定连接条件查询所有有2号课程成绩的学生的学号、姓名和成绩。
(9)查询学生中年龄相同的学生情况(使用自连接查询)。
(10)查询所有学生的总成绩(包括没有成绩的学生)、学号和姓名(外部连接查询)。
(11)查询某课程成绩在90分以上的学生的学号和姓名(使用谓词IN连接子查询)。
(12)查询有课程成绩的学生的学号和姓名(使用谓词EXISTS连接子查询)。
(13)求没有选修2号课程的学生姓名;
(14)求选修了1号课程的学生中,成绩高于张三选修1号课程的学生学号和成绩;
(15)列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号
课的成绩。
三、实验学时
2学时
四、实验设备与环境
Windows2003平台+SQLServer2008系统
--
(1)求计算机系学生的学号和姓名
selectSno,Sname
fromstudent
whereSdept='CS';
--
(2)求选修3号课程的学生学号和成绩,结果按成绩降序排列,如成绩同按学号升序排列
selectSno,Grade
fromSC
whereCno='3'
orderbyGradedesc,SnoASC;
--(3)求选修课程2成绩在80—90之间的学生学号和成绩,并将成绩乘以0.8输出
selectSno,Grade*0.8GradefromSC
whereCno='2'andGradebetween80and90;
--(4)求数学或计算机系姓“张”的学生信息
select*
fromstudent
whereSnamelike'张%'andSdeptin('MA','CS');
--(5)求缺少了成绩的学生的学号和课程号
selectSno,Cno
fromSC
whereGradeisnull;
--(6)查询各门课程及相应的选课人数
selectCourse・Cno,Course・Cname,count(Sc・SnO选课人数
fromCourse
leftjoinSConSC・Cno=Course・Cno
groupbyCourse.Cno,Course.Cname;
--(7)查询总成绩在200分以上的学生的学号、总成绩和平均成绩
selectSno,SUM(Grade总成绩,AVG(Grade)平均成绩
fromSC
groupbySno
havingSUM(Grade)>200;
--(8)在form子句中用innerjoin连接符指定连接条件查询所有有2号课程成绩的学生的学号、姓名和成绩
selectstudent.Sno,student.Sname,Grade
fromstudentinnerjoinSConstudent.Sno=SC.Sno
whereSC.Cno='2'
--(9)查询学生中年龄相同的学生情况(使用自连接查询)
selectdistincta.Sno,a.Sname,a.Sagefromstudentasa,studentasbwherea.Sage=b.Sageanda.Sno<>b.Sno
--(10)查询所有学生的总成绩(包括没有成绩的学生)、学号和姓名(外部连接查询)
selectStudent・Sno,Student・Sname,SUM(Grad总成绩
fromStudentleftjoinSConStudent.Sno=SC.SnogroupbyStudent・Sno,Student・Sname--(11)查询某课程成绩在90分以上的学生的学号和姓名(使用谓词IN连接子查询)
selectstudent・Sno,student・Sname
fromStudent,SC
whereStudent・Sno=SC・SnoandGradeIN
(selectGrade
fromSC
whereGrade>=90
)
--(12)查询有课程成绩的学生的学号和姓名(使用谓词EXISTS连接子查询)
selectstudent.Sno,student.SnamefromStudent
whereexists
(select*
fromSC
whereSC.Sno=Student.SnoandGradeisnotnull
)
--(13)求没有选修2号课程的学生姓名
selectSname
fromstudent
wherenotexists
(select*
fromSC
whereSno=student.SnoandCno='2')
--(14)求选修了1号课程的学生中,成绩高于张三选修1号课程的学生学号和成绩
selectSno,GradefromSc
whereCno=1
andGrade>
(selectGrade
fromSc
whereCno=1
andSno=
(selectSno
fromstudent
whereSname=张三'
)
)
--(15).列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩
selectSC1.Sno,'1'=SC1.Grade,'2'=SC2.Grade
fromSCSC1,SCSC2
whereSC1.Cno='1'
andSC2.Cno='2'
andSC1.Sno=SC2.Sno
andSC1.Grade>SC2.Grade