尚学堂马士兵Oracle学习笔记之三：表连接

13_表连接.avi

在where子句中进行多表连接(SQL1992)

实际需要在很多表中取数据，

例子：把员工姓名及员工所在部门的名字同时显示出来

Select ename, dnamefrom emp, dept;

第二天上午：

13_表连接--21:41

为什么启动慢？因为每次启动Oracle的服务都会启动（解决办法，把Oracle服务设置成“手动”启动）

 

scott/tiger --> 用户名密码可以直接这样输入

 

把第一天学的内容用一句话总结出来

问：从emp这张表里把平均工资和部门编号列出来，并且过滤掉大写是A的名字 ，把剩下的数据按照部门编号进行分组，分组之后的平均薪水必须大于2000，按照部门编号的倒序排列

select avg(sal), deptno from emp

where ename not like '_A%'

group by deptno

having avg(sal) > 2000

order by deptno

这是一个单条的select语句，(只是从一张表里取数据)第一天已经讲完

 

今天讲：多表的连接

例子1：请你选出雇员的名字，和雇员在部门的名字？

错误写法：select ename, deptno from emp;//这里选的是部门的编号，问题里是让选部门的名字

select dname, deptno from dept where deptno=20;//选出了编号是20的这个人所在部门的名字

正确写法：select ename,dname from emp, dept whereemp.deptno = dept.deptno;

必须明确的指出重复字段是哪个表的例如：

select ename, dname,dept.deptnofrom emp, dept where emp.deptno = dept.deptno;

指定哪张表的deptno实际上对它有一个效率上的影响

 

例子2：求每个人的薪水值包括他的名字

select ename, salfrom emp;

例子3：求每个人的薪水值，名字和他的薪水处于第几个级别(非等值连接)

select ename, sal,grade from emp,salgrade

where sal between losal and hisal;

 

select ename, sal, gradefrom emp,salgrade

where sal >= losal and sal <=hisal;

例子4：求出他的名字，求出他所在部门的名称，求出他的薪水等级

首先分析这几个数据在3张表里

Select ename, dname,grade from emp e, dept d,salgrade s

Where e.deptno =d.deptno and e.sal betweens.losal and s.hisal;

例子5：求出职位除’PRESIDENT‘以外的所有雇员的名字，部门名称，薪水等级

select ename, dname, grade from emp e, deptd, salgrade s

where e.deptno = d.deptno and e.sal>=s.losal and e.sal <= s.hisal and job <> 'PRESIDENT';

 

13_子查询

 

例子1：求谁挣的钱最多？

错误的写法：select ename, max(sal) from emp;

正确的写法：select ename, sal from emp

  wheresal = (select max(sal)from emp);

例子2：求出来有哪些工资位于所有人平均工资之上

 selectename, sal from emp

where sal > (select avg(sal) from emp);

例子3：按照部门进行分组之后挣钱最多的那个人的名字，部门编号？

select ename, sal, t.deptno from emp

join(select max(sal) max_sal, deptno fromempgroup by deptno) t

on (emp.sal = t.max_sal and emp.deptno=t.deptno);

****理解子查询的关键-->把它当成一张表

例子4：(练习)求每个部门的平均薪水等级是多少？

Select t.avg_sal, grade, t.deptnofromsalgrade s

Join( select avg(sal) avg_sal, deptno fromempgroup by deptno) t

On ( t.avg_sal between s.losal and s.hisal);

14_self_table_connection.avi

 

例子1：求这个人的名字和他经理人的名字(自连接)

select e1.ename, e2.ename from emp e1, empe2where e1.mgr = e2.empno;

自连接：为同一张表起不同的别名，然后当成两张表来用

 

15_SQL1999_table_connections.avi

 

SQL1992是在where语句里直接写表连接的条件

有一个小小的问题：

select ename, dname, grade from emp e, deptd,salgrade s

where e.deptno = d.deptno and e.salbetweens.losal and s.hisal and -->两个表的连接条件

job <> 'CLERK';  -->过滤条件

连接条件和过滤条件混在一起让人读起来SQL语句会困难一些，不太容易清楚

怎么把连接条件和过滤条件分开来呢？

原来的交叉连接：

select ename, dname from emp, dept; -->笛卡尔乘积56行

1999年标准的语法：（写法）

select ename, dename from empcross join dept; crossjoin --> 叫做交叉连接(新语法定义的比较明确)

原来的等值连接：

select ename, dname from emp, deptwhereemp.deptno = dept.deptno;

新语法的等值连接：

select ename, dname from emp join dept on(emp.deptno = dept.deptno);

等值连接的简单的写法：

select ename, dname from emp join dept using(deptno)

using (deptno) --> 是说我这个表的deptno等于你这个表的deptno

* using的用法不推荐使用-->两张表中要有相同的字段，类型必须相同

 

非等值连接：

例子1：取出雇员名称和薪水等级

select ename, grade from emp e

join salgrade s

on (e.sal between s.losal and s.hisal);

三个表连接在一起的：

例子2：取出雇员名字，他的部门名称，和薪水等级其中名字第二个字母包含A的不要取出

select ename, dname, grade from

emp e join dept d on (e.deptno = d.deptno)

join salgrade s on (e.sal between s.losalands.hisal)

where ename not like '_A%';

例子3：自连接新语法求这个人的名字，他经理人的名字

select e1.ename, e2.ename from emp e1 joinempe2 on (e1.mgr = e2.empno);

King如果想显示出来该怎么办呢？

外连接：

左外连接：可以把左边这张表的多余的数据（不能产生连接的数据给拿出来）

