A07_DDL与Oracle数据库对象ppt课件

1、DDL与数据库对象课程代码：AMPC0103V20.课程引见目的：了解DDL指令与常规数据库对象操作。内容：数据库表及相关DDL操作、运用约束、视图操作、索引操作、运用序列、运用同义词。重点：表、约束难点：索引.数据库对象表约束视图索引序列同义词.常用数据库对象命名规那么：必需以字母开头可以包含字母、数字、_、$、和#同一方案用户下的对象不能重名不能运用Oracle 的保管字 对象描 述 表存放数据的基本数据库对象，由行（记录）和列（字段）组成 约束条件执行数据校验，保证数据完整性的系列规则 视图表中数据的逻辑显示 索引根据表中指定的字段建立起来的顺序，用于提高查询性能 序列一组有规律的整数值

2、 同义词对象的别名.一. 表创建表修正表构造删除表重命名表数据字典.创建表根本语法：举例：阐明：在向表中添加记录时如未显式指定某个字段的值，那么该字段会被赋为此缺省值，假设在定义表时未设置过该字段的缺省值，那么会被赋值为NULL。CREATE TABLE schema.table (column datatype DEFAULT expr,.);CREATE TABLE scott.test1( eid NUMBER(10), name VARCHAR2(20), hiredate DATE DEFAULT SYSDATE, salary NUMBER(8,2) DEFAULT 1500);.

3、运用子查询创建表在创建表的同时，可以将子查询的结果直接插入其中。语法：举例：阐明：子查询中查询字段列表中假设出现了表达式，那么应指定其表达式别名，否那么就必需显式指定新建表的字段名。CREATE TABLE schema.table (column ,.) AS subquery;CREATE TABLE myemp(编号, 姓名, 年薪) AS SELECT empno, ename, sal*12 FROM emp;CREATE TABLE myemp2AS SELECT empno, ename, sal*12 annsal FROM emp;.修正表构造运用ALTER TABLE语句可

4、修正表的构造，包括：添加字段修正字段删除字段禁用字段ALTER语句为DDL指令，一经执行不可吊销.添加字段在ALTER TABLE语句中，运用ADD子句指定新添加的字段列表，新字段只能被加到整个表的最后。语法：举例：ALTER TABLE tableADD (column datatype DEFALUT expr , column datatype .);ALTER TABLE test1ADD( grade NUMBER(3), phone VARCHAR2(20) DEFAULT 无);.修正字段在ALTER TABLE语句中，运用MODIFY子句可修正现有字段属性，包括字段的数据类型、

