ORACLE数据库设计

目录

第1章Oracle数据库安装配置 (3)

1.1 配置监听程序 (3)

1.2 配置网络服务名 (4)

第2章管理用户访问 (4)

2.1表空间、用户及方案概述 (4)

2.2用户访问 (5)

2.3创建用户 (6)

2.4修改用户 (6)

2.5 锁定用户账户 (7)

2.6 解锁用户账户 (7)

2.7 默认角色 (7)

2.8 授予用户访问权 (7)

2.9撤消用户 (9)

2.10废除用户访问 (9)

2.11创建角色 (9)

2.12 新建的用户怎样才能成功创建一个表 (10)

第3章建立和管理表 (11)

3.1简介 (12)

3.2建表 (13)

3.3修改表 (14)

3.4截断和删除表 (14)

3.5显示表信息 (15)

第4章Oracle操作符 (16)

第5章基本查询 (19)

5.1简单查询语句 (19)

5.2排序数据 (21)

第6章复杂查询 (22)

6.1数据分组 (22)

6.2连接查询 (23)

6.3相等连接（包括SQL：1999标准内连接、自然连接） (24)

6.4不等连接 (24)

6.5自连接 (24)

6.6[内连接和]外连接 (24)

6.6.1内连接 (25)

6.6.2左外连接 (25)

6.6.3右外连接 (25)

6.6.4完全外连接 (25)

6.7子查询 (25)

6.7.1单行子查询（单列） (25)

6.7.2多行子查询（单列） (25)

6.7.3多列子查询 (26)

6.7.4其他子查询 (26)

6.8使用集合操作符 (27)

第7章常用SQL函数 (27)

第8章操纵数据 (41)

8.1插入数据 (41)

8.2更新数据 (43)

8.3删除数据 (44)

第9章使用事务 (45)

9.1概述 (46)

9.2事务分类 (46)

9.3回复修改 (47)

9.4回复部分事务 (48)

9.5事务和锁 (49)

第10章使用约束 (49)

10.1约束简介 (49)

10.2定义约束 (50)

10.3维护约束 (51)

10.5显示约束信息 (52)

第11章使用视图 (53)

11.1视图简介 (53)

11.2建立视图 (54)

11.3维护视图 (55)

11.4显示视图信息 (55)

第12章使用其它对象（索引序列同义词） (56)

12.1使用索引 (56)

12.1.1建立索引 (57)

12.1.2维护索引 (57)

12.1.3显示索引信息 (58)

11.2使用序列 (58)

11.2．1建立序列 (58)

11.2.2使用序列 (58)

11.2.3维护序列 (59)

11.2.4显示序列信息 (59)

11.3使用同义词 (59)

11.3.1建立同义词 (59)

11.3.2删除同义词 (59)

第13章PL/SQL语句 (60)

13.1 PL/SQL简介 (60)

13.2 PL/SQL块 (62)

13.2.1定义并使用变量 (63)

13.2.2异常 (64)

13.2.2游标 (67)

13.3过程函数包 (71)

第14章过程函数包及触发器 (71)

14.1过程 (71)

14.1.1建立过程 (71)

14.1.2显示过程代码 (72)

14.2函数 (72)

14.2.1建立函数 (72)

14.2.2删除函数 (73)

14.2.3显示函数代码 (73)

14.3包 (73)

14.3.1建立包规范 (73)

14.3.2建立包体 (74)

14.3.3删除包 (75)

14.3.4显示包代码 (75)

14.4触发器 (75)

14.4.1语句触发器 (75)

14.4.2行触发器 (76)

14.4.3使用触发器的注意事项 (77)

14.4.4编译触发器 (77)

14.4.5删除触发器 (77)

14.4.6显示触发器代码 (77)

第十五章使用EXP和IMP (77)

15.1使用EXP (78)

15.1.1导出表 (78)

15.1.2导出方案 (78)

15.1.3导出数据库 (78)

15.2使用IMP (79)

15.2.1导入表 (79)

15.2.2导入方案 (79)

15.2.3导入数据库 (79)

第1章Oracle数据库安装配置

当安装Oracle Database时，如果没有建立数据库，在安装完成之后可以使用DBCA工具建立数据库。数据库配置助手(Database Configuration Assistant)用于建立数据库、配置数据库选项、删除数据库和管理模板。

