mysql 联表查询 简书_MySQL-多表查询

多表链接

连接是在多个表之间通过一定的连接条件，使表之间发生关联，进而能从多个表之间获取数据。

语法为

SELECT table1.column, table2.column

FROM table1, table2

WHERE table1.column1 = table2.column2;

笛卡尔积: 第一个表中的所有行和第二个表中的所有行都发生连接。

为了避免笛卡尔积的产生，通常需要在WHERE子句中包含一个有效的连接条件

接下来我们详细学习这些连接条件：

等值连接

select * from emp,dept where emp.deptno=dept.deptno;

image

image

select empno, ename , emp.deptno, loc from emp, dept where emp.deptno=dept.deptno and loc = 'NEW YORK';

限制歧义列名

在用到多个表时可以使用表名作前缀来限定列；

通过使用表前缀可以提高性能；

select emp.empno, emp.ename , emp.deptno, dept.loc from emp, dept where emp.deptno=dept.deptno and loc = 'NEW YORK';

通过使用列的别名可以区分来自不同表但是名字相同的列

select e.empno as 员工编号, e.ename 姓名,d.dname 部门名称

from emp e, dept d where e.deptno=d.deptno;

image

练习：

写一个查询，显示所有员工姓名，部门编号，部门名称。

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

写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金

select e.ename,d.loc,e.comm from emp e,dept d where e.deptno = d.deptno and d.loc = 'CHICAGO' and comm is not null;

写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点。

select e.ename,d.loc from emp e,dept d where e.deptno = d.deptno and e.ename like '%A%';

非等值连接

image

select e.e

name 员工姓名, e.sal 工资 , s.grade 工资等级 from emp e, salgrade s where e.sal between s.losal and s.hisal;

两个表以上的链接

image

多表连接的写法分析

分析要查询的列都来自于哪些表，构成FROM子句;

分析这些表之间的关联关系，如果表之间没有直接关联关系，而是通过另一个中间表关联，则也要在FROM子句中补充中间关联表;

接下来在WHERE子句中补充表之间的关联关系，通常N个表，至少要有N-1个关联关系;

分析是否还有其它限制条件，补充到WHERE子句的表关联关系之后，作为限制条件;

根据用户想要显示的信息，补充SELECT子句。

分析是否有排序要求，如果排序要求中还涉及到其它表，则也要进行第2步补充排序字段所在的表，并且添加表之间的关联关系;

select e.empno 编号,e.ename 姓名,e.sal 员工薪水,s.grade 工资等级,d.loc 所在城市 from emp e,salgrade s,dept d where (e.deptno=d.deptno) and (e.sal between s.losal and s.hisal) order by s.grade;

image

自连接

查询每个员工的姓名和直接上级姓名

select w.ename 员工姓名, m.ename 领导姓名 from emp w, emp m where w.mgr = m.empno;

image

select w.ename 员工姓名 ,w.empno 员工编号,m.ename 领导姓名,m.empno 领导编号 from emp w,emp m,dept d where w.mgr = m.empno and d.deptno = w.deptno and d.loc in('NEW YORK','CHICAGO');

交叉连接

创建一个员工表和部门表的交叉连接。

select e.empno, e.ename, d.dname from emp e cross join dept d;

自然连接

select e.empno, e.ename, d.dname from emp e natural join dept d;

相比于等值连接，等值连接直接指明连接条件， 自然连接是自己找(两个表字段名相同，自动添加)， 自然连接无法控制用哪些字段连接

外连接

左外链接

左外连接以FROM子句中的左边表为基表，该表所有行数据按照连接条件无论是否与右边表能匹配上，都会被显示出来。

查询所有雇员姓名，部门编号，部门名称，包括没有部门的员工也要显示出来

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

image

右外链接

查询所有雇员姓名，部门编号，部门名称，包括没有员工的部门也要显示出来

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

image

创建一个员工表和部门表的交叉连接。

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

使用自然连接，显示入职日期在80年5月1日之后的员工姓名，部门名称，入职日期

select e.ename,d.dname,e.hiredate from emp e natural join dept d where e.hiredate>'1980-05-01';

使用左连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。

select w.ename 员工,m.ename 经理 from emp w left outer join emp m on (w.mgr = m.empno);

使用右连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。

select w.ename 员工,m.ename 经理 from emp m right outer join emp w on (w.mgr = m.empno);

显示员工SMITH的姓名，部门名称，直接上级名称

select e.ename 员工姓名, d.dname 部门名称, h.ename 上级名称 from emp e, emp h,dept d where e.deptno=d.deptno and e.mgr=h.empno and e.ename='SMITH';

显示员工姓名，部门名称，工资，工资级别，要求工资级别大于4级。

select e.ename 员工姓名, d.dname 部门名称, e.sal 工资,s.grade 工资等级 from emp e, salgrade s,dept d where e.deptno=d.deptno and e.sal between s.losal and s.hisal and s.grade>4;

显示员工KING和FORD管理的员工姓名及其经理姓名。

SELECT w.ename 员工姓名,m.ename 领导姓名 FROM emp w,emp m WHERE w.mgr = m.empno AND m.ename in ('KING','FORD');

显示员工姓名，参加工作时间，经理名，参加工作时间，要求参加时间比经理早。

SELECT w.ename 员工姓名,w.hiredate 参加工作时间,m.ename 经理名,m.hiredate 参加工作时间 FROM emp w,emp