数据库原理与应用实验报告嵌套查询.docx
《数据库原理与应用实验报告嵌套查询.docx》由会员分享,可在线阅读,更多相关《数据库原理与应用实验报告嵌套查询.docx(20页珍藏版)》请在冰豆网上搜索。
数据库原理与应用实验报告嵌套查询
《数据库原理及应用》电子实验报告
题目:
SQL语言数据查询—嵌套查询
日期
姓名
QQ
实验环境:
Windows7,SqlServer2005
实验目的:
1.理解嵌套查询的意义,明确嵌套查询的分类
2.理解嵌套查询求解方法
3.掌握带有IN谓词、比较运算符、ANY或ALL谓词、EXISTS谓词的子查询
实验内容:
1.嵌套查询
1)嵌套查询的定义
一个SELECT-FROM-WHERE语句称为一个查询块。
将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。
此查询块称为子查询或内层查询,包含子查询的查询块称为父查询或外层查询。
2)嵌套查询的分类
嵌套查询的分类有相关子查询和不相关子查询。
2.嵌套查询求解方法
1)不相关子查询
不相关子查询是由里向外逐层处理。
即每个子查询在上一级查询处理之前求解,子查询的结果用于建立其父查询的查询条件。
2)相关子查询
相关子查询首先取外层查询中表的第一个元组,把与内层查询相关的属性值代入并处理内层查询,若WHERE子句返回值为真,则取此元组放入结果表;然后再取外层表的下一个元组;重复这一过程,直至外层表全部检查完为止。
3.采用的数据库表
在数据库中,数据库表是一系列二维数组的集合,用来代表和存储数据对象之间的的关系。
它是由纵向的列和横向的行组成。
可以是一对一,一对多,多对多的关系。
一般情况下是一对一的关系。
1)库表结构定义:
学生表:
CREATETABLEStudent/*建立一个"学生"表*/
(SnoCHAR(9)PRIMARYKEY,
SnameCHAR(20)UNIQUE,
SsexCHAR
(2),
SageSMALLINT,
SdeptCHAR(20)
);
课程表:
CREATETABLECourse/*建立一个"课程"表*/
(CnoCHAR(4)PRIMARYKEY,
CnameCHAR(40),
CcreditSMALLINT,
);
选课表:
CREATETABLESC/*建立一个"选课"表*/
(SnoCHAR(9),
CnoCHAR
(1),
Gradesmallint,
Primarykey(Sno,Cno));
2)库表记录
学生表:
insertintoStudentvalues(’950010001,刘涛','男',20,'CS');
insertintoStudentvalues('950010002','白明','女',19,'IS');
insertintoStudentvalues('950010003','田雅','女',18,'MA');
insertintoStudentvalues('950010004','王文','男',20,'IS');
insertintoStudentvalues('950010005','赵琦','男',21,'CS');
insertintoStudentvalues('950010006','陈永','男',18,'IS');
insertintoStudentvalues('950010007','李娟','男',18,'CS');
insertintoStudentvalues('950010008','李磊','男',18,'MA');
课程表:
insertintoCoursevalues('1','数据库','4');
insertintoCoursevalues('2','操作系统','3');
insertintoCoursevalues('3','计算方法','4');
insertintoCoursevalues('4','计算机导论','3');
insertintoCoursevalues('5','单片机','4');
insertintoCoursevalues('6','图像处理','2');
insertintoCoursevalues('7','JAVA','2');
选课表:
insertintoscvalues('950010006','1',45);
insertintoscvalues('950010007','4',85);
insertintoscvalues('950010001','1',76);
insertintoscvalues('950010001','2',58);
insertintoscvalues('950010002','3',87);
insertintoscvalues('950010003','2',95);
insertintoscvalues('950010002','6',86);
insertintoscvalues('950010003','5',82);
insertintoscvalues('950010004','6',59);
insertintoscvalues('950010005','7',95);
insertintoscvalues('950010006','3',45);
insertintoscvalues('950010005','7',95);
insertintoscvalues('950010006','2',45);
insertintoscvalues('950010008','7',95);
4.带有IN谓词的子查询(写出题目、关系代数表达式和sql语句)
1)IN谓词表达的意义
IN子查询谓词,子查询只返回一个值(集合中的一个值)。
2)构造嵌套查询的方法
SQL允许多层嵌套查询,即一个子查询中还可以嵌套其他子查询。
子查询的SELECT语句中不能使用ORDERBY子句,ORDERBY子句只能对最终结果排序。
在嵌套查询中,子查询往往是一个集合。
3)实例操作
*【实例1】查询和李磊同一个系的学生的所有信息。
代码如下:
SELECT*
FROMstudent
WHERESdeptIN
(SELECTSdept
FROMstudent
WHEREsname='李磊');
结果如图所示:
*【实例2】查询所有选了‘数据库’的学生的学号和姓名。
代码如下:
ELECTSno,Sname
FROMStudent
WHERESnoIN
(SELECTSno
FROMSC
WHERECnoIN
(SELECTCno
FROMCourse
WHERECname='数据库')
);
结果如图所示:
*【实例3】查询所选课程成绩为95的课程号及所对应的学号与年龄相同的学生的所有信息。
代码如下:
ELECT*
FROMStudent
WHERESageIN
(SELECTSage
FROMStudent
WHERESnoIN
(SELECTSno
FROMSC
WHERECnoIN
(SELECTCno
FROMSC
WHEREGrade='95')
)
);
结果如图所示:
5.带有比较运算符的子查询
1)比较运算符的种类
当能确切知道内层查询返回单值时,可用比较运算符(>,<,=,>=,<=,!
=或<>)。
2)实例操作(写出题目、关系代数表达式和sql语句及sql环境运算截图)
*【实例1】查询和‘陈永’同龄的学生的详细信息。
代码如下:
SELECT*
FROMStudent
WHERESage=
(SELECTSage
FROMStudent
WHERESname='陈永');
结果如图所示:
*【实例2】查询课程号与学号为‘950010007’所选课程一样的学生的详细信息。
代码如下:
SELECT*
FROMSC
WHERECno=
(SELECTCno
FROMSC
WHERESno='950010007');
结果如图所示:
*【实例3】查询成绩在60分以下的学生的所在系别、姓名和性别。
代码如下:
SELECTSdept,Sname,Ssex
FROMStudent
WHERESnoIN
(SELECTSno
FROMSC
WHEREGrade<'60');
结果如图所示:
6.带有ANY或ALL谓词的子查询(写出题目、关系代数表达式和sql语句及sql环境运算截图)
1)ANY或ALL谓词的含义
谓词语义:
ANY:
任意一个值ALL:
所有值
2)ANY或ALL谓词需要配合使用比较运算符
需要配合使用比较运算符
>ANY大于子查询结果中的某个值
>ALL大于子查询结果中的所有值
>=ANY大于等于子查询结果中的某个值
>=ALL大于等于子查询结果中的所有值
<=ANY小于等于子查询结果中的某个值
<=ALL小于等于子查询结果中的所有值
=ANY等于子查询结果中的某个值
=ALL等于子查询结果中的所有值(通常没有实际意义)
!
=(或<>)ANY不等于子查询结果中的某个值
!
=(或<>)ALL不等于子查询结果中的任何一个值
3)ANY与ALL与集函数的对应关系
=
<>或!
=
<
<=
>
>=
ANY
IN
--
<=MAX
>MIN
>=MIN
ALL
--
NOTIN
<=MIN
>MAX
>=MAX
4)实例操作
*【实例1】查询比任意一个女生都大的的学生的姓名和年龄。
代码如下:
SELECTsname,sage
FROMstudent
WHEREsage>ANY(SELECTsage
FROMstudent
WHERESsex='女');
结果如图所示:
*【实例2】查询成绩比5号课程成绩小的任意学生的详细信息。
代码如下:
SELECT*
FROMSC
WHEREGradeFROMSC
WHERECno='5');
结果如图所示:
*【实例3】查询比‘数据库’学分小的课程的信息。
代码如下:
SELECT*
FROMCourse
WHERECcredit(SELECTCcredit
FROMCourse
WHERECname='数据库');
结果如下图所示:
7.外连接查询(写出题目、关系代数表达式和sql语句及sql环境运算截图)
1)外连接查询的定义
外连接查询又分为:
左外连接、右外连接、全外连接三种。
它由被连接表中的列和比较运算符、逻辑运算符等构成。
2)左外连接查询的定义
左外联接的结果集包括LEFTOUTER子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。
如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值
3)右外连接查询的定义
右向外联接是左向外联接的反向联接。
将返回右表的所有行。
如果右表的某行在左表中没有匹配行,则将为左表返回空值。
4)实例操
*【实例1】列出Student左边的关系元组。
代码如下:
SELECTStudent.sno,Ssex,sname,Grade,Cno
FROMStudentLEFTJOINSCON(Student.Sno=SC.Sno);
结果如图所示:
【实例2】查询没有选修1号课程的学生姓名。
代码如下:
SELECTSname
FROMStudent
WHERENOTEXISTS
(SELECT*
FROMSC
WHERESno=Student.Sno
ANDCno='1');
结果如图所示:
*【实例3】查询每个学生的学号,姓名,以及他们选修课程的数目,结果按照课程数目的
降序排序.
代码如下:
SELECTStudent.Sno,Sname,count(Cno)
ROMStudent,SC
WHEREStudent.Sno=SC.Sno
groupbyStudent.Sno,Sname
orderbycount(Cno)desc
结果如图所示:
8.复合条件连接查询(写出题目、关系代数表达式和sql语句及sql环境运算截图)
1)复合条件连接查询的定义
Where子句中只有一个条件,即连接谓词。
在where子句中,有多个连接条件,称之为“复合条件连接”。
2)实例操作
*【实例1】查询所选课程号为‘1’的学生的姓名,性别,所在系,课程名和成绩。
代码如下:
SELECTSname,Ssex,Sdept,Cname,Grade
FROMStudent,SC,Course
WHEREStudent.Sno=SC.SnoAND
SC.Cno=Course.CnoAND
Course.Cno='1';
结果如图所示:
*【实例2】查询学号为为‘950010003’的学生的姓名,性别,所在系,课程名和成绩。
代码如下:
SELECTSname,Ssex,Sdept,Cname,Grade
FROMStudent,SC,Course
WHEREStudent.Sno=SC.SnoAND
Student.Sno=SC.SnoAND
Student.Sno='950010003';
*【实例3】查询所选课程号为‘3’的全部信息。
代码如下:
SELECT*
FROMStudent,SC,Course
WHEREStudent.Sno=SC.SnoAND
SC.Cno=Course.CnoAND
Course.Cno='3';
结果如图所示:
完成情况:
1完成了嵌套查询基本含义的理解,以课本例题为主改编联系了嵌套查询的结构。
2完成了各种谓词的子查询操作。
出现的问题:
(小四宋体)
在完成外连接查询任务时,与复合连接查询出现概念相混淆,做实际例题时,解决思路是查找相关资料和例题解决疑难。