Oracle 数据类型，表空间、用户与权限，DDL、DML 语句、约束，exp/imp

偏执的太偏执、 2022-02-01 19:49 420阅读 0赞

**目录**

Oracle 数据类型

Oracle 表空间

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

用户授权、权限查询

用户锁定与解锁

create 创建表与 as 子查询建表

表与视图设置注释

修改表结构 & 删除表

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

插入、更新、删除 表数据

exp/imp 导入导出数据

oracle 11g exp 空表导出处理

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

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

<table> 
 <tbody> 
  <tr> 
   <td>数据定义语言（ DDL ）：包括 CREATE （创建）命令、 ALTER （修改）命令、 DROP （删除）命令等。<br> 数据操纵语言（ DML ）：包括 INSERT （插入）命令、 UPDATE （更新）命令、 DELETE（删除）命令、 SELECT FOR UPDATE （查询）等。<br> 数据查询语言（ DQL ）：包括基本查询语句、 Order By 子句、 Group By 子句等。<br> 事务控制语言（ TCL ）：包括 COMMIT （提交）命令、 SAVEPOINT （保存点）命令、ROLL BACK （回滚）命令。<br> 数据控制语言（ DCL）：&nbsp;GRANT （授权）命令、 REVOKE （撤销）命令。</td> 
  </tr> 
 </tbody> 
</table>

# Oracle 数据类型 #

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

<table> 
 <tbody> 
  <tr> 
   <td><strong>类型</strong></td> 
   <td><strong>描述</strong></td> 
  </tr> 
  <tr> 
   <td>CHAR(length)</td> 
   <td><span style="color:#f33b45;">存储 固定长度 的字符串。</span>参数 length 指定了长度，如 果存储的字符串长度小于 length ，用空格填充。默认长度是 1 ，最长不超过 2000 字节。</td> 
  </tr> 
  <tr> 
   <td>VARCHAR2(length)</td> 
   <td><span style="color:#f33b45;">存储可变长度的字符串</span>。length 指定了该字符串的最大长度。默认长度是 1 ，最长不超过 4000 字符。</td> 
  </tr> 
  <tr> 
   <td>NUMBER(p,s)</td> 
   <td> <p>既可以存储浮点数，也可以存储整数。<span style="color:#f33b45;">p 表示数字的最大位数（如果是小数，则包括整数部分和小数部分</span>， p 默认是 38 位）。 s 是指 小数位数，小数位不能大于总数位。</p> <p>整数部分不允许大于 p -s，否则报错：<span style="color:#fe2c24;">ORA-01438: 值大于为此列指定的允许精度</span></p> </td> 
  </tr> 
  <tr> 
   <td>Number(p)</td> 
   <td>声明一个整数，相当于Number(p, 0)</td> 
  </tr> 
  <tr> 
   <td>DATE</td> 
   <td>存储日期和时间，存储纪元、 4 位年、月、日、时、分、秒，存储时间。从公元前4712 年 1 月 1 日到公元后 4712 年 12 月 31 日。</td> 
  </tr> 
  <tr> 
   <td>TIMESTAMP</td> 
   <td>不但存储日期的年月日，时分秒，以及秒后6 位 ，同时包含时区。</td> 
  </tr> 
  <tr> 
   <td>raw、long raw</td> 
   <td> <p style="margin-left:0cm;">用于存储二进制数据</p> </td> 
  </tr> 
  <tr> 
   <td>CLOB</td> 
   <td><span style="color:#f33b45;">存储大的文本</span>，比如存储非结构化的 XML 文档</td> 
  </tr> 
  <tr> 
   <td>BLOB</td> 
   <td><span style="color:#f33b45;">存储二进制对象</span>，如图形、视频、声音等。</td> 
  </tr> 
 </tbody> 
</table>

NUMBER 类型的示例：

