目录

Oracle 数据类型

Oracle 表空间

用户创建、删除、查询，修改密码

用户授权、权限查询

用户锁定与解锁

create 创建表与 as 子查询建表

表与视图设置注释

修改表结构 & 删除表

五大约束：主、外键，唯一，非空，检查

插入、更新、删除 表数据

exp/imp 导入导出数据

oracle 11g exp 空表导出处理

Oracle SQL 语句由如下命令组成：

Oracle 数据类型

1、在学习建表、操作表之前，应该先清楚 Oralce 的数据类型。Oracle 数据库的核心是表，表中的列使用到的常见数据类型如下：

NUMBER 类型的示例：

Oracle 表空间

Oracle 表空间概述

1、Oracle 可以存放海量数据，所有数据都在数据文件中存储，而数据文件大小受操作系统限制，并且过大的数据文件对数据的存取性能影响非常大。同时 Oracle 是跨平台的数据库, 可以轻松的在不同平台上移植，那么如何才能提供统一存取格式的大容量呢? Oracle 采用表空间来解决。

2、若干操作系统文件(文件可以不是很大)可以组成一个表空间，表空间统一管理空间中的数据文件，一个数据文件只能属于一个表空间。一个数据库空间由若干个表空间组成。

3、Oracle 中所有的数据(包括系统数据)，全部保存在表空间中，常见的表空间有:

Oracle 表空间创建

1、新建 Java 项目后，需要先新建表空间，然后创建用户并分配表空间，接着给用户授权，最后由此用户来建表操作。

2、表空间创建语法：

3、表空间只能由管理员创建，如下所示创建表空间 "dongShan"（datafile 的存储目录必须存在，.dbf 文件会自动创建），默认一个数据文件最大只能到 32G，后续可以再新加数据文件:

create tablespace dongShandatafile 'E:\oracle_tablesapce\dongShan.dbf'size 500M autoextend onnext 50M;

4、上面的语句执行之后，在 oralce 服务器的指定位置就会自动生成 dbf 文件，且大小为初始大小500M。

5、数据文件(.dbf文件)无法直接右键删除，因为它被 Oracle 占用了，如果真的不再需要，可以先删除表空间关系：drop tablespace 表空间名，然后再手动删除数据文件。

表空间命令汇总

用户创建、删除、查询，修改密码

1、新建 Java Web 项目后，需要先新建表空间，然后创建用户并分配表空间，接着授权给用户，最后由此用户来建表操作。

2、创建用户语法：create user 用户名 identified by 密码 default tablespace 表空间名; 为用户指定了表空间，那么该用户下的所有数据对象(比如表)默认都存在该表空间下。

3、alter user 用户名 identified by "新密码"; --修改用户密码

用户授权、权限查询

一：角色与权限

1、用户创建之后只是普通用户，没有任何权限，无法登陆 oralce，接下来需要为它分配角色/授权。Oracle 中有3个重要的角色：connect、resource、dba（角色拥有对应右边的权限）：

二：授权语法

1、grant 角色1,角色2... | 权限1,权限2... to 用户名1,用户名2... (授权操作必须是 dba 操作)

三、权限查询

1、查询用户所拥有的权限是一个很有用的功能：

四：为用户设置某个表/视图的 CRUD 权限

1、非 dba 角色的账户默认只能查看自己建立的表、视图等信息，无法查看以及操作其它用户下的表和视图；而拥有 dba 角色的账户则可以 增删改查 自己以及其它账户（包括其它 dba角色的账户）下的表和视图等信息。

2、项目中遇到过这种情况，需要为一个用户开通自己数据库账户下的一个视图、一张表的权限（显然它不是 dba 角色）。

3、授权语法：grant select [,update,delete,insert,alter] on 表名或者视图名 to 用户名1,用户名2...;

4、如为 scott 用户授权当前登陆用户 gbm_bs_ageninfo 表的查询与更新权限: grant select,update on gbm_bs_ageninfo to scott;

五：权限收回

1、收回语法与授权语法一致，只是将 grant 改为 revoke，to 改为 from：

revoke 角色1,角色2...|权限1,权限2 from 用户名；

revoke select [,update,delete,insert,alter] on 表名或者视图名 from 用户名;

用户锁定与解锁

create 创建表与 as 子查询建表

1、标准建表语法：create table 表名( 列名 数据类型 [约束],列名 数据类型 [约束],...);

2、as 子查询建表语法：create table 表名 as select 查询语句

