触发器 n) 函数 从表 介绍 数据结构 enc 丢失
数据定义语言 (Data Definition Language, DDL) 是 SQL 语言集中负责数据结构定义与数据库对象定义的语言,由 CREATE、ALTER 与 DROP 三个语法所组成。数据库对象包括表、视图、索引、同义词、序列等 (TABLE/VIEW/INDEX/SYN/SEQUENCE)。我们先对创建和管理表和视图进行一个介绍。
一、创建和管理表
数据表是数据库中一个非常重要的对象,是其他对象的基础。可分为用户表和数据字典两类。
1、创建表(CREATE TABLE)
CREATE TABLE 语句创建表
- CREATE TABLEtable--table:表名 column:列名(columndatatype--列的数据类型和长度
- [DEFAULT expr] --指定默认值,防止插入时输入空值到列中
- [,...]);
最为常见的数据类型有三种,分别为字符型,数值型,日期型。
数据类型 | 说明 |
VARCHAR2(size) | 可变长度字符数据(必须指定最大字符数:最小字符数是 1;最大字符数是 4000) |
CHAR [(size)] | 固定长度字符数据,长度的大小以字节为单位 (默认和最小字符数为 1;最大字符数为 2000) |
NUMBER [(p,s)] | 数字,精度为 p,小数为 s (p 是数字总长度,s 是小数点右边的数字长度;p 的范围从 1 到 38,s 的范围从 - 84 到 127) |
DATE | 日期和时间值,从公元前 4712.1.1 到公元 9999.12.31 |
用子查询语法创建表(CTAS)
- CREATE TABLE table
- [(column, column...)]
- ASsubquery--使用子查询
如果给出了指定的列,列的数目必须等于子查询的 SELECT 列表的列数目。如果没有给出了指定的列,表的列名应和子查询中的列名是相同的。完整性规则不会被传递到新表中,仅列的数据类型被定义。
2、修改表(ALTER TABLE)
- ALTER TABLEtableADD(column datatype[DEFAULT expr][,column datatype]...);--添加列
- ALTERTABLE tableMODIFY (column datatype[DEFAULT expr][,column datatype]...);--修改列
- ALTERTABLE tableDROP (column);--删除列ALTERTABLE table SET UNUSED(column); --隐藏列ALTERTABLE tableDROP UNUSED COLUMNS;--删除隐藏的列
添加列时不能指定新添加的列出的位置,新列将成为最后一列;修改列时可以修改数据类型大小和默认值,当列中只包含空值时才能减少一个列的宽度和改变数据类型,对默认值的改变仅改变影响以后插入的列;删除列时一次只能有一列被删除。表被修改后必须至少保留一列。一旦一列被删除,它不能再恢复;当一列从表中被删除时,该表中任何其他的被用 SET UNUSED 选项标记列也被删除,设置 UNUSED 的作用是为了在 cpu、内存等资源不充足的时候,先做上 UNUSED 标记再等数据库资源空闲的时候用 DROP UNUSED COLUMNS 删除。
3、删除表(DROP TABLE)
- DROP TABLEtable [PURGE];
当你删除一个表时,数据库丢失表中所有的数据,并且所有与其相关的索引也被删除,任何视图和同义词被保留但无效。ORACLE 10g 中由于引入了回收站,因此要彻底删除一个表需要加 PURGE 关键字。
4、截断表(TRUNCATE TABLE)
- TRUNCATE TABLEtable;
截断表用于从表中删除所有的行,并且释放该表所使用的存储空间。DELETE 语句也可以从表中删除所有的行,但它不能释放存储空间。TRUNCATE 命令更快一些,原因如下:TRUNCATE 语句是数据定义 (DDL) 语句,不产生回滚信息;截断一个表不触发表的删除触发器。
二、定义和管理约束
约束 (constraints) 用来防止无效数据输入到表中。你可以使用约束做下面的事:在插入、更新行或者从表中删除行的时候强制表中的数据遵循规则;对于成功的操作,约束必须被满足,如果表之间有依赖关系,防止表的删除;为 Oracle 工具提供规则,例如 Oracle Developer。
约束可以在创建表时定义,也可以在表创建之后定义。你可以用 USER_CONSTRAINTS 和 USER_CONS_COLUMNS 数据字典表查看对一个表约束的定义。
1、定义约束
- CREATE TABLE [schema.]table
- (columndatatype[DEFAULT expr] --数据类型
- [column [CONSTRAINT constraint_name]constraint_type],...--列级约束
- [column,...[CONSTRAINT constraint_name]constraint_type] (column,...) --表级约束
- [,...]);
约束通常在创建表的同时被创建。在表创建后约束能够被添加,并且约束可以可以被临时禁用。 约束可以在两个级别上定义:列级约束,只涉及一个单个的列,对于该列用规范定义,能够定义完整性约束的任何类型;表级约束,涉及一个或多个列,表中的列被分别定义,除了 NOT NULL,能够定义任意约束。
2、约束类型
约束名称 | 说明 |
NOT NULL 约束 | 确保列无不包含空值。在默认情况下,列没有 NOT NULL 约束,可以包含空值。 |
UNIQUE 约束 | 完整性约束,要求列或者列的组合中 (键) 的每个值是唯一的,既在表中指定的列或列组合中不能有两行有相同的值。定义 UNIQUE 键约束的列 (或列组合) 被称为唯一键 (unique key)。因为空不等于任何事。在一个列 (或者在一个复合 UNIQUE 键中的所有列) 中的空总是满足 UNIQUE 约束。 |
PRIMARY KEY 约束 | 为表创建一个主键。每个表只能创建一个主键。PRIMARY KEY 约束是表中的对行唯一标识的一个列或者多个列的组合,该约束强制列或列组合的唯一性,并且确保作为主键一部分的列不能包含空值。PRIMARY KEY 约束既可以定义在列级也可以定义在表级。用表级定义创建一个组合 PRIMARY KEY。一个表只能有一个 PRIMARY KEY 约束,但可以有多个 UNIQUE 约束。 |
FOREIGN KEY 约束 | 引用完整性约束,指明一个列或者列的组合作为一个外键,并且在相同表或者不同表的主键或者唯一键和外键之间建立一个关系。前提是被引用的外键必须是另外一个表的主键。 |
CHECK 约束 | 定义一个每行都必须满足的条件,该条件可以用和查询条件一样的结构。 |
3、管理约束
- ALTER TABLE table ADD [CONSTRAINT constraint]type (column);--添加约束
- ALTER TABLE table DROP PRIMARY KEY|UNIQUE(column)|CONSTRAINT constraint [CASCADE];--删除约束
- ALTER TABLE tableDISABLECONSTRAINT constraint [CASCADE];--禁用约束
- ALTER TABLE tableENABLECONSTRAINT 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 基础(四):数据定义语言(创建和管理表、视图)
来源: http://www.bubuko.com/infodetail-2048289.html