本文链接：https://blog.csdn.net/GoldenKitten/article/details/84947386 以下内容为转载以上博客，自己做了略微的补充，如需查看原文，请点击上面的链接，指引到原博主的博客。

1 Oracle

1.1 Oracle简介

ORACLE数据库系统是美国ORACLE公司（甲骨文）提供的以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一。比如SilverStream就是基于数据库的一种中间件。ORACLE数据库是目前世界上使用最为广泛的数据库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个关系数据库，它是一个完备关系的产品；作为分布式数据库它实现了分布式处理]功能。但它的所有知识，只要在一种机型上学习了ORACLE知识，便能在各种类型的机器上使用它。

1.2 Oracle体系结构

它由至少一个表空间和数据库模式对象组成。这里，模式是对象的集合，而模式对象是直接引用数据库数据的逻辑结构。模式对象包括这样一些结构:表、视图、序列、存储过程、同义词、索引、簇和数据库链等。逻辑存储结构包括表空间、段和范围，用于描述怎样使用数据库的物理空间。

总之,逻辑结构由逻辑存储结构(表空间,段,范围,块)和逻辑数据结构(表、视图、序列、存储过程、同义词、索引、簇和数据库链等)组成,而其中的模式对象(逻辑数据结构)和关系形成了数据库的关系设计。

段(Segment):是表空间中一个指定类型的逻辑存储结构，它由一个或多个范围组成，段将占用并增长存储空间。

数据库的物理存储结构是由一些多种物理文件组成，主要有数据文件、控制文件、重做日志文件、归档日志文件、参数文件、口令文件、警告文件等。

控制文件:存储实例、数据文件及日志文件等信息的二进制文件。alter system set control_files='路径'。V$CONTROLFILE。

数据文件:存储数据，以.dbf做后缀。一句话:一个表空间对多个数据文件，一个数据文件只对一个表空间。dba_data_files/v$datafile。

数据库： Oracle数据库是数据的物理存储。这就包括（数据文件ORA或者DBF、控制文件、联机日志、参数文件）。其实Oracle数据库的概念和其它数据库不一样，这里的数据库是一个操作系统只有一个库。可以看作是Oracle就只有一个大数据库。
实例：一个Oracle实例（Oracle Instance）有一系列的后台进程（Backguound Processes)和内存结构（Memory Structures)组成。一个数据库可以有n个实例。
用户：用户是在实例下建立的。不同实例可以建相同名字的用户。
表空间：表空间是Oracle对物理数据库上相关数据文件（ORA或者DBF文件）的逻辑映射。一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一个表空间(称之为system表空间)。每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)。一个数据文件只能属于一个表空间。
数据文件（dbf、ora）: 数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真正是在某一个或者多个数据文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。
注：表的数据，是由用户放入某一个表空间的，而这个表空间会随机把这些表数据放到一个或者多个数据文件中。
由于oracle的数据库不是普通的概念，oracle是有用户和表空间对数据进行管理和存放的。但是表不是有表空间去查询的，而是由用户去查的。因为不同用户可以在同一个表空间建立同一个名字的表！这里区分就是用户了！

1.3

DDL : 数据定义语言 create alter drop truncate

DML : 数据操纵语言 insert update delete

DCL : 数据控制语言 安全 授权 grant revoke

DQL : 数据查询语言 select from子句 where子句

1.3 Oracle常用操作

1.3.1 登录

运行cmd进入命令行

Sqlplus 用户名/密码@ip地址:1521/orcl [as sysdba]

如果是超级管理员需要在用户名/密码后面加上 as sysdba,是以系统管理员的身份来登录的

sqlplus sys/sys as sysdba;

如果是普通用户不需要as sysdba

sqlplus scott/tiger

1.3.2 查看当前连接数据库的用户

-- 只在sql plus中有效

show user

-- PL/SQL中使用下面的语句

select user from dual

1.3.3 用户的切换

#切换为超级管理员

conn sys/sys as sysdba;

#切换为普通用户

conn scott/scott

1.3.4 查看用户下的表

在用户登录的情况下输入：select * from tab;

