Oracle基础知识和常用操作（修订版）

Oracle基础知识和常用操作

oracle数据库和oracle实例

☆  Oracle数据库：位于硬盘上实际存放数据的文件，这些文件组织在一起，成为一个逻辑的整体，即为Oracle数据库，因此可以说，数据库就是存储在磁盘上的数据集合。表现为一组文件。它必须要与内存里实例合作，才能对外提供数据管理服务。

☆  oracle实例：位于物理内存里的数据结构，它由一个共享的内存池和多个后台进程所组成，共享的内存池可以被所有进程访问，用户如果要存取数据库里的数据，必有通过实例才能实现，不能直接读取硬盘上的文件。

☆  区别：实例（instance）可以操作数据库，在任何时刻一个实例只能与一个数据库关联，一个数据库可以有多个实例。多数据情况下，一个数据库上只能有一个实例对其进行操作。在Oracle 12c之前的版本中，实例与数据库是一对一或多对一关系：即一个实例只能与一个数据库相关联，或者多个实例加载到一个数据库中，实例与数据库不能是一对多的关系，对于Oracle 12c, 实例与数据库可以是一对多的关系。

表空间（tablespace）和数据文件（data file）

☆  表空间是由多个数据文件组成，数据文件只能属于一个表空间

☆  表空间为逻辑概念，数据文件为物理概念

【关于Oracle 表空间（tablespace）更多知识，可参见 https://mp.csdn.net/mp_blog/creation/editor/125151902 】

段（segment）、区（extent）、块（block）

☆  段存在于表空间中

☆  段是区的集合，由一个或多个数据区（extent）构成。当一个段中已有空间已经用完，Oracle为这个段分配新的数据扩展。

☆  区是数据块的集合，比数据块更高一层的数据库逻辑存储结构。

☆  数据块会被映射到磁盘块中，Oracle对数据库数据文件（data file）中的存储空间进行管理的单位就是数据块。

【 数据库是由一个或多个被称为表空间（table space）的逻辑存储单位构成。表空间内的逻辑存储单位为段（segment），段又可以继续划分为数据区（extent，数据扩展）。而数据区是由一组连续的数据块（data block）构成。

每一个Oracle数据库都包含名为 SYSTEM和SYSAUX 的表空间，她们在数据库创建时由Oracle自动创建。只要数据库处于开启（open）状态，SYSTEM 表空间就一定是联机（online）的；SYSAUX 表空间（table space）是 SYSTEM 表空间的一个辅助性表空间。Oracle中很多组件使用 SYSAUX 表空间作为默认的数据存储位置。

表空间可以处于联机状态（可访问）或脱机状态（不可访问）。表空间通常处于联机状态，用户可以访问其中的信息。有时可以把某个表空间切换到脱机状态，使与脱机表空间对应的数据库部分失效，而数据库其余部分仍可以正常工作。这个功能使许多管理任务更容易执行。

数据文件（data file）是Oracle数据库用来存储实际数据的，所以数据文件是存储数据的物理概念，一个Oracle数据库可以拥有一个或多个物理的数据文件，数据文件包含了全部Oracle数据库数据，逻辑数据库结构的数据也存储在数据文件中。

Oracle数据库中每个表空间都是由一个或多个物理数据文件构成的。一个数据文件只能由一个数据库的一个表空间使用。

Oracle为表空间（table space）创建数据文件时，分配的磁盘空间总和为用户指定的存储容量加管理开销所需的文件头空间。当数据文件被创建后，Oracle所在的操作系统负责清除文件的数据及授权信息，并将她分配给Oracle使用。如果文件很大，这个过程将会消耗较长时间。Oracle数据库中的第一个表空间总是 SYSTEM 表空间，因此Oracle在创建数据库时总是将第一个数据文件分配给 SYSTEM 表空间。

数据库控制文件（control file）是一个二进制文件，供数据库启动及正常工作时使用。在数据库运行过程中，控制文件会频繁地被Oracle修改，因此数据库处于开启状态时控制文件必须可写。如果控制文件因故不能访问，数据库也将无法正常工作。 每个控制文件只能供一个Oracle数据库使用。 】

