[mysql]多表查询详解

我们如果要查询,我们就要用

SELECT ....

FROM ....

WHERE AND/OR/NOT #我们需要用过滤的条件来对数据进行筛选,不然会有很多多余数据

ORDER BY (ASC/DESC)#排序

LIMIT....,#是在几个有限的数据库管理系统里所以,PGsql,mysql,等

多表查询的意义

我们目前为止的查询语句难度还不算大,我们要将一个重要的内容,多表查询,在我们后面的查询比较重要的就是多表查询和子查询,在我们之后的开发环境里多表查询和子查询是离不开.一旦有了这两个查询代码量就会变大,那么我们就会难一点.我们如果要进行sql的优化就是因为嵌套的循环,所以我们的多表查询和子查询中是要进行优化.我们写出来就已经很不错了,但是我们还得对它进行优化.我们还得根据指标来对它进行优化.

说这么多的目的就是为了引出多表查询.我们还有一个引入方式:

另外一个角度,我们说数据库概述的时候,我们提到表记录和表记录的关系,有1对1,1对多的关系.就是自己引用自己,我们不从优化的角度来看我们是不可以把所有的一对一的关系的表放在同一个表里保存起来,但是

我们之前是不是说到了一对多的关系:这就涉及到了我们的多表查询了.我们也可以想象到多表的之间的查询是非常常见了,因为我们的业务是非常多表,我们要熟悉一下2个表,一个是部门表和坐落位置的表location是表

DESC employees;

DESC departments;

DESC locations;

我们发现它们都有对应的字段相同的.我们现在想查看我们的一个员工名是'abel'的人在哪个城市工作,这是我们的需求.

我们的员工表里面是没有说城市的事.我们首先查询一下,

SELECT * FROM employees WHERE last_name ='abel'

我们可以找到abel的department_id是80也就是我们的销售部门

我们可以发现它的id是80对应的location_id是2500,这代表了位置信息.我们就可以去查询它的位置信息.

我们现在就abel是在oxford这个地方工作.

我们是不是借助了3张表知道了这个表,我们先通过last_name知道department_id,然后在第二张表里知道了locationid,然后最后在第三张表里找到了city的位置

就是这样的一个顺序,这呢,我们就进行了一个多表的查询,你们是不是觉得这样就有点简单了,我们这个需求虽然用这个3个查询出来了.但是我们如果向后台的数据库查询,我们是不是要进行3次查询,进行3次交互.才能查到这个数据,如果我们能一次完成,那我们是不是要比3次强的多的多.

多表查询就是可以把这3个sql语句合成一个sql语句,我们再回过来墨迹一下.我们要把思路打开.

我们有些情况大家想不到,我们替大家想到,然后再解答,下篇我们再深入解答,我们先有这个广度,我们抛出去一个问题,我们不是讲公司员工的事,我们能不能把这3张表合并成一个表呢,可不可以呢?可以为什么我们不这样做呢,为什么我们没这样做呢,这是一个高级的问题,我举一个场景给大家说明,我们有一个it部门,这里面有300个人,我们会把这300个人的详细信息,和他们的id对对应的部门,.

如果分成两张表,那么这个部门我只要写一条记录,如果要全部合并的话,是不是300个人都要写着it部门的信息,那么是不是就有很大的冗余,而且不止如此,因为有些人没有部门,那么合并之后这些人的部门字段就会留空,有些部门没有人,这些部门的人员信息也会留空,那么是不是就浪费资源.如果我们再加入部门id的location表,冗余就更多了.

如果我们说到优化呢我们查询的时候我们要查询其中一个范围的数据我们要把磁盘的数据加载到内存中,我们内存是10万封顶,我们要把十个字段的话那么我们就加载1万条数据,如果字段多的话,比如20条,那么我们要加载的字段就只能加载2次io.

再比如我们一个人查一个表,但是另一个人就不能去查这个表.如果我们分成2个表,那么这个过程就可以同时进行.可以更高的并发.

总之我们根据具体的实际情况,我们会把单位的字段放在一个表,部门放在一个表里,我们用关联查询,或者说多表查询来做到这个查询.这就是多表查询的意义