为了防止命令行中数据展示表格错乱的情况可以设计行宽和列宽

    set  linesize 300;    每行展示300个字符

    Col 列名     for  a[数字]，某一列占几个字符

1.3.5 查看表结构

desc 表名;

1.3.6 创建表空间

表空间， ORACLE数据库的逻辑单元。数据库—表空间一个表空间可以与多个数据文件（物理结构）关联,一个数据库下可以建立多个表空间，一个表空间可以建立多个用户、一个用户下可以建立多个表。

create tablespace test1

datafile 'E:\test1.dbf'

size 100m

autoextend on

next 10m

# test1 为表空间名称

# datafile  指定表空间对应的数据文件

# size  后定义的是表空间的初始大小

# autoextend on  自动增长 ，当表空间存储都占满时，自动增长

# next 后指定的是一次自动增长的大小。

1.3.7 创建用户

create user c##testuser

identified by testuser

default tablespace test1

# oracle12 用户名必须以c##开头

# identified by  后边是用户的密码

# default tablespace 后边是表空间名称

# oracle数据库与其它数据库产品的区别在于，表和其它的数据库对象都是存储在用户下的。

新创建用户没有任何权限，登录会报错：

Oracle中已存在三个重要的角色：connect角色，resource角色，dba角色。

CONNECT角色： --是授予最终用户的典型权利，最基本的

ALTER SESSION --修改会话

CREATE CLUSTER --建立聚簇

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

CREATE SEQUENCE --建立序列

CREATE SESSION --建立会话

CREATE SYNONYM --建立同义词

CREATE VIEW --建立视图

RESOURCE角色： --是授予开发人员的

CREATE CLUSTER --建立聚簇

CREATE PROCEDURE --建立过程

CREATE SEQUENCE --建立序列

CREATE TABLE --建表

CREATE TRIGGER --建立触发器

CREATE TYPE --建立类型

DBA角色：拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构，并且系统权限也需要DBA授出，且DBA用户可以操作全体用户的任意基表，包括删除

# 进入system用户下给用户赋予dba权限，否则无法正常登陆

grant dba to testuser

1.3.8 文件操作命令

start和@ 说明: 运行sql脚本

     案例: sql>@ d:\a.sql或是sql>start d:\a.sql

edit 说明: 该命令可以编辑指定的sql脚本

    案例: sql>edit d:\a.sql,这样会把d:\a.sql这个文件打开

spool 说明: 该命令可以将sql*plus屏幕上的内容输出到指定文件中去。

    案例: sql>spool d:\b.sql并输入sql>spool off eg、 sql>spool d:\b.sql; sql>select * from emp; sql>spool off;

1.3.9 交互式命令

&说明：可以替代变量，而该变量在执行时，需要用户输入。 select * from emp where job='&job'；

1.4 用户管理

1.4.1 创建用户

概述：在oracle中要创建一个新的用户使用create user语句，一般是具有dba(数据库管理员)的权限才能使用。

create user 用户名 identified by 密码;

注意：oracle有个毛病，密码必须以字母开头，如果以数字开头，它不会创建用户 create user xiaoming identified by oracle;

1.4.2 给用户修改密码

概述：如果给自己修改密码可以直接使用SQL> password 用户名或passw,如果给别人修改密码则需要具有dba的权限，或是拥有alter user的系统权限 SQL> alter user 用户名 identified by 新密码

1.4.3 删除用户

概述：一般以dba的身份去删除某个用户，如果用其它用户去删除用户则需要具有drop user的权限。比如drop user 用户名【cascade】注意：在删除用户时，如果要删除的用户，已经创建了表，那么就需要在删除的时候带一个参数cascade，即把该用户及表一同删除;

1.4.4 权限

权限分为系统权限和对象权限。何为系统权限？用户对数据库的相关权限，connect、resource、dba等系统权限，如建库、建表、建索引、建存储过程、登陆数据库、修改密码等。何为对象权限？用户对其他用户的数据对象操作的权限，insert、delete、update、select、all等对象权限，数据对象有很多，比如表，索引，视图，触发器、存储过程、包等。执行SELECT * FROM Dba_Object_Size;语句可得到oracle数据库对象。

1.4.5 角色