oracle实例、服务名、SID等概念可参见：
https://blog.csdn.net/cnds123/article/details/103904794

2013年7月，Oracle公司发布新一代数据库Oracle 12c，其中的“c”表示这是一款针对云计算(Cloud)而设计的数据库。oracle12c增加了CDB和PDB的概念。CDB全称为Container Database（数据库容器）； PDB全称为Pluggable Database（可插拔数据库）。

Oracle数据库创建完毕后，设置了一些数据库的默认用户。其中，常用的有，为管理员预置了两个用户分别是SYS和SYSTEM。同时Oracle为程序测试提供了一个普通用户scott，口令管理中，可以对数据库用户设置密码，设置是否锁定。 Oracle客户端使用用户名和密码登录Oracle系统后才能对数据库操作。默认的用户中，SYS和SYSTEM用户是没有锁定的，安装成功后可以直接使用，SCOTT用户默认为锁定状态，因此不能直接使用，需要把SCOTT用户设定为非锁定状态才能正常使用。

Oracle系统默认安装下，自带的一个客户端工具。在Windows命令行中输入“sqlplusw”命令，就能够启动该工具了，输入：sqlplus输入密码后就可以直接使用了。

Oracle数据库默认的的端口号是：1521

一个Oracle数据库服务器有一个数据库和至少一个数据库实例组成，由于数据库和实例是紧密相连的，所以很多时候就使用Oracle数据库来表示实例和数据库，但在严格意义上的Oracle数据库包含如下两方面的内容：

(1) 数据库：数据库是磁盘上存储数据的一组文件的集合，这些文件可以独立于数据库实例存在，Oracle的数据库是基于多租户架构的。

(2) 数据库实例：数据库实例是管理数据库文件的内存结构的集合，一个数据库实例有一个称为系统全局区（SGA）的共享内存区和一组后台进程组成。

多租户架构（multitenant architecture）使得Oracle数据库可以作为一个包含0个、1个或者多个用户创建的可插拔式数据库（Pluggable Database, PDB）的多租户容器数据库（Container Database, CDB）。Oracle Database 12c之前的数据库都是非CDB。

Oracle的大小写

Oracle的关键字时是不需要区分大小写的,比如创建表的CREATE或create是等价的。

默认表名、字段名等不区分大小写，小写会自动转换为大写；若表名、列名使用英语引号包起来，则是区分大小写的!

Oracle对字符串中的内容是大小写敏感的。

具体可参见https://blog.csdn.net/u011479200/article/details/89025708

SQL语言

SQL语言已经成为关系数据库的标准语言，是关系数据库的基础，oracle，SQL Server和DB2都使用SQL。很多数据库产品都对SQL语句进行再开发与扩展，如oracle提供的PL/SQL（Procedure Language and SQL）就是对SQL的一种扩展。

SQL语言的分类

★数据定义语言DDL（Data Definition Language）

CREATE、DROP、ALTER

主要为以上操作 即对逻辑结构等有操作的，其中包括表结构，视图和索引。

★数据操纵语言DML（Data Manipulation Language）

INSERT、UPDATE、DELETE

主要为以上操作 即对数据进行操作的，对应上面所说的查询操作 DQL与DML共同构建了多数初级程序员常用的增删改查操作。而查询是较为特殊的一种 被划分到DQL中。

★数据查询语言DQL（Data Query Language）

SELECT

这个较为好理解 即查询操作，以select关键字。各种简单查询，连接查询等 都属于DQL。

★数据控制功能DCL（Data Control Language）

GRANT、REVOKE、COMMIT、ROLLBACK

关系数据库使用表（即关系）来表示实体及其联系。

表是数据项的集合。由列和行组成。

Oracle数据库数据类型可参见 https://blog.csdn.net/cnds123/article/details/114684695

数据定义语言DDL