--创建学生表CREATE TABLE STUDENT ( 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) default 0 NOT NULL , --座号，注意：默认值必须在 not null 前面ENROLLDATE DATE default sysdate, --入学时间 STUADDRESS VARCHAR2(50) DEFAULT '地址不详', --住址 CLASSNO VARCHAR2(4) NOT NULL --班号 班号=学期序号+班级序号 );comment on table STUDENT is '学生信息表'; comment on column STUDENT.STUID is '主键';

create as 子查询建表

1、如果对于一张以及存在的表，现在想要复制它，则可以使用子查询复制表来实现。

2、语法：create table 表名 as select 查询语句：

3、如果子查询有结果，则复制表结构的同时也会复制表中的数据，如果子查询没有结果，则只复制表结构。子查询建的表只复制了目标表的结构（如字段名称、数据类型），却不复制约束、索引（如主外键、约束、索引）。

表与视图设置注释

Oracle 12C 以前的版本表名、列名、约束名、索引名称、视图名、视图列名等等的长度不允许大于30个字符。

1、为表及其字段添加注释：

2、为视图字段设置注释：

3、如果明确知道需要查看哪个表或者视图的注释，则使用 PL/SQL 工具选择表名、视图名，然后右键选择“查看”即可看到。

4、但是也有这种需求，比如接手一个新项目，想查看一下所有表及其字段的注释信息，以便了解一下每张表大概的用途，或者你想根据某个注释名称来检索整个用户下所有表的字段信息。查询注释信息，可以操作 Oracle 内置的 user_col_comments 视图，可以查询当前登陆用户下的所有表和视图的列的注释。

修改表结构 & 删除表

Oracle 12C 以前的版本表名、列名、约束名、索引名称、视图名、视图列名等等的长度不允许大于30个字符。

select * from person;--查询 person 表alter table person add phone varchar2(11);--添加 phone 列alter table person add (--同时添加多列fatherName varchar2(16) not null,fatherPhone varchar2(11) );--为 saf_fund_account 表添加 fund_name 列，不允许为 nullalter table saf_fund_account add fund_name varchar2(60) not null;-- 修改表 student 的列 age 的数据类型为 number(2)，默认值为 0alter table student modify (age number(2) default 0);-- 为 saf_fund_account 表的 fund_name 列设置描述comment on column saf_fund_account.fund_name is '专户账户名';alter table person drop column fatherName;--删除列alter table person drop column fatherPhone;--删除列alter table person modify fatherName varchar2(12);--修改列的数据类型alter table gbm_bs_affix modify AFFIX_TITLE VARCHAR2(200);--修改列数据类型长度alter table person rename column gender to sex;--修改列名rename person to person_1;--修改表名rename person_1 to person;--修改表名drop table person_1;--删除表

3、Oracle 中删除表时，没有像 Mysql 一样的 drop table if exists 语法，如果想要实现同样的功能，需要借助存储过程来实现：

--表名作为参数，如果表已经存在，则删除它。create or replace procedure pro_drop_table_by_name(tableName in user_tables.TABLE_NAME%type)isflag number := 0; --表是否存在的表示，大于0表示表已经存在begin--user_tables 是系统定义的视图，可以查看当前用户下的所有表信息，表中的表名区分大小写，而且是大写select count(1) into flag from user_tables where table_name = upper(tableName) ;if flag > 0 thenexecute immediate 'drop table '|| tableName ;--如果表已经存在，则删除它end if;end;-- 数据库中调用存储过程：call pro_drop_table_by_name('student');

同理 Oracle 建表也没有像 Mysql 一样的 create table if not exists 语法，也需要借助存储过来实现。

五大约束：主、外键，唯一，非空，检查

1、表的约束目的是为了保持数据的完整性，约束表中数据的规则。主要有：主键约束（PRIMARY KEY）、非空约束（NOT NULL ）、唯一约束（UNIQUE）、检查约束（CHECK）、外键约束（FOREIGN KEY ）。

2、主键约束：主键（primary key）不能为空，且必须唯一。

建表的同时设置约束

--建表的同时创建约束create table student_1(stuid number(32) primary key,--主键stuname varchar2(16) unique,--唯一约束age varchar2(4) default 0 not null,--默认值 与 非空约束，默认值必须放在前面gender varchar2(8) check (gender in ('男','女','保密')));insert into student_1 values(1,'张三',22,'男');--插入数据，必须符合约束insert into student_1 values(2,'李四',22,'女');--插入数据，必须符合约束insert into student_1(stuid,stuname,gender) values(3,'王五','保密');--插入数据，必须符合约束select * from student_1;--查询

