例子:
-------------------------------------------------
a表 id name b表 id job parent_id
1 张3 1 23 1
2 李四 2 34 2
3 王武 3 34 4
a.id同parent_id 存在关系
--------------------------------------------------
1) 内连接
select a.*,b.* from a inner join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
2)左连接
select a.*,b.* from a left join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
3 王武 null
3) 右连接
select a.*,b.* from a right join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
null 3 34 4
4) 完全连接
select a.*,b.* from a full join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
null 3 34 4
3 王武 null
二.经典sql语句整理:
Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表
1.查询平均成绩大于60分的同学的学号和平均成绩
select id , avg(score) from student group by id having avg(score)>60;
2*.查询“001”课程比“002”课程成绩高的所有学生的学号
select a.id from(select id,score from student where kc = '001') a ,(select id,score from student where kc = '002')b
where a.score>b.score and a.id = b.id;
3*.查询所有同学的学号、姓名、选课数、总成绩
select s# ,sname,count(sc.s#),sum(score) from Student left outer join SC on Student.s# = SC.s#
group by student.s#,sname;
4、查询姓“李”的老师的个数;
select count(distinct(Tname)) from Teacher
where Tname like ‘李%’;
5.查询所有课程成绩小于60分的同学的学号、姓名
select s#,sname from student where s# not in(select student.s#
from student,sc where student.s# = sc.s# and sc.score>60);
6.删除学习“叶平”老师课的SC表记录
delete sc from Teacher ,course where sc.c# = Course.c# and course.T#=Teacher.T#
and Teacher.Tname='叶平'
7.查询A(ID,Name)表中第31至40条记录,ID作为主键可能不是连续增长的列
select top 10* from A where ID not in(select top 30 from A order by ID) order by ID
8、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
SELECT L.C# 课程ID,L.score 最高分,R.score 最低分
FROM SC L ,SC R
WHERE L.C# = R.C#
and
L.score = (SELECT MAX(IL.score)
FROM SC IL,Student IM
WHERE IL.C# = L.C# and IM.S#=IL.S#
GROUP BY IL.C#)
and
R.Score = (SELECT MIN(IR.score)
FROM SC IR
WHERE IR.C# = R.C#
GROUP BY IR.C# );
9、查询学生平均成绩及其名次
SELECT 1+(SELECT COUNT( distinct 平均成绩)
FROM (SELECT S#,AVG(score) 平均成绩
FROM SC
GROUP BY S# ) T1
WHERE 平均成绩 > T2.平均成绩) 名次, S# 学生学号,平均成绩
FROM (SELECT S#,AVG(score) 平均成绩 FROM SC GROUP BY S# ) T2
ORDER BY 平均成绩 desc;
10、查询各科成绩前三名的记录:(不考虑成绩并列情况)
SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
FROM SC t1
WHERE score IN (SELECT TOP 3 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC)
ORDER BY t1.C#;
11、查询每门功成绩最好的前两名
SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
FROM SC t1
WHERE score IN (SELECT TOP 2 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC )
ORDER BY t1.C#;