第四章-表的设计、创建及维护

第4章表的设计、创建及维护知识点回顾Oracle服务器包括：oracle数据库和oracle实例Oracle实例结构Oracle数据库的物理组件和逻辑组件结构化查询语言(sql)及分类创建新用户，并授予权限和角色2本讲目标了解Oracle数据类型掌握表的设计及创建掌握数据定义语言的使用3SQL简介2-1SQL是StructuredQueryLanguage（结构化查询语言）的首字母缩写词SQL是数据库语言，Oracle使用该语言存储和检索信息表是主要的数据库对象，用于存储数据通过SQL可以实现与Oracle服务器的通信SELECTenameFROMEmp;发送SQL查询Oracle服务器enameBLAKESMITHALLENDAVIDMARTIN发送命令输出到用户端用户4SQL简介2-2SQL支持下列类别的命令：数据定义语言（DDLDataDefinitionLanguage）数据操纵语言（DMLDataManipulationLanguage）事务控制语言（TCLTransactionControlLanguage）数据控制语言（DCLDataControlLanguage）数据定义语言CREATEALTERDROP数据操纵语言INSERTSELECTDELETEUPDATE事务控制语言COMMITSAVEPOINTROLLBACK数据控制语言GRANTREVOKE5Oracle数据类型5-1创建表时，必须为各个列指定数据类型以下是Oracle数据类型的类别：数据类型字符数值日期时间RAW/LONGRAWLOB6Oracle数据类型5-2字符数据类型CHARVARCHAR2LONG当需要固定长度的字符串时，使用CHAR数据类型。CHAR数据类型存储字母数字值。CHAR数据类型的列长度可以是1到2000个字节。VARCHAR2数据类型支持可变长度字符串VARCHAR2数据类型存储字母数字值VARCHAR2数据类型的大小在1至4000个字节范围内LONG数据类型存储可变长度字符数据LONG数据类型最多能存储2GB7Oracle数据类型5-3数值数据类型可以存储整数、浮点数和实数最高精度为

