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

《第四章-表的设计、创建及维护.ppt》由会员分享，可在线阅读，更多相关《第四章-表的设计、创建及维护.ppt（36页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Inspur Education Inspur Group第第4 4章章 表的设计、创建及维护表的设计、创建及维护Inspur Education 知识点知识点回顾回顾lOracleOracle服务器服务器包括：包括：oracleoracle数据库和数据库和oracleoracle实例实例lOracleOracle实例结构实例结构lOracleOracle数据库的物理组件和逻辑组件数据库的物理组件和逻辑组件l结构化查询语言结构化查询语言(sqlsql)及分类及分类l创建新用户，并授予权限和角色创建新用户，并授予权限和角色2Inspur Education 本讲目标本讲目标了解了解 Oracle

2、 Oracle 数据类型数据类型 掌握表的设计及创建掌握表的设计及创建掌握数据定义语言的使用掌握数据定义语言的使用3Inspur Education SQL SQL 简介简介 2-12-1lSQL 是 Structured Query Language（结构化查询语言）的首字母缩写词lSQL 是数据库语言，Oracle 使用该语言存储和检索信息l表是主要的数据库对象，用于存储数据l通过 SQL可以实现与 Oracle 服务器的通信SELECT ename FROM Emp;发送 SQL 查询Oracle 服务器enameBLAKESMITH ALLEN DAVID MARTIN发送命令输出到用

3、户端用户4Inspur Education SQL SQL 简介简介 2-22-2lSQL 支持下列类别的命令：数据定义语言（DDL Data Definition Language）数据操纵语言（DML Data Manipulation Language）事务控制语言（TCL Transaction Control Language）数据控制语言（DCL Data Control Language）数据定义语言数据定义语言 CREATEALTERDROP数据操纵语言数据操纵语言 INSERTSELECTDELETEUPDATE事务控制语言事务控制语言COMMITSAVEPOINTROLLB

4、ACK数据控制语言数据控制语言 GRANTREVOKE5Inspur Education Oracle Oracle 数据类型数据类型 5-15-1l创建表时，必须为各个列指定数据类型l以下是 Oracle 数据类型的类别：数据类型数据类型字符数值日期时间RAW/LONG RAWLOB6Inspur Education Oracle Oracle 数据类型数据类型5-25-2字符数据类型字符数据类型CHARVARCHAR2LONGl当需要固定长度的字符串时，使用 CHAR 数据类型。lCHAR 数据类型存储字母数字值。lCHAR 数据类型的列长度可以是 1 到 2000 个字节。lVARCHA

5、R2数据类型支持可变长度字符串lVARCHAR2数据类型存储字母数字值lVARCHAR2数据类型的大小在1至4000个字节范围内lLONG 数据类型存储可变长度字符数据lLONG 数据类型最多能存储 2GB7Inspur Education Oracle Oracle 数据类型数据类型 5-35-3l数值数据类型可以存储整数、浮点数和实数最高精度为 38 位l数值数据类型的声明语法：NUMBER(p,s)P表示精度，S表示小数点的位数l日期时间数据类型存储日期和时间值，包括年、月、日，小时、分钟、秒l主要的日期时间类型有：DATE-存储日期和时间部分，精确到整个的秒TIMESTAMP-存储日期

6、、时间和时区信息，秒值精确到小数点后6位8Inspur Education Oracle Oracle 数据类型数据类型 5-45-4lRAW 数据类型用于存储二进制数据lRAW 数据类型最多能存储 2000 字节lLONG RAW 数据类型用于存储可变长度的二进制数据lLONG RAW 数据类型最多能存储 2 GBLOBlLOB 称为“大对象”数据类型，可以存储多达 4GB 的非结构化信息，例如声音剪辑和视频文件等lLOB 数据类型允许对数据进行高效、随机、分段的访问BLOBCLOBBFILECLOB 即 Character LOB（字符 LOB），它能够存储大量字符数据BLOB 即 Bin

7、ary LOB（二进制 LOB），可以存储较大的二进制对象，如图形、视频剪辑和声音文件 BFILE 即 Binary File（二进制文件），它用于将二进制数据存储在数据库外部的操作系统文件中9Inspur Education Oracle Oracle 数据类型数据类型 5-5 5-5 qOracle 中伪列就像一个表列，但是它并没有存储在表中q伪列可以从表中查询，但不能插入、更新和删除它们的值q常用的伪列有ROWID和ROWNUMROWID 是表中行的存储地址，该地址可以唯一地标识数据表中的一行，可以使用 ROWID 伪列快速地定位表中的一行ROWNUM 是查询返回的结果集中行的序号，可以

