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

1、情境五 表的创建与管理校本教材情境五 表的创建与管理 【学习目标】 定义方案对象和数据类型 使用 OEM 创建和修改、查看和删除数据表 使用、创建、修改、查看和删除数据表。 使用 PL/SQL 创建、修改、查看和删除数据表。 使用 SQL Plus 创建、修改、查看和删除数据表。 使用 SQL Plus 添加、删除和修改数据表中的记录 数据完整性约束操作 v 非空 v 默认 v 唯一 v 检查 v 主键 v 外键 管理序列和同义词 临时表 【学习指导】 Oracle 中的方案 “方案”是指由特定用户拥有的数据库对象的集合。对于生产数据库而言，该用户通常代表的不是一个人，而是一个应用程序。方案的

2、名称与拥有该方案的用户的名称相同。方案对象是指直接引用数据库数据的逻辑结构。方案对象包含表、视图和索引等多种结构。 使用SQL 或Enterprise Manager 可创建并处理方案对象。使用Oracle Enterprise Manager时，系统会生成基础SQL 语句。 注：方案不一定需要直接与单个表空间相关。你可以定义一些配置，以便一个方案中的对象可以保存在不同的表空间中，一个表空间也可以保存不同方案中的对象。 Oracle 11g数据库中并不是所有的数据库对象都是方案对象，方案对象有表、索引、触发器、数据库链接、PL/SQL包、序列、同义词、视图、存储过程、存储函数等，非方案对象有表

3、空间、用户、角色、概要文件等。 在Oracle 11g数据库中，每个用户都拥有自己的方案，创建了一个用户，就创建了一个同名的方案，方案与数据库用户是对应的。 用户在数据库中创建了一个方案对象后，这个方案对象默认地属于这个用户的方案。 创建数据库时，系统会创建多个方案，其中包括以下两个重要的方案： SYS 方案：包含数据字典 SYSTEM 方案：包含存储管理信息的其它表和视图，在完整安装Oracle DB 期间，会自动安装示例方案。 示例方案用于为Oracle 文档和课件中的示例提供一个公用平台。这些示例方案是一组互相有联系的方案，旨在提供不同复杂程度的示例，其中包括： BI：“业务智能”方案包

4、含的一组对象用于展示业务智能功能。 HR：“人力资源 ” 方案是一个用于介绍基本主题的简单方案。此方案经过扩展后可支持Oracle Internet Directory 演示。 OE：“订单录入” 方案是一个中等复杂程度的方案。OE 方案中提供了多种数据类型。OC（Online Catalog，联机目录）子方案是内置于OE 方案中的对象关系数据库对象的集合。 IX：“信息交换”方案包含的一组对象用于展示通过企业对企业(B2B) 应用程序进行发运。 PM：“产品媒体”方案专用于多媒体数据类型。 SH：“销售历史记录”方案用于演示大量数据。这个方案经过扩展可支持高级分析处理。 以下为方案所包含的相

5、关对象 关系数据库与表 关系数据库使用关系或二维表存储信息。 例如，你可能需要存储公司中所有雇员的信息。在关系数据库中，可以创建多个表来存储雇员的各种信息，如雇员表、部门表和薪金表。 关系数据库可以包含一个或多个表。表是 RDBMS 的基本存储结构。表包含现实生活中某些人或事物（如雇员、发票或客户）的所有必需数据。 上图显示了 EMPLOYEES（雇员表）的内容。其中给出的圆形编号分别表示以下内容： 1. 单个行（又称元组），代表一位特定雇员的所有必需数据。表中的每一行均应由主键进行标识，以避免出现重复的行。行的顺序无关紧要，可以在检索数据时指定行的顺序。 2. 包含雇员编号的列或属性。在 E

6、MPLOYEES 表中，雇员编号用于唯一地标识一个雇员。在本示例中，雇员编号列被指定为主键。主键必须包含值，而且此值必须是唯一的。 3. 不含键值的列。列代表表中的一类数据，本示例中的数据为所有雇员的薪金。存储数据时列的顺序无关紧要，可以在检索数据时指定列的顺序。 4. 包含部门编号的列，此列也是外键。外键是定义表之间如何关联的列。外键用于引用同一个表或另一表中的主键或唯一键。在本示例中，DEPARTMENT_ID 用于唯一地标识 DEPARTMENTS 表中的部门。 5. 行和列的交叉处是字段。字段中只能有一个值。 6. 字段中也可以没有值。此时称为空值。在 EMPLOYEES 表中，只有是

