MYSQL中的SQL语句----连接查询_自连接查询的sql语句-CSDN博客

本文链接：https://blog.csdn.net/qq_52585260/article/details/126096424

什么是连接查询

在实际开发中，大部分情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结构。一般一个业务会对应多张表，比如：学生和班级，单表存放会产生数据的冗余。

连接查询的分类

根据语法出现的年代：

        SQL92 SQL99

根据连接方式：

        内连接

等值连接

非等值连接

自连接

外连接

左外连接（左连接）

右外连接（右连接）

        全连接（很少用）

表的连接查询方面有一种现象：笛卡尔乘积现象

当两张表进行连接查询的时候，若没有任何条件的限制，最终的查询结果条数将会是两张表记录条数的乘积。

如何避免：加条件进行过滤。

注意：避免了笛卡尔积现象，会减少匹配的次数吗？

并不会，底层还是会进行原次数的匹配，只不过只显示出有效记录。

一、内连接

1、内连接之等值连接：最大的特点是条件是等量关系

语法：

例：查询每个员工的部门名称，要求显示与员工名和部门名

SQL92：（老语法，用的少）

mysql> select
    -> e.ename,d.dname
    -> from
    -> emp e,dept d
    -> where
    -> e.deptno=d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
+--------+------------+

SQL99：（常用）join.....on语法

mysql> select
    -> e.ename,d.dname
    -> from
    -> emp e
    -> join
    -> dept d
    -> on
    -> e.deptno=d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
+--------+------------+

select

对象

from

表1

(inner) join

表2

on

连接条件

where

过滤条件在SQL99中，把过滤条件和表连接条件分开了

2、内连接之非等值连接：最大的特点是连接条件中的关系是非等值关系

例：找出每个员工的工作等级，要求显示员工名、工资、工资等级

mysql> select e.ename,e.sal,s.grade
    -> from emp e
    -> join salgrade s
    -> on e.sal between s.losal and s.hisal;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+

3、内连接之自连接：最大特点是一张表看作两张表，自己连自己。

例：找出每个员工的上级领导，要求显示员工名和对应的领导名。

mysql> select a.ename '员工名' ,b.ename '领导名'
    -> from emp a
    -> join emp b
    -> on a.mgr = b.empno;
+--------+--------+
|  员工名 | 领导名 |
+--------+--------+
| SMITH  | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+
13 rows in set (0.00 sec)

二、外连接

什么是外连接？和内连接有什么区别？

内连接：

假设A和B表进行连接，使用内连接的话，凡是A 和B表能够匹配上的记录查询出来。AB两张表没有主副之分。

外连接：

假设A和B表进行连接，使用外连接的话，AB表中有一张是主表，一张表是副表，主要查询主表中的数据，当副表中的数据没有和主表匹配上，副表中会自动生成NULL与之匹配。最重要的特征是会把主表的数据全部找出来。

例：找出每个员工的上级领导（每个员工都需要查出来）

mysql> select a.ename,b.ename
    -> from emp a
    -> left outer join emp b     //用left 或者 right来指示外连接
    -> on a.mgr = b.empno;
+--------+-------+
| ename  | ename |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| KING   | NULL  |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+
14 rows in set (0.00 sec)

例：找出哪个部门没有员工

mysql> select a.dname from dept a left join emp b on a.deptno = b.deptno where b.ename is null;
+------------+
| dname      |
+------------+
| OPERATIONS |
+------------+
1 row in set (0.00 sec)

mysql> select
    -> a.*
    -> from
    -> dept a
    -> left join
    -> emp b
    -> on
    -> a.deptno = b.deptno
    -> where
    -> b.ename is null;
+--------+------------+--------+
| DEPTNO | DNAME      | LOC    |
+--------+------------+--------+
|     40 | OPERATIONS | BOSTON |
+--------+------------+--------+
1 row in set (0.00 sec)

三、三张表怎么连接查询

例：找出每一个员工的部门名称及工资等级

select 
    e.ename,d.dname,s.grade
from
    emp e
join
    dept d
on
    e.deptno = d.deptno
join 
    salgrade s
on 
    e.sal between s.losal and s.hisal;

+--------+------------+-------+
| ename  | dname      | grade |
+--------+------------+-------+
| SMITH  | RESEARCH   |     1 |
| ALLEN  | SALES      |     3 |
| WARD   | SALES      |     2 |
| JONES  | RESEARCH   |     4 |
| MARTIN | SALES      |     2 |
| BLAKE  | SALES      |     4 |
| CLARK  | ACCOUNTING |     4 |
| SCOTT  | RESEARCH   |     4 |
| KING   | ACCOUNTING |     5 |
| TURNER | SALES      |     3 |
| ADAMS  | RESEARCH   |     1 |
| JAMES  | SALES      |     1 |
| FORD   | RESEARCH   |     4 |
| MILLER | ACCOUNTING |     2 |
+--------+------------+-------+

例：找出每一个员工的部门名称及工资等级以及上级领导

select 
    a.ename,d.dname,s.grade,b.ename
from
    emp a
join 
    dept d
on 
    a.deptno = d.deptno
join
    salgrade s
on
    a.sal between s.losal and s.hisal
left join 
    emp b
on 
    a.mgr = b.empno;

+--------+------------+-------+-------+
| ename  | dname      | grade | ename |
+--------+------------+-------+-------+
| SMITH  | RESEARCH   |     1 | FORD  |
| ALLEN  | SALES      |     3 | BLAKE |
| WARD   | SALES      |     2 | BLAKE |
| JONES  | RESEARCH   |     4 | KING  |
| MARTIN | SALES      |     2 | BLAKE |
| BLAKE  | SALES      |     4 | KING  |
| CLARK  | ACCOUNTING |     4 | KING  |
| SCOTT  | RESEARCH   |     4 | JONES |
| KING   | ACCOUNTING |     5 | NULL  |
| TURNER | SALES      |     3 | BLAKE |
| ADAMS  | RESEARCH   |     1 | SCOTT |
| JAMES  | SALES      |     1 | BLAKE |
| FORD   | RESEARCH   |     4 | JONES |
| MILLER | ACCOUNTING |     2 | CLARK |
+--------+------------+-------+-------+