Oracle知识概括

Oracle简介

Oracle 简介：

• Oracle介绍：
  ①Oracle 是殷墟出土的甲骨文(oracle bone inscriptions)的英文翻译的第一个单词。
  ②Oracle 公司是全球最大的信息管理软件及服务供应商，成立于1977年，总部位于美国加州 Redwood shore。
  ③Oracle 公司因其复杂的关系数据库产品而闻名。Oracle的关系数据库是世界第一个支持SQL语言的数据库。
• Oracle概述：
  ①Oracle数据库是一种网络上的数据库, 它在网络上支持多用户, 支持服务器/客户机等部署(或配置)
  ②服务器与客户机是软件概念, 它们与计算机硬件不存在一一对应的关系. 即: 同一台计算机既可以充当服务器又可以充当客户机, 或者, 一台计算机只充当服务器或只充当充当客户机.

Oracle SQL Developer 和PL SQL Developer区别：

• Oracle SQL Developer是Oracle公司出品的一个免费的集成开发环境。是一个免费非开源的用以开发数据库应用程序的图形化工具，使用 SQL Developer 可以浏览数据库对象、运行 SQL 语句和脚本、编辑和调试 PL/SQL语句。另外还可以创建执行和保存报表。该工具可以连接任何 Oracle 9.2.0.1 或者以上版本的 Oracle 数据库，支持Windows、Linux 和 Mac OS X 系统。
• PL/SQL Developer是一个集成开发环境，由Allround Automations公司开发，专门面向Oracle数据库存储的程序单元的开发。有越来越多的商业逻辑和应用逻辑转向了Oracle Server，因此，PL/SQL编程也成了整个开发过程的一个重要组成部分。PL/SQL Developer侧重于易用性、代码品质和生产力，充分发挥Oracle应用程序开发过程中的主要优势的。

Oracle数据库的安装：

• 安装步骤：
  ①下载oracle 19c安装包 ，WINDOWS.X64_193000_db_home.zip，解压文件
  ②以管理员身份运行setup.exe,注意解压和安装路径中都不不要有中文
  ③选择以下任意安装选项：创建并配置【单实例数据库】。此选项用于创建启动数据库。
  ④桌面类就是客户端，服务器类是数据库服务，根据需求选择，可考虑到后面深入学习Oracle数据库，所以选择安装【服务器类】的，当然对运行的系统资源的开销也大。
  ⑤安装类型：有典型安装和高级安装，考虑到学习需要因此选择【高级安装】。
  ⑥数据库版本：有企业版本和标准版本，考虑到学习需要因此选择【企业版本】。
  ⑦Oracle主目录用户：这里可以选择使用【虚拟账户】或者指定【标准windows账户】。
  ⑧安装位置：填写Oracle基目录：存放地址
  ⑨配置类型：选择要创建的数据库类型，有一般用途/事务处理和数据库仓库两种选择，这里我们选择【一般用途/事务处理】。
  ⑩数据库标识符：这里需要配置【全局数据库名】和【Oracle系统标识符(SID)】。统一设置为orcl。注意：如果不想在安装进度 42% 处等待半小时，请不要勾选创建为容器数据库 选项
  ⑪配置选项：此处主要配置数据库使用的内存，字符集以及示例方案，字符集方面我选择的是【UTF－８】编码。
  ⑫数据库存储：选择【文件系统】，并默认指定了数据库文件存储位置。
  ⑬管理选项：默认即可。
  ⑭恢复选项 ：学习阶段可选择不启用。
  ⑮方案口令：选择【对所有账户使用相同的口令】，然后输入口令和确认口令。注意：设置密码后会出现下面这个提示框，选择“是”即可，上面设置的密码只是为了方便使用
  ⑯先决条件检查：检查安装条件。
  ⑰概要：当安装环境检查完成后，会进入下面这个对话框，点击安装会启动安装程序。
  ⑱安装产品：安装数据库。
• 验证：win + r 打开dos窗口，再命令行输入“ cmd ”回车，然后输入指令：sqlplus/nolog。
• 用户名与密码：
  ①用户名：system，密码：默认为创建的时候填的口令，SID：创建数据库时的数据库名，没更改的话，默认为orcl。

Oracle 数据库体系结构简介：