多表的查询如何实现.

笛卡尔积错误

SELECT employee_id,department_name FROM employees,departments

我们先不差locations表,因为他比较特别

我们由于有两张表,所以我们FROM要加一个表,我们是不是没有什么过滤条件,那直接查询就好了

,我们查出来了2889条记录,虽然没有报错,我们会发现一个员工有很多个部门,好像管培生,这个是错误的,为什么来说这个是怎么来的.我们知道员工表一共只有107条记录,然后我们看2889/107 能除的干净,我们发现答案的27,我们查询一下部门表,department;刚好27条

这里代表的含义,就是107条记录乘以27条记录.我们就知道为什么出错了,因为每个员工都与每个部门匹配了一次,我们把这种错误起了一个名字.出现笛卡尔积的错误.我们把每个员工和部门都连接了一遍,笛卡尔积不是一个错误的计算,是因为我们这个错误和笛卡尔计算是相同的.他代表的就是x的集合和y的集合进行交叉相乘.这就是笛卡尔积的交叉连接

(笛卡尔是一个人,是法国的大数学家,当时数学有很多的大佬,高斯和其他大佬,笛卡尔有一个很大的贡献,就是平面直角坐标系,它在国外是笛卡尔坐标系,就是它发明的.我们早期学的代数,我们通过算数的方式去研究数学,我们数学实际是物理的需求,我们物理是具体的内容也就是几何,就是因为几何和数学不相关,但是笛卡尔坐标系就可以把它有了个几何的坐标系.我们高中的解析几何,就是用代数的方式来解决代数问题.你想想一个人能随便想出来这个物体,他其实是一个哲学家,数学家其实只是它的第二重点哈哈.

比较可惜的一个人是伽罗瓦,只活了21岁,在数学上的贡献非常大,群论就是它已一己之力来开发了一个新的领域,他送了3篇的学术论文,均被退回和失去,几十年之后才被发现,现在还有很多知识没被了解.)

我们闲聊一会,反正这种错误就被交笛卡尔积错误,这个错误的原因是什么呢,就是因为我们缺少了连接条件,我们需要一个多表的连接条件

多表的连接条件

SELECT employee_id,department_name FROM employees CROSS JOIN departments

我们告诉服务器去找,它就懵了,他就出现了笛卡尔积错误.

正确的方式就是加上链接条件,需要有连接条件,我们把它粘贴过来.两个表的连接条件得告诉服务器的连接字段,,也是在条件里添加.

我们直接就WHERE

SELECT employee_id,department_name FROM employees,departments
WHERE employees.department_id=departments.department_id

我们会发现只有106条记录,如果大家有印象,这个表里因为有一个人是没有部门id的.因为他没有部门id,他是NULL,因为我们想让他出来,我们就得涉及到外连接了,之后我们再说.

这就是我们多表的查询,也可以叫关联查询.两个表和更多的表进行操作和关联的.我们现在就把这个小问题给解决了.我们接着说,

我们加上了多表连接的条件,我们就解决的笛卡尔积问题.

笛卡尔积错误是什么情况会出现呢.

1省略了多个表的连接条件(关联条件),我们的关联条件(连接条件0)写错了.或者所有表的所有行都互相连接了.这块说的就是一个内容,因为连接条件没起作用.所以我们在WHERE添加连接条件就可以了.

我们查询表中的两个字段,SELECT employee_id,department_name,department_id FROM employees ,

SELECT employee_id,department_name,department_id
FROM employees,departments
WHERE employees.department_id=departments.department_id

这里出现了一个单词ambiguous,就是说它不明白这个字段在那个位置,因为两个表里都有department_id.所以我们必须指明在那个表

如果查询语句中多个表中都存在的字段,就必须指明此字段所在的表.其他的字段需要指明吗,从结果来看是不需要指明的,但是从优化的角度来看,在多表查询的时候都指明字段所在字段的表

这里可以发现我们的运行速度是0.671,如果我们指明了另外两个表的话,那么结果就是0.383秒.这里可以发现几乎少了一半.如果我们不声明的话,就会发现他就会查询2个表,如果声明的话,就只会查询一个表.一定要记得加上.

