Oracle DB 使用DDL语句创建和管理表.docx

1、Oracle DB 使用DDL语句创建和管理表 对主要的数据库对象进行分类 查看表结构 列举列可以使用的数据类型 创建简单的表 说明创建表时如何创建约束条件 描述方案对象如何工作 数据库对象 命名规则 CREATE TABLE语句： 访问另一个用户的表 DEFAULT选项 数据类型 约束条件概览：NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK约束条件 使用子查询创建表 ALTER TABLE 只读表 DROP TABLE语句 数据库对象Oracle DB 可以包含多种数据结构。在数据库设计中应对每种结构加以概述，以便可在数据库开发的构建阶段创建数据库

2、结构。 表：用于存储数据 视图：一个或多个表中数据的子集 序列：用于生成数字值 索引：提高某些查询的性能 同义词：给出对象的替代名称Oracle 表结构 在任何时候都可以创建表，即使用户正在使用数据库时也是如此。 无需指定表的大小。表的大小最终由全部分配给数据库的空间量确定。但是，需要估计一个表将要使用的空间大小，这一点非常重要。 可以联机修改表结构。 命名规则表名和列名必须满足以下条件： 以字母开头 长度为1-30 个字符 只包含A-Z、a-z、0-9、_、$ 和# 不与同一用户拥有的其它对象重名 不是Oracle Server 的保留字应根据命名任意Oracle DB 对象的标准规则来命名

3、数据库表和列： 表名和列名必须以字母开头，长度必须为1-30 个字符。 名称中只能包含字符A-Z、a-z、0-9、_（下划线）、$ 和#（这两个字符是合法字符，但建议不要使用它们）。 不能与同一Oracle Server 用户拥有的其它对象重名。 不能是Oracle Server 的保留字。- 还可以使用加引号的标识符来表示对象名称。加引号的标识符以双引号(“”) 开始和结束。如果使用加双引号的标识符为方案命名，那么，只要引用该对象，就必须使用双引号。加引号的标识符可以是保留字，不过建议不要这样做。命名准则对于表和其它数据库对象，应使用描述性名称。注：名称不区分大小写，例如，EMPLOYEES

4、与eMPloyees或eMpLOYEES被认为是同一名称。但是，加引号的标识符区分大小写。 CREATE TABLE语句 必须具有以下项才能使用此语句： CREATE TABLE权限 一个存储区CREATE TABLE schema.table(column datatypeDEFAULT expr, .); 可以指定： 表名称 列名、列数据类型和列大小通过执行SQL CREATE TABLE语句可以创建用于存储数据的表。此语句是一条DDL 语句，DDL 语句是SQL 语句的子集，用于创建、修改或删除Oracle DB 结构。这些语句会对数据库产生直接的影响，它们还会在数据字典中记录信息。要创

5、建一个表，用户必须具有CREATE TABLE权限和一个用于在其中创建对象的存储区。数据库管理员(DBA) 可以使用数据控制语言(DCL) 语句为用户授权。在该语法中：schema 与所有者的姓名相同table 是表名称DEFAULT expr 指定当INSERT语句中省略了值时所使用的默认值语句column 是列名称datatype 是列的数据类型和长度 引用另一个用户的表 在用户方案中没有属于其他用户的表。 应使用所有者姓名作为那些表的前缀。方案是由数据或方案对象构成的一组逻辑结构。方案由数据库用户拥有，而且与该用户具有相同的名称。每个用户都拥有一个方案。方案对象可使用SQL 来创建和操作

6、；方案对象包括表、视图、同义词、序列、存储过程、索引、集群和数据库链接。如果某个表不属于该用户，则必须将所有者的姓名作为该表的前缀。例如，假设存在名为USERA和USERB的两个方案，每个方案都有一个EMPLOYEES表，如果USERA要访问属于USERB的EMPLOYEES表，USERA就必须将USERB方案名作为该表名的前缀：SELECT *FROM userb.employees;如果USERB要访问属于USERA的EMPLOYEES表，USERB就必须将USERA的方案名作为该表名的前缀：SELECT *FROM usera.employees; DEFAULT选项 指定插入过程中列的

7、默认值。. hire_date DATE DEFAULT SYSDATE, . 文字值、表达式或SQL 函数都是合法值。 其它列的名称或假列是非法值。 默认数据类型必须与列的数据类型相匹配。CREATE TABLE hire_dates(id NUMBER(8),hire_date DATE DEFAULT SYSDATE);在定义表时，可以使用DEFAULT选项指定列的默认值。当插入的行中没有某列的相应值时，使用此选项可以防止将空值输入到列中。默认值可以是文字值、表达式或SQL 函数（例如SYSDATE或USER），但是该值不能是其它列或假列的名称（例如NEXTVAL或CURRVAL）。默认

