Oracle知识点总结

Oracle知识点总结根据阎赫老师讲义整理Zealjiang1、Oracle数据库的安装和配置⑴Oracle Internet级数据库SQLServer 中小企业级数据库Access 桌面级数据库⑵Oracle的安装注意：来源和目标的目录不允许有中文或空格⑶Oracle数据库启动时必需开启的后台服务①OracleOrahome90TNSListener 使第三方的软件或语言访问②OracleServiceETC Oracle的实例 CRUD 增删改查注意：②中的ETC是你起的实例的名字⑷Oracle的开发工具①DOS界面的开发平台 -> 运行->sqlplus②Oracle本身基于DOS的平台 ->运行-> sqlplusw③Oracle Enterprise Manager Console④PL/SQL Developer7.1.5⑸创建一个表的完整流程①创建一个数数库例子：创建一个数据库 ETC , 物理文件放在F:\ ，初始化1m ,自增长开启create tablespace etcdatafile 'f:\etc.dbf'size 1mautoextend on;删除表空间drop tablespace 数据库名称;②为该数据库创建一个帐号和密码语法： create user 用户名称identified by 密码default tablespace 数据库名称注意：1、在Oracle中账号和数据库one2one绑定2、Oracle的密码不允许为纯数字或空3、需要在system账号完成动作修改Oracle的账号密码语法：alter user 用户identified by 新密码③用grant 权限 to 用户为该帐户授权语法： grant 权限名称 to 用户;撤销权限语法：revoke 权限名称 from 用户名;④在该帐号下创建表⑹Oracle中的事务处理Transacation 事务特点：整体提交(commit) 整体回滚(rollback)事务的四个特性①原子性不可分割②持久性 -> 当数据整整写入到数据库物理文件中后，该数据被持久化③隔离性 -> 事务之间相互独立互不干扰④一致性 -> 数据安全⑺Oracle中的保留点(还原点)关键字：savepoint使用方法：savepoint 名称;如进行保留点回滚rollback to mark2⑻Oracle备份数据表数据①create table 备份表名称 as 查询语句;②Oracle备份表结构create table emp_bak as select * from emp where 1=2;2、Oracle的函数⑴function （系统预定义函数）函数和存储过程相比，在于函数必须有返回值，而存储过程只有输出参数。

语法：create or replace function 函数名称return 返回类型asPL/SQL语句块例子：输入一个员工工号，返回该员工所在部门平均工资create or replace function fun_demo1(eno emp.empno%type) return emp.sal%typeasavgsal emp.sal%type;beginselect avg(sal) into avgsal from emp where empno=eno; return avgsal;end;调用：select fun_demo1(7788) from dual;public int add(int num1,int num2){int res = 0;res = num1+num2;return res;}⑵单行函数(单值函数) ：函数有且只有返回1个值|-字符函数 substr instr①字符串连接函数：concatconcat(字符串1,字符串2)Oracle中字符连接操作符号 " || "②字符串对齐函数：lpad -> left padding 左填充 /rpad语法：lpad(字符串,预留位置,填充字符)SQL: select lpad(ename,10,' ') from emp;③字符串截取函数：substr -> substring语法：substr(字符串,截取的起始位置,截取个数)substr(字符串,截取的起始位置)SQL:> select substr(ename,1,3) frome emp;④字符查找函数：instr -> inner string语法：instr(字符串,待找的字符,查找的起始位置,出现次数) SQL:> select ename,instr(ename,'T',1,1) from emp;注意：当返回值为0时，字符不存在⑤函数：initcap -> initial captor语法：initcap(字符串)SQL:> select initcap(ename) from emp;⑥函数：length语法：length(字符串)SQL:> select * from emp where length(ename)=5;⑦函数：lower / upper语法：lower(字符串)SQL:> select lower(ename) from emp;|-日期函数 sysdate add_months①函数：sysdateSQL:> select sysdate from dual; dual-无实际意义，函数测试或其他测试使用②月份差：months_between语法：months_between(日期1,日期2)SQL: > select * from emp wheremonths_between(sysdate,hiredate)>=144③函数：last_day语法：last_day(日期数据)SQL:> select * from emp wherelast_day(hiredate)-2=hiredate;④函数：add_months语法：add_months(时间,添加月份)SQL：>select add_months(sysdate,3) from dual;|-转换函数 to_char to_date①转换函数 to_char语法：to_char(时间数据,制定格式)SQL:> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss d ddd day') from dual;yyyy-mm-dd 年月日hh24:mi:ss 时分秒(24)d 一周第几天ddd 一年的第几天day 星期几②to_date: 将文本类型数据按照制定格式转换成时间类型数据语法：to_date('具体的时间字符数据','时间字符串格式')to_date('2010-01-19','yyyy-mm-dd')注意：Oracle中的date字段类型及其特殊|-数学函数 abs ceil floorabs(n) 绝对值ceil(n) 向上取值 ceilling天花板 ceil(1.1) -> 2floor(n) 向下取值 floor地板 floor(2.9) -> 2mod(m,n) 求模取余 mod(5,2) -> 1power(m,n) m的n次方 power(2,8) ->256round(m,n) 四舍五入 m待处理数据 n精度 round(3.1415,2) -> 3.14trunc(m) 整数截取 trunc(3.1415) = 3sign(m) 符号sqrt(m) 平方根|-混合函数 user nvl①user : 返回当前登录账号SQL> select user from dual;②nvl : 替空函数语法：nvl(字符串,替换后的数据)注意：替换后的数据必须与该字段类型保持一致SQL> select ename,nvl(comm,0) from emp;⑶分析函数：数据分析和挖掘功能 Data Mining rank dense_rank作用：主要用于排名使用函数：rank①语法：rank() over (order by 字段名称)SQL:> select rank() over(order by sal desc),ename,sal from emp; 若排名相同下一个将跳转SQL:> select dense_rank() over(order by sal desc),ename,sal from emp; 连续②语法：rank(参数1，参数2) within group(order by 字段1，字段2)SQL:> select ename,rank(2850,'BLAKE') within group(order bysal,ename) from emp;⑷分组函数：类似于SQLServer中的聚合函数 sum avg countgroup by 分组关键字 having 条件3、Oracle数据库的查询⑴制定区间查询Oracle中伪列：Oracle为每张数据表(包括用户自定义创建的数据表)都自动创建两个位列分别是：rowid、rownum通过命令：SQL> desc 表名; --查看表结构rowid: 由18个字符组成唯一标识每一行rownum: 行号⑵介绍问题：使用Oracle分层查询以数据结构显示emp表中的人员关系语法：select lpad(ename,level*5,' ') from empconnect by prior empno=mgrstart with mgr is null;⑶并集：查询部门10的办事员和部门20的经理关键字：union allselect * from emp where deptno=10 and job='CLERK'union allselect * from emp where deptno=20 and job='MANAGER';注意：union all不会消除查询结果中的重复数据, union会消除查询结果中的重复记录⑷交集：查询部门10和部门20都有的工作类型关键字：intersectselect job from emp where deptno=10intersectselect job from emp where deptno=20;⑸差集：查询部门30中有，而部门10中没有的工作类型关键字：minusselect job from emp where deptno=30minusselect job from emp where deptno=10;4、Oracle数据库的数据对象Oracle的数据库对象：用户、表、约束、序列、视图、同义词和索引定义：但凡使用"create" 开头创建的对象称之为数据库对象。