Oracle_第二弹

 点击下载1-4 word版本

目 录

 

oracle_day2_多表联查    1

查询方式    1

笛卡尔积查询    1

内连接查询    1

外链接查询    1

自关联查询    1

子查询    1

排列查询    2

分组查询    2

分页查询    2

exists 的用法    2

集合运算    2

交集    2

并集    2

补集    2

范例    2

 

oracle_day2_多表联查

查询方式

• 笛卡尔积查询

  select * from emp e ,dept d --注：笛卡尔积没有实际意义，在工程中出现说明语句有问题

• 内连接查询

  • 显式内连接

      select * from emp e inner join dept d on e.deptno = d.deptno

  • 隐式内连接

    --等值连接

          select* from emp e ,dept d where e.deptno=d.deptno

      --不等值连接

          select * from emp e ,dept d where e.deptno != d.deptno

• 外链接查询

  • 左外链接查询

    select * from emp e1 LEFT JOIN emp e2 on e1.mgr=e2.empno

    select * from emp e1 ,emp e2 where e1.mgr=e2.empno(+);

  • 右外链接查询

    select * from emp e1 RIGHT JOIN emp e2 on e1.mgr=e2.empno

    select * from emp e1 ,emp e2 where e1.mgr(+)=e2.empno;

• 自关联查询

  select * from emp e1 join emp e2 on e1.mgr =e2.empno

• 子查询

  子查询中如果有null值 先过滤掉 nvl（ 字段，替代） 或 is not null

  SELECT * from emp where sal>(    select sal from emp where ename='SCOTT')

• 排列查询

  select * from emp ORDER by empno desc

• 分组查询

  select e.deptno from emp e GROUP BY e.deptno

 

• 分页查询

  select * from (select t.*,rownum rm from (select e.* from emp e order by e.sal desc) t ) tt where tt.rm>3 and tt.rm<7;

 

exists 的用法

判断后边的表达式是否为真(能查到) 真前边的执行 假不执行

select * from emp where exists (select * from dept where deptno=10);

--相当于select * from emp where 1=1;

集合运算

• 交集

  sql语句

  intersect

  sql语句

• 并集

  sql语句

  union

  sql语句

 

union all 不去重复

• 补集

  sql语句

  minus

  sql语句

范例

--范例：查询出雇员的编号，姓名，部门编号，部门名称，部门地址

    SELECT e.empno,e.ename,e.deptno,d.dname,d.loc FROM emp e join dept d on e.deptno=d.deptno

 

--范例：查询出每个员工的编号，姓名，上级领导的编号，姓名

    SELECT e1.empno,e1.ename,e2.empno,e2.ename from emp e1 join emp e2 on e1.mgr=e2.empno

 

--范例：子上一个例子的基础上查询该工资大于1500的员工的部门名称

    SELECT e1.empno,e1.ename,e2.empno,e2.ename,d.dname

    from (emp e1 join emp e2 on e1.mgr=e2.empno)

                 join dept d on e1.deptno=d.deptno

    where e1.sal>1500

 

--范例：查询出薪水大于1500的员工编号，姓名，部门名称，工资等级和他的上级领导的姓名和工资等级，并按员工编号排序

    select e1.empno,e1.ename, e1.deptno,d.dname,e1.sal,s1.grade "员工工资等级",e2.empno,e2.ename ,e2.sal,s2.grade "领导的工资等级"

    FROM (((emp e1 join emp e2 on e1.mgr=e2.empno )

                 join dept d on e1.deptno =d.deptno)

                 join salgrade s1 on e1.sal BETWEEN s1.losal and s1.hisal)

                 join salgrade s2 on e2.sal BETWEEN s2.losal and s2.hisal

    where e1.sal>1500

    ORDER BY e1.empno desc

 

--范例：查询出所有员工的上级领导

    --1

    select e1.empno ,e1.ename,e2.empno ,e2.ename from emp e1 LEFT JOIN emp e2 on e1.mgr=e2.empno

    --2 (+)oracle特有的外链接 放到哪个字段哪个字段的空值也显示出来

    select e1.empno,e1.ename,e2.empno,e2.ename from emp e1 ,emp e2 where e1.mgr=e2.empno(+);

 

--范例：查询比SCOTT工资高的员工 （子查询）

    SELECT * from emp where sal>(    select sal from emp where ename='SCOTT')

 

--范例：查询职位是经理并且工资比7782号员工高的员工

    select * from emp where sal>(    select sal from emp where empno=7782) and job='MANAGER'

 

--范例：查询工资最低的员工

    select * from emp where sal=(    SELECT min(sal) from emp)

 

--范例：查询部门最低工资大于30号部门最低工资的结果

    select d.dname , avg(d.deptno),min(sal)

    from emp e join dept d on e.deptno=d.deptno

GROUP BY d.dname

    HAVING min(sal)>(    select min(sal) from emp where deptno=30 )

 

--范例：查询出和scott同部门并且同职位的员工

    select * from emp where (deptno,job)=(select deptno,job from emp where ename='SCOTT' )

 

--范例：查询每个部门的最低工资和最低工资的雇员和部门名称

    select t.x "最低工资",e.ename,t.y from emp e ,(select min(sal) x,deptno y from emp GROUP BY deptno ) t where e.sal=t.x and e.deptno=t.y

 

--范例：子查询过滤null

    select * from emp e where e.empno not in(select mgr from emp where mgr is not null);

    select * from emp e where e.empno not in(select nvl( mgr,0) from emp );

 

--重点：查询员工表中工资最高的前三名

    --伪列 ROWNUM 自增数 ROWID 磁盘地址

        --用rownum写分页

            select * from (select t.*,rownum rm from (select e.* from emp e order by e.sal desc) t ) tt where tt.rm>3 and tt.rm<7;

 

--重点：找到员工表中薪水大于本部门平均薪水的员工

        select avg(sal),deptno from emp GROUP BY deptno

        select * from emp e ,(select avg(sal) x,deptno y from emp GROUP BY deptno ) t where e.deptno=t.y and e.sal>t.x

 

--重点：统计每年入职的个数

 

--集合运算

--范例：工资大于1500，或者是20号部门下的员工 (并集)

select * from emp e where e.sal >1500 or e.deptno=20;

 

select * from emp e where e.sal >1500

union

select * from emp e where e.deptno=20;

--all不去重复

select * from emp e where e.sal >1500

union all

select * from emp e where e.deptno=20;

 

--范例：工资大于1500，并且是20号部门下的员工 （交集）

select * from emp e where e.sal >1500

intersect

select * from emp e where e.deptno=20;

 

--范例：1981年入职的普通员工（不包括总裁和经理） (补集)

select * from emp e where e.hiredate between to_date('1981-01-01','yyyy-mm-dd') and to_date('1981-12-31','yyyy-mm-dd');

 

select * from emp e where to_char(e.hiredate ,'yyyy')='1981'

minus

select * from emp e where e.job in ('MANAGER','PRESIDENT');