当建立了Oracle数据库之后，为了使得服务器端可以监听该Oracle数据库，必须配置监听程序。为了使得客户端可以访问该数据库，必须在客户端配置网络服务名。只有合理地配置了监听程序和网络服务名之后，客户应用才能访问该数据库。配置监听程序和网络服务名可以使用网络管理工具Net Manager完成。(源码网整理：https://www.wendangku.net/doc/2f12875678.html,)

1.1 配置监听程序

监听程序用于接收客户端的连接请求。当客户应用访问Oracle Server时，监听程序会接收并检查连接请求，以确定是否可以为该客户应用提供数据服务。在建立了Oracle数据库之后，为了使得客户应用可以访问Oracle数据库，必须在监听程序中追加该数据库。一个监听程序可以监听多个Oracle数据库，多个监听程序也可以监听同一个Oracle数据库。当

安装数据库产品时，会自动建立默认监听程序LISTENER。

配置监听程序的具体步骤如下：

●展开监听程序，并选中LISTENER节点，此时在NET MANAGER窗口右端会显示

默认监听位置，其中“协议”用于指定监听程序要使用的网络协议（默认为TCP/IP）；

“主机”用于指定服务器所在机器的主机名或IP地址；“端口”用于指定监听程序

要使用的TCP/IP端口号（默认1521）。

●在Net Manager窗口上端的下拉列表中选择数据库服务，此时会显示默认的数据库

配置，“全局数据库名”用于指定数据库的全局数据库名；“Oracle主目录”用于指

定Oracle数据库软件的安装路径；“SID”用于指数据库例程名。为了监听新建的

数据库（如DEMO），必须追加该数据库。

●单击“添加数据库”按钮，然后进行相应配置，在“全局数据库名”处输入DEMO

数据库的初始化参数SERVICE—HOME所对应的值，在SID处输入DEMO数据

库的例程名。配置了监听程序之后，保存网络配置信息。

●保存了监听程序配置之后，为了使得其网络配置生效，必须重新启动监听程序。（通

过服务器管理器重新启动监听程序）

1.2 配置网络服务名

●选中“服务命名”，然后单击+按钮，此时会显示“Net 服务名”界面，建议使用数

据库名作网络服务名。

●选取与监听程序一致的网络协议“TCP/IP”。

●指定数据库所在主机名及其监听端口号。

●指定监听程序所配置的全局数据库名或者SID。

●测试网络服务名配置是否成功，如果成功则表示网络服务名配置正确。

●完成网络服务名配置之后，保存网络配置信息。

第2章管理用户访问

本章主要内容：

●表空间、用户及方案概述

●用户访问

●创建用户

●修改用户

●授予用户访问权

●撤消用户

●废除用户访问

●创建角色

2.1表空间、用户及方案概述

表空间是数据库的逻辑组成部分。从物理上说，数据库数据存放在数据文件中；从逻辑上说，数据库数据存放在表空间(tablespace)中，并且表空间是由一个或多个数据文件组成的。

一个表空间是由一个或多个数据文件组成的。

用户（也称为帐户）是定义在数据库中的一个名称，它是Oracle数据库的基本访问控制机制。当连接到Oracle数据库时，默认情况下必须要提供用户名和口令。只有在输入了正确的用户名和口令之后，才能够连接到数据库，并执行各种管理操作和数据访问操作。

方案(Schema)是用户所拥有数据库对象的集合。在Oracle数据库中对象是以用户来组织的，用户与方案是一一对应的关系，并且二者名称相同。例SCOTT用户所拥有的所有对象都属于SCOTT方案，而SYSTEM用户所拥有的所有对象都属于SYSTEM 方案。当访问数据库对象时，有一些注意事项：

●在同一个方案中不能存在同名对象，但不同方案可以具有同名对象。

●用户可以直接访问其方案对象，但如果要访问其他方案对象，则必须具有对象

权限。如用户SCOTT可以直接查询其方案表EMP，但如果用户SMITH要检

索SCOTT方案的表EMP，则必须在EMP表上具有SELECT对象权限。

●当用户访问其他方案对象时，必须加方案名为前缀。例，如用户SMITH要访

问SCOTT方案的EMP表，则必须使用SCOTT.EMP。

2.2用户访问

在多用户环境里，一个数据库可能有多个用户同时在访问。当有不同的用户同时访问数据库时，保护数据库安全，防范非授权访问非常重要。因此，必须在数据库里创建用户，并为用户指定用户名和密码，这样可以保证只有经过授权的，即有正确用户名和密码的用户才能访问数据库。数据库管理员（DBA）是最高级别的用户，他可以创建其他用户。在创建了用户后，DBA需要按用户的需求为用户分配权限。权限指用户执行特定语句的许可，这意味并非所有用户都被允许修改重要数据。例如，某个用户可能只需要有连接数据库和查询某些表的记录的权限。类似地，另一个用户可能要求有创建和修改表的权限。

DBA有访问数据库的一切权限，并有权为其他用户分配权限。下表列出了部分DBA 权限：

为了维护存储在数据库中数据的安全，Oracle提供了以下数据库安全措施：

●管理和控制数据库访问

●用Oracle数据字典验证权限

●为指定用户提供对数据库特定对象（表、视图和序列等）的访问

●为数据库对象提供同义词

可被数据库操作采用的数据库的安全策略包括：

●系统安全：系统安全涉及系统级的访问，如允许用户通过指出用户名和密码连接Oracle，

为用户分配磁盘空间，限定用户所能执行的操作。用户能执行的操作包括：查询数据库对象的内容、创建数据库对象和更改数据库对象。

●数据安全：数据安全涉及对数据库对象的访问和使用，以及用户在数据库对象上所拥有

的权限的程度。

2.3创建用户

CREATE USER语句用于创建新用户。在创建新用户时，必须为新用户指定用户名和密码。为了使新用户能登录服务器和访问数据库，DBA必须显式地为用户分配权限。

只有拥有CREATE USER权限的用户才能创建新用户。例如，DBA创建了新用户Susan，但Susan无权创建其他新用户。只有当Susan有了CREATE USER这个权限后，她才能创建别的新用户。

创建用户的语法示例：

CREATE USER user IDENTIFIED BY password

DEFAULT TABLESPACE data01

TEMPORARY TABLESPACE temp

QUOTA 3M ON data01

PASSWORD EXPIRE;

其中IDENTIFIED BY用于指定用户口令；DEFAULT TABLESPACE用于指定用户的默认表空间，当建立表或者索引时，如果不指定TABLESPACE子句，那么Oracle 会自动在默认表空间上为这些对象分配空间；TEMPORARY TABLESPACE用于指定用户的临时表空间，当用户执行排序操作时，或临时数据超过PGA工作区，则会在该表空间上建立临时段；QUOTA用于指定表空间配额，即用户对象在表空间上可占用的最大空间；PASSWORD EXPIRE用于指定终止口令，最终强制用户在登录时改变口令。

当建立了新用户之后，需要注意以下问题：

●初始创建的数据库用户没有任何权限，不能执行任何数据库操作。

●如果在建立用户时不指定DEFAULT TABLESPACE子句，那么Oracle会将数

据库默认表空间作为用户的默认表空间。在Oracle Database 10g之前，如果不

指定DEFAULT TABLESPACE子句，那么Oracle会将SYSTEM表空间作为用

户的默认表空间。

●如果在建立用户时不指定TEMPORARY TABLESPACE子句，那么Oracle会

将数据库默认临时表空间作为用户的临时表空间。

●如果在建立用户时没有为特定表空间指定QUOTA子句，那么用户在特定表空

间上的配额为0，这样用户将不能在相应表空间上建立数据对象。

2.4修改用户

修改用户信息是使用ALTER USER命令完成的。一般情况下，该命令是由DBA 来执行的，如果以其他用户身份修改用户信息，必须要具有ALTER USER系统权限。

1．修改口令

创建用户时为每个用户指定一个初始密码。之后可修改密码，修改密码的方法有两种：

●管理员修改：ALTER USER user IDENTIFIED BY password;(password为用户的

新密码)

●用户自己修改：用户登录后输入命令：PASSWORD；

修改表空间配额

表空间配额用于限制用户对象在表空间上可占用的最大空间。如果用户对象己经占满了表空间配额所允许的最大空间，那么该用户将不能在该表空间上分配新的空间。此时如果执行了涉及到空间分配的SQL操作（如INSERT、UPDATE、CREATE TABLE 等），则会显示错误，修改表空间语法：

ALTER USER user QUOTA 10M ON data01;

2.5 锁定用户账户

为了禁止特定数据库用户用户访问数据库，DBA可以锁定用户账户，

ALTER USER user ACCOUNT LOCK;

2.6 解锁用户账户

为了使得数据库用户可以访问数据库，DBA可以解锁用户账户。

ALTER USER user ACCOUNT UNLOCK；

2.7 默认角色

当将多个角色授予数据库用户之后，通过使用ALTER USER 命令可以设置用户的默认角色。（当为用户指定了默认角色后，以该用户身份登录时会自动激活其默认角色，并不激活非默认角色）

ALTER USER user DEFAULT ROLE select_role;

[补充：]

默认角色和非默认角色的区别是什么?

这个理解有多种，第一种：默认角色可以是我们创建数据库就可以见的connect/dba等，非默认角色需要我们自己创建；第二种：一个用户可以有多个角色，默认的角色登录即生效，非默认的需要激活才能使用。

默认角色和权限集是Oracle安装过程中预先定义的。每个版本的默认角色都有所变化。

CREATE USER SMIS IDENTIFIED BY SMIS；

grant dba,connect to SMIS;

create role r_px;

grant r_px to SMIS;

select * from dba_role_privs where grantee='SMIS';

alter user SMIS default role all except r_px;

select * from dba_role_privs where grantee='SMIS';

2.8 授予用户访问权

用户创建后，数据库管理员需要为他分配权限。权限关系到数据库的安全，它决定了用户在数据库上所能执行的操作。GRANT语句用于为用户分配权限。语法如下：GRANT privilege TO user;

[注释：授予所有系统权限GRANT ALL PRIVILEGES to test_2;]

[注释：授予所有对象权限 GRANT ALL [PRIVILEGES] ON DEMO.DEPT TO TEST_1;]

可分配给用户的权限有：

●系统权限：允许用户访问数据库的权限称为系统权限。

●对象权限：允许用户在数据库对象上执行查询、更新、删除或添加数据等操作

的权限，此称为对象权限。

常用的系统权限有：

任何方案中执行相应操作。例如，如果用户具有SELECT ANY TABLE系统权限，那么用户可以查询任何方案的表（除数据字典基表和数据字典视图DBA_XXX----DBA和特权用户专访）。

如：GRANT CREATE SESSION,CREATE SEQUENCE,CREA TE VIEW TO john;

上述命令执行后，用户john将拥有创建会话、视图和序列的系统权限。

*Oracle提供了100多种系统权限（ALTER TABLE，ALTER VIEW，ALTER PROCEDURE，DROP TABLE，DROP VIEW，DROP PROCEDURE等）。

一般情况下，授予系统权限是由DBA来完成的；如果要以其他用户身份授予系统权限，则要求该用户必须具有GRANT ANY PRIVILEGE系统权限，或者具有相应系统权限及其转授系统权限选项（WITH ADMIN OPTION），授予系统权限是使用GRANT 命令来完成的，其语法如下：

GRANT system_priv[,system_priv,…]

TO {user | role | public},[,{user | role | public}]...

[WITH ADMIN OPTION];

注：user也可以是用户组PUBLIC；UNLIMITED TABLESPACE权限不能被授予角色

如果用户在某个同义词上拥有权限，而这个同义词又需要引用其他基表，那么用户在同义词上拥有的权限会转换为在基表上的权限。

缺省地，用户对他所创建的对象拥有完全的权限。如用户在用户模式上创建了一个表，那么缺省地，他在用户模式上拥有所创建的这个表的所有权限。

授予对象权限一般情况下是由对象所有者或者DBA用户来完成的；如果以其他用户身份授予对象权限，则要求用户必须具有该对象权限及转授对象权限选项（WITH GRANT OPTION），语法如下：

GRANT { object_priv [ (columns) ][ ,object_priv[(columns) ] ]…

| ALL [ PRIVILEGES ]} ON [schema.]object

TO {user | role | PUBLIC} [, {user | role | PUBLIC}]…

[ WITH GRANT OPTION ];

例：grant update on scott..emp to blake;

Grant update(sal) on emp to blake;（只能在insert、update和references上授予列权限

2.9撤消用户

DROP USER语句用于删除用户。

语法：DROP USER username;

撤消用户时，用户创建的对象并没有被撤消。为了撤消创建对象的用户

语法：DROP USER username CASCADE；

（不指定CASCADE不能撤消创建对象的用户）

2.10废除用户访问

一般情况下，收回系统权限是由DBA来完成的；如果以其他用户身份收回系统权限，则要求该用户必须具有相应系统权限及其转授系统权限选项(WITH ADMIN OPTION)。收回系统权限是使用REVOKE命令来完成的。语法如下：

REVOKE system_priv[,system_priv]…

FROM {user | role | PUBLIC }[,{user | role | PUBLIC }]…

用户的权限可使用REVOKE语句废除。一旦某个用户的权限被撤消，由他创建的所有用户和从他那获得权限的所有用户也都被撤消。如，Jim把CREATE TABLE权限授给John。当Jim的权限被撤消时，同时自动撤消John的权限。

如：GRANT CREATE TABLE TO John;

REVOKE CREATE TABLE FROM John;（不能撤消自己的权限）

一般情况下，收回对象权限是由对象所有者或者DBA用户来完成的；如果以其他用户身份收回对象权限，则要求用户必须具有该对象权限及转授对象权限选项（WITH GRANT OPTION）。语法如下：

REVOKE { object_priv [ , object_priv ]… | ALL [ PRIVILEGES ] }

ON [ schema. ] object

FROM { user | role | PUBLIC }[,{ user | role | PUBLIC }]…

[CASCADE CONSTRAINTS];

CASCADE CONSTRAINTS 用于删除任何与该对象相关的约束和对象，例如索引、触发器、权限、完整性约束等。

2.11创建角色

角色是相关权限的命名集合，使用角色的主要目的是为了简化权限管理。角色可以是权限的组合，也可以是角色的组合。角色包括预定义角色和自定义角色两类。

常用的预定义角色有：（oracle 10g）

●CONNECT Role：分配给临时用户的角色。通常，为只需要查询材料而无须创

建表的用户分配这个角色。

●RESOURCE Role：这个角色分配给常规用户

●DBA Role：这个角色拥有一切系统权限，包括不加限制的表空间配额以及

WITH ADMIN OPTION选项。默认的DBA用户为SYS和SYSTEM，他们可

以将任何系统权限授予其他用户。读者需要注意，DBA角色不具备SYSDBA

和SYSOPER特权。

创建角色的语法：

CREATE ROLE role [ NOT IDENTIFIED ]; 不验证—用于公用角色或用户默认角色

或

CREATE ROLE role

IDENTIFIED BY password; 数据库验证

为角色分配密码或修改角色密码：

ALTER ROLE role

IDENTIFIED BY password;（password为要设置的新密码）

如：

CREATE ROLE Acadre;

ALTER ROLE Acadre IDENTIFIED BY success;

为角色授予权限

GRANT privilege TO role;

例：只授予CONNECT权限给角色Acadre，这样他们不可能操作数据库。

GRANT connect TO Acadre;

将角色指派给用户

一个角色可指派给多个用户。类似的，一个用户也可具有多个角色。当把角色指派

给用户时，赋予该角色的权限也自动分配给用户。为用户分配角色的语法：

GRANT role TO user;

也可以使用ALTER USER语句授权用户。

ALTER USER John Default ROLE Student;

授予用户John以缺省角色student。

2.12 新建的用户怎样才能成功创建一个表

当一个用户刚被创建时是不具备任何权限的，因此要在该用户模式下创建表，需授予CREATE SESSION、CREATE TABLE、以及UNLIMITED TABLESPACE（或分配配额）权限，因为：当用户要连接到数据库时必须拥有CREATE SESSION权限

当用户要创建表时必须拥有CREATE TABLE权限，同时用户还需要在表空间中拥有配额或者被授予UNLIMITED TABLESPACE。现在我们来做一个测试：

1）、创建用户TEST，密码为passwd_1:

SQL> CREATE USER test

IDENTIFIED BY passwd_I1

用户已创建

2）当用TEST连接数据库时：

SQL> conn test/passwd_1

ERROR:

ORA-01045: user TEST lacks CREATE SESSION privilege; logon denied

警告: 您不再连接到 ORACLE。

//因为缺少CREATE SESSION的权限，登陆失败。

3）利用SYS给TEST授予CREATE SESSION权限：

SQL> grant create session to test;

授权成功。

4）SQL> conn test/passwd_1

已连接。

5）在test的方案中创建表exam1:

SQL> create table exam1

(student_id int,

paper_id int);

create table exam1

ERROR 位于第 1 行:

ORA-01031: 权限不足

//因为未给TEST用户授予create table 权限，因此不能够创建表exam1.

6) 给TEST用户授予CRETE TABLE 权限

