oracle3

from where group byhaving select order by

 

子查询

非关联子查询

单列子查询

t1表里的列和t2表里的列没有写成条件表达式

t1.c1 = t2.c2

select

from t1

where c1 > (select c2from t2 ...)

先执行子查询,当返回多条记录时,系统自动去重,应该选择多值运算符如in.

=表达single-rowsubquery 单行子查询

ORA-01427: single-rowsubquery returns more than one row

单行子查询返回多行

 

子查询

非关联子查询

多列子查询

哪些员工的工资等于本部门的平均工资

where (deptno,salary) in(select deptno,round(avg(salary))...)

 

子查询

关联子查询

t1表里的列和t2表里的列写成where条件表达式

查询从主表中依次取得记录,子查询根据主表的记录执行.因此子查询会执行多遍.

 

exists 存在 notexists 不存在

exists只要找到一条符合条件的记录立即返回,主表的记录进入结果集,所有的记录多不符合条件返回,主表的记录被过滤.

not exists只要找到一条符合条件的记录立即返回,主表的记录被过滤.所有的记录都不符合where之后的条件,说明notexists成立,主表的记录进入结果集.

 

集合操作

用集合运算符将多个select语句连接起来,将结果集看成集合

要求select语句是同构的,列的个数以及数据类型一致

并集

union (去重记录)union all(不去重记录)

将以下写法改为case when

并集

select ename,deptno,salary*1.1

from emp_hiloo

where deptno = 10

union all

selectename,deptno,salary*1.2

from emp_hiloo

where deptno = 20

union all

selectename,deptno,salary

from emp_hiloo

where deptno not in(10,20)

 

 

交集

intersect(去重记录)

select deptno fromdept_hiloo

intersect

select deptno fromemp_hiloo

 

minus(去重记录),select的顺序改变了结果集不一样

select deptno fromdept_hiloo

minus

select deptno fromemp_hiloo

 

多表查询(表连接)

三种连接方式有三种生成结果集的方式

cross join (交叉连接) 笛卡尔积

从t1表中任取一条记录和t2表中任取一条记录组合放入结果集,最后的个数为m*n,m为t1的记录数,n为t2的记录数

 

inner join  (内连接)

两张表的任意一条记录要想出现在结果集中,必须在另一张表中根据on条件找到匹配的记录.如果解决的是匹配问题,用inner join实现.

on e.deptno = d.deptno 等值连接

ORA-00905: missing(丢失)keyword(关键字)

谁做驱动表不影响结果集

outer join (外连接)

left表示左边的表必须做驱动表

right表示右边的表必须做驱动表

驱动表里的记录在结果集里一个都不能少

from t1 left join t2

on t1.c1. = t2.c2

外连接结果集=内连接的结果集 + t1表中匹配不上的记录和t2表中的一条null记录的组合

from t1 right join t2

on t1.c1. = t2.c2

外连接结果集=内连接的结果集 + t2表中匹配不上的记录和t1表中的一条null记录的组合

from t1 full join t2

on t1.c1. = t2.c2

外连接结果集=内连接的结果集 + t1表中匹配不上的记录和t2表中的一条null记录的组合+t2表中匹配不上的记录和t1表中的一条null记录的组合

 

什么情况下用外连接?

1 员工名称领导名称,包括zhangsanfeng,除了匹配的记录,匹配不上的记录也要出现在结果集中.

2 哪些人不是领导?否定问题,结果集中只要匹配不上的记录

 outer join + where 匹配表.pk列 isnull  <=> not exists not in

 

子查询in not in

子查询 exists not exists

表连接 inner join outer join 10=9+1 15=9+6

以上查询形式,结果集是如何产生的.

 

课堂练习

1 哪些人是领导?

select ename fromemp_hiloo

where empno in (selectmgr from emp_hiloo) 

in    =any

4 rows selected

2 哪些人是员工?不是领导?

select ename fromemp_hiloo