角色分为预定义角色和自定义角色。

1.4.6 用户管理的综合案例

概述：创建的新用户是没有任何权限的，甚至连登陆的数据库的权限都没有，需要为其指定相应的权限。给一个用户赋权限使用命令grant，回收权限使用命令revoke。为了讲清楚用户的管理，这里我给大家举一个案例。

SQL> conn xiaoming/oracle

ERROR:

ORA-01045: user XIAOMING lacks CREATE SESSION privilege; logon denied

警告: 您不再连接到 ORACLE。

SQL> show user

USER 为 ""

SQL> conn system/oracle

已连接。

SQL> grant connect to xiaoming;

授权成功。

SQL> conn xiaoming/oracle

已连接。

现在说下对象权限，现在要做这么件事情：

希望xiaoming用户可以去查询emp表
希望xiaoming用户可以去查询scott的emp表 grant select on scott.emp to xiaoming
希望xiaoming用户可以去修改scott的emp表 grant update on scott.emp to xiaoming
希望xiaoming 用户可以去修改/删除，查询，添加scott的emp表 grant all on scott.emp to xiaoming
scott希望收回xiaoming对emp表的查询权限 revoke select on scott.emp from xiaoming

1.4.7 权限的传递

对权限的维护。希望xiaoming用户可以去查询scott的emp表/还希望xiaoming可以把这个权限传递给别人。

如果是对象权限，就加入with grant option

grant select on emp to xiaoming with grant option

我的操作过程：

SQL> conn scott/oracle;

已连接。

SQL> grant select on scott.emp to xiaoming with grant option;

授权成功。

SQL> conn system/oracle;

已连接。

SQL> create user xiaohong identified by oracle;

用户已创建。

SQL> grant connect to xiaohong;

授权成功。

SQL> conn xiaoming/oracle;

已连接。

SQL> grant select on scott.emp to xiaohong;

授权成功。

如果是系统权限。 system给xiaoming权限时：grant connect to xiaoming with admin option 问题：如果scott把xiaoming对emp表的查询权限回收，那么xiaohong会怎样？

答案：被回收。下面是我的操作过程：

SQL> conn scott/oracle;

已连接。

SQL> revoke select on emp from xiaoming;

撤销成功。

SQL> conn xiaohong/oracle;

已连接。

SQL> select * from scott.emp;

select * from scott.emp

*

第 1 行出现错误:

ORA-00942: 表或视图不存在

结果显示：小红受到诛连了。。

with admin option与with grant option区别

with admin option用于系统权限授权，with grant option用于对象授权。
给一个用户授予系统权限带上with admin option时，此用户可把此系统权限授予其他用户或角色，但收回这个用户的系统权限时，这个用户已经授予其他用户或角色的此系统权限不会因传播无效，如授予A系统权限create session with admin option,然后A又把create session权限授予B,但管理员收回A的create session权限时，B依然拥有create session的权限，但管理员可以显式收回B create session的权限，即直接revoke create session from B. 而with grant option用于对象授权时，被授予的用户也可把此对象权限授予其他用户或角色，不同的是但管理员收回用with grant option授权的用户对象权限时，权限会因传播而失效，如grant select on table with grant option to A,A用户把此权限授予B，但管理员收回A的权限时，B的权限也会失效，但管理员不可以直接收回B的SELECT ON TABLE 权限。

1.5 用户口令管理

1.5.1 使用profile管理用户口令

概述：profile是口令限制，资源限制的命令集合，当建立数据库时，oracle会自动建立名称为default的profile。当建立用户没有指定profile选项时，那么oracle就会将default分配给用户。

1.5.2 账户锁定

概述：指定该账户(用户)登陆时最多可以输入密码的次数，也可以指定用户锁定的时间(天)一般用dba的身份去执行该命令。
例子：指定scott这个用户最多只能尝试3次登陆，锁定时间为2天，让我们看看怎么实现。
创建profile文件

SQL> create profile lock_account limit failed_login_attempts 3 password_lock_time 2;

SQL> alter user scott profile lock_account;

1.5.3 给账户(用户)解锁

SQL> alter user scott account unlock;

1.5.4 终止口令

