hcl
Oracle 数据库核心操作

Oracle 数据库核心操作

一、基础查询操作

1. 基础查询（最常用）
-- 查询表中所有列
SELECT * FROM emp;

-- 查询指定列，并重命名列名（别名）
SELECT empno AS 员工编号, ename AS 员工姓名, sal AS 工资 FROM emp;

-- 条件查询（WHERE）
SELECT ename, sal FROM emp WHERE sal > 3000 AND deptno = 10;

-- 排序（ORDER BY，ASC升序/ DESC降序，默认升序）
SELECT ename, sal FROM emp ORDER BY sal DESC;

-- 去重查询（DISTINCT）
SELECT DISTINCT deptno FROM emp;

-- 分页查询（Oracle 特有，ROWNUM）
-- 示例：查询第6-10条数据（先查前10条，再筛选大于5条）

SELECT * FROM (
SELECT a.*, ROWNUM rn FROM (
    SELECT ename, sal FROM emp ORDER BY sal DESC
) a WHERE ROWNUM <= 10

) WHERE rn > 5;
2. 聚合查询（统计分析）
-- 求和、平均值、最大值、最小值、计数

SELECT
	COUNT(*) AS 总人数,
	SUM(sal) AS 工资总和,
	AVG(sal) AS 平均工资,
	MAX(sal) AS 最高工资,
	MIN(sal) AS 最低工资
FROM emp;
	WHERE deptno = 20;

-- 分组查询（GROUP BY）

SELECT deptno AS 部门编号, COUNT(*) AS 部门人数 
	FROM emp
	GROUP BY deptno
	HAVING COUNT(*) > 5; -- 分组后筛选（区别于WHERE，WHERE是分组前筛选）

二、表管理操作

1. 创建表
-- 基础建表语法（含常用字段类型、主键、非空约束）

CREATE TABLE emp_copy (
	empno NUMBER(4) PRIMARY KEY, -- 数字类型，长度4，主键（唯一且非空）
	ename VARCHAR2(20) NOT NULL, -- 字符串类型，长度20，非空
	sal NUMBER(7,2), -- 数字类型，总长度7，小数位2（如 12345.67）
	hiredate DATE, -- 日期类型
	deptno NUMBER(2)
);
2. 修改表
-- 添加列
	ALTER TABLE emp_copy ADD (phone VARCHAR2(11));

-- 修改列类型/长度
	ALTER TABLE emp_copy MODIFY (phone VARCHAR2(15));

-- 删除列
	ALTER TABLE emp_copy DROP COLUMN phone;

-- 添加主键约束（如果建表时未加）
	ALTER TABLE emp_copy ADD CONSTRAINT pk_empno PRIMARY KEY (empno);
3. 删除 / 清空表
-- 删除表（结构+数据，不可恢复）
	DROP TABLE emp_copy;

-- 清空表数据（可回滚，速度慢）
	DELETE FROM emp_copy;

-- 清空表数据（不可回滚，速度快，适合大数据量）
	TRUNCATE TABLE emp_copy;

三、数据操作（增删改）

1. 插入数据
-- 插入指定列
	INSERT INTO emp_copy (empno, ename, sal, hiredate)
	VALUES (1001, '张三', 5000, SYSDATE); -- SYSDATE 是Oracle系统当前日期

-- 插入所有列（列顺序需与表结构一致）
	INSERT INTO emp_copy VALUES (1002, '李四', 6000, TO_DATE('2024-01-01', 'YYYY-MM-DD'), 20);

-- 批量插入（从现有表复制数据）
	INSERT INTO emp_copy (empno, ename, sal)
	SELECT empno, ename, sal FROM emp WHERE deptno = 10;
2. 修改数据
-- 修改指定数据（务必加WHERE，否则修改全表）
UPDATE emp_copy
	SET sal = 5500, deptno = 30
	WHERE empno = 1001;
3. 删除数据
-- 删除指定数据（加WHERE，否则删除全表）
	DELETE FROM emp_copy WHERE empno = 1002;

四、用户与权限管理（运维常用）

1. 创建用户
-- 创建用户并指定表空间（默认表空间可根据实际调整）
	CREATE USER test_user IDENTIFIED BY 123456;
	DEFAULT TABLESPACE users;
	TEMPORARY TABLESPACE temp;
2. 授权
-- 授予连接数据库权限
	GRANT CONNECT TO test_user;

-- 授予操作表的权限（增删改查）
	GRANT SELECT, INSERT, UPDATE, DELETE ON scott.emp TO test_user;

-- 授予DBA权限（最高权限，谨慎使用）
	GRANT DBA TO test_user;
3. 撤销权限
REVOKE DELETE ON scott.emp FROM test_user;
4. 修改用户密码
ALTER USER test_user IDENTIFIED BY 654321;

五、其他高频操作

1. 查看表结构
-- 查看表的字段、类型、约束等信息
DESC emp; -- 简写，等价于 DESCRIBE emp
2. 事务控制
-- 提交事务（确认修改，永久生效）
COMMIT;

-- 回滚事务（撤销未提交的修改）
ROLLBACK;

-- 设置保存点（部分回滚）
SAVEPOINT sp1;

ROLLBACK TO sp1;
3. 查看当前用户 / 数据库信息
-- 查看当前登录用户
SELECT USER FROM DUAL;

-- 查看数据库版本
SELECT * FROM v$version;

-- 查看表空间使用情况
SELECT tablespace_name, sum(bytes)/1024/1024 AS 大小MB;

FROM dba_data_files;

GROUP BY tablespace_name;

