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、章节总结