Oracle05表操作和约束（范文模版）

第一篇：Oracle05表操作和约束（范文模版）

一、DDL数据定义语言 1.创建表：create table 表名要求：

1.必须以字母开头

2.必须在1-30个字符之间

3.必须只能包含A-Z,a-z,0-9,$,_,# 4.必须不能和用户定义的其他对象重名 5.必须不能是Oracle的保留字 例如： 方式一：

create table student(stu_no number(11), name varchar(32), gender char(5), age number(3)default 19//为字段设置默认值);Create table test1(ID char(11), Name carchar2(24), Age number(3)default 19);方式二：子查询建表

Create table user_info As

select first_name,salary,dept_id From s_emp;2.删除表：drop table 例如，drop table studentzxf;3.修改表：alter table Create table tal(Stu_no number(11), Name varchar2(32));1.增加一列

Alter table tal add age number(3);2.增加多列

Alter table tal add(address varchar2(32),birthday date);3.删除一列

Alter table tal drop column address(列名);4.删除多列

Alter table tal drop(age,birthday);5.逻辑删除列

Alter table tal set unused column name;Alter talbe tal drop unused columns;//在数据库不繁忙的时候用 Oracle在设计的时候，没有考虑可逆性 6.修改一列

Alter table tal modify stu_no number(20);//将原来的长度为11 修改为长度为20 修改列时注意：

考虑现有数据的安全性（包括数据的类型，长度），修改长度是 只能往大改 如果表中有数据，不可以修改类型，只有当表中没数据了才能修改类型 修改多列

Alter tabale tal modify(stu_no number(11)not null,name char(20));7.重命名列

Alter table tal rename column stu_no to s_no;8.更改约束

Alter table tal add constraint tal_pk primary key(s_no);9.删掉约束

Alter table test4 drop constraint tal_pk;10.重命名表名

Rename tal to 新表名 11.添加外键约束

ALTER TABLE tal ADD CONSTRAINT STUDENT_FK FOREIGN KEY(dept_no)REFERENCES dept(dept_no);

二、DML数据操纵语言

1.插入数据：insert 例如: insert into Student values（1，’Jack’）;//按表中的自然顺序逐个插入数据

insert into student(stu_no,name)values(1,’Jack’);//指定字段插入值 2.删除数据：delete from[tablename]where… 例如: delete from testdefault where age = 19;3.修改数据：update[tablename]set a = exp1 where b = exp2 如果没有限定条件，则会修改整张表中的数据,即修改整列。例如: update Student set age = 24 where name = ‘jack’;也可一次性写入多条insert语句一次执行完毕。也可加载脚本（.sql文件）命令@+路径/...sql 4.truncate截断表

delete删除时会产生日志信息的。

truncate删除数据时不生成日志信息，所以效率快，但也不可逆。truncate截断后会释放表空间，而delete并不释放表空间。

三、约束

考虑数据库的完整性约束 三方面：1.实体完整性

2.参照完整性

3.用户自定义完整性 5种约束：

Not null 非空约束

(nk)例:Create table stu_info(S_no char(11)not null, S_name varchar2(24)not null, S_age number(3));Unique 唯一约束(uk)(只保证唯一，不保证不为空，多条记录的值都可以为空,因为Null和null是不相等的，不会提示违反了唯一性)方式一：（列级约束）例:Create table stu_info(S_no char(11)unique not null, S_name varchar2(24)not null, S_age number(3));方式二：（表级约束）例:Create table stu_info(S_no char(11)not null, S_name varchar2(24)not null, S_age number(3), Constraint stu_info_uk unique(S_no,S_name));//约束名为stu_info_uk Primary key 主键约束（pk）唯一且非空 方式一：（列级约束）例：create table test2(C1 number(3)constraint test2_pk primary key, C2 number(3));方式二：（表级约束）例：create table test2(C1 number(3), C2 number(3), Primary key(c1,c2))；c1 , c2 组合起来是唯一的,c1,c2单独看不一定是唯一的 Atler table tablename add constraint 主键名称primay key

表级约束可以约束很多列，可以定义联合主键，而列级约束是做不到的。

Foreign key 外键约束（fk）

要求引用另一张表中已存在的值（主键、唯一键）

例：create table parent(C1 number(3)primary key, C2 number(3));Create table child(C1 number(3)primary key, C2 number(3)references parent(c1));先建父表再建子表。

不能随便删除，删除时只能先删子表记录再删父表；删除表时同理。插入数据时先给父表插，再给子表插。

Drop table parent cascade cinstraint;//删除表时，如果想先删除父表，子表不变 例：create table parent(C1 number(3)primary key, C2 number(3));Create table child(C1 number(3)primary key, C2 number(3)references parent(c1)on delete cascade);//删除记录时切断关系，删除了子表中的记录再删除父表

级联删除：世纪是先删除子表的记录，再删除父表的记录。表都存在。学生表：学号 姓名 性别

成绩表：成绩编号 学号 成绩 课程编号 课程表：课程编号 课程名称

外键：

成绩表里面学号应该是学生表

成绩表里面的课程编号应该是课程表里面的课程编号

Create table student(sno number primary key, name varchar2(10),sex varchar2(2))Create table score(scno number primary key,sno number not null,sco number(5,2),cno number not null references course(cno))Create table course(cno number primary key,name varchar2(20));Alter table score add constrain fk_stuandscore foreign key(sno)references student(sno);Check 检查约束（ck）方式一：（列级约束）

例：create table test3(C1 number(3)check(c1<150), C2 number(3)check(c2 is not null));方式二：（表级约束）

例：Create table test3(C1 number(3), C2 number(3), Check(c1 = c2));

第二篇：oracle表空间管理及操作实用案例

Oracle

表空间管理及操作详解（附带案例）

一：表空间概述

表空间是数据库的逻辑组成部分，从物理上讲，数据库数据存放在数据文件中；数据库逻辑上讲是由一个或者多个表空间组成，数据文件则是存放在表空间中，表空间有一个或者多个数据文件组成即表空间有数据文件构成的。

数据库，表空间，段，区，块，按大小排列依次是：数据库---》表空间-----》段-----》区----》块。

1.建立表空间的作用：

（1.）控制数据库占用的磁盘空间。（2）.dba可以将不同的数据类型部署到不同的位置，这样有利于提高i/o性能，同时有利与备份和恢复等管理操作。

二：管理表空间和数据文件。

（1）建立表空间create tablespace;一般情况下建立表空间是特权用户或者dba来执行的，如果其他用户来创建表空间，则用户建必须要具有create tablespace的系统权限。（2）建立数据表空间

在建立数据库后，为便于管理表，最好建立自己的表空间。

create tablespace sp02 datafile 'd:testsp02.dbf' size 20m uniform size 128k 说明：执行完上述命令后，会建立名称为sp02的表空间，并为该表空间建立名称为sp02.dbf的数据文件，区的大小为128k。（3.）在表空间建表

create table tablesp02(ID number(5),name varchar2(20))tablespace sp02（4）显示当前用户拥有哪些表。

select * from tab；

（5）.知道表空间名，显示该表空间包括的所有表。

select * from all_tables where tablespace_name='表空间名'。

注：拥有权限不同的用户在进行以上操作得到的结果可能不同。System可以查看得到某空间的所拥有表。（比如users表空间）（6）知道表名，查看该表属于那个表空间。

select TABLESPACE_NAME, TABLE_NAME from user_tables where TABLE_NAME='EMP';(7)知道表名，查看该表属于那个表空间，属于哪个用户。

select owner,TABLESPACE_NAME, TABLE_NAME from all_tables where TABLE_NAME='EMP';（8）改变表空间的状态 使表空间可读写

alter tablespace 表空间名 read write；

删除表空间，一般情况下由特权用户或者dba来操作。具有drop tablespace系统权限。

drop tablespace ‘表空间’includeing contens and datafiles；

说明：includeing contens表示删除表空间时，删除该空间的所有数据库对象而datafiles表示将数据库文件也删除。（9）改变表空间的状态（1）使用空间脱机

alter tablespace 表空间名 offline（2）使用空间联机

alter tablespace表空间名online（3）只读表空间

建立表空间时，表空间可以读写，如果不希望在表空间上执行update，delete，insert操作。那么可以将表空间修改为只读。alter talbespace sp01 read only；

三：移动数据文件：

（1）确定数据文件所在的表空间(知道表属于哪个表空间)Select tablespace_name from dba_data_files where file_name='D:TEST5.DBF';（2）使用表空间脱机

确保数据文件的一致性，将表空间转换为offline的状态。alter tablespace sp01 offline；

（3）使用命令移动数据文件到指定的目标位置。host move d:testsp01.dpf c:testsp01.dbf；（4）执行alter tablespace命令

在物理上移动了数据后，还必须执行alter tablespace命令对数据库文件进行逻辑修改。

alter tablespace sp01 rename datefile ‘d：sp01.dbf’ to 'c:sp01.dbf'(5)使得表空间联机

在移动了数据文件后，为了使用户可以访问该表空间，必须将其变为online状态：

alter tablespace sp01 online；

四：显示表空间信息：

查询数据视图字典dba_tablespace,显示表空间的信息：

select tablespace_name from dba_tablespaces 显示某个表空间的详细信息： select * from dba_tablespaces where TABLESPACE_NAME='SP05';显示表空间所包含的数据文件。查询数据字典视图dba_data_files。可显示表空间所包含的数据文件如下：

select file_name,bytes from dba_data_files where tablespace_name='SP05';常用表空间，undo表空间和临时表空间，非标准块的表空间。了解表空间的各个状态：如何改变表空间的的状态的方法。（online，offline，read write，read only）system是系统表空间，不能作为只读，如果是普通表空间，那么我们可以设为只读。

五：附加案例：建一个表空间，在表空间里面建表，并且增加表空间的存储空间。

1.建立表空间 create tablespace sp05 datafile 'd:testsp05.dbf' size 20m uniform size 128k 2.建表

create table tablesp05(ID number(5),name varchar2(20))tablespace sp02 3.insert into tablesp05 select * from tablesp05；（重复多步提示空间不足）

4，拓展表空间，扩大存储空间。

alter tablespace sp05 add datafile 'D:test5.dbf' size 20m;

拓展表空间的存储空间这里有三种方法：

（1）增加数据文件alter tablespace sp05 add datafile 'D:test5.dbf' size 20m;

（2）修改数据文件大小

alter database datafile 'd:testsp05.dbf' resize 50m;数据文件不要超过500m。（3）设置文件的自动增长 alter database datafile 'D:testSP05.dbf' autoextend on next 10m maxsize 500m;

第三篇：建立表和定义完整性约束、数据操作 实验报告

经济管理学院

财务管理

专业

协作者______________

教师评定 _____________ 实验题目

建立表和定义完整性约束、数据操作

一、实验目的与实验方法

本实验课程的目的：

本实验课程的实验方法：

二、实验要求

1.用CREATE DATABSE命令建立数据库

2.用CREATE SCHEMA命令创建模式（自己命名模式名称）

3.在定义的模式下，参照教材内容，使用SQL语言创建教材P43—P44面的样本数据库及定义完整性约束

4.使用Insert命令完成记录的插入操作，要求分别设计若干违反实体完整性、参照完整性和用户自定义完整性的插入操作，并分析原因。

三、实验结果和数据

1.用CREATE DATABSE命令建立数据库

2.用CREATE SCHEMA命令创建模式（自己命名模式名称）

3.在定义的模式下，参照教材内容，使用SQL语言创建教材P43—P44面的样本数据库及定义完整性约束

4.使用Insert命令完成记录的插入操作，要求分别设计若干违反实体完整性、参照完整性和用户自定义完整性的插入操作，并分析原因。

违反实体完整性：

违背了primary key 约束，所以执行不成功。违反参照完整性：

在执行插入操作时，系统会自动计算院系系列值是7，而院系列参照院系表的编号值，此时在院系表中找不到编号为7的院系。所以插入操作失败。违反用户自定义完整性：

结果是违反了unique约束，因为院系表名称不允许重复，而名称为“经济管理学院”的院系已经存在，所以不成功。

四、问题与讨论

在创建的过程中没有注意括号是用英文括号还是中文括号，所以出现了错误。应该使用英文括号的。

在同一个数据库中是不能同时创建两个相同的对象的。

心得体会：

数据库是一门很难学的课程，而且，我在平时比较少关注这方面的东西，所以，我在做这个实验的时候就觉得十分的困难。很多的时候我都需要别人的帮助。

我是一个比较粗心的人，因此，我在做这个实验的时候没有留意输入的时候要用英文符号，要加空格。所以，我总是出现错误，让我觉得很烦恼。

但是，在做这个实验的时候，我觉得一点快乐。因为我又接触了以前我从未接触过的东西，我又学了一点新的知识。

过程是最重要的，在这个实验过程中我学会了新知识，这是最重要的。

第四篇：Oracle 临时表总结===

Oracle 临时表总结

什么是临时表？

临时表只存在于某个会话或事务，不产生redo。

临时表主要用于一些需要缓存结果的应用中。例如，在一个学生选课系统中，学生暂时的选课表信息保存到一个临时表中，此时的选课信息只对学生自己可见，当学生确定选课内容后，系统会将临时表中的信息转存到普通表，然后自动清除临时表中的内容。

与普通表一样，临时表的结构信息也是保存到数据字典中，当第一次向临时表中插入数据后，系统机会分配对应的临时段，当事务或者session结束后，会释放临时段空间。

默认情况下，临时表中的信息是保存到用户的默认临时表空间中，也可以在创建临时表的时候指定具体的表空间

临时表中的内容只有session内可见，但是临时表的定义信息是全局可见的，可以通过以下语句查询一个表是否为临时表 查看是否为临时表以及临时表的周期(transaction delete on commit, session preserve on commit)

select table_name,LOGGING,TEMPORARY,DURATION from user_tables where TEMPORARY='Y';

MY_TEMP_TABLE

NO Y SYS$TRANSACTION PARTITION_TEST

N T_COMPRESS

YES N EMP

YES N ORIGINAL_INDEX_TAB

YES N T_UNCOMPRESS

YES N

如何创建一个临时表？

创建临时表的语句如下：

CREATE GLOBAL TEMPORARY TABLE admin_work_area

(startdate DATE, enddate DATE, class CHAR(20))

ON COMMIT DELETE ROWS;

创建临时表有两种：delete rows, preserve rows

delete rows: 表示一个事务结束后，临时表中的数据将会被自动清除

preserve rows: 表示事务结束后，依然保留临时表中的内容，直到session结束后，自动清除临时表中的内容。

delete rows 临时表示例：

SQL> CREATE GLOBAL TEMPORARY TABLE admin_work_area 2

(startdate DATE, 3

enddate DATE, 4

class CHAR(20))5 ON COMMIT DELETE ROWS;

Table created.SQL> insert into ADMIN_WORK_AREA values(sysdate,sysdate+100,'Math');row created.SQL> select count(*)from ADMIN_WORK_AREA;

COUNT(*)----------

SQL> commit;

Commit complete.SQL> select count(*)from ADMIN_WORK_AREA;

COUNT(*)----------

0

preserve rows 示例

SQL> CREATE GLOBAL TEMPORARY TABLE admin_work_area_preserve 2(startdate DATE, 3 enddate DATE, 4 class CHAR(20))5 ON COMMIT preserve ROWS;

SQL> insert into ADMIN_WORK_AREA_preserve values(sysdate,sysdate+100,'Math');1 row created.SQL> select count(*)from ADMIN_WORK_AREA_preserve;COUNT(*)----------1 SQL> commit;Commit complete.SQL> select count(*)from ADMIN_WORK_AREA_preserve;COUNT(*)----------1 SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.064bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show user USER is “USER01” SQL> select count(*)from ADMIN_WORK_AREA_preserve;COUNT(*)----------0

通过如下语句可以查询临时表的属性信息：

SQL> select table_name,LOGGING,TEMPORARY,DURATION from user_tables where TEMPORARY='Y';TABLE_NAME LOG T DURATION-------------------------------------------------MY_TEMP_TABLE NO Y SYS$TRANSACTION ADMIN_WORK_AREA NO Y SYS$TRANSACTION ADMIN_WORK_AREA_PRESERVE NO Y SYS$SESSION

临时表空间上也可以创建索引，周期与对应的临时表相同。创建临时表的索引时，确保临时表不在使用中

SQL> create index idx_temp_admin on ADMIN_WORK_AREA_preserve(startdate);create index idx_temp_admin on ADMIN_WORK_AREA_preserve(startdate)* ERROR at line 1: ORA-14452: attempt to create, alter or drop an index on temporary table already in use SQL> commit;Commit complete.SQL> insert into ADMIN_WORK_AREA_preserve values(sysdate,sysdate+100,'Math');1 row created.

第五篇：oracle创建临时表

Oracle数据库临时表管理心得

我们在创建数据表的时候，若没有特殊的指明，那么我们创建的表是一个永久的关系型表格，也就是说，这个表格中对应的数据，除非是我们显示的删除的话，表中的数据是永远都存在的。相对应的，在Oracle数据库中还有一种类型的表，叫做临时表。这个临时表跟永久表最大的区别就是表中的数据不会永远的存在。当一个会话结束或者事务结束的时候，这个临时表中的数据，不用用户自己删除，数据库自己会自动清

除。

1、事务临时表的管理。

(1)事务临时表的创建。

Oracle数据库根据临时表的性质不同，可以分为事务临时表与会话临时表。事务临时表是指数据只有在当前事务内有效。一般情况下，如果在创建数据表的时候，没有特殊指明这表是会话临时表的话，则该表默认为事务临时表。

我们可以以下面的语句创建事务临时表。

Create global temporary table Temp_user

(ID NUMBER(12)Primary key,name varchar2(10));

笔者建议：

这个创建临时表的语句中，虽然没有显性的指明该表是事务临时表，但是，默认的情况下，若没有指明是什么临时表的话，系统默认是事务临时表。我们要创建事务临时表时，可以不指定关键字。但是，这查看起来比较麻烦。我建议，无论在建立什么临时表，都要利用具体的关键字来显形的指明，这大家看起来都方便。一般可以利用ON COMMIT DELETE ROWS关键字来说明该表就是事务性的临时表，而不是会话性质的临时表。

(2)事务临时表数据的变化分析。

事务临时表的话，当事务结束的时候，就会清空这个事务临时表。所以，当我们在数据库临时表中插入数据后，只要事务没有提交的话，该表中的数据就会存在。但是，当事务提交以后，该表中的数据就会被删除。而且，这个变化不会在重做日志中

显示。

具体事务临时表与会话临时表有什么区别，我们在介绍完会话临时表后会详细介

绍。

2、会话临时表的管理。

会话临时表，顾名思义，是指数据只在当前会话内有效的临时表。关闭当前会话或者进行新的连接之后，数据表中的内容就会被清除。那会话临时表跟事务临时表到底有什么区别呢?我们以一个实例来看其中的区别。

(1)首先，创建一个会话临时表。

CREATE GLOBAL TEMPOPARY TABLE TEMP_USER

(ID NUMBER(12)Primary key,name varchar2(10))

ON COMMIT PRESERVE ROWS;

也就是说，会话临时表跟事务临时表的创建语法大致相同，只有最后的关键字有区别。不过两个表虽然类似，但是其内部的处理机制还是有比较大的区别。

(2)往该表中插入数据。

Insert into TEMP_USER values(1001,’victor’);

往数据库临时表中插入数据的方法，跟往普通表中插入数据的方法是一样的，都利用insert into语句进行操作。该临时表的数据在会话结束之前都是存在这个表格

中的。

(3)提交该事务并查询相关记录。

我们利用COMMIT的语句把该事务提交以后，再用SELECT查询语句进行查询。我们知道，若该表是事务临时表的话，则当该事务结束以后，该表中的内容就会被删除。但是，这是会话临时表，所以即使该事务提交了，但是，利用SELECT语句进行查询

时，仍然可以查到该条员工记录。

(4)结束当前会话，并重新连接数据库。

关闭当前会话，从新连接到数据库后，再利用SELECT语句查询时，会有什么结果呢?此时，就查不到我们刚才插入的数据。这也就是说，在关闭对话的时候，数据库系统已经把原有的数据删除了。从以上的分析我们可以看中，会话临时表与事务临时表主要的差异就在于删除数据时机的不同。事务性临时表是在事务提交的时候清除数据，而会话性临时表则是在关闭当前会话的时候清除临时表。只要当前会话没有关闭，即使事务完成了，会话临时表中的数据仍然存在，不会被清除。

3、临时表管理需要注意的地方。

临时表相对与其他表来说，是一种比较特殊的表结构，但是，作用又比较大，Oracle数据库若没有这种表的话，还真是不行。为了管理好这种特殊的表，我们需要

注意几个细节。

一是要注意临时表不能永久的保存数据。只所以称为临时表，就是因为该表中的内容只是临时存在的。当一个会话或者事务结束时，该表中的内容就会被自动清空。所以，在临时表中，一般不要保存永久数据。在实务中，有个不好的操作习惯，就是有些人在测试数据库的时候，喜欢把测试的数据放在临时数据表中。其实，这是对Oralce临时数据表认识的错误。若我们在数据库中，把要测试的数据，如销售定单的内容放在数据库的临时表中的话，则在其他功能中，如要测试销售定单日报表的功能时，就会找不到相关的定单内容。因为离开特定的会话或者事务的话，临时表中的内容就会不存在了。所以，Oralce数据库中所讲的临时表不是给我们来存储测试数据的。

二是临时表中的数据不会备份、恢复，对其的修改也不会有任何的日志信息。若我们在操作数据库的时候，往数据库的临时表中存入了一些信息。此时突然服务器出现当机。此时，我们想通过数据库备份文件恢复数据库临时表中的内容，或者查看临时表的日志信息，都是无法实现的。也就是说，当服务器以外死机重新启动后，临时表中的内容就会被清空。在数据库的任何地方，如数据库备份文件或者日志信息中，都查不到在重新启动之前数据库临时表中保存了哪些内容，就好象根本没有对临时表

进行操作一样。

三是临时表表空间的管理。临时表在Oracle数据库中，也是表的一种，其也有对应的表空间。在创建临时表的时候，若我们不指定表空间的话，默认的表空间是SYSTEM。对于临时表的表空间管理的话，我们需要注意一个小的细节。若我们把临时表的表空间归属为SYSTEM的话，也就是说，在创建临时表的时候不具体指定具体的表空间，则这个默认的表空间是不能被删除的。而若我们在创建临时表表空间的时候，指定为SYSTEM以外的表空间的话，则在不需要这表空间的时候，我们可以删除。所以，为了后续管理的方便，笔者还是建议大家在创建临时表的时候，要指定表空间。

四是要注意一个问题，临时表只是数据是临时的，而表仍然是永久的。也就是说，当一个会话结束或者一个事务完成时，其临时表中的数据虽然删除了，但是，临时表本身仍然是存在的。也就是说。Oracle数据库中的临时表表是全局的，只是数据是临时的。这跟SQL Server数据库系统具有比较大的区别。其实，这两个数据库在临时表的处理上有很大的不同，各有各的特色。在以后的文章中，我会专门叙述这两种数据库在临时表管理机制上的不同，欢迎大家关注。

五是要注意Oracle数据库在给临时表填入数据的时候，不会对相应的记录加锁。也就是说，当在临时表上执行DML语句的操作时，不会给记录加锁，也不会将数据的变化内容写到重做(REDO)日志中。所以不能用临时表保存永久的数据，也不能对临时表进行共同的操作。这是新手在管理数据库临时表经常会碰到的问题。

六是临时表与普通表之间不能相互转换。在一般情况下，临时表建立后，该表就不能被转换成永久表。所以，这也说明一个道理，利用临时表作为数据库设计时候的测试表不合适。这个临时表可能跟我们按字面意思理解的临时表有误，不是我们所认为的为了测试表结构而建立的临时表。这一点是我们在刚开始接触ORACLE数据库时，经常会犯的错误。