第4章数据库常用对象对象

1第4章数据库对象从数据库用户的角度看，数据库中的数据是以表、视图等方式存储的。此外，用户还可以通过为表创建索引，以提高查询的执行速度。在Oracle系统中，对表、视图和索引的创建、维护是通过模式实现的。在本章中，我们将初步介绍Oracle中的模式概念，并重点介绍如何在模式中创建基本对象——基本表、索引以及其他数据库常用对象2本章学习目标：•理解什么是模式•了解模式对象与非模式对象之间的区别•初步掌握如何创建用户账户，并进行基本的管理•了解在创建表时Oracle允许使用的数据类型•掌握如何在SQL*Plus环境中创建、修改基本的表•了解其他索引的的创建和管理•了解索引组织表、临时表、视图、同义词、序列的创建和基本使用34.1.1用户和模式在Oracle中，用户不能直接操作物理结构。用户可以直接操作的是表、索引和视图这样的逻辑对象。在Oracle数据库中，逻辑对象不是随意保存在数据库中的，Oracle是通过“模式”来组织和管理这些数据库对象。一个模式下对象不能重名，但是两个不同模式下可以存在同名的对象。一个模式对象可能被存于多个数据文件中（因为存于某一个表空间，而表空间包含多个数据文件）概述•Oracle模式（方案）是逻辑结构与对象的集合•模式包含相关的数据库对象，与物理存储无关。•一个模式对应一个数据库用户，且跟用户同名。用户可以创建模式对象，用于包含或引用数据。•模式对象的创建：•OEM工具•语句对象名称作用TABLE表用于存储数据的基本结构VIEW视图以不同的侧面反映表的数据，是一种逻辑上的表INDEX索引加快表的查询速度CLUSTER聚簇将不同表的字段并用的一种特殊结构的表集合SEQUENCE序列生成数字序列，用于在插入时自动填充表的字段SYNONYM同义词为简化和便于记忆，给对象起的别名DATABASELINK数据库链接为访问远程对象创建的通道STOREDPROCEDURE、FUNCTION存储过程和函数存储于数据库中的可调用的程序和函数PACKAGE、PACKAGEBODY包和包体将存储过程、函数及变量按功能和类别进行捆绑TRIGGER触发器由DML操作或数据库事件触发的事件处理程序表1Oracle数据库模式对象Oracle非模式对象•表空间•用户•角色•模式和用户一一对应关系，模式是对象集合，用户可以管理创建模式对象，有时可以替换使用，对应的名称是一致的。但概念是完全不同的。比如创建表时可以指定创建到某个模式，也可以说成用户。4.1.2用户•用户的创建和授权（必须具有connect或createsession权限）•创建表必须有createtable和unlimitedtablespace权限，resource权限比较大，包含了创建表等的权限。•默认用户创建对应的用户表空间是users，除非显式指定，临时表空间temp•还可以给用户指定使用的空间配额quota关键字•账户的锁定和解除锁定方法•删除用户，如果用户中有对象必须指定cascade选项84.2表在Oracle系统中，表是主要的模式对象，也是基本的数据存储结构。一个表通常由多个列(字段)组成，并且每个列都具有名称，表中的每一行存放一条信息。4.2.1创建表•用户必须具备:¯CREATETABLE的权限¯一定的存贮空间•需要指定:¯表名¯列名、列的类型及列的宽度CREATETABLE[schema.]table(columndatatype[DEFAULTexpr];命名规则•必须以字母开头•长度为1–30个字符•只能包含A–Z,a–z,0–9,_,$和#•对于同一个用户不能使用和其他对象相同的名字•不能是Oracle的保留字DEFAULT选项指定在插入数据时，列的默认值…hiredateDATEDEFAULTSYSDATE,…合法的值可以是字面值、表达式或SQL函数非法的值是另一个列的名称或虚拟列默认的数据类型必须和列的类型匹配数据类型类型描述VARCHAR2(size)变长字符串CHAR(size)固定长度字符串NUMBER(p,s)变长数值DATE，timestamp日期和时间值，date用户转换LONG可存放高达2GB的变长字符数据CLOB可存放高达4GB的变长单字节数据RAWandLONGRAW二进制数据BLOB可存放高达4GB二进制数据BFILEBinarydatastoredinanexternalfile;upto4gigabytes建议使用固定长度类型，nchar表示uncode类型，一个字符占两个字节位置，rowid和rownum的使用。创建表SQLCREATETABLEdept12(deptnoNUMBER(2),3dnameVARCHAR2(14),4locVARCHAR2(13));Tablecreated.创建表验证是否创建成功SQLDESCRIBEdept1NameNull?Type--------------------------------------------DEPTNONOTNULLNUMBER(2)DNAMEVARCHAR2(14)LOCVARCHAR2(13)表特性•Tablespace子句指定表创建在哪个表空间，可以用user_tables查看表空间•Storage子句，指定表如何使用存储空间，可以根据不同表进行设置•设置数据块子句，主要减少行迁移•重做子句，指定对表的操作是否记录到日志•Cache子句可以指定将某个常用的表数据常驻内存的数据库高速缓存中。查询数据字典列出当前用户的表列出当前用户不同的对象类型查询当前用户的表、视图、同义词及序列SQLSELECTtable_name2FROMuser_tables;SQLSELECTDISTINCTobject_type2FROMuser_objects;SQLSELECT*2FROMuser_catalog;用子查询创建表•在CREATETABLE语句的AS子句中使用子查询可以在创建表的同时插入数据•所指定列的数量和类型必须和子查询中列的数量和类型相匹配–由列名和默认值定义列CREATETABLEtable[column(,column...)]ASsubquery;使用子查询创建表NameNull?Type-----------------------------------------EMPNONOTNULLNUMBER(4)ENAMEVARCHAR2(10)ANNSALNUMBERHIREDATEDATESQLDESCRIBEdept30SQLCREATETABLEdept302AS3SELECTempno,ename,sal*12ANNSAL,hiredate4FROMemp5WHEREdeptno=30;Tablecreated.184.2.2修改表在表被创建之后，如果发现对表的定义有不满意的地方，还可以对表进行修改操作。对表进行修改操作包括：增加或删除表中的字段、改变表的存储参数设置，以及对表进行增加、删减和重命名等操作。同样对表的修改即可以通过在SQL*Plus中使用ALTERTABLE语句实现，也可以通过OEM图形化工具实现。ALTERTABLE语句•使用ALTERTABLE语句可以：¯添加一个列¯修改已存在的列¯为新列定义默认值ALTERTABLEtableADD(columndatatype[DEFAULTexpr][,columndatatype]...);ALTERTABLEtableMODIFY(columndatatype[DEFAULTexpr][,columndatatype]...);添加新列DEPT30EMPNOENAMEANNSALHIREDATE------------------------7698BLAKE3420001-MAY-817654MARTIN1500028-SEP-817499ALLEN1920020-FEB-817844TURNER1800008-SEP-81...“在DEPT30表中添加新列JOB”DEPT30EMPNOENAMEANNSALHIREDATE------------------------7698BLAKE3420001-MAY-817654MARTIN1500028-SEP-817499ALLEN1920020-FEB-817844TURNER1800008-SEP-81...JOBJOB新列添加新列•使用ADD子句添加新列columns.EMPNOENAMEANNSALHIREDATEJOB-----------------------------------------7698BLAKE3420001-MAY-817654MARTIN1500028-SEP-817499ALLEN1920020-FEB-817844TURNER1800008-SEP-81...6rowsselected.SQLALTERTABLEdept302ADD(jobVARCHAR2(9));Tablealtered.新列成为表的最后一列修改列•可以修改列的数据类型、长度及默认值•改变列的默认值仅仅影响后来插入的新记录•删除列,altertablet_namedropcolumncol_n（删除一个列的话必须带column关键字，如果两个以上的列不能带关键字，并且用小括号括起来）ALTERTABLEdept30MODIFY(enameVARCHAR2(15));Tablealtered.•设置列为无用状态•Altertableempsetunused(job,sal)•数据字典user_unused_col_tabs,all_unused_col_tabs•Altertableempdropunusedcolumn•调整表特性–Move子句–手动给表分配存储空间–回收无用表空间244.2.3删除表定义当删除表定义时，需要注意用户一般情况下只能删除自己模式中的表，如果用户要删除其他模式中的表，则用户必须具有DROPANYTABLE系统权限。要删除不再需要的表，可以使用DROPTABLE语句。如下面的语句删除了表EMPLOYEE。删除表•表中的所有数据和结构都被删除•任何悬而未决的事务都将被提交•所有的索引被删除.•不能回滚该语句SQLDROPTABLEdept30;Tabledropped.4.2.4修改对象的名称•要修改表、视图、序列或同义词的名称，可以使用RENAME语句•必须拥有对象的所有权.SQLRENAMEdept1TOdepartment;Tablerenamed.4.2.5截断表•TRUNCATETABLE语句：¯删除表中所有记录¯释放该表的存储空间•使用TRUNCATE移除后不能回滚–换一种方法可以使用DELETE语句删除记录SQLTRUNCATETABLEdepartment;Tabletruncated.4.2.6给表添加注释•使用COMMENT语句，可以为表或列添加注释•注释可以通过以下字典表查询¯ALL_COL_COMMENTS¯USER_COL_COMMENTS¯ALL_TAB_COMMENTS¯USER_TAB_COMMENTSSQLCOMMENTONTABLEemp2IS'EmployeeInformation';Commentcreated.4.3数据完整性（约束）数据库不仅仅是存储数据，它也必须保证所保存的数据的正确性。如果数据不准确或不一致，那么该数据的完整性可能会受到破坏，从而给数据库本身的可靠性带来问题。为了维护数据库中数据的完整性，在创建表时常常需要定义一些约束。通过为表中的字段定义约束，可以防止将错误的数据插入到表中。对约束的定义即可以在CREATETABLE语句中进行，也可以在ALTERTABLE语句中进行。4.3.1什么是约束•约束在表一级实施相应规则•约束可以阻止删除和其它表具有相关性的表•以下是Oracle中有效的约束类型：¯NOTNULL¯UNIQUEKey¯PRIMARYKEY¯FOREI