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
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1