Oracle常见sql

以下是 Oracle 数据库中常见 SQL 用法的分类整理，涵盖基础查询、高级功能及典型场景示例：

---

1. 基础查询 (SELECT)

• 查询所有列

  SELECT * FROM employees;
  

• 查询指定列

  SELECT employee_id, first_name, salary FROM employees;
  

• 过滤数据 (WHERE)

  SELECT * FROM employees WHERE department_id = 10;
  SELECT * FROM employees WHERE salary > 5000 AND hire_date < DATE '2023-01-01';
  

• 排序 (ORDER BY)

  SELECT * FROM employees ORDER BY salary DESC;
  SELECT * FROM employees ORDER BY hire_date ASC, last_name DESC;
  

---

2. 数据操作 (DML)

• 插入数据 (INSERT)

  INSERT INTO employees (employee_id, first_name, last_name) VALUES (1001, 'John', 'Doe');
  INSERT INTO employees SELECT * FROM temp_employees;  -- 从其他表复制
  

• 更新数据 (UPDATE)

  UPDATE employees SET salary = salary * 1.1 WHERE department_id = 20;
  

• 删除数据 (DELETE)

  DELETE FROM employees WHERE employee_id = 1001;
  

---

3. 聚合与分组

• 聚合函数 (SUM, AVG, COUNT 等)

  SELECT COUNT(*), AVG(salary), MAX(hire_date) FROM employees;
  

• 分组统计 (GROUP BY)

  SELECT department_id, AVG(salary) 
  FROM employees 
  GROUP BY department_id;
  

• 分组过滤 (HAVING)

  SELECT department_id, AVG(salary) 
  FROM employees 
  GROUP BY department_id 
  HAVING AVG(salary) > 6000;
  

---

4. 连接查询 (JOIN)

• 内连接 (INNER JOIN)

  SELECT e.employee_id, d.department_name 
  FROM employees e 
  JOIN departments d ON e.department_id = d.department_id;
  

• 左外连接 (LEFT JOIN)

  SELECT e.employee_id, d.department_name 
  FROM employees e 
  LEFT JOIN departments d ON e.department_id = d.department_id;
  

• Oracle 旧式外连接（不推荐但需了解）

  SELECT e.employee_id, d.department_name 
  FROM employees e, departments d 
  WHERE e.department_id = d.department_id(+);
  

---

5. 子查询

• 标量子查询

  SELECT employee_id, (SELECT department_name FROM departments WHERE department_id = e.department_id) 
  FROM employees e;
  

• IN 子查询

  SELECT * FROM employees 
  WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);
  

• EXISTS 子查询

  SELECT * FROM employees e 
  WHERE EXISTS (SELECT 1 FROM departments d WHERE d.department_id = e.department_id);
  

---

6. 常用函数

• 字符串函数

  SELECT UPPER(first_name), LENGTH(last_name), 
         SUBSTR(phone_number, 1, 3), REPLACE(email, '@', '_') 
  FROM employees;
  

• 日期函数

  SELECT SYSDATE FROM dual;  -- 当前时间
  SELECT TO_CHAR(hire_date, 'YYYY-MM-DD') FROM employees;
  SELECT ADD_MONTHS(hire_date, 6) FROM employees;  -- 加6个月
  

• 转换函数

  SELECT TO_CHAR(1234.56, '9999.99'), TO_NUMBER('100'), TO_DATE('2023-10-01', 'YYYY-MM-DD');
  

---

7. 分页查询

• 使用 ROWNUM（Oracle 传统方式）

  SELECT * 
  FROM (SELECT t.*, ROWNUM rn FROM (SELECT * FROM employees ORDER BY hire_date) t) 
  WHERE rn BETWEEN 11 AND 20;
  

• 使用 FETCH（Oracle 12c+）

  SELECT * FROM employees ORDER BY hire_date 
  OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
  

---

8. 数据字典查询

• 查看用户表

  SELECT table_name FROM user_tables;
  

• 查看表结构

  DESC employees;  -- SQL*Plus 命令
  SELECT column_name, data_type FROM user_tab_columns WHERE table_name = 'EMPLOYEES';
  

---

9. 高级功能

• 分析函数 (ROW_NUMBER, RANK)

  SELECT employee_id, salary, 
         ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank 
  FROM employees;
  

• MERGE 语句（数据合并）

  MERGE INTO employees_target t 
  USING employees_source s 
  ON (t.employee_id = s.employee_id) 
  WHEN MATCHED THEN UPDATE SET t.salary = s.salary 
  WHEN NOT MATCHED THEN INSERT VALUES (s.employee_id, s.first_name, s.salary);
  

• 层次查询 (CONNECT BY)

  SELECT employee_id, last_name, manager_id 
  FROM employees 
  START WITH manager_id IS NULL 
  CONNECT BY PRIOR employee_id = manager_id;  -- 查询上下级关系
  

---

10. 事务控制

• 提交与回滚

  COMMIT;   -- 提交事务
  ROLLBACK; -- 回滚事务
  

---

以上是 Oracle 中常见的 SQL 用法，涵盖基础操作与典型场景。实际使用时需结合业务需求和数据模型调整。