8、使用它来限制查询返回的行数10Inspur Education 数据定义语言数据定义语言数据定义语言用于改变数据库结构，包括创建、更改和删除数据库对象数据定义语言用于改变数据库结构，包括创建、更改和删除数据库对象用于操纵表结构的数据定义语言命令有：用于操纵表结构的数据定义语言命令有：CREATE TABLEALTER TABLETRUNCATE TABLEDROP TABLE11Inspur Education 表的创建和维护表的创建和维护用来创建和修改数据库表的命令被称为用来创建和修改数据库表的命令被称为“数据定义语言数据定义语言”（Data Data Definition Language

9、Definition Language，DDLDDL）命令。这些命令实际上是专门用来创建或修）命令。这些命令实际上是专门用来创建或修改数据库对象的改数据库对象的SQLSQL命令。命令。12Inspur Education 表设计表设计 3-13-1首先表的名称并确定结构；确定哪些列将包含在这个表中，确定任何字符首先表的名称并确定结构；确定哪些列将包含在这个表中，确定任何字符或数字列所需要的宽度或数字列所需要的宽度在在OracleOracle中命名表和列时的规则：中命名表和列时的规则：表和列的名称最多可以达到30个字符长，而且必须以一个字母开始，这个限制只适合于表或列的名称，而不适合用于一列中的

10、数据量。表和列名称中允许数字，下划线（_）和数字符号（#），但是不能在表和列名称中包含任何空格。用户拥有的每一个表应该具有一个唯一的表名称，而且每一个表中的列名称也应该是唯一的不能使用Oracle“保留字”，例如：SELECT，DISTINCT，CHAR和NUMBER等。13Inspur Education 表设计表设计 3-23-2u确定了表的内容，可以创建列了，必须完成确定了表的内容，可以创建列了，必须完成1.1.为每一列选择一个名称为每一列选择一个名称2.2.确定每一列将存储的类型。确定每一列将存储的类型。3.3.确定（在某些情况下）列的最大宽度确定（在某些情况下）列的最大宽度14Ins

11、pur Education 表设计表设计 3-33-3在选择列名称之前，先看一下数据类型以及他们的默认值在选择列名称之前，先看一下数据类型以及他们的默认值OracleOracle数据类型数据类型数据类型数据类型说明说明VARCHAR2(n)长度可变的字符数据，其中n表示这个列的最大长度，最大大小是4000个字符。这种数据类型没有默认大小，必须指定一个最小值。例子：VARCHAR2(9),最多可以包含9个字母、数字或符号CHAR(n)长度固定的字符，其中n表示列的长度，默认大小是1，最大大小是2000个字符。例子：CHAR(9)可以包含9个字母，数字或符号，但是输入的字符少于9个，则会向右边添加

12、空格，使数据达到9个字符的长度。NUMBER(p,s)数字列，其中p表示精度（或小数右边和左边的总位数），最大值是38位NUMBER(7,2)DATE日期类型，默认的格式“DD-MM-YY”，可以使用TO_CHAR格式显示日期其他元素LONG最多存储2G的长度可变的字符数据CLOB用于最多4GB的单字节字符数据RAW(n)最多存储2000字节的原始二进制数据。LONG RAW最多2GB的未结构化数据BLOB最多可存储4GB的未结构化数据BFILE存储了操作系统存储的一个二进制文件的文件定位器TIMESTAMPDATE的数据扩展，不需要TO_CHAR函数就可引用时，分，秒INTERVAL用来确定

13、特定的时间间隔或时间量15Inspur Education 创建表创建表u关键字“CREATE TABLE”指示Oralce创建一个表。可以包括可选的schema（架构），表示谁将“拥有”要创建的这个表，例如：如果创建这个表的用户也就是这个将要拥有这个表的人，那么可以忽略架构，将默认采用当前的用户名。另一方面，如果使用用户名为DRAKE为某个人创建了ACCTMANAGER表，那么架构和表名称将属于DRAKE的架构，而不是属于你的架构。数据库对象的所有者有权在对象上执行这些操作。要想为其他人的架构创建一个表（也就是其他人拥有的表），你必须具有对那个用户的架构使用CREATE TABLE命令的权限

