使用 DDL 语句

使用 DDL 语句

使用 DDL 语句创建和管理表

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

  • 表 – 存储数据

  • 查看 – 从一个或多个表中以所需格式投影数据

  • 序列 – 生成数值

  • 索引 – 提高对表的查询性能

  • 同义词 – 对象的替代名称

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

创建表

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

表命名约定 –

  • 您为表选择的名称必须遵循以下标准规则：

  • 名称必须以字母 AZ 或 az 开头

  • 可以包含数字和下划线

  • 可以在小写的 UPPER

  • 最长可达 30 个字符

  • 不能在您的架构中使用另一个现有对象的相同名称

  • 不能是 SQL 保留字

遵循上述准则，’EMP85′ 可以是有效的表名。但 85EMP 不是。同样，不能选择 UPDATE 作为表名，因为它是 SQL 保留关键字。

创建表语句

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 am

字符数据类型

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 数据类型最多可以存储 8 TB使用 CHAR 数据库字符集的字符数据。BLOB 数据类型用于存储非结构化二进制大对象，例如与图像和视频数据相关的那些，其中数据只是“位”值的流。BLOB 数据类型最多可以存储8 TB 的二进制数据。NCLOB 数据类型可以存储多字节国家字符集的字符大对象，大小可达 8TB 到 128TB。BFILE 数据类型值用作文件定位器或指向服务器文件系统上的文件的指针。支持的最大文件大小为 8TB 到 128TB。

约束

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

以下语法可用于在列级别施加约束。

句法： 

column [data type] [CONSTRAINT constraint_name] constraint_type

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

非空约束

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

句法： 

COLUMN [data type] [NOT NULL]

唯一约束

有时需要为不是主键列的列值强制唯一性。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 表示删除父表中的行时，外键值设置为空。

句法：

列级：

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 语句用于从数据库中删除表。删除的表及其数据仍然不再可供选择。删除的表可以使用 FLASHBACK 实用程序恢复，如果在 recyclebin 中可用。删除表删除与其关联的索引和触发器。

句法：

DROP TABLE [TABLE NAME] [PURGE]

下面的语句将删除该表并将其放入回收站。

DROP TABLE emp_new;

下面的语句将删除表并将其从回收站中刷新出来。

DROP TABLE emp_new PURGE;

觉得文章有用？

点个广告表达一下你的爱意吧 ！😁