Oracle数据库

📚Oracle学习记录，陆续更新

1.Oracle数据库的安装和配置

Oracle数据库是一种网络上的数据库, 它在网络上支持多用户, 支持服务器/客户机等部署(或配置)

服务器与客户机是软件概念, 它们与计算机硬件不存在一一对应的关系. 即: 同一台计算机既可以充当服务器又可以充当客户机, 或者, 一台计算机只充当服务器或只充当充当客户机.

Oracle 数据库体系结构简介
平常所说的 Oracle 或 Oracle 数据库指的是 Oracle 数据库管理系统. Oracle 数据库管理系统是管理数据库访问的计算机软件(Oracle database manager system). 它由 Oracle 数据库和 Oracle 实例(instance)构成.

Oracle 数据库: 一个相关的操作系统文件(即存储在计算机硬盘上的文件)集合,这些文件组织在一起, 成为一个逻辑整体, 即为 Oracle 数据库.
Oracle 用它来存储和管理相关的信息.Oracle数据库必须要与内存里实例合作，才能对外提供数据管理服务。

Oracle 实例: 位于物理内存里的数据结构，它由操作系统的多个后台进程和一个共享的内存池所组成,共享的内存池可以被所有进程访问.
Oracle 用它们来管理数据库访问.用户如果要存取数据库(也就是硬盘上的文件) 里的数据, 必须通过Oracle实例才能实现, 不能直接读取硬盘上的文件.
实际上, Oracle 实例就是平常所说的数据库服务(service) .

区别：实例可以操作数据库；在任何时刻一个实例只能与一个数据库关联，访问一个数据库，类似用户；而同一个数据库可由多个实例访问（RAC）

1.1服务端安装

安装类型：企业版
全局数据库名：自己设置，设置为：orcl
数据库口令（密码）：自己设置，设置为：orcl




数据库的创建，时间花费较多

展示所有的默认的用户，其中SYS、SYSTEM默认是不锁定的。
解锁后面的SCOTT用户，之后的学习都使用SCOTT用户


安装完成之后任务管理器的服务里面会新增几项Oracle的服务


安装文件比较大，安装之后服务一直保持开启的话比较耗费资源

耗费资源太多，学习时只需要开启（手动）以下两项服务，其他三项服务禁用
OracleServiceORDL
OracleOraDb10g_home1TNSListener：监听器，连接服务端

服务端安装完毕！

1.2客户端安装

要从局域网内的一台计算机上访问另一台计算机上的 Oracle 服务. 需要在此计算机上安装能通过局域网访问另一台计算机上的 Oracle 服务的客户机.


安装类型选择管理员

首次安装的路径为：client_1
第二次安装路径为：client_2，以此类推



服务名orcl


主机名添加访问服务端的IP
端口号为默认的Oracle端口号：1521

进行测试

更改登录：输入安装服务端时设置的系统用户密码，这里是：orcl

一直下一步，最后完成

1.3测试连接不成功

若测试成功，则忽略此步。

如果测试不成功，需要操作如下：
通过开始-服务端选项的“配置和移植工具”-net manager来配置监听文件，并重启服务-homeListener
通过开始-服务端选项的“配置和移植工具”-net configuration assitant来加载监听器和本地Net服务连接。

本地>监听程序>配置监听程序：LISTENER

1.添加地址
2.主机：127.0.0.1
3.端口：1521
4.监听位置：数据库服务

1.选择数据库服务之后点击添加数据库
2.全局数据库名：为安装服务端时的第一个页面填写的全局数据库名，这里是：orcl
3.Oracle主目录：安装的Oracle数据库位置，选中db_1

文件>保存网络位置

重启Listenet

管理员运行客户端的Net Configuration Assistant



删除监听程序，直接下一步

之后重新配置一个监听程序，点击添加

先删除之前有的本地Net服务
直接下一步，删除完，直到除了添加按钮其他按钮全为灰色


服务名：orcl

选择TCP



之后进行测试输入口令：orcl

之后一直下一步到完成

1.4安装成功确认

输入口令：orcl
输入主机字符串：orcl

连接成功时输入：select sysdate from dual
能查到系统时间说明成功

1.5Oracle数据库的卸载

• 以上只是简单的将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

2.Oracle数据库管理

2.1plsqldev使用

使用plsqldev，优于sqlplus，直接安装就行
需要以管理员身份运行

上面设置的密码
Database：ORCL

输入：select sysdate from dual;
显示时间

导入表@盘符:/文件名;
例如依次输入
@d:/del_data.sql;
@d:/hr_cre.sql;
@d:/hr_popul.sql

