数据库的查询(二）

实验题目：数据库的查询(二）

一、实验目的

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）查询各个课程号与相应的选课人数；

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

五、小结

     本次实验是对数据库中的数据进行查询、统计、分组、排序等操作，其中使用聚合查询时，容易错误使用查询语句导致出错，而连接查询的思想虽然较为简单，但是在实际的操作时却由于复杂的语句从而导致出错，尤其要注意的是要标明所涉及的列名在哪个表中，否则容易报错。在统计查询结果的个数时，要注意使用count来统计总数，而排序分为升序和降序，这一点操作起来较为容易。随着查询语句和思想的复杂程度越来越高，需要多练习相关的知识，把课本上的内容实践化，这样才能真正的理解和应用。

实验题目：数据库的查询(二）

一、实验目的

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

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

二、 实验内容与步骤

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

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

代码：select cno,count(sno)

from dbo.xs_kc

group by cno;

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

  

代码:select dbo.xs.sno,sname,dbo.kc.cno,cname

from dbo.xs_kc,dbo.kc,dbo.xs

where dbo.xs.sno=dbo.xs_kc.sno and dbo.kc.cno=dbo.xs_kc.cno;

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

代码：select dbo.xs.*

from dbo.xs_kc,dbo.xs

where dbo.xs.sno=dbo.xs_kc.sno and dbo.xs_kc.cno='102' and dbo.xs_kc.grade>90;

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

代码:select dbo.xs.sno,sname,dbo.kc.cname,grade

from dbo.xs_kc,dbo.xs,dbo.kc

where  dbo.xs.sno=dbo.xs_kc.sno and dbo.kc.cno=dbo.xs_kc.cno;

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

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

代码:select dbo.xs.sno,sname

from dbo.xs_kc,dbo.xs,dbo.kc

where  dbo.xs.sno=dbo.xs_kc.sno and cname like '离散数学'and dbo.kc.cno=dbo.xs_kc.cno;

1. 求102号课程的成绩高于王林的学生学号和成绩。

 

代码：select dbo.xs.sno,grade

from dbo.xs_kc,dbo.xs

where  dbo.xs.sno=dbo.xs_kc.sno and cno='102' and grade>(

select grade

from dbo.xs_kc,dbo.xs

where cno='102' and sname like '王林' and dbo.xs.sno=dbo.xs_kc.sno);

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

代码：select *

from dbo.xs

where sbirth<(

select max(sbirth)

from dbo.xs

where  sdept='is');

1. 求选修了102号课程的学生姓名。

代码：select sname

from dbo.xs,dbo.xs_kc

where dbo.xs.sno=dbo.xs_kc.sno and dbo.xs_kc.cno='102';

1. 求没有选修102号课程的学生姓名。

代码：select sname

from dbo.xs

where sname not in(

select sname

from dbo.xs,dbo.xs_kc

where dbo.xs.sno=dbo.xs_kc.sno and dbo.xs_kc.cno='102');

1. 统计离散数学课程成绩在85分以上的人数。

代码：select count(sname)

from dbo.xs

where sname in (

select sname

from dbo.xs,dbo.xs_kc,dbo.kc

where dbo.xs.sno=dbo.xs_kc.sno and dbo.kc.cno=dbo.xs_kc.cno and dbo.kc.cname='离散数学' and grade>85);

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

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

代码：select sname

from dbo.xs

where sdept='is' and sbirth<=23;

1. 查询选修了101号课程或者选修了102号课程的学生的姓名。

     

代码:select distinct(sname)

from dbo.xs,dbo.xs_kc

where dbo.xs.sno=dbo.xs_kc.sno and(cno='101'or cno='102');

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

代码:select sno,avg(grade)

from dbo.xs_kc

group by sno

having  avg(grade)>85;

1. 查询xscj数据库中各专业的学生数。

代码:select sdept,count(sname)

from dbo.xs

group by sdept;

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

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

1.（1）代码：

select cno,count(sno)

from dbo.xs_kc

group by cno;

1.（2）代码：

select dbo.xs.sno,sname,dbo.kc.cno,cname

from dbo.xs_kc,dbo.kc,dbo.xs

where dbo.xs.sno=dbo.xs_kc.sno and dbo.kc.cno=dbo.xs_kc.cno;

1.（3）代码：

select dbo.xs.*

from dbo.xs_kc,dbo.xs

where dbo.xs.sno=dbo.xs_kc.sno and dbo.xs_kc.cno='102' and dbo.xs_kc.grade>90;

1.（4）代码：

select dbo.xs.sno,sname,dbo.kc.cname,grade

from dbo.xs_kc,dbo.xs,dbo.kc

where  dbo.xs.sno=dbo.xs_kc.sno and dbo.kc.cno=dbo.xs_kc.cno;

2.（1）代码：

select dbo.xs.sno,sname

from dbo.xs_kc,dbo.xs,dbo.kc

where  dbo.xs.sno=dbo.xs_kc.sno and cname like '离散数学'and dbo.kc.cno=dbo.xs_kc.cno;

2.（2）代码：

select dbo.xs.sno,grade

from dbo.xs_kc,dbo.xs

where  dbo.xs.sno=dbo.xs_kc.sno and cno='102' and grade>(

select grade

from dbo.xs_kc,dbo.xs

where cno='102' and sname like '王林' and dbo.xs.sno=dbo.xs_kc.sno);

2.（3）代码：

select *

from dbo.xs

where sbirth<(

select max(sbirth)

from dbo.xs

where  sdept='is');

2.（4）代码：

select sname

from dbo.xs,dbo.xs_kc

where dbo.xs.sno=dbo.xs_kc.sno and dbo.xs_kc.cno='102';

2.（5）代码：

select sname

from dbo.xs

where sname not in(

select sname

from dbo.xs,dbo.xs_kc

where dbo.xs.sno=dbo.xs_kc.sno and dbo.xs_kc.cno='102');

2.（6）代码：

select count(sname)

from dbo.xs

where sname in (

select sname

from dbo.xs,dbo.xs_kc,dbo.kc

where dbo.xs.sno=dbo.xs_kc.sno and dbo.kc.cno=dbo.xs_kc.cno and dbo.kc.cname='离散数学' and grade>85);

3.（1）代码：

select sname

from dbo.xs

where sdept='is' and sbirth<=23;

3.（2）代码：

select distinct(sname)

from dbo.xs,dbo.xs_kc

where dbo.xs.sno=dbo.xs_kc.sno and(cno='101'or cno='102');

3.（3）代码：

select sno,avg(grade)

from dbo.xs_kc

group by sno

having  avg(grade)>85;

3.（4）代码：

select sdept,count(sname)

from dbo.xs

group by sdept;

四、思考题

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

答：在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。当获得一个查询的答案需要多个步骤的操作，首先必须创建一个查询来确定用户不知道但包含在数据库中的值，将一个查询块嵌套在另一个查询块的WHERE字句或HAVING短语的条件中查询块称为子查询。有谓词in、any、all、exists等。

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

答：若一个查询同时涉及两个以上的表，则称之为连接查询。连接查询是关系数据库中最主要的查询，包括等值连接查询、自然连接查询、非等值连接查询、自身连接查询、外连接查询和复合条件连接查询等。

五、实验过程中遇到的问题及解决方法

    本次实验在统计学生的人数时，误用了sum导致出错，应使用count；连接语句时要注意用and而不是逗号；在完成“求没有选修102号课程的学生姓名”这个查询时，可以先找出选修了这门课程的学生姓名，再使用嵌套查询完成任务。再使用group by时，最初写的是where…group by发生了错误，因为where子句中是不能用聚集函数作为条件表达式的，所以更改为了group by…having。这次的查询语句相对复杂一些，还需要多上机实验从而达到熟能生巧。