实验五 连接、嵌套和集合查询

实验五 连接、嵌套和集合查询

课时分配:4课时

实验性质:综合

实验主要内容及方法:各种连接、嵌套和集合查询方法

实验要求:

(1) 掌握连接、嵌套和集合查询语句的一般格式。;

(2) 掌握连接、嵌套和集合查询的各种使用方法。

实验目的:熟练掌握连接、嵌套和集合查询的使用。

实验设备:装有SQL SERVER 2005的电脑

实验步骤:

(1) 启动2005 SQL Server Management studio;

(2) 启动数据库引擎查询;

(3) 在实验四数据库的基础上,综合练习如下例题:

一、连接查询

1 不同表之间的连接查询

例3.37  查询每个学生及其选修课程的情况。

本查询实际上是涉及Students与Reports两个表的连接操作。这两个表之间的联系是通过公共属性Sno实现的,因此,其操作命令为:

SELECT Students.*, Reports.*
FROM Students, Reports
WHERE Students.Sno = Reports.Sno

说明:若在以上等值连接中把目标列中重复的属性列去掉则为自然连接其命令为
去除重复的sno

SELECT Students.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Students, Reports
WHERE Students.Sno= Reports.Sno;

例3.38 查询每个学生的学号(Sno)、姓名(Sname)、选修的课程名(Cname)及成绩(Grade)。

本查询涉及到三个表的连接操作,完成该查询的SQL语句如下:

SELECT Students.Sno, Sname, Cname, Grade
FROM Students, Reports, Courses
WHERE Students.Sno=Reports.Sno  AND
Reports.Cno=Courses.Cno;

 

2 自身连接

