orale表管理： 

Oracle创建表同SQL Server一样，使用CREATE TABLE命令来完成。创建约束则使用如下命令：

语法格式：alter table命令

alter table 表名 add constraint 约束名 约束内容。

不论创建表还是约束，与SQL Server基本相同，注：在Oracle中default是一个值，而SQL Server中default是一个约束，因此Oracle的default设置可以在建表的时候创建。

案例1：创建一个学生信息（INFOS）表和约束

 Oracle创建表和约束

 create table INFOS(

 STUID            varchar2(7) not null,               --学号 学号=‘S’+班号+2位序号

 STUNAME          varchar2(10) not null,              --姓名

 GENDER           varchar2(2) not null,               --性别 

 AGE              number(2) not null,                 --年龄

 SEAT             number(2) not null,                 --座号

 ENROLLDATE       date,                               --入学时间

 STUADDRESS       varchar2(50) default '地址不详',     --住址

 CLASSNO          varchar2(4) not null,               --班号 班号=学期序号+班级序号 

);

/  ①

alter table INFOS add constraint pk_INFOS primary key(STUID)  ②

/

alter table INFOS add constraint ck_INFOS_gender check(GENDER = '男' or GENDER = '女')  ③

/

alter table INFOS add constraint ck_INFOS_SEAT check(SEAT >=0 and SEAT =0 and AGE='1001' and CLASSNO='2001' and CLASSNO alter session set constraints deferred.

7. 由实体关系图到创建表的例子 s_dept 前提条件:已有region表且含唯一关键字的字段id SQL> CREATE TABLE s_dept (id NUMBER(7) CONSTRAINT s_dept_id_pk PRIMARY KEY, name VARCHAR2(25) CONSTRAINT s_dept_name_nn NOT NULL, region_id NUMBER(7) CONSTRAINT s_dept_region_id_fk REFERENCES region (id), CONSTRAINT s_dept_name_region_id_uk UNIQUE(name, region_id));

8. 较复杂的创建表例子 SQL> CREATE TABLE s_emp (id NUMBER(7) CONSTRAINT s_emp_id_pk PRIMARY KEY, last_name VARCHAR2(25) CONSTRAINT s_emp_last_name_nn NOT NULL, first_name VARCHAR2(25), userid VARCHAR2(8) CONSTRAINT s_emp_userid_nn NOT NULL CONSTRAINT s_emp_userid_uk UNIQUE, start_date DATE DEFAULT SYSDATE, comments VARCHAR2(25), manager_id NUMBER(7), title VARCHAR2(25), dept_id NUMBER(7) CONSTRAINT s_emp_dept_id_fk REFERENCES s_dept(id), salary NUMBER(11,2), commission_pct NUMBER(4,2) CONSTRAINT s_emp_commission_pct_ck CHECK (commission_pct IN(10,12.5,15,17.5,20)));

8. 通过子查询建表 通过子查询建表的例子 SQL>CREATE TABLE emp_41 AS SELECT id, last_name, userid, start_date FROM s_emp WHERE dept_id = 41;

SQL> CREATE TABLE A as select * from B where 1=2; 只要表的结构.

10. 用子查询建表的注意事项 1)可以关连多个表及用集合函数生成新表,注意选择出来的字段必须有合法的字段名称,且不能重复。 2)用子查询方式建立的表，只有非空NOT NULL的约束条件能继承过来, 其它的约束条件和默认值都没有继承过来. 3)根据需要，可以用alter table add constraint ……再建立其它的约束条件，如primary key等.

11. Foreign Key的可选参数ON DELETE CASCADE 在创建Foreign Key时可以加可选参数: ON DELETE CASCADE它的含义是如果删除外键主表里的内容，子表里相关的内容将一起被删除. 如果没有ON DELETE CASCADE参数，子表里有内容，父表里的主关键字记录不能被删除掉.

12. 如果数据库表里有不满足的记录存在，建立约束条件将不会成功.

13. 给表创建和删除同义词的例子 SQL> CREATE SYNONYM d_sum 2 FOR dept_sum_vu;

SQL> CREATE PUBLIC SYNONYM s_dept 2 FOR alice.s_dept;

SQL> DROP SYNONYM s_dept;

create table INSURES

(

 INSURE_NO                CHAR(18) not null,          --医保号

 GETSURE_UNIT_NO          CHAR(9) not null,           --经办机构号

 INSURE_NAME              VARCHAR2(10) not null,      --姓名

 INSURE_SEX               CHAR(1) not null,           --性别

 ID_CARD_NO               CHAR(18) not null,          --身份证号

);

