oracle第二讲(创建及管理表).ppt

第二讲创建和管理表,Oracle数据库管理系统,课程目标,了解数据类型,修改表的定义,简单的增、删、改、查操作,4,1,2,3,第二讲内容,Oracle体系介绍(物理、逻辑、服务器)Oracle的数据类型？数据表的创建、修改数据的修改和删除简单查询,4,ORACLE11g体系结构概述,在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=YOVARCHAR2型数据：YOYO,Oracle的数据类型,第二讲创建和管理表,数值数据类型NUMBER数据类型：NUMBER(p,s)：p表示数据的总有效数字位数，s表示小数的位数。注意：可以表示整数，也可以表示小数。number(5,2)表示一个小数，范围-999.99-999.99Number(5)表示一个五位整数，范围-99999-99999,Oracle的数据类型,第二讲创建和管理表,日期时间数据类型DATE：用于存储日期的世纪、年、月、日、小时、分和秒。SYSDATE函数返回当前日期和时间。TIMESTAMP：用于存储日期的年、月、日以及时间的小时、分和秒。该数据类型同时包含时区信息。SYSTIMESTAMP函数返回当前日期、时间和时区。其中秒的精度可以到ns。,Oracle的数据类型,第二讲创建和管理表,LOB数据类型（“大对象”数据类型）CLOB（CharacterLOB）：存储大量字符数据，是内部LOB。BLOB（BinaryLOB）：存储较大的二进制对象，如图形、视频剪辑和声音剪辑等，是内部LOB。BFILE（BinaryFile）：存储二进制文件，是外部LOB。,二、创建数据表,必须以字母开头必须在130个字符之间必须只能包含AZ,az,09,_,$,和#必须不能和用户定义的其他对象重名必须不能是Oracle的保留字,第二讲创建和管理表,1、Oracle数据表的命名规则,二、创建数据表,第二讲创建和管理表,2.简单表的创建语法创建表CREATETABLEschema.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;,三、修改表结构,第二讲创建和管理表,重命名表RENAMEoldtablenameTOnewtable