Oracle学习笔记档.docx
《Oracle学习笔记档.docx》由会员分享，可在线阅读，更多相关《Oracle学习笔记档.docx（21页珍藏版）》请在冰豆网上搜索。
Oracle学习笔记档
第二章SQL函数
转换函数
数据类型转换:
1隐式转换
2显示转换
显示转换:
to_date
to_number
to_charselectto_char（hiredate,'YY-MM-DD'）fromemp;
NVL函数将NULL值转换成一个实际的值
当数值与空值进行运算的时候结果同样为空这就需要在运算时避免使用空值
NVL（comm,0）如果字段comm为空时用0替代
NVL（hiredate,'2007-12-18'）
DECODE函数功能类似CASE或者IF-THEN-ELSE语句但更容易
DECODE（'表达式或值','结果1','返回值1',
'结果2','返回值2'...,
'结果n','返回值n','没有对应结果时的返回值'）;
函数嵌套
单行函数可以多重嵌套
Oracle调试时常用到的命令
运行SQL*PLUS常常碰到错误需要调试此时需要使用许多Oracle命令
1save命令
SQL>save'D:
\empsql.txt'把上次运行的sql语句保存到d盘empsql.txt文件中
2get命令读取文件
SQL>get'D:
\empsql.txt';读取硬盘上D:
\empsql.txt文件中的sql语句
加载sql语句但不执行使用run命令可以执行
3@命令与get命令相似不同的是@命令加载sql语句并执行
SQL>@'D:
\empsql.txt';加载empsql.txt文件并运行其中sql语句
4connect连接命令如果命令没有与其他命令重复时可以简写为conn
connectscott/tiger连接到本机scott用户
conn简写connect命令sys/sys连接到本机sys用户
connoracle/oracle@oracle如果加上连接字符串（@oracle）表明连接到远程服务器如果不加则连接本地
5在同一个服务器上有不同的用户名用户之间权限是不同的如果两个用户（MAIN,SCOTT）互相访问的话需要授权
授权语句:
grantselectonemptoMAIN;把emp表查询权限赋予MAIN
grantselect,update,insteronemptoMAIN;把emp表查询,修改,插入权限赋予MAIN
访问其他用户语句:
select*fromSCOTT.emp;MAIN访问SCOTT用户下的emp表;
另外,grantresourcetomain;语句可以赋予用户main当前的资源创建（创建视图的时候可用）
6exit退出SQL*PLUS
7clearscreen清屏命令清除SQL*PLUS页面上所有显示文字
问ed是什么命令?
第三章数据库锁及表分区
数据库锁:
锁的原因:
多个客户端同时访问一个表时同时执行事务处理有的查询有的修改等同时操作产生冲突
共享资源:
不仅指数据库行,而且也指其他的资源（如表）
当多个用户同时访问数据库中的同一资源时,就会产生对同一资源的访问的冲突.为了防止资源被破坏,
数据库采取了解决访问冲突的机制,称之为锁.
更改提示符（SQL>）显示:
"setsqlpromptMAIN>"把提示符改为"MAIN"
锁:
行级锁锁住部分行行被排他锁定
在某行的锁被释放之前,其他用户不能修改此行
使用commit或rollback释放锁
获取行级锁:
1使用insert,update自动锁
2select...forupdate（ofename）语句自动获取行级锁
锁住查询得到的行数据在表的一行或多行上放置排他锁
用于防治其他用户更新该行但其他用户可以执行除更新之外的其他操作
"ofename"表示可以锁定规定的字段
3select...forupdatewait15
锁住其他用户更新操作15秒15秒以后其他用户会返回错误信息.
表级锁锁住整个表保护表的数据
在多个用户同时访问数据时确保数据的完整性
使用commit或rollback释放锁
三种模式可设置:
语法:
Locktablein;
1共享锁模式
语法:
Locktableinsharemode;
例如:
SQL>LOCKTABLEscott.employeesINshareMODE;
允许多个用户同时对一个表放置共享锁
当A用户对表锁定其他用户只可以查询数据不能更新插入删除数据
当有多个用户对表同时上锁那么所有的用户都只能执行查询操作
2共享更新锁模式
语法:
Locktableinshareupdatemode;
锁定要被更新的行
允许其他用户同时查询插入更新未被锁定的行
在SELECT语句中使用"FORUPDATE"子句,可以强制使用共享更新锁
允许多个用户同时锁定表的不同行
3排他锁模式
语法:
Locktableinexclusivemode;
限制性最强仅允许其他用户查询数据不允许插入删除更新操作
在同一时间仅允许一位用户在表上放置排他锁
注:
在使用排他锁时,可加参数"nowait"
例:
Locktabletestinexclusivemodenowait;
如果表已被其他用户锁住时不等待直接返回报错信息
Oracle表操作:
Oracle数据库存储数据的方式:
规则表分区表索引组织表簇
Oracle数据库中的数据类型:
VARCHAR2（size）:
可变字符数据
CHAR（size）:
固定长度字符数据
NUMBER（p,s）:
数值型例如NUMBER（5,3）的最大值为99.999
DATE日期时间型
LONG可变长度的字符型最大2GOracle7.0以后支持
CLOB单字节的大型字符对象最大4G
ROWandLONGRAW二进制型
BLOB二进制大型对象,最大可达4G
BFILE二进制数据,在数据库外部存储文件最大4G
表的创建和维护
创建表:
CREATETABLE表名（字段1类型1,字段2类型2,...,字段n类型n）
`createtableempasselect*fromscott.emp;//在另一个用户下创建用户scott下emp表的复制表
createtablestudent（stunoint,stunamevarchar2（10）notnull,stubirthdate
defaultto_date（'1980-1-1','YYYY-MM-DD'））;
添加表字段:
ALTERTABLE表名ADD（字段1类型1,......,字段n类型n）;
altertablestudentadd（stuphonevarchar（18））;
修改表字段:
ALTERTABLE表名MODIFY（字段名,类型）;
altertablestudentmodify（stunamevarchar（12））;
删除表字段:
ALTERTABLE表名DROPCOLUMN字段1,字段2,...;
altertablestudentdropcolumnstuphone;
注意:
当有其他用户操作或使用表时Oracle不允许本用户删除表字段此时可把字段设置为无效
ALTERTABLE表名SETUNUSEDCOLUMN字段名;
altertablestudentsetunusedcolumnstuphone;
当某字段设置为"无效"即不可恢复且在查看（desc）表结构时该字段不会显示
要删除表中已经设置为"无效"的字段可使用:
ALTERTABLE表名DROPUNUSEDCOLUMNS;
altertablestudentdropunusedcolumns;
表增加约束:
ALTERTABLE表名ADDCONSTRAINTPK_字段名PRIMARYKEY（字段名）;
altertablestudentaddconstraintpk_stunoprimarykey（stuno）;
删除约束:
ALTERTABLE表名DROPCONSTRAINTPK_字段名;
altertablestudentdropconstraintpk_stuno;
查看表约束:
descuser_constraints;（数据字典中提供）
表分区:
Oracle允许用户对表进一步的规划,即对表进一步拆分,将表分成若干个逻辑部分,每个部分称其为表分区
优点:
1增强可用性,单个分区出现故障,不影响其他分区;
2数据在各个分区合理的分布使得I/O负载平衡,不同的分区可以映射到不同的硬盘;
3改善性能提高效率;
第一种方案:
范围分区发
1根据表中某一列的值得范围对表进行分区
2分区时,首先依据列中的值可能的范围进行划分
例如:
对某省参加四六级考试的成绩进行整理（按得分数60以下60-8080-100）并保存至数据库
注:
范围分区的字段也可以是时间类型
范围分区语法:
CreatetableStudent（//创建表Student
Studentidintegernotnull,//非空字段Studentid
Studentnamevarchar2（20）,//字段Studentname
Scoreinteger//字段Score
）
Partitionbyrange（Score）（//以字段Score作为分区标准
Partitionp1valueslessthan（60）,//p1区为<60分
Partitionp2valueslessthan（80）,//p2区为>60and<80分
Partitionp3valueslessthan（maxvalue）//范围分区要考虑空值"maxvalue"包括最大值及空值等
）;
第二种方案:
散列分区法
1散列分区法提供了一种通过指定分区编号来均匀的分布数据的方法比较复杂
2通过Hash函数将数据映射到相应的分区上具体映像过程怎么操作不需要知道
3使得数据均匀的分布到各分区上,个分区大小趋向一致
散列分区语法:
Createtabledepartment（//创建表department
Deptnoint,
Deptnamevarchar2（20）;
）
Partitionbyhash（deptno）（//"deptno"是散列分区的字段hash函数是由oracle提供的
Partitionp1,//具体p1p2分区怎么划分由hash函数决定
Partitionp2
）;
第三种方法:
复合分区法
复合分区先对数据进行范围分区,然后在每个子分区又进行散列分区的一种表分区方法
复合分区语法:
Creattablesalgrade
（gradenumber,losalnumber,hisalnumber）
Partitionbyrange（grade）//首先进行范围分区以工资级别"grade"为分区标准
Subpartitionbyhash（losal,hisal）//在范围分区又进行散列hash分区losal-最低工资标准hisal-最好标准
（
Partitionp1valueslessthan（10）,//定义范围分区p1
（subpartitionsp1,subpartitionsp2）,//把范围分区再散列分区分为sp1sp2
partitionp2valueslessthan（20）
（subpartitionsp3,subpartitionsp4）
）
第四种分区方法:
列表分区
1列表分区允许用户明确地控制行到分区的映射
2不同于范围分区或散列分区,它允许按自然方式对无序和不相关的数据集进行分组和组织
例如:
在客户表中记录着客户的国别信息中国,美国,法国,英国,加拿大那么在创建表时
我们可以对表进行列表分区
列表分区语法:
Createtablecustomer（//创建表customer
custNoint,
custnamevarchar（20）,
cusrStatevarchar（20）
）
Partitionbylist（custState）（//以字段custState划分列表分区
Partitionasiavalues（'中国','韩国','新加坡'）,//asia列表区只能出现中国等
partitionEuropevalues（'英国','法国','德国'）,
partitionameria（'美国','加拿大','墨西哥'）
）
注:
向已经分区的表中插入数据与普通的一样新加入的数据会自动分配到所属的分区
查询分区表数据可以按照普通的方法查询所有数据也可以只查询某个分区的数据
例如:
Select*fromstudentpartition（p1）//只查询分区p1中的数据
SQL>descuser_tab_partitions;查看数据字典中包含的表分区信息
SQL>selecttable_name,partition_name,partition_positionfromuser_tab_partitions;通过数据字典查看分区的表名分区名等
分区表维护:
添加分区:
Altertablestudentaddpartitionp5valueslessthan（120）;
删除分区:
Altertablestudentdroppartitionp4;
截短分区:
Altertablestudenttruncatepartitionp5;
合并分区:
Altertablestudentmergepartitionsp3,p4Intopartitionp6;
设计到表分区的数据字典:
User_tab_partitions;
User_ind_partitions;查询表分区的索引信息
第四章数据库对象
课程目标
同义词
序列
视图
索引
簇
同义词:
数据库对象的替换名称实质上就是别名
优点:
简化SQL语句
隐藏对象的名称和所有者
为分布式数据库的远程对象提供了位置透明性
提供对对象的公共访问
同义词类型:
私有:
在某一个用户下创建的同义词只为这个用户使用
共有:
由DBA（数据库管理员）创建的同义词并授予权限至所有用户
CREATESYNONYM同义词for原表名;命令用于创建同义词
例如:
createpublicsynonymemployeeforscott.emp;把scott用户下emp表创建"公共"同义词为employee
DROPSYNONYM命令用于删除同义词
例如:
Dropsynonymemployee;
USER_SYNONYMS数据字典包含有关同义词的信息
序列:
序列是能够自动产生连续唯一值的数据库共享对象
序列用于为主键提供值
创建序列语句:
CREATESEQUENCE序列名
[INCREMENTBYn]//增长间隔数默认是1
[STARTWITHn]//增长开始数
[{MAXVALUEn|NOMAXVALUE}]//"MAXVALUE"是指定序列的最大值如果不限定最大值使用'NOMAXVALUE'默认不限定最大值
[{MINVALUEn|NOMINVALUE}]//"MINVALUE"指定序列最小值默认为0一般序列多数向上增长也有向下减少递减方式
[{CYCLE|NOCYCLE}]//设定是否循环
[{CACHEn|NOCACHE}]//如果一个序列多个用户访问增长较快可设定缓存区大小
删除序列:
dropsequence序列名;
序列的两个重要属性:
NEXTVAL返回下一个可用的序列值或初始化序列值
CURRVAL获取当前的序列值
对于一个新定义的序列必须先用NEXTVAL产生值再可以用CURRVAL返回当先值
调用这两个方法的时候通常用--序列名.nextval或序列名.currval
修改序列定义:
AlterSequence
删除序列:
DropSequence
序列的数据字典:
USER_SEQUENCES查看数据字典用descuser_sequences;
视图:
用来显示来自一个或多个表的数据也称为"虚拟表"或"已存储的查询"大部分应用在分布式数据库中
基表:
创建视图所依据的表
视图的优点:
提供了另为一种级别的表安全性
隐藏数据的复杂性
简化用户的SQL命令
将应用程序与基表定义的修改隔离开来限定了表的修改
从另一个角度提供数据
视图的创建:
CREATEORREPLACEVIEW语句用于创建视图其中"ORREPLACE"表示如果这个用户下视图存在则替换否则即创建
ORDERBY子句可以用来按特定的顺序对行进行排列
例如:
createorreplaceviewv_empasselectempnoas编号,enameas姓名
fromscott.empwheredeptno=10;
带有错误的视图:
使用"FORCE"可以强制创建一个带有错误的视图
FORCE选项用于创建带有错误的视图
查询引用不存在的表
查询引用无效列
创建视图时没有权限
例如,可以用一个不存在的基表创建视图CREATEORREPLACEFORCEVIEWv_ssasselect*fromtt;//表tt不存在
联接视图:
通过联接多个表来创建视图
DML只能修改一个基表
键保留表:
视图涉及到多张表关联所有的表中的主键全部出现在视图当中
联机视图中的表,其主键列显示在联接视图中
视图中的函数
在视图中可以使用函数和表达式
分区视图
数据存储在单独的表分区中
在运行期间,使用UNIONALL将这些表分区联合起来
例如:
createorreplaceviewUnionViewasselect*fromtempunionallselect*fromtemp1;
表temp和temp1结构相同可分区的字段相同
DROPVIEW语句
从数据库中删除视图
有关视图的信息（数据字典）
查询USER_VIEWSdescuser_views;
注:
alterview视图名compile;重新编译视图一般用于基表结构改变等原因而导致的视图失效或暂时停用
索引:
又称为"快表"提供了快速检索表中数据的机制快表也是一张表可以存储数据
1索引与表是相关联的
2加快SQL语句的执行
3减少磁盘I/O
4CREATEINDEX语句用于创建索引
5在逻辑上和物理上独立于表中的数据
索引从物理上讲确实存储在oracle物理文件上oracle后台有数据文件存储了索引
当表中的数据增加时索引也增加当表非常大时索引页非常大
Oracle在创建数据库划分表空间时单独划出索引表空间
6Oracle自动维护索引
7普通索引的过程（除索引组织表）:
索引和表的数据是分离的检索数据是先到索引表中检索速度快再到表中把数据取出来
唯一索引
确保在定义索引的列中没有重复的值
起到唯一值约束的作用当表中数据重复时索引也会重复
Oracle自动为主键和唯一键列创建唯一索引
CREATEUNIQUEINDEX语句用于创建唯一索引
例如:
createuniqueindexidx_studentidonstudent（studentid）;//创建表stud
升级会员 