Oracle数据库

本文深入解析Oracle数据库,涵盖其体系结构、实例组成、物理与逻辑存储结构、事务管理、查询优化及字符串函数应用等内容,帮助读者全面理解Oracle数据库的运作机制。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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

distinctNULL值视为重复值,如果使用select distinct 从具有多个NULL值的列中查询数据,结果集只包含一个NULLselect 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'
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值