导入之后select查询导入的数据条数是否正常，需要按照上方的顺序导入

2.2Oracle 企业管理器

管理员身份运行客户端的企业管理器

首次打开需要添加数据库信息，这里使用已经存在的数据库orcl

使用系统用户登录
用户名：system，口令orcl

或者不输入使用SYSDBA登录

可以查看所有用户的详细信息: 用户名, 账户状态, 失效日期……
新用户创建

输入名称和口令，其他默认
概要文件里有输入密码次数过多的锁定信息

使用新创建的用户登录

一个用户创建的表，另一个用户访问不到
在SCOTT中创建的表，ATGUIGU不能访问

对象：Oracle中的各个用户

找到ATGUIGU的对象里面的SCOTT用户

打开SCOTT用户下的表，添加seclet功能
查询时输入：select * from scott.employees；可以查询到

创建表，输入ed回车，弹出输入框
输入建表语句
输入/回车

打开创建表的权限
后面的管理选项×和√。×代表只能自己用，√代表权限可以赋给别人


分配存储空间之后，可以建表成功

可以获取其他用户的权限，比如建表和查询
赋予其他角色之后进行重启之后即可进行查询和建表

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

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

Oracle 数据库的默认概要文件

• 每个 Oracle 数据库都有一个默认的资源概要文件, 名为 DEFAULT
• 当创建一个新的数据库用户且不对用户分配一个特定的概要文件时, Oracle 自动给用户分配数据库的 DEFAULT 概要文件. 默认时,数据库 DEFAULT 概要文件的所有资源限制设置为无限制的.

进行登录账户错误次数过多时的解锁

模式(schema)

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

模式对象和非模式对象

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

用户的默认表空间

• 表空间是数据库的逻辑存储设备, 它把数据库信息组织成物理存储空间.
• 表空间由数据文件组成.用户的各种模式对象(如表, 索引, 过程, 触发器等) 都是放在表空间中.
• 对每个数据库用户, 都可以设置一个默认表空间. 当用户创建一个新的数据库对象(如表), 并且不明确地为此对象指定表空间时, Oracle 会把所创建的这个新数据库对象存放到用户默认的表空间中.
• 如果不给用户指定默认表空间, 则用户的默认表空间为 USERS 表空间.

用户的临时表空间

• 一般, SQL 语句在完成任务时需要临时工作空间. 例如：一个用来连接和排序大量的查询需要临时工作空间来存放结果. 除非另外指定, 一般情况下, 用户的临时表空间是 TEMP 表空间.
• 若数据库中没有创建 TEMP 表空间, 则用户的临时表空间为 SYSTEM 表空间.
• 因为 SYSTEM 表空间是用来保存数据库系统信息(数据库自身信息的内部系统表和视图 ---- 数据字典; 所有 PL/SQL 程序的源代码 ---- 包括函数, 触发器等)的. 如果用户大量使用此表空间存储自己的数据, 将会影响系统的执行效率. 因此一般不建议用户使用 SYSTEM 表空间

权限管理
为一个 Oracle 数据库系统创建用户之后, 这些用户既不能与数据库服务器连接, 也不能做任何事情, 除非他们具有执行特定数据库操作的权限.

Oracle 中的数据库访问权限类型共有两种:
系统权限: 一种功能很强的权限, 他向用户提供了执行某一种或某一类型的数据库操作的能力.
对象权限: 控制用户是否能在特定数据库对象(如表, 视图或存储过程) 上执行特定类型的操作.

使用系统权限

• 用户连接到数据库必须具备 create session 权限.
• 如果用户具有 create any procedure 系统权限, 则能够创建, 修改, 删除或执行任何存储过程, 程序包和函数
• 如果用户具有 create any table 系统权限, 则能够在自己的模式中创建, 修改, 删除或查询任何表
• 开发人员一般需要 create table, create view 和 create type 系统权限.
  
  
  利用角色进行权限管理
• 数据库应用程序所需要的系统权限和对象权限很多. 为了使 ”安全管理” 成为比较容易的工作, 可以利用角色
• 角色(role): 系统权限和对象权限的一个集合. 可以将角色授予 用户, 被授予角色的用户会自动拥有角色所具有的权限. 如果修改了角色所拥有的权限, 则被授予角色的用户的权限也会随之自动修改.

3.SELECT语句

3.1SQL初步

SQL语句分为以下三种类型：
DML: Data Manipulation Language 数据操纵语言
DDL: Data Definition Language 数据定义语言
DCL: Data Control Language 数据控制语言