六、高频时间函数（日常开发必备）

1. 获取系统时间（最常用）
-- 获取当前系统日期（仅日期，无时分秒）
SELECT SYSDATE FROM DUAL; -- 示例结果：2026/3/9

-- 获取当前系统时间戳（含日期+时分秒+毫秒）
SELECT SYSTIMESTAMP FROM DUAL; -- 示例结果：2026-03-09 15:20:30.123456 +08:00

-- 获取当前会话的日期（与SYSDATE一致，仅作用域不同）
SELECT CURRENT_DATE FROM DUAL;
2. 日期格式转换（TO_DATE/TO_CHAR）

这是处理日期最核心的两个函数，用于字符串转日期和日期转字符串，避免格式不匹配导致的报错。

-- 1. TO_DATE：字符串 → 日期（指定格式解析）
-- 语法：TO_DATE(字符串, 格式模板)

SELECT TO_DATE('2026-03-09', 'YYYY-MM-DD') AS 转换结果 FROM DUAL; -- 结果：2026/3/9
SELECT TO_DATE('20260309 15:30:45', 'YYYYMMDD HH24:MI:SS') AS 转换结果 FROM DUAL; -- 含时分秒

-- 2. TO_CHAR：日期 → 字符串（指定格式输出）
-- 语法：TO_CHAR(日期, 格式模板)

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS 日期格式 FROM DUAL; -- 结果：2026-03-09
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS 完整时间 FROM DUAL; -- 结果：2026-03-09 15:35:20
SELECT TO_CHAR(SYSDATE, 'YYYY年MM月DD日') AS 中文格式 FROM DUAL; -- 结果：2026年03月09日

-- 常用格式模板说明：
-- YYYY：4位年 | MM：2位月 | DD：2位日 | HH24：24小时制 | HH12：12小时制
-- MI：分钟 | SS：秒 | D：周几（1=周日，7=周六） | DAY：星期名称（如星期一）
3. 日期计算（加减 / 间隔）

Oracle 中日期可直接与数字（代表天数）加减，也可通过 ADD_MONTHS 等函数处理月 / 年维度的计算。

-- 1. 基础加减（按天计算）
SELECT
	SYSDATE AS 当前时间,
	SYSDATE + 1 AS 明天此时,
	SYSDATE - 7 AS 7天前,
	SYSDATE + 0.5 AS 12小时后 -- 0.5天 = 12小时
FROM DUAL;

-- 2. 按月/年计算（ADD_MONTHS）

SELECT
	ADD_MONTHS(SYSDATE, 1) AS 1个月后, -- 第二个参数：正数加，负数减
	ADD_MONTHS(SYSDATE, -3) AS 3个月前,
	ADD_MONTHS(SYSDATE, 12) AS 1年后
FROM DUAL;

-- 3. 计算两个日期的间隔

SELECT
		-- 两个日期相减 = 间隔天数（含小数，代表时分秒）
	TO_DATE('2026-04-01', 'YYYY-MM-DD') - SYSDATE AS 间隔天数,
		-- 取整数天数（TRUNC截断小数）
	TRUNC(TO_DATE('2026-04-01', 'YYYY-MM-DD') - SYSDATE) AS 整间隔天数
FROM DUAL;

-- 4. 计算下一个指定日期（NEXT_DAY）
SELECT NEXT_DAY(SYSDATE, '星期一') AS 下周一 FROM DUAL; -- 第二个参数：星期名称（需与数据库语言匹配，或用数字1-7）
4. 提取日期元素（EXTRACT）

从日期 / 时间戳中提取年、月、日、时、分、秒等具体元素，便于统计分析。

SELECT
	EXTRACT(YEAR FROM SYSDATE) AS 年, -- 结果：2026
	EXTRACT(MONTH FROM SYSDATE) AS 月, -- 结果：3
	EXTRACT(DAY FROM SYSDATE) AS 日, -- 结果：9
	EXTRACT(HOUR FROM SYSTIMESTAMP) AS 小时, -- 结果：15（需用SYSTIMESTAMP，SYSDATE无小时提取）
	EXTRACT(MINUTE FROM SYSTIMESTAMP) AS 分钟
FROM DUAL;
5. 日期截断（TRUNC）

将日期截断到指定精度（如年、月、日），常用于按周期统计（如月初、年初）。

SELECT
	TRUNC(SYSDATE) AS 今天零点, -- 结果：2026/3/9 00:00:00
	TRUNC(SYSDATE, 'MM') AS 本月初, -- 结果：2026/3/1 00:00:00
	TRUNC(SYSDATE, 'YYYY') AS 本年初, -- 结果：2026/1/1 00:00:00
	TRUNC(SYSDATE, 'DD') AS 当日起始 -- 等价于TRUNC(SYSDATE)
FROM DUAL;
6. 实际业务场景示例
-- 示例1：查询近30天内入职的员工

SELECT 
	ename, hiredate
FROM emp
	WHERE hiredate >= TRUNC(SYSDATE - 30);

-- 示例2：统计2026年每个月的工资总和

SELECT
	TO_CHAR(hiredate, 'YYYY-MM') AS 月份,
	SUM(sal) AS 工资总和
FROM emp
	WHERE EXTRACT(YEAR FROM hiredate) = 2026
	GROUP BY TO_CHAR(hiredate, 'YYYY-MM')
	ORDER BY 月份;

-- 示例3：将日期格式化为「2026年03月09日 15点40分」	
SELECT TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日" HH24"点"MI"分"') AS 中文时间 FROM DUAL;

（注：文档部分内容可能由 AI 生成）

目录