Sql Certificate 简明教程
SQL - Using DDL Statements
Using DDL Statements to Create and Manage Tables
模式是多個數據庫對象的集合，這些對象稱為模式對象。這些對象由其所有者模式直接訪問。下表列出了模式對象。
-
表格 - 存儲數據
-
視圖 - 以所需格式從一張或多張表格中投影數據
-
序列 - 生成數字值
-
索引 - 提高表查詢的性能
-
同義詞 - 對象的備用名稱
創建數據庫的第一步之一是創建將存儲組織數據的表。數據庫設計涉及識別各種組織系統（例如訂單輸入、庫存管理和應收帳款）的系統用戶需求。無論數據庫大小和複雜性如何，每個數據庫都由表組成。
CREATE TABLE statement
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 语句中使用该默认值。除了 SYSDATE 和 USER 或未完全指定的日期常量，它不能包含对其他表列或伪列（CURRVAL、NEXTVAL、LEVEL 和 ROWNUM）的引用。
约束是在列级别或表级别（本章后面会介绍）中定义的规则（可选）。在对表执行任何数据操作（插入、更新）时会检查这些规则，并在违反时引发错误以中止操作。
例如，下面的 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 - Create table using subquery
可以使用子查询选项从数据库中的现有表中创建表。它复制了表结构以及表中的数据。还可以根据条件复制数据。列数据类型定义（包括明确指定的 NOT NULL 约束）被复制到新表中。
下面的 CTAS 脚本创建了新表 EMP_BACKUP。将部门 20 的员工数据复制到新表中。
CREATE TABLE EMP_BACKUP
AS
SELECT * FROM EMP_TEST
WHERE department_id=20;Number data type
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 data type
对于每种 DATE 数据类型，世纪、年、月、日、时、分、秒都存储在数据库中。每个数据库系统都有由初始化参数 NLS_DATE_FORMAT 定义的默认日期格式。该参数通常设置为 DD-MON-YY。如果您未指定时间，则默认时间为上午 12:00:00。
Character data type
Oracle 支持三种预定义的字符数据类型，包括 CHAR、VARCHAR、VARCHAR2 和 LONG。VARCHAR 和 VARCHAR2 实际上是同义词，Oracle 建议使用 VARCHAR2 而不是 VARCHAR。当列将存储固定长度的字符值时，请使用 CHAR 数据类型。例如，美国社会安全号码 (SSN) 分配给每个公民，始终为 9 个字符（即使 SSN 严格由数字组成，数字也将被视为字符），并会指定为 CHAR(9)。使用 VARCHAR2 数据类型以存储可变长度的字母数字数据。例如，客户姓名或地址在要存储的字符数方面会有很大差异。VARCHAR2 列的最大大小为 4,000 个字符。
LOB data type
Oracle 提供了几种不同的 LOB 数据类型，包括 CLOB（字符大型对象）和 BLOB（二进制大型对象）。这些数据类型的列可以存储非结构化数据，包括文本、图像、视频和空间数据。CLOB 数据类型可以使用 CHAR 数据库字符集存储最多八太字节的字符数据。BLOB 数据类型用于存储非结构化的二进制大型对象，例如与图像和视频数据关联的对象，其中数据仅仅是“位”值的流。BLOB 数据类型可以存储最多八太字节的二进制数据。NCLOB 数据类型可以存储多字节国家字符集中的字符大型对象，大小为 8 TB 到 128 TB。BFILE 数据类型值用作服务器文件系统上文件的定位器或指针。支持的最大文件大小为 8 TB 到 128 TB。
Constraints
约束是 Oracle 表中定义的一组规则，用于确保数据完整性。这些规则对每列或列集强制执行。每当表参与数据操作时，都会验证这些规则并在违反时引发异常。可用的约束类型有 NOT NULL、主键、唯一、检查和外键。
可使用以下语法在列级别上强制执行约束。
NOT NULL Constraint
NOT NULL 约束表示数据行必须为指定为 NOT NULL 的列具有值。如果指定某个列为 NOT NULL，Oracle RDBMS 则不允许将违反此约束的行存储到 employee 表中。它只能在列级别定义，而不能在表级别定义。
UNIQUE constraint
有时有必要对不是主键列的列值强制唯一性。UNIQUE 约束可用于强制执行此规则，Oracle 将拒绝违反唯一约束的任何行。唯一约束确保列值是唯一的，没有任何重复值。
Syntax:
列级别：
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)
);Primary Key
每张表通常必须包含一列或一组列，该列或一组列唯一标识存储在表中的数据行。此列或列集称为主键。大多数表都将一列用作主键。主键列受限制不可为 NULL，并且不可重复。
Syntax:
Column level:
COLUMN [data type] [CONSTRAINT <constraint name> PRIMARY KEY]Table level:
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)
);Foreign Key
当两个表基于特定列共享父子关系时，子表中的联接列称为外键。父表中相应列的此属性称为参照完整性。子表中的外键列值可以为 null，或者必须是父表的现有值。请注意，只有被引用的表的「主键」列才有资格强制参照完整性。
若子表某列定义了外键，则 Oracle 不允许删除父行（如果它包含任何子行）。但是，如果在定义外键时指定了 ON DELETE CASCADE 选项，Oracle 就会在父行被删除时删除所有子行。类似地，ON DELETE SET NULL 表示在父表中删除行时，将外键值设为 null。
Syntax:
Column Level:
COLUMN [data type] [CONSTRAINT] [constraint name] [REFERENCES] [table name (column name)]Table level:
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),
...
);Usage of ON DELETE CASCADE clause
CREATE TABLE TEST
(ccode varchar2(5)
CONSTRAINT TEST_FK REFERENCES PARENT_TEST (ccode)
ON DELETE CASCADE,
...
);Check constraint
有时存储在特定列中的数据值必须在一些可接受的值域内。CHECK 约束要求每行为存储在表中的特定检查条件为真或未知。检查约束允许对一列施加一个条件规则，必须在将数据插入到该列之前进行验证。此条件不能包含子查询或伪列 CURRVAL、NEXTVAL、LEVEL、ROWNUM 或 SYSDATE。
Oracle 允许同一列具有多个 CHECK 约束。实际上，可为一列定义 CHECK 约束的数量没有实际限制。
Syntax:
Column level:
COLUMN [data type] CONSTRAINT [name] [CHECK (condition)]Table level:
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 statement
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;Read Only Tables
只读表是 Oracle 11g 中的一项增强功能。它允许表用于只读目的。在早期版本中，通过授予其他用户 SELECT 权限使表变为只读，但所有者仍然具有读写权限。但现在，如果将表设为只读，即使所有者也没有数据操作访问权限。
Illustration
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.