创建/修改主键，唯一性约束和外键   这里INSURE_NO, GETSURE_UNIT_NO唯一性约束

alter table INSURES

add constraint UNQ_INSURES unique (INSURE_NO, GETSURE_UNIT_NO)

alter table TWN_SEED

add constraint UNQ_INSURES primary key (。。。。)

创建索引

create index IDX_INSURES on INSURES (GETSURE_UNIT_NO, SONSURE_UNIT_NO, UNIT_NO, FAMILY_NO, HOSPS_NO)

创建一个PK的时候，是自动创建一个与之对应的唯一索引的。  如果不特别指定，那么这个索引的表空间和表格的空间是一样的，但是我们不建议放在一起。

create table testone(

name varchar2(10 char))

TABLESPACE1;

ALTER TABLE TESTONE ADD CONSTRAINT  PK_TESTONE1 PRIMARY KEY(NAME) USING INDEX TABLESPACE TABLESPACE2;

作为一种好习惯，不要把索引和表格的数据存在在同一个表空间中

--创建Oracle表（使用create关键字）

-- ******    

（1）创建新表 use 数据库（在那个数据库中建表） create table 表名

( 字段名1（列名） 数据类型 列的特征,

字段名2（列名） 数据类型 列的特征(NOT NULL),

...... )

（2）创建带有主键约束的表语法 create table 表名 (

字段名1（列名） 数据类型 列的特征,

字段名2 数据类型 列的特征(NOT NULL),

...... primary key(主键列字段))

（3）利用现有的表创建表 -- 注意：仅复制Oracle数据表结构：采用的是子查询方式 create table 新表 as select * from 旧的表 where 1=2 （4）利用现有的表的结构创建新表 -- 注意：仅复制Oracle数据表结构：采用的是子查询方式 create table 新表 select 字段1,字段2... from 旧的表 where 条件（旧的表字段满足的条件） （5）利用现有的表的结构创建新表 -- 注意：复制Oracle数据表数据 create table 新表 as select * from 旧的表 where 1=1 （6）利用现有的表的结构创建新表  -- 注意：复制Oracle数据表数据 create table 新表 as select 字段1,字段2... from 旧的表 where 条件（旧的表字段满足的条件）（7）将查询结果插入另一张表 insert into 另一张表     select * from 要查询的表 where 条件（要查询的表的列符合什么条件）

oracle create table(转)

          *下面讲解上面命令的各方面的含义           create table test_user --创建数据表               no              number(5)            not null ,        --pk               (列名或字段名) 数据类型(数据长度) 该数据列不能为空 ,是列之间的分隔符 --后的内容是注释                   constraint pk_connectdb primary key(no)           (约束)    约束名      (主键)    (列名)    意思就是 在数据表 test_user中所有行数据 no的值不能相同(这就是主键的含义)

          storage (initial 10k next 10k pctincrease 0); 这个说起来比较复杂, 反正如果某个数据表要存放大量数据，就把initial和next后的值设置大一点, 否则设置小一点.

          既然上面在创建数据表中没有特别指定 表空间，当然该表就存放在study缺省表空间data_test了.

create tablespace data_phonepos datefile 'd:\install\OracleXpdb\datafilephonepos.dbf' size 8000M; create user phonepos identified by phonepos default tablespace data_phonepos;

grant connect, resource to phonepos; grant dba to phonepos;

权限的查询

5.1 查询某个用户授予其他用户在当前用户模式下的对象权限    select * from user_tab_privs_made --假如当前用户为WENZI，那么查询结果就是由WENZI授权，在WENZI模式下的权限记录

5.2 查询某个用户授予其他用户在该用户模式对象及其他模式对象上的对象权限    select * from all_tab_privs_made -- 假如当前登录用户为WENZI，那么查询结果就是所有由WENZI授予的权限的记录

修改用户

ALTER USER avyrros IDENTIFIED EXTERNALLY DEFAULT TABLESPACE data_ts TEMPORARY TABLESPACE temp_ts QUOTA 100M ON data_ts QUOTA 0 ON test_ts PROFILE clerk;

删除用户 DROP USER username [CASCADE] --CASECADE 选项会删除该用户模式下的所有对象，建议在删除前，先确认是否有其他的依赖关系存在。

查询属于用户的对象 select owner,object_name,object_type,status from dba_objects where owner='WENZI'

5.3 查询为某个用户授予的，在其他模式对象上的权限          select * from user_tab_privs_recd --假如当前登录用户为WENZI，那么查询结果就是WENZI在其他模式对象上的权限

