我的Oracle数据库学习笔记

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

文章目录

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

复习总结

求和函数

所有的人总工资

select sum(sal) 总工资 from emp;

查看每个部门的总工资：
思路：是不是要按照部门分组–分组的关键字 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 子句

select sum(sal) 部门总工资 from emp e group by e.deptno having sum(sal)>10000;

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练习题：复习题

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

select e.deptno,e.ename,e.sal from emp e order by e.deptno asc ,e.ename desc;

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

select * from emp e where e.deptno <> 10 order by e.empno ; 

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

select * from emp e where e.sal>800 and e.ename not like '_A%' order by e.sal*12 desc;

基础题目

1. 统计各个部门不同职务的员工薪水的总和，平均工资
   关键点在于按照两个字段分组：练习点

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

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

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

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

select e.deptno 部门编号, max(sal)-min(sal) 差额 from emp e group by e.deptno;

多表连查——连接查询

笛卡儿积

select count(*) from emp e,dept d; --56

统计emp表数据

select count(*) from emp; --14 

统计dept表数据

select count(*) from dept; --4

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表中数据

insert into emp values(20,'wq','teacher',7902,'18-12月-09',200,null,10);

2.外连接

①：左外连接： 表名1 left join 表名2 on 连接条件
–执行顺序：以左表为主表，左表中所有的数据都会输出，而右表中没有数据，补空

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

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

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

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

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

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

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

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的职务

select job from emp where lower(ename)='smith'; --子查询结果单列值

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

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

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

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

select max(avg(sal)) from emp group by deptno;

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

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 distinct job from emp where deptno=30;

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

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

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

– > any :比子查询返回结果中的最小值要大
查询比10部门的最低工资要高的其他部门员工信息
思路：

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

select sal from emp where deptno=10;

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

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

< any :比子查询返回结果中的最小值要小
查询比20部门的最高工资要低的其他部门的员工信息
思路：

1. 查询20部门的工资

select sal from emp where deptno=20;

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

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

子查询习题：

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

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

select e.hiredate from emp e where e.deptno=10

2. 外层查询

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;

2. 查询入职日期比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;

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

select job from emp where deptno=10;

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

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

4. 查询与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';

5. 查询部门的名称，部门人数，部门最高工资，部门的最低工资，部门最低工资的姓名
   内层子查询： 部门人数，部门最高工资，部门的最低工资

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;

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

select job,mgr from emp where upper(ename)='SCOTT' or upper(ename)='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';

7. 查询不是经理的员工姓名
   查询是经理的员工的编号

select mgr from emp where mgr is not null

找其他人的名称

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

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

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

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

select rownum, emp.* from emp ;

分页查询：需要用到rownum字段 ,每一个页中显示的记录数，显示多少页
–规则：注意事项：rownum只能与<,<=一起使用，不能与>,>=一起使用
–特点：rownum的值永远从1开始

查询emp表中前5条信息

select emp.* from emp where rownum<=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

2. 最后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;