DML用于查询与修改数据记录，包括如下SQL语句：
INSERT：添加数据到数据库中
UPDATE：修改数据库中的数据
DELETE：删除数据库中的数据
SELECT：选择（查询）数据，SELECT是SQL语言的基础，最为重要。

DDL用于定义数据库的结构，比如创建、修改或删除数据库对象，包括如下SQL语句：
CREATE TABLE：创建数据库表
ALTER TABLE：更改表结构、添加、删除、修改列长度
DROP TABLE：删除表
CREATE INDEX：在表上建立索引
DROP INDEX：删除索引

DCL用来控制数据库的访问，包括如下SQL语句：
GRANT：授予访问权限
REVOKE：撤销访问权限
COMMIT：提交事务处理
ROLLBACK：事务处理回退
SAVEPOINT：设置保存点
LOCK：对数据库的特定部分进行锁定

3.2SELECT语句

SELECT：标识选择哪些列。
FROM：标识从哪个表中选择。

注意
1.SQL 语言大小写不敏感。
2.SQL 可以写在一行或者多行
3.关键字不能被缩写也不能分行
4.各子句一般要分行写。
5.使用缩进提高语句的可读性。

数字和日期使用的算术运算符。

操作符	描述
+	加
-	减
*	乘
/	除

操作符优先级
乘除的优先级高于加减。
同一优先级运算符从左向右执行。
括号内的运算先执行。

空值
空值是无效的，未指定的，未知的或不可预知的值
空值不是空格或者0。
包含空值的数学表达式的值都为空值

列的别名
1.重命名一个列。
2.便于计算。
3.紧跟列名，也可以在列名和别名之间加入关键字‘AS’，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。

连接符
把列与列，列与字符连接在一起。
用 ‘||’表示。
可以用来‘合成’列。

字符串
1.字符串可以是 SELECT 列表中的一个字符,数字,日期。
2.日期和字符只能在单引号中出现。
3.每当返回一行时，字符串被输出一次。

在 SELECT 子句中使用关键字 ‘DISTINCT’ 删除重复行（去重）。

3.3SQL 和 SQL*Plus

SQL语句 与 SQL*Plus命令对比

使用SQL*Plus可以:
1.描述表结构。
2.编辑 SQL 语句。
3.执行 SQL语句。
4.将 SQL 保存在文件中并将SQL语句执行结果保存在文件中。
5.在保存的文件中执行语句。
6.将文本文件装入 SQL*Plus编辑窗口。

使用 DESCRIBE 命令，显示表结构。

5.过滤和排序数据

过滤
使用WHERE 子句，将不满足条件的行过滤掉。
WHERE 子句紧随 FROM 子句

字符和日期
字符和日期要包含在单引号中。
字符大小写敏感，日期格式敏感。
默认的日期格式是 DD-MON月-RR。

比较运算

操作符	含义
=	等于 (不是 ==)，也可以赋值
>	大于
>=	大于、等于
<	小于
<=	小于、等于
<>	不等于 (也可以是 !=)
BETWEEN…AND…	在两个值之间 (包含边界)
IN(set)	等于值列表中的一个
LIKE	模糊查询
IS (NOT) NULL	判断空值

LIKE
1.使用 LIKE 运算选择类似的值
2.选择条件可以包含字符或数字:
3.% 代表零个或多个字符(任意个字符)。
4._ 代表一个字符。
5.‘%’和‘-’可以同时使用
6.可以使用 ESCAPE 标识符 选择‘%’和 ‘_’ 符号。

逻辑运算

操作符	含义
AND	逻辑并，要求并的关系为真
OR	逻辑或，要求或关系为真
NOT	逻辑否

优先级
可以使用括号改变优先级顺序

优先级
1	算术运算符
2	连接符
3	比较符
4	IS [NOT] NULL, LIKE, [NOT] IN
5	[NOT] BETWEEN
6	NOT
7	AND
8	OR

ORDER BY子句排序
ASC（ascend）: 升序（默认），上小下大
DESC（descend）: 降序，上大下小
ORDER BY 子句在SELECT语句的结尾。

6.单行函数

单行函数
1.操作数据对象
2.接受参数返回一个结果
3.只对一行进行变换
4.每行返回一个结果
5.可以转换数据类型
6.可以嵌套
7.参数可以是一列或一个值

字符函数

大小写控制函数
这类函数改变字符的大小写。

函数	结果
LOWER(‘SQL Course’)	sql course
UPPER(‘SQL Course’)	SQL COURS
INITCAP(‘SQL Course’)	Sql Course

字符控制函数