SQL> grant create table to test;

授权成功。

7）SQL> create table exam1

(student_id int,

paper_id int);

create table exam1

ERROR 位于第 1 行:

ORA-01950: 表空间'SYSTEM'中无权限

//因为在创建用户时没有指定表空间，因此默认的表空间是SYSTEM表空间，而TEST用户还需要在表空间SYSTEM中既没有拥有配额又没有被授予UNLIMITED TABLESPACE权限，因此对于这种情况有两种解决办法：

第一种方法：

SQL> alter user test

quota 15m on system;

用户已更改。

//在SYSTEM表空间中，给用户TEST分配15M的使用空间

SQL> create table exam1

(student_id int,

paper_id int);

表已创建

第二种方法：

SQL> grant unlimited tablespace to test

授权成功。

SQL> create table exam2

(student_id int,

paper_id int);

第3章建立和管理表

本章主要内容：

●表简介

●创建表

●修改表

●截断和删除表

●显示表的信息

3.1简介

表是Oracle数据库最基本的对象，它用于存储用户数据。

●设计表

当设计表时，需要考虑以下因素：

?当规划表和列时，应该使用有意义的名称。当定义表名和列名时，只能使用字符（A－Z，a-z）、数字（0－9）、_、$和#，名称必须以字符开始，并且长度

