6-连接查询.md

mysql Comments

[TOC]

一。关键字

  1. distinct
    作用：查询结果去重
    位置：查询字段的前方，出现两个字段之前，表示两个字段联合查询。
    —-> 统计岗位数量

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    mysql> select count(job) from emp;
    +------------+
    | count(job) |
    +------------+
    |         14 |
    +------------+
    1 row in set (0.01 sec)

    mysql> select count(distinct job) from emp;
    +---------------------+
    | count(distinct job) |
    +---------------------+
    |                   5 |
    +---------------------+
    1 row in set (0.00 sec)

一、什么是连接查询

image-20221029205555257

二、 连接查询的分类

  1. 根据表连接的方式分类

    1. 内连接 —–> 值一一对应

      1. 等值连接
      2. 非等值连接
      3. 自连接

    2. 外连接
      ==哪个==外连接，表示 ==把哪张表当成主表==

      1. 左外连接（也叫左连接） —>左表为主表
      2. 右外连接（也叫右连接） —>右表为主表

    3. 什么时候使用 内 外连接

      1. 判断两张表是否有 ==主次之分==

    4. 全连接 (不常用)

      1. a 和 b 两张都是主表,

三、SQL92语法

四、案例分析

内连接： 完全能够匹配上这个条件的数据查询出来。( A 和 B 进行连接，A B 没有主次之分 )

​

​ A 内连接之等值连接

笛卡尔积现象

笛卡尔积现象：当两张表进行连接查询，没有任何条件限制的时候，最终查询结果的条数，是两张表条数的乘积，这种现象被称为：笛卡尔积现象。（笛卡尔发现的，这是一个属性现象）

1
2
3
4
5
6
7
8
9
10
11
mysql> select dname,ename from emp,dept;
+------------+--------+
| dname      | ename  |
+------------+--------+
| OPERATIONS | SMITH  |
………………………………………………

| ACCOUNTING | MILLER |
+------------+--------+
56 rows in set (0.01 sec)
=14*4

避免该现象：加筛选条件

  1. 查询每个员工所在部门名称，显示员工名和部门名

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75

    # SQL92写法
    mysql> 
    select 
    	ename,dname
    	#这样子写查询效率会偏低，mysql会 分别去 emp 和 dept 两张表中都去查询这两个字段
    #提高效率
    #	emp.ename,dept.dname;
    from 
    	emp,dept
    	# 起别名 
    	# emp xx, dept xxx
    where 
    	emp.deptno=dept.deptno; and xxx加其他条件

    SQL92的缺点：结构不清晰，表的链接条件，和后期进一步筛选的条件，都放到了 where 后面。

    +--------+------------+
    | ename  | dname      |
    +--------+------------+
    | SMITH  | RESEARCH   |
    | ALLEN  | SALES      |
    | WARD   | SALES      |
    | JONES  | RESEARCH   |
    | MARTIN | SALES      |
    | BLAKE  | SALES      |
    | CLARK  | ACCOUNTING |
    | SCOTT  | RESEARCH   |
    | KING   | ACCOUNTING |
    | TURNER | SALES      |
    | ADAMS  | RESEARCH   |
    | JAMES  | SALES      |
    | FORD   | RESEARCH   |
    | MILLER | ACCOUNTING |
    +--------+------------+
    14 rows in set (0.00 sec)
     
    #SQL99的语法
    mysql> 
    select 
    	ename,dname
    	#这样子写查询效率会偏低，mysql会 分别去 emp 和 dept 两张表中都去查询这两个字段
    #提高效率
    #	emp.ename,dept.dname;
    from 
    	emp
    inner join
    #inner 可省略
    	dept
    	# 起别名 
    	# emp xx, dept xxx
    on 
    	emp.deptno=dept.deptno;
    where
    	xx
    SQL92优点：表连接的条件是独立的，连接之后如果需要进一步筛选，再往后添加where即可。
    +--------+------------+
    | ename  | dname      |
    +--------+------------+
    | SMITH  | RESEARCH   |
    | ALLEN  | SALES      |
    | WARD   | SALES      |
    | JONES  | RESEARCH   |
    | MARTIN | SALES      |
    | BLAKE  | SALES      |
    | CLARK  | ACCOUNTING |
    | SCOTT  | RESEARCH   |
    | KING   | ACCOUNTING |
    | TURNER | SALES      |
    | ADAMS  | RESEARCH   |
    | JAMES  | SALES      |
    | FORD   | RESEARCH   |
    | MILLER | ACCOUNTING |
    +--------+------------+
    14 rows in set (0.00 sec)

    Q:dept中有4条记录，empt中有14条。最终查询的结果条数是14条，但是匹配的过程中，匹配的次数减少了吗？
    A:并没有，还是56次（每次匹配时，都会拿empt中的一条数据去逐一匹配dept中的4条记录。只不过进行了四选一(选符合条件的那条)_）

B 内连接之非等值连接 ——> 条件不是一个等量关系

  1. 找出每个员工的薪资登记，要求显示员工名、薪资、薪资登记？
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    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 |
    +--------+---------+-------+
    14 rows in set (0.00 sec)

C、内连接之自连接 ——>

