一、实验目的

1．熟悉SQL Server数据库中的数据查询、统计、分组、排序等操作。

2．掌握使用查询分析器对数据进行简单查询、连接查询、嵌套查询和组合查询。

二、实验仪器

计算机、SQL Server 2019软件，U盘（学生自备）。

三、实验原理

1.启动数据库服务软件SQL Server 2019的查询分析器，用SELECT语句对表进行简单查询操作，整个查询过程只涉及到一个表，是最基本的查询语句。

2.用SELECT语句对表进行连接查询操作，连接查询涉及被连接和连接两个表，所以数据源一般为多个表。

3.用SELECT语句对表进行嵌套查询操作，一个Select…From…Where语句称为一个查询块，将一个查询块嵌套在另一个查询块的Where子句或Having短语的条件中的查询，就是嵌套查询。主要有使用In操作符、使用比较运算符的嵌套查询方式。

4.用SELECT语句对表进行组合查询操作，将SELECT语句的查询结果集再进行集合运算就构成了SQL的组合查询。组合查询操作符有Union(并操作）、Intersect(交操作）和Minus(差操作）。

四、 实验内容与步骤

各种表展示：

 

 

1.在查询分析器窗口下用SELECT语句完成连接（嵌套）查询：

（1）查询各个课程号与相应的选课人数；

代码：SELECT cno,COUNT(cno)AS number FROM xs_kc GROUP BY cno ;

试验结果：

 

（2）查询每个学生的学号，姓名及其选修课程的情况包括课程号和课程名；

代码：SELECT xs.sno,xs.sname,xs_kc.cno,cname FROM xs_kc,xs,kc WHERE xs.sno=xs_kc.sno;

实验结果：

 

（3）查询选修102号课程且成绩在75分以上的所有学生的情况；

代码：SELECT * FROM xs,xs_kc WHERE xs_kc.cno=102 AND xs_kc.grade>75 AND xs_kc.sno=xs.sno ;

实验结果：

 

（4）查询每个学生的学号、姓名及其选修的课程名及成绩；

代码：SELECT xs.sno,xs.sname,kc.cname,xs_kc.grade FROM kc,xs_kc,xs WHERE xs.sno=xs_kc.sno AND

xs_kc.cno=kc.cno;

实验结果：

 

2.在查询分析器窗口下用SELECT语句完成嵌套查询：

（1）求选修了离散数学的学生学号和姓名。

代码：SELECT xs.sno,xs.sname FROM xs,xs_kc,kc WHERE xs.sno=xs_kc.sno AND kc.cno=xs_kc.cno AND kc.cname='离散数学';

实验结果：

 

（2）求102号课程的成绩高于王林的学生学号和成绩。

代码：SELECT xs_kc.sno,xs_kc.grade FROM xs_kc WHERE xs_kc.cno=102 AND xs_kc.grade>(SELECT grade FROM xs_kc,xs WHERE xs.sname='王林'AND xs_kc.sno=xs.sno AND xs_kc.cno=102) ;

实验结果：

 

（3）求其他系中年龄小于计算机科学系年龄最大者的学生情况。

代码：SELECT*FROM xs WHERE xs.sdept <> 'IS'AND xs.sbirth<(SELECT MAX(xs.sbirth) FROM xs WHERE xs.sdept='IS');

实验结果：

 

（4）求选修了102号课程的学生姓名。

代码：SELECT xs.sname FROM xs,xs_kc WHERE xs.sno=xs_kc.sno AND xs_kc.cno=102;

实验结果：

 

（5）求没有选修102号课程的学生姓名。

代码：SELECT xs.sname FROM xs,xs_kc WHERE xs.sno=xs_kc.sno AND xs_kc.cno<>102;

实验结果：

 

（6）统计离散数学课程成绩在85分以上的人数。

代码：SELECT COUNT(*)AS number FROM xs_kc,kc WHERE xs_kc.cno=kc.cno AND kc.cname='离散数学'AND xs_kc.grade>85;

实验结果：

 

3.在查询分析器的窗口下用SELECT语句完成集合查询：

（1）查询计算机科学系的年龄不大于23岁的学生的姓名。

代码：SELECT xs.sname FROM xs WHERE xs.sdept='IS' AND xs.sbirth <=23;

实验结果：

 

（2）查询选修了101号课程或者选修了102号课程的学生的姓名。

代码：SELECT xs.sname FROM xs,xs_kc WHERE xs_kc.cno IN(102,101) AND xs.sno=xs_kc.sno;

实验结果：

 

（3）查询xscj数据库中平均成绩在85分以上的学生的学号和平均成绩。

代码：SELECT xs_kc.sno,AVG(xs_kc.grade) AS average FROM xs_kc  GROUP BY sno HAVING average>85;

实验结果：

 

（4）查询xscj数据库中各专业的学生数。

代码：SELECT xs.sdept,COUNT(*) FROM xs GROUP BY xs.sdept;

实验结果：

 

五、实验数据的记录及处理

以上操作内容使用SQL语句编程并运行成功后，将程序进行记录，并写在报告纸上。

六、思考题

1.什么是子查询？有哪些谓词？

答：子查询是一种常用计算机语言SELECT-SQL语言中嵌套查询下层的程序模块。当一个查询是另一个查询的条件时，称之为子查询。子查询是本质上就是一个完整 的SELECT 语句，它可以使一个 SELECT、SELECT...INTO 语句、INSERT...INTO 语句、DELETE 语句、或 UPDATE 语句或嵌套在另一子查询中。子查询的输出可以包括一个单独的值（单行子查询）、几行值（多行子查询）、或者多列数据（多列子查询）。

IN子查询

在嵌套查询中，子查询的结构往往是一个集合，所以谓词 IN是嵌套查询中最经常使用的谓词。

比较运算符

带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户确切知道内层查询返回单个值时，可以用>、<、=、>=、<=、!=或<>等比较运算符。

ANY子查询

子查询返回单值可以用比较运算符，但返回多值时要用ANY（有的系统用SOME）或ALL谓词修饰符。而使用ANY或ALL谓词的时候必须同时使用比较运算符。

EXISTS子查询

在 true/false 比较中使用 EXISTS 谓词（与可选的 NOT 保留字一道）来决定子查询是否会返回任何记录。  EXISTS代表存在量词ヨ，带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值"false"。

2.什么是连接查询？在T-SQL语言中连接查询有哪几类表示形式？

答：连接查询是关系数据库中最主要的查询，主要包括内连接、外连接和交叉连接等。通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，也是它区别于其它类型数据库管理系统的一个标志。 在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当检索数据时，通过连接操作查询出存放在多个表中的不同实体的信息。连接操作给用户带来很大的灵活性，他们可以在任何时候增加新的数据类型。为不同实体创建新的表，然后通过连接进行查询。

    在T_SQL语言中,连接查询有两大类表示形式,一是符合SQL标准连接谓词表示形式,一是 T_SQL扩展的使用关键字JOIN的表示形式。连接条件可通过以下方法定义两个表在查询中的关联方式:指定每个表中要用于连接的列。典型的连接条件在一个表中指定外键,在另一个表中指定与其关联的键。

七、试验过程中遇到的问题及解决方法

答：SELECT是在编写时在编辑器中执行的，但数据库并不是这样，所以SQL查询其实并不是从SELECT开始的。数据库是从FROM和JOIN开始执行查询的。这也就解释了为什么我们可以用WHERE来使用已经JOIN过的表格。那为什么我们不能过滤掉WHERE中的GROUP BY呢？这是因为，GROUP BY是在WHERE之后执行的，所以我们还需要下一步：HAVING。

最后，运行SELECT。它选择了要包括的列，定义了要计算的聚合函数。另外，Window Functions也在这里执行。这就是为什么当我们尝试在WHERE中使用Window Function的输出进行过滤时，会出现错误。

请注意，数据库是利用查询优化器（query optimizer）来优化查询执行的。优化器可能会改变某些操作的顺序，让查询运行更快。但这是一些幕后的高级概念。

本次试验中所用到的SQL代码如下：

-- SELECT cno,COUNT(cno)AS number FROM xs_kc GROUP BY cno ;

-- SELECT*FROM xs_kc;

-- SELECT xs.sno,xs.sname,xs_kc.cno,cname FROM xs_kc,xs,kc WHERE xs.sno=xs_kc.sno;

-- SELECT * FROM xs,xs_kc WHERE xs_kc.cno=102 AND xs_kc.grade>75 AND xs_kc.sno=xs.sno ;

-- SELECT xs.sno,xs.sname,kc.cname,xs_kc.grade FROM kc,xs_kc,xs WHERE xs.sno=xs_kc.sno AND -- xs_kc.cno=kc.cno;

-- SELECT xs.sno,xs.sname FROM xs,xs_kc,kc WHERE xs.sno=xs_kc.sno AND kc.cno=xs_kc.cno AND kc.cname='离散数学';

-- SELECT xs_kc.sno,xs_kc.grade FROM xs_kc WHERE xs_kc.cno=102 AND xs_kc.grade>(SELECT grade FROM xs_kc,xs WHERE xs.sname='王林'AND xs_kc.sno=xs.sno AND xs_kc.cno=102) ;

-- SELECT*FROM xs WHERE xs.sdept <> 'IS'AND xs.sbirth<(SELECT MAX(xs.sbirth) FROM xs WHERE xs.sdept='IS');

-- SELECT xs.sname FROM xs,xs_kc WHERE xs.sno=xs_kc.sno AND xs_kc.cno<>102;

-- SELECT COUNT(*)AS number FROM xs_kc,kc WHERE xs_kc.cno=kc.cno AND kc.cname='离散数学'AND xs_kc.grade>85;

-- SELECT xs.sname FROM xs WHERE xs.sdept='IS' AND xs.sbirth <=23;

-- SELECT xs.sname FROM xs,xs_kc WHERE xs_kc.cno IN(102,101) AND xs.sno=xs_kc.sno;

-- SELECT xs_kc.sno,AVG(xs_kc.grade) AS average FROM xs_kc  GROUP BY sno HAVING average>85;