可以给表起别名,在SELECT和WHERE中使用表的别名.和字段一样直接用空格或者,

我们表的名字比较长的话,大公司会要求我们第一个表用T1,第二个表用T2,都可以.

所以我们在多表查询的时候我们就可以起别名了.

我们如果给表起了别名之后,一旦再WHERE和SELECT中使用原表的表名.必须得用表的别名.而不能再使用表名.凭什么呢.还是那个意思,因为我们是先执行from,我们起了别名,别名就覆盖了原名.之后运行WHER和SELECT,那就只认这个表的别名了

这个情况大家要去关注

然后我们要进行一个练习,查询员工的employee_id,last_name,department_id,city

如果有n个表需要多表的连接,我们就需要至少有个n-1个连接条件.用AND相连.

我们要连接3个表的话,我们必须要两两连接,至少连接一个表内

那么我们就这样成功查询出来了.另外记得,如果我们有字段department和location_id一定要记得加他们存在那个表.

为什么至少有n-1连接条件.我们看我们的表有两个字段,2个表也可以两个连接条件.

多表查询到此是不是就差不多了,其实不是的,我们只是从问题出发,把问题解决.

我们提出问题1:解决问题1

提出问题2,解决问题2

这是演绎式的教学,学习的时候我们用演绎式的方式是比较经常用于开头.然后再多次解决问题之后,我们就可以用到归纳式了.我们中国的教育就是有点无趣,是填鸭式的,可能没有演绎式有效率,但是比较直接

我们现在要宏观的来把多表查询说一下,多表查询的一个分类:我从几个不同的角度来一个说明

多表查询的几个角度的分类

角度1:等值连接:非等值连接,这是针对连接条件的角度

角度2:自连接vs非自连接,这是针对是否进行自我引用的角度

角度3内连接vs外连接这是比较难理解

我们没必要再开发的时候看自己的多表查询是什么类型的查询,我们这边的希望大家知道多表查询的几个类别,知道它的情况.比如我们人可以从肤色来说性别来说,之类的分类,目的是为了让大家看见多表查询的各种常见了

先说角度1:等值连接:非等值连接,这是针对连接条件的角度

我们前面写的都是等值连接.就是两个条件不一定得是=,也可以是>,<,<>.

我们再查询一个表 SELECT * FROM job_grades

我现在希望查询员工表的每个工资等级

这里假如我们知道一个员工的工资是5000,我们是不是知道它在B阶段

那么我们就知道怎么写了

SELECT T1.last_name, T1.salary,T2.grade_level
FROM job_grades T2,employees T1
WHERE T1.salary BETWEEN T2.lowest_sal AND T2.highest_sal

这里是不是就可以把salary对应的等级就给表示出来了.

那么我们继续

角度2:自连接vs非自连接,这是针对是否进行自我引用的角度

如果我们是表自己和自己连呢,自恋是什么,自己喜欢自己,自律是什么自己严格要求自己..

比如我们有一个例子,员工有一个字段交主管编号.因为员工编号就是主管编号,其实也是公司的一个员工,和我们这个情况其实是对应的.

我们的表是不是也有一个字段叫做mange_id.这个id是不是也一定是员工部门的一部分.

练习:员工id 员工姓名管理者的id和管理者的姓名

这里留一个练习给大家,这里我们员工的id是不是应该和我们管理者的id是一个等的关系.记住我们要声明表的字段是哪个位置.我们

答案:

那我们就把这两种情况就给说明了,到时候别看见了类似的条件就说自己没见过慌了,我们也是见过大世面的人了.接下来我们再说我们的内连接和外连接

角度3内连接vs外连接这是比较难理解

我们来对它进行一个剖析,我们上面写的全部其实都是内连接.我们拿个对应的例子,

SELECT employee_id,department_name FROM employees,departments
WHERE employees.department_id=departments.department_id这里我们是不是只把左表和右表满足条件的连接给放进去了,这个就叫内连接

内连接,合并具有同一列的两个以上表的行,结果集中不包含一个表与另一个表不匹配的行

