Oracle数据库

注意：其实oracle数据库跟mysql数据库基本语法大致一样只有小部分语言存在差别。

安装PL/SQL Developer

一.数据库实例

1.1 启动数据库实例 一个Oracle实例（Oracle Instance）有一系列的后台进程（Backguound Processes)和内存结构 （Memory Structures)组成。一个数据库可以有n个实例。。当启动数据库的时候，这些内存结构和服务器进程得到分配、初始化、启动。这样 Oracle才能管理数据库。

启动数据库实例分为3个步骤：

用户可以根据实际情况的需要，以不同的模式启动数据库。启动数据库使用startup命令，语法格式如下：

NOMOUNT：表示启动实例但不加载数据库

MOUNT：表示启动实例，加载数据库，并保持数据库的关闭状态

OPEN:表示启动实例，加载并打开数据库，它是默认选项

FORCE:表示终止实例并重新启动数据库

RESTRICT：用于指定以受限制的会话方式启动数据库

用sys用户登录，使用FORCE模式启动数据库

使用OPEN默认模式启动数据库

1.2 关闭数据库实例

关闭数据库实例和启动是相反的，也分为三个步骤：

在SQL*Plus中，可以使用SHUTDOWN语句关闭数据库，具体语法格式如下：

使用NORMAL方式关闭数据库：

使用TRANSACTIONAL方式关闭数据库：

二.Oracle体系结构

2.1 Oracle体系结构概述 Oracle数据库服务器由两部分组成：实例、

数据库数据库的本质就是存储数据，数据库存储数据的方式通常称为存储结构。Oracle数据库的存储结构分为逻辑存储 结构和物理存储结构。逻辑存储结构用于描述Oracle内部组织和管理数据的方式。物理存储结构用于展示Oracle在 操作系统中的物理文件组成情况。

启动Oracle数据库服务器实际上是在服务器的内存中创建一个Oracle实例，然后用这个实例来访问和控制磁盘中 的数据文件。当用户连接到数据库时，实际上连接的是数据库的实例(orcl)，然后由实例负责与数据库进行通信。最 后将处理结果返回给用户

实例指的是一组Oracle后台进程以及在服务器中分配的共享内存结构，他用于管理和控制数据库。 数据库的主要功能是存储数据，是由基于磁盘的数据文件、控制文件、日志文件、参数文件、归档文件等 组成的物理文件集合。它为实例提供数据。 数据库服务器是指管理数据库的各种软件工具(PL/SQL Developer、SQLPlus等)、实例及数据库。 一个数据库可以被多个实例装载和打开，而一个实例在其生存期内只能装载和打开一个数据库。

2.2 逻辑存储结构

逻辑存储结构是从逻辑角度分析数据库的组成，它是一种层次结构。主要由数据库、表空间、段、区、数据块等 概念组成。

从上图可知Oracle由多个表空间组成，而表空间又是由多个段组成，段由多个数据区组成，数据区又是由多个数 据块组成。

SQL语言基础

SQL(Struct Query Language)是结构化查询语言的简称，是一种在关系型数据库中定义和操纵数据的标准语言。不要使用面向对象的思想学习SQL，因为它不是面向对象的语言

目标

SQL语言简介(了解)

从数据库数据检索数据(重点)

子查询(重点)

Oracle常用函数(掌握)

1 SQL语言简介

1.1 SQL的特点

SQL语句通常用于完成一些数据库的操作任务，例如增加、修改、删除、查询数据以及对数据库对象(表、视图、索引、过程、函数、触发器等等)的一些列操作。巧妙运用SQL语句能够简化编程，起到事半功倍效果。

程序员或者数据库管理员使用SQL语句能够完成下面的操作

：创建用户、创建表空间、创建表

create user

create tablespace

create table

删除用户、删除表空间、删除表

drop user

drop tablespace

drop table

改变数据库系统环境设置

set pagesize

set linesize

set newpage

用户授权grant

为数据库的表建立索引

create index

修改数据库表结构

