Oracle

查询数据

本节将学习如何从Oracle数据库中查询数据。我们将从一个简单的查询开始，从单个表中检索数据。

• Select语句 - 演示如何查询单个表中的数据。

--查询语句: select 数据 from 数据源  查询一张表中的所有数据的指定字段值
--条件查询: select 数据字段 from 数据源 where 行过滤条件    --> 满足行过滤条件where的数据被保留在结果集中,只保留指定字段的值select
--执行过程: from -->where-->select
/*
a)、= 、 >、 <、 >=、 <=、 !=、 <>、 between and
b)、and 、or、 not 
c)、集合函数 union、  union all、    intersect 、minus
c)、null  :is null、  is not null、  --not is null
d)、like :模糊查询 % _ escape('单个字符')
*/
-- 查询20部门的员工信息
--查询的数据 : 员工信息 *
--数据的来源 : 员工表 emp
--条件 : 20部门  deptno=20
select * from emp where deptno=20;

-- 查询工资大于1000的员工的姓名 工作岗位  工资   所属部门编号
--查询的数据 : ename, job,sal,deptno  
--数据的来源 : 员工表 emp
--条件 : 工资大于1000 sal>1000
select ename,job,sal,deptno from emp where sal>1000;

-- 查询不在20部门工作的员工信息
select * from emp where deptno!=20;
select * from emp where deptno<>20;
select * from emp where not deptno=20;

-- 查询员工的年薪大于20000的 员工名称、岗位 年薪
select empno,ename,job,sal,sal*12 from emp where sal*12>20000;
--select后的别名在where中不能使用,因为执行流程问题,先执行from->where->select
--select empno,ename,job,sal,sal*12 result from emp where result>20000;

--先查询后判断
--1)查询出所有员工的信息,包含年薪
select empno,ename,job,sal,sal*12 result from emp;
--2)对已经查询出来(1)保留的结果集)当做数据源判断年薪>20000
select * from (select empno,ename,job,sal,sal*12 result from emp) where result>20000;

-- 假设现在 900,1000,1100,查询工资比我们三个人都高的那些员工的信息
select * from emp where sal > all(900,1000,1500);

-- 查询工资比我们三个人都低的那些员工的信息
select * from emp where sal < all(900,1000,1500);

-- 查询比我们中随便一个工资高的员工信息
select * from emp where sal > any(900,1000,1500);

-- 查询 工种为’SALESMAN’的员工信息 （注意 内容区分大小写）
select * from emp where job = 'SALESMAN';

-- -检索 工资 2000, 3000员工名称 岗位 工资
select * from emp where sal=1500 or sal=3000;
-- 工资在2000到3000之间的员工信息
select * from emp where sal>=2000 and sal<=3000;
--between and 相当于>= <=
select * from emp where sal between 1500 and 3000;  

---查询 岗位 为 CLERK 且部门编号为 20的员工名称 部门编号，工资
select ename,deptno,sal,job from emp where job='CLERK' and deptno=20;
-- 查询 岗位 为 CLERK 或部门编号为 20的员工名称 部门编号，工资
select ename,deptno,sal,job from emp where job='CLERK' or deptno=20;
-- 查询 岗位 不是 CLERK 员工名称 部门编号，工资
select ename,deptno,sal,job from emp where job!='CLERK';
select ename,deptno,sal,job from emp where not job='CLERK';
select ename,deptno,sal,job from emp where job<>'CLERK';

-- 查询 岗位 不为 CLERK 并且部门编号不为 20的员工名称 部门编号，工资
select * from emp where job!='CLERK' and deptno!=20;

select * from emp where not(job='CLERK' and deptno=20);

-- 存在奖金的员工名称
select ename,sal,comm from emp where comm is not null;
select ename,sal,comm from emp where not comm  is null;
-- 不存在奖金的员工名称
select ename,sal,comm from emp where  comm is null;

-- 查询工资大于1500 或 含有佣金的人员姓名
select * from emp where sal>1500 or comm is not null;

--集合函数 union(去重)、 union all、    intersect 、minus
select * from emp where sal>1500
union
select * from emp where comm is not null;

-- 查询显示不存在雇员的所有部门号。
select deptno from dept
minus
select distinct deptno from emp;

--like :模糊查询 % _ escape('单个字符')
-- % 任意个任意字符
-- _ 一个任意字符
-- 查询员工姓名中包含字符A的员工信息
select * from emp where ename like '%A%';
-- 查询员工姓名中第二个字母为A的员工信息
select * from emp where ename like '_A%';

-- 查询名字 中包含%的员工信息
--插入2条数据,员工名称中带有%
insert into emp(empno,ename,sal) values(1000,'t_%test',8989); 
insert into emp(empno,ename,sal) values(1200,'t_tes%t',8000);
--在对一张表中的数据做增删改操作的时候会默认开启事务,commit提交  rollback回滚

select * from emp where ename like '%S%%' escape('S');

--删除表中的数据
delete from emp where ename like '%S%%' escape('S');

-- 查询工资为 1500， 2000， 2500， 5000的员工的信息
select * from emp where sal=1500 or sal=2000 or sal=2500 or sal=5000;

