Oracle数据库学习上（SQL语句）

Oracle数据库SQL语句

笔记信息：

作者：彭相瑜
创建日期：20261001

表相关修改

创建表空间

create tablespace waterboss
datafile 'C:\OracleDBRepo\waterbossdb.dbf'
size 100m
autoextend on
next 10m

创建用户

create user wateruser
    identified by abc123
    default tablespace WATERBOSS

赋予用户权限

grant dba to waterusr

创建表

create table T_OWNERS(
    ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(30),
    ADDRESSID NUMBER,
    HOUSENUM VARCHAR2(30),
    WATERMETER VARCHAR2(30),
    ADDDATE DATE,
    OWNERTYPEID NUMBER
)

增加字段

ALTER TABLE 表名称 ADD(列名1 类型 [DEFAULT 默认值], 列名2 类型 [DEFAULT 默认值])

追加字段

ALTER TABLE T_OWNERS ADD
(
    REMARK VARCHAR2(20),
    OUTDATE DATE
)

修改字段（属性）

ALTER TABLE 表名 MODIFY(列名1 类型[DEFAULT 默认值] , 列名2 类型[DEFAULT 默认值] ...)

修改字段名

ALTER TABLE 表名 RENAME COLUMN 原列名 TO 新列名

删除字段

ALTER TABLE 表名称 DROP COLUMN 列名1,列名2

删除表

DROP TABLE 表名称

数据相关修改

插入数据

（执行完 INSERT 后要 Commit）

INSERT INTO 表名称[(列名1,列名2,...)] VALUES(值1，值2,...)

修改数据

UPDATE 表名 SET 列名1=值1,列名2=值2,... WHERE 修改条件

删除数据

DELETE FROM 表名 WHERE 删除条件

删除表

（DELETE 的数据可以 ROLLBACK；DELETE 删除的数据可能产生碎片，且不释放空间；TRUNCATE 是先摧毁表结构，再重构表结构）

TRUNCATE TABLE 表名称

数据导入与导出操作

整库导出

exp system/databasename full=y

整库导入

（当某一对象存在时会自动跳过）

imp system/databasename full=y

单表查询

精确查询

SELECT * FROM TableName WHERE var=val

模糊查询

SELECT * FROM TableName WHERE var LIKE '%刘%'

AND运算符

SELECT * FROM TableName WHERE var LIKE '%刘%' AND var2 like '%5%'

OR运算符

SELECT * FROM TableName WHERE var LIKE '%刘%' OR var2 like '%5%'

SELECT * FROM TableName WHERE (var LIKE '%刘%' OR var2 like '%5%') AND var3 like val3

范围查询

SELECT * FROM TableName WHERE var>=num1 AND var2<=num2

SELECT * FROM TableName WHERE var BETWEEN num1 AND num2

空值查询

SELECT * FROM TableName WHERE var IS NULL

SELECT * FROM TableName WHERE var IS NOT NULL

跳过重复记录查询

SELECT DISTINCT ColumnName FROM TabelName

排序查询

升序查询

SELECT * FROM TableName ORDER BY var

降序查询

SELECT * FROM TableName ORDER BY var DESC

基于伪列的查询

ROWID（行ID）

SELECT ROWIDVal,t.* FROM TableName

ROWNUM

SELECT ROWNum,t.* FROM TableName t

聚合统计

求和SUM

（对查询到的 usenum 值进行求和计算）

SELECT SUM(usenum) FROM TableName WHERE var=val

求平均值AVG

SELECT AVG(usenum) FROM TableName WHERE var=val

求最大值MAX

SELECT MAX(usenum) FROM TableName WHERE var=val

求最小值MIN

SELECT MIN(usenum) FROM TableName WHERE var=val

统计记录数

SELECT COUNT(*) FROM TableName WHERE var=val

分组聚合统计

SELECT areaid,sum(var) FROM TableName GROUP BY areaid

分组后条件查询HAVING

SELECT areaid,SUM(var) FROM TableName GROUP BY areaid HAVING SUM(var)>1260

多表连接查询

多表内连接查询

SELECT o.id val,o.name val2,ot.name val3 FROM Table1Name o,Table2Name ot WHERE o.val=ot.val3

左外连接查询

SQL1999:

SELECT tid.var1,tid.var2,var3,var4,var5 FROM TableName tid LEFT JOIN TableName2 tid2 WHERE tid.var1=tid2.var2

ORACLE:

SELECT tid.var1,tid.var2,var3,var4,var5 FROM TableName tid,TableName2 tid2 WHERE tid.var1=tid2.var2(+)

右外连接查询

SQL1999:

SELECT tid.var1,tid.var2,var3,var4,var5 FROM TableName tid RIGHT JOIN TableName2 tid2 WHERE tid.var1=tid2.var2

ORACLE:

SELECT tid.var1,tid.var2,var3,var4,var5 FROM TableName tid,TableName2 tid2 WHERE tid.var1(+)=tid2.var2

子查询（嵌套查询）

WHERE子句中的子查询（嵌套查询）

单行子查询

只返回一条记录
单行操作符

操作符含义

= Equal to

> Greater than

>= Greater than or equal to

< Less than

<= Less than or equal to

<> Not equal to


操作符	含义
=	Equal to
>	Greater than
>=	Greater than or equal to
<	Less than
<=	Less than or equal to
<>	Not equal to

SELECT * FROM TableName WHERE var=val AND var2=val2 AND var3>(SELECT avg(var3) FROM TableName WHERE var=val)

多行子查询

返回了多条记录
多行操作符

操作符含义

IN/NOT IN 等于（不等于）列表中的任何一个

ANY 和子查询返回的任意一个值比较

ALL 和子查询返回的所有值比较


操作符	含义
IN/NOT IN	等于（不等于）列表中的任何一个
ANY	和子查询返回的任意一个值比较
ALL	和子查询返回的所有值比较

SELECT * FROM TableName WHERE var IN (val1,val2,val3)

相同作用语句：

