Sql Certificate 简明教程

SQL - Using DDL Statements

Using DDL Statements to Create and Manage Tables

模式是多個數據庫對象的集合，這些對象稱為模式對象。這些對象由其所有者模式直接訪問。下表列出了模式對象。

A schema is the collection of multiple database objects,which are known as schema objects.These objects have direct access by their owner schema.Below table lists the schema objects.

Table - to store data
View - to project data in a desired format from one or more tables
Sequence - to generate numeric values
Index - to improve performance of queries on the tables
Synonym - alternative name of an object

創建數據庫的第一步之一是創建將存儲組織數據的表。數據庫設計涉及識別各種組織系統（例如訂單輸入、庫存管理和應收帳款）的系統用戶需求。無論數據庫大小和複雜性如何，每個數據庫都由表組成。

One of the first steps in creating a database is to create the tables that will store an organization’s data.Database design involves identifying system user requirements for various organizational systems such as order entry, inventory management, and accounts receivable. Regardless of database size and complexity, each database is comprised of tables.

Creating the table

要在數據庫中創建表，DBA 必須掌握某些信息 - 表名、列名、列數據類型和列大小。稍後可以使用 DDL 命令修改所有這些信息。

To create a table in the database,a DBA must have certain information in hand - the table name, column name, column data types, and column sizes. All this information can be modified later using DDL commands.

Table Naming Conventions -

The name you choose for a table must follow these standard rules:
The name must begin with a letter A-Z or a-z
Can contain numbers and underscores
Can be in UPPER of lower case
Can be up to 30 characters in length
Cannot use the same name of another existing object in your schema
Must not be a SQL reserved word

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

Following the above guidelines, 'EMP85' can be a valid table name.But 85EMP is not.Similarly, UPDATE cannot be a chosen as a table name since it a SQL reserved keyword.

CREATE TABLE statement

CREATE TABLE 是 DDL 语句，用于在数据库中创建表。创建 CREATE TABLE 脚本后，表会立即被创建，并准备保存数据。用户必须有 CREATE TABLE 系统权限才能在其自己的架构中创建表。但是要在任何用户的架构中创建表，用户必须有 CREATE ANY TABLE 架构。

The CREATE TABLE is a DDL statement which is used to create tables in the database.The table gets created as soon as the CREATE TABLE script is executed and is ready to hold the data onwards.The user must have the CREATE TABLE system privilege to create the table in its own schema.But to create a table in any user’s schema, user must have CREATE ANY TABLE schema.

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

Here is the syntax of a basic CREATE TABLE statement.There may be many additional clauses to explicitly provide the storage specifications or segment values.

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）的引用。

In the above syntax, DEFAULT specifies default value which can be used during INSERT statement if the column is ignored. It cannot contain references to other table columns or pseudo columns (CURRVAL, NEXTVAL, LEVEL, and ROWNUM) except SYSDATE and USER, or date constants that are not fully specified.

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

Constraints are the rules defined optionally at the column level or table level (covered later in this chapter).These rules are checked during any data action (Insert, update) on the table and raise error to abort the action upon its violation.

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

For example, the CREATE TABLE statement below creates a table EMP_TEST. Note the column specifications, data type and precision.

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 表中查询员工姓名和工资。他可以发出以下查询 -

A user can refer the tables from other user’s schema by prefixing the username or schema with the table name.For example, a user GUEST wishes to query the employee name and salary from the EMP_TEST table which is owned by SCOTT. He can issue the below query -

SELECT  ENAME, SALARY,
FROM 	GUEST.EMP_TEST;

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

A column can hold a default value during the time of table creation.It helps to restrict the NULL values getting into the column. Default value can be deduced from either a literal, expression or SQL function which must return a compatible data type to the column. In the below CREATE TABLE statement, note that the LOCATION_ID column has default value 100.

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

CTAS - Create table using subquery

可以使用子查询选项从数据库中的现有表中创建表。它复制了表结构以及表中的数据。还可以根据条件复制数据。列数据类型定义（包括明确指定的 NOT NULL 约束）被复制到新表中。

A table can be created from an existing table in the database using a subquery option.It copies the table structure as well as the data from the table. Data can also be copied based on conditions.The column data type definitions including the explicitly imposed NOT NULL constraints are copied into the new table.

下面的 CTAS 脚本创建了新表 EMP_BACKUP。将部门 20 的员工数据复制到新表中。

The below CTAS script creates a new table EMP_BACKUP. Employee data of department 20 gets copied into the new table.

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

Data types

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

Data types are used to specify the basic behavior of a column in the table.On a broader basis,column behavior can either belong to number,character or a date family.There are multiple other subtypes which belong to these families.

Number data type

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

