Oracle的加强

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;--需求。查询没有奖金的用户,如果没有资金返回结果为0select * 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'),to_date('2015-03-11','yyyy-MM-dd')) from dual; --extract(c1 from d1),日期提取函数--c1 year,month,day hour minute secondselect 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;--数字的格式设置，注意，数字的格式占位符必须使用9select 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',10,'*') from dual;select LPAD('传智播客',10,'*') from dual; --RPAD(c1,n[,c2]),后填充函数select RPAD('abcd',10,'*') from dual;select RPAD('传智播客',10,'*') 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模式，它是用户的所有数据库对象的集合；一般在创建用户的同时会自动创建一个这样的模式，名称和用户名称一样。

用户

说明

sys

超级用户，主要用来维护系统信息和管理实例，以SYSDBA或SYSOPER角色登录。密码为在安装时设置的管理口令，如一般设置为：orcl

system

默认的系统管理员，拥有DBA权限，通常用来管理Oracle数据库的用户、权限和存储，以Normal方式登录。密码为在安装时设置的管理口令，如一般设置为：orcl

scott

示范用户，使用users表空间。一般该用户默认密码为tiger

 

--用户管理--创建用户/*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 asselect 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，40create 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,ename,sal,deptno) values(2,'李四',2000,20);insert into tb_emp(empno,ename,sal,deptno) values(3,'王五',300000,30);insert into tb_emp(empno,ename,sal,deptno) values(4,'赵六',4000,40);insert into tb_emp(empno,ename,sal,deptno) values(5,'陈六',4000,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,  ename varchar2(50) not null,  deptno    number(10) not null,  sal   number(8,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,ename,sal,deptno) values(1,'张三',1000,10);insert into tb_emp_1(empno,ename,sal,deptno) values(2,'李四',2000,20);insert into tb_emp_1(empno,ename,sal,deptno) values(3,'王五',300000,30);insert into tb_emp_1(empno,ename,sal,deptno) values(4,'赵六',4000,40);insert into tb_emp_1(empno,ename,sal,deptno) values(5,'陈六',4000,50); select * from tb_emp_1 partition(par3);select * from tb_emp_1 partition(par2); --如果不指定分区，查全表，如果指定分区，只查指定分区的数据