4-Oracle数据定义语言(DDL)ppt课件

1、数据库开发技术第4章 Oracle数据定义言语DDL福建工程学院. 本章要点创建和管理Oracle表约束Oracle 数据库表的各种类型.4.1 创建和管理表 描画主要的数据库对象 表和列名的命名规那么 创建表 描画可用于定义列的数据类型 修正表定义 删除、更名、截断表.4.1.1 数据库对象对象描画表存储数据的根本单元，由行和列组成视图 来自一个或多个表的数据的子集序列 产生主关键字值索引提高某些查询的性能的数据构造同义词 赋予对象另外的名字.4.1.2 命名规那么和商定(表和列名)必需以字母开头长度为130 个字符只能包含 AZ, az, 09, _, $和 #对于同一个用户不能运用和其他

2、对象一样的名字不能是Oracle的保管字注：1. 名字不区分大小写，例如：EMP 、 eMP 或eMp指的是同一个表。 2. 在不同的表中命名一样的实体采用坚持一致的原那么，例如：部门编号在 EMP和 DEPT中都运用一样的名字DEPTNO。.4.1.3 CREATE TABLE语句您必需具备 :CREATE TABLE的权限一定的存贮空间您需求指定:表名列名、列的数据类型及列的宽度CREATE TABLE schema.table (column datatype DEFAULT expr;.4.1.3 创建表(DDL) SQL CREATE TABLE dept1 2(deptno NUM

3、BER(2), 3 dname VARCHAR2(14), 4 loc VARCHAR2(13);表已创建。创建表验证能否创建胜利(查看表构造) SQL DESCRIBE dept1 Name Null? Type - - - DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13).4.1.3 创建表: DEFAULT 选项指定在插入数据时，列的默许值 hiredate DATE DEFAULT SYSDATE, 合法的值可以是字面值、表达式或SQL函数非法的值是另一个列的称号或虚拟列默许值的数据类型必需和列的数据类型匹配.4

4、.1.4 援用其他用户的表属于其他用户而不属于当前用户的表需运用用户名作为表的前缀.4.1.5 Oracle的数据类型 以下是 Oracle 数据类型的类别：数据类型字符数值日期时间RAW/LONG RAWLOB.字符数据类型CHARVARCHAR2LONG当需求固定长度的字符串时，运用 CHAR 数据类型。CHAR 数据类型存储字母数字值。CHAR 数据类型的列长度可以是 1 到 2000 个字节。VARCHAR2数据类型支持可变长度字符串VARCHAR2数据类型存储字母数字值VARCHAR2数据类型的大小在1至4000个字节范围内LONG 数据类型存储可变长度字符数据LONG 数据类型最多

5、能存储 2GB4.1.5 Oracle的数据类型 .数值数据类型可以存储整数、浮点数和实数最高精度为 38 位数值数据类型的声明语法：NUMBER ( p, s)P表示精度，S表示小数点的位数日期时间数据类型存储日期和时间值，包括年、月、日，小时、分钟、秒主要的日期时间类型有：DATE - 存储日期和时间部分，准确到整个的秒TIMESTAMP - 存储日期、时间和时区信息，秒值准确到小数点后6位4.1.5 Oracle的数据类型 .RAW 数据类型用于存储二进制数据RAW 数据类型最多能存储 2000 字节LONG RAW 数据类型用于存储可变长度的二进制数据LONG RAW 数据类型最多能存

6、储 2 GBLOBLOB 称为“大对象数据类型，可以存储多达 4GB 的非构造化信息，例如声音剪辑和视频文件等LOB 数据类型允许对数据进展高效、随机、分段的访问BLOBCLOBBFILECLOB 即 Character LOB字符 LOB，它可以存储大量字符数据BLOB 即 Binary LOB二进制 LOB，可以存储较大的二进制对象，如图形、视频剪辑和声音文件 BFILE 即 Binary File二进制文件，它用于将二进制数据存储在数据库外部的操作系统文件中4.1.5 Oracle的数据类型 .4.1.5 数据类型 P50类型描画VARCHAR2(size)变长字符串(字符族):1400

7、0个字符CHAR(size) 固定长度字符串(字符族) ):12000个字符NUMBER(p,s) 定点小数值 , p为精度, s为标度(数字族)DATE 日期和时间值(日期族)LONG 可存放高达2GB的变长字符数据(字符族)CLOB 字符大对象可存放高达4GB的变长单字节字符数据RAW and LONG RAW 原始二进制数据BLOB 二进制大对象可存放高达4GB二进制数据BFILE援用一个外部的二进制文件;可存放高达 4GB数据 P50 表3-1.4.1.6 查询数据字典列出当前用户的表 列出当前用户不同的对象类型查询当前用户的表、视图、同义词及序列SQL SELECTtable_nam

