Oracle数据库管理系统教材课件.ppt

第二讲 创建和管理表Oracle数据库管理系统课程目标课程目标创建表创建表了解数据类型了解数据类型修改表的定义修改表的定义 简单的增、删、改、查操作简单的增、删、改、查操作4123第二讲内容Oracle体系介绍(物理、逻辑、服务器)Oracle的数据类型？数据表的创建、修改数据的修改和删除简单查询4ORACLE 11g体系结构概述体系结构概述 在Oracle数据库管理系统中有3个重要的 概 念 需 要 理 解，那 就 是 实 例（Instance）、数据库（Database）和数据库服务器（Database Server）。其中，实例是指一组Oracle后台进程以及在服务器中分配的共享内存区域；数据库是由基于磁盘的数据文件、控制文件、日志文件、参数文件和归档日志文件等组成的物理文件集合；数据库服务器是指管理数据库的各种软件工具（比如，sqlplus、OEM等）和实例及数据库三个部分。从实例与数据库之间的辩证关系来讲，实例用于管理和控制数据库；而数据库为实例提供数据。一个数据库可以被多个实例装载和打开；而一个实例在其生存期内只能装载和打开一个数据库。逻辑存储结构本讲大纲：1、数据块（Data Blocks）3、段（Segment）2、数据区（Extent）4、表空间（TableSpace）数据块（Data Blocks）数据块是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的日志文件包括重做日志文件（Redo Log File）和归档日志文件（Archive Log File），它们是Oracle系统的主要文件之一，尤其是重做日志文件，它是Oracle数据库系统正常运行所不可或缺的。下面将介绍这两种日志文件。日志文件服务器参数文件 服务器参数文件SPFILE（Server parameter File）是二进制文件，用来记录了Oracle数据库的基本参数信息（如，数据库名、控制文件所在路径、日志缓冲大小等）。数据库实例在启动之前，Oracle系统首先会读取SPFILE参数文件中设置的这些参数，并根据这些初始化参数来配置和启动实例。比如，设置标准数据块的大小（即参数db_block_size的值）、设置日志缓冲区的大小（即参数log_buffer的值）等等，所以SPFILE参数文件非常重要。服务器参数文件在安装Oracle数据库系统时由系统自动创建，文件的名称为SPFILEsid.ora，sid为所创建的数据库实例名。与早期版本的初始化参数文件INITsid.ora不同的是，SPFILE中的参数由Oracle系统自动维护，如果要对某些参数进行修改，则尽可能不要直接对SPFILE进行编辑，最好通过企业管理器（OEM）或ALTER SYSTEM命令来修改，所修改过的参数会自动写到SPFILE中。Oracle系统运行时，除了必须的数据文件、控制文件、日志文件及服务器参数文件外，还需要一些辅助文件，如，密码文件、警告文件和跟踪文件，下面将对这些辅助文件进行简单的介绍。密码文件、警告文件和跟踪文件Oracle 11g服务器结构本讲大纲：1、系统全局区（SGA）2、程序全局区（PGA）3、前台进程4、后台进程系统全局区（SGA）系统全局区（System Global Area）是所有用户进程共享的一块内存区域，也就是说，SGA中的数据资源可以被多个用户进程共同使用。SGA主要由高速数据缓冲区、共享池、重做日志缓存区、java池和大型池等内存结构组成。SGA随着数据库实例的启动而加载到内存中，当数据库实例关闭时，SGA区域也就消失了。程序全局区（PGA）程序全局区（Program Global Area）也可称作用户进程全局区，它的内存区在进程私有区而不是共享区中。虽然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 可变长二进制数据类型，最大长度可变长二进制数据类型，最大长度4KB LONG RAW 可变长二进制数据，最大长度可变长二进制数据，最大长度2GBCLOB字符数据，最大可达到字符数据，最大可达到4GBLOB二进制数据，最大可达到二进制数据，最大可达到4GBFILE存储外部文件的存储外部文件的二进制数据，最大可达到二进制数据，最大可达到4GROWID行地址行地址Oracle的数据类型第二讲 创建和管理表字符类型字符类型CHAR：有固定长度和最大长度的字符串。：有固定长度和最大长度的字符串。作为变量最大作为变量最大32767个字节，作为字段类型最大为个字节，作为字段类型最大为2000字节。字节。VARCHAR2数据类型：可变长度的字符串。数据类型：可变长度的字符串。作为变量最大作为变量最大32767个字节，作为字段类型最大为个字节，作为字段类型最大为4000字节。字节。Clob 大字符串类型，长度最大大字符串类型，长度最大4GB。思考思考：一个空的：一个空的VARCHAR2(2000)字段和一个空的字段和一个空的VARCHAR2(2)字段所占用的空间是否是一样的？字段所占用的空间是否是一样的？Oracle的数据类型第二讲 创建和管理表字符类型字符类型 比较规则：比较规则：对对CHAR型数据，尾部的空格将被忽略掉，对于型数据，尾部的空格将被忽略掉，对于VARCHAR2型数据尾部带空格的数据排序比没有空格的要大些。型数据尾部带空格的数据排序比没有空格的要大些。比如：比如：CHAR 型数据：型数据：YO=YO VARCHAR2 型数据：型数据：YOYO 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（Character LOB）：存储大量字符数据，是内：存储大量字符数据，是内部部LOB。BLOB（Binary LOB）：）：存储较大的二进制对象，如存储较大的二进制对象，如图形、视频剪辑和声音剪辑等，是内部图形、视频剪辑和声音剪辑等，是内部LOB。BFILE（Binary File）：）：存储二进制文件，是外部存储二进制文件，是外部LOB。二、创建数据表必须以字母开头必须以字母开头必须在必须在 130 个字符之间个字符之间必须只能包含必须只能包含 AZ,az,09,_,$,和和#必须不能和用户定义的其他对象重名必须不能和用户定义的其他对象重名必须必须 不能是不能是Oracle 的保留字的保留字第二讲 创建和管理表1、Oracle数据表的命名规则数据表的命名规则二、创建数据表第二讲 创建和管理表2.简单表的创建语法简单表的创建语法创建表创建表CREATE TABLE schema.table_name(column_name1 datatype,column_name2 datatype,column_name3 datatype)二、创建数据表第二讲 创建和管理表2.简单表的创建语法简单表的创建语法例例 创建创建stu表表CREATE TABLE stu(sid varchar2(8)Primary Key,name varchar2(20)Not Null,age number(3),addr varchar2(40);二、创建数据表第二讲 创建和管理表3.利用查询创建表利用查询创建表用查询的结果集创建一张表用查询的结果集创建一张表拷贝一张表的全部信息，即建一张新表，这张表拷贝一张表的全部信息，即建一张新表，这张表的内容与另外存在的一张表的内容完全相同而表的内容与另外存在的一张表的内容完全相同而表名不同。名不同。CREATE TABLE newtablename AS SELECT*FROM existtablename;?举例：举例：CREATE TABLE emp1 AS SELECT*FROM emp;二、创建数据表第二讲 创建和管理表？拷贝一张表，只拷贝已存在表的结构，不拷贝拷贝一张表，只拷贝已存在表的结构，不拷贝表的内容。表的内容。CREATE TABLE newtablename AS SELECT*FROM existtablename WHERE 恒假条件恒假条件;举例：举例：CREATE TABLE emp2 AS SELECT*FROM emp WHERE 1=2;三、修改表结构第二讲 创建和管理表修改表修改表添加新列添加新列 ALTER TABLE table_name ADD(newcolumnname datatype);?举例：给举例：给emp2表增加新列表增加新列 ALTER TABLE emp2 ADD(remark VARCHAR2(100);三、修改表结构第二讲 创建和管理表更改现有列的数据类型和字段宽度更改现有列的数据类型和字段宽度 ALTER TABLE tablename MODIFY(columnname datatype);?举例：修改举例：修改remark列宽为列宽为200字节字节 ALTER TABLE emp2 MODIFY(remark VARCHAR2(200);更改列名更改列名 ALTER TABLE tablename RENAME COLUMN oldcolumnname TO newcolumnname;?举例：更改举例：更改emp2表中表中remark列名为列名为email ALTER TABLE emp2 RENAME COLUMN remark TO email;三、修改表结构第二讲 创建和管理表删除表中现有列删除表中现有列 ALTER TABLE tablename DROP COLUMN columnname;?举例：删除举例：删除emp2表中的表中的email列列 ALTER TABLE emp2 DROP COLUMN email;添加或删除完整性约束条件添加或删除完整性约束条件将某列定义为主键约束将某列定义为主键约束 ALTER TABLE emp2 ADD CONSTRAINT keyname PRIMARY KEY(columnname);?举例：将举例：将emp1表中的表中的empno列设置为主键列设置为主键ALTER TABLE emp1 ADD CONSTRAINT PK_emp2 PRIMARY KEY(empno);三、修改表结构第二讲 创建和管理表删除主键或唯一键约束删除主键或唯一键约束 ALTER TABLE tablename DROP CONSTRAINT keyname;?举例：删除举例：删除emp2表中的主键约束表中的主键约束 ALTER TABLE emp2 DROP CONSTRAINT PK_emp2;三、修改表结构第二讲 创建和管理表重命名表重命名表 RENAME oldtablename TO newtablename;?举例：将举例：将emp2表的表名修改为表的表名修改为newemp;RENAME emp2 TO newemp;删除表删除表DROP TABLE tablename;举例：删除举例：删除emp2表表 DROP TABLE emp2;四、添加数据第二讲 创建和管理表INSERT INTO tablename(field1，field2，）Values（val1，val2,）;注意：插入注意：插入null和日期型字段和日期型字段 五、修改数据第二讲 创建和管理表 UPDATE tablename SET field1=val1，field2=val2，WHERE子句子句;?注意：置空值注意：置空值 六、删除数据第二讲 创建和管理表 DELETE FROM tablename WHERE子句子句;?注意：注意：WHERE子句中的空值判断子句中的空值判断 六、删除数据第二讲 创建和管理表 TRUNCATE TABLE tablename;两种方法的区别：两种方法的区别：DELETE删除，需要写日志和回滚段，能恢复。删除，需要写日志和回滚段，能恢复。TRUNCATE删除，不写日志和回滚段，因此删除，不写日志和回滚段，因此不能恢复，但速度快。不能恢复，但速度快。六、删除数据第二讲 创建和管理表 TRUNCATE TABLE tablename;两种方法的区别：两种方法的区别：DELETE删除，需要写日志和回滚段，能恢复。删除，需要写日志和回滚段，能恢复。TRUNCATE删除，不写日志和回滚段，因此删除，不写日志和回滚段，因此不能恢复，但速度快。不能恢复，但速度快。七、实验内容第二讲 创建和管理表创建简单表创建简单表stu创建创建emp的备份表的备份表emp_old;创建一个表创建一个表stu2，并制定存储参数，并制定存储参数创建带约束的表创建带约束的表student、course、score表的维护：修改列定义、增加列、删除列、禁用列、增加约束、启用表的维护：修改列定义、增加列、删除列、禁用列、增加约束、启用与禁用主键、重命名约束、删除约束、重命名表和列。与禁用主键、重命名约束、删除约束、重命名表和列。表的删除与截断表的删除与截断？如何向表中插入日期型数据、空值如何判断？如何向表中插入日期型数据、空值如何判断革命尚未成功，同学仍需努力!