sql一些例题整理与总结,还有sql子链接的几种模型的整理

例题

例题1 巧妙利用布尔值

有如下表及数据

要求:查询出2门及2门以上不及格者的平均成绩

解法:score<60 返回布尔值 用int 1表示,此时用sum()函数就可以解决

答案:SELECT name,avg(score),sum(score<60) count FROM student GROUP BY name HAVING count >=2;

例题2 where型及from型子查询的陷阱

表及数据如例题1

知识点:关于from型子查询: 先select 查询出结果集看作一张表,起一个别名,再在外层select这张临时表中的内容;

where型子查询:内层的select 查询结果(应该为一行一列),放在了外层的查询条件里面;这种查询叫做where型子查询,

要求:查询出各个人的最高分数,及对应的科目

解法:因为这里每个人的分数不同,可以使用where型子查询解法2:使用from型子查询

错误解法2及错误原因:本来打算利用from型子查询,将表格进行重新排序,再对排序后的表格进行分组,默认取出第一条,但实践后发现行不通,原因是 from型子查询现在不支持内部的排序。

答案:SELECT name,score,subject from student where score in (SELECT max(score) FROM student GROUP BY name);

例题3 exists子查询

表及数据同例题1,同时多一个subjects表

要求,查询出有人选修的课程

解法:从subjects表中利用exists子查询,看students表中subject是否有与subjects中的subject相等的,如果有,那说明这个课有人选修

答案:

SELECT subject FROM subjects WHERE EXISTS (SELECT * FROM student where subjects.subject=student.subject);

例题4 内连接查询

表m,hid代表本场比赛的主队id,gid代表本场比赛的客队的id

mid hid gid mres matime
1 1 2 2:0 2006-05-21
2 2 3 1:2 2006-06-21
3 3 1 2:5 2006-06-25
4 2 1 3:2 2006-07-21

表t

tid tname
1 国安
2 申花
3 布尔联队

要求:查出 2006-6-1 到2006-7-1之间举行的所有比赛,并且用以下形式列出: 申花 1:2 布尔联队 2006-6-21

解法:把tname利用id与第一个表联系起来,两次利用内连接,分别选出主队以及客队的tname,最后按要求选择时间

答案:

SELECT t1.tname as hname,m.mres,t2.tname as gname,m.matime FROM m INNER JOIN t as t1 on m.hid=t1.tid INNER JOIN t as t2 on m.gid=t2.tid where m.matime  between '2006-06-01' and '2006-07-01';

例题5 UNION查询

解法1:先union all 把两张表合并在一起,再利用group by计算各个id 的和

答案:

SELECT sum(num),id FROM(SELECT id,num from a UNION ALL SELECT id,num FROM b) as tmp GROUP BY id; 

子连接模型

这里的示例表为例题5中的a,b表

上图从左到右分别是左连接,内连接,右连接。

sql语句分别为

SELECT a.id,a.num,b.id as id1,b.num as num1 FROM a LEFT JOIN b on a.id=b.id;
SELECT a.id,a.num,b.id as id1,b.num as num1 FROM a INNER JOIN b on a.id=b.id;
SELECT a.id,a.num,b.id as id1,b.num as num1 FROM a RIGHT JOIN b on a.id=b.id;

效果分别如图

以下为三种比较特殊的情况

sql语句分别为

SELECT a.id,a.num,b.id as id1,b.num as num1 FROM a LEFT JOIN b on a.id=b.id UNION SELECT a.id,a.num,b.id as id1,b.num as num1 FROM b LEFT JOIN a on a.id=b.id;
SELECT a.id,a.num,b.id as id1,b.num as num1 FROM a LEFT JOIN b on a.id=b.id WHERE b.id is null;
SELECT a.id,a.num,b.id as id1,b.num as num1 FROM a RIGHT JOIN b on a.id=b.id WHERE a.id is null;

效果分别如图