Oracle数据库

以下是Oracle数据库中常用的基础语法概览：

一、数据类型（部分常用）

二、数据定义语言 (DDL)

1. 创建表 (CREATE TABLE)

CREATE TABLE employees (
    employee_id   NUMBER(6)     CONSTRAINT pk_emp PRIMARY KEY,
    first_name    VARCHAR2(20),
    last_name     VARCHAR2(25)  NOT NULL,
    email         VARCHAR2(25)  UNIQUE,
    hire_date     DATE          DEFAULT SYSDATE,
    salary        NUMBER(8,2)
);

2. 修改表结构 (ALTER TABLE)

--添加列
Alter table employees ADD (deparment_id NUMBER(4));

-- 修改列
ALTER TABLE employees MODIFY (salary NUMBER(10,2));

-- 删除列
ALTER TABLE employees DROP COLUMN department_id;

-- 添加约束
ALTER TABLE employees ADD CONSTRAINT fk_dept FOREIGN KEY (department_id) REFERENCES departments(department_id);

-- 删除约束
ALTER TABLE employees DROP CONSTRAINT fk_dept;

3. 删除表 (DROP TABLE)

DROP TABLE employees;

4. 创建索引 (CREATE INDEX)

CREATE INDEX idx_emp_name ON employees(last_name);

5. 创建视图 (CREATE VIEW)

CREATE VIEW emp_view AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 5000;

6. 创建序列 (CREATE SEQUENCE)

CREATE SEQUENCE seq_emp_id
START WITH 100
INCREMENT BY 1
NOCACHE
NOCYCLE;

三、数据操作语言 (DML)

1. 插入数据 (INSERT)

-- 插入完整行
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary)
VALUES (seq_emp_id.NEXTVAL, 'John', 'Doe', 'john.doe@example.com', SYSDATE, 6000);

-- 插入部分列
INSERT INTO employees (employee_id, last_name, email)
VALUES (101, 'Smith', 'smith@example.com');

-- 从查询结果插入
INSERT INTO emp_archive
SELECT * FROM employees WHERE hire_date < DATE '2020-01-01';

2. 更新数据 (UPDATE)

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

3. 删除数据 (DELETE)

DELETE FROM employees WHERE employee_id = 101;

4. 合并数据 (MERGE) - Upsert操作

MERGE INTO bonuses b
USING (SELECT employee_id, salary, department_id FROM employees) e
ON (b.employee_id = e.employee_id)
WHEN MATCHED THEN
    UPDATE SET b.bonus = e.salary * 0.1
    WHERE e.salary > 5000
WHEN NOT MATCHED THEN
    INSERT (b.employee_id, b.bonus)
    VALUES (e.employee_id, e.salary * 0.05)
    WHERE e.salary <= 5000;

四、数据查询语言 (DQL)

1. 基本SELECT

SELECT first_name, last_name, salary
FROM employees;

-- 查询所有列
SELECT * FROM employees;

-- 去重
SELECT DISTINCT department_id FROM employees;

-- 别名
SELECT first_name AS "First Name", last_name "Last Name" FROM employees;

2. WHERE子句

SELECT * FROM employees WHERE salary > 5000 AND department_id = 10;

-- 模糊查询
SELECT * FROM employees WHERE last_name LIKE 'Smi%';

-- 范围查询
SELECT * FROM employees WHERE hire_date BETWEEN DATE '2020-01-01' AND DATE '2022-12-31';

-- 列表查询
SELECT * FROM employees WHERE department_id IN (10, 20, 30);

-- 空值判断
SELECT * FROM employees WHERE commission_pct IS NULL;

3. 排序 (ORDER BY)

-- 升序 ASC（默认）
SELECT * FROM employees ORDER BY salary;

-- 降序 DESC
SELECT * FROM employees ORDER BY hire_date DESC;

-- 多列排序
SELECT * FROM employees ORDER BY department_id, salary DESC;

4. 分组与聚合 (GROUP BY, HAVING)

-- 按部门统计平均工资
SELECT department_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id;

-- 带条件的分组
SELECT department_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

5. 连接查询 (JOIN)

-- 内连接
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- 左外连接 (LEFT JOIN)
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- 右外连接 (RIGHT JOIN)
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

-- 全外连接 (FULL OUTER JOIN)
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;

-- 笛卡尔积 (CROSS JOIN)
SELECT e.first_name, d.department_name
FROM employees e
CROSS JOIN departments d;

6. 子查询

-- 标量子查询
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 行子查询
SELECT * FROM employees
WHERE (employee_id, salary) IN (SELECT employee_id, salary FROM emp_bonus);

-- 相关子查询
SELECT department_id, department_name
FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.department_id);

-- 内联视图（From子句中的子查询）
SELECT *
FROM (SELECT employee_id, first_name, last_name, salary FROM employees ORDER BY salary DESC)
WHERE ROWNUM <= 5;

7. 集合操作 (UNION, INTERSECT, MINUS)

-- 合并结果集（去重）
SELECT department_id FROM employees
UNION
SELECT department_id FROM departments;

-- 合并结果集（不去重）
SELECT department_id FROM employees
UNION ALL
SELECT department_id FROM departments;

-- 交集
SELECT department_id FROM employees
INTERSECT
SELECT department_id FROM departments;

-- 差集
SELECT department_id FROM departments
MINUS
SELECT department_id FROM employees;

8. 分页查询 (ROWNUM / ROW_NUMBER())

