SQL Server(2019) 实验二 单表查询

一、实验目的

1.掌握SELECT语句的基本用法
2.使用WHERE子句进行有条件的查询
3.掌握使用IN和NOT IN，BETWEEN…AND和NOT BETWEEN…AND来缩小查询范围的方法
4.掌握聚集函数的使用方法
5.利用LIKE子句实现字符串匹配查询
6.利用ORDER BY子句对查询结果排序
7.利用GROUP BY子句对查询结果分组

二、实验内容和要求

2.1、表结构修改

2.1.1．在实验三的所建立的数据库中增加Teacher表，结构如下：

Teacher表（教师表）的表结构

字段名称	数据类型	长度	精度	小数位数	是否允许Null值	说明
Tno	Char	3	0	0	否	教师号，主码
Tname	Varchar	8	0	0	是	教师名
Tsex	Char	2	0	0	是	性别，取值：男或女
Tbirthday	Date	8	0	0	是	出生日期
Tdept	Char	16	0	0	是	所在系

2.1.2．在实验三的所建立的数据库中增加Teaching表，表结构如下：

Teaching表（授课表）的表结构

Cno	Char	5	0	0	否	课程号，外码
Tno	Char	3	0	0	否	教师号，外码
Cterm	Tinyint	1	0	0	是	1-8之间，开课学期

2.1.3．向上述两表中输入如下数据记录

教师表Teacher

Tno	Tname	Tsex	Tbirthday	Tdept
101	李新	男	1977-01-12	CS
102	钱军	女	1968-06-04	CS
201	王小花	女	1979-12-23	IS
202	张小青	男	1968-08-25	IS

授课表Teaching

Cno	Tno	Cterm
C01	101	2
C02	102	1
C03	201	3
C04	202	4

2.2、完成下面查询

1．查询所有男生信息
2．查询年龄大于24岁的女生学号和姓名
3．查询所有教师的Tname、Tdept
4．查询“电子商务”专业的学生姓名、性别和出生日期
5．查询成绩低于90分的学生学号及课号，并按成绩降序排列
6．查询Student表中所有的系名
7．查询“C01”课程的开课学期
8．查询成绩在80分至90之间的学生学号及课号
9．统计有学生选修的课程门数
10．查询成绩为77，88或99的记录
11．计算“C02”课程的平均成绩
12．输出有成绩的学生学号
13．查询所有姓“刘”的学生信息
14．统计输出各系学生的人数
15．查询选修了“C03”课程和学生的学号及其成绩，查询结果按分数的降序排列
16．查询各个课程号及相应的选课人数，并为选课人数取别名为“人数”
17．统计每门课程的选课人数和最高分，并为选课人数和最高分分别取别名为“人数”、“最高分”
18．统计每个学生的选课门数和考试总成绩，并为选课门数和总成绩分别取别名为“门数”、“总成绩”，并按选课门数降序排列。

三、实操

3.1表结构的修改

内容简单，如有疑问参照 实验一。(注意设置主键、外键、属性取值约束）

3.2相关查询

先回顾一下我们的5个表：

建立查询的方法：在数据库上右键，选择“新建查询”，在出来的界面使用相应的语句进行查询即可。

（注：下面的18个查询是我的实验要求的，并非按照难度和复杂程度排序。）

3.2.1．查询所有男生信息

select * 
from Student
where Ssex ='男';

3.2.2．查询年龄大于24岁的女生学号和姓名

select Sno,Sname 
from Student
where Ssex ='女' and (year(getdate())-year(Sbirthday))>24;

3.2.3．查询所有教师的Tname、Tdept

select Tname,Tdept 
from Teacher;

3.2.4．查询“电子商务”专业的学生姓名、性别和出生日期

select Sname,Ssex,Sbirthday 
from Student
where Speciality='电子商务';

3.2.5．查询成绩低于90分的学生学号及课号，并按成绩降序排列

select  Sno,Cno
from SC
where Degree<90
order by Degree desc;

注：这里没有使用distinct关键字，显示的结果是符合条件的所有学生的所有课程成绩的降序排列。升序使用asc代替desc即可。

3.2.6．查询Student表中所有的系名

select  distinct Speciality
from Student;

注：这里使用了distinct，所有系名只显示一次。

3.2.7．查询“C01”课程的开课学期

select  Cterm
from Teaching
where Cno='C01';

3.2.8．查询成绩在80分至90之间的学生学号及课号

第一种：使用between and 语句，包含了80与90分

select  Sno,Cno
from SC
where Degree between 80 and 90;

第一种：使用大于小于，不包含80与90分

select  Sno,Cno
from SC
where Degree >80  and Degree <90;

3.2.9．统计有学生选修的课程门数

select  COUNT(distinct Cno)
from SC;

3.2.10．查询成绩为77，88或99的记录

select  *
from SC
where Degree='77' or Degree='88' or Degree='99';

3.2.11．计算“C02”课程的平均成绩

select  AVG(Degree)
from SC
where Cno='C02';

3.2.12．输出有成绩的学生学号

select  distinct Sno
from SC
where Degree is not null;

3.2.13．查询所有姓“刘”的学生信息

select  *
from Student
where Sname like '刘%';

3.2.14．统计输出各系学生的人数

select  distinct Sdept,COUNT(Sdept) as '人数'
from Student
group by Sdept;

注：注意distinct和as的使用技巧。

3.2.15．查询选修了“C03”课程的学生的学号及其成绩，查询结果按分数的降序排列

select  Sno,Degree
from SC
where Cno='C03'
order by Degree desc;

3.2.16．查询各个课程号及相应的选课人数，并为选课人数取别名为“人数”

与14题一样的操作。

select  distinct Cno,COUNT(Cno) as '人数'
from SC 
group by Cno;

3.2.17．统计每门课程的选课人数和最高分，并为选课人数和最高分分别取别名为“人数”、“最高分”

select  distinct Cno,COUNT(Cno) as '人数',MAX(distinct Degree) as '最高分'
from SC 
group by Cno;

3.2.18．统计每个学生的选课门数和考试总成绩，并为选课门数和总成绩分别取别名为“门数”、“总成绩”，并按选课门数降序排列。

select  distinct Sno,COUNT(Cno) as '门数',SUM(Degree) as '总成绩'
from SC 
group by Sno
order by COUNT(Cno) desc;