《Oracle知识点总结.docx》由会员分享，可在线阅读，更多相关《Oracle知识点总结.docx（42页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、精品名师归纳总结Oracle 学问点总结1、Oracle 数据库的安装和配置Oracle Internet级数据库SQLServer中小企业级数据库Access桌面级数据库Oracle 的安装留意： 来源和目标的目录不答应有中文或空格Oracle 数据库启动时必需开启的后台服务OracleOrahome90TNSListener使第三方的软件或语言拜访OracleServiceETC Oracle的实例CRUD增删改查留意：中的 ETC是你起的实例的名字Oracle 的开发工具DOS界面的开发平台 -运行-sqlplusOracle 本身基于 DOS的平台 - 运行- sqlpluswOrac
2、le Enterprise Manager ConsolePL/SQLDeveloper7.1.5创建一个表的完整流程创建一个数数库例子：创建一个数据库 ETC ,物理文件放在 F:,初始化 1m , 自增长开启create tablespace etc datafile f:etc.dbf可编辑资料 - - - 欢迎下载精品名师归纳总结size 1m autoextend on;删除表空间drop tablespace数据库名称 ;为该数据库创建一个帐号和密码语法： create user用户名称identified by密码default tablespace数据库名称留意： 1、在 Or
3、acle 中账号和数据库 one2one 绑定2 、Oracle 的密码不答应为纯数字或空3 、需要在 system 账号完成动作修改 Oracle 的账号密码语法： alter user用户identified by新密码用 grant权限 to用户 为该帐户授权语法： grant权限名称 to用户; 撤销权限语法： revoke权限名称 from用户名;在该帐号下创建表Oracle 中的事务处理可编辑资料 - - - 欢迎下载精品名师归纳总结Transacation事务特点：整体提交 commit整体回滚 rollback事务的四个特性原子性不行分割长久性化-当数据整整写入到数据库物理文件
4、中后, 该数据被 长久隔离性-事务之间相互独立互不干扰一样性-数据安全Oracle 中的保留点 仍原点关键字： savepoint使用方法： savepoint名称;如进行保留点回滚rollback to mark2Oracle 备份数据表数据create table备份表名称 as查询语句 ;Oracle 备份表结构create table emp_bak as select * from emp where 1=2; 2、Oracle 的函数function（系统预定义函数）函数和储备过程相比,在于函数必需有返回值,而储备过程只有输出参数。语法： create or replace fun
5、ction函数名称return返回类型可编辑资料 - - - 欢迎下载精品名师归纳总结asPL/SQL语句块例子：输入一个员工工号,返回该员工所在部门平均工资createor replacefunctionfun_demo1eno emp.empno%type return emp.sal%typeasavgsal emp.sal%type; beginselectavgsalintoavgsal from empwhere empno=eno; return avgsal;end;调用： select fun_demo17788 from dual;public int addint num
6、1,int num2 int res = 0;res = num1+num2;return res;单行函数 单值函数 ：函数有且只有返回1 个值|-字符函数 substr instr字符串连接函数： concat可编辑资料 - - - 欢迎下载精品名师归纳总结concat字符串 1, 字符串 2 Oracle中字符连接操作符号 | 字符串对齐函数： lpad - left padding左填充 /rpad语法： lpad 字符串, 预留位置 , 填充字符 SQL: select lpadename,10, from emp;字符串截取函数： substr - substring语法： sub
7、str 字符串, 截取的起始位置 , 截取个数 substr字符串, 截取的起始位置 SQL: select substrename,1,3 frome emp;字符查找函数： instr - inner string语法： instr字符串, 待找的字符 , 查找的起始位置 , 显现 次数 SQL: select ename,instrename,T,1,1 from emp;留意：当返回值为 0 时,字符不存在函数： initcap - initial captor语法： initcap字符串SQL: select initcapename from emp;函数： length语法： l
8、ength 字符串SQL: select * from emp where lengthename=5;函数： lower / upper语法： lower 字符串SQL: select lowerename from emp;可编辑资料 - - - 欢迎下载精品名师归纳总结|-日期函数 sysdate add_months函数： sysdateSQL: select sysdate from dual;dual-无实际意 义,函数测试或其他测试使用月份差： months_between语法： months_between日期 1, 日期 2SQL: select * from emp whe
9、re months_betweensysdate,hiredate=144函数： last_day语法： last_day日期数据 SQL: select * from emp where last_dayhiredate-2=hiredate;函数： add_months语法： add_months时间, 添加月份 SQL：select add_monthssysdate,3 from dual;|-转换函数 to_char to_date转换函数 to_char语法： to_char 时间数据 , 制定格式 SQL:selectto_charsysdate,yyyy-mm-ddhh24:m
10、i:ssd ddd day from dual;yyyy-mm-dd年月日可编辑资料 - - - 欢迎下载精品名师归纳总结hh24:mi:ss时分秒 24d一周第几天ddd一年的第几天day星期几to_date:将文本类型数据根据制定格式转换成时间类型数据语法： to_date详细的时间字符数据 ,时间字符串格式 to_date2022-01-19,yyyy-mm-dd留意： Oracle 中的 date 字段类型及其特别|-数学函数 abs ceil floor absn肯定值ceiln向上取值 ceilling天花板 ceil1.1 - 2floorn向下取值 floor的板 floor2
11、.9 - 2 modm,n求模取余 mod5,2 - 1powerm,n m的 n 次方 power2,8 -256roundm,n四舍五入 m 待处理数据 n 精度 round3.1415,2 - 3.14truncmsignm整数截取 trunc3.1415 = 3符号|-sqrtm混合函数 user :平方根user nvl返回当前登录账号SQL select user from dual;可编辑资料 - - - 欢迎下载精品名师归纳总结 nvl :替空函数语法： nvl 字符串, 替换后的数据 留意：替换后的数据必需与该字段类型保持一样SQL select ename,nvlcomm,
12、0 from emp;分析函数：数据分析和挖掘功能Data Mining rank dense_rank作用：主要用于排名使用函数： rank语法： rank over order by字段名称 SQL: select rank overorder by sal desc,ename,sal from emp;如排名相同下一个将跳转SQL:selectdense_rankoverorderby sal desc,ename,salfrom emp;连续语法： rank 参数 1,参数 2 within grouporder by字段 1,字段2SQL: select ename,rank285
13、0,BLAKE within grouporder by sal,ename from emp;分组函数：类似于 SQLServer 中的聚合函数 sum avg count group by分组关键字 having条件3、Oracle 数据库的查询制定区间查询Oracle 中伪列： Oracle 为每张数据表 包括用户自定义创建的数据表 都自动创建两个位列分别是： rowid、rownum可编辑资料 - - - 欢迎下载精品名师归纳总结通过命令： SQL desc 表名;-查看表结构rowid: 由 18 个字符组成唯独标识每一行rownum: 行号介绍 问题：使用 Oracle 分层查询以
14、数据结构显示 emp表中的人员关系语法： select lpadename,level*5, from emp connect 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会排除查询结果中的重复记录交集
15、：查询部门10 和部门 20 都有的工作类型关键字： intersectselect job from emp where deptno=10 intersectselect job from emp where deptno=20;差集：查询部门 30 中有,而部门 10 中没有的工作类型关键字： minusselect job from emp where deptno=30可编辑资料 - - - 欢迎下载精品名师归纳总结minusselect job from emp where deptno=10;4、Oracle 数据库的数据对象Oracle 的数据库对象：用户、表、约束、序列、视图
16、、同义词和索引定义：但凡使用 create 开头创建的对象称之为数据库对象。锁定/ 解除用户语法： alter user用户名称 account lock; 留意：该指令一般在 system 账号下输入解除用户锁定语法： alter user用户名称 account unlock;SQL alter user scott account unlock;留意： Oracle 11g中 scott账号是默认锁定的,需要进行解锁处理。用户授权 / 撤销grant权限名称 to用户名称 ;撤销用户权限revoke权限名称 from用户名;表数据字段的类型数字类型： number语法： number 长
17、度, 精度number5,2数字长度为 3,精度为 2范畴： -999.99 999.99可编辑资料 - - - 欢迎下载精品名师归纳总结number5数字长度为 5,精度为 0范畴： -99999 99999number精度默认为 0 范畴： -32767 32768字符类型： varchar2可变长度 char字符类型语法： varchar2 长度日期类型： datelong 数据类型： 该字段最大储备空间为2GB,该字段不答应添加索引LOB数据类型：该字段最大储备空间为4GB,该字段不答应添加索引CLOB：大字符类型 -文章 小说, cnkiBLOB：大二进制类型 -图片、音频、视频 .
18、多媒体文件FLOB：文件定位器 -内存指针创建数据表的语法结构：create table表名字段名称 1类型约束,字段名称 1类型约束,字段名称 n类型约束使用 desc表名 查看表结构给表添加字段 address 和 telephone语法： alter table表名 add 字段名称 类型;可编辑资料 - - - 欢迎下载精品名师归纳总结删除表字段 address语法： alter table表名 drop column字段名称 ;修改表中已有字段的类型语法： alter table表名 modify字段名称 新类型 ;修改表名称语法： rename 原表名 to新表名;约束完整性约束
19、=精确性 +一样性 约束的四大分类：实体完整性约束 行约束 - 尽量削减数据表中数据的冗余 重复的数据技术实施：主键约束、唯独约束语法： alter table表名 add primary key字段名称 ; altet table表名 add unique字段名称 ;添加非空约束语法： alter table表名 modify字段名称 not null;域完整性约束 列约束 -达到数据的精确性,掌握数据的大小或范畴或格式技术实施： check 检查约束alter table表名 add constraint约束名称 check 条件;引用完整性约束 表间约束 -达到数据的一样性技术实施：外键
20、约束可编辑资料 - - - 欢迎下载精品名师归纳总结添加外键约束alter table外键表 add constraint约束名称 foreign key外键字段 references主键表 主键字段 ;删除外键约束语法： alter table表名 drop constraint约束名称 ;删除匿名约束语法： alter table表名 modify字段名称 null;自定义完整性约束 -以上三种数据库内置约束不能满意开发人员的需求是,需要开发人员自定一些约束条件技术实施：触发器使用 sql 脚本批量插入数据使用命令 SQL 路径+文件名称SQL f:/a.sql;序列定义： Oracle
21、中使用 sequence 来实现字段的自增长功能,和SQLServer 中的 identity属性类型语法： create sequence序列名称start with起始数字increment by增长量;用法：序列对象通过两个重要的属性进行拜访取值.nextval - next value -下一个值可编辑资料 - - - 欢迎下载精品名师归纳总结例子： select seq_1.nextval from dual;.currval - current value -当前值例子： select seq_1.currval from dual;如何实现其自增长功能通过语法结构将序列对象与对应
22、的数据表进行绑定,实现其自增长功能。例子： create table usersuserid number2,username varchar210;create sequence seq_users start with 1 increment by 1; insert into users valuesseq_users.nextval,alvin;留意： Oracle 建议一个序列对象尽与一张数据表进行绑定技巧： create sequence序列名称 ;默认从 1 每次增长 1视图作用： 1、简化复杂的 SQL语句2、提高数据的拜访安全性语法： create or replace vi
23、ew视图名称as复杂的 SQL查询语句视图是一张虚拟的数据表,在 Table 对象中不存在,只存在于内存中留意：如视图由 1 张基表组成,修改视图将会修改基表数据,如视图由多张表组成,就不会修改基表数据同义词可编辑资料 - - - 欢迎下载精品名师归纳总结作用：Oracle 中的同义词供应各种数据库对象 表 的别名, 目的在于提高数据表拜访的安全性,特别多用户并发拜访时。语法： create synonym表的别名 for表名;SQL create synonym bak1 for emp;扩展：公有同义词 public语法： create public synonym别名 for表名;索引作
24、用：提高 SQL查询语句根据制定字段查询的效率语法： create index索引的名称 on数据表 字段例子：为 hiredate字段添加索引,提高按日期查询的SQL语句的效率create index idx_hire on emphiredate原理：空间换效率SQL 语句索引优化规章不带 where 条件的 SQL语句肯定不能使用索引在 where 条件中有索引的字段不能使用函数例子：假设我们查询在1987 年参与工作的全部员工SQL select * from emp whereto_charhiredate,yyyy=1987; SQL select * from emp where
25、 hiredate=to_date1987,yyyy;在 where 条件中有索引的字段不能参与运算例子：查询在 10000 天以前参与工作的员工信息SQL select * from emp where sysdate-hiredate10000;可编辑资料 - - - 欢迎下载精品名师归纳总结SQL select * from emp where hiredate selecte.*,d.dnamefrom empe,deptd where e.deptno=d.deptno and d.dname=ACCOUNTING;Oracle 中常用的数据字典Oracle是以中以表管表的模式,同时
26、数据字典是有表或视图组成。数据字典的分类：USER_xxx：表示当前用户所拥有的数据库对象ALL_xxxx：表示当前用户与权力查看的数据库对象DBA_xxxx：表示数据中所拥有的全部对象,只有在超级治理员级别下才可查看。xxxx：代表数据库对象的复数形式,例如： tables indexes .可编辑资料 - - - 欢迎下载精品名师归纳总结5、Oracle 数据库的多表查询等连接： select emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno;使用内连接模式编写：内连接： inner join .on.selec
27、t emp.ename,dept.dname from emp inner join dept on emp.deptno=dept.deptno;分析：部门为主显示字段, dept 为主表姓名为帮助显示字段, emp 为帮助表外连接： select d.dname,e.ename from emp e,dept d where e.deptno+=d.deptno;6、Oracle 数据库的游标游标 cursor游标的分类： 隐式游标 ： Oracle 自动应以一个隐式游标名称为SQL ,该游标不被程序员掌握,自动开启、操作及终止。显式游标 ： 可供程序员自己创建及操作|静态游标|动态游标静
28、态游标 loop 循周游标 / for 循周游标可编辑资料 - - - 欢迎下载精品名师归纳总结=loop 循周游标 =步骤 1：创建一个游标cursor 游标名称 is SQL 查询语句 ;步骤 2：开启游标open 游标名称 ;- 执行定义的 SQL 查询语句并将结果集合存放到游标中步骤 3：使用 loop 循环遍历游标中的数据并进行相应处理loopfetch 游标名称 into 变量;exit when 游标名称 %notfound;.end loop;步骤 4：关闭游标close 游标名称 ;例子：显示部门编号为10 的员工姓名declare-步骤 1：创建一个游标cursor myc
29、ur is select ename from emp where deptno=10; en emp.ename%type;可编辑资料 - - - 欢迎下载精品名师归纳总结begin-步骤 2：开启游标open mycur;-步骤 3：使用 loop 循环遍历游标中的数据并进行相应处理loopfetch mycur into en;exit when mycur%notfound; dbms_output.put_lineen;end loop;-步骤 4：关闭游标close mycur;end;=for 循周游标 = declare-步骤 1：创建一个游标cursor mycur is s
30、elect * from emp where deptno=10; erow emp%rowtype;begin-步骤 3：使用 for 循环遍历游标中的数据并进行相应处理for erow in mycur loopdbms_output.put_lineerow.ename;可编辑资料 - - - 欢迎下载精品名师归纳总结end loop; end;动态游标 扩展作用：使用游标变量高度重用 只能使用 loop 循环语法结构：步骤 1：声明一个动态游标类型type类型名称 is ref cursor;步骤 2：使用声明号的类型创建一个游标变量游标名称 类型名称 ;步骤 3：打开游标并且绑定 S
31、QL语句open游标名称 for SQL查询语句步骤 4：使用循环遍历游标中的数据并进行处理loopfetch游标名称 into变量; exit when游标名称 %notfound.数据处理 .end loop;步骤 5：重复 34步骤 6：关闭游标可编辑资料 - - - 欢迎下载精品名师归纳总结close游标名称 ;例子：显示部门 30 的员工姓名及 dept 表中全部部门名称declare-声明一个游标类型type cur is ref cursor;-声明游标变量mycur cur;-声明变量erow emp%rowtype; drow dept%rowtype; begin-打开游标
32、open mycur for select * from emp where deptno=30; loopfetch mycur into erow;exit when mycur%notfound; dbms_output.put_lineerow.ename; end loop; dbms_output.put_line=;-打开游标open mycur for select * from dept; loop可编辑资料 - - - 欢迎下载精品名师归纳总结fetch mycur into drow;exit when mycur%notfound; dbms_output.put_li
33、nedrow.dname; end loop;-关闭游标close mycur; end;游标全部属性游标名称 %属性名称属性名称含义notfound当游标中没有遍历的数据时返回 1found当游标中存在有遍历的数据时返回1isopen当游标开启状态时返回 1 rowcount返回游标影响行数7、Oracle 数据的 PL/SQLPL/SQL 块语法格式： declare声明.变量声明区begin程序开头.执行语句区exception反常处理部分可编辑资料 - - - 欢迎下载精品名师归纳总结.end;程序终止 ;变量的声明标量标识符,不区分大小写,先声明再使用变量声明的语法结构：标量名称类型
34、; 例子： declareeno number4; edate date;beginend; 属性引用类型|-字段引用类型例子： declareeno emp.ename%type;|-行引用类型 例子： declareerow emp%rowtype;变量的赋值四种方式：声明时赋值可编辑资料 - - - 欢迎下载精品名师归纳总结赋值运算符 “:= ”例子：声明一个变量赋值为 2 declarenum1 number2:=2;.begin.end;执行区赋值例子：声明一个变量赋值为 2 declarenum1 number2;.begin num1:=2;.end;对话框赋值 -程序与用户之间
35、进行数据交互例子：声明一个变量赋值为 2declarenum1 number2;.可编辑资料 - - - 欢迎下载精品名师归纳总结beginnum1:=& 请输入数字 ;.end;留意点：当输入字符串类型数据的使用书写格式为：name:=&姓名;当输入数字类型数据的使用书写格式为：age:=&年龄; select.into.赋值模式例子：将 emp表中 simth 的工资复制到变量 esal 中declareesal emp.sal%type; beginselect sal into esal from emp where ename=SMITH;end;Oracle 的 PL/SQL中屏幕
36、输出语句语法： dbms_output.put_linexxxxxxx;将信息输出并换行dbms_output.putxxxxxxx;输出不换行留意： Oracle 默认关闭屏幕输出功能,需要开发人员输入指令将其功能开启SQL set serveroutput on;PL/SQL中的反常处理语法： exception可编辑资料 - - - 欢迎下载精品名师归纳总结when反常名称 then.处理方式 .反常： others -任何反常都可捕捉执行语句部分 循环的使用： for while loopfor语法结构： for循环变量 in起始数字 . 终止数字loop.循环体.end loop;例
37、子 1：显示数字 101 reversedeclare i number; beginfor i in reverse 1. 10loopdbms_output.put_linei; end loop;end;例子 1：显示乘法口诀declarei number;可编辑资料 - - - 欢迎下载精品名师归纳总结j number; begin-外层循环掌握行数for i in 1.9 loop-内层循环掌握个数for j in 1.i loopdbms_output.putj|*|i|=|i*j|; end loop;-换行dbms_output.put_line ; end loop;end;
38、 while 循环语法： while进入条件loop.循环体.end loop; loop循环语法： loopexit when推出条件 ;可编辑资料 - - - 欢迎下载精品名师归纳总结.循环体.end loop; 条件判定语法： if条件 thenelsif条件 thenelseend if;例子：模拟一个登录结构,用户名为admin 密码为 chinasoftdeclareusername varchar210; password varchar210;begin-用户输入账号及密码username:=&账号; password:=&密码;-规律判定if username=admin a
39、nd password=chinasoft then dbms_output.put_line欢迎登录： |username;可编辑资料 - - - 欢迎下载精品名师归纳总结elsedbms_output.put_line账号或密码错误！ ; end if;end;处理自定义反常例子：如用户输入的数字不再 110 之内就抛出反常步骤 1：创建一个反常对象在变量声明区输入一下代码exp exception;-声明一个反常对象,名称为 exp步骤 2：编写条件判定结构抛出反常在语句执行区编写一下代码ifthenraise反常对象名称 ;-抛出反常end if;步骤 3：反常捕捉在语句执行区编写exceptionwhen反常对象名称 then.处理方式