• 平常所说的 Oracle 或 Oracle 数据库指的是 Oracle 数据库管理系统. Oracle数据库管理系统是管理数据库访问的计算机软件(Oracle database manager system). 它由 Oracle 数据库和Oracle 实例(instance)构成.
• Oracle 数据库: 一个相关的操作系统文件(即存储在计算机硬盘上的文件)集合,这些文件组织在一起, 成为一个逻辑整体, 即为 Oracle 数据库。Oracle 用它来存储和管理相关的信息.Oracle数据库必须要与内存里实例合作，才能对外提供数据管理服务。
• Oracle 实例: 位于物理内存里的数据结构，它由操作系统的多个后台进程和一个共享的内存池所组成,共享的内存池可以被所有进程访问。
  ①Oracle 用它们来管理数据库访问.用户如果要存取数据库(也就是硬盘上的文件) 里的数据, 必须通过Oracle实例才能实现, 不能直接读取硬盘上的文件。
  ②实际上, Oracle 实例就是平常所说的数据库服务(service) 。
• 区别：实例可以操作数据库；在任何时刻一个实例只能与一个数据库关联，访问一个数据库；而同一个数据库可由多个实例访问（RAC）。

Oracle 数据库的启动：

• Oracle 数据库是一个庞大的软件. 启动它会占有大量的内存和 CPU 资源. 如果不想让 Oracle 数据库自动启动可做如下设置：
  ①我的电脑-管理-服务和应用程序-服务：
  ②将ServiceORCL和Listener设置为手动，其它禁用

Oracle卸载：

• windows服务中将Oracle所有服务全部停掉
• 选中Oracle - OraDb10g_home2->Oracle Installation Products->Universal Installer
• 以上只是简单的将Oracle卸载掉了，还学要对注册表进行修改：
  ①修改注册表，在开始-运行中执行regedit命令，进入注册表，对注册表中的键值进行修改
  <1>将HKEY_CLASS_ROOT下所有以ORACLE或者ORAL开头的注册表项删除
  <2>将HKEY_LOCAL_MACHINE\SOFTWARE下ORACLE注册表项删除
  <3>将HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Service下的以Oracle开头的注册表项删除
• 重新启动计算机
• 删除 c:\Program Files\Oracle目录
• 通过以上步骤才正真的完成了Oracle的卸载，此时可以安装新的Oracle

Oracle 的(资源限制)概要文件：

• 为了控制系统资源的使用, 可以利用资源限制概要文件.
• 资源限制概要文件是 Oracle 安全策略的重要组成部分, 利用资源限制概要文件可以对数据库用户进行基本的资源限制,而且还可以对用户的口令进行管理.
• 使用资源限制概要文件可以限制下列资源的使用：
  ①每个会话或每个语句的 CPU 时间(以百分之一秒计)
  ②每个用户的并发数据库会话
  ③每个会话的最大链接事件和空闲时间(以分计)
  ④可供多线程服务器会话使用的最大的服务器内存.
• 使用资源限制概要文件可以对每个指定此概要文件的用户账号进行一下设置：
  ①允许用户连续输入错误口令的次数, 在此之后 Oracle 将锁定账户
  ②口令的过期时间(以天计)
  ③允许用户使用一个到期口令的天数, 这之后 Oracle 将锁定账号
  ④是否检查一个账号口令的复杂性, 以防止账号使用明显的口令
• Oracle 数据库的默认概要文件：
  ①每个 Oracle 数据库都有一个默认的资源概要文件, 名为 DEFAULT
  ②当创建一个新的数据库用户且不对用户分配一个特定的概要文件时, Oracle 自动给用户分配数据库的 DEFAULT 概要文件. 默认时,数据库 DEFAULT 概要文件的所有资源限制设置为无限制的.
• 可以利用企业管理器查看概要文件和创建概要文件。

Oracle模式(schema)：

• 模式: 组织相关数据库对象的一个逻辑概念, 与数据库对象的物理存储无关. 一个模式只能属于一个数据库用户,而且模式的名称与用户的名称相同.
• Oracle 数据库的每个用户都拥有唯一的模式. 默认情况下, 用户所创建的所有模式对象都保存在自己的模式中.在 Oracle数据库中模式与用户账号为一一对应的关系
• 如果要从一个模式中引用另一个模式中的对象, 可以使用 点表示法. 不同模式中的对象名可以重复.

 george要访问 scott 用户的 emp 表, 
   scott.emp

