数据库 - Oracle从入门到入土

Oracle数据库是一种复杂的对象关系型数据库管理系统，广泛应用于大型企业和政府机构中。以下是一些基本的Oracle数据库操作和SQL语句示例。

1. 创建用户:

CREATE USER username IDENTIFIED BY password;

2. 给用户授权:

GRANT CONNECT, RESOURCE TO username;

3. 创建表:

CREATE TABLE employees (
    employee_id NUMBER(6),
    first_name VARCHAR2(20),
    last_name VARCHAR2(25) NOT NULL,
    email VARCHAR2(25) NOT NULL,
    phone_number VARCHAR2(15),
    hire_date DATE NOT NULL,
    job_id VARCHAR2(10),
    salary NUMBER(8, 2),
    commission_pct NUMBER(2, 2),
    manager_id NUMBER(6),
    department_id NUMBER(4)
);

4. 插入数据:

INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
VALUES (100, 'John', 'Doe', 'john.doe@example.com', '123-4567-8901', TO_DATE('2000-01-01', 'YYYY-MM-DD'), 'IT_PROG', 60000, NULL, NULL, 50);

5. 查询数据:

SELECT * FROM employees WHERE department_id = 50;

6. 更新数据:

UPDATE employees SET salary = salary * 1.1 WHERE employee_id = 100;

7. 删除数据:

DELETE FROM employees WHERE employee_id = 100;

8. 删除表:

DROP TABLE employees;

9. 创建索引:

CREATE INDEX emp_last_name_idx ON employees(last_name);

10. 创建视图:

CREATE VIEW employees_view AS SELECT employee_id, first_name, last_name FROM employees;

11. 创建存储过程:

CREATE OR REPLACE PROCEDURE raise_salary(p_employee_id IN NUMBER, p_raise_amount IN NUMBER) IS
BEGIN
    UPDATE employees SET salary = salary + p_raise_amount WHERE employee_id = p_employee_id;
    COMMIT;
END;
/

12. 调用存储过程:

EXEC raise_salary(100, 5000);

13. 创建触发器:

CREATE OR REPLACE TRIGGER before_emp_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    SELECT sequence_name.NEXTVAL INTO :NEW.employee_id FROM dual;
END;
/

这些操作和SQL语句是Oracle数据库操作的基础。Oracle数据库的复杂性要求对SQL和数据库管理有深入的理解。在实际应用中，还会涉及到更多的高级特性，如分区、数据仓库、序列、事务、数据库链接、PL/SQL编程等。