Oracle基础知识

oracle基本概念

一个oracle服务器:由一个oracle数据库和多个oracle实例组成.
是一个数据管理系统(RDBMS),它提供开放的,全面的,近乎完整的信息管理.

数据库:物理概念,/opt/oracle/oradata/ORCL中的数据文件  ORCL实例名
oracle实例:数据库在内存文件中的镜像.
oracle实例=内存(SGA系统全局区)+后台进程(主要5个:SMON/PMON/DBWR/LGWR/CKPT).
oracle数据库:
1.控制文件:control files 包含了维护和校验数据库一致性所需的信息.
2.重做日志文件:redo log files 包含了当系统崩溃后进行恢复所需记录变化信息.
3.数据文件: data files 包含了数据库中真正的数据.
oracle服务的其他文件:
1.初始化参数文件(parameter files):定义了实例的特性:如全局区中的一些内存结构的大小,DBWR的个数.
2.密码文件(password files):包含了数据库管理员或操作员用户在启动和关闭实例所需的密码.
3.归档重做日志文件(archived redo log files):是重做日志文件的脱机备份.在系统崩溃后恢复可能需要这些文件.


jdbc通过实例操作数据库.

一个oracle数据库对应一个oracle实例--单机
一个oracle数据库对应多个oracle实例--集群(RAC:real application cluster)





集群的优点:
1.load balance负载均衡.
2.fail over 失败迁移.

表空间(users)和数据文件(*.dbf)

基本查询

-- spool d:/基本查询
-- 清屏 host clear/cls  linux/windows
-- show user;
-- select * from tab;
-- 员工表的结构 desc emp;
-- 查询所有员工信息 select * from emp;

-- 查询员工信息:员工号,姓名,月薪 select empno,ename,sal from emp;
-- 查询员工信息:员工号,姓名,月薪,年薪  select empno,ename,sal,sal*12 from emp;
-- 查询员工信息:员工号,姓名,月薪,年薪,奖金(null),年收入 select empno,ename,sal,sal*12,comm,sal*12+comm from emp;
-- SQL中null值:
1.包含null的表达式都为null 
2.null永远!=null 
3.如果集合中含有空值不能用not in 可以用in.
4.组函数(多行函数)自动滤空,可以嵌套滤空函数,来屏蔽滤空功能

-- 滤空函数:nvl(a,b)  nvl2
-- select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0) from emp;
--查询奖金为null的员工 select * from emp where comm is null;
-- 别名 select empno as "员工号",ename as "姓名",sal as "月薪",sal*12 "年薪",comm "奖金",sal*12+nvl(comm,0) "年收入" from emp;

--连接符
--dual表:存在的意义,满足语法要求,伪表
--select 'Hello'||' World' 字符串 from dual;
--去重distinct
--编辑最近一条SQL ed

-- 字符和日期 
1.字符和日期要包含在单引号中.
2.字符大小写敏感,日期格式敏感 
3.默认的日期格式:DD-MON-RR.
--修改日期格式
select * from V$nls_Parameters;
alter session|system set NLS_DATE_FORMAT='yyyy-mm-dd';
select * from emp where hiredate='1981-11-17';

--between and
-- 查询月薪在1000到2000之间的员工
select * from emp where sal between 1000 and 2000; 效果等于[1000,2000]

--in/not in
-- 查询是10和20号部门的员工
select * from emp where deptno in (10,20); --可以有null
-- 查询不是10和20号部门的员工
select * from emp where deptno not in (10,20); --不能出现null

--like 模糊查询(%多个字符,_一个字符)
--查询名字以S打头的员工 select * from emp where ename like 'S%';
--查询名字是4个字的员工 select * from emp where ename like '____';
--查询名字中含有下划线的员工 select * from emp where ename like '%\_%' escape '\';

--排序(默认升序asc,降序desc),oracle null最大
-- order by 作用于后面所有的列,desc只作用于离他最近的列
-- order by后面+列,表达式,别名,序号(列数)
--查询所有员工信息 按照月薪排序  select * from emp order by sal desc;
--查询员工信息,按照奖金排序(降序) select * from emp order by comm desc nulls last;

函数和多表查询

注意:函数可以没有参数,但必须要有返回值.

1.单行函数
通用:
nvl(a,b) 当a=null,返回b.
nvl2(a,b,c) 当a=null 返回c,否则返回b
nullif(a,b) 当a=b的时候,返回null;否则返回a.
coalesce(a,b,c...) 从左到右返回第一个不为null的值.
select ename,coalesce(comm,sal) from emp;

