数据库原理上机实验3.docx
- 文档编号:983080
- 上传时间:2022-10-14
- 格式:DOCX
- 页数:9
- 大小:18.06KB
数据库原理上机实验3.docx
《数据库原理上机实验3.docx》由会员分享,可在线阅读,更多相关《数据库原理上机实验3.docx(9页珍藏版)》请在冰豆网上搜索。
数据库原理上机实验3
《数据库原理》上机实验三
实验名称:
表连接和SELECT的嵌套利用
一、实验目的
1.理解连接语句的操作和大体利用方式,掌握内连接、外连接、自身连接的概念和利用。
2.掌握SELECT语句的嵌套利用,实现表的复杂查询,进一步理解SELECT语句的高级利用方式。
二、实验内容与步骤
连接查询
1不同表之间的连接查询
例查询每一个学生及其选修课程的情形。
本查询实际上是涉及Students与Reports两个表的连接操作。
这两个表之间的联系是通过公共属性Sno实现的,因此,其操作命令为:
SELECTStudents.*,Reports.*
FROMStudents,Reports
WHERE=;
说明:
若在以上等值连接中把目标列中重复的属性列去掉则为自然连接,其命令为
SELECT,Sname,Ssex,Sage,Sdept,Cno,Grade
FROMStudents,Reports
WHERE=;
例查询每一个学生的学号(Sno)、姓名(Sname)、选修的课程名(Cname)及成绩(Grade)。
本查询涉及到三个表的连接操作,完成该查询的SQL语句如下:
SELECT,Sname,Cname,Grade
FROMStudents,Reports,Courses
WHERE=AND=;
2自身连接
例查`询每一门课的间接先修课(即先修课的先修课)。
在Courses表关系中,只有每门课的直接先修课信息,而没有先修课的先修课。
要取得那个信息,必需先对一门课找到其先修课,再按此先修课的课程号,查找它的先修课程。
这就需要要将Courses表与其自身连接。
为方便连接运算,这里为Courses表取两个别名别离为A,B。
则完成该查询的SQL语句为:
SELECT,,
FROMCoursesA,CoursesB
WHERE=;
3外连接
例把例中的等值连接改成左连接。
该左连接操作在SQLServer2000中的命令格式为:
SELECT,Sname,Ssex,Sdept,Cno,Grade
FROMStudents
LEFTJOINReportsON
=;
说明:
以上左连接操作也能够用如下的右连接操作代替,其结果完全一样。
SELECT,Sname,Ssex,Sdept,Cno,Grade
FROMReports
RIGHTJOINStudentsON
=;
嵌套查询
1带谓词IN的嵌套查询
例查询选修了编号为“C02”的课程的学生姓名(Sname)和所在系(Sdept)。
SELECTSname,Sdept
FROMStudents
WHERESnoIN
(SELECTSno
FROMReports
WHERECno='C02');
例查询与“李伟”在同一个系学习的学生学号(Sno)、姓名(Sname)和系名(Sdept)。
该查询可构造嵌套查询实现,其SQL语句如下:
SELECTSno,Sname,Sdept
FROMStudents
WHERESdeptIN
(SELECTSdept
FROMStudents
WHERESname=’李伟’);
说明:
本例中的查询也能够用自身连接来完成,其SQL语句如下:
SELECT,,
FROMStudentsA,StudentsB
WHERE=AND=’李伟’;
例查询选修了课程名为“数据结构”的学生学号(Sno)和姓名(Sname)。
本查询涉及学号、姓名和课程名(Cname)三个属性。
学号和姓名寄存在Students表中,课程名的寄存在Courses表中,但Students与Courses两个表之间没有公共属性,必需通过Reports表成立它们之间的联系。
所以本查询实际上涉及三个关系的连接操作。
SELECTSno,Sname/*③最后在Studenst关系中*/
FROMStudents/*掏出Sno和Sname*/
WHERESnoIN
(SELECTSno/*②然后在SC关系中找出*/
FROMReports/*选修了3号课程的学生学号*/
WHERECnoIN
(SELECTCno/*①第一在Courses关系中*/
FROMCourses/*找出“数据结构”的课程号,*/
WHERECname=‘数据结构’));/*结果为C02号*/
说明:
本查询一样能够用连接查询实现:
SELECT,Sname
FROMStudentsS,ReportsR,CoursesC
WHERE=AND=AND='数据结构';
2带有比较运算符的嵌套查询
例将例改成带有比较运算符的嵌套查询。
由于一个学生只可能在一个系学习,因此子查询的结果是一个值,因此能够用=代替IN,其SQL语句如下:
SELECTSno,Sname,Sdept
FROMStudents
WHERESdept=
(SELECTSdept
FROMStudents
WHERESname=’李伟’);
3带谓词ANY或ALL的嵌套查询
例查询非自动化系的不超过自动化系所有学生的年龄的学生姓名(Sname)和年龄(Sage)。
其查询命令为
SELECTSname,Sage
FROMStudents
WHERESdept<>'自动化'
ANDSage<=ALL(SELECTSage
FROMStudents
WHERESdept='自动化');
说明:
本查询也能够用集函数来实现。
其SQL语句如下:
SELECTSname,Sage
FROMStudents
WHERESdept<>'自动化'
ANDSage<=(SELECTMIN(Sage)
FROMStudents
WHERESdept='自动化');
4带谓词EXISTS的嵌套查询
例查询所有选修了编号为“C01”课程的学生姓名(Sname)和所在系(Sdept)。
本查询的SQL语句是:
SELECTSname,Sdept
FROMStudents
WHEREEXISTS
(SELECT*
FROMReports
WHERESno=ANDCno='C01');
例将例改成带谓词EXISTS的查询,其SQL语句如下
SELECTSno,Sname,Sdept
FROMStudentsA
WHEREEXISTS
(SELECT*
FROMStudentsB
WHERE=AND=’李伟’);
例查询选修了所有课程的学生姓名(Sname)和所在系。
由于没有全称量词,可将题目的意思转换成等价的用存在量词的形式:
查询如此的学生,没有一门课程是他不选修的。
其SQL语句为:
SELECTSname,Sdept
FROMStudents
WHERENOTEXISTS
(SELECT*
FROMCourses
WHERENOTEXISTS
(SELECT*FROMReportsWHERESno=
ANDCno=);
集合查询
例查询运算机科学系的学生或年龄不大于20岁的学生信息。
SELECT*
FROMStudents
WHERESdept='运算机'
UNION
SELECT*
FROMStudents
WHERESage<=20;
例查询数学系的学生且年龄不大于20岁的学生的交集,这实际上就是查询数学系中年龄不大于20岁的学生。
SELECT*
FROMStudents
WHERESdept='数学'ANDSage<=20;
例查询数学系的学生与年龄不大于20岁的学生的差集。
本查询的等价说法是,查询数学系中年龄大于20岁的学生。
SELECT*
FROMStudents
WHERESdept='运算机'ANDSage>20;
下列查询利用的大体表与上机实验一中Sale_Manage数据库中表相同。
1.用T-SQL语句完成下列涉及表连接的查询操作。
(1)查找出employee表中部门相同且住址相同的女员工的姓名、性别、职称、薪水、住址。
select,,,,,,,,,
fromemployeeasxinnerjoinemployeeasy
on!
=and=and='女')and=and='女')
(2)检索product表和sale_item表中相同产品的产品编号、产品名称、数量、单价。
select,,,
fromsale_itemasxinnerjoinproductasy
on=
orderby
(3)检索product表和sale_item表中单价高于500元的相同产品的产品编号、产品名称、数量、单价。
select,,,
fromsale_itemasxinnerjoinproductasy
on=andunit_price>500
orderby
(4)找出公司中姓名相同的员工,而且依据员工编号排序这些员工信息。
select,,,
fromemployeeasxinnerjoinemployeeasy
on!
=and=
orderbydesc
2.用T-SQL语句完成下列涉及SELECT语句嵌套利用的查询操作。
(1)由sales表中查找出销售金额最高的定单。
select*
fromsales
wheretot_amt=
(selectmax(tot_amt)
fromsales)
(2)由sales表中查找出定单金额大于“E006业务员在2004/10/15此日所接任一张定单的金额”的所有定单,并显示承接这些定单的业务员和该条定单的金额。
selectsale_id,tot_amt
fromsales
wheretot_amt>all
(selecttot_amt
fromsales
wheresale_id='E006'andorder_date='2004/10/15')
orderbytot_amt
(3)找出公司女业务员所接的定单。
selectemp_no
fromemployee
wheresex='女'
(4)找出目前业绩未超过200000元的员工。
selectemp_no,emp_name
fromemployee
wheresalary<200000
3.利用数据库ST中的相关表完成查询。
(1)检索选修课程名为“MS”的学生号和学生姓名。
(连接查询和嵌套查询两种方式)
嵌套查询:
selectSno,Sname
froms
whereSnoin
(selectSno
fromc
whereCname='MS')
连接查询:
selectSno,Sname
froms,c
where=andCname='MS'
(2)检索至少选修了课程号为c01和c02的学生学号。
select
f
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 原理 上机 实验