这个时候是不是原本表里有107条但是我们内连接出来只有106条,这时候是不是就有一个员工因为没有部门所以没有连接上,而且我们完全可以怀疑是不是出现了部门有但是部门里面没有员工的情况.

我们如果想把这两种情况的哥们和部门查出来,那我们就需要外连接

外连接:合并具有同一列的两个以上表的行,结果集中包含一个表与另一个表匹配的行还包含一个表与另一个表不匹配的行

外连接的分类,左外连接,右外连接.满外连接.

我们要先了解了左外和右外就了解满外连接了

左外连接:合并具有同一列的两个以上表的行,结果集中包含一个表与另一个表匹配的行还包含一个左表与另一个表不匹配的行

右外连接:合并具有同一列的两个以上表的行,结果集中包含一个表与另一个表匹配的行还包含一个右表与另一个表不匹配的行

练习,查询所有员工的last_name department_name信息

如果这个时候出现了所有,我们一定要打起10000分的精神来,所有了,所以它一定是一个外连接.那,我们查出来的就不能是106,因为我们公司一样

SELECT employee_id,department_name FROM employees,departments
WHERE employees.department_id=departments.department_id

SQL就和编程语言一样,它的语言是有不同版本,我们开发的时候,根据时间的,SQL和不同的编程语言.对于我们开发的时间来看,最重要的就是ql92和sql99,对于我们开发来看只要看这个就行,sql92的标准有500页,sql99,,92的语句可读性比较差.sql99的可读性就比较高了,也被分别叫做sql2标准和sql3标准.

sql92的标准如何进行外连接:

那大家可能会好奇,sql92如何实现内连接:那就是见上.

sql92语法如何实现外连接,使用+号就行.我们员工表有107条记录,我们匹配的时候前面的106个都能匹配上,它的想法是这样的,左边是左腿,右边是右腿,思路就是给右边垫上一点东西,让左腿和右腿一样长,我们

SELECT employee_id,department_name FROM employees,departments
WHERE employees.department_id=departments.department_id(+)#这边的话因为我们的员工在左边,所以就是左外连接,我们就只要在右边加一个加号就可以,那我们运行一下,

我们发现它报错了,这是为什么呢,这是因为mysql不支持sql92标准的外连接写法

mysql不支持sql92标准

我们这边是因为我们不一定要支持没有mysql的写法.我们这边如果我们换成Oracle这个写法就是可以支持的,一共是107条记录,

sql99语法中如何实现多表查询.

使用的是JOIN ON的方法实现多表查询的方式,这种方式也能解决外连接的问题,mysql是支持这种方式的写法,现在我们就把眼光投入mysql,查看sql99语法如何实现多表查询的

sql99语法如何实现多表查询,我们如何复刻呢.

内连接

SQL99语法实现内连接:zli

SELECT last_name,department_name
FROM employees T1
(INNER) JOIN departments T2
ON T1.department_id=T2.department_id

我们每加入一个表,我们就要用INNER JOIN加入一个新的表.这里的逻辑就是像一个组织,如果有新人加入,它就要说它认识里面的那个人.也就是用JOIN声明它里面那个字段的表是相同的.

SELECT last_name,department_name
FROM employees T1
JOIN departments T2
ON T1.department_id=T2.department_id

JOIN departments T3
ON T2.location_id=T3.location_id

左外连接

这边回到我们的练习,我们如何使用sql99查询所有员工的last_name,department_name信息:(注意着里面的所有,代表的含义就是外连接),如果要进行左外连接,使用的就是outer join

SELECT last_name,department_name
FROM employees T1
LEFT OUTER JOIN departments T2
ON T1.department_id=T2.department_id

右外连接

这里因为我们内连接不会写方向,所以可以直接把outer给删除掉,如果我们需要的是右外连接,那么就写的是right join

SELECT last_name,department_name
FROM employees T1
RIGHT OUTER JOIN departments T2
ON T1.department_id=T2.department_id

如果用SQL92语法来说的那么加号就

SELECT last_name,department_name
FROM employees T1
RIGHT OUTER JOIN departments T2
ON T1.department_id=T2.department_id(+)

虽然很形象可惜我们mysql不支持,输出结果就如下

我们现在有员工表和部门表,中间是内连接是106条记录,左外连接是107,右外连接是122

