内连接之等值连接

例:查询每个员工所在部门名称,显示员工名和部门名

思路:emp e和dept d表进行连接,条件是e.deptno = d.deptno

SQL92语法:

mysql> select e.ename,d.dname from emp e,dept d where 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)

SQL92的缺点:结构不清晰,表的连接条件和后期进一步筛选的条件,都一同放到了where后面

SQL99语法:

mysql> select e.ename,d.dname from emp e join 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)

SQL99的优点:表连接的条件是独立的,链接之后,如果还需要进一步筛选,再往后继续添加where条件

inner关键字

之所以是称为内连接,是因为有inner关键字,这个关键字是可以省略的,带着inner可读性更好

例:

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

注:因为条件是等量关系,所以被称为等值连接

内连接之非等值连接

例:找出每个员工的薪资等级,要求显示员工名,薪资,薪资等级

mysql> select e.ename,e.sal,s.grade from emp e inner 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)

注:条件不是一个等量关系,称为非等值连接

内连接之自连接

例:查询员工的上级领导,要求显示员工名和对应的领导名

思路:员工领导的编号为mgr,员工的编号为empno,相当于mgr=empno即可,这时候需要使用自连接,即一张表使用两次,相互连接查询

mysql> select a.ename as 'employer',b.ename as 'boss' from emp a inner join emp b on a.mgr = b.empno; 
+----------+-------+
| employer | boss  |
+----------+-------+
| 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)

外连接

先举个例子

mysql> select ename,deptno from emp;
+--------+--------+
| ename  | deptno |
+--------+--------+
| SMITH  |     20 |
| ALLEN  |     30 |
| WARD   |     30 |
| JONES  |     20 |
| MARTIN |     30 |
| BLAKE  |     30 |
| CLARK  |     10 |
| SCOTT  |     20 |
| KING   |     10 |
| TURNER |     30 |
| ADAMS  |     20 |
| JAMES  |     30 |
| FORD   |     20 |
| MILLER |     10 |
+--------+--------+
14 rows in set (0.00 sec)

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

我们知道有4种deptno,然后我们接着使用内连接查询

mysql> select e.ename,d.dname from emp e inner join 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)

我们可以从中发现我们员工的deptno是没有40的,所以我们内连接查询时无法查找出40 | OPERATIONS | BOSTON这个的,但假如我们想让dept中没查找到的也全部显示出来,就需要使用到外连接

右外连接(右连接)

mysql> select e.ename,d.dname from emp e right join dept d 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.00 sec)

注:right表示将join关键字右边的这张表看成主表,主要是为了将这张表的数据全部查询出来,捎带着关联查询左边的表,这样| NULL | OPERATIONS |虽然不是40但也被查出来了

左外连接(左连接)

只需将right的左右查询表互换,right改换为left即可

mysql> select e.ename,d.dname from dept d left join emp e 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.00 sec)

注:

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

outer关键字

在外连接中,在join前可以加上outer关键字,与内连接的inner效果一样,可读性更强,也可以省略

例:

mysql> select e.ename,d.dname from dept d left outer join emp e on e.deptno = d.deptno;

mysql> select e.ename,d.dname from emp e right outer join dept d on e.deptno = d.deptno;

例:查询每个员工的上级领导,要求显示所有员工的名字和领导名

思路:其中KING的上司是NULL,相当于KING是查询不到的,所以应该以KING作为主表

mysql> select a.ename as 'employer',b.ename as 'boss' from emp a left join emp b on a.mgr = b.empno;
+----------+-------+
| employer | boss  |
+----------+-------+
| 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
    ...
from
    a
join
    b
on
    a和b的连接条件
join
    c
on
    a和c的连接条件
right join
    d
on
    a和d的连接条件

注:一条SQL语句中内连接和外连接可以混合使用,都可以出现

例:找出每个员工的部门名称以及工资等级,要求显示员工名,部门名,薪资,薪资等级,(只有内连接)