不能超过30个字符。

?当规划表名和列名时，要使用一致的缩写格式、单数或复数格式。

?为了给用户和其他人员提供有意义的帮助信息，应该使用COMMENT命令描述表、列的作用

?当设计表时，应该使用第一范式（1NF）、第二范式（2NF）和第三范式（3NF）规范化每张数据库表。

?当定义表列时，应该选择合适的数据类型和长度。

?当定义表列时，为了节省存储空间，应该将NULL列放在后面。

●常用数据类型

当建立表时，不仅需要指定表名、列名，而且要根据情况为列选择合适的数据类型

和长度。下面是常用数据类型。

?CHAR（N）或CHAR（N BYTE）：定义固定长度的字符串（以字节为单位），最大长度为2000字节。如果CHAR（100）或CHAR（100 BYTE），表示可存

储100个字节的字符串，并且占用空间是固定的（100个字节）

?CHAR（N CHAR）：定义固定长度的字符串（以字符个数为单位）。如果CHAR （100 CHAR），表示该列最多可以存储100个字符（单字节或多字节）。如果

该列存放的全是汉字，则占用空间最多为200个字节；如果存放的全部是英文

字符，则占用空间最多为100个字节。

?V ARCHAR2（N）或V ARCHAR2（N BYTE）：用于定义变长字符串（以字节为单位），其最大长度为4000字节。