--in(值列表) 判断指定的字段的值是否与in后面的值列表中的某个值相等,相等就满足
select * from emp where sal in (1500， 2000， 2500， 5000);

-- 部门名称为 SALES 或 ACCOUNTING 的雇员信息
--1)查询到部门名称为 SALES 或 ACCOUNTING的部门编号
select deptno from dept where dname='SALES' or dname='ACCOUNTING';
select deptno from dept where dname in('SALES','ACCOUNTING');
--2)在以上两个部门编号的员工信息
select * from emp where deptno in(10,30);
--子查询 : 查询语句嵌套查询语句
select *
  from emp
 where deptno in
       (select deptno from dept where dname in ('SALES', 'ACCOUNTING'));

-- 查询工资等级为 2的员工信息
--查询2等级的最低薪
select losal from salgrade where grade = 2;
--查询2等级的最高薪
select hisal from salgrade where grade = 2;

select *
  from emp
 where sal between (select losal from salgrade where grade = 2) and
       (select hisal from salgrade where grade = 2);

--查询1500薪资的等级
select grade from salgrade where 1500 between losal and hisal;

-- 查询 销售部（SALES） 中 工资大于1500的员工信息
--查询工资>1500的员工信息
select * from emp where sal>1500;
select * from emp where deptno = (select deptno from dept where dname = 'SALES') and sal>1500;

-- 查询工资比SMITH高的与SMITH同一部门的员工信息
--SMITH的工资
select sal from emp where ename='SMITH';
--SMITH的部门编号
select deptno from emp where ename='SMITH';

select * from emp where sal>(SMITH的薪资) and deptno = (SMITH的部门编号);
select * from emp where sal>(select sal from emp where ename='SMITH') and deptno = (select deptno from emp where ename='SMITH');
 

 排序数据

• ORDER BY语句 - 按升序或降序对查询的结果集进行排序。

过滤数据

• DISTINCT - 介绍如何消除查询输出中的重复行。
• WHERE - 演示如何为查询返回结果集中的行记录指定过滤条件。
• AND - 组合两个或两个以上的布尔表达式，如果所有表达式都为true，则返回true。
• OR - 组合两个或两个以上的布尔表达式，如果其中一个表达式为true，则返回true。
• FETCH - 演示如何使用行限制子句限制查询返回的行数。
• IN - 确定值是否与列表或子查询中的任何值相匹配。
• BETWEEN - 基于一系列值(区间值)过滤数据。
• LIKE - 根据特定模式执行匹配。

--函数
--单行函数 : 一行记录返回一个结果
--多行函数|组函数|聚合函数 : 多条记录返回一个结果  

--单行函数
-- 当前时间
select distinct sysdate from emp;
select sysdate from dual;
select current_date from dual;

-- 2天以后日期
-- 日期可以进行+-
select sysdate,sysdate+2 from dual;

-- 所有员工入职的3天后是几号
select empno,ename,sal,hiredate,hiredate-3 from emp;

-- 查询所有员工的试用期期到期（转正的日期） 3个月试用期
select empno,ename,sal,hiredate,hiredate+90 from emp;
--add_months(日期,月数)
select empno,ename,sal,hiredate,add_months(hiredate,3) from emp;

-- 查询所有员工到目前为止一共工作了几个月
select empno,ename,hiredate,months_between(sysdate,hiredate) from emp;

-- 查询当前月的最后一天
select last_day(sysdate) from emp;

-- 下一个星期三是几号
select next_day(sysdate,'星期四') from dual;

-- 日期对象 与 字符串之间转换问题
--to_char(日期对象,'模板')
select to_char(sysdate,'yyyy"年"mm"月"dd"日"') from dual;
--to_date(日期字符串,'模板')
select to_date('2021-7-29 15:37:47','yyyy-mm-dd hh24:mi:ss') from dual;

-- 查询82年入职员工的信息

聚合函数

--聚合函数|组函数|多行函数
-- count(*|字段|伪列)  sum()  max()   min()  avg()
--注意:
   --如果select后面出现组函数 的使用,只能和组函数或者分组字段一起使用

-- 统计一下一共有多少个员工
select count(*) from emp;
select count(empno) from emp;
select count(deptno) from emp;
select count(1) from emp;

-- 统计一共有几个部门 
select count(deptno) from dept;

-- 统计有员工存在的部门总数
select count(distinct deptno) from emp;

-- 统计20部门一共有多少人
select count(1) from emp where deptno  = 20;

-- 计算本公司每个月一共要在工资上花费多少钱
select sum(sal) from emp;

-- 计算20部门每个月的工资花销
select sum(sal) from emp where deptno = 20;

-- 查询本公司的最高工资和最低工资
select max(sal),min(sal) from emp;

--查看30部门的最高工资和最低工资
select max(sal),min(sal) from emp where deptno = 30;

-- 请查询出 20部门的平均工资， 部门编号
select avg(sal),deptno from emp where deptno = 20 group by deptno;

-- 计算出所有员工的奖金总和
select sum(comm) from emp;
--null值不参与组函数的计算

