Oracle 基础语句大全：从数据定义到复杂查询

一、DDL（数据定义语言）：定义数据库结构

1. 创建表（CREATE TABLE）

-- 语法格式
CREATE TABLE [schema.]table_name (column1 datatype [CONSTRAINT constraint1],column2 datatype [DEFAULT default_value],-- 表级约束[CONSTRAINT primary_key PRIMARY KEY (column1)],[CONSTRAINT foreign_key FOREIGN KEY (column2) REFERENCES ref_table(ref_col)]
);-- 示例：创建员工表
CREATE TABLE hr.employees (emp_id NUMBER(6) PRIMARY KEY,emp_name VARCHAR2(50) NOT NULL,salary NUMBER(10,2) CHECK (salary > 0),hire_date DATE DEFAULT SYSDATE,dept_id NUMBER(4) REFERENCES hr.departments(dept_id)
);-- Oracle 特有数据类型
-- VARCHAR2(n)：可变长度字符串，n为最大长度
-- NUMBER(p,s)：数值型，p总位数，s小数位
-- DATE：日期时间类型，包含年月日时分秒
-- CLOB：大文本类型（最大4GB）
-- BLOB：二进制大对象

2. 修改表（ALTER TABLE）

-- 添加列
ALTER TABLE table_name ADD (new_column datatype [CONSTRAINT]);
ALTER TABLE hr.employees ADD (email VARCHAR2(100) UNIQUE);-- 修改列定义
ALTER TABLE table_name MODIFY (column_name datatype [NULL|NOT NULL]);
ALTER TABLE hr.employees MODIFY (salary NUMBER(12,2));-- 删除列
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE hr.employees DROP COLUMN email;-- 添加约束
ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY/UNIQUE/CHECK/Foreign KEY (...);
ALTER TABLE hr.employees ADD CONSTRAINT uk_emp_name UNIQUE (emp_name);

3. 删除表（DROP TABLE）

-- 普通删除
DROP TABLE table_name [CASCADE CONSTRAINTS]; -- 级联删除外键
DROP TABLE hr.temp_employees;-- 清空表数据但保留结构
TRUNCATE TABLE table_name; -- 比DELETE更快，不记录日志
TRUNCATE TABLE hr.employees;

4. 创建索引（CREATE INDEX）

-- 普通索引
CREATE INDEX idx_table_column ON table_name(column1, column2);
CREATE INDEX idx_emp_salary ON hr.employees(salary);-- 唯一索引
CREATE UNIQUE INDEX idx_unique ON table_name(unique_column);-- 函数索引（Oracle 特有）
CREATE INDEX idx_emp_upper ON hr.employees(UPPER(emp_name));

5. 创建序列（CREATE SEQUENCE）

-- 语法
CREATE SEQUENCE [schema.]sequence_nameSTART WITH nINCREMENT BY nMINVALUE n | NOMINVALUEMAXVALUE n | NOMAXVALUECYCLE | NOCYCLECACHE n | NOCACHE;-- 示例：创建员工ID序列
CREATE SEQUENCE hr.emp_seqSTART WITH 1001INCREMENT BY 1MINVALUE 1NOMAXVALUENOCYCLECACHE 20;-- 使用序列
INSERT INTO hr.employees(emp_id, emp_name) 
VALUES (hr.emp_seq.NEXTVAL, '张三');
SELECT hr.emp_seq.CURRVAL FROM DUAL; -- 查询当前值

二、DML（数据操作语言）：操作表数据

1. 插入数据（INSERT）

-- 标准插入
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
INSERT INTO hr.employees(emp_id, emp_name, salary) 
VALUES (1001, '张三', 8000);-- 插入查询结果
INSERT INTO target_table SELECT * FROM source_table;
INSERT INTO hr.emp_backup SELECT * FROM hr.employees;-- 批量插入（Oracle 特有）
INSERT ALLINTO dept_emp VALUES (1001, 10)INTO dept_emp VALUES (1002, 20)
SELECT * FROM DUAL;

2. 更新数据（UPDATE）

-- 单表更新
UPDATE table_name SET column1 = value1, column2 = value2
WHERE condition;
UPDATE hr.employees SET salary = salary * 1.1
WHERE dept_id = 10 AND hire_date < '2020-01-01';-- 多表更新（Oracle 特有）
UPDATE hr.employees e
SET e.salary = (SELECT avg(salary) FROM hr.employees WHERE dept_id = e.dept_id)
WHERE e.salary < (SELECT avg(salary) FROM hr.employees WHERE dept_id = e.dept_id);

3. 删除数据（DELETE）

