Oracle DB 使用DDL语句创建和管理表.docx
- 文档编号:29149088
- 上传时间:2023-07-20
- 格式:DOCX
- 页数:21
- 大小:23.74KB
Oracle DB 使用DDL语句创建和管理表.docx
《Oracle DB 使用DDL语句创建和管理表.docx》由会员分享,可在线阅读,更多相关《Oracle DB 使用DDL语句创建和管理表.docx(21页珍藏版)》请在冰豆网上搜索。
OracleDB使用DDL语句创建和管理表
•对主要的数据库对象进行分类
•查看表结构
•列举列可以使用的数据类型
•创建简单的表
•说明创建表时如何创建约束条件
•描述方案对象如何工作
•数据库对象
–命名规则
•CREATETABLE语句:
–访问另一个用户的表
–DEFAULT选项
•数据类型
•约束条件概览:
NOTNULL、UNIQUE、PRIMARYKEY、
FOREIGNKEY、CHECK约束条件
•使用子查询创建表
•ALTERTABLE
–只读表
•DROPTABLE语句
∙数据库对象
OracleDB可以包含多种数据结构。
在数据库设计中应对每种结构加以概述,以便可在数据库开发的构建阶段创建数据库结构。
•表:
用于存储数据
•视图:
一个或多个表中数据的子集
•序列:
用于生成数字值
•索引:
提高某些查询的性能
•同义词:
给出对象的替代名称
Oracle表结构
•在任何时候都可以创建表,即使用户正在使用数据库时也是如此。
•无需指定表的大小。
表的大小最终由全部分配给数据库的空间量确定。
但是,需要估计一个表将要使用的空间大小,这一点非常重要。
•可以联机修改表结构。
∙命名规则
表名和列名必须满足以下条件:
•以字母开头
•长度为1-30个字符
•只包含A-Z、a-z、0-9、_、$和#
•不与同一用户拥有的其它对象重名
•不是OracleServer的保留字
应根据命名任意OracleDB对象的标准规则来命名数据库表和列:
•表名和列名必须以字母开头,长度必须为1-30个字符。
•名称中只能包含字符A-Z、a-z、0-9、_(下划线)、$和#(这两个字符是合法字符,但建议不要使用它们)。
•不能与同一OracleServer用户拥有的其它对象重名。
•不能是OracleServer的保留字。
-还可以使用加引号的标识符来表示对象名称。
加引号的标识符以双引号(“”)开始和结束。
如果使用加双引号的标识符为方案命名,那么,只要引用该对象,就必
须使用双引号。
加引号的标识符可以是保留字,不过建议不要这样做。
命名准则
对于表和其它数据库对象,应使用描述性名称。
注:
名称不区分大小写,例如,EMPLOYEES与eMPloyees或eMpLOYEES被认为是同一名称。
但是,加引号的标识符区分大小写。
∙CREATETABLE语句
•必须具有以下项才能使用此语句:
–CREATETABLE权限
–一个存储区
CREATETABLE[schema.]table
(columndatatype[DEFAULTexpr][,...]);
•可以指定:
–表名称
–列名、列数据类型和列大小
通过执行SQLCREATETABLE语句可以创建用于存储数据的表。
此语句是一条DDL语句,DDL语句是SQL语句的子集,用于创建、修改或删除OracleDB结构。
这些语句会对数据库产生直接的影响,它们还会在数据字典中记录信息。
要创建一个表,用户必须具有CREATETABLE权限和一个用于在其中创建对象的存储区。
数据库管理员(DBA)可以使用数据控制语言(DCL)语句为用户授权。
在该语法中:
schema与所有者的姓名相同
table是表名称
DEFAULTexpr指定当INSERT语句中省略了值时所使用的默认值语句
column是列名称
datatype是列的数据类型和长度
∙引用另一个用户的表
•在用户方案中没有属于其他用户的表。
•应使用所有者姓名作为那些表的前缀。
方案是由数据或方案对象构成的一组逻辑结构。
方案由数据库用户拥有,而且与该用户具有相同的名称。
每个用户都拥有一个方案。
方案对象可使用SQL来创建和操作;方案对象包括表、视图、同义词、序列、存储过程、索引、集群和数据库链接。
如果某个表不属于该用户,则必须将所有者的姓名作为该表的前缀。
例如,假设存在名为USERA和USERB的两个方案,每个方案都有一个EMPLOYEES表,如果USERA要访问属于USERB的EMPLOYEES表,USERA就必须将USERB方案名作为该表名的前缀:
SELECT* FROMuserb.employees;
如果USERB要访问属于USERA的EMPLOYEES表,USERB就必须将USERA的方案名作为该表名的前缀:
SELECT* FROMusera.employees;
∙DEFAULT选项
•指定插入过程中列的默认值。
...hire_dateDATEDEFAULTSYSDATE,...
•文字值、表达式或SQL函数都是合法值。
•其它列的名称或假列是非法值。
•默认数据类型必须与列的数据类型相匹配。
CREATETABLEhire_dates (id NUMBER(8), hire_dateDATEDEFAULTSYSDATE);
在定义表时,可以使用DEFAULT选项指定列的默认值。
当插入的行中没有某列的相应值时,使用此选项可以防止将空值输入到列中。
默认值可以是文字值、表达式或SQL函数(例如SYSDATE或USER),但是该值不能是其它列或假列的名称(例如NEXTVAL或CURRVAL)。
默认表达式必须与列的数据类型相匹配。
请看如下示例:
INSERTINTOhire_datesvalues(45,NULL);
以上语句将插入空值而非默认值。
INSERTINTOhire_dates(id)values(35);
以上语句将在HIRE_DATE列中插入SYSDATE。
∙创建表
•创建表:
CREATETABLEdept
(deptno NUMBER
(2),
dname VARCHAR2(14),
loc VARCHAR2(13),
create_dateDATEDEFAULTSYSDATE);
•确认表创建:
DESCRIBEdept
示例中创建的DEPT表包含以下四列:
DEPTNO、DNAME、LOC和CREATE_DATE。
CREATE_DATE列具有默认值。
如果没有为INSERT语句提供值,则会自动插入系统日期。
要确认该表是否已创建,请运行DESCRIBE命令。
因为创建表的命令是一条DDL语句,所以在执行该语句后会自动提交。
注:
可以通过查询数据字典来查看你拥有的表列表。
例如:
selecttable_namefromuser_tables
使用数据字典视图,还可以查找有关其它数据库对象(例如视图、索引等)的信息。
∙数据类型
在指定表的列时,需要提供列的数据类型。
下面是几种可用的数据类型:
准则
•在使用子查询创建表时不复制LONG列。
•不能在GROUPBY或ORDERBY子句中包括LONG列。
•每个表只能使用一个LONG列。
•不能对LONG列定义约束条件。
•可以要求使用CLOB列,而不是LONG列。
∙日期时间数据类型
注:
可以在Oracle9i和更高版本中使用这些日期时间数据类型。
∙包括约束条件
∙包括约束条件
•约束条件用于在表级别强制执行各种规则。
•约束条件用于防止在存在相关性时删除表。
•下列约束条件类型有效:
–NOTNULL:
指定该列不能包含空值
–UNIQUE:
定一个列或列组合的值对于表中的所有行必须是唯一的
–PRIMARYKEY:
唯一地标识表中的每一行
–FOREIGNKEY:
在该列和所引用表的列之间建立联系后强制实施引用完整性,这样其中一个表的值与另一个表中的值相匹配
–CHECK:
指定必须为真的条件
OracleServer使用约束条件来防止将无效的数据输入到表中。
可以使用约束条件完成以下任务:
•在表中插入、更新或删除某一行时,对表中的数据强制执行各种规则。
必须满足约束条件,操作才会成功。
•防止当某个表与其它表存在相关性时删除该表。
•为Oracle工具(例如OracleDeveloper)提供规则。
∙约束条件准则
•可以为约束条件命名,也可以由OracleServer使用SYS_Cn格式生成一个名称。
•可采用以下任何一种方式创建约束条件:
–创建表的同时创建约束条件
–创建表以后
•可以在列或表级别定义约束条件。
•可以在数据字典中查看约束条件。
所有约束条件都存储在数据字典中。
如果为约束条件指定了一个有意义的名称,则引用时较为容易。
约束条件名称必须遵循标准对象命名规则,但是该名称不能与同一用户的另一对象名称相同。
如果你没有对约束条件命名,OracleServer就会按照SYS_Cn格式生成一个名称,其中n是一个整数,这样约束条件名称是唯一的。
既可以在创建表的同时定义约束条件,也可以在创建表之后定义约束条件。
你可以在列级别或表级别定义约束条件。
从功能上来说,表级别约束条件与列级别约束条件的作用是相同的。
∙定义约束条件
•语法:
CREATETABLE[schema.]table
(columndatatype[DEFAULTexpr]
[column_constraint],
...
[table_constraint][,...]);
•列级别约束条件语法:
column[CONSTRAINTconstraint_name]constraint_type,
•表级别约束条件语法:
column,...
[CONSTRAINTconstraint_name]constraint_type
(column,...),
示例给出了在创建表时定义约束条件的语法。
你可以在列级别或表级别创建约束条件。
定义列时会包括在列级别定义的约束条件。
在表定义结束时定义表级别约束条件,必须在一组括号中引用应用了约束条件的列或列组合。
这二者主要在语法上有所不同;此外从功能上来说,列级别约束条件和表级别约束条件的作用是相同的。
必须在列级别定义NOTNULL约束条件。
必须在表级别定义适用于多个列的约束条件。
在该语法中:
schema:
与所有者的姓名相同
table:
是表名称
DEFAULTexpr:
指定当INSERT语句中省略了值时所使用的默认值
column:
是列名称
datatype:
是列的数据类型和长度
column_constraint:
是作为列定义一部分的完整性约束条件
table_constraint:
是作为表定义一部分的完整性约束条件
•列级别约束条件示例:
CREATETABLEemployees(
employee_idNUMBER(6) CONSTRAINTemp_emp_id_pkPRIMARYKEY,
first_name VARCHAR2(20),
...);
•表级别约束条件示例:
CREATETABLEemployees(
employee_idNUMBER(6),
first_name VARCHAR2(20),
...
job_idVARCHAR2(10)NOTNULL,
CONSTRAINTemp_emp_id_pk PRIMARYKEY(EMPLOYEE_ID));
通常会在创建表的同时创建约束条件。
可以在创建表之后将约束条件添加到表,也可以临时禁用约束条件。
示例中都对EMPLOYEES表的EMPLOYEE_ID列创建了主键约束条件。
1.第一个示例使用列级别语法定义约束条件。
2.第二个示例使用表级别语法定义约束条件。
∙NOTNULL约束条件
NOTNULL约束条件可以确保某列不包含空值。
默认情况下,没有NOTNULL约束条件的列可以包含空值。
必须在列级别定义NOTNULL约束条件。
在EMPLOYEES表中,EMPLOYEE_ID列继承了NOTNULL约束条件,因为该列已定义为主键。
否则,在LAST_NAME、EMAIL、HIRE_DATE和JOB_ID列上强制实施NOTNULL约束条件。
∙UNIQUE约束条件
UNIQUE关键字完整性约束条件要求一列或一组列(键)中的每个值必须是唯一的,即在指定的列或一组列中,表的任意两行无重复值。
UNIQUE关键字约束条件的定义中包括的列(或一组列)被称为唯一关键字。
如果UNIQUE约束条件由多个列组成,则该组列被称为组合唯一关键字。
UNIQUE约束条件允许输入空值,除非你还为同一列定义了NOTNULL约束条件。
实际上,因为空值被认为不等于任何值,所以任意数量的行都可以在没有NOTNULL约束条件的列中包括空值。
一个列(或组合UNIQUE关键字的所有列)中的空值总是满足UNIQUE约束条件。
注:
由于多个列上UNIQUE约束条件的搜索机制所致,在部分空值组合UNIQUE关键字约束条件的非空列中不能有相同的值。
可以在表级别或列级别定义:
CREATETABLEemployees(
employee_id NUMBER(6),
last_name VARCHAR2(25)NOTNULL,
email VARCHAR2(25),
salary NUMBER(8,2),
commission_pctNUMBER(2,2),
hire_date DATENOTNULL,
...
CONSTRAINTemp_email_ukUNIQUE(email));
可以在列级别或表级别定义UNIQUE约束条件。
如果要创建一个组合唯一关键字,则可在表级别定义该约束条件。
如果不能使用单个属性来唯一地标识某一行,则需定义组合关键字。
在这种情况下,可以创建由两个或两个以上列组成的唯一关键字,其组合值总是唯一的,可用于标识行。
示例中将UNIQUE约束条件应用于EMPLOYEES表的EMAIL列。
该约束条件的名称为EMP_EMAIL_UK。
注:
OracleServer通过对一个或多个唯一关键字列隐式创建一个唯一索引来强制实现UNIQUE约束条件。
∙PRIMARYKEY约束条件
PRIMARYKEY约束条件用于创建创建表的主键。
只能为每一个表创建一个主键。
PRIMARYKEY约束条件是唯一标识表中每一行的一个列或一组列。
此约束条件可以强制一个列或列组合是唯一的,还可以确保作为主键一部分的列不包含空值。
注:
因为唯一性是主键约束条件定义的一部分,所以OracleServer通过对一个或多个主键列隐式创建一个唯一索引来强制实现唯一性。
∙FOREIGNKEY约束条件
FOREIGNKEY(或引用完整性)约束条件指定一个列或列组合作为外键,并建立与同一表或不同表中主键或唯一关键字的关系。
示例中,DEPARTMENT_ID已被定义为EMPLOYEES表(相关表或子表)中的外键,它引用DEPARTMENTS表(被引用表或父表)的DEPARTMENT_ID列。
准则
•外键值必须与父表中的现有值相匹配,或为NULL。
•外键取决于数据值,外键是纯逻辑指针,而不是物理指针。
可以在表级别或列级别定义:
CREATETABLEemployees(
employee_idNUMBER(6),
last_nameVARCHAR2(25)NOTNULL,
emailVARCHAR2(25),
salaryNUMBER(8,2),
commission_pctNUMBER(2,2),
hire_dateDATENOTNULL,
...
department_idNUMBER(4),
CONSTRAINTemp_dept_fkFOREIGNKEY(department_id)
REFERENCESdepartments(department_id),
CONSTRAINTemp_email_ukUNIQUE(email));
可以在列级别或表级别定义FOREIGNKEY约束条件。
必须使用表级别定义来创建组合外键。
示例中使用表级别语法,对EMPLOYEES表的DEPARTMENT_ID列定义一个FOREIGNKEY约束条件。
该约束条件的名称为EMP_DEPT_FK。
如果约束条件只是针对单个列,则也可以在列级别定义外键。
语法上的不同之处在于没有出现关键字FOREIGNKEY。
例如:
CREATETABLEemployees
(...
department_idNUMBER(4)CONSTRAINTemp_deptid_fk
REFERENCESdepartments(department_id),
...
)
∙FOREIGNKEY约束条件:
关键字
外键是在子表中定义的,而包含被引用列的表是父表。
外键是使用以下关键字的组合定义的:
•FOREIGNKEY用于在表约束条件级别定义子表中的列。
•REFERENCES用于标识父表中的表和列。
•ONDELETECASCADE指出在删除父表中的行时,还删除子表中的相关行。
•ONDELETESETNULL指出在删除父表中的行时,将外键值设为空值。
默认行为被称为限制规则,该规则可禁止更新或禁止删除被引用的数据。
在没有ONDELETECASCADE或ONDELETESETNULL选项时,如果在子表中引用父表中的一行,则不能删除该行。
∙CHECK约束条件
•定义每行都必须满足的一个条件
•以下表达式是不允许的:
–引用CURRVAL、NEXTVAL、LEVEL和ROWNUM假列的表达式
–调用SYSDATE、UID、USER和USERENV函数的表达式
–引用其它行中的其它值的查询
...,salaryNUMBER
(2)
CONSTRAINTemp_salary_min CHECK(salary>0),...
CHECK约束条件用于定义每行都必须满足的一个条件。
该条件可以使用与查询条件相同的结构,但是以下项除外:
•引用CURRVAL、NEXTVAL、LEVEL和ROWNUM假列的表达式
•调用SYSDATE、UID、USER和USERENV函数的表达式
•引用其它行中的其它值的查询
一个列可以有多个CHECK约束条件,这些约束条件将在其定义中引用该列。
可以按需要,对一个列定义任意数量的CHECK约束条件。
可以在列级别或表级别定义CHECK约束条件。
CREATETABLEemployees
(...
salaryNUMBER(8,2) CONSTRAINTemp_salary_min CHECK(salary>0),
...
示例中显示用于创建HR方案中EMPLOYEES表的语句
CREATETABLEemployees
(employee_idNUMBER(6)
CONSTRAINTemp_employee_idPRIMARYKEY
first_nameVARCHAR2(20)
last_nameVARCHAR2(25)
CONSTRAINTemp_last_name_nnNOTNULL
emailVARCHAR2(25)
CONSTRAINTemp_email_nnNOTNULL
CONSTRAINTemp_email_ukUNIQUE
phone_numberVARCHAR2(20)
hire_dateDATE
CONSTRAINTemp_hire_date_nnNOTNULL
job_idVARCHAR2(10)
CONSTRAINTemp_job_nnNOTNULL
salaryNUMBER(8,2)
CONSTRAINTemp_salary_ckCHECK(salary>0)
commission_pctNUMBER(2,2)
manager_idNUMBER(6)
CONSTRAINTemp_manager_fkREFERENCES
employees(employee_id)
department_idNUMBER(4)
CONSTRAINTemp_dept_fkREFERENCES
departments(department_id));
通过以下查询表中哪列有哪些约束,上面创建表的语句对应下面的约束。
hr@TEST0924>l
1 selecta.owner,a.TABLE_NAME,b.COLUMN_NAME,a.CONSTRAINT_NAME,a.CONSTRAINT_TYPE,a.SEARCH_CONDITION
2 fromUSER_CONSTRAINTSa,USER_CONS_COLUMNSb
3*wherea.CONSTRAINT_NAME=b.CONSTRAINT_NAMEandA.table_name='EMPLOYEES'
hr@TEST0924>/
OWNER TABLE_NAME COLUMN_NAMCONSTRAINT_NAME CSEARCH_CONDITION
-----------------------------------------------------------------------------------------------------
HR EMPLOYEES SALARY EMP_SALARY_MIN C salary>0
HR EMPLOYEES EMAIL EMP_EMAIL_UK U
HR EMPLOYEES EMPLOYEE_ID EMP_EMP_ID_PK P
HR EMPLOYEES DEPARTMENT_IDEMP_DEPT_FK R
HR EMPLOYEES JOB_ID EMP_JOB_FK R
HR EMPLOYEES
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle DB 使用DDL语句创建和管理表 使用 DDL 语句 创建 管理