• 模式对象和非模式对象：
  ①能包含在模式中的对象称为模式对象.
  ②Oracle 数据库中有许多类型的对象, 但不是所有的对象都可以组织在模式中. 可以组织在模式中的对象有: 表, 索引, 触发器等.
  ③有一些不属于任何模式的数据库对象, 称为非模式对象. 如: 表空间, 用户账号, 角色, 概要文件等.

Oracle用户表空间:

• 用户的默认表空间：
  ①表空间是数据库的逻辑存储设备, 它把数据库信息组织成物理存储空间.
  ②表空间由数据文件组成.用户的各种模式对象(如表, 索引, 过程, 触发器等) 都是放在表空间中.
  ③对每个数据库用户, 都可以设置一个默认表空间. 当用户创建一个新的数据库对象(如表), 并且不明确地为此对象指定表空间时, Oracle 会把所创建的这个新数据库对象存放到用户默认的表空间中.
  ④如果不给用户指定默认表空间, 则用户的默认表空间为 USERS 表空间.
• 用户的临时表空间：
  ①一般, SQL 语句在完成任务时需要临时工作空间. 例如：一个用来连接和排序大量的查询需要临时工作空间来存放结果. 除非另外指定, 一般情况下, 用户的临时表空间是 TEMP 表空间.
  ②若数据库中没有创建 TEMP 表空间, 则用户的临时表空间为 SYSTEM 表空间.
  ③因为 SYSTEM 表空间是用来保存数据库系统信息(数据库自身信息的内部系统表和视图 ---- 数据字典; 所有 PL/SQL 程序的源代码 ---- 包括函数, 触发器等)的. 如果用户大量使用此表空间存储自己的数据, 将会影响系统的执行效率. 因此一般不建议用户使用 SYSTEM 表空间

Oracle SQL

SQL语句分为以下三种类型：

• DML: Data Manipulation Language 数据操纵语言，DML用于查询与修改数据记录，包括如下SQL语句：
  ①INSERT：添加数据到数据库中
  ②UPDATE：修改数据库中的数据
  ③DELETE：删除数据库中的数据
  ④SELECT：选择（查询）数据
  ⑤SELECT是SQL语言的基础，最为重要。
• DDL: Data Definition Language 数据定义语言，DDL用于定义数据库的结构，比如创建、修改或删除数据库对象，包括如下SQL语句：
  ①CREATE TABLE：创建数据库表
  ②ALTER TABLE：更改表结构、添加、删除、修改列长度
  ③DROP TABLE：删除表
  ④CREATE INDEX：在表上建立索引
  ⑤DROP INDEX：删除索引
• DCL: Data Control Language 数据控制语言，DCL用来控制数据库的访问，包括如下SQL语句：
  ①GRANT：授予访问权限
  ②REVOKE：撤销访问权限
  ③COMMIT：提交事务处理
  ④ROLLBACK：事务处理回退
  ⑤SAVEPOINT：设置保存点
  ⑥LOCK：对数据库的特定部分进行锁定

基本SQL SELECT语句：

• 语句：SELECT *|{[DISTINCT] column|expression [alias],…} FROM table;
• 注意：
  ①SQL 语言大小写不敏感。
  ②SQL 可以写在一行或者多行
  ③关键字不能被缩写也不能分行
  ④各子句一般要分行写。
  ⑤使用缩进提高语句的可读性。
• 操作符优先级：
  ①乘除的优先级高于加减。
  ②同一优先级运算符从左向右执行。
  ③括号内的运算先执行。
• 定义空值：
  ①空值是无效的，未指定的，未知的或不可预知的值。
  ②空值不是空格或者0。
  ③包含空值的数学表达式的值都为空值。
• 列的别名：
  ①重命名一个列。
  ②便于计算。
  ③紧跟列名，也可以在列名和别名之间加入关键字‘AS’，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。
• 连接符:
  ①把列与列，列与字符连接在一起。
  ②用 ‘||’表示。
  ③可以用来‘合成’列。
• 字符串：
  ①字符串可以是 SELECT 列表中的一个字符,数字,日期。
  ②日期和字符只能在单引号中出现。
  ③每当返回一行时，字符串被输出一次。
• 重复行：
  ①默认情况下，查询会返回全部行，包括重复行。
  ②在 SELECT 子句中使用关键字 ‘DISTINCT’ 删除重复行。