通过alter table 关键字新建、删除、修改表个字段

对数据库中的数据进行添加、修改、删除、查询

通过insert into、update、delete from、select....from语句对数据库表中的数据进行增删改查SQL语言主要的特点如下：

1. 集合性

SQL语句执行返回一个结果集。例如：使用select查询一个结果集、使用delete from语句删除表中的多条数据、使用update语句批量修改多条数据

2. 统一性

SQL语句为许多任务提供了统一的命令

查询数据使用select from关键字

在表中插入、修改、删除数据使用insert into、update、delete from关键字

创建、删除、修改数据对象

创建数据对象使用create关键字

修改数据对象使用alter关键字

删除数据对象使用drop关键字

控制对数据和数据对象的读写

commit关键字提交数据

rollback关键字回滚数据

保证数据的完整性和一致性，每一种约束都有自己统一的关键字

主键约束使用primary key关键字

外键约束使用foreign key关键字

唯一约束使用unique关键字

检查约束使用check关键字

非空约束使用not null关键字

默认约束使用default关键字

3. 非过程化

SQL语句是一个非过程化语言，因为它没有分支、循环结构

4. 简单

SQL只有几个命令动词就可以实现对数据库以及数据的查询和管理。下面的表讲解了SQL语言的分类，以及对应的关键字(命令动词)

1.2 SQL的编写规则

SQL关键字不区分大小写、既可以使用大写格式、又可以使用小写格式、或者大小写混用。

对象名(表名称)、列名称不区分大小写

字符值区分大小写

如果SQL语句很长，可以将其分行显示，提高阅读性

select empno 员工编号, ename 员工姓名, job 职位

from emp

where empno = 7369

小结：本单元主要对SQL语言做了一个宏观上的总体介绍，后面还会针对每个关键字进行详细讲解

2 用户模式

在Oracle数据库中，为了方便管理创建的数据库对象（表、视图、索引等等），引入了模式的概念，某个用户创建的数据库对象都属于该用户模式。例如在scott模式下创建了student表，那么该表就属于scott模式。

Student表此时就是一个数据对象，它在scott用户下面创建的，只能属于scott用户

模式也叫作用户 模式 等价于 用户

Oracle数据库里面的数据库对象（表、视图、索引等等）都是跟着用户(模式)走的。创建用户(模式)的目的：为了更好的管理数据对象（表、视图、索引等等）

2.1 模式与数据库对象关系

模式是数据库对象的集合，一个模式对应多个数据库对象。例如：sys模式、scott模式等等。在一个模式(sys)内部不可以直接访问其他模式(scott)的数据库对象，即使在具有访问权限的情况下，也需要指定模式名称才可以访问其它模式的数据库对象。

例如：使用sys模式登录，在sys模式(用户)下访问scott模式(用户)的emp表

select * from scott.emp;scott.emp

表示scott模式(用户)下的emp表

小结：1. 模式(用户)用来管理数据库对象的，一个模式(用户)下面可以有多个数据对象

2. 某个数据对象(emp表)只能属于一个模式(用户)

3. 必须指定模式名称才可以访问其它模式的数据库对象 scott.emp

2.2 scott模式(用户)

scott模式(用户)是Oracle数据库经典的模式(用户)，该模式演示了一个很简单的人力资源管理的数据结构。我们通常使用它进行学习。在Oracle19c中并没有scott用户需要我们手动创建并激活。

小结：

1. 模式(用户)拥有数据库对象（表、视图、索引等等），数据对象被模式(用户)拥有。

一个模式(用户)下面有多个数据对象

一个数据库对象只属于一个模式(用户)

2. 在工作中我们通常说数据库对象（表、视图、索引等等）是跟着用户走的。

3 数据检索(查询)

用户对表或视图用的最多的操作就是检索(查询)数据。检索数据可以通过select语句来实现，该语句由多个子句组成，通过这些子句对数据库表进行各种操作，最终得到用户想要的查询结果。select基本语法格式如下：

在数据检索(查询)语句中SELECT和FROM子句必须有，其它关键字可选

[] 表示可选

|表示或者

3.1 简单的查询 只包含select和from子句的查询就是简单查询连接scott用户，

查询员工表所有的列

1. 上面的查询语句表示：从emp表中检索数据，然后选择表中所有的列

2. 先执行from关键字确定要查询哪张表，然后执行select选择表中的列

查询指定的列

指定查询表中某些列名称，这些列名称跟在select关键字后面，每个列名称之间使用逗号分隔。语法如下：

例如：查询员工表的：员工编号、员工姓名、基本工资、职位

为列指定别名列别名

就是指列的小名，使用列别名可以提高查询语句的可读性

select语句中的算术运算符 + - * /

算数运算符大多数情况下用在select后面的列中，对列值进行算数运算

通常只对数值类型的列进行算数运算

select语句中的比较运算符

比较运算符通常对where或者having关键字后面的条件进行判断，如果条件成立就提取数据，否则就过滤数据。Oracle的比较运算符：= > >= < <= != <>

注意：Oracle比较运算符有两个不等于 != <>

小结：

1. from关键字确定要查询那张表

2. where关键字用来做条件判断(行过滤)，如果条件成立就提取数据，否则就过滤数据。

3. select用来选择表中指定的列进行查询

4. 关键字执行顺序from-->where-->select

注意：WHERE执行之前通过from就已经产生了一个查询结果集，where条件判断是在结果集里面进行的

select语句中的逻辑运算符

逻辑运算符的两侧都是条件表达式，Oracle支持的逻辑运算符如下：

逻辑运算符通常定义在WHERE关键字后面，用来根据条件进行行过滤示例：查询工资大于等于1500,并且职位等于销售人员的所有员工信息

示例：查询工资大于等于1500或者职位等于销售人员的所有员工信息

示例：查询工资不大于等于1500,或者职位不等于销售人员的所有员工信息

oracle中所有运算符的优先级如下

通常使用()可以改变运算符的优先级

显示不重复记录

在select语句中，可以使用DISTINCT关键字来显示不重复记录据。语法如下：

示例：显示emp表中的job(职位)列，要求显示的“职位”列记录不能重复：

小结：

distinct关键字用来去重(显示不重复记录)，该关键字在select和列名称之间定义，如果某个列名称前面有distinct关键字，那么该列的数据不会重复。

3.2 特殊关键字筛选

in、between and、like、not null这些特殊的关键字必须定义在WHERE条件后面，用来筛选数据的。

集合查询

in 关键字 在...里面 。用来判断某个字段的值是否在in集合里面，如果存在就提取数据，否则就过滤数据。语法： where 字段名称 in(目标值1，目标值2，目标值3....)判断“字段名称”里面的每个值是否在in里面，如果在就提取数据，否则就过滤掉数据示例：

查询员工编号为7566、7499、7369的员工信息

模糊查询

like 关键字 像......一样

特征：只需要部分字符匹配即可

模糊查询有三种：

前面精确后面模糊语法：

where 字段名称 like '字符串%'%

表示模糊匹配多个字符

要查找的字符串必须写在一对半角单引号里面

示例：前面精确后面模糊的方式查询所有员工姓名以J开头的员工信息

前面模糊后面精确

语法：where 字段名称 like '%要查找的字符串'

示例：前面模糊后面精确的方式查询员工名称以R结束的所有员工信息

先后模糊中间精确语法：

where 字段名称 like '%要查找的字符串%'

特征：前面和后面都可以是任意字符串，只要中间满足条件即可

示例：先后模糊中间精确的方式查询员工名称包含A的所有员工信息

小结：like关键字通常用在where后面,模糊查询的数据类型通常是字符串类型

区间查询 BETWEEN...AND

BETWEEN...AND关键字用在WHERE后面，用于判断某个条件是否在指定的区间。如果条件成立就提取数据，否则就过滤数据

BETWEEN后面跟着低区间值

AND后面跟着高区间值

示例：查询工资在1000到2000范围的所有员工信息

IS NULL 关键字

IS NULL 关键字用来判断某个字段是否为空，通常在WHERE关键字后面使用。当字段值为空就提取数据否则就过滤数据

示例：查询emp表中没有奖金的员工信息

3.3 聚合函数

聚合就是对查询结果集做汇总，Oracle提供了5个聚合函数：count(计数)、sum(求总和)、avg(求平均值)、max(计算最大值)、min(计算最小值)。 特征：在结果集之上进行汇总

count

count聚合函数用来统计结果集总行数。示例：统计emp表总行数

sum

sum聚合函数用来计算某一列的总和。示例：统计emp表所有员工工资总和

max

max聚合函数用来计算某一列的最大值。示例：统计emp表最高工资

min

min聚合函数用来计算某一列的最小值。示例：统计emp表最低工资

avg

avg聚合函数用来计算某一列的平均值。示例：统计emp表平均工资

多个聚合函数一起使用

示例：查询员工表的总行数、工资总和、最高工资、最低工资、平均工资

小结：1. 每个聚合函数返回的结果集为单行单列

2. 除了count以外，其它的聚合函数都使用数值类型作为参数

3.4 分组查询

数据分组的目的是用来汇总数据，在查询结果集中使用GROUP BY子句对记录(行)进行分组，它通常和聚合函数一起使用。语法格式如下：

3.4.1 使用GROUP BY进行单列分组

进行单列分组时，会基于分组列的每个不同值生成一个统计结果

示例：统计每个职位的总工资，还要排除员工编号为7369的员工步骤：

1. 列出查询语句需要的关键字 select from where group by

2. 在from后面定义要查询的表名称 emp

3. 在where后面定义条件 ，此时的条件是：排除员工编号为7369的员工 where empno <> 7369

4. 在group by后面定义 需要分组的列，此时为职位

5. 在select后面定义要查询的列名称

小结：1. 上面的SQL语句执行的时候先执行group by对每个职位进行分组，然后执行聚合函数 sum(sal)对每个分组的职位进行汇总

2. 关键字执行的顺序 from-->group-->select

3.4.2 使用GROUP BY进行多列分组 多列分组是基于两个或者两个以上的列生成分组统计结果。当进行多列分组时，会基于多个列的不同值生成统计结果。

示例：显示每个部门每种职位平均工资、职位最高工资。

分析：我们需要对deptno(部门编号)字段和job(职位)字段进行分组，然后对sal(工资字段)进行汇总 步骤：1. 列出查询语句需要的关键字 select from group by

2. 在from后面定义要查询的表名称 emp

3. 在group by后面定义 需要分组的列，此时为部门编号和职位

4. 在select后面定义要查询的列名称

3.5 having

having跟where关键字一样都用于行过滤，如果要对分组之后的结果集进行行过滤请使用having关键字。having职责：对分组之后的结果集进行过滤

示例：我要统计每个职位的总工资，但是排除员工编号为7369的员工，还要排除职位总工资小于5000的数据步骤：

1 列出查询关键字 select、from、where、group by、having

2 使用from 关键字查询emp表

3 使用where关键字过滤掉7369的员工信息

4 使用group by 关键字对职位进行分组

5 使用having关键字对分组之后的结果集进行行过滤，过滤掉职位工资总和小于5000的数据

6 在select关键字后面，使用聚合函数计算每个职位的工资总和

目的：理解having关键字

3.6 order by 子句 order by关键字用于对查询的结果集进行排序排序

关键字： order by

语法 ： order by 列名称 或者 列别名 asc 或者 desc

asc 升序排序

desc 降序排序

默认asc 升序排序

注意：order by 关键字通常定义在having的后面

示例：在上一个示例基础上对职位工资总和进行升序排序

步骤： 1 列出查询关键字 select、from、where、group by、having、order by

2 使用from 关键字查询emp表

3 使用where关键字过滤掉7369的员工信息

4 使用group by 关键字对职位进行分组

5 使用having对分组之后的结果集进行行过滤，过滤掉职位工资总和小于5000的数据

6 定义select关键字，使用聚合函数计算每个职位的工资总和

7 使用order by 关键字对职位工资总和进行升序排序

3.7 多表联合查询

关系型数据库管理系统为了减少数据冗余将数据化整为零存放在多张表中，彼此之间通过某种关系进行关联。在需要的时候用户可以将多张表的数据关联起来，组合成一个结果集这个就是多表联合查询。常用的多表联合查询分为4种：内连接查询、左外连接查询、自连接查询、合并查询查询。

3.7.1 内连接查询

内连接查询两张表共有的数据(取交集)，会用到inner join on关键字。其中使用inner join用来指定连接的两张表，使用on指定连接表的连接条件。内连接语法格式如下：

示例：使用内连接查询员工编号、员工姓名、部门名称

分析：员工编号、员工姓名在员工表(emp)，部门名称在部门表(dept)

步骤：

1. 列出查询关键字。select、from、inner join on

2. 确定要查询的表名称。例如：from dept inner join emp

3. 使用on关键字进行条件过滤。例如： on dept.deptno = emp.deptno

4. 指定要查询的列。例如： emp.empno 员工编号,emp.ename 员工名称, dept.dname 部门名称

小结：

1. 多表联合查询在指定列时使用表名称.列名称，这样做的目的是告诉MySQL服务器你的列属于那张表。

2. select emp.empno 表示选择员工表的员工编号字段进行查询。点(英文dot)可以理解为“的”。

使用inner join on关键字的内连接叫做显示内连接，除此之外还有一种内连接叫做隐式内连接。它忽略了inner join on关键字, 将内连接的条件表达式定义在where后面。隐式内连接语法如下：

示例：使用隐式内连接查询员工编号、员工姓名、部门名称。

步骤：1. 列出查询关键字。select、from、where

2. 确定要查询的表名称。例如：from dept，emp

3. 使用where关键字进行条件过滤。例如：where dept.deptno =emp .deptno

4. 指定要查询的列。例如： emp.empno 员工编号,emp.ename 员工名称, dept.dname 部门名称

上面的示例，使用表名称.列名称指定查询哪个表的列，除此之外还可以使用表别名.列名称，这样做的好处让查询语句更加简洁。

表别名就是表的小名，其目的是让查询更加简洁高效

示例：使用表别名的方式查询员工编号、员工姓名、部门名称

3.7.2 左外连接查询

A - A∩B 左外连接查询两张表条件都满足的数据，以及左边表(A表)存在的数据(以左边表为主查询表)。

A - A∩B (A和A交B)

示例：使用左外连接将dept表作为主查询表，查询员工编号、员工姓名、部门名称。观察内连接和左外连接有啥区别？

步骤：1. 列出查询关键字。select、from、leftjoin on

2. 确定要查询的表名称。例如：from emp inner join dept

3. 使用on关键字进行条件过滤。例如：on emp.deptno = dept.deptno

4. 指定要查询的列。例如： emp.empno 员工编号,emp.ename 员工名称, dept.dname 部门名称

小结：

3.7.2 自连接查询

自连接查询将自己(emp表)连接自己(emp表)进行关联查询。多个关联查询的表是同一张表，通过取别名的方式生成两张虚拟表。语法如下：

示例：查询每个员工编号、姓名、上司编号、上级姓名

步骤：1 确定查询关键字 select、from、where

2 确定表名称 emp e1, emp e2

3 确定关联条件 e1.mgr = e2.empno

4 确定列名称 e1.empno 员工编号 ,e1.ename 员工姓名, e2.empno 上司编号 , e2.ename 上司姓名核心步骤：员工表的上司编号字段关联上司表的员工编号字段

3.7.3 合并查询查询

合并查询将多个查询结果集合并成一个

关键字：union all 和 union

区别如下：union all将多个查询结果集合并成一个结果集，不会去掉重复的数据

union将多个查询结果集合并成一个结果集，会去掉重复的数据

