oracle数据库从入门到实战

oracle数据库从入门到实战

数据库从入门到实战指南一、数据库基础1. 数据库简介2. 安装与配置3. 基本管理工具二、SQL语言基础1. 数据定义语言(DDL)

-- 创建表
CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50) NOT NULL,
    hire_date DATE DEFAULT SYSDATE,
    salary NUMBER(10,2),
    dept_id NUMBER
);
-- 修改表结构
ALTER TABLE employees ADD (email VARCHAR2(100));
-- 删除表
DROP TABLE employees PURGE;

2. 数据操作语言(DML)

-- 插入数据
INSERT INTO employees VALUES (1, '张三', TO_DATE('2020-01-15', 'YYYY-MM-DD'), 8500, 10);
-- 更新数据
UPDATE employees SET salary = salary * 1.1 WHERE dept_id = 10;
-- 删除数据
DELETE FROM employees WHERE emp_id = 1;

3. 数据查询语言(DQL)

-- 基本查询
SELECT emp_id, emp_name, salary FROM employees WHERE salary > 5000;
-- 连接查询
SELECT e.emp_name, d.dept_name 
FROM employees e JOIN departments d ON e.dept_id = d.dept_id;
-- 聚合函数
SELECT dept_id, AVG(salary) avg_salary 
FROM employees 
GROUP BY dept_id 
HAVING AVG(salary) > 6000;

三、高级特性1. PL/SQL编程

-- 存储过程示例
CREATE OR REPLACE PROCEDURE raise_salary (
    p_dept_id IN NUMBER,
    p_percent IN NUMBER
) AS
BEGIN
    UPDATE employees 
    SET salary = salary * (1 + p_percent/100)
    WHERE dept_id = p_dept_id;
    COMMIT;

    DBMS_OUTPUT.PUT_LINE('薪资调整完成');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('错误: ' || SQLERRM);
END raise_salary;
/
-- 触发器示例
CREATE OR REPLACE TRIGGER trg_emp_audit
BEFORE INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        INSERT INTO emp_audit VALUES ('INSERT', :new.emp_id, SYSDATE, USER);
    ELSIF UPDATING THEN
        INSERT INTO emp_audit VALUES ('UPDATE', :old.emp_id, SYSDATE, USER);
    ELSIF DELETING THEN
        INSERT INTO emp_audit VALUES ('DELETE', :old.emp_id, SYSDATE, USER);
    END IF;
END;
/

2. 索引与性能优化

-- 创建索引
CREATE INDEX idx_emp_dept ON employees(dept_id);
-- 复合索引
CREATE INDEX idx_emp_name_dept ON employees(emp_name, dept_id);
-- 函数索引
CREATE INDEX idx_emp_upper_name ON employees(UPPER(emp_name));
-- 执行计划查看
EXPLAIN PLAN FOR SELECT * FROM employees WHERE dept_id = 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

3. 分区表

-- 范围分区
CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER(10,2),
    region VARCHAR2(50)
) PARTITION BY RANGE (sale_date) (
    PARTITION sales_q1 VALUES LESS THAN (TO_DATE('01-APR-2023', 'DD-MON-YYYY')),
    PARTITION sales_q2 VALUES LESS THAN (TO_DATE('01-JUL-2023', 'DD-MON-YYYY')),
    PARTITION sales_q3 VALUES LESS THAN (TO_DATE('01-OCT-2023', 'DD-MON-YYYY')),
    PARTITION sales_q4 VALUES LESS THAN (TO_DATE('01-JAN-2024', 'DD-MON-YYYY')),
    PARTITION sales_future VALUES LESS THAN (MAXVALUE)
);
-- 查询特定分区

SELECT * FROM sales PARTITION(sales_q1);

四、数据库管理实战1. 用户与权限管理

-- 创建用户
CREATE USER app_user IDENTIFIED BY "P@ssw0rd"
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 100M ON users;
-- 授予权限
GRANT CONNECT, RESOURCE TO app_user;
GRANT SELECT, INSERT ON employees TO app_user;
-- 创建角色
CREATE ROLE hr_clerk;
GRANT SELECT, INSERT, UPDATE ON employees TO hr_clerk;
GRANT hr_clerk TO app_user;

2. 备份与恢复

-- RMAN备份示例
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
-- 表空间备份
RMAN> BACKUP TABLESPACE users;
-- 数据泵导出(expdp)
expdp system/password DIRECTORY=dpump_dir DUMPFILE=employees.dmp TABLES=hr.employees
-- 数据泵导入(impdp)
impdp system/password DIRECTORY=dpump_dir DUMPFILE=employees.dmp TABLES=hr.employees

3. 性能监控与调优

-- 查看锁等待
SELECT * FROM v$session WHERE blocking_session IS NOT NULL;
-- 查看资源消耗高的SQL
SELECT sql_id, executions, buffer_gets, disk_reads, elapsed_time/1000000 elapsed_sec
FROM v$sqlarea
ORDER BY buffer_gets DESC;
-- AWR报告生成
SQL> @?/rdbms/admin/awrrpt.sql

五、实战项目案例1. 电商系统数据库设计2. 数据仓库构建3. 高可用解决方案六、学习资源推荐1. 官方文档2. 推荐书籍3. 在线资源七、学习路径建议

初级阶段(1-3个月)

中级阶段(3-6个月)

高级阶段(6个月以上)

通过系统学习和实践oracle从入门到精通下载oracle从入门到精通下载oracle数据库从入门到实战，您将能够掌握数据库从基础到实战的各项技能oracle从入门到精通下载oracle数据库从入门到实战，为成为专业的 DBA或开发人员打下坚实基础。