我们现在有一个需求是满外连接,也就是要全部查询出来一共123条.

满外连接:

那么满外连接怎么做阿mysql不支持FULL OUTER写法,这里

oracle是支持full的,国内的互联网公司mysql的使用率还是比较高的.mysql的性能还是不错的.不要因为这个原因决定是否使用它,但是企业就用mysql,怎么办呢

很遗憾

现在我们把多表查询的情况全部包含在这里面了.现在我们先把sql99语法.

我们官方规范资料,我们看ANSI做的sql,是有FULLJOIN的案例,只是mysql不支持而已,oracle是全部支持.但是这个述求在我们mysql还是有必要的.就希望能看见查询结果有123条.

也就是说我们在笔试面试中也会遇见的,既然笔试会考,面试会问,

UNION和UNION ALL的使用

优先使用UNION ALL提高效率

使用UNION可以得到多个SELECT语句然后把结果合并成单个结果集,合并时两个表的名字要相同.

这里是否包含ALL,也就是说,我们用的UNION是会去重的得到的结果是1+106+16,如果用的是ALL得到的结果就是1+106+106+16,所以我们使用的时候就要用UNION是吧,但是实际上的情况中,我们能用UNION ALL,它是不用去重的,UNION在连接的情况下,不用去重效率比较高.

那我们如何用union实现满外连接,我们可以用

这两个图来进行union的连接是不是,进而他们两就可以用union all了啊,我们就不用多做一个去重的操作了.我们搭配完之后就会出现外连接了.大家先了解union和union all的区别.

7种join的实现:

1:内连接

SELECT last_name,department_name FROM employees T1 {INNER} JOIN departments T2 ON T1.department_id=T2.department_id

2左外连接

SELECT last_name,department_name FROM employees T1 LEFT {OUTER} JOIN departments T2 ON T1.department_id=T2.department_id

3右外连接

SELECT last_name,department_name FROM employees T1 RIGHT {OUTER} JOIN departments T2 ON T1.department_id=T2.department_id

4:现在实现

是不是抹去的左外连接的中间部分,我们就用WHERE 其中部门表中是NULL

SELECT last_name,department_name FROM employees T1 LEFT {OUTER} JOIN departments T2 ON T1.department_id=T2.department_id WHERE T1.department_id IS NULL

SELECT T1.employee_id,T2.department_id

FROM employees T1 RIGHT JOIN departments T2

ON T1.department_id=T2.department_id

WHERE T1.department_id IS NULL

UNION ALL

SELECT T1.employee_id,T2.department_id

FROM employees T1 LEFT JOIN departments T2

ON T1.department_id=T2.department_id

这就是把左上图和右中图连接起来了

这里需要注意的是,UNION ALL既然是连接,那么你这两个连接的表字段是要一样,而且类型也要一样.

只有这样我们去union 才有意义,

是不是觉得有点悲壮,明明在oracle可以直接使用的我们缺只能在mysql搞这么麻烦才能实现

,我们是不是左中图和右上图也可以实现.

我们试一试 ,

是不是也可以实现,我们这里去掉ALL也是可以实现,但是会有去重的行为,所以还是建议添加ALL

这样我们终于把全部的内容就搞定了,这里我们没有介绍场景,只是把最本质的事情给解决了.我们课后练习这块我们先看看.我们分为两部分,多表查询1:这里面不是主要的服务外连接,虽然也有,多表查询2:就是主要的服务于外连接的7个图.

我们上面的创建2个表

这里我们说明一下sql99的语法新特性.

这里我们教过的已经可以实现这个内容,但是为了我们之后看别人写能看得懂,所以也介绍一下

自然连接

SELECT employee_id,last_name,department_id

FROM employees T1 NATURAL JOIN departments T2

我们发现我们的表有2个链接条件,如果使用自然连接,他会自动查询相同的字段进行等值的连接.

我们员工表和部门表有2个字段是一样的,他会自然而然的把这两个字段连接.

但是因为自然连接不够灵活,比如我们只要一个字段连接,它就会多出来一些记录

using连接

SELECT employee_id,last_name,department_id

