情境五 表的创建与管理校本教材.docx

情境五 表的创建与管理校本教材.docx

《情境五 表的创建与管理校本教材.docx》由会员分享，可在线阅读，更多相关《情境五 表的创建与管理校本教材.docx（43页珍藏版）》请在冰豆网上搜索。

情境五表的创建与管理校本教材

情境五表的创建与管理

【学习目标】

Ø定义方案对象和数据类型

Ø使用OEM创建和修改、查看和删除数据表

Ø使用、创建、修改、查看和删除数据表。

Ø使用PL/SQL创建、修改、查看和删除数据表。

Ø使用SQLPlus创建、修改、查看和删除数据表。

Ø使用SQLPlus添加、删除和修改数据表中的记录

Ø数据完整性约束操作

v非空

v默认

v唯一v检查

v主键

v外键

Ø管理序列和同义词

Ø临时表

【学习指导】

Oracle中的方案

“方案”是指由特定用户拥有的数据库对象的集合。

对于生产数据库而言，该用户通常代表的不是一个人，而是一个应用程序。

方案的名称与拥有该方案的用户的名称相同。

方案

对象是指直接引用数据库数据的逻辑结构。

方案对象包含表、视图和索引等多种结构。

使用SQL或EnterpriseManager可创建并处理方案对象。

使用OracleEnterprise

Manager时，系统会生成基础SQL语句。

注：

方案不一定需要直接与单个表空间相关。

你可以定义一些配置，以便一个方案中的

对象可以保存在不同的表空间中，一个表空间也可以保存不同方案中的对象。

Oracle11g数据库中并不是所有的数据库对象都是方案对象，方案对象有表、索引、触发器、数据库链接、PL/SQL包、序列、同义词、视图、存储过程、存储函数等，非方案对象有表空间、用户、角色、概要文件等。

在Oracle11g数据库中，每个用户都拥有自己的方案，创建了一个用户，就创建了一个同名的方案，方案与数据库用户是对应的。

用户在数据库中创建了一个方案对象后，这个方案对象默认地属于这个用户的方案。

创建数据库时，系统会创建多个方案，其中包括以下两个重要的方案：

ØSYS方案：

包含数据字典

ØSYSTEM方案：

包含存储管理信息的其它表和视图，在完整安装OracleDB期间，会自动安装示例方案。

示例方案用于为Oracle文档和课件中的示例提供一个公用平台。

这些示例方案是一组互相有联系的方案，旨在提供不同复杂程度的示例，其中包括：

ØBI：

“业务智能”方案包含的一组对象用于展示业务智能功能。

ØHR：

“人力资源”方案是一个用于介绍基本主题的简单方案。

此方案经过扩展后可支持OracleInternetDirectory演示。

ØOE：

“订单录入”方案是一个中等复杂程度的方案。

OE方案中提供了多种数据类型。

OC（OnlineCatalog，联机目录）子方案是内置于OE方案中的对象关系数据库对象的

集合。

ØIX：

“信息交换”方案包含的一组对象用于展示通过企业对企业（B2B）应用程序进行发运。

ØPM：

“产品媒体”方案专用于多媒体数据类型。

ØSH：

“销售历史记录”方案用于演示大量数据。

这个方案经过扩展可支持高级分析处理。

以下为方案所包含的相关对象

关系数据库与表

关系数据库使用关系或二维表存储信息。

例如，你可能需要存储公司中所有雇员的信息。

在关系数据库中，可以创建多个表来存储雇员的各种信息，如雇员表、部门表和薪金表。

关系数据库可以包含一个或多个表。

表是RDBMS的基本存储结构。

表包含现实生活中某些人或事物（如雇员、发票或客户）的所有必需数据。

上图显示了EMPLOYEES（雇员表）的内容。

其中给出的圆形编号分别表示以下内容：

1.单个行（又称元组），代表一位特定雇员的所有必需数据。