38位数值数据类型的声明语法：NUMBER[(p[,s])]P表示精度，S表示小数点的位数日期时间数据类型存储日期和时间值，包括年、月、日，小时、分钟、秒主要的日期时间类型有：DATE-存储日期和时间部分，精确到整个的秒TIMESTAMP-存储日期、时间和时区信息，秒值精确到小数点后6位8Oracle数据类型5-4RAW数据类型用于存储二进制数据RAW数据类型最多能存储2000字节LONGRAW数据类型用于存储可变长度的二进制数据LONGRAW数据类型最多能存储2GBLOBLOB称为“大对象”数据类型，可以存储多达4GB的非结构化信息，例如声音剪辑和视频文件等LOB数据类型允许对数据进行高效、随机、分段的访问BLOBCLOBBFILECLOB即CharacterLOB（字符LOB），它能够存储大量字符数据BLOB即BinaryLOB（二进制LOB），可以存储较大的二进制对象，如图形、视频剪辑和声音文件BFILE即BinaryFile（二进制文件），它用于将二进制数据存储在数据库外部的操作系统文件中9Oracle数据类型5-5Oracle中伪列就像一个表列，但是它并没有存储在表中伪列可以从表中查询，但不能插入、更新和删除它们的值常用的伪列有ROWID和ROWNUMROWID是表中行的存储地址，该地址可以唯一地标识数据表中的一行，可以使用ROWID伪列快速地定位表中的一行ROWNUM是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数10数据定义语言数据定义语言用于改变数据库结构，包括创建、更改和删除数据库对象用于操纵表结构的数据定义语言命令有：CREATETABLEALTERTABLETRUNCATETABLEDROPTABLE11表的创建和维护用来创建和修改数据库表的命令被称为“数据定义语言”（DataDefinitionLanguage，DDL）命令。这些命令实际上是专门用来创建或修改数据库对象的SQL命令。12表设计3-1首先表的名称并确定结构；确定哪些列将包含在这个表中，确定任何字符或数字列所需要的宽度在Oracle中命名表和列时的规则：表和列的名称最多可以达到30个字符长，而且必须以一个字母开始，这个限制只适合于表或列的名称，而不适合用于一列中的数据量。表和列名称中允许数字，下划线（_）和数字符号（#），但是不能在表和列名称中包含任何空格。用户拥有的每一个表应该具有一个唯一的表名称，而且每一个表中的列名称也应该是唯一的不能使用Oracle“保留字”，例如：SELECT，DISTINCT，CHAR和NUMBER等。13表设计3-2确定了表的内容，可以创建列了，必须完成为每一列选择一个名称确定每一列将存储的类型。确定（在某些情况下）列的最大宽度14表设计3-3在选择列名称之前，先看一下数据类型以及他们的默认值Oracle数据类型数据类型说明VARCHAR2(n)长度可变的字符数据，其中n表示这个列的最大长度，最大大小是4000个字符。这种数据类型没有默认大小，必须指定一个最小值。例子：VARCHAR2(9),最多可以包含9个字母、数字或符号CHAR(n)长度固定的字符，其中n表示列的长度，默认大小是1，最大大小是2000个字符。例子：CHAR(9)可以包含9个字母，数字或符号，但是输入的字符少于9个，则会向右边添加空格，使数据达到9个字符的长度。NUMBER(p,s)数字列，其中p表示精度（或小数右边和左边的总位数），最大值是38位NUMBER(7,2)DATE日期类型，默认的格式“DD-MM-YY”，可以使用TO_CHAR格式显示日期其他元素LONG最多存储2G的长度可变的字符数据CLOB用于最多4GB的单字节字符数据RAW(n)最多存储2000字节的原始二进制数据。LONGRAW最多2GB的未结构化数据BLOB最多可存储4GB的未结构化数据BFILE存储了操作系统存储的一个二进制文件的文件定位器TIMESTAMPDATE的数据扩展，不需要TO_CHAR函数就可引用时，分，秒INTERVAL用来确定特定的时间间隔或时间量15创建表关键字“CREATETABLE”指示Oralce创建一个表。可以包括可选的schema（架构），表示谁将“拥有”要创建的这个表，例如：如果创建这个表的用户也就是这个将要拥有这个表的人，那么可以忽略架构，将默认采用当前的用户名。另一方面，如果使用用户名为DRAKE为某个人创建了ACCTMANAGER表，那么架构和表名称将属于DRAKE的架构，而不是属于你的架构。数据库对象的所有者有权在对象上执行这些操作。要想为其他人的架构创建一个表（也就是其他人拥有的表），你必须具有对那个用户的架构使用CREATETABLE命令的权限。

