- 数据定义语言（DDL）。
- 怎样建立我们自己的用户账号。
- 可以用于Oracle表的不同数据类型。
- 怎样在表上建立约束。
- 怎样使用数据词典获取与数据库中对象有关的信息。
3.1 数据定义语言
DDL通常用于建立和管理数据库。它可以使用SQL建立对象、配置数据库安全、管理统计数据以及完成其它功能。
在第2章中，我COMMIT和ROLLBACK。在表中执行插入、更新和删除等操作之后使用COMMIT语句，就可以使修改结果在用户数据库中永久存在。
当用户向数据库发出DDL语句的时候，则在语句执行前后就产生隐匿的COMMIT语句（即使DDL失败）。这是因为DDL不能进行回滚，而且，这也意味着DDL语句使用之前任何没有提交的改变也不能够进行回滚。
在DDL完成之后（由于它不能够进行回滚），ORACLE会调用另一个隐式的COMMIT，为用户开始一个新的事务处理。伪代码如下：
|
1
2
3
4
5
6
7
8
9
|
Insert
into
some_table
values
(…):
Insert
into
some_table
values
(…):
Create
table
another_table(column1 data type,column2 data type);
Insert
into
another_table
values
(…);
Insert
into
another_table
values
(…);
|
那么Oracle就会在后台为我们执行如下操作：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
Insert
into
some_table
values
(…):
Insert
into
some_table
values
(…):
Commit
;
Create
table
another_table(column1 data type,column2 data type);
Commit
;
Insert
into
another_table
values
(…);
Insert
into
another_table
values
(…);
|
如果进行ROLLBACK，前三条记录仍然存在，ANOTHER_TBALE表中的三条记录就会丢失。
合法的Oracle标识符
Oracle遵守如下规则：
- 它不能是Oracle保留字（如SELECT等）
- 它必须使用1到30个字符。例外的情况数据库的名称（最多8个字符）和数据库链接（最多128个字符）
- 它必须以数据库字符集中的字母字符开头
- 它只能够包含数据库字集中的字母字符，以及以下字符：#、$、_（另外，数据库连接可以包含‘@’和‘.’）
- 它不能够包含（单或者双）引号
如下的标识符列表为非法：
- %EMP（不能包含%）
- SELECT（Oracle保留字）
- THIS_NAME_IS_LONGER_THAN_30_CHARACTERS
- 1STCOLUMN（不能使用数字开始）
- SEAN’S_TABLE（不能包含引号）
3.2 表空间
在Oracle中，数据库被分割成为了称为表空间的独立存储单元。这些表空间是存放表、懿旨以及其它消耗磁盘空间的数据库对象的地方。
3.3 用户
用户可能要为自己的数据建立自己的用户账号。这项工作很重要，因为用户应该避免使用Oracle提供的SYS和SYSTEM账号，或者我们曾经在前面的章节中使用过的SCOTT账号。
3.3.1 建立用户
DBA角色是一个具有超过120个系统特权的角色。
CREATE_SESSION是一个系统特权，它可以为用户提供连接数据库的能力。
IDENTIFIED BY <标识符>是用来给出用户的密码（如下密码为zyf）
以SYSTEM最高权限帐户进入，建立用户账号，并赋予DBA特权：
|
1
2
3
4
5
6
7
8
9
|
SQL>
create
user
oracle_admin identified
by
zyf
2 /
用户已创建
SQL>
grant
create
session,dba
to
oracle_admin;
授权成功。
|
注意：
DBA角色可以让用户完全控制数据库，尽管这种权利在数据库的时候是完成必要的，但是如果它掌握在错误的用户手里，也将是非常危险的。
在建立USER的时候，一些可用的选项如下所示：
- DEFAULT TABLESPACE。除非另行规定，否则用户所建立的对象都会存储在它们用户的默认表空间中。如果没有明确规定，这个默认值就是SYSTEM。
- TEMPORARY TABLESPACE。例如在查询中进行排序需要空间这样的临时字段，都会在用户的临时表空间中进行分配。在Oracle 9i之前，这个默认值都是SYSTEM表空间。在Oracle 9i中，默认的临时表空间是TEMP。
- QUOTA。用户可以为它们存储数据的表空间赋予配额。这是用户能够在表空间中分配的最大空间数。
- PASSWORD EXPIRE。使用这个子句意味着用户在他们第一次登录的时候必须改变他们的密码。
3.3.2 改变用户
当管理数据库的时候，用户可能会有各种原因来改变用户账号：
- 重置用户密码
- 锁定账号和解除账号锁定
- 修改用户的默认表空间或者临时表空间
- 修改表空间配额
1. 重置密码
ALTER USER命令以及IDENTIFIED BY子句，改变密码。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
create
user
oracle_admin identified
by
zyf
2 /
用户已创建
SQL>
grant
create
session,dba
to
oracle_admin;
授权成功。
SQL>
alter
user
oracle_admin identified
by
oracle_admin;
用户已更改。
|
2. 锁定账号以及解除账号锁定
在Oracle中，可以将用户账号锁定，以使它们不能使用。如下语法：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
ALTER
USER
<username> ACCOUNT [LOCK|UNLOCK];
SQL>
connect
oracle_admin/oracle_admin;
已连接。
SQL>
connect
system/zyf
已连接。
SQL>
alter
user
oracle_admin account lock;
用户已更改。
SQL>
connect
oracle_admin/oracle_admin;
ERROR:
ORA-28000: the account
is
locked
警告: 您不再连接到 ORACLE。
|
3. 修改表空间设置
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
SQL>
select
tablespace_name,contents
from
dba_tablespaces;
TABLESPACE_NAME CONTENTS
------------------------------ ---------
SYSTEM PERMANENT
UNDOTBS1 UNDO
TEMP
TEMPORARY
CWMLITE PERMANENT
DRSYS PERMANENT
EXAMPLE PERMANENT
INDX PERMANENT
ODM PERMANENT
TOOLS PERMANENT
USERS PERMANENT
XDB PERMANENT
已选择11行。
|
将默认表空间设置为USERS，临时表空间设置为TEMP。
|
1
2
3
|
SQL>
alter
user
oracle_admin
default
tablespace users
temporary
tablespace
temp
;
用户已更改。
|
4. 修改表空间配额
UNLIMITED关键字指出用户可以在USERS表空间中存储数据，直到它被完全占满。我们设置了TEMP表空间中的存储上限为10M，SYSTEM表空间中为0M。
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
alter
user
oracle_admin
2 quota unlimited
on
users
3 quota 10M
on
temp
4 quota 0M
on
system
5 /
用户已更改。
|
3.3.3 删除用户
为了删除用户，使用DROP USER命令。
CASCADE如果将其用于DROP USER命令末尾，就可以让Oracle知道应该在从数据库移走用户之前，删除用户的所有对象（例如：表、视图、以及过程代码）。
试验：建立和删除用户
(1) 连接ORACLE_ADMIN账号，建表DROPME授权
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
connect
oracle_admin/oracle_admin
已连接。
SQL>
create
user
dropme
2 identified
by
doomed
3
default
tablespace users
4
temporary
tablespace
temp
5 quota unlimited
on
users
6 /
用户已创建
SQL>
grant
create
session,
create
table
to
dropme
2 /
授权成功。
|
(2) 作为DROPME用户连接数据库，并且建立一个如下所示的简单表：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL>
connect
dropme/doomed
已连接。
SQL>
create
table
employees_backup(
2 employee_id number,
3 last_name varchar2(30),
4 email varchar2(100)
5 )
6 /
表已创建。
|
(3) 使用ORACLE_ADMIN账号（它具有DROP USER特权）删除用户DROPME：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
connect
oracle_admin/oracle_admin
已连接。
SQL>
drop
user
dropme;
drop
user
dropme
*
ERROR 位于第 1 行:
ORA-01922: 必须指定
CASCADE
以删除
'DROPME'
|
(4) DROPME用户拥有一个表，使用CASCADE关键字，如下所示：
|
1
2
3
|
SQL>
drop
user
dropme
cascade
;
用户已丢弃
|
3.4 Oracle数据类型
3.4.1 数值类型
数值类型可以用于存储整数、浮点值以及实数。在Oracle中的NUMBER数据类型具有精度（precision）和范围（scale）。精度是数值中的数字总数，可以为1到38个数字之间的任何值。范围取值可以从0到3，它表述了任何给定数值的小数点右边的位数。
|
1
|
<column_name>NUMBER（
precision
[, scale]）
|
声明示例如：NUMBER（即NUMBER(38)）、NUMBER(9,2)、NUMBER(4,10)。
3.4.2 字符类型
1. CHAR
CHAR数据长度的数值将会让SQL使用空格填充剩余长度。例如：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
SQL>
create
table
authorized_blends(
2 bean_name
char
(50)
3 );
表已创建。
SQL>
insert
into
authorized_blends
values
(
'Papua New Guinea'
);
已创建 1 行。
SQL>
insert
into
authorized_blends
values
(
'Ethiopia'
);
已创建 1 行。
SQL>
insert
into
authorized_blends
values
(
'Sumatra'
);
已创建 1 行。
SQL>
select
bean_name,length(bean_name)
2
from
authorized_blends
3 /
BEAN_NAME LENGTH(BEAN_NAME)
-------------------------------------------------- -----------------
Papua New Guinea 50
Ethiopia 50
Sumatra 50
|
2. NCHAR
NCHAR数据类型使用了与CHAR数据类型大体相同的方法来存储固定长度的字符数据。两者之间的差异仅仅在于在数据库中存储NCHAR数据的方式不同。
3. VARCHAR2
VARCHAR2数据类型与CHAR类型非常相似，两者之间的不同之处在于类型VARCHAR2的声明是可变长度，而非固定长度（不会像CHAR一样有空格对值的填充）。
4. NVARCHAR2
NVARCHAR2列可以存储可变长度的基于字符的数据，它大体使用与VARCHAR2数据类型相同的方式进行工作。它们两者之间的主要差异是NVARCHAR2数据在数据库中的存储方式。
5. 长度语义
Oracle不能够自动考虑到多字节字符集。Oracle 9i中可以在列大小附加BYTE或者CHAR，以告诉Oracle怎样解释数值。
6. RAW
RAW数据类型与VARCHAR2相似，虽然RAW列可以用于存储可变长度的二进制数据，而不是字符数据。RAW列的最大长度是2000个字节。
7. LONG和LONG RAW
LONG数据类型可以用于在单独的列中存储大数据量的可变长度字符数据。LONG列的大小最高可以为2GB。在Oracle8中，可以使用CLOB或者NCLOB数据类型对其进行替代。
LONG RAW数据类型就如同它们的名字所描述的那样，是一个可以远远超过2000个字节的RAW数据类型。当声明LONG RAW列的时候，不用规定其大小。与此相对，LONG RAW列能够包含最高到2GB的二进制数据或者字节。在Oracle 8i中，建议管理员使用BLOB或者BFILE数据类型。
8. 大对象
在Oracle 8中引入了大对象或者称为LOB。它们为基于二进制和字符的大规模数据，提供了比以前的LONG RAW数据类型列灵活的存储机制。特别是：
- LOB在数据库中存储物理数据时，可以进行一些选择。
- LOB可以在Oracle对象类型中使用。
- LOB支持对数据的分段访问。
- 每种类型的LOB都可以存储4GB的数据。
- 在Oracle提供的PL/SQL程序包DBMS_LOB中，提供了处理LOB的功能。
9. LOB类型
有一些不同类型的LOB：
- 二进制大对象（BLOB）。
- 字符大对象（CLOB）。
- 国家字符大对象（NCLOB）。
- 二进制文件（BFILE）。
10. LOB定位符
LOB定位符是在使用外挂方式或者在BFILE中存储LOB的时候，在表的记录中实际存储的对象。它通常是一个指向实际LOB的指针，可以告诉Oracle怎样在需要的时候获取LOB。这种方式可以避免在进行全表搜索的时候，让Oracle遍历表的数据块中存储的成百上千的带有LOB的记录。通过使用在表中存储的LOB定位符来代替实际数据，Oracle就可以读取表的数据块，而使LOB定位符的影响最小。
3.4.3 日期和时间
Oracle提供如下类别的时间数据类型：
- DATE
- TIMESTAMP
- TIMESTAMP WITH TIME ZONE*
- TIMESTAMP WITH LOCAL TIME ZONE*
- INTERVAL YEAR TO MONTH*
- INTERVAL DAY TO SECOND*
这些标记了*的类型只存在于Oracle 9i中。
1. DATE
TO_DATE和SYSDATE
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
SQL>
connect
oracle_admin/oracle_admin;
已连接。
SQL>
create
table
company_events(
2 event_name varchar2(100),
3 event_date
date
)
4 /
表已创建。
SQL>
insert
into
company_events(event_name,event_date)
values
(
'Created DATE Sample code'
,SYSDATE);
SQL>
insert
into
company_events(event_name,event_date)
values
(
'Oracle Open World'
,to_date(
'2-12月-2001'
,
'DD-MON-YYYY'
));
SQL>
column
event_name format a40;
SQL>
select
*
from
company_events;
EVENT_NAME EVENT_DATE
---------------------------------------- ----------
Created
DATE
Sample code 31-12月-12
Oracle
Open
World 02-12月-01
|
SHOW PARAMETERS来判断我们数据库的默认日期格式，如下所示：
|
1
2
3
4
5
6
7
|
SQL> show parameters nls_date_format;
NAME
TYPE VALUE
--------------------- ------------ -----------
nls_date_format string
|
DATE关键字规定日期值，如下语法实现：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL>
insert
into
company_events(event_name,event_date)
values
(
'Created an Oracle 9i DATE value'
,
DATE
'2001-10-11'
);
已创建 1 行。
SQL>
select
*
from
company_events;
EVENT_NAME EVENT_DATE
---------------------------------------- ----------
Created
DATE
Sample code 31-12月-12
Oracle
Open
World 02-12月-01
Created an Oracle 9i
DATE
value 11-10月-01
|
2. TIMESTAMP
|
1
|
<column_name>
TIMESTAMP
[SECONDS_PRECISION]
|
例如：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
SQL>
create
table
other_company_events(
2 event_name varchar2(100),
3 event_date
date
,
4 event_timestamp
timestamp
);
表已创建。
SQL>
insert
into
other_company_events(event_name,event_date,event_timestamp)
values
(
'Created COMPAN
_EVENTS table'
,sysdate,sysdate);
已创建 1 行。
SQL>
column
event_name format a28;
SQL>
column
event_date format a18;
SQL>
column
event_timestamp format a28;
SQL>
select
event_name,to_char(event_date,
'DD-MON-YY HH24:MI:SS'
) event_date,event_timestamp
from
ot
her_company_events;
EVENT_NAME EVENT_DATE EVENT_TIMESTAMP
---------------------------- ------------------ ----------------------------
Created COMPANY_EVENTS
table
31-12月-12 16:26:5 31-12月-12 04.26.59.000000
9 下午
|
在这个例子中，我们向两个列EVENT_DATE和EVENT_TIMESTAMP中插入了SYSDATE。当从这个表中选择数据的时候，我们就能够看到TIMESTAMP列为SECOND字段提供了6位数字的精度，而DATE列没有存储小数部分的秒。
3. TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH TIME ZONE数据类型是TIMESTAMP数据类型的扩展。语法如下：
|
1
|
<column_name>
TIMESTAMP
[SECONDS_PRECISION]
WITH
TIME
ZONE
|
(1) 建立名为CONFERENCE_CALLS的表，然后插入与三个电话会议有关的信息：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
create
table
conference_calls(
2 title varchar2(100),
3 phone varchar2(20),
4 place varchar2(100),
5 starts
timestamp
with
time
zone)
6 /
表已创建。
SQL>
insert
into
conference_calls(title,phone,place,starts)
2
values
(
'Sales Strategy'
,
'212.123.4567'
,
'Washington'
,
TIMESTAMP
'2001-12-01 15:00:00.000000 EST'
)
已创建 1 行。
|
(2) 查询所处的时区
|
1
2
3
4
5
6
7
|
SQL>
select
dbtimezone
from
dual;
DBTIME
------
-07:00
|
(3) 查询所有在当前本地时间15：00的记录
|
1
2
3
4
5
|
SQL>
select
title,phone
from
conference_calls
2
where
starts=
TIMESTAMP
'2001-12-01 15:00:00.000000 -7:00'
;
未选定行
|
4. TIMESTAMP WITH LOCAL TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE与TIMESTAMP WITH TIME ZONE大体相同，只是在前一种情况中，要根据用户会话时区在列中存储时区数据。语法如下：
|
1
|
<column_name> TIMESTAME [SECONDS_PRECISION]
WITH
LOCAL
TIME
ZONE
|
如：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL>
create
table
local_conference_calls(
2 title varchar2(100),
3 phone varchar2(20),
4 place varchar2(100),
5 starts
timestamp
with
local
time
zone);
表已创建。
SQL>
insert
into
local_conference_calls(title,phone,place,starts)
2
values
(
'Sales Strategy'
,
'212.123.4567'
,
'New York'
,
TIMESTAMP
'2001-12-01 15:00:00.000000 EST'
);
已创建 1 行。
|
ALTER SESSION SQL语句为我们的会话设置时区。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
SQL>
alter
session
set
time_zone=
'-05:00'
;
会话已更改。
SQL>
column
title format a25;
SQL>
column
stats format a30;
SQL>
select
title,starts
from
local_conference_calls;
TITLE
-------------------------
STARTS
---------------------------------------------------------------------------
Sales Strategy
01-12月-01 03.00.00.000000 下午
英国时区：
SQL>
alter
session
set
time_zone=
'GMT'
;
会话已更改。
SQL>
select
title,starts
from
local_conference_calls;
TITLE
-------------------------
STARTS
---------------------------------------------------------------------------
Sales Strategy
01-12月-01 08.00.00.000000 下午
|
5. INTERVAL YEAR TO MONTH
它可以用于存储月或者年的时间周期。
|
1
|
INTERVAL
YEAR
[(YEAR_PRECISION)]
TO
MONTH
|
这个年精度（YEAR_PRECISION）值规定了可以在间隔值的YEAR字段中包含的数字数量。合法值为0到9，默认值是2。
6. INTERVAL DAY TO SECOND
INTERVAL DAY TO SECOND是Oracle 9i中的另一个间隔数据类型，它可以用于存储天、小时、分钟以及秒。语法如下：
|
1
|
INTERVAL DAT [(DAY_PRECISION)]
TO
SECOND
[(SECONDS_PRECISION)]
|
如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
SQL>
create
table
employee_breaks(
2 employee_id number,
3 break_reason varchar2(100),
4 break_time interval
day
(1)
to
second
(2));
表已创建。
SQL>
insert
into
employee_breaks
values
(100,
'COFFEE BREAK'
,
TIMESTAMP
'2001-09-03 12:47:00.000000'
-TI
MESTAMP
'2001-09-03 13:13:00.000000'
);
已创建 1 行。
SQL>
column
break_reason format a20;
SQL>
column
break_time format a20;
SQL>
select
employee_id,break_reason,break_time
from
employee_breaks;
EMPLOYEE_ID BREAK_REASON BREAK_TIME
----------- -------------------- --------------------
100 COFFEE BREAK -0 00:26:00.00
|
3.4.4 ANSI数据类型
Oracle支持工业标准的ANSI数据类型，以及IBM DB/2和SQL/DS数据类型。这些类型出于一些原因会很有用：
- 一些数据库设计工具可以导出可以被其它类型的数据库使用的ANSI兼容SQL。
- 一些在其他数据库上使用的SQL脚本不需要转换成为Oracle语法就可以在Oracle中使用。
1. 数值数据类型转换表
表3-2 数值数据类型转换表
| 可用SQL数据类型 | Oracle等价数据类型 |
| NUMERIC(PRECISION,SCALE) | NUMBER(PRECISION,SCALE) |
| DECIMAL(PRECISION,SCALE) | NUMBER(PRECISION,SCALE) |
| INTEGER | NUMBER(38) |
| INT | NUMBER(38) |
| SMALLINT | NUMBER(38) |
| FLOAT(b) | NUMBER |
| DOUBLE PRECISION | NUMBER |
| REAL | NUMBER |
2. 字符数据类型转换表
表3-3 字符数据类型转换表
| 可用SQL数据类型 | Oracle等价数据类型 |
| CHARACTER(size) | CHAR(size) |
| CHAR(size) | CHAR(size) |
| CHARACTER VARYING(size) | VARCHAR(size) |
| CHAR VARYING(size) | VARCHAR(size) |
| NATIONAL CHARACTER(size) | NCHAR(size) |
| NATIONAL CHAR(size) | NCHAR(size) |
| NCHAR(size) | NCHAR(size) |
| NATIONAL CHARACTER VARYING(size) | NVARCHAR2(size) |
| NATION CHAR VARYING(size) | NVARCHAR2(size) |
| NCHAR VARYING(size) | NVARCHAR2(size) |
3.5 建立表
建立表的最简单和最常见的方法会如下语法：
|
1
2
3
4
5
6
7
8
9
|
CREATE
TABLE
[
SCHEMA
.].<table_name>(
<column_name> <data type> [
DEFAULT
<expression>] [<
constraint
>]
[,<column_name> <data type> [
DEFAULT
<expression>] [<
constraint
>]
[,…]
);
|
SCHEMA。这是表所属的用户名称，或者模式名称。
TABLE_NAME。这是要建立的表的名称。
COLUMN_NAME。这是在表中要建立的列的名称。
DATA TYPE。
DEFAULT <expression>。对于每个列，用户都可以定义一个默认值，以用于没有为列插入语句提供值的情况。
CONSTRAINT。用户可以选择在用户建立的各个列上定义约束。
3.5.1 约束
在Oracle中，可以使用一些声明型完整性约束，来确保用户数据正确。
声明型完整性（declarative integrity）是可以用于表列的强制规则。
参照完整性（referential integrity）是使Oracle成为关系数据库的重要组成部分。
1. 约束语法
|
1
2
3
4
5
6
7
8
9
|
CREATE
TABLE
[
SCHEMA
.].<table_name>(
<column_name> <data type> [
DEFAULT
<expression>] [<
constraint
>]
[,<column_name> <data type> [
DEFAULT
<expression>] [<
constraint
>]
[,…]
);
|
还可以使用ALTER TABLE 语句向表中添加约束，如下所示：
|
1
2
3
4
5
6
7
|
ALTER
TABLE
[
SCHEMA
.]<table_name>
ADD
[
CONSTRAINT
[<
name
>]]
<
constraint
definition>
);
|
2. 主键
表的主键可以确保在一个表中没有重复行。尽管用户可以建立没有主键的表，但是这通常被认为是不好的习惯，应该尽量避免。
用户的表中定义主键的时候需要牢记的一些要点如下所示：
- 在任意给定表上只能够有一个主键。
- 表中不能有任何两行具有相同的主键。
- 主键列不能够为NULL。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
create
user
hr_audit identified
by
zyf
2 /
用户已创建
SQL>
grant
create
session,dba
to
hr_audit
2 /
授权成功
SQL>
create
table
another_dept
as
select
*
from
scott.dept;
表已创建。
SQL>
alter
table
another_dept
2
add
constraint
another_dept_pk
3
primary
key
(deptno);
表已更改。
|
3. 外键
在Oracle中，表能够通过使用所谓的外键约束进行关联。约束可以放置在一个表（子表）中的一个列或者一组列上。作为约束定义的组成部分，必须要定义子表的列去参照另一个表中（父表）的一个匹配列或者列组。
在使用外键的时候，需要牢记一些要点：
- 在父表中被参照的列必须是唯一键或者主键
- 外键可以由多个列构成。这些列被称为复合外键。复合外键所拥有的子表列要与父表列的数量相匹配。
- 当在子表中插入记录的时候，不论父表在相应的列中是否具有NULL值，外键列都可以插入NULL值。
- 外键可以是自参照约束，也就是说它们可以指回到相同的表。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
grant
select
on
emp
to
hr_audit;
授权成功。
SQL>
create
table
another_emp
as
select
*
from
scott.emp;
表已创建。
SQL>
alter
table
another_emp
2
add
constraint
another_dept_fk
foreign
key
(deptno)
references
another_dept(deptno)
3 /
表已更改。
|
4. 唯一性约束
唯一性约束（unique constraints）可以确保表中的各行，对于值为非NULL的给定列或者列组都具有唯一值。如同于外键约束，在使用唯一性约束的时候也有一些要点：
- 在表中，可以有多个行在相应的唯一性约束列中具有NULL值。由于NULL值不等于另一个NULL值，所以可以认为在唯一性约束列中具有NULL值的列唯一。
- 可以使用多个列建立唯一性约束。这些列称为复合唯一键。
- 唯一键能够由最多32个列构成。
- 当定义唯一键约束的时候，Oracle会在后台建立一个唯一性索引来强制唯一性。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL>
alter
table
another_emp
2
add
(
3 ssn varchar2(9)
4 );
表已更改。
SQL>
alter
table
another_emp
2
add
constraint
another_emp_ssn_uk
3
unique
(ssn);
表已更改。
|
5. CHECK约束
CHECK约束是用于表中积德的评估条件。如果在建立约束的时候，针对表中任何记录的条件评估为FALSE，那么它的建立就会失败。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
SQL>
alter
table
another_emp
2
add
(
3 gender
varchar
(10));
表已更改。
SQL>
alter
table
another_emp
2
add
constraint
ck_gender
3
check
(gender
in
(
'MALE'
,
'FEMALE'
));
表已更改。
SQL>
select
*
from
another_emp
where
sal>1500;
SQL>
update
another_emp
set
gender=
'MALE'
where
mod(empno,2)=0;
SQL>
update
another_emp
set
gender=
'FEMALE'
where
mod(empno,2)=1;
SQL>
select
empno,job,sal,gender
from
another_emp
where
sal>3000;
EMPNO JOB SAL GENDER
---------- --------- ---------- ----------
7839 PRESIDENT 5000 FEMALE
|
3.5.2 CREATE TABLE AS SELECT
复制表结构
|
1
|
CREATE
TABLE
<table_name>
AS
SELECT
|
注意：
当使用CREATE TABLE … AS SELECT 语法建立表的时候，支持对象（例如约束、索引和触发器）将不会作为操作的结果建立。
3.6 数据词典
作为数据词典视图的简单示例，这是一个可以被所有数据库用户使用的视图。
|
1
|
SQL> describe user_tables;
|
对视图所处的空间感兴趣，则如下所示：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
select
table_name,tablespace_name
from
user_tables
order
by
table_name
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
ANOTHER_DEPT SYSTEM
ANOTHER_EMP SYSTEM
BONUS SYSTEM
DEPT SYSTEM
EMP SYSTEM
SALGRADE SYSTEM
已选择6行。
|
其它视图：
- USER_TAB_COLUMNS——这个视图不仅提供了有关表中的列的数据，而且还提供了有关属于当前用户的视图和簇中的列的数据。
- USER_TAB_MODIFICATIONS——这个表包含了自从Oracle优化器最后一次对表进行分析以来，在当前用户所拥有的表上进行的改变。
- USER_TAB_PRIVS——这个表包含了所有针对表进行的授权。
- USER_TABLES——这个视图包含了用户所拥有的表的元数据。
数据词典范围
USER_TABLES视图只能向我们展示与当前用户所拥有的表有关的信息。而另一方面，数据库管理账号却可以使用DBA_TABLES视图。
|
1
|
SQL>
select
owner,table_name
from
all_tables
order
by
owner,table_name;
|
3.7 小结
文章根据自己理解浓缩，仅供参考。
摘自：《Oracle编程入门经典》 清华大学出版社 http://www.tup.com.cn/
from: http://www.cnblogs.com/yongfeng/archive/2013/01/06/2846996.html
2343
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
