多表查询

多表查询

语法:
select 

from

join...on...join...on...join...on... 

where
举例理解:
select a.ename,b.dname,c.grade from emp a left join dept b on a.deptno=b.deptno join salgrade c on a.sal between c.losal and c.hisal;
解释:emp a 先与 dept b 进行外连接查询,查询出之后的表再与 salgrade c 进行内连接查询。

注:
什么时候需要外连接查询,什么时候不需要,并且注意哪一张表作为主表,哪张表是副表,使用左连接与右连接要灵活。

示例一:找出每位员工的部门名称和工资等级

员工部门编号,员工名称,月薪:
mysql> select empno,ename,sal from emp;
+-------+--------+---------+
| empno | ename  | sal     |
+-------+--------+---------+
|  7369 | SMITH  |  800.00 |
|  7499 | ALLEN  | 1600.00 |
|  7521 | WARD   | 1250.00 |
|  7566 | JONES  | 2975.00 |
|  7654 | MARTIN | 1250.00 |
|  7698 | BLAKE  | 2850.00 |
|  7782 | CLARK  | 2450.00 |
|  7788 | SCOTT  | 3000.00 |
|  7839 | KING   | 5000.00 |
|  7844 | TURNER | 1500.00 |
|  7876 | ADAMS  | 1100.00 |
|  7900 | JAMES  |  950.00 |
|  7902 | FORD   | 3000.00 |
|  7934 | MILLER | 1300.00 |
+-------+--------+---------+
14 rows in set (0.00 sec)

部门编号,部门名称:
mysql> select deptno,dname from dept;
+--------+------------+
| deptno | dname      |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
|     30 | SALES      |
|     40 | OPERATIONS |
+--------+------------+
4 rows in set (0.00 sec)


mysql> select a.ename,b.dname from emp a left join dept b on a.deptno=b.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 a.ename,b.dname,c.grade from emp a left join dept b on a.deptno=b.deptno join salgrade c on a.sal between c.losal and c.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.00 sec)

示例二:出每位员工的部门名称,工资等级,以及上级领导

mysql> select a.ename as '员工名',b.ename as '领导名',c.dname,d.grade from emp a left join emp b on a.mgr=b.empno join dept c on a.deptno=c.deptno join salgrade d on a.sal between d.losal and d.hisal;
+-----------+-----------+------------+-------+
| 员工名    | 领导名    | dname      | grade |
+-----------+-----------+------------+-------+
| SMITH     | FORD      | RESEARCH   |     1 |
| ALLEN     | BLAKE     | SALES      |     3 |
| WARD      | BLAKE     | SALES      |     2 |
| JONES     | KING      | RESEARCH   |     4 |
| MARTIN    | BLAKE     | SALES      |     2 |
| BLAKE     | KING      | SALES      |     4 |
| CLARK     | KING      | ACCOUNTING |     4 |
| SCOTT     | JONES     | RESEARCH   |     4 |
| KING      | NULL      | ACCOUNTING |     5 |
| TURNER    | BLAKE     | SALES      |     3 |
| ADAMS     | SCOTT     | RESEARCH   |     1 |
| JAMES     | BLAKE     | SALES      |     1 |
| FORD      | JONES     | RESEARCH   |     4 |
| MILLER    | CLARK     | ACCOUNTING |     2 |
+-----------+-----------+------------+-------+
14 rows in set (0.00 sec)
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页