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 service基础（四）连接查询、自身连接查询、外连接查询和复合条件连接查询[通俗易懂]

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

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

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

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

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

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

　请将查询到的信息截图

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

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

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．完成查询所有比“刘伟”工资高的老师的姓名、工资以及刘伟的工资，请回答以下问题：

①　请将查询到的信息截图

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

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

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

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

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

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

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

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

三、外连接

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

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

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

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

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

①　再将上面得到的所有学生的部分信息左连接先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

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

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

select student.sno,sname

from student,sc

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

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

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

select sc.sno,cname,score

from sc,course

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

order by score desc

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

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

select sname,cname,score

from sc,student,course

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

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

⑤　查询课程名称为“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

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

⑥　查询平均成绩大于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

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

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

select student.sno,sname

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

group by student.sno,sname

having count(cno)=1

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

注意：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

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

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

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

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

若不想把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(*)

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

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/138836.html原文链接：https://javaforall.cn