数据库学习之路 五 sql的所有操作

例34．统计每门课程的选课人数，列出课程号和人数。
    SELECT Cno as 课程号, 
                 COUNT(Sno) as 选课人数 
       FROM SC GROUP BY Cno 

对查询结果按Cno的值分组，所有具有相同Cno值的元组为一组，然后再对每一组使用COUNT计算，求得每组的学生人数。

例35．查询每名学生的选课门数和平均成绩。
    SELECT Sno as 学号, 
                 COUNT(*) as 选课门数, 
                 AVG(Grade) as 平均成绩 
       FROM SC GROUP BY Sno

HAVING用于对分组自身进行限制，它有点象WHERE子句，但它用于组而不是对单个记录。
例36．查询修了3门以上课程的学生的学号
	SELECT Sno FROM SC 
      GROUP BY Sno
     HAVING COUNT(*) > 3 

例37．查询修课门数等于或大于4门的学生的平均成绩和选课门数。
	SELECT Sno, AVG(Grade) 平均成绩, 
                COUNT(*) 修课门数
	  FROM SC 
     GROUP BY Sno 
     HAVING COUNT(*) >= 4 

查询有1门以上课程是90分的学生的学号和课程数
SELECT Sno, 
COUNT(*) as 修课门数
FROM SC 
WHERE Grade>= 90
GROUP BY Sno 
HAVING COUNT(*) >= 1 

查男同学年龄组超过50人，查询结果按升序，人数相同按降序
SELECT Sage, Count(Sno) 
FROM Student
WHERE Ssex= '0'
GROUP BY Sage 
HAVING COUNT(Sno) > 50
ORDER By 2,Sage Desc; 

若一个查询同时涉及两个或两个以上的表，则称之为连接查询。
连接查询是关系数据库中最主要的查询
连接查询包括内连接、外连接和交叉连接等。

SQL-92 内连接语法如下：
    SELECT …
     FROM 表名  [INNER] JOIN
       被连接表
       ON  连接条件

执行连接操作的过程：
首先取表1中的第1个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，
找到后就将表1中的第1个元组与该元组拼接起来，形成结果表中的一个元组。
表2全部查找完毕后，再取表1中的第2个元组，然后再从头开始扫描表2， …
重复这个过程，直到表1中的全部元组都处理完毕为止。 

SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno = SC.Sno
自然连接
Student.sno = sno
查询先修课程
SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST,Course SECOND
WHERE FIRST.Cpno = SECOND.Cno;

例40.查询计算机系学生的修课情况，要求列出学生的名字、所修课的课程号和成绩。
	SELECT Sname, Cno, Grade 
     FROM Student JOIN SC  
     ON Student.Sno = SC.Sno
     WHERE Sdept = '计算机系' 

例41. 查询信息系修了VB课程的学生的修课成绩，要求列出学生姓名、课程名和成绩。
SELECT Sname, Cname, Grade
  FROM  Student  s  JOIN  SC 
  ON s.Sno = SC. Sno
  JOIN  Course c ON c.Cno = SC.Cno
  WHERE Sdept = '信息系' 
        AND Cname = 'VB' 

为特殊的内连接
相互连接的表物理上为同一张表。
必须为两个表取别名，使之在逻辑上成为两个表。

例43. 查询与刘晨在同一个系学习的学生的姓名和所在的系。
SELECT S2.Sname, S2.Sdept
  FROM Student S1 JOIN Student S2
  ON S1.Sdept = S2.Sdept
  WHERE S1.Sname = ‘刘晨’
  AND S2.Sname != ‘刘晨’

只限制一张表中的数据必须满足连接条件，而另一张表中数据可以不满足连接条件。 
ANSI方式的外连接的语法格式为：
	FROM  表1  LEFT | RIGHT  [OUTER]  
      JOIN  表2  ON  <连接条件> 
theta方式的外连接的语法格式为：
左外连接：
   FROM  表1, 表2  WHERE [表1.]列名(+) ＝ [表2.]列名
右外连接：
   FROM  表1, 表2  WHERE [表1.]列名＝ [表2.]列名(+) 

例44. 查询学生的修课情况，包括修了课程的学生和没有修课的学生。
	SELECT Student.Sno, Sname, Cno, Grade
		FROM Student LEFT OUTER JOIN SC
		ON Student.Sno = SC.Sno 

例37 查询所有选修了1号课程的学生姓名
SELECT Sname
FROM Student
WHERE Sno IN
(
SELECT Sno
FROM SC
WHERE Cno = '95001'
)

例38查询所有选修了课程名为“信息系统”的学生学号和姓名
用连接查询
SELECT Student.Sno,Sname
FROM Student,SC,Course
WHERE Student.Sno =SC.Sno ANd
SC.Cno = Course.Cno AND
Course.Cname ='信息系统'

用嵌套查询
SELECT Sno,Sname
FROM Student
WHERE Sno IN
(
SELECT Sno
FROM SC
WHERE Cno IN
(
SELECT Cno
FROM Course
WHERE Cname='信息系统'
)
);

例38-1找出所有学生超过所有课程平均成绩的课程号
SELECT Sno,Cno
FROM SC
WHERE Grade>=(SELECT AVG(Grade)
from SC);

找出每个学生超过他选修课程平均成绩的课程号
SELECT Sno,Cno
FROM SC X
WHERE Grade>=(SELECT AVG(Grade) FROM SC Y WHERE Y.Sno=X.Sno)

例39 查询其它系中比信息系任意一个学生年龄小的学生姓名
SELECT Sname，Sage
FROM Student
WHERE Sage <any (SELECT Sage
FROM Student
Where Sdept='IS') AND Sdept<>'IS'

查询其它系中比信息系比所有学习年龄都小的的学生姓名
SELECT Sname，Sage
FROM Student
WHERE Sage <ALL (SELECT Sage
FROM Student
Where Sdept='IS') AND Sdept<>'IS'

用集函数实现
SELECT Sname，Sage
FROM Student
WHERE Sage<
(
 SELECT MIN(Sage)
 FROM Student
 WHERE Sdept='IS'
)AND Sdept<>'IS'

查询所有选修了1号课程的学生姓名
SELECT Sname
FROM Student
WHERE EXISTS
(
 SELECT *
 FROM SC
 WHERE Student.Sno='1' and Cno = '1'
)

SELECT Sname
FROM Student
WHERE NOT EXISTS
(
 SELECT *
 FROM Course
 WHERE NOT EXISTS( 
 SELECT *
 FROM SC
  WHERE Sno=Student.sno and Cno = Course.Cno
)
)