Oracle常用函数

常用的函数包括分析函数日期函数，字符函数等

--分析函数
--需求，查询员工的信息，先按部门排序，再按工资排序，每个部门最高工资的员工？
--rank() over( )  --rank有断号，有重复
--dese_rank() over( )  --dese_rank没断号，有重复
--row_number() over( ) --没断号，也没重复
select * from (select emp.*,row_number()  over( partition by deptno order by sal desc)  as r from emp) where r=1;
 
--需求：查询每个岗位的最高工资的前3个员工
select rownum,tmp.* from  (select * from emp order by job,sal desc) tmp;
--作用，将两次的排序放在结果上，而且返回分类后统计的序列
select * from (select emp.*,row_number() over( partition by job order by sal desc ) as r from emp) where r<=3 ;
--应用场景，需要两次排序，而且排序以后还需要条件过滤的情况可以使用分析函数
 
--其它数据
--NVL(e1,e2),当为null时,代替值
--e1,为null就返回e2的值
--需求。查询奖金大于等于300的员工
select * from emp where comm >=300;
--需求。查询没有奖金的用户,如果没有资金返回结果为0
select * from emp where comm =0 or comm is null;
select ename,nvl(comm,0) from emp where nvl(comm,0)=0;
--NVL2 (e1,e2,e3)
--e1,源字符串，
--e2,如果不为null,返回这个值
--e3,如果为null,返回这个值
select ename,nvl2(comm,'有奖金','没有奖金') from emp where nvl(comm,0)=0;
 
--decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
 
--需求，查询员工信息，返回对应部分的字称
select ename,decode(deptno,10,'综合部',20,'财务部',30,'技术部') from emp;
 

日期函数：

--日期函数
 
--查询当前日期
select sysdate from dual;
 
--增加月份add_months(d1,n1)
--d1:源日期
--n1:正数为加，负为减
select to_char(add_months(sysdate,-1),'yyyy-MM-dd') from dual;
 
--months_between(d1,d2)，对比日期的前后
/*
如果d1>d2，则返回正数
如果d1<d2，则返回负数
*/
 
select months_between(to_date('2016-02-09','yyyy-MM-dd'),to_date('2015-03-11','yyyy-MM-dd')) from dual;
 
select months_between(to_date('2014-02-09','yyyy-MM-dd')) from dual;
 
--extract(c1 from d1),日期提取函数
--c1 year,month,day hour minute second
select extract(year from sysdate)  from dual;
select extract(month from sysdate)  from dual;
select extract(day from sysdate)  from dual;
 
select extract(hour from to_timestamp('2016-09-12 12:13:45','yyyy-MM-dd HH:mi:ss'))  from dual;
select extract(minute from to_timestamp('2016-09-12 12:13:45','yyyy-MM-dd HH:mi:ss'))  from dual;
select extract(second from to_timestamp('2016-09-12 12:13:45','yyyy-MM-dd HH:mi:ss'))  from dual;
 
 
--需求：查询入职为1981年的的员工
select * from emp where extract(year from hiredate)=1981;
--需求：查询员工5月份入职
select * from emp where extract(month from hiredate)=5;
 
--to_char,转换函数，将数字或日期转为字符串
select to_char(sysdate,'yyyy-MM-dd') from dual;
select to_char(to_timestamp('1981-09-03 12:44:23','yyyy-MM-dd HH:mi:ss'),'yyyy-MM-dd HH:mi:ss') from dual;
--数字的格式设置，注意，数字的格式占位符必须使用9
select to_char(88888888,'$999,999,999') from dual;
 
--TO_NUMBER(X[[,c2],c3])，字符串转数据
select TO_NUMBER('23')+9 from dual;

数值函数和字符串函数：

--常用函数
--数值处理函数
--四舍五入
--round(x[,y])
--x:表示源数据，y表示精度
--需求；查询每个部门的平均工资，平均工资只保留两位，四舍五入
--y:是正数，精确小数点后的值
--y:是负数，精确的是小数点前的值
select avg(sal) from emp group by deptno ;
select round(avg(sal),-2) from emp group by deptno ;
 
--需求；查询每个部门的平均工资，平均工资只保留两位，截取
--trunc(x[,y])
select trunc(avg(sal),2) from emp group by deptno ;
 
 
--1.2字符型函数
--LENGTH(c1) 字符串长度计算函数
--不管汉字和英文都是算一个字符
select length('传智播客') from dual;
 