8、表达式必须与列的数据类型相匹配。请看如下示例：INSERT INTO hire_dates values(45, NULL);以上语句将插入空值而非默认值。INSERT INTO hire_dates(id) values(35);以上语句将在HIRE_DATE列中插入SYSDATE。 创建表 创建表：CREATE TABLE dept(deptno NUMBER(2),dname VARCHAR2(14),loc VARCHAR2(13),create_date DATE DEFAULT SYSDATE); 确认表创建：DESCRIBE dept示例中创建的DEPT表包含以下四列：DEPTN

9、O、DNAME、LOC和CREATE_DATE。CREATE_DATE列具有默认值。如果没有为INSERT语句提供值，则会自动插入系统日期。要确认该表是否已创建，请运行DESCRIBE命令。因为创建表的命令是一条DDL 语句，所以在执行该语句后会自动提交。注：可以通过查询数据字典来查看你拥有的表列表。例如：select table_name from user_tables使用数据字典视图，还可以查找有关其它数据库对象（例如视图、索引等）的信息。 数据类型在指定表的列时，需要提供列的数据类型。下面是几种可用的数据类型：准则 在使用子查询创建表时不复制LONG列。 不能在GROUP BY或ORD

10、ER BY子句中包括LONG列。 每个表只能使用一个LONG列。 不能对LONG列定义约束条件。 可以要求使用CLOB列，而不是LONG列。 日期时间数据类型注：可以在Oracle9i和更高版本中使用这些日期时间数据类型。 包括约束条件 包括约束条件 约束条件用于在表级别强制执行各种规则。 约束条件用于防止在存在相关性时删除表。 下列约束条件类型有效： NOT NULL ：指定该列不能包含空值 UNIQUE :定一个列或列组合的值对于表中的所有行必须是唯一的 PRIMARY KEY :唯一地标识表中的每一行 FOREIGN KEY :在该列和所引用表的列之间建立联系后强制实施引用完整性，这样其

11、中一个表的值与另一个表中的值相匹配 CHECK :指定必须为真的条件Oracle Server 使用约束条件来防止将无效的数据输入到表中。可以使用约束条件完成以下任务： 在表中插入、更新或删除某一行时，对表中的数据强制执行各种规则。必须满足约束条件，操作才会成功。 防止当某个表与其它表存在相关性时删除该表。 为Oracle 工具（例如Oracle Developer）提供规则。 约束条件准则 可以为约束条件命名，也可以由Oracle Server 使用SYS_Cn格式生成一个名称。 可采用以下任何一种方式创建约束条件： 创建表的同时创建约束条件 创建表以后 可以在列或表级别定义约束条件。 可以

12、在数据字典中查看约束条件。所有约束条件都存储在数据字典中。如果为约束条件指定了一个有意义的名称，则引用时较为容易。约束条件名称必须遵循标准对象命名规则，但是该名称不能与同一用户的另一对象名称相同。如果你没有对约束条件命名，Oracle Server 就会按照SYS_Cn格式生成一个名称，其中n是一个整数，这样约束条件名称是唯一的。既可以在创建表的同时定义约束条件，也可以在创建表之后定义约束条件。你可以在列级别或表级别定义约束条件。从功能上来说，表级别约束条件与列级别约束条件的作用是相同的。 定义约束条件 语法：CREATE TABLE schema.table(column datatypeD

13、EFAULT exprcolumn_constraint,.table_constraint,.); 列级别约束条件语法：columnCONSTRAINT constraint_name constraint_type, 表级别约束条件语法：column,.CONSTRAINT constraint_name constraint_type(column, .),示例给出了在创建表时定义约束条件的语法。你可以在列级别或表级别创建约束条件。定义列时会包括在列级别定义的约束条件。在表定义结束时定义表级别约束条件，必须在一组括号中引用应用了约束条件的列或列组合。这二者主要在语法上有所不同；此外从功能

