接上一篇博文SQL Server 数据库实验课4——常用查询条件_tmd的博客-CSDN博客
一、HAVING短语
GROUP BY分组后可使用HAVING语句按照一定的条件对这些组进行筛选
例3.47查询选修了3门以上课程的学生学号 门以上课程的学生学号 。
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*) >3;
这里先用GROUP BY子句按S进行分组,再用聚集函数COUNT对每一组计数HAVING短语给出了选择组的条件,只有满足条件的组才会被选择出来。
WHERE子句与HAVING短语的区别在于作用对象不同。WHERE子句作用于基本表或视图,从中选择满足条件的元组。HAVING短语作用于组,从中选择满足条件的组。
例3.48查询平均成绩大于等于90分的学生学号和平均成绩。
SELECT Sno,AVG(Grade)
FROM SC
WHERE AVG(Grade)>=85
GROUP BY Sno;
报错,因为WHERE子句是不能用聚焦函数作为条件表达式的,正确的查询语句应该是
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=85;
二、连接查询
1.等值与非等值连接查询
运算符为=时,成为等值连接。使用其他运算符成为非等值连接
格式
[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>
此外连接谓词还可用一下形式
[<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2>AND[<表名2>.]<列名3>
连接查询: 同时涉及两个及以上的表的查询
连接条件: 连接查询的WHERE子句用来连接两个表的条件
连接字段: 连接谓词中的列名称。连接条件中各连接字段必须是可比的,名字不必相同
例3.49查询每个学生及其选修课程的情况
SELECT Student.*,SC.* --SELECT Student.*, SC.*可简写为SELECT *
FROM Student,SC
WHERE Student.Sno=SC.Sno;
若去掉表中第三行则运行结果为笛卡尔积
若在等值连接中把目标列中重复的属性列去掉则为自然连接
例3.50对例3.49用自然连接完成
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Sno=SC.Sno;
因为Sno在两个表中都出现了,引用时必须加上表明前缀
去掉重复的Sno这一列
一条SQL语句可以同时完成选择和连接查询
例3.51查询选修2号课程且成绩在85分以上的所有学生和姓名
SELECT Student.Sno,Sname
FROM Student,SC
WHERE Student.Sno=SC.Sno AND SC.Cno='2'AND SC.Grade>85
--打印全表对比
SELECT *
FROM Student,SC
WHERE Student.Sno=SC.Sno;
先从SC中选择出Cno='2’且Grade>85的元组形成一个中间关系,再和Student中满足连接条件的元组进行连接得到最终的结果
连接操作的执行方法有
嵌套循环法:就是将一个表为出发点,将该表全部记录逐条去遍历另外一张表的记录,符合条件的就是写入结果集
排序合并法:排序合并连接原理是先对两个表J进行排序(排序的时候要删去不符合where条件的列),然后再进行连接。
不同的DBMS执行方法可能会有所区别,不过出发点都是提高效率
2. 自身连接
自身连接: 一个表与其自己进行连接
例3.52 查询每一门课的间接先修课(即先修课的先修课)
为Course表取两个别名,FIRST和SECOND
SELECT FIRST.Cno, SECOND.Cpno
FROM Course FIRST, Course SECOND
WHERE FIRST.Cpno = SECOND.Cno;
--打印全表对比
SELECT FIRST.*, SECOND.*
FROM Course FIRST, Course SECOND
WHERE FIRST.Cpno = SECOND.Cno;
3. 外连接
普通连接操作只输出满足连接条件的元组,外连接操作以指定表为连接主体,将主体表中不满足连接条件的元组一并输出
外连接分三类:左外连接(LEFT OUTER JOIN)、右外连接(RIGHT OUTER JOIN)、全外连接(FULL OUTER JOIN)
例3.53 改写上面等值连接的例子:查询每个学生及其选修课程的情况:
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUTER JOIN SC ON(Student.Sno=SC.Sno);
--也可使用USING来去掉结果中的重复值
--FROM Student LEFT OUTER JOIN SC USING(Sno);
左外连接列出左边关系中所有的元组,右外连接列出右边关系中所有的元组。
4.多表连接
多表连接:两个以上的表进行连接
通常是先进行两个表的连接操作,再将其连接结果与第三个表进行连接
例3.54: 查询每个学生的学号、姓名、选修的课程名及成绩:
SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno
AND SC.Cno=Course.Cno;