建表后设置约束

--创建学生表CREATE TABLE STUDENT ( 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 --班号 班号=学期序号+班级序号 );--添加主键约束。constraint 关键字英文意思为约束、限制，PK_INFOS 为约束名称（自定义），primary key 约束类型ALTER TABLE STUDENT ADD CONSTRAINT PK_INFOS PRIMARY KEY(STUID);ALTER TABLE STUDENT ADD CONSTRAINT CK_INFOS_GENDER CHECK(GENDER = '男' OR GENDER = '女');--添加检查约束，性别只能是男或者女ALTER TABLE STUDENT ADD CONSTRAINT CK_INFOS_SEAT CHECK(SEAT >=0 AND SEAT <=50);--添加检查约束，坐位号只能在 [0,50] 之间ALTER TABLE STUDENT ADD CONSTRAINT CK_INFOS_AGE CHECK(AGE >=0 AND AGE<=100);--与上同理--添加检查约束，限制班号范围ALTER TABLE STUDENT ADD CONSTRAINT CK_INFOS_CLASSNO CHECK((CLASSNO >='1001' AND CLASSNO<='1999') OR (CLASSNO >='2001' AND CLASSNO<='2999'))ALTER TABLE STUDENT ADD CONSTRAINTS UN_STUNAME UNIQUE(STUNAME);--添加唯一约束，姓名不能重复--添加 GBM_BS_QTRY 唯一约束：ID_CARD,AGENCY_ID，END_DATETIMEALTER TABLE GBM_BS_QTRY ADD CONSTRAINTS UK_ID_CART_AGENCY_ID_QTRY UNIQUE(ID_CARD,AGENCY_ID,END_DATETIME);--为 saf_income_src 表的 SRC_CODE 列添加非空约束--因为 not null 约束类型必须声明在列上,无法声明在表上,所以必须用 modify 方式来写 alter table saf_income_src modify SRC_CODE not null;--删除 saf_income_src 表的 SRC_CODE 列的非空约束alter table saf_income_src modify SRC_CODE null;

外键约束

外键约束时另一张表被引用的列必须是主键 或 unique 约束

--创建商品类型表。商品类型与商品 1 对多 关系，商品依赖类型表，类型表为主表，商品为从表create table productType(pid number(32) primary key,pname varchar2(16) not null);--创建商品表。建表的同时设置外键create table product(pid number(32) primary key,pname varchar2(16) not null,price number(8,2) not null,--价格ptId number(32) ,--外键foreign key(ptId) references productType(pid)--设置外键，关联类型表的主键);--如果建表的时候没有设置外键，则修改表，为其添加外键。默认情况下，当主表记录被从表依赖时，主表记录无法删除，除非先删除依赖项alter table product add constraints fk_ptId foreign key(ptId) references productType(pid);--添加外键的同时设置级联删除。即主表删除记录时，会自动搜索，如果有其它记录依赖自己，则先删除依赖项，然后删除自己。alter table product add constraints fk_ptId foreign key(ptId) references productType(pid) on delete cascade;

--插入数据insert into productType values(1,'IT');insert into productType values(2,'书籍');insert into productType values(3,'美食');select * from productType;insert into product values(1,'鹿鼎记',35.55,2);insert into product values(2,'蚂蚁上树',66.55,3);select * from product;delete from productType where pid =1;--正常删除，因为 pid =1 没有被任何记录依赖--如果依赖自己的外键没有设置级联删除，则这里无法删除自己，报错：违反完整约束条件，已找到子记录。除非先删除所有依赖的子记录，否则只能强制删除（不建议）--如果依赖自己的外键设置了级联删除，则会一并删除delete from productType where pid =2 or pid = 3;

约束的删除与禁用：如果想修改一个约束类型，只能先删除约束再添加新的约束，如果禁用约束后加了一些非法数据，再开启约束是会失败的。

-- 查看当前用户创建的全部约束信息：如约束名称、约束类型、所属的表、最后修改时间、约束名称是用户自定义还是系统自动新建等等select * from USER_constraints b order by b.TABLE_NAME;-- 查看全部用户创建的全部约束信息：如约束名称、约束类型、所属的表、最后修改时间、约束名称是用户自定义还是系统自动新建等等select * from dba_constraints b ;-- constraint type：约束类型：常见的值为：C(普通约束)、R(外键约束)、P(主键约束)、U(唯一约束)