14、上来说，列级别约束条件和表级别约束条件的作用是相同的。必须在列级别定义NOT NULL约束条件。必须在表级别定义适用于多个列的约束条件。在该语法中：schema ：与所有者的姓名相同table ：是表名称DEFAULT expr ：指定当INSERT语句中省略了值时所使用的默认值column： 是列名称datatype ：是列的数据类型和长度column_constraint：是作为列定义一部分的完整性约束条件table_constraint ：是作为表定义一部分的完整性约束条件 列级别约束条件示例：CREATE TABLE employees(employee_id NUMBER(6)CON

15、STRAINT emp_emp_id_pk PRIMARY KEY,first_name VARCHAR2(20),.); 表级别约束条件示例：CREATE TABLE employees(employee_id NUMBER(6),first_name VARCHAR2(20),.job_id VARCHAR2(10) NOT NULL,CONSTRAINT emp_emp_id_pkPRIMARY KEY (EMPLOYEE_ID);通常会在创建表的同时创建约束条件。可以在创建表之后将约束条件添加到表，也可以临时禁用约束条件。示例中都对EMPLOYEES表的EMPLOYEE_ID列创建了主

16、键约束条件。1.第一个示例使用列级别语法定义约束条件。2.第二个示例使用表级别语法定义约束条件。 NOT NULL约束条件NOT NULL约束条件可以确保某列不包含空值。默认情况下，没有NOT NULL约束条件的列可以包含空值。必须在列级别定义NOT NULL约束条件。在EMPLOYEES表中，EMPLOYEE_ID列继承了NOT NULL约束条件，因为该列已定义为主键。否则，在LAST_NAME、EMAIL、HIRE_DATE和JOB_ID列上强制实施NOT NULL约束条件。 UNIQUE约束条件UNIQUE关键字完整性约束条件要求一列或一组列（键）中的每个值必须是唯一的，即在指定的列或一

17、组列中，表的任意两行无重复值。UNIQUE关键字约束条件的定义中包括的列（或一组列）被称为唯一关键字。如果UNIQUE约束条件由多个列组成，则该组列被称为组合唯一关键字。UNIQUE约束条件允许输入空值，除非你还为同一列定义了NOT NULL约束条件。实际上，因为空值被认为不等于任何值，所以任意数量的行都可以在没有NOT NULL约束条件的列中包括空值。一个列（或组合UNIQUE关键字的所有列）中的空值总是满足UNIQUE约束条件。注：由于多个列上UNIQUE约束条件的搜索机制所致，在部分空值组合UNIQUE关键字约束条件的非空列中不能有相同的值。可以在表级别或列级别定义：CREATE TAB

18、LE employees(employee_id NUMBER(6),last_name VARCHAR2(25) NOT NULL,email VARCHAR2(25),salary NUMBER(8,2),commission_pct NUMBER(2,2),hire_date DATE NOT NULL,.CONSTRAINT emp_email_uk UNIQUE(email);可以在列级别或表级别定义UNIQUE约束条件。如果要创建一个组合唯一关键字，则可在表级别定义该约束条件。如果不能使用单个属性来唯一地标识某一行，则需定义组合关键字。在这种情况下，可以创建由两个或两个以上列组成的

19、唯一关键字，其组合值总是唯一的，可用于标识行。示例中将UNIQUE约束条件应用于EMPLOYEES表的EMAIL列。该约束条件的名称为EMP_EMAIL_UK。注：Oracle Server 通过对一个或多个唯一关键字列隐式创建一个唯一索引来强制实现UNIQUE约束条件。 PRIMARY KEY约束条件PRIMARY KEY约束条件用于创建创建表的主键。只能为每一个表创建一个主键。PRIMARY KEY约束条件是唯一标识表中每一行的一个列或一组列。此约束条件可以强制一个列或列组合是唯一的，还可以确保作为主键一部分的列不包含空值。注：因为唯一性是主键约束条件定义的一部分，所以Oracle Ser

20、ver 通过对一个或多个主键列隐式创建一个唯一索引来强制实现唯一性。 FOREIGN KEY约束条件FOREIGN KEY（或引用完整性）约束条件指定一个列或列组合作为外键，并建立与同一表或不同表中主键或唯一关键字的关系。示例中，DEPARTMENT_ID已被定义为EMPLOYEES表（相关表或子表）中的外键，它引用DEPARTMENTS表（被引用表或父表）的DEPARTMENT_ID列。准则 外键值必须与父表中的现有值相匹配，或为NULL。 外键取决于数据值，外键是纯逻辑指针，而不是物理指针。可以在表级别或列级别定义：CREATE TABLE employees(employee_id NU

21、MBER(6),last_name VARCHAR2(25) NOT NULL,email VARCHAR2(25),salary NUMBER(8,2),commission_pct NUMBER(2,2),hire_date DATE NOT NULL,.department_id NUMBER(4),CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)REFERENCES departments(department_id),CONSTRAINT emp_email_uk UNIQUE(email);可以在列级别或表级别定义FOREIGN

22、KEY约束条件。必须使用表级别定义来创建组合外键。示例中使用表级别语法，对EMPLOYEES表的DEPARTMENT_ID列定义一个FOREIGN KEY约束条件。该约束条件的名称为EMP_DEPT_FK。如果约束条件只是针对单个列，则也可以在列级别定义外键。语法上的不同之处在于没有出现关键字FOREIGN KEY。例如：CREATE TABLE employees(.department_id NUMBER(4) CONSTRAINT emp_deptid_fkREFERENCES departments(department_id),.) FOREIGN KEY约束条件：关键字外键是在子表

23、中定义的，而包含被引用列的表是父表。外键是使用以下关键字的组合定义的： FOREIGN KEY用于在表约束条件级别定义子表中的列。 REFERENCES用于标识父表中的表和列。 ON DELETE CASCADE指出在删除父表中的行时，还删除子表中的相关行。 ON DELETE SET NULL指出在删除父表中的行时，将外键值设为空值。默认行为被称为限制规则，该规则可禁止更新或禁止删除被引用的数据。在没有ON DELETE CASCADE或ON DELETE SET NULL选项时，如果在子表中引用父表中的一行，则不能删除该行。 CHECK约束条件 定义每行都必须满足的一个条件 以下表达式是不

24、允许的： 引用CURRVAL、NEXTVAL、LEVEL和ROWNUM假列的表达式 调用SYSDATE、UID、USER和USERENV函数的表达式 引用其它行中的其它值的查询., salary NUMBER(2)CONSTRAINT emp_salary_minCHECK (salary 0),.CHECK约束条件用于定义每行都必须满足的一个条件。该条件可以使用与查询条件相同的结构，但是以下项除外： 引用CURRVAL、NEXTVAL、LEVEL和ROWNUM假列的表达式 调用SYSDATE、UID、USER和USERENV函数的表达式 引用其它行中的其它值的查询一个列可以有多个CHECK约

25、束条件，这些约束条件将在其定义中引用该列。可以按需要，对一个列定义任意数量的CHECK约束条件。可以在列级别或表级别定义CHECK约束条件。CREATE TABLE employees(.salary NUMBER(8,2)CONSTRAINT emp_salary_minCHECK (salary 0),.示例中显示用于创建HR方案中EMPLOYEES表的语句CREATE TABLE employees( employee_id NUMBER(6)CONSTRAINT emp_employee_id PRIMARY KEY, first_name VARCHAR2(20), last_nam

26、e VARCHAR2(25)CONSTRAINT emp_last_name_nn NOT NULL, email VARCHAR2(25)CONSTRAINT emp_email_nn NOT NULLCONSTRAINT emp_email_uk UNIQUE, phone_number VARCHAR2(20), hire_date DATECONSTRAINT emp_hire_date_nn NOT NULL, job_id VARCHAR2(10)CONSTRAINT emp_job_nn NOT NULL, salary NUMBER(8,2)CONSTRAINT emp_sal

27、ary_ck CHECK (salary0), commission_pct NUMBER(2,2), manager_id NUMBER(6)CONSTRAINT emp_manager_fk REFERENCESemployees (employee_id), department_id NUMBER(4)CONSTRAINT emp_dept_fk REFERENCESdepartments (department_id);通过以下查询表中哪列有哪些约束，上面创建表的语句对应下面的约束。hrTEST0924 l 1 select a.owner,a.TABLE_NAME,b.COLUMN

28、_NAME,a.CONSTRAINT_NAME,a.CONSTRAINT_TYPE,a.SEARCH_CONDITION 2 from USER_CONSTRAINTS a,USER_CONS_COLUMNS b 3* where a.CONSTRAINT_NAME=b.CONSTRAINT_NAME and A.table_name=EMPLOYEEShrTEST0924 /OWNER TABLE_NAME COLUMN_NAM CONSTRAINT_NAME C SEARCH_CONDITION- - - - - -HR EMPLOYEES SALARY EMP_SALARY_MIN C salary 0HR EMPLOYEES EMAIL EMP_EMAIL_UK UHR EMPLOYEES EMPLOYEE_ID EMP_EMP_ID_PK P HR EMPLOYEES DEPARTMENT_ID EMP_DEPT_FK R HR EMPLOYEES JOB_ID EMP_JOB_FK RHR EMPLOYEES