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 YO VARCHAR2型数据 YO YO 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 二 创建数据表 必须以字母开头必须在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 四 添加数据 第二讲创建和管理表 INSERTINTOtablename field1 field2 Values val1 val2 注意 插入null和日期型字段 五 修改数据 第二讲创建和管理表 UPDATEtablenameSETfield1 val1 field2 val2 WHERE子句 注意 置空值 六 删除数据 第二讲创建和管理表 DELETEFROMtablename WHERE子句 注意 WHERE子句中的空值判断 六 删除数据 第二讲创建和管理表 TRUNCATETABLEtablename 两种方法的区别 DELETE删除 需要写日志和回滚段 能恢复 TRUNCATE删除 不写日志和回滚段 因此不能恢复 但速度快 六 删除数据 第二讲创建和管理表 TRUNCATETABLEtablename 两种方法的区别 DELETE删除 需要写日志和回滚段 能恢复 TRUNCATE删除 不写日志和回滚段 因此不能恢复 但速度快 七 实验内容 第二讲创建和管理表 创建简单表stu创建emp的备份表emp old 创建一个表stu2 并制定存储参数创建带约束的表student course score表的维护 修改列定义 增加列 删除列 禁用列 增加约束 启用与禁用主键 重命名约束 删除约束 重命名表和列 表的删除与截断 如何向表中插入日期型数据 空值如何判断 革命尚未成功 同学仍需努力