MySQL数据库学习之连接查询

第三章 连接查询

第一节 内连接

	--等值连接
		案例：要求查询每一个员工所在的部门名，最终显示员工名和部门名
		SQL92语法：mysql> select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
		SQL99语法：mysql> select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
		+--------+----------------+
		| ename  | dname          |
		+--------+----------------+
		| Jack   | sales          |
		| White  | administration |
		| cathy  | planning       |
		| merry  | project        |
		| lucy   | Secretarys     |
		| Hellen | technical      |
		| jenny  | sales          |
		| Andrew | planning       |
		| ford   | sales_2        |
		| Angus  | sales          |
		| Andy   | sales_2        |
		| Jacob  | administration |
		+--------+----------------+
		--非等值连接
		SQL92语法：select e.ename,e.sal,s.sgrade from emp e,salgrade s where e.sal between s.lowsal and s.highsal;
		SQL99语法：select e.ename,e.sal,s.sgrade from emp e join salgrade s on e.sal between s.lowsal and s.highsal;
		+--------+---------+--------+
		| ename  | sal     | sgrade |
		+--------+---------+--------+
		| Jack   | 4000.00 |      3 |
		| White  | 4500.00 |      4 |
		| cathy  | 4000.00 |      3 |
		| merry  | 3500.00 |      2 |
		| lucy   | 2800.00 |      1 |
		| Hellen | 2700.00 |      1 |
		| jenny  | 5500.00 |      6 |
		| Andrew | 6000.00 |      7 |
		| ford   | 2700.00 |      1 |
		| Angus  | 3000.00 |      1 |
		| Andy   | 3500.00 |      2 |
		| Jacob  | 2700.00 |      1 |
		+--------+---------+--------+
		--自连接
		案例：要求查询每一个员工的上级领导名
		SQL92语法：mysql> select a.ename,b.ename as leadername from emp a , emp b where a.mgr=b.empno;
		SQL99语法：mysql> select a.ename,b.ename as leadername from emp a join emp b on a.mgr=b.empno;
		+--------+------------+
		| ename  | leadername |
		+--------+------------+
		| Jack   | ford       |
		| White  | Andrew     |
		| cathy  | merry      |
		| merry  | lucy       |
		| lucy   | cathy      |
		| Hellen | Jack       |
		| jenny  | ford       |
		| ford   | White      |
		| Angus  | Andy       |
		| Andy   | ford       |
		| Jacob  | Angus      |
		+--------+------------+

第二节 外连接

		--左外连接(左连接)
		案例：要求查询每一个员工的上级领导名，要求显示每一个人的情况，包括最高领导(下表Andrew为最高领导)
		SQL99语法：mysql> select a.ename,b.ename as leadername from emp a left join emp b on a.mgr=b.empno;
		+--------+------------+
		| ename  | leadername |
		+--------+------------+
		| Jack   | ford       |
		| White  | Andrew     |
		| cathy  | merry      |
		| merry  | lucy       |
		| lucy   | cathy      |
		| Hellen | Jack       |
		| jenny  | ford       |
		| Andrew | NULL       |
		| ford   | White      |
		| Angus  | Andy       |
		| Andy   | ford       |
		| Jacob  | Angus      |
		+--------+------------+
		--右外连接(右连接)
                每一个左连接都可以转化为右连接

第三节 多表连接

		案例：要求查询每一个员工所在的部门名及薪水等级，最终显示员工名和部门名以及薪水等级
		SQL99语法：
		mysql> select
		-> e.ename,d.dname,s.sgrade
		-> from
		-> emp e
		-> join
		-> dept d
		-> on
		-> e.deptno=d.deptno
		-> join
		-> salgrade s
		-> on
		-> e.sal between s.lowsal and s.highsal;
		+--------+----------------+--------+
		| ename  | dname          | sgrade |
		+--------+----------------+--------+
		| Jack   | sales          |      3 |
		| White  | administration |      4 |
		| cathy  | planning       |      3 |
		| merry  | project        |      2 |
		| lucy   | Secretarys     |      1 |
		| Hellen | technical      |      1 |
		| jenny  | sales          |      6 |
		| Andrew | planning       |      7 |
		| ford   | sales_2        |      1 |
		| Angus  | sales          |      1 |
		| Andy   | sales_2        |      2 |
		| Jacob  | administration |      1 |
		+--------+----------------+--------+