8、e 2FROMuser_tables;SQL SELECTDISTINCT object_type 2FROM user_objects;SQL SELECT* 2FROMuser_catalog;.4.1.7 用子查询创建表 -利用现有的表创建表在 CREATE TABLE 语句的AS子句中运用子查询可以在创建表的同时插入数据所指定列的数量和类型必需和子查询中列的数量和类型相匹配由列名和默许值定义列CREATE TABLE table column(, column.)AS subquery;.4.1.7 运用子查询创建表 Name Null? Type - - - EMPNO NOT NU

9、LL NUMBER(4) ENAME VARCHAR2(10) ANNSAL NUMBER HIREDATE DATESQL DESCRIBE dept30SQL CREATE TABLE dept30 2AS 3SELECT empno, ename, sal*12 ANNSAL, hiredate 4FROM emp 5WHERE deptno = 30;表已创建。.4.1.8 ALTER TABLE 语句运用 ALTER TABLE 语句可以：添加或删除一个列修正已存在的列为新列定义默许值ALTER TABLE tableADD (column datatype DEFAULT expr

10、 , column datatype.);ALTER TABLE tableMODIFY (column datatype DEFAULT expr , column datatype.);.添加新列 DEPT30 EMPNO ENAME ANNSAL HIREDATE - - 7698BLAKE 3420001-MAY-81 7654MARTIN 1500028-SEP-81 7499ALLEN 1920020-FEB-81 7844TURNER 1800008-SEP-81.“在DEPT30 表中添加新列JOB DEPT30 EMPNO ENAME ANNSAL HIREDATE - -

11、7698BLAKE 3420001-MAY-81 7654MARTIN 1500028-SEP-81 7499ALLEN 1920020-FEB-81 7844TURNER 1800008-SEP-81. JOB JOB新列.添加新列运用 ADD 子句添加新列 columns. EMPNO ENAME ANNSAL HIREDATE JOB- - - - - 7698 BLAKE 34200 01-MAY-81 7654 MARTIN 15000 28-SEP-81 7499 ALLEN 19200 20-FEB-81 7844 TURNER 18000 08-SEP-81.6 rows se