7、销售代表的雇员在COMMISSION_PCT（佣金）字段中才有值。 使用 SQL 查询数据库在关系数据库中，你不必指定表的访问路径，也无需知道数据的实际排列方式。要访问该数据库，请执行结构化查询语言 (SQL) 语句，该语言是美国国家标准协会 (ANSI)制定的一种标准语言，用于操作关系数据库。SQL 是所有程序和用户访问 Oracle DB 中的数据时使用的语句的集合。借助应用程序和 Oracle 工具，用户通常不需要直接使用 SQL访问数据库，但这些应用程序在执行用户请求时必须使用 SQL。 SQL 为多种任务提供了语句，其中包括： 查询数据 在表中插入、更新和删除行 创建、替换、更改和删

8、除对象 控制对数据库及其对象的访问 保证数据库的一致性和完整性 SQL 将所有任务统一为一种语言，可以在一个逻辑层上处理数据。Oracle 支持的 SQL 语句遵循行业标准。Oracle Corporation 积极地与 SQL 标准委员会的重要职员保持联系，以确保始终遵从不断发展的标准。行业公认的委员会有 ANSI 和国际标准组织 (ISO)。ANSI 和 ISO 都已接受 SQL 作为关系数据库的标准语言。Oracle中SQL语言共分为4类： 语句 说明 SELECT INSERT UPDATE DELETE MERGE 分别用于从数据库中检索数据、在数据库的表中输入新行、更改现有行以及删

9、除不需要的行。其通称为数据操纵语言 (DML)。 数据操纵语言 (DML) CREATE ALTER DROP RENAME TRUNCATE COMMENT 用于在表中设置、更改和删除数据结构。其通称为数据定义语言 (DDL)。 数据定义语言 (DDL) GRANT REVOKE 用于授予或撤消对 Oracle DB 及其中的结构的访问权限。 数据控制语言 (DCL) COMMIT ROLLBACK SAVEPOINT 用于管理由 DML 语句所做的更改。可以将对数据的更改组合到逻辑事务处理中。 事务处理控制 指定表中的数据类型 数据类型的作用在于指明存储数值时需要占据的内存空间大小和进行运

10、算的依据。 创建表时，必须指定每个表列的数据类型。创建过程或函数时，必须指定每个参数的数据类型。这些数据类型用于定义每个列中可包含的值的范围，或者每个参数可采用的值的范围。 Oracle的数据类型分为标量（Scalar）类型、复合（Composite）类型、引用（Reference）类型和LOB（Large Object）类型4种类型 。 CHAR：长度为size 个字节或字符的固定长度字符数据。最大大小为2,000 个字节或字符，默认大小及最小大小均为1 个字节。 v BYTE 指示列采用字节长度语义（即列的长度以字节为单位）。 v CHAR 指示列采用字符语义（即将字符串视为字符序列）。

11、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

12、之间。小数位数可介于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 列的表时，请以字符为单位定义列长度。创建数据库时，请定义国家字符集。列的最大长度由国家字符集定义来确定。N

13、CHAR 数据类型的宽度规范指的是字符数。允许的最大列大小为 2,000 个字节。如果插入值小于列长度，为了完全达到列长度，Oracle DB 会使用空格填充该值。不能将CHAR 值插入到NCHAR 列，也不能将NCHAR 值插入到CHAR 列。 NVARCHAR2(size BYTE|CHAR)：仅限 Unicode 数据类型。它与 NCHAR 相似，不同之处在于，它的最大长度为4,000 字节且不能使用空格填充。 LONG：可变长度的字符数据，最大为2 GB（或231 1 字节）。LONG 数据类型已弃用；请改用大对象(LOB) 数据类型。 LONG RAW：可变长度的原始二进制数据，最大

