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);三、修改表表结构第二讲创创建和管管理表删除主键或或唯一键约约束ALTERTABLEtablenameDROPC