The NUMBER datatype encompasses both integer,fixed-point,and floating-point numeric values.Early versions of Oracle defined different datatypes for each of these different types of numbers,but now the NUMBER datatype serves all of these purposes.Choose the NUMBER datatype when a column must store numerical data that can be used in mathematical calculations.Occasionally,the NUMBER datatype is used to store identification numbers where those numbers are generated by the DBMS as sequential numbers.

NUMBER (p, s)，其中 p 为精度，最高可达 38 位，s 为刻度（小数点右边的位数）。刻度范围为 -84 到 127。

NUMBER (p, s), where p is the precision up to 38 digits and s is the scale (number of digits to the right of the decimal point).The scale can range between -84 to 127.

NUMBER (p) 是定点数，刻度为零，精度为 p。

NUMBER (p),is a fixed-point number with a scale of zero and a precision of p.

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

FLOAT [(p)],where p is the binary precision that can range from 1 to 126. If p is not specified the default value is binary 126.

Date data type

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

For each DATE data type, Century, Year, Month, Day, Hour, Minute, Second are stored in database. Every database system has a default date format that is defined by the initialization parameter NLS_DATE_FORMAT. This parameter is usually set to DD-MON-YY.If you do not specify a time, the default time is 12:00:00 a.m.

Character data type

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

Oracle supports three predefined character datatypes including CHAR, VARCHAR, VARCHAR2, and LONG.VARCHAR and VARCHAR2 are actually synonymous, and Oracle recommends using VARCHAR2 instead of VARCHAR.Use the CHAR datatype when the column will store character values that are fixed-length.For example, a Social Security number (SSN) in the United States is assigned to every citizen and is always 9 characters in size (even though an SSN is strictly composed of digits,the digits are treated as characters), and would be specified as CHAR(9). Use the VARCHAR2 datatype to store alphanumeric data that is variable-length.For example, a customer name or address will vary considerably in terms of the number of characters to be stored.The maximum size of a VARCHAR2 column is 4,000 characters.

LOB data type

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

Oracle provides several different LOB datatypes, including CLOB (character large object) and BLOB (binary large object).Columns of these datatypes can store unstructured data including text, image, video, and spatial data.The CLOB datatype can store up to eight terabytes of character data using the CHAR database character set.The BLOB datatype is used to store unstructured binary large objects such as those associated with image and video data where the data is simply a stream of "bit" values.A BLOB datatype can store up to eight terabytes of binary data.The NCLOB data type can store character large objects in multibyte national character set up to 8TB to 128TB.The BFILE data type value works as a file locator or pointer to file on the server’s file system. The maximum file size supported is 8TB to 128TB.

Constraints

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

Constraints are the set of rules defined in Oracle tables to ensure data integrity.These rules are enforced placed for each column or set of columns.Whenever the table participates in data action, these rules are validated and raise exception upon violation. The available constraint types are NOT NULL, Primary Key, Unique, Check, and Foreign Key.

可使用以下语法在列级别上强制执行约束。

The below syntax can be used to impose constraint at the column level.

Syntax:

column [data type] [CONSTRAINT constraint_name] constraint_type

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

All constraints except NOT NULL, can also be defined at the table level. Composite constraints can only be specified at the table level.

NOT NULL Constraint

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

A NOT NULL constraint means that a data row must have a value for the column specified as NOT NULL.If a column is specified as NOT NULL,the Oracle RDBMS will not allow rows to be stored to the employee table that violate this constraint.It can only be defined at column level, and not at the table level.

Syntax:

COLUMN [data type] [NOT NULL]

UNIQUE constraint

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

Sometimes it is necessary to enforce uniqueness for a column value that is not a primary key column.The UNIQUE constraint can be used to enforce this rule and Oracle will reject any rows that violate the unique constraint.Unique constraint ensures that the column values are distinct, without any duplicates.

Syntax:

列级别：

*Column Level: *

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

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

*Table Level: *CONSTRAINT [constraint name] UNIQUE (column name)

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

Note: Oracle internally creates unique index to prevent duplication in the column values.Indexes would be discussed later in PL/SQL.

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

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

In case of composite unique key,it must be defined at table level as below.

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

Primary Key

每张表通常必须包含一列或一组列，该列或一组列唯一标识存储在表中的数据行。此列或列集称为主键。大多数表都将一列用作主键。主键列受限制不可为 NULL，并且不可重复。

Each table must normally contain a column or set of columns that uniquely identifies rows of data that are stored in the table.This column or set of columns is referred to as the primary key.Most tables have a single column as the primary key.Primary key columns are restricted against NULLs and duplicate values.

Points to be noted -

A table can have only one primary key.
Multiple columns can be clubbed under a composite primary key.
Oracle internally creates unique index to prevent duplication in the column values.Indexes would be discussed later in PL/SQL.

Syntax:

Column level:

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

Table level:

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

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