-- 删除符合条件的记录
DELETE FROM table_name WHERE condition;
DELETE FROM hr.employees WHERE hire_date < '2015-01-01';-- 清空表（与TRUNCATE区别：DELETE可回滚，TRUNCATE不可回滚）
DELETE FROM hr.employees;

4. 查询数据（SELECT）

-- 基础查询
SELECT column1, column2 FROM table_name WHERE condition;
SELECT emp_name, salary FROM hr.employees WHERE dept_id = 10;-- 去重与别名
SELECT DISTINCT dept_id FROM hr.employees;
SELECT emp_name AS "员工姓名", salary * 12 AS "年薪" FROM hr.employees;-- 排序与限制（Oracle 12c+支持LIMIT，传统用ROWNUM）
SELECT * FROM hr.employees ORDER BY salary DESC, emp_name ASC;
SELECT * FROM hr.employees WHERE ROWNUM <= 10; -- 前10条-- 多表连接
-- 内连接
SELECT e.emp_name, d.dept_name
FROM hr.employees e INNER JOIN hr.departments d
ON e.dept_id = d.dept_id;-- 外连接
SELECT e.emp_name, d.dept_name
FROM hr.employees e LEFT JOIN hr.departments d
ON e.dept_id = d.dept_id;-- 子查询
SELECT emp_name, salary
FROM hr.employees
WHERE salary > (SELECT avg(salary) FROM hr.employees);

三、TCL（事务控制语言）：管理事务

-- 开始事务（隐式开始，无需命令）
BEGIN TRANSACTION; -- 非Oracle语法，Oracle自动开始事务-- 提交事务
COMMIT; -- 永久保存数据变更-- 回滚事务
ROLLBACK; -- 撤销未提交的变更-- 设置保存点
SAVEPOINT savepoint_name;
UPDATE hr.employees SET salary = salary * 1.1 WHERE dept_id = 10;
SAVEPOINT dept10_updated;
UPDATE hr.employees SET salary = salary * 1.2 WHERE dept_id = 20;
ROLLBACK TO dept10_updated; -- 回滚到dept10_updated点，保留dept10的更新-- 自动提交（会话级设置）
SET AUTOCOMMIT ON; -- 每条DML后自动提交

四、DCL（数据控制语言）：管理权限

1. 用户管理

-- 创建用户
CREATE USER username IDENTIFIED BY passwordDEFAULT TABLESPACE tablespace_nameQUOTA nG ON tablespace_name;
CREATE USER hr_user IDENTIFIED BY hr123DEFAULT TABLESPACE usersQUOTA 500M ON users;-- 修改密码
ALTER USER username IDENTIFIED BY new_password;
ALTER USER hr_user IDENTIFIED BY hr456;-- 删除用户
DROP USER username [CASCADE]; -- CASCADE删除用户所有对象
DROP USER hr_user CASCADE;

2. 权限管理

-- 授予权限
-- 系统权限（如创建表、删除用户）
GRANT CREATE TABLE, ALTER USER TO username;
GRANT CREATE SESSION TO hr_user; -- 允许登录数据库-- 对象权限（表、视图等）
GRANT SELECT, INSERT, UPDATE ON hr.employees TO hr_user;
GRANT ALL PRIVILEGES ON hr.departments TO hr_user;-- 授予角色
GRANT DBA, CONNECT TO username; -- DBA角色拥有全部权限-- 回收权限
REVOKE CREATE TABLE FROM username;
REVOKE UPDATE ON hr.employees FROM hr_user;

五、常用函数与表达式

1. 字符串函数

-- 拼接：CONCAT或||
SELECT CONCAT(emp_name, ' works in ') || dept_name
FROM hr.employees e, hr.departments d
WHERE e.dept_id = d.dept_id;-- 长度：LENGTH
SELECT emp_name, LENGTH(emp_name) FROM hr.employees;-- 大小写转换：UPPER, LOWER, INITCAP
SELECT UPPER(emp_name) FROM hr.employees;
SELECT INITCAP(emp_name) FROM hr.employees; -- 首字母大写-- 截取：SUBSTR(start, length)
SELECT SUBSTR(emp_name, 1, 3) FROM hr.employees; -- 前3个字符-- 替换：REPLACE
SELECT REPLACE(emp_name, '张', '王') FROM hr.employees;

2. 数值函数

-- 四舍五入：ROUND(n, decimal)
SELECT ROUND(salary, -3) FROM hr.employees; -- 四舍五入到千位-- 取整：FLOOR, CEIL
SELECT FLOOR(3.7), CEIL(3.1) FROM DUAL; -- 3, 4-- 绝对值：ABS
SELECT ABS(-100) FROM DUAL; -- 100-- 幂运算：POWER(n, m)
SELECT POWER(2, 3) FROM DUAL; -- 8