SELECT * FROM TableName WHERE var=val1 OR var=val2 OR var=val3

FROM子句中的子查询

SELECT * FROM (SELECT tid.var val,tid.var2 val2,tid2.var3 val3 FROM TableName tid,TableName2 tid2 WHERE tid.var4=tid2.var5) WHERE var6='val6'

SELECT子句中的子查询

SELECT var,var2,(SELECT var2 FROM TableName WHERE var=val) val2 FROM TableName2

分页查询

简单分页

此处ROWNUM后的运算符只能是'<'或者'<='

SELECT ROWNUM,t.* FROM TableName t WHERE ROWNUM<10

使用范围确定ROWNUM时可以用子查询

SELECT * FROM (SELECT ROWNUM r,tid.* FROM TableName tid) WHERE r<=20 AND r>10

基于排序的分页

普通排序：

SELECT * FROM TableName ORDER BY var DESC

为防止 ROWNUM 生成时机冲突，需要嵌套子查询

SELECT * FROM (SELECT ROWNUM r,tid.* FROM (SELECT * FROM TableName tid ORDER BY var DESC)) WHERE r<=20 AND r>10

函数

单行函数

字符函数


函数	说明
ASCII	返回对应字符的ASCII值（十进制）
CHR	给出某十进制值的ASCII字符
CONCAT	拼接两个字符串，与
INITCAT	将字符串的第一个字母转换为大写
INSTR	找出某个字符串的位置
INSTRB	找出某个字符串的位置和字节数
LENGTH	返回字符串的字符个数
LENGTHB	返回字符串的字节数
LOWER	将字符串转换为小写
LPAD	使用指定的字符在字符左边填充
LTRIM	在左边裁剪指定的字符
RPAD	使用指定的字符在字符右边填充
RTRIM	在右边裁剪指定的字符
REPLACE	执行字符串搜索和替换
SUBSTR	取字符串的子串
SUBSTRB	取字符串的子串（二进制）
SOUNDEX	返回一个同音字符串
TRANSLATE	执行字符串搜索和替换
TRIM	裁剪掉前面或者后面的字符串
UPPER	将字符串转换为小写
DUAL伪表

SELECT LENGTH('ABCD') FROM dual

字符串拼接

SELECT 'abc'||'def' FROM dual

数值计算函数

Oracle 数值计算 SQL 函数整理

本文系统整理 Oracle 数据库中常用的数值计算 SQL 函数，涵盖算术运算、数学函数、取整函数、随机数、进制转换及聚合统计等类别，并附用法说明与示例。

1. 基本算术运算

Oracle 支持标准的四则运算及取模运算。


运算符	说明	示例	结果
`+`	加法	`SELECT 5 + 3 FROM dual;`	`8`
`-`	减法	`SELECT 5 - 3 FROM dual;`	`2`
`*`	乘法	`SELECT 5 * 3 FROM dual;`	`15`
`/`	除法	`SELECT 5 / 2 FROM dual;`	`2.5`
`MOD(n, m)`	取模（求余数）	`SELECT MOD(5, 2) FROM dual;`	`1`

注意：MOD 结果的正负号取决于 n 的符号。除数 m 为 0 时返回 n（不报错）。

-- 示例
SELECT 10 + 5 AS 加,
       10 - 5 AS 减,
       10 * 5 AS 乘,
       10 / 4 AS 除,
       MOD(10, 3) AS 取模
FROM dual;

2. 数值处理函数

2.1 绝对值 ABS

返回数字的绝对值。

SELECT ABS(-15) FROM dual;  -- 结果：15
SELECT ABS(15.6) FROM dual; -- 结果：15.6

2.2 符号 SIGN

返回数字的符号：-1（负数）、0（零）、1（正数）。

SELECT SIGN(-15), SIGN(0), SIGN(15) FROM dual;
-- 结果：-1, 0, 1

2.3 平方根 SQRT

返回数字的平方根。参数为负数时返回 NaN（非数值）。

SELECT SQRT(16) FROM dual;  -- 结果：4
SELECT SQRT(2)  FROM dual;  -- 结果：1.4142135623730951

2.4 幂运算 POWER

返回 n 的 m 次幂。如果 n 为负数，m 必须为整数。

SELECT POWER(2, 10) FROM dual;  -- 结果：1024
SELECT POWER(2, -2) FROM dual;  -- 结果：0.25

2.5 指数 EXP

返回 e 的 n 次幂（e ≈ 2.718281828）。

SELECT EXP(1) FROM dual;  -- 结果：2.718281828459045

2.6 自然对数 LN

返回以 e 为底的对数，参数必须大于 0。

SELECT LN(2.718281828) FROM dual;  -- 结果：约 1

2.7 常用对数 LOG

返回以 n1 为底、n2 的对数。

SELECT LOG(10, 100) FROM dual;  -- 结果：2
SELECT LOG(2, 8)    FROM dual;  -- 结果：3

3. 数学与三角函数

三角函数参数以弧度为单位，需用 RADIANS 或常量换算。

3.1 正弦 SIN / 余弦 COS / 正切 TAN

SELECT SIN(0), COS(0), TAN(0) FROM dual;
-- 结果：0, 1, 0

-- 角度转弧度后计算（30° 的正弦）
SELECT SIN(30 * 3.14159265 / 180) FROM dual;  -- 结果：约 0.5

3.2 反正弦 ASIN / 反余弦 ACOS / 反正切 ATAN

SELECT ASIN(0.5) FROM dual;  -- 结果：约 0.5235987756（弧度）
SELECT ATAN(1)   FROM dual;  -- 结果：约 0.7853981634（弧度）

3.3 双参数反正切 ATAN2

返回 n1/n2 的反正切，可正确处理象限。

SELECT ATAN2(1, 1) FROM dual;  -- 结果：约 0.7853981634

3.4 双曲函数 SINH / COSH / TANH

SELECT SINH(0), COSH(0), TANH(0) FROM dual;
-- 结果：0, 1, 0