select length('abcd') from dual;
--LPAD(c1,n[,c2])，前填充函数.汉字是两个字符，英文字母是一个字符
select LPAD('abcd','*') from dual;
select LPAD('传智播客','*') from dual;
 
--RPAD(c1,c2]),后填充函数
select RPAD('abcd','*') from dual;
select RPAD('传智播客','*') from dual;
 
--REPLACE(c1,c2[,c3]) 代替函数
--c1:源字符串
--c2:被替换的字符串
--c3:替换的字符串
select replace('my name is itcast','my','your' ) from dual;
 
--SUBSTR(c1,n1[,n2])
--从1开始，不包括截取的位置
--c1:源字符串
--n1：截取的开始位置（截取时不包括该位置的字符）
--n2: 截取的字符的位数
select substr('mynameisitcast',1,2) from dual;

表空间：

表空间是数据库中最大的逻辑单元，Oracle数据库采用表空间将相关的逻辑组件组合在一起，一个Oracle数据库至少包含一个表空间。每个表空间由一个或者多个数据文件组成，一个数据文件只能与一个表空间相联系。

在每个数据库中都有一个名为SYSTEM的表空间，即是系统表空间，该表空间是在创建数据库或者数据库安装时自动创建的，用于存储系统的数据字典表、程序单元、过程、函数、包和触发器等。

表空间的类型：

永久性表空间：一般保存表、视图、过程和索引等的数据

临时性表空间：只用于保存系统中短期活动的数据

撤销性表空间：用来帮助回退未提交的事务数据

表空间的操作：

---oracle数据库的管理模式，使用用户模式
--用户模式，一般情况一下，oracle数据库管理系统，就只管理一个数据库。
--使用用户来分隔不同的空间
--用户本身没有存储的空间，每个用户的东西都是放在同一个数据库里面的。
--使用表空间来分隔
---MysqL里面一个数据库就是一个文件夹
--oracle一个数据文件就是一个表空间
 
--表空间的格式
/*
create tablespace <表空间名>
  datafile '路径/文件名'  --如果不指定路径默认在D:\app\Administrator\product\11.2.0\dbhome_1\database
  size  大小
  [autoextend on] --如果超出表空间的大小，需不需要自动加增空间
  [maxsize 大小]，默认不写 值为unlimit
  [next  大小] --如果支持自动增加大小，每次增加多大
;
*/
 
create tablespace itcast_tbsp datafile 'itcast_tbsp.dbf' size 10m;
 
 
---查看表空间
select * from dba_data_files;
 
--删除表空间
drop tablespace ITCAST_TS;  --逻辑删除，不删除文件
drop tablespace ITCAST_TBSP including contents and datafiles;
 
--修改表空间
--一个表空间可以有多个文件
--增加文件
alter tablespace ITCAST_TBSP add datafile 'ITCAST_TBSP_01.dbf' size 10m;
 
--删除文件
alter tablespace ITCAST_TBSP drop datafile 'ITCAST_TBSP_01.dbf';

用户管理：

Oracle有个schema模式，它是用户的所有数据库对象的集合；一般在创建用户的同时会自动创建一个这样的模式，名称和用户名称一样。

--用户管理
--创建用户
/*
create user <用户名> identified by <密码>
   default tablespace <表空间>
   [temporary tablespace <临时表空间>];
*/
--查看用户
select * from all_users;  --查看用户的简单信息
select * from dba_users;  --用户的详细信息
create user itcast identified by orcl
 default tablespace ITCAST_TBSP
 temporary tablespace temp;
 
 --删除用户
 drop user itcast cascade; --删除用户并且它的所有相关数据
 
 --修改用户
 
 --修改密码
 alter user itcast identified by itcast;
 
 --解锁和锁定
 alter user scott account lock;
 
 --用户解锁
 alter user scott account unlock;
 
 ---用户的授权(DCL,数据控制语言)
 /*
 系统以及角色授权的格式
 grant 系统权限/角色 to <用户名>
 
 
 */
 --查看系统的角色以及系统权限
 select * from dba_sys_privs;
/*
常见的系统角色
CONNECT --连接角色，提供用户登录权限
RESOURCE --资源角色，提供用户常见的操作权限
DBA      --管理员角色，如果设置了这个角色就是一个普通管理员
 
*/
grant connect to itcast;
--等同于
grant create session to itcast;
 