选出雇员名字和它经理人的名字(可以将没有经理人的那个人取出来)

select e1.ename, e2.ename from emp e1left join emp e2on(e1.mgr= e2.empno);

 

例子4：求：每个雇员的名字，他所在部门的名称，全部选出来，并且把多余的部门也选出来？

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

 

全外连接：即把左边的多余的数据拿出来，又把右边的多余的数据拿出来，

例：select ename, dname from emp efull join deptdon(e.deptno = d.deptno);

 

16_部门平均薪水的等级.avi

1.求部门平均薪水等级？

select deptno, avg_sal, grade from

(select deptno, avg(sal) avg_sal fromempgroup by deptno) t

join salgrade s on (t.avg_sal betweens.losaland s.hisal);

2.求部门中哪些人的薪水最高

select ename, sal from emp

join (select max(sal) max_sal, deptno fromempgroup by deptno) t

on (emp.sal = t.max_sal and emp.deptno=t.deptno);

 

17_部门平均的薪水等级.avi

例子1:求出每个人的薪水等级，然后再平均求出的就是平均薪水等级

select deptno, ename, grade from emp

join salgrade s on (emp.sal betweens.losaland s.hisal);

 

select deptno, avg(grade) from

(select deptno, ename, grade from emp

join salgrade s

on (emp.sal between s.losal and s.hisal)) t

group by deptno;

 

18_哪些人是经理.avi

select ename from emp where empno in(selectmgr from emp);

select ename from mep where empno in(selectdistinct mgr from emp);

 

19_不用组函数求最高薪水.avi

(面试题)，考虑使用“自连接”：

select distinct sal from emp where sal notin

(select distinct e1.sal from emp e1

join emp e2

on (e1.sal < e2.sal)

);

20_平均薪水最高的部门编号与名称.avi

1.先求每个部门的平均薪水

select avg(sal), deptno from emp groupbydeptno;

2.拿出最高的值

select max(avg_sal) from

(select avg(sal) avg_sal, deptno fromempgroup by deptno);

 

结果：

select deptno,avg_sal from

(select avg(sal) avg_sal, deptnofrom empgroup by deptno)

where avg_sal =

(select max(avg_sal) from

(select avg(sal) avg_sal, deptnofromemp group by deptno)

)

21_embedded_group_functions.avi

select deptno, avg_sal from

(select avg(sal) avg_sal, deptno fromempgroup by deptno)

where avg_sal =

(select max(avg(sal)) from emp groupbydeptno) -->组函数可以嵌套，但最多只能嵌套两层。

 

22_平均薪水的等级最低的部门名称.avi

 

1.先求平均薪水

select avg(sal) from emp group by deptno;

2.求平均薪水的等级

把上面看成是一张表，另外一张表与它做连接

select deptno, grade, avg_sal from

(select deptno, avg(sal) avg_sal fromempgroup by deptno) t

join salgrade s on (t.avg_sal betweens.losaland s.hisal);

3.取出最低等级

select min(grade)from

(

select deptno, grade, avg_sal from

(select deptno, avg(sal)avg_salfrom emp group by deptno) t

join salgrade s on (t.avg_salbetweens.losal and s.hisal)

);

最后结果：

select dname,t1.deptno, avg_sal,grade from

(

select avg_sal, grade, deptno from

(

(select avg(sal) avg_sal, deptnofrom empgroup by deptno) t

join salgrade s

on (t.avg_sal between s.losal ands.hisal)

)

) t1

join deptd on (t1.deptno =d.deptno)

wheret1.grade =

(

select min(grade) from

(

select avg_sal, grade, deptno from

(

(select avg(sal) avg_sal, deptnofrom empgroup by deptno) t

join salgrade s

on (t.avg_sal between s.losal ands.hisal)

)

)

);

 

23_view.avi

权限问题：

connsys/bjsxtas sysdba;

grantcreatetable, create view to scott;

connscott/tiger

创建视图：

createviewv$_dept_avg_sal_infoas

select deptno, grade, avg_sal from

(select deptno, avg(sal) avg_sal fromempgroup by deptno) t

join salgrade s on (t.avg_sal betweens.losaland s.hisal);

视图是什么东西？

 就是一个子查询或者就是一张表,视图中的表叫虚表，实际数据依然在实际当中的表里面

从视图里面取数据：

select * from v$_dept_vag_sal_info;

视图的作用：首先建一个虚表，给一个别名，然后在虚表的基础上取数据就方便多了

 

第二天下午：

 

24_比普通员工的最高薪水还要高的经理人名称.avi

例子1：求比普通员工的最高薪水还要高的经理人名称

先求普通员工的最高薪水？

select max(sal) from emp where empno not in

(select distinct mgr from emp where mgr isnotnull);

求比这个值还要大而且他本身必须是经理人

select ename from emp

where empno in (select mgr from emp wheremgris not null)

and

sal >

(

select max(sal) from emp where empno notin

 (select distinct mgr from emp where mgr isnotnull);

)

 

25_create_new_user_and_insert.avi

 

之前学的select语句全部掌握住

--面试题：比较效率

select * from emp where deptno = 10 andenamelike '%A%';

select * from emp where ename like '%A%'anddeptno = 10;

先比较数字比较快一些，只要数字不对，后面就不用看了，只有数字相同的

情况下才比较后面的字符串这其实有点像短路的那个意思

类似前面学的短路与和短路或。这两条语句放到Oracle里面执行，Oracle很可能

对他进行优化，很可能把后面的语句放到前面去了。