SQL - 使用 DDL 语句

使用 DDL 语句创建和管理表

模式是多个数据库对象的集合，这些对象称为模式对象。这些对象可以通过其所有者模式直接访问。下表列出了模式对象。

表 - 用于存储数据
视图 - 用于以所需格式从一个或多个表中投影数据
序列 - 用于生成数值
索引 - 用于提高对表的查询性能
同义词 - 对象的替代名称

创建数据库的第一步之一是创建将存储组织数据的表。数据库设计涉及识别各种组织系统（如订单输入、库存管理和应收账款）的系统用户需求。无论数据库的大小和复杂程度如何，每个数据库都由表组成。

创建表

要在数据库中创建表，DBA 必须掌握某些信息 - 表名、列名、列数据类型和列大小。所有这些信息都可以稍后使用 DDL 命令修改。

表命名约定 -

您为表选择的名称必须遵循以下标准规则
名称必须以字母 A-Z 或 a-z 开头
可以包含数字和下划线
可以是大写或小写
长度最多可达 30 个字符
不能使用模式中其他现有对象的相同名称
不能是 SQL 保留字

按照上述指南，“EMP85”可以是有效的表名。但 85EMP 不是。类似地，UPDATE 不能用作表名，因为它是一个 SQL 保留关键字。

CREATE TABLE 语句

CREATE TABLE 是一个 DDL 语句，用于在数据库中创建表。一旦执行 CREATE TABLE 脚本，表就会被创建，并随时准备保存数据。用户必须拥有 CREATE TABLE 系统权限才能在其自己的模式中创建表。但是，要在任何用户的模式中创建表，用户必须拥有 CREATE ANY TABLE 模式。

以下是基本 CREATE TABLE 语句的语法。可能还有许多其他子句来明确提供存储规范或段值。

CREATE TABLE [schema.]table 
          ( { column datatype [DEFAULT expr] [column_constraint] ... 
            | table_constraint} 
         [, { column datatype [DEFAULT expr] [column_constraint] ... 
            | table_constraint} ]...) 
         [AS subquery]

在上述语法中，DEFAULT 指定默认值，如果忽略该列，则可以在 INSERT 语句中使用。它不能包含对其他表列或伪列（CURRVAL、NEXTVAL、LEVEL 和 ROWNUM）的引用，除了 SYSDATE 和 USER，或者未完全指定的日期常量。

约束是在列级别或表级别（在本节后面介绍）可选定义的规则。这些规则在对表的任何数据操作（插入、更新）期间进行检查，并在违反规则时引发错误以中止操作。

例如，下面的 CREATE TABLE 语句创建了一个名为 EMP_TEST 的表。请注意列规范、数据类型和精度。

CREATE TABLE SCOTT.EMP_TEST
(EMPID NUMBER,
ENAME VARCHAR2(100),
DEPARTMENT_ID NUMBER,
SALARY NUMBER,
JOB_ID VARCHAR2(3),
HIREDATE DATE,
COMM NUMBER);

用户可以通过在表名前缀用户名或模式来引用其他用户模式中的表。例如，用户 GUEST 希望从 SCOTT 拥有的 EMP_TEST 表中查询员工姓名和薪资。他可以发出以下查询 -

SELECT  ENAME, SALARY,
FROM 	GUEST.EMP_TEST;

列可以在创建表时保存默认值。它有助于限制 NULL 值进入列。默认值可以从文字、表达式或 SQL 函数推导出，该函数必须返回与列兼容的数据类型。在下面的 CREATE TABLE 语句中，请注意 LOCATION_ID 列的默认值为 100。

CREATE TABLE SCOTT.DEPARTMENT
(DEPARTMENT_ID NUMBER,
   DNAME VARCHAR2 (100),
   LOCATION_ID NUMBER DEFAULT 100);

CTAS - 使用子查询创建表

可以使用子查询选项从数据库中的现有表创建表。它复制表结构以及表中的数据。数据也可以根据条件复制。列数据类型定义（包括显式施加的 NOT NULL 约束）将复制到新表中。