为了让用户定期修改密码可以使用终止口令的指令来完成，同样这个命令也需要dba的身份来操作。
例子：给前面创建的用户test创建一个profile文件，要求该用户每隔10天要修改自己的登陆密码，宽限期为2天。看看怎么做。

SQL> create profile myprofile limit password_life_time 10 password_grace_time 2;

SQL> alter user test profile myprofile;

1.5.5 口令历史

概述：如果希望用户在修改密码时，不能使用以前使用过的密码，可使用口令历史，这样oracle就会将口令修改的信息存放到数据字典中，这样当用户修改密码时，oracle就会对新旧密码进行比较，当发现新旧密码一样时，就提示用户重新输入密码。
例子：

建立profile

SQL>create profile password_history limit password_life_time 10 password_grace_time 2

password_reuse_time 10

//password_reuse_time指定口令可重用时间即10天后就可以重用

分配给某个用户SQL> alter user test profile password_history;
删除profile
概述：当不需要某个profile文件时，可以删除该文件。SQL> drop profile password_history 【casade】注意：文件删除后，用这个文件去约束的那些用户通通也都被释放了。。加了casade，就会把级联的相关东西也给删除掉

1.6 Oracle数据类型

常用的数据类型

1	Varchar， varchar2	表示一个字符串
2	NUMBER	Number(10,2):整数部分占 8 位，小数部分占2份
3	DATE	表示日期类型
4	CLOB	大对象，表示大文本数据类型，可存4G
5	BLOB	大对象，表示二进制数据，可存4G

字符类 char 长度固定，最多容纳2000个字符。例子：char(10) ‘小韩’前四个字符放‘小韩’，后添6个空格补全，如‘小韩 ’ varchar2(20) 长度可变，最多容纳4000个字符。例子：varchar2（10） ‘小韩’ oracle分配四个字符。这样可以节省空间。 clob(character large object) 字符型大对象，最多容纳4g char 查询的速度极快浪费空间，适合查询比较频繁的数据字段。 varchar 节省空间
数字型 number范围-10的38次方到10的38次方，可以表示整数，也可以表示小数 number(5,2)表示一位小数有5位有效数，2位小数；范围：-999.99 到999.99 number(5)表示一个5位整数；范围99999到-99999
日期类型 date 包含年月日和时分秒 oracle默认格式1-1月-1999 timestamp 这是oracle9i对date数据类型的扩展。可以精确到毫秒。
图片 blob 二进制数据，可以存放图片/声音4g；一般来讲，在真实项目中是不会把图片和声音真的往数据库里存放，一般存放图片、视频的路径，如果安全需要比较高的话，则放入数据库。

1.7 表管理

1.7.1 建表

语法：

Create table 表名（

字段1   数据类型     [default 默认值],

字段2   数据类型     [default 默认值],

...

字段n   数据类型     [default 默认值]

）;

范例：创建person表

create table person(

       pid      number(10),

       name     varchar2(10),

       gender   number(1)  default 1,

       birthday date

);

insert into person(pid, name, gender, birthday)

values(1, '张三', 1, to_date('1999-12-22', 'yyyy-MM-dd'));

1.7.2 删除表

DROP TABLE 表名;

1.7.3 修改表

在sql中使用alter可以修改表

添加语法：

ALTER TABLE 表名称 ADD(列名1 类型 [DEFAULT 默认值]，列名1 类型 [DEFAULT 默认值]…)

修改语法：

ALTER TABLE 表名称 MODIFY(列名1 类型 [DEFAULT 默认值]，列名1 类型 [DEFAULT 默认值]…)

修改列名:

ALTER TABLE 表名称 RENAME COLUMN 列名1 TO 列名2

–添加一个字段

sql>alter table student add (classid number(2));

–修改一个字段的长度

sql>alter table student modify (xm varchar2(30));

–修改字段的类型或是名字（不能有数据）不建议做

sql>alter table student modify (xm char(30));

–删除一个字段不建议做(删了之后，顺序就变了。加就没问题，应该是加在后面)

sql>alter table student drop column sal;

–修改表的名字很少有这种需求

sql>rename student to stu;

