数据定义语言 (DDL)

创建表

CREATE TABLE table_name (
  column1 datatype [CONSTRAINT constraint_name],
  column2 datatype,
  ...
);

示例

CREATE TABLE employees (
  emp_id NUMBER PRIMARY KEY,
  emp_name VARCHAR2(50) NOT NULL,
  hire_date DATE DEFAULT SYSDATE,
  salary NUMBER(8,2),
  dept_id NUMBER REFERENCES departments(dept_id)
);

修改表

-- 添加列
ALTER TABLE table_name
  ADD column_name datatype;

-- 修改列
ALTER TABLE table_name
  MODIFY column_name new_datatype;

-- 删除列
ALTER TABLE table_name
  DROP COLUMN column_name;

删除表

DROP TABLE table_name [CASCADE CONSTRAINTS];

数据操作语言 (DML)

插入数据

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

示例

INSERT INTO employees (emp_id, emp_name, hire_date, salary)
VALUES (101, 'John Doe', TO_DATE('2023-01-15', 'YYYY-MM-DD'), 5000);

更新数据

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

示例

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

删除数据

DELETE FROM table_name
WHERE condition;

示例

DELETE FROM employees
WHERE emp_id = 105;

数据查询语言 (DQL)

SELECT 语句

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

示例

SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date > TO_DATE('2020-01-01', 'YYYY-MM-DD')
GROUP BY dept_id
HAVING AVG(salary) > 4000
ORDER BY avg_salary DESC;

约束

主键约束

-- 创建表时定义
CREATE TABLE table_name (
  id NUMBER PRIMARY KEY,
  ...
);

-- 添加主键约束
ALTER TABLE table_name
  ADD CONSTRAINT pk_name PRIMARY KEY (column_name);

外键约束

ALTER TABLE child_table
  ADD CONSTRAINT fk_name
  FOREIGN KEY (child_column)
  REFERENCES parent_table(parent_column);

事务控制

开始事务

-- Oracle中事务在第一条DML语句执行时隐式开始
SET TRANSACTION [READ ONLY | READ WRITE];

提交事务

COMMIT;

回滚事务

ROLLBACK [TO SAVEPOINT savepoint_name];

示例

SAVEPOINT before_update;

UPDATE employees
SET salary = salary * 1.1;

-- 回滚到保存点
ROLLBACK TO before_update;

索引

创建索引

CREATE INDEX index_name
ON table_name (column1, column2, ...);

示例

CREATE INDEX idx_emp_dept
ON employees (dept_id, hire_date);

删除索引

DROP INDEX index_name;

视图

创建视图

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

示例

CREATE VIEW high_salary_emp AS
SELECT emp_id, emp_name, salary
FROM employees
WHERE salary > 8000;

序列

创建序列

CREATE SEQUENCE sequence_name
  [INCREMENT BY n]
  [START WITH n]
  [{MAXVALUE n | NOMAXVALUE}]
  [{MINVALUE n | NOMINVALUE}]
  [{CYCLE | NOCYCLE}]
  [{CACHE n | NOCACHE}];

示例

CREATE SEQUENCE emp_seq
  INCREMENT BY 1
  START WITH 1001
  MAXVALUE 9999
  NOCYCLE
  CACHE 20;

使用序列

-- 获取下一个序列值
sequence_name.NEXTVAL;

-- 获取当前序列值
sequence_name.CURRVAL;

示例

INSERT INTO employees (emp_id, emp_name)
VALUES (emp_seq.NEXTVAL, 'Alice Smith');

函数

创建函数

CREATE OR REPLACE FUNCTION function_name
  (parameter1 IN datatype, ...)
RETURN return_datatype
IS
  [declaration_section]
BEGIN
  executable_section
  [EXCEPTION
    exception_section]
END [function_name];

示例

CREATE OR REPLACE FUNCTION get_emp_salary
  (p_emp_id IN employees.emp_id%TYPE)
RETURN employees.salary%TYPE
IS
  v_salary employees.salary%TYPE;
BEGIN
  SELECT salary INTO v_salary
  FROM employees
  WHERE emp_id = p_emp_id;

  RETURN v_salary;
END get_emp_salary;

使用函数

SELECT function_name(parameters) FROM dual;

示例

SELECT get_emp_salary(101) FROM dual;

存储过程

创建存储过程

CREATE OR REPLACE PROCEDURE procedure_name
  (parameter1 [IN | OUT | IN OUT] datatype, ...)
IS
  [declaration_section]
BEGIN
  executable_section
  [EXCEPTION
    exception_section]
END [procedure_name];

示例

CREATE OR REPLACE PROCEDURE increase_salary
  (p_emp_id IN employees.emp_id%TYPE,
  p_percent IN NUMBER)
IS
BEGIN
  UPDATE employees
  SET salary = salary * (1 + p_percent/100)
  WHERE emp_id = p_emp_id;

  COMMIT;
END increase_salary;

调用存储过程

EXECUTE procedure_name(parameters);

示例

EXECUTE increase_salary(101, 10);

触发器

创建触发器

CREATE OR REPLACE TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}
{INSERT | UPDATE | DELETE}
ON table_name
[REFERENCING OLD AS old NEW AS new]
[FOR EACH ROW]
[WHEN (condition)]
BEGIN
  -- 触发器逻辑
END;

示例

CREATE OR REPLACE TRIGGER audit_emp_changes
BEFORE INSERT OR UPDATE OR DELETE
ON employees
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO emp_audit (action, emp_id, change_date)
    VALUES ('INSERT', :NEW.emp_id, SYSDATE);
  ELSIF UPDATING THEN
    INSERT INTO emp_audit (action, emp_id, change_date)
    VALUES ('UPDATE', :OLD.emp_id, SYSDATE);
  ELSIF DELETING THEN
    INSERT INTO emp_audit (action, emp_id, change_date)
    VALUES ('DELETE', :OLD.emp_id, SYSDATE);
  END IF;
END;

表连接

内连接 (INNER JOIN)

SELECT columns
FROM table1
INNER JOIN table2
  ON table1.column = table2.column;

示例

SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d
  ON e.dept_id = d.dept_id;

左外连接 (LEFT JOIN)

SELECT columns
FROM table1
LEFT JOIN table2
  ON table1.column = table2.column;

示例

SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d
  ON e.dept_id = d.dept_id;

右外连接 (RIGHT JOIN)

SELECT columns
FROM table1
RIGHT JOIN table2
  ON table1.column = table2.column;

示例

SELECT e.emp_name, d.dept_name
FROM employees e
RIGHT JOIN departments d
  ON e.dept_id = d.dept_id;

全外连接 (FULL JOIN)

SELECT columns
FROM table1
FULL JOIN table2
  ON table1.column = table2.column;

示例

SELECT e.emp_name, d.dept_name
FROM employees e
FULL JOIN departments d
  ON e.dept_id = d.dept_id;