2022年实验二SQL数据查询 .pdf
实验二 SQL 数据查询 一、 实验目的: 熟练掌握基本表的定义、删除与修改，为后继学习作准备。 二、实验属性（验证性） 1. 了解Microsoft SQL Server 2005并掌握 SQL查询分析器的使用; 2. 掌握基本表的定义、删除与修改。 三、实验仪器设备及器材1. 电脑。 2. Microsoft SQL Server 2005。 四、实验要求(预习、实验前、实验中、实验后等要求) 1预习教材第三章，熟悉SQL语句。 2熟悉Microsoft SQL Server 2005 开发环境。 3能够熟练掌握Microsoft SQL Server 2005环境下的数据库的编程。 4掌握单表查询语句的一般格式。; 5掌握单表无条件、有条件查询及查询结果排序与分组。 6掌握多表连接、嵌套和集合查询语句的一般格式及各种使用方法。 五、 实验原理 SQL语言应用。 六、实验步骤： (1) 启动 SQLSERVER 2005 (2) 打开查询分析器 (点击工具栏上的新建) (3) 验证如下例子：一、单表查询1 无条件查询例 2.1 查询全体学生的详细记录。这是一个无条件的选择查询，其命令为：SELECT * /* 这里的“ *”等价于 ALL*/ FROM Students; 其结果为表3.3 中的全部数据。例 2.2查询全体学生的姓名(Sname)、学号 (Sno)、所在系 (Sdept)。这是一个无条件的投影查询，其命令为：SELECT Sname, Sno, Sdept FROM Students; 例 2.3查询全体学生的姓名(Sname)、出生年份及学号(Sno)。由于SELECT 子句的 不仅可以是表中的属性列，也可以是表达式，故可以查询经过计算的值。其命令为：SELECT Sno, Sname, 2001-Sage FROM Students; 例 2.4 查询全体学生的姓名、出生年份和学号，要求用小写字母表示学号中的字母。其命令为：SELECT Sname, Birth: Title, 1996-Sage BirthYear, LOWER(Sno) Lsno FROM Students; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 7 页 - - - - - - - - - 例 2.5查询选修了课程的学生学号。其命令为：SELECT DISTINCT Sno FROM Reports; 2 条件查询例 2.6 查询数学系全体学生的学号(Sno)和姓名(Sname)。其命令为：SELECT Sno, Sname FROM Students WHERE Sdept=数学 ; 例 2.7 查询所有年龄在1822 岁(包括 18 岁和 22 岁)之间的学生姓名(Sname)及年龄 (Sage)。其命令为：SELECT Sname, Sage FROM Students WHERE Sage=18 AND Sage二、连接查询1 不同表之间的连接查询例 2.27查询每个学生及其选修课程的情况。本查询实际上是涉及Students 与 Reports 两个表的连接操作。 这两个表之间的联系是通过公共属性Sno实现的，因此，其操作命令为：SELECT Students.*, Reports.* FROM Students, Reports WHERE Students.Sno = Reports.Sno；说明说明： ：若在以上等值连接中把目标列中重复的属性列去掉则为自然连接，其命令为SELECT Students.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM Students, Reports WHERE Students.Sno= Reports.Sno; 例 2.28 查询每个学生的学号(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 自身连接例 2.29 查询每一门课的间接先修课(即先修课的先修课)。在 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 外连接例 2.30 把例 3.37 中的等值连接改为左连接。该左连接操作在SQL Server 2000 中的命令格式为：SELECT Students.Sno, Sname, Ssex, Sdept, Cno, Grade 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 7 页 - - - - - - - - - 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 的嵌套查询例 2.31 查询选修了编号为“C02”的课程的学生姓名(Sname)和所在系 (Sdept)。SELECT Sname, Sdept FROM Students WHERE Sno IN (SELECT Sno FROM Reports WHERE Cno=C02); 例 2.32 查询与“李伟”在同一个系学习的学生学号(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=李伟 ; 例 2.33查询选修了课程名为“数据结构”的学生学号(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 号*/ 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 7 页 - - - - - - - - - 说明说明： ：本查询同样可以用连接查询实现：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 带有比较运算符的嵌套查询例 2.34 将例 3.42 改为带有比较运算符的嵌套查询。由于一个学生只可能在一个系学习，因此子查询的结果是一个值，因此可以用=代替 IN，其 SQL 语句如下：SELECT Sno , Sname, Sdept FROM Students WHERE Sdept = (SELECT Sdept FROM Students WHERE Sname= 李伟 ); 3 带谓词ANY 或 ALL 的嵌套查询例 2.35查询非自动化系的不超过自动化系所有学生的年龄的学生姓名(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 的嵌套查询例 2.36查询所有选修了编号为“C01”课程的学生姓名(Sname)和所在系 (Sdept)。本查询的SQL 语句是：SELECT Sname, Sdept FROM Students WHERE EXISTS (SELECT * FROM Reports WHERE Sno=Students.Sno AND Cno=C01); 例 2.37 将例 3.42 改为带谓词EXISTS 的查询，其SQL 语句如下SELECT Sno, Sname, Sdept FROM Students A 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 7 页 - - - - - - - - - WHERE EXISTS (SELECT * FROM Students B WHERE B.Sdept=A.Sdept AND B.Sname=李伟 ); 例 2.38 查询选修了所有课程的学生姓名(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); 四、集合查询例 2.39 查询计算机 科学系的学生或年龄不大于 20 岁的学生信息。SELECT * FROM Students WHERE Sdept=计算机 UNION SELECT * FROM Students WHERE Sage=20; 例 2.40 查询数学系的学生且年龄不大于 20 岁的学生的 交集，这实际上就是查询数学系中年龄不大于 20 岁的学生。SELECT * FROM Students WHERE Sdept=数学 AND Sage20; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 7 页 - - - - - - - - -
