Oracle学习笔记_01

备注：Oracle服务器和客户机提前安装配置完成（scott和hr用户必须解锁以供学习测试）！！！了解Oracle的体系结构
Oracle中文合集手册：链接: https://pan.baidu.com/s/1d4WhBos9sYfVZRw4l4rabg 提取码: yvmj

Oracle

操作表

select * from tab;

select * from emp;

select * from dept;

select * from bonus;-- 空表

select * from salgrade;

别名查询

1. 在别名查询过程中可能遇到编码错误的问题
   处理方法：

-- 查询服务器端的编码
select userenv('language') from dual; -- 我的查询结果是AMERICAN_AMERICA.ZHS16GBK
-- 执行这条语句
select * from V$NLS_PARAMETERS -- 第一条的结果的值若与上边的结果不同，则需要配置
-- 环境变量的配置（变量值为第一条查询语句查询出来的值）

1. 查询

select ename 姓名, sal 工资 from emp;-- 别名中不存在空格以及其它字符
select ename 姓名, sal "工 资" from emp;-- 别名中存在空格以及其它字符(双引号)
-- 查询年薪加奖金
/* 这里需要注意，因为有的人没有奖金，所以值为null，调用函数nvl若值为null，则置为指定数据（第二个参数为指定的数据） */
select sal*12 + nvl(comm,0) "奖金+工资" from emp ;

条件查询

/* 条件查询 算术运算符： > < >= <= = !=（不等于） <>(不等于) 逻辑运算符：and or not 其它运算符： between...and... 两者之间 like 模糊查询 in(set) 在集合中 is null 为空 is not null 不为空 */
-- 查询拥有奖金的员工
select * from emp where comm is not null;

-- 查询工资在1500-3000之间的员工
select * from emp where sal between 1500 and 3000 ;
select * from emp where sal >= 1500 and sal <= 3000;

-- 查询名字在某个范围内的员工
select * from emp where ename in ('ALLEN','JONES','BLAKE');

模糊查询

select * from emp;
-- 先将原表中的名字进行修改供之后操作
update emp set ename='SMI%TH' where empno=7369; 
-- 模糊查询（若名字中存在特殊符号%） 关键字escape的作用为说明哪个符号为转义字符
select * from emp where ename like '%\%%' escape '\' 

排序

/* 排序： asc：升序 desc：降序 注意：nulls关键字后边有两个值 first 和 last，意思是空值放置在前面或者后面查询 */
select * from emp order by comm desc nulls last; 

select * from tab;
select * from dept;

-- 查询部门编号和工资 且按照部门升序和工资降序查询
-- 通过两张表进行查询
-- select d.deptno, e.sal from emp e, dept d where d.deptno=e.deptno order by d.dname asc, e.sal desc;
-- 通过emp表进行查询
select deptno, sal from emp order by deptno asc, sal desc; 

多行（聚合）函数

/* 函数： 单行函数：ceil floor round 多行函数（聚合函数）：sum max min avg count */
select count(*) from emp;

select sum(nvl(comm,0)) from emp;

-- sum自动将null设置为0
select avg(comm) from emp;-- 550 错误结果 null值影响
select avg(nvl(comm,0)) from emp;
select sum(comm)/count(*) from emp;-- 157.142857142857

数值函数

-- 注意：dual表是Oracle中保证语法正确的一个内置表
select * from dual;--X
-- 返回大于或者等于指定表达式的最小整数
select ceil(12.999) from dual;--13
select ceil(12.444) from dual;--13

-- 返回小于或者等于指定表达式的最大整数
select floor(12.999) from dual;
select floor(12.444) from dual;

-- 四舍五入以及保留几位小数（第二个参数）
select round(12.456,3) from dual;
select round(12.456,2) from dual;
select round(12.456,1) from dual;
select round(12.456,0) from dual;
select round(12.456,-1) from dual;
select round(12.456,-2) from dual;
select round(12.456,-3) from dual;

-- 不四舍五入以及保留几位小数（第二个参数）
select trunc(12.456,3) from dual;
select trunc(12.456,2) from dual;
select trunc(12.456,1) from dual;
select trunc(12.456,0) from dual;
select trunc(12.456,-1) from dual;
select trunc(12.456,-2) from dual;
select trunc(12.456,-3) from dual;

-- 取余
select mod(9,2) from dual;

select userenv('language') from dual;
select * from V$NLS_PARAMETERS
select ename 姓名, sal "工 资" from emp
select * from emp;
select * from dept;
select * from bonus;
select * from salgrade;

字符函数

日期函数

转换函数

条件表达式

select ename from emp;
select 
case ename
  when 'SMITH' then 'zhangsan'
    when 'ALLEN' then 'lisi'
      else '路人'
        end
from emp;

/* Oracle特有的条件表达式 查询用户以及用户的工资等级 */
select e.ename, 
decode(s.grade,'1','一级','2','二级','3','三级','4','四级','五级') "工资等级"
from emp e, salgrade s
where e.sal between s.losal and s.hisal; 

分组