5.4 查询为某个用户授予的，在该用户模式对象与其他模式对象上的权限    select * from all_tab_privs_recd --假如当前用户为wenzi，则查询结果为wenzi在整个数据库中拥有权限的对象

角色管理

创建口令文件 orapwd file='..........\pwd{SID}.ora' password='***(sys的密码)' tntries=10(口令文件最大的用户数量)

要使某个用户可以使用口令文件，必须为其授予SYSDBA权限，系统会自动将其加入到口令文件中。 grant sysdba to wenzi 当收回SYSDBA权限时，系统将对应的用户从口令文件中删除。 revoke sysdba from wenzi

查看口令文件管理的用户 select * from v$pwfile_users

   SQL> conn sys/h1w2D3B4 as sysdba

   SQL> startup

   [user@root ~]$ lsnrctl start

// 分为四步

//第1步：创建临时表空间  

create temporary tablespace user_temp  

tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf'

size 50m  

autoextend on  

next 50m maxsize 20480m  

extent management local;  

 //第2步：创建数据表空间  

create tablespace user_data

logging  

datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf' 

size 50m  

autoextend on  

next 50m maxsize 20480m  

extent management local;  

//第3步：创建用户并指定表空间  

create user username identified by password  

default tablespace user_data  

temporary tablespace user_temp;  

//第4步：给用户授予权限  

 grant connect,resource to username;  

---------------------------------------------------------------------------------

//以后以该用户登录，创建的任何数据库对象都属于user_temp 和user_data表空间，

这就不用在每创建一个对象给其指定表空间了 

撤权：  

 revoke   权限...   from  用户名;

删除用户命令

drop user user_name cascade;

建立表空间

CREATE TABLESPACE data01

DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M

UNIFORM SIZE 128k; #指定区尺寸为128k,如不指定，区尺寸默认为64k

删除表空间

DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;

一、建立表空间

CREATE TABLESPACE data01

DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M

UNIFORM SIZE 128k; #指定区尺寸为128k,如不指定，区尺寸默认为64k

二、建立UNDO表空间

CREATE UNDO TABLESPACE UNDOTBS02

DATAFILE '/oracle/oradata/db/UNDOTBS02.dbf' SIZE 50M

#注意：在OPEN状态下某些时刻只能用一个UNDO表空间，如果要用新建的表空间，必须切换到该表空间:

ALTER SYSTEM SET undo_tablespace=UNDOTBS02;

三、建立临时表空间

CREATE TEMPORARY TABLESPACE temp_data

TEMPFILE '/oracle/oradata/db/TEMP_DATA.dbf' SIZE 50M

四、改变表空间状态

1.使表空间脱机

ALTER TABLESPACE game OFFLINE;

如果是意外删除了数据文件，则必须带有RECOVER选项

ALTER TABLESPACE game OFFLINE FOR RECOVER;

2.使表空间联机

ALTER TABLESPACE game ONLINE;

3.使数据文件脱机

ALTER DATABASE DATAFILE 3 OFFLINE;

4.使数据文件联机

ALTER DATABASE DATAFILE 3 ONLINE;

5.使表空间只读

ALTER TABLESPACE game READ ONLY;

6.使表空间可读写

ALTER TABLESPACE game READ WRITE;

五、删除表空间

DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;

六、扩展表空间

首先查看表空间的名字和所属文件

select tablespace_name, file_id, file_name,

round(bytes/(1024*1024),0) total_space

from dba_data_files

order by tablespace_name;

1.增加数据文件

ALTER TABLESPACE game

ADD DATAFILE '/oracle/oradata/db/GAME02.dbf' SIZE 1000M;

2.手动增加数据文件尺寸

ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf'

RESIZE 4000M;

3.设定数据文件自动扩展

ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf

AUTOEXTEND ON NEXT 100M

MAXSIZE 10000M;

设定后查看表空间信息

SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,

