第六章 多表连接（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<m.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

来源：简书

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

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 206,723评论 6赞 481
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 88,485评论 2赞 382
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 152,998评论 0赞 344
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 55,323评论 1赞 279
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 64,355评论 5赞 374
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,079评论 1赞 285
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,389评论 3赞 400
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,019评论 0赞 259
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 43,519评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,971评论 2赞 325
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,100评论 1赞 333
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,738评论 4赞 324
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,293评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,289评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,517评论 1赞 262
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,547评论 2赞 354
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,834评论 2赞 345