来源：互联网转载 | 更新日期：2023-09-13 09:30:24

文章目录

• • @[toc]
• 1、学习目标
• 2、Oracle简介
• 3、Oracle数据库
• 4、安装Oracle
• 5、启动、配置和连接数据库
• • 5.1启动数据库服务
  • 5.2配置客户端网络服务
  • 5.3连接数据库
• 6、 使用Oracle数据库的开发流程
• 7、Oracle数据类型
• 8、SQL 简介
• • 8.1 数据操纵语言（DML）
  • 8.2事务控制语言（TCL）
  • 8.3Oracle 支持的SQL操作符分类
• 9、 SQL函数
• • 9.1转换函数
  • 9.2其它函数
  • 9.3分析函数
• 10、章节总结

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数据库管理系统

Oracle公司的核心产品

目前最流行的数据库

主要版本Oracle8i/9i(internet)、Oracle10g/11g(grid)

基于C/S系统结构

---

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

3.2.1 物理存储结构

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

数据文件：数据文件包含真实数据，例如销售订单和客户等。逻辑数据库结构(如表和索引)的数据被物理存储在数据文件中。

控制文件：每个 Oracle 数据库都有一个包含元数据的控制文件。元数据用来描述包括数据库名称和数据文件位置的数据库物理结构。

联机重做日志文件：每个 Oracle 数据库都有一个联机重做日志，里面包含两个或多个联机重做日志文件。联机重做日志由重做条目组成，能够记录下所有对数据所做的更改。

除这些文件外，Oracle 数据库还包括如参数文件、网络文件、备份文件以及用于备份和恢复的归档重做日志文件等重要文件。

3.1.2 逻辑结构

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

数据块（Data Blocks）：Oracle 将数据存储在数据块中。数据块也被称为逻辑块，Oracle 块或页，对应于磁盘上的字节数。

范围（Extents）：范围是用于存储特定类型信息的逻辑连续数据块的具体数量。

段（Segements）：段是分配用于存储用户对象(例如表或索引)的一组范围。

表空间（Tablespaces）：数据库被分成称为表空间的逻辑存储单元。 表空间是段的逻辑容器。 每个表空间至少包含一个数据文件。

---

4.Oracle 主要组件

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

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

---

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

PMON 是 Oracle 数据库中最活跃的一个进程，是调节所有其他进程的进程监视器。PMON 能够清理异常连接的数据库连接，并自动向侦听器进程注册数据库实例。

SMON 是执行系统级清理操作的系统监视进程。它有两个主要职责，包括在发生故障的情况下自动恢复实例，例如断电和清理临时文件。

DBWn 是数据库编写器。Oracle 在内存中执行每个操作而不是磁盘。因为在内存中的处理速度比在磁盘上快。DBWn 进程从磁盘读取数据并将其写回到磁盘。 一个 Oracle 实例有许多数据库编写器，如：DBW0，DBW1，DBW2等等。

CKPT 是检查点过程。 在 Oracle 中，磁盘上的数据称为块，内存中的数据称为缓冲区。 当该块写入缓冲区并更改时，缓冲区变脏，需要将其写入磁盘。CKPT 进程使用检查点信息更新控制和数据文件头，并向脏盘写入脏缓冲区的信号。

---

7.数据库

磁盘上存储的数据的集合

在物理上表现为数据文件、日志文件和控制文件等

在逻辑上以表空间形式存在

必须首先创建数据库，然后才能使用Oracle

---

8.数据库实例

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

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

---

9.数据文件(dbf)

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

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

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

---

10.表空间

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

表空间类型：

永久性表空间：一般保存表、视图、过程和索引等的数据。(默认SYSTEM、USERS)

临时性表空间：只用于保存系统中短期活动的数据。(TEMP)

撤销表空间：用来帮助回退未提交的事务数据。(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拥有更大的权限

用户类型SYSTEM用户SYS用户

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

通过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表和数据，对该表中数据执行操作

STUNOSTUNAMESTUAGESTUIDSTUSEAT

1	张三	18		1
2	李四	20		2
3	王五	15		3
4	张三	18		4
5	张三	20		5

示例与要求：

/* ====================================== 创建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.获得所有重复记录的ROWIDselect max(rowId) from stuInfo --1.查找重复的记录，只保留一条group by stuName,stuAgehaving (count(stuAge||stuAge)>1)union --3.合并结果集，获得所有符合条件且不重复的记录--2.查找不重复的记录select max(rowId) from stuInfogroup by stuName,stuAgehaving (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操作符分类

集合操作符

UNION（联合）

UNION ALL（联合所有）

INTERSECT（交集）

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转换函数

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

TO_CHAR( )

TO_DATE( )

TO_NUMBER( )

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

语法： 函数名([ 参数]) 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、章节总结

---