以下是一些Oracle SQL的使用技巧，包含大量的SQL语法代码示例：
一、数据查询
- 基本查询
-- 查询所有列
SELECT * FROM employees;
-- 查询指定列
SELECT employee_id, last_name, salary FROM employees;
-- 使用别名
SELECT employee_id AS "员工编号", last_name AS "姓名", salary AS "工资" FROM employees;
-- 去除重复行
SELECT DISTINCT department_id FROM employees;
- 条件查询
-- 使用WHERE子句
SELECT * FROM employees WHERE salary > 5000;
-- 使用逻辑运算符
SELECT * FROM employees WHERE salary > 5000 AND department_id = 20;
SELECT * FROM employees WHERE salary > 5000 OR department_id = 20;
-- 使用IN运算符
SELECT * FROM employees WHERE department_id IN (10, 20, 30);
-- 使用BETWEEN运算符
SELECT * FROM employees WHERE salary BETWEEN 3000 AND 6000;
-- 使用LIKE运算符
SELECT * FROM employees WHERE last_name LIKE 'S%'; -- 查询姓氏以S开头的员工
SELECT * FROM employees WHERE last_name LIKE '%n'; -- 查询姓氏以n结尾的员工
SELECT * FROM employees WHERE last_name LIKE '%a%'; -- 查询姓氏包含a的员工
- 排序查询
-- 按某一列升序排序
SELECT * FROM employees ORDER BY salary;
-- 按某一列降序排序
SELECT * FROM employees ORDER BY salary DESC;
-- 按多列排序
SELECT * FROM employees ORDER BY department_id, salary DESC;
- 聚合函数
-- 计算总行数
SELECT COUNT(*) FROM employees;
-- 计算某一列的总和
SELECT SUM(salary) FROM employees;
-- 计算某一列的平均值
SELECT AVG(salary) FROM employees;
-- 计算某一列的最大值和最小值
SELECT MAX(salary), MIN(salary) FROM employees;
-- 分组计算
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;
-- 带有HAVING子句的分组查询
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 4000;
- 连接查询
-- 内连接
SELECT employees.employee_id, employees.last_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;
-- 左连接
SELECT employees.employee_id, employees.last_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;
-- 右连接
SELECT employees.employee_id, employees.last_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;
-- 自连接
SELECT e1.employee_id, e1.last_name, e2.manager_id, e2.last_name AS manager_name
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.employee_id;
- 子查询
-- 单行子查询
SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
-- 多行子查询
SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);
-- 多列子查询
SELECT * FROM employees WHERE (department_id, job_id) IN (SELECT department_id, job_id FROM employees WHERE last_name = 'King');
-- 子查询作为表
SELECT * FROM (SELECT department_id, AVG(salary) avg_sal FROM employees GROUP BY department_id) WHERE avg_sal > 4000;
二、数据操作
- 插入数据
-- 插入单行数据
INSERT INTO employees (employee_id, last_name, email, hire_date, job_id, salary, department_id)
VALUES (207, 'Smith', 'S.Smith', SYSDATE, 'IT_PROG', 5000, 60);
-- 插入多行数据
INSERT ALL
INTO employees (employee_id, last_name, email, hire_date, job_id, salary, department_id) VALUES (208, 'Jones', 'J.Jones', SYSDATE, 'IT_PROG', 6000, 60)
INTO employees (employee_id, last_name, email, hire_date, job_id, salary, department_id) VALUES (209, 'Brown', 'B.Brown', SYSDATE, 'IT_PROG', 5500, 60)
SELECT * FROM dual;
-- 从其他表插入数据
INSERT INTO employees_backup SELECT * FROM employees;
- 更新数据
-- 更新单表
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 60;
-- 更新连接表
UPDATE employees e
SET e.salary = e.salary * 1.1
WHERE e.department_id IN (SELECT d.department_id FROM departments d WHERE d.location_id = 1700);
- 删除数据
-- 删除单表数据
DELETE FROM employees WHERE department_id = 60;
-- 删除连接表数据
DELETE FROM employees e
WHERE e.department_id IN (SELECT d.department_id FROM departments d WHERE d.location_id = 1700);
三、数据定义
- 创建表
-- 基本创建表语法
CREATE TABLE employees_backup (
employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(100),
hire_date DATE,
job_id VARCHAR2(10),
salary NUMBER(8,2),
department_id NUMBER(4)
);
-- 创建表并复制数据结构
CREATE TABLE employees_backup AS SELECT * FROM employees WHERE 1=2;
-- 创建表并复制数据
CREATE TABLE employees_backup AS SELECT * FROM employees;
- 修改表
-- 添加列
ALTER TABLE employees ADD (bonus NUMBER(8,2));
-- 修改列
ALTER TABLE employees MODIFY (salary NUMBER(10,2));
-- 重命名列
ALTER TABLE employees RENAME COLUMN bonus TO commission;
-- 删除列
ALTER TABLE employees DROP COLUMN commission;
-- 重命名表
ALTER TABLE employees RENAME TO staff;
- 删除表
-- 删除表
DROP TABLE employees_backup;
四、索引
- 创建索引
-- 创建单列索引
CREATE INDEX idx_employees_last_name ON employees(last_name);
-- 创建复合索引
CREATE INDEX idx_employees_last_name_job_id ON employees(last_name, job_id);
-- 创建唯一索引
CREATE UNIQUE INDEX uix_employees_email ON employees(email);
- 查看索引
-- 查看表的索引
SELECT * FROM user_indexes WHERE table_name = 'EMPLOYEES';
-- 查看索引的列
SELECT * FROM user_ind_columns WHERE index_name = 'IDX_EMPLOYEES_LAST_NAME';
- 删除索引
-- 删除索引
DROP INDEX idx_employees_last_name;
五、视图
- 创建视图
-- 创建视图
CREATE VIEW v_employees_dept AS
SELECT employee_id, last_name, department_id
FROM employees
WHERE department_id IN (10, 20, 30);
- 查询视图
-- 查询视图
SELECT * FROM v_employees_dept;
- 更新视图
-- 更新视图
UPDATE v_employees_dept SET last_name = 'NewName' WHERE employee_id = 100;
- 删除视图
-- 删除视图
DROP VIEW v_employees_dept;
六、存储过程
- 创建存储过程
-- 创建存储过程
CREATE OR REPLACE PROCEDURE p_raise_salary (
p_employee_id IN employees.employee_id%TYPE,
p_percent IN NUMBER
)
AS
BEGIN
UPDATE employees
SET salary = salary * (1 + p_percent/100)
WHERE employee_id = p_employee_id;
COMMIT;
END;
/
- 调用存储过程
-- 调用存储过程
EXECUTE p_raise_salary(100, 10);
- 查看存储过程
-- 查看存储过程
SELECT text FROM user_source WHERE name = 'P_RAISE_SALARY';
- 删除存储过程
-- 删除存储过程
DROP PROCEDURE p_raise_salary;
七、函数
- 创建函数
-- 创建函数
CREATE OR REPLACE FUNCTION f_calculate_bonus (
p_employee_id IN employees.employee_id%TYPE
) RETURN NUMBER
AS
v_salary employees.salary%TYPE;
v_bonus NUMBER;
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = p_employee_id;
v_bonus := v_salary * 0.1;
RETURN v_bonus;
END;
/
- 调用函数
-- 调用函数
SELECT f_calculate_bonus(100) FROM dual;
- 查看函数
-- 查看函数
SELECT text FROM user_source WHERE name = 'F_CALCULATE_BONUS';
- 删除函数
-- 删除函数
DROP FUNCTION f_calculate_bonus;
八、触发器
- 创建触发器
-- 创建触发器
CREATE OR REPLACE TRIGGER t_audit_salary
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
INSERT INTO salary_audit (employee_id, old_salary, new_salary, update_date)
VALUES (:old.employee_id, :old.salary, :new.salary, SYSDATE);
END;
/
- 查看触发器
-- 查看触发器
SELECT trigger_name, trigger_type, table_name, status FROM user_triggers WHERE table_name = 'EMPLOYEES';
- 删除触发器
-- 删除触发器
DROP TRIGGER t_audit_salary;
九、事务控制
-- 开始事务
BEGIN TRANSACTION;
-- 插入数据
INSERT INTO employees_backup SELECT * FROM employees WHERE department_id = 60;
-- 更新数据
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 60;
-- 删除数据
DELETE FROM employees WHERE department_id = 60;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;
十、分区表
- 创建分区表
-- 创建范围分区表
CREATE TABLE sales (
prod_id NUMBER(6),
cust_id NUMBER,
time_id DATE,
quantity_sold NUMBER(3),
amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id) (
PARTITION sales_q1 VALUES LESS THAN (TO_DATE('01-04-2024', 'DD-MM-YYYY')),
PARTITION sales_q2 VALUES LESS THAN (TO_DATE('01-07-2024', 'DD-MM-YYYY')),
PARTITION sales_q3 VALUES LESS THAN (TO_DATE('01-10-2024', 'DD-MM-YYYY')),
PARTITION sales_q4 VALUES LESS THAN (TO_DATE('01-01-2025', 'DD-MM-YYYY'))
);
-- 创建列表分区表
CREATE TABLE customers (
customer_id NUMBER(6),
cust_name VARCHAR2(50),
country_code CHAR(2)
)
PARTITION BY LIST (country_code) (
PARTITION cust_usa VALUES ('US'),
PARTITION cust_europe VALUES ('UK', 'FR', 'DE'),
PARTITION cust_asia VALUES ('IN', 'CN', 'JP')
);
-- 创建哈希分区表
CREATE TABLE orders (
order_id NUMBER(12),
order_date DATE,
amount NUMBER(10,2)
)
PARTITION BY HASH (order_id) PARTITIONS 4;
- 查询分区表数据
-- 查询整个分区表
SELECT * FROM sales;
-- 查询特定分区
SELECT * FROM sales PARTITION (sales_q1);
- 维护分区表
-- 添加分区
ALTER TABLE sales ADD PARTITION sales_q5 VALUES LESS THAN (TO_DATE('01-01-2026', 'DD-MM-YYYY'));
-- 合并分区
ALTER TABLE sales MERGE PARTITIONS sales_q1, sales_q2 INTO PARTITION sales_q1_q2;
-- 拆分分区
ALTER TABLE sales SPLIT PARTITION sales_q3 AT (TO_DATE('15-09-2024', 'DD-MM-YYYY')) INTO (
PARTITION sales_q3a,
PARTITION sales_q3b
);
-- 修剪分区
ALTER TABLE sales DROP PARTITION sales_q1;
十一、PL/SQL块
- 声明和赋值
DECLARE
v_employee_id employees.employee_id%TYPE := 100;
v_last_name employees.last_name%TYPE;
v_salary employees.salary%TYPE;
BEGIN
SELECT last_name, salary INTO v_last_name, v_salary FROM employees WHERE employee_id = v_employee_id;
DBMS_OUTPUT.PUT_LINE('员工姓名：' || v_last_name || ', 工资：' || v_salary);
END;
/
- 条件语句
DECLARE
v_salary employees.salary%TYPE := 5000;
BEGIN
IF v_salary > 10000 THEN
DBMS_OUTPUT.PUT_LINE('高薪员工');
ELSIF v_salary > 5000 THEN
DBMS_OUTPUT.PUT_LINE('中等收入员工');
ELSE
DBMS_OUTPUT.PUT_LINE('低收入员工');
END IF;
END;
/
- 循环语句
DECLARE
v_counter NUMBER := 1;
BEGIN
WHILE v_counter <= 10 LOOP
DBMS_OUTPUT.PUT_LINE('计数：' || v_counter);
v_counter := v_counter + 1;
END LOOP;
END;
/
DECLARE
CURSOR c_employees IS
SELECT employee_id, last_name FROM employees WHERE department_id = 60;
v_employee_id employees.employee_id%TYPE;
v_last_name employees.last_name%TYPE;
BEGIN
OPEN c_employees;
LOOP
FETCH c_employees INTO v_employee_id, v_last_name;
EXIT WHEN c_employees%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('员工ID：' || v_employee_id || ', 姓名：' || v_last_name);
END LOOP;
CLOSE c_employees;
END;
/
- 异常处理
DECLARE
v_employee_id employees.employee_id%TYPE := 999;
v_last_name employees.last_name%TYPE;
BEGIN
BEGIN
SELECT last_name INTO v_last_name FROM employees WHERE employee_id = v_employee_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('未找到员工');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('发生其他错误');
END;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('发生错误：' || SQLERRM);
END;
/
以上是一些Oracle SQL的使用技巧和示例代码，涵盖了数据查询、数据操作、数据定义、索引、视图、存储过程、函数、触发器、事务控制、分区表以及PL/SQL块等方面。通过这些示例，可以更好地理解和掌握Oracle SQL的使用方法。
扫一扫
535
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