表中的每一行均应由主键进行标识，以避免出现重复的行。

行的顺序无关紧要，可以在检索数据时指定行的顺序。

2.包含雇员编号的列或属性。

在EMPLOYEES表中，雇员编号用于唯一地标识一个雇员。

在本示例中，雇员编号列被指定为主键。

主键必须包含值，而且此值必须是唯一的。

3.不含键值的列。

列代表表中的一类数据，本示例中的数据为所有雇员的薪金。

存储数据时列的顺序无关紧要，可以在检索数据时指定列的顺序。

4.包含部门编号的列，此列也是外键。

外键是定义表之间如何关联的列。

外键用于引

用同一个表或另一表中的主键或唯一键。

在本示例中，DEPARTMENT_ID用于唯一地标识

DEPARTMENTS表中的部门。

5.行和列的交叉处是字段。

字段中只能有一个值。

6.字段中也可以没有值。

此时称为空值。

在EMPLOYEES表中，只有是销售代表的雇员在COMMISSION_PCT（佣金）字段中才有值。

使用SQL查询数据库在关系数据库中，你不必指定表的访问路径，也无需知道数据的实际排列方式。

要访问该数据库，请执行结构化查询语言（SQL）语句，该语言是美国国家标准协会（ANSI）制定的一种标准语言，用于操作关系数据库。

SQL是所有程序和用户访问OracleDB中的数据时使用的语句的集合。

借助应用程序和Oracle工具，用户通常不需要直接使用SQL访问数据库，但这些应用程序在执行用户请求时必须使用SQL。

SQL为多种任务提供了语句，其中包括：

◆查询数据

◆在表中插入、更新和删除行

◆创建、替换、更改和删除对象

◆控制对数据库及其对象的访问

◆保证数据库的一致性和完整性

SQL将所有任务统一为一种语言，可以在一个逻辑层上处理数据。

Oracle支持的SQL语句遵循行业标准。

OracleCorporation积极地与SQL标准委员会的重要职员保持联系，以确保始终遵从不断发展的标准。

行业公认的委员会有ANSI和国际标准组织（ISO）。

ANSI和ISO都已接受SQL作为关系数据库的标准语言。

Oracle中SQL语言共分为4类：

语句

说明

SELECT

INSERT

UPDATE

DELETE

MERGE

分别用于从数据库中检索数据、在数据库的表中输入新行、更改现有行以及删除不需要的行。

其通称为数据操纵语言（DML）。

数据操纵语言（DML）

CREATE

ALTER

DROP

RENAME

TRUNCATE

COMMENT

用于在表中设置、更改和删除数据结构。

其通称为数据定义语言

（DDL）。

数据定义语言（DDL）

GRANT

REVOKE

用于授予或撤消对OracleDB及其中的结构的访问权限。

数据控制语言（DCL）

COMMIT

ROLLBACK

SAVEPOINT

用于管理由DML语句所做的更改。

可以将对数据的更改组合到逻辑事务处理中。

事务处理控制

指定表中的数据类型

数据类型的作用在于指明存储数值时需要占据的内存空间大小和进行运算的依据。

创建表时，必须指定每个表列的数据类型。

创建过程或函数时，必须指定每个参数的数据类型。

这些数据类型用于定义每个列中可包含的值的范围，或者每个参数可采用的值的范围。

Oracle的数据类型分为标量（Scalar）类型、复合（Composite）类型、引用（Reference）类型和LOB（LargeObject）类型4种类型。

ØCHAR：

长度为size个字节或字符的固定长度字符数据。

最大大小为2,000个字节或字

符，默认大小及最小大小均为1个字节。

vBYTE指示列采用字节长度语义（即列的长度以字节为单位）。

vCHAR指示列采用字符语义（即将字符串视为字符序列）。

ØVARCHAR2：

最大长度为size个字节或字符的可变长度字符串。

最大大小为4,000个字

节。