以下 CTAS 脚本创建了一个名为 EMP_BACKUP 的新表。部门 20 的员工数据被复制到新表中。

CREATE TABLE EMP_BACKUP
AS
SELECT * FROM EMP_TEST
WHERE department_id=20;

数据类型

数据类型用于指定表中列的基本行为。从更广泛的角度来看，列行为可以属于数字、字符或日期系列。还有许多其他子类型属于这些系列。

数字数据类型

NUMBER 数据类型包含整数、定点和浮点数值。早期版本的 Oracle 为每种不同类型的数字定义了不同的数据类型，但现在 NUMBER 数据类型满足所有这些用途。当列必须存储可用于数学计算的数值数据时，选择 NUMBER 数据类型。有时，NUMBER 数据类型用于存储标识号，其中这些号码由 DBMS 作为顺序号生成。

NUMBER (p, s)，其中 p 是最多 38 位的精度，s 是标度（小数点右边的位数）。标度范围从 -84 到 127。

NUMBER (p) 是一个定点数字，标度为零，精度为 p。

FLOAT [(p)]，其中 p 是二进制精度，范围从 1 到 126。如果未指定 p，则默认值为二进制 126。

日期数据类型

对于每个 DATE 数据类型，世纪、年份、月份、日期、小时、分钟、秒都存储在数据库中。每个数据库系统都有一个由初始化参数 NLS_DATE_FORMAT 定义的默认日期格式。此参数通常设置为 DD-MON-YY。如果未指定时间，则默认时间为上午 12:00:00。

字符数据类型

Oracle 支持三种预定义的字符数据类型，包括 CHAR、VARCHAR、VARCHAR2 和 LONG。VARCHAR 和 VARCHAR2 实际上是同义词，Oracle 建议使用 VARCHAR2 而不是 VARCHAR。当列将存储固定长度的字符值时，使用 CHAR 数据类型。例如，美国的社会安全号码 (SSN) 分配给每个公民，并且始终为 9 个字符（即使 SSN 严格由数字组成，这些数字也被视为字符），并且将指定为 CHAR(9)。使用 VARCHAR2 数据类型存储长度可变的字母数字数据。例如，客户姓名或地址在要存储的字符数方面会有很大差异。VARCHAR2 列的最大大小为 4,000 个字符。

LOB 数据类型

Oracle 提供了几种不同的 LOB 数据类型，包括 CLOB（字符大对象）和 BLOB（二进制大对象）。这些数据类型的列可以存储非结构化数据，包括文本、图像、视频和空间数据。CLOB 数据类型可以使用 CHAR 数据库字符集存储高达 8 TB 的字符数据。BLOB 数据类型用于存储非结构化二进制大对象，例如与图像和视频数据关联的对象，其中数据只是“位”值的流。BLOB 数据类型可以存储高达 8 TB 的二进制数据。NCLOB 数据类型可以使用多字节国家字符集存储高达 8TB 到 128TB 的字符大对象。BFILE 数据类型值用作文件定位器或指向服务器文件系统上文件的指针。支持的最大文件大小为 8TB 到 128TB。

约束

约束是在 Oracle 表中定义的一组规则，用于确保数据完整性。这些规则针对每个列或列集强制执行。每当表参与数据操作时，都会验证这些规则，并在违反规则时引发异常。可用的约束类型有 NOT NULL、主键、唯一键、检查和外键。

以下语法可用于在列级别强制约束。

语法

column [data type] [CONSTRAINT constraint_name] constraint_type

除了 NOT NULL 之外的所有约束也可以在表级别定义。复合约束只能在表级别指定。

NOT NULL 约束

NOT NULL 约束意味着数据行必须具有指定为 NOT NULL 的列的值。如果将列指定为 NOT NULL，则 Oracle RDBMS 将不允许将违反此约束的行存储到员工表中。它只能在列级别定义，不能在表级别定义。

