数据定义语言 (Data Definition Language, DDL) 是SQL语言集中负责数据结构定义与数据库对象定义的语言，由CREATE、ALTER与DROP三个语法所组成。数据库对象包括表、视图、索引、同义词、序列等(TABLE/VIEW/INDEX/SYN/SEQUENCE)。我们先对创建和管理表和视图进行一个介绍。

         一、创建和管理表

        数据表是数据库中一个非常重要的对象，是其他对象的基础。可分为用户表和数据字典两类。

        1、创建表（CREATE TABLE）

        CREATE TABLE 语句创建表

CREATE TABLE table                 --table：表名   column:列名
            (column datatype       --列的数据类型和长度
            [DEFAULT expr]         --指定默认值，防止插入时输入空值到列中
            [,...]);

        最为常见的数据类型有三种，分别为字符型，数值型，日期型。

        用子查询语法创建表（CTAS）

CREATE TABLE table
             [(column, column...)]
          AS subquery              --使用子查询

       如果给出了指定的列，列的数目必须等于子查询的SELECT列表的列数目。如果没有给出了指定的列，表的列名应和子查询中的列名是相同的。完整性规则不会被传递到新表中，仅列的数据类型被定义。

       2、修改表（ALTER TABLE）

ALTER TABLE table ADD (column datatype [DEFAULT expr][,column datatype]...);    --添加列

ALTER TABLE table MODIFY (column datatype [DEFAULT expr][,column datatype]...); --修改列

ALTER TABLE table DROP (column);                                                --删除列
ALTER TABLE table SET UNUSED (column);                                          --隐藏列
ALTER TABLE table DROP UNUSED COLUMNS;                                          --删除隐藏的列                 

       添加列时不能指定新添加的列出的位置，新列将成为最后一列；修改列时可以修改数据类型大小和默认值，当列中只包含空值时才能减少一个列的宽度和改变数据类型，对默认值的改变仅改变影响以后插入的列；删除列时一次只能有一列被删除。表被修改后必须至少保留一列。一旦一列被删除，它不能再恢复；当一列从表中被删除时，该表中任何其他的被用SET UNUSED选项标记列也被删除，设置UNUSED的作用是为了在cpu、内存等资源不充足的时候，先做上UNUSED标记再等数据库资源空闲的时候用DROP UNUSED COLUMNS删除。

       3、删除表（DROP TABLE）

DROP TABLE table [PURGE];

       当你删除一个表时，数据库丢失表中所有的数据，并且所有与其相关的索引也被删除，任何视图和同义词被保留但无效。ORACLE 10g中由于引入了回收站，因此要彻底删除一个表需要加PURGE关键字。

       4、截断表（TRUNCATE TABLE）

TRUNCATE TABLE table;

       截断表用于从表中删除所有的行，并且释放该表所使用的存储空间。DELETE语句也可以从表中删除所有的行，但它不能释放存储空间。TRUNCATE命令更快一些，原因如下：TRUNCATE语句是数据定义 (DDL) 语句，不产生回滚信息；截断一个表不触发表的删除触发器。

       二、定义和管理约束

       约束 (constraints)用来防止无效数据输入到表中。你可以使用约束做下面的事：在插入、更新行或者从表中删除行的时候强制表中的数据遵循规则；对于成功的操作，约束必须被满足，如果表之间有依赖关系，防止表的删除；为 Oracle工具提供规则，例如Oracle Developer。

       约束可以在创建表时定义，也可以在表创建之后定义。你可以用USER_CONSTRAINTS和USER_CONS_COLUMNS数据字典表查看对一个表约束的定义。

       1、定义约束

CREATE TABLE [schema.]table
             (column datatype [DEFAULT expr]                                       --数据类型
             [column [CONSTRAINT constraint_name] constraint_type],...             --列级约束
             [column,...[CONSTRAINT constraint_name] constraint_type] (column,...) --表级约束
             [,...]);

       约束通常在创建表的同时被创建。在表创建后约束能够被添加，并且约束可以可以被临时禁用。 约束可以在两个级别上定义：列级约束，只涉及一个单个的列，对于该列用规范定义，能够定义完整性约束的任何类型；表级约束，涉及一个或多个列，表中的列被分别定义，除了 NOT NULL，能够定义任意约束。

       2、约束类型

       3、管理约束

