Oracle笔记

Oracle笔记

一.Oracle简介

• Oracle Database，简称Oracle。是甲骨文公司的一款关系数据库管理系统。到目前仍在数据库市场上占有主要份额。甲骨文公司是全球最大的企业级软件公司，最大的数据库供应商、企业应用平台供应商。
• ORACLE数据库系统
  • 是美国ORACLE公司（甲骨文）提供的以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一。
  • ORACLE数据库是目前世界上使用最为广泛的数据库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能；
  • 作为一个关系数据库，它是一个完备关系的产品；
  • 作为分布式数据库，它实现了分布式处理功能；
  • Oracle数据库相关知识在不同系统上无差异；

二.Oracle数据库版本

• 企业版
  • Oracle数据库11g企业版可以运行在Windows、Linux和Unix的集群服务器或单一服务器上，它提供了全面的功能来进行相关的事务处理、商务智能和内容管理，具有业界领先的性能、可伸缩性、安全性和可靠性。
• 标准版
  • Oracle数据库11g标准版功能全面。可适用于多达四个插槽的服务器。它通过应用集群服务实现了高可用性，提供了企业级性能和安全性，易于管理并可随需求的增长轻松扩展。标准版可向上兼容企业版，并随企业的发展而扩展，从而保护企业的初期投资。
• 标准版1
  • Oracle数据库11g标准版1功能全面，可适用于两个插糟的服务器。它提供了企业级性能和安全性，易于管理，并可随需求的增长轻松进行扩展。与标准版一样，标准版1可向上兼容其他数据库版本，并随企业的发展而扩展。从而使得企业能够以最低的成本获得朵高的性能，保护企业的初期投资。
• 个人版
  • 个人版数据库只提供Oracle作为DBMS的基本数据库管理服务，它适用于单用户开发环境，其对系统配置的要求也比较低，主要面向开发技术人员使用。

三.Oracle中的用户

用户	描述
sys用户	是超级管理员，具有最高权限，具有sysdba,有create database权限，默认密码change_on_install
system用户	是普通管理员，权限也很大，具有sysoper角色，没有create database权限。默认密码是manager
普通用户	用户名：scott; 密码：tiger(默认) 安装时内置好，默认锁定的。
大数据用户	用户名：sh，密码：sh

四.Oracle基本术语

1.数据字典

• 概念

​ 数据字典是Oracle数据库的重要组成部分。它由一系列拥有数据库元数据(metadata)信息的数据字典表和用户可以读取的数据字典视图组成，存放有Oracle数据库所用的有关信息。

• 存放Oracle数据库的主要信息内容
  • 系统的空间信息，即分配了多少空间，当前使用了多少空间等；
  • 数据库中所有模式对象的信息，如表、视图、簇、同义词及索引等；
  • 例程运行的性能和统计信息；
  • Oracle用户的名字；
  • 用户访问或使用的审计信息；
  • 用户及角色被授予的权限信息；
  • 列的约束信息的完整性；
  • 列的缺省值等

2.数据文件

• 概念

​ 一个Oracle数据库可以拥有一个或多个物理的数据文件。数据文件包含了全部数据库数据。逻辑数据库结构的数据也物理的存储在数据库的数据文件中。

• 数据文件特征
  • 一个数据库可拥有多个数据文件，但一个数据文件只对应一个数据库；
  • 可以对数据文件进行设置，使其在数据库空间用完的情况进行自动扩展；
  • 一个表空间(数据库存储的逻辑单位)可以由多个数据文件组成；
• 数据文件大小表示
  • 数据文件的大小可以 用两种方式表示，即字节和数据块。数据块是Oracle数据库中最小的数据组织单位，它的大小由参数“DB_BLOCK_SIZE”来确定。

3.控制文件

• 概念

  ​ 数据库控制文件是一个很小的二进制文件，它维护者数据库的全局结构，用以支持数据库成功地启动和运行。创建数据库时，同时就提供了与之对应的控制文件。在数据库使用过程中，Oracle不断更新控制文件，所以只要数据库是打开的，控制文件就必须处于可写状态。若由于某些原因控制文件不能被访问，则数据库也就不能正常工作。