?V ARCHAR2（N CHAR）：用于定义变长字符串（以字符为单位）。

?NUMBER（P，S）：定义数据类型的数据，P表示数字的总位数（最大字节个数，而S表示小数点后面的位数。当定义整数类型时，可以直接使用NUMBER

的子类型INT。

?DATE ：定义日期时间数据，其长度为7个字节。当查询DA TE类型列时，其数据的默认显示格式为（DD－MON－YY），如“29－4月－05”。

?TIMESTAMP：是DATE的扩展，在该数据类型上执行DML操作与DATE类型完全相同。但当查询时，数据的显示格式为（DD－MON－YY HH.MI.SS

AM），如“29－4月－03 04.02.03.000000 下午”。

?RAW(N)：定义二进制数据，N的上限值为2000。

?大对象数据类型：早期版本（6，7）中，存储大批量字符数据采用数据类型LONG，存储大批量二进制数据采用LONG RAW类型。从8版本开始，建议

使用CLOB存储大批量字符，建议使用BLOB类型存储大批量二进制数据。

下表列出了这些数据类型之间的区别：

?伪列ROWID和ROWNUM

ROWID

用于惟一地标识表行，它间接给出了表行的物理位置，并且ROWID是定位表

行最快的方式。如果某表包含了完全相同的行数据，为了删除重复行，那么可

