实验5数据库综合查询 实验目的 1.掌握 SELECT语句的基本语法和查询条件表示方法; 2.掌握查询条件种类和表示方法 3.掌握连接查询的表示及使用 4.掌握嵌套查询的表示及使用 5.了解集合查询的表示及使用。 二、实验环境 1.硬件 每位学生配备计算机一台,满足相关软件的安装 件 Windows2003操作系统,安装 SQL Server2008企业版软件 3.网络 需要局域网支持 4.工具 无 实验理论与预备知识 1. SELECT语句的基本语法格式和执行方法 2.连接查询的表示及使用; 在TSQL中,连接查询有两大表示形式 (1)符合SQL标准的连接谓词表示形式 在 SELECT语句的 WHERE子句中使用比较运算符给出连接条件对表进行连接。 (2)TSQL扩展的,使用JOIN关键字的表示形式 以JOIN关键字指定的连接有三种类型:内连接( INNER JOIN)、外连接外连接( OUTER JON)、交叉连接( CROSS JOIN)。 3.嵌套查询的表示及使用; (1)带有In谓词的子查询 在嵌套查询中,子査询的结果往往是一个集合,所以谓词ⅣN是嵌套査询中最常用的谓 词。IN子查询用于进行一个给定值是否在子查询结果集中的判断。 (2)带有比较运算符的子查询 带有比较运算符的子査询是指父查询与子査询之间用比较运算符连接。当用户确切知道 内层查询返回的是单值时,可以用,>=,!=或◇等比较运算符。 (3)带有ANY或ALL谓词的子查询
实验 5 数据库综合查询 一、实验目的 1. 掌握 SELECT 语句的基本语法和查询条件表示方法； 2. 掌握查询条件种类和表示方法； 3. 掌握连接查询的表示及使用； 4. 掌握嵌套查询的表示及使用； 5. 了解集合查询的表示及使用。 二、 实验环境 1. 硬件 每位学生配备计算机一台，满足相关软件的安装； 2. 软件 Windows 2003 操作系统，安装 SQL Server 2008 企业版软件。 3. 网络 需要局域网支持； 4. 工具 无 三、实验理论与预备知识 1. SELECT 语句的基本语法格式和执行方法； 2. 连接查询的表示及使用； 在 T-SQL 中，连接查询有两大表示形式： （1）符合 SQL 标准的连接谓词表示形式 在 SELECT 语句的 WHERE 子句中使用比较运算符给出连接条件对表进行连接。 （2）T-SQL 扩展的，使用 JOIN 关键字的表示形式 以 JOIN 关键字指定的连接有三种类型：内连接（INNER JOIN）、外连接外连接（OUTER JOIN）、交叉连接（CROSS JOIN）。 3. 嵌套查询的表示及使用； （1）带有 In 谓词的子查询： 在嵌套查询中，子查询的结果往往是一个集合，所以谓词 IN 是嵌套查询中最常用的谓 词。IN 子查询用于进行一个给定值是否在子查询结果集中的判断。 （2）带有比较运算符的子查询 带有比较运算符的子查询是指父查询与子查询之间用比较运算符连接。当用户确切知道 内层查询返回的是单值时，可以用， >=， !=或<> 等比较运算符。 （3）带有 ANY 或 ALL 谓词的子查询
子査询返回单值时可以用比较运算符,但返回多值时要用ANY或ALL谓词修饰,而 使用ANY或ALL谓词时必须同时使用比较运算符 需要配合使用比较运算符 >ANY大于子查询结果中的某个值 >ALL大于子查询结果中的所有值 =ANY大于等于子查询结果中的某个值 >=ALL大于等于子查询结果中的所有值 <=ANY小于等于子查询结果中的某个值 <=ALL小于等于子查询结果中的所有值 ANY等于子查询结果中的某个值 =ALL等于子查询结果中的所有值(通常没实际意义) !=(或∞)ANY不等于子查询结果中的某个值 !=(或∞)ALL不等于子查询结果中的任何一个值 4.集合查询的表示及使用; LECT语句的查询结果是元组的集合,所以多个 SELECT语句的结果可进行集合操 作。集合操作主要包括 (1)并操作 UNION (2)交操作 INTERSECT (3)差操作 EXCEPT 四、实验任务 使用SQL语句实现查询操作。 五、实验内容及步骤 以数据库 SQL STU,数据表 student、 course、Sc及表数据为基础(数据表及相关表数 据如附表所示),请使用SQL语句实现以下査询操作,并验证查询结果。 (一)连接查询 1.查询学生的学号、姓名、性别、所选课程的课程号及成绩 2.査询课程的课程号、课程名、学分及平均成绩 3.査询所有课程被选修的详细情况,包括课程号、课程名、学号、姓名及成绩 4.查询成绩在90分以上的学生学号、姓名、课程号及成绩 5.查询所选课程尚未登记成绩的学生学号、姓名及所在院系 6.査询选修了‘数据库’或者‘数据结构’课程的学生学号、姓名、所在院系、 课程号及成绩;
子查询返回单值时可以用比较运算符，但返回多值时要用 ANY 或 ALL 谓词修饰，而 使用 ANY 或 ALL 谓词时必须同时使用比较运算符。 需要配合使用比较运算符： > ANY 大于子查询结果中的某个值 > ALL 大于子查询结果中的所有值 = ANY 大于等于子查询结果中的某个值 >= ALL 大于等于子查询结果中的所有值 ）ANY 不等于子查询结果中的某个值 !=（或<>）ALL 不等于子查询结果中的任何一个值 4. 集合查询的表示及使用； SELECT 语句的查询结果是元组的集合，所以多个 SELECT 语句的结果可进行集合操 作。集合操作主要包括： （1）并操作 UNION （2）交操作 INTERSECT （3）差操作 EXCEPT 四、实验任务 使用 SQL 语句实现查询操作。 五、实验内容及步骤 以数据库 SQL_STU，数据表 student、course、sc 及表数据为基础（数据表及相关表数 据如附表所示），请使用 SQL 语句实现以下查询操作，并验证查询结果。 （一）连接查询 1. 查询学生的学号、姓名、性别、所选课程的课程号及成绩； 2. 查询课程的课程号、课程名、学分及平均成绩； 3. 查询所有课程被选修的详细情况，包括课程号、课程名、学号、姓名及成绩； 4. 查询成绩在 90 分以上的学生学号、姓名、课程号及成绩； 5. 查询所选课程尚未登记成绩的学生学号、姓名及所在院系； 6. 查询选修了‘数据库’或者‘数据结构’课程的学生学号、姓名、所在院系、 课程号及成绩；
7.查询“数据库”课程成绩的最高分、最低分和平均分; 8.按照学号,姓名,所在院系,总学分的顺序查询学生学分的获得情况。其中 总学分为选修课程中考试及格的课程学分之和; 9.査询只被一名学生选修的课程的课程号、课程名; 10.查询与‘王芳’在一个院系的学生的信息: (二)嵌套查询 1.查询与‘王芳’年龄相同的学生的基本信息; 2.查询所选课程的平均成绩小于“王芳”的平均成绩的学生学号、姓名及平均 成绩 3.查询只选修一门课程的学生的学号、姓名、所在院系及成绩; 4.查询选修了至少一门和“李勇”选修课程一样的学生的学号、姓名及课程号 5.查询所学课程包含“李勇”所学课程的学生学号和姓名; 6.查询选修了“数据结构”课程的学生学号和姓名 7.查询其它院系中年龄小于CS专业某个学生年龄的学生姓名、年龄和院系; 8.查询其他院系中比CS专业所有学生年龄小的学生基本情况; (三)集合查询 请通过集合查询的方式完成以下查询要求,并列出SQL语句。 1.査询只选修“数据库”和“数据结构”两门课程的学生的基本信息; 2.查询至少选修“数据库”或“数据结构”课程的学生的基本信息: 3.查询“IS”专业的学生以及性别为“女”的学生名单 查询“CS”专业的学生与年龄不大于19岁的学生的差集; 5.查询选修“01”号课程的学生集合与选修“05”号课程的学生集合的交集 六、实验分析 WHERE子句和 HAVING子句在具体使用过程中有何不同? 2.使用关键字JON建立的连接查询中,内连接、外连接和交叉连接有何区别 3.使用IN关键字引入的子查询和使用 EXISTS关键字引入的子查询在语法上有何不 同 4.连接査询和子查询一般都要涉及两个或多个表,它们之间有何区别?什么情况下优 先选择连接查询,什么情况下优先选择子查询的方式? 七课外自主实验 无 八、实验扩展资源 1.图书 (1)郑阿奇 SQL Server实用教程(第3版)( SQL Server2008版)[M,电子工业
7. 查询“数据库”课程成绩的最高分、最低分和平均分； 8. 按照学号，姓名，所在院系，总学分的顺序查询学生学分的获得情况。其中 总学分为选修课程中考试及格的课程学分之和； 9. 查询只被一名学生选修的课程的课程号、课程名； 10. 查询与‘王芳’在一个院系的学生的信息； （二）嵌套查询 1. 查询与‘王芳’年龄相同的学生的基本信息； 2. 查询所选课程的平均成绩小于“王芳”的平均成绩的学生学号、姓名及平均 成绩； 3. 查询只选修一门课程的学生的学号、姓名、所在院系及成绩； 4. 查询选修了至少一门和“李勇”选修课程一样的学生的学号、姓名及课程号； 5. 查询所学课程包含“李勇”所学课程的学生学号和姓名； 6. 查询选修了“数据结构”课程的学生学号和姓名； 7. 查询其它院系中年龄小于 CS 专业某个学生年龄的学生姓名、年龄和院系； 8. 查询其他院系中比 CS 专业所有学生年龄小的学生基本情况； （三）集合查询 请通过集合查询的方式完成以下查询要求，并列出 SQL 语句。 1. 查询只选修“数据库”和“数据结构”两门课程的学生的基本信息； 2. 查询至少选修“数据库”或“数据结构”课程的学生的基本信息； 3. 查询“IS”专业的学生以及性别为“女”的学生名单； 4. 查询“CS”专业的学生与年龄不大于 19 岁的学生的差集； 5. 查询选修“01”号课程的学生集合与选修“05”号课程的学生集合的交集； 六、实验分析 1. WHERE 子句和 HAVING 子句在具体使用过程中有何不同？ 2. 使用关键字 JOIN 建立的连接查询中，内连接、外连接和交叉连接有何区别？ 3. 使用 IN 关键字引入的子查询和使用 EXISTS 关键字引入的子查询在语法上有何不 同？ 4. 连接查询和子查询一般都要涉及两个或多个表，它们之间有何区别？什么情况下优 先选择连接查询，什么情况下优先选择子查询的方式？ 七、课外自主实验 无 八、实验扩展资源 1. 图书 （1）郑阿奇.SQL Server 实用教程（第 3 版）（SQL Server 2008 版）[M].电子工业
出版社2009 (2)闪四清 SQL Server2008基础教程[M清华大学出版社2010. 2.在线学习平台 http://database.xg.hactcm.edu.cn/ 3.其他资源 (1)http://msdn.microsoft.com/zh-cn/sqlserver (2)http://bbs.csdn.net/
出版社,2009. （2）闪四清.SQL Server 2008 基础教程 [M]. 清华大学出版社,2010. 2. 在线学习平台 http://database.xg.hactcm.edu.cn/ 3. 其他资源 （1）http://msdn.microsoft.com/zh-cn/sqlserver （2）http://bbs.csdn.net/
附表
附表：