• 每一个数据库均有一个控制文件，记录着数据库的物理结构，主要包含：

  • 数据库名称；
  • 数据库数据文件和日志文件的名字和位置；
  • 数据库建立日期；
  • 日志历史；
  • 归档日志信息；
  • 表空间信息；
  • 数据文件脱机范围等等

4.日志文件

• 概念
  • 日志文件也称为重做日志文件，重做日志文件用于记录对数据库的所有修改信息，修改信息包括用户对数据的修改以及管理员对数据库结构的修改。重做日志文件是保证数据库安全和数据备份与恢复的文件。
  • 重做日志文件主要在数据库出现故障时使用。
• Oracle日志模式：
  • 归档模式(ARCHIVELOG);
  • 非归档模式(NOARCHIVELOG);

五.Oracle安装部署

1.Oracle数据库软件安装准备

• Oracle服务端

  • 在Oracle数据库服务器上安装运行，方便客户端访问
  • 属于重量级应用，服务器配置好坏直接影响到Oracle数据库的性能

• Oracle客户端

  • 在需要连接Oracle数据库服务的客户机上安装运行，辅助客户机连接本地或远程Oracle数据库
  • 属于轻量级应用，对客户机器需求不高

• Windows环境下Oracle安装部署(一般指安装服务端)

  Windows系统采用图形化界面安装数据库，首先将安装包放置在磁盘空间较大的分区。(安装包下载地址：Software Download | Oracle)

  具体安装部署教程见 (106条消息) oracle的安装和配置_斌斌Running的博客-优快云博客_oracle怎么配置

2.Oracle数据库必要配置

主要有：

​ 优化最大连接数；

​ 会话数；

​ 数据库密码有效期更改；

​ SGA、PGA优化；

​ 关闭审计功能；

​ 关闭监听日志功能

3. PL/SQL Developer数据库开发工具

• PL/SQL Developer工具介绍

  PL/SQL Developer是一个集成开发环境，专门面向Oracle数据库存储程序单元的开发工具

• PL/SQL Developer 常用功能

  • PL/SQL编辑器：该编辑器具有语法加强、SQL和PL/SQL帮助、对象描述、代码助手、编译器提示、PL/SQL完善、代码内容、代码分级、浏览器按钮、超链接导航、宏库等许多智能特性，能够满足要求性最高的用户需求。当您需要某个信息时，它将自动出现，至多单击即可将信息调出；
  • 集成调试器：该调试器提供您所需要的全部特性：跳入（Step In）、跳过（Step Over）、跳出（Step Out）、异常时停止运行、断点、观察和设置变量、观察全部堆栈等。基本能够调试任何程序单元（包括触发器和对象类型），无需作出任何修改；
  • SQL窗口：该窗口允许您输入任何SQL语句，并以栅格形式对结果进行观察和编辑，支持按范例查询模式，以便在某个结果集合中查找特定记录。另外，还含有历史缓存，您可以轻松调用先前执行过的SQL语句。该SQL编辑器提供了同PL/SQL编辑器相同的强大特性；
  • 命令窗口：使用PL/SQL Developer 的命令窗口能够开发并运行SQL脚本。该窗口具有同SQLPlus相同的感观，另外还增加了一个内置的带语法加强特性的脚本编辑器。这样，您就可以开发自己的脚本，无需编辑脚本/保存脚本/转换为SQLPlus/运行脚本过程，也不用离开PL/SQL Developer集成开发环境；
  • 对象浏览器：可配置的树形浏览能够显示同PL/SQL开发相关的全部信息，使用该浏览器可以获取对象描述、浏览对象定义、创建测试脚本以便调试、使能或禁止触发器或约束条件、重新编译不合法对象、查询或编辑表格、浏览数据、在对象源中进行文本查找、拖放对象名到编辑器等。
  • 导出用户对象：该工具可以导出用户所选对象的DDL（数据定义语言）语句。您可以方便的为其他用户重新创建对象，也可以保存文件作为备份；
  • 工具：PL/SQL Developer为简化日常开发专门提供了几种工具。使用这些工具，您可以重新编译全部不合法对象、查找数据库源中文本、导入或导出表格、生成测试数据、导出文本文件、监控dbms_alert和dbms_pipe事件、浏览会话信息等；

• PL/SQL Developer安装及简单配置

  详细安装教程：(106条消息) PL/SQL Developer下载安装及使用_小咪喵了个咪的博客-优快云博客_plsql下载