FROM employees T1 JOIN departments T2

USING(department_id)

他的作用就是字段相同的时候自动把相同的字段连接起来,但是因为自连接的时候一般连接字段都不一样,所以不适用.括号中必须是同名的字段才行.

我们是不是完全可以不用USING和NATRUAL JOIN 他们合适的时候是很好用的,但是使用的情况比较少.

章节小结:多表连接

我们多表的连接有3种方式,sql92语法中,是在WHERE里写,99语法是在ON的方式里面去写连接或者是USING中去写.

建议是用ON后面加条件来写,

这里有一个小细节,我们3张表中
SELECT T2.department_name,T3.city,T1.last_name,T4.job_title,T1.salary
FROM employees T1 JOIN departments T2 JOIN locations T3 JOIN jobs T4
ON T1.department_id=T2.department_id
AND T2.location_id=T3.location_id
AND T1.job_id=T4.job_id
WHERE T2.department_name='Executive'\我们可以先写JOIN3个表然后再用on写他们的连接条件,这里必须用内连接才能实现,但是建议还是大家join on一个一个写

这里有几个建议:是阿里巴巴的开发条件

超过3个表禁止用join,需要join的字段数据类型保持绝对一致,多表关联查询时保证被关联的字段需要有索引

因为表多的时候join的效率就会差,下篇我们之后再谈,因为我们每加一个join我们相当于for循环的嵌套,我们一般都不会超过3个.

接下来我们先去看这个课后练习题了

【练习题目】

# 1.显示所有员工的姓名，部门号和部门名称。

# 2.查询90号部门员工的job_id和90号部门的location_id

# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city

# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name

# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’ manager Mgr# kochhar 101 king # 7.查询哪些部门没有员工 100

# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式

7.查询哪些部门没有员工

# 8. 查询哪个城市没有部门

# 9. 查询部门名为 Sales 或 IT 的员工信息

【练习答案】

# 1.显示所有员工的姓名，部门号和部门名称。

SELECT T1.last_name,T2.department_id,T2.department_name

FROM employees T1 LEFT JOIN departments T2

ON T1.department_id=T2.department_id

# 2.查询90号部门员工的job_id和90号部门的location_id

SELECT T1.job_id,T2.location_id

FROM employees T1 LEFT JOIN departments T2

ON T1.department_id=T2.department_id

WHERE T1.department_id=90

# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city

SELECT T1.last_name,T2.department_name,T3.city

FROM employees T1 LEFT JOIN departments T2

ON T1.department_id=T2.department_id

JOIN locations T3

ON T2.location_id=T3.location_id

WHERE T1.salary IS NOT NULL

# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name

SELECT T1.last_name,T1.job_id,T2.department_id,T2.department_name

FROM employees T1 LEFT JOIN departments T2

ON T1.department_id=T2.department_id

JOIN locations T3

ON T2.location_id=T3.location_id

WHERE T3.city='Toronto'

# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’

SELECT T2.department_name,T3.city,T1.last_name,T4.job_title,T1.salary

FROM employees T1 LEFT JOIN departments T2

ON T1.department_id=T2.department_id

JOIN locations T3

ON T2.location_id=T3.location_id

JOIN jobs T4

ON T1.job_id=T4.job_id

WHERE T2.department_name='Executive'

# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式

SELECT T1.last_name,T1.employee_id,T2.last_name,T2.employee_id

FROM employees T1 LEFT JOIN employees T2

ON T1.employee_id=T2.manager_id

# 7.查询哪些部门没有员工

SELECT T2.department_id

FROM employees T1 RIGHT JOIN departments T2

ON T1.department_id=T2.department_id

WHERE T1.last_name IS NULL

# 8. 查询哪个城市没有部门

SELECT T1.city

FROM locations T1 LEFT JOIN departments T2

ON T1.location_id=T2.location_id

WHERE T2.department_id IS NULL

# 9. 查询部门名为 Sales 或 IT 的员工信息

SELECT T1.last_name,T1.employee_id,T2.department_name

FROM employees T1 RIGHT JOIN departments T2

ON T1.department_id=T2.department_id

WHERE T2.department_name IN ('Sales','IT')