<table> 
 <thead> 
  <tr> 
   <th>格式</th> 
   <th>输入的数字</th> 
   <th>实际存储的值</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>NUMBER</td> 
   <td>3.14159</td> 
   <td>3.14159</td> 
  </tr> 
  <tr> 
   <td>NUMBER(7,2)</td> 
   <td>15000.548</td> 
   <td>15000.55（自动四舍五入）</td> 
  </tr> 
  <tr> 
   <td>NUMBER(7,2)</td> 
   <td>150001.5</td> 
   <td><span style="color:#fe2c24;">整数部分不允许大于5位，所以报错：ORA-01438: 值大于为此列指定的允许精度</span></td> 
  </tr> 
  <tr> 
   <td>NUMBER(4)</td> 
   <td>5443.45</td> 
   <td>5443</td> 
  </tr> 
  <tr> 
   <td>NUMBER(4)</td> 
   <td>54140</td> 
   <td><span style="color:#fe2c24;">整数部分不允许大于4位，所以报错：ORA-01438: 值大于为此列指定的允许精度</span></td> 
  </tr> 
 </tbody> 
</table>

# Oracle 表空间 #

**Oracle 表空间概述**

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

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdteDE5OTMzMjg_size_16_color_FFFFFF_t_70][]

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

<table> 
 <tbody> 
  <tr> 
   <td style="width:96px;">系统表空间</td> 
   <td> <p>存放系统数据，系统表空间在数据库创建时创建。表空间名称为 SYSTEM。存放数据字典和视图以及数据库结构等重要系统数据信息。在运行时如果 SYSTEM 空间不足，对数据库影响会比较大，<span style="color:#f33b45;">虽然在系统运行过程中可以通过命令扩充空间，但还是会影响数据库的性能，因此有必要在创建数据库时适当的把数据文件设置大一些。</span></p> </td> 
  </tr> 
  <tr> 
   <td style="width:96px;">TEMP表空间</td> 
   <td>临时表空间，安装数据库时创建，可以在运行时通过命令增大临时表空间。临时表空间的重要作用是数据排序。比如当用户执行了诸如 Order by 等命令后，服务器需要对所选取数据进行排序，如果数据很大，内存的排序区可能装不下太大数据,就需要把一些中间的排序结果写在硬盘的临时表空间中。</td> 
  </tr> 
  <tr> 
   <td style="width:96px;">用户表自定义空间</td> 
   <td>用户可以通过 CREATE TABLESPACE 命令创建的表空间。推荐一开始就将表空间初始大小设置的大一些。</td> 
  </tr> 
 </tbody> 
</table>

**Oracle 表空间创建**

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

2、表空间创建语法：

<table> 
 <tbody> 
  <tr> 
   <td> <p><span style="color:#f33b45;">create tablespace 表空间名 datafile '数据文件存储路径' size 表空间初始大小 autoextend on next 每次扩展的大小;</span></p> <p>（autoextend on：表示超出初始大小后自动进行扩展）</p> </td> 
  </tr> 
 </tbody> 
</table>

3、表空间只能由管理员创建，如下所示创建表空间 "dongShan"（datafile 的存储目录必须存在，.dbf 文件会自动创建）:

create tablespace dongShan
      datafile 'E:\oracle_tablesapce\dongShan.dbf'
      size 500M 
      autoextend on
      next 50M;

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

![20190530081844206.png][]

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

![20190530082815550.gif][]

**表空间命令汇总**

<table> 
 <tbody> 
  <tr> 
   <td colspan="2">drop tablespace xxx： 删除的是 Oralce 中维护的表空间关系，删除之后 dbf 文件只是一个普通的文件，所以可以手动删除。</td> 
  </tr> 
  <tr> 
   <td style="width:612px;"><span style="color:#f33b45;">select * from dba_tablespaces;</span></td> 
   <td style="width:290px;"><span style="color:#f33b45;">-- 查询所有表空间</span></td> 
  </tr> 
  <tr> 
   <td style="width:612px;"><span style="color:#f33b45;">select distinct tablespace_name&nbsp; from dba_all_tables;</span></td> 
   <td style="width:290px;"><span style="color:#f33b45;">-- 查询所有已经使用过的表空间</span></td> 
  </tr> 
  <tr> 
   <td style="width:612px;"><span style="color:#f33b45;">select *&nbsp; from dba_all_tables where tablespace_name = 'GRP' ;</span></td> 
   <td style="width:290px;"><span style="color:#f33b45;">-- 查询指定表空间下所有的表信息</span></td> 
  </tr> 
  <tr> 
   <td style="width:612px;">select t1.name,t2.name,t2.CREATION_TIME,t2.BYTES,t2.BLOCKS,t2.BLOCK_SIZE&nbsp; from v$tablespace t1,v$datafile t2 where t1.ts# = t2.ts#;</td> 
   <td style="width:290px;"><span style="color:#f33b45;">--查询表空间数据文件存放物理路径</span></td> 
  </tr> 
 </tbody> 
