Oracle 基础知识和语句

Oracle 介绍

Oracle Database，又名 Oracle RDBMS，或简称 Oracle。
是甲骨文公司的一款关系数据库管理系统。它是在数据库领域一直处于领先地位的产品。可以说 Oracle 数据库系统是目前世界上流行的关系数据库管理系统，系统可移植性好、使用方便、功能强，适用于各类大、中、小、微机环境。它是一种高效率、可靠性好的、适应高吞吐量的数据库解决方案。作为一个关系数据库，它是一个完备关系的产品；作为分布式数据库它实现了分布式处理功能。但它的所有知识，只要在一种机型上学习了 ORACLE 知识，便能在各种类型的机器上使用它。

Oracle 主要组件

数据库

磁盘上存储的数据的集合，在物理上表现为数据文件、日志文件、控制文件、联机日志、参数文件等。在逻辑上以表空间形式存在必须首先创建数据库，然后才能使用 Oracle，其实 Oracle 数据库的概念和其它数据库不一样，这里的数据库是一个操作系统只有一个库。可以看作是 Oracle 就只有一个大数据库。

数据文件

扩展名是.DBF，用于存储数据库数据的文件数据库表和数据文件不存在一对一对应关系

控制文件

扩展名是.CTL，是数据库启动及运行所必需的文件默认包含 3 个控制文件，各个控制文件内容相同

日志文件

扩展名是.LOG，它记录了对数据的所有更改信息多个日志文件组之间循环使用数据库实例（Oracle Instance），每个启动的数据库都对应一个数据库实例，由这个实例来访问和控制数据库为了运行数据库，一Oracle 实例有一系列的后台进程（Backguound Processes)和内存结构（Memory Structures)组成。一个数据库可以有 n 个实例。

表空间管理

表空间是 Oracle 对物理数据库上相关数据文件（ORA 或者 DBF 文件）的逻辑映射。一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一个表空间(称之为 system 表空间)。 每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)。一个数据文件只能属于一个表空间，一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行，表的数据，是有用户放入某一个表空间的，而这个表空间会随机把这些表数据放到一个或者多个数据文件中。
一个数据库下可以建立多个表空间，一个表空间可以建立多个用户、一个用户下可以建立多个表

Oracle 表空间分类

|类别|举例

类别	举例	说明
永久性表空间	SYSTEM，USERS	一般保存表、视图、过程和索引等的数据
临时性表空间	TEMP	只用于保存系统中短期活动的数据
撤销表空间	UNDO	用来帮助回退未提交的事务数据

一般不需要建临时和撤销表空间，除非把它们转移其他磁盘中以提高性能

什么时候创建表空间

为不同的子系统创建独立的表空间，子系统的表或者数据会单独存在某个表空间中，方便备份或转移等操作

创建表空间

--语法：
CREATE TABLESPACE tablespacename
DATAFILE 'filename' [ SIZE integer [ K | M ] ]
[ AUTOEXTEND [ OFF | ON ] ] ;
}
--示例：
--创建表空间
create tablespace HrSys datafile 'e:\data\HrSys.dbf' -- 文件位置• 先创建文件夹
size 100m -- 初始大小 autoextend on -- 开启自动增长 next 10m; --每次增长大小
LOGGING -- 记录日志

修改表空间

--调整表空间的大小
--方法一：更改数据文件的大小
ALTER DATABASE DATAFILE 'e:\data\HrSys.dbf' 
RESIZE 80M; 
--方法二：向表空间内添加数据文件
ALTER TABLESPACE HrSys ADD DATAFILE 'e:\data\HrSys02.dbf'
SIZE 20M AUTOEXTEND ON;

删除表空间

-- 只删除表空间 不删除数据文件
DROP TABLESPACE HrSys 
/* ======================================
| 删除表空间时同时删除里面的数据文件
====================================== */
DROP TABLESPACE HrSys INCLUDING CONTENTS AND DATAFILES

用户管理

用户是在实例下建立的。不同实例可以建相同名字的用户。SYS 和 SYSTEM 用户都是

	SYS 用户	SYSTEM 用户
地位	Oracle 的一个超级用户	Oracle 默认的系统管理员，拥有 DBA 权限
作用	主要用来维护系统信息和管理实例	通常用来管理 Oracle 数据库的用户、权限和存储等
登录身份	只能以 SYSDBA 或 SYSOPER 角色登录	只能以 Normal 方式登录

Oracle 的系统用户，它们都使用 SYSTEM 表空间，SYS 拥有更大的权限。

创建用户

-- 创建用户
CREATE user ktedu identified BY ktedu123 
-- 创建用户 A_Hr 并指定表空间为 kttbs
CREATE user A_Hr identified BY hr123 DEFAULT TABLESPACE kttbs
--修改密码
ALTER USER A_Hr IDENTIFIED BY hr123456
-- 删除用户
DROP USER A_Hr 
-- 删除用户以及用户下的对象
DROP USER A_Hr CASCADE

