mysql学习笔记:自连接的3道例题和总结

自连接的应用和生命力是蛮广泛和深刻的，也是我考试时候的十分要注意的地方！

关系代数，和SQL中都有着自连接的身影。

1.先来一个前导引入哈哈：

查询：查询选修1号同学选修的课程的学生学号。

分析：这个查询语句的含义是：选修1号同学他所选修的课程的一门，两门，多门甚至所有全部都是可以的，这里是只要有就行了！

这里这个语句的选修的词后面有隐含的从1门到所有的意思.  (between 1 and all)

先用关系代数语言来解决：

 

 

有不完善的地方希望大佬门或未来的我自己再来指正修改哒2333

 

 

 

SQL语言实现:

 

 

 

 

select * from sc;

 

　　

 

第一种方案就是我们讲的自连接啦：

select  distinct sc1.sno/*去重*/
from sc as sc1,sc as sc2
where sc2.sno='1' 
and sc1.cno=sc2.cno;/*可能会出现重复,没有去重,so*/

　　

 效果如下:

 

 

 

 

当然，也可以进一步规范化2333

 

 

 

 

 

第二种方法就是我不前还不多熟练地嵌套查询：

 

 

/*选修1号学生选的课程的学生的学号，没加所有哦所以不是除法*/
select distinct sno
from sc 
where cno in
(select cno
from sc
where sno='1'
);/*嵌套不熟练*/

 

　　

 

 

2.查询：查询同时选修1号和2号课程的学生学号。

方法：自连接，除法，交运算。

自连接方法：

select sc1.sno
from sc as sc1,sc as sc2
where sc1.sno=sc2.sno 
and sc1.cno=1 and sc2.cno=2;

　　

 

或者：

嘿嘿

 

交运算实现：

 

/*查询同时选修1号和2号课程的学生学号。*/
select sno
from sc
where cno='1' 
and sno in
(select sno
from sc
where cno='2');

 

　　至于除法运算啦，我目前还没有怎么多多地实现之qwq

 

 

——————————————————————————————————————

除法运算实现形式：

#查询同时选修1号和2号课程的学生学号。__除法
select sno from student where not exists
(select * from c where (cno='1' or cno='2' )and not exists
(select * from sc where sno=student.sno and cno=c.cno));

#查询同时选修1号和2号课程的学生学号。__除法
select sno from student where not exists
(select * from c where cno in ('1','2') and not exists
(select * from sc where sno=student.sno and cno=c.cno));

　　

 

 

记住，括号一定要加，and优先级比or要高哦2333！括号不加的话答案可就不对了23333！（ps：蟹蟹jiaxin大佬的灵犀指点，奥力给！）

 

 

 

 

 

 

 

 

3.

 

 

 

 

自连接方法解决之:

 

 

/*间接先修课程号*/
select c1.cno,c2.cpre as cppre
from c as c1,c as c2
where c1.cpre=c2.cno;

 

　　

拓展：

 

select c1.cno,c2.cpre as cppre,c3.cname
from c as c1,c as c2,c as c3
where c1.cpre=c2.cno and c2.cpre=c3.cno;/*间接先修课课程名*/

 

 

 

补充一下哈：cpre or cpno是课程表c的外码，Cno是C的主码

设置cpre成为c的外码的语句格式如下:

alter table c
add constraint FK_c 
foreign key(cpre)
references c(cno);/*将课程表c的cpre字段设置为c的外码，参照关系也是c*/

 

 

 

4.小结：写到这一地步，今天上课的3道自连接题目有了一定程度上的总结！勤奋奋斗，冲鸭，龙龙！多多实践之，冲冲冲！