CREATETABLE[schema]tablename(columnname

datatype[DEFAULTvalue],columnname

datatype[DEFAULTvalue],…);16定义列一个表最多可以包含1000列。CREATETABLEacctmanager(amidVARCHAR2(4),amnameVARCHAR2(20),amedateDATEDEFAULTSYSDATE,regionCHAR(2));创建表的时候允许向列分配一个默认值。默认值是用户没有在列中输入内容情况，Oracle自动存储的值。表名虽然是以小写字母输入，但ORACLE会在处理命令时自动转为大写。将表名与列名取小写字母为了与Oracle关键字区别。由于创建表的用户就是这个表的拥有者，所以省略架构名。amedate分配了一个默认值SYSDATE，表明如果用户输入新的销售经理信息，而没有包括这个人的聘用时间时，则oracle服务器会插入当前日期。注意：如果定义amedate为空，则会生成一个错误。执行了命令之后，只是创建了表结构，并没有数据。能够执行CREATETABLE命令必须有这个权限，如果没有要求管理员授予。17查看表结构：DESCRIBEDESCRIBEtablename用来显示表的结构注意：DESCRIBE是sql*plus的命令而不是SQL命令。命令的简写DESC结果中列出了定义的列和数据类型，还显示了这一列是否为空，也就是是否允许NULL值，注意：amid如果定义为了主键，则会显示NOTNULL，在讲约束的时候会讲到。18通过子查询创建表创建一个包含现有表的数据的表，可以使用CREATETABLE命令并包括一个包含子查询的AS子句。CREATETABLEtablename[(columnname,…)]AS(subquery);CREATETABLEsecustomerordersAS(SELECTcustomer#,state,ISBN,Category,quantity,cost,retailFROMcustomersNATURALJOINordersNATURALJOINOrderitemsNATURALJOINbooksWHEREstateIN('FL','GA','AL'));19修改现有表对表结构进行更改：添加一列，删除一列，更改列的大小通过ALTERTABLE命令来来实现ORACLE的特性：可以修改表而不必关闭数据库，即使用户正在访问这个表，仍然可以修改这个表而不必中断服务。

ALTERTABLEtablenameADD|MODIFY|DROPCOLUMN|coloumnname[definition];20ALTERTABLE…ADD命令示例：acctmanager表中添加电话分机号码ALTERTABLEtablenameADD(columnnamedatatype,[DEFAULT]…);ALTERTABLEacctmanagerADD(extNUMBER(4));21ALTERTBALE…MODIFY命令对列进行的更改包括：更改一列的大小（增加或减小）更改数据类型更改或添加一列的默认值ALTERTABLEtablenameMODIFY(columnnamedatatype,[DEFAULT]…);22ALTERTBALE…MODIFY命令修改这个表的时候要注意三个规则：1.一列必须与它已经包含的数据字段一样宽。（例如：假设已经将一个列定义为15个字符宽的VARCHAR2数据类型，但是，这个特定列中的最大项目只包含12个字符，因此你只能将这一列的大小减小到12个字符宽。）示例：customers表lastname列最大项字符是8，减小到6要报错ALTERTABLEcustomersMODIFY(lastnameVARCHAR(6));23ALTERTBALE…MODIFY命令2.如果一个NUMBER列已经包含了数据，那么你不能降低这一列的精确度或小数位数。ALTERTABLEbooksMODIFY(costNUMBER(4,2));24ALTERTBALE…MODIFY命令3.更改一列的默认值不会更改表中已经存在的数据值。ALTERTABLEacctmanagerMODIFY(extDEFAULT1200);同时更改多列ALTERTABLEacctmanagerMODIFY(extDEFAULT1200,amnamevarchar2(25));25ALTERTABLE…DROPCOLUMN命令使用该命令可以从一个表中删除现有列注意：因为该子句将删除列及其内容，所以使用要格外小心ALTERTABLEtablenameDROPCOLUMNcloumnname;26ALTERTABLE…DROPCOLUMN命令使用DROPCOLUMN子句时，记住以下几点：与带有ADD或MODIFY的子句的ALTERTABLE命令不同，DROPCOLUMN子句只能引用一个列。如果从表中删除一列，那么删除将是永久的，如果不小心从表中错误地删除了列，那么你不能“取消”这种损坏，唯一的选择是将这一列重新添加到表中，然后手工重新输入以前包含的所有数据。你不能删除表中剩余的最后一列，如果一个表只包含一列并且你尝试删除这一列，那么这个命令将会失败，将返回一个出错消息。27ALTERTABLE…DROPCOLUMN命令继续删除ALTERTABLEacctmanagerDROPCOLUMNext;ALTERTABLEacctmanagerDROPCOLUMNREGION;ALTERTABLEacctmanagerDROPCOLUMNAMEDATE;ALTERTABLEacctmanagerDROPCOLUMNAMNAME;ALTERTABLEacctmanagerDROPCOLUMNAMID;28ALTERTABLE…SETUNUSED/DROPUNUSEDCOLUMNS命令当Oracle服务器从一个非常大的表中删除一列时，这将降低用户的查询或其他SQL命令的处理速度，为了避免这种问题，可以在ALTERTABLE命令中包括SETUNUSED子句，将这一标记记为以后再删除.如果将一列标记为删除，那么这一列就是标记为不可用的，它不会显示在表结构中。因为这一列是不可用的，所以它也不会出现在任何查询的结果中，也不能在这一列上执行除了ALTERTABLE…DROPUNUSED命令之外的其他任何操作。换句话说，将一列设置为“unused”（不使用）之后.这一列及其所有的内容都将无法使用，以后也不能恢复它。这是推迟从存储设备中物理清除数据——通常推迟到服务器正在处理的查询很少时，例如在营业时间之后。ALTERTABLE命令使用DROPUNUSED子句来完成已经标记为“不使用”的任何列的删除过程。29ALTERTABLE…SETUNUSED/DROPUNUSEDCOLUMNS命令ALTERTABLEtablenameSETUNUSED(columnname);或者ALTERTABLEtablenameSETUNUSEDCOLUMNcolumnname;ALTERTABLEtablen