Oracle数据库常用SQL语句指南

基础查询语句

1. 简单查询

SELECT * FROM employees;

2. 条件查询

SELECT first_name, last_name, salary 
FROM employees 
WHERE department_id = 10 AND salary > 5000;

3. 排序查询

SELECT employee_id, last_name, hire_date 
FROM employees 
ORDER BY hire_date DESC;

数据操作语句

1. 插入数据

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id)
VALUES (207, 'John', 'Doe', 'JDOE', SYSDATE, 'IT_PROG');

2. 更新数据

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

3. 删除数据

DELETE FROM employees 
WHERE employee_id = 207;

表操作语句

1. 创建表

CREATE TABLE departments (
    department_id NUMBER(4) PRIMARY KEY,
    department_name VARCHAR2(30) NOT NULL,
    manager_id NUMBER(6),
    location_id NUMBER(4)
);

2. 修改表结构

ALTER TABLE employees 
ADD (middle_name VARCHAR2(25));

3. 删除表

DROP TABLE employees_backup;

高级查询

1. 连接查询

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

2. 分组统计

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

3. 子查询

SELECT last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

PL/SQL块示例

DECLARE
    v_emp_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_emp_count
    FROM employees
    WHERE department_id = 10;

    DBMS_OUTPUT.PUT_LINE('Department 10 has ' || v_emp_count || ' employees');

    IF v_emp_count > 5 THEN
        DBMS_OUTPUT.PUT_LINE('This department is well staffed');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Consider hiring more staff');
    END IF;
END;
/

常用函数

1. 字符串函数

SELECT UPPER(last_name), LENGTH(first_name), SUBSTR(phone_number, 1, 3)
FROM employees;

2. 日期函数

SELECT last_name, hire_date, MONTHS_BETWEEN(SYSDATE, hire_date) AS months_employed
FROM employees;

3. 数值函数

SELECT ROUND(salary, -2), TRUNC(salary, -2), MOD(salary, 1000)
FROM employees;

需要针对特定场景的Oracle SQL语句或有任何具体问题，请随时告诉我。