文章目录
建立相关表七大JOINInner JOINLeft JOINLeft Excluding JOINRight JOINRight Excluding JOINFULL Outer JOINFULL Outer Excluding JOIN本文将着重分析下图中联合查询的用法:
图片来源: 一张图看懂 SQL 的各种 JOIN 用法 。
建立相关表
在理解之前,我们首先建立相关的表,首先建立学生表:
create table student(stuNochar(5) primary key, -- 学生学号stuName varchar(32) not null, -- 学生姓名gender char(1) not nulldefault '男', -- 学生性别age int not null -- 学生年龄)charset='utf8';
之后插入数据:
-- 添加学生数据insert into student(stuNo,stuName,gender,age) values('001','李志','男',14);insert into student(stuNo,stuName,gender,age) values('002','宋东野','男',23);insert into student(stuNo,stuName,gender,age) values('003','赵雷','男',34);insert into student(stuNo,stuName,gender,age) values('004','马頔','男',32);insert into student(stuNo,stuName,gender,age) values('005','陈粒','女',18);insert into student(stuNo,stuName,gender,age) values('006','筠子','女',4);insert into student(stuNo,stuName,gender,age) values('007','寸铁','男',56);insert into student(stuNo,stuName,gender,age) values('008','狗毛','男',47);insert into student(stuNo,stuName,gender,age) values('009','崔健','男',39);insert into student(stuNo,stuName,gender,age) values('010','草东','男',40);insert into student(stuNo,stuName,gender,age) values('011','张悬','女',36);insert into student(stuNo,stuName,gender,age) values('012','撒娇','女',25);insert into student(stuNo,stuName,gender,age) values('013','宋东野','男',35);
再建立分数表,此处的分数表不严谨,因为并没有相关课程的信息,并且应该和学生表有一个外键关联,但为了简单的理解联合查询以及需要插入一个特殊的数据,此处就不弄那么复杂了:
create table score(id int not null AUTO_INCREMENT primary key,sNo char(5) not null , -- 教师号score double not null -- 成绩-- foreign key(sNo) references student(stuNo));
之后插入数据:
-- 添加成绩数据insert into score(sNo,score) values('001',56);insert into score(sNo,score) values('002',87);insert into score(sNo,score) values('004',92);insert into score(sNo,score) values('005',76);insert into score(sNo,score) values('007',90);insert into score(sNo,score) values('008',53);insert into score(sNo,score) values('010',67);insert into score(sNo,score) values('011',69);insert into score(sNo,score) values('012',78);insert into score(sNo,score) values('014',80); -- 此数据如果有外键关联则无法插入
此时就得到了学生表和分数表,删除重建命令如下:
-- 删除表drop table if exists score;drop table if exists student;
七大JOIN
现在我们假设学生表为A,分数表为B。
两张表的详细信息如下:
Inner JOIN
查询学生中参加考试的学生的分数
select s.stuNo, s.stuName, sc.score FROM student as s inner join score as sc on s.stuNo=sc.sNo ;
可得结果:
Left JOIN
查询所有学生的分数(包含缺考的)
select s.stuNo, s.stuName, sc.score FROM student as s left join score as sc on s.stuNo=sc.sNo order by s.stuNo ASC;
可得结果:
Left Excluding JOIN
查询缺考的学生
select s.stuNo, s.stuName, sc.score FROM student as s left join score as sc on s.stuNo=sc.sNo where sc.score is NULL order by s.stuNo ASC;
可得结果如下:
Right JOIN
查询所有分数对应的学生
select s.stuNo, s.stuName, sc.score FROM student as s right join score as sc on s.stuNo=sc.sNo order by s.stuNo ASC;
可得结果如下:
Right Excluding JOIN
查询有分数但不在学生表的分数
select s.stuNo, s.stuName, sc.score FROM student as s right join score as sc on s.stuNo=sc.sNo where s.stuNo is NULL order by s.stuNo ASC;
可得结果如下:
FULL Outer JOIN
我们需要注意, Oracle数据库支持full join,mysql是不支持full join的,但仍然可以同过左外连接+ union+右外连接实现。 UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。
查询所有的学生和所有的分数
select s.stuNo, s.stuName, sc.score FROM student as s left join score as sc on s.stuNo=sc.sNoUNIONselect s.stuNo, s.stuName, sc.score FROM student as s right join score as sc on s.stuNo=sc.sNo;
可得结果如下:
FULL Outer Excluding JOIN
查询分数为空的学生和学生为空的分数
select s.stuNo, s.stuName, sc.score FROM student as s left join score as sc on s.stuNo=sc.sNo where sc.score is NULL UNIONselect s.stuNo, s.stuName, sc.score FROM student as s right join score as sc on s.stuNo=sc.sNo where s.stuNo is NULL ;
可得结果如下: