Oracle数据库基础

1、学习目标

1、理解数据库基本概念
2、掌握安装、配置和连接数据库
3、了解数据类型和操作符
4、会使用SQL语句对数据进行操作
5、会使用常用内置函数

---

2、Oracle简介

Oracle公司（甲骨文）是全球最大的信息管理软件及服务供应商，成立于1977年，总部位于美国加州Redwood shore，面向全球开放oracle认证。2002年04月26日，启用 “甲骨文” 作为中文注册商标。

Oracle开发的关系数据库产品因性能卓越而闻名，Oracle数据库产品为财富排行榜上的前1000家公司所采用，许多大型网站也选用了Oracle系统，是世界最好的数据库产品。此外，Oracle公司还开发其他应用程序和软件。同时，Oracle在英语里还是“神谕”的意思，意为 “替神说话的”，寓指Oracle公司的发展目标和决心地位。

主要产品目前分为两大类：

1.服务器及工具（主要竞争对手：IBM、微软）

2.应用软件（主要竞争对手：德国SAP公司）

---

3、Oracle数据库

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

3.1Oracle数据库管理系统

1. Oracle公司的核心产品
2. 目前最流行的数据库
3. 主要版本Oracle8i/9i(internet)、Oracle10g/11g(grid)
4. 基于C/S系统结构

---

3.2Oracle 数据库的一个基本任务是存储数据，以下部分简要地介绍 Oracle 数据库的物理和逻辑存储结构

3.2.1 物理存储结构

物理存储结构是存储数据的纯文件。当执行一个 CREATE DATABASE 语句来创建一个新的数据库时，将创建下列文件：

1. 数据文件：数据文件包含真实数据，例如销售订单和客户等。逻辑数据库结构(如表和索引)的数据被物理存储在数据文件中。
2. 控制文件：每个 Oracle 数据库都有一个包含元数据的控制文件。元数据用来描述包括数据库名称和数据文件位置的数据库物理结构。
3. 联机重做日志文件：每个 Oracle 数据库都有一个联机重做日志，里面包含两个或多个联机重做日志文件。联机重做日志由重做条目组成，能够记录下所有对数据所做的更改。
4. 除这些文件外，Oracle 数据库还包括如参数文件、网络文件、备份文件以及用于备份和恢复的归档重做日志文件等重要文件。

3.1.2 逻辑结构

Oracle 数据库使用逻辑存储结构对磁盘空间使用情况进行精细控制。以下是 Oracle 数据库中的逻辑存储结构：

1. 数据块（Data Blocks）：Oracle 将数据存储在数据块中。数据块也被称为逻辑块，Oracle 块或页，对应于磁盘上的字节数。
2. 范围（Extents）：范围是用于存储特定类型信息的逻辑连续数据块的具体数量。
3. 段（Segements）：段是分配用于存储用户对象(例如表或索引)的一组范围。
4. 表空间（Tablespaces）：数据库被分成称为表空间的逻辑存储单元。 表空间是段的逻辑容器。 每个表空间至少包含一个数据文件。

---

4.Oracle 主要组件

5.Oracle 实例由三个主要部分组成：

系统全局区 (SGA)，程序全局区 (PGA) 和后台进程。

---

6.主要的Oracle数据库的后台进程

1. PMON 是 Oracle 数据库中最活跃的一个进程，是调节所有其他进程的进程监视器。PMON 能够清理异常连接的数据库连接，并自动向侦听器进程注册数据库实例。
2. SMON 是执行系统级清理操作的系统监视进程。它有两个主要职责，包括在发生故障的情况下自动恢复实例，例如断电和清理临时文件。
3. DBWn 是数据库编写器。Oracle 在内存中执行每个操作而不是磁盘。因为在内存中的处理速度比在磁盘上快。DBWn 进程从磁盘读取数据并将其写回到磁盘。 一个 Oracle 实例有许多数据库编写器，如：DBW0，DBW1，DBW2等等。
4. CKPT 是检查点过程。 在 Oracle 中，磁盘上的数据称为块，内存中的数据称为缓冲区。 当该块写入缓冲区并更改时，缓冲区变脏，需要将其写入磁盘。CKPT 进程使用检查点信息更新控制和数据文件头，并向脏盘写入脏缓冲区的信号。

---

7.数据库

1. 磁盘上存储的数据的集合
2. 在物理上表现为数据文件、日志文件和控制文件等
3. 在逻辑上以表空间形式存在
4. 必须首先创建数据库，然后才能使用Oracle

---

8.数据库实例

Oracle 数据库服务器由一个数据库和至少一个数据库实例组成。 数据库是一组存储数据的文件，而数据库实例则是管理数据库文件的内存结构。此外，数据库是由后台进程组成。

数据库和实例是紧密相连的，所以我们一般说的 Oracle 数据库，通常指的就是实例和数据库，下图说明了 Oracle 数据库服务器体系结构：

---

9.数据文件(dbf)

数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真正是在某一个或者多个数据文件中，而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。

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

日志文件
扩展名是.LOG，它记录了对数据的所有更改信息
多个日志文件组之间循环使用

---

10.表空间

表空间是 Oracle 对物理数据库上相关数据文件（ORA 或者 DBF 文件）的逻辑映射。一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一个表空间（称之为 system 表空间）。每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据库文件（datafile）。一个数据文件只能属于一个表空间。

表空间类型：

1. 永久性表空间：一般保存表、视图、过程和索引等的数据。(默认SYSTEM、USERS)
2. 临时性表空间：只用于保存系统中短期活动的数据。(TEMP)
3. 撤销表空间：用来帮助回退未提交的事务数据。(UNDO)

---

11.用户

用户是在实例下建立的。不同实例中可以建相同名字的用户。

注：表的数据，是由用户放入某一个表空间的，而这个表空间会随机把这些表数据放到一个或者多个数据文件中。由于 Oracle 的数据库不是普通的概念，oracle 是由用户和表空间对数据进行管理和存放的。但是表不是由表空间去查询的，而是由用户去查的。因为不同用户可以在同一个表空间建立同一个名字的表！这里区分就是用户了！scott 与 hr 就是初始的普通用户，这些用户下面都默认存在了表结构。

一个数据库，可以有多个实例

一个实例可以创建多个用户

一个用户只能分配一个表空间

一个表空间它可以给n个用户使用

---

4、安装Oracle

安装注意事项

1、选择安装目录最好是非系统盘

2、安装时可以直接创建数据库，也可以选择不创建

3、服务器的主机名不能有特殊字符 （例如：主机名为abc_01,安装过程中可能因为有“_”而报错）

4、如果在创建数据库阶段提示错误，继续安装

---

5、启动、配置和连接数据库

1、启动数据库服务

2、配置客户端网络服务

3、每个数据库都有SYS和SYSTEM两个默认用户，都具有创建用户权限

4、使用SYSTEM用户登录ORCL数据库

5.1启动数据库服务

OracleService：数据库服务
OracleOraDb11g_hom1TNSListener ：数据库监听服务
OracleDBConsole：企业管理器服务

---

5.2配置客户端网络服务

Oracle中的 Net Manager工具和Net Configuration Assistant都能用来配置监听器和网络服务名

注意：其中SERVICE_NAME为服务名。

Oracle服务器端配置文件为listener.ora,代码片段：

# listener.ora Network Configuration File: D:\Oracle\app\lenovo\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\Oracle\app\lenovo\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\Oracle\app\lenovo\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = D:\Oracle\app\lenovo

Oracle客户端配置文件为tnsnames.ora,代码片段：

# tnsnames.ora Network Configuration File: D:\Oracle\app\lenovo\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

AIDEN =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


MYORACLE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

---

5.3连接数据库

SYS和SYSTEM用户

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

通过SQLPlus方式连接

---

6、 使用Oracle数据库的开发流程

开发流程示意图：

---

7、Oracle数据类型

常用数据类型：varchar2、number、date、char

不建议使用VARCHAR、 INTEGER、FLOAT、DOUBLE等类型

---

Oracle 中伪列

Oracle 中伪列就像一个表列，但是它并没有存储在表中，伪列可以从表中查询，但不能插入、更新和删除它们的值，常用的伪列有 ROWID 和 ROWNUM

ROWID格式说明：

ROWID使用示例：

select rowid,e.empno,e.ename 
from emp e 
where ename like '%S%'

ROWNUM 是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数

--ROWNUM 是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数
select rownum, e.* from emp e;

--分页查询语句 每页显示5条查询第2页
select * from (
 select rownum as pageRow , e.empNo, ename from emp e
) where pageRow > (2 - 1) * 5  and pageRow <= 2 * 5

---

8、SQL 简介

8.1 数据操纵语言（DML）

现有stuInfo表和数据，对该表中数据执行操作

示例与要求：

/*
======================================
创建stuInfo表
======================================
*/
--创建学生信息表
create table stuInfo(
    stuName varchar2(20) not null,--学员姓名
    stuNo char(6) not null,     --学生学号
    stuAge number(3,0) not null,--年龄
    stuID numeric(18,0) ,       --身份证号码
    stuSeat numeric (2,0)       --座位号
);

/*
======================================
  更改stuName列的定义，使此列能容纳25个字符
======================================
*/

alter table stuInfo 
modify (stuName varchar2(25));

/*
======================================
向stuInfo 表添加stuTelNo 和stuAddress 两个列
======================================
*/

alter table stuInfo 
add(stuTelNo varchar2(11),stuAddress varchar2(50));

/*
======================================
从stuInfo表中删除stuTelNo列
======================================
*/

alter table stuInfo
drop column stuTelNo;
--或者删除多个列
alter table stuInfo
drop (stuTelNo,stuSex);

/*
======================================
删除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;--提交事务

/*
======================================
不重复显示所有学员姓名和年龄 
======================================
*/
select distinct stuName,stuAge from stuInfo;

/*
======================================
按学员姓名升序，年龄降序查询学员信息 
======================================
*/
select stuNo, stuName ,stuAge from stuInfo
order by stuName asc ,stuAge desc;

/*
======================================
使用列别名，当中文字符包含空格则必须使用双引号  
======================================
*/
select stuNo 学号, stuName as "姓名" ,stuAge as "年 龄" from stuInfo;
/*
======================================
利用现有的表创建新表  
======================================
*/
--选择所有数据
create table newStu1 as select * from stuInfo;
--选择指定的列所有数据
create table newStu2 as select stuNo, stuName ,stuAge from stuInfo;
--只留表结构，不留数据
create table newStu3 as select stuNo, stuName ,stuAge from stuInfo where 1=2;

/*
======================================
查看表中行数  
======================================
*/              
select count(0)from stuInfo;
select count(*)from stuInfo;

/*
======================================
取出stuName,stuAge列不完全重复记录 
======================================
*/                
select stuname,stuAge from  stuInfo
group by stuName,stuAge
having(count(stuName||stuAge))<2; --重复的个数为1

/*
======================================
删除stuName,stuAge列重复的行(保留一行) 
======================================
*/              
select stuNo 学号, stuName as "姓名" ,stuAge as "年龄" from stuInfo;   
delete from stuInfo where 
rowid not in(--4.获得所有重复记录的ROWID
   select max(rowId) from stuInfo --1.查找重复的记录，只保留一条
   group by stuName,stuAge
   having (count(stuAge||stuAge)>1)
   union --3.合并结果集，获得所有符合条件且不重复的记录
   --2.查找不重复的记录
   select max(rowId) from stuInfo
   group by stuName,stuAge
   having (count(stuAge||stuAge)=1)
);

/*
======================================
查看当前用户所有数据量>100万的表的信息
======================================
*/   
select * from user_all_tables  u
where u.num_rows>1000000;

8.2事务控制语言（TCL）

用于事务控制的语句

COMMIT --提交事务，把事务中对数据库的修改进行永久保存
ROLLBACK --回滚事务，即取消对数据库所做的任何修改
SAVEPOINT --在事务中创建存储点
ROLLBACK TO <SavePoint_Name> --将事务回滚到存储点

/*
===========================================================
事务控制语句应用举例
============================================================
*/
--执行步骤一：创建dept 表
create table dept
(    deptno number(2) primary key,--部门编号
     dname varchar2(14),--部门名称
     loc varchar2(13)--地址
); 

--执行步骤二：插入数据
insert into dept values (10,'accounting','new york');
insert into dept values (20,'research','dallas');
insert into dept values (30,'sales','chicago');
insert into dept values (40,'operations','boston');
commit;
--执行步骤三：操作dept 表
insert into dept values(50,'a',null);
insert into dept values(60,'b',null);
savepoint a;--在事务中创建存储点 a
insert into dept values(70,'c',null);
rollback to savepoint a;--将事务回滚到存储点

--执行步骤四：查看dept 表,有50、60 号部门
select * from dept;

--执行步骤五：回滚事务
rollback;--没有50、60 号部门

--执行步骤六：查看dept 表
select * from dept;

---

8.3Oracle 支持的SQL操作符分类

集合操作符

1. UNION（联合）
2. UNION ALL（联合所有）
3. INTERSECT（交集）
4. MINUS（减集）

--操作符示例
--准备操作1：创建退休员工表
create table employee  /*-创建员工信息表-*/
(
    empno number(4),   --员工编号
    ename varchar2(10),--员工姓名
    job varchar2(9),   --员工工种
    mgr number(4),     --上级经理编号
    hiredate date,     --受雇日期
    sal number(7,2),   --员工薪水
    comm number(7,2),  --福利
    deptno number(2)   --部门编号
); 

--准备操作2：插入数据
insert into employee values
(7369,'smith','clerk',7902,to_date('17-12-2005','dd-mm-yyyy'),5800,null,20);
insert into employee values
(7499,'allen','salesman',7698,to_date('20-2-2006','dd-mm-yyyy'),6600,300,30);
insert into employee values
(7521,'ward','salesman',7698,to_date('22-2-2006','dd-mm-yyyy'),6250,500,30);
insert into employee values
(7566,'jones','manager',7839,to_date('2-4-2006','dd-mm-yyyy'),7975,null,20);
insert into employee values
(7654,'martin','salesman',7698,to_date('28-9-2006','dd-mm-yyyy'),6250,1400,30);
insert into employee values
(7698,'blake','manager',7839,to_date('1-5-2006','dd-mm-yyyy'),7850,null,30);
insert into employee values
(7782,'clark','manager',7839,to_date('9-6-2006','dd-mm-yyyy'),7450,null,10);
insert into employee values
(7788,'scott','analyst',7566,to_date('13-7-2012','dd-mm-yyyy'),8000,null,20);
insert into employee values
(7839,'king','president',null,to_date('17-11-2006','dd-mm-yyyy'),10000,null,10);
insert into employee values
(7844,'turner','salesman',7698,to_date('8-9-2006','dd-mm-yyyy'),6500,0,30);
insert into employee values
(7876,'adams','clerk',7788,to_date('13-7-2012','dd-mm-yyyy'),7100,null,20);
insert into employee values
(7900,'james','clerk',7698,to_date('3-12-2006','dd-mm-yyyy'),5950,null,30);
insert into employee values
(7902,'ford','analyst',7566,to_date('3-12-2006','dd-mm-yyyy'),5000,null,20);
insert into employee values
(7934,'miller','clerk',7782,to_date('23-1-2007','dd-mm-yyyy'),6300,null,10);

--准备操作3：基于employee表创建新表retireEmp
create table retireEmp as select  * from employee;

--准备操作4：修改列名
alter table  retireEmp rename column empno to rempno;  

--准备操作5：修改
update retireEmp  SET rempno=8888 WHERE rempno=7788;

/*
===========================================================
集合操作符
============================================================
*/
/*union 并集操作符（返回employee与retireEmp中所有不重复的列）*/
select empNo from employee
union 
select rempNo from retireEmp

/*union all 并集all操作符（返回employee与retireEmp所有行，包裹重复的行）*/
select empNo from employee
union 
select rempNo from retireEmp

/*union order by*/
select empNo from employee
union
select rempNo from retireEmp
order by empNo

/*intersect 交集操作符 (返回两个查询中都有的行) */
select empNo from employee 
intersect
select rempNo from retireEmp  

/*minus 减集操作符 (返回第一个查询选定而未被第二个查询选定的行)*/
select empNo from employee 
minus
select rempNo from retireEmp  

--Sqlserver 中的写法：
select * from employee e
where  not exists(
    select * from retireEmp r where  e.empno=r.rempNo
)

/*||连接操作符*/
select ename||'_'||job from retireEmp

---

9、 SQL函数

9.1转换函数

转换函数将值从一种数据类型转换为另一种数据类型，常用的转换函数：

1. TO_CHAR( )
2. TO_DATE( )
3. TO_NUMBER( )

/*
===========================================================
转换函数
============================================================
*/
/*
 * TO_CHAR()函数==>Oracle中的dual表是一个单行单列的虚拟表,主要用来选择系统变量或求一个表达式的值）
*/
select to_char(sysdate,'yyyy-MM-dd HH:mm:ss') from dual

select to_char（sysdate,'YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS'）  from dual;

--货币格式    
select  to_char(1210.7, '$9,999.00') FROM dual;

/*TO_DATE()*/
select to_date('2021-10-25','yyyy-MM-dd') from dual;

/*TO_NUMBER()*/
select to_number('100')   to_number('100') from dual;
select '100' '100' from dual;--也可以
select to_number('100') from dual;

---

9.2其它函数

转换空值的函数： NVL()、NVL2()、DECODE()

--常用其他函数：
/*
===========================================================
nvl函数语法 NVL(expr1,expr2)
如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。
============================================================
*/
select comm,nvl(comm,0),sal,hiredate from employee;
/*
===========================================================
nvl2函数：如果该函数的第一个参数为空那么显示第三个参数的值，如果第一个参数的值不为空，则显示第二个参
============================================================
*/
select comm,nvl2(comm,0,1),sal,hiredate from employee;  
/*
===========================================================
decode函数 判断字符串是否一样 ：
语法：decode(value,if1,then1,if2,then2,if3,then3,...,else)
============================================================
*/
select hiredate,
decode(to_char(hiredate,'MM'),
'01','一月','02','二月','03','三月','04','四月','05','五月','06','六月',
'07','七月','08','八月','09','九月','10','10月','11','十一月','12','十二月')  as Month
from employee;

---

9.3分析函数

1. 分析函数根据一组行来计算聚合值
2. 分析函数为每组记录返回多个行
3. 以下三个分析函数用于计算一个行在一组有序行中的排位，序号从1开始

语法： 函数名([ 参数]) OVER( [ 分区子句 ] [ 排序子句])

rank( ) over (partition by deptno order by sal desc)

函数名：分析函数名字
参数：函数需要传入的参数
分区子句（PARTITION BY）：将查询结果分为不同的组，功能类似于 GROUP BY语句
排序子句（ORDER BY）：将每个分区进行排序

--分析函数 [rank() dense_rank() row_number()]

/*按照每个部门分组,对薪水从大到小排序,每个部门序号从1开始,同一个部门相同薪水序号相同*/
--rank:跳跃排序
select  ename, deptno, sal ,
rank() over(partition by deptNo order by sal desc)as "Rank"    
from  employee;

/*按照每个部门分组,对薪水从大到小排序,每个部门序号从1开始,同一个部门相同薪水序号相同*/
--dense_rank() 连续排序
select  ename, deptno, sal ,
dense_rank() over(partition by deptNo order by sal desc)as "DenseRank"    
from  employee;

/*按照每个部门分组,对薪水从大到小排序,每个部门序号从1开始,同一个部门相同薪水序号继续递增，顺序排名*/
select  ename, deptno, sal ,
row_number() over(partition by deptNo order by sal desc)as "RowNumber"    
from  employee;

10、章节总结

---

这篇好文章是转载于：学新通技术网