Oracle 数据库
1 简介
Oracle 数据库,又称 Oracle RDBMS (Relational Database Management System),是由甲骨文公司开发和维护的对象-关系数据库管理系统
基于 C/S 技术
Oracle Internet 文件系统(iFS),以前称为 WebDB
- 是一个基于 Java 的应用程序,可以使数据库成为基于Internet的开发平台
- 与 Oracle InterMedia 集成,使客户端应用程序(如 Oracle InterMedia Audio、Image、Video)可以轻松访问和操作数据库中的多媒体数据
2 基本概念
2.1 Oracle 数据库组件
- 数据库由下列组件构成
- 数据库文件
- 控制文件
- 恢复日志文件
- 表空间
- 段
- 扩展区
- 数据块
2.1.1 物理组件
数据库文件包括下述三种文件
- 数据文件(Data Files):
- 功能:物理操作系统文件,存储实际的用户数据和系统数据。
- 组成:一个或多个数据文件组成一个表空间,每个数据库至少一个数据文件。
- 特性:每个数据文件只能属于一个表空间,数据以数据块为单位存储。
- 控制文件(Control Files):
- 功能:包含数据库的元数据,如结构信息、文件位置等。
- 特性:数据库启动和恢复所必需的,通常有多个以提高可靠性。
- 恢复日志文件(Redo Log Files):
- 功能:记录所有事务的变化,用于故障恢复。
- 特性:循环使用,帮助在系统故障后恢复未提交的数据,数据库至少包含两组。
2.1.2 逻辑组件
- 表空间(Tablespace):
- 功能:逻辑存储单元,用于管理多个数据文件。保留相关数据库对象的组。
- 特性:允许数据库管理员管理不同的存储部分。
- 段(Segment):
- 功能:表空间中的一部分,用于存储特定类型的数据对象。
- 特性:由一个或多个扩展区组成,段的大小可以动态增长。
- 扩展区(Extent):
- 功能:段的一部分,包含一组连续的数据块。
- 特性:当段需要更多空间时,数据库会分配一个新的扩展区。
- 数据块(Data Block):
- 功能:数据库中最小的存储单元。
- 特性:数据块是数据库读写数据的基本单位。
通过合理地使用表空间、段、扩展区和数据块,数据库管理员可以对存储空间进行更加细致的控制,从而优化数据库性能和存储管理。
2.1.2.1 表空间
2.1.2.1.1 典型表空间
- SYSTEM 表空间:
- 包含数据库的核心系统对象,如数据字典、系统表等。
- 是数据库中最重要的表空间之一,用于存储 Oracle 数据库的内部元数据。
- DATA 表空间:
- 存储用户数据的主要表空间,包含用户表、索引和其他数据库对象。
- 数据库管理员通常将用户数据存储在 DATA 表空间中,以便对其进行管理和维护。
- USER 表空间:
- 用于分配给数据库用户的默认表空间。
- 每个用户可以有自己的 USER 表空间,用于存储其数据和对象。
- TOOLS 表空间:
- 用于存储数据库管理工具和实用程序所需的对象。
- 包含了数据库管理和开发工具所需的表、索引等对象。
- TEMP 表空间:
- 临时表空间,用于存储临时数据和临时结果集。
- 在排序、连接和其他需要临时存储空间的操作中使用。
2.1.2.1.2 功能
- 控制对象的空间分配,包括表、索引等。
- 设置数据库用户的空间配额,限制用户在表空间中的存储空间使用。
- 作为备份和恢复数据的一部分,保护数据库免受数据丢失的影响。
2.2 Oracle 数据库预定义角色
`USER`
在Oracle数据库中,USER
不是一个预定义角色,而是一个关键字,用于指代当前数据库会话所使用的用户名。用户可以创建名为 USER
的角色,但 USER
本身并不是一个Oracle预定义的角色(=> 可能的预定义角色)。
2.2.1 CONNECT
- 描述: 允许用户连接到数据库并创建基本的数据库对象。
- 权限: 创建会话、表、视图等基本对象。
2.2.2 RESOURCE
- 描述: 允许用户创建和管理特定类型的数据库对象。
- 权限: 创建表、索引、过程等对象。
2.2.3 DBA
- 描述: 拥有数据库的完全控制权限。
- 权限: 所有系统权限,包括创建用户、删除数据库等管理权限。
2.2.4 EXP_FULL_DATABASE
- 描述: 允许用户导出整个数据库。
- 权限: 使用
exp
工具导出数据库中的所有对象和数据。
2.2.5 IMP_FULL_DATABASE
- 描述: 允许用户导入整个数据库。
- 权限: 使用
imp
工具导入数据库中的所有对象和数据。
2.2.6 SELECT_CATALOG_ROLE
- 描述: 允许用户查询数据字典视图和动态性能视图。
- 权限: 访问所有数据字典视图(如
DBA_*
视图)。
2.2.7 EXECUTE_CATALOG_ROLE
- 描述: 允许用户执行数据库字典中的包和过程。
- 权限: 执行数据字典中定义的包和过程。
2.3 Oracle 数据库对象
- 同义词是数据库对象的替换名称
- 同义词隐藏了对象的名称和所有者
- 序列生成唯一、连续的整数
- Nextval 和 Currval 用于访问序列
- 视图是一种经过定制的表示方式,用于显示来自一个或多个表的数据
- 索引加快了 SQL 语句的执行速度
- 在表的多个列上创建的索引称为“组合索引”
- 在索引组织表中,数据访问基于主键值
2.3.1 同义词
同义词是数据库对象的替换名称,用作表、视图、序列、过程、存储函数、程序包、实体化视图或其他同义词的别名或替换名称。
优点:
- 简化 SQL 语句
- 隐藏对象的名称和所有者
- 提供对对象的公共访问
同义词类型:
- 私有同义词:由普通用户创建,只有创建该同义词的用户才能使用。
- 公用同义词:由DBA创建,任何数据库用户都可以使用,用于隐藏基表的身份,并降低SQL语句的复杂性。
创建和删除同义词的命令:
CREATE SYNONYM
命令用于创建同义词。DROP SYNONYM
命令用于删除同义词。
示例:
CREATE SYNONYM emp_syn FOR hr.employees;
DROP SYNONYM emp_syn;
注意:
- 当公用对象和本地对象具有相同的名称时,本地对象优先,但在删除本地对象之后,可以像平常一样使用公用同义词。
USER_SYNONYMS
包含有关同义词的信息。
2.3.2 序列
序列用于生成唯一、连续的整数,通常用于生成主键值。序列可以是升序排序,也可以是降序排序。
创建序列的语法:
CREATE SEQUENCE <sequencename>
INCREMENT BY n
START WITH n
[MAXVALUE n] [MINVALUE n]
[CYCLE|NOCYCLE]
[CACHE n|NOCACHE];
参数说明:
INCREMENT BY n
: 指定序列数字之间的整数间隔。START WITH n
: 指定要生成的第一个序列号。MINVALUE n
: 指定序列的最小值。MAXVALUE n
: 指定序列可以生成的最大值。CYCLE
: 指定序列即使达到了最大值,还应继续生成值,通常是循环从头开始产生序列值。CACHE
: 允许更快地生成序列号,Oracle分配序列号,并将其保存在内存中以便更快地访问。
示例:
CREATE SEQUENCE venseq
INCREMENT BY 1
START WITH 1
MAXVALUE 10
MINVALUE 1
CYCLE
CACHE 4;
访问序列:
NEXTVAL
: 第一次使用NEXTVAL
时,将返回该序列的初始值。CURRVAL
: 返回序列的当前值,即最后一次引用NEXTVAL
时返回的值。
示例:
SELECT venseq.NEXTVAL FROM dual;
SELECT venseq.CURRVAL FROM dual;
INSERT INTO vendor_master(vencode, venname) VALUES ('V' || venseq.NEXTVAL, 'vijay');
2.3.3 视图
视图是一种经过定制的表示方式,用于显示来自一个或多个表的数据,也称为“虚拟表”或“已存储的查询”。
基表:
创建视图所依据的表。
视图的优点:
- 提供了另外一种级别的表安全性(根据个人权限只允许查看表的某几个字段)。
- 隐藏数据的复杂性(所有字段都显示太复杂,同时也可能是无用数据)。
- 简化用户的 SQL 命令。
- 将应用程序与基表定义的修改隔离开来。
- 从另一个角度提供数据。
视图中的函数:
在视图中可以使用函数和表达式。
示例:
CREATE VIEW itemsold AS
SELECT itemcode, SUM(qty_ord) AS sold_qty
FROM order_detail
GROUP BY itemcode;
注意事项:
- 不能选择伪列,如
CURRVAL
和NEXTVAL
。 - 如果视图包括联接、集合操作符、分组函数、DISTINCT子句,就不能执行删除、更新、插入操作,只能查询。
- 在视图中所作的修改将影响基表,反之亦然。
- 分组函数和
GROUP BY
子句也可以包含在视图中。 - 使用函数时,应为列指定一个别名。
删除视图:
使用 DROP VIEW
语句从数据库中删除视图。
示例:
DROP VIEW empview;
有关视图的信息:
可以通过查询 USER_VIEWS
获取。
2.3.4 索引
索引用于加快 SQL 语句的执行速度,是与表关联的可选结构。索引通过减少磁盘 I/O 提高查询性能。
创建索引的语法:
CREATE INDEX index_name ON table_name(column_name);
索引的特点:
- 索引在逻辑上和物理上独立于表中的数据。
- Oracle 自动维护索引。
索引类型:
- 唯一索引:
确保在定义索引的列中没有重复的值。Oracle 自动为主键和唯一键列创建唯一索引。
创建唯一索引的语法:
CREATE UNIQUE INDEX index_name ON table_name(column_name);
- 组合索引:
在表的多个列上创建的索引,也称为“连接索引”。组合索引中的列可以按任意顺序排列。对于在WHERE
子句中包含多个列的查询,可以提高数据访问速度。
创建组合索引的语法:
CREATE INDEX index_name ON table_name(column1, column2);
- 索引组织表:
表的数据存储在与其关联的索引中,对表数据的增删改只会导致对索引的更新。
3 SQL
3.1 Oracle 的结构化查询语言
3.1.1 SQLPlus
- 功能:用于输入、编辑、存储、检索和运行SQL命令及PL/SQL块。
- 优点:提供命令行界面,直接执行SQL语句,进行数据库管理和查询。
- 使用场景:数据库管理、数据查询、脚本执行等。
3.1.2 iSQLPlus
- 功能:类似SQLPlus,提供基于Web的界面,通过浏览器访问。
- 优点:无需客户端软件,方便远程数据库操作。
- 使用场景:远程数据库访问,无SQLPlus环境下的数据库操作。
3.1.3 PL/SQL
- 定义:Oracle对标准SQL的扩展,过程化SQL语言。
- 特点:结合SQL数据操纵能力和编程语言处理能力,支持变量、条件语句、循环等。
- 使用场景:编写复杂数据库应用程序,如存储过程、函数、触发器和包。
3.2 SQL特点
- 非过程性:不支持循环和分支结构,主要用于数据查询和操作。
- 集合性:一次性对多条记录进行操作,而非单个记录。
3.3 Oracle 数据库主要数据类型
3.3.1 字符类型
- CHAR
- 定长字符类型,最大长度为2000字节。
- VARCHAR2
- 变长字符类型,最大长度为4000字节。
- NCHAR
- 定长国家字符集数据类型,最大长度为1000字符。
- NVARCHAR2
- 变长国家字符集数据类型,最大长度为2000字符。
- LONG
- 可变长字符数据类型,最大长度为2GB。适用于不需要字符串搜索的长文本数据(不能在
LONG
列上建立索引),一个表中只能有一个LONG
列,LONG
列不能定义为唯一或主C键约束,过程或存储过程不能接受LONG
数据类型作为参数,建议改用CLOB
- 可变长字符数据类型,最大长度为2GB。适用于不需要字符串搜索的长文本数据(不能在
3.3.2 数值类型
- NUMBER(p,s)
- 其中
p
是精度,表示数字中的总位数,范围可以是1到38 s
是小数位数,表示小数点右边的位数,范围是-84到127
- 其中
- 存储能力
- 可以存储正数、负数、0、定点数以及精度为38的浮点数
- 示例
PhoneNo NUMBER
: 默认情况下,如果没有指定p
和s
,则p
为38,s
为0,即PhoneNo NUMBER
等同于PhoneNo NUMBER(38, 0)
Age NUMBER(3)
: 表示一个定点数,有3位整数,没有小数位,等同于Age NUMBER(3, 0)
Salary NUMBER(7, 2)
: 表示一个数值,其中整数部分有5位,小数部分有2位
3.3.3 日期类型
- Date
- 存储结构
- 使用7个字节分别存储世纪、年、月、日、小时、分钟和秒。
- 范围
- 从公元前4712年1月1日到公元4712年12月31日。
- 默认格式
dd-mon-yy
- SYSDATE()函数
- 用于获取当前日期和时间
- 存储结构
- Timestamp
- 存储日期和时间,包括小数秒。
- Interval day to second
- 存储时间间隔,以天、小时、分钟和秒为单位,包括小数秒。
- Interval year to month
- 存储时间间隔,以年和月为单位。
- **Timestamp with time zone
- 存储日期和时间值,包括时区信息。
- Timestamp with local time zone
- 存储日期和时间值,自动转换为数据库服务器所在时区的本地时间。检索数据时,数据将被调整为与客户机的时区相匹配。
3.3.4 二进制类型
- RAW: 面向字节的数据类型,用于存储二进制数据。
- 大小限制: 可以存储1到2000字节的数据。应指定大小
- 特点: RAW类型在网络中传输或使用Oracle实用程序移动数据时,不会进行字符集转换,保持数据的原始二进制格式
- LONG RAW: 用于存储可变长度的二进制数据
- 大小限制: 可以存储1到2GB的数据
- 特点: 类似于
LONG
数据类型,LONG RAW
不可被索引,且有与LONG
类型相同的限制。Oracle推荐使用BLOB
数据类型来代替LONG RAW
,以便于更好地处理大量的二进制数据
3.3.5 大型对象类型
- LOB
- 大小范围:从1字节到4GB,用于存储非结构化信息,如声音剪辑、视频文件。
- 访问方式:允许有效、随机、分段地访问数据。
- 存储位置:LOB数据既可以是外部的,也可以是内部的,这取决于它们相对于数据库的位置。
- LOB 数据类型:
- CLOB(Character Large Object):用于存储大量的字符数据,如非结构化的XML文档。
- BLOB(Binary Large Object):用于存储无结构的二进制数据,如图形、视频剪辑、声音文件。
- BFILE:用于在数据库之外的操作系统文件中存储二进制文件。
- 特点:数据库表中存储的是指向操作系统文件的指针,因此BFILE类型的数据是只读的。
- 多列支持:数据库表中可以有多个LOB列,不同列可以为不同的LOB类型。
- 索引:LOB可以被索引,但不能删除和重建在LOB列上建立的索引。
3.4 SQL 命令类别
- DDL(数据定义语言)
- 定义和修改数据库结构。如创建、修改、截断。
- DML(数据操纵语言)
- 对数据库中的数据进行增加、删除和修改。
- DCL(数据控制语言)
- 控制数据的访问权限。如授权grant、撤销revoke。
- TCL(事务控制语言)
- 管理数据库事务,保证数据一致性和完整性。如回滚rollback、提交commit。
3.4.1 DDL(数据定义语言)
schema
通常代表数据库中的一个命名空间,它包含了一组数据库对象,如表、视图、存储过程等,通过对象所有者指定。
CREATE: 用于创建新的数据库对象,如表、索引、视图等。
- 表名必须以字母开头,不能以下划线或数字开头,但可以包含中文字段名。
- 不能使用Oracle保留字作为表名。
- 表名的最大长度应小于等于30个字符。
- 在同一数据库模式(schema)中,不允许出现相同的表名。
- 表名可以包含字母、数字和下划线,但不能包含空格和单引号。
- 如果使用双引号命名表名,如
"inf"
,则表名将区分大小写。例如,"inf"
和"INF"
会被视为不同的表名。
sqlCREATE TABLE [schema.]table_name ( column1 datatype [DEFAULT expr], [...] );
ALTER: 用于修改现有数据库对象的结构。
- 允许用户添加新的列到现有表。
- 可以修改列的数据类型或其宽度。
- 允许新增或删除完整性约束,如主键、外键、唯一约束等。
- 当表的列不为空时,不能减小其长度。
sqlALTER TABLE table_name ADD (column_name datatype);
sqlALTER TABLE table_name MODIFY (column_name new_datatype);
sqlALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_type(column_name);
sqlALTER TABLE table_name DROP CONSTRAINT constraint_name;
DROP: 用于删除数据库中的对象。
sqlDROP TABLE table_name;
**–DELETE [FROM] table_name
[WHERE condition];**: 用于删除表中的数据,但不删除表本身。
- REUSE STORAGE
保留被删除的行所使用的空间
```sql
TRUNCATE TABLE table_name [REUSE STORAGE];
```
DESC: 用于查看表结构。
sqlDESC TABLE table_name;
COMMENT: 用于向数据字典添加注释。
sqlCOMMENT ON TABLE table_name IS 'comment_text';
RENAME: 用于重命名数据库中的对象。
sqlRENAME TABLE old_table_name TO new_table_name;
3.4.2 DML(数据操纵语言)
`SCOTT`
在 Oracle 数据库中,SCOTT
是一个常用的示例用户,用于演示和测试目的。通常情况下,SCOTT
用户具有一些预定义的示例表和数据,可以用来展示 SQL 查询、数据操作和其他数据库功能。
SCOTT
用户常包含一些示例表,如EMP
(员工信息)、DEPT
(部门信息)等。这些表中包含一些模拟的数据,用于演示 SQL 查询和操作。SCOTT
用户也被用于演示各种 SQL 语句的使用,包括查询、插入、更新、删除等操作。这有助于学习者加深对 SQL 语言的理解。- 由于
SCOTT
用户权限较少且包含模拟数据,因此它也常被用于进行一些简单的测试和验证操作,以确保 SQL 语句的语法和逻辑正确。 - 在 Oracle 数据库的学习和培训过程中,
SCOTT
用户经常被用作演示和练习的对象,帮助学生和培训者学习 SQL 语言和数据库操作。
`DUAL`
在 Oracle 数据库中,DUAL
是一个特殊的单行单列表,主要用于需要返回单行结果的查询或测试简单表达式的情况下。
DUAL
表只包含一行数据,列名为DUMMY
,其值为X
。DUAL
表常用于SELECT
语句中以返回常量、计算表达式、调用函数等。
SELECT: 用于从数据库中检索数据。
- 创建现有表的副本
- 插入来自其他表的记录
- 使用别名让列显示其他名称
sqlSELECT * |{[DISTINCT] column | expression [alias],…} FROM table_name WHERE conditions ORDER BY column_name; - 创建现有表的副本 CREATE TABLE new_table_name[(column1, ...)] AS SELECT * | [(column1, ...)] FROM existing_table_name; - 插入来自其他表的记录 INSERT INTO target_table_name (column1, column2, …) SELECT column1, column2, … FROM source_table_name WHERE conditions; - 使用别名让列显示其他名称 SELECT column_name [AS] alias_name FROM table_name;
INSERT INTO: 用于向表中添加新数据。
sqlINSERT INTO table_name [(column1[, column...])] VALUES (value1[, value...]);
UPDATE: 用于修改表中的现有数据。
WHERE
子句和SET
子句还可以包含子查询
sqlUPDATE table_name SET column = value[, column = value, …] [WHERE condition];
DELETE: 用于从表中删除数据。
- 只删除表记录,表结构保持不变。
sqlDELETE [FROM] table_name [WHERE condition];
- TRUNCATE
- 是DDL操作,执行后立即提交,无法回滚。
- 快速删除所有行,不触发触发器,释放空间。
- DELETE
- 是DML操作,可以回滚,可以指定条件。
- 删除速度较慢,触发触发器,不释放空间。
3.4.3 DCL(数据控制语言)
GRANT: 用于授予用户或角色权限。
sqlGRANT privilege_name ON object_name TO {user_name | PUBLIC | role_name}
REVOKE: 用于撤销用户或角色的权限。
sqlREVOKE privilege_name ON object_name FROM {user_name | PUBLIC | role_name}
3.4.4 TCL(事务控制语言)
COMMIT: 用于提交当前事务,使所有更改成为永久性的。
sqlCOMMIT;
ROLLBACK: 用于回滚当前事务,取消所有未提交的更改。
sqlROLLBACK [TO SAVEPOINT savepoint_name];
SAVEPOINT: 用于在事务中创建一个保存点,可以回滚到该点。
sqlSAVEPOINT savepoint_name;
SET TRANSACTION: 用于设置事务的特性。
sqlSET TRANSACTION ISOLATION LEVEL READ COMMITTED;
3.4.5 数据导入导出操作
导入和导出数据是数据库管理中的重要操作,虽然它们不属于标准的SQL命令类别,但在数据库维护和数据迁移中发挥着关键作用。
3.4.5.1 导入数据
导入数据通常用于将外部数据文件中的数据加载到数据库表中。
使用
LOAD DATA
语句导入数据:sqlLOAD DATA INFILE 'datafile.txt' INTO TABLE tablename FIELDS TERMINATED BY ',' (column1, column2, …);
使用
INSERT INTO … SELECT
语句从另一个表中导入数据:sqlINSERT INTO target_table (column1, column2, …) SELECT column1, column2, … FROM source_table;
使用
imp
命令导入数据库:shellimp username/password@database FILE=exportfile.dmp
3.4.5.2 导出数据
导出数据用于将数据库中的数据写入到外部文件中,常用于备份和数据迁移。
使用
SPOOL
命令导出查询结果:sqlSPOOL outputfile.txt SELECT column1, column2, … FROM tablename; SPOOL OFF;
使用
exp
工具导出数据:shellexp username/password@database TABLES=tablename FILE=exportfile.dmp
这些导入和导出操作虽然不直接属于DDL、DML、DCL或TCL,但在数据库管理中不可或缺。
3.5 SQLPlus
- 用于操纵数据
- 接受一个或多个参数并返回一个值
3.5.1 SQLPlus中的运算符
3.5.1.1 算术运算符
算术运算符用于对数值数据进行数学运算。
+
:加法运算-
:减法运算*
:乘法运算/
:除法运算
示例:
SELECT salary, salary + 500 AS new_salary
FROM employees;
3.5.1.2 比较运算符
比较运算符用于比较两个表达式的值。
=
:等于!=
或<>
:不等于>
:大于<
:小于>=
:大于或等于<=
:小于或等于IN
:检查值是否在一组值中LIKE
:匹配字符串中的模式IS NULL
:检查值是否为 NULLIS NOT NULL
:检查值是否不为 NULLBETWEEN
:检查值是否在指定范围内NOT BETWEEN
:检查值是否不在指定范围内
示例:
SELECT first_name, last_name
FROM employees
WHERE salary > 5000;
SELECT first_name, last_name
FROM employees
WHERE department_id IN (10, 20, 30);
SELECT first_name, last_name
FROM employees
WHERE last_name LIKE 'S%';
SELECT first_name, last_name
FROM employees
WHERE manager_id IS NULL;
SELECT first_name, last_name
FROM employees
WHERE manager_id IS NOT NULL;
SELECT first_name, last_name
FROM employees
WHERE salary BETWEEN 3000 AND 5000;
3.5.1.3 逻辑运算符
逻辑运算符用于组合多个条件。
AND
:且OR
:或NOT
:非
示例:
SELECT first_name, last_name
FROM employees
WHERE salary > 5000 AND department_id = 10;
3.5.1.4 连接运算符
连接运算符用于连接两个或多个字符串,或者将一个字符串与一个数据值合并。
用于合并各列为一个字符串的查询。
注意符号的左右两边必须留空格。
||
:连接符
示例:
SELECT first_name || ' ' || last_name AS full_name
FROM employees;
3.5.1.5 操作符的优先级
优先级从高到低
- 算术操作符
- 连接操作符
- 比较操作符
NOT
逻辑操作符AND
逻辑操作符OR
逻辑操作符
3.5.2 SQLPlus函数的类别
3.5.2.1 单行函数
单行函数对每一行数据进行操作并返回一个结果。
3.5.2.1.1 日期函数
用于处理和操作日期值的函数。
SYSDATE
:返回当前日期和时间ADD_MONTHS(date, n)
:在指定日期上加上n
个月MONTHS_BETWEEN(date1, date2)
:返回date1
和date2
之间的月份数LAST_DAY(date)
:返回月末最后一天相应的日期NEXT_DAY(date, weekday)
:返回在指定日期之后的下一个指定星期几的日期ROUND(date,[format])
:返回日期,舍入到格式模型所指定的单位,默认情况下日期舍入到最接近的日期format => year
date >= year.07.1
:舍入下一年的 1 月 1 日
format => month
month > 15
:舍入下个月 1 日,否则当月 1 日
format => day
:舍入到最接近的日期日
3.5.2.1.2 字符函数
用于处理和操作字符数据的函数。
UPPER(string)
:将字符串转换为大写LOWER(string)
:将字符串转换为小写SUBSTR(string, start, length)
:从字符串的指定位置开始提取子字符串INSTR(string, substring)
:返回子字符串在字符串中首次出现的位置LENGTH(string)
:返回字符串的长度CHR(ascii_code)
:将ASCII码转换为字符ASCII(character)
:将字符转换为ASCII码INITCAP(string)
:将字符串的每个单词的首字母转换为大写LPAD(string, length, pad_string)
:用指定的字符串在左边填充到指定长度RPAD(string, length, pad_string)
:用指定的字符串在右边填充到指定长度LTRIM(string, trim_string)
:去除字符串左边的所有指定字符RTRIM(string, trim_string)
:去除字符串右边的所有指定字符TRIM
:TRIM(s)
:合并了LTRIM
和RTRIM
的功能,删除字符串两端的空格或其他指定字符TRIM(leading s1 from s)
:返回删除前导与s1
相同的字符串后所余下的字符串TRIM(trailing s1 from s)
:返回删除结尾与s1
相同的字符串后所余下的字符串TRIM(s1 from s)
:删除字符串s
两端含有的s1
TRIM(s)
:删除字符串s
两端的空格
DECODE(expression, search, result, default)
:对表达式进行条件查询,类似于CASE
表达式TRANSLATE(string, from_string, to_string)
:将字符串中的字符从from_string
转换为to_string
中的字符REPLACE(string, search_string, replace_string)
:用指定的字符串替换字符串中的子字符串CONCAT(string1, string2)
:连接两个字符串
示例:
SELECT UPPER(first_name) FROM employees;
SELECT SUBSTR(last_name, 1, 3) FROM employees;
SELECT LENGTH('Oracle') FROM dual;
SELECT CHR(65) FROM dual;
SELECT ASCII('A') FROM dual;
SELECT INITCAP('oracle database') FROM dual;
SELECT LPAD('Oracle', 10, '*') FROM dual;
SELECT RPAD('Oracle', 10, '*') FROM dual;
SELECT LTRIM('***Oracle***', '*') FROM dual;
SELECT RTRIM('***Oracle***', '*') FROM dual;
SELECT TRIM('*' FROM '***Oracle***') FROM dual;
SELECT DECODE(department_id, 10, 'Sales', 20, 'Marketing', 'Other') FROM employees;
SELECT TRANSLATE('12345', '123', 'abc') FROM dual;
SELECT REPLACE('Hello World', 'World', 'Oracle') FROM dual;
SELECT CONCAT('Hello', ' World') FROM dual;
3.5.2.1.3 数字函数
用于处理和操作数字数据的函数。
ROUND(number, decimals)
:将数字四舍五入到指定的小数位TRUNC(number, decimals)
:将数字截断到指定的小数位MOD(number, divisor)
:返回数字被除数整除后的余数ABS(number)
:返回数字绝对值CEIL(number)
:对n
按四舍五入取整FLOOR(number)
:对n
使用截取法取整
示例:
SELECT ROUND(salary, 0) FROM employees;
SELECT MOD(salary, 10) FROM employees;
3.5.2.1.4 转换函数
用于在不同的数据类型之间进行转换的函数。
TO_CHAR(value, format)
:将数字或日期转换为字符串TO_DATE(string, format)
:将字符串转换为日期TO_NUMBER(string)
:将包含数字的字符串转换为可以执行算术操作的number
类型
示例:
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual;
SELECT TO_DATE('2023-12-25', 'YYYY-MM-DD') FROM dual;
3.5.2.1.5 其他函数
3.5.2.1.6 常规函数
NVL(exp1, exp2)
:如果exp1
为NULL
,返回exp2
,否则返回exp1
NVL2(exp1, exp2, exp3)
:如果exp1
不为NULL
,返回exp2
,否则返回exp3
NULLIF(exp1, exp2)
:如果exp1
等于exp2
,返回NULL
,否则返回exp1
COALESCE(exp1, exp2, …, expn)
:返回列表中第一个非NULL
的表达式
示例:
SELECT NVL(commission_pct, 0) FROM employees;
SELECT NVL2(commission_pct, salary * commission_pct, salary) FROM employees;
SELECT NULLIF(salary, 0) FROM employees;
SELECT COALESCE(commission_pct, salary, 0) FROM employees;
3.5.2.2 分组函数
分组函数对一组行进行计算并返回一个结果。
SUM(column)
:返回列的总和AVG(column)
:返回列的平均值MAX(column)
:返回列的最大值MIN(column)
:返回列的最小值COUNT()
COUNT(*)
:返回行数COUNT(column)
:返回指定列列中非NULL
行数COUNT(DINTINCT column)
:返回指定列不为空行和重复行的行数
3.5.2.2.1 GROUP BY
子句
用于将信息划分为较小的组,每一组行返回针对每组的单个结果行。
示例:
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;
3.5.2.2.2 HAVING
子句
用于指定针对行的某些条件,对分组后的结果按条件进行筛选。它限定组中的行,与 GROUP BY
配套使用。与 WHERE
的区别:WHERE
先按条件查询再按 GROUP BY
分组;HAVING
先分组再按条件过滤。
示例:
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000;
3.5.2.2.3 ORDER BY
子句
在使用 GROUP BY
子句进行分组时,可以在后续的 ORDER BY
子句中指定升序或降序排序。默认情况下,排序是按升序排列的。
示例:
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000;
ORDER BY AVG(salary) DESC
3.6 高级 SQL
3.6.1 子查询
子查询是嵌套在其它查询中的查询。
SELECT column1
FROM table_name
WHERE column2 = (SELECT column2 FROM another_table WHERE condition);
3.6.2 联接
联接用于根据相关列将来自两个或多个表的数据结合起来。
SELECT a.column1, b.column2
FROM table1 a
JOIN table2 b ON a.common_column = b.common_column;
3.6.3 聚合函数
聚合函数用于执行计算并返回单个值,如 SUM
, AVG
, COUNT
等。
SELECT COUNT(*), SUM(column1), AVG(column2)
FROM table_name
WHERE condition;
4 PL/SQL (Procedural Language/SQL)
DECLARE
--declarations
BEGIN
--executable statements
EXCEPTION
--handlers
END;
4.1 存储过程
存储过程是一组预编译的 SQL 语句,可通过名称调用。
CREATE PROCEDURE procedure_name
AS
BEGIN
-- SQL statements
END;
4.2 函数
函数类似于存储过程,但有一个返回值。
CREATE FUNCTION function_name
RETURN return_datatype
AS
BEGIN
-- SQL statements
RETURN value;
END;
4.3 触发器
触发器是自动执行的程序块,当特定事件发生时触发执行。
CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
-- SQL statements
END;
4.4 类型标记
%TYPE
:- 描述:
%TYPE
用于声明变量或参数的数据类型,该类型与指定数据库表中的列或变量的数据类型相同。 - 用法:
variable_name table_name.column_name%TYPE;
- 示例:
emp_salary employees.salary%TYPE;
- 作用:可以根据数据库表的结构定义变量,保持与表中列的数据类型同步,从而提高代码的可维护性和可读性。
- 描述:
%ROWTYPE
:- 描述:
%ROWTYPE
用于声明变量或参数的数据类型,该类型与指定数据库表的整行数据结构相同。 - 用法:
variable_name table_name%ROWTYPE;
- 示例:
emp_record employees%ROWTYPE;
- 作用:可以根据数据库表的结构定义变量,保持与表的整行数据结构同步,从而方便地处理整行数据的操作,如插入、更新、删除等。
- 描述:
4.5 控制流语句
4.5.1 条件控制
IF 语句:
描述:根据条件执行不同的代码块。
语法:
sqlIF condition THEN -- statements END IF;
示例:
sqlIF emp_salary > 5000 THEN bonus := 500; END IF;
IF-THEN-ELSE 语句:
描述:根据条件执行不同的代码块。
语法:
sqlIF condition THEN -- statements ELSE -- statements END IF;
示例:
sqlIF emp_salary > 5000 THEN bonus := 500; ELSE bonus := 200; END IF;
IF-THEN-ELSIF 语句:
描述:根据多个条件依次执行不同的代码块。
语法:
sqlIF condition1 THEN -- statements ELSIF condition2 THEN -- statements ELSE -- statements END IF;
示例:
sqlIF emp_salary > 5000 THEN bonus := 500; ELSIF emp_salary > 3000 THEN bonus := 300; ELSE bonus := 200; END IF;
4.5.2 迭代控制
简单循环:
描述:无限循环执行代码块。
语法:
sqlLOOP -- statements [EXIT [CONDITION]] END LOOP;
示例:
sqlLOOP total := total + 1; EXIT WHEN total > 10; END LOOP;
WHILE 循环:
描述:当条件为真时重复执行代码块。
语法:
sqlWHILE condition LOOP -- statements END LOOP;
示例:
sqlWHILE total <= 10 LOOP total := total + 1; END LOOP;
FOR 循环:
描述:按照指定次数重复执行代码块。
语法:
sqlFOR counter IN range LOOP -- statements END LOOP;
示例:
sqlFOR i IN 1. LOOP total := total + 1; END LOOP;
4.5.3 顺序控制
GOTO 语句:
描述:将控制转移到指定标签处。
语法:
sql<<label_name>> GOTO label_name;
示例:
sql<<end_loop>> LOOP EXIT WHEN total > 10; total := total + 1; IF total = 5 THEN GOTO end_loop; END IF; END LOOP end_loop;
NULL 语句:
描述:什么也不做,用于占位或注释。
语法:
sqlNULL;
示例:
sqlIF emp_salary > 5000 THEN bonus := 500; ELSE NULL; -- do nothing END IF;
5 性能调优
性能调优是指通过各种技术和方法提升数据库的运行效率,包括但不限于索引优化、查询优化和数据库配置优化。
6 备份与恢复
Oracle 提供了多种备份和恢复工具,如 RMAN 和 Data Pump,确保数据的安全性和完整性。