六. Oracle数据库基础SQL

1.SQL概述

• 什么是SQL：SQL全称结构化查询语言(Structured
  Query Language)，是数据库操作的国际标准语言，也是所有的数据库产品均要支持的语言
• SQL的作用：数据库连接客户端使用SQL来操作数据库
• SQL标准(例如SQL99，即1999年制定的标准)
  • 由国际标准化组织(ISO)制定的，对DBMS的统一操作方式(例如相同的语句可以操作：mysql、oracle等)。
• SQL方言

​ 某种DBMS不仅会支持SQL标准，而且还会有一些自己独有的语法，这就称之为方言（例如limit语句只在mysql中使用）

• SQL语言按功能分类

  数据定义语言、数据操纵语言、数据查询语言、数据控制语言、事务控制语言（本教学不讲解，可查找资料自学）

• SQL语言规则

  • SQL关键字不区分大小写，建议大写；
  • 对象名和列名也不区分大小写，建议大写；
  • 字符值和日期值区分大小写；
  • SQL语句可以在单行或多行书写，以分号结尾；
  • 可使用空格和缩进来增强语句的可读性；

2.数据定义语言(DDL)

2.1 基于Oracle数据库的数据定义语言(DDL)

概念：SQL的数据定义语言定义功能通过数据定义语言(Data
Definition Language, DDL)实现，用来定义数据库的逻辑结构，包括定义基表、视图和索引。基本的DDL主要包括三类，即定义、修改和删除，分别对应CREATE\ALTER和DROP三条语句。

在标准SQL语句中，DDL主要用于基表、视图和索引的定义，在Oracle数据库中，还有各种其他数据库对象，如空表空间、触发器、存储过程、游标、序列等

本节重点讲解Oracle数据库部分基础数据库对象的DDL

2.2 Oracle数据库表空间（TABLESPACES）

• 概念

  Oracle表空间(TABLESPACES)是Oracle数据库的逻辑划分，每个数据库至少有一个表空间(称作SYSTEM表空间)。一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫做数据文件，一个数据文件只能属于一个表空间，数据文件的大小和位置可以自己定义。

• 特性

  • 控制数据库数据磁盘分配
  • 限制用户在表空间中可以使用的磁盘空间大小
  • 表空间具有online，offline，readonly，readwrite属性

• 表空间的分类

  • 永久表空间：数据库中要永久化存储的一些对象，如：表、视图、存储过程
  • 临时表空间：数据库操作当中中间执行的过程，执行结束后，存放的内容会被自动释放
  • UNDD表空间：用于保存事务所修改数据的旧值，可以进行数据的回滚

• 通过Oracle数据字典查询表空间

  • select * from dba_tablespaces; --系统管理员级别查看的数据字典
  • select * from user_tablespaces; --普通用户查看的数据字典

• 查询Oracle表空间对应数据文件信息

  • select * from dba_data_files; --系统管理员查询数据文件信息
  • select * from dba_temp_files; --系统管理员查询临时数据文件信息

• 创建Oracle数据库表空间一般语法

  CREATE TABLESPACE tablespace_name
  DATAFILE filename 
  SIZE size 
  REUSE
  [AUTOEXTEND [ON/OFF]] 
  NEXT size 
  [MAXSIZE size]
  [PERMANENT|TEMPORARY]
  [EXTENT MANAGEMENT [DICTIONARY|LOCAL [AUTOALLOCATE|UNIFORM [SIZE integer[K|M]] ] ] ]
  

  语法解析：

  • TABLESPACE：指定要创建的表空间名称。
  • DATAFILE:指定在表空间中存放数据文件的文件名，包含文件的存放路径。
  • SIZE:指定数据文件大小
  • REUSE：表示数据文件支持被重用。
  • AUTOEXTEND:指定数据文件扩展方式，ON代表自动扩展，OFF代表非自动扩展。另外，如果要把数据文件指定为自动扩展，应该在NEXT后面指定具体大小。
  • MAXSIZE：指定数据文件为自动扩展方式的最大值
  • PERMANENT|TEMPORARY：指定表空间类型， PERMANENT是指永久表空间；TEMPORARY指临时表空间，在创建表空间时默认都是永久表空间。
  • EXTENT MANAGEMENT DICTIONARY|LOCAL：指定表空间的管理方式，DICTIONARY是字典管理方式；LOCAL是指本地管理方式，在创建表空间时默认是本地管理方式（Oracle推荐）。
  • AUTOALLOCATE|UNIFORM：AUTOALLOCATE说明表空间自动分配范围，用户不能指定范围的大小； UNIFORM说明表空间的范围的固定大小，缺省是1M。

  ​ Oracle数据库表空间创建示例

  CREATE TABLESPACE TSP_KYEECIS
      DATAFILE 'D:\APP\PDA\ORADATA\ORCL\TSP_KYEECIS.DBF'
      SIZE 16M
      REUSE
      AUTOEXTEND ON
      NEXT 4M
      MAXSIZE UNLIMITED;
  
  