思路:我们需要三张表的数据,分别为emp表,dept表,salgrade表

mysql> select
    ->  e.ename,e.sal,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  | sal     | dname      | grade |
+--------+---------+------------+-------+
| JAMES  |  950.00 | SALES      |     1 |
| ADAMS  | 1100.00 | RESEARCH   |     1 |
| SMITH  |  800.00 | RESEARCH   |     1 |
| MILLER | 1300.00 | ACCOUNTING |     2 |
| MARTIN | 1250.00 | SALES      |     2 |
| WARD   | 1250.00 | SALES      |     2 |
| TURNER | 1500.00 | SALES      |     3 |
| ALLEN  | 1600.00 | SALES      |     3 |
| FORD   | 3000.00 | RESEARCH   |     4 |
| SCOTT  | 3000.00 | RESEARCH   |     4 |
| CLARK  | 2450.00 | ACCOUNTING |     4 |
| BLAKE  | 2850.00 | SALES      |     4 |
| JONES  | 2975.00 | RESEARCH   |     4 |
| KING   | 5000.00 | ACCOUNTING |     5 |
+--------+---------+------------+-------+
14 rows in set (0.01 sec)

例:找出每个员工的部门名称以及工资等级,还有上级领导,要求显示员工名,领导名,部门名,薪资,薪资等级(有外连接)

mysql> select e.ename,e.sal,d.dname,s.grade,l.ename from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal
    -> left join
    ->  emp l
    -> on
    ->  e.mgr = l.empno;
+--------+---------+------------+-------+-------+
| ename  | sal     | dname      | grade | ename |
+--------+---------+------------+-------+-------+
| SMITH  |  800.00 | RESEARCH   |     1 | FORD  |
| ALLEN  | 1600.00 | SALES      |     3 | BLAKE |
| WARD   | 1250.00 | SALES      |     2 | BLAKE |
| JONES  | 2975.00 | RESEARCH   |     4 | KING  |
| MARTIN | 1250.00 | SALES      |     2 | BLAKE |
| BLAKE  | 2850.00 | SALES      |     4 | KING  |
| CLARK  | 2450.00 | ACCOUNTING |     4 | KING  |
| SCOTT  | 3000.00 | RESEARCH   |     4 | JONES |
| KING   | 5000.00 | ACCOUNTING |     5 | NULL  |        //领导的NULL找出来了
| TURNER | 1500.00 | SALES      |     3 | BLAKE |
| ADAMS  | 1100.00 | RESEARCH   |     1 | SCOTT |
| JAMES  |  950.00 | SALES      |     1 | BLAKE |
| FORD   | 3000.00 | RESEARCH   |     4 | JONES |
| MILLER | 1300.00 | ACCOUNTING |     2 | CLARK |
+--------+---------+------------+-------+-------+
14 rows in set (0.00 sec)

子查询

select语句中嵌套select语句,被嵌套的select语句称为子查询

子查询可以出现的地方

select
    ..(select).
from
    ..(select).
where
    ..(select).

where中的子查询

例:找出比最低工资高的员工姓名和工资

错误做法:

mysql> select ename,sal from emp where sal > min(sal);
ERROR 1111 (HY000): Invalid use of group function

正确思路:

• 先找出最低工资是800

mysql> select min(sal) from emp;
+----------+
| min(sal) |
+----------+
|   800.00 |
+----------+
1 row in set (0.00 sec)

• 找出比800高的员工姓名与工资

mysql> select ename,sal from emp where sal > 800;
+--------+---------+
| ename  | sal     |
+--------+---------+
| 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 |
+--------+---------+
13 rows in set (0.00 sec)

• 合并

mysql> select ename,sal from emp where sal > (select min(sal) from emp);
+--------+---------+
| ename  | sal     |
+--------+---------+
| 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 |
+--------+---------+
13 rows in set (0.00 sec)

注:先执行括号内的select语句