-- 使用 ROWNUM (注意：ROWNUM 是在结果返回前分配的)
-- 获取前5行
SELECT * FROM (SELECT * FROM employees ORDER BY employee_id) WHERE ROWNUM <= 5;

-- 获取第6到第10行 (Oracle 12c之前较复杂)
SELECT *
FROM (
    SELECT emp.*, ROWNUM rn
    FROM (
        SELECT * FROM employees ORDER BY employee_id
    ) emp
    WHERE ROWNUM <= 10
)
WHERE rn > 5;

-- Oracle 12c 及以后版本 (推荐)
SELECT * FROM employees
ORDER BY employee_id
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY; -- 跳过5行，取接下来的5行

五、事务控制语言 (TCL)

-- 开始事务 (隐式)
-- 每条DML语句都开始一个事务

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 设置保存点
SAVEPOINT sp1;
-- 回滚到保存点
ROLLBACK TO sp1;

六、常用内置函数（部分）

1. 字符函数

SELECT UPPER(last_name), LOWER(first_name), INITCAP(email)
FROM employees;

SELECT SUBSTR('Oracle Database', 8, 8) AS result FROM dual; -- 'Database'

SELECT LENGTH('Hello World') AS len FROM dual; -- 11

SELECT INSTR('Oracle Database', 'a') AS pos FROM dual; -- 3 (第一个'a'的位置)

SELECT LPAD(salary, 10, '*') AS padded_sal FROM employees;

2. 数值函数

SELECT ROUND(123.456, 2) FROM dual; -- 123.46
SELECT TRUNC(123.456, 2) FROM dual; -- 123.45
SELECT MOD(10, 3) FROM dual; -- 1
SELECT CEIL(123.1) FROM dual; -- 124
SELECT FLOOR(123.9) FROM dual; -- 123

3. 日期函数

SELECT SYSDATE FROM dual; -- 当前日期时间

SELECT ADD_MONTHS(SYSDATE, 3) FROM dual; -- 加3个月

SELECT MONTHS_BETWEEN(SYSDATE, DATE '2023-01-01') FROM dual; -- 相差月数

SELECT NEXT_DAY(SYSDATE, 'MONDAY') FROM dual; -- 下一个周一

SELECT LAST_DAY(SYSDATE) FROM dual; -- 本月最后一天

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual; -- 格式化日期

SELECT TO_DATE('2023-10-26', 'YYYY-MM-DD') FROM dual; -- 字符转日期

4. 转换函数

SELECT TO_CHAR(salary, 'L999,999.99') FROM employees; -- 货币格式

SELECT TO_NUMBER('1234.56') FROM dual;

SELECT NVL(commission_pct, 0) FROM employees; -- 空值替换

SELECT NVL2(commission_pct, 'Has Commission', 'No Commission') FROM employees;

SELECT NULLIF(salary, 5000) FROM employees; -- 相等则返回NULL

SELECT COALESCE(commission_pct, salary * 0.1, 0) FROM employees; -- 返回第一个非空值

5. 条件表达式

-- CASE 表达式
SELECT employee_id, first_name, last_name,
       CASE
           WHEN salary > 10000 THEN 'High'
           WHEN salary > 5000 THEN 'Medium'
           ELSE 'Low'
       END AS salary_level
FROM employees;

-- DECODE 函数 (Oracle特有)
SELECT employee_id, first_name, last_name,
       DECODE(department_id, 10, 'Accounting', 20, 'Research', 'Other') AS dept_name
FROM employees;

七、PL/SQL 基础（简要）

PL/SQL是Oracle的过程化SQL扩展。

1. 匿名块

DECLARE
    v_name VARCHAR2(50) := 'John Doe';
    v_salary NUMBER(8,2);
BEGIN
    SELECT salary INTO v_salary
    FROM employees
    WHERE employee_id = 100;

    DBMS_OUTPUT.PUT_LINE('Employee ' || v_name || ' has salary: ' || v_salary);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee not found.');
END;
/

2. 存储过程

CREATE OR REPLACE PROCEDURE raise_salary(p_emp_id NUMBER, p_amount NUMBER) IS
BEGIN
    UPDATE employees
    SET salary = salary + p_amount
    WHERE employee_id = p_emp_id;
    
    IF SQL%ROWCOUNT = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Employee ID not found.');
    END IF;
    
    COMMIT;
END raise_salary;
/

-- 调用存储过程
BEGIN
    raise_salary(100, 500);
END;
/

3. 函数

CREATE OR REPLACE FUNCTION get_annual_salary(p_emp_id NUMBER) RETURN NUMBER IS
    v_salary NUMBER(8,2);
BEGIN
    SELECT salary INTO v_salary
    FROM employees
    WHERE employee_id = p_emp_id;
    
    RETURN v_salary * 12;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END get_annual_salary;
/

-- 使用函数
SELECT employee_id, get_annual_salary(employee_id) AS annual_sal FROM employees;

八、其他常用语法

1. 注释

-- 单行注释

/*
多行注释
可以跨越多行
*/

2. DUAL 表

Oracle提供的虚拟表，用于执行不需要真实表的查询。

SELECT 1 + 1 FROM dual;
SELECT SYSDATE FROM dual;
SELECT USER FROM dual;

3. 查看执行计划

EXPLAIN PLAN FOR SELECT * FROM employees WHERE last_name = 'Smith';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

九、总结

以上是Oracle数据库的基础语法概览。掌握这些内容可以进行大部分日常的数据库操作。实际应用中，根据具体需求查阅官方文档（如Oracle Database SQL Language Reference）会更全面。