Oracle数据库常用语法详解

detayun

于 2025-07-27 16:51:56 发布

阅读量257

点赞数 2

CC 4.0 BY-SA版权

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/Dxy1239310216/article/details/149692370

一、基础查询

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 ALL
  INTO 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
BEGIN
  UPDATE employees 
  SET salary = salary * (1 + p_percent/100)
  WHERE employee_id = p_emp_id;
  
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    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;