Oracle数据库学习总结

我的Oracle数据库学习笔记

Day 7 Oracle数据库学习复习总结

文章目录

• 我的Oracle数据库学习笔记
• • 复习总结
  • • 求和函数
    • 分组：注意事项：
    • having子句用法：
    • where子句和having子句区别：
    • • 课堂案例：
      • Oracle练习题：复习题
      • 基础题目
    • 多表连查——连接查询
    • • • 1.内连接：两个表中相同输出，交集
      • 2.外连接
      • 3.自连接（一个特殊的内连接，自己连接自己的一个副本）
    • 总结：
    • • 连接查询：
    • 子查询:
    • • 一、嵌套子查询：单列子查询：子查询的返回结果是单行单列的，经常用在where，having
      • 二、多列子查询 与集合运算:IN一起使用
      • 三、ANY关键字的子查询语句
    • 子查询习题：

复习总结

求和函数

所有的人总工资

查看每个部门的总工资：

思路：是不是要按照部门分组–分组的关键字 group by

select e.deptno,sum(sal) 部门工资 from emp e group by e.deptno;
--select e.ename,sum(sal) 部门工资 from emp e group by e.deptno; --错误的
           

分组：注意事项：

1. 分组一般与聚合函数一起使用
2. 分组中select 的选项只能是聚合函数或者是分组的条件字段(表达式)，只能是这两种

要对刚才查询的结果进行筛选：查看那些部门总工资高于10000的部门：

分组的条件筛选： 用having 子句

having子句用法：

1. 一定分组的条件筛选
2. 必须与group by 关键字一起使用

查找每个部门工资高于2000的员工的总工资。（注意：这其中我们要让工资低于2000的记录不参加聚合，所以我们使用where来筛选表中的记录）

工资高于2000的员工，你应该在哪里找，基于整个表，整个表的条件筛选 --where

基于整个表，整个结果集中的条件筛选用where

select sum(sal) 员工工资高于的2000部门总工资 from emp e where sal >2000 group by 
e.deptno;
           

查找每个部门工资高于2000的员工的总工资，且部门总工资大于5000。 --having子句

select sum(sal) 员工工资高于的2000部门总工资 from emp e where sal >2000 group by 
e.deptno having sum(sal)>5000;
           

where子句和having子句区别：

where的整个条件的筛选

having子句一定分组条件下的筛选，必须与group by一起使用

语法格式

课堂案例：

1. 查找每个部门工资高于2000的员工的总工资，要求只显示总工资高于5000的，按总工

   资降序排列：

select sum(sal) 员工工资高于的2000部门总工资 from emp e
where sal >2000 
group by e.deptno having sum(sal)>5000 
order by sum(sal) desc;
           

Oracle练习题：复习题

1. 按部门编号升序且姓名降序的方式排列员工信息

1. 按员工编号升序排列不在10号部门工作的员工信息

1. 查询姓名第二个字母不是”A”且薪水大于800元的员工信息，按年薪降序排列

基础题目

1. 统计各个部门不同职务的员工薪水的总和，平均工资

   关键点在于按照两个字段分组：练习点

select deptno 部门编号,job 员工职务, sum(sal) 总工资 ,avg(sal) 平均工资 from emp group 
by deptno,job order by deptno asc;
           

1. 查询各个部门的平均工资大于2000的部门编号和平均工资

select e.deptno 部门编号, round(avg(e.sal),2) 平均工资 from emp e group by e.deptno 
having avg(e.sal)>2000;
           

1. 查询部门平均工资最高的平均工资

多表连查——连接查询

笛卡儿积

统计emp表数据

统计dept表数据

1.内连接：两个表中相同输出，交集

① where 子句的内连接 与select 的where子句

显示出姓名第二个字母是A的员工的名称，工资和部门名称。

select e.ename 员工名称,e.sal 员工工资,d.dname 部门名称 from emp e,dept d where 
e.ename like '_A%' and e.deptno =d.deptno ;
           

②用(inner) join on 连接条件 内连接

select e.ename 员工名称,e.sal 员工工资,d.dname 部门名称 from emp e join dept d on 
e.deptno =d.deptno where e.ename like '_A%';
           

总结：连接必须写连接条件，否则结果不正确

铺垫;

select * from emp;

向emp表中数据

2.外连接

①：左外连接：

表名1 left join 表名2 on 连接条件

–执行顺序：以左表为主表，左表中所有的数据都会输出，而右表中没有数据，补空

②: 右外连接：

表名1 right join 表名2 on 连接条件

执行顺序：以右表为主表，右表中所有的数据都会输出，而左表中没有数据，补空

③：全外连接： ：

表名1 full join 表名2 on 连接条件

执行顺序：两个表中所有记录全都输出

3.自连接（一个特殊的内连接，自己连接自己的一个副本）

要查找一个员工领导的姓名

1、查询员工的领导

2、领导的姓名

select e1.empno 员工编号,e2.ename 领导姓名 from emp e1,emp e2 where e1.mgr 
=e2.empno and e1.empno=7698;
           

总结：

连接查询：

1. 内连接：

   语法：、where子句 b、表名1 [inner] join 表名2 on 条件

   执行：交集

2. 外连接：

   ①左外连接 ：表名1 left join 表名2 on 条件

   执行：左表为主表，左表所有数据输出，右边没有补空

   ②右外连接 ：表名1 right join 表名2 on 条件

   执行：右表为主表，右表所有数据输出，左边没有补空

   ③全外连接 ：表名1 full join 表名2 on 条件

   执行：并集

3. 自连接：自身连接

   强调：无论是哪种连接，必须要加连接条件，否则一定是错误

