ORACLE学习总结.docx

ORACLE学习总结默认分类 2008-06-08 02:42:25 阅读62 评论0 字号：大中小订阅 一.文件系统和作用 执行文件:Oracle系统的核心,数据文件只能被Oracle处理程序所识别,离开了可执行文件系统就无意义.存放可执行文件的空间一定要安全可靠. 参数文件:启动实例时候配置Oracle数据库的一些基本信息,确定运行环境.参数文件应数据库不同而不同,命名方式为init.ora. 控制文件:在Oracle服务器启动期间来标识物理文件和数据库结构的二进制文件.控制文件提供了建立新实例时候所需要的文件目录,可以在Oracle操作其间更新控制文件,可以建立多个控制文件副本,以便进行数据库恢复. 数据文件:数据文件用来存放用户的数据,它的稳定性和完整性都十分重要.每个数据库都至少一个相关数据文件. 联机重做日志文件:是数据库的事物日志,它用于恢复数据库. 输出文件:它影响数据库的可恢复性,它由Oracle自带的输出程序生成,它在指定时间点上对数据库的数据和结构进行备份.它是数据库逻辑副本,而不是物理副本. 临时文件:用以在内存不足以保存中间结果集的时候保存结果集.它不生成重做,即不生成联机重做文件. 二:后台进程和作用 后台进程的主要作用是为了提高数据库的性能和可靠性,没个进程都有自己的职责. pmon:监控服务器进程,确保能够销毁发生损坏或出现故障的进程,并释放资源. smon:负责重新启动系统,清楚临时段,执行盘区结合任务.确保系统达到一致性. dbwr:负责将缓存中的数据块写入到磁盘中. lgwr:日志写入器,负责向联机重做日志文件中写入已提交的事物处理. ckpt:检查点进程,负责使用最新的检查点信息更新所有控制文件和数据文件的标题.数据库写入器会周期性的将其缓寸写入到磁盘中,存储检查点. cjq0:负责管理和协调作业. arc0:归档器:主要负责备份联机重做日志中的事物,以防止他们被覆盖. 三.系统表,视图和作用 查看有关用户的信息:dba_users 查看有关角色的信息:dba_roles,dba_role_privs,role_sys_privs 查看有关系统权限的信息:dba_sys_privs 查看当前数据库表空间状况:dba_tablespaces 查看用户的系统权限:user_sys_privs 查看某个用户对另外一个用户授予的权限:user_tab_privs_made 查看某个用户对另外一个用户授予的列级权限:user_col_privs_made 查看某个用户接受的权限:user_tab_privs_recd 查看某个用户接受的列级权限:user_col_privs_recd 查看有关用户的角色信息:user_role_privs 查看有关授予某个角色的系统权限信息:role_sys_privs 查看有关授予某个角色的对象权限信息:role_tab_privs 查看当前用户所拥有的表信息:user_tables 查看当前用户有权限访问的表信息:all_tables 查看当前用户所拥有的所有表的列信息:user_tab_columns 查看当前用户可以访问的表中的列信息:all_tab_columns 查看当前用户所拥有的所有约束信息:user_constraint 查看当前用户所拥有的所有约束和列的关系:user_cons_constraint 查看表中注释内容:user_tab_comments 查看表中列注释内容：user_col_comments 提供练习的表:dual 查看相关时区的名称和简称:v$timezone_names 四.表空间和作用 system表空间:记录运行信息. sysaux表空间:存储支持Oracle系统活动的许多工具和选项. temp表空间:存储中间结果. undo表空间:存储事物提交或撤消的改变数据. 五.默认用户角色 DBA:执行数据库中所有的操作 connect:可以执行数据库连接等操作 resource:可以执行使用数据库资源等操作 select_catalog_role:可以执行select操作 OLAP_DBA:可以执行与表,视图有关的操作 scheduler_admin:可以执行作业管理等操作 exp_full_database:可以执行引出数据库等操作 imp_full_database:可以执行引入数据库等操作 六.权限 系统权限:可以执行数据库中某些操作的权限称为系统权限.如:create table create user系统权限:拥有此权限才可以创建用户 create session系统权限:拥有此权限的用户可以连接数据库 create any table系统权限:在任意模式中创建表 create synonym系统权限:可以创建同意词 execute any procedure系统权限:执行任何过程 create role系统权限:可以创建角色 drop any table系统权限:可以删除任何表 创建用户:create user 用户名identified by 口令default tablespance 默认表空间 temporary tablespace 临时表空间 修改用户口令:alter user 用户名 identified by 新密码 锁定用户和解锁用户:alter user 用户名 account lock|unlick 修改用户默认表空间:alter user 用户名 default tablespace 新表空间名 temporary tablespace 新临时表空间名 删除用户:drop user 用户名 cascade 如果当前用户拥有对象则必须加上 cascade 为用户授以系统权限:grant 系统权限1,系统权限2,系统权限3. to 用户名 with admin option 如果允许用户把权限转受于其它用户则要加上with admin option 收回授予的系统权限:revoke 系统权限1,系统权限2,系统权限3. from 用户名 在收回权限时,被当前用户所转授的权限不会被收回 对象权限:针对数据库中的表,视图等对象的权限为对象权限.如:select insert update delete execute 为用户授予对象权限:grant 对象权限(列名称) on 对象名 to 用户名 with grant option 如果允许用户把权限转受于其它用户则要加上with grant option 收回授予的对象权限:revoke 对象权限 on 对象名 from 用户名 收回权限只能从表上收回,不能从列上收回,在权限被收回时,被该用户转授的权限也会被收回 创建同义词:create public synonym 同义词名 for 对象名 public代表所有用户都可以使用公共同义词 角色:角色是一组可以授予用户和其它角色的权限,一个用户可以拥有多种角色 创建角色:create role 角色名 identified by 角色口令 授予角色权限:类似授予用户权限 收回角色:drop role 角色名 七.表 建表:create global temporary table 表名 (列名 列类型其它信息) on commit (delete|preserve) row tablespace 表空间名 on commit指定表中数据持续时间,delete在事物执行完毕后删除,preserve表示在会话结束后删除 查看表的系统信息:desc|describe 对象名 删除表：drop table 表名 添加列:alter table 表名 add 列名 列类型 更新列:alter table 表名 modify 列名 列类型 删除列:alter table 表名 drop column 列名 禁止约束:alter table 表名 discount contraint 约束名 禁止约束:alter table 表名 enable novalidate contraint 约束名 如果有novalidate则表示在激活时不验证数据内容是否满足约束 删除约束:alter table 表名 drop constraint 约束名 修改表名称:rename 旧表名 to 新表名 注释表:注释表就是为表和表中列添加文字说明以让其它人可以看懂表结构 注释表语法:comment to table 表名 is 注释信息 comment to column 表名.列名 is 注释信息 注释内容用单引号 八.操作数据 插入数据:insert into 表名(列名列表) values(列值列表) 插入列时用default关键字代表值则是插入列的默认值,如果用null则代表插入一个空值,插入列的时候必须满足列的约束 复制数据:insert into 表名(列列表) 和列对应的查询语句 修改数据:update 表名 set 列名=列值,列名1=列值1. where 限制条件列表 删除数据:delete from 表名 where 限制条件列表 删除表中所有数据的两种方法:delete from 表名 ; truncate table 表名;truncate table效率高些,因为它是重新设置表的存储空间 合并数据操作:把两个表合并成一个表,并且以某一字段作为判断是否相同的机准.如表A和表B 用id来判断是否相同,那么把表A合并到B中,则以A中的数据为基准,如果a.id=b.id成立,那么合并后的其它字段将采用a表的 合并数据:merge into 表名1 using 表名2 on (比较表达式) when matched then 更新语句 when no matched then 插入语句 闪回查询:有两种类型的闪回查询1.基于时间的闪回查询,把系统回复到之前的某一时间;2.是基于版本号的闪回查询,把系统回复到之前一个版本号.使用闪回查询,则必须拥有dbms_flashback包的execute权限. 授予用户闪回查询的对象权限:grant execute on dbms_flashback to 用户名; 执行基于时间的闪回查询:execute dbms_flashback.enable_at_time(时间); 时间一般算法:sysdate-分钟数/1440 执行基于系统版本号的闪回查询:execute dbms_flashback.enable_at_system_change_number(系统改变号); 改变号可以通过;dbms_flashback.get_system_change_number();获得 关闭闪回查询:执行完闪回查询功能后应该关闭,关闭的方法为:dbms_flashback.disable(); 事务:事务是一个单元操作,这些操作要么全部成功,要么全部失败. 九.检索 算数运算:可以在查询中使用算数运算(+,-,*,/) 别名:在Select语句中为了便于查看为列临时定义的名称.定义方式: 列名 as 别名 合并输出结果:使用|符号连接.例:结果1|结果2|结果3. 空值处理:nvl(空值列,显示值) 如果列的值是空则显示显示值 distinct关键字:限定在检索结果中不出现重复值;select distinct 列列表 from 表名 where:where后面跟运算表达式;例:select 列列名 from 表名 where 限制条件 运算符: =等于 或!=不等于 大于 =大于或等于 any(值列表):比值列表中的最小值大 all(值列表):比值列表中的最大值大 all(值列表):比值列表中的最小值小 like:按照指定的模式匹配 like _A% _代表任意一个字符,%代表任意多个字符 如果在匹配的字符串中包含_或%那么则使用escape来说明是实际数据而不是匹配表达式 in:匹配值清单 如: in(select a from TA) between:匹配范围内值 如:between(1,20) is null:与空值匹配 is nan:与非数字值匹配 is infinite:与无穷的binary_float和binary_double匹配 not:用于在各种运算符中取反 or:或,当两个条件中有一个为真时,就匹配 and:与,必须两个条件全部是真才匹配 排序数据:order by 排序表达式 asc|desc,排序表达式1 asc|desc asc和desc代表排列顺序,asc是正序,desc是倒序,默认asc 检索多个表的数据1:select 列列表 from 表名1 as 别名1 , 表名2 as 别名2 where 限制条件 笛卡尔集:两个集合的乘集,如:集合A中有2个记录,集合B中有20个,则他们的笛卡尔集就有40条记录. 内连接:内连查询出来的所有数据都是满足查询条件的.方式:select 列列表 表名1 别名1 inner join 表名2 别名2 on 内连接条件 外连接:外连接可以把不符合条件的表的数据显示在结果中.分为左外连接,右外连接,全外连接.左外连接包含左表中不满足条件的数据,右外连接包含右表中不满足条件的数据.全外连接则包含左右两表中不满足条件的数据.join关键字左边的称为左表,右边的是右表 外连接方式:select 列列表 from 表名1 别名1 left|right|full outer join 表名2 别名2 on 连接条件 自连接:是一个表连接自身的一个连接.可以采用别名的方式查询,自连常用内连接方式 自连接方式:select 列列表 from 表名 别名1 inner join 表名 别名2 on 连接条件 交叉连接:交叉连接没有连接条件,结果就是笛卡尔集. 交叉连接方式:select 列列表 from 表名1 cross join 表名2 十.程序包 dbms_flushback:用于执行闪回查询 十一.函数 字符串函数: ascii(x):返回字符x的ascii码; char(x):返回asscii值的字符; concat(x,y):把y附加到x上并返回; initcap(x):将字符x中的每一个英文单词设置为首字母大写的形式; instr(x,find_string,start,occurrence:在字符串x中搜索find_string,返回find_string出现的位置,可以选择开始搜索的位置start,还可以限制它是在第几次出现的occurrence length(x):返回字符串X的长度 lower(x):把x转换为小写,并返回 lpad(x,width,pad_string):使用空格补齐x的左边使其长度为width,如果提供了pad_string,则使用pad_string补充 ltrim(x,trim_string):删除x左边的字符,默认是删除空格,如果指定了trim_string则删除trim_string nanvl(x,value):如果x不是数字则返回value否则返回x nvl(x,value):如果X是空,则返回value否则返回x nvl2(x,value1,value2):如果X不是空,返回value1否则返回value2 replace(x,search_string,replace_string):在X字符串中搜索search_string并替换为replace_string rpad(x,width,pad_string):和lpad一样但是是从右边 rtrim(x,trim_string):和ltrim一样但是是从右边 soundex(x):返回包含字符串的音标 substr(x,start,length):返回x的子字符串,开始位置是start长度为length,默认为全部返回 trim(char fromx):删除x左右两边的字符串,如果有char from 则删除左右两边的char upper(x):把字符串x中的字母转变为大写字母 数学函数: abs(x):返回X绝对值 acos(x):返回X的反余弦值 asin(x):返回X的反正弦值 atan(x):返回X的反正切值 atans(x,y):返回X和Y的反正切值 bitand(x,y):返回X和Y的二进制与的结果 cos(x):返回X的余弦值其中X以弧度表示 cosh(x):返回X的双曲弦值 ccil(x):返回大于或者等于X的最小整数 exp(x):返回e的X幂,其中e等于2.71828183 floor(x):返回小于或者等于x的最大整数 log(x,y):返回底为x的外的对数值 ln(x):返回x的自然对数值 mod(x,y):返回X除以Y的余数 power(x,y):返回X的Y次方 round(x,y):圆整X值.如果不指定Y,则圆整到整数;如果指定Y,则圆整到Y指定的小数点位数;如果Y是负数,则圆整到小数点左端的位数 sign(x):符号函数,如果X是负数,则返回-1;如果X是正数,则返回1;如果X是0则返回0 sin(x):返回X的正弦值 sinh(x):返回X的双曲正弦值 sqrt(x):返回X的平方根 tan(x):返回X的正切值 tanh(x):返回X的双曲正切值 trunk(x,y):截断X值,如果不值定Y,则截断到整数;如果指定Y则截断到Y值定的小数点位数;如果Y是负数,则截断到小数点左端的位数 转换函数: asciistr(x):把X转换为ascii字符串,其中x可以是任意字符集中的字符串 bin_to_num(x):把X转变成一个二进制数,返回数字 cast(x as type_name):把X从一种数据类型转换成type_name数据类型 chartorowid(x):把X转变为rowid值 compose(x):把X转换成unicode字符串,unicode使用2字节字符集,并且可以表示出65000多个字符,可以用于表示非英语字符. convert(x,source_char_set,dest_char_set):把X从source_char_set字符集转换为dest_char_set字符集 decode(x,search,result,default):比较X和search如果相等就返回result不等就返回default decompose(x):把X转换为UNICODE字符串. hextoraw(x):把包含了十六进制的X字符转变为二进制数字,返回结果是raw数字 numtodsinterval(x):把数字x转换为interval_day_to_second类型 numtoyminterval(x):把数字X转换为interval_year_to_money类型 rowtohex(x):把二进制数据X转变成包含了等价十六进制代码的varchar2字符 rowidtochar(x):把rowid类型的X转换为varchar2类型 rowidtonchar(x):把rowid类型饿x转变成nvarchar2类型 to_binary_double(x):把X转变成binary_double to_binary_float(x):把x转变成binary_float to_char(x,format):把x转换为varchar2字符串,可以提供一个格式化表达式format to_clob(x):把X转换为一个CLOB to_date(x,format):把X转换为日期 to_dsinterval(x):把字符穿X转变成interval_day_to_second类型 to_multi_byte(x):把X中的单字节字符转变为多字节字符 to_nchar(x):把x转变成nvarchar2类型 to_nclob(x):把X转变成nclob类型 to_number(x,format):把X转变成number to_single_byte(x):把X中的多字节字符转变单字节字符 to_timestamp(x):把字符串X转变为一个时间戳 to_timestamp_tz(x):把字符串X转变成timestamp with time zone to_yminterval(x):把字符串X转变成interval_year_to_moth translate(x,from_string,to_string):把出现在X中的所有from_string替换为to_string unistr(x):把X中的字符转变为nchar字符集 格式化参数: 9:返回数,如果数字是负数则有负号 0:0999表示数字前面有0,9990表示数字后面有0 .:表示小数点的位置 ,:指定位置显示逗号 $:S99表示数字前面是货币号 B:如果整数部分为0,则使用空格表示 C:在指定的位置使用ISO标准货币符号 D:在指定的位置返回小数点位置 EEEE:用科学计算法记载 FM:删除数字前后空格 G:在指定的位置显示分组符号 L:在指定的位置显示本地货币符号 PR:负数的尾部有三角扩号,正数的头部和尾部有空格 MI:负数的尾部有负号,正数的尾部有空格 RN/m:返回罗马数字,RN表示大写,m小写,数字必须是1-3999之间 S:S999表示负数前面有负号,正数前面有正号,999S表示符号在后面 TM:使用最小的字符数返回数字 U:在指定位置返回双货币号 V:返回一个数字乘以10的x的次方,x为指定数字 X:返回十六进制数字 正则表达式: *:匹配0个或多个任意字符 ?:匹配1个任意字符 :表示字符串的开始 $:表示字符串结束 x|y:匹配x或者y :匹配方扩号中任意一个字符 m:允许匹配m次 m,n:至少匹配M次,最多匹配N次 n:表示前一个表达式重复N次 :字符类:脂定一个字符类,匹配该类中的任意一个字符 regexp_like(search_string,pattern,match_option):类似like运算符,可以使用正则表达式执行模式匹配操作 option有四个值C匹配时,大小写敏感,I匹配时大小写不敏感,N允许使用.匹配任意新增字符, regexp_instr(search_string,pattern,position,occurrence,return_opotion,match_option):instr运算符扩展,它可以用来搜索匹配正则表达式模式的输入字符 regexp_replace(seach_string,pattern,replacestr,positionoccurrence,match_option):replace函数扩展,它允许用户搜索符合正则表达式的输入字符传,并且用替换字符串替换 regexp_substr(search_string,pattern,positioin,occurrence,match_option):substr函数扩展 合计函数: avg(x):平均值 count(x):统计数量 max(x):最大值 median(x):中间值 min(x):最小值 stddev(x):标准差 sum(x):汇总值 variance(x):方差 分组技术:select 列列表 from 表名 where 限制条件 group by 分组列 having 分组后的数据应该满足的条件 时间函数: add_months(x,y):在x上增加y个月,若Y为负数则减少 last_day(x),返回包含在X中的月份的最后一天 months_between(x,y):返回X和Y之间的月数,如果Y在X前返回负数 next_day(x,day);返回仅接着X的下一天,DAY是一个字串 sysdate():返回当前系统的日期 trunk(x,unit):截断x round(x,unit):圆整X current_date():返回本地区的当前日期 dbtimezone():返回当前数据库的时区 new_time(x,time_zone1,time_zone2):把x从时区1转换为时区2 sessiontimezone();返回当前数据库对话的时区 tz_offset(time_zone):按照最小时哈分钟返回time_zone的偏置时间 时间戳函数: current_timestamp():返回包含了当前会话时间,时区信息的timestamp with time zone类型 extact(year|month|day|hour|minute|second|timezone_hour|timezone_minute|timezone_region|timezone_abbr from x):从表达式X中提取参数信息 from_tz(x,time_zone):把指定的时间戳和日期转换为timestamp with time zone类型 localtimestamp():返回会话的timestamp with time zone类型数据 systimestamp():返回当前数据库的timestamp with time zone类型数据 sys_extact_utc(x):把类型为tmestamp with time zone 的表达式转换为timestamp类型 to_timestamp(x,format):把字串表达式X转换成timestamp类型可以使用format指定格式 to_timestamp_tz(x,format):把字串表达式X转换成timestamp with time zone类型可以使用format指定格式 十二.日期和时间 设置默认时间格式:默认情况下是:dd-mon-yy;alter session nls_date_format 日期格式;则可以改变 转换日期数据: cc:两位数字的世纪 scc:有负号的两位数字的世纪,表示世纪前 q:表示一位数季度 yyyy:表示4位数年 iyyy:表示4位数