必须指定VARCHAR2的大小。

ØDATE：

有效日期范围为公元前4712年1月1日至公元9999年12月31日。

它还可以存储时间（小时、分钟和秒）。

ØTIMESTAMP（fractional_seconds_precision）：

指定日期中的年、月和日的值，以及时间中的小时、分钟和秒的值。

其中fractional_seconds_precision是秒小数部分的位

数。

可接受的值为0到9之间的数字。

默认值为6。

ØNUMBER：

精度为p且小数位数为s的数字。

精度可介于1与38之间。

小数位数可介于84与127之间。

ØBINARY_FLOAT：

32位浮点数。

这种数据类型需要5个字节（包括长度字节）。

ØBINARY_DOUBLE：

64位浮点数。

这种数据类型需要9个字节。

ØFLOAT（p）：

美国国家标准协会（ANSI）数据类型。

FLOAT数据类型是二进制精度为p的

浮点数。

这种数据类型的默认精度为二进制数126或十进制数38。

ØINTEGER：

相当于NUMBER（p,0）

ØNCHAR（length）：

仅限Unicode数据类型。

创建包含NCHAR列的表时，请以字符为单位定义列长度。

创建数据库时，请定义国家字符集。

列的最大长度由国家字符集定义来确定。

NCHAR数据类型的宽度规范指的是字符数。

允许的最大列大小为2,000个字节。

如果插入值小于列长度，为了完全达到列长度，OracleDB会使用空格填充该值。

不能

将CHAR值插入到NCHAR列，也不能将NCHAR值插入到CHAR列。

ØNVARCHAR2（size[BYTE|CHAR]）：

仅限Unicode数据类型。

它与NCHAR相似，不同之

处在于，它的最大长度为4,000字节且不能使用空格填充。

ØLONG：

可变长度的字符数据，最大为2GB（或231–1字节）。

LONG数据类型已弃用；

请改用大对象（LOB）数据类型。

ØLONGRAW：

可变长度的原始二进制数据，最大为2GB

ØRAW（size）：

长度为size个字节的原始二进制数据。

最大大小为2,000个字节。

必须

指定RAW值的大小。

ØROWID：

Base-64字符串，表示数据库中某一行的唯一地址。

这种数据类型主要用于

ROWID假列返回的值。

ØUROWID：

Base-64字符串，表示索引表中某一行的逻辑地址。

可选大小为UROWID类型的列的大小。

最大大小及默认大小均为4,000个字节。

列中的UROWID数据类型具有一个字节的类型代码，后面跟随匹配类型的行ID。

三种类型分别是物理行ID、逻辑行ID和外部行ID（通过异构服务网关和数据库链接来选择行ID列时会用到该ID）。

ØBLOB：

二进制大对象

ØCLOB：

包含单字节字符或多字节字符的字符大对象。

支持固定宽度字符集和可变宽度字符集，这两种字符集都使用CHAR数据库字符集。

对于单字节字符集，以字符语义定义的列基本上与以字节语义定义的列相同。

在定义可变宽度的多字节字符串时，字符语义是很有用的，它有助于在定义数据存储的实际长度需求时降低复杂性。

例如，在Unicode数据库（UTF8）中，需要定义一个VARCHAR2列，该列最多可以同时存储5个中文字符和5个英文字符。

使用字节语义，该列需要（5x3个字节）+（1x5个字节）=20

个字节；而使用字符语义，该列需要10个字符。

ØNCLOB：

包含Unicode字符的字符大对象。

支持固定宽度字符集和可变宽度字符集，这两种字符集都使用NCHAR数据库字符集。

该数据类型存储国家字符集数据。

注：

所有LOB数据类型（BLOB、CLOB和NCLOB）的大小范围都为8TB–128TB。

ØBFILE：

包含指向存储在数据库外部的大二进制文件的定位器。

通过这种数据类型可以字节流I/O方式访问驻留在数据库服务器上的外部LOB。