-- 统计有奖金的员工有几个
select count(1) from emp where  comm is not null;
select count(comm) from emp;

-- 查询 最高薪水的员工姓名,及薪水
select max(sal) from emp;

select ename,sal from emp where sal = (最高薪资);
select ename,sal from emp where sal = (select max(sal) from emp);

-- 查询工资低于平均工资的员工编号，姓名及工资
select empno,ename,sal from emp where sal <(select avg(sal) from emp);

 连接表

• INNER JOIN - 演示如何从表中查询具有与其他表匹配的行记录。
• LEFT JOIN - 介绍左连接概念，并学习如何使用它选择左表中具有，但右表中不具有的行记录。
• RIGHT JOIN - 解释右连接概念，并演示如何从右表查询具有，但左表中不具有的行记录。
• CROSS JOIN –介绍如何从多个表中构建笛卡尔乘积。
• Self Join - 演示如何将表连接到自身以查询分层数据或比较同一个表中的行记录。

--连表查询
--当要查询的数据来自与不同的表|数据源,就可以使用连表查询
--语法: 92  99

--92

--笛卡尔积 : 对乘
--select 数据 from 数据源1,数据源2...;

--注意:
        --查询同名字段需要指明出处

--查询所有员工信息,这个员工所在的部门信息
--查询的数据: 员工信息,部门信息
--数据的来源: emp dept
select * from emp,dept;

--表连接条件
--等值连接
select e.*,d.* from emp e,dept d where e.deptno=d.deptno;
select e.empno,d.dname,e.deptno from emp e,dept d where e.deptno=d.deptno;

--查询10,30部门的员工 信息以及所在部门信息
select e.*,d.* from emp e,dept d where e.deptno=d.deptno and e.deptno in(10,30) order by sal;

--等值连接条件的字段一般为主外键关联关系或者同名字段,但是主要类型相同就可
select * from emp e,dept d where e.ename=d.dname;

--非等值连接

--查询员工的信息以及工资等级信息
select grade from salgrade where 1500 between losal and hisal;

select * from emp e,salgrade s where e.sal between s.losal and s.hisal;

--自连接
--一张包作为2个身份,自己与自己连接
--查询由上级存在的员工信息以及上级经理人信息
--数据: 员工信息,经理人信息
--来源: 员工表emp e1, 经理人表 emp e2
select * from emp e1,emp e2 where e1.mgr=e2.empno;

--内连接: 满足连接条件才保留,不满足连接条件不保留

--所有员工的员工信息以及上级经理人信息
--需求: 员工表中数据无论是否满足连接条件都要显示

--外连接 : 主表中的数据无论是否满足连接条件都显示
--左外连接: 左连接    表连接的位置,主表 在左边
--右外连接: 右连接    表连接的位置,主表 在右边
--员工表作为主表
--在连接条件的位置,主表的对面添加(+)
select * from emp e1,emp e2 where e1.mgr=e2.empno(+); -->  左连接
select * from emp e2,emp e1 where e1.mgr=e2.empno(+); -->  右连接

 分组数据

• GROUP BY - 演示如何将行分组为子组，并为每个分组应用聚合函数。
• HAVING - 演示如何过滤分组中的行记录。

--分组: group by 分组字段1,分组字段2
--查询: select 数据 from 数据源 where 行过滤条件 group by 分组字段 having 组过滤信息 order by 排序字段;
--流程: from-->where --> group by --> having --> select --> order by 

--注意 : 
     --一旦分组,分组之后的行为操作都是以组为单位,只能看到有多少组,每组的标签(分组字段的值),但是看不到组中的数据
     --一旦分组,select后面只能查询分组字段或者组函数
     --where中不能使用组函数

-- 查出比本部门平均工资高的员工信息

-- 找出每个部门的最高工资 
select deptno,max(sal),min(sal),sum(sal),avg(sal),count(1) from emp group by deptno;

-- 找出20部门和30部门的每个部门最高工资 
--1)先过滤后分组
select max(sal),deptno from emp where deptno in(20,30) group by deptno order by max(sal) desc;

--2)先分组后过滤
select max(sal),deptno from emp group by deptno having deptno in(20,30);

-- 求出每个部门员工工资高于1000的的平均工资
select avg(sal),deptno from emp where sal>1000 group by deptno;

-- 求出10和20部门部门的哪些工资高于1000的员工的平均工资
select avg(sal),deptno from emp where sal>1000 and deptno in(10,20) group by deptno;
select avg(sal),deptno from emp where sal>1000  group by deptno having deptno in(10,20);

-- 求出平均工资高于2000的部门编号和平均工资
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

--查询 最低平均工资的部门编号
select deptno from emp group by deptno having avg(sal) = (最低平均工资);
--1)
select deptno
  from emp
 group by deptno
having avg(sal) = (select min(avg(sal)) from emp group by deptno);

--2)
select *
  from (select deptno, avg(sal) avg_sal from emp group by deptno)
 where avg_sal = (select min(avg(sal)) from emp group by deptno);

--最低平局工资
select min(avg(sal)) from emp group by deptno;