Oracle数据库

Oracle数据库是什么

一款关系数据库管理系统，一种高效率、可靠性好的、适合搞吞吐量的数据库解决方案。Oracle数据库服务器由一个数据库和至少一个数据库实例组成。数据库是一组存储数据的文件，而数据库实例则是管理数据库文件的内存结构。此外，数据库由后台进程组成。

Oracle数据库服务器体系结构

Oracle的物理结构和逻辑结构

1、物理存储结构
物理存储结构是存储数据的纯文件。当执行一个create database 来创建一个新的数据库，将创建下列文件：
1）数据文件：数据文件包含真实数据，逻辑数据库结构（如表和索引）的数据被存储在数据文件中。
2）控制文件：每个Oracle数据库的都有一个包含元数据的控制文件。元数据用来描述包括数据库名称和数据文件位置的数据库物理结构。
3）联机重做日志文件：每个Oracle数据库都要有一个联机重做日志文件，包含两个或多个联机重做日志文件。它由重做条目组成，能够记录下所有对数据所做的更改。

2、逻辑存储结构
Oracle使用逻辑存储结构对磁盘空间使用情况进行精细控制。
1）数据块（data blocks）：将数据存储在数据块中。数据块也被称为逻辑块，oralce块或页，对应于磁盘上的字节数。
2）范围（extents）：用于存储特定类型信息的逻辑连续数据块的具体数量。
3）段（segments）：分配于存储用户对象（如表和索引）的一组范围。
4）表空间（tables spaces）：数据库被分成表空间的逻辑存储单元。表空间是段的逻辑容器。每个表至少包含一个数据文件。


Oracle实例
实例是客户端程序（用户）和数据库之间的接口。Oracle实例由三个主要部分组成：系统全局区（SGA），程序全局区（PGA）和后台进程。

SGA是实例启动时分配的共享内存结构，关闭时释放。SGA是一组包含一个数据库实例的数据和控制信息的共享内存结构。
不同于所有进程可用的SGA，PGA是会话开始时为每个会话分配的私有内存区当会话结束时释放。

主要的Oracle数据库的后台进程：
1）PMON是Oracle数据库中最活跃的一个进程，是调节所有其他进程的进程监视器。PMON能够清理异常连接的数据库连接，并自动向侦听器进程注册数据库实例。
2）SMON是执行系统级清理操作的系统监视进程。有两个主要职责，包括发生故障的情况下自动恢复实例，如断电和清理临时文件。
3）DBWn是数据库编写器。Oracle在内存中执行每个操作而不是磁盘。因为在内存中处理速度比磁盘快。DBWn进程从磁盘中读取数据并将其协会到磁盘。
4）CKPT：检查点进程。在Oracle中，磁盘上的数据称为块，内存中的数据成为缓冲区。当该块写入缓冲区并更改时，缓冲区变脏，需要将其写入磁盘。CKPT进程使用检查点更新控制和数据文件头，并向脏盘写入脏缓冲区的信号。

5）LGWR时日志写入过程，可恢复架构的关键。在数据库中发生的每一个变化都被写入到一个名为redo日志文件中用于恢复目的。LGWR进程首先将更改写入内存，然后将磁盘写入重做日志，然后将其用于恢复。
6）ARCn是归档进程，将重做日志的内容复制到归档重做日志，存档进程可以有多个进程，允许存档程序写入多个目标。
7）MMON收集性能指标的可管理性监控流程。
8）LREG监听器注册过程，使用Oracle Net Listener 在数据库实例和调度程序进程上注册信息。
9）MIMAN自动管理Oracle数据库内存的内存管理器。

Order By 用法讲解

1）按行排序示例

select name，address，credit_limit from customers  order by name ASC；

2）按多个行进行排序，可以用逗号order by 子句中的每列。

select first_name,last_name from contacts order by first_name,last_name DESC;

3）按列位置排序行示例

select  name,credit_limit,address from customers order by 2 DESC,1;

name列的位置是1， credit_limit列的位置是2

4）使用NULL值排序行的示例
当使用非NULL值对混合NULL进行排序时，Oracle允许指定哪个应该首先出现。

select  country_id,city,state from locations order by state ASC NULLS FIRST;(NULL值放在前面)
select  country_id,city,state from locations order by state ASC NULLS LAST;(NULL值放在后面)

5）按函数或表达式排序数据

使用order by 子句中的UPPER()函数区分客户名称的大小写

select customer_id,name from customers order by UPPER(name);

Oracle distinct 用法讲解

1、在一列上应用的例子

select distinct first_name from  order by first_name;

2、distinct 应用多列示例

