6-连接查询.md

[TOC]

一。关键字

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

   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)

一、什么是连接查询

二、 连接查询的分类

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

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

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

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

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

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

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

   4. 全连接 (不常用)

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

三、SQL92语法

四、案例分析

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

​

​ A 内连接之等值连接

笛卡尔积现象

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

mysql> select dname,ename from emp,dept;
+------------+--------+
| dname      | ename  |
+------------+--------+
| OPERATIONS | SMITH  |
………………………………………………

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

避免该现象：加筛选条件

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

   # 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. 找出每个员工的薪资登记，要求显示员工名、薪资、薪资登记？

   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)，要求显示员工名和对应的领导名

   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 也全部显示出来

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. 左外连接

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. 查询每个员工的上级领导，要求显示所有员工的名字和领导名？

   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)

五、多张表之间进行连接

select
	xxx
from 
	a
join 
	b
on
	a 和 b 的连接条件
join
	c
on
	a 和 c 的连接
right join 
	d
on
	a 和 d 的连接

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