Oracle表的创建与管理

• 1. 表的创建
• 2. 表的修改
• 3. 表中数据的增删改查
• 4. 表的Merge
• 5. 表的删除
• 6. 表的重命名
• 7. 表的索引
• 8. 表的约束
• 9. dual表

表是Oracle数据库中最基础的存储对象，用于存储数据。本文主要介绍了Oracle表的创建与管理，包括表的创建、修改、删除、重命名，表的索引、约束以及表中数据的增、删、改、查等基本操作。

1. 表的创建

在Oracle数据库中，创建表需要使用CREATE TABLE语句：

CREATE TABLE table_name
(
    column1 datatype [ NULL | NOT NULL ],
    column2 datatype [ NULL | NOT NULL ],
    ...
    columnN datatype [ NULL | NOT NULL ]
);

其中，table_name是要创建的表的名称，column1 ~ columnN是表中的列名，datatype是数据类型，NULL和NOT NULL表示该列是否允许为空。

如创建一个名为student的表，包含学生的姓名、性别、年龄和学号四个字段，使用以下语句：

CREATE TABLE student
(
    name VARCHAR2(50) NOT NULL,
    gender CHAR(1) NOT NULL,
    age NUMBER(3),
    num VARCHAR2(20) PRIMARY KEY
);

上述语句创建了一个名为student的表，包含四个字段：name（姓名）、gender（性别）、age（年龄）和num（学号）。其中，name和gender字段不允许为空，age字段允许为空，num字段为主键，用于唯一标识每个学生。

创建表的其他参数：

• PCTFREE 保留空间
• PCTUSED 从表中删除数据，使得数据块空间不断减少，减少至40%时，可再次插入数据（PCTFREE与PCTUSED之和越接近100%，数据块空间利用率越高）。
• INITRANS 初始事务数量
• MAXTRANS 最大的事务并发数量
• CACHE 指定将表中的数据放在数据库高速缓存中，默认NOCACHE。对于较小、访问频繁的表，使用CACHE，在用户第一次访问表中数据时，整个表被加载到数据库高速缓存。
• LOGGING 默认情况下，用户在表上执行DDL和DML都会产生重做日志。如果不希望产生重做日志，使用NOLOGGING子句。
• COMPRESS 使用该子句，一个数据块中两行完全相同的数据将被压缩为一行。

创建表的时候插入数据

 create table tt as select id，name from table1; 

create table t1 parallel 8 nologging compress as select * from table1; 

创建临时表

• 临时表为事务级，事务提交或回滚时，数据即被删除

create global temporary table t1(name varchar(10)) on commit delete rows;

临时表为会话级，表中数据一致保留直到当前会话结束。

create global temporary table t1(name varchar(10)) on commit preserve rows;

2. 表的修改

实际开发中可能需要对已有的表进行修改，如添加新的列、删除已有的列、修改列的数据类型等。Oracle提供了ALTER TABLE语句来实现这些操作：

• ADD用于添加新的列

ALTER TABLE table_name
ADD (column_name datatype [ NULL | NOT NULL ],
     column_name datatype [ NULL | NOT NULL ],
     ...);

• DROP COLUMN用于删除已有的列

ALTER TABLE table_name DROP COLUMN column_name;

• MODIFY用于修改列的数据类型

ALTER TABLE table_name
MODIFY (column_name datatype [ NULL | NOT NULL ]);

如对于之前创建的student表，添加一个新的字段address：

ALTER TABLE student
ADD (address VARCHAR2(100) NULL);

上述语句在student表中添加了一个新的字段address，数据类型为VARCHAR2，长度为100，允许为空。

3. 表中数据的增删改查

（1）插入数据

使用INSERT语句向表中插入数据：

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

其中，table_name是要插入数据的表的名称，column1、column2、column3等是表中的列名，value1、value2、value3等是要插入的数据值。

例如向student表中插入一条数据，可以使用以下语句：

INSERT INTO student (name,gender, age,num)
VALUES ( 'John','M',20,01);

该语句将向student表中插入一条数据，包含name为’John’、gender为’M’、age为20、num为01的记录。

向目标表插入源表数据

插入数据时，如果源表和目标表字段名、字段数量、字段顺序都相同：

insert into table1 select * from table2;

否则 使用：

insert into table1(col1， col2， ...， coln) select col1， col2， ...， coln from table2;

（2）删除数据

使用DELETE语句从表中删除数据：

DELETE FROM table_name WHERE condition;

其中，table_name是要删除数据的表的名称，condition是删除记录的条件。

例如从student表中删除age大于等于25的记录，可以使用以下语句：

DELETE FROM student WHERE age >= 25;

（3）更新数据

使用UPDATE语句更新表中的数据：

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

其中，table_name是要更新数据的表的名称，column1、column2等是要更新的列名，value1、value2等是要更新的数据值，condition是更新记录的条件。

例如将student表中id为1的记录的age更新为21，可以使用以下语句：

UPDATE student
SET age = 21
WHERE id = 1;

（4）查询数据

使用SELECT语句从表中检索数据，例如查询student表中i所有年龄大于26岁的学生信息：

SELECT * FROM student WHERE age > 20;

4. 表的Merge

Merge用于需要使用SQL语句同时进行Insert/Update的操作，也就是说当存在记录时就更新(Update)，不存在数据时就插入(Insert)。

执行Merge前：

执行以下语句：

Merge Into products t
Using newproducts  s
On (t.product_id=s.product_id)
When Matched Then 
Update Set t.product_name=s.product_name，t.Category=s.Category
When Not Matched Then
Insert Values(s.product_id，s.product_name，s.Category)