​ 技巧：一张表看做两张表

  1. 查询员工的上级领导(mgr)，要求显示员工名和对应的领导名
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    select empno,ename,mgr from emp;
    +-------+--------+------+
    | empno | ename  | mgr  |
    +-------+--------+------+
    |  7369 | SMITH  | 7902 |
    |  7499 | ALLEN  | 7698 |
    |  7521 | WARD   | 7698 |
    |  7566 | JONES  | 7839 |
    |  7654 | MARTIN | 7698 |
    |  7698 | BLAKE  | 7839 |
    |  7782 | CLARK  | 7839 |
    |  7788 | SCOTT  | 7566 |
    |  7839 | KING   | NULL |
    |  7844 | TURNER | 7698 |
    |  7876 | ADAMS  | 7788 |
    |  7900 | JAMES  | 7698 |
    |  7902 | FORD   | 7566 |
    |  7934 | MILLER | 7782 |
    +-------+--------+------+
    14 rows in set (0.00 sec)
    用 mgr 的序号，去匹配 empno 得到ename ，然后填入

    -->技巧，看成两张表

    emp a 员工表
    +-------+--------+------+
    | empno | ename  | mgr  |
    +-------+--------+------+
    |  7369 | SMITH  | 7902 |
    |  7499 | ALLEN  | 7698 |
    |  7521 | WARD   | 7698 |
    |  7566 | JONES  | 7839 |
    |  7654 | MARTIN | 7698 |
    |  7698 | BLAKE  | 7839 |
    |  7782 | CLARK  | 7839 |
    |  7788 | SCOTT  | 7566 |
    |  7839 | KING   | NULL |
    |  7844 | TURNER | 7698 |
    |  7876 | ADAMS  | 7788 |
    |  7900 | JAMES  | 7698 |
    |  7902 | FORD   | 7566 |
    |  7934 | MILLER | 7782 |
    +-------+--------+------+
    14 rows in set (0.00 sec)

    emp b领导表
    +-------+--------+------+
    | empno | ename  | mgr  |
    +-------+--------+------+
    |  7369 | SMITH  | 7902 |
    |  7499 | ALLEN  | 7698 |
    |  7521 | WARD   | 7698 |
    |  7566 | JONES  | 7839 |
    |  7654 | MARTIN | 7698 |
    |  7698 | BLAKE  | 7839 |
    |  7782 | CLARK  | 7839 |
    |  7788 | SCOTT  | 7566 |
    |  7839 | KING   | NULL |
    |  7844 | TURNER | 7698 |
    |  7876 | ADAMS  | 7788 |
    |  7900 | JAMES  | 7698 |
    |  7902 | FORD   | 7566 |
    |  7934 | MILLER | 7782 |
    +-------+--------+------+
    14 rows in set (0.00 sec)

    select 
    	a.ename as '员工名', b.ename as '领导名'
    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.01 sec)

    King没有领导。

外连接—-> 存 在 主表(即 主表中不匹配 其他表的项也会被列举出来。)

​ A.右外连接

​ 使用 emp 表中的 deptno 对应 ，dept 表中的 deptno 字段，显示结果匹配的，ename 和 dname 字段，并把 dept 中不匹配的 dname 也全部显示出来 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
mysql> select  
	e.ename,d.dname 
from 
	emp e 
right outer join 
# outer 可以省略
	dept d 
	# right：表示将 join 关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表。没有匹配的使用 null 
on 
	e.deptno=d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| MILLER | ACCOUNTING |
| KING   | ACCOUNTING |
| CLARK  | ACCOUNTING |
| FORD   | RESEARCH   |
| ADAMS  | RESEARCH   |
| SCOTT  | RESEARCH   |
| JONES  | RESEARCH   |
| SMITH  | RESEARCH   |
| JAMES  | SALES      |
| TURNER | SALES      |
| BLAKE  | SALES      |
| MARTIN | SALES      |
| WARD   | SALES      |
| ALLEN  | SALES      |
| NULL   | OPERATIONS |
+--------+------------+
15 rows in set (0.02 sec)

​ B. 左外连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
mysql> 
select  
	e.ename,d.dname 
from 
	emp e 
left outer join 
# outer 可以省略
	dept d 
on 
	e.deptno=d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)

任何一个右连接都有左连接的写法。

任何一个左连接都有右连接的写法。

Q：外连接的查询结果条数一定是 >= 内连接的查询结果条数？

A：正确**

外连接案例

  1. 查询每个员工的上级领导，要求显示所有员工的名字和领导名？
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    mysql> select empno,ename,mgr from emp;
    +-------+--------+------+
    | empno | ename  | mgr  |
    +-------+--------+------+
    |  7369 | SMITH  | 7902 |
    |  7499 | ALLEN  | 7698 |
    |  7521 | WARD   | 7698 |
    |  7566 | JONES  | 7839 |
    |  7654 | MARTIN | 7698 |
    |  7698 | BLAKE  | 7839 |
    |  7782 | CLARK  | 7839 |
    |  7788 | SCOTT  | 7566 |
    |  7839 | KING   | NULL |
    |  7844 | TURNER | 7698 |
    |  7876 | ADAMS  | 7788 |
    |  7900 | JAMES  | 7698 |
    |  7902 | FORD   | 7566 |
    |  7934 | MILLER | 7782 |
    +-------+--------+------+
    14 rows in set (0.00 sec)

    # 1.分析思路 ---> 一张表
    # 2.判断主表 ---> a员工表
    mysql> select a.ename as '员工名',b.ename from emp a left join emp b 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)

五、多张表之间进行连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
select
	xxx
from 
	a
join 
	b
on
	a 和 b 的连接条件
join
	c
on
	a 和 c 的连接
right join 
	d
on
	a 和 d 的连接

一条 sql 中内连接和外连接可以混合，都可以出现。

jhfutureWeb Developer & Designer

一个记录技术分享的网站。