14、为2 GB RAW(size)：长度为size 个字节的原始二进制数据。最大大小为2,000 个字节。必须指定RAW 值的大小。 ROWID：Base-64 字符串，表示数据库中某一行的唯一地址。这种数据类型主要用于ROWID 假列返回的值。 UROWID：Base-64 字符串，表示索引表中某一行的逻辑地址。可选大小为UROWID类型的列的大小。最大大小及默认大小均为4,000 个字节。列中的UROWID 数据类型具有一个字节的类型代码，后面跟随匹配类型的行ID。三种类型分别是物理行ID、逻辑行ID 和外部行ID（通过异构服务网关和数据库链接来选择行ID 列时会用到该ID）。 BLOB：二进

15、制大对象 CLOB：包含单字节字符或多字节字符的字符大对象。支持固定宽度字符集和可变宽度字符集，这两种字符集都使用CHAR 数据库字符集。对于单字节字符集，以字符语义定义的列基本上与以字节语义定义的列相同。在定义可变宽度的多字节字符串时，字符语义是很有用的，它有助于在定义数据存储的实际长度需求时降低复杂性。例如，在Unicode 数据库(UTF8) 中，需要定义一个VARCHAR2 列，该列最多可以同时存储5 个中文字符和5 个英文字符。使用字节语义，该列需要（5 x 3 个字节）+（1 x 5 个字节）= 20 个字节；而使用字符语义，该列需要10 个字符。 NCLOB：包含Unicode

16、字符的字符大对象。支持固定宽度字符集和可变宽度字符集，这两种字符集都使用NCHAR 数据库字符集。该数据类型存储国家字符集数据。 注：所有LOB 数据类型（BLOB、CLOB 和NCLOB）的大小范围都为8 TB128 TB。 BFILE：包含指向存储在数据库外部的大二进制文件的定位器。通过这种数据类型可以字节流I/O 方式访问驻留在数据库服务器上的外部LOB。最大大小为4 GB。 建表 本课程中我们新建一方案hrlx，权限为角色DBA，登录密码为2015lx。 在hrlx方案的表记录中，每位雇员都有各自的标识号、电子邮件地址、职务标识代码、薪金和经理。某些雇员除了薪金还领取佣金。 公司还跟踪

17、组织内职务信息。每个职务都有对应的标识代码、职位、最低和最高薪金范围。某些雇员在公司中已工作很长的时间，因此在公司内担任过不同的职位。当某一雇员辞职时，就会记录该雇员的工作期限、职务标识号和部门。 示例公司的经营地比较分散，所以需要跟踪公司仓库和公司部门的具体位置。每个雇员都分配到一个部门，每个部门不是用唯一部门编号来标识，就是用一个简称来标识。每个部门都与一个位置相关联，每个位置都有一个全称地址，其中包括街道名、邮政编码、城市、州/省，以及国家/地区代码。 公司会记录部门和仓库所在地的详细信息，如国家/地区名称、货币符号、货币名称以及国家/地区所在的地理区域。 针对以上要求在该方案中创建如下

18、二维表： EMPLOYEES（存储雇员的详细资料）表结构如下图所示 EMPLOYEES（存储雇员的详细资料）表各字段注释如下： EMPLOYEES：employees table References with departments，jobs， job_history tables。 Contains a self reference。 employee_id：Primary key of employees table。 first_name：First name of the employee. A not null column。 last_name：Last name of the

19、employee. A not null column。 email：Email id of the employee。 phone_number：Phone number of the employee; includes country code and area code。 hire_date：Date when the employee started on this job. A not null column。 job_id：Current job of the employee; foreign key to job_id column of the jobs table. A

20、not null column。 salary：Monthly salary of the employee. Must be greater than zero (enforced by constraint emp_salary_min)。 commission_pct：Commission percentage of the employee; Only employees in sales department elgible for commission percentage。 manager_id：Manager id of the employee; has same domai

21、n as manager_id in departments table. Foreign key to employee_id column of employees table. (useful for reflexive joins and CONNECT BY query)。 department_id：Department id where employee works; foreign key to department_id column of the departments table。 EMPLOYEES表中部分记录如下： JOBS（各级别职位薪金的详细资料）表结构如下图所示

22、 JOBS：jobs table with job titles and salary ranges，References with employees and job_history table job_id：Primary key of jobs table job_title：A not null column that shows job title, e.g. AD_VP, FI_ACCOUNTANT min_salary：Minimum salary for a job title max_salary：Maximum salary for a job title JOBS表中部分