</table>

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

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

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

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

<table> 
 <tbody> 
  <tr> 
   <td>create user c##wmx identified by 123456 default tablespace dongShan;</td> 
   <td>--<span style="color:#f33b45;">创建用户</span>。Oracle 12c 创建用户的时候用户名必须以 c## 或者 C## 开头<br> --oracle12c 创建用户不用 C## 开头时，会报错：Oracle-65096:公用用户名或角色无效</td> 
  </tr> 
  <tr> 
   <td>drop user 用户名名 cascade;</td> 
   <td>--<span style="color:#f33b45;">删除用户</span>，如果用户已经建了表，或者视图等，则必须使用 cascade 进行级联删除<br> --删除用户，必须使用其它管理员账号来进行操作</td> 
  </tr> 
  <tr> 
   <td><span style="color:#f33b45;">select * from all_users; </span></td> 
   <td><span style="color:#f33b45;">--查询系统用户</span></td> 
  </tr> 
  <tr> 
   <td><span style="color:#f33b45;">select * from v$session; </span></td> 
   <td><span style="color:#f33b45;">-- 查询当前所有连接的用户</span></td> 
  </tr> 
  <tr> 
   <td><span style="color:#f33b45;">select * from session_privs; </span></td> 
   <td><span style="color:#f33b45;">-- 查看当前用户权限</span></td> 
  </tr> 
  <tr> 
   <td><span style="color:#f33b45;">select * from all_tables where owner='WORKFLOW_1'; </span></td> 
   <td><span style="color:#f33b45;">-- 查询用户 'WORKFLOW_1' 下的所有表，用户名 WORKFLOW_1 必须大写</span></td> 
  </tr> 
 </tbody> 
</table>

# 用户授权、权限查询 #

**一：角色与权限**

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

<table> 
 <tbody> 
  <tr> 
   <td style="text-align:center;width:256px;"><strong>角色</strong></td> 
   <td colspan="2" style="text-align:center;width:357px;"><strong>权限</strong></td> 
  </tr> 
  <tr> 
   <td style="width:256px;"> <p>connect</p> <p>--最基本的角色，通常授予实施人员</p> </td> 
   <td style="width:341px;"> <p>ALTER SESSION&nbsp; --修改会话</p> <p>CREATE CLUSTER&nbsp;&nbsp; --建立聚簇</p> <p>CREATE DATABASE LINK&nbsp;&nbsp; --建立数据库链接</p> </td> 
   <td style="width:305px;"> <p>CREATE SEQUENCE&nbsp; --建立序列</p> <p>CREATE SESSION&nbsp; --建立会话</p> <p>CREATE SYNONYM&nbsp; --建立同义词</p> <p>CREATE VIEW&nbsp; --建立视图</p> </td> 
  </tr> 
  <tr> 
   <td style="width:256px;"> <p>resource</p> <p>--通常授予开发人员</p> </td> 
   <td style="width:341px;"> <p>CREATE PROCEDERE&nbsp;&nbsp; --建立存储过程/函数</p> <p>CREATE TABLE&nbsp;&nbsp; --建表</p> </td> 
   <td style="width:305px;"> <p>CREATE TRIGGER&nbsp; --建立触发器</p> <p>CREATE TYPE&nbsp; --建立类型</p> </td> 
  </tr> 
  <tr> 
   <td style="width:256px;"> <p>dba</p> <p>--拥有全部权限，是系统最高权限</p> </td> 
   <td colspan="2" style="width:646px;"><span style="color:#f33b45;">只有 dba 才可以创建数据库结构，系统权限也只能由 dba 授出，dba 可以操作全体用户（包括其它dba）的任意表，包括删除表。</span></td> 
  </tr> 
 </tbody> 
