数据库设计与优化.doc

数据库应用系统设计中的一个核心问题，就是如何设计一个能够满足用户当前与可预见的末来的各项应用要求、性能良好的数据库。数据库设计是从用户的数据需求、处理要求及建立数据库的环境条件（软、硬件特性，其他限制）出发，把给定的应用环境（现实世界）存在的数据加以合理地组织起来逐步抽象成已经选定的某个数据库管理系统能够定义和描述的具体的数据结构的过程。数据库设计的成果是数据库模式和应用程序，而应用程序是以数据为基础的。因此，数据库设计中最基本的是数据库模式的设计。但是，设计一个完善的数据库系统往往是一个不断反复的过程，数据库模式必须反映数据处理的要求，保证常用的或大多数的数据处理，使用方便，性能满意，且应根据应用需求适当地修改，调整数据结构，优化数据模型，以便进一步提高数据库应用系统的性能。1规范数据模式数据模式是关系数据库的重要组成部分，构造合适的数据模式是实现关系数据库优化设计的一个重要方面。由于关系模型有较为严格的数学工具做支撑，故一般多以关系模型做讨论的环境，从而形成了关系数据库设计理论。由于这种合适的数据模式应该符合一定的规范化要求，因而又可称为关系数据库的规范化理论。11规范化理论所谓关系规范化就是按统一标准对关系进行优化，以提高关系的质量，为构造一个高效的数据库应用系统打下基础。例如有一个反映轮船信息的数据库，由以下8个属性组成：船号、船名、马力、部件号、部件名、型号、重量及用量。将这8个属性构造成一个合适的关系模式，从而构造一个关系数据库，其构造方法很多。最简单的是，将8个属性组成如下关系：轮船（船号、船名、马力、部件号、部件名、型号、重量及用量）但是，在对这个关系操作时会有3个问题：（1）冗余度大一艘轮船有N个部件，就有N次重复船号、船名、马力的数据；（2）插入异常一种部件，如果在某一阶段没有轮船用到，其对应的信息就无法插入，使数据库在功能上产生了不正常的现象，同时也给用户带来极大的不便；（3）删除异常一种部件，若只有一艘轮船用到，则删除他的信息，有关他选用的部件信息同时也删除了，从而丢失了应用的数据。从上面的分析可知，轮船关系不是一个“好”的数据库模式。一个“好”的模式应当避免发生插入异规范化理论认为，关系中的各属性是相互关联的，他们互相依赖、互相制约，构成一个结构严谨的整体。因此，在关系设计中，必须从语义中摸清这些关联，特别是依赖关系，只能把那些相互关联密切的属性拼凑在一起。构造一个“好”的数据库模式，必须使他的关系模式的属性之间满足某种内在的语义条件，而这种联系又可对关系的不同要求分为若干等级，这就是关系规范化。以函数依赖为基础的关系模式的规范化等级主要有5种，即第1范式（1NF），第2范式（2NF），第3范式（3NF），BC范式（BCNF）和第4范式（4NF），满足这些范式条件的关系模式可在不同程度上避免冗余、插入和更新异常问题。12规范化理论的应用为了消除关系模式在操作上的异常问题，优化数据模式，可进行规范化处理。具体做法是：确定数据依赖，把每个关系模式的各个属性按数据分析阶段所得到的语义写出其数据依赖，同时，考察不同的关系模式属性之间是否还存在某种数据依赖，得到一组数据依赖及诸关系的全部数据依赖。按照数据依赖的理论，逐一分析这组关系模式，确定他们属于第几范式，进行模式分解。例如，对上述轮船关系进行分解，提高范式等级，可构成一个轮船信息的数据库，他的数据模式由4个关系组成：轮船（船号、船名、马力）；使用（船号、部件号、用量）；部件（部件号、部件名、型号）；型重（型号、重量）。这4个关系属于3NF，至此，是一个比较“好”的数据库了。2改善数据库性能关系数据库的性能的好坏，主要体现在查询的速度上，他是数据库应用中的一个关键问题，是必须在数据库的设计中加以认真考虑的问题，特别是对那些响应时间要求较苛刻的应用，应予以特别注意。可从以下几个方面提高查询速度，改善数据库性能，从而达到数据库设计的优化目的。21减少连接操作连接操作对数据库的查询速度有着重要的影响，参与联接的关系越多，查询越慢。因此，对一些常用的、性能要求较高的数据库查询，最好是一元查询，这与规范化的要求相矛盾。有时为了保证性能，把规范化的关系再合并起来，称之为逆规范化。当然，这样会引起更新异常，破坏数据库完整性，必须采取相应的措施来保证数据库的完整性，从而也会增加系统的开销。因此，决定做逆规范化时，一定要权衡利弊，仔细分析应用的数据存取需求和实际的系统性能。22减小关系大小及数据量被查询关系的大小对查询速度影响很大。为了提高查询速度，可以采用水平分割或垂直分割等方法把一个关系分成几个关系，使每个关系的数据量减少。例如，对于有关学生的关系，既可以把全校集中在一个关系中，也可用水平分割的方法，分系建立关系，从而减少了每个关系的元组数。前者对全校范围内的查询较方便，后者则可以显著提高对系的查询速度；也可采用垂直分割的方法，把常用数据和不常用的数据分开，以提高常用数据的查询速度。例如：人事档案中，属性很多，有些需经常查询，有些则很少查询。如果放在一起，则关系数据量很大，影响查询速度。分开可提高常用数据的查询速度。垂直分割提高了一些操作的速度，但也可能使某些操作不得不执行连接操作，从而降低了效率。因此，决定是否进行垂直分割取决于是否垂直分割后数据库上的所有操作的总效率得到了提高。同时，垂直分割还要确保无损连接和保持函数依赖。3节省存储空间 尽管随着硬件技术的发展，提供给用户使用的存储空间越来越大，但毕竟是有限的，而数据库，尤其是一型的数据库，需要占用的存储空间比较大。因此，节省存储空间仍是数据库设计中要考虑的问题。为此，在数据库优化设计中，可采取以下措施：31缩小每个属性占用的空间一般用编码表示属性，用缩写名代替全称，可以节省存储空间，但用户看起来就不那么直观了，须根据实际条件斟酌决定。32采用假属性在有些关系中，某些数据会多次出现，采用假属性可以减少重复数据占用的存储空间。例如：在职工关系中，职工的经济状况这一属性，通常由职工号决定。一个大型企业的职工人数较多。如每一个职工逐一填写经济状况，就要占用较多的空间，而其经济状况有相同的和相似的情况，也即，经济状况这一属性不同值比较少，而在关系中可能有较多的重复。为此，可把经济状况分为几种类型，用类型代替原来的经济状况（这类经济状况的类型就是假属性），另外建立一个较小的关系来描述每种经济状况的具体内容。这样，可大大减小数据占用存储空间的用量。4结语数据库设计不同于数学问题，他是一项综合性工作，受到各种各样因素的制约，有些要求往往是彼此矛盾的。因此，设计结果常常是有得有失。因此，设计者必须根据实际情况，综合应用上述技术，在基本合理的总体设计的基础上，做一些优化调整，力求最大限度地满足用户各种各样的要求，实现数据库的优化设计。13模式分解的2条原则关系规范化是可以解决关系操作的问题，但进行模式分解时由于受到数据间的相互约束，因此分解不可能是随意的。在规范化化的关系分解过程中，不仅要着眼于提高关系的范式等级，而且应遵守以下2条原则：（1）无损分解原则无损分解就是在关系分解过程中，既不丢失数据也不增加数据，同时还能保持原有的函数依赖。一个关系分解为多个关系，原来的数据就存储到多个关系中，起码要求分解后不能丢失原来的信息。（2）相互独立原则所谓独立是指分解后的新关系之间相互独立，对一个关系内容的修改不应该影响到另一关系。此外还应注意到，关系分解必须从实际出发，并不是范式等级越高，分解得越细就越好。若把关系分解得过于琐碎，虽然对于消除数据冗余和更新异常等有好处，但在进行检索操作时往往又需要进行链接，从而使检索效率大大降低。另外，在数据操作中经常是检索操作多于更新操作，其结果很可能是分解带来的好处与检索的效率降低相比，得不偿失。正因为如此，一般规范化只需达到3NF就可以了。 PLSQL开发笔记和小结 关键字: plsql开发笔记和小结 PLSQL开发笔记和小结 *PLSQL基本结构*基本数据类型变量 1. 基本数据类型 Number 数字型 Int 整数型 Pls_integer 整数型，产生溢出时出现错误 Binary_integer 整数型，表示带符号的整数 Char 定长字符型，最大255个字符 Varchar2 变长字符型，最大2000个字符 Long 变长字符型，最长2GB Date 日期型 Boolean 布尔型（TRUE、FALSE、NULL三者取一） 在PL/SQL中使用的数据类型和Oracle数据库中使用的数据类型，有的含义是完全一致的，有的是有不同的含义的。 2. 基本数据类型变量的定义方法 变量名 类型标识符 not null:=值; declare age number(3):=26; -长度为3，初始值为26 begin commit; end; 其中，定义常量的语法格式： 常量名 constant 类型标识符 not null:=值; declare pi constant number(9):=3.1415926;-为pi的数字型常量，长度为9，初始值为3.1415926 begin commit; end;表达式 变量、常量经常需要组成各种表达式来进行运算，下面介绍在PL/SQL中常见表达式的运算规则。 1. 数值表达式 PL/SQL程序中的数值表达式是由数值型常数、变量、函数和算术运算符组成的，可以使用的算术运算符包括+（加法）、-（减法）、*（乘法）、/（除法）和*（乘方）等。 命令窗口中执行下列PL/SQL程序，该程序定义了名为result的整数型变量，计算的是10+3*4-20+5*2的值，理论结果应该是27。 set serveroutput on Declare result integer; begin result:=10+3*4-20+5*2; dbms_output.put_line(运算结果是：|to_char(result); end; dbms_output.put_line函数输出只能是字符串，因此利用to_char函数将数值型结果转换为字符型。 2. 字符表达式 字符表达式由字符型常数、变量、函数和字符运算符组成，唯一可以使用的字符运算符就是连接运算符“|”。 3. 关系表达式 关系表达式由字符表达式或数值表达式与关系运算符组成，可以使用的关系运算符包括以下9种。 大于 = 等于（不是赋值运算符:=） like 类似于 in 在之中 = 大于等于 != 不等于 或 between 在之间 关系型表达式运算符两边的表达式的数据类型必须一致。 4. 逻辑表达式 逻辑表达式由逻辑常数、变量、函数和逻辑运算符组成，常见的逻辑运算符包括以下3种。 NOT：逻辑非 OR：逻辑或 AND：逻辑与 运算的优先次序为NOT、AND和OR。PLSQL函数 PL/SQL程序中提供了很多函数供扩展功能，除了标准SQL语言的函数可以使用外，最常见的数据类型转换函数有以下3个。 To_char：将其他类型数据转换为字符型。 To_date：将其他类型数据转换为日期型。 To_number：将其他类型数据转换为数值型。 继续追加中.系统输出打印 利用pl/sql在数据库服务器端打印一句话： set serveroutput on-设置数据库输出，默认为关闭，每次重新打开窗口需要重新设置。 BEGIN DBMS_OUTPUT.PUT_LINE(Hello PL/SQL); END;pl/sql程序中对大小写不敏感（打印声明的变量） set serveroutput on DECLARE v_char varchar2(20):=a; v_char1 varchar2(20):=b; BEGIN DBMS_OUTPUT.PUT_LINE(v_char); DBMS_OUTPUT.PUT_LINE(v_char1); END;pl语句块是pl/sql里最小的编程块，其中可以再嵌套begin end begin dbms_output.put_line(Hello World); dbms_output.put_line(2*3=|(2*3); dbms_output.put_line(whats); end; PL/SQL中的变量声明 所有变量必须在declare中声明，程序中不允许声明。 没有初始化的变量默认值为null，屏幕上null是看不见的，命名习惯：PL/SQL中变量一般以v_开头（等同于存储过程中as和begin区域的变量定义习惯）。 注意number也能存小数，最长38位，所以以后建议整数都用binary_integer存。 long是字符类型，boolean类型不能打印。 标准变量类型：数字，字符，时间，布尔。 declarev_number1 number;v_number2 number(3,2) ;v_number3 binary_integer :=1;v_name varchar2(20) :=kettas;v_date date :=sysdate;v_long long :=ni hao;v_b boolean := true; beginif (v_number1 is null) thendbms_output.put_line( hello);end if;dbms_output.put_line(v_number1);dbms_output.put_line(v_number2);dbms_output.put_line(v_number3);dbms_output.put_line(v_name);dbms_output.put_line(v_date);dbms_output.put_line(v_long); -dbms_output.put_line(v_b); -执行该句ORACLE提示“调用 PUT_LINE 时参数个数或类型错误” end; 备注： 关于声明number(4,3)中括号中的两个数字的意义，前面的数字叫精度，后面的叫刻度。 刻度： 当刻度为正数的时候，表示四舍五入到小数点后面的位数 当刻度为负数的时候，表示四舍五入到小数点前面的位数 精度： 从数字的最前面不为零开始到刻度精确到的位置 v_Number number(4,3):=123.12312 1、按刻度进行四舍五入得到123.123 2、确定刻度精确到的位置123123处，精度为6位（.符号不算） 2、根据精度进行判断6位（4）精度上限值 -报错不能存储 number(3,-3):=44445 1、根据刻度3进行四舍五入得到44000 2、小数点向前移动3位44.此位置为刻度精确到的位置 3、根据精度进行判断2位（3）精度上限值 -不报错可存储结果为44000 DECLARE v_Number number(4,3):=123.12312;-实际精度6位大于上限精度值4位，提示“ORA-06502: PL/SQL: 数字或值错误 : 数值精度太高” BEGIN DBMS_OUTPUT.PUT_LINE(v_Number); END ; DECLARE v_Number number(7,3):=4555; -实际精度7位等于上限精度值，可以存储 BEGIN DBMS_OUTPUT.PUT_LINE(v_Number); END ; *变量赋值方式*oracle中变量赋值方式是值拷贝而非引用 declare v_number1 number:=100; v_number2 number; begin v_number2:=v_number1; v_number1:=200; dbms_output.put_line(v_number1); -200 dbms_output.put_line(v_number2); -100 end; *PLSQL复合类型*记录类型recordrecord类型最常用，声明的时候可以加not null，但必须给初始值，如果record类型一致可以相互赋值，如果类型不同，里面的字段恰好相同，不能互相赋值。引用记录型变量的方法是“记录变量名.基本类型变量名”。 declare type t_first is record( id number(3), name varchar2(20) ); v_first t_first; begin v_first.id:=1; v_:=cheng; dbms_output.put_line(v_first.id); dbms_output.put_line(v_); end; record类型变量间赋值declare type t_first is record( id number, name varchar2(20) ); v_first t_first; v_second t_first; begin v_first.id:=1; v_:=susu; v_second:=v_first;-相互赋值 v_first.id:=2; v_:=kettas; dbms_output.put_line(v_first.id); dbms_output.put_line(v_); dbms_output.put_line(v_second.id); dbms_output.put_line(v_); end; 表类型变量table语法如下： type 表类型 is table of 类型 index by binary_integer; 表变量名 表类型;类型可以是前面的类型定义，index by binary_integer子句代表以符号整数为索引，这样访问表类型变量中的数据方法就是“表变量名(索引符号整数)”。table类型，相当于java中的Map容器，就是一个可变长的数组，key（符号整数索引）必须是整数，可以是负数，value（类型）可以是标量，也可以是record类型。可以不按顺序赋值，但必须先赋值后使用。1. 定义一维表类型变量 declare type t_tb is table of varchar2(20) index by binary_integer; v_tb t_tb; begin v_tb(100):=hello; v_tb(98):=world; dbms_output.put_line(v_tb(100); dbms_output.put_line(v_tb(98); end; 类型为record的表类型变量 declare type t_rd is record(id number,name varchar2(20); type t_tb is table of t_rd index by binary_integer; v_tb2 t_tb; begin v_tb2(100).id:=1; v_tb2(100).name:=hello; -dbms_output.put_line(v_tb2(100).id); -dbms_output.put_line(v_tb2(100).name); dbms_output.put_line(v_tb2(100).id| |v_tb2(100).name); end; 2. 定义多维表类型变量该程序定义了名为tabletype1的多维表类型，相当于多维数组，table1是多维表类型变量，将数据表tempuser.testtable中recordnumber为60的记录提取出来存放在table1中并显示。 declare type tabletype1 is table of testtable%rowtype index by binary_integer; table1 tabletype1; begin select * into table1(60) from tempuser.testtable where recordnumber=60; dbms_output.put_line(table1(60).recordnumber|table1(60).currentdate); end; 备注：在定义好的表类型变量里，可以使用count、delete、first、last、next、exists和prior等属性进行操作，使用方法为“表变量名.属性”，返回的是数字。 set serveroutput on declare type tabletype1 is table of varchar2(9) index by binary_integer; table1 tabletype1; begin table1(1):=成都市; table1(2):=北京市; table1(3):=青岛市; dbms_output.put_line(总记录数：|to_char(table1.count); dbms_output.put_line(第一条记录：|table1.first); dbms_output.put_line(最后条记录：|table1.last); dbms_output.put_line(第二条的前一条记录：|table1.prior(2); dbms_output.put_line(第二条的后一条记录：|table1.next(2); end; * %type和%rowtype*使用%type定义变量，为了让PL/SQL中变量的类型和数据表中的字段的数据类型一致，Oracle 9i提供了%type定义方法。这样当数据表的字段类型修改后，PL/SQL程序中相应变量的类型也自动修改。 create table student( id number, name varchar2(20), age number(3,0) ); insert into student(id,name,age) values(1,susu,23); -查找一个字段的变量 declare v_name varchar2(20); v_name2 %type; begin select name into v_name2 from student where rownum=1; dbms_output.put_line(v_name2); end; -查找多个字段的变量 declare v_id student.id%type; v_name %type; v_age student.age%type; begin select id,name,age into v_id,v_name,v_age from student where rownum=1; dbms_output.put_line(v_id| |v_name| |v_age); end; -查找一个类型的变量，推荐用* declare v_student student%rowtype; begin select * into v_student from student where rownum=1; dbms_output.put_line(v_student.id| |v_| |v_student.age); end; -也可以按字段查找，但是字段顺序必须一样，不推荐这样做 declare v_student student%rowtype; begin select id,name,age into v_student from student where rownum=1; dbms_output.put_line(v_student.id| |v_| |v_student.age); end; declare v_student student%rowtype; begin select id,name,age into v_student.id,v_,v_student.age from student where id=1; -select * into v_student.id,v_,v_student.age from student where id=1; dbms_output.put_line(); end; 备注：insert，update，delete，select都可以，create table，drop table不行。DPL，DML，和流程控制语句可以在pl/sql里用，但DDL语句不行。 declare v_name %type:=wang; begin insert into student(id,name,age) values(2,v_name,26); end; begin insert into student(id,name,age) values(5,hehe,25); end; declare v_name %type:=hexian; begin update student set name=v_name where id=1; end; begin update student set name=qinaide where id=2; end; *PLSQL变量的可见空间*变量的作用域和可见性，变量的作用域为变量申明开始到当前语句块结束。当外部过程和内嵌过程定义了相同名字的变量的时候，在内嵌过程中如果直接写这个变量名是没有办法访问外部过程的变量的，可以通过给外部过程定义一个名字,通过outername变量名来访问外部过程的变量（待测试.）。 declare v_i1 binary_integer:=1; begin declare v_i2 binary_integer:=2; begin dbms_output.put_line(v_i1); dbms_output.put_line(v_i2); end; dbms_output.put_line(v_i1); -dbms_output.put_line(v_i2); 解开后执行Oracle会提示“必须说明标识符 V_I2” end; *PLSQL流程控制*if判断declare v_b boolean:=true;begin if v_b then dbms_output.put_line(ok); end if;end;if else判断declare v_b boolean:=true;begin if v_b then dbms_output.put_line(ok); else dbms_output.put_line(false); end if;end;if elsif else判断declare v_name varchar2(20):=cheng;begin if v_name=0701 then dbms_output.put_line(0701); elsif v_name=cheng then dbms_output.put_line(cheng); else dbms_output.put_line(false); end if;end;loop循环，注意推出exit是推出循环，而不是推出整个代码块declare v_i binary_integer:=0;begin loop if v_i10 then exit; end if; v_i:=v_i+1; dbms_output.put_line(hehe); end loop; dbms_output.put_line(over);end;loop简化写法declare v_i binary_integer :=0;begin loop exit when v_i10; v_i :=v_i+1; dbms_output.put_line(hehe); end loop; dbms_output.put_line(over);end;while循环declare v_i binary_integer:=0;begin while v_iv_id; begin v_id:=10; open c_student; close c_student; end;第三种游标的定义方式，带参数的游标，用的最多。 declare cursor c_student(v_id binary_integer) is select * from book where idv_id; begin open c_student(10); close c_student; end;游标的使用，一定别忘了关游标。 declare v_student book%rowtype; cursor c_student(v_id binary_integer) is select * from book where idv_id; begin open c_student(10); fetch c_student into v_student; close c_student; dbms_output.put_line(v_); end;如何遍历游标fetch 游标的属性 %found，%notfound,%isopen,%rowcount。 %found:若前面的fetch语句返回一行数据，则%found返回true，如果对未打开的游标使用则报ORA-1001异常。 %notfound，与%found行为相反。 %isopen，判断游标是否打开。 %rowcount:当前游标的指针位移量，到目前位置游标所检索的数据行的个数，若未打开就引用，返回ORA-1001。注：no_data_found和%notfound的用法是有区别的，小结如下1）SELECT . . . INTO 语句触发 no_data_found；2）当一个显式光标(静态和动态)的 where 子句未找到时触发 %notfound；3）当UPDATE或DELETE 语句的where 子句未找到时触发 sql%notfound；4）在光标的提取(Fetch)循环中要用 %notfound 或%found 来确定循环的退出条件，不要用no_data_found。下面是几个实例：create table BOOK( ID VARCHAR2(10) not null, BOOKNAME VARCHAR2(10) not null, PRICE VARCHAR2(10) not null, CID VARCHAR2(10) not null);-insertcreate or replace procedure say_hello(i_name in varchar2,o_result_msg out varchar2) as v_price varchar2(100); e_myException exception; begin insert into book(id,bookname,price) values (1,2,3); o_result_msg := success; exception when others then rollback; o_result_msg := substr(sqlerrm, 1, 200); end;-update or deletecreate or replace procedure say_hello(i_name in varchar2,o_result_msg out varchar2) as v_price varchar2(100); e_myException exception; begin update book set price = 55 where bookname = i_name; delete from book where bookname = i_name; if sql%notfound then raise e_myException; end if; /*if sql%rowcount = 0 then-写法2 raise e_myException; end if;*/ o_result_msg := success; exception when e_myException then rollback; o_result_msg := update or delete dail; end;-selectcreate or replace procedure say_hello(i_name in varchar2,o_result_msg out varchar2) as v_price varchar2(100); e_myException exception; begin select price into v_price from book where bookname = i_name; o_result_msg := success; exception when no_data_found then rollback; o_result_msg := select into dail; end;loop方式遍历游标 declare v_bookname varchar2(10