where empno not in(select mgr from emp_hiloo

                        where mgr is notnull) 

not in    <>all

3 哪些员工的工资等于本部门的平均工资

selectename,deptno,salary

from emp_hiloo

where (deptno,salary) in(

                         selectdeptno,round(avg(salary))

                         from emp_hiloo

                         group by deptno)

 

3 哪些员工的工资大于本部门的平均工资

select ename,deptno,salary

from emp_hiloo o

where salary > (selectround(avg(salary))

                   from emp_hiloo i

                   where i.deptno = o.deptno)

 

4 哪些人是领导?

select ename fromemp_hiloo o

where exists (

                select 1 from emp_hiloo i

                where o.empno = i.mgr)

-----------------------------------------

5哪些部门有员工,列出部门名称

select dname fromdept_hiloo o

where exists

              (select 1 from emp_hiloo i

               where o.deptno = i.deptno)

 

select dname fromdept_hiloo o

where deptno in (selectdeptno from emp_hiloo)

6哪些部门没有员工

select dname fromdept_hiloo o

where not exists

              (select 1 from emp_hiloo i

               where o.deptno = i.deptno)

select dname fromdept_hiloo o

where deptno not in(select deptno from emp_hiloo)

7 哪些人是员工,哪些人不是领导?

select ename fromemp_hiloo o

where not exists

                 (select 1 from emp_hiloo i

                  where o.empno = i.mgr)

8 列出员工的名称和部门名称

select e.ename,d.dname

from emp_hiloo e  join dept_hiloo d

on e.deptno = d.deptno

9 列出beijing地区有哪些员工?

select e.ename,d.dname

from emp_hiloo e  join dept_hiloo d

on e.deptno = d.deptno

and d.location ='beijing'

10 zhangwuji在哪个地区上班?

select e.ename,d.location

from emp_hiloo e  join dept_hiloo d

on e.deptno = d.deptno

and e.ename = 'zhangwuji'

11 列出员工的名称以及他的领导的名称

不包含zhangsanfeng

select e.ename,m.ename

from emp_hiloo e joinemp_hiloo m

on e.mgr = m.empno

e 10 9 1 (匹配9zhangsanfeng 不匹配 1)

m 10 4 6 (匹配4条 6条不匹配)

 

包含zhangsanfeng

select e.ename,m.ename

from emp_hiloo e joinemp_hiloo m

on e.mgr = m.empno

union all

select ename,'Boss'

from emp_hiloo

where mgr is null

 

select e.ename employee,

       decode(m.ename,e.ename,'Boss',

                      m.ename) manager

from emp_hiloo e joinemp_hiloo m

on nvl(e.mgr,e.empno) =m.empno

e 10 10 (匹配10)

m 10 4 6 (匹配4条 6条不匹配)

 

select e.ename employee,

       nvl(m.ename,'Boss') manager

from emp_hiloo e leftjoin emp_hiloo m

on e.mgr = m.empno

10=9+1

12 哪些人是领导?(用innerjoin )

select distinct  m.ename

from emp_hiloo e joinemp_hiloo m

on e.mgr = m.empno

13 哪些人是员工,哪些人不是领导?(用outerjoin)

select m.ename

from emp_hiloo e rightjoin emp_hiloo m

on e.mgr = m.empno

where e.empno is null

先执行外连接再执行where过滤,通过匹配表的列过滤外连接的结果集

 

课外练习

1 zhangwuji的领导是谁? (用 in)

2 zhangwuji领导谁?(用 in)

3 哪些员工的工资比同职位的平均工资高?

4 zhangwuji的领导是谁? (用 innerjoin)

5  zhangwuji领导谁?(用 innerjoin)

6 各个部门的平均工资,列出部门名称,平均工资

7 哪些部门没有员工(用 outerjoin 用 not in)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

posted @ 2014-04-01 16:29  JAVA之迷  阅读(117)  评论(0编辑  收藏  举报