5、大小和默许值等。语法：举例：ALTER TABLE tableMODIFY (column datatype DEFALUT expr , column datatype .);ALTER TABLE test1MODIFY( grade NUMBER(2), phone VARCHAR2(15) DEFAULT;.删除字段在ALTER TABLE语句中，运用DROP子句可删除现字段、并释放相应的存储空间。语法：举例：ALTER TABLE table DROP COLUMN column;ALTER TABLE table DROP (column, column

6、);ALTER TABLE test1 DROP COLUMN salary;ALTER TABLE test1 DROP (grade, phone);.禁用字段在业务顶峰期，可运用SET UNUSED子句可以将表中字段设为“无用形状、待稍后再做删除。语法：举例：ALTER TABLE table SET UNUSED COLUMN column;ALTER TABLE table SET UNUSED (column,.);ALTER TABLE test1 SET UNUSED COLUMN hiredate;ALTER TABLE test1 SET UNUSED (name,sala

7、ry);.删除表丢弃表：运用DROP语句，销毁表构造、释放空间语法：举例：去除表中数据：运用TRUNCATE语句，保管表构造语法：举例：DROP TABLE table;DROP TABLE test1;TRUNCATE TABLE table; TRUNCATE TABLE myemp1; .重命名表运用RENAME语句可以改动现有表的称号也可修正其他数据库对象视图、序列、同义词等的称号执行重命名操作的必需是对象的一切者为DDL语句，一经执行不可吊销语法：举例：RENAME object_name TO new_object_name;RENAME myemp2 TO mm2;.数据字典Or

8、acle数据库中的表可分为两类：用户表：由用户本人创建并维护，其中保管用户数据；数据字典表：由Oracle数据库自动创建并维护的一组表，其中保管的是数据库相关本身信息，由一系列只读的表和视图组成。数据字典内容：数据库的物理和逻辑构造对象的定义和空间分配完好性约束条件用户角色权限审计记录.数据字典分类及访问数据字典主要可分为三类dba - 一切方案包含的对象信息all - 用户可以访问的对象信息user - 用户方案的对象信息访问数据字典：-查看当前用户拥有的一切表的名字SELECT table_name FROM user_tables;-查看当前用户可以访问的一切表的名字SELECT tab

9、le_name FROM all_tables;-查看当前用户拥有的一切对象的类型SELECT DISTINCT object_type FROM user_objects;-查看一切用户拥有的一切对象的类型执行本指令需具备DBA身份SELECT table_name FROM dba_tables;.二. 约束约束概述非空约束独一性约束主键约束外键约束检查约束约束管理.约束概述约束Constraint是在表上强迫执行的数据校验规那么，用于维护数据的完好性。Oracle运用SYS_Cn格式自动命名约束，也可以由用户命名。可在建表的同时创建约束，也可以在建表后单独添加约束。可以在表级或列级定义约

10、束。可以经过数据字典视图查看约束。约束分类：not null 非空unique key 独一键primary key 主键foreign key 外键check 检查.建表的同时创建约束语法：CREATE TABLE schema.table(column datatype DEFAULT expr column_constraints,table_constraints);.非空约束非空NOT NULL约束用于确保字段值不能为空NULL，非空约束只能在字段级定义。举例：CREATE TABLE student( sid NUMBER(3) NOT NULL, name VARCHAR2(20

11、) , birth DATE CONSTRAINT student_birth_nn NOT NULL);.独一性约束独一性UNIQUE约束用于确保其所约束的字段或字段组合不出现反复的值。独一性约束的字段允许出现空值。Oracle会自动为独一性约束创建对应的独一性索引独一性约束既可以在字段级定义、也可以在表级定义CREATE TABLE student( sid NUMBER(3) UNIQUE, name VARCHAR2(20);CREATE TABLE student( sid NUMBER(3), name VARCHAR2(20), CONSTRAINT student_sid_un

12、 UNIQUE(sid);.主键约束主键PRIMARY KEY用于独一标识表中的某一行记录，其功能上相当于非空且独一。一个表中只允许一个主键，可以是单个字段或多字段的组合。Oracle会自动为主键字段创建对应的独一性索引。主键约束既可以在字段级定义、也可以在表级定义。CREATE TABLE student( sid NUMBER(3) PRIMARY KEY, name VARCHAR2(20);CREATE TABLE student( sid NUMBER(3), name VARCHAR2(20), CONSTRAINT student_sid_pk PRIMARY KEY(sid);

13、.结合主键由多个字段组合而成的主键称结合主键。结合主键中每一个字段都不能为空。结合主键字段组合的值不能出现反复。结合主键只能定义为表级约束。CREATE TABLE scores( sid NUMBER(3), subject VARCHAR2(20), score NUMBER(3), CONSTRAINT score_sid_subject_pk PRIMARY KEY(sid, subject) );INSERT INTO scores VALUES(101, 语文, 88);-合法INSERT INTO scores VALUES(101, 数学, 99);-合法INSERT INTO

14、 scores VALUES(101, 语文, 66);-非法INSERT INTO scores VALUES(NULL, 语文, 77);-非法.外键约束外键FOREIGN KEY约束用于确保相关的两个字段之间的参照关系。子表外键字段的值必需在主表被参照字段值的范围内，或者为空值NULL；外键参照的必需是主表的主键或者独一键；主表主键/独一键值被子表参照时，主表相应记录不允许被删除。.外键约束续CREATE TABLE empinfo( eid NUMBER(3) PRIMARY KEY, ename VARCHAR2(20), job VARCHAR2(20), birth DATE);

15、CREATE TABLE salary( eid NUMBER(3), basic_salary NUMBER(8,2), job_allowance NUMBER(8,2), travelling_allowance NUMBER(8,2), personal_income_tax NUMBER(8,2), CONSTRAINT salary_eid_fk FOREIGN KEY(eid) REFERENCES empinfo(eid);CREATE TABLE salary( eid NUMBER(3) REFERENCES empinfo(eid)， .);.外键约束续外键约束可以和主键

16、约束同时运用。CREATE TABLE salary( eid NUMBER(3) PRIMARY KEY, . CONSTRAINT salary_eid_fk FOREIGN KEY(eid) REFERENCES empinfo(eid);CREATE TABLE salary( eid NUMBER(3) PRIMARY KEY REFERENCES empinfo(eid), .);.外键约束续外键约束也可以构建于同一个表内部的两个字段之间-定义测试表，在其内部字段间定义参照完好性约束CREATE TABLE myemp( empno NUMBER(4) PRIMARY KEY, e

17、name VARCHAR2(20), job VARCHAR2(9), mgr NUMBER(4) REFERENCES myemp(empno), sal NUMBER(7,2);-插入测试数据，操作合法符合完好约束条件INSERT INTO myemp(empno, ename, mgr) VALUES(101, Billy, NULL);INSERT INTO myemp(empno, ename, mgr) VALUES(102, John, 101);INSERT INTO myemp(empno, ename, mgr) VALUES(103, Nancy, 101);INSERT

18、 INTO myemp(empno, ename, mgr) VALUES(104, Tom, 102);-操作非法违反完好约束条件-未找到父项关键字INSERT INTO myemp(empno, ename, mgr) VALUES(106, Billy, 108);.检查约束检查CHECK约束用于定义数据字段必需求满足的条件，包括数据的取值范围、文本内容格式等，详细以条件表达式的方式给出。检查约束的条件表达式中不允许出现如下内容：currval, nextval, level, rownum等伪列sysdate, uid, user, userenv等函数对其它字段值的援用CREATE

19、TABLE test1( name VARCHAR2(20), age NUMBER(3) CHECK(age=0 AND age =120);.查看约束查询用户字典视图user_constrains，可得到用户的一切约束。查询用户字典视图user_cons_columns，可获知约束建立在哪些字段上。SELECT * FROM user_constraints;SELECT * FROM user_cons_columns;.建表后添加约束语法：举例：ALTER TABLE tableADD CONSTRAINT constraint_name constraint_type (column

20、,.);CREATE TABLE student( sid NUMBER(10), name VARCHAR2(20);ALTER TABLE student ADD CONSTRAINT student_sid_pk PRIMARY KEY(sid);.删除约束删除主键约束的另一种方式ALTER TABLE tableDROP CONSTRAINT constraint_name CASCADE;CREATE TABLE student( sid NUMBER(10), name VARCHAR2(20) CONSTRAINT student_name_nn NOT NULL, CONSTR

21、AINT student_sid_pk PRIMARY KEY(sid);ALTER TABLE student DROP CONSTRAINT student_sid_pk;ALTER TABLE student DROP CONSTRAINT student_name_nn;ALTER TABLE table DROP PRIMARY KEY;ALTER TABLE student DROP PRIMARY KEY;.删除级连约束在删除约束时，假设还存在与该约束相关联的其它约束，那么删除操作会失败，此时可运用CASCADE子句将其它关联约束一并删除。CREATE TABLE empinfo

22、( eid NUMBER(3) CONSTRAINT empinfo_eid_pk PRIMARY KEY, ename VARCHAR2(20), -);CREATE TABLE salary( eid NUMBER(3) REFERENCES empinfo(eid), -);ALTER TABLE empinfoDROP CONSTRAINT empinfo_eid_pk CASCADE; .删除级连约束续在删除表中字段时，假设该字段处于多字段结合约束条件结合主键、结合独一键、存在参照当前字段的外键中，那么删除会失败，此时可运用CASCADE CONSTRAINT子句将与该字段相关的约束

23、一并删除。CREATE TABLE record( student_id NUMBER(3), subject_id VARCHAR2(20), record NUMBER(3), CONSTRAINT record_stuId_subId_pk PRIMARY KEY(student_id, subject_id);ALTER TABLE record DROP (student_id) CASCADE CONSTRAINT;.禁用/启用约束ALTER TABLE语句中可运用DISABLE CONSTRAINT子句禁用已有约束，还可运用CASCADE选项将相关联的约束一并禁用。相应地，可以运

24、用ENABLE CONSTRAINT子句启用先前被禁用的约束，但此时无法再运用CASCADE选项一并启用相关联的其它约束。ALTER TABLE tableDISABLE CONSTRAINT constraint_name CASCADE;ALTER TABLE student DISABLE CONSTRAINT student_sid_pk CASCADE; ALTER TABLE student ENABLE CONSTRAINT student_sid_pk; .三. 视图什么是视图根本视图操作创建视图查询视图删除视图重新编译视图强迫创建视图创建复杂视图在视图上执行更新操作添加数据更

25、新数据删除数据.什么是视图？视图View是从数据表或者其它视图中提取数据而成的一种虚拟表。.视图优点及分类视图优点：简化复杂数据查询提高运转效率屏蔽数据库表构造，实现数据逻辑独立性限制数据库访问在一样数据上提供不同的视图，便于数据共享视图分类： 特征项目简单视图复杂视图 基表数目一个一个或多个 使用函数无有 使用分组无有 支持DML操作支持不一定.创建视图运用嵌入子查询的CREATE VIEW语句创建视图。语法：举例：CREATE OR REPLACE VIEW schema.view (alias,.)AS subquery;WITH READ ONLYCREATE OR REPLACE V

26、IEW myview1(编号, 姓名, 职位, 工资) AS SELECT empno, ename, job, sal FROM emp WHERE deptno = 20;.查询视图DESC myview1;SELECT * FROM myview1;SELECT 编号, 工资 FROM myview1 WHERE 职位=ANALYST;SELECT * FROM user_views;.删除视图语法：举例：DROP VIEW view;DROP VIEW myview1;.重新编译视图假设视图的基表构造发生变化，比如添加或删除了字段，那么视图形状会被标志为无效，以后当再有用户访问该视图时

27、，Oracle会自动重新编译该视图，以防止能够的运转错误。此外，用户也可以执行ALTER VIEW语句手工编译视图。语法：举例：ALTER VIEW view COMPILE;ALTER VIEW myview1 COMPILE;.强迫创建视图假设视图定义中所涉及的基表不存在、或者字段有问题，那么视图创建操作会失败。此时可运用FORCE选项强迫创建视图，然后再定义基表或修正基表构造。语法：举例：CREATE OR REPLACE FORCE | NO FORCE VIEW schema.view (alias,.)AS subquery;CREATE OR REPLACE FORCE VIEW

28、 myview2AS SELECT empno, ename, job, sal FROM emp2 WHERE deptno = 20;.创建复杂视图视图定义中涉及到了多个基表，或者在视图定义的子查询中运用了函数、复合表达式或数据分组等。CREATE OR REPLACE VIEW v_sal(deptno, maxsal, minsal, avgsal)AS SELECT deptno, max(sal), min(sal), avg(sal) FROM emp GROUP BY deptno;CREATE OR REPLACE FORCE VIEW v_emp(工号, 姓名, 职位, 年

29、薪, 工龄(月), 部门编号,部门称号) AS SELECT empno, ename, job, sal*12, months_between(SYSDATE, hiredate), emp.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno;.在视图上执行更新操作在可更新视图上进展DML操作，可以修正基表中数据。在视图上执行数据更新操作，应遵照如下原那么：用户还需求拥有对视图基表进展更新操作的权限；视图定义的子查询中不能运用分组函数、GROUP BY子句、DISTINCT关键字、rownum伪列，其查询字段不能为表达式；由

30、两个以上基表中导出的视图上不允许进展DELETE操作；基表中非空的字段在视图定义中未包括、该字段也未曾设置过缺省值，那么不可在视图上进展INSERT操作；在视图上进展DML操作，语法与在表上操作一样。.四. 索引索引概述创建索引查看索引删除索引.索引概述索引Index一种用于提升查询效率的数据库对象，运用索引可以快速定位数据、减少磁盘I/O操作次数。常规索引分类独一性索引和非独一性索引单字段索引和结合索引普通索引和函数索引B树索引和位图索引创建索引的两种方式自动创建：在定义主键或独一键约束时系统会自动在相应的字段上创建独一性索引。手动创建：用户可在其它列上创建非独一性索引以加速查询。.创建索引

31、CREATE UNIQUE | BITMAP INDEX schema.index ON table (column, column.);-创建测试数据表myempCREATE TABLE myemp AS SELECT empno, ename, job, sal, deptno FROM emp;-创建基于ename字段的普通索引CREATE INDEX myindex ON myemp(ename);-创建基于deptno和sal字段的结合索引CREATE INDEX myindex2 ON myemp(deptno, sal);-创建基于empno字段的独一性索引CREATE UNIQ

32、UE INDEX myindex3 ON myemp(empno);-创建基于job字段的位图索引CREATE BITMAP INDEX myindex4 ON myemp(job);-创建基于lower(ename)、sal*12的函数索引CREATE INDEX myindex5 ON myemp(lower(ename);CREATE INDEX myindex6 ON myemp(sal*12);.查看索引查询用户字典视图user_indexes，可获取索引称号、类型、所在表名、表拥有者等信息。查询用户字典视图user_ind_columns，可获取索引详细建立在哪个字段上。SELEC

33、T * FROM user_indexes;SELECT * FROM user_ind_columns WHERE table_name=myemp;.删除索引索引一经创建、不可以修正，假设要修正那么应先删除掉再重新创建。假设不再需求，可以手工删除索引。在删除数据表和字段时系统会自动删除相关索引。语法：举例：DROP INDEX index_name;DROP INDEX myindex1;.索引优缺陷优点：提升查询效率缺陷建立索引会添加物理区域开销插入、修正、删除数据时效率变低原那么很少做查询条件的字段不要建索引逻辑字段(性别)不要建索引其他索引函数、全局、分区、独一、非独一、位图、反向.

34、五. 序列创建序列运用序列修正序列删除序列.序列概述序列Sequence实践上是一种“序列号生成器，它可以自动生成一组独一的整型数字，其典型用途是为数据表的主键字段赋值替代运用程序编号。序列是用户创建的数据库对象，可以被多个用户共享。.创建序列Oracle数据库运用CREATE SEQUENCE语句创建序列。语法：举例：CREATE SEQUENCE schema.sequence INCREMENT BY nSTART WITH nMAXVALUE n| NOMAXVALUE MINVALUE n| NOMINVALUE CYCLE | NOCYCLE - 能否循环CACHE n| NOCACHE-能否预设值ORDER | NOORDER;-并发读取CREATE SEQUENCE mysequence1 INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE; CREATE SEQUENCE mysequence2.运用序列相关伪列：nextval伪列用于从指定的序列数值中取出下一个值currval伪列援用的是指定序列的“当前值用法举例：SELECT mysequence1.currval FROM dua