MySQL查询篇——（二）多表连接

内连接

#显示拥有系别的学生学号，姓名，及所在系名称-[内连接方式] 内连接必须都要对应能显示
SELECT s.sno,s.sname,d.dname from student s inner join dept d ON s.did = d.did
SELECT s.sno,s.sname,d.dname from student s join dept d ON s.did = d.did

#不推荐写法
SELECT s.*, d.dname from student s ,dept d where s.did = d.did

左连接

#显示所有学生的信息，及所在系的情况-[左连接/左外连接]左表有的数据都能显示
SELECT s.*,d.dname from student s LEFT join dept d ON s.did = d.did
SELECT s.*,d.* from student s LEFT OUTER join dept d ON s.did = d.did

#显示所有院系 ，并将对应的学生列出来
SELECT d.*,s.* from dept d LEFT join student s ON s.did = d.did

右连接

#显示所有院系 ，并将对应的学生列出来
SELECT d.*,s.* from student s RIGHT JOIN dept d on s.did = d.did

全连接

SELECT s.*,d.dname from student s LEFT join dept d ON s.did = d.did
union
SELECT s.*,d.dname FROM student s RIGHT JOIN dept d on s.did = d.did

综合案例

#查询已选课学生姓名，课程名称，课程成绩
select s.sname,c.cname,sc.degree from sc 
INNER JOIN student s ON sc.sno = s.sno
INNER JOIN course c ON sc.cno = c.cno

#查询至少选修了一门课的女同学姓名，除去重复姓名项
select distinct s.sname from sc join student s on sc.sno = s.sno where s.sex = '女'

配套视频：0-查询篇-课程导读_哔哩哔哩_bilibili

sql文件：百度网盘 请输入提取码