OCP学习笔记.docx
《OCP学习笔记.docx》由会员分享，可在线阅读，更多相关《OCP学习笔记.docx（30页珍藏版）》请在冰豆网上搜索。
OCP学习笔记
OCP
学习笔记
Unit1
PreparingforOCPDBAExamI:
SQLandPL/SQL
1.CHAPTER1SelectingDatafromOracle
1.1Oracle’sRDBMSandORDBMSImplementations（Oracle的RDBMS和ORDBMS的实现）
1.1.1OracleGlobalArea（SGA）内存组成
⏹BufferCache:
用户数据文件读取到内存中让用户进行读取或者处理，在一段时间后写回到磁盘中。
⏹LogBuffer用来存储日志
⏹SharedPool
⏹LargePool
1.1.2OracleDiskComponents.（Oracle的磁盘组成）
⏹Datafile用来存储Oracle的字典文件和应用程序的数据库对象。
Datafile不断增长，BufferCache的内容就来自于此。
⏹RedoLogs用来存储Redo日志文件。
⏹ControlFiles控制文件，记录oracle磁盘组成的位置等资料。
可以有多个控制文件，当数据库启动时读取，当数据文件添加或删除时更新。
⏹PasswordFiles密码文件，用来存储加密的oracle用户密码。
⏹ParameterFiles参数文件，数据库的init.ora
1.1.3Oracle的服务后台进程
⏹进程名：
oracle
1.1.4WhatanORDBMDIs
⏹存储用户定义的数据类型做为对象表存储在数据库中。
⏹关联方法到对象表定义
⏹扩充关系设计到用户定义的数据类型的列中。
1.2PL/SQL的用途和优点
⏹容易学习和使用
⏹存储在数据库中
⏹和Oracle数据库结合得非常好
⏹特别擅长管理大的数据块
⏹带有大量的oracle支持的代码来帮助完成任务
⏹支持匿名和非匿名的程序
⏹支持封装和模块化
⏹支持重载
⏹允许程序员包装他们自己的Oracle代码
⏹支持高级的数据类型
1.3编写基本的执行语句
⏹$sqlplus
⏹SQL>select*fromemp;
⏹Sqlplus–s为静默模式
⏹;为语名的结束
⏹@脚本名运行定义好的脚本
⏹get脚本名将定义好的脚本文件导入到命令行
⏹nvl（列名，如果是null的值）例：
selectempno,nvl（mgr,0）fromemp
⏹ColumnAliases列别名例selectempno,nvl（mgr,0）asmgrfromemp
Ps：
as关键字可以省略，但是为了明朗，建议保留
⏹连接多个列，用concat或者”||”，（两个管道符）,concat只能带两个参数。
例：
selectename||concat（’的主管是:
’,nvl（mgr,0））asManagerfromemp
1.4SQL和SQL*Plus命令的不同点
⏹命令修改：
如果输入的命令有错误，可以调回修改。
例：
C/old_value/new_value
⏹修改后用”/”执行命令
⏹调用ed或edit可以调用系统的编辑器来编程命令。
⏹修改编辑器的命令：
define_editor=’vi’
1.5其它SQL*PLUS命令
⏹describetablename返回一个表的的说明，即表结构
⏹list当前工作缓冲区
⏹delnumber从缓冲区中删去命令
⏹appendstring加上命令
⏹clearbuffer清除缓冲区
⏹input进行行编辑sql语名
⏹run运行缓冲区中的脚本
⏹linenumber指定命令行
⏹spool文件名，将输入记录在外部文件中
⏹save文件名，将缓冲区的记录记录在文件中
1.6筛选和排序数据
⏹orderby字段名，数据排序，也可以用字段序号，或者别名
⏹desc反向排序
⏹where条件名，限制返回数据
⏹in（1,2,3,4,5）在一个序列中
⏹between1and100介于2者之间
⏹like%ann%相似
⏹soundex读音相似
1.7使用单行的函数
⏹decode函数decode（column,val1,return1,val2,return2…）根据字段值和val1来选择return1的值
1.7.1文本函数
⏹lpad（x,y[,z]）和rpad（x,y[,z]）
⏹lower（x）返回小写
⏹upper（x）返回大写
⏹initcap（x）把首字母变为大写
⏹length（x）长度
⏹substr（x,y[,z]）返回字符的子字符串
⏹instr（x,y）检查y是否在x中
1.7.2trim（）函数
⏹ltrim,rtrim,trim（[keyword]from字段名）删除首尾的空格或字符
selectcol_1,trim（both‘-‘fromcol_1）astrimmedfromexample;
1.7.3Mathfunctins数学函数
⏹Abs（x）取得绝对值
⏹Ceil（x）取不小于此数值的整数
⏹Floor（x）取不大于此数值的整数
⏹Mod（x,y）取余数
⏹Round（x,y）将x四舍五入到y位小数，如y为负值，则向左移
⏹Sign（x）取一个数值的符号，-1或1
⏹Sqrt（x）平方根
⏹Trunc（x,y）将数据的小数位减少到y的数值
⏹Vsize（x）取数据x的存储长度，单位字节
1.7.4列表函数
⏹greatest（x,y,z….）取列表的最大值
⏹least（x,y,z…）取列表的最小值
1.7.5日期函数
⏹sysdate系统日期
⏹add_months（x,y）在x加上y个月，返回日期
⏹last_day（x）返回这个月的最后一天
⏹months_between（x,y）两个日期之间的天数
⏹new_time（x,y,z）将时区y的时间x转化为时区z的时间
⏹next_day（x,y）时间x的下一个星期x
⏹设置时间格式：
altersessionsetNLS_DATE_FORMAT=’DD-MON-YYYYHH24:
MI:
SS’;
⏹时间转换的格式问题：
DD:
两个数字的日期
DAY：
日期的全部（星期几）
MON：
三个字母的月份
MONTH:
整个月份词
YY:
两个数字的年份
YYYY：
整个年份
RR：
两个数字的年份（兼容两千年问题）
RRRR：
整个年份（兼容两千年问题）
HH：
两个数字的时间
AM、PM:
上午下午
HH24:
用24小时制表示时间
MI:
两个数字的分钟
SS：
两个数字的秒钟
1.7.6转换函数
⏹to_char（x）转换到字符串
⏹to_number（x）转换到数字
⏹to_date（x[,y]）转换到日期
⏹to_multi_byte（x）转换单字节字符到多字节字符
⏹to_single_byte（x）转换到单字节字符
⏹chartorowid（x）将转换转化成为Oracle的ROWID
⏹hextoraw（x）将16进制值x转化成raw（binary）值
⏹rawtohex（x）将raw值x转化成为16进制值
⏹convert（x[,y][,z]）将x从z类型转换到y类型
⏹translate（x,y,z）
CHAPTER2AdvancedDataSelectioninOracle
2.1DisplayDatafromMultipleTables（多表查询）
⏹连接多个表
⏹表别名：
表名表别名，如empa，有助于更好的书写查询主语句
⏹selecta.ename,b.dname,b.locfromempa,deptbwherea.deptno=b.deptno
⏹多表字段如果不相同，可以不用写表名,相同的字段名前一定要接表名。
⏹（+）OutJoinoperator，外部连接符,在一对多的情况下，进行外部连接。
2.2GroupFunctionsandTheirUsers（分组函数和用途）
2.2.1现有的分组函数
⏹arg（x）列x的平均数
⏹count（x）列x的非null值的个数,使用count（rowed）可以计算行数
⏹max（x）列x的最大值
⏹min（x）列x的最小值
⏹stddev（x）计算x列标准偏差
⏹sum（x）计算列x的总数
⏹variance（x）计算variance的不一致性,即方差
2.2.2使用groupby子句
⏹groupby字段名
⏹在select字名在加上groupfunction
⏹可以用having限定返回的行，相当于where
⏹group函数可以用orderby排序
⏹例：
SQL>getgroupby
1selectjob,round（avg（sal）,2）assal
2fromemp
3groupbyjob
4havinground（avg（sal）,2）>=3000
5*orderbysaldesc
2.2.3使用子查询
⏹子查询比父查询先执行
⏹单行子查询，只返回一行的子查询，如:
SQL>list
1selectename,deptno,sal
2fromemp
3wheredeptno=
4（selectdeptnofromdept
5*wheredname='ACCOUNTING'）
⏹多行子查询条件语句用in函数
⏹distinct限定返回的结果，去掉重复的记录`
⏹多列子查询，主查询的条件语句必须放在括号中，另外主查询的条件语句的顺序也必须和子查询的select子句中的顺序一至。
例：
1selectdeptno,ename,job,sal
2fromemp
3where（deptno,sal）in
4（selectdeptno,max（sal）
5fromemp
6groupbydeptno）
7*orderbysaldesc
⏹groupby语句忽略null值，如果想在查询中返回NULL值，应使用其它的查询方法。
⏹from子句的子查询，子查询的结果被当成表使用。
2.3ProducingReadableOutputwithSQL*Plus（用SQL*Plus产生可读输出）
2.3.1输入参数
⏹在参数前加上&字符。
⏹setdefine?
来将参数标识符设为?
⏹Define变量名，预先定义好一个变量
⏹Undefined变量名，取消变量定义
⏹用accept来接收变量的值，然后在后面的语句中直接引用。
2.3.2定制SQL*Plus环境
⏹set变量名值
⏹方括号中为缩写，大括号中为允许的值
ØARRAYSIZE[ARRAY]{I5|n}从数据库中一次读取的行数
ØCOLSEP[COLSEP]{|text}两列之间打印的符号
ØFEEDBACK[FEED]{6|n|OFF|ON}显示查询选择至少n个记录时返回的记录数。
ØHEADING[HEA]{ON|OFF}是否打印列标题
ØLINESIZE[LIN]{80|n}设置每行显示的最大字符数
ØPAGESIZE[PAGES]{24|n}设置每页行数
ØPAUSE[PAU]{OFF|ON|text}控制运行报表时的终端滚动。
ØTERM[TERM]{OFF|ON}控制从命令文件执行的命令产生的终端输出提示。
ØSQLPROMPT[SQLP]{SQL>|TEXT}设置SQL*Plus的命令提示
ØECHO[ECHO]{OFF|ON}控制start命令是否在执行命令文件中的每个命令。
⏹COLUMN{col}FORMAT{fmt}HEADING{string}定义列col的显示格式为fmt另外列表头提示为string
⏹UNDERLINE[UND]{-|c|ON|OFF}把表头下划线变为字符c,或者关闭
⏹Breakon某列将某列列值相同的数值只显示第一个
⏹Compute显示列名of表中列名on依据，生成简单的报表
CHAPTER3CreatingOracleDatabaseObjects
3.1建立表
⏹使用describe表名来生成表结构注释，也可以缩写为desc
⏹建立表用createtable表名（字段名数据结构）
⏹建立临时表createglobaltemporarytable表名
⏹以另一个表为基准建立新表createtable表名as语句
⏹表命名规则
Ø名称简明扼要，硬规则：
以字母起头，1-30个字符，但数据库最多8个字符，数据库链接最多128个字符，名称大小写无关软规则：
要易懂
Ø将子表名与父表名相联系，硬规则：
一个用户不能拥有或引用两个同名对象，软规则：
用外键联系的表（即父/子关系）应共享部分相同表名。
Ø两个表的外键列应同名硬规则不要用dual作表名，软规则：
父子关系中多表共享的列应同名
Ø相关对象应与表相联系软规则：
应当给与表相关对象有意义的名字以便与表相联系，如PK_EMP_01（EMP的主键）
Ø避免使用引号、关键字和非字母数字字符硬规则：
不要使用以SYS开头的表名;表名和列名只能使用下列三个特殊字符：
#号、_号和$号;数据库名、全局数据库名和数据库链接名不要使用欧洲字符集中的特殊字符;对象名不能使用oracle保留字，如select等;根据访问数据库对象的产品，名称还受到更多保留字的限制。
3.2数据类型和列定义
3.2.1Datatypesandtheirdecriptions:
⏹Varchar24000字节的文本字符串，可变长度
⏹Nvarchar2（n）可变长度的单字节或多字节变长文本字符串，最长4000字节
⏹Char（n）包含长度为n字节的定长文本字符串，最多2000个
⏹Nchar（n）长度为n字节的单字节或多字节定长文本字符串，在oracle中n可以取最大2000字节
⏹Number（n，m）包含最多n位的数字数据，在oracle中n最大38位。
小数点后有m位。
默认为38位
⏹Date包含日期信息，七位
⏹Raw二进制数据,oracle中最多2000字节，可变长
⏹Long包含文本数据，最多2GB
⏹Longraw包含二进制数据，最多2GB
⏹Rowed包含表中的行的地址
⏹Blob存储最多4gb的大型非结构化二进制对象数据
⏹Clob存储最多4gb的大型数据库字符集数据
⏹Nclob存储最多4GB的大型单字节或多字节基于unicode字符的字符集数据
⏹Bfile在oracle数据库之外存储大型非结构化操作系统文件指针
3.2.2文本类型数据
⏹CHAR和Varchar2，varchar2保存4000个，并且变长，而char为定长
3.2.3Number数据类型
⏹插入的值超过允许数位时，只要超出的位数在小数点后面，oracle总是四舍五入
⏹如果超出的位数在小数点前面，则oracle返回错误.
3.2.4Oracle中的其它数据类型
⏹Date数据类型，从公元前4713年12月31日算起的天数
⏹LONG和RAW和LONGRAW，数据不到4KB时，存储在表内，否则存储指针，数据存放在表外。
可以有多个blob和clob和nclob列,LONG存储在表内，一个表只能有一个LONG类型的字段。
⏹ROWID,为伪列，标识oracle如何定位行。
3.2.5列默认值
⏹使用default子句定义表的使用默认值填充列
如:
SQL>list
1createtabledisplay
2（colvarchar2（10）,
3*col2numberdefault0）
3.3改变表定义
3.3.1在表中增加新列
⏹两个列不能同名
⏹只能有一个long或LONGRAW数据类型的列
⏹列数不能大于1000
⏹altertable表名add（列名数据类型）;
3.3.2修改列数据类型
⏹altertable表名modify（列名数据类型）：
⏹通常可接受的操作：
Ø增加varchar2和CHAR列的长度
Ø增加number列的长度
⏹减少一个列长度的步骤：
Ø将该列的数据转存到另外一个表中
Ø将该列的数据全部设为空
Ø改变该列的长度
Ø将数据转存回来
Ø删去建立的临时表
Ø例:
SQL>createtabledisplay2（col2）asselectcolfromdisplay;
SQL>updatedisplaysetcol=null;
SQL>altertabledisplaymodify（colvarchar2（5））;
SQL>updatedisplaysetcol=（selectcolfromdisplay2）;
SQL>droptabledisplay2
3.3.3从表中删除列
⏹方法一先设无用，再删除无用列
Ø将该列设为无用的列:
altertable表名setunusedcolumn列名;
Ø删除无用列：
altertable表名dopunsedcolumns;
⏹方法二立即删除
Øaltertable表名dropcolumn列名;
3.4表的删除、更名与截断表
3.4.1删除表
⏹droptable表名;
⏹不能撤销命令
⏹必须有权限
3.4.2截断表
⏹truncate表名
⏹截断为删除表的所有数据
⏹不改变表定义
⏹不能恢复
3.4.3更名表
⏹rename表名to表名2
⏹altertable表名renameto表名2
3.4.4注释对象
⏹commentontable表名is语名
⏹commentoncolumn表名.列名is语句
3.5包括约束
3.5.1描述约束
⏹作用
Ø建立多个表间真实具体的关系，是典型数据库应用程序所共有的
Ø约束防止’不良’数据进入数据库中
Ø在表间存在相关性时防止删除数据
⏹约束类型
Ø主键约束：
规定列或列组中的标识oracle表中行的维一性，notnull
Ø外建约束：
表示基于共享列的两个表之间的父子关系
Ø唯一约束：
对个列的值要求唯一性
Ø检查约束：
保证这个列的值符合指定的静态条件。
Ø非null约束：
不允许列中放入null值
3.5.2生成与维护约束
⏹生成主键约束
Ø表约束constraintpk_emplolyee_01primarykey（empid）
Ø列约束empidvarchar2（5）constraintpk_employee_01primarykey
Ø简化列约束声明：
department_numnumber（5）primarykey
⏹复合主键
Ø如果要生成两个或多个列构成的复合主键，则要把列名放在括号中，在关键字primarykey后面出现
Ø复合主键的惟一性通过组合主键中所有列的值来确定
⏹定义外键约束
Ødepartment_numnumber（5）referencesdepartment（department_num）
Øondeletesetnull
⏹定义唯一键约束
Øgovt_idnumber（18）unique
Ø只是非null列的惟一
⏹定义其它约束
Ø字段名数据类型notnull
Ø字段名数据类型check（条件）
⏹约束生成的索引
Ø声明惟一约束和主键约束时，也生成支持惟一性的惟一索引，约束定义的所有列的所有值都进行索引
Ø索引名与约束同名
⏹在现有表中增加完整性约束
Ø格式为：
altertabletable_nameaddconstraintconstraint_nametype（define）
Ø主键约束：
pk_tablename_01primarykey（empid）
Ø外键约束：
fk_tablename_01foreignkey（department_num）
referencesdepartment（department_num）;
Ø检查约束：
ck_tablename_01check（salary<=250000）
Ø惟一约束：
uk_tablename_01unique（govt_id）
Ø非空约束：
直接modify表altertabletable_nameemployeemodify
（lastnamenotnull）;
⏹关闭约束
Ø关闭主键约束:
altertableemployeedisableprimarykey;
Øaltertableemployeedisableconstraintuk_employee_01（直接关闭约束名）
Ø当有外键依赖于主键时，在末尾加上cascade
⏹打开关闭的约束
Øaltertableemployeeenableprimarykey;
⏹打开列中现有数据违反关闭的约束
Ø由于数据违反约束，故不能打工关闭的约束
Ø使用exceptions表来找出违反约束的行
Ø打开的语句exceptionsintoexceptions表
Ø修改违反约束行的值
Ø打开约束
⏹删除约束
Øaltertabletable_namedrop约束类型（语句）cascade
Ø非null约束用表修改语句来删除altertabletable_namemodify（字段名null）
⏹删除具有外键约束引用的父表
Ø由于有外键引用，删除表或主键时会出错
Ø使用cascadeconstraints关键字可以删除子表的主键约束
⏹oracle中的约束延迟性
Ødefererrableinitiallydeferred将约束定为可以延迟,用非惟一索引存储主键值。
允许在主键存储数据，直到事务结束之后再报告约束违犯错误。
Ødefererrableinitiallyimmediate用惟一索引存储主键值，在输入数据时执行主键约束。
Ø使用enablenovalidateprimarykey可以起用约束，但是不验证约束列中的所有数据.
CHAPTER4操作Oracle数据
4.1Orac
升级会员 