【搬运】oracle常用SQL语句(汇总版)

参考博客

https://www.cnblogs.com/xrhou12326/p/4094737.html

前言：
看到了一篇比较全面的oracle常用命令集合，奈何博主没有做格式看起来比较费力，故修改了样式

正文

第一部分：数据操作语句 (DML)

DML 语句用于操作表中的数据，主要包括插入、删除和更新。

1.1 插入数据 (INSERT)

-- 标准插入语句
INSERT INTO 表名(字段1, 字段2) VALUES (值1, 值2);
-- 从另一张表插入数据
INSERT INTO 表名(字段1, 字段2) SELECT (字段1, 字段2) FROM 另一张表名;

重要提示：

字符串：必须用单引号括起来。如果值中包含单引号，需要将其替换为两个单引号 ‘’。

日期：可使用系统时间 SYSDATE，或用 TO_DATE(‘2001-08-01’, ‘YYYY-MM-DD’) 函数指定。

自增序列：需要先创建 SEQUENCE，插入时使用 序列名.NEXTVAL 获取下一个值。

大数据量：操作超过上万条记录时，建议分段执行并适时 COMMIT，避免占用大量回滚段导致程序响应慢。

1.2 删除数据 (DELETE) 与截断表 (TRUNCATE)

-- 删除符合条件的记录（可回滚）
DELETE FROM 表名 WHERE 条件;
-- 清空表，释放数据块空间（不可回滚，效率高）
TRUNCATE TABLE 表名;

区别： DELETE 不会释放表空间，只是将数据块标记为未使用，且操作可以回滚。TRUNCATE 则会立即释放空间且不可回滚。

1.3 更新数据 (UPDATE)

UPDATE 表名 SET 字段1=值1, 字段2=值2 WHERE 条件;

提示： 如果更新的值为 NULL，原记录该字段将被清空，建议更新前进行非空校验。

第二部分：数据定义语句 (DDL)

DDL 语句用于创建、修改和删除数据库对象（如 表、索引、视图）。

2.1 创建表 (CREATE)

常用字段类型：

• CHAR(n)：固定长度字符串
• VARCHAR2(n)：可变长度字符串
• NUMBER(m,n)：数字，m 为总位数，n 为小数位数
• DATE：日期类型

-- 创建表示例
CREATE TABLE 表名 (
    字段1 NUMBER(6) NOT NULL,
    字段2 VARCHAR2(50),
    创建时间 DATE DEFAULT SYSDATE, -- 默认值为当前时间
    CONSTRAINT 约束名 PRIMARY KEY (字段1)
);

最佳实践： 把较小的、不为空的字段放在前面；可使用中文字段名，但推荐用英文；可以用 DEFAULT 关键字为字段设置默认值。

2.2 修改表结构 (ALTER)

-- 修改表名
ALTER TABLE 原表名 RENAME TO 新表名;
-- 添加字段
ALTER TABLE 表名 ADD (新字段名 字段描述);
-- 修改字段定义（当字段值为空时才能减小长度或改变类型）
ALTER TABLE 表名 MODIFY (字段名 新的字段描述);
-- 添加约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (字段名);
-- 将表放入/移出内存区
ALTER TABLE 表名 CACHE;   -- 放入
ALTER TABLE 表名 NOCACHE; -- 移出

2.3 删除对象 (DROP)

-- 删除表及其所有约束
DROP TABLE 表名 CASCADE CONSTRAINTS;

第三部分：查询语句 (SELECT)

查询是 SQL 中最核心、最灵活的部分。

3.1 基础查询与函数

SELECT 字段1, 字段2, ... FROM 表名 WHERE 条件;
-- 常用函数
SELECT COUNT(*), MIN(字段), MAX(字段), AVG(字段), DISTINCT(字段) FROM 表名;
-- 处理空值：若 expr1 为 NULL，返回 expr2，否则返回 expr1
SELECT NVL(expr1, expr2) FROM 表名;
-- 条件判断：类似 if-then-else
SELECT DECODE(字段, 值1, 返回1, 值2, 返回2, ...) FROM 表名;
-- 左填充：用 char2 将 char1 左填充到 n 位显示
SELECT LPAD(char1, n, char2) FROM 表名;
-- 字符串连接
SELECT '前缀' || 字段名 || '后缀' FROM 表名;

模糊查询：

-- 推荐使用 INSTR，性能较好
SELECT * FROM 表名 WHERE INSTR(字段名, '关键词') > 0;
-- 使用 LIKE
SELECT * FROM 表名 WHERE 字段名 LIKE '%关键词%';

3.2 排序、分组与集合

