SQL service基础（四）连接查询、自身连接查询、外连接查询和复合条件连接查询[通俗易懂]

大家好，又见面了，我是你们的朋友全栈君。

实验目标：

1．掌握涉及一个以上数据表的查询方法。

2．掌握等值连接

3．掌握自然连接

4．掌握非等值连接

5．掌握自身连接、外连接和复合条件连接

本次实验sql脚本：

INSERT [dbo].[T] ([TNO], [TN], [SEX], [AGE], [PROF], [SAL], [COMM], [DEPT]) VALUES (N’T1′, N’李力    ‘, N’男’, 47, N’教授      ‘, 1800, 3000, N’计算机    ‘)
GO
INSERT [dbo].[T] ([TNO], [TN], [SEX], [AGE], [PROF], [SAL], [COMM], [DEPT]) VALUES (N’T2′, N’王平    ‘, N’女’, 28, N’讲师      ‘, 850, 1200, N’信息      ‘)
GO
INSERT [dbo].[T] ([TNO], [TN], [SEX], [AGE], [PROF], [SAL], [COMM], [DEPT]) VALUES (N’T3′, N’刘伟    ‘, N’男’, 30, N’讲师      ‘, 900, 1200, N’计算机    ‘)
GO
INSERT [dbo].[T] ([TNO], [TN], [SEX], [AGE], [PROF], [SAL], [COMM], [DEPT]) VALUES (N’T4′, N’张雪    ‘, N’女’, 51, N’教授      ‘, 1900, 3000, N’自动化    ‘)
GO
INSERT [dbo].[T] ([TNO], [TN], [SEX], [AGE], [PROF], [SAL], [COMM], [DEPT]) VALUES (N’T5′, N’张兰    ‘, N’女’, 39, N’副教授    ‘, 1300, 2000, N’信息      ‘)
GO
INSERT [dbo].[C] ([CNO], [CN], [CT]) VALUES (N’C1′, N’程序设计  ‘, 60)
GO
INSERT [dbo].[C] ([CNO], [CN], [CT]) VALUES (N’C2′, N’微机原理  ‘, 80)
GO
INSERT [dbo].[C] ([CNO], [CN], [CT]) VALUES (N’C3′, N’数字逻辑  ‘, 60)
GO
INSERT [dbo].[C] ([CNO], [CN], [CT]) VALUES (N’C4′, N’数据结构  ‘, 80)
GO
INSERT [dbo].[C] ([CNO], [CN], [CT]) VALUES (N’C5′, N’数据库    ‘, 60)
GO
INSERT [dbo].[C] ([CNO], [CN], [CT]) VALUES (N’C6′, N’编译原理  ‘, 60)
GO
INSERT [dbo].[C] ([CNO], [CN], [CT]) VALUES (N’C7′, N’操作系统  ‘, 60)
GO
INSERT [dbo].[TC] ([TNO], [CNO], [Evaluation]) VALUES (N’T1′, N’C1′, NULL)
GO
INSERT [dbo].[TC] ([TNO], [CNO], [Evaluation]) VALUES (N’T1′, N’C4′, NULL)
GO
INSERT [dbo].[TC] ([TNO], [CNO], [Evaluation]) VALUES (N’T2′, N’C5′, NULL)
GO
INSERT [dbo].[TC] ([TNO], [CNO], [Evaluation]) VALUES (N’T3′, N’C1′, NULL)
GO
INSERT [dbo].[TC] ([TNO], [CNO], [Evaluation]) VALUES (N’T3′, N’C5′, NULL)
GO
INSERT [dbo].[TC] ([TNO], [CNO], [Evaluation]) VALUES (N’T4′, N’C2′, NULL)
GO
INSERT [dbo].[TC] ([TNO], [CNO], [Evaluation]) VALUES (N’T4′, N’C3′, NULL)
GO
INSERT [dbo].[TC] ([TNO], [CNO], [Evaluation]) VALUES (N’T5′, N’C5′, NULL)
GO
INSERT [dbo].[TC] ([TNO], [CNO], [Evaluation]) VALUES (N’T5′, N’C7′, NULL)
GO
INSERT [dbo].[S] ([SNO], [SN], [SEX], [AGE], [DEPT]) VALUES (N’S0′, N’王青山  ‘, N’男’, 19, N’计算机    ‘)
GO
INSERT [dbo].[S] ([SNO], [SN], [SEX], [AGE], [DEPT]) VALUES (N’S1′, N’赵亦    ‘, N’女’, 17, N’计算机    ‘)
GO
INSERT [dbo].[S] ([SNO], [SN], [SEX], [AGE], [DEPT]) VALUES (N’S2′, N’钱尔    ‘, N’男’, 18, N’信息      ‘)
GO
INSERT [dbo].[S] ([SNO], [SN], [SEX], [AGE], [DEPT]) VALUES (N’S3′, N’张晓明  ‘, N’男’, 18, N’信息      ‘)
GO
INSERT [dbo].[S] ([SNO], [SN], [SEX], [AGE], [DEPT]) VALUES (N’S4′, N’李思    ‘, N’男’, 21, N’自动化    ‘)
GO
INSERT [dbo].[S] ([SNO], [SN], [SEX], [AGE], [DEPT]) VALUES (N’S5′, N’周武    ‘, N’男’, 19, N’计算机    ‘)
GO
INSERT [dbo].[S] ([SNO], [SN], [SEX], [AGE], [DEPT]) VALUES (N’S6′, N’吴丽    ‘, N’女’, 20, N’自动化    ‘)
GO
INSERT [dbo].[S] ([SNO], [SN], [SEX], [AGE], [DEPT]) VALUES (N’S7′, N’范思萌  ‘, N’女’, 18, N’信息      ‘)
GO
INSERT [dbo].[S] ([SNO], [SN], [SEX], [AGE], [DEPT]) VALUES (N’S8′, N’孙姗    ‘, N’女’, 20, N’信息      ‘)
GO
INSERT [dbo].[S] ([SNO], [SN], [SEX], [AGE], [DEPT]) VALUES (N’S9′, N’张海涛  ‘, N’男’, 19, N’信息      ‘)
GO
INSERT [dbo].[SC] ([SNO], [CNO], [SCORE]) VALUES (N’S1′, N’C1′, 90)
GO
INSERT [dbo].[SC] ([SNO], [CNO], [SCORE]) VALUES (N’S1′, N’C2′, 85)
GO
INSERT [dbo].[SC] ([SNO], [CNO], [SCORE]) VALUES (N’S2′, N’C5′, 50)
GO
INSERT [dbo].[SC] ([SNO], [CNO], [SCORE]) VALUES (N’S2′, N’C6′, 80)
GO
INSERT [dbo].[SC] ([SNO], [CNO], [SCORE]) VALUES (N’S3′, N’C1′, 75)
GO
INSERT [dbo].[SC] ([SNO], [CNO], [SCORE]) VALUES (N’S3′, N’C2′, 70)
GO
INSERT [dbo].[SC] ([SNO], [CNO], [SCORE]) VALUES (N’S3′, N’C4′, 85)
GO
INSERT [dbo].[SC] ([SNO], [CNO], [SCORE]) VALUES (N’S4′, N’C1′, 93)
GO
INSERT [dbo].[SC] ([SNO], [CNO], [SCORE]) VALUES (N’S4′, N’C2′, 85)
GO
INSERT [dbo].[SC] ([SNO], [CNO], [SCORE]) VALUES (N’S4′, N’C3′, 83)
GO
INSERT [dbo].[SC] ([SNO], [CNO], [SCORE]) VALUES (N’S5′, N’C2′, 89)
GO