</table>

**二：授权语法**

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

<table> 
 <tbody> 
  <tr> 
   <td>grant connect to c##wmx;</td> 
   <td>--授予 connect 角色给 c##wmx 用户</td> 
  </tr> 
  <tr> 
   <td>grant resource to c##wmx;</td> 
   <td>--授予 resource 角色给 c##wmx 用户</td> 
  </tr> 
  <tr> 
   <td>grant dba to c##wmx;</td> 
   <td>--授予 dba 角色给 c##wmx 用户</td> 
  </tr> 
  <tr> 
   <td>grant create table to c##scott;<br> grant create view to c##scott;</td> 
   <td> <p><span style="color:#f33b45;">--赋予scott 用户创建 VIEW（表） 的权限</span></p> <p><span style="color:#f33b45;">--赋予scott 用户创建 VIEW（视图） 的权限</span></p> </td> 
  </tr> 
  <tr> 
   <td colspan="2"> <p><span style="color:#f33b45;">grant connect,resource to soctt; -- oracle 自带的 scott 用户就是默认拥有&nbsp; connect,resource 角色</span></p> </td> 
  </tr> 
 </tbody> 
</table>

**三、权限查询**

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

<table> 
 <tbody> 
  <tr> 
   <td>select * from user_role_privs;&nbsp;&nbsp;&nbsp; --查询当前登陆用户的角色，如 CONNECT、RESOURCE、DBA 角色</td> 
  </tr> 
  <tr> 
   <td>select * from session_privs;&nbsp; --查询当前登陆用户拥有的权限，如 CREATE TABLE（建表）、CREATE TRIGGER（创建触发器）</td> 
  </tr> 
  <tr> 
   <td>select * from dba_role_privs where grantee='SCOTT';&nbsp;&nbsp; -- dba 账户查询其它指定账户的的角色信息</td> 
  </tr> 
  <tr> 
   <td> <p>--dba 账户查询其它指定账户的的权限信息<br> select * from dba_sys_privs where grantee='SCOTT'&nbsp;&nbsp;</p> <p>union &nbsp;<br> select * from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='SCOTT' );</p> </td> 
  </tr> 
  <tr> 
   <td>select * from dba_roles; &nbsp; --dba 账户查询所有角色</td> 
  </tr> 
 </tbody> 
</table>

![20200626102456678.gif][]

**四：为用户设置某个表/视图的 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;

![20200626110931738.gif][]

**五：权限收回**

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

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

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

<table> 
 <tbody> 
  <tr> 
   <td> <p>grant resource to scott; -- 授权 scott 用户 resource 角色（dba 账户操作）</p> <p>revoke resource from scott; -- 收回 scott 的 resource 角色（dba 账户操作）</p> </td> 
  </tr> 
  <tr> 
   <td> <p>grant create view to scott;&nbsp; -- 授权 scott 用户创建视图(create view) 的权限（dba 账户操作）</p> <p>revoke create view from scott;&nbsp; -- 收回 scott 创建视图(create view) 的权限（dba 账户操作）</p> </td> 
  </tr> 
  <tr> 
   <td> <p>grant update on hnbs_3.gbm_bs_ageninfo to scott; --&nbsp; 授权 scott 用户对 hnbs_3 用户下 gbm_bs_ageninfo 表的更新权限（dba 账户操作）</p> <p>revoke update on hnbs_3.gbm_bs_ageninfo from scott;&nbsp; -- 收回 scott 用户对 hnbs_3 用户下 gbm_bs_ageninfo 表的更新权限（dba 账户操作）</p> </td> 
  </tr> 
 </tbody> 