-- having过滤条件，只能用在分组group by后
select deptno,avg(sal), count(*) from emp group by deptno having avg(sal)>1500;

笛卡儿积

select * from emp,dept;-- 14 * 4

内连接

/* 显式内连接(条件在on之后) */
select * from emp e inner join dept d on e.deptno=d.deptno;
select * from dept d inner join emp e on e.deptno=d.deptno;

外连接

/* 外连接(通用)：outer关键字可以省略 左外连接：left outer join（右边的显示空值） 右外连接：right outer join（左边的显示空值） Oracle： 右：如下 左：如下 */
select * from emp e left join dept d on e.deptno=d.deptno;
select * from emp e right 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 e.*
from (select max(sal) maxsal from emp) s, emp e
where e.sal=s.maxsal;

-- 查询出比雇员7654的工资高，同时和7788从事相同工作的员工信息
select * from emp;
select * 
from emp
where sal>(select sal from emp where empno=7654) and job=(select job from emp where empno=7788);

/* 查询每个部门最底工资的员工和它所在的部门信息 */
select e.*, d.*, deptminsal.minsal 部门最低工资
from (select deptno, min(sal) minsal
             from emp
                  group by deptno) deptminsal, dept d, emp e
where d.deptno=deptminsal.deptno and e.sal=deptminsal.minsal;
/* 查询不是领导的信息 */
select * from emp;
select mgr from emp;
-- 领导信息
-- 1
select e.* 
from emp e, (select distinct mgr from emp) ld
where e.empno=ld.mgr;
-- 2
select * from emp where empno in (select mgr from emp);
-- 不是领导的信息(我们查询出来是领导的结果中存在一个null值，因此不添加is not null条件是无法查询的)
select * from emp where empno not in (select mgr from emp where mgr is not null);

exists

/* 查询有员工的部门 exists:返回值为布尔类型 */
select * from dept d where exists(select * from emp e where d.deptno=e.deptno);

rownum

/* 查询工资由高到低的前三位员工信息 rownum：伪列,默认值为1，Oracle特有，每查询出来一个结果(即在select后执行)，自加一 sql执行顺序： from...where...group by...having...select...order by... */
select rownum, e1.*
from (select * from emp order by sal desc) e1 
where rownum<=3;

复杂查询练习

/* 查询员工表中薪水大于本部门平均薪水的员工信息 */
select avg(sal),deptno from emp group by deptno;

select e1.*
from (select avg(sal) avgsal,deptno from emp group by deptno) e2, emp e1
where e1.sal>e2.avgsal and e1.deptno=e2.deptno;

select rownum, e3.*
from 
       (select e1.*
               from (select avg(sal) avgsal,deptno from emp group by deptno) e2, emp e1
                    where e1.sal>e2.avgsal and e1.deptno=e2.deptno
) e3;

/* 查询每年入职的员工数 */
select to_char(hiredate,'yyyy') rzn from emp;

select rztable.rzn, count(*) "num"
from (select to_char(hiredate,'yyyy') rzn from emp) rztable
group by rztable.rzn;

select sum("num") total
from (select rztable.rzn, count(*) "num" from (select to_char(hiredate,'yyyy') rzn from emp) rztable group by rztable.rzn);

select 
     sum("num") total,
     sum(case rzn
       when '1987' then "num"
         end) "1987",
     sum(case rzn
       when '1980' then "num"
         end) "1980",
     sum(case rzn
       when '1982' then "num"
         end) "1982",
     sum(case rzn
       when '1981' then "num"
         end) "1981"
from (select rztable.rzn, count(*) "num" from (select to_char(hiredate,'yyyy') rzn from emp) rztable group by rztable.rzn);

rowid

/* rowid:每条数据存储的直接物理地址 */
select rowid,e.* from emp e;

分页

/* 使用rownum进行分页 Oracle中没有提供向mysql一样的关键词 */
select rownum, e.* from emp e; 

select e1.* from (select rownum r, e.* from emp e) e1 where r>5 and r<10;
select e1.* from (select rownum r, e.* from emp e) e1 where r between 5 and 10;

并集

/* 并集运算（数据格式必须一致） */
select e1.* from (select rownum r, e.* from emp e) e1 where r>5 and r<10-- 6~9
union-- 自动排序去除重复值
select e1.* from (select rownum r, e.* from emp e) e1 where r between 1 and 7;

select e1.* from (select rownum r, e.* from emp e) e1 where r>5 and r<10-- 6~9
union all-- 不排序不去除重复值
select e1.* from (select rownum r, e.* from emp e) e1 where r between 1 and 7;

交集

/* 交集 */
select e1.* from (select rownum r, e.* from emp e) e1 where r>5 and r<10-- 6~9
intersect
select e1.* from (select rownum r, e.* from emp e) e1 where r between 1 and 7;

差集

/* 差集：第一条语句减第二条语句 */
select e1.* from (select rownum r, e.* from emp e) e1 where r>5 and r<10-- 6~9
minus
select e1.* from (select rownum r, e.* from emp e) e1 where r between 1 and 7;-- 1~7

select * from emp
minus
select * from emp where job='CLERK';