文章

Oracle数据库基本语法

发表于
作者 deathwhispers
9 分钟阅读
Oracle数据库基本语法

Oracle 数据库基本语法

一、简单查询

1. 基本查询语法

查询所有列或指定列：

1
2
3
4
5

-- 查询所有列
SELECT * FROM 表名;

-- 查询指定列
SELECT 列名1, 列名2 FROM 表名;

2. 别名用法

可以为列名设置别名，AS 关键字可以省略。

1

SELECT job AS "工作", ename AS "姓名" FROM emp;

3. 除去重复的数据

使用 DISTINCT 关键字可以消除查询结果中的重复行。只有当每一列的值都相同时，才被认为是重复的行。

1
2

SELECT DISTINCT * FROM emp;
SELECT DISTINCT 列名 FROM emp;

4. 查询中的四则运算

SQL 查询支持四则运算。

1
2

-- 计算年薪
SELECT ename, sal * 12 FROM emp;

二、限定查询

使用 WHERE 子句对查询结果进行筛选。

1. 基本条件过滤

1
2

-- 查询工资大于1500的所有雇员
SELECT * FROM emp WHERE sal > 1500;

2. 空值判断

  • 非空：IS NOT NULL
  • 空：IS NULL
1

SELECT * FROM emp WHERE comm IS NOT NULL;

3. 多条件查询

  • AND：多个条件同时满足。
  • OR：满足其中一个条件即可。
1
2
3
4
5

-- AND 示例
SELECT * FROM emp WHERE comm IS NOT NULL AND sal > 1500;

-- OR 示例
SELECT * FROM emp WHERE comm IS NOT NULL OR sal > 1500;

4. NOT 运算符

NOT 用于对查询条件取反。

5. BETWEEN AND

用于选取介于两个值之间的数据范围。

6. 模糊查询 LIKE

LIKE 关键字用于模糊查询，支持以下通配符：

  • %：匹配任意长度的字符。
  • _：匹配单个字符。

范例1： 查询所有雇员姓名中第二个字符为 “M” 的雇员。

1

SELECT * FROM emp WHERE ename LIKE '_M%';

范例2： 查询名字中带有 “M” 的雇员。

1

SELECT * FROM emp WHERE ename LIKE '%M%';

三、对结果集排序

使用 ORDER BY 子句对查询结果进行排序。ORDER BY 应放在 SQL 语句的最后。

  • ASC：升序（默认）。
  • DESC：降序。
1
2
3

SELECT * FROM 表名
{WHERE 查询条件}
ORDER BY 列名1 [ASC|DESC], 列名2 [ASC|DESC], ...;

四、单行函数

1. 字符函数

  • UPPER('string')：将字符串转换为大写。
  • LOWER('string')：将字符串转换为小写。
  • INITCAP('string')：将字符串首字母大写。
  • CONCAT('str1', 'str2') 或 ||：连接字符串，推荐使用 ||。
  • SUBSTR('string', start, length)：截取子字符串。
  • LENGTH('string')：获取字符串长度。
  • REPLACE('string', 'search', 'replace')：替换字符串中的子串。

伪表 DUAL

DUAL 是一个虚拟表，用于执行不依赖于任何实际表的计算或函数调用。

1
2

SELECT 7 FROM dual;
SELECT '小钻风' FROM dual;

2. 数值函数

  • ROUND(n, [decimal_places])：四舍五入。
  • TRUNC(n, [decimal_places])：截断数字，向下取整。
  • MOD(m, n)：取余数。
1
2
3

SELECT ROUND(12.6455, 2) FROM dual; -- 结果: 12.65
SELECT TRUNC(12.6455, 2) FROM dual; -- 结果: 12.64
SELECT MOD(10, 3) FROM dual;      -- 结果: 1

3. 日期函数

Oracle 提供了丰富的日期函数，并支持日期加减运算：

  • 日期 - 数字 = 日期
  • 日期 + 数字 = 日期
  • 日期 - 日期 = 数字 (天数)

4. 转换函数

  • TO_CHAR(date, 'format_model')：将日期或数字转换为字符串。

五、事务

事务的特性 (ACID)

六、Oracle BLOB 转字符串

根据 BLOB 字段的长度，转换方式有所不同。

1. BLOB 长度 < 2000

1
2
3
4
5

SELECT
    ID,
    UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(clume_name))
FROM
    A;

2. BLOB 长度 > 2000 且 < 4000

1
2
3
4
5
6

SELECT
    ID,
    UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(colume_name, 2000, 1)) ||
    UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(colume_name, 2000, 2001))