其中，insert和update是可选的，UPDATE 和INSERT后面可以跟WHERE子句，UPDATE子句后面可以跟delete来去除一些不需要的行。在ON条件中可以使用常量来insert所有的行到目标表中，不需要连接到源表和目标表。

5. 表的删除

• 使用TRUCATE TABLE语句清除表中所有内容，保留结构：

 truncate table table_name; 

• 使用DROP TABLE语句删除某个表：

DROP TABLE table_name;

例如删除之前创建的student表：

DROP TABLE student;

上述语句将删除名为student的表及其所有数据。在执行DROP TABLE语句前，应确保该表不再被其他对象所使用，否则会抛出错误。

• 使用purge彻底删除表

6. 表的重命名

使用RENAME语句修改表名：

RENAME table_name TO new_table_name;

例如将之前创建的student表重命名为new_student：

RENAME student TO new_student;

7. 表的索引

数据库索引的作用是减少读数据所需的磁盘访问次数，加快数据访问速度，提高数据库性能。

索引提供指向存储在表的指定列中的数据值的指针，可快速定位需要的数据，从而提高查询效率。如果没有索引，查询需要扫描整个表，将花费大量时间和资源。

Oracle索引由一系列存储在磁盘上的索引项组成，索引项第一列是索引键(keyval)，第二列是行指针(ROWID)。

ROWID由数据库自动生成，包含18个字符串：

OOOOOO/FFF/BBBBBB/RRR对应数据库对象编号/数据文件编号/数据块号/行号。

Oracle数据库中有多种类型的索引，包括B树索引、位图索引、函数索引、全文索引等。

（1）B树索引

Oracle默认为B树，是Oracle数据库中最常用的索引类型。它是一种平衡树结构，可以快速定位需要的数据。B树索引通常由一个或多个列组成，这些列存储了数据库表中的数据。当查询需要访问这些数据时，B树索引可以帮助快速定位它们。

B树索引的优点是查询速度快，适用于大多数查询场景。但B树索引维护成本高，当表中的数据发生变化时，需要更新索引，这将影响数据库的性能。

（2）位图索引

位图索引是一种用于处理大量重复数据的索引类型。它将每个索引值映射到一个位图，每个位图表示一个索引值是否存在。当查询需要访问这些数据时，位图索引可以帮助快速定位它们。

位图索引的优点是适用于处理大量重复数据的查询场景，可以显著提高查询效率。但是，位图索引不适用于处理不重复的数据，且索引维护成本高。

（3）函数索引

函数索引是一种用于处理函数表达式的索引类型。它将函数表达式的计算结果存储在索引中，当查询需要访问这些数据时，函数索引可以帮助快速定位它们。

函数索引的优点是适用于处理函数表达式的查询场景，可以显著提高查询效率。但是，函数索引的缺点是索引维护成本高，且只适用于特定的函数表达式。

（4）全文索引

全文索引是一种用于处理文本数据的索引类型。它将文本数据分解为单词，并将每个单词映射到一个索引值。当查询需要访问这些数据时，全文索引可以帮助快速定位它们。

全文索引的优点是适用于处理文本数据的查询场景，可以显著提高查询效率。但是，全文索引的缺点是索引维护成本高，且需要特定的全文搜索引擎。

• 当字段取值较多时，如证件号码，则应使用B-Tree索引
• 当字段值取值较少的情况下，如性别，应使用位图索引

在Oracle中可以为表中的列创建索引。例如，在student表的num列上创建一个名为“idx_num”的索引：

CREATE INDEX idx_num ON student(num);

8. 表的约束

数据库中，约束是用来保证数据的完整性和一致性的规则。Oracle数据库通过使用约束来限制表中数据的输入和更改，使得数据库中存储的数据是结构正确的，并且可以被其他程序和用户正确地共享和查询。

（1）主键约束

主键约束是用来唯一标识表中每个记录的一种方式，即主键必须在表中具有唯一性，不能为NULL，且只能定义一个主键约束。主键通常用来建立关系型数据库之间的连接。

建立主键约束

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2);

如对于student表，建立主键约束：

Alter Table student Add Constraint pk_num Primary Key (num);

（2）外键约束

外键约束用于在表之间建立一对多或多对多的关系，它指明了一个表中的某个字段必须引用另一个表中的主键约束。外键值必须在引用表中存在或者为空值。

创建外键约束：

ALTER TABLE table_name
ADD CONSTRAINT constraint_name FOREIGN KEY (column1) REFERENCES other_table(column2);

（3）唯一约束

唯一约束用于确保某个字段或一组字段中的值是唯一的。与主键不同，唯一约束允许 null 值。

创建唯一约束：

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2);

（4）CHECK约束

创建CHECK约束：

Alter Table nn Add Constraint ck_emp_n2 Check(n2='男' Or n2='女')

9. dual表

dual是一个虚拟表，用来构成select的语法规则。

查看表结构：

 desc dual; 

oracle保证dual里面永远只有一条记录，查询表数据：

select * from dual; 

Dual属于SYS schema，以PUBLIC SYNONYM的方式供其他数据库USER使用

SQL> select owner， object_name ， object_type from dba_objects where object_name like '%DUAL%';　OWNER OBJECT_NAME OBJECT_TYPE

常见用途如下：

select user from dual;  #查看当前用户
select SYS_CONTEXT('USERENV'，'TERMINAL') from dual; #调用系统函数
SQL> select your_sequence.nextval from dual; #得到序列下一个值
SQL> select your_sequence.currval from dual; #得到序列当前值
SQL> select 7*9 from dual; #计算器