select distinct product_id,quatity from order_items order by product_id;
product_id 和 quatity 列的值都用于评估结果集中行的唯一性。

3、diatinct和NULL

distinct 将NULL值视为重复值，如果使用select distinct 从具有多个NULL值的列中查询数据，结果集只包含一个NULL。
select distinct state from locations order by state NULLS FIRST;

Fetch 用法讲解

用来限制查询返回的行数

select  product_name,quantity from inventories inner join products using(product_id) order by quantity DESC FETCH NEXT 5 ROWS ONLY;

上面查询仅能在Oracle 12c 以上的版本中运行

Like 运算符用法

% 匹配零个多个字符的任何字符串；_ 匹配任何单个字符。
要执行不区分大小写的匹配，可以使用LOWER()或UPPER()函数

select first_name,last_name,email from contacts where UPPER(first_name) LIKE 'CH%' order by first_name;
查找名字以CH开头的联系人的电子邮件

Oracle事务

1、什么是事务

事务是在数据库中工作的逻辑单元，单个事务是由一个或多个完成一组的相关行为的SQL语句组成，通过事务机制可以确保这一组SQL语句所作的操作要么都成功执行，完成整个工作单元操作，要么一个也不执行。

2、事务特性（ACID特性）

1）原子性（Atomicity）：一个事务里所有包含的SQL语句都是一个整体，是不可分割的，要么做，要么不做。
2）一致性（Consistency）：事务开始，数据库中的数据是一致的，事务结束时，数据库的数据也应该是一致的。
3）隔离性（Isolation）：数据库允许多个并发事务同时对其中的数据1进行读写和修改的能力，隔离性可以防止事务在并发执行时，由于他们的操作命令交叉执行而导致的数据不一致状态。
4）持久性（Durability）：当事务结束后，它对数据库中的影响时永久的，即便系统遇到故障的情况下，数据也不会丢失。

3、Oracle COMMIT语句（提交事务）
COMMIT语句用来提交当前事务的所有更改。提交后，其他用户将能够看到您的更改。

语法：

commit  【work】【commit clause】【write clause】【force clause】

参数：

• work：使用或不使用work参数来执行commit将产生相同的结果。
• commit clause：用于指定与当前事务关联的注释。该注释最多包含在单引号中的255哥字节的文本中。如出现问题，将与事务ID一起存储在名为DBA_2PC_PENDING的系统视图中。
• write clause：用于指定将已提交事务的重做信息写入重做日志的优先级。用这个子句，有两个参数可以指定，WAIT和NOWAIT IMMEDIA和BATCH。
• froce clause：用于强制提交可能已损坏或有疑问的事务。有了这个子句，有三种方式指定force：froce ‘string’，【interger】或force corrupt_xid‘string’或force corrupt_xid_all
• 必须拥有DBA权限才能访问系统视图 DBA_2PC_PENDING和V$CORRUPT_XID_LIST。必须拥有DBA权限才能指定COMMIT语句的某些功能。

commit；将执行以下相同操作  commit work write wait immediate；//这两者等效
隐含了work关键字，并且忽略write子句将缺省为WRITE WAIT IMMEDIATE

添加备注

commit comment 'this is comment for the transaction';
commit work comment 'this is the comment for the transaction';

由于始终隐含WORK关键字，因此这两个COMMIT示例都是等效的。 如果事务出错或存在疑问，COMMIT会将包含在引号中的注释与事务ID一起存储在DBA_2PC_PENDING系统视图中。

commit force 'xx.xx.xx';   commit work force 'xx.xx.xx';

始终隐含WORK关键字，因此这两个COMMIT示例都会强制执行由事务ID “xx.xx.xx”标识的已损坏或疑问事务。

ROLLBACK语句（回滚事务）

rollback 语句可以用来撤销当前事务或有问题的事务。

语法

ROLLBACK【WORK】【TO【SAVEPOINT】savepoint_name | FORCE 'string'】；

参数

• work：使用或不使用work参数来执行commit将产生相同的结果。
• TO【SAVEPOINT】savepoint_name ：ROLLBACK语句撤销当前会话的所有更改，直到由savepoint_name 指定的保存点。
• force ‘string’ ：用于强制回滚可能已损坏或有问题的事务。
  可以将单引号中的事务ID指定为字符串。可在系统视图中找到名为DBA_ 2PC_PENDING和V$CORRUPT_XID_LIST。
• 必须拥有DBA权限才能访问系统视图：DBA_ 2PC_PENDING和V$CORRUPT_XID_LIST。
• 您无法将有问题的事务回滚到保存点。

示例

rollback；等同于 rollback work；//将回滚当前事务
 