以考虑使用ROWID作为条件。当使用INSERT语句插入数据时，Oracle会自

动生成ROWID，并将其值与表数据一起存放到表行。ROWID与表列一样可

以直接查询，如

SELECT dname,rowid FROM dept;

ROWNUM

用于返回标识行数据顺序的数字值。当执行SELECT语句返回数据时，第1

行的ROWNUM为1，第2行的ROWNUM为2，以此类推

3.2建表

语法：CREATE TABLE [schema.]table_name(

Column_name datatype [DEFAULT expr]

[,…]

);

其中：schema用于指定方案名（与用户名完全相同），table_name用于指定表名，column_name 用于指定列名，datatype用于指定列的数据类型，DEFAULT子句用于指定列的默认值。（每张表最多可定义1000列）

[注释：查询表结构：DESC]

?在当前方案中建表

CREATE TABLE dept01(

dno NUMBER(2),name V ARCHAR2(10),loc V ARCHAR2(20)

);

?在其他方案中建表

CREATE TABLE scott.dept02(

dno NUMBER(2),name V ARCHAR2(10),loc V ARCHAR2(20)

)；

?在建表时为列指定默认值

CREATE TABLE scott.dept03(

dno NUMBER(2),name V ARCHAR2(10),

loc V ARCHAR2(20) DEFAULT …呼和浩特?

)；

