MySQL关联查询

一、关联查询
1、概念
在查询数据时，所需要的数据不只在一张表中，可能在两张或多张表中。这个时候，需要同时操作这些表来查询数据，即关联查询。

关联查询所涉及到的表与表之间都会存在有关联的字段，如员工表的部门编号和部门表的部门编号。

2、笛卡尔积
在做关联查询时，数据库会使用某一张表中的每一条记录都与另外一张表的所有记录进行组合。比如表A有x条记录，表B有y条记录，最终组合数为x*y，这个值就是笛卡尔积，通常没有意义。

 

 

 

 

3、等值连接
在做关联查询时，这些表中存在着有关联的两个字段。我们使用某一张表中的一条记录通过相关联的字段与另外一张表的记录进行匹配，组合成一条新的记录。使用"="连接关联字段

需求1:查询员工的姓名，职位及其所在部门的名称 、地址

 

 

 

 

4、内连接
内连接返回所有满足条件的记录，关键字join on。查询效果与等值连接一样。
用法：表A [inner] join 表B on 关联条件

 

 

 

 

 

5、外连接
在做关联查询时，我们所需要的数据，除了那些满足关联条件的数据外，还有不满足关联条件的数据。此时需要使用外连接。

会涉及到两个概念：

驱动表(主表)：除了显示满足条件的数据，还需要显示不满足条件的数据的表
从表(副表)：只显示满足关联条件的数据的表
mysql外连接只支持左外连接，右外连接，不支持全外连接

左外连接:
表A left [outer] join 表B on 关联条件。
表A是驱动表，表B是从表
右外连接
表A right [outer] join 表B on 关联条件
表B是驱动表，表A是从表
全外连接：
两张表的数据不管满不满足条件，都做显示。
表A full [outer] join 表B on 关联条件
PS:mysql 不支持全外连接

 

需求1：查询所有员工的姓名，职位，及其部门编号，部门名称----分析：员工表为驱动表，部门表为从表

select e.ename,e.job,e.deptno,d.dname from emp e left join dept d on e.deptno = d.deptno;

 

 

6、自连接
自连接是一种特殊的关联查询。数据的来源是同一个表，这样的表内的多个字段要存有关系。我们要使用表别名来虚拟出两个表。

需求1：查询员工姓名，职位及其上司姓名，职位。

mysql > select a.ename 员工姓名,
a.job 员工职位,
b.ename 上司姓名,
b.job 上司职位
from emp a join emp b on a.mgr=b.empno;

　　

分析：可以看出 a的mgr与b的empno关联，所以，a是员工表，b是领导表

 

二、高级关联查询
有的时候，我们要查询的数据，一个简单的查询语句完成不了，并且我们使用的数据，表中不能直观体现出来。而是预先经过一次查询才会有所体现。那么先执行的查询，我们称之子查询。被子查询嵌入的查询语句称之为父查询或主查询。

主查询可以是select语句，也可以是DML语句或者是DDL语句。

根据子查询返回结果的不同，可以分为单行单列子查询、多行单列子查询、多行多列子查询。

 

 

 

 

子查询所在的位置，有可能出现在以下地方：

1)子查询可以在where子句中
2)子查询可以在from子句中
3)子查询可以在having子句中
4)子查询可以在select字句中，相当于外连接的另外一种写法。
1、在where子句中
需求1：查询和员工姓名scott同职位的员工信息。

select ename,job,hiredate,sal,deptno from emp where job=(select job from emp where ename='scott');

 

 

 

 

需求2：查询薪水比所有员工的平均薪水高的员工信息

mysql > select ename,job,hiredate,sal from emp where sal > (select avg(ifnull(sal,0)) from emp);

 

需求3：查询出部门中有salesman但是职位不是salesman的员工信息

select ename,job,hiredate,sal,deptno from emp where deptno in (select distinct deptno from emp where job='salesman') and job <> 'salesman';

 

exists 关键字
有时候，子查询需要引用主查询的字段数据，我们使用exists关键字。exists后面的子查询至少返回一条记录，则整个条件为true;

需求：查询有员工的部门信息

mysql > select deptno,dname,loc from dept d where exists (select * from emp e where d.deptno =e.deptno);

 

2、在from子句中
from子句用于指定表，如果想在一个子查询的结果里继续查询，则子查询需要写在from子句中，相当于一个表。

需求1：查询工资大于本部门平均工资的员工的信息。

mysql > select e.ename,e.sal,t.avg_sal,t.deptno from emp e join (select deptno,avg(ifnull(sal,0)) 'avg_sal' from emp group by deptno) t on e.deptno = t.deptno and e.sal>t.avg_sal order by t.deptno;

 

