Oracle数据库学习总结

我的Oracle数据库学习笔记

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


文章目录


复习总结

求和函数

所有的人总工资

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

  1. 按部门编号升序且姓名降序的方式排列员工信息
select e.deptno,e.ename,e.sal from emp e order by e.deptno asc ,e.ename desc;
  1. 按员工编号升序排列不在10号部门工作的员工信息
select * from emp e where e.deptno <> 10 order by e.empno ; 
  1. 查询姓名第二个字母不是”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;
  1. 查询各个部门的平均工资大于2000的部门编号和平均工资
select e.deptno 部门编号, round(avg(e.sal),2) 平均工资 from emp e group by e.deptno 
having avg(e.sal)>2000;
  1. 查询部门平均工资最高的平均工资
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'; --子查询结果单列值
  1. 找到与上面查出来的结果职务相同的其他员工
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;
  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 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;
  1. 比最低工资高,即比任何一个人的工资都高
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;
  1. 比最高工资低的其他部门员工信息
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
  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部门员工
    内层:任意一个员工职位
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;
  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 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';
  1. 查询不是经理的员工姓名
    查询是经理的员工的编号
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
);
  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数据库为每一个表中的行加的行号

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
  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;
上一篇:JDBC访问Oracle数据库例子源代码,包括创建table,删除table,插入记录,删除记录,查询记录等


下一篇:MySQL内存分配