Mysql两张表连接查询

什么是连接查询？

在实际的开发中，大部分的情况下都不是单表中查询数据，一般都是多张表联合查询取出最终的结果。
在实际开放中，一般一个业务都会对一个多张表，比如：学生和班级，起码是两张表。学生和班级信息存储到一张表中，数据会存在大量的重复，导致出现数据的冗余，浪费磁盘空间。

连接查询的分类？

根据语法的出现年代来划分的话，包括：
SQL92(一些老的DBA可能还在使用这种语法。DBA：DataBase Administartor，数据库管理员)
SQL99(比较新的语法)
根据表的连接方式来划分，包括：
内连接：等值连接、非等值连接、自连接
外连接：左外连接(左连接)、右外连接(右连接)
全连接（很少用）

 

表的别名

select e.ename,d.dname from emp emp e,dept d;

表的别名有什么好处？
第一：执行效率高。
第二: 可读性好。

 

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

案例：查询每个员工的名称，要求显示员工名和部门名。
SQL92:(太老了，不用了)

select
    e.ename,d.dname
from
    emp e,dept d
where
     e.deptno = d.deptno;

SQL99:(常用的)

语法：
...
    A
inner join
    B
on
    连接条件
where
    ...

SQL99的语法更清晰一些，表的连接条件和后来的where条件分离了
inner可以省略，带着inner目的是可读性好一些。

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

执行sql

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      |
+--------+------------+
14 rows in set (0.00 sec)

 

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

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

mysql> select ename,sal from emp;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
14 rows in set (0.00 sec)

工资等级

mysql> select * from salgrade;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

sql 语句

select
    e.ename,e.sal,s.grade
from
    emp e
join
    salgrade s
on
e.sal between s.losal and hisal;

sql 执行结果

mysql> select
    ->     e.ename,e.sal,s.grade
    -> from
    ->     emp e
    -> join
    ->     salgrade s
    -> on
    -> e.sal between s.losal and 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)

内连接的中的自连接：最大的特点是：一张表看成两张表，自己连自己。

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

//员工号、员工姓名、上级领导。是员工表也是领导表。
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)

原本的领导编号 = 等于领导的员工编号
sql 语句

select
     a.ename as '员工名',b.ename as '领导名'
from
  emp a
join
 emp b
on
a.mgr = b.empno;

执行sql 指令

mysql> 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.00 sec)

外连接？(以后的开发过程中，外连接用的多)

什么是外连接和内连接有什么区别？
内连接：假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询处理。AB两张表没有主副关系。
外连接：假设A和B表进行连接，使用外连接的话，AB两张表有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。

外连接分类？
左外连接（左连接）：表示左边的这张表是主表。
右外连接（右连接）：表示右边的这张表是主表。
左连接有右连接的写法，右连接也有左连接的写法。

案例：找出所有员工的上级领导？

内连接：13条记录

select
     a.ename as '员工名',b.ename as '领导名'
from
  emp a
join
 emp b
on
a.mgr = b.empno;

外连接最重要的特点是：主表的数据无条件的全部查询出来。
outer 可以省略，带着outer目的是可读性好一些。

外连接：14条记录，左连接

select
     a.ename as '员工名',b.ename as '领导名'
from
  emp a
left outer join
 emp b
on
a.mgr = b.empno;

外连接：14条记录，右连接

select
     a.ename as '员工名',b.ename as '领导名'
from
  emp b
right join
 emp a
on
a.mgr = b.empno;

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

select
    d.*
from
    emp e
right join
    dept d
on
    e.deptno = d.deptno
where
    e.empno is null;

sql 执行

mysql> select
    ->     d.*
    -> from
    ->     emp e
    -> right join
    ->     dept d
    -> on
    ->     e.deptno = d.deptno
    -> where
    ->     e.empno is null;
+--------+------------+--------+
| deptno | dname      | loc    |
+--------+------------+--------+
|     40 | OPERATIONS | BOSTON |
+--------+------------+--------+
1 row in set (0.01 sec)