业软公共_新员工培训_Oracle数据库知识-SQL_培训胶片V1.0.ppt

Oracle基础知识SQL,CCSELECT子句用于指定检索数据库的中哪些列，FROM子句用于指定从哪一个表或视图中检索数据，还可以把多个表、视图的数据结合起来，使得查询结果的每一行中包含来自多个表达式或视图的数据，这种操作被称为连接查询。WHERE子句中的条件可以是一个包含等号或不等号的条件表达式，也可以是一个含有IN、NOTIN、BETWEEN、LIKE、ISNOTNULL等比较运算符的条件式，还可以是由单一的条件表达通过逻辑运算符组合成复合条件。,Page7,INSERTINTO,基本语法：INSERTINTOtablenamecolumn1,column2,VALUESexpression1,expression2,;样例1：INSERTINTODEPT（DNAME，DEPTNO）VALUES(ACCOUNTING，10);样例2：INSERTINTODEPT（DNAME，DEPTNO）SELECTACCOUNTING，10FROMDUAL;,Page8,DELETE,基本语法：DELETEFROMtablenameWHEREconditions;示例：DELETEFROMEMPWHEREEMPNO=7654;DELETE命令删除一条记录,而且DELETE命令只能删除整行,而不能删除某行中的部分数据。DELETE语句和TRUNCATE语句的差别：DELETE语句是DML语句，TRUNCATE语句是DDL语句。DELETE语句在删除记录的时候不释放表空间占用的磁盘资源，TRUNCATE则释放。DELETE语句可以删除表中满足条件的记录，TRUNCATE只能进行整表删除。DELETE语句在删除时，在回滚段表空间中需要记录回滚信息，而TRUNCATE语句则没有。,Page9,UPDATE,基本语法：UPDATEtablenameSETcolumn1=expression1,column2=expression2,WHEREconditions;实例：UPDATEEMPSETJOB=MANAGERWHEREENAME=MAPTIN;事务控制命令提交命令(COMMIT)，可以使数据库的修改永久化。使用方法是：SQLSETCOMMIT;回滚命令(ROLLBACK)，消除上一个COMMIT命令后的所做的全部修改，使得数据库的内容恢复到上一个COMMIT执行后的状态。使用方法是:SQLROLLBACK;,Page10,创建并管理表,Page11,Oracle常用数据类型,Page12,Oracle块空间的使用,Inserts,Inserts,Inserts,Inserts,1,2,3,4,PCTFREE=20PCTUSED=40,80%,80%,40%,Page13,创建表,CREATETABLEt_Dept(DeptNoVARCHAR2(4)notnull,DeptNameVARCHAR2(20)notnull,CreateDateDATE,MembersNUMBER)tablespaceTBS_LOG_DATpctfree10pctused85initrans1maxtrans255storage(initial10Mnext1Mminextents1maxextentsunlimitedpctincrease0);,Page14,使用子查询创建表,CRETAETBALEt_Dept_bk(DeptNoVARCHAR2(4)notnull,DeptNameVARCHAR2(20)notnull,CreateDateDATE,MembersNUMBER)ASSELECTt.DeptNo,t.DeptName,t.CreateDate,t.MembersFROMt_DepttWHEREt.DeptNoLIKE02%;,Page15,使用分区表,把一个大的表或索引分割成多块数据片（区），SQL语句可以用数据区访问来代替对整个表或索引的访问。这种表存储方式叫分区表（PARTITION）。一般来说，表的Partitioning多应用在数据仓库（大量历史数据的储存和访问）。通过对表或索引的分区实现了大对象向小对象的转化。分区表的分区方式主要分成rangepartitioning和hashpartitioning：Range把表或索引中同一范围的值划分到一起。hash通过hashfunction划分数据。compositepartitioning把数据首先通过range方式划分，然后通过hash方式进行细化。,Page16,创建分区表,CRETAETBALEt_Dept(DeptNoVARCHAR2(2)notnull,DeptNameVARCHAR2(20)notnull,CreateDateDATE,MembersNUMBER)tablespaceTBS_DATpctfree10pctused85initrans1maxtrans255storage(initial10Mnext1Mpctincrease0)partitionbyrange(DeptNo)(partitionP01VALUESLESSTHAN(04)TABLESPACETBS_DAT,partitionP02VALUESLESSTHAN(07)TABLESPACETBS_DAT,partitionP03VALUESLESSTHAN(MAXVALUE)TABLESPACETBS_DAT);,Page17,分区表扩容,ALTERTABLEt_DeptADDpartitionP04valueslessthanTHAN(10)TABLESPACETBS_DATstorage(initial100knext100kminextents1maxextentsunlimitedpctincrease0);,Page18,创建和使用视图,Page19,使用视图,视图是封装的复合查询。当对一组数据建立视图以后你像处理另外一个表一样去处理视图，但是在视图中修改数据时要受到一些限制；当表中的数据改变以后你将会在查询视图时发现相应的改变。视图并不占用数据库或表的物理空间。,CREATEORREPLACEVIEWv_Rpt_OrgaASSELECT99ASOrgaID,所有地市ASOrgaNameFROMdualUNIONALLSELECTOrgaID,OrgaNameFROMt_pub_OrgaWHERESuperID=0;,Page20,为什么使用视图,简单性：看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。安全性：通过视图用户只能查询和修改他们所能见到的数据，数据库中的其它数据则既看不见也取不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定行和列上。逻辑数据独立性：视图可帮助用户屏蔽真实表结构变化带来的影响使，应用程序和数据库表在一定程度上分割开来。,Page21,在视图中修改数据的几个限制,对于多表视图不能使用DELETE语句。除非底层表的所有非空列都已经在视图中出现否则不能使用INSERT语句。如果对一个归并的表格插入或更新记录那么所有被更新的记录必须属于同一个物理表。如果在创建视图时使用了DINTINCT子句那么你就不能插入或更新这个视图中的记录。不能更新视图中的虚拟列（它是用计算字段得到的）。,Page22,创建和使用索引,Page23,使用索引,SQL采用树形结构来存储和返回索引数据，用以指示的数据存储在树的最末端（也就是叶子），它们被称为结点（也可以叫叶子），每一个结点中有一个指向其它结点的指针，结点左边的值只是它的双亲结点，结点右边的值则是孩子结点或叶子。SQL将从根结点开始直到找到所需要的数据。在SQL中使用索引是基于以下几个原因：在使用UNIQUE关键字时强制性地保证数据的完整性可以容易地用索引字段或其它字段进行排序提高查询的执行速度,Page24,创建索引,createuniqueindexix_tAgentInfoont_AgentInfo(AGENTID,SUBCCNO)tablespaceICD30_SERVICE_INDpctfree10initrans2maxtrans255storage(initial64Knext1Mminextents1maxextents500pctincrease0);,Page25,创建基于函数的索引,createindexix_tAgentInfoont_AgentInfo(UPPER(AGENTID)tablespaceICD30_SERVICE_INDpctfree10initrans2maxtrans255storage(initial64Knext1Mminextents1maxextents500pctincrease0);,Page26,在分区表上创建索引,createindexix_tAgentInfoont_AgentInfo(AGENTID)localtablespaceICD30_SERVICE_INDpctfree10initrans2maxtrans255storage(initial64Knext1Mminextents1maxextents500pctincrease0);,Page27,重建索引,alterindexix_tAgentInforebuild;,创建索引时，oracle会为索引创建索引树，表和索引树通过rowid(伪列)来定位数据。当表里的数据发生更新时，oracle会自动维护索引树。但是在索引树中没有更新操作，只有删除和插入操作。例如在某表id列上创建索引，某表id列上有值“101”，当我将“101”更新为“110”时，oracle同时会来更新索引树，但是oracle先将索引树中的“101”标示为删除（实际并未删除，只是标示一下），然后再将“110”写到索引树中。如果表更新比较频繁，那么在索引中删除标示会越来越多，这时索引的查询效率必然降低，所以应该定期重建索引，来消除索引中这些删除标记,Page28,使用索引的一般性原则（1）,表的主键、外键必须有索引，当PRIMARYKEY和UNIQUEKEY的约束有效时，Oracle自动创建此索引。经常与其他表进行连接的表，在连接字段上应该建立索引。经常出现在Where子句中的字段，特别是大表的字段，应该建立索引。索引应该建在选择性高的字段上，否则即使建了索引也无法被利用。索引应该建在小字段上，对于大的文本字段甚至超长字段，如LOB、LONG、LONGRAW等类型字段不能建索引。复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替。频繁进行数据操作的表，不要建立太多的索引。避免使用位图索引，特别是频繁进行数据操作的表，禁止使用位图索引,Page29,使用索引的一般性原则（2）,对于分区表，除有唯一性要求的字段建全局索引外，其它字段一律建成局部索引。索引对应SELECT语句来说，是加快了检索的速度，但对于UPDATE、INSERT和DELETE来说，则是减慢了速度。因此对于临时性的大批量数据操作，尽可能先删除索引，事务完成后再重建。删除无用的索引，避免对执行计划造成负面影响。频繁进行数据操作的表，需对索引进行周期性重建。SQL语句的WHERE子句中应尽可能将操作移至等式的右边，特别是索引字段的操作，必要时可考虑建函数索引。SQL语句的WHERE子句中操作符两边类型应相同，避免潜在的数据类型转换。,Page30,创建其它常用对象,Page31,创建定时任务,DECLAREJobNumNUMBER(5):=0;JobIDNUMBER(5);BEGINSELECTCOUNT(*)INTOJobNumFROMDBA_JOBSWHEREUPPER(SUBSTRB(WHAT,1,21)=UPPER(Sp_Rpt_DelSrvTmpTable);IFJobNum0THENSELECTNVL(JOB,0)INTOJobIDFROMDBA_JOBSWHEREUPPER(SUBSTRB(WHAT,1,21)=UPPER(Sp_Rpt_DelSrvTmpTable);IFJobID0THENDBMS_JOB.REMOVE(JobID);ENDIF;ENDIF;DBMS_JOB.SUBMIT(JobID,Sp_Rpt_DelSrvTmpTable;,TRUNC(SYSDATE,DD)+25/24,TRUNC(SYSDATE+1,DD)+1/24);COMMIT;END;,Page32,创建数据库连接,CREATEPUBLICDATABASELINKlinkNameCONNECTTOusernameIDENTIFIEDBYpasswordUSINGconnectstring,DATABASELINK使物理上存放于网络的多个ORACLE数据库，逻辑上可以看成一个单一的大型数据库。必须首先在建立链接的数据库上设置链接字符串，即配置一个远程数据库的本地网络服务名。最好把global_name改为false，否则数据库连接名