23、记录如下： DEPARTMENTS（部门详细资料）表结构如下图所示： DEPARTMENTS（部门详细资料）表各字段注释： DEPARTMENTS：Departments table that shows details of departments where employees Work， references with locations, employees, and job_history tables department_id：Primary key column of departments table department_name：A not null column tha

24、t shows name of a department. Administration,Marketing, Purchasing, Human Resources, Shipping, IT, Executive, Public Relations, Sales, Finance, and Accounting. ; manager_id：Manager_id of a department. Foreign key to employee_id column of employees table. The manager_id column of the employee table r

25、eferences this column location_id：Location id where a department is located. Foreign key to location_id column of locations table DEPARTMENTS（部门详细资料）表中部分记录如下： LOCATIONS（公司详细地址）表结构如下图所示： LOCATIONS（公司详细地址）表各字段注释： LOCATIONS：Locations table that contains specific address of a specific office,warehouse,

26、and/or production site of a company. Does not store addresses /locations of customers.references with the departments and countries tables. location_id：Primary key of locations table street_address：Street address of an office, warehouse, or production site of a company.Contains building number and s

27、treet name postal_code：Postal code of the location of an office, warehouse, or production site of a company. city：A not null column that shows city where an office, warehouse, or production site of a company is located. state_province：State or Province where an office, warehouse, or production site

28、of a company is located country_id：Country where an office, warehouse, or production site of a company is located. Foreign key to country_id column of the countries table. LOCATIONS（公司详细地址）表中部分记录如下： 数据完整性 数据完整性是指数据的精确性和可靠性。它是为防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。数据完整性主要分为4 类：域完整性、实体完整性、引用完

29、整性和用户定义完整性。 1） 域完整性 域完整性是指数据库表中的列必须满足某种特定的数据类型或约束，其中约束又包括取值范围精度等规定。表中的CHECK、FOREIGN KEY 约束和DEFAULT、NOT NULL定义都属于域完整性的范畴。 2） 实体完整性 实体完整性规定表的每一行在表中是唯一的。实体表中定义的UNIQUE、PRIMARY KEY 和IDENTITY 约束就是实体完整性的体现。 3） 引用完整性 引用完整性是指两个表的主关键字和外关键字的数据应对应一致。它确保了有主关键字的表中对应其他表的外关键字的行存在，即保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩

30、散。 引用完整性作用表现在如下几个方面： 禁止在从表中插入包含主表中不存在的关键字的数据行； 禁止会导致从表中的相应值孤立的主表中的外关键字值改变； 禁止删除在从表中有对应记录的主表记录。 4）用户定义完整性 用户定义完整性指的是由用户指定的一组规则，它不属于实体完整性、域完整性或引用完整性。 NOT NULL：默认情况下，表中的所有列均允许使用空值。单词null 表示没有值。NOT NULL 约束条件可以确保某列不包含空值，它必须存在具体的值。例如，可以通过定义NOT NULL 约束条件来要求在EMPLOYEES 表的每一行的LAST_NAME 列中输入值。如果没有NOT NULL 约束条件

31、为列创建非空约束，则该列默认为允许空值。非空约束可以通过OEM或PL/SQL等创建。 默认约束 是指表中添加新行时给表中某一列指定的默认值。使用默认约束一是可以避免不允许为空值的数据错误，二是可以加快用户的输入速度。默认约束可以通过OEM 或PL/SQL创建。如果创建了称为“默认值”的对象。当绑定到列或用户定义数据类型时，如果插入时没有明确提供值，默认值便指定一个值，并将其插入到对象所绑定的列中 。默认约束是指表中添加新行时给表中某一列指定的默认值。使用默认约束一是可以避免不允许为空值的数据错误，二是可以加快用户的输入速度。默认约束可以通过OEM或PL/SQL创建。 UNIQUE ：UNIQUE 关键字完整性约束条件要求一列或一组列（键）中的每个值必须是唯一的，即在指定的列或一组列中，表的任意两行无重复值。UNIQUE 关键字约束条件的定义中包括的列（或一组列）被称为唯一关键字。如果 UNIQUE 约束条件由多个列组成，则该组列被称为组合唯一关键