The following example shows how to use PRIMARY KEY constraint at column level.

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

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

The following example shows how to define composite primary key using PRIMARY KEY constraint at the table level.

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

Foreign Key

当两个表基于特定列共享父子关系时，子表中的联接列称为外键。父表中相应列的此属性称为参照完整性。子表中的外键列值可以为 null，或者必须是父表的现有值。请注意，只有被引用的表的「主键」列才有资格强制参照完整性。

When two tables share the parent child relationship based on specific column, the joining column in the child table is known as Foreign Key.This property of corresponding column in the parent table is known as Referential integrity.Foreign Key column values in the child table can either be null or must be the existing values of the parent table.Please note that only primary key columns of the referenced table are eligible to enforce referential integrity.

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

If a foreign key is defined on the column in child table then Oracle does not allow the parent row to be deleted,if it contains any child rows.However,if ON DELETE CASCADE option is given at the time of defining foreign key,Oracle deletes all child rows while parent row is being deleted.Similarly,ON DELETE SET NULL indicates that when a row in the parent table is deleted, the foreign key values are set to 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 约束。

The following example shows how to use FOREIGN KEY constraint at column level.

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。

Sometimes the data values stored in a specific column must fall within some acceptable range of values.A CHECK constraint requires that the specified check condition is either true or unknown for each row stored in the table.Check constraint allows to impose a conditional rule on a column, which must be validated before data is inserted into the column. The condition must not contain a sub query or pseudo column CURRVAL NEXTVAL, LEVEL, ROWNUM, or SYSDATE.

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

Oracle allows a single column to have more than one CHECK constraint. In fact, there is no practical limit to the number of CHECK constraints that can be defined for a column.

Syntax:

Column level:

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

Table level:

CONSTRAINT [name] CHECK (condition)

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

The following example shows how to use CHECK constraint at column level.

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

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

The following example shows how to use CHECK constraint at table level.

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

ALTER TABLE statement

DBA 可以在表创建到数据库之后对表结构或列定义进行更改。DDL 命令 ALTER TABLE 用于执行此类操作。Alter 命令为模式对象提供多个独有实用工具。ALTER TABLE 语句用于添加、删除、重命名以及修改表中的列。

A DBA can make changes to the table structure or column definitions after the table has been created in the database.The DDL command ALTER TABLE is used to perform such actions.Alter command provides multiple utilities exclusive for schema objects.The ALTER TABLE statement is used to add, drop, rename, and modify a column in a table.

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

The below ALTER TABLE statement renames the table EMP to EMP_NEW.

ALTER TABLE EMP RENAME TO EMP_NEW;

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

The below ALTER TABLE statement adds a new column TESTCOL to the EMP_NEW table

ALTER TABLE EMP_NEW ADD (TESTCOL VARCHAR2 (100))

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

The below ALTER TABLE statement renames the column TESTCOL to TESTNEW.

ALTER TABLE EMP_NEW RENAME COLUMN TESTCOL TO TESTNEW

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

The below ALTER TABLE statement drop the column TESTNEW from EMP_NEW table

ALTER TABLE EMP_NEW DROP COLUMN TESTNEW;

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

The below ALTER TABLE statement adds primary key on the EMPLOYEE_ID column.

ALTER TABLE EMP_NEW ADD PRIMARY KEY (EMPLOYEE_ID)

下面的 ALTER TABLE 语句用于删除主键。

The below ALTER TABLE statement drop the primary key.

ALTER TABLE EMP_NEW DROP PRIMARY KEY;

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

The below ALTER TABLE statement switches the table mode to read only.

ALTER TABLE EMP_NEW READ ONLY;

Read Only Tables

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

Read only tables came as an enhancement in Oracle 11g.It allows the tables to be used for read only purpose. In earlier oracle versions, tables were made read only by granting SELECT privilege to the other users, but owner still had the read write privilege.But now,if a table is set as Read only,even owner doesn’t have access on data manipulation.

Syntax:

ALTER TALE [TABLE NAME] READ ONLY

ALTER TALE [TABLE NAME] READ WRITE

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.

DROP TABLE statement

DROP TABLE 语句用于从数据库中删除表。已删除的表及其数据将不再可用于选择。如果已放在回收站中，则可以使用 Flashback 实用工具恢复已删除的表。删除表时，将删除与其关联的索引和触发器。

The DROP TABLE statement is used to remove a table from the database. The dropped table and its data remain no longer available for selection.Dropped table can be recovered using FLASHBACK utility,if available in recyclebin.Dropping a table drops the index and triggers associated with it.

Syntax:

DROP TABLE [TABLE NAME] [PURGE]

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

The below statement will drop the table and place it into the recyclebin.

DROP TABLE emp_new;

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

The below statement will drop the table and flush it out from the recyclebin also.

DROP TABLE emp_new PURGE;