范例：在person表中增加列address

alter table person add(address varchar2(10));

范例：把person表的address列的长度修改成20长度

alter table person modify(address varchar2(20));

1.7.4 截断表

在person表中使用delete语句删除数据，则可以使用rollback来回滚，如果想要清空一张表的数据，同时不想回滚可以立即释放资源需要使用截断表的语法

语法：TRUNCATE TABLE 表名

范例：截断person表

truncate table person;

1.8 表数据更新

1.8.1 INSERT

语法：

INSERT INTO表名[(列名1，列名2，…)]VALUES(值1，值2，…)

标准写法

insert into person(pid,name,gender,birthday,addrress)

values(1,'张三',1,'9-5月-1981','北京');

时候，想要将从其它表中选择数据并将其插入另一个表中。要做到这一点，可使用Oracle INSERT INTO SELECT语句，如下所示：

INSERT INTO target_table (col1, col2, col3)

SELECT col1,

       col2,

       col3

FROM source_table

WHERE condition;

Oracle INSERT INTO SELECT语句要求源表和目标表匹配，有时我们可能希望将多行插入到一个或多个表中。在这种情况下，就可以使用Oracle INSERT ALL语句，该语句也被称为多项式插入语句。

Oracle提供了两种类型的多项式插入语句：无条件的和有条件的。

无条件的Oracle INSERT ALL语句

将多行插入到表中，要将多行插入到表中，请使用以下Oracle INSERT ALL语句：

INSERT ALL

    INTO table_name(col1,col2,col3) VALUES(val1,val2, val3)

    INTO table_name(col1,col2,col3) VALUES(val4,val5, val6)

    INTO table_name(col1,col2,col3) VALUES(val7,val8, val9)

Subquery;

在这个语句中，每个值表达式值：val1，val2或val3必须引用由子查询的选择列表返回的列对应的值。

如果要使用文字值而不是子查询返回的值，请使用以下子查询：

SELECT * FROM dual;

条件多项插入语句允许根据指定的条件将行插入到表中。

以下显示了条件多项插入语句的语法：

INSERT [ ALL | FIRST ]

    WHEN condition1 THEN

        INTO table_1 (column_list ) VALUES (value_list)

    WHEN condition2 THEN

        INTO table_2(column_list ) VALUES (value_list)

    ELSE

        INTO table_3(column_list ) VALUES (value_list)

Subquery

如果指定了ALL关键字，则Oracle将在WHEN子句中评估每个条件。如果条件评估/计算为true，则Oracle执行相应的INTO子句。

但是，当指定FIRST关键字时，对于由子查询返回的每一行，Oracle都会从WHEN子句的上下方向评估每个条件。如果Oracle发现条件的计算结果为true，则执行相应的INTO子句并跳过给定行的后续WHEN子句。

请注意，单条件多项式插入语句最多可以有127个WHEN子句。

Oracle多表插入语句受以下主要限制：

它可以用来将数据只插入到表中，而不是视图或物化视图。
它不能用来将数据插入到远程表中。
所有INSERT INTO子句中的列总和不得超过999。
一个表集合表达式不能在一个多表插入语句中使用。
多表插入语句的子查询不能使用序列。

1.8.2 事务

因为oracle的事务对数据库的变更的处理，我们必须做提交事务才能让数据真正的插入到数据库中，在同样在执行完数据库变更的操作后还可以把事务进行回滚，这样就不会插入到数据库。如果事务提交后则不可以再回滚。

提交：commit

回滚：rollback

事务保存点与回滚：

语法：

savepoint 保存点名字

示例：

savepoint a1;    --设置一个保存点 a1;

update person set name='张三' where pid=''  --修改一条数据

savepoint a2;   --设置一个保存点 a1;

update tmp set name='李四' where id='102  --再修改一条数据

rollback to a2;   --回滚到a2 保存点。 此时在 a2 保存点之后的所有数据修改视为无效。

rollback to a1;   --这里可以再从a2保存点再回滚到a1 保存点。 此时在 a1 保存点之后的所有数据修改视为无效。

rollback;           --回滚全部。即撤消至上一次提交事务后的所有数据修改。