插入、更新、删除 表数据

--当前使用的是拥有管理员权限的用户 wmx 登录，管理员可以查询/操作任何用户的数据，包括其它管理员select * from dept;select * from system.dept;--查询系统用户 system 的部门表insert into dept select * from system.dept;--子查询插入。将系统用户的部门表数据全部插入到 wmx 用户的部门表中insert into dept(deptno,dname) values(50,'财务部');--为部分列添加数据insert into dept values(60,'开发部','深圳');--为所有列添加值--往员工表插入数据，对于日期使用 to_date 函数进行专户，Java 代码中也是同理insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(8001,'李四','主管',7782,to_date('/06/25 15:42:00','YYYY-MM-DD HH24:MI:SS'),9800.80,100,20);--复制部门数据并重新插入，主键 deptno 再原基础上减1insert into dept (deptno, loc, dname) select t2.deptno - 1, t2.loc, t2.dname from dept t2 where t2.deptno <= 30;

-- 单表更新 格式：update 表名 set 列名1=value,列名2=value,... [where 条件];update dept set dname='测试部',loc='长沙' where deptno = 50;--如果不加 where 条件，则整个表的 dname 都会被更新update dept set dname='人事部' where deptno = 60;-- 多表更新 格式：UPDATE table1 SET (col11[,col12...]) = (SELECT col21,[,col22 ...] FROM table12 [WHERE 条件]) where 条件;-- 注意1：如果右侧的子查询没有查询到值，则左侧列的值默认会赋值为 null，此时使用 nvl 函数让它默认等于0-- 注意2：如果右侧的子查询结果出现多条，则报错：单行子查询返回多个行，此时使用 rownum 只取第一个结果-- 将工资(sal)最低员工的奖金(comm)设置和奖金最高的员工相同update emp t1 set comm = nvl((select comm from emp t2 where m = (SELECT max(m) FROM emp t3) and rownum<=1),0)where t1.sal = (SELECT min(T4.Sal) FROM emp t4);-- 将工资(sal)最低员工的奖金(comm)、以及工资设置和奖金最高的员工相同update emp t1 set（sal,comm ) = (select sal,comm from emp t2 where m = (SELECT max(m) FROM emp t3) and 1=2 and rownum <=1)where t1.sal = (SELECT min(T4.Sal) FROM emp t4);-- 通过 BAS_AGENCY_INFO 单位基本信息表 更新 ele_agency 单位要素表update ele_agency b set(b.ele_name,b.mof_dep_id,b.agency_kind_id)=(select agency_name,mof_dep_id,agency_type_id from BAS_AGENCY_INFO a where b.is_deleted=2 and b.ele_code=a.agency_code and b.mof_div_code=a.mof_div_code) where exists(select 1 from BAS_AGENCY_INFO a where b.is_deleted=2 and a.agency_code=b.ele_code and b.mof_div_code=a.mof_div_code);

--delete 删除数据：delete from 表名 [where 条件]; delete 是逐条删除，支持事务操作--truncate 删除删除：truncate 先删除整个表，然后再创建表结构，不支持事务操作，如果是删除全部数据，效率会高些--delete 删除的数据可以恢复，truncate 删除的数据不可恢复，必须慎重操作.delete from dept where deptno = 60 or deptno = 50;truncate table dept;--删除表中所有数据

truncate 和 delete 删除数据的区别

1、truncate 是 ddl 命令，删除的数据不能恢复、delete 命令是 dml 命令，删除后的数据可以通过日志文件恢复。

2、如果一个表中数据记录很多，truncate 相对 delete 速度快。

3、由于 truncate 命令比较危险，因此在实际开发中，truncate 命令慎用。

exp/imp 导入导出数据

1、在安装了 Oracle 后，在 bin 目录下提供了很多工具，其中就有 exp 与 imp. 可以在任意目录下的 cmd 中执行这些命令：