?使用子查询建表

CREATE TABLE emp04(name,salary,job,dno) AS

SELECT ename,sal,job,deptno FROM emp WHERE deptno=30;

?建立临时表

临时表用于存放会话或事务的私有数据。临时表包括事务临时表和会话临时表两种

类型，其中事务临时表是指数据只在当时事务内有效的临时表，会话临时表是指数

据只在当前会话内有效的临时表。当建立临时表时，需要使用CREATE GLOBAL

TEMPORARY TABLE命令。通过使用ON COMMIT DELETE ROWS选项可以指

定事务临时表，通过使用ON COMMIT PRESERVE ROWS选项可以指定会话临时

表，例：

CREATE GLOBAL TEMPORARY TABLE temp1(cola INT)

ON COMMIT DELETE ROWS

说明：当执行了以上语句之后，会建立事务临时表TEMP1。因为事务临时表的数

据只在当前事务内有效，所以在事务结束之后会自动清除其数据。

3.3修改表

如果表结构不符合实际情况，建表之后，可用ALTER TABLE改变表结构。

●增加列

语法：ALTER TABLE table_name ADD(

column datatype [DEFAULT expr][,column datatype…]

};

如ALTER TABLE emp01 ADD eno NUMBER(4);

●修改列定义

语法：ALTER TABLE table_name MODIFY(

column datatype [DEFAULT expr][,column datatype…]

};

如ALTER TABLE emp01 MODIFY job V ARCHAR2(15);

●删除列

语法：ALTER TABLE table_name DROP(column);

如：ALTER TABLE emp01 DROP COLUMN dno;

●修改列名

语法：ALTER TABLE table_name

RENAME COLUMN column_name TO new_column_name;

如：ALTER TABLE emp01 RENAME COLUMN eno TO empno;

●修改表名

语法：RENAME object_name TO new_object_name;

如：RENAME emp01 TO employee;

●增加注释

语法：COMMENT ON TABLE table_name IS …text?;

COMMENT ON COLUMN table_name.column IS …text?;

如：COMMENT ON TABLE employee IS …存放雇员信息?;

COMMENT ON COLUMN https://www.wendangku.net/doc/2f12875678.html, IS …描述雇员姓名?;

3.4截断和删除表

●截断表

当表结构必须保留，而表数据不再需要时，可以使用TRUNCA TE TABLE命令截断表。执行此命令时，会删除表的所有数据，并释放表所占用的空间，但会保留表的结构。

语法：TRUNCA TE TABLE table_name;

说明：当删除表的所有数据时，既可以使用DELETE语句，也可以使用TRUNCATE TABLE命令。注意的是，DELETE（DML）操作可以回退，但TRUNCATE TABLE（DDL）操作不能回退。

如：TRUNCATE TABLE employee;

●删除表

当表不再需要时，可以使用DROP TABLE命令删除表。用此命令，不仅会删除表的所有数据，而且会删除表结构。

语法：DROP TABLE table_name [CASCADE CONSTRAINTS] [PURGE];

CASCADE CONSTRAINTS用于指定级联删除从表的外键约束，PURGE用于指定彻底删除表（这个选项是10g的新特征）

如：DROP TABLE employee;

●恢复被删除表

当执行DROP TABLE语句删除表时，Oracle会将被删除表存放到数据库回收站。从Oracle Database 10g开始，使用FLASHBACK TABLE命令可以恢复被删除表。

语法：FLASHBACK TABLE table_name TO BEFORE DROP;

3.5显示表信息

●USER_TABLES

当建立表时，Oracle会将表信息存放到数据字典。通过查询数据字典视图USER_TABLES，可以显示当前用户的所有表信息。

如：conn scott/tiger

SELECT table_name FROM user_tables;

●USER_OBJECTS

当建立数据库对象（表、视图、索引等）时，Oracle会将对象信息存放到数据字典中。

通过查询数据字典视图USER_OBJECTS，可显示所有数据库对象。

如：SELECT object_name FROM user_objects

