-- 创建表
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;
-- 插入数据
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;
-- 基本查询
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;
-- 存储过程示例
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;
/
-- 创建索引
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);
-- 范围分区
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);
-- 创建用户
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;
-- 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
-- 查看锁等待
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-3个月)
中级阶段(3-6个月)
高级阶段(6个月以上)
通过系统学习和实践,您将能够掌握Oracle数据库从基础到实战的各项技能,为成为专业的Oracle DBA或开发人员打下坚实基础。