数据库原理实验2数据查询.docx
- 文档编号:8820161
- 上传时间:2023-02-01
- 格式:DOCX
- 页数:20
- 大小:464.65KB
数据库原理实验2数据查询.docx
《数据库原理实验2数据查询.docx》由会员分享,可在线阅读,更多相关《数据库原理实验2数据查询.docx(20页珍藏版)》请在冰豆网上搜索。
数据库原理实验2数据查询
信息工程学院
实验报告
课程名称数据库原理
实验序号2
实验项目数据查询
实验地点
实验学时实验类型验证性
指导教师实验员
专业班级
学号姓名
年月日
成绩:
A
教师评语
该学生本次实验的内容丰富,完成的操作步骤详细具体,实验结果正确,在实验报告的填写中态度十分严谨,对数据分析有自己的见解。
一、实验目的及要求
1、掌握select语句进行查询语句的一般格式;
2、熟练掌握简单查询、连接查询、嵌套查询的操作方法。
二、实验原理与内容
数据查询是数据库的核心操作。
SQL语言提供了SELECT语句进行数据库的查询,该语句具有灵活的使用方式和丰富的功能。
其一般格式为
SELECT[ALL|DISTINCT]<目标列表达式>[,<目标表达式>]
FROM<表名或视图名>[,<表名或视图名>]..
[WHERE<条件表达式>]
[GROUPBY<列表1>[HAVING<条件表达式>]]
[ORDERBY<列表2>[ASC|DESC]]
整个SELECT语句的含义是:
根据WHERE子句的设定条件,从FROM子句指定的基本表或视图中找出满足条件的元组,再按照目标表达式规定的属性列选出元组中的对应属性形成结果。
如果由GROUP子句,则将结果按<列名1>的值进行分组,该属性列值相等的元组为一个组。
如果GROUP子句带HAVING短语,则只输出满足指定条件的组。
如果由ORDER子句,则结果表还要按<列名2>的值升序或降序方式排序.
三、实验软硬件环境
装有MicrosoftSQLServer2008ManagementStudio的电脑
4、实验过程(实验步骤、记录、数据、分析)
实验准备:
在实验一的基础上(包括数据库的建立、定义表和添加表内容)进行实验,下面分别为depts表、students表、courses表、reports表。
一、简单的选择与投影查询
1、无条件查询
1.1查询全体学生的详细记录。
这是一个无条件的选择查询,其命令为:
其命令为:
select*fromstudents
运行结果如右图,显示整张表的内容。
1.2查询全体学生的姓名(Sname)、学号(Sno)、所在系(dno)。
这是一个无条件的投影查询,其命令为:
selectsname,ssex
fromstudents
运行结果如右图,显示了表中的三列。
1.3查询全体学生的姓名(Sname)、出生年份及学号(Sno)。
其命令为:
selectsno,sname,2017-sageasbirthfromstudents
运行结果如右图,显示了三列内容。
1.4查询全体学生的姓名、出生年份和学号,要求用小写字母表示学号中的字母。
其命令为:
selectsname,'birth:
'title,YEAR(GETDATE())-sagebirthyear,LOWER(sno)lsno
fromstudents
运行结果如右图,显示了四列内容。
1.5查询选修了课程的学生学号。
其命令为:
selectdistinctsno
fromreports
运行结果如右图,distinct短语是为了消去查询结果中的重复值。
2、条件查询
(1)比较条件
1.1查询d03系全体学生的学号(Sno)和姓名(Sname)。
其命令为:
selectsno,sname
fromstudents
wheredno='d03'
运行结果如右图,显示了d03系的全体学生姓名与学号。
1.2查询所有年龄在18~22岁(包括18岁和22岁)之间的学生姓名(Sname)及年龄(Sage)。
其命令为:
selectsno,sage
fromstudents
wheresage>=18andsage<=20
运行结果如右图,共有5名在18岁到20岁的学生。
(2)谓语条件
2.1查询年龄在18~22岁(包括18岁和22岁)之间的学生姓名(Sname)及年龄(Sage)。
其命令为:
selectsno,sage
fromstudents
wheresagebetween18and20
运行结果如右图,结果与比较条件的结果一样。
2.2查询年龄不在18-22岁之间的学生姓名(Sname)及年龄(Sage)。
其命令为:
selectsno,sage
fromstudents
wheresagenotbetween18and20
运行结果如右图,共有2个人不在18岁到20岁之间。
2.3查询系别编号为d01、d02、d03学生的学号(Sno)、姓名(Sname)和性别(Ssex)。
其命令为:
selectsno,sname,ssex
fromstudents
wheredno='d01'ordno='d02'ordno='d03'
运行结果如右图,共有6个人。
2.4查询系别编号既不是d01、d02,也不是d03的学生的姓名(Sname)和性别(Ssex)。
其命令为:
selectsname,ssex
fromstudents
wherednonotin('d01','d02','d03')
运行结果如右图,因为没有其他系别的学生,因此查询出来的是空表。
2.5查询所有姓刘的学生的姓名(Sname)、学号(Sno)和性别(Ssex)。
其命令为:
selectsname,sno,ssex
fromstudents
wheresnamelike'刘%'
运行结果如右图,只有一个人姓刘。
2.6查询姓“刘”而且全名不多于3个汉字的学生的姓名(sname)和所在系(dno),其命令为:
selectsname,dno
fromstudents
wheresnamelike'刘__'
运行结果如右图,在英文系统中下横线“_”代表一个英文字符,但在支持汉字的数据库系统中下横线“_”也代表一个汉字,所以匹配串“刘”的后面只需要跟2个“_”(下横线)
2.7查询所有不姓刘的学生姓名(Sname)和年龄(Sage)。
其命令为:
selectsname,sage
fromstudents
wheresnamenotlike'刘%'
运行结果如右图,5个人不姓刘。
2.8查询课程名为“DB_设计”的课程号(Cno)和学分(Credits)。
其命令为:
selectcno,credits
fromcourses
wherecnamelike'DB\_设计'escape'\'
运行结果如右图,ESCAPE‘\’短语表示\为换码字符,这样匹配串中紧跟在\后面的字符“_”不再具有通配符的含义,转义为普通的“”字符。
2.9查询以"DB_"开头,且倒数第2个汉字字符为“设”的课程的详细情况。
其命令为:
select*
fromcourses
wherecnamelike'DB\_设_'escape'\'
运行结果如右图,这里的匹配串为“DB\_%设_。
第1个“_”前面有换码字符“\”,所以它被转义为普通的“_”字符。
由于“%”和“设”字后面的下横线“_”的前面均没有换码字符“\”,所以它们仍作为通配符。
2.10假设某些学生选修课程后没有参加考试,所以有选课记录,但没有考试成绩。
试查询缺少成绩的学生的学号(Sno)和相应的课程号(Cno)。
其命令为:
selectsno,cno
fromreports
wheregradeISNULL
运行结果如右图,没有缺少成绩的学生,因此查询结果为空。
IS不能用等号“=”代替,否则不能得到正确的查询结果。
2.11查询所有有成绩的学生学号(Sno)和课程号(Cno)。
其命令为:
selectsno,cno
fromreports
wheregradeisnotNULL
运行结果如右图,
3、查询结果排序
3.1查询选修了C03号课程的学生的学号(Sno)和成绩(Grade),并按成绩降序排列。
其命令为:
selectsno,grade
fromreports
wherecno='c03'
orderbygradedesc
运行结果如右图,查询并降序排列。
3.2查询全体学生情况,查询结果按所在系的系别编号(dnot)升序排列,同一系中的学生按年龄(Sage)降序排列。
其命令为:
select*
fromstudents
orderbydno,sagedesc
运行结果如右图
3、集函数的使用
4.1查询学生总人数。
其命令为:
selectCOUNT(*)renshu
fromstudents
运行结果如右图,学生总人数为6人。
4.2查询选修了课程的学生人数。
其命令为:
selectCOUNT(distinctsno)renshu
fromreports
运行结果如右图,为了避免重复,必须在函数中使用distinct短语。
4.3计算选修C01号课程的学生平均成绩。
其命令为:
selectAVG(grade)平均成绩
fromreports
wherecno='c01'
运行结果如右图
4.4查询选修C01号课程的学生最高分数。
其命令为:
selectmax(grade)最高分
fromreports
wherecno='c01'
运行结果如右图
4、查询结果分组
5.1求各个课程号(Cno)及相应的选课人数。
其命令为:
selectcno,count(sno)cntsno
fromreports
groupbycno
运行结果如右图
5.2查询选修了3门或3门以上课程的学生学号(Sno)。
其命令为:
selectsno
fromreports
groupbysno
havingCOUNT(cno)>=3
运行结果如右图
2、连接查询
1、不同表之间的连接查询
1.1查询每个学生及其选修课程的情况。
本查询实际上是涉及Students与Reports两个表的连接操作。
这两个表之间的联系是通过公共属
性Sno实现的,因此,其操作命令为:
selectstudents.*,reports.*
fromstudents,reports
wherestudents.sno=reports.sno
运行结果如右图
1.2查询每个学生的学号(Sno)、姓名(Sname)、选修的课程名(Cname)及成绩(Grade)。
本查询涉及到三个表的连接操作,完成该查询的SQL语句如下:
selectstudents.sno,sname,cname,grade,dname
fromstudents,reports,courses,depts
wherestudents.sno=reports.snoando=o
andstudents.dno=depts.dno
运行结果如右图
2、自身连接
2.1查询每一门课的间接先修课(即先修课的先修课)。
在Courses表关系中,只有每门课的直接先修课信息,而没有先修课的先修课。
要得到这个信息,必须先对一门课找到其先修课,再按此先修课的课程号,查找它的先修课程。
这就需要要将Courses表与其自身连接。
为方便连接运算,这里为Courses表取两个别名分别为A,B。
则完成该查询的SQL语句为:
selectA.cno,A.cname,B.Pre_Cno
fromcoursesA,coursesB
WHEREA.Pre_Cno=B.Cno
运行结果如右图
3、外连接
把连接查询中1.2中的等值连接改为左连接。
该左连接操作在SQLServer2000中的命令格式为:
selectstudents.sno,sname,ssex,sage,dno,cno,grade
fromstudents,reports
wherestudents.sno=reports.sno
运行结果如右图
3、嵌套查询
1、带谓词IN的嵌套查询
1.1查询选修了编号为“C02”的课程的学生姓名(Sname)和所在系别编号(dno)。
其命令为:
selectsname,dno
fromstudents
wheresnoin
(selectsno
fromreports
wherecno='c02')
运行结果如右图,在本例中的“select sno fromreportswherecno=‘c02’"称为子查询,“selectsname,dnofromstudentswheresnoin(…)”称为外层查询或父查询。
SQL语言允许多层嵌套查询。
需要特别指出的是,子查询的SELECT语句中不能使用ORDERBY子句。
嵌套查询一般的求解方法是由里向外处理,即先执行子查询,后执行父查询。
子查询的结果用于建立父查询的查找条件。
嵌套查询可以使用多个简单查询构成复杂的查询,从而增强SQL的查询能力。
以层层嵌套的方式来构造程序正是SQL中“结构化”的含义所在。
1.2查询与“李伟”在同一个系学习的学生学号(Sno)、姓名(Sname)和系别编号(dno)。
该查询可构造嵌套查询实现,其SQL语句如下:
selectsno,sname,dno
fromstudents
wherednoin
(selectdno
fromstudents
wheresname='李伟')
运行结果如右图,DBMS求解该查询时先执行子查询,找出“李伟”所在的系别编号(dno),然后查询在该系学习的学生学号和姓名。
1.3查询选修了课程名为“数据结构”的学生学号(Sno)和姓名(Sname)。
本查询涉及学号、姓名和课程名(Cname)三个属性。
学号和姓名存放在Students表中,课程名的存放在Courses表中,但Students与Courses两个表之间没有公共属性,必须通过Reports表建立它们之间的联系。
所以本查询实际上涉及三个关系的连接操作。
selectsno,sname
fromstudents
wheresnoin
(selectsno
fromreports
wherecnoin
(selectcno
fromcourses
wherecname='数据结构'))
运行结果如右图。
2、
带有比较运算符的嵌套查询
2.1将嵌套查询中的1.2改为带有比较运算符的嵌套查询。
由于一个学生只可能在一个系学习,因此子查询的结果是一个值,因此可以用=代替IN,其SQL语句如下:
selectsno,sname,dno
fromstudents
wheredno=
(selectdno
fromstudents
wheresname='李伟')
运行结果如右图,和1.2的查询结果一样。
3、带谓词ANY或ALL的嵌套查询
3.1查询系别编号为d01系的学生,而且年龄不超过d01系所有学生的年龄的学生学号(sno)、姓名(Sname)、性别(ssex)、系别编号(dno)和年龄(Sage)。
其查询命令为
selectsno,sname,ssex,sage,dno
fromstudents
wheredno<>'d01'
andsage<=all(selectsage
fromstudents
wheredno='d01')
运行结果如右图
4、
带谓词EXISTS的嵌套查询
4.1查询所有选修了编号为“C01”课程的学生姓名(Sname)和所在系别编号(dno)。
其查询命令为:
selectsname,dno
fromstudents
whereexists
(select*
fromreports
wheresno=students.snoandcno='c01')
运行结果如右图
4.1将嵌套查询中的1.3改为带谓词EXISTS的查询,其SQL语句如下
selectsno,sname,dno
fromstudentsa
whereexists
(select*
fromstudentsb
whereb.dno=A.dnoandb.sname='李伟')
运行结果如右图
4.3询选修了所有课程的学生姓名(Sname)和所在系别编号(dno)。
其命令为
selectsname,dno
fromstudents
wherenotexists
(select*
fromcourses
wherenotexists
(select*
fromreports
wheresno=students.snoandcno=o))
运行结果如右图,因为没有学生选修了所有的课程,因此查询结果为空。
5、集合查询
1.查询系别编号为d02的学生或年龄不大于20岁的学生信息。
其命令为
fromstudents
wheredno='d02'
union
select*
fromstudents
wheresage<=20
运行结果如右图
2.查询系别编号为d03的学生且年龄不大于20岁的学生的交集,这实际上就是查询d03系中年龄不大于20岁的学生。
其命令为
select*
fromstudents
wheredno='d03'andsage<=20
运行结果如右图
3.查询系别编号为d03的学生与年龄不大于18岁的学生的差集。
其命令为
select*
fromstudents
wheredno='d03'andsage>18
运行结果如右图
五、测试/调试及实验结果分析
实验结果与分析如上所述
六、实验结论与体会
通过本次实验,经过一步一步地调试与分析,把课堂所学知识应用于实际操作中,锻炼了自己的耐心,使我基本掌握了数据查询的整个过程,能够熟练地使用select语句进行查询语句,对简单查询、连接查询、嵌套查询的操作方法也有更深的理解。
年月日
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 原理 实验 数据 查询
![提示](https://static.bdocx.com/images/bang_tan.gif)