12、lected.SQL ALTER TABLE dept30 2 ADD (job VARCHAR2(9);表已更改。新列成为表的最后一列.修正列可以修正列的数据类型、长度及默许值改动列的默许值仅仅影响后来插入的新记录ALTER TABLEdept30MODIFY(ename VARCHAR2(15); -原来是VARCHAR2(10)表已更改。.删除一列(Oracle8i以上版本)从数据表中删除一列至少还必需剩下一列ALTER TABLEdept30DROP COLUMN ename; -一次只允许删除一列表已更改。.4.1.9 删除表表中的一切数据和构造都被删除任何与该表相关的视图或同义词依

13、然存在，但是却是不可用的任何悬而未决的事务都将被提交一切的索引被删除.不能回滚该语句只需表的创建者或具有DROP ANY TABLE权限的用户才干删除表SQL DROP TABLE dept30;表已删除。.4.1.10 修正对象的称号要修正表、视图、序列或同义词的称号，可以运用RENAME 语句您必需拥有对象的一切权.SQL RENAME dept1 TO department;表已重命名。RENAME old_name TO new_name;.4.1.11 截断表TRUNCATE TABLE 语句：删除表中一切记录释放该表的存储空间运用TRUNCATE移除后不能回滚DELETE语句同样可

14、以删除表中记录，但是它并不释放存储空间。SQL TRUNCATE TABLE department;表被截断。.4.1.12 给表或列添加注释运用COMMENT语句，可以为表或列添加注释(不超越2000字节)注释可以经过以下字典表查询ALL_COL_COMMENTSUSER_COL_COMMENTSALL_TAB_COMMENTSUSER_TAB_COMMENTSSQL COMMENT ON TABLE emp 2 IS Employee Information;注释已创建。.4.2 约束描画主要的约束类型建立表的同时创建约束对已存在的表添加、删除约束禁用、启用约束.什么是约束阻止无效数据进入

15、表中约束在表一级实施强迫规那么约束可以阻止删除和其它表具有相关性的表以下是Oracle中有效的约束类型：NOT NULLUNIQUE KeyCHECKPRIMARY KEYFOREIGN KEY值约束完好性约束.约束准那么给约束命名或者由Oreacle效力器产生一个方式为SYS_Cn的称号创建约束的方式:创建表的同时便创建约束表创建以后再创建约束在列或者表一级定义约束在数据字典中可以查询约束.命名约束对于一个帐号，每个约束名必需是独一的命名约束的商定格式:_如：emp_deptno_fk约束缩写PRIMARY KEYpkFOREIGN KEYfkUNIQUEukCHECKck 或 ccNOT

16、NULLnn图 普遍采用的约束的缩写表示.定义约束CREATE TABLE schema.table (column datatype DEFAULT exprcolumn_constraint,table_constraint);CREATE TABLE emp( empno NUMBER(4), ename VARCHAR2(10), deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_empno_pk PRIMARY KEY (EMPNO);.定义约束列约束级表约束级column CONSTRAINT constraint_name constrain

17、t_type,column,. CONSTRAINT constraint_name constraint_type (column, .),.NOT NULL约束确保该列不允许存放空值 EMP EMPNO ENAME JOB . COMM DEPTNO 7839KINGPRESIDENT 10 7698BLAKEMANAGER 30 7782CLARKMANAGER 10 7566JONESMANAGER 20 .NOT NULL 约束(该列没有记录为NUILL)没有 NOT NULL约束(任何一个记录该列可以为空值)NOT NULL 约束.NOT NULL 约束在列一级上定义约束SQL C

18、REATE TABLE employee( 2 empno NUMBER(4), 3enameVARCHAR2(10) NOT NULL, 4jobVARCHAR2(9), 5mgrNUMBER(4), 6hiredateDATE, 7salNUMBER(7,2), 8 commNUMBER(7,2), 9deptnoNUMBER(7,2) NOT NULL);.UNIQUE 约束 DEPT DEPTNO DNAME LOC - - 10ACCOUNTINGNEW YORK 20RESEARCHDALLAS 30SALESCHICAGO 40OPERATIONSBOSTONUNIQUE 约束

19、50SALESDETROIT 60BOSTONInsert into不允许插入 (SALES 曾经存在)允许插入.UNIQUE 约束可以定义在表或列一级SQL CREATE TABLE dept( 2 deptno NUMBER(2), 3dname VARCHAR2(14), 4loc VARCHAR2(13), 5CONSTRAINT dept_dname_uk UNIQUE(dname);注: Oracle效力器是经过隐含地在独一关键字上创建独一索引的方式实施UNIQUE约束的。.PRIMARY KEY 约束 DEPT DEPTNO DNAME LOC - - 10ACCOUNTINGN

20、EW YORK 20RESEARCHDALLAS 30SALESCHICAGO 40OPERATIONSBOSTONPRIMARY KEYInsert into 20MARKETINGDALLAS FINANCENEW YORK不允许 (DEPTNO-20 曾经存在)不允许(DEPTNO 不允许为空).PRIMARY KEY 约束可以定义在表或列一级SQL CREATE TABLE dept( 2 deptno NUMBER(2), 3dname VARCHAR2(14), 4loc VARCHAR2(13), 5CONSTRAINT dept_dname_uk UNIQUE (dname),

21、 6CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno);注: Oracle效力器会自动为主关键字列创建一个独一索引。.FOREIGN KEY 约束 DEPT DEPTNO DNAME LOC - - 10ACCOUNTINGNEW YORK 20RESEARCHDALLAS .PRIMARYKEY EMP EMPNO ENAME JOB . COMM DEPTNO 7839KINGPRESIDENT 10 7698BLAKEMANAGER 30 .FOREIGNKEY 7571FORDMANAGER . 200 9 7571FORDMANAGER . 2

22、00Insert into不允许插入(DEPTNO-9 在DEPT表中不存在)允许.FOREIGN KEY 约束可以定义在表或列一级SQL CREATE TABLE employee( 2 empno NUMBER(4), 3enameVARCHAR2(10) NOT NULL, 4jobVARCHAR2(9), 5mgrNUMBER(4), 6hiredateDATE, 7salNUMBER(7,2), 8 commNUMBER(7,2), 9deptnoNUMBER(7,2) NOT NULL, 10CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)

23、 11REFERENCES dept (deptno);.FOREIGN KEY 约束可以定义在表或列一级SQL CREATE TABLE employee( 2 empno NUMBER(4), 3enameVARCHAR2(10) NOT NULL, 4jobVARCHAR2(9), 5mgrNUMBER(4), 6hiredateDATE, 7salNUMBER(7,2), 8 commNUMBER(7,2), 9deptnoNUMBER(7,2) 10 constraint emp_deptno_fk references dept(deptno);.FOREIGN KEY 约束中的关

24、键字FOREIGN KEY在表或列一级上定义外键REFERENCES标识父表及相应的列ON DELETE CASCADE假设删除父表中的记录那么允许级联删除子表中相关连的记录或用ON DELETE SET NULL.CHECK约束定义每条记录都必需满足的条件表达式不允许运用:对伪列的援用，例如： CURRVAL, NEXTVAL, LEVEL及 ROWNUM对其它记录其它值的查询可以调用SYSDATE, UID, USER及 USERENV 函数., deptnoNUMBER(2), CONSTRAINT emp_deptno_ck CHECK (DEPTNO BETWEEN 10 AND 9

25、9),.添加约束添加、删除但不是修正约束启用一个曾经禁用的约束运用MODIFY子句添加NOT NULL约束 ALTER TABLE table ADD CONSTRAINT constraint type (column);.添加约束为EMP表添加一个 FOREIGN KEY约束，指定雇员的管理员编号必需是在EMP表中曾经存在的雇员的雇员编号。SQL ALTER TABLE emp 2 ADD CONSTRAINT emp_mgr_fk 3 FOREIGN KEY(mgr) REFERENCES emp(empno);表已更改。.删除约束从 EMP表删除管理员约束SQL ALTER TABLE

26、 emp 2 DROP CONSTRAINT emp_mgr_fk;表已更改。从DEPT表删除主键约束PRIMARY KEY，同时删除也删除了与该约束关联的EMP表DEPTNO上的外键 FOREIGN KEY约束。SQL ALTER TABLEdept 2 DROP PRIMARY KEY CASCADE;表已更改。.禁用约束在ALTER TABLE 语句中运用DISABLE子句可以使约束失效运用CASCADE 选项可以使与该约束是从属关系的约束同时失效SQL ALTER TABLEdeptment 2 DISABLE CONSTRAINTdept_deptno_pk CASCADE;表已更改。.启用约束运用ENABLE子句可以激活曾经禁用的约束假设您启用独一UNIQUE或主键PRIMARY KEY约束，独一 UNIQUE或主键 PRIMARY KEY索引便自动创建SQL ALTER TABLEdeptment 2 ENABLE CONSTRAINTdept_deptno_pk;表