《2022年Oracle知识点.docx》由会员分享，可在线阅读，更多相关《2022年Oracle知识点.docx（25页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -学习好资料 欢迎下载DDL_DML_DQL 创建用户：CREATE USER MARTIN -创建用户名IDENTIFIED BY martinpwd -设定密码DEFAULT TABLESPACE USERS-设置表空间TEMPORARY TABLESPACE TEMP;-暂时表空间授予权限：GRANT CONNECT TO MARTIN; CONNECT角色答应用户连接至数据库,并创建数据库对象；GRANT RESOURCE TO MARTIN; RESOURCE角色答应用户使用,数据库中的储备空间；GR
2、ANT CREATE SEQUENCE TO MARTIN; 此系统权限答应用户在当前模式中创建序列,此权限包含在授予用户MARTIN 操作 TEST表对象的权限：CONNECT角色中；GRANT SELECT ON TEST TO MARTIN; 答应用户查询 TEST 表的记录；GRANT ALL ON TEST TO MARTIN; 答应用户插入、删除、更新和查询；ALTER USER 命令可用于更换口令：ALTER USER MARTIN IDENTIFIED BY martinpass; DROP USER 命令用于删除用户：DROP USER MARTIN CASCADE; DDL
3、: 创建表 create table table_name column1 varchar232 primary key, - 列名类型 长度 修饰符 primary key 代表该列为主键column2 varcahr220 not null default 默认值 , - not null 设定该列不能为空, default 设定该列的默认值column3 number5,3 unique - 设定该列值唯独 ,不能重复 ; 修改表 alter table table_name addcolumn varchar220 ; -添加列 modify -修改列 remove -删除列 清空表
4、truncate table table_name; -清空之后不能回滚 删除表细心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 1 页,共 14 页 - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -学习好资料 欢迎下载drop table table_name; DML: 插入数据insert into table_namecolumn1, column2, . valuesvalue1, value2, value3.; -值必需与指定列一一对应insert into tab
5、le_name valuesvalue1, value2, value3.; - 值必需与表定义结构一样insert into table_name select column1,colum2. from table_name1 where . - 将从 table_name1 中查询到的结果插入到 table_name 中 修改数据 update table_name set column1=value1,column2=value2. where column3=value3 and . 删除数据 delete from table_name where column1=value1 an
6、d . DQL: 查询数据 select column1,column2. from table_name where column1=value1 and . 排序 order by column desc/asc - desc 降序 ,asc 升序 分组 group by column having column1=value1 - having 是分组条件 查询条件select . from . where column1 in value1, value2, value3 - 该记录查询出来select . from . where column1 between value1 and
7、 value2 和 value2 之间时将该记录查询出来当 column1 在括号中显现就将- 当 column1 的值在 value1select . from . where column1 like _value% - 模糊查询_代表一个字符, %代表多个字符select . from . where column1 is null 询出来连接查询- 当 column1 的值为 null 就将该记录查左外连接 -以左边表为基准依据 on 条件查找右边的表对应数据生成一个暂时表供查询 select . from table1 t1 left outer join table2 t2 on
8、t1.column1 = t2.column2 where . 细心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 2 页,共 14 页 - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -学习好资料 欢迎下载右外连接 -以右边表为基准依据 select . from table1 t1 right outer join table2 t2 on on 条件查找左边表对应数据生成一个暂时表供查询t1.column1 = t2.column2 where . 内连接 -以两边表为基准 写
9、法一 select . from table1 t1 inner join table2 t2 on ,当一边显现空值时不选取该行数据t1.column1 = t2.column2 where . 写法二 select . from table1 t1, table2 t2 where t1.column1 = t2.column2 and . 全连接 -一两边表为基准,当一边有值时查询改行,另一边以空值代替select . from table1 t1 full join table2 t2 on 细心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 3
10、 页,共 14 页 - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -学习好资料 欢迎下载t1.column1 = t2.column2 where . exists-当括号中查询有值时,该条记录将被查询出来,通常用 exists 来代替 in select * from t_school sc where exists select 1 from dual where sc.id 4 查询并集 -union- 默认去重复行 ,假如要不去重复使用 union all select 1 from dual where sc.
11、id = 1 union select 1 from dual where sc.id = 2 union select 1 from dual where sc.id = 5 union select 1 from dual where sc.id = 6 查询交集 -intersect select * from t_person where id 100 intersect select * from t_person where height 1.70 查询差集 -minus 函数 _锁_表分区sysdate - 获得系统当前时间 函数to_date2022-09-02 09:04:0
12、0, YYYY-MM-DD HH24:MI:SS - 将字符串依据指定的格式转 换为时间类型串to_charsysdate, YYYY-MM-DD HH24:MI:SS - 将时间对象依据指定的格式转换为字符add_monthssysdate, 2 - 为时间加上指定月份值last_daysysdate - 取当月最终一天truncsysdate, MM - 按指定格式截取时间 ,将指定格式之后的值都取 0 lowerABC - 将字符串转换为小写upperabc - 将字符串转为大写substrabcdefg, 2, 3- 将字符串从下标 2 开头截取 3 个字符initcaphelLo w
13、oRld - 将字符串格式化 ,每个单词首字母大写 ,其余字符小写trim hello - 去掉字符串两端的空格字符细心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 4 页,共 14 页 - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -lengthabc 学习好资料欢迎下载- 猎取字符串的长度decode - 相当于 java 中的 switch, 当 column 的值为 condition1 时,取 value1.假如找不到对应的 condition, 取 defaultVa
14、lue, 假如找不到 defaultValue, 取 null decode column, condition1, value1, condition2, value2, condition3, value3, . defaultValue abs-20 - 取肯定值ceil12.01 - 13 取大于该小数的最小整数floor12.06 - 12 取小于该小数的最大整数powerx,y - 求 x 的 y 次方roundm, n - 将 m 值保留 n 位小数 ,对 n+1 位四舍五入truncm,n - 将 m 值保留 n 位小数 ,不会对 n+1 位四舍五入nvlcolumn, val
15、ue - 当 column 不为 null 时,取 column 的值 ,否就取 value 值nvl2column, value1, value2 - 当 column 不为空时 ,取 value1,否就取 value2 nullifcolumn1, column2 - 当 column1 的值等会column2 的值时 ,返回null, 否就返回column1 的值avgcolumn - 求平均数mincolumn - 取最小值maxcolumn - 取最大值sumcolumn - 求和countcolumn - 求总记录数分析函数 : 主要用来做累计排名row_number - 直接累计
16、排名 ,当值相同时排名也不相同rank - 当值相同时 ,排名相同 ,后续记录跳动相应的排名数dense_rank - 当值相同时 ,排名相同 ,后续记录不跳动- 使用时必需用 over 函数指定排名规章row_number overorder by height desc as he_order 锁锁是数据库用来掌握共享资源并发拜访的机制；锁用于爱护正在被修改的数据直到提交或回滚了事务之后,其他用户才可以更新数据行级锁当执行 insert,update,delete,select.from.for update 表级锁共享锁 : 答应多个用户同时锁定同一张表,锁定表之后其他用户只能查询数据,
17、不能更新lock table table_name in share mode 排他锁 : 在同一时间仅答应一个用户锁定一张表,其他用户要锁定表必需等待该用户细心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 5 页,共 14 页 - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -学习好资料 欢迎下载释放锁 ,使用 nowait 不等待 ,直接报错lock table table_name in exclusive mode 当两个事务相互等待对方释放资源时,就会形成死锁,Oracl
18、e 会自动检测死锁,并通过结束其中的一个事务来解决死锁 ,右边是一个死锁的例子表分区 : 答应将一张表中的数据分成多个分区来储备 范畴分区,提高检索速度 ,保证数据安全 . 以表中一个列或一组列的值进行范畴段划分来分区 ,该列尽可能的是数值或日期型 create table t_employee id number8 primary key, name varchar20, salary number10, 2 partition by rangesalary partition pa1 values less than5000, partition pa2 values less than1
19、0000, partition pa3 values less than20000, partition pa4 values less thanmaxvalue - 当列的值为null,也被划分到maxvalue 分区 查询或删除的时候可以使用partition 分区名 来指定要查询的分区select * from t_employee partitionpa4; delete from t_employee partitionpa1; 散列分区答应用户对不具有规律范畴的数据进行分区,通过在分区键上执行HASH函数打算储备的分区 ,将数据平均地分布到不同的分区partition by has
20、h salary partition p1, partition p2 列表分区答应用户将不相关的数据组织在一起 create table t_employee id number8 primary key, name varchar20, department varchar220, salary number10, 2 partition by listdepartment ,依据指定列的值分类分区partition jishubu values 技术部 , partition caiwubu values 财务部 , partition xzrs values 行政部 , 人事部 , 细
21、心整理归纳 精选学习资料 partition hwb values海外部 第 6 页,共 14 页 - - - - - - - - - - - - - - - - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -学习好资料 欢迎下载 复合分区 范畴分区与散列分区或列表分区的组合 ,可以建立子分区添加分区 alter table table_name add partition partition_name values less than. - 范畴分区 alter table table_name add partitio
22、n partition_name values. - 列表分区 alter table table_name add partition partition_name; - 散列分 区 删除分区 alter table table_name drop partition partition_name; 截断分区 alter table table_name truncate partition partition_name; 合并分区 alter table table_name merge partitions partition1,partition2 into partition par
23、tition_name; 拆分分区 alter table table_name split partition partition_name at. intopartition p1, partition p2 数据库对象数据库对象是规律结构的集合,最基本的数据库对象是表 . 同义词公有同义词可被全部的数据库用户拜访；CREATE SYNONYM emp FOR SCOTT.emp; CREATE OR REPLACE SYNONYM emp_syn FOR SCOTT.emp; 私有同义词只能在其模式内拜访,且不能与当前模式的对象同名；CREATE PUBLIC SYNONYM emp_s
24、yn FOR SCOTT.emp; 序列序列是用于生成唯独、连续序号的对象,序列可以是升序的,也可以是降序的,使用CREATE SEQUENCE 语句创建序列CREATE SEQUENCE toys_seq START WITH 10 INCREMENT BY 10 MAXVALUE 2000 MINVALUE 10 NOCYCLE CACHE 10; 序列名 .NEXTVAL 返回序列的下一个值 序列名 .CURRVAL 返回序列的当前值使用 ALTER SEQUENCE 语句修改序列,不能更换序列的START WITH参数使用 DROP SEQUENCE语句删除序列 ,如:DROP SEQ
25、UENCE toys_seq; 细心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 7 页,共 14 页 - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -学习好资料 欢迎下载视图视图以经过定制的方式显示来自一个或多个表的数据 储的查询”,创建视图所依据的表称为“ 基表”视图的优点有：供应了另外一种级别的表安全性 隐匿的数据的复杂性简化的用户的 SQL命令 隔离基表结构的转变 通过重命名列,从另一个角度供应数据 创建视图的语法 : CREATE OR REPLACE FORCE VI
26、EW view_name alias, alias. AS select_statement WITH CHECK OPTION WITH READ ONLY; ,视图可以视为“ 虚拟表” 或“ 存在视图上也可以使用修改数据的DML 语句,如INSERT、UPDATE和 DELETE 视图上的 DML 语句有如下限制：只能修改一个底层的基表 假如修改违反了基表的约束条件,就无法更新视图假如视图包含连接操作符、DISTINCT 关键字、集合操作符、聚合函数或GROUP BY 子句,就将无法更新视图假如视图包含伪列或表达式,就将无法更新视图索引创建标准索引 CREATE INDEX item_in
27、dex ON itemfile itemcode TABLESPACE index_tbs; 重建索引 ALTER INDEX item_index REBUILD; 删除索引 DROP INDEX item_index; PL_SQL PL/SQL 标量 |- 数值 |- 字符|- 布尔-boolean PL/SQL所特有的 ,不能用于 sql语句中 ,不能作为表的字段类型|- 日期 lob |-BLOB 细心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 8 页,共 14 页 - - - - - - - - - 名师归纳总结 精品学习资料 - - -
28、 - - - - - - - - - - - -学习好资料 欢迎下载|-CLOB |-NCLOB 属性|-%type -列类型用户名 .表名 .列名 %type |-%rowtype -行类型用户名 .表名 %rowtype PL/SQL块 是 PL/SQL程序的基本单元 主要由三个部分构成 : 声明部分 ,可执行部分 ,反常处理部分 declare /* 声明变量 */ begin /* 可执行部分 */ exception /* 反常处理部分 */ end; 变量声明 变量名 constant 是否常量 数据类型 长度 := 值; 变量赋值 1. := 2. select column1,
29、column2,column3. into temp1,temp2,temp3. from . where . 2.1 into 的变量次序和类型必需跟查询出来的结果一样 2.2 查询出来的结果只能是单条数据条件判定- if if user_name = 张三 then DBMS_OUTPUT.PUT_LINEuser_name是张三 ; end if; - if else if b_output = 张三 then DBMS_OUTPUT.PUT_LINEb_output是张三 ; else DBMS_OUTPUT.PUT_LINEb_output不是张三 ; end if; - if el
30、sif if b_output = 张三 then DBMS_OUTPUT.PUT_LINEb_output是张三 ; elsif b_output = 李四 then DBMS_OUTPUT.PUT_LINEb_output是李四 ; else 细心整理归纳 精选学习资料 DBMS_OUTPUT.PUT_LINEb_output不是张三也不是李四; 第 9 页,共 14 页 - - - - - - - - - - - - - - - - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -学习好资料 欢迎下载end if; -
31、 case 相当于 java 中的 switch case b_output when 张三 then DBMS_OUTPUT.PUT_LINEb_output是张三 ; when 李四 then DBMS_OUTPUT.PUT_LINEb_output是李四 ; when 王强 then DBMS_OUTPUT.PUT_LINEb_output是王强 ; else DBMS_OUTPUT.PUT_LINEb_output不是张三也不是李四也不是王强 .; end case; 循环掌握 loop . exit|exit when . end loop; while . loop . end l
32、oop; for i in xx.ooo loop - 变量由 for 循环来隐式定义 . end loop; 次序掌握 goto 强制流转 null 空语句 ,没有任何实际意义的语句动态 sql ,不需要在 declare 块中进行声明指 PL/SQL块在执行过程中动态的生成一端 sql 语句 ,并且进行执行 execute immediate sql 语句 into 结果集变量 using 参数 ; 反常处理预定义反常 用户定义反常 需要在 declare 块中声明 ,通过 raise 关键字抛出反常- 反常处理细心整理归纳 精选学习资料 - - - - - - - - - - - - -
33、 - - 第 10 页,共 14 页 - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -学习好资料 欢迎下载exception when too_many_rows then dbms_output.put_line 查询到的结果大于一行,请指定查询条件.; when my_exception then dbms_output.put_lineuser_name的值是张三 ,怎么可以是张三呢.; 游标 _子程序和程序包游标相当于一个指针,指向某块内存 ,主要储存一段sql 语句执行后的结果. 游标属性 : found 当
34、sql 执行时 ,找到要执行的记录时返回 true, 否就返回 false notfound 跟 found 相反 ,找到记录返回 false,否就返回 true rowcount sql 执行后影响的记录数isopen 判定游标是否打开 隐式游标猎取到的永久是 false 拜访游标属性 游标名 %属性隐式游标由数据库系统爱护的游标,当执行了DMLinsert,update,delete 语句之后由系统自动的开启和关闭游标隐式游标的名称是 SQL 显示游标- 静态游标创建游标 : cursor 游标名 is select.from.where. 使用显示游标之前必需 open 游标使用完游标之
35、后必需 close 游标通过 fetch 游标名into 行级变量 ;来提取游标中的记录使用 for 提取游标中的记录,必需是游标未打开状态,可以不用定义行级变量,不用显示的通过 fetch 来提取记录for 暂时行级变量in 游标名loop . end loop; 引用游标- 动态游标在执行过程中来指定游标的内容声明动态游标之前必需声明游标类型type 游标类型名is ref cursor return 记录类型 ; 强型游标- 声明类型的时候需要声明游标记录类型 , 使用 return 声明记录类型弱型游标- 没有声明记录类型的游标声明动态游标 : 游标名 游标类型名 ; 初始化游标 :
36、open 游标名 for select.from.where.; 子程序和程序包细心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 11 页,共 14 页 - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -学习好资料 欢迎下载命名的 PL/SQL块 ,编译后储存在数据库服务器上 参数分为 : in 入参 , out 出参 , in out 既是入参又是出参 过程执行一段程序操作代码 ; create or replace procedure 过程名 参数列表 is|as 变量声明 b
37、egin 可执行块 ; exception 反常处理部分 end; 执行过程 : 1. execute 过程名 参数列表 ; - 命令行执行过程 2. 过程名 参数列表 ;- PL/SQL 块 函数执行一段程序操作代码 ,并返回一个结果 ; create or replace function 函数名 参数列表 return 返回类型 is|as 变量声明 begin 可执行块 ; return 返回值 ; exception 反常处理部分 end; 执行函数 : 1. 变量 := 函数名 参数 ; -PL/SQL块 2. select 函数名 参数 ,column1. from . wher
38、e. 程序包程序包是对过程,函数 ,变量 ,游标和反常的封装; 程序包有两部分组成 包头 : : 包规范 包头 和包主体 包体 create or replace package 包名 is|as - 过程 ,函数 ,变量 ,游标和反常的声明- 声明过程 procedure 过程名 参数列表 ; - 声明函数 function 函数名 参数列表 return 返回类型 ; end 包名 ; 包体 : 细心整理归纳 精选学习资料 create or replace package body 包名 第 12 页,共 14 页 - - - - - - - - - - - - - - - - - -
39、- - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -学习好资料 欢迎下载is|as - 过程的声明 procedure 过程名 参数列表 is 过程的变量声明 ; begin 过程的可执行部分 ; exception 过程的反常处理部分 ; end 过程名 ; - 函数的声明 function 函数名 参数列表 return 返回类型 is 过程的变量声明 ; begin 过程的可执行部分 ; exception 过程的反常处理部分 ; end 过程名 ; end 包名 ; 声明包体的时候,必需要有同名的包头存在,也就是说包体的名称必需
40、跟包头的名称一样包中定义函数和过程的时候不需要使用或 procedure 来定义执行程序包中的函数 : 变量 := 包名 .函数名 参数列表 ; 执行程序包中的过程 : 包名 .过程名 参数列表 ; 触发器和内置程序包触发器是当特定大事显现时自动执行的储备过程create or replace 关键字 ,直接使用 function特定大事可以是执行更新的 DML 语句和 DDL语句 触发器不能被显式调用触发器的功能：自动生成数据 自定义复杂的安全权限 供应审计和日志记录 启用复杂的业务规律细心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 13 页,共 14 页 - - - - - - - -