连接查询
三张表如下： mysql> select * from dept; +--------+------------+----------+ | deptno | dname | loc | +--------+------------+----------+ | 10 | accounting | new york | | 20 | research | dallas | | 30 | sales | chicago | | 40 | operations | boston | +--------+------------+----------+ mysql> select * from emp; +-------+--------+-----------+------+------------+---------+---------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+-----------+------+------------+---------+---------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1987-04-02 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1987-09-28 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1987-05-01 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-01-17 | 5000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1987-12-03 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+---------+--------+ mysql> select * from salgrade; +-------+-------+-------+ | grade | losal | hisal | +-------+-------+-------+ | 1 | 700 | 1200 | | 2 | 1201 | 1400 | | 3 | 1401 | 2000 | | 4 | 2001 | 3000 | | 5 | 3001 | 9999 | +-------+-------+-------+
在实际的开发中，数据并不是存在一张表中的，而是同时存在多张表中，联合多张表取得数据的多表查询被称为连接查询，也叫跨表查询。
根据出现的年代，连接查询可分为SQL92，SQL99两种
根据连接的方式，可分为：
1.内连接：
*等值连接
**非等值连接
***自连接
2.外链接：
*左外连接
**右外连接
3.全连接（使用几率很小）
内连接和外连接的区别：内连接：只有两个表相匹配的行才能在结果集中出现 ；外连接：有一张表无条件的显示出来，另外一张表没有与之对应的用空值与之对应。
若两张表进行连接查询时没有任何的限制条件，则会出现笛卡尔积现象，查询结果总数为两张表记录条数额乘积，为了避免此现象发生，必须加上过滤条件
内连接：等值连接
例1：查询每一个员工所在的部门名称，要求显示员工名和对应的部门名
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 | +--------+------------+
SQL99： mysql> select e.ename,d.dname from emp e （inner） join dept d on e.deptno=d.deptno;
SQL92 跟 SQL99的区别：SQL92把连接操作放在了where子句中，如果还要加入过滤操作，需要在后面调用and语法
SQL99则是把连接操作独立了出来，结构相对比较清晰，如果还要加入过滤操作，需要在后面调用where子句
内连接：非等值连接
例2：找出每名员工对应的工资等级，要求显示员工名，工资和工资等级
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 |
【SQL92】：mysql> select e.ename,e.sal,s.grade from emp e，salgrade s where e.sal between s.losal and s.hisal;
内连接：自连接
例3：找出每个员工的上级领导，要求显示员工名和对应的领导名
mysql> select a.ename empname,b.ename bossname from emp a join emp b on a.empno=b.mgr; +---------+----------+ | empname | bossname | +---------+----------+ | FORD | SMITH | | BLAKE | ALLEN | | BLAKE | WARD | | KING | JONES | | BLAKE | MARTIN | | KING | BLAKE | | KING | CLARK | | JONES | SCOTT | | BLAKE | TURNER | | SCOTT | ADAMS | | BLAKE | JAMES | | JONES | FORD | | CLARK | MILLER | +---------+----------+
外连接：左外连接跟右外连接是可以互相转换的
例4：找出每个员工对应的部门名称，要求显示全部部门名称
mysql> select e.ename,d.dname from emp e right （outer） 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 | | NULL | operations | +--------+------------+
等价于：
mysql> select e.ename,d.dname from dept d left （outer） join emp e 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 |
| NULL | operations |
+--------+------------+
例5：找出每个员工对应的部门名称，以及该员工对应的工资等级，要求显示员工名，部门名和工资等级
mysql> 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 | +--------+------------+-------+ 14 rows in set (0.04 sec)
原理：a表和b表进行连接后，再和c表进行连接
inner跟outer都可以省略，因为区分内外连接的关键是语句中是否存在left/right，添加上去只是为了增添可读性！！！
