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

Oracle 专栏收录该内容
14 篇文章 0 订阅

目录

Oracle 数据类型

Oracle 表空间

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

用户授权、权限查询

用户锁定与解锁

create 创建表与 as 子查询建表

表与视图设置注释

修改表结构 & 删除表

五大约束:主、外键,唯一,非空,检查

插入、更新、删除 表数据

exp/imp 导入导出数据

oracle 11g exp 空表导出处理


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

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

Oracle 数据类型

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

类型描述
CHAR(length)存储 固定长度 的字符串。参数 length 指定了长度,如 果存储的字符串长度小于 length ,用空格填充。默认长度是 1 ,最长不超过 2000 字节。
VARCHAR2(length)存储可变长度的字符串。length 指定了该字符串的最大长度。默认长度是 1 ,最长不超过 4000 字符。
NUMBER(p,s)

既可以存储浮点数,也可以存储整数。p 表示数字的最大位数(如果是小数,则包括整数部分和小数部分, p 默认是 38 位)。 s 是指 小数位数,小数位不能大于总数位。

整数部分不允许大于 p -s,否则报错:ORA-01438: 值大于为此列指定的允许精度

Number(p)声明一个整数,相当于Number(p, 0)
DATE存储日期和时间,存储纪元、 4 位年、月、日、时、分、秒,存储时间。从公元前4712 年 1 月 1 日到公元后 4712 年 12 月 31 日。
TIMESTAMP不但存储日期的年月日,时分秒,以及秒后6 位 ,同时包含时区。
raw、long raw

用于存储二进制数据

CLOB存储大的文本,比如存储非结构化的 XML 文档
BLOB存储二进制对象,如图形、视频、声音等。

NUMBER 类型的示例:

格式输入的数字实际存储的值
NUMBER3.141593.14159
NUMBER(7,2)15000.54815000.55(自动四舍五入)
NUMBER(7,2)150001.5整数部分不允许大于5位,所以报错:ORA-01438: 值大于为此列指定的允许精度
NUMBER(4)5443.455443
NUMBER(4)54140整数部分不允许大于4位,所以报错:ORA-01438: 值大于为此列指定的允许精度

Oracle 表空间

Oracle 表空间概述

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

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

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

系统表空间

存放系统数据,系统表空间在数据库创建时创建。表空间名称为 SYSTEM。存放数据字典和视图以及数据库结构等重要系统数据信息。在运行时如果 SYSTEM 空间不足,对数据库影响会比较大,虽然在系统运行过程中可以通过命令扩充空间,但还是会影响数据库的性能,因此有必要在创建数据库时适当的把数据文件设置大一些。

TEMP表空间临时表空间,安装数据库时创建,可以在运行时通过命令增大临时表空间。临时表空间的重要作用是数据排序。比如当用户执行了诸如 Order by 等命令后,服务器需要对所选取数据进行排序,如果数据很大,内存的排序区可能装不下太大数据,就需要把一些中间的排序结果写在硬盘的临时表空间中。
用户表自定义空间用户可以通过 CREATE TABLESPACE 命令创建的表空间。推荐一开始就将表空间初始大小设置的大一些。

Oracle 表空间创建

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

2、表空间创建语法:

create tablespace 表空间名 datafile '数据文件存储路径' size 表空间初始大小 autoextend on next 每次扩展的大小;

(autoextend on:表示超出初始大小后自动进行扩展)

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

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

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

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

表空间命令汇总

drop tablespace xxx: 删除的是 Oralce 中维护的表空间关系,删除之后 dbf 文件只是一个普通的文件,所以可以手动删除。
select * from dba_tablespaces;-- 查询所有表空间
select distinct tablespace_name  from dba_all_tables;-- 查询所有已经使用过的表空间
select *  from dba_all_tables where tablespace_name = 'GRP' ;-- 查询指定表空间下所有的表信息
select t1.name,t2.name,t2.CREATION_TIME,t2.BYTES,t2.BLOCKS,t2.BLOCK_SIZE  from v$tablespace t1,v$datafile t2 where t1.ts# = t2.ts#;--查询表空间数据文件存放物理路径

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

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

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

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

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