3.5 角度与弧度转换

Oracle 未内置 DEGREES / RADIANS 函数，可通过公式换算：

-- 弧度转角度
SELECT (ASIN(0.5) * 180 / 3.14159265) AS 角度 FROM dual;  -- 约 30°

-- 角度转弧度
SELECT (30 * 3.14159265 / 180) AS 弧度 FROM dual;          -- 约 0.5236

4. 取整与舍入函数

4.1 四舍五入 ROUND

SELECT ROUND(15.193, 1) FROM dual;  -- 结果：15.2
SELECT ROUND(15.193, 0) FROM dual;  -- 结果：15
SELECT ROUND(15.193, -1) FROM dual; -- 结果：20（对十位取整）

第二个参数 d 可为负数，表示对小数点左侧进行舍入。

4.2 截断 TRUNC

直接截断，不做四舍五入。

SELECT TRUNC(15.79, 1)  FROM dual;  -- 结果：15.7
SELECT TRUNC(15.79)     FROM dual;  -- 结果：15
SELECT TRUNC(15.79, -1) FROM dual;  -- 结果：10

4.3 向上取整 CEIL / 向下取整 FLOOR

SELECT CEIL(15.1)  FROM dual;  -- 结果：16
SELECT FLOOR(15.9) FROM dual;  -- 结果：15
SELECT CEIL(-15.1) FROM dual;  -- 结果：-15
SELECT FLOOR(-15.9) FROM dual; -- 结果：-16

4.4 保留 N 位有效数字 ROUND（配合负精度）

ROUND 与 TRUNC 也可通过负数精度实现到十位、百位的舍入 / 截断（见 4.1、4.2 示例）。

5. 随机数函数

Oracle 无 RAND()，使用包 DBMS_RANDOM。

5.1 生成 [0,1) 之间的随机数

SELECT DBMS_RANDOM.VALUE FROM dual;  -- 结果：0 到 1 之间的随机小数

5.2 生成指定范围的随机数

-- 生成 [low, high) 之间的随机数
SELECT DBMS_RANDOM.VALUE(1, 100) FROM dual;

-- 生成随机整数
SELECT TRUNC(DBMS_RANDOM.VALUE(1, 101)) FROM dual;  -- 1 ~ 100 的整数

5.3 生成随机字符串

SELECT DBMS_RANDOM.STRING('U', 10) FROM dual;  -- 10 位大写字母
SELECT DBMS_RANDOM.STRING('A', 10) FROM dual;  -- 10 位大小写混合
SELECT DBMS_RANDOM.STRING('X', 10) FROM dual;  -- 10 位大写字母+数字
SELECT DBMS_RANDOM.STRING('P', 10) FROM dual;  -- 10 位可打印字符

5.4 设置随机种子（保证可复现）

BEGIN
  DBMS_RANDOM.SEED(123456);
END;
/

6. 进制与编码转换

6.1 十进制转十六进制 TO_CHAR

SELECT TO_CHAR(255, 'XXXXXXXX') FROM dual;  -- 结果：FF（十六进制）
SELECT TO_CHAR(255, '0XXXXXXX') FROM dual; -- 结果：00000FF（补零）

6.2 十六进制转十进制 TO_NUMBER

SELECT TO_NUMBER('FF', 'XXXXXXXX') FROM dual;  -- 结果：255

6.3 二进制转十进制（Oracle 不直接支持）

Oracle 未内置二进制转换函数，可通过 RAW 类型或自定义函数实现。以下为常见方案：

-- 通过 RAW 实现十六进制字符串与 RAW 互转
SELECT HEXTORAW('FF') FROM dual;   -- 十六进制字符串转 RAW
SELECT RAWTOHEX('abc') FROM dual;  -- 字符串转十六进制

说明：二进制（BASE 2）转换通常借助 UTL_RAW 包或编写自定义 PL/SQL 函数完成。

7. 数值聚合统计函数


函数	说明	示例
`SUM(col)`	求和，忽略 NULL	`SELECT SUM(salary) FROM emp;`
`AVG(col)`	平均值，忽略 NULL	`SELECT AVG(salary) FROM emp;`
`COUNT(col)`	非空计数	`SELECT COUNT(comm) FROM emp;`
`COUNT(*)`	总行数（含 NULL）	`SELECT COUNT(*) FROM emp;`
`MAX(col)`	最大值	`SELECT MAX(salary) FROM emp;`
`MIN(col)`	最小值	`SELECT MIN(salary) FROM emp;`
`STDDEV(col)`	标准差	`SELECT STDDEV(salary) FROM emp;`
`VARIANCE(col)`	方差	`SELECT VARIANCE(salary) FROM emp;`
`MEDIAN(col)`	中位数（Oracle 10g+）	`SELECT MEDIAN(salary) FROM emp;`
`STATS_MODE(col)`	众数（出现最频繁的值）	`SELECT STATS_MODE(salary) FROM emp;`

-- 综合示例
SELECT SUM(salary)     AS 总和,
       AVG(salary)     AS 平均,
       MAX(salary)     AS 最高,
       MIN(salary)     AS 最低,
       STDDEV(salary)  AS 标准差,
       MEDIAN(salary)  AS 中位数
FROM emp;

8. 数值类型转换

8.1 转数字 TO_NUMBER

SELECT TO_NUMBER('123.45') FROM dual;        -- 结果：123.45
SELECT TO_NUMBER('1,234.5', '9,999.9') FROM dual;  -- 带格式转换

8.2 转字符 TO_CHAR（数值格式化）

SELECT TO_CHAR(1234.5, '9,999.99') FROM dual;   -- 结果：1,234.50
SELECT TO_CHAR(1234.5, '$9,999.00') FROM dual;  -- 结果：$1,234.50
SELECT TO_CHAR(0.123, '0.00') FROM dual;        -- 结果：0.12
SELECT TO_CHAR(1234567, '9.99EEEE') FROM dual;  -- 科学计数法

8.3 二进制浮点数转十进制浮点数