14、。CREATE TABLEschema tablename(columnname datatypeDEFAULT value,columnname datatypeDEFAULT value,);16Inspur Education 定义列定义列一个表最多可以包含一个表最多可以包含10001000列。列。CREATE TABLE acctmanager(amid VARCHAR2(4),amname VARCHAR2(20),amedate DATE DEFAULT SYSDATE,region CHAR(2);u创建表的时候允许向列分配一个默认值。默认值是用户没有在列中输入内容情况，Orac

15、le自动存储的值。u表名虽然是以小写字母输入，但ORACLE会在处理命令时自动转为大写。将表名与列名取小写字母为了与Oracle关键字区别。u由于创建表的用户就是这个表的拥有者，所以省略架构名。uamedate分配了一个默认值SYSDATE，表明如果用户输入新的销售经理信息，而没有包括这个人的聘用时间时，则oracle服务器会插入当前日期。注意：如果定义amedate为空，则会生成一个错误。u执行了命令之后，只是创建了表结构，并没有数据。u能够执行CREATE TABLE命令必须有这个权限，如果没有要求管理员授予。17Inspur Education 查看表结构：查看表结构：DESCRIBED

16、ESCRIBEDESCRIBE tablename用来显示表的结构注意：DESCRIBE是sql*plus的命令而不是SQL命令。命令的简写DESC结果中列出了定义的列和数据类型，还显示了这一列是否为空，也就是是否允许NULL值，注意：amid如果定义为了主键，则会显示NOT NULL，在讲约束的时候会讲到。18Inspur Education 通过子查询创建表通过子查询创建表创建一个包含现有表的数据的表，可以使用创建一个包含现有表的数据的表，可以使用CREATE TABLECREATE TABLE命令并包括一个命令并包括一个包含子查询的包含子查询的ASAS子句。子句。CREATE TABLE

17、 tablename(columnname,)AS(subquery);CREATE TABLE secustomerordersAS(SELECT customer#,state,ISBN,Category,quantity,cost,retailFROM customers NATURAL JOIN orders NATURAL JOINOrderitems NATURAL JOIN booksWHERE state IN(FL,GA,AL);19Inspur Education 修改现有表修改现有表对表结构进行更改：添加一列，删除一列，更改列的大小对表结构进行更改：添加一列，删除一列，更

18、改列的大小通过通过ALTER TABLEALTER TABLE命令来来实现命令来来实现ORACLEORACLE的特性：可以修改表而不必关闭数据库，即使用户正在访问这个表，的特性：可以修改表而不必关闭数据库，即使用户正在访问这个表，仍然可以修改这个表而不必中断服务。仍然可以修改这个表而不必中断服务。ALTER TABLE tablenameADD|MODIFY|DROP COLUMN|coloumnnamedefinition;20Inspur Education ALTER TABLEADDALTER TABLEADD命令命令示例：示例：acctmanageracctmanager表中添加电话

19、分机号码表中添加电话分机号码ALTER TABLE tablenameADD(columnname datatype,DEFAULT);ALTER TABLE acctmanagerADD(ext NUMBER(4);21Inspur Education ALTER TBALEMODIFYALTER TBALEMODIFY命令命令对列进行的更改包括：对列进行的更改包括：更改一列的大小（增加或减小）更改数据类型更改或添加一列的默认值ALTER TABLE tablenameMODIFY(columnname datatype,DEFAULT);22Inspur Education ALTER T

20、BALEMODIFYALTER TBALEMODIFY命令命令修改这个表的时候要注意三个规则：修改这个表的时候要注意三个规则：1.一列必须与它已经包含的数据字段一样宽。（例如：假设已经将一个列定义为15个字符宽的VARCHAR2数据类型，但是，这个特定列中的最大项目只包含12个字符，因此你只能将这一列的大小减小到12个字符宽。）示例：customers表lastname列最大项字符是8，减小到6要报错ALTER TABLE customersMODIFY(lastname VARCHAR(6);23Inspur Education ALTER TBALEMODIFYALTER TBALEMOD

21、IFY命令命令2.如果一个NUMBER列已经包含了数据，那么你不能降低这一列的精确度或小数位数。ALTER TABLE booksMODIFY(cost NUMBER(4,2);24Inspur Education ALTER TBALEMODIFYALTER TBALEMODIFY命令命令3.更改一列的默认值不会更改表中已经存在的数据值。ALTER TABLE acctmanagerMODIFY(ext DEFAULT 1200);同时更改多列ALTER TABLE acctmanagerMODIFY(ext DEFAULT 1200,amname varchar2(25);25Inspur

22、 Education ALTER TABLEDROP COLUMNALTER TABLEDROP COLUMN命令命令使用该命令可以从一个表中删除现有列使用该命令可以从一个表中删除现有列注意：因为该子句将删除列及其内容，所以使用要格外小心ALTER TABLE tablenameDROP COLUMN cloumnname;26Inspur Education ALTER TABLEDROP COLUMNALTER TABLEDROP COLUMN命令命令使用使用DROP COLUMNDROP COLUMN子句时，记住以下几点：子句时，记住以下几点：与带有ADD或MODIFY的子句的ALTER

23、 TABLE命令不同，DROP COLUMN子句只能引用一个列。如果从表中删除一列，那么删除将是永久的，如果不小心从表中错误地删除了列，那么你不能“取消”这种损坏，唯一的选择是将这一列重新添加到表中，然后手工重新输入以前包含的所有数据。你不能删除表中剩余的最后一列，如果一个表只包含一列并且你尝试删除这一列，那么这个命令将会失败，将返回一个出错消息。27Inspur Education ALTER TABLEDROP COLUMNALTER TABLEDROP COLUMN命令命令继续删除ALTER TABLE acctmanagerDROP COLUMN ext;ALTER TABLE acc

24、tmanagerDROP COLUMN REGION;ALTER TABLE acctmanagerDROP COLUMN AMEDATE;ALTER TABLE acctmanagerDROP COLUMN AMNAME;ALTER TABLE acctmanagerDROP COLUMN AMID;28Inspur Education ALTER TABLESET UNUSED/DROP UNUSED COLUMNSALTER TABLESET UNUSED/DROP UNUSED COLUMNS命令命令当当OracleOracle服务器从一个非常大的表中删除一列时，这将降低用户的查询或服

25、务器从一个非常大的表中删除一列时，这将降低用户的查询或其他其他SQLSQL命令的处理速度，为了避免这种问题，可以在命令的处理速度，为了避免这种问题，可以在ALTER TABLEALTER TABLE命令中命令中包括包括SET UNUSEDSET UNUSED子句，将这一标记记为以后再删除子句，将这一标记记为以后再删除.如果将一列标记为删除，那么这一列就是标记为不可用的，它不会显示在如果将一列标记为删除，那么这一列就是标记为不可用的，它不会显示在表结构中。因为这一列是不可用的，所以它也不会出现在任何查询的结果表结构中。因为这一列是不可用的，所以它也不会出现在任何查询的结果中，也不能在这一列上执行

26、除了中，也不能在这一列上执行除了ALTER TABLEDROP UNUSEDALTER TABLEDROP UNUSED命令之外的其命令之外的其他任何操作。换句话说，将一列设置为他任何操作。换句话说，将一列设置为“unusedunused”（不使用）之后（不使用）之后.这一列这一列及其所有的内容都将无法使用，以后也不能恢复它。及其所有的内容都将无法使用，以后也不能恢复它。这是推迟从存储设备中物理清除数据这是推迟从存储设备中物理清除数据通常推迟到服务器正在处理的查通常推迟到服务器正在处理的查询很少时，例如在营业时间之后。询很少时，例如在营业时间之后。ALTER TABLEALTER TABLE命

27、令使用命令使用DROP UNUSEDDROP UNUSED子句来完成已经标记为子句来完成已经标记为“不使用不使用”的任的任何列的删除过程。何列的删除过程。29Inspur Education ALTER TABLESET UNUSED/DROP UNUSED COLUMNSALTER TABLESET UNUSED/DROP UNUSED COLUMNS命令命令ALTER TABLE tablenameSET UNUSED(columnname);或者ALTER TABLE tablenameSET UNUSED COLUMN columnname;ALTER TABLE tablenameD

28、ROP UNUSED COLUMNS;删除已经设置为“不使用”的任何列，而且可以使用这些列中包含的数据以前所占用的任何存储空间30Inspur Education ALTER TABLESET UNUSED/DROP UNUSED COLUMNSALTER TABLESET UNUSED/DROP UNUSED COLUMNS命令命令示例：示例：ALTER TABLE acctmanagerSET UNUSED(region);ALTER TABLE acctmanagerDROP UNUSED COLUMNS;31Inspur Education 重命名表重命名表RENAME oldtabl

29、ename TO newtablename;Oracle允许使用RENAMETO命令更改你所拥有的任何表的名称在一个组织工作中，不要更改其他用户访问的表的名称，除非你事先通知了他们新的表的名称RENAME acctmanager TO acctman;32Inspur Education 截断表截断表在截断一个表时，表中包含的所有行都将被删除，但表本身将会保留，换在截断一个表时，表中包含的所有行都将被删除，但表本身将会保留，换句话说，列依然存在，只是其中没有存储值。实际上与删除一个表中的所句话说，列依然存在，只是其中没有存储值。实际上与删除一个表中的所有行是相同的，但是，如果只是删除一个表中的

30、所有行，那么这些行多占有行是相同的，但是，如果只是删除一个表中的所有行，那么这些行多占用的存储空间仍然是分配给这个表的。要想删除存储在一个表中的行并释用的存储空间仍然是分配给这个表的。要想删除存储在一个表中的行并释放这些行所占有的存储空间，可以使用放这些行所占有的存储空间，可以使用TRUNCATE TABLETRUNCATE TABLE命令。命令。TRUNCATE TABLE tablename;33Inspur Education 删除表删除表DROP TABLE tablename;删除时一定要非常小心，删除一个表之后，表及它所包含的所有数据都将丢失，这个表所创建的任何索引也将删除，索引后面讲解。DROP TABLE acctman;34Inspur Education 总结总结u建表时常用的数据类型u表的设计及创建u掌握数据定义语言的使用lcreate.lalter ldrop 35Inspur Education Inspur Group谢谢谢谢!36