--查看connect角色的权限
--一般情况下，有create的权限就包括了删除以及更改的权限
select * from dba_sys_privs where grantee='CONNECT';
select * from dba_sys_privs where grantee='RESOURCE';
 
grant resource to itcast;
 
---itcast能不能访问scott用户的表
select * from scott.emp;
 
--如果让itcast用户有权限访问scott用户的表
--解决方案： 授权对象权限（只有增删查改四个权限）
/*
grant insert|delete|update|select on 用户名.表名 to 授予权限的用户
 
*/
grant select on scott.emp to itcast;
grant update,insert,delete on scott.emp to itcast;
create table emp as select * from scott.emp;
 
--查看当前用户的权限
--查看当前用户的系统权限
select * from user_sys_privs;
--查看当前用户的角色
select * from user_role_privs;
--查看当前用户的对象权限
select * from user_tab_privs;
--查看当前用户的角色权限
select * from role_sys_privs;
 
---撤权
--撤消对象权限
--语法:revoke insert,delete,select,update on 用户名.表名 from <用户名>
revoke insert,delete on scott.emp from itcast;
--撤消系统或角色权限
--语法:revoke 角色/系统权限 from <用户名>
revoke connect from itcast;
revoke create session from itcast;

视图：

视图是由一个或者多个表组成的虚拟表；那些用于产生视图的表叫做该视图的基表。视图不占用物理空间，视图本身的定义语句还是要存储在数据字典里的。视图只有逻辑定义，每次使用的时候只是重新执行sql。一个视图也可以从另一个视图中产生，视图没有存储真正的数据，真正的数据还是存储在表中。一般出于对基本的安全性和常用的查询语句会建立视图；并且一般情况下不对视图进行新增、更新等操作。

--视图
 
--作用
select * from emp,dept where emp.deptno=dept.deptno;
 
/*
create [or replace] view <视图名> as
 
 select语法
 
 
*/
--授予创建视图的权限
grant create view to scott;
select * from role_sys_privs;
 
create or replace view vw_emp_all as
select emp.*,dept.dname,dept.loc from emp,dept where emp.deptno=dept.deptno;
 
 
select * from vw_emp_all;
 
--查看视图
select * from user_views;
drop view emp_all;
 
---注意事项目，
--单表视图支持增删查改(oracle的特性)，多表视图只能查。
 
--单表视图
create or replace view vw_emp as select * from emp;
 
select * from vw_emp;
 
--插入数据,视图只是一个表的虚拟表的没有存储数据空间，所以数据是插入到视图对应的表里面的。
insert into vw_emp(empno,ename) values(2000,'陈七');
update  vw_emp set sal=4000 where empno=2000;
delete from vw_emp where empno=2000;

同义词：

同义词是数据库模式对象的一个别名，用于简化对象访问和提高对象访问的安全性。同义词与视图相似，同义词并不占用实际存储空间，只有在数据字典中保存了同义词的定义。在Oracle数据库中的大部分数据库对象中，数据库管理员都可以根据实际情况为他们定义同义词。

同义词的类型：

私有同义词：私有Oracle同义词由创建它的用户所有；创建的用户需要具有Create synonym的权限。

公有同义词：公有Oracle同义词由一个public所拥有。用于标识一些比较普通的数据库对象，一般由管理员用户创建及删除，需要具有create public synonym权限。

---私有同义词，就是数据库对象别名。。只能当前用户使用，其它有权限的用户可以使用用户调用。
/*
  create synonmy <同义词> for 表|视图
*/
---查看同义词
select * from user_synonyms;
--授权
grant create synonym to scott;
--创建同义词
create synonym e for emp;
 
--查询,私有同义词，可以使用用户调用
select * from scott.e;
insert into e(empno,ename) values('2001','小天一');
 
--删除同义词
drop synonym e;
 
--共有同义词
/*
创建公有同义词
create public synonym <同义词> for 表名|视图名
*/
 
grant create public synonym to scott;
create public synonym pu_e for emp;
--当前用户调用是一样的。
select * from pu_e;
--非当前用户，不需要加上所属的用户名
select * from pu_e;
 
--删除公有同义词
drop public synonym pu_e;

索引：

索引是建立数据库中的某些列的上面，是与表关联的，可以提供快速访问数据方式，但是会影响增删改的效率。

建立索引的时候：

1、在经常需要搜索、主键、连接的列上

2、表很大，记录内容分布范围很广