示例：查询职位是“职员”的所有员工信息合并查询部门编号为20的所有员工信息

步骤：1. 查询职位是“职员”的所有员工信息

2. 查询部门编号为20的所有员工信息

3. 将上面两个查询的结果集使用union all关键字合并成一个结果集

查询结果如下：

小结：上面的查询编号769的员工出现了两次，union all 并没有去掉两个结果集重复的数据 。为了解决上面的问题，我们可以使用union关键字去掉结果集里面重复的数据

使用union进行合并查询，去掉了结果集重复的数据。查询结果如下：

小结：1. 合并查询里面的每个select语句后面的列名称必须相同，否则执行报错2. union能够去掉多个结果集中重复的数据3. union all由于没有去重，所以效率高于union校招面试题：union和union all区别？

3.8 子查询

在一个查询语句中嵌套一个查询语句叫做子查询，所有的子查询都包含在父查询的一对小括号里面。子查询通常可以出现在以下几个地方：where之后、having之后、from之后、from之前。子查询返回的结果集有三种：单(单行单列)、多(多行单列)、多(多行多列) 。

3.8.1 单行单列子查询

单行单列子查询通常定义在父查询的where关键字后面作为父查询行过滤的条件

示例：查询工资高于公司平均工资的所有员工

分析：公司平均工资我们不知道，先查询平均工资，然后嵌入到父查询

步骤：1. 定义查询语句需要的关键字 select、from、where

2. 在from后面定义父查询的表名称 emp

3. 在where后面确定父查询条件 where 工资高于公司平均工资

4. 在select确定要查询的列名称

查询结果如下：

3.8.2多行单列子查询

多行单列子查询作为一个伪列定义在父查询的from关键字之前。

示例：查询员工编号、员工名称、员工所在部门

第一种做法：多表联合查询

第二种做法：

使用将“员工所在部门”字段作为子查询产生一个伪列，嵌入到父查询中。

步骤：

1. 定义父查询需要的关键字 select、from

2. 在父查询的from后面定义要查询的表名称

3. 在父查询的select后面定义要查询的列

4. 在父查询的select后面定义子查询

5. 定义子查询需要的关键字 select、from、where

6. 在子查询的from后面定义要查询的表名称

7. 在子查询的where后面定义条件

8. 在子查询的select后面定义要查询的列

查询结果如下：

3.8.3 多行多列子查询

多行多列子查询通常作为一张伪表，定义在父查询的from关键字之后。

为了增加查询语句的可读性，通常会为伪表定义表别名

示例：查询平均工资高于2000的职位名称和职位平均工资

第一种做法：使用group by对“职位”字段进行分组，然后使用having关键字后面定义条件(平均工资高于2000)

小结:

1. 这种方式能够查询到正确的结果集，但是聚合函数avg使用了两次，很影响程序执行的性能。

2. 解决方案：将上一个示例的select、from、group by作为子查询嵌入到父查询中。

3. having 关键字工作中效率比较低，可以先使用子查询计算职位名称和职位平均工资，然后将子查询合入到父查询，最后在父查询的where后面定义条件(平均工资高于2000

示例：查询部门编号、部门名称、部门位置、部门人数、部门平均工资

分析：前三列都是部门表(dept)的基础列，后两列需要在员工表需要在聚合函数中进行计算。将员工表需要计算的字段作为子查询生成一张伪表，嵌入到父查询的dept表中。

示例：查询出所有在销售部工作的员工编号、姓名、基本工资、奖金、职位、入职日期、部门最高和最低工资。

分析：1. 该示例可以分成两段

查询出所有在销售部工作的

员工编号、姓名、基本工资、奖金、职位、入职日期、部门最高和最低工资

2. 要查询的字段都在emp表，其中员工编号、姓名、基本工资、奖金、职位、入职日期是基础字段，部门最高工资、部门最低工资这两个字段是需要使用聚合函数进行计算的。我们把需要计算的字段作为子查询的伪表嵌入到父查询