commit;  --提交事务 将回滚后的事务提交，并会删除所有保存点。

注意：我们可以从a2向前再回滚到a1 ，但无法从a1回滚到a2。也就是只能向前回滚，不能从前面的点再向后回滚！

1.8.3 UPDATE

全部修改：

UPDATE 表名 SET 列名1=值1，列名2=值2，…

局部修改：

UPDATE 表名 SET 列名1=值1，列名2=值2，…WHERE 修改条件

1.8.4 DELETE

语法 :

DELETE FROM 表名 WHERE 删除条件;

在删除语句中如果不指定删除条件的话就会删除所有的数据

现实应用中，经常从与其他表中的行具有外键关系的表中来删除行记录。

例如，想要从orders表中删除id为1的销售订单，并从order_items表中删除与订单id是1关联的所有行项目。通常可以发出两个DELETE语句，如下所示：

-- 第一条

DELETE FROM

    orders

WHERE

    order_id = 1;

-- 第二条

DELETE FROM

    order_items

WHERE

    order_id = 1;

COMMIT WORK;

请注意，COMMIT WORK语句确保两个DELETE语句以全部或全部方式执行，以防第一条语句成功，但第二个DELETE语句失败时在order_items表中的孤行。

但是，如果知道如何正确设置表的约束，那么这是不必要的。

在这种情况下，当创建order_items表时，可以使用DELETE CASCADE选项定义一个外键约束，如下所示：

CREATE TABLE order_items

(

    order_id   NUMBER( 12, 0 )                                ,

    -- other columns

    -- ...

    CONSTRAINT fk_order_items_orders

    FOREIGN KEY( order_id )

    REFERENCES orders( order_id )

    ON DELETE CASCADE

);

通过这样做，每当从orders表中删除一行时，例如：

DELETE

FROM

    orders

WHERE

    order_id = 1;

order_items表中order_id为1的所有行也被数据库系统自动删除。

Truncate table实现数据删除

比较truncat与delete实现数据删除？

delete删除的数据可以rollback,也可以闪回

delete删除可能产生碎片，并且不释放空间

truncate是先摧毁表结构，再重构表结构

1.8.5 Merge语句

Oracle MERGE语句从一个或多个源表中选择数据并更新或将其插入到目标表中。 MERGE语句可指定一个条件来确定是更新数据还是将数据插入到目标表中。

以下说明了Oracle MERGE语句的语法：

MERGE INTO target_table

USING source_table

ON search_condition

    WHEN MATCHED THEN

        UPDATE SET col1 = value1, col2 = value2,...

        WHERE <update_condition>

        [DELETE WHERE <delete_condition>]

    WHEN NOT MATCHED THEN

        INSERT (col1,col2,...)

        values(value1,value2,...)

        WHERE <insert_condition>;

下面来仔细看看上面MERGE语句的语法：

首先，指定要在INTO子句中更新或插入的目标表(target_table)。
其次，指定要更新或插入USING子句中的数据源(source_table)。
第三，指定合并操作在ON子句中更新或插入的搜索条件。

对于目标表中的每一行，Oracle都会评估搜索条件：

如果结果为true，则Oracle使用源表(source_table)中的相应数据更新该行。
如果任何行的结果为false，则Oracle将源表(source_table)中相应的行插入到目标表(target_table)中。

当想要在单个操作中组合多个INSERT，UPDATE和DELETE语句时，MERGE语句变得很方便。

因为MERGE是确定性语句，所以不能在同一个MERGE语句中多次更新目标表的同一行。

可以将一个可选的DELETE WHERE子句添加到MATCHED子句中，以在合并操作之后进行清理。 DELETE子句只删除目标表中与ON和DELETE WHERE子句匹配的行。

Oracle MERGE前提条件

要执行MERGE语句，必须在源表上具有INSERT和UPDATE对象权限。如果使用DELETE子句，则还必须在目标表上具有DELETE对象特权。

DDL : 数据定义语言 create alter drop truncate

DML : 数据操纵语言 insert update delete

DCL : 数据控制语言安全授权 grant revoke

DQL : 数据查询语言 select from子句 where子句

DBA

Oracle基础教程(一)