3、在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的

4、在经常使用在where子句中的列上面创建索引

--100万条数据，如果全扫描查询都要十几秒，如果索引创建合理，可以优化到1秒
/*
create [unique] index <索引名> on 表名(列表 asc|desc);
*/
 
--创建一个非唯一索引
create index ind_name on emp(ename asc);
--查看索引
select * from user_indexes;
 
--删除索引
drop index ind_name;
 
--如果创建唯一索引，这个列一定要加上唯一约束
create unique index ind_empno on emp(ename asc);

序列：

序列是oracle中提供的一个产生唯一数值型值的机制。通常用于表的主键自增长，序列只能保证唯一但是不保证连续。通常是使用触发器和序列实现oracle的主键自增长。

--oracle默认是不支持像MysqL那样表的自增长的
 
create sequence seq_emp
  increment by 1   --步长为1
  start with   1   --开始位置
  --maxvalue  
  nomaxvalue
  --minvalue     --最小最
  nocycle        --如果超出了最大值，不重新开始计数
  ;
  
  ---查看序列
  select * from SYS.user_sequences;
  
  --使用序列
  --获取下一个值，而且计数加1
  select seq_emp.nextval from dual;
  --获取当前值
  select seq_emp.currval from dual;
  --使用序列实现自增长
  insert into emp(empno,ename) values(seq_emp.nextval,'12346');
  
  drop sequence seq_emp;
  
  --倒序序列
  
  create sequence seq_emp
  increment by -1   --步长为1
  start with   5000   --开始位置
  maxvalue     5000   --倒序需要指定最大大小
  --nomaxvalue
  --minvalue     --最小最
  nocycle        --如果超出了最大值，不重新开始计数
  ;
   select seq_emp.nextval from dual;

分区分表：

分区表是通过对分区列的判断，把分区列不同的记录，放在不同的分区中。分区完全对应用透明。Oracle的分区表可以包括多个分区，每个分区都是一个独立的段，可以存放到不同的表空间中。查询时可以通过查询表来访问各个分区中的数据，也可以通过在查询时直接指定分区的方法来进行查询。

优点：

1、由于将数据分散到各个分区中，减少了数据损坏的可能性

2、可以对单独的分区进行备份和恢复

3、可以将分区映射到不同的物理磁盘上来分散IO

4、提高可管理性、可用性和性能

分区表的类型：

分区分为范围分区，散列分区，列表分区，复合分区、间隔分区等

--分区表的创建格式
/*
--列表分区
create table <表名>(
   字段 数据类型 [not null] [primary key]
   ....
)
partition by list(字段名)(
  partion p1  value(值),partion p2  value(值)
  partion p3  value(值)  -如果值为默认default
 
);
 
 
 
*/
 
--需求：创建一个员工表，使用部门分区10，20，30，40
create table tb_emp(
  empno number(10) not null primary key,ename varchar2(50) not null,deptno    number(10) not null,sal   number(8,2) not null
 
)partition by list(deptno)(
  partition p1  values(10),partition p2  values(20),partition p3  values(30),partition p4  values(default)
 
);
 
insert into tb_emp(empno,ename,sal,deptno) values(1,'张三',1000,10);
insert into tb_emp(empno,deptno) values(2,'李四',2000,20);
insert into tb_emp(empno,deptno) values(3,'王五',300000,30);
insert into tb_emp(empno,deptno) values(4,'赵六',4000,40);
insert into tb_emp(empno,deptno) values(5,'陈六',50);
 
--全表查询
select * from tb_emp;
--分区查询,指定分区名
select * from tb_emp partition(p4);
 
--查看表分区
select * from  user_tab_partitions;
 
--范围分区
--需求：使用工资范围分区，0-1999，2000-3999，4000以上的区
 
create table tb_emp_1(
  empno number(10) not null primary key,2) not null
 
)partition by range(sal)(
  partition par1  values less than(2000),partition par2  values less than (4000),--范围不括4000
  partition par3  values less than (maxvalue)
);
 
insert into tb_emp_1(empno,10);
insert into tb_emp_1(empno,20);
insert into tb_emp_1(empno,30);
insert into tb_emp_1(empno,40);
insert into tb_emp_1(empno,50);
 
select * from tb_emp_1 partition(par3);
select * from tb_emp_1 partition(par2);
 
--如果不指定分区，查全表，如果指定分区，只查指定分区的数据