WHERE object_type=?TABLE?;

●USER_TAB_COMMENTS

当执行COMMENT命令为表、视图增加注释信息时，Oracle会将注释存放到数据字典中。通过查询数据字典视图USER_TAB_COMMENTS，可以显示当前用户所有表的注释。

如：SELECT comments FROM user_tab_comments

WHERE table_name=?EMPLOYEE?;

●USER_COL_COMMENTS

当执行COMMENT命令为列增加注释信息时，Oracle会将注释存放到数据字典中。

通过查询数据字典视图USER_COL_COMMENTS，可以显示当前用户所有表的列注释。

如：SELECT comments FROM user_col_comments

WHERE table_name=?EMPLOYEE? AND column_name=?NAME?;

[注释：表名及列名均大写，因为Oracle中是以大写字母存储对象名及列名

第4章Oracle操作符

Oracle中有很多的操作符，每种操作符都有自己的含义，在使用时需要很好的理解其中的内涵。这些操作符与平时大家见到的一些操作符几乎是一样的，含义也差不多。需要注意一点的是，oracle中的赋值语句用:=的方式，而不是=。

第5章基本查询

●简单查询语句

●限制数据

●排序数据

5.1简单查询语句

查询所有列：select * from dept;

查询指定列：desc emp(显示表结构) select empno,ename,sal from emp;

查询日期列：日期列是指date类型列，默认显示格式为dd-mon-yy，不同语言和地区的日期显示结果有所不同。如果想以自己习惯的日期格式显示日期值，必须

要用to_char函数进行转换。当日期语言为SIMPLIFIED CHINESE时，

格式：17-12月-80

Select ename,to_char(hiredate,?YYYY-MM-DD? from emp;

上述语句显示格式：1980-12-17

取消重复行：默认会显示所有行，但完全相同的查询结果没有实际意义，因此有时需要取消重复结果

Select distinct deptno,job from emp;(显示三列都不相同的记录集)

Select distinct deptno;(只显示deptno不同的记录集)

使用算术表达式：当招行查询操作时，可在数字列上用算术表达式(+,-,*,/)

Select ename,sal,sal*12 from emp;

使用列别名：默认情况下，列标题是大写格式的列名或表达式，如果使用列别名，列别名可跟在列名后，并且在二者之间可以加AS关键字，若列别名区分大小

写、包含特殊字符或空格，必须用双引号引住

Select ename as name,sal*12 “Annual Salary” from emp;

处理NULL：NULL表示未知值，既不是空格也不是0。若没为列提供数据且该列无默认值，则其数据为NULL。当算术表达式包含NULL时，其结果也是NULL。

Select ename,sal,comm,sal+comm from emp;

Select ename,sal,comm,sal+nvl(comm,0) from emp;

Nvl(comm,0)说明：如果comm存在数值，则函数返回其原有数值；如果

comm列为NULL，则函数返回0。

连接字符串：连接字符串是使用||操作符完成的。如果在字符串中要加入数字值，那么在||后可以直接指定数字；如果在字符串中加入字符和日期值，则必须用单引

号引住。

Select ename||?的岗位是?||job “Employee” from emp;

或Select ename||?的岗位是?||job AS Employee from emp;

[注释：dual是一个虚拟表，用来查那些不属于实际表里的内容，如：

select sysdate from dual; select 3+3 from dual;]

1．限制数据（条件查询）

条件查询中条件表达式中需要使用各种比较操作符，如（=，<>(!=)，>=，<=，>，<，BETWEEN…AND…，IN(list)，LIKE，IS NULL）

使用数字值：select ename,sal from emp where sal>2000;

使用字符值：select ename,sal from emp where job=?MANAGER?;//字符值必须用单引号引住

需要注意：字符值区分大小写

使用日期值：在where条件中使用日期值时，必须用单引号引住，并且日期必须符合默认日期显示格式和日期语言。如果不符合默认日期显示格式，必须使用

to_date函数进行转换。

Select ename,sal,hiredate from emp where hiredate>?01-1月-82?;

使用BETWEEN…AND操作符：

select ename,sal,job from emp where sal between 2000 and 4000 使用LIKE操作符：执行模糊查询。%：通配0或多个字符_：通配单个字符

Select ename,sal from emp where ename like …S%?//显示首字符为S的

所有雇员名及其工资

Select ename,sal from emp where ename like …__o%?；

当希望使用这两个字符（%，_）招行模糊查询时，必须使用ESCAPE