数据库SQL Server实验报告 之 SQL语言进行复杂查询（4/8）_1. 查找cs系所有女生的信息; n 2. 列出所有不姓王的学生的学号和姓名;

 SQL语言进行复杂查询

注

意

原版word在下载

资源里面

今日

感想

历经千万种悲伤

然后从悲伤种走出来

实验目的及要求：

1. 掌握SELECT语句的基本语法和查询条件表示方法；
2. 掌握查询条件表达式和使用方法；
3. 掌握GROUP BY 子句的作用和使用方法；
4. 掌握HAVING子句的作用和使用方法；
5. 掌握ORDER BY子句的作用和使用方法。
6. 了解数据库查询；
7. 了解数据库查询的实现方式；
8. 完成实验报告；

实验内容及步骤：

以XSGL数据库中数据为基础，请使用T-SQL 语句实现以下操作：

1. 列出所有不姓刘的所有学生；

select sname,sno--查询学生即查询学生姓名和学号

from student

where sname not like'刘%';--利用not like''语句筛选条件

1. 列出姓“张”且全名为3个汉字的学生；

select sname

from student

where sname like'张__'and len(sname)=3;--“len（）=”语句用来限制sname的长度

1. 显示在1993年以后出生的学生的基本信息；

select sname,sno,sage,year(getdate())-sage yearsage--后面空一格表示给该列属性起的名字

from student

where (year(getdate())-sage)>1993;--year(getdate())表示当前年份

1. 查询出课程名含有“数据”字串的所有课程基本信息；

select course.*,sno,grade--课程表和选课表包含课程的所有信息

from course,sc

where course.cno=sc.cno--课程表的cno是其主码，需要连接sc表中的cno外码

and cname like '数据%'

1. 列出选修了‘1’课程的学生，按成绩的降序排列；

select sname,student.sno,grade--sno是student中的主码，grade在选课表中所以需要连接sc表中的sno外码

from student,sc--查询学生一般是指查询姓名学号

where cno='1'

order by grade desc--order by   对某属性进行排序，desc为倒序，acs为正序

1. 列出同时选修“1”号课程和“2”号课程的所有学生的学号；

select sno--课程号和学号在sc表中均有，所以不需要连接表

from sc

where cno='1'and--cno不能同时为1和2，所以再嵌套一个选了2号课程的语句

sno in(select sno from sc where cno='2');

1. 列出课程表中全部信息，按先修课的升序排列；

select *

from course

order by cpno asc;--asc代表升序，也可以省略

1. 列出年龄超过平均值的所有学生名单，按年龄的降序显示；

select sname,sno,sage

from student

where sage>(select avg(sage) from student)--where关键词后面不能跟聚合函数，所以嵌套一个表示平均值

order by sage desc;

1. 按照出生年份升序显示所有学生的学号、姓名、性别、出生年份及院系，在结果集中列标题分别指定为“学号，姓名，性别，出生年份，院系”；

select sno 学号,sname 姓名,ssex 性别,

year(getdate())-sage 出生年份,sdept 院系

from student

order by year(getdate())-sage;

1. 按照课程号、成绩降序显示课程成绩在70-80之间的学生的学号、课程号及成绩；

select student.sno,cno,grade

from student,sc

where student.sno=sc.sno and

grade>=70 and grade<80

order by cno,grade--两者顺序不能颠倒，不然结果会不一样

1. 显示学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为“学生总人数，平均年龄”

select count(sno) 学生总人数,--count表示计算行数，sno为学生表的主码，即唯一，所以计算学号个数

avg(sage) 平均年龄

from student

1. 显示选修的课程数大于3的各个学生的选修课程数

select sno,count(sno)--只要求显示选修课程数，需要计算一个学号的行

from sc

group by sno--需要计算一个学号的行，对学号分组，则求得每个学生的选课数

having count(sno)>3;--不能用where，因为那是分组前的限制，分组前本来行就大于3所以起不到限制的作用，意思就是说一般group by和having连用

1. 按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩；

select cno 课程号,count(sno) 课程总人数,

max(grade) 最高成绩,min(grade) 最低成绩,

avg(grade) 平均成绩

from sc

group by cno--按照课程号进行，即对每一个课程进行统计，需要分组

order by cno desc;

1. 显示各个院系男女生人数，其中在结果集中列标题分别指定为“院系名称、男生人数、女生人数”；（已给出答案）

select sdept 院系名称,count(case when ssex='男'then 1 end) 男生人数,--case when..then 1 end这里的1仅仅代表记号

count(case when ssex='女'then 1  end) 女生人数--当ssex=女时count加1，如果含有else 0 则无论男女count均加1造成女生数量与总人数相同

from student

group by sdept;--各个院系，则需要对院系分组然后计算男女人数

1. 按照“性别、学号、姓名、年龄、院系”的顺序列出学生信息，其中性别按以下规定显示：性别为男显示为男 生，性别为女显示为女 生，其他显示为“条件不明”；

select case when ssex='男' then '男生' when ssex='女' then '女生'

else '条件不明' end 性别,--case when ..then..when..then..else..end

sno 学号,sname 姓名,sage 年龄,sdept 院系

from student

1. 显示学号第八位或者第九位是1、2、3、4或者9的学生的学号、姓名、性别、年龄及院系；

select sno,sname,ssex,sage,sdept

from student--[..]表示取框内任意一个

where sno like '_______[1,2,3,4,9]_' or sno like '________[1,2,3,4,9]';

1. 按照院系降序显示所有学生的 “院系，学号、姓名、性别、年龄”等信息，其中院系按照以下规定显示：院系为CS显示为计算机系，院系为IS显示为信息系，院系为MA显示为数学系，院系为EN显示为外语系，院系为CM显示为中医系，院系为WM显示为西医系，其他显示为院系不明；

select case when sdept='CS' then '计算机系'

when sdept='IS' then '信息系' when sdept='MA' then '数学系'

when sdept='EN' then '外语系 'when sdept='CM' then '中医系'

when sdept='WM' then '西医系'

else '院系不明' end

 sno,sname,ssex,sage

from student

order by sdept desc

1. 显示所有院系（要求不能重复，不包括空值），并在结果集中增加一列字段“院系规模”，其中若该院系人数>=5则该字段值为“规模很大”，若该院系人数大于等于4小于5则该字段值为“规模一般”， 若该院系人数大于等于2小于4则该字段值为“规模稍小”，否则显示“规模很小”；

select sdept 院系,case when count(sno)>=5 then '规模一般'

when count(sno)>=4 then '规模一般'

when count(sno)>=3 then '规模稍小'

else '规模很小' end--when 之间没有，且主义字符串要加''

from student

where sdept is not null

group by sdept;--必须对院系进行分组

1. 显示平均成绩大于“200515001”学生平均成绩的各个学生的学号、平均成绩。

select sno,avg(grade) 平均成绩

from sc

group by sno--平均成绩需要分组确定每个学生

having avg(grade)>(select avg(grade) --分组后的限制语句having一般用聚合函数，聚合函数比较可以用嵌套表示另外一个聚合函数

from sc where sno='200515001')

1. 列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩；

select sno,avg(grade) 平均成绩

from sc--不能先用where限制grade<60 否则无法计算>60的成绩

group by sno--必须对学号分组区分不同的学生

having count(case when grade<=60 then 1 end)>=2