FROM
    A;

3. BLOB 长度 > 4000

当 BLOB 长度超过 4000 时，不要使用拼接字段的方式，否则会出错。应分段查询。

1
2
3
4
5
6
7

SELECT
    ID,
    UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(colume_name, 2000, 1)) AS colume_name1,
    UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(colume_name, 2000, 2001)) AS colume_name2,
    UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(colume_name, 2000, 4001)) AS colume_name3
FROM
    A;

错误拼接示例：

七、Oracle 数据字典 DBA_INDEXES 字段解析

DBA_INDEXES 视图记录了数据库中所有索引的详细信息。

  • OWNER: 索引拥有者
  • INDEX_NAME: 索引名称
  • INDEX_TYPE: 索引类型
  • TABLE_OWNER: 表的拥有者
  • TABLE_NAME: 表名
  • TABLE_TYPE: 表类型
  • UNIQUENESS: 是否唯一索引
  • COMPRESSION: 是否压缩
  • PREFIX_LENGTH: 压缩键上前缀的列数量
  • TABLESPACE_NAME: 索引所在的表空间
  • INI_TRANS: 事务表的初始大小，默认为 2
  • MAX_TRANS: 最大事务条目数，默认为 255
  • INITIAL_EXTENT: 初始化区大小
  • NEXT_EXTENT: 第二个区大小
  • MIN_EXTENTS: 段中允许的最小区数量
  • MAX_EXTENTS: 段中允许的最大区数量
  • PCT_INCREASE: 后续区相对于前一个区的增长百分比
  • PCT_THRESHOLD: 每个块中允许索引条目的百分比阈值
  • INCLUDE_COLUMN: 索引组织表主键索引中包含的最后一列的列 ID
  • FREELISTS: 分配给此段的进程自由列表数量
  • FREELIST_GROUPS: 分配给此段的进程自由列表组的数量
  • PCT_FREE: 块中为未来更新保留的最小自由空间百分比
  • LOGGING: 索引变更是否记录到日志
  • BLEVEL: B-Tree 索引的层级（从根块到叶子块的深度）
  • LEAF_BLOCKS: 索引中的叶子块数量
  • DISTINCT_KEYS: 不同索引值的数量
  • AVG_LEAF_BLOCKS_PER_KEY: 每个索引值平均占用的叶子块数（唯一索引为 1）
  • AVG_DATA_BLOCKS_PER_KEY: 通过索引中的一个值指向的数据块的平均数量
  • CLUSTERING_FACTOR: 聚集因子，表示表中行基于索引的排序程度
  • STATUS: 索引状态（VALID 或 UNUSABLE）
  • SAMPLE_SIZE: 用于分析索引的样本大小
  • LAST_ANALYZED: 最近一次分析索引统计信息的日期
  • DEGREE: 每个实例扫描索引的线程数
  • INSTANCES: 索引被多少实例扫描
  • PARTITIONED: 索引是否为分区索引
  • TEMPORARY: 索引是否在临时表上
  • GENERATED: 索引名称是否由系统生成
  • SECONDARY: 索引是否通过 ODCIIndexCreate 方法创建
  • BUFFER_POOL: 用于索引块的缓冲池
  • FLASH_CACHE: 用于索引块的数据库智能闪存缓存提示
  • CELL_FLASH_CACHE: 用于索引块的 CELL_FLASH_CACHE 提示
  • USER_STATS: 统计信息是否由用户直接提供
  • DURATION: 临时表空间的持续时间
  • PCT_DIRECT_ACCESS: 对于索引组织表上的二级索引，行访问的百分比猜测
  • ITYP_OWNER: 域索引类型的拥有者
  • ITYP_NAME: 域索引类型的名称
  • PARAMETERS: 域索引的参数字符串
  • GLOBAL_STATS: 索引的统计信息是否已全局收集
  • DOMIDX_STATUS: 域索引的状态
  • DOMIDX_OPSTATUS: 域索引的操作状态
  • FUNCIDX_STATUS: 基于函数的索引的状态
  • JOIN_INDEX: 索引是否为连接索引
  • IOT_REDUNDANT_PKEY_ELIM: 在索引组织表中，冗余的主键列是否已从二级索引中删除
  • DROPPED: 索引是否已被删除并位于回收站中
  • VISIBILITY: 索引是否可见
  • DOMIDX_MANAGEMENT: 如果是域索引，主索引是系统管理还是用户管理
  • SEGMENT_CREATED: 索引段是否已创建
Database
Oracle
本文由作者按照 CC BY 4.0 进行授权