3. 日期函数

-- 获取当前日期：SYSDATE
SELECT SYSDATE FROM DUAL; -- 格式：2025-06-18 15:30:00-- 日期运算：加减天数
SELECT SYSDATE + 7 FROM DUAL; -- 一周后
SELECT hire_date, SYSDATE - hire_date AS "入职天数"
FROM hr.employees;-- 提取部分日期：EXTRACT
SELECT EXTRACT(YEAR FROM hire_date) AS "入职年份"
FROM hr.employees;-- 日期格式化：TO_CHAR(date, '格式')
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL; -- 2025-06-18 15:30:00

4. 转换函数

-- 字符串转日期：TO_DATE
SELECT TO_DATE('2025-01-01', 'YYYY-MM-DD') FROM DUAL;-- 数字转字符串：TO_CHAR
SELECT TO_CHAR(salary, 'L999,999.00') FROM hr.employees; -- 带货币符号格式化-- 日期转数字：TO_NUMBER
SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) FROM DUAL; -- 获取年份数字

六、PL/SQL 基础语句

1. 存储过程（PROCEDURE）

-- 创建存储过程：计算员工平均工资
CREATE OR REPLACE PROCEDURE hr.get_avg_salary(p_dept_id IN NUMBER,p_avg_salary OUT NUMBER
) AS
BEGINSELECT AVG(salary) INTO p_avg_salaryFROM hr.employeesWHERE dept_id = p_dept_id;IF p_avg_salary IS NULL THENp_avg_salary := 0;END IF;
END;
/-- 调用存储过程
DECLAREv_avg_salary NUMBER;
BEGINhr.get_avg_salary(10, v_avg_salary);DBMS_OUTPUT.PUT_LINE('部门10平均工资：' || v_avg_salary);
END;
/

2. 函数（FUNCTION）

-- 创建函数：返回员工姓名
CREATE OR REPLACE FUNCTION hr.get_emp_name(p_emp_id IN NUMBER)
RETURN VARCHAR2 ASv_emp_name VARCHAR2(50);
BEGINSELECT emp_name INTO v_emp_nameFROM hr.employeesWHERE emp_id = p_emp_id;RETURN v_emp_name;
EXCEPTIONWHEN NO_DATA_FOUND THENRETURN '员工不存在';
END;
/-- 调用函数
SELECT hr.get_emp_name(1001) FROM DUAL;

3. 触发器（TRIGGER）

-- 创建触发器：记录员工工资变更日志
CREATE OR REPLACE TRIGGER hr.log_salary_change
AFTER UPDATE OF salary ON hr.employees
FOR EACH ROW
BEGININSERT INTO hr.salary_log (emp_id, old_salary, new_salary, update_time)VALUES (:OLD.emp_id, :OLD.salary, :NEW.salary, SYSDATE);
END;
/

七、Oracle 特有功能语句

1. 分区表操作

-- 创建分区表（范围分区）
CREATE TABLE hr.sales_history (sale_id NUMBER,sale_date DATE,amount NUMBER
)
PARTITION BY RANGE (sale_date) (PARTITION p_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),PARTITION p_2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD')),PARTITION p_other VALUES LESS THAN (MAXVALUE)
);-- 新增分区
ALTER TABLE hr.sales_history ADD PARTITION p_2026 VALUES LESS THAN (TO_DATE('2027-01-01', 'YYYY-MM-DD'));

2. 物化视图（Materialized View）

-- 创建物化视图（自动刷新）
CREATE MATERIALIZED VIEW hr.dept_salary_summary
REFRESH COMPLETE ON DEMAND
AS
SELECT dept_id, AVG(salary) avg_sal, COUNT(*) emp_count
FROM hr.employees
GROUP BY dept_id;-- 手动刷新物化视图
REFRESH MATERIALIZED VIEW hr.dept_salary_summary;

3. 闪回查询（Flashback Query）

-- 查询过去时间点的数据
SELECT * FROM hr.employees
AS OF TIMESTAMP SYSDATE - 1 -- 24小时前的数据-- 闪回删除的表（回收站功能）
FLASHBACK TABLE hr.employees TO BEFORE DROP;

八、常用系统视图与查询

-- 查看用户所有表
SELECT table_name FROM user_tables;-- 查看表结构
DESCRIBE hr.employees;
SELECT column_name, data_type, nullable
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES';-- 查看用户权限
SELECT * FROM user_sys_privs;
SELECT * FROM user_tab_privs;-- 查看数据库性能视图（需要DBA权限）
SELECT * FROM v$session;
SELECT * FROM v$instance;