Oracle数据库是由Oracle公司开发的关系型数据库管理系统(RDBMS),是目前全球使用最广泛的企业级数据库之一。
-- 创建表
CREATE TABLE employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(50) NOT NULL,
hire_date DATE,
salary NUMBER(10,2)
);
-- 修改表
ALTER TABLE employees ADD (department VARCHAR2(30));
-- 删除表
DROP TABLE employees;
-- 插入数据
INSERT INTO employees VALUES (1, '张三', SYSDATE, 5000);
-- 更新数据
UPDATE employees SET salary = 5500 WHERE emp_id = 1;
-- 删除数据
DELETE FROM employees WHERE emp_id = 1;
-- 基本查询
SELECT emp_id, emp_name, salary FROM employees;
-- 条件查询
SELECT * FROM employees WHERE salary > 5000;
-- 排序
SELECT * FROM employees ORDER BY salary DESC;
-- 分组
SELECT department, AVG(salary) FROM employees GROUP BY department;
PL/SQL是Oracle的过程化语言扩展,用于编写存储过程、函数、触发器等。
DECLARE
-- 变量声明
v_name VARCHAR2(50);
v_salary NUMBER;
BEGIN
-- 执行语句
SELECT emp_name, salary INTO v_name, v_salary
FROM employees WHERE emp_id = 1;
-- 输出
DBMS_OUTPUT.PUT_LINE(v_name || '的薪水是' || v_salary);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('没有找到数据');
END;
CREATE OR REPLACE PROCEDURE update_salary(
p_emp_id IN NUMBER,
p_amount IN NUMBER
) AS
BEGIN
UPDATE employees
SET salary = salary + p_amount
WHERE emp_id = p_emp_id;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
CREATE OR REPLACE TRIGGER audit_employee_changes
BEFORE INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO employee_audit VALUES('INSERT', :new.emp_id, SYSDATE);
ELSIF UPDATING THEN
INSERT INTO employee_audit VALUES('UPDATE', :old.emp_id, SYSDATE);
ELSIF DELETING THEN
INSERT INTO employee_audit VALUES('DELETE', :old.emp_id, SYSDATE);
END IF;
END;
-- 创建用户
CREATE USER testuser IDENTIFIED BY password;
-- 授予权限
GRANT CONNECT, RESOURCE TO testuser;
-- 撤销权限
REVOKE RESOURCE FROM testuser;
-- 导出数据(expdp是数据泵工具)
expdp scott/tiger@orcl schemas=scott dumpfile=scott.dmp directory=DATA_PUMP_DIR
-- 导入数据
impdp scott/tiger@orcl schemas=scott dumpfile=scott.dmp directory=DATA_PUMP_DIR
-- 查看锁信息
SELECT * FROM v$locked_object;
-- 查看会话信息
SELECT sid, serial#, username, status FROM v$session;
-- 查看SQL执行计划
EXPLAIN PLAN FOR SELECT * FROM employees WHERE emp_id = 1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
以上是Oracle数据库的基础知识概述,实际应用中还需要根据具体场景深入学习各个模块的详细内容。