使用DDL语句

使用DDL语句创建和管理表

模式是多个数据库对象的集合，称为模式对象.这些对象可由其所有者模式直接访问.Below表列出了模式对象.

• 表 - 存储数据

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

• 序列 - 生成数值

• 索引 - 提高表格查询的效果

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

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

创建表

要在数据库中创建表，DBA必须手头有一些信息 - 表名，列名，列数据类型和列大小.以后可以使用DDL命令修改所有这些信息.

表命名约定 -

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

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

• 可以包含数字和下划线

• 可以是小写的上限

• 最多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语句中使用.它不能包含对除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  - 使用子查询创建表

可以使用子查询从数据库中的现有表创建表option.It复制表结构以及表中的数据.也可以根据条件复制数据.包括明确强加的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数据类型，Century，Year，Month，Day，Hour，Minute，Second都存储在数据库中.每个数据库系统都有一个默认日期格式，该格式由初始化参数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数据类型可以使用CHAR数据库字符集存储多达8TB的字符数据.BLOB数据类型用于存储非结构化二进制大对象，例如与图像和视频数据，其中数据只是一个"位"值流.一个BLOB数据类型可以存储多达8TB的二进制数据.NCLOB数据类型可以存储多字节国家字符集中的字符大对象，最大为8TB到128TB.数据类型值用作文件定位器或指向服务器文件系统上文件的指针.支持的最大文件大小为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将不允许将行存储到违反此约束的employee表.它只能在列级别定义，而不能在表级别定义.

语法:

 COLUMN [数据类型] [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 [约束名称] 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)
);

外键

当两个表根据特定列共享父子关系时，子项中的连接列table被称为Foreign Key.父表中相应列的这个属性称为参照完整性.子表中的子键值可以为null，也可以是父表的现有值.请注意，只有主表.引用表的键列有资格强制引用完整性.

如果在子表中的列上定义了外键，那么Oracle不允许删除父行，如果它包含任何子行.但是，如果在定义外键时给出了ON DELETE CASCADE选项，则Oracle会在删除父行时删除所有子行.同样，ON DELETE SET NULL表示父表中的行是删除后，外键值设置为null.

语法:

列级:

 
 COLUMN [数据类型] [CONSTRAINT] [约束名称] [REFERENCES] [表名(列名)]

表级别:

 
 CONSTRAINT [约束名称] [FOREIGN KEY(外键列名)REFERENCES] [引用的表名(引用的列名)]

以下示例显示了如何在列级使用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 [数据类型] CONSTRAINT [名称] [检查(条件)]

表级:

 
 CONSTRAINT [name] CHECK(condition)

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

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

以下示例显示了如何在表级使用CHECK约束.

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

ALTER TABLE语句

DBA可以在创建表后更改表结构或列定义在数据库中.DTL TABLE命令ALTER TABLE用于执行此类操作.Alter命令提供了多个独立于模式对象的实用程序.ALTER TABLE语句用于添加，删除，重命名和修改表中的列.

下面的ALTER TABLE语句将表EMP重命名为EMP_NEW.

 
 ALTER TABLE EMP RENAME to EMP_NEW;

下面的ALTER TABLE语句将新列TESTCOL添加到EMP_NEW表

 
 ALTER TABLE EMP_NEW ADD(TESTCOL VARCHAR2(100))

下面的ALTER TABLE语句将TESTCOL列重命名为TESTNEW.

 
 ALTER TABLE EMP_NEW RENAME COLUMN TESTCOL to TESTNEW

下面的ALTER TABLE语句删除列TESTNEW来自EMP_NEW表

 
 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]只读

 
 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实用程序恢复删除表(如果在recyclebin中可用).删除表会删除与之关联的索引和触发器.

语法:

 
 DROP TABLE [TABLE NAME] [PURGE]

以下语句将删除表并将其放入recyclebin.

 
 DROP TABLE emp_new;

以下语句将删除表格并将其从循环库中冲出.

 
 DROP TABLE emp_new PURGE;