2.3 Oracle数据库用户(USER)

• Oracle数据库用户

  Oracle数据库建好后，要想在数据库里建表，必须为数据库建立用户，并为用户指定表空间

• 创建Oracle数据库用户

  CREATE USER           c##用户名  
  IDENTIFIED BY         密码  
  DEFAULT TABLESPACE    默认表空间(默认USERS)
  TEMPORARY TABLESPACE  临时表空间(默认TEMP) 
  

• 创建Oracle数据库用户实例

  CREATE USER c##KYEECIS
  IDENTIFIED BY kyee_password
  DEFAULT TABLESPACE TSP_KYEECIS
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT;
  grant connect,resource,dba to KYEECIS;   --授权
  

2.4 Oracle数据库表(TABLE)

• 创建表的基本语法

  CREATE TABLE table_name (
      column_name data_type column_constraint,
      column_name data_type column_constraint,
      ...
      table_constraint
   );
  

  • table_name: 在数据库中创建的数据表名称，在一个数据库表中表名不能重复；
  • column_name:表中的列名，列名在一个表中也不能重复；
  • data_type: 数据表的列对应的数据类型声明；
  • column_constraint:列的一些约束及默认值设置等；
  • table_constraint: 为表设置约束，约束主要包括主键约束，外键约束，检查约束等。

• 创建表示例：创建用户与科室关系表，主键定义、外键定义等

  create table SYS_USER_VS_DEPT 
  (
     USER_DEPT_ID         VARCHAR2(50)         not null,
     USER_ID              VARCHAR2(50),
     DEPT_ID              VARCHAR2(50),
     CREATOR              VARCHAR2(20),
     CREATE_DATE          DATE,
     constraint PK_SYS_USER_VS_DEPT primary key (USER_DEPT_ID)
  );
  comment on table SYS_USER_VS_DEPT is '用户科室关系表';
  comment on column SYS_USER_VS_DEPT.USER_DEPT_ID is '用户科室关系主键';
  comment on column SYS_USER_VS_DEPT.USER_ID is '用户主键';
  comment on column SYS_USER_VS_DEPT.DEPT_ID is '科室主键';
  comment on column SYS_USER_VS_DEPT.CREATOR is '创建人';
  comment on column SYS_USER_VS_DEPT.CREATE_DATE is '创建时间';
  --外键约束声明
  alter table SYS_USER_VS_DEPT
     add constraint FK_SYS_USER_REF_SYS_USER_DEPT foreign key (USER_ID) references SYS_USERS (USER_ID);
  alter table SYS_USER_VS_DEPT
     add constraint FK_SYS_USER_REFERENCE_SYS_DEPT foreign key (DEPT_ID) references SYS_DEPT (DEPT_ID);
  
  

2.5 Oracle数据库视图(VIEW)

• 视图概念

  • 是基于一个表或者多个表或视图的逻辑表，本身不包含数据，通过它可以对表里面的数据进行查询和修改。视图基于的表称为基表。
  • 视图是存储在数据字典里面的一条select语句，通过创建视图可以提取数据的逻辑上的集合或组合。

• 视图的优点

  • 对数据库的访问，因为视图可以有选择性的选取数据库里的一部分；
  • 用户通过简单的查询可以从复杂查询中得到结果；
  • 维护数据的独立性，视图可从多个表检索数据；
  • 对于相同的数据可产生不同的视图

• 创建视图语法

  CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name
      [(alias[, alias]...)]
  AS subquery
       [WITH CHECK OPTION [CONSTRAINT constraint]]
       [