Oracle 表查询方法

在Oracle数据库中查询表数据有多种方式，以下是常用的查询方法：

基本查询语法

SELECT [DISTINCT] column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY column1, column2, ...]
[HAVING condition]
[ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...];

常用查询示例

1. 查询表中所有数据

   SELECT * FROM employees;
   

2. 查询特定列

   SELECT employee_id, first_name, last_name FROM employees;
   

3. 带条件的查询

   SELECT * FROM employees WHERE department_id = 10;
   

4. 模糊查询

   SELECT * FROM employees WHERE last_name LIKE 'Sm%';
   

5. 排序查询

   SELECT * FROM employees ORDER BY hire_date DESC;
   

6. 分组查询

   SELECT department_id, COUNT(*) 
   FROM employees 
   GROUP BY department_id;
   

7. 多表连接查询

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

高级查询技巧

1. 分页查询

   -- Oracle 12c及以上版本
   SELECT * FROM employees 
   OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
   
   -- Oracle 11g及以下版本
   SELECT * FROM (
    SELECT a.*, ROWNUM rn 
    FROM (
      SELECT * FROM employees ORDER BY employee_id
    ) a 
    WHERE ROWNUM <= 20
   ) WHERE rn > 10;
   

2. 子查询

   SELECT * FROM employees 
   WHERE salary > (SELECT AVG(salary) FROM employees);
   

3. 使用WITH子句(CTE)

   WITH dept_avg AS (
    SELECT department_id, AVG(salary) avg_sal
    FROM employees
    GROUP BY department_id
   )
   SELECT e.employee_id, e.last_name, e.salary, d.avg_sal
   FROM employees e
   JOIN dept_avg d ON e.department_id = d.department_id
   WHERE e.salary > d.avg_sal;
   

查看表结构

1. 查看表列信息

   DESCRIBE employees;
   -- 或
   SELECT column_name, data_type, nullable 
   FROM user_tab_columns 
   WHERE table_name = 'EMPLOYEES';
   

2. 查看表约束

   SELECT constraint_name, constraint_type, search_condition
   FROM user_constraints
   WHERE table_name = 'EMPLOYEES';
   

3. 查看表索引

   SELECT index_name, column_name
   FROM user_ind_columns
   WHERE table_name = 'EMPLOYEES';
   

性能优化提示

1. 只查询需要的列，避免使用SELECT *
2. 为常用查询条件创建适当的索引
3. 对大表使用分区表技术
4. 使用EXPLAIN PLAN分析查询执行计划

希望这些查询方法对您在Oracle数据库中的工作有所帮助！