用户授权、权限查询

一:角色与权限

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

角色权限

connect

--最基本的角色,通常授予实施人员

ALTER SESSION  --修改会话

CREATE CLUSTER   --建立聚簇

CREATE DATABASE LINK   --建立数据库链接

CREATE SEQUENCE  --建立序列

CREATE SESSION  --建立会话

CREATE SYNONYM  --建立同义词

CREATE VIEW  --建立视图

resource

--通常授予开发人员

CREATE PROCEDERE   --建立存储过程/函数

CREATE TABLE   --建表

CREATE TRIGGER  --建立触发器

CREATE TYPE  --建立类型

dba

--拥有全部权限,是系统最高权限

只有 dba 才可以创建数据库结构,系统权限也只能由 dba 授出,dba 可以操作全体用户(包括其它dba)的任意表,包括删除表。

二:授权语法

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

grant connect to c##wmx;--授予 connect 角色给 c##wmx 用户
grant resource to c##wmx;--授予 resource 角色给 c##wmx 用户
grant dba to c##wmx;--授予 dba 角色给 c##wmx 用户
grant create table to c##scott;
grant create view to c##scott;

--赋予scott 用户创建 VIEW(表) 的权限

--赋予scott 用户创建 VIEW(视图) 的权限

grant connect,resource to soctt; -- oracle 自带的 scott 用户就是默认拥有  connect,resource 角色

三、权限查询

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

select * from user_role_privs;    --查询当前登陆用户的角色,如 CONNECT、RESOURCE、DBA 角色
select * from session_privs;  --查询当前登陆用户拥有的权限,如 CREATE TABLE(建表)、CREATE TRIGGER(创建触发器)
select * from dba_role_privs where grantee='SCOTT';   -- dba 账户查询其它指定账户的的角色信息

--dba 账户查询其它指定账户的的权限信息
select * from dba_sys_privs where grantee='SCOTT'  

union  
select * from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='SCOTT' );

select * from dba_roles;   --dba 账户查询所有角色

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

grant resource to scott; -- 授权 scott 用户 resource 角色(dba 账户操作)

revoke resource from scott; -- 收回 scott 的 resource 角色(dba 账户操作)

grant create view to scott;  -- 授权 scott 用户创建视图(create view) 的权限(dba 账户操作)

revoke create view from scott;  -- 收回 scott 创建视图(create view) 的权限(dba 账户操作)

grant update on hnbs_3.gbm_bs_ageninfo to scott; --  授权 scott 用户对 hnbs_3 用户下 gbm_bs_ageninfo 表的更新权限(dba 账户操作)

revoke update on hnbs_3.gbm_bs_ageninfo from scott;  -- 收回 scott 用户对 hnbs_3 用户下 gbm_bs_ageninfo 表的更新权限(dba 账户操作)

用户锁定与解锁

--查询系统用户信息,如账号状态、是否被锁定,密码是否失效,默认表空间等.
select t.username as 用户名,
       t.user_id as 用户id,
       t.password as 用户密码,
       t.account_status as 账号状态, -- OPEN:表示开放的;LOCKED:表示账号被锁定;EXPIRED:表示密码已过期
       t.lock_date as 账号锁定日期,
       t.expiry_date as 密码过期日期,
       t.default_tablespace as 默认表空间,
       t.temporary_tablespace as 临时表空间 ,
       t.created 创建日期,
       t.profile,
       t.initial_rsrc_consumer_group,
       t.external_name,
       t.password_versions,
       t.editions_enabled,
       t.authentication_type from dba_users t ;-- 可以带 where 条件查询指定用户 如 where username='SCOTT'

alter user SCOTT account lock; -- 锁定指定用户/账号
alter user SCOTT account unlock; -- 解除锁定指定用户/账号
alter user SCOTT identified by 1; -- 为指定用户/账号修改密码(如果密码已经过期,则需要为其修改密码)

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

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