权限和角色管理

权限指执行特定类型 SQL 命令或访问其他对象的权利，用户必须赋予相应的权限权分为系统权限和对象权限，系统权限允许用户执行某些数据库操作，对象权限允许用户对某一特定对象执行特定的操作。
为了简化权限管理，引入了角色的概念，角色是具有名称的一组权限的组合，常用系统预定义角色：
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 用户可以操作全体用户的任意基表，包括删除。

对于普通用户：授予 connect, resource 权限。对于 DBA 管理用户：授予connect，resource, dba 权限。
语法：
–分配权限或角色
GRANT privileges or role TO user;
–撤销权限或角色
REVOKE privileges or role FROM user;
示例：

--分配权限或角色
GRANT connect, resource TO ktedu ;
--撤销权限或角色
REVOKE resourceor FROM ktedu ;

自定义角色并给用户赋指定的角色

--1.建一个角色
create role role1; 
--2.授权给角色
grant create any table,create procedure to role1; 
--3.授予角色给用户
grant role1 to Hr; 
--4.查看角色所包含的权限
select * from role_sys_privs; 
--5、锁住用户
alter user liumingcheng account lock; 
--6.启用用户
alter user liumingcheng account unlock;

Oracle 数据类型

Oracle 中常用的数据类型有字符类型、数字类型、日期类型、大对象类型

字符型：
varchar2：范围为 1-4000，如果你超出了这个范围，Oracle 会报错。
char： 表示固定长度，比如 char(10),存小于或等 10 的任意大小文件，在数据库中这个都占 10 个空间。
varchar： 不推荐使用因为它对中文字符的规则是占两个字节，其他数据库占个 1 字节，这样使用不方便日后进行数据库的转换。

日期型：
在 Oracle 中，表示日期统一使用 Date 类型后面不用跟范围它所能 。

大型字符集合
blog：主要用来存放非字符的文件，比如音频视等二进制文件，最大 4G。
clob：主要用来存放文本文件，最大能存 4G 的字节

数字类型：
在 Oracle 中，表示数字的类型为 number，它有三种写法：
1）、 number：能够表示任意的何数字
2）、 number(p,s) ：其中， p 代表精度， s 代表小数点的位
3）、 number§：相当于表示的是 number(p,0)的写法，即表示是整数的写法。
注意：
第二种写法中， s 有可能为正，也有可能为负，如果是正数就代表保留小数点后面，代表保留小数点后面 s 位，如果小数点后面的字长度大于 s 位，那么多部分进行四舍五入,如果为负，则表示小数点左边 s 个数字全部以 0 替换 ,然后四舍五入必须要是整数；

不建议使用 VARCHAR、 INTEGER、FLOAT、DOUBLE 等类型，非 SQL 标准

数据库表的管理

建表

语法：
Create table 表名（
字段 1 数据类型 [default 默认值],
字段 2 数据类型 [default 默认值],
…
字段 n 数据类型 [default 默认值] ）;

范例：创建并操作 stuInfo 表

示例 1：创建 stuInfo 表

CREATE TABLE stuInfo /*-创建学员信息表-*/
(
	stuName VARCHAR2(20) NOT NULL, --学员姓名,非空（必填）
	stuNo CHAR(6) NOT NULL, --学号,非空（必填）
	stuAge NUMBER(3,0) NOT NULL, --年龄,非空（必填）
	stuID NUMERIC(18,0), --身份证号，代表 18 位数字,小数位数为 0
	stuSeat NUMERIC(2,0) --座位号
);

补充：更改 stuName 列的定义，使此列能容纳 25 个字符

ALTER TABLE stuInfo 
MODIFY (stuName VARCHAR2 (25));

补充：向 stuInfo 表添加 stuTel_no 和 stuAddress 两个列

ALTER TABLE stuInfo
ADD (stuTel_no VARCHAR2 (12),
stuAddress VARCHAR2(20));

补充：从 stuInfo 表中删除列

ALTER TABLE stuInfo 
DROP COLUMN stuTel_no; --删除 stuTel_no 列
--或者
ALTER TABLE stuInfo 
DROP (stuTel_no,stuSeat);--删除 stuTel_no 和 stuSeat 列

补充：删除 stuInfo 表

DROP TABLE stuInfo;

补充：插入 stuInfo 表数据

INSERT INTO stuInfo VALUES ('张三'，1，18，null，1);
INSERT INTO stuInfo VALUES ('李四'，2，20，null，2);
INSERT INTO stuInfo VALUES ('王五'，3，15，null，3);
INSERT INTO stuInfo VALUES ('张三'，4，18，null，4);
INSERT INTO stuInfo VALUES ('张三'，5，20，null，5);
COMMIT;

其他详情Oracle知识请敬请关注作者或进入作者主页查看，谢谢！