Oracle数据库管理系统教材

第二讲创建和管理表Oracle数据库管理系统课程目标创建表了解数据类型修改表的定义简单的增、删、改、查操作4123第二讲内容Oracle体系介绍(物理、逻辑、服务器)Oracle的数据类型？数据表的创建、修改数据的修改和删除简单查询4ORACLE11g体系结构概述在Oracle数据库管理系统中有3个重要的概念需要理解，那就是实例（Instance）、数据库（Database）和数据库服务器（DatabaseServer）。其中，实例是指一组Oracle后台进程以及在服务器中分配的共享内存区域；数据库是由基于磁盘的数据文件、控制文件、日志文件、参数文件和归档日志文件等组成的物理文件集合；数据库服务器是指管理数据库的各种软件工具（比如，sqlplus、OEM等）和实例及数据库三个部分。从实例与数据库之间的辩证关系来讲，实例用于管理和控制数据库；而数据库为实例提供数据。一个数据库可以被多个实例装载和打开；而一个实例在其生存期内只能装载和打开一个数据库。逻辑存储结构本讲大纲：1、数据块（DataBlocks）3、段（Segment）2、数据区（Extent）4、表空间（TableSpace）数据块（DataBlocks）数据块是Oracle逻辑存储结构中的最小的逻辑单位，也是执行数据库输入输出操作的最小存储单位。Oracle数据存放在“Oracle数据块”中，而不是“操作系统块”中。通常Oracle数据块是操作系统块的整数倍，如果操作系统快的大小为2048B，并且Oracle数据块的大小为8192B，则表示Oracle数据块由4个操作系统块构成。Oracle数据块有一定的标准大小，其大小被写入到初始化参数DB_BlOCK_SIZE中。另外，Oracle支持在同一个数据库中使用多种大小的快，与标准块大小不同的块就是非标准块。数据区（也可称作数据扩展区）是由一组连续的Oracle数据块所构成的Oracle存储结构，一个或多个数据块组成一个数据区，一个或多个数据区再组成一个段（Segment）。当一个段中的所有空间被使用完后，Oracle系统将自动为该段分配一个新的数据区，这也正符合Extent这个单词所具有的“扩展”的含义，可见数据区是Oracle存储分配的最小单位，Oracle就以数据区为单位进行存储控件的扩展。使用数据区的目的是用来保存特定数据类型的数据，也是表中数据增长的基本单位。在Oracle数据库中，分配存储空间就是以数据区为单位的。一个Oracle对象包含至少一个数据区。设置一个表或索引的存储参数包含设置它的数据区大小。数据区（Extent）段（Segment）数据区（也可称作数据扩展区）是由一组连续的Oracle数据块所构成的Oracle存储结构，一个或多个数据块组成一个数据区，一个或多个数据区再组成一个段（Segment）。当一个段中的所有空间被使用完后，Oracle系统将自动为该段分配一个新的数据区，这也正符合Extent这个单词所具有的“扩展”的含义，可见数据区是Oracle存储分配的最小单位，Oracle就以数据区为单位进行存储控件的扩展。使用数据区的目的是用来保存特定数据类型的数据，也是表中数据增长的基本单位。在Oracle数据库中，分配存储空间就是以数据区为单位的。一个Oracle对象包含至少一个数据区。设置一个表或索引的存储参数包含设置它的数据区大小。表空间（TableSpace）