使用CREATE TABLE语句创建表，语法如下

CREATE  TABLE  [<模式名>.] <基本表名>

  ( <列名 ><数据类型> [列级完整性约束条件]                  

  [,<列名 ><数据类型> [列级完整性约束条件]]

               ……

  [,表级完整性约束条件]）；

说明

<模式名>：模式（schema方案）是指该表所属的用户，可以省略，如果省略则默认为当前登录的用户。若具有CREATE ANY TABLE的权限，则可以在任何模式下创建表，若创建其它模式下的表，则表名前必须以模式名为前缀。简单地说，模式是一个用户下所拥有数据库对象的逻辑集合，每个用户都有一个对应的模式。创建用户时，会同时生成一个与用户同名的模式，此模式归同名用户所有。实际使用时，模式与用户完全一样，没有什么区别，在出现模式名的地方也可以出现用户名。从用户方面来看，Oracle的一个用户就是一个模式。所有的表都属于不同的用户，一个用户要访问另一个用户的表，需要有授权。

【模式概念

是指一系列逻辑数据结构或对象的集合。

模式与用户的关系

模式与用户相对应，一个模式只能被一个数据库用户所拥有，并且模式的名称与这个用户的名称相同。

通常情况下，用户所创建数据库对象都保存在与自己同名的模式中。

同一模式中数据库对象的名称必须惟一，而在不同模式中的数据库对象可以同名。

默认情况下，用户引用的对象是与自己同名模式中的对象，如果要引用其他模式中的对象，需要在该对象名之前指明对象所属模式。】

表名或列名中若包含空格，需要要加英文双引号括起来。一个表中列名要唯一。如果完整性约束条件涉及到该表的多个属性列时，必须在表级定义该约束条件，否则既可以定义在列级，也可以定义在表级。

主键约束（PRIMARY KEY）：体现了实体完整性。要求某一列的值既不能为空，也不能重复。

外键约束（FOREIGN KEY）：体现参照完整性。外键的取值或者为空或者参考父表的主键。

属性约束：体现了用户定义的完整性。属性约束主要限制某一属性的取值范围。分为以下几类：

非空约束(NOT NULL)：要求某一属性的值不允许为空值。

唯一约束（UNIQUE）：要求某一属性的值不允许重复。

检查约束（CHECK）：CHECK约束可以对某一个属性列的值加以限制。

如建STUDENT表：

CREATE  TABLE  STUDENT

( Sno  CHAR(8)  PRIMARY  KEY,          /*主键约束*/

Sname  VARCHAR2(20)  UNIQUE ,      /*唯一约束*/

Sex  CHAR(4)  NOT NULL,                    /*非空约束*/

Age  INT  CHECK(Age>16),                   /*检查约束*/

Dept  VARCHAR2(15));

建立COURSE表：
CREATE  TABLE  COURSE

(Cno  CHAR(8)  PRIMARY KEY,          /*主键约束*/

Cname  VARCHAR2(10),

Tname  VARCHAR2(10),

Cpno  CHAR(8)  REFERENCES Course(Cno),   /*外键约束*/

Credit  NUMBER);

建立CourseSelection表：

CREATE  TABLE  CourseSelection

(Sno  CHAR(8),

Cno  CHAR(8),

Grade  NUMBER,

PRIMARY  KEY(Sno,Cno),                           /*主键约束*/

FOREIGN  KEY(Sno)  REFERENCES  Student(Sno),     /*外键约束*/

FOREIGN  KEY (Cno)  REFERENCES  Course(Cno) );   /*外键约束*/

)

修改基本表结构语法：

ALTER TABLE <基本表名>

  [ADD <新列名> <数据类型> [列级完整性约束] ]

  [DROP COLUMN <列名>]

  [MODIFY <列名> <新的数据类型>]

  [ADD CONSTRAINT <完整性约束>]

  [DROP CONSTRAINT <完整性约束>];

表的删除语法:

DROP TABLE <表名>  [CASCADE CONSTRAINTS];

