数据库系统—数据查询

针对数据库设计各种单表查询SQL语句、分组统计查询语句；设计单个表针对自身的连接查询，设计多个表的连接查询。理解和掌握SQL查询语句的特点和作用，按照SQL程序设计规范写出具体的SQL查询语句，并调试通过。

利用P45-46页的Student、Course和SC表。用SQL语句完成下面的操作，并调试通过。
Student表

Course表

Sc表

初始化测试数据
Student表

Course

Sc

（1）查询学生选课表中的全部数据。

select * from sc;

（2）查询计算机系的学生的姓名、年龄。

SELECT Sname, Sage FROM Student WHERE Sdept = 'CS'

（3）查询成绩在70~80分之间的学生的学号、课程号和成绩。 SELECT sno,cno,grade from sc

where grade between 70 and 80					

（4）查询计算机系年龄在18~20之间且性别为“男”的学生的姓名、年龄。

SELECT sname,Sage from student where Sage between 18 and 20 and Ssex='男';

（5）查询课程号为“c001”的课程的最高的分数。

SELECT TOP 1 grade  from	SC   WHERE  cno='c001';

（6）查询计算机系学生的最大年龄和最小年龄。

SELECT MAX(Sage) '最大年龄', MIN(Sage) '最小年龄' from STUDENT

（7）统计每个系的学生人数。

SElECT Sdept ,count(sno) as '学生人数' from STUDENT GROUP BY Sdept

（8）统计每门课程的选课人数和考试最高分。

SElECT count(sno) as '选课人数' , MAX(grade) as '最高分' from SC GROUP BY cno;

（9）统计每个学生的选课门数和考试总成绩，并按选课门数升序显示结果。

SELECT student.sno '学号',student.sname '姓名',count(sc.sno) '课程数量',sum(grade) '总成绩'
FROM Sc 
JOIN Student ON Student.sno = SC.sno
GROUP BY Sc.Sno, Student.Sname, Student.Sno
ORDER BY count(cno) 

（10）查询总成绩超过200分的学生，要求列出学号、总成绩。

select sno '学号',sum(grade) '总成绩' from sc
GROUP BY  sno
Having sum(grade)>200;

注： 测试数据中没有成绩大于200的学生
（11）查询选修了“c002”号课程的学生的姓名和所在系。

SELECT student.sname , student.sdept 
from student 
JOIN sc ON student.sno = sc.sno
WHERE sc.cno = 'c002';

（12）查询成绩80分以上的学生的姓名、课程号和成绩，并按成绩降序排列结果。

SELECT Student.sname , Sc.cno,Sc.grade 
from Student , Sc
WHERE Student.sno = Sc.sno and grade>80
ORDER BY grade desc

（13）查询哪些学生没有选课，要求列出学号、姓名和所在系。
根据LEFT JOIN 的性质，加上where的判断，可以筛选出A表中没有与B表关联的数据。如图所示

SELECT S.sno,S.sname,S.Sdept
From Student S
LEFT JOIN Sc
ON S.sno=Sc.sno
WHERE Sc.sno is null

右表为sc表可见25号学生没有任何选修课
（14）查询与Java在同一学期开设的课程的课程名和开课学期。

SELECT cname,Semester
FROM Course 
WHERE Semester =    //嵌套查询出java对应的Semester 
(SELECT Semester   
FROM course
WHERE cname='java'
)

（15）查询与李勇年龄相同的学生的姓名、所在系和年龄。

SELECT sname,sdept,sage 
FROM student
WHERE sage = (select sage from student where sname='李勇')

（16）用子查询实现如下查询：
a.查询选修了“c001”课程的学生姓名和所在系。

SELECT  S.sname,S.sdept
FROM Student S
WHERE S.sno in (
	SELECT sno from Sc
	WHERE cno = 'c001'
)

b.查询数学系成绩80分以上的学生的学号、姓名、选的课程号和成绩。

SELECT S.sno,sname
FROM student S
JOIN Sc ON S.sno=Sc.sno
WHERE sdept='MA' and Grade>=80

c.查询计算机系考试成绩最高的学生的姓名。

SELECT S.sname FROM Student S
JOIN Sc ON S.sno=Sc.sno
where  grade = (
	select MAX(grade) from sc where S.Sdept = 'CS'
)

d.查询数据结构考试成绩最高的学生的姓名、所在系、性别和成绩。

SELECT S.sname,S.sdept,S.ssex,grade 
FROM Student S
JOIN Sc ON S.sno=Sc.sno
WHERE grade=(
SELECT  max(grade) FROM Sc JOIN Course ON Sc.cno=Course.cno
WHERE Course.cname='数据结构'
)

4、小结

充分实践数据库的各种单表查询SQL语句、分组统计查询语句；以及自身的连接查询，多表连接查询。熟练掌握SQL查询语句的特点和作用，按照SQL程序设计规范，创建STUDENT,COURSE,SC三个表，并且自己插入相关的测试数据。完全地根据需求通过SQL查询出对应结果，同时通过dbug和查询资料解决了一些棘手的问题，比如LEFT JOIN 那里的筛选条件时，需要让B表的key为NULL，本次实验让我在平时感到模糊的知识又复习了一遍，并且亲手实践，按照不同的需求，修改不同的数据来测试结果。

5、思考

说明分组统计查询中的Where和Having有何区别？

执行顺序
where>聚合函数(sum,min,max,avg,count)>having
分组查询的结构：
SELECT 字段1,字段2 FROM 表名 GROUP BY分组字段 HAVING 分组条件;
区别：
关于数据过滤：
having子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个 分组标准进行分组
where子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据
关于统计函数使用
having后面可以使用分组函数(统计函数)
where后面不能使用聚合函数，因为where的执行顺序大于聚合函数