create as 子查询建表

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

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

 CREATE TABLE t2 AS SELECT * FROM emp; -- 如果子查询有结果,则复制表结构的同时也会复制表中的数据
 CREATE TABLE t3 AS SELECT c1,c2,c3 FROM emp; -- 只复制目标表的部分字段
 CREATE TABLE t4 AS SELECT * FROM emp WHERE 1 = 2; -- 只复制表结构

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

表与视图设置注释

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

comment on table dept  is '部门表';  --为 dept 表添加注释
comment on column dept.deptno is '部门编号'; --为 dept 表的 deptno 字段 添加注释

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

create or replace view vw_dept as select t1.deptno,t1.dname,t1.loc from dept t1 ; --创建视图

--原表中字段的注释是不会出现在视图中的,仍然需要为视图字段设置注释,语法和表字段设置注释一样

comment on column vw_dept.deptno is '部门编号';
comment on column vw_dept.dname is '部门名称';
comment on column vw_dept.loc is '部门地址';

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

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

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

select * from user_col_comments t where t.TABLE_NAME='SAF_REQUEST'; -- 查询某个表中的列的注释信息

修改表结构 & 删除表

alter table 表名 add (列1 数据类型,列2, 数据类型....);添加列:-- 如果表中已有数据,则新增列不可约束为"NOT NULL"
alter table 表名 modify (列1 新数据类型 [约束]..);修改列
alter table 表名 drop column 列名删除列
alter table 表名 rename column 旧列名 to 新列修改列名
rename 旧表名 to 新表名修改表名
drop table 表名;删除表
truncate table PERSON2; --注意此操作数据不可恢复!清空表数据
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;--查询

建表后设置约束

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

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

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

alter table student disable constraints uk_stuid;--禁用 student 表的唯一约束 uk_stuid,禁用后索引也会暂时失效。后面的约束名称也可以是主键约束名称、或者外键约束名称
alter table student enable constraints uk_stuid;--启用 student 表的唯一约束 uk_stuid,后面的约束名称也可以是主键约束名称、或者外键约束名称
alter table student drop constraint uk_stuid;-- 删除 student 表的唯一约束 uk_stuid,后面的约束名称也可以是主键约束名称、或者外键约束名称

--如果删除主键约束时,该主键是另一表的外键,则该主键不能直接删除,除非连带把外键约束也删除。
alter table dept drop constraint pk_dept cascade;

插入、更新、删除 表数据

insert into 表名 values(value1,value2,value3,...);--表中所有列按顺序给出所有值
insert into 表名(列1,列2,列3...) values(value1,value2,value3,...);--为部分列添加值
insert into 表名 查询语句;--使用子查询插入数据

insert into t1 (c1,c2,c3) select b1,b2,b3 from t2; -- 将表 t2 中的 b1,b2,b3 字段值复制到 t1 表的 c1,c2,c3 中

insert into xxx(c1,c2,c3) select c1,c2,c3 语法:要求 select 结果集中每一列的数据类型必须与 insert 表中的每一列的数据类型一致,结果集中的列的数量与表中的列的数量一致。

-- 批量插入(效率高于逐个插入)
insert into tableName (id, user_id, title) values (1, 2, 'a'),(2,3,'b');

--当前使用的是拥有管理员权限的用户 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);

--复制部门数据并重新插入,主键 deptno 再原基础上减1
insert into dept (deptno, loc, dname) select t2.deptno - 1, t2.loc, t2.dname from dept t2 where t2.deptno <= 30;

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 进行查看:

exp

默认表中数据都会一并导出,如果只想导出表结构,不需要导出数据,则 exp 命令结尾加上 rows=n 参数(默认为 y);
imp默认会导入 .dmp 文件中的表结构和数据,如果只想导入数据而不导入表结构,则加上 data_only=y 参数(默认为 n);
对于 Linux 系统,必须先使用 su - oracle 切换到 oralce 用户,然后再使用 exp 、imp 命令
[root@localhost ~]# su - oracle
上一次登录:四 10月 21 17:07:26 CST 2021pts/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 statistics
Export 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 oinstall      59088 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; -- 再次查询确认参数是否修改成功

 

  • 2
    点赞
  • 9
    评论
  • 4
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 游动-白 设计师:白松林 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值