• 显示表结构：DESCRIBE(DESC) 表名;

SQL 和 SQL*Plus：

• SQL：
  ①一种语言
  ②ANSI 标准
  ③关键字不能缩写
  ④使用语句控制数据库中的表的定义信息和表中的数据
• SQL*Plus：
  ①一种环境
  ②Oracle 的特性之一
  ③关键字可以缩写
  ④命令不能改变数据库中的数据的值
  ⑤集中运行
• 使用SQL*Plus可以：
  ①描述表结构。
  ②编辑 SQL 语句。
  ③执行 SQL语句。
  ④将 SQL 保存在文件中并将SQL语句执行结果保存在文件中。
  ⑤在保存的文件中执行语句。
  ⑥将文本文件装入 SQL*Plus编辑窗口。
  

过滤和排序数据：

• 使用WHERE 子句，将不满足条件的行过滤掉。
• 语句：SELECT *|{[DISTINCT] column|expression [alias],…} FROM table [WHERE condition(s)];
• 字符和日期：
  ①字符和日期要包含在单引号中。
  ②字符大小写敏感，日期格式敏感。
  ③默认的日期格式是 DD-MON月-RR。
• 比较运算：

操作符	含义
=	等于 (不是 ==)
>	大于
>=	大于、等于
<	小于
<=	小于、等于
<>	不等于 (也可以是 !=)
:=	赋值

• 其它比较运算：
  ①使用 BETWEEN 运算来显示在一个区间内的值。
  ②使用 IN运算显示列表中的值。
  ③使用 LIKE 运算选择类似的值，选择条件可以包含字符或数字:
  1、% 代表零个或多个字符(任意个字符)。
  2、_ 代表一个字符。
  3、'%‘和’_'可以同时使用。
  4、可以使用 ESCAPE 标识符 选择‘%’和 ‘_’ 符号。
  5、回避特殊符号的：使用转义符。例如：将[%]转为[%]、[_]转为[_]，然后再加上[ESCAPE ‘\’] 即可。
  ④使用 IS (NOT) NULL 判断空值。

操作符	含义
BETWEEN…AND…	在两个值之间 (包含边界)
IN(set)	等于值列表中的一个
LIKE	模糊查询
IS NULL	空值

• 逻辑运算：
  ①AND 要求并的关系为真。
  ②OR 要求或关系为真。

操作符	含义
AND	逻辑并
OR	逻辑或
NOT	逻辑否

• ORDER BY子句：
  ①使用 ORDER BY 子句排序
  <1>ASC（ascend）: 升序
  <2>DESC（descend）: 降序
  ②ORDER BY 子句在SELECT语句的结尾。
  ③按别名排序：SELECT salary*12 annsal FROM employees ORDER BY annsal;
  ④多个列排序：可以使用不在SELECT 列表中的列排序。

单行函数：

• 单行函数:
  ①操作数据对象
  ②接受参数返回一个结果
  ③只对一行进行变换
  ④每行返回一个结果
  ⑤可以转换数据类型
  ⑥可以嵌套
  ⑦参数可以是一列或一个值
  ⑧function_name [(arg1, arg2,…)]
• 字符函数：
  ①大小写控制函数：
  <1>LOWER
  <2>UPPER
  <3>INITCAP
  ②字符控制函数
  <1>CONCAT
  <2>SUBSTR
  <3>LENGTH
  <4>INSTR
  <5>LPAD | RPAD
  <6>TRIM
  <7>REPLACE
• 数字函数：
  ①ROUND: 四舍五入
  ②TRUNC:截断
  ③MOD: 求余
• 日期函数：Oracle 中的日期型数据实际含有两个值: 日期和时间。
  ①函数SYSDATE 返回：日期和时间。
  ②日期的数学运算：
  <1>在日期上加上或减去一个数字结果仍为日期。
  <2>两个日期相减返回日期之间相差的天数。日期不允许做加法运算，无意义。
  <3>可以用数字除24来向日期中加上或减去天数。
  ③mysql文档中对于dual表的解释：你可以在没有表的情况下指定一个虚拟的表名。DUAL是为了方便那些要求所有SELECT语句都应该具有FROM和其他子句的人。MySQL可能会忽略该条款。如果没有引用表，MySQL不需要从DUAL。

