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)表示一个小数数，范围Number(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(newcolumnname datatype);举例：给emp2表增加新列ALTERTABLEemp2ADD(remarkVARCHAR2(100));三、修改表结结构第二讲创创建和管理表表更改现有列的的数据类型和和字段宽度ALTERTABLEtablenameMODIFY(columnnamedatatype);举例：修改remark列宽为200字节ALTERTABLEemp2MODIFY(remarkVARCHAR2(200));更改列名ALTERTABLEtablenameRENAMECOLUMNoldcolumnnameTOnewcolumnname;举例：更改emp2表中remark列名为emailALTERTABLEemp2RENAMECOLUMNremarkTOemail;三、修改表结结构第二讲创创建和管理表表删除表中现有有列ALTERTABLEtablenameDROPCOLUMNcolumnname;举例：删除emp2表中的email列ALTERTABLEemp2DROPCOLUMNemail;添加或删除完完整性约束条条件将某列定义为为主键约束ALTERTABLEemp2ADDCONSTRAINTkeynamePRIMARYKEY(columnname);举例：将em