-- BINARY_FLOAT / BINARY_DOUBLE 类型支持特殊值
SELECT CAST(1 AS BINARY_FLOAT) FROM dual;
SELECT CAST(1 AS BINARY_DOUBLE) FROM dual;

9. 分析函数（窗口函数）

数值分析函数用于在分组内进行排名、累计、移动平均等计算。

9.1 排名 RANK / DENSE_RANK / ROW_NUMBER

SELECT empno, salary,
       RANK()       OVER (ORDER BY salary DESC) AS rk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dr,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM emp;


函数	说明
`RANK`	相同值排名相同，下一名次跳号（1,2,2,4）
`DENSE_RANK`	相同值排名相同，名次连续（1,2,2,3）
`ROW_NUMBER`	唯一行号，即使值相同也连续编号

9.2 累计与移动平均

-- 累计求和
SELECT empno, salary,
       SUM(salary) OVER (ORDER BY empno) AS 累计工资
FROM emp;

-- 移动平均（当前行前后各 1 行共 3 行）
SELECT empno, salary,
       AVG(salary) OVER (ORDER BY empno ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS 移动平均
FROM emp;

9.3 分组内占比 RATIO_TO_REPORT

-- 计算每人工资占部门总工资的比例
SELECT empno, deptno, salary,
       RATIO_TO_REPORT(salary) OVER (PARTITION BY deptno) AS 占比
FROM emp;

9.4 分组内前后值 LAG / LEAD

-- 取上一条 / 下一条记录的值
SELECT empno, salary,
       LAG(salary, 1)  OVER (ORDER BY empno) AS 上一条,
       LEAD(salary, 1) OVER (ORDER BY empno) AS 下一条
FROM emp;

附录：常用函数速查表


函数	功能	返回类型
`ABS(n)`	绝对值	NUMBER
`SIGN(n)`	符号	NUMBER
`SQRT(n)`	平方根	NUMBER
`POWER(n, m)`	n 的 m 次幂	NUMBER
`EXP(n)`	e 的 n 次幂	NUMBER
`LN(n)`	自然对数	NUMBER
`LOG(n1, n2)`	以 n1 为底的对数	NUMBER
`MOD(n, m)`	取模	NUMBER
`ROUND(n, d)`	四舍五入	NUMBER
`TRUNC(n, d)`	截断	NUMBER
`CEIL(n)`	向上取整	NUMBER
`FLOOR(n)`	向下取整	NUMBER
`SIN / COS / TAN`	三角函数	NUMBER
`ASIN / ACOS / ATAN`	反三角函数	NUMBER
`SINH / COSH / TANH`	双曲函数	NUMBER
`TO_NUMBER`	字符串转数字	NUMBER
`TO_CHAR`	数字转字符串	VARCHAR2
`SUM / AVG / COUNT`	聚合	NUMBER
`MAX / MIN`	极值	NUMBER
`STDDEV / VARIANCE`	标准差 / 方差	NUMBER
`MEDIAN`	中位数	NUMBER
`STATS_MODE`	众数	NUMBER
`DBMS_RANDOM.VALUE`	随机数	NUMBER

提示：
所有数学函数在 dual 表上可直接测试。
聚合函数需配合 GROUP BY 使用，分析函数需配合 OVER 子句使用。
涉及大数值或精确计算的场景，建议使用 NUMBER 类型，避免浮点精度损失。

日期函数

1. 系统日期与时间

1.1 当前日期时间 SYSDATE

返回数据库服务器的当前日期和时间（精确到秒）。

SELECT SYSDATE FROM dual;  -- 结果：2026-10-18 20:14:32

1.2 当前时间戳 SYSTIMESTAMP

返回带时区的当前时间戳（精确到小数秒）。

SELECT SYSTIMESTAMP FROM dual;
-- 结果：2026-10-18 20:14:32.123456 +08:00

1.3 会话时区当前时间 CURRENT_DATE / CURRENT_TIMESTAMP

返回会话时区（而非服务器时区）下的当前日期时间。

SELECT CURRENT_DATE FROM dual;
SELECT CURRENT_TIMESTAMP FROM dual;
SELECT SESSIONTIMEZONE FROM dual;  -- 查看会话时区

1.4 数据库时区 DBTIMEZONE

返回数据库所在时区。

SELECT DBTIMEZONE FROM dual;  -- 结果：+00:00 或 +08:00 等

2. 日期与字符串转换

2.1 字符串转日期 TO_DATE

按指定格式将字符串转换为 DATE 类型。

SELECT TO_DATE('2026-10-18', 'YYYY-MM-DD') FROM dual;
SELECT TO_DATE('2026/10/18 20:14:32', 'YYYY/MM/DD HH24:MI:SS') FROM dual;

2.2 日期转字符串 TO_CHAR

按指定格式将日期转换为字符串。

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual;              -- 2026-10-18
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;   -- 2026-10-18 20:14:32
SELECT TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日"') FROM dual;     -- 2026年10月18日

2.3 字符串转时间戳 TO_TIMESTAMP

将字符串转换为 TIMESTAMP 类型。

SELECT TO_TIMESTAMP('2026-10-18 20:14:32.123456', 'YYYY-MM-DD HH24:MI:SS.FF') FROM dual;

2.4 字符串转带时区时间戳 TO_TIMESTAMP_TZ

SELECT TO_TIMESTAMP_TZ('2026-10-18 20:14:32 +08:00',
       'YYYY-MM-DD HH24:MI:SS TZH:TZM') FROM dual;

3. 日期运算与差值

3.1 日期加减天数

DATE 类型可直接与数字做加减（单位：天）。

SELECT SYSDATE + 1    FROM dual;  -- 明天
SELECT SYSDATE - 1    FROM dual;  -- 昨天
SELECT SYSDATE + 1/24 FROM dual;  -- 1 小时后（1/24 天）
SELECT SYSDATE + 30   FROM dual;  -- 30 天后

3.2 日期相减求天数差

两个日期相减返回相差的天数（可为小数）。

SELECT TO_DATE('2026-10-18','YYYY-MM-DD') - TO_DATE('2026-01-01','YYYY-MM-DD') AS 相差天数
FROM dual;  -- 结果：290

3.3 月份加减 ADD_MONTHS

在指定日期上加减 N 个月，自动处理月末。

SELECT ADD_MONTHS(SYSDATE, 1)  FROM dual;  -- 下个月
SELECT ADD_MONTHS(SYSDATE, -1) FROM dual;  -- 上个月
SELECT ADD_MONTHS(TO_DATE('2026-01-31','YYYY-MM-DD'), 1) FROM dual;
-- 结果：2026-02-28（自动调整为当月最后一天）

3.4 月份差 MONTHS_BETWEEN

返回两个日期之间相差的月数（可为小数）。

SELECT MONTHS_BETWEEN(TO_DATE('2026-10-18','YYYY-MM-DD'),
                      TO_DATE('2026-01-18','YYYY-MM-DD')) FROM dual;
-- 结果：9

4. 日期截取与四舍五入

4.1 日期截取 TRUNC

按指定精度截断日期（默认到天）。

SELECT TRUNC(SYSDATE)          FROM dual;  -- 截断到当天 00:00:00
SELECT TRUNC(SYSDATE, 'MM')    FROM dual;  -- 本月第一天
SELECT TRUNC(SYSDATE, 'YYYY')  FROM dual;  -- 本年第一天
SELECT TRUNC(SYSDATE, 'HH')    FROM dual;  -- 当前小时开始

4.2 日期四舍五入 ROUND

按指定精度对日期进行四舍五入。

SELECT ROUND(SYSDATE, 'MM')   FROM dual;  -- 月中之后则舍入到下月第一天
SELECT ROUND(SYSDATE, 'YYYY') FROM dual;  -- 7 月之后舍入到下一年

5. 日期分量提取

5.1 提取分量 EXTRACT

从日期 / 时间戳中提取年、月、日、时、分、秒等分量。

SELECT EXTRACT(YEAR   FROM SYSDATE) FROM dual;  -- 年
SELECT EXTRACT(MONTH  FROM SYSDATE) FROM dual;  -- 月
SELECT EXTRACT(DAY    FROM SYSDATE) FROM dual;  -- 日
SELECT EXTRACT(HOUR   FROM SYSTIMESTAMP) FROM dual;  -- 时
SELECT EXTRACT(MINUTE FROM SYSTIMESTAMP) FROM dual;  -- 分
SELECT EXTRACT(SECOND FROM SYSTIMESTAMP) FROM dual;  -- 秒

5.2 星期几 TO_CHAR 的 D 与 DAY

SELECT TO_CHAR(SYSDATE, 'D')   FROM dual;  -- 一周中的第几天（1=周日）
SELECT TO_CHAR(SYSDATE, 'DAY') FROM dual;  -- 星期名称，如 SUNDAY
SELECT TO_CHAR(SYSDATE, 'DY')  FROM dual;  -- 星期缩写，如 SUN

5.3 一年中的第几天 / 第几周

SELECT TO_CHAR(SYSDATE, 'DDD') FROM dual;  -- 一年中的第几天（1-366）
SELECT TO_CHAR(SYSDATE, 'WW')  FROM dual;  -- 一年中的第几周
SELECT TO_CHAR(SYSDATE, 'IW')  FROM dual;  -- ISO 标准第几周

6. 月份处理函数

6.1 当月最后一天 LAST_DAY

返回指定日期所在月份的最后一天。

SELECT LAST_DAY(SYSDATE) FROM dual;  -- 本月最后一天
SELECT LAST_DAY(TO_DATE('2026-02-10','YYYY-MM-DD')) FROM dual;
-- 结果：2026-02-28

6.2 下一个指定星期几 NEXT_DAY

返回指定日期之后第一个指定星期几的日期。

SELECT NEXT_DAY(SYSDATE, 'MONDAY') FROM dual;  -- 下一个周一
SELECT NEXT_DAY(SYSDATE, 2)        FROM dual;  -- 用数字表示星期（1=周日）

7. 时区与时间戳

7.1 时间戳类型


类型	说明
`DATE`	精确到秒
`TIMESTAMP`	精确到小数秒（默认 6 位）
`TIMESTAMP WITH TIME ZONE`	带时区偏移
`TIMESTAMP WITH LOCAL TIME ZONE`	带时区，自动按会话时区显示

7.2 本地时区转换 LOCALTIMESTAMP

返回当前会话时区的时间戳（无时区信息）。

SELECT LOCALTIMESTAMP FROM dual;

7.3 时区名称 SESSIONTIMEZONE / DBTIMEZONE

SELECT SESSIONTIMEZONE FROM dual;  -- 会话时区，如 +08:00
SELECT DBTIMEZONE     FROM dual;   -- 数据库时区

8. 时间间隔 INTERVAL

8.1 年到月间隔 INTERVAL YEAR TO MONTH

SELECT INTERVAL '1-6' YEAR TO MONTH FROM dual;  -- 1 年 6 个月
SELECT SYSDATE + INTERVAL '1' YEAR FROM dual;   -- 1 年后

8.2 天到秒间隔 INTERVAL DAY TO SECOND

SELECT INTERVAL '2 10:30:00' DAY TO SECOND FROM dual;  -- 2 天 10 小时 30 分
SELECT SYSTIMESTAMP + INTERVAL '1 02:00:00' DAY TO SECOND FROM dual;  -- 1 天 2 小时后

8.3 间隔运算与提取

-- 间隔与日期相加
SELECT SYSDATE + INTERVAL '90' MINUTE FROM dual;  -- 90 分钟后

-- 从间隔中提取分量
SELECT EXTRACT(DAY FROM INTERVAL '5 04:30:00' DAY TO SECOND) FROM dual;  -- 5

9. 会话与全球化参数

9.1 日期显示格式 NLS_DATE_FORMAT

控制日期默认显示格式。

-- 查看当前日期格式
SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT';

-- 修改会话日期格式
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

9.2 语言与地区 NLS_LANGUAGE / NLS_TERRITORY

SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_LANGUAGE';
SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_TERRITORY';

9.3 常用日期格式符速查


格式符	说明	示例
`YYYY` / `YY`	四位 / 两位年份	2026 / 26
`MM`	两位月份	10
`MON` / `MONTH`	月份缩写 / 全称	OCT / OCTOBER
`DD`	两位日期	18
`DDD`	一年中第几天	291
`HH24` / `HH`	24 小时制 / 12 小时制	20 / 08
`MI`	分钟	14
`SS`	秒	32
`FF`	小数秒	123456
`DAY` / `DY`	星期全称 / 缩写	SUNDAY / SUN
`WW` / `IW`	第几周 / ISO 周	42
`Q`	季度	4
`AM` / `PM`	上午 / 下午	PM

附录：常用日期函数速查表


函数	功能	返回类型
`SYSDATE`	当前日期时间	DATE
`SYSTIMESTAMP`	当前时间戳（带时区）	TIMESTAMP WITH TZ
`CURRENT_DATE`	会话时区当前日期	DATE
`CURRENT_TIMESTAMP`	会话时区当前时间戳	TIMESTAMP WITH TZ
`LOCALTIMESTAMP`	本地时间戳	TIMESTAMP
`TO_DATE(str, fmt)`	字符串转日期	DATE
`TO_CHAR(date, fmt)`	日期转字符串	VARCHAR2
`TO_TIMESTAMP(str, fmt)`	字符串转时间戳	TIMESTAMP
`ADD_MONTHS(d, n)`	月份加减	DATE
`MONTHS_BETWEEN(d1, d2)`	月份差	NUMBER
`LAST_DAY(d)`	当月最后一天	DATE
`NEXT_DAY(d, w)`	下一个指定星期几	DATE
`TRUNC(d, fmt)`	日期截断	DATE
`ROUND(d, fmt)`	日期四舍五入	DATE
`EXTRACT(part FROM d)`	提取日期分量	NUMBER
`DBTIMEZONE`	数据库时区	VARCHAR2
`SESSIONTIMEZONE`	会话时区	VARCHAR2

提示：
日期格式串大小写敏感：MM 是月份，MI 是分钟，勿混淆。
TO_DATE 建议显式指定格式，避免依赖默认 NLS_DATE_FORMAT 导致解析错误。
日期相减得到天数（NUMBER），时间戳相减得到 INTERVAL。
涉及跨时区业务时，优先使用 TIMESTAMP WITH TIME ZONE 类型。

转换函数

1. 转换函数概述

Oracle 中的数据类型主要分为：字符（CHAR/VARCHAR2/CLOB）、数值（NUMBER/BINARY_FLOAT）、日期时间（DATE/TIMESTAMP）、二进制（RAW/BLOB）等。转换函数用于在这些类型之间进行相互转换。


源类型 → 目标类型	使用的函数
字符 → 数值	`TO_NUMBER`
数值 → 字符	`TO_CHAR`
字符 → 日期	`TO_DATE`、`TO_TIMESTAMP`
日期 → 字符	`TO_CHAR`
任意 → 任意（标准）	`CAST`
字符集间转换	`CONVERT`

2. 字符串转数值 TO_NUMBER

2.1 基本用法

SELECT TO_NUMBER('12345')    FROM dual;  -- 结果：12345
SELECT TO_NUMBER('123.45')   FROM dual;  -- 结果：123.45
SELECT TO_NUMBER('-123.45')  FROM dual;  -- 结果：-123.45
SELECT TO_NUMBER('0.5')      FROM dual;  -- 结果：0.5

2.2 带格式模型的转换

-- 千分位分隔符
SELECT TO_NUMBER('1,234.56', '9,999.99') FROM dual;  -- 结果：1234.56

-- 货币符号
SELECT TO_NUMBER('$1,234.56', '$9,999.99') FROM dual;  -- 结果：1234.56

-- 科学计数法
SELECT TO_NUMBER('1.5E3', '9.9EEEE') FROM dual;  -- 结果：1500

2.3 格式模型符号说明


符号	含义	示例格式
`9`	数字位（可缺省）	`9999`
`0`	数字位（补零）	`0000`
`.`	小数点	`9999.99`
`,`	千分位	`9,999`
`$`	美元符号	`$9999`
`L`	本地货币符号	`L9999`
`EEEE`	科学计数法	`9.9EEEE`

3. 数值转字符串 TO_CHAR

3.1 基本用法

SELECT TO_CHAR(12345)       FROM dual;  -- 结果：12345
SELECT TO_CHAR(12345.678)   FROM dual;  -- 结果：12345.678
SELECT TO_CHAR(-123.45)     FROM dual;  -- 结果：-123.45

3.2 常用格式化

-- 千分位
SELECT TO_CHAR(1234567.89, '9,999,999.99') FROM dual;  -- 1,234,567.89

-- 保留两位小数
SELECT TO_CHAR(123.4, '999.00') FROM dual;             -- 123.40

-- 货币
SELECT TO_CHAR(1234.5, '$9,999.00') FROM dual;         -- $1,234.50
SELECT TO_CHAR(1234.5, 'L9,999.00') FROM dual;         -- 本地货币符号

-- 百分比
SELECT TO_CHAR(0.75, '990.00%') FROM dual;             -- 0.75%

-- 前导零
SELECT TO_CHAR(123, '00000') FROM dual;                -- 00123

3.3 格式模型符号速查


符号	含义
`9`	数字位，不足不补零
`0`	数字位，不足补零
`.`	小数点
`,`	千分位分隔符
`$`	美元符号
`L`	本地货币符号
`C`	ISO 货币符号
`MI`	负数后置负号（`123-`）
`PR`	负数用尖括号（`<123>`）
`S`	正负号
`%`	百分比符号
`EEEE`	科学计数法
`FM`	去除前导空格

4. 日期与字符串互转

4.1 字符串转日期 TO_DATE

SELECT TO_DATE('2026-10-18', 'YYYY-MM-DD') FROM dual;
SELECT TO_DATE('2026/10/18 20:18:26', 'YYYY/MM/DD HH24:MI:SS') FROM dual;
SELECT TO_DATE('18-10-2026', 'DD-MM-YYYY') FROM dual;

4.2 日期转字符串 TO_CHAR

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual;            -- 2026-10-18
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual; -- 2026-10-18 20:18:26
SELECT TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日"') FROM dual;   -- 2026年10月18日
SELECT TO_CHAR(SYSDATE, 'Day') FROM dual;                   -- Sunday（星期）

4.3 字符串转时间戳 TO_TIMESTAMP / TO_TIMESTAMP_TZ

SELECT TO_TIMESTAMP('2026-10-18 20:18:26.123456',
       'YYYY-MM-DD HH24:MI:SS.FF') FROM dual;

SELECT TO_TIMESTAMP_TZ('2026-10-18 20:18:26 +08:00',
       'YYYY-MM-DD HH24:MI:SS TZH:TZM') FROM dual;

5. 通用类型转换 CAST

CAST 是 ANSI SQL 标准函数，用于在多种类型间转换。

5.1 基本语法

CAST(expr AS type_name)

5.2 常见用法

-- 数值转字符串
SELECT CAST(12345 AS VARCHAR2(20)) FROM dual;     -- '12345'

-- 字符串转数值
SELECT CAST('123.45' AS NUMBER(8,2)) FROM dual;   -- 123.45

-- 字符串转日期
SELECT CAST('2026-10-18' AS DATE) FROM dual;

-- 数值转时间戳（从 epoch 秒数）
SELECT CAST(1760000000 AS DATE) FROM dual;

-- 转浮点类型
SELECT CAST(1 AS BINARY_FLOAT)  FROM dual;
SELECT CAST(1 AS BINARY_DOUBLE) FROM dual;

5.3 CAST 与 TO_ 系列的区别


对比项	`CAST`	`TO_*` 系列
是否标准	ANSI SQL 标准	Oracle 专有
格式模型	不支持自定义格式	支持格式模型（如 `'9,999.99'`）
可移植性	好	差
灵活性	低	高

6. 通用类型转换 CONVERT

CONVERT 用于在不同字符集之间转换字符串。

6.1 基本语法

CONVERT(char, dest_char_set [, source_char_set])

6.2 常见用法

-- 转换到 UTF-8
SELECT CONVERT('中文', 'UTF8') FROM dual;

-- 从 GBK 转换到 UTF-8
SELECT CONVERT('中文', 'UTF8', 'ZHS16GBK') FROM dual;

-- 转换到 ASCII（非 ASCII 字符会被替换）
SELECT CONVERT('abc', 'US7ASCII') FROM dual;

7. 其他常用转换函数

7.1 ASCII 码与字符 CHR / ASCII

-- 数字转 ASCII 字符
SELECT CHR(65)    FROM dual;  -- 结果：A
SELECT CHR(48)    FROM dual;  -- 结果：0

-- 字符转 ASCII 码
SELECT ASCII('A') FROM dual;  -- 结果：65
SELECT ASCII('a') FROM dual;  -- 结果：97

7.2 二进制与十六进制互转 HEXTORAW / RAWTOHEX

-- 十六进制字符串转 RAW
SELECT HEXTORAW('4F7261636C65') FROM dual;

-- RAW 转十六进制字符串
SELECT RAWTOHEX('Oracle') FROM dual;  -- 结果：4F7261636C65

7.3 大对象转换 TO_CLOB / TO_BLOB / TO_NCLOB

-- 字符串转 CLOB
SELECT TO_CLOB('这是一段长文本') FROM dual;

-- 字符串转 NCLOB
SELECT TO_NCLOB('Unicode 文本') FROM dual;

-- RAW 转 BLOB
SELECT TO_BLOB(HEXTORAW('4F7261636C65')) FROM dual;

7.4 任意类型转字符 TO_CHAR（补充）

TO_CHAR 也支持将 NCHAR/NVARCHAR2/CLOB/NCLOB 转换为 VARCHAR2。

SELECT TO_CHAR(N'中文字符') FROM dual;

8. 字符集与编码转换

8.1 查看数据库字符集

-- 数据库字符集
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET';

-- 国家字符集（NCHAR 使用）
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_NCHAR_CHARACTERSET';

8.2 常用字符集


字符集	说明
`AL32UTF8`	UTF-8 编码（推荐）
`UTF8`	UTF-8 编码（旧版别名）
`ZHS16GBK`	简体中文 GBK
`US7ASCII`	7 位 ASCII
`AL16UTF16`	UTF-16 编码（国家字符集）

8.3 长度计算 LENGTH / LENGTHB / LENGTHC

SELECT LENGTH('中文')  FROM dual;  -- 按字符计：2
SELECT LENGTHB('中文') FROM dual;  -- 按字节计（UTF8 下为 6）
SELECT LENGTHC('中文') FROM dual;  -- 按 Unicode 字符计：2

9. 隐式类型转换规则

Oracle 在特定场景会自动进行隐式类型转换，了解这些规则有助于避免性能问题与隐性错误。

9.1 隐式转换示例

-- 数值与字符串比较（隐式将字符串转为数值）
SELECT * FROM emp WHERE empno = '7369';

-- 字符串与日期比较（隐式转日期）
SELECT * FROM emp WHERE hiredate = '17-DEC-80';

-- 数值与字符连接（隐式转字符串）
SELECT '工号：' || 7369 FROM dual;  -- 结果：工号：7369

9.2 隐式转换的风险

注意：隐式转换可能导致：
索引失效（列上的函数转换使索引无法使用）
性能下降
结果依赖 NLS 参数，出现非预期结果

-- 不推荐：隐式转换（可能不走索引）
SELECT * FROM emp WHERE empno = '7369';

-- 推荐：显式转换，保持类型一致
SELECT * FROM emp WHERE empno = 7369;
SELECT * FROM emp WHERE empno = TO_NUMBER('7369');

附录：常用转换函数速查表


函数	功能	返回类型
`TO_NUMBER(str [, fmt])`	字符串转数值	NUMBER
`TO_CHAR(n [, fmt])`	数值转字符串	VARCHAR2
`TO_CHAR(d [, fmt])`	日期转字符串	VARCHAR2
`TO_DATE(str, fmt)`	字符串转日期	DATE
`TO_TIMESTAMP(str, fmt)`	字符串转时间戳	TIMESTAMP
`TO_TIMESTAMP_TZ(str, fmt)`	字符串转带时区时间戳	TIMESTAMP WITH TZ
`CAST(expr AS type)`	标准类型转换	目标类型
`CONVERT(str, dest [, src])`	字符集转换	VARCHAR2
`CHR(n)`	ASCII 码转字符	VARCHAR2
`ASCII(ch)`	字符转 ASCII 码	NUMBER
`HEXTORAW(str)`	十六进制转 RAW	RAW
`RAWTOHEX(raw)`	RAW 转十六进制	VARCHAR2
`TO_CLOB(str)`	字符串转 CLOB	CLOB
`TO_BLOB(raw)`	RAW 转 BLOB	BLOB
`TO_NCLOB(str)`	字符串转 NCLOB	NCLOB
`LENGTH(str)`	字符长度	NUMBER
`LENGTHB(str)`	字节长度	NUMBER
`LENGTHC(str)`	Unicode 字符长度	NUMBER

提示：
优先使用 CAST 保证 SQL 的可移植性，需要自定义格式时再用 TO_* 系列。
尽量避免隐式类型转换，显式转换可提升可读性与索引利用率。
处理中文字符集时注意 LENGTH（字符）与 LENGTHB（字节）的区别。
TO_DATE / TO_NUMBER 转换失败会抛出异常，可用 VALIDATE_CONVERSION 函数预先校验（Oracle 12.2+）。

其他函数

空值处理函数

NVL(被检测的值, 如果为 NULL 则返回的值)

SELECT NVL(NULL,0) FROM dual

NVL2(被判断对象, 条件为真时的处理, 条件为假时的处理)

DECODE 条件判断

(DECODE 遍历一次条件，找到匹配值就返回匹配位置后面一个的值，否则将最后一位当做缺省值并返回)

SELECT DECODE(100,1,2,3,4,100,200) FROM dual

返回 200

CASE WHEN THEN

SELECT var,(CASE TableName 
    WHEN 1 THEN val1
    WHEN 2 THEN val2
    WHEN 3 THEN val3
    WHEN 4 THEN val4
    ELSE DefaultVal
) FROM TableName2

行列转换

SELECT  (SELECT var3 FROM TableName2 WHERE var4=AreaID),
    SUM(CASE WHEN var5=val5 THEN VARR ELSE val5 END) FROM TableName WHERE var2=val GROUP BY AreaID

分析函数

三个分析函数

值相同，排名相同，序号跳跃RANK()
值相同，排名相同，序号连续DENSE_RANK()
序号连续，无视值ROW_NUMBER()

SELECT RANK() OVER(ORDER BY var DESC) ResName,t.* FROM TableName t

使用分析函数实现分页

SELECT ROW_NUMBER() OVER(ORDER BY var DESC) ResName,t.* FROM TableName t

集合运算

并集

UNION 不包含重复数据
UNION ALL 包含重复数据

SELECT * FROM TableName WHERE var>5
UNION
SELECT * FROM TableName WHERE var<8

交集

SELECT * FROM TableName WHERE var>5
INTERSECT
SELECT * FROM TableName WHERE var<8

差集

SELECT * FROM TableName WHERE var>5
MINUS
SELECT * FROM TableName WHERE var<8

使用差集实现分页

SELECT  ROWNUM,t.* FROM TableName t WHERE ROWNUM<=20
MINUS
SELECT  ROWNUM,t.* FROM TableName t WHERE ROWNUM<=10

xpeng4643Here

Oracle数据库SQL语句

Oracle数据库学习 上（SQL语句）

Oracle数据库SQL语句

笔记信息：

表相关修改

创建表空间

创建用户

赋予用户权限

创建表

增加字段

追加字段

修改字段（属性）

修改字段名

删除字段

删除表

数据相关修改

插入数据

修改数据

删除数据

删除表

数据导入与导出操作

整库导出

整库导入

单表查询

精确查询

模糊查询

AND运算符

OR运算符

范围查询

空值查询

跳过重复记录查询

排序查询

升序查询

降序查询

基于伪列的查询

ROWID（行ID）

ROWNUM

聚合统计

求和SUM

求平均值AVG

求最大值MAX

求最小值MIN

统计记录数

分组聚合统计

分组后条件查询HAVING

多表连接查询

多表内连接查询

左外连接查询

右外连接查询

子查询（嵌套查询）

WHERE子句中的子查询（嵌套查询）

单行子查询

多行子查询

FROM子句中的子查询

SELECT子句中的子查询

分页查询

简单分页

基于排序的分页

函数

单行函数

字符函数

数值计算函数

Oracle 数值计算 SQL 函数整理

目录

1. 基本算术运算

2. 数值处理函数

2.1 绝对值 ABS

2.2 符号 SIGN

2.3 平方根 SQRT

2.4 幂运算 POWER

2.5 指数 EXP

2.6 自然对数 LN

2.7 常用对数 LOG

3. 数学与三角函数

3.1 正弦 SIN / 余弦 COS / 正切 TAN

3.2 反正弦 ASIN / 反余弦 ACOS / 反正切 ATAN

3.3 双参数反正切 ATAN2

3.4 双曲函数 SINH / COSH / TANH

3.5 角度与弧度转换

Oracle数据库学习上（SQL语句）