</table>

# 用户锁定与解锁 #

<table> 
 <tbody> 
  <tr> 
   <td> <p>--查询系统用户信息，如账号状态、是否被锁定，密码是否失效，默认表空间等.<br> select t.username as 用户名,<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; t.user_id as 用户id,<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; t.password as 用户密码,<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; t.account_status as 账号状态, -- OPEN:表示开放的；LOCKED：表示账号被锁定；EXPIRED：表示密码已过期<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; t.lock_date as 账号锁定日期,<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; t.expiry_date as 密码过期日期,<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; t.default_tablespace as 默认表空间,<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; t.temporary_tablespace as 临时表空间 ,<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; t.created 创建日期,<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; t.profile,<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; t.initial_rsrc_consumer_group,<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; t.external_name,<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; t.password_versions,<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; t.editions_enabled,<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; t.authentication_type from dba_users t ;-- 可以带 where 条件查询指定用户 如 where username='SCOTT'</p> <p>alter user SCOTT account lock; -- 锁定指定用户/账号<br> alter user SCOTT account unlock; -- 解除锁定指定用户/账号<br> alter user SCOTT identified by 1; -- 为指定用户/账号修改密码（如果密码已经过期，则需要为其修改密码）</p> </td> 
  </tr> 
 </tbody> 
</table>

![20200706140403122.gif][]

# 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 '主键';

![20190530094206980.gif][]

<table> 
 <tbody> 
  <tr> 
   <td>1）desc 用于查看表的结构，它不属于 sql ，所以需要到命令窗口中操作。<br> 2）更多建表与约束操作可以参考下面的“五大约束”部分。<br> 3）select * from all_tables where owner='WORKFLOW_1'; -- 查询用户 'WORKFLOW_1' 下的所有表，用户名 WORKFLOW_1 必须大写<br> 4）select * from user_tables; --查询当前登陆用户下的所有表</td> 
  </tr> 
 </tbody> 
</table>

**create as 子查询建表**

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

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

<table> 
 <tbody> 
  <tr> 
   <td> <p>&nbsp;CREATE TABLE t2 AS SELECT * FROM emp; -- 如果子查询有结果，则复制表结构的同时也会复制表中的数据<br> &nbsp;CREATE TABLE t3 AS SELECT c1,c2,c3 FROM emp; -- 只复制目标表的部分字段<br> &nbsp;CREATE TABLE t4 AS SELECT * FROM emp WHERE 1 = 2; -- 只复制表结构</p> </td> 
  </tr> 
 </tbody> 
</table>

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

# **表与视图设置注释** #

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

<table> 
 <tbody> 
  <tr> 
   <td>comment on table <span style="color:#f33b45;">dept&nbsp; </span>is '部门表';&nbsp; --为 dept 表添加注释<br> comment on column <span style="color:#f33b45;">dept</span>.deptno is '部门编号'; --为 dept 表的 deptno 字段 添加注释</td> 
  </tr> 
 </tbody> 
</table>

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

<table> 
 <tbody> 
  <tr> 
   <td> <p>create or replace view vw_dept as select t1.deptno,t1.dname,t1.loc from <span style="color:#f33b45;">dept </span>t1 ; --创建视图</p> <p>--原表中字段的注释是不会出现在视图中的，仍然需要为视图字段设置注释，语法和表字段设置注释一样</p> <p>comment on column vw_dept.deptno is '部门编号';<br> comment on column vw_dept.dname is '部门名称';<br> comment on column vw_dept.loc is '部门地址';</p> </td> 
  </tr> 
 </tbody> 
</table>

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

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

<table> 
 <tbody> 
  <tr> 
   <td> <p>--查询当前登陆用户下所有表和视图的注释信息<br> select t.TABLE_NAME as 表或视图名,t.COLUMN_NAME as 列名,t.COMMENTS as 列注释,t.ORIGIN_CON_ID as 行起始的容器的ID from user_col_comments t ;<br> select * from user_col_comments t where t.COMMENTS like '%指标%'; -- 查询某个注释信息</p> <p>select * from user_col_comments t where t.TABLE_NAME='SAF_REQUEST'; -- 查询某个表中的列的注释信息</p> </td> 
  </tr> 
 </tbody> 
