oracle章节总结.doc

第01章 DBMS发展史 项目（章节）补充1、数据库设计步骤：设计ER图，根据ER图生成数据库。在实际项目中经常使用PowerDesigner来进行数据库建模（要求会使用PD创建物理模型、设计表、视图和存储过程，会生成数据库；也可以根据数据库现有的表使用ODBC逆向生成PD数据模型）。 第02章 表格及约束 项目（章节）补充1、建立外键约束时，建议：被引用的主表的字段必须是主表的主键，且与子表的列的数据类型必须一致。2、通过语句添加主键时必须保证该字段为非空字段。唯一约束可以为空3、Char的效率要高于varchar数据类型，例如定义如下字段：A char(10)，假设存储一个ABC的字符串，那么剩下的七个字节会自动用空格填充，而varchar则会将剩下的七个字节去除，这样就会增加额外的操作而降低效率。所以char是以空间换效率。4、unicode含义：在windows编码中一个汉字占两个字节，一个字母占一个字节，如果使用了unicode则一个字母也占两个字节和汉字所占长度一致。5、在oracle中，增删改查操作对于日期类型要做特殊处理而不能直接以字符串做处理，例如对日期类型要做如下处理：insert into tbl_studentinfo (stubirth)values(to_date(1988-12-11,yyyy-mm-dd)，而在其他的数据库中，例如db2、mysql等可以直接将日期作为字符串插入数据库。6、在实际的项目开发中，经常将多对多关系的表拆分成两个一对多的关系，使用关系表来建立关联。在数据库建模的过程中，外键的含义是子表对主表的引用。 第03章 表格操作及事务处理 项目（章节）补充1、带外键关联的主从表操作方式：update：如果主表中信息在子表中关联存在则该关联字段不可以修改；如果不存在则可以修改。如果子表中的关联字段在修改过后在主表中可以找到关联信息并且不违反子表的主键约束则可以修改；反之则不可修改。delete：删除时应该先删除子表再删除主表。insert：插入时应该先插入主表再插入子表。2、物理删除和逻辑删除：物理删除指使用delete from方式删除数据，删除后数据在表中不存在；逻辑删除是指在表中建立一个标志列，当删除一行时只是对这列做update，例如使用1表示未删除，使用0表示删除。这样在查询的时候，可以使用where子句来判断是否显示逻辑删除的列。 第04章 单表检索 项目（章节）补充1、单表查询结构如下：1） SELECT DISTINCT | UNIQUE (*, 字段名 AS 别名, )2） FROM table name AS allies3） WHERE condition4） ORDER BY 执行顺序如下：2）- 3）- 1）- 4）2、别名问题，在oracle中对象的别名又叫同义词。oracle中对于表的别名不能使用as，而列的别名可以使用as。按照单表查询结构顺序别名可以用于排序。3、distinct用于对单列或多列的组合进行重复数据过滤，如：select distinct am from test 对a和b列数据的组合进行过滤。4、查询效率问题：select * from test 大数据量：例如在1000万条记录在这种场景下进行这样的查询无论是数据的查询还是传输都会造成系统极大的负担。改进方法：分页 列数据过大：例如test表中某列为blob类型，用于存储高清照片信息，那么使用select * 这种罗列出所有列的做法也会造成数据量过大。改进方法：选取需要的列信息，而对于不需要的列不要通过select选取出来 select a from test 索引失效：索引的作用是为了提高通过某列的限定行查询提高查询效率，但是有一些情况下会造成索引失效，例如使用like % 进行字符串的模糊匹配；使用in或not in关键字等。 5、oracle中的 / 运算符不是整除。6、oracle中的简单分页sql：select * from(select rownum as rn,tmp.* from tbl_studentinfo tmp)where rn between 3 and 6; 第05章 数据库内置函数 项目（章节）补充1、函数和过程的区别（function和procedure）：函数有返回值；过程是对业务的处理没有返回值。2、case when语句的补充用法：select stuno,classno, case when score=90 then 优秀 when score=60 and score60 以上sql语句的含义为检索出所有学生和其考试成绩信息，最后只保留分数大于60分的信息。 select * from tbl_studentinfo stu left join tbl_scoreinfo score on score.stuno=stu.stuno and score60 将score60放在on子句后面的含义是先将成绩表中的小于等于60分的学生信息过滤掉再和tbl_studentinfo做外关联，这样所有低于60分的学生信息仍然会出现在结果中，但是他们的分数这一栏显示的是null值，因为在过滤的小于60分的成绩临时表中没有与之相对应的学号存在。 4、简单行变列：select rownum rn, stdtinfA.stuno, stdtinfB.stuno, stdtinfC.stunofrom tbl_studentinfo stdtinfAleft join tbl_studentinfo stdtinfB on to_number(stdtinfA.stuno)+1= to_number(stdtinfB.stuno)left join tbl_studentinfo stdtinfC on to_number(stdtinfA.stuno)+2= to_number(stdtinfC.stuno)where mod(to_number(stdtinfA.stuno),3)=0; 第07章 分组查询及分组函数 SQL运算顺序：5) SELECT column, group_function1) FROM table2) WHERE condition3) GROUP BY group_by_expression4) HAVING group_condition6) ORDER BY column;如下SQL语句：SELECT STUNO,CLASSNO,SUM(SCORE)FROM TBL_SCOREINFOGROUP BY CLASSNO;由于stuno字段不是分组字段，所以无法在select中选取出来，所以只有出现在分组里的字段才能通过select选取出来。 第09章 其他数据库对象 项目（章节）补充一、关于视图数据更新问题-Oracle中在一些情况下可以通过视图更新数据-示例一：可更新视图:-1.create or replace view view_studentinfo asselect * from tbl_studentinfo;-2.select * from view_studentinfo;-3.update view_studentinfo set stuno=05002 where stuno=05007;-示例二：不可更新视图1。-create or replace view view_sum_score_by_class as-select classno,sum(score) from tbl_scoreinfo group by classno; 无别名报错-1.create or replace view view_sum_score_by_class asselect classno,sum(score) as s from tbl_scoreinfo group by classno;-2.select * from view_sum_score_by_class;-3.update view_sum_score_by_class set s=200 where classno=001;-示例二：不可更新视图2。-1.create or replace view view_student_class_score asselect stu.stuno,stu.stuname,ci.classno,ci.classname,sc.scorefrom tbl_studentinfo stuleft join tbl_scoreinfo sc on stu.stuno=sc.stunoleft join tbl_classinfo ci on sc.classno=ci.classno;-2.select * from view_student_class_score;-3.update view_sum_score_by_class set score=200 where classno=001;-示例三：部分可更新视图1。-1.create or replace view view_student_class_score asselect stu.stuno,stu.stuname,ci.classno,ci.classname,sc.scorefrom tbl_studentinfo stu,tbl_scoreinfo sc,tbl_classinfo ciwhere stu.stuno=sc.stuno and sc.classno=ci.classno;-2.select * from view_student_class_score;-3.-不可更新，因为stuname不是stuno和classno确定表中的字段update view_student_class_score set stuname=张三 where stuno=05001;-可更新，因为score是stuno和classno确定表中的字段update view_student_class_score set score=90 where stuno=05001;-示例四：部分可更新视图2。-1.create or replace view view_orders_items asselect o.ORDER,oi.item,oi.quantity,o.shipstreetfrom tbl_orders o,tbl_orderitems oiwhere o.ORDER=oi.ORDER;-2.select * from view_orders_items;-3.update view_orders_items set quantity=1 where ORDER=1000;-总结：视图能否更新，取决所更新的行是否能否实际的数据表中的行一一对应，如果能对应，则可以更新。- 同时要能够通过主键确定是某张表中的记录，在视图中如果出现外关联，可能会造成无法确定修改目标表。二、关于oracle索引类型说明1、 oracle索引的类型B-树索引：几乎所有的关系型数据库中都有b*tree类型索引，也是被最多使用的。其树结构与二叉树比较类似，根据rid快速定位所访问的行。B*Tree索引是最常见的索引结构，默认建立的索引就是这种类型的索引。B*Tree索引在检索高基数数据列（高基数数据列是指该列有很多不同的值）时提供了最好的性能。当取出的行数占总行数比例较小时B-Tree索引比全表检索提供了更有效的方法。但当检查的范围超过表的10%时就不能提高取回数据的性能。B-Tree索引是基于二叉树的，由分支块（branch block）和叶块（leaf block）组成。在树结构中，位于最底层底块被称为叶块，包含每个被索引列的值和行所对应的rowid。在叶节点的上面是分支块，用来导航结构，包含了索引列（关键字）范围和另一索引块的地址。假设我们要找索引中值为80的行，从索引树的最上层入口开始，定位到大于等于50，然后往左找，找到第2个分支块，定位为75100，最后再定位到叶块上，找到80所对应的rowid，然后根据rowid去读取数据块获取数据。如果查询条件是范围选择的，比如where column 20 and column 80，那么会先定位到第一个包含20的叶块，然后横向查找其他的叶块，直到找到包含80的块为止，不用每次都从入口进去再重新定位。语法：Create index indexname on tablename(columnnamecolumnname.)反向索引：反转了b*tree索引码中的字节，是索引条目分配更均匀，多用于并行服务器环境下，用于减少索引叶的竞争应用于特殊场合，在ops环境加序列增加的列上建立语法： Create index indexname on tablename(columnnamecolumnname.) reverse降序索引：8i中新出现的索引类型，针对逆向排序的查询。是B*Tree的另一个衍生物,它的变化就是列在索引中的储存方式从升序变成了降序，在某些场合下降序索引将会起作用应用于有降序排列的搜索语句中，索引中储存了降序排列的索引码，提供了快速的降序搜索。语法：Create index indexname on tablename(columnname DESCcolumnname.)位图索引 使用位图来管理与数据行的对应关系，多用于OLAP系统。- 值 1 2 3 4 5 6 7 8.- 开发 0 1 1 0 0 0 1 1.- 人事 1 0 0 0 1 1 0 0.- 财务 0 0 0 1 0 0 0 0.位图索引对于相异基数（distinctcard inality）低的数据最为合适（也就是说，与整个数据集的基数相比，这个数据只有很少几个不同的值）语法：Create BITMAP index indexname on tablename(columnnamecolumnname.)HASH索引：其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。- Hash 索引仅仅能满足=,IN和查询，不能使用范围查询- Hash 索引无法被用来避免数据的排序操作。- Hash 索引不能利用部分索引键查询。- Hash 索引在任何时候都不能避免表扫描。- Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。函数索引：这种索引中保存了数据列基于function返回的值，在select * from table where function(column)=value这种类型的语句中起作用。语法：Create index indexname on tablename(functionname(columnname)分区索引：略本地和全局索引：略索引编排表 ：略2、索引失效-类型不匹配时。-条件列包含函数但没有创建函数索引-复合索引中的前导列没有被作为查询条件。-使用IS NULL 或IS NOT NULL like-使用不等于操作符（、!=）-CBO模式下选择的行数比例过大，优化器采取了全表扫描。-CBO模式下表很久没分析，表的增长明显，优化器采取了全表扫描。3、关于RBO和CBO方式：-RBO方式：优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。- 比如我们常见的，当一个where子句中的一列有索引时去走索引。-CBO方式：它是看语句的代价(Cost),这里的代价主要指Cpu和内存。- 优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。- 统计信息给出表的大小、有少行、每行的长度等信息。这些统计信息起初在库内是没有的，- 是做analyze后才出现的，很多的时侯过期统计信息会令优化器做出一个错误的执行计划,- 因些应及时更新这些信息。-注意：走索引不一定就是优的，比如一个表只有两行数据，一次IO就可以完成全表的检索,- 而此时走索引时则需要两次IO,这时全表扫描(full table scan)是最好三、实体化视图实体化视图治理是用于汇总,预计算,复制或分发数据的对象, 在大型的数据库中使用它可以提高涉及到的SUM,COUNT,AVG,MIN,MAX等的表的查询的速度,只要在实体化视图治理上创建了统计,查询优化器将自动的使用实体化视图治理,这特性被称为QUERY REWRITE(查询重写).与普通的视图不同的是实体化视图治理存储数据,占据数据库的物理空间的. 练习1 /*利用匿名块写一段小程序，具体需求为：从控制台输入一个学生的学号，查询该学生的成绩信息，要求显示所有课程信息表中的课程名称和其考试分数，如果该学生没有参加某项考试则该课程以零分表示。如果查询的学生不存在，则提示用户查询的学生信息不存在。author:leo*/set serveroutput ondeclare-动态游标，获取所有考试科目和成绩，如果成绩不存在以零表示cursor cur_test(in_stuno varchar2) is select ci.classno,si.stuno,ci.classname,nvl(si.score,0) test_scorefrom tbl_classinfo cileft join tbl_scoreinfo si on si.classno=ci.classno and si.stuno=in_stuno;-动态游标对应的记录类型和该类型变量type type_ci_si is record(classno tbl_classinfo.classno%type,stuno tbl_scoreinfo.stuno%type,stuname tbl_studentinfo.stuname%type,classname tbl_classinfo.classname%type,score tbl_scoreinfo.score%type);rec_ci_si type_ci_si;-输入学号变量tmp_stuno varchar2(20);-学号是否存在标志量stu_count number;begin-判断学号是否存在tmp_stuno:=&请输入学号;select count(*) into stu_count from tbl_studentinfo where stuno=tmp_stuno;if (stu_count=0) thendbms_output.put_line(输入的学号不存在.);return;end if;-获取所有成绩信息if (not cur_test%isopen) thenopen cur_test(tmp_stuno);end if;loopfetch cur_test into rec_ci_si;exit when cur_test%notfound;dbms_output.put_line(rec_ci_si.classname| |rec_ci_si.classno| |rec_ci_si.score);end loop;close cur_test;-全局异常处理exceptionwhen others thendbms_output.put_line(sqlerrm);end; 练习2 /*利用匿名块写一段小程序，具体需求为：输入顾客编号查询该顾客的所有订单信息列表，要求按照如下格式显示： 订单号 订货日期 发货日期 图书总数量 订单总花费 1000 2011-08-08 2011-08-14 14 200author:leo*/set serveroutput ondeclare-动态游标，获取客户订单及总价、数量cursor cur_test(in_cus number) isselect o.ORDER,o.orderdate,o.shipdate,tmp.total_num,tmp.total_pricefrom tbl_orders oleft join (select ORDER,count(QUANTITY) total_num,nvl(sum(nvl(b.retail,0)*oi.quantity),0) total_pricefrom tbl_orderitems oileft join tbl_books b on b.isbn=oi.isbngroup by ORDER) tmp on tmp.ORDER=o.ORDERwhere o.customer=in_cus;-动态游标对应的记录类型和该类型变量type type_co_count is r