字符:
大小写控制函数
select lower('hello word') 转小写,upper('hello world') 转大写,initcap('hello word') 首字母大写 from dual;
字符控制函数
-- subsrt(a,b,c) 从a中,第b位开始取
select substr('hello world',3) from dual;
-- subsrt(a,b,c) 从a中,第b位开始,取c位
select substr('hello world',3,5) from dual;
-- insrt(a,b) 从a中查找b,返回位置
select instr('hello world','ll') from dual;
-- length 字符数 lengthb字节数,针对英文一样,中文一个字符=两个字节
select length('hello world'),lengthb('hello world') from dual;
select length('北京'),lengthb('北京') from dual;
-- lpad左填充 rpad右填充 --abcde长度有4位,使用*填充到10位
select lpad('abcdef',10,'*'),rpad('abcdef',10,'*') from dual;
-- trim 去掉前后指定的字符
select trim('h' from 'hhello worldh') from dual;
--replace e替换为l
select replace('hello word','e','l') from dual;

数值:
ROUND(值,保留几位小数):四舍五入
select round(45.923,2),round(45.923,1),round(45.923,0),round(45.923,-1),round(45.923,-2),round(45.923,-3) from dual;

trunc:截断
select trunc(45.923,2),trunc(45.923,1),trunc(45.923,0),trunc(45.923,-1),trunc(45.923,-2),trunc(45.923,-3) from dual;

转换:隐式转换/显示转换
to_number/to_char/to_date
--to_char(date,'format_model')
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd hh:mm:ss" today is "day') from dual;
--to_char(number,'formate_model')
--查询员工薪水,两位小数,千位符,货币代码
select ename,to_char(sal,'L9,999.99') from emp;


日期:
-- 昨天 今天 明天
select sysdate-1,sysdate,sysdate+1 from dual;
-- 计算员工的工龄:天 星期 月 年
select ename,hiredate,(sysdate-hiredate) ,(sysdate-hiredate)/7,(sysdate-hiredate)/30,(sysdate-hiredate)/365 from emp;
--months_between两个日期相差的月数
select ename,hiredate,(sysdate-hiredate)/30,months_between(sysdate,hiredate) from emp;
--add_months指定日期加上若天个月
select add_months(sysdate,-56) from dual;
--last_day本月最后一天.
select last_day(sysdate) from dual;
--next_day指定日期的下一星期几.
--应用:每个星期一自动备份数据 1.分布式数据库 2.快照 3.触发器.
 select next_day(sysdate,'MONDAY') from dual;
--ROUND(sysdate,'MONTH'),ROUND(sysdate,'YEAR')
select round(sysdate,'year'),round(sysdate,'month') from dual;

条件表达式:
case表达式:sql99的语法,繁琐
decode 函数:oracle 自己的语法,类似java,简单
--涨工资,总裁1000,经理800,其他400
select ename,case job when 'PRESIDENT' then sal+1000 when 'MANAGER' then sal+400 else sal+400 end from emp;
select ename,decode(job,'PRESIDENT',sal+1000,'MANAGER',sal+800,sal+400) from emp;

2.多行函数
分组函数:作用于一组数据,并对一组数据返回一个值.
常用:avg,count,max,min,sum
-- 平均工资(两个结果一样)
select sum(sal)/count(*),avg(sal) from emp;
-- 平均奖金(null问题)
select sum(comm)/count(*),sum(comm)/count(comm),avg(comm) from emp;
--count(distinct expr) 去重
--avg(nvl(comm,0)) nvl函数使分组函数无法忽略空值.

--group by分组(select a,组函数(x) from table group by a)
--求每个部门的平均工资
 select deptno,avg(sal) from emp group by deptno;
 --多个列的分组
select deptno,job,sum(sal) from emp group by deptno,job order by 1;
--having where语句后面不能使用多行函数.
--求平均工资大于2000的部门
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
--查询10号部门的平均工资
select deptno,avg(sal) from emp group by deptno having deptno=10;
select deptno,avg(sal) from emp where deptno=10 group by deptno;(优先)
--求每个部门每个职位的总工资,以及部门的工资总和
select deptno,job,sum(sal) from emp group by rollup(deptno,job);
抽象:
group by rollup(a,b)==group by a,b+group by a + group by null


多表查询
-- 等值连接

--查询员工信息:员工号 姓名 月薪 部门名称
select e.deptno,e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno;

--不等值连接
--查询员工信息:员工号 姓名 月薪 工薪级别
 select e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;

--外连接
--按照部门统计员工人数:部门号,部门名称,人数
select d.deptno,d.dname,count(e.empno) from emp e,dept d where d.deptno=e.deptno group by d.deptno,d.dname;(可能造成部门的丢失,假设该部门没人)
--右外连接
select d.deptno,d.dname,count(e.empno) from emp e right join dept d on e.deptno=d.deptno group by d.deptno,d.dname;
select d.deptno,d.dname,count(e.empno) from emp e,dept d where e.deptno(+)=d.deptno group by d.deptno,d.dname;