作用	函数	结果
拼接	CONCAT(‘Hello’, ‘World’)	HelloWorld
截取	SUBSTR(‘HelloWorld’,1,5)	Hello
求长度	LENGTH(‘HelloWorld’)	10
求第几位	INSTR(‘HelloWorld’, ‘W’)	6
	LPAD(salary,10,‘*’)	*****24000
	RPAD(salary, 10, ‘*’)	24000*****
	TRIM(‘H’ FROM ‘HelloWorld’)	elloWorld
	REPLACE(‘abcd’,’b’,’m’)	amcd

数字函数

作用	函数	结果
四舍五入	ROUND(45.926, 2)	45.93
截断	TRUNC(45.926, 2)	45.92
求余	MOD(1600, 300)	100

DUAL 是一个‘伪表’，可以用来测试函数和表达式

日期函数

Oracle 中的日期型数据实际含有两个值: 日期和时间。
在日期上加上或减去一个数字结果仍为日期。
两个日期相减返回日期之间相差的天数。
日期不允许做加法运算，无意义
可以用数字除24来向日期中加上或减去天数

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

转换函数

隐式数据类型转换
Oracle 自动完成下列转换：

显式数据类型转换

通用函数

这些函数适用于任何数据类型，同时也适用于空值：
NVL (expr1, expr2)
NVL2 (expr1, expr2, expr3)
NULLIF (expr1, expr2)
COALESCE (expr1, expr2, …, exprn)

使用 COALESCE 函数
COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。
如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE 。

条件表达式

在 SQL 语句中使用IF-THEN-ELSE 逻辑
使用两种方法:
CASE 表达式
DECODE 函数

嵌套函数

7.多表查询

笛卡尔集会在下面条件下产生:
省略连接条件
连接条件无效
所有表中的所有行互相连接

为了避免笛卡尔集， 可以在 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;

区分重复的列名
使用表名前缀在多个表中区分相同的列。
在不同表中具有相同列名的列可以用表的别名加以区分。

连接多个表
连接 n个表,至少需要 n-1个连接条件。 例如：连接三个表，至少需要两个连接条件。

非等值连接

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;
--salary 在j.lowest_sal和j.highest_sal之间

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

8.分组函数

9.子查询

子查询语法

SELECT	select_list
FROM	table
WHERE	expr operator
		 	(SELECT	select_list FROM table);

子查询 (内查询) 在主查询之前一次执行完成。
子查询的结果被主查询(外查询)使用 。

SELECT last_name
FROM   employees
WHERE  
	salary >(SELECT salary FROM   employees WHERE  last_name = 'Abel');--11000
--实际上为salary >11000

注意事项
子查询要包含在括号内。
将子查询放在比较条件的右侧。
单行操作符对应单行子查询，多行操作符对应多行子查询。

子查询类型

单行子查询
只返回一行，使用单行比较操作符

--题目：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资
SELECT last_name, job_id, salary
FROM   employees
WHERE  job_id =  
                (SELECT job_id
                 FROM   employees
                 WHERE  employee_id = 141)--ST_CLERK

AND    salary >
                (SELECT salary
                 FROM   employees
                 WHERE  employee_id = 143);--2600

--题目：返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name, job_id, salary
FROM   employees
WHERE  salary = 
                (SELECT MIN(salary)
                 FROM   employees);--最小工资：2500

子查询中的 HAVING 子句
首先执行子查询。
向主查询中的HAVING 子句返回结果。

多行子查询
返回多行。
使用多行比较操作符

操作符	含义
IN	等于列表中的任意一个
ANY	和子查询返回的某一个值比较
ALL	和子查询返回的所有值比较

10.创建和管理表

11.数据处理

12.约束

13.视图

14.其它数据库对象

30.SET运算符及练习

各种SET 操作符

使用 SET 操作符注意事项

• 在SELECT 列表中的列名和表达式在数量和数据类型上要相对应
• 括号可以改变执行的顺序
• ORDER BY 子句：只能在语句的最后出现，可以使用第一个查询中的列名, 别名或相对位置
• 系统将第一个查询的列名显示在输出中
• 除 UNION ALL之外，系统会自动将重复的记录删除
• 除 UNION ALL之外，系统自动按照第一个查询中的第一个列的升序排列

SELECT employee_id, job_id
FROM   employees
UNION
SELECT employee_id, job_id
FROM   job_history;

SELECT employee_id, job_id, department_id
FROM   employees
UNION ALL
SELECT employee_id, job_id, department_id
FROM   job_history
ORDER BY  employee_id;

SELECT employee_id, job_id
FROM   employees
INTERSECT
SELECT employee_id, job_id
FROM   job_history;

SELECT employee_id,job_id
FROM   employees
MINUS
SELECT employee_id,job_id
FROM   job_history;