(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"

FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C

WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE

创建数据表：

Oracle中建表和指定表空间

--建一个表 create table HH2(

tid number primary key ,--主键设定

tname varchar2(20)

);

--删除表 drop table HH;

--表空间（相当于一个数据库）(DBA权限) create tablespace test datafile 'D:test.dbf' size 10M autoextend on next 10M maxsize 100M

--指定表在那个表空间里面(默认在USERS表空间里) create table HH(tid number primary key) tablespace test; select * from tabs;

--删除 表空间 drop tablespace test including contents and datafiles --连带物理文件和表空间中的数据也一起删除

--建表建约束 create table student1(    

sid number primary key,    

sname varchar2(20) not null,

sage number,

ssex char(2),

saddress varchar2(100),

cid number references tclass(cid)--建立外键关系 );

create table tclass (

cid number primary key,

cname varchar2(20) );

--唯一unique 检查 check 默认值 modify 添加外键关系 添加列

alter table student1 add constraint UQ_student1_sname unique(sname);

alter table student1 add constraint CK_student1_agae check(sage between 19 and 70);

alter table student1 modify ssex default '男';

alter table student1 add constraint FK_student1_cid foreign key(cid) references tclass(cid);

alter table student1 add dt date;

--删除约束 alter table student1 drop constraint  UQ_student1_sname ;

1.创建oracle数据表

创建oracle数据表的语法如下：

create table命令用于创建一个oracle数据表；括号内列出了数据表应当包含的列及列的数据类型；tablespace则指定该表的表空间。

创建数据表students。

在该创建语句中，依次定义了student_id、student_name、student_age、status及version等列；tablespace users表示将表创建于表空间users中。

通过视图user_tables可以获得当前用户所拥有的表信息，利用如下SQL语句可以查看表student的表空间信息。

在oracle的SQL命令行下，可以利用describe命令来查看已有数据表的表结构，如下所示。

2.数据表的相关操作

数据表创建之后，由于某些原因，例如，设计时的考虑不足，往往需要对其进行结构上的调整。常见的调整包括，增加新列、修改已有列、删除、重命名已有列。另外，还可以转移数据表的表空间。

修改数据表结构应当使用alter table命令。例如，在表student中，增加新列class_id(班级ID)的SQL语句如下图所示。

alter table student 用于修改表student的结构；add用于增加列，注意此处没有collumn关键字；小括号内是列及列的数据类型；用户可以一次性为表增加多个列，各列之间使用逗号进行分隔。

在修改成功之后，表student的结构如下所示。

同样，可以利用alter命令修改和删除已有列。

利用alter命令将class_id的数据类型修改为varchar2(20)。

modify(class_id varchar2(20))用于修改表student中的已有列class_id，实际相当于重新定义。该列新的数据类型为varchar2(20)。此时表student的结构如下：

通过modify选项可以将列的类型重新定义，而通过drop选项则删除已有列，如下所示。

drop column class_id用于删除已有列class_id；需要注意的是，此处必须添加column选项，才能表示删除的目标是一个列。此时，表student的结构已经修改如下：

对于数据表的列，除了增、删、改操作之外，还可以进行重命名操作。重命名一个列，应该使用rename选项。例如，为了与其他数据表进行统一，需要将表student的列student_id重命名为id，则可以利用如下SQL语句。

rename column student_id to id用于将列student_id重命名为id。此时，表student的结构如下所示。

对于调整数据表结构来说，要特别注意严谨性。列的数据类型的修改，有可能会影响应用程序对数据库进行存取；而列的删除和重命名更需要检查应用程序是否会出现关联性错误。

如果数据表创建时，选择了错误的表空间，那么可以利用alter table命令，结合move tablespace选项转移表空间，如下图所示。

将表student转移至表空间users中。

alter table student用于修改表student的属性；move tablespace users用于将表student从当前表空间转移至表空间users。可以通过查询视图user_tables获得表student转移之后的表空间信息，如下所示。

分析查询结果可知，利用move tablespace选项已经成功实现了表空间的转移。

删除数据表

利用drop table命令删除数据表

有时，由于某些约束的存在，例如，当前表的主键被其他表用作外键，会导致无法成功删除。利用cascade constraints选项可以将约束同时删除，从而保证drop table命令一定能够成功执行。

特殊的数据表dual

dual表实际属于系统用户sys，具有了数据库基本权限的用户，均可查询该表的内容，如下所示：

或者

分析查询结果可知，dual表仅含有一行一列。该表并非为了存储数据而创建的，其存在的意义在于提供强制的数据源。

在oracle中，所有查询语句必须满足select column_name from table_name的格式。但是，在某些场景下，数据源table_name并不明确。例如，函数sysdate()用于返回当前日期，那么在SQL命令行下调用该函数时，很难有明确的数据源，此时即可使用dual表。

利用dual表提供数据源，以获得当前日期。

同样，对于各种数学运算，同样可以利用dual表作为数据源，来打印和查看输出结果。

dual表提供了一行一列的数据格式，从而使各种表达式、函数运算在以其为数据源时，能够输出单行单列的形式。

由于dual表的所有者为系统用户sys，因此，只有用户利用该身份登录数据库才可以修改该表。但是，修改该表的内容或者结构都应该被禁止。