例3.39 查`询每一门课的间接先修课(即先修课的先修课)。

在Courses表关系中,只有每门课的直接先修课信息,而没有先修课的先修课。要得到这个信息,必须先对一门课找到其先修课,再按此先修课的课程号,查找它的先修课程。这就需要要将Courses表与其自身连接。为方便连接运算,这里为Courses表取两个别名分别为A,B。则完成该查询的SQL语句为:

SELECT A.Cno, A.Cname, B.Pre_Cno
FROM Courses A, Courses B
WHERE A.Pre_Cno =B.Cno;

3 外连接

例3.40把例3.37中的等值连接改为左连接。该左连接操作在SQL Server 2005中的命令格式为:

SELECT Students.Sno, Sname, Ssex, Sdept, Cno, Grade
FROM Students
LEFT JOIN Reports ON
Students.Sno= Reports.Sno;

说明:以上左连接操作也可以用如下的右连接操作代替,其结果完全一样。

SELECT Students.Sno, Sname, Ssex, Sdept, Cno, Grade
FROM Reports
RIGHT JOIN Students ON
Reports.Sno=Students.Sno;

二、嵌套查询

1 带谓词IN的嵌套查询

例3.41 查询选修了编号为“C02”的课程的学生姓名(Sname)和所在系(Sdept)。

SELECT Sname, Sdept
FROM Students
WHERE Sno IN
(SELECT Sno
FROM Reports
WHERE Cno='C02');

例3.42 查询与“李伟”在同一个系学习的学生学号(Sno)、姓名(Sname)和系名(Sdept)。

该查询可构造嵌套查询实现,其SQL语句如下:

SELECT Sno, Sname, Sdept
FROM Students
WHERE Sdept IN
(SELECT Sdept
FROM Students
WHERE Sname='李伟');

说明:本例中的查询也可以用自身连接来完成,其SQL语句如下:

SELECT A.Sno , A.Sname , A.Sdept
FROM Students A , Students B
WHERE A.Sdept=B.Sdept AND B.Sname='李伟';

例3.43  查询选修了课程名为“数据结构”的学生学号(Sno)和姓名(Sname)。

本查询涉及学号、姓名和课程名(Cname)三个属性。学号和姓名存放在Students表中,课程名的存放在Courses表中,但Students与Courses两个表之间没有公共属性,必须通过Reports表建立它们之间的联系。所以本查询实际上涉及三个关系的连接操作。

SELECT Sno, Sname /* ③最后在Studenst关系中 */
FROM Students /* 取出Sno和Sname */
WHERE Sno IN
(SELECT Sno /*② 然后在SC关系中找出 */
FROM Reports /*选修了3号课程的学生学号*/
WHERE Cno IN
(SELECT Cno /*① 首先在Courses关系中 */
FROM Courses /*找出“数据结构”的课程号,*/
WHERE Cname = '数据结构')); /*结果为C02号 */

说明:本查询同样可以用连接查询实现:

SELECT S.Sno, Sname
FROM Students S, Reports R, Courses C
WHERE S.Sno=R.Sno AND R.Cno=C.Cno AND C.Cname='数据结构';

2 带有比较运算符的嵌套查询

例3.44 将例3.42改为带有比较运算符的嵌套查询。由于一个学生只可能在一个系学习,因此子查询的结果是一个值,因此可以用=代替IN,其SQL语句如下:

SELECT Sno , Sname, Sdept
FROM Students
WHERE Sdept =
(SELECT Sdept
FROM Students
WHERE Sname='李伟');

3 带谓词ANY或ALL的嵌套查询

例3.45  查询非自动化系的不超过自动化系所有学生的年龄的学生姓名(Sname)和年龄(Sage)。其查询命令为

 

SELECT Sname, Sage
FROM Students
WHERE Sdept<>'自动化'
AND Sage<=ALL (SELECT Sage
FROM Students
WHERE Sdept= '自动化');

 

说明:本查询也可以用集函数来实现。其SQL语句如下:

SELECT Sname, Sage
FROM Students
WHERE Sdept<>'自动化'
AND Sage<= (SELECT MIN(Sage)
FROM Students
WHERE Sdept='自动化');

4 带谓词EXISTS的嵌套查询

例3.46  查询所有选修了编号为“C01”课程的学生姓名(Sname)和所在系(Sdept)。

本查询的SQL语句是:

SELECT Sname, Sdept
FROM Students
WHERE EXISTS
(SELECT *
FROM Reports
WHERE Sno=Students.Sno AND Cno='C01');

 

例3.47 将例3.42改为带谓词EXISTS的查询,其SQL语句如下

 

SELECT Sno, Sname, Sdept
FROM Students A
WHERE EXISTS
(SELECT *
FROM Students B
WHERE B.Sdept=A.Sdept AND B.Sname='李伟');

 

例3.48  查询选修了所有课程的学生姓名(Sname)和所在系。

由于没有全称量词,可将题目的意思转换成等价的用存在量词的形式:查询这样的学生,没有一门课程是他不选修的。其SQL语句为:

SELECT Sname, Sdept
FROM Students
WHERE NOT EXISTS
(SELECT *
FROM Courses
WHERE NOT EXISTS
(SELECT * FROM Reports WHERE Sno=Students.Sno
AND Cno=Courses.Cno));

 

三、集合查询

例3.49 查询计算机科学系的学生或年龄不大于20岁的学生信息。

 

SELECT *
FROM Students
WHERE Sdept='计算机'
UNION
SELECT *
FROM Students
WHERE Sage<=20;

 

例3.50  查询数学系的学生且年龄不大于20岁的学生的交集,这实际上就是查询数学系中年龄不大于20岁的学生。

 

SELECT *
FROM Students
WHERE Sdept='数学' AND Sage<=20;

例3.51  查询数学系的学生与年龄不大于20岁的学生的差集。

本查询的等价说法是,查询数学系中年龄大于20岁的学生。

 

SELECT *
FROM Students
WHERE Sdept='数学' AND Sage>20;

1、基于实验一建立的 “图书读者数据库”(Book_Reader_DB),输入部分虚拟数据;假定实验一的实验后的结果数据库的关系模式如下: 图书(书号,类别,出版社,作者,书名,定价,数量,购买日期,备注) 读者(编号,姓名,单位,性别,电话) 借阅(书号,读者编号,借阅日期,还书日期) 2、打开查询分析器,并建立与服务器的连接,在数据库对象下拉框中选择“Book_Reader_DB”,在查询分析器输入区中输入Transact-SQL语句,然后点击“查询”菜单中的“执行”菜单项(或用F5快捷键),执行输入区的Transact-SQL语句,如下图所示; 3、用连接查询嵌套查询的方法实现下列查询 在“图书读者数据库”(Book_Reader_DB)中,用连接查询的方法完成下列查询任务: ① 查询借阅了类别为“环境”类图书的所有读者编号、姓名及单位; ② 查询借阅过图书的读者编号、姓名及单位; ③ 查询姓名为“李明”的读者目前借阅的图书书号和书名; ④ 查询借书过期的所有读者姓名及所借图书名(假定借阅期为60天); ⑤ 查询没有借阅过图书的所有读者姓名; ⑥ 查询借出次数超过10次的所有图书的书号和书名; ⑦ 查询除已还的书以外,目前借了5本或以上图书的读者编号和姓名; ⑧ 查询书库中除借出的图书外,现存的图书书号、书名、现存数量;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值