SQL语言学习——连接查询
本帖最后由 Marbles 于 2022-4-12 23:22 编辑第三部分——连接查询
1. 等值与非等值连接查询
2. 自身连接
3. 外连接
4. 多表连接
1.等值连接 连接运算符为 =
连接执行过程
（1）嵌套循环法（nested-loop）类似两重循环左表指针每向下移一次，右表便从头开始遍历一次
（2）排序合并法（sort-merge）两表各自排序其次，右表指针不返回
（3）索引连接（index-join）根据连接字段建立索引，直接找到满足条件的元组
特殊的等值连接——自然连接（去除重复的列）
明确写出select后面是哪个表的
例如：select student.sno,sname,ssex,sdept,cno,gradefrom student,scwhere student.sno=sc.sno
2.自身连接（需要给表起别名来区别，列名使用别名前缀）
例：查询每一门课的间接先修课（先修课的先修课）
select first.cno,second.cpno
from course first,course second
where first.cpno=second.cno
3. 外连接 自然连接会抛弃掉悬浮元组（不满足连接条件的元组）
而外连接会将悬浮元组放在自然连接的下面，其余列值补null
（1）左外连接，列出左边所有元组 from student left join sc on (student.sno=sc.sno)
（2）右外连接，列出右边所有元组
（3）全连接，from A outer join B on A.key=B.key
4. 多表连接（where或者from）
例：查询每个学生的学号、姓名、选修的课程名及成绩
select student.sno,sname,cname,grade
from student,sc,course
where student.sno=sc.snoand sc.cno=course.cno
或者
select student.sno,sname,cname,grade
from student join sc on student.sno=sc.sno
join course on sc.cno=course.cno
练习：
1.为了方便题目练习，重新建表和插入数据如下：（和第一部分表的差别是新增birthdate列，course表中新增cpno）
create table student (
sno char(7) primary key,
sname char(10) not null,
sex char(2) check(sex in('男','女')),
sage int,
dept char(20),
birthdate date )
create table course (
cno char(10) primary key,
cname char(20) not null,
credit int check(Credit>0),
cpno char(10),
semester int)
create table sc (
sno char(7),
cno char(10),
grade int check(grade>=0 and grade<=100),
primary key (sno, cno),
foreign key (sno) references student(sno),
foreign key (cno) references course(cno) )
insert into student(sno,sname,sex,sage,dept,birthdate)
values('0811101','李勇','男', 21,'计算机系','2000/5/6'),
('0811102','刘晨','男', 20,'计算机系','2001/8/8'),
('0811103','王敏','女', 21,'计算机系','2000/3/18'),
('0811104','张小红','女', 19,'计算机系','2002/1/10'),
('0821101','张立','男',21,'信息管理系','2000/10/12'),
('0821102','吴宾','女',20,'信息管理系','2001/3/20'),
('0821103','张海','男',20,'信息管理系','2001/6/3'),
('0831101','钱小平','女',21,'通信工程系','2000/11/9'),
('0831102','王大力','男',21,'通信工程系','2000/5/6'),
('0831103','张姗姗','女',20,'通信工程系','2001/2/26')
insert into course(cno,cname,credit,cpno,semester)
values('c001','高等数学',4,null,1),
('c002','大学英语',3,null,1),
('c003','大学英语',3,'c002',2),
('c004','计算机文化学',2,'c001',2),
('c005','java',2,'c004',3),
('c006','数据库基础',4,'c007',5),
('c007','数据结构',4,'c001',4),
('c008','计算机网络',4,'c004',4)
insert into sc (sno,cno,grade)values('0811101','c001',96),
('0811101','c002',80),('0811101','c003',84),
('0811101','c005',62),('0811102','c001',92),
('0811102','c002',90),('0811102','c004',84),
('0821102','c001',76),('0821102','c004',85),
('0821102','c005',73),('0821102','c007',null),
('0821103','c001',50),('0821103','c004',80),
('0831101','c001',50),('0831101','c004',80),
('0831102','c007',null),('0831103','c004',78),
('0831103','c005',65),('0831103','c007',null)
2.练习如下：
（注：每个练习后的
select * from tn
drop table tn 这两条语句，是为了方便检查语句是否正确，不属于题干操作）
（1）查询“计算机系”学生的修课情况，要求列出学生的名字、所修课的课程号和成绩，查询结果存入新表t1。
select sname,cno,grade into t1 from student,sc
where student.sno=sc.sno and dept='计算机系'
select * from t1
drop table t1
（2）查询“信息管理系”选修了“计算机文化学”课程的学生姓名和成绩，查询结果存入新表t2。
select sname,grade into t2 from student,sc,course
where student.sno=sc.sno and course.cno=sc.cno and cname='计算机文化学' and dept='信息管理系'
select * from t2
drop table t2
（3）查询所有选修了“Java”课程的学生情况，列出学生姓名和所在系，查询结果存入新表t3。
select sname,dept into t3 from student join sc on student.sno =sc.sno
join course on sc.cno=course.cno
where course.cname='Java'
select * from t3
drop table t3
（4）统计每个系的学生的考试平均成绩，列出系名和平均成绩，查询结果存入新表t4。
select dept '系名',AVG(grade) '平均成绩' into t4 from student join sc on student.sno=sc.sno
group by dept
select * from t4
drop table t4
（5）统计“计算机系”每个学生的选课门数、平均成绩、最高成绩和最低成绩,列出学生学号和以上信息，查询结果存入新表t5。
select student.sno '学生学号',COUNT(sc.cno) '选课门数',AVG(grade) '平均成绩',MAX(grade) '最高成绩',MIN(grade) '最低成绩'
into t5
from student left join sc on student.sno=sc.sno
group by student.sno
select * from t5
drop table t5
（6）查询与“刘晨”在同一个系学习的学生的姓名和所在的系，查询结果存入新表t6。
select st2.sname '姓名',st2.dept '所在系' into t6 from student st1,student st2
where st1.sname='刘晨'
and st2.sname<>'刘晨'
and st1.dept=st2.dept
select * from t6
drop table t6
（7）查询与“数据结构”在同一个学期开设的课程的课程名和开课学期，查询结果存入新表t7。
select c2.cname '课程名',c2.semester '开课学期' into t7 from course c1,course c2
where c1.cname='数据结构'
and c2.cname<>'数据结构'
and c1.semester=c2.semester
select * from t7
drop table t7
（8）查询至少被两个学生选的课程的课程号，查询结果存入新表t8。
select cno into t8 from sc
group by cno
having COUNT(sno)>=2
select * from t8
drop table t8
（9）查询全体学生的选课情况，包括选修了课程的学生和没有选修课程的学生，列出学生学号、姓名、课程号、成绩，查询结果存入新表t9。
select student.sno,sname,cno,grade into t9 from student left join sc on student.sno=sc.sno
select * from t9
drop table t9
（10）查询没人选的课程的课程名，查询结果存入新表t10。
select cname into w10 from course left join sc on course.cno=sc.cno
where sc.cno is null
select * from w10
drop table w10
（11）查询“计算机系”没有选课的学生，列出学生姓名和性别，查询结果存入新表t11。
select sname,sex into t11 from student left join sc on student.sno=sc.sno
where dept='计算机系'
and sc.sno is null
select * from t11
drop table t11
（12）统计“计算机系”每个学生的选课门数，包括没有选课的学生。列出学生学号、选课门数，查询结果存入新表t12。
select student.sno '学号',COUNT(cno) '选课门数' into t12 from student left join sc on student.sno=sc.sno
where dept='计算机系'
group by student.sno
select * from t12
drop table t12
（13）查询“信息管理系”选课门数少于3门的学生的学号和选课门数，包括没有选课的学生。查询结果按选课门数递增排序，查询结果存入新表t13。
select student.sno '学号',COUNT(cno) '选课门数' into t13 from student left join sc on student.sno=sc.sno
where dept='信息管理系'
group by student.sno
having COUNT(cno)<3
order by COUNT(cno)
select * from t13
drop table t13
其他部分链接：
第一部分 建表和插入数据：https://www.52pojie.cn/thread-1619534-1-1.html
第二部分 单表查询：https://www.52pojie.cn/forum.php ... 619534&pid=42268783
欢迎指正~~ SQL真的蛮好的，值得收藏，下次学习 不错不错 赞，学习一下
编辑的有些语句黏到一块了 select ....into..在mysql中不能用，create table 新表 as select
学习学习，感谢分享 SQL收藏，备查用！ 好耶 之前学了一半可以接着学
页:
[1]
2