Oracle 数据库工作中常用知识点:sql语法与常用函数
-- 创建一个简单的表
CREATE TABLE employees (
id NUMBER PRIMARY KEY,
name VARCHAR2(50),
salary NUMBER,
department_id NUMBER
);
-- 插入数据
INSERT INTO employees (id, name, salary, department_id) VALUES (1, 'Alice', 50000, 10);
INSERT INTO employees (id, name, salary, department_id) VALUES (2, 'Bob', 60000, 20);
-- 查询所有员工的姓名和薪水,并按薪水降序排序
SELECT name, salary FROM employees ORDER BY salary DESC;
-- 计算每个部门的平均薪水
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;
-- 查询工资高于20000的员工姓名和工资
SELECT name, salary FROM employees WHERE salary > 20000;
-- 更新部门ID为10的员工的薪水增加10%
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;
-- 删除部门ID为20的所有员工
DELETE FROM employees WHERE department_id = 20;
-- 创建一个视图,展示员工姓名和他们所在部门的平均薪水
CREATE VIEW department_avg_salary AS
SELECT e.name, AVG(e.salary) OVER (PARTITION BY e.department_id) AS avg_salary
FROM employees e;
-- 查询视图,获取所有员工的姓名和他们所在部门的平均薪水
SELECT name, avg_salary FROM department_avg_salary;
这段代码展示了如何在Oracle数据库中创建表、插入数据、执行查询、使用聚合函数、分组和排序、更新和删除数据,以及创建和查询视图。这些操作是数据库开发中常用的技能,对于数据库开发者来说非常有价值。
评论已关闭