函数	描述
MONTHS_BETWEEN	两个日期相差的月数
ADD_MONTHS	向指定日期中加上若干月数
NEXT_DAY	指定日期的下一个星期 * 对应的日期
LAST_DAY	本月的最后一天
ROUND	日期四舍五入
TRUNC	日期截断

• 转换函数：数据类型转换分为隐性和显性。
  ①Oracle 自动完成下列转换：
  
  ②显式数据类型转换：
  
  ③函数：
  <1>TO_CHAR函数对日期和数字的转换
  <2>TO_DATE 函数对字符的转换
  <3>TO_NUMBER 函数对字符的转换

日期格式的元素	-
YYYY	2004
YEAR	TWO THOUSAND AND FOUR
MM	02
MONTH	JULY
MON	JUL
DY	MON
DAY	MONDAY
DD	02

数字格式的元素	-
9	数字
0	零
$	美元符
L	本地货币符号
.	小数点
,	千位符

• 通用函数：这些函数适用于任何数据类型，同时也适用于空值：
  ①NVL (expr1, expr2)：将空值转换成一个已知的值，可以使用的数据类型有日期、字符、数字。
  ②NVL2 (expr1, expr2, expr3)：expr1不为NULL，返回expr2；为NULL，返回expr3。
  ③NULLIF (expr1, expr2)：相等返回NULL，不等返回expr1 。
  ④COALESCE (expr1, expr2, …, exprn)：COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE 。
  ⑤SYS_GUID()：该函数用于生产类型为RAW的16字节的唯一标识符，每次调用该函数都会发生不同的RAW数据。
• 条件表达式：在 SQL 语句中使用IF-THEN-ELSE 逻辑

一、CASE 表达式：
CASE expr WHEN comparison_expr1 THEN return_expr1
         [WHEN comparison_expr2 THEN return_expr2
          WHEN comparison_exprn THEN return_exprn
          ELSE else_expr]
END

二、DECODE 函数
DECODE(col|expression, search1, result1 ,
      			   [, search2, result2,...,]
      			   [, default])

• 嵌套函数：
  ①单行函数可以嵌套。
  ②嵌套函数的执行顺序是由内到外。

多表查询：

• 笛卡尔集：
  ①笛卡尔集会在下面条件下产生:
  <1>省略连接条件
  <2>连接条件无效
  <3>所有表中的所有行互相连接
  ②为了避免笛卡尔集， 可以在 WHERE 加入有效的连接条件。
• Oracle 连接：
  ①使用连接在多个表中查询数据。
  ②SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2;
  ③在 WHERE 子句中写入连接条件。
  ④在表中有相同列时，在列名之前加上表名前缀。
• 等值连接：

SELECT employees.employee_id, employees.last_name, 
       employees.department_id, departments.department_id,
       departments.location_id
FROM   employees, departments
WHERE  employees.department_id = departments.department_id;

• 区分重复的列名：
  ①使用表名前缀在多个表中区分相同的列。
  ②在不同表中具有相同列名的列可以用表的别名加以区分。
• 表的别名：
  ①使用别名可以简化查询。
  ②使用表名前缀可以提高执行效率。
• 非等值连接：

SELECT e.last_name, e.salary, j.grade_level
FROM   employees e, job_grades j
WHERE  e.salary 
       BETWEEN j.lowest_sal AND j.highest_sal;

• 内连接和外连接：
  ①内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行。
  ②外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL). 外连接的 WHERE 子句条件类似于内部连接, 但连接条件中没有匹配行的表的列后面要加外连接运算符, 即用圆括号括起来的加号(+)。
  ③使用外连接可以查询不满足连接条件的数据。
  ④外连接的符号是 (+)。

一、右外连接：
SELECT	table1.column, table2.column
FROM	table1, table2
WHERE	table1.column(+) = table2.column;

二、左外连接：
SELECT	table1.column, table2.column
FROM	table1, table2
WHERE	table1.column = table2.column(+);

• 自连接：

SELECT worker.last_name || ' works for ' 
       || manager.last_name
FROM   employees worker, employees manager
WHERE  worker.manager_id = manager.employee_id ;

• 使用SQL: 1999 语法连接：

SELECT	table1.column, table2.column
FROM	table1
[CROSS JOIN table2] |
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2 
  ON(table1.column_name = table2.column_name)] |
[LEFT|RIGHT|FULL OUTER JOIN table2 
  ON (table1.column_name = table2.column_name)