当前位置：首页 > news >正文

Oracle数据库常用语法详解

news 2025/7/28 14:08:14

一、基础查询

1. SELECT语句

-- 基本查询
SELECT employee_id, first_name, salary 
FROM employees 
WHERE department_id = 50;-- 列计算
SELECT employee_id, salary * 12 AS annual_salary 
FROM employees;-- 去重查询
SELECT DISTINCT department_id 
FROM employees;

2. 条件过滤

-- 多条件查询
SELECT * 
FROM employees 
WHERE salary > 5000 AND department_id IN (30, 50);-- 模式匹配
SELECT * 
FROM employees 
WHERE first_name LIKE 'A%'; -- 以A开头

3. 排序与分页

-- 排序
SELECT * 
FROM employees 
ORDER BY salary DESC, hire_date ASC;-- 分页（Oracle 12c+）
SELECT * 
FROM employees 
ORDER BY employee_id 
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

二、数据操作

1. 插入数据

-- 单条插入
INSERT INTO employees (employee_id, first_name, salary)
VALUES (101, 'John', 6000);-- 多条插入
INSERT ALLINTO employees VALUES (102, 'Alice', 5500)INTO employees VALUES (103, 'Bob', 5800)
SELECT * FROM DUAL;

2. 更新数据

-- 基础更新
UPDATE employees 
SET salary = salary * 1.1 
WHERE department_id = 50;-- 带条件更新
UPDATE employees 
SET (salary, commission_pct) = (SELECT 1.2 * salary, 0.1 FROM employees WHERE employee_id = 101)
WHERE employee_id = 102;

3. 删除数据

-- 安全删除
DELETE FROM employees 
WHERE hire_date < SYSDATE - 365 AND department_id = 30;-- 级联删除（需外键约束）
DELETE FROM departments 
WHERE department_id = 30;

三、高级功能

1. 连接查询

-- 内连接
SELECT e.first_name, d.department_name 
FROM employees e
JOIN departments d 
ON e.department_id = d.department_id;-- 左连接
SELECT e.first_name, d.department_name 
FROM employees e
LEFT JOIN departments d 
ON e.department_id = d.department_id;-- 自连接
SELECT e1.first_name AS employee, e2.first_name AS manager 
FROM employees e1
JOIN employees e2 
ON e1.manager_id = e2.employee_id;

2. 子查询

-- 单行子查询
SELECT * 
FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);-- 多行子查询
SELECT * 
FROM employees 
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);-- 关联子查询
SELECT * 
FROM employees e 
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

3. 聚合函数

-- 基础聚合
SELECT department_id, COUNT(*) AS employee_count,AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;-- HAVING过滤
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 6000;

四、数据库对象

1. 视图

-- 创建视图
CREATE VIEW emp_salary_view AS
SELECT employee_id, first_name, salary 
FROM employees 
WHERE salary > 5000;-- 更新视图
CREATE OR REPLACE VIEW emp_salary_view AS
SELECT employee_id, first_name, salary, department_id 
FROM employees 
WHERE salary > 5000 
WITH CHECK OPTION; -- 限制更新范围

2. 索引

-- 创建B树索引
CREATE INDEX idx_emp_last_name 
ON employees(last_name);-- 函数索引
CREATE INDEX idx_emp_upper_name 
ON employees(UPPER(last_name));-- 复合索引
CREATE INDEX idx_emp_dept_sal 
ON employees(department_id, salary);

3. 存储过程

-- 基本结构
CREATE OR REPLACE PROCEDURE raise_salary (p_emp_id IN NUMBER, p_percent IN NUMBER)
IS
BEGINUPDATE employees SET salary = salary * (1 + p_percent/100)WHERE employee_id = p_emp_id;COMMIT;
EXCEPTIONWHEN OTHERS THENROLLBACK;RAISE;
END;

五、性能优化

1. 执行计划分析

EXPLAIN PLAN FOR
SELECT * 
FROM employees 
WHERE department_id = 50;SELECT * 
FROM TABLE(DBMS_XPLAN.DISPLAY);

2. 索引优化原则

高选择性的列优先建索引
避免在频繁更新的列建索引
复合索引遵循最左前缀原则

定期分析表统计信息：

ANALYZE TABLE employees COMPUTE STATISTICS;

3. 查询优化技巧

-- 使用绑定变量
VAR dept_id NUMBER;
EXEC :dept_id := 50;SELECT * 
FROM employees 
WHERE department_id = :dept_id;-- 避免SELECT *
SELECT employee_id, first_name, salary 
FROM employees;-- 合理使用HINT
SELECT /*+ INDEX(employees idx_emp_dept_sal) */ * 
FROM employees 
WHERE department_id = 50;

六、常见问题解决

1. 空值处理

-- NVL函数
SELECT employee_id, NVL(commission_pct, 0) 
FROM employees;-- COALESCE函数
SELECT employee_id, COALESCE(manager_id, 0) 
FROM employees;

2. 字符串处理

-- 连接字符串
SELECT first_name || ' ' || last_name AS full_name 
FROM employees;-- 截取字符串
SELECT SUBSTR(first_name, 1, 3) 
FROM employees;

3. 日期处理

-- 当前日期时间
SELECT SYSDATE FROM DUAL;-- 日期计算
SELECT hire_date, SYSDATE - hire_date AS days_employed 
FROM employees;-- 格式化日期
SELECT TO_CHAR(hire_date, 'YYYY-MM-DD') 
FROM employees;