一、连接查询

1．完成查询所有选课学生的学号、姓名、选课名称及成绩后，请回答以下问题：

　请将查询到的信息截图。

　下面的SQL语句可否完成上面的查询任务？为什么？

不可以，因为列名sno不明确，因为S表和SC表都存在sno列，需要表名.列名的形式来区分。

该题是通过连接谓词“＝”完成两表的等值连接，请将此题改成用JOIN为关键字的内连接，将相应的SQL语句描述，from子句可参考以下格式：

2．完成查询每门课程的课程号、任课老师姓名及其选课人数，请回答以下问题：

　请将查询到的信息截图

①　此题哪几个表进行连接，连接的条件分别是什么？

T,TC,.C 和SC表，

连接条件是t.tno=tc.tno and c.cno=tc.cno and c.cno=sc.cno

②　说说你对此题分组的理解。

使用GROUP BY子句可以对C.CNO,T.TN多个字段进行分组操作，并可以对分组结果进行count累计过滤操作

二、自身连接

1．完成查询所有比“刘伟”工资高的老师的姓名、工资以及刘伟的工资，请回答以下问题：

①　请将查询到的信息截图

请将此题改成用JOIN为关键字的内连接，将相应的SQL语句描述。

2．在完成查询同时选修了“程序设计”和“微机原理”的学生姓名、系名题前，请回答以下问题：