子查询:

什么是子查询：一个查询语句中嵌套了另一个查询语句:子查询的语句可以直接运行

基本格式 一般写在()中

子查询使用场合：一般出现在SELECT,FROM,WHERE,HAVING关键字语句中

分类：

一、嵌套子查询：单列子查询：子查询的返回结果是单行单列的，经常用在where，having

查询与smith相同职务的其他员工信息

思路：

1. 查询smith的职务

1. 找到与上面查出来的结果职务相同的其他员工

select * from emp where job=(
select job from emp where lower(ename)='smith'
)
and lower(ename)!='smith';
           

查询部门平均工资最高的平均工资和部门名称

思路：

1. 先查出部门平均工资最高的 --子查询

1. 最高的平均工资和部门名称

select d.dname ,round(avg(sal),1) 
from emp e join dept d on e.deptno =d.deptno 
group by d.dname 
having avg(sal) =(select max(avg(sal)) from emp group by deptno);
           

二、多列子查询 与集合运算:IN一起使用

查询与30部门职务相同的其他部门的员工信息

思路：

内层子查询：找到部门编号是30的员工的job

外层查询： 查询其他部门（）的信息

select * from emp where job in (select distinct job from emp where deptno=30) and 
deptno !=30;
           

三、ANY关键字的子查询语句

– > any :比子查询返回结果中的最小值要大

查询比10部门的最低工资要高的其他部门员工信息

思路：

1. 内层子查询：10部门的最低工资

1. 比最低工资高，即比任何一个人的工资都高

select * from emp where sal > any(
select sal from emp where deptno=10
) and deptno!=10;
           

< any :比子查询返回结果中的最小值要小

查询比20部门的最高工资要低的其他部门的员工信息

思路：

1. 查询20部门的工资

1. 比最高工资低的其他部门员工信息

select * from emp where sal < any(
select sal from emp where deptno=20
) and deptno!=20;
           

子查询习题：

1.查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，不包括10部门员工

1. 查10部门所有员工的入职时间

1. 外层查询

select e.ename,e.hiredate from emp e
where e.hiredate > any (
select e.hiredate from emp e where e.deptno=10
) and deptno!=10;
           

1. 查询入职日期比10部门所有员工晚的员工姓名、入职日期，不包括10部门员工

   关键字：all

select e.ename,e.hiredate from emp e
where e.hiredate > all (
select e.hiredate from emp e where e.deptno=10
) and deptno!=10;
           

1. 查询职位和10部门任意一个员工职位相同的员工姓名，职位，不包括10部门员工

   内层：任意一个员工职位

外层:在任意职位之中的就可以 关键字可以使用in 或者 =any

select ename,job from emp 
where job = any(
select job from emp where deptno=10
)and deptno!=10;
           

1. 查询与scott用户工资和职务都匹配的其他员工的信息

   内层两个子查询： 第一个scott用户工资 第二个：scott用户的职务

   外层

select * from emp
where sal=(select sal from emp where lower(ename)='scott')
and job=(select job from emp where lower(ename)='scott') 
and lower(ename)!='scott';
           

1. 查询部门的名称，部门人数，部门最高工资，部门的最低工资，部门最低工资的姓名

   内层子查询： 部门人数，部门最高工资，部门的最低工资

select deptno,count(*) c ,max(sal) ma,min(sal) mi from emp group by deptno;
select dname,t.c,t.ma,t.mi,ename 
from dept,emp,(select deptno,count(*) c ,max(sal) ma,min(sal) mi from emp group by 
deptno) t
where emp.deptno = dept.deptno and emp.deptno=t.deptno;
           

1. 查询职务和经理同员工SCOTT或BLAKB完全相同的员工姓名、职务、不包括SCOTT和BLAKB

   内层查询

外层查询

select ename,job 
from emp
where (job,mgr) in(
select job,mgr from emp where upper(ename)='SCOTT' or upper(ename)='BLAKB' 
)and upper(ename)!='SCOTT' and upper(ename)!='BLAKB';
           

1. 查询不是经理的员工姓名

   查询是经理的员工的编号

找其他人的名称

select ename from emp
where empno not in(
select mgr from emp where mgr is not null
);
           

1. 求各个部门薪水最高的员工信息

   各个部分的最高薪水

select max(sal) from emp group by deptno;
select * from emp
where sal in(
select max(sal) from emp group by deptno
);
           

分页查询

rownum： 伪列：是Oracle数据库为每一个表中的行加的行号

分页查询：需要用到rownum字段 ,每一个页中显示的记录数，显示多少页

–规则：注意事项：rownum只能与<,<=一起使用，不能与>,>=一起使用

–特点：rownum的值永远从1开始

查询emp表中前5条信息

查询emp表的第6到10行

第一步：先将rownum值固化在emp表（原结果集）

select rownum r ,emp.* from emp
           

子查询

select rownum,t.* from (
select rownum r ,emp.* from emp
) t where t.r between 6 and 10;
           

查询emp表的最后5行信息

1. 先将rownum值固化在原结果集上

select rownum r ,emp.* from emp
           

1. 最后5行

select rownum, t.* from(
select rownum r ,emp.* from emp) t 
where t.r> (select count(*) from emp)-5;
           

每页显示三条记录，查询第三页和第五页的员工信息

– 1: 1-3

– 2: 4-6

– 3: 7-9

– 4: 10-12

– 5: 13-15

select rownum,t.* from(
select rownum r,emp.* from emp) t
where t.r>(3-1)*3 and t.r<=3*3 or t.r>(5-1)*3 and t.r<=5*3;