需求2：查询每个员工的工资，姓名和其部门的平均工资。

select e.ename, e.sal, t.avg_sal from emp e , (select deptno,avg(ifnull(sal,0)) 'avg_sal' from emp group by deptno) t where e.deptno = t.deptno order by t.deptno;
3、在having子句中

需求：查询平均工资大于30号部门平均工资的部门号、平均工资

mysql > select deptno,avg(ifnull(sal,0)) from emp group by deptno having avg(ifnull(sal,0))>(select avg(ifnull(sal,0)) from emp where deptno=30);
4、在select子句中

需求1：查询每个员工的姓名，工资，及其部门的平均工资,工资之和

select ename,sal,
(select avg(ifnull(sal,0)) from emp a where a.deptno=b.deptno) avg_sal ,
(select sum(sal) from emp c where c.deptno=b.deptno ) sum_sal
from emp b order by b.deptno;

 

EXISTS
语法：

SELECT 字段 FROM table WHERE EXISTS (subquery);

 

参数：

subquery是一个受限的SELECT语句（不允许有COMPUTE子句和INTO关键字）

示例：

SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.id = A.id);

 

EXISTS执行顺序：

1、首先执行一次外部查询，并缓存结果集，如 SELECT * FROM A

2、遍历外部查询结果集的每一行记录R，代入子查询中作为条件进行查询，如 SELECT 1 FROM B WHERE B.id = A.id

3、如果子查询有返回结果，则EXISTS子句返回TRUE，这一行R可作为外部查询的结果行，否则不能作为结果

NOT EXISTS
语法：

SELECT 字段 FROM table WHERE NOT EXISTS (subquery);
参数：

subquery是一个受限的SELECT语句（不允许有COMPUTE子句和INTO关键字）

示例：

SELECT * FROM A WHERE NOT EXISTS (SELECT 1 FROM B WHERE B.id = A.id);
NOT EXISTS执行顺序：

1、首先执行一次外部查询，并缓存结果集，如 SELECT * FROM A

2、遍历外部查询结果集的每一行记录R，代入子查询中作为条件进行查询，如 SELECT 1 FROM B WHERE B.id = A.id

3、如果子查询没有返回结果（与EXISTS相反），则NOT EXISTS子句返回TRUE，这一行R可作为外部查询的结果行，否则不能作为结果

IN
IN常用于where表达式中，其作用是查询某个范围内的数据。

示例：

select * from where field in (value1,value2,value3,…)
NOT IN
当 IN 前面加上 NOT 运算符时，表示与 IN 相反的意思，即不在这些列表项内的选择

示例：

select * from where field not in (value1,value2,value3,…)
实例
假设现在有三张表：

student：学生表，其中有字段sno为学号，sname为学生姓名

course：课程表，其中有字段cno为课程号，cname为课程名称

student_course_relation：选课表，记录学生选择了哪些课程，其中有字段sno为学号，cno为课程号

下面通过几个示例来说明一下EXISTS和NOT EXISTS的用法，及其与IN和NOT IN的区别

1、在子查询中使用NULL，仍然返回结果集

下面三种情况返回数据相同，都会返回student表的所有数据：

select * from student;
select * from student where exists (select 1);
select * from student where exists (select null);

 

2、EXISTS子查询返回的是一个布尔值true或false

EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回布尔值true或false，EXISTS指定一个子查询，检测行的存在。

EXISTS只在乎子查询中是否有记录，与具体的结果集无关，所以下面示例中，子查询中的select sno也可以换成select cno或者select 1，查询出的结果集是一样的。

查询所有选修了课程号为3的学生：

select * from student a
where exists (select sno from student_course_relation b where b.cno=3 and b.sno=a.sno);

select * from student a
where exists (select cno from student_course_relation b where b.cno=3 and b.sno=a.sno);

select * from student a
where exists (select 1 from student_course_relation b where b.cno=3 and b.sno=a.sno);

 

3、比较使用EXISTS和IN的查询

示例，查询所有选修了课程号为1的学生名单

使用exists：

select * from student
where exists (select sno from student_course_relation where cno=1 and student_course_relation.sno=student.sno)

 

使用in：

select * from student
where sno in (select sno from student_course_relation where cno=1)

 

以上两个sql查询，返回相同的结果。

EXISTS查询：先执行一次外部查询，然后为外部查询返回的每一行执行一次子查询，如果外部查询返回100行记录，sql就将执行101次查询。

