2022年《数据库原理》上机实验 .pdf

《2022年《数据库原理》上机实验 .pdf》由会员分享，可在线阅读，更多相关《2022年《数据库原理》上机实验 .pdf（7页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据库原理上机实验三实验名称：表连接和SELECT 的嵌套使用一、实验目的1. 理解连接语句的操作和基本使用方法，掌握内连接、外连接、自身连接的概念和使用。2. 掌握 SELECT 语句的嵌套使用，实现表的复杂查询，进一步理解SELECT 语句的高级使用方法。二、实验内容与步骤连接查询1 不同表之间的连接查询例 3.37查询每个学生及其选修课程的情况。本查询实际上是涉及Students 与 Reports 两个表的连接操作。这两个表之间的联系是通过公共属性Sno 实现的，因此，其操作命令为：SELECT Students.*, Reports.* FROM Students, Reports

2、WHERE Students.Sno = Reports.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,

3、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

4、 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 2000 中的命令格式为：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 7 页 - - - - - - - - - SELECT Students.Sno, Sname, Ssex, Sdept, Cno, Grade

5、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

6、 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

7、.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 S

8、tudents /* 取出 Sno 和 Sname */ 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 7 页 - - - - - - - - - WHERE Sno IN (SELECT Sno /* 然后在 SC 关系中找出*/ FROM Reports /*选修了 3 号课程的学生学号*/ WHERE Cno IN (SELECT Cno /* 首先在 Courses 关系中*/ FROM Courses /*找出“数据结构”的课程号，*/ WHERE Cname

9、 = 数据结构 ) ；/*结果为 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 S

10、dept = (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 Student

11、s WHERE Sdept 自动化 AND Sage= (SELECT MIN(Sage) FROM Students WHERE Sdept=自动化 ); 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 7 页 - - - - - - - - - 4 带谓词 EXISTS的嵌套查询例 3.46查询所有选修了编号为“C01”课程的学生姓名(Sname)和所在系 (Sdept)。本查询的SQL 语句是：SELECT Sname, Sdept FROM Students WH

12、ERE 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)和所在系。由于没有全称量词，可将题目的意思转换成等价的用存在量词的形式：查询这样的

13、学生，没有一门课程是他不选修的。其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

14、=20; 例 3.50 查询数学系的学生且年龄不大于20 岁的学生的交集， 这实际上就是查询数学系中年龄不大名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 7 页 - - - - - - - - - 于 20 岁的学生。SELECT * FROM Students WHERE Sdept=数学 AND Sage20; 下列查询使用的基本表与上机实验一中Sale_Manage 数据库中表相同。1. 用 T-SQL语句完成下列涉及表连接的查询操作。（ 1）查找出employe

15、e 表中部门相同且住址相同的女员工的姓名、性别、职称、薪水、住址。select x.emp_name,x.sex,x.title,x.salary,x.addr,y.emp_name,y.sex,y.title,y.salary,y.addr from employee as x inner join employee as y on (x.emp_no!=y.emp_no)and(x.addr=y.addr)and(x.sex=女)and(x.dept=y.dept)and(y.sex=女)（2）检索 product 表和 sale_item表中相同产品的产品编号、产品名称、数量、单价。se

16、lect x.prod_id,x.qty,x.unit_price,y.prod_name from sale_item as x inner join product as y on(x.prod_id=y.prod_id) order by x.prod_id （ 3）检索 product 表和 sale_item表中单价高于500 元的相同产品的产品编号、产品名称、数量、单价。select x.prod_id,x.qty,x.unit_price,y.prod_name from sale_item as x inner join product as y on(x.prod_id=y.

17、prod_id)and unit_price500 order by x.prod_id (4 ）找出公司中姓名相同的员工，并且依据员工编号排序这些员工信息。 select x.emp_no,x.emp_name,y.emp_no,y.emp_name from employee as x inner join employee as y on (x.emp_no!=y.emp_no)and(x.emp_name=y.emp_name) order by x.emp_no desc 2. 用 T-SQL语句完成下列涉及SELECT 语句嵌套使用的查询操作。（ 1）由 sales 表中查找出销售

18、金额最高的订单。select * from sales where tot_amt= (select max(tot_amt) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 7 页 - - - - - - - - - from sales) （ 2）由 sales 表中查找出订单金额大于“E006业务员在2004/10/15这天所接任一张订单的金额”的所有订单，并显示承接这些订单的业务员和该条订单的金额。select sale_id,tot_amt from sales

19、 where tot_amtall (select tot_amt from sales where sale_id=E006and order_date=2004/10/15) order by tot_amt （ 3）找出公司女业务员所接的订单。select emp_no from employee where sex=女 （ 4）找出目前业绩未超过200000 元的员工。select emp_no,emp_name from employee where salary3) 三、实验报告按实验名称、实验目的、实验内容、实验结果的顺序在作业本上写出。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 7 页 - - - - - - - - -