语法

COLUMN [data type] [NOT NULL]

UNIQUE 约束

有时需要对不是主键列的列值强制唯一性。可以使用 UNIQUE 约束来强制执行此规则，Oracle 将拒绝任何违反唯一约束的行。唯一约束确保列值是不同的，没有任何重复。

语法

列级

COLUMN [data type] [CONSTRAINT <name>] [UNIQUE]

**表级：**CONSTRAINT [约束名称] UNIQUE (列名)

注意：Oracle 在内部创建唯一索引以防止列值重复。索引将在后面的 PL/SQL 中讨论。

CREATE TABLE TEST
( ... ,
  NAME VARCHAR2(20) 
          CONSTRAINT TEST_NAME_UK UNIQUE,
  ... );

对于复合唯一键，必须在表级别定义如下。

CREATE TABLE TEST
( ... ,
  NAME VARCHAR2(20),
  STD VARCHAR2(20) ,
      CONSTRAINT TEST_NAME_UK UNIQUE (NAME, STD)
 );

主键

每个表通常必须包含一个列或一组列，这些列唯一地标识存储在表中的数据行。此列或列集称为主键。大多数表只有一个列作为主键。主键列被限制为不允许 NULL 和重复值。

需要注意的要点 -

一个表只能有一个主键。
多个列可以组合在一个复合主键下。
Oracle 在内部创建唯一索引以防止列值重复。索引将在后面的 PL/SQL 中讨论。

语法

列级

COLUMN [data type] [CONSTRAINT <constraint name> PRIMARY KEY]

表级

CONSTRAINT [constraint name] PRIMARY KEY [column (s)]

以下示例显示如何在列级别使用 PRIMARY KEY 约束。

CREATE TABLE TEST
( ID  NUMBER CONSTRAINT TEST_PK PRIMARY KEY,
  ...  );

以下示例显示如何在表级别使用 PRIMARY KEY 约束定义复合主键。

CREATE TABLE TEST
 ( ...,
   CONSTRAINT TEST_PK PRIMARY KEY (ID) 
 );

外键

当两个表基于特定列共享父子关系时，子表中的连接列称为外键。父表中对应列的此属性称为引用完整性。子表中的外键列值可以为空，也可以必须是父表中现有值。请注意，只有被引用表的列才能强制引用完整性。

如果在子表中的列上定义了外键，则 Oracle 不允许删除父行，如果它包含任何子行。但是，如果在定义外键时给出了 ON DELETE CASCADE 选项，则 Oracle 在删除父行时会删除所有子行。类似地，ON DELETE SET NULL 表示当父表中的行被删除时，外键值将设置为 null。

语法

列级

COLUMN [data type] [CONSTRAINT] [constraint name] [REFERENCES] [table name (column name)]

表级

CONSTRAINT [constraint name] [FOREIGN KEY (foreign key column name) REFERENCES] [referenced table name (referenced column name)]

以下示例显示如何在列级别使用 FOREIGN KEY 约束。

CREATE TABLE TEST
(ccode varchar2(5) 
     CONSTRAINT TEST_FK REFERENCES PARENT_TEST(ccode),
   ...
);

ON DELETE CASCADE 子句的使用

CREATE TABLE TEST
(ccode varchar2(5) 
   CONSTRAINT TEST_FK REFERENCES PARENT_TEST (ccode)
   ON DELETE CASCADE,
   ...
);

检查约束

有时特定列中存储的数据值必须落入某些可接受的值范围内。CHECK 约束要求为存储在表中的每一行，指定的检查条件必须为真或未知。检查约束允许在列上强制执行条件规则，在将数据插入列之前必须验证该规则。条件不能包含子查询或伪列 CURRVAL NEXTVAL、LEVEL、ROWNUM 或 SYSDATE。

Oracle 允许单个列具有多个 CHECK 约束。实际上，为列定义的 CHECK 约束数量没有实际限制。

语法

列级

COLUMN [data type] CONSTRAINT [name] [CHECK (condition)]