最大大小为4GB。

建表

本课程中我们新建一方案hrlx，权限为角色DBA，登录密码为2015lx。

在hrlx方案的表记录中，每位雇员都有各自的标识号、电子邮件地址、职务标识代码、薪金和经理。

某些雇员除了薪金还领取佣金。

公司还跟踪组织内职务信息。

每个职务都有对应的标识代码、职位、最低和最高薪金范围。

某些雇员在公司中已工作很长的时间，因此在公司内担任过不同的职位。

当某一雇员辞职时，就会记录该雇员的工作期限、职务标识号和部门。

示例公司的经营地比较分散，所以需要跟踪公司仓库和公司部门的具体位置。

每个雇员都分配到一个部门，每个部门不是用唯一部门编号来标识，就是用一个简称来标识。

每个部门都与一个位置相关联，每个位置都有一个全称地址，其中包括街道名、邮政编码、城市、州/省，以及国家/地区代码。

公司会记录部门和仓库所在地的详细信息，如国家/地区名称、货币符号、货币名称以及国家/地区所在的地理区域。

针对以上要求在该方案中创建如下二维表：

EMPLOYEES（存储雇员的详细资料）表结构如下图所示

EMPLOYEES（存储雇员的详细资料）表各字段注释如下：

EMPLOYEES：

employeestableReferenceswithdepartments，jobs，job_historytables。

Containsaselfreference。

employee_id：

Primarykeyofemployeestable。

first_name：

Firstnameoftheemployee.Anotnullcolumn。

last_name：

Lastnameoftheemployee.Anotnullcolumn。

email：

Emailidoftheemployee。

phone_number：

Phonenumberoftheemployee;includescountrycodeandareacode。

hire_date：

Datewhentheemployeestartedonthisjob.Anotnullcolumn。

job_id：

Currentjoboftheemployee;foreignkeytojob_idcolumnofthejobstable.

Anotnullcolumn。

salary：

Monthlysalaryoftheemployee.Mustbegreaterthanzero（enforcedbyconstraintemp_salary_min）。

commission_pct：

Commissionpercentageoftheemployee;Onlyemployeesinsalesdepartmentelgibleforcommissionpercentage。

manager_id：

Manageridoftheemployee;hassamedomainasmanager_idindepartmentstable.Foreignkeytoemployee_idcolumnofemployeestable.（usefulforreflexivejoinsandCONNECTBYquery）。

department_id：

Departmentidwhereemployeeworks;foreignkeytodepartment_idcolumnofthedepartmentstable。

EMPLOYEES表中部分记录如下：

JOBS（各级别职位薪金的详细资料）表结构如下图所示

JOBS：

jobstablewithjobtitlesandsalaryranges，Referenceswithemployeesandjob_historytablejob_id：

Primarykeyofjobstablejob_title：

Anotnullcolumnthatshowsjobtitle,e.g.AD_VP,FI_ACCOUNTANTmin_salary：

Minimumsalaryforajobtitlemax_salary：

Maximumsalaryforajobtitle

JOBS表中部分记录如下：

DEPARTMENTS（部门详细资料）表结构如下图所示：

DEPARTMENTS（部门详细资料）表各字段注释：

DEPARTMENTS：

DepartmentstablethatshowsdetailsofdepartmentswhereemployeesWork，referenceswithlocations,employees,andjob_historytablesdepartment_id：

Primarykeycolumnofdepartmentstabledepartment_name：

Anotnullcolumnthatshowsnameofadepartment.

Administration,Marketing,Purchasing,HumanResources,Shipping,IT,Executive,PublicRelations,Sales,Finance,andAccounting.';manager_id：

Manager_idofadepartment.Foreignkeytoemployee_idcolumnofemployeestable.Themanager_idcolumnoftheemployeetablereferencesthiscolumnlocation_id：

Locationidwhereadepartmentislocated.Foreignkeytolocation_idcolumnoflocationstable