rollback  to  savepoint  savepoint1；rollback work to 
 savepoint savepoint1； //将事务回滚到名为savepoint1的保存点

rollback force ‘xx.xx.xx’; 或者 rollback work force ‘xx.xx.xx’; //强制回滚由事务ID“xx.xx.xx” 标识的损坏或有问题的事务。

SET TRANSACTION 设置事务

设置事务的各种状态，比如只读、读写、隔离级别，为事务分配名称或将事务分配回滚段等。
语法

SET TRANSACTION [READ ONLY| READ WRITE]
                [ISOLATION LEVEL[SERIALIZE| READ COMMITED]]
                [USE ROLLBACK SEGMENT 'segment_name']
                [NAME 'transaction_name'];

• READ ONLY：将事务设置为只读事务。
• READ WRITE：将事务设置为读写事务。
• ISOLATION LEVEL：如果指定，有两个选项：
  1）ISOLATION LEVEL SERIALIZE：如果事务尝试更新由另一个事务更新并未提交的
  资源，则事务失败。
  2）ISOLATION LEVEL READ COMMITTED：如果事务需要另一个事务持有的行锁，则事务等待，直到行锁被释放。
  • USE ROLLBACK SEGMENT：u如果指定，将事务分配给由“segment_name” 标识的回退段，由引号引起的段名称。
  • NAME：为‘transaction_name’ 标识的事务分配给一个名称，该事务用引号括起来。

示例

set reansaction read only name ‘ro_example’;//将事务设置为只读，为其分配“o_example”的名称
set transaction read write name 'rw_example';//将事务设置为读写，为其分配“rw_example”的名称

LOCK TABLE 语句（锁表）

用来锁定表、表分区或表子分区。
语法

LOCK TABLE tables IN lock_mode MODE 【WAIT【，interger】| NOWAIT】；

参数

• tables：用逗号分隔的表格列表
• lock_mode：
  
• WAIT：指定数据库将等待（达到指定整数的特定秒数）以获取DML锁定。
• NOWAIT：指定数据库不应该等待释放锁。
  示例
  LOCK TABLE suppliers IN SHARE MODE NOWAIT；//锁定suppliers表在共享模式，而不是等待锁定被释放。

Oracle外键

示例

CREATE TABLE supplier
(supplier_id numeric(10) not null,
 supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE products 
(product_id numeric(10) not null,
 supplier_id numeric(10) not null,
 CONSTRAINT fk_supplier
   FOREIGN KEY
   REFERENCES supplier(supplier_id) 
);

在supplier表上创建一个名为supplier_pk 的主键，只包含一个字段supplier_id。在product表上创建了一个名为fk_supplier 的外键，根据supplier_id字段引用supplier表。

使用ALTER TABLE 创建外键的示例

ALTER TABLE products 
ADD CONSTRAINT fk_supplier
    FOREIGN KEY (supplier_id)
    REFERENCES supplier(ssupplier_id);

创建了一个名为fk_supplier的外键，根据supplier_id字段引用supplier表的supplier_id字段。

级联删除外键

级联删除是指当主表中的一个记录被删除，那么子表中所关联的记录也相应自动删除

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  CONSTRAINT fk_supplier
    FOREIGN KEY (supplier_id)
    REFERENCES supplier(supplier_id)
    ON DELETE CASCADE
);

在supplier表创建了一个名为supplier_pk的主键，主键只包含supplier_id字段，在products表山创建一个名为fk_supplier的外键，依据supplier_id字段引用supplier表的supplier_pk字段。由于级联删除，当supplier表中的记录被删除时，product表中相应的所有数据也被删除。因为这些记录具有相同的supplier_id 。

使用ALTER TABLE 语句定义级联删除的示例

ALTER TABLE products
ADD CONSTRAINT fk_supplier
    FOREIGN  KEY (supplier_id)
    REFERENCES  supplierz(supplier_id)
    ON DELECT CASECADE;

创建了名为fk_supplier的外键（带级联删除），该外键基于supplier_id 字段引用supplier表。

删除外键

使用ALTER TABLE 语句对外键进行删除

语法

ALTER TABLE table_name
DROP CONSTRAINT  constraint_name;

示例

ALTER TABLE products
DROP CONSTRAINT  fk_supplier;//删除fk_supplier外键

禁用外键

语法

ALTER TABLE table_name
DISABLE CONSTRAINT  constraint_name;

示例

ALTER TABLE products
DISABLE CONSTRAINT  fk_supplier;//禁用fk_supplier外键

启用外键

语法

ALTER TABLE table_name
ENABLE CONSTRAINT  constraint_name;

