mysql表与表之间怎么连接6_第六章 多表连接（6课时 0917）

多表连接初步

引出

•思考如下问题？

–写一条查询语句，查询员工姓名、部门名称、工作地点？

写一个查询显示员工姓名 员工编号 部门名称 地址

笛卡尔积

select emp.ename,emp.empno,emp.deptno,dept.deptno,dept.dname

from emp,dept

order by ename

--上述情况叫笛卡尔积现象

等值连接

利用关联连接相等漏选掉多余数据

--多表连接实际上是利用意义相同的字段相同来漏选掉多余数据

select ename,empno,dname

from emp,dept

where emp.deptno=dept.deptno

order by ename

限制歧义别名

用 表名.列名

select ename,empno,emp.deptno,dept.deptno,dname

from emp,dept

where emp.deptno=dept.deptno

order by ename

练习1：

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

select ename,emp.deptno,dept.dname

from emp,dept

where emp.deptno=dept.deptno

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

SELECT ename,loc,comm

from emp,dept

where  emp.DEPTNO=dept.DEPTNO

and loc='CHICAGO'

and comm is not null;

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

SELECT ename,loc FROM emp,dept

WHERE emp.deptno=dept.deptno

and INSTR(ename,'a')>0;

SELECT ename,loc FROM emp,dept

WHERE emp.deptno=dept.deptno

and ename like '%A%';

表别名

#可以为表定义别名,上面的语句可以精简为：

#两种写法 tablename as 别名  |tablename 别名

SELECT ename,loc,e.deptno

FROM emp as e,dept as d

WHERE e.deptno=d.deptno

and ename like '%A%';

多于两个表的连接及非等值连接

#查询员工编号，员工姓名，工作岗位名称，工作地点，月工资，薪资等级

select e.empno,e.ename,e.job,d.LOC,e.sal,s.GRADE

from emp e,dept d,salgrade s

where e.deptno=d.DEPTNO

and e.sal BETWEEN s.LOSAL and s.HISAL;

多表连接的写法

1、分析查询结果都来自哪些表 产生from子句

2、分析表之间的关联条件 产生where子句(注意重复字段问题)

如果还有其他的限制条件上，就继续向where子句中追加

3、如果涉及排序的，把order by写上

4、分析要显示哪些字段 产生select语句

自身连接

•思考：查询每个员工的姓名和直接上级姓名？(把自己分身，利用表别名)

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

#这种自已跟自己连接的写法叫自身连接

select e.ename 员工姓名,m.ENAME 主管姓名

from emp e,emp m

where e.mgr=m.empno;

sql99写法

美国国家标准协会(ANSI)的SQL：1999标准的连接语法。

交叉连接(笛卡尔积 了解)

select e.*,d.*

from emp e,dept d;

#sql99写法

select e.*,d.*

from emp e

cross joindept d;

例子：

a表 学生表(a,b,c,c)

b表 课程(java,oracle,jquery,c)

每个班的学生的选课系统

NATURAL JOIN 自然连接(了解，两种写法对比写)

缺点：无法控制用哪些字段连接。

两个表里字段名相同的，自动加到连接条件里。

select e.*,d.*

from emp e,dept d

where e.deptno=d.deptno;

等价sql99写法

select e.*,d.*

from emp e

NATURAL join dept d;

自然连接时是利用字段名称和类型都相同的字段进行连接。

用using子句(建议了解)

(可以控制用哪些字段进行连接)

用using子句的写法，指定用哪个字段将两个表连接起来。

select e.*,d.*

from emp e

join dept d using (deptno);

外连接(重点掌握)

引出：查询所有员工的姓名及其主管姓名，没有主管的也要显示出来

左外连接

(以左边为基准，显示左边所有数据，如果右边没有对应数据，就补充一个空数据)

from emp e,emp m

查询所有员工的姓名及其主管姓名，没有主管的也要显示出来

select e.ENAME,m.ENAME

from emp e

LEFT OUTER JOIN emp m

on e.mgr=m.empno;

结果是：没有主管的也能显示出数据

右外连接

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

select e.ENAME,d.DEPTNO,d.DNAME

from emp e

right join dept d

on e.deptno=d.deptno;

where连接条件注意事项

–在 WHERE子句中书写连接条件。

–如果在多个表中出现相同的列名，则需要使用表名作为来自该表的列名的前缀。

–N个表相连时，至少需要N－1个连接条件。

练习4

•使用SQL-99写法，完成如下练习

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

select e.*,d.*

from emp e cross join dept d;

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

select e.ENAME,d.DNAME,e.HIREDATE

from emp e

NATURAL join dept d

where e.hiredate>'1980-05-01';

作业

练习2

•1.查询每个员工的编号，姓名，工资，工资等级，所在工作城市，按照工资等级进行升序排序。

select empno,ename,sal,salgrade.grade,dept.loc

from emp,dept,salgrade

where emp.deptno=dept.deptno

and sal between losal and hisal

order by salgrade.grade

练习3

•1.查询所有工作在NEW YORK和CHICAGO的员工姓名，员工编号，以及他们的经理姓名，经理编号。

select e.ename,e.empno,m.ename,m.empno,loc

from emp e,emp m,dept d

where e.mgr = m.empno

and e.deptno=d.deptno

and (loc = 'NEW YORK' or loc = 'CHICAGO')

练习4

3.使用USING子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点

select ename,dname,loc

from emp

join dept

using(deptno)

where loc = 'CHICAGO'

4.使用ON子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点，薪资等级

select ename,dname,loc,grade

from emp

join dept

on emp.deptno = dept.deptno

join salgrade

on sal between losal and hisal

where loc = 'CHICAGO'

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

select e.ename,m.ename

from emp e

left join emp m

on e.mgr = m.empno

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

select  e.ename,m.ename

from emp e left join emp m

on e.mgr=m.EMPNO

课后作业

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

SELECT e.ename,m.ename,d.dname

FROM emp e,emp m,dept d

WHERE e.mgr=m.empno

AND e.deptno=d.DEPTNO

AND e.ename='SMITH';

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

select e.ename,d.dname,e.sal,s.GRADE

from emp e,dept d,salgrade s

where e.deptno=d.deptno and e.sal BETWEEN s.LOSAL and s.HISAL and

grade>4

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

SELECT e.ENAME 员工名,m.ename 经理名

from emp e

LEFT JOIN emp m

ON e.MGR=m.empno

WHERE m.ename in('KING','FORD');

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

select  e.ename 员工姓名,e.HIREDATE 员工参加工作时间,m.ENAME 经理姓名,m.HIREDATE 经理参加工作时间

from emp e,emp m

where e.mgr=m.empno

and e.HIREDATE

第3题后续可以用子查询(学完子查询再看)

select e.empno,e.ename,m.ename

from emp e,emp m

where

e.mgr=m.empno  and e. mgr in(select empno from emp where ename in('KING','FORD') )

#or (e.mgr=m.empno and e.empno in(select mgr from emp where ename='FORD' ))

分析步骤

-- 显示经理为king或FORD的员工的姓名和经理姓名

1)查询姓名为king或FORD的员工编号

select EMPNO

from emp

where ename in('king','FORD')

2)上面查询所得的员工编号作为in语句的值

select *

from emp e,emp m

where e.mgr=m.empno

-- 7839 7902

and e.mgr in(select EMPNO

from emp

where ename in('king','FORD'))

转至：↓↓↓

链接：https://www.jianshu.com/p/4cd2dcca0051

来源：简书

著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。