DEPARTMENTS（部门详细资料）表中部分记录如下：

LOCATIONS（公司详细地址）表结构如下图所示：

LOCATIONS（公司详细地址）表各字段注释：

LOCATIONS：

Locationstablethatcontainsspecificaddressofaspecificoffice,warehouse,and/orproductionsiteofacompany.Doesnotstoreaddresses

/locationsofcustomers.referenceswiththedepartmentsandcountriestables.location_id：

Primarykeyoflocationstablestreet_address：

Streetaddressofanoffice,warehouse,orproductionsiteofacompany.Containsbuildingnumberandstreetnamepostal_code：

Postalcodeofthelocationofanoffice,warehouse,orproductionsiteofacompany.

city：

Anotnullcolumnthatshowscitywhereanoffice,warehouse,orproductionsiteofacompanyislocated.

state_province：

StateorProvincewhereanoffice,warehouse,orproductionsiteofacompanyislocatedcountry_id：

Countrywhereanoffice,warehouse,orproductionsiteofacompanyislocated.Foreignkeytocountry_idcolumnofthecountriestable.

LOCATIONS（公司详细地址）表中部分记录如下：

数据完整性

数据完整性是指数据的精确性和可靠性。

它是为防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

数据完整性主要分为4类：

域完整性、实体完整性、引用完整性和用户定义完整性。

1）域完整性

域完整性是指数据库表中的列必须满足某种特定的数据类型或约束，其中约束又包括取值范围精度等规定。

表中的CHECK、FOREIGNKEY约束和DEFAULT、NOTNULL定义都属于域完整性的范畴。

2）实体完整性

实体完整性规定表的每一行在表中是唯一的。

实体表中定义的UNIQUE、PRIMARYKEY和

IDENTITY约束就是实体完整性的体现。

3）引用完整性

引用完整性是指两个表的主关键字和外关键字的数据应对应一致。

它确保了有主关键字的表中对应其他表的外关键字的行存在，即保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散。

引用完整性作用表现在如下几个方面：

Ø禁止在从表中插入包含主表中不存在的关键字的数据行；

Ø禁止会导致从表中的相应值孤立的主表中的外关键字值改变；

Ø禁止删除在从表中有对应记录的主表记录。

4）用户定义完整性

用户定义完整性指的是由用户指定的一组规则，它不属于实体完整性、域完整性或引用

完整性。

ØNOTNULL：

默认情况下，表中的所有列均允许使用空值。

单词null表示没有值。

NOTNULL约束条件可以确保某列不包含空值，它必须存在具体的值。

例如，可以通过定义NOTNULL

约束条件来要求在EMPLOYEES表的每一行的LAST_NAME列中输入值。

如果没有NOT

NULL约束条件为列创建非空约束，则该列默认为允许空值。

非空约束可以通过OEM或

PL/SQL等创建。

Ø默认约束是指表中添加新行时给表中某一列指定的默认值。

使用默认约束一是可以避免不允许为空值的数据错误，二是可以加快用户的输入速度。

默认约束可以通过OEM或PL/SQL创建。

如果创建了称为“默认值”的对象。

当绑定到列或用户定义数据类型时，如果插入时没有明确提供值，默认值便指定一个值，并将其插入到对象所绑定的列中。

默认约束是指表中添加新行时给表中某一列指定的默认值。

使用默认约束一是可以避免不允许为空值的数据错误，二是可以加快用户的输入速度。

默认约束可以通过

OEM或PL/SQL创建。

ØUNIQUE：

UNIQUE关键字完整性约束条件要求一列或一组列（键）中的每个值必须是唯一的，即在指定的列或一组列中，表的任意两行无重复值。

UNIQUE关键字约束条件的定义中包括的列（或一组列）被称为唯一关键字。

如果UNIQUE约束条件由多个列组成，则该组列被称为组合唯一关键