Oracle使用表空间将相关的逻辑结构（比如段、数据区等）组合在一起，表空间是数据库的最大逻辑划分区域，通常用来存放数据表、索引、回滚段等数据对象（即Segment），任何数据对象在创建时都必须被指定存储在某个表空间中。表空间（属逻辑存储结构）与数据文件（属物理存储结构）相对应，一个表空间由一个或多个数据文件组成，一个数据文件只属于一个表空间；Oracle数据的存储空间在逻辑上表现为表空间，而在物理上表现为数据文件。举个例子来说，表空间相当于操作系统中的文件夹，而数据文件就相当于文件夹中的文件。每个数据库至少有一个表空间（即SYSTEM表空间），表空间的大小等于所有从属于它的数据文件大小的总和。物理存储结构本讲大纲：1、数据文件3、日志文件4、服务器参数文件5、密码文件、警告文件和跟踪文件2、控制文件数据文件数据文件是用用于保存用户户应用程序数数据和Oracle系统统内部数据的的文件，这些些文件在操作作系统中就是是普通的操作作系统文件，，Oracle在创建表表空间的同时时会创建数据据文件。Oracle数数据库在逻辑辑上由表空间间组成，每个个表空间可以以包含一个或或多个数据文文件，一个数数据文件只能能隶属于一个个表空间。控制文件是一一个二进制文文件，它记录录了数据库的的物理结构，，其中主要包包含数据库名名、数据文件件与日志文件件的名字和位位置、数据库库建立日期等等信息。控制制文件一般在在Oracle系统安装装时或创建数数据库时自动动创建，控制制文件所存放放的路径由服服务器参数文文件spfileorcl.ora的control_files参参数值来指定定。由于控制文件件存放有数据据文件、日志志文件等的相相关信息，因因此，Oracle实例例在启动时必必须访问控制制文件。如果果控制文件正正常，实例才才能加载并打打开数据库；；但若控制文文件中记录了了错误的信息息，或者实例例无法找到一一个可用的控控制文件，则则实例无法正正常启动。控制文件日志文件的主主要功能是记记录对数据所所作的修改，，对数据库所所作的修改几几乎都记录在在日志文件中中。在出现问问题时，可以以通过日志文文件得到原始始数据，从而而保障不丢失失已有操作成成果。Oracle的日志文件包包括重做日志志文件（RedoLogFile）和归档日志志文件（ArchiveLogFile），它们是Oracle系统的主要文文件之一，尤尤其是重做日日志文件，它它是Oracle数据库系统正正常运行所不不可或缺的。。下面将介绍绍这两种日志志文件。日志文件服务器参数文文件服务器参数文文件SPFILE（ServerparameterFile））是二进制文文件，用来记记录了Oracle数据据库的基本参参数信息（如如，数据库名名、控制文件件所在路径、、日志缓冲大大小等）。数数据库实例在在启动之前，，Oracle系统首先先会读取SPFILE参参数文件中设设置的这些参参数，并根据据这些初始化化参数来配置置和启动实例例。比如，设设置标准数据据块的大小（（即参数db_block_size的值）、、设置日志缓缓冲区的大小小（即参数log_buffer的的值）等等，，所以SPFILE参数数文件非常重重要。服务器器参数文件在在安装Oracle数据据库系统时由由系统自动创创建，文件的的名称为SPFILEsid.ora，sid为所创建的的数据库实例例名。与早期版本的的初始化参数数文件INITsid.ora不同同的是，SPFILE中中的参数由Oracle系统自动维维护，如果要要对某些参数数进行修改，，则尽可能不不要直接对SPFILE进行编辑，，最好通过企企业管理器（（OEM）或或ALTERSYSTEM命令来来修改，所修修改过的参数数会自动写到到SPFILE中。Oracle系统运行时时，除了必须须的数据文件件、控制文件件、日志文件件及服务器参参数文件外，，还需要一些些辅助文件，，如，密码文文件、警告文文件和跟踪文文件，下面将将对这些辅助助文件进行简简单的介绍。。密码文件、警警告文件和跟跟踪文件Oracle11g服务器结构本讲大纲：1、系统全局区区（SGA）2、程序全局区（（PGA）3、前台进程4、后台进程系统全局区（（SGA）系统全局区（（SystemGlobalArea）是是所有用户进进程共享的一一块内存区域域，也就是说说，SGA中中的数据资源源可以被多个个用户进程共共同使用。SGA主要由由高速数据缓缓冲区、共享享池、重做日日志缓存区、、java池池和大型池等等内存结构组组成。SGA随着数据库库实例的启动动而加载到内内存中，当数数据库实例关关闭时，SGA区域也就就消失了。程序全局区（（PGA）程序全局区（（ProgramGlobalArea）也可称作用用户进程全局局区，它的内内存区在进程程私有区而不不是共享区中中。虽然PGA是一个全局区区，可以把代代码、全局变变量和数据结结构都可以存存放在其中，，但区域内的的资源并不像像SGA一样被所有的的用户进程所所共享，而是是每个Oracle服务器进程都都只拥有属于于自己的那部部分PGA资源。在程序全局区区（PGA）中，一个服服务进程只能能访问属于它它自己的那部部分PGA资源区，各个个服务进程的的PGA区的总和即为为实例的PGA区的大小。通通常PGA区由私有SQL区和会话区组组成。前台进程1．用户进程用户进程是指指那些能够产产生或执行SQL语句的应用程程序，无论是是SQL*Plus，还是其它应应用程序，只只要是能生成成或执行SQL语句，都被称称作用户进程程。2．服务器进程程服务进程就是是用于处理用用户会话过程程中向数据库库实例发出的的SQL语句或SQL*Plus命令，它可以以分为专用服服务器模式和和共享服务器器模式。后台进程Oracle后台进程是一一组运行于Oracle服务器端的后后台程序，是是Oracle实例的重要组组成部分。进进程包括DBWR、CKPT、LGWR、ARCH、SMON、PMON、LCKN、RECO、DNNN、SNPN。其中SMON、PMON、DBWR、LGWR和CKPT这5个后台进程必必须正常启动动，否则将导导致数据库实实例崩溃。此此外，还有很很多辅助进程程，用于实现现相关的辅助助功能，如果果这些辅助进进程发生问题题，只是某些些功能受到影影响，一般不不会导致数据据库实例崩溃溃。Oracle的数据类型第二讲创创建和管理表表数据类型描述VARCHAR2(size)可变长字符数数据CHAR(size)定长字符数据据NUMBER(p,s)可变长数值数数据DATE日期型数据据LONG可变长字符符数据，最最大可达到到2GRAW可变长二进进制数据类类型，最大大长度4KBLONGRAW可变长二进进制数据，，最大长度度2GBCLOB字符数据，，最大可达达到4GBLOB二进制数据据，最大可可达到4GBFILE存储外部文文件的二进制数据据，最大可可达到4GROWID行地址Oracle的数据类型型第二讲创创建和管管理表字符类型CHAR：有固定长长度和最大大长度的字字符串。作为变量最最大32767个字节，作作为字段类类型最大为为2000字节。VARCHAR2数据类型：：可变长度度的字符串串。作为变量最最大32767个字节，作作为字段类类型最大为为4000字节。Clob大字符串类类型，长度度最大4GB。思考：一个空的的VARCHAR2(2000)字段和一个个空的VARCHAR2(2)字段所占用用的空间是是否是一样样的？Oracle的数据类型型第二讲创创建和管管理表字符类型比较规则：：对CHAR型数据，尾尾部的空格格将被忽略略掉，对于于VARCHAR2型数据尾部部带空格的的数据排序序比没有空空格的要大大些。比如如：CHAR型数据：‘YO’=‘YO’’VARCHAR2型数据：‘YO’<‘YO’’Oracle的数据类型型第二讲创创建和管管理表数值数据类类型NUMBER数据类型：：NUMBER(p,s)：p表示数据的的总有效数数字位数，，s表示小数的的位数。注意：可以以表示整数数，也可以以表示小数数。number(5,2)表示一个小小数，范围围-999.99---999.99Number(5)表示一个五五位整数，，范围-99999---99999Oracle的数据类型型第二讲创创建和管管理表日期时间数数据类型DATE：用于存储日日期的世纪纪、年、月月、日、小小时、分和和秒。SYSDATE函数返回当当前日期和和时间。TIMESTAMP：用于存储日日期的年、、月、日以以及时间的的小时、分分和秒。该该数据类型型同时包含含时区信息息。SYSTIMESTAMP函数返回当当前日期、、时间和时时区。其中秒的精精度可以到到ns。Oracle的数据类型型第二讲创创建和管管理表LOB数据类型（“大对象象”数据类类型）CLOB（CharacterLOB）：存储大量量字符数据据，是内部部LOB。BLOB（BinaryLOB）：存储较大的的二进制对对象，如图图形、视频频剪辑和声声音剪辑等等，是内部部LOB。BFILE（BinaryFile）：存储二进制制文件，是是外部LOB。二、创建数数据表必须以字母母开头必须在1–30个字符之间间必须只能包包含A–Z,a–z,0–9,_,$,和#必须不能和和用户定义义的其他对对象重名必须不能能是Oracle的保留字第二讲创创建和管管理表1、Oracle数据表的命命名规则二、创建数数据表第二讲创创建和管管理表2.简单表的创创建语法创建表CREATETABLE[schema.]table_name(column_name1datatype,column_name2datatype,column_name3datatype)二、创建数数据表第二讲创创建和管管理表2.简单表的创创建语法例创建stu表CREATETABLEstu(sidvarchar2(8)PrimaryKey,namevarchar2(20)NotNull,agenumber(3),addrvarchar2(40));二、创建数数据表第二讲创创建和管管理表3.利用查询创创建表用查询的结结果集创建建一张表拷贝一张表表的全部信信息，即建建一张新表表，这张表表的内容与与另外存在在的一张表表的内容完完全相同而而表名不同同。CREATETABLEnewtablenameASSELECT*FROMexisttablename;举例：CREATETABLEemp1ASSELECT*FROMemp;二、创建数数据表第二讲创创建和管管理表？拷贝一张表表，只拷贝贝已存在表表的结构，，不拷贝表表的内容。。CREATETABLEnewtablenameASSELECT*FROMexisttablenameWHERE恒假条件;举例：CREATETABLEemp2ASSELECT*FROMempWHERE1=2;三、修改表表结构第二讲创创建和管管理表修改表添加新列ALTERTABLEtable_nameADD(newcolumnnamedatatype);举例：给emp2表增加新列列ALTERTABLEemp2ADD(remarkVARCHAR2(100));三、修改表表结构第二讲创创建和管管理表更改现有列列的数据类类型和字段段宽度ALTERTABLEtablenameMODIFY(columnnamedatatype);举例：修改改remark列宽为200字节ALTERTABLEemp2MODIFY(remarkVARCHAR2(200));更改列名ALTERTABLEtablenameRENAMECOLUMNoldcolumnnameTOnewcolumnname;举例：更改改emp2表中remark列名为emailALTERTABLEemp2RENAMECOLUMNremarkTOemail;三、修改表表结构第二讲创创建和管管理表删除表中现现有列ALTERTABLEtablenameDROPCOLUMNcolumnname;举例：删除除emp2表中的email列ALTERTABLEemp2DROPCOLUMNemail;添加或删除除完整性约约束条件将某列定义义为主键约约束ALTERTABLEemp2ADDCONSTRAINTkeynamePRIMARYKEY(columnname);举例：将emp1表中的empno列设置为主主键ALTERTABLEemp1ADDCONSTRAINTPK_emp2PRIMARYKEY(empno);三、修改表表结构第二讲创创建和管管理表删除主键或或唯一键约约束ALTERTABLEtablenameDROPCONSTRAINTkeyname;举例：删除除emp2表中的主键键约束ALTERTABLEemp2DROPCONSTRAINTPK_emp2;三、修改表表结构第二讲创创建和管管理表重命名表RENAMEoldtablenameTOnewtablename;举例：将emp2表的表名修修改为newemp;RENAMEemp2TOnewemp;删除表DROPTABLEtablename;举例：删除除emp2表DROPTABLEemp2;四、、添添加加数数据据第二二讲讲创创