表级

CONSTRAINT [name] CHECK (condition)

以下示例显示如何在列级别使用 CHECK 约束。

CREATE TABLE TEST
( ...,
   GRADE char (1) CONSTRAINT TEST_CHK
   CHECK (upper (GRADE) in ('A','B','C')),
   ...
);

以下示例显示如何在表级别使用 CHECK 约束。

CREATE TABLE TEST
( ...,
   CONSTRAINT TEST_CHK
   CHECK (stdate < = enddate),
);

ALTER TABLE 语句

数据库管理员 (DBA) 可以在数据库中创建表之后更改表结构或列定义。DDL 命令 ALTER TABLE 用于执行此类操作。ALTER 命令提供了多个专用于模式对象的实用程序。ALTER TABLE 语句用于在表中添加、删除、重命名和修改列。

以下 ALTER TABLE 语句将表 EMP 重命名为 EMP_NEW。

ALTER TABLE EMP RENAME TO EMP_NEW;

以下 ALTER TABLE 语句向 EMP_NEW 表添加一个新的列 TESTCOL。

ALTER TABLE EMP_NEW ADD (TESTCOL VARCHAR2 (100))

以下 ALTER TABLE 语句将列 TESTCOL 重命名为 TESTNEW。

ALTER TABLE EMP_NEW RENAME COLUMN TESTCOL TO TESTNEW

以下 ALTER TABLE 语句从 EMP_NEW 表中删除列 TESTNEW。

ALTER TABLE EMP_NEW DROP COLUMN TESTNEW;

以下 ALTER TABLE 语句在 EMPLOYEE_ID 列上添加主键。

ALTER TABLE EMP_NEW ADD PRIMARY KEY (EMPLOYEE_ID)

以下 ALTER TABLE 语句删除主键。

ALTER TABLE EMP_NEW DROP PRIMARY KEY;

以下 ALTER TABLE 语句将表的模式切换为只读。

ALTER TABLE EMP_NEW READ ONLY;

只读表

只读表是 Oracle 11g 中的一个增强功能。它允许将表用于只读目的。在早期的 Oracle 版本中，通过授予其他用户 SELECT 权限来使表成为只读，但所有者仍然具有读写权限。但是现在，如果将表设置为只读，即使是所有者也无法访问数据操作。

语法

ALTER TALE [TABLE NAME] READ ONLY

ALTER TALE [TABLE NAME] READ WRITE

示例

SQL>CREATE TABLE ORATEST (id NUMBER)

SQL>INSERT INTO ORATEST VALUES (1);

SQL>ALTER TABLE ORATEST READ ONLY;

SQL> INSERT INTO ORATEST VALUES (2);
INSERT INTO ORATEST VALUES (2)
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."ORATEST"

SQL> UPDATE ORATEST SET id = 2;
UPDATE ORATEST SET id = 2
       *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."ORATEST"

SQL> DELETE FROM ORATEST;
DELETE FROM ORATEST
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."ORATEST"

SQL> TRUNCATE TABLE ORATEST;
TRUNCATE TABLE ORATEST
               *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."ORATEST"

SQL> ALTER TABLE ORATEST ADD (description VARCHAR2 (50));
ALTER TABLE ORATEST ADD (description VARCHAR2 (50))
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."ORATEST"

SQL> ALTER TABLE ORATEST READ WRITE;

Table altered.

SQL> DELETE FROM ORATEST;

1 row deleted.

DROP TABLE 语句

DROP TABLE 语句用于从数据库中删除表。删除的表及其数据将不再可用进行选择。如果回收站中可用，可以使用 FLASHBACK 实用程序恢复已删除的表。删除表会删除与其关联的索引和触发器。

语法

DROP TABLE [TABLE NAME] [PURGE]

以下语句将删除表并将其放入回收站。

DROP TABLE emp_new;

以下语句将删除表并将其从回收站中清除。

DROP TABLE emp_new PURGE;

打印页面