IN查询：先查询子查询，然后把子查询的结果放到外部查询中进行查询。IN语句在mysql中没有参数个数的限制，但是mysql中sql语句有长度大小限制，整段最大为4M。IN引导的子查询只能返回一个字段。

当子查询的表大的时候，使用EXISTS可以有效减少总的循环次数来提升速度，当外查询的表大的时候，使用IN可以有效减少对外查询表循环遍历来提升速度，显然，外表大而子表小时，IN的效率更高，而外表小，子表大时，EXISTS的效率更高，若两表差不多大，则差不多。

4、比较使用NOT EXISTS和NOT IN的区别

示例，查询没有选修课程号为1的学生名单

使用NOT EXISTS：

select * from student a
where not exists (select sno from student_course_relation b where cno=1 and a.sno=b.sno)

 

使用NOT IN：

select * from student a
where sno not in (select sno from student_course_relation b where cno=1)

 

NOT EXISTS：先执行一次外部查询，然后为外部查询返回的每一行记录R执行一次子查询，如果子查询没有返回记录，则NOT EXISTS子句返回TRUE，这一行R可作为外部查询的结果行。

NOT IN：外部查询在表中查询每条记录，符合要求的就返回结果集，不符合的就继续查询下一条记录，直到把表中的记录查询完，也就是说为了证明找不到，需要查询全部记录才能证明，NOT IN不会用到索引。

5、在插入记录前，检查这条记录是否存在，只有当记录不存在时才执行插入操作

示例，若学号为3的学生没有选课程号为2的课程，则选择此课程

insert into student_course_relation(sno, cno)
select '3' as sno, '2' as cno from student_course_relation
where not exists (select sno from student_course_relation where sno=3 and cno=2) limit 1

 

此Sql适用场景：虽然业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引，但是有些老业务可能已经写入了重复数据，且重复数据不能删除，这样的话，就不能建立唯一索引，后续的数据却又要求两个字段的组合唯一，可以使用以上sql语句解决这个问题。

6、查询出选修了全部课程的学生姓名

思路1：首先我们需要知道一共有几门课程，然后扫描student_course_relation表，统计出选修了所有课程的学号，最后在student表中根据学号打出学生姓名。

select sname from student where sno in (
select sno from student_course_relation
group by sno -- 根据sno分组，统计每个学生选修了几门课，如果等于course的总数，就是我们要找的sno
having(count(sno)) = (select count(*) from course)
)

 

思路2：

首先我们来查询学号为3的学生没有选修的课程

select cname from course
where not exists -- 找不到的记录，提交course
(select cno from student_course_relation where student_course_relation.cno=course.cno and sno='3')

 

如果我们对所有的学号进行循环，这道题的题目可以转化为：查询 没有 没有选修课的学生姓名，即选修了全部课程的学生姓名

select sname from student
where not exists ( -- 查询 没有 没有选修的课程 的学生，即选修了全部课程的学生
select cname from course -- 查询某学生没有选修的课程
where not exists
(select cno from student_course_relation
where student_course_relation.cno=course.cno and student_course_relation.sno=student.sno)
)

 

7、查询没有选择所有课程的学生

即存在这样的一个学生，他至少有一门课没有选

select * from student a
where exists (
select * from course b
where not exists (select * from student_course_relation c where c.sno=a.sno and c.cno=b.cno))

 

注意：EXISTS或NOT EXISTS写法需要注意子查询中的条件语句一般需要带上外查询的表做关联，不然子查询的条件可能会一直为真，或者一直为假，外查询的表进行循环匹配的时候，要么全部都查询出来，要么一条也没有。

8、查询至少选修了学生3选修的全部课程的学生名单

这个题目可以转化为：不存在这样的课程x，学生3选修了x而学生m没有选

select * from student m where sno!=3 and not exists (
select sno from student_course_relation x where sno=3 and not exists (
select cno from student_course_relation y where y.cno=x.cno and y.sno=m.sno
)
)

 

9、查询一门课也没有选的学生

即不存在这样的一个学生，他至少选修了一门课程

select * from student a
where not exists (
select * from course b
where exists (select cno from student_course_relation c where c.cno=b.cno and c.sno=a.sno))

 

10、查询至少选修了一门课程的学生

select * from student a
where exists (
select cno from course b
where exists (select sno from student_course_relation c where c.cno=b.cno and c.sno=a.sno))

 

11、选出每门课程中成绩最高的学生

select * from student_course_relation a
where not exists (select * from student_course_relation b where b.cno=a.cno and b.score > a.score)

 

另一种实现方式：

select * from student_course_relation a
where score=(select max(score) from student_course_relation b where b.cno=a.cno)