--导出数据库-- cmd 中执行如下命令，hnbs 为目标 Oracle 数据库中的账号，1 是密码，...240/1521 是目标数据库 ip 与端口，file 后面跟保存的 .dmp 文件路径-- log 用于指定日志文件，不需要记录日志时可以省略不写exp hnbs/1@192.168.2.240:1521/orcl file=d:/hnbs.dmp log=d:/hnbs.log--在新数据库中创建同名的用户。在 plsql 等工具中执行命令。如果导出的数据库有表空间，则新数据库中的用户也建议设置同名的表空间，表空间需要提前创建。--如果导出的数据库没有设置表空间，则这里也可以省略select * from dba_tablespaces; -- 查询数据库所有表空间create user hnbs identified by 1 default tablespace grp--为用户授予权限grant dba to hnbs;--在 cmd 中执行命令，将导出的数据库文件导入到新数据库中,不需要记录日志时，可以省略 log 不写imp hnbs/1@192.168.2.240:1521/orcl file=d:/hnbs.dmp log=d:/hnbs.log--拓展：如果不想和导出的数据库使用一样的用户名称，则也可以创建不同的用户名，使用 fromuser 指定导出的数据库用户名称，touser 指定导入数据库用户名称imp hnbs_3/1@192.168.2.240:1521/orcl file=C:\Users\Lenovo\Desktop\0909yselement.dmp fromuser=hnbs touser=hnbs_3

2、除了导出、导入整个数据库，也可以单独导出、导入某张表：

--导出数据库下的指定表--导出本机（127.0.0.1）用户 ptframe_1 下的 gap_sys_uidetail 表到 c:/wmx/gap_sys_uidetail.dmpexp ptframe_1/1@127.0.0.1:1521/orcl file=c:/wmx/gap_sys_uidetail.dmp tables=(gap_sys_uidetail)--导入数据下的指定表--导入 c:/wmx/gap_sys_uidetail.dmp 表脚本到 192.168.2.240 机器用户 ptframe_1 下。--注意默认情况下 240 上 ptframe_1 用户事先不能有表 gap_sys_uidetail 存在，否则导入失败.--tables 指定的表名必须和.dmp文件中的一致，不能随便写，逗号分割,Linux 中括号需要转义，如 tables=\(gap_sys_uidetail,bas_agency_info\)imp ptframe_1/1@192.168.2.240:1521/orcl file=c:/wmx/gap_sys_uidetail.dmp tables=(gap_sys_uidetail,bas_agency_info)

3、exp、imp 命令的完整参数，可以通过 -help 进行查看：

[root@localhost ~]# su - oracle上一次登录：四 10月 21 17:07:26 CST pts/1 上[oracle@localhost ~]$ exp hn_yszx0312/1@10.104.65.180:1521/orcl file=/home/oracle/hn_yszx0312.dmp log=/home/oracle/hn_yszx0312.log........ exporting dimensions. exporting post-schema procedural objects and actions. exporting statisticsExport terminated successfully without warnings.[oracle@localhost ~]$ ll-rw-r--r--. 1 oracle oinstall 2935046144 10月 21 17:13 hn_yszx0312.dmp-rw-r--r--. 1 oracle oinstall59088 10月 21 17:13 hn_yszx0312.log[oracle@localhost ~]$

IMP-00010: 不是有效的导出文件, 标头验证失败解决办法

oracle 11g exp 空表导出处理

1、oralce 11g 中增加了一个新特性 "deferred_segment_creation-段延迟创建分片"，默认是 true 延迟分片。

2、deferred_segment_creation=true 时，如果 create 了一个 table，并且没有 insert 数据，那么这个表不会立即分配 extend，也就不占数据空间，即表不分配 segment 以节省空间，所以这些表不能 exp 导出来。

3、系统表 user_tables 中的 segment_treated 字段如果为"NO"，表示此表未分配 segment，exp 无法导出，否则为 true 表示此表已经分配了 segment exp 可以导出.

select * from user_tables where segment_created='NO' ;-- 查询当前登陆用户下所有未分片、exp 无法导出的空表

4、解决办法一：对空表主动分片（segment）：

select 'alter table ' ||table_name ||' allocate extent;' from user_tables where segment_created='NO';

运行后生成每张空表的分片 SQL 语句，将其生成的所有 sql 代码复制出来然后全部执行，就可以给每一张已经存在的表来分配 segment，然后就可以利用 exp 导出了。

5、解决办法二：设置系统参数，让后续 create 的新表自动分配 segment：

--查询系统参数 deferred_segment_creation，TRUE 表示延迟分片，FALSE 表示 create 时都分配 segment.show parameter deferred_segment_creation;alter system set deferred_segment_creation=false; --修改参数值为 FALSE，默认自动分片show parameter deferred_segment_creation; -- 再次查询确认参数是否修改成功