</table>

# 修改表结构 & 删除表 #

<table> 
 <tbody> 
  <tr> 
   <td>alter table 表名 add (列1 数据类型,列2, 数据类型....);</td> 
   <td>添加列：-- <span style="color:#003366;">如果表中已有数据,则新增列不可约束为"NOT NULL"</span></td> 
  </tr> 
  <tr> 
   <td>alter table 表名 modify (列1 新数据类型 [约束]..);</td> 
   <td>修改列</td> 
  </tr> 
  <tr> 
   <td><span style="color:#003366;">alter table 表名 drop column 列名</span></td> 
   <td>删除列</td> 
  </tr> 
  <tr> 
   <td>alter table 表名 rename column 旧列名 to 新列</td> 
   <td>修改列名</td> 
  </tr> 
  <tr> 
   <td>rename 旧表名 to 新表名</td> 
   <td>修改表名</td> 
  </tr> 
  <tr> 
   <td>drop table 表名;</td> 
   <td>删除表</td> 
  </tr> 
  <tr> 
   <td>truncate table PERSON2;<span style="color:#f33b45;"> --注意此操作数据不可恢复!</span></td> 
   <td>清空表数据</td> 
  </tr> 
 </tbody> 
</table>

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 列，不允许为 null
    alter table saf_fund_account add fund_name varchar2(60) not null;
    -- 修改表 student 的列 age 的数据类型为 number(2)，默认值为 0
    alter 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)
    is
      flag number := 0; --表是否存在的表示，大于0表示表已经存在
    begin
        --user_tables 是系统定义的视图，可以查看当前用户下的所有表信息，表中的表名区分大小写，而且是大写
        select count(1) into flag from user_tables where table_name = upper(tableName) ;
        if flag > 0 then
            execute 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;--查询

![20200314105034681.gif][]

**建表后设置约束**

--创建学生表
    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_DATETIME
    ALTER 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;

![20190530110358738.png][]

--插入数据
    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;

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

<table> 
 <tbody> 
  <tr> 
   <td> <p><span style="color:#f33b45;">alter table</span> student <span style="color:#f33b45;">disable constraints</span> uk_stuid;--禁用 student 表的唯一约束 uk_stuid，禁用后索引也会暂时失效。<span style="color:#f33b45;">后面的约束名称也可以是主键约束名称、或者外键约束名称</span><br><span style="color:#f33b45;">alter table</span> student <span style="color:#f33b45;">enable constraints</span> uk_stuid;--启用 student 表的唯一约束 uk_stuid，<span style="color:#f33b45;">后面的约束名称也可以是主键约束名称、或者外键约束名称</span><br><span style="color:#f33b45;">alter table </span>student <span style="color:#f33b45;">drop constraint </span>uk_stuid;-- 删除 student 表的唯一约束 uk_stuid，<span style="color:#f33b45;">后面的约束名称也可以是主键约束名称、或者外键约束名称</span></p> <p>--如果删除主键约束时，该主键是另一表的外键，则该主键不能直接删除，除非连带把外键约束也删除。<br> alter table dept drop constraint pk_dept cascade;</p> </td> 
  </tr> 
 </tbody> 
</table>

# 插入、更新、删除 表数据 #

<table> 
 <tbody> 
  <tr> 
   <td> <p>insert into 表名 values(value1,value2,value3,...);--表中所有列按顺序给出所有值<br> insert into 表名(列1,列2,列3...) values(value1,value2,value3,...);--为部分列添加值<br> insert into 表名 查询语句;--使用子查询插入数据</p> <p>insert into t1 (c1,c2,c3) select b1,b2,b3 from t2; -- 将表 t2 中的 b1,b2,b3 字段值复制到 t1 表的 c1,c2,c3 中</p> <p><span style="color:#f33b45;">insert xxx select xxx 语法：要求 select 结果集中每一列的数据类型必须与 insert 表中的每一列的数据类型一致，结果集中的列的数量与表中的列的数量一致。</span></p> <p>-- 批量插入（效率高于逐个插入）<br> insert into tableName (id, user_id, title) values (1, 2, 'a'),(2,3,'b');</p> </td> 
  </tr> 
 </tbody> 