删除基本表时要注意以下几点:

①表一旦被删除,则无法恢复。

②如果表中有数据，则表的结构连同数据一起删除。

③在表上的索引、约束条件、触发器，以及表上的权限也一起被删除。

④当删除表时，涉及该表的视图、存储过程、函数、包被设置为无效。

⑤只有表的创建者或者拥有DROP ANY TABLE权限的用户才能删除表。

⑥如果两张表之间有主外键约束条件，则必须先删除子表，然后再删除主表。

⑦如果加上CASCADE CONSTRAINTS，在删除基本表的同时，相关的依赖对象也一起被删除。

数据操纵语言DML

当基本表建立以后，就可以使用INSERT语句向表中插入数据了。

INSERT语句有两种插入形式：

(1) 插入单行(行也称为元组或记录）语法格式如下：

INSERT INTO <基本表名> [(<列名1>，<列名2>，…，<列名n>)]VALUES（<列值1>，<列值2>，…，<列值n>）

注意：

1）向表中插入数据之前，表的结构必须已经创建。

2）插入的数据及列名之间用逗号分开。

3）在INSERT语句中列名是可以选择指定的，如果没有指定列名，则表示这些列按表中或视图中列的顺序和个数。

4）插入值的数据类型、个数、前后顺序必须与表中属性列的数据类型、个数、前后顺序匹配。

(2) 插入多行（插入子查询结果:select）语法格式如下：

INSERT INTO <基本表名> [(<列名1>，<列名2>， …，<列名n>)] 子查询;

UPDATE语句修改数据

一般语法格式为：

UPDATE <基本表名>

SET <列名1> = <表达式> [，<列名2> = <表达式>]…

[WHERE <条件>] ;

其中，UPDATE关键字用于定位修改哪一张表，SET关键字用于定位修改这张表中的哪些属性列，WHERE<条件>用于定位修改这些属性列当中的哪些行。

DELETE语句删除数据

一般语法格式为：

DELETE FROM <表名> [WHERE <条件>] ;

注意：

DELETE语句的功能是从指定表中删除满足WHERE<条件>的所有元组。如果省略WHERE<条件>，表示删除表中全部的元组信息。

DELETE语句只删除表中的数据，而不能删除表的结构，所以表的定义仍然在数据字典中。

数据查询语言DQL

SQL语言中最重要、最核心的操作就是数据查询。SQL语言提供SELECT语句进行数据库的查询，该语句具有灵活的使用方式和丰富的功能。

SELECT 语句基本语法格式为：

SELECT [ALL|DISTINCT] <目标列表达式>[，<目标列表达式>]…

FROM <表名或视图名>[，<表名或视图名>]…

[ WHERE<条件表达式> ]

[ GROUP BY<列名1> [ HAVING <组条件表达式> ] ]

[ ORDER BY<列名2> [ ASC|DESC ] ];

其中：

①SELECT子句说明要查询的数据。ALL表示筛选出数据库表中满足条件的所有记录，一般情况下省略不写。DISTINCT表示输出结果中无重复记录。

②FROM子句说明要查询的数据来源。。

③WHERE子句指定查询条件。

④GROUP BY子句表示在查询时，可以按照某个或某些字段分组汇总。HAVING子句必须跟随GROUP BY一起使用，表示在分组汇总时，可以根据组条件表达式筛选出满足条件的组记录。

⑤ORDER BY子句表示在显示结果时，按照指定字段进行排序。ASC表示升序，DESC表示降序，省略不写默认情况下是ASC。

什么是PL/SQL?

☆ PL/SQL是oracle对sql语言的过程化扩展，其中 PL：Process Language

☆ 在SQL语言中添加了过程处理语句（如分支，循环），使SQL语言具有过程处理能力

Oracle PL/SQL语句，需要分号结束；存储过程结束用斜杠/。

Oracle PL/SQL代码的注释可分为2种：

    单行注释以"--"开始。

    多行注释以“/*”开始，以“*/”结束。

PL/SQL程序结构

待续