数据库sql语言学习(六)

数据库查询之多表（一）

/*
可能用到的表结构
Departments(Dno, Dname,Dheadno)  
院系（院系号，院系名，院主任）
Teachers (Tno,Tname,Sex,Birthday,Title,Dno)
老师（教工号，教师名，性别，生日，职称，所在院系号）
Students(Sno,Sname,Sex,Birthday,Speciality,Dno)
学生（学号，名字，性别，生日，专业名，院系号）
Course(Cno, Cname,Credit)
课表（课程号，课程名，学分）
Teaches(Tno,Cno，TCscore)
实体教（教工号，课程号,评估得分）
SC(Sno,Cno,Greade)
选课（学生学号，课程号，学生成绩）








select [all|distinct] <选择序列>
from <表引用>， ...，<表引用>
[where <查询条件>]
[group by <分组列> ，..., <分组列> [having <分组选择条件>]  ]
[order by <排序列> [asc|desc],..., <排序列> [asc|desc] ]


多表查询，也就是将多张表做连接之后查询：
其实在from后面可以有很多的表引用，这个就是多表查询的语法；但是他们做的是笛卡尔乘积
然而，我们最常使用的是自然连接，所以，只能在where中进行实现


*/




-- 查询学号为200705001的学生的各科成绩，对每一门课程显示课程名称和成绩
-- 显然，课程名在Courses中，成绩在SC中，所以，需要做自然连接
select Cname,Greade
from Courses , SC
where SC.Cno = Courses.Cno and Sno = '200705001';--其中SC.Cno = Courses.Cno 这是自然连接的条件
-- Cno既是表SC的属性，也是Courses的属性。为了避免二义性，我们必须在Cno前加前缀“SC.”或“Courses.”
-- 其实，任何属性前都可以加前缀。但是，当A只是FROM子句中一个表的属性时，前缀可以省略 






-- 查询课程为CS202课程，并且分数在80分以上的所有学生的学号，姓名和成绩
-- 显然，分数在SC表中，学生姓名在学生表Students中，
select Students.Sno,Sname,Greade
from SC,Students
where SC.Sno = Students.Sno and Cno = 'CS202' and Greade>80;




-- 查询每个学生选修的每门课程的成绩，要求列出的学号、姓名、课程名和成绩
-- 显然：姓名在学生Students中，课程名在Courses中，成绩在SC中
select Students.Sno, Sname,Cname,Greade
from SC,Students,Courses
where SC.Sno = Students.Sno and SC.Cno = Courses.Cno;




-- 查询每个学生的平均成绩，并输出平均成绩大于85的学生学号、姓名和平均成绩。
-- 显然，有姓名需要Students表，有成绩需要SC表
-- 这里一定要注意：对于带group by子句的查询，select子句中的结果列只能是(分组属性和聚集函数)这个很重要
-- 由于查询要求显示学生的姓名，因此在该查询中，我们按学生的学号和姓名分组
select Students.Sno ,Sname, avg(Greade)
from SC,Students
where SC.Sno = Students.Sno
group by Students.Sno ,Sname having avg(Greade)>85;


-- 自身连接是一个表和它自己进行连接，通常情况下使用的不多，对于一些特定的查询，自身连接查询非常有效
-- 显然，需要查询的就是一张表Students，其实，这个时候很容易想到程序设计语言中数组的查询，
-- 查询和张华出生年月相同的学生的姓名
select  S1.Sno
from Students S1,Students S2
where  S1.Birthday = S2.Birthday and S1.Sname = '张华' and S2.Sname<> '张华';