</table>

--当前使用的是拥有管理员权限的用户 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('2019/06/25 15:42:00','YYYY-MM-DD HH24:MI:SS'),9800.80,100,20);

![2019053018193779.gif][]

select * from dept;
    --更新数据格式：update 表名 set 列名1=value,列名2=value,... [where 条件];
    update dept set dname='测试部',loc='长沙' where deptno = 50;--如果不加 where 条件，则整个表的 dname 都会被更新
    update dept set dname='人事部' where deptno = 60;
    
    --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\20190909yselement.dmp fromuser=hnbs touser=hnbs_3

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

--导出数据库下的指定表
    --导出本机（127.0.0.1）用户 ptframe_1 下的 gap_sys_uidetail 表到 c:/wmx/gap_sys_uidetail.dmp
    exp 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文件中的一致，不能随便写
    imp ptframe_1/1@192.168.2.240:1521/orcl file=c:/wmx/gap_sys_uidetail.dmp tables=(gap_sys_uidetail)

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

<table> 
 <tbody> 
  <tr> 
   <td> <p>exp</p> </td> 
   <td><span style="color:#f33b45;">默认表中数据都会一并导出，如果只想导出表结构，不需要导出数据，则 exp 命令结尾加上 rows=n 参数</span>（默认为 y）；</td> 
  </tr> 
  <tr> 
   <td>imp</td> 
   <td>默认会导入 .dmp 文件中的表结构和数据，如果只想导入数据而不导入表结构，则加上<span style="color:#f33b45;"> data_only=y </span>参数（默认为 n）;</td> 
  </tr> 
 </tbody> 
</table>

![20200810155444405.gif][]

[IMP-00010: 不是有效的导出文件, 标头验证失败解决办法][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; -- 再次查询确认参数是否修改成功

![20200212164145258.gif][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdteDE5OTMzMjg_size_16_color_FFFFFF_t_70]: /images/20220126/c9b055bf929341f9a0b628cd1d4f3a81.png
[20190530081844206.png]: /images/20220126/6719ae1a076844129d1da9b1d41eb290.png
[20190530082815550.gif]: /images/20220126/745977e0711342238d17ec10bae4756a.png
[20200626102456678.gif]: /images/20220126/b5a2602a26f444b08a4b256aea1c3cfc.png
[20200626110931738.gif]: /images/20220126/66ee6236892b41b0ab30f5c95c27a9d5.png
[20200706140403122.gif]: /images/20220126/f83f453d85dd4fc4a905dac1445ce677.png
[20190530094206980.gif]: /images/20220126/1a41615bf9a34797abb018b131e74330.png
[20200314105034681.gif]: /images/20220126/406088ad52454f83be08e6f2e77944cc.png
[20190530110358738.png]: /images/20220126/caaea9491e244c8faaeb29731632d8ea.png
[2019053018193779.gif]: /images/20220126/3483645e70084a76a5be436835d5c8e5.png
[20200810155444405.gif]: /images/20220126/1a35a8bacc54435b9930a37e097bbbea.png
[IMP-00010_ _]: https://wangmaoxiong.blog.csdn.net/article/details/106134401#IMP-00010%3A%20%E4%B8%8D%E6%98%AF%E6%9C%89%E6%95%88%E7%9A%84%E5%AF%BC%E5%87%BA%E6%96%87%E4%BB%B6%2C%20%E6%A0%87%E5%A4%B4%E9%AA%8C%E8%AF%81%E5%A4%B1%E8%B4%A5
[20200212164145258.gif]: /images/20220126/12b7b6c9076d49c6b2e13953b4840a33.png