-- 排序：ASC 升序（默认），DESC 降序
ORDER BY 字段1, 字段2 DESC;
-- 分组统计
SELECT 字段1, COUNT(*) FROM 表名 GROUP BY 字段1 HAVING COUNT(*) > 1;
-- 集合操作
SELECT ... UNION        -- 并集（去重）
SELECT ... UNION ALL    -- 并集（不去重）
SELECT ... INTERSECT    -- 交集
SELECT ... MINUS;       -- 差集

3.3 连接查询

-- 内连接
SELECT * FROM 表1, 表2 WHERE 表1.关联字段 = 表2.关联字段;
-- 外连接（+号所在表补空值）
SELECT * FROM 表1, 表2 WHERE 表1.关联字段 = 表2.关联字段(+); -- 左外连

3.4 高级查询技巧

-- 为查询结果生成序号
SELECT ROWNUM, 字段 FROM 表名;
-- 取前 N 条记录
SELECT * FROM (SELECT 字段 FROM 表名 ORDER BY 字段) WHERE ROWNUM <= N;
-- 查询第 N 大的值（使用分析函数）
SELECT * FROM (SELECT 字段, DENSE_RANK() OVER (ORDER BY 字段 DESC) RANK FROM 表) WHERE RANK = N;

第四部分：常用数据对象 (Schema)

第五部分：权限管理 (DCL)

-- 授予系统权限
GRANT CONNECT, RESOURCE TO 用户名;
-- 授予对象权限
GRANT SELECT, INSERT ON 表名 TO 用户名;
-- 回收权限
REVOKE CONNECT, RESOURCE FROM 用户名;

常用系统权限集：

• CONNECT：基本连接权限

• RESOURCE：程序开发权限

• DBA：数据库管理权限

第六部分：实用运维查询

6.1 查看数据库状态与配置

-- 查看数据库实例
SELECT * FROM V$INSTANCE;
-- 查看当前用户
SHOW USER;
-- 查看数据库SID
SELECT NAME FROM V$DATABASE;
-- 查看最大会话/进程数
SHOW PARAMETER PROCESSES;
-- 查看当前连接的用户
SELECT USERNAME FROM V$SESSION;
-- 查看每个用户的权限
SELECT * FROM DBA_SYS_PRIVS;

6.2 表空间管理

-- 查看所有表空间信息
SELECT * FROM DBA_TABLESPACES;
-- 查看数据文件路径及大小
SELECT TABLESPACE_NAME, BYTES/1024/1024 AS SIZE_MB, FILE_NAME FROM DBA_DATA_FILES;
-- 查看表空间使用情况
SELECT B.TABLESPACE_NAME, 
       SUM(NVL(A.BYTES,0))/B.BYTES*100 AS REMAINING_PERCENT 
FROM DBA_FREE_SPACE A, DBA_DATA_FILES B
WHERE A.FILE_ID = B.FILE_ID GROUP BY B.TABLESPACE_NAME, B.BYTES;

6.3 对象与约束查询

-- 查询当前用户的所有表
SELECT * FROM USER_TABLES;
-- 查询表的主键字段
SELECT * FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE='P' AND TABLE_NAME='表名';
-- 查看表和列的注释
SELECT * FROM USER_TAB_COMMENTS WHERE COMMENTS IS NOT NULL;
-- 查看存储过程源码
SELECT LINE, TEXT FROM USER_SOURCE WHERE NAME = '过程名' ORDER BY LINE;

6.4 锁与会话管理

-- 查看被锁的对象
SELECT * FROM V$LOCKED_OBJECT;
-- 查看锁的详细信息及SQL
SELECT S.SID, S.USERNAME, L.LMODE, L.REQUEST, 
       O.OBJECT_NAME, Q.SQL_TEXT
FROM V$LOCK L, V$SESSION S, DBA_OBJECTS O, V$SQLTEXT Q
WHERE L.SID = S.SID AND L.ID1 = O.OBJECT_ID AND S.SQL_ADDRESS = Q.ADDRESS;
-- 杀掉会话（解锁）
ALTER SYSTEM KILL SESSION 'sid, serial#';

6.5 数据库维护

-- 查看数据库版本
SELECT * FROM V$VERSION;
-- 查看归档模式
ARCHIVE LOG LIST;
-- 查看当前SCN号（System Change Number）
SELECT MAX(KTUXESCNW * POWER(2, 32) + KTUXESCNB) FROM X$KTUXE;
-- 获取当前时间戳（毫秒级）
SELECT SYSTIMESTAMP FROM DUAL;
-- 查询任意SQL执行时间
SET TIMING ON;
SELECT * FROM 表名;
SET TIMING OFF;

常见日期/时间格式转换