ALTER TABLE table ADD [CONSTRAINT constraint] type (column);                       --添加约束
ALTER TABLE table DROP PRIMARY KEY|UNIQUE(column)|CONSTRAINT constraint [CASCADE]; --删除约束
ALTER TABLE table DISABLE CONSTRAINT constraint [CASCADE];                         --禁用约束
ALTER TABLE table ENABLE CONSTRAINT constraint;                                    --启用约束

       你可以添加、删除或禁用一个约束，但你不能修改它的结构。你可以用ALTER TABLE语句的MODIFY子句添加一个NOT NULL约束到一个已经存在的列。只有在表是空的或者每个行的该列都有非空值的情况下，你才可以定义一个NOT NULL列。
       为了删除约束，你可以先从USER_CONSTRAINTS和USER_CONS_COLUMNS数据字典视图中确定约束的名字，然后使用带DROP子句的ALTER TABLE语句。DROP子句的CASCADE选项导致任何与其相依赖的约束也被删除。
       你可以禁用一个约束而不删除它，或者用带DISABLE子句的ALTER TABLE语句重新创建它。我们即可以在CREATE TABLE语句也可以在ALTER TABLE语句中使用 DISABLE子句。CASCADE子句禁用相依赖的完整性约束。禁用唯一或主键约束会移除唯一性索引。
       你可以用带ENABLE子句的ALTER TABLE语句启用一个禁用的约束，而不需要重新创建它。如果启用一个约束，约束将应用于表中所有的数据，所有在表中的数据都必须适合该约束。如果你启用一个UNIQUE键或者PRIMARY KEY约束，一个UNIQUE或PRIMARY KEY索引将被自动地创建。你即可以CREATE TABLE语句也可以在ALTER TABLE语句中使用ENABLE子句。启用一个带CASCADE选项的被禁用的主键约束不会起用任何依赖于该主键的外键。

       三、创建和管理视图

       视图是数据来自一个或者多个表的数据子集的逻辑表示。视图是基于表或另一个视图的逻辑表，一个视图并不包含它自己的数据，它象一个窗口，通过该窗口可以查看或改变表中的数据。视图基于其上的表称为基表。
       视图限制数据的访问，因为视图能够选择性的显示表中的列；视图可以用来构成简单的查询以取回复杂查询的结果；视图对特别的用户和应用程序提供数据独立性，一个视图可以从几个表中取回数据；视图提供用户组按照他们的特殊标准访问数据。

       1、创建视图

       视图有两种分类：简单视图和复杂视图。简单视图：数据仅来自一个表，不包含函数或数据分组，能通过视图执行DML操作。复杂视图：数据来自多个表，包含函数或数据分组，除查询外，只能进行少数DML操作。下面是创建视图的语法。

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)]
                                      AS subquery
                      [WITH CHECK OPTION [CONSTRAINT constraint]]
                         [WITH READ ONLY [CONSTRAINT constraint]];
----OR REPLACE 如果视图已经存在重新创建它
----FORCE 创建视图，而不管基表是否存在
----NOFORCE 只在基表存在的情况下创建视图(这是默认值)
----view 视图的名字
----alias 为由视图查询选择的表达式指定名字(别名的个数必须与由视图选择的表达式的个数匹配)
----subquery 是一个完整的SELECT语句(对于在SELECT列表中的字段你可以用别名)
----WITH CHECK OPTION 指定只有可访问的行在视图中才能被插入或修改
----constraint 为CHECK OPTION约束指定的名字
----WITH READ ONLY 确保在该视图中没有DML操作被执行

       创建视图的原则：定义一个视图的子查询可以包含复杂的SELECT语法，包括连分组和多个字查询；定义视图的子查询不能包含ORDER BY子句，当你从视图取回数据时可以指定ORDER BY子句；如果你没有为用WITH CHECK OPTION选项创建的视图指定一个约束名字，系统将以SYS_Cn格式指定一个默认的名字。

       2、管理视图

DROP VIEW view;      --view是视图的名字

       我们可以用DROP VIEW语句来删除视图。该语句从数据库中删除视图定义。删除视图不影响用于建立视图的基表。基于已删除视图上的其它视图或应用程序将无效。

       用OR REPLACE选项，允许创建视图时同名的视图已经存在，这样旧版本的视图会被替换，这意味着视图可以在不被删除、重新创建和再次授予对象权限的情况下修改。当在CREATE VIEW子句中指定列别名时，不要望了别名的列表顺序与子查询的列顺序一样。

oracle sql 基础（四）：数据定义语言（创建和管理表、视图）