--左外连接
select d.deptno,d.dname,count(e.empno) from dept d left join emp e on d.deptno=e.deptno group by d.deptno,d.dname;
select d.deptno,d.dname,count(e.empno) from dept d,emp e where d.deptno=e.deptno(+) group by d.deptno,d.dname;

--自连接(不适合操作大表)
--通过表的别名,将同一张表视为多张表
--查询员工和他老板的姓名.
select e.ename,b.ename from emp e,emp b where e.mgr=b.empno;

--层次查询(树状结果) 伪列level
select level,empno,ename,mgr from emp connect by prior empno = mgr start with mgr is null order by 1;

子查询以及集合运算

子查询:不能一步求解
-- 查询工资比SCOTT高的员工信息
select * from emp where sal >(select sal from emp where ename='SCOTT');

注意的问题:
1.括号
2.合理的书写风格.
3.可以在where select having from 后面,都可以使用子查询.
4.不可以在group by 后面使用子查询
5.强调from后面的子查询
6.主查询和子查询可以不是同一张表:只要子查询返回的结果,主查询可以使用即可
7.一般不再子查询中排序:但是top-n分析问题中,必须对子查询排序.
8.一般先执行子查询,再执行住查询;但相关子查询例外.
9.单行子查询只能使用单行操作符:多行子查询只能使用多行操作符
10.子查询中null

--针对问题3:
select empno,ename,sal,(select job from emp where empno=7839) as four from emp;
--针对问题4:查询部门最低工资大于部门号为30的员工中的最低工资
select deptno,min(sal) from emp group by deptno having min(sal)>(select min(sal) from emp where deptno=30);
--针对问题5:查询员工信息:员工号 姓名 薪水
select empno,ename,sal from emp; 
select * from (select empno,ename,sal from emp);--在oracle中性能一样
--针对问题6:查询部门名称是sales的员工
select * from emp where deptno=(select deptno from dept where dname='SALES');
select * from emp e,dept d where e.deptno=d.deptno and d.dname='SALES'; 

针对问题9:in在集合中 查询部门名称是SALES和ACCOUNTING的员工
select * from emp where deptno in (select deptno from dept where dname='SALES' or dname ='ACCOUNTING');

--any 查询工资比30号部门任意一个员工高的员工信息
select * from emp where sal >(select min(sal) from emp where deptno=30);
select * from emp where sal >any(select sal from emp where deptno=30);
--all 查询工资比30号部门所有员工高的员工信息
select * from emp where sal >(select max(sal) from emp where deptno=30);
select * from emp where sal >all(select sal from emp where deptno=30);

--多行子查询中null --> not in(10,20,null)<=>expr1 !=10 or expr1 !=20 or (expr1 !=null 返回null) 
--查询不是老板的员工(mgr 不为 null).
select * from emp where empno not in (select mgr from emp where mgr is not null);


集合运算
--查询10和20部门的员工
1.select * from emp where deptno in(10,20);
2.select * from emp where deptno =10 or deptno =20;
3.select * from emp where deptno =10 union select * from emp where deptno =20;

select deptno,job,sum(sal) from emp group by deptno,job
union
select deptno,to_char(null),sum(sal) from emp group by deptno
union
select to_number(null),to_char(null),sum(sal) from emp;
<=>
select deptno,job,sum(sal) from emp group by rollup(deptno,job);


注意问题:
1.参与运算的各个集合必须列数相同且类型一致.
2.采用第一个集合作为最后的表头
3.order by 永远在最后
4.括号改变顺序.

查询练习

--找到员工表中工资最高的前三名
select rownum,empno,ename,sal from (select * from emp order by sal desc) where rownum <=3;

--oracle分页(Pageing Query)
select * from (select rownum r,empno,ename,sal from (select * from emp order by sal desc) where rownum <=8) where r>=5;
select rownum,e2.* from (select rownum r,empno,ename,sal from (select * from emp order by sal desc) where rownum <=8)e2  where r>=5;

--查询员工表中薪水大于本部门平均薪水的员工.
select e.empno,e.ename,e.sal,d.avgsal from emp e,(select deptno,avg(sal) avgsal from emp group by deptno ) d where e.deptno=d.deptno and e.sal >d.avgsal;
--相关子查询:将主查询的值作为参数传递给子查询.

--rowid:行号,代表一行存储的物理地址,不会改变.
--rownum:伪列,随时可能改变.
--select rowid,s.* from salgrade s;

--统计每年入职的员工个数(前提知道统计那些年份)
select count(*) Total,sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980",sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981",sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982",sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987" from emp;


注意:关于行号
1.rownum 永远按照默认顺序生成.
2.rownum 只能使用<=不能使用>=;

临时表:
1.手动创建:create temporary table xxxx;
2.自动创建:order by
特点:当事务或者会话结束的时候,表中数据自动删除.

SQL优化

1.使用具体字段名而不是*.
2.where解析顺序:右>>左.
3.尽量优先使用where.
4.尽量使用多表查询.