chap6管理和维护表

chap6管理和维护表王之仓青海师范大学6.1表的概述•在Oracle数据库中，每个关系表都由许多列组成。给每一列指派特定的数据类型来定义将在这个列中存储得数据类型。一、标量数据类型1、CHAR最多可以以固定长度的格式存储2000个字符或字节。默认指定为以字符形式进行存储，这个数据类型是固定长度的，并且当位数不够时，需要在其右边添加空格来补满。CREATETABLEtest(namechar(20))2、VARCHAR和VARCHAR2最多可以以可变长度来存储4000B，因此不需要空格来作补充。VARCHAR2比VARCHAR更适合使用，由于兼容性的原因，所以仍然在Oracle数据库中保留着VARCHAR。CREATETABLEtest(namevarchar2(20))一、标量数据类型3、NCHARNLS（nationallanguagesupport，国际语言支持）的数据类型仅可以存储由数据库NLS字符集定义的Unicode字符集。该数据类型最多可以存储2000B。NCHAR的列在位数不够时需要在右边填充空格。CREATETABLEtest(nameNchar(20))注意：在Oracle9i数据库及其更新的版本中，仅使用Unicode数据类型4、NVARCHAR2NLS的数据类型与VARCHAR2数据类型等价。这个数据类型最多可存储4000B。CREATETABLEtest(nameNvarchar2(20))注意：在Oracle9i数据库及其更新的版本中，仅使用Unicode数据类型一、标量数据类型5、NUMBER•用于存储零、正数、定长负数以及浮点数。•以NUMBER(P,S)的形式来定义数字的精度和范围。•p表示精度（1-38），它表示存储在列中数字的总长度是p位。•s表示范围，它表示小数点后的位数。该取值范围可以从-84到127。#使用精度5来定义一个正数（如12345）CREATETABLEtest(namenumber(5))#使用精度5和范围2来定义一个数字。如12.34。CREATETABLEtest(namenumber(5,2))6、LONGLONG类型的列存储可变长度的字符串，最多可以存储2GB的数据。LONG类型的列有很多在VARCHAR2类型列中所具有的特征。可以使用LONG类型的列来存储LONG类型的文本字符串。LONG数据类型的使用是为了向前兼容的需要。建议使用LOB数据类型来代替LONG类型。例如：CREATETABLEtest(namelong)一、标量数据类型7、DATE用于在数据库中存储日期和时间。存储时间的精度可以达到1/100s。不提供时区的相关信息。CREATETABLEtest(nameDATE)8、TIMESTAMP使用年、月、日、小时、分钟、秒域来对日期/时间提供更详细的支持。最多可以使用9位数字的精度来存储秒（受底层操作系统支持的限制）。这个数据类型没有时区的相关信息，CREATETABLEtest(timestamp_columnTIMESTAMP);一、标量数据类型9、RAW用于存储raw类型的二进制数据。最多可以存储2000B。建议使用BLOB来代替它。CREATETABLEtest(raw_columnRAW(2000));10、LONGRAW用于存储raw类型的二进制数据。最多可以存储2GB的数据。建议使用BLOB来代替它。CREATETABLEtest(raw_columnLONGRAW);一、标量数据类型11、CLOB用于存储基于字符的大对象。可存储4GB。CREATETABLEtest(clob_columnCLOB);13、BLOB最多可以存储4GB数据的二进制大对象，比如照片、PPT、二进制图像等。CREATETABLEtest(blob_columnBLOB);一、标量数据类型14、BFILE存储指向数据库外部文件的定位符。外部文件最大为4GB。CREATETABLEtest(bfile_columnBFILE);二、集合数据类型---嵌套表一种数据挖掘模型配置，该配置中表的一个列包含一个表。1、嵌套表的定义嵌套表是表中之表。一个嵌套表是某些行的集合，它在主表中表示为其中的一列。对主表中的每一条记录，嵌套表可以包含多个行。在某种意义上，它是在一个表中存储一对多关系的一种方法。考查一个包含部门信息的表，在任何时间内每个部门会有很多项目正在实施。在一个严格的关系模型中，将需要建立两个独立的表department和project。二、集合数据类型---嵌套表2举例说明嵌套表的使用假设有一个关于动物饲养员的表，希望其中具有他们饲养的动物的信息。用一个嵌套表，就可以在同一个表中存储饲养员和其饲养的全部动物的信息。1）创建类型animal_ty：此类型中，对于每个动物都包含有一个记录，记载了其品种、名称和出生日期信息。CREATETYPEanimal_tyASOBJECT(breedvarchar2(25),namevarchar2(25),birthdatedate);2）创建animals_nt：此类型将用作一个嵌套表的基础类型。CREATETYPEanimal_ntastableofanimal_ty;3）创建表breeder：饲养员的信息表createtablebreeder(breedernamevarchar2(25),animalsanimal_nt)nestedtableanimalsstoreasanimals_nt_tab;二、集合数据类型---嵌套表3、向嵌套表中插入记录insertintobreedervalues('mary',animal_nt(animal_ty('dog','butch','31-MAR-97'),animal_ty('dog','rover','31-MAR-97'),animal_ty('dog','julio','31-MAR-97')));insertintobreedervalues('jane',animal_nt(animal_ty('cat','an','31-MAR-97'),animal_ty('cat','jame','31-MAR-97'),animal_ty('cat','killer','31-MAR-97')));commit;二、集合数据类型---嵌套表4、查询嵌套表selectname,birthdatefromtable(selectanimalsfrombreeder);selectname,birthdatefromtable(selectanimalsfrombreederwherebreedername=’mary’)wherename=’dog’;三、关系数据类型--游标Cursor1概念游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。三、关系数据类型--游标Cursor2类型Cursor类型包含三种:•隐式Cursor•显式Cursor•RefCursor（动态Cursor）四、ROWID1.rowid的介绍1）SQLselectROWIDfromBruce_testwhererownum2;ROWID----------------------------AAABnlAAFAAAAAPAAA2）ROWID的格式如下：数据对象编号文件编号块编号行编号OOOOOOFFFBBBBBBRRRAAABnl是数据对象编号AAF是相关文件编号AAAAAP是块编号AAA是行编号四、ROWID创建表•Oracle创建表的步骤1创建表空间createtablespacespace_demodatafile'd:/test'size5[M/K]--务必加上autoextendon--打开自动扩展容量功能6.2创建表2创建用户createuserrfdidentifiedbyrfd520defaulttablespacespace_demo--为用户设置默认空间temporarytablespacetempspace--临时空间3权限的授予grantconnect,resourcetorfd4创建表并操作表前提是要切换到用户名下6.2.1创建普通表•CREATETABLE表名称(字段名1字段类型[DEFAULT默认值],字段名2字段类型[DEFAULT默认值],...字段名N字段类型[DEFAULT默认值]);•CREATETABLE表名称AS(子查询)–如果子查询写的是SELECT*FROMemp，表示把表结构和表内容一起复制–如果子查询写的是SELECT*FROMWHERE1==2，加入了一个永远不成立的条件，则表示只复制表结构，不复制内容！由实体关系图到创建表的例子s_dept前提条件:已有region表且含唯一关键字的字段idSQLCREATETABLEs_dept(idNUMBER(7)CONSTRAINTs_dept_id_pkPRIMARYKEY,nameVARCHAR2(25)CONSTRAINTs_dept_name_nnNOTNULL,region_idNUMBER(7)CONSTRAINTs_dept_region_id_fkREFERENCESregion(id),CONSTRAINTs_dept_name_region_id_ukUNIQUE(name,region_id));通过子查询建表的例子SQLCREATETABLEemp_41ASSELECTid,last_name,userid,start_dateFROMs_empWHEREdept_id=41;在SQLDeveloper中创建表•OracleSQLDeveloper提供了所见即所得的数据表设计器窗口，可以通过导航面板的树状视图，展开数据库连接节点，右击数据表节点，选择新建表...菜单项，将显示表设计器窗口。•在创建表窗口中，可以通过添加列按钮添加新的列，从下拉列表框中选择列数据类型及约束，并可单击DDL标签页来查看创建表的数据定义语言。创建表时的命名规则和注意事项•命名规则1)表名和字段名的命名规则：必须以字母开头，可以含符号A-Z,a-z,0-9,_,$,#2)大小写不区分3)不用SQL里的保留字,一定要用时可用双引号把字符串括起来．4)用和实体或属性相关的英文符号长度有一定的限制•注意事项:1)建表时可以用中文的字段名,但最好还是用英文的字段名2)创建表时要把较小的不为空的字段放在前面,可能为空的字段放在后面3)建表时如果有唯一关键字或者唯一的约束条件，建表时自动建了索引4)一个表的最多字段个数也是有限制的，254个.约束名的命名规则和语法•约束名的命名规则约束名如果在建表的时候没有指明，系统命名规则是SYS_Cn(n是数字)•约束名字符串的命名规则同于表和字段名的命名规则创建invoice表并指定主键CREATETABLEinvoice(invoice_idNUMBERPRIMARYKEY,--自动编号，唯一，不为空vendor_idNUMBERNOTNULL,--供应商IDinvoice_numberVARCHAR2(50)NOTNULL,--发票编号invoice_dateDATEDEFAULTSYSDATE,--发票日期invoice_totalNUMBER(9,2)NOTNULL,--发票总数payment_totalNUMBER(9,2)DEFAULT0--付款总数)在列属性中使用CONSTRAINT关键字CREATETABLEinvoice(--自动编号，唯一，不为空invoice_idNUMBERCO