①　请查询只选修了“程序设计”的学生姓名、系名，请将查询到的信息截图。

②　请查询只选修了“微机原理”的学生姓名、系名，请将查询到的信息截图。

③　再把两个结果连接起来，现在是不是就得到了此题的结果，请把结果截图。

三、外连接

1．完成查询所有学生的学号、姓名、选课名称及成绩（没有选课的学生的选课信息显示为空）前，请完成下面的问题。

　前面用相应的内连接SQL语句（如下）完成了查询选课学生的相关信息，得到如下图所示的信息。可以用这种方法得到此题要求的信息吗？为什么？

　用左连接完成SC表与S表的连接，有下面两种方案，哪种方案是正确的？为什么？

①　再将上面得到的所有学生的部分信息左连接先C表，得到此题要求的所有信息，请把结果截图，并说说对左连接的理解。

②　此题可以改为右连接吗？如果可以，请描述相应的SQL语句

四、综合练习

综合练习的sql脚本：

–学生表（学号、姓名、年龄、性别）
create table student(
sno varchar(10) primary key,
sname varchar(20),
sage int,
ssex varchar(5)
)
–教师表（教师工号、姓名）
create table teacher(
tno varchar(10) primary key,
tname varchar(20)
)
–课程表（课程号、课程名、教师工号）
create table course(
cno varchar(10),
cname varchar(20),
tno varchar(10),
constraint pk_course primary key (cno,tno)
)
–成绩表（学号、课程号、分数）
create table sc(
sno varchar(10),
cno varchar(10),
score real,
constraint pk_sc primary key (sno,cno)
)
–初始化学生表
insert into student values (‘s001′,’张三’,23,’男’);
insert into student values (‘s002′,’李四’,23,’男’);
insert into student values (‘s003′,’吴鹏’,25,’男’);
insert into student values (‘s004′,’琴沁’,20,’女’);
insert into student values (‘s005′,’王丽’,20,’女’);
insert into student values (‘s006′,’李波’,21,’男’);
insert into student values (‘s007′,’刘玉’,21,’男’);
insert into student values (‘s008′,’萧蓉’,21,’女’);
insert into student values (‘s009′,’陈萧晓’,23,’女’);
insert into student values (‘s010′,’陈美’,22,’女’);
–初始化教师表
insert into teacher values (‘t001’, ‘刘阳’);
insert into teacher values (‘t002’, ‘谌燕’);
insert into teacher values (‘t003’, ‘胡明星’);
–初始化课程表
insert into course values (‘c001′,’J2SE’,’t002′);
insert into course values (‘c002′,’Java Web’,’t002′);
insert into course values (‘c003′,’SSH’,’t001′);
insert into course values (‘c004′,’Oracle’,’t001′);
insert into course values (‘c005′,’SQL SERVER 2005′,’t003’);
insert into course values (‘c006′,’C#’,’t003′);
insert into course values (‘c007′,’JavaScript’,’t002′);
insert into course values (‘c008′,’DIV+CSS’,’t001′);
insert into course values (‘c009′,’PHP’,’t003′);
insert into course values (‘c010′,’EJB3.0′,’t002’);
–初始化成绩表
insert into sc values (‘s001′,’c001’,78.9);
insert into sc values (‘s002′,’c001’,80.9);
insert into sc values (‘s003′,’c001’,81.9);
insert into sc values (‘s004′,’c001’,60.9);
insert into sc values (‘s001′,’c002’,82.9);
insert into sc values (‘s002′,’c002’,72.9);
insert into sc values (‘s003′,’c002’,81.9);
insert into sc values (‘s001′,’c003′,’59’);

创建另一数据库，执行shiyan综合.sql，完成表的创建及数据的插入。回答以下问题（请贴出以下问题的sql语句）

①　查询不同课程成绩相同的学生的学号、课程号、学生成绩（自身连接）

select a.* from sc a ,sc b where a.score=b.score and a.cno<>b.cno

②　查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名

select student.sno,sname

from student,sc

where student.sno=sc.sno and sc.cno=’c001′ and score>=80

③　查询不及格的课程，并按课程成绩降序排列

select sc.sno,cname,score

from sc,course

where sc.cno=course.cno and sc.score<60

order by score desc

④　查询课程成绩在70 分以上的姓名、课程名称和分数；

select sname,cname,score

from sc,student,course

where sc.sno=student.sno and sc.cno=course.cno and sc.score>70

⑤　查询课程名称为“Oracle”，且分数低于60 的学生姓名和分数

select sname,score

from student,sc,course

where sc.sno=student.sno and sc.cno=course.cno and course.cname=’Oracle’ and scourse.score<60

⑥　查询平均成绩大于85 的所有学生的学号、姓名和平均成绩

select student.sno,sname,avg(score) as 平均成绩

from student  join sc on student.sno=sc.sno

group by student.sno,sname

having avg(score)>85

⑦　查询出只选修了一门课程的全部学生的学号和姓名

select student.sno,sname

from student left join sc on student.sno=sc.sno

group by student.sno,sname

having count(cno)=1

注意：having count(cno)不能写成having count(*)，因为没有成绩的学生信息也有一行数据。

也可以：

select student.sno,sname

from student,sc

where student.sno=sc.sno

group by student.sno,sname

having count(cno)=1

⑧　统计列印各科成绩,各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[ <60]

select course.cno,cname,sum(case when score<60 then 1 else 0 end) as ‘[<60]’,

sum(case when score>=60 and score<70 then 1 else 0 end) as ‘[70-60]’,

sum(case when score>=70 and score<85 then 1 else 0 end) as ‘[85-70]’,

sum(case when score>=85 and score<100 then 1 else 0  end) as ‘[100-85]’

from sc,course

where sc.cno=course.cno

group by course.cno,cname

⑨　查询不同老师所教不同课程平均分从高到低显示，平均分最多保留两位小数。

select sc.cno,teacher.tname,cname,round(avg(score),2) as 平均成绩

from course,sc,teacher

where course.cno=sc.cno and course.tno=teacher.tno

group by teacher.tname,sc.cno,cname

order by avg(score) desc

若不想把cname放在group by 里，也可以放在一些聚集函数，但不影响它的值，如max(cname)

⑩　查询所有课程成绩小于60 分的同学的学号、姓名

select student.sno,sname

from student,sc

where student.sno=sc.sno

group by student.sno,sname

having sum(case when score<60 then 1 else 0 end)=count(*)