示例

ALTER TABLE products
ENABLE CONSTRAINT  fk_supplier;//启用fk_supplier外键

原先被禁用的外键就被启用了

字符串函数

1、Asii() 函数

语法：ASCII（single_character）
参数：single_character 指定的字符来检索NUMBER代码，如果输入多个字符，ASCII函数将返回第一个字符的值，并忽略第一个字符后的所有字符。
适用于：Oracle 12c、Oracle 11g、Oracle 10g、Oracle 9i、Oracle 8i
示例：ASCII（‘t’）result:116 ASCII（‘T’）result:84 ASCII（‘T2’）result:84

2、Asciistr()函数
使用数据库字符集将任何字符集中的字符串转化为ASCII字符串

语法：ASCIISTR(string)
参数：string 任何字符集中的字符串，希望将其转换为数据库字符集中的ASCII字符串
适用于：Oracle 12c、Oracle 11g、Oracle 10g、Oracle 9i
示例：

ASCIISTR('A B C Ä Ê')
Result: 'A B C 0C4 0CA'

ASCIISTR('A B C Õ Ø')
Result: 'A B C 0D5 0D8'

ASCIISTR('A B C Ä Ê Í Õ Ø')
Result: 'A B C 0C4 0CA 0CD 0D5 0D8'

3、Chr() 函数
根据数字代码返回字符，功能与ASCII函数相反。

语法：CHR（number_code）
参数：number_code：检索对应字符的NUMBER代码
适用于：Oracle 12c、Oracle 11g、Oracle 10g、Oracle 9i
示例：CHR（116）result：‘t’ CHR（84）result：‘T’

4、Compose()函数
返回一个Uncoide字符串

语法：COMPOSE(string) //string 创建Unicode字符串的输入值。
返回值：

适用于：Oracle 12c、 Oracle 11g、 Oracle 10g、 Oracle 9i
示例：

COMPOSE('o' || unistr('\0308') )
Result: ö

COMPOSE('a' || unistr('\0302') )
Result: â

COMPOSE('e' || unistr('\0301') )
Result: é

5、Concat()函数

将两个字符串连接在一起
语法：concat(string1,string2) //string1：第一个要连接的字符串。string2：第二个要连接的字符串。
返回值：返回string1连接string2后的一个字符串值
适用于：Oracle 12c、 Oracle 11g、 Oracle 10g、 Oracle 9i
示例：

concat（‘oracle’，‘com’）；result：‘oracle.com’

如果要连接多个值，可以用嵌套concat函数

SELECT CONCAT(CONCAT('A', 'B'),'C')
FROM dual;
-- Result: 'ABC' //将三个值连接在一起并返回‘ABC’字符串值

连接单引号

select concat  （'Let''s,'Learn Oracle'）
from  dual;

result : Let's learn Oracle

6、|| 运算符
将两个或两个以上的字符串连接在一起

示例：

'a'||'b'||'c'||'d'   result：abcd

7、 convert()函数
convert() 函数将字符串从一个字符集转换为另一个字符集。
语法：CONVERT（sring1，char_set_to [,char_set_from]）
string1：要转换的字符串。
char_set_to：要转换为的字符集。
char_set_from：可选的，要从中转换的字符集。
示例：

CONVERT('A B C D E Ä Ê Í Õ Ø', 'US7ASCII', 'WE8ISO8859P1')
-- Result: 'A B C D E A E I ? ?'

8、Dump()函数
返回一个varchar2值，这个值包含了数据类型代码、字节长度和表达式的内部表达形式。
语法：DUMP( expression [, return_format] [, start_position] [, length]

返回值：如果省略了return_format，start_position和length参数，则DUMP函数将以十进制表示形式返回整个内部表示形式。

示例：

DUMP('Tech')
-- Result: 'Typ=96 Len=4: 84,101,99,104'

DUMP('Tech', 10)
-- Result: 'Typ=96 Len=4: 84,101,99,104'

DUMP('Tech', 16)
-- Result: 'Typ=96 Len=4: 54,65,63,68'

DUMP('Tech', 1016)
-- Result: 'Typ=96 Len=4 CharacterSet=US7ASCII: 54,65,63,68'

DUMP('Tech', 1017)
-- Result: 'Typ=96 Len=4 CharacterSet=US7ASCII: T,e,c,h'

9、Initcap（）函数
将每个单词中的第一个字符设置为大写，其余设置为小写。
语法：INITCAP（string1）
示例：

INITCAP('tech on the oraok');
-- Result: 'Tech On The Oraok'

INITCAP('GEORGE BURNS');
-- Result: 'George Burns'