Oracle基本操作

## 一、 与MySQL概念差异

- **Oracle**:
  - **Database** 通常指整个Oracle实例管理的所有数据文件的集合，是一个宏观的物理概念。
  - **Schema** 是与特定用户关联的逻辑容器，一个用户对应一个同名的Schema。创建表时，表默认属于当前用户的Schema。我们通常操作的是 Schema。
  - 创建数据库是一个非常复杂的过程（使用DBCA或CREATE DATABASE命令），不常进行。
- **MySQL**:
  - **Database** 和 **Schema** 是同义词，可以互换使用（`CREATE DATABASE` 等价于 `CREATE SCHEMA`）。
  - 它是一个逻辑容器，包含表、视图等对象。操作相对简单和频繁。

## 二、DDL操作

#### 1、CREATE

##### 创建表

```sql
-- 创建学生表
CREATE TABLE students (
    student_id NUMBER(10) PRIMARY KEY,
    name VARCHAR2(50) NOT NULL,
    birth_date DATE,
    email VARCHAR2(100),
    created_date DATE DEFAULT SYSDATE
);

-- 创建带约束的表
CREATE TABLE courses (
    course_id NUMBER(10) PRIMARY KEY,
    course_name VARCHAR2(100) NOT NULL UNIQUE,
    credits NUMBER(2) CHECK (credits BETWEEN 1 AND 10),
    department VARCHAR2(50)
);

-- 创建学生选课表（关联表）
CREATE TABLE student_courses (
    student_id NUMBER(10),
    course_id NUMBER(10),
    enrollment_date DATE DEFAULT SYSDATE,
    grade VARCHAR2(2),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
```

**注意**：若要添加对表或字段的`COMMENTS`需要通过单独的`COMMENTS ON TABLE/COLUMN [表/字段名字] IS '注释'`

##### 创建索引

```sql
-- 创建普通索引
CREATE INDEX idx_student_name ON students(name);

-- 创建唯一索引
CREATE UNIQUE INDEX uk_student_email ON students(email);

-- 创建复合索引
CREATE INDEX idx_student_dept_birth ON students(department, birth_date);
```

##### 创建视图

```sql
-- 创建简单视图
CREATE VIEW student_info AS
SELECT student_id, name, email, birth_date
FROM students
WHERE birth_date > DATE '2000-01-01';

-- 创建带连接的复杂视图
CREATE VIEW student_course_details AS
SELECT s.student_id, s.name AS student_name, 
       c.course_id, c.course_name, sc.enrollment_date
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id;
```

#### 2、ALTER

##### 修改表结构

```sql
-- 添加新列
ALTER TABLE students ADD (
    phone_number VARCHAR2(15),
    address VARCHAR2(200)
);

-- 修改列定义
ALTER TABLE students MODIFY (
    email VARCHAR2(150),
    name VARCHAR2(100)
);

-- 删除列
ALTER TABLE students DROP COLUMN phone_number;

-- 重命名列
ALTER TABLE students RENAME COLUMN email TO email_address;

-- 添加约束
ALTER TABLE students ADD CONSTRAINT chk_birth_date 
CHECK (birth_date > DATE '1900-01-01');

-- 删除约束
ALTER TABLE students DROP CONSTRAINT chk_birth_date;

-- 重命名表
ALTER TABLE students RENAME TO university_students;
```

#### 3、DROP

##### 删除操作

```sql
-- 删除表
DROP TABLE student_courses;

-- 删除表（如果存在）
DROP TABLE students CASCADE CONSTRAINTS;

-- 删除视图
DROP VIEW student_info;

-- 删除索引
DROP INDEX idx_student_name;
```

#### 4、TRUNCATE

##### 截断表操作

```sql
-- 清空表数据（保留表结构）
TRUNCATE TABLE student_courses;

-- 清空表数据并立即释放空间
TRUNCATE TABLE students DROP STORAGE;

-- 清空表数据但保留空间分配
TRUNCATE TABLE courses REUSE STORAGE;
```

#### 5、其他操作

##### 注释操作

```sql
-- 为表添加注释
COMMENT ON TABLE students IS '学生基本信息表';

-- 为列添加注释
COMMENT ON COLUMN students.student_id IS '学生ID，主键';
COMMENT ON COLUMN students.name IS '学生姓名';
```

##### 序列创建（用于自增主键）

```sql
-- 创建序列
CREATE SEQUENCE student_seq
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;

-- 在插入时使用序列
INSERT INTO students (student_id, name, email)
VALUES (student_seq.NEXTVAL, '张三', 'zhangsan@email.com');
```

#### 6、DDL操作特点

* **自动提交**：DDL语句执行后会自动提交事务
* **影响数据库结构**：改变数据库对象的定义
* **通常需要权限**：需要相应的系统权限才能执行
* **不可回滚**：执行后不能使用ROLLBACK撤销（某些数据库支持DDL回滚，但Oracle中大部分DDL自动提交）

## 三、DML操作

#### 0、创建示例表

```sql
-- 创建部门表
CREATE TABLE departments (
    dept_id NUMBER(5) PRIMARY KEY,
    dept_name VARCHAR2(100) NOT NULL,
    location VARCHAR2(100),
    budget NUMBER(12,2)
);

-- 创建员工表
CREATE TABLE employees (
    emp_id NUMBER(10) PRIMARY KEY,
    emp_name VARCHAR2(100) NOT NULL,
    salary NUMBER(10,2),
    hire_date DATE,
    dept_id NUMBER(5),
    email VARCHAR2(100),
    CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) 
    REFERENCES departments(dept_id)
);

-- 创建项目表
CREATE TABLE projects (
    project_id NUMBER(10) PRIMARY KEY,
    project_name VARCHAR2(200) NOT NULL,
    start_date DATE,
    end_date DATE,
    budget NUMBER(15,2),
    manager_id NUMBER(10)
);
```

#### 1、INSERT

##### 基本插入

```sql
-- 插入部门数据
INSERT INTO departments (dept_id, dept_name, location, budget)
VALUES (1, '技术部', '北京', 5000000);

INSERT INTO departments (dept_id, dept_name, location, budget)
VALUES (2, '销售部', '上海', 3000000);

INSERT INTO departments (dept_id, dept_name, location, budget)
VALUES (3, '人事部', '广州', 1000000);

-- 插入员工数据
INSERT INTO employees (emp_id, emp_name, salary, hire_date, dept_id, email)
VALUES (101, '张三', 8000, DATE '2020-03-15', 1, 'zhangsan@company.com');

INSERT INTO employees (emp_id, emp_name, salary, hire_date, dept_id, email)
VALUES (102, '李四', 7500, DATE '2019-07-20', 1, 'lisi@company.com');

INSERT INTO employees (emp_id, emp_name, salary, hire_date, dept_id, email)
VALUES (103, '王五', 9000, DATE '2018-11-10', 2, 'wangwu@company.com');
```

##### 插入带默认值的数据

```sql
-- 使用SYSDATE作为默认值
INSERT INTO employees (emp_id, emp_name, salary, dept_id, email)
VALUES (104, '赵六', 8500, 2, 'zhaoliu@company.com');

-- 使用DEFAULT关键字
INSERT INTO employees (emp_id, emp_name, salary, hire_date, dept_id)
VALUES (105, '钱七', 8200, DEFAULT, 3);
```

##### 插入多行数据

```sql
-- 方法1：多次INSERT
INSERT INTO employees VALUES (106, '孙八', 7800, DATE '2021-05-01', 1, 'sunba@company.com');
INSERT INTO employees VALUES (107, '周九', 9200, DATE '2017-09-12', 2, 'zhoujiu@company.com');

-- 方法2：使用INSERT ALL（Oracle特有）
INSERT ALL
    INTO employees VALUES (108, '吴十', 8800, DATE '2020-08-25', 1, 'wushi@company.com')
    INTO employees VALUES (109, '郑十一', 9500, DATE '2016-12-30', 3, 'zhengshiyi@company.com')
    INTO employees VALUES (110, '王十二', 7200, SYSDATE, 2, 'wangshier@company.com')
SELECT * FROM DUAL;
```

##### 从其他表插入数据

```sql
-- 创建临时表并插入数据
CREATE TABLE temp_employees AS 
SELECT * FROM employees WHERE 1=0;  -- 只复制结构，不复制数据

-- 从employees表插入数据到temp_employees
INSERT INTO temp_employees 
SELECT * FROM employees WHERE salary > 8000;

-- 插入特定列
INSERT INTO temp_employees (emp_id, emp_name, salary)
SELECT emp_id, emp_name, salary 
FROM employees 
WHERE dept_id = 1;
```

#### 2、UPDATE

##### 基本更新

```sql
-- 更新单个员工的工资
UPDATE employees 
SET salary = 9000 
WHERE emp_id = 102;

-- 更新多个列
UPDATE employees 
SET salary = salary * 1.1,  -- 涨薪10%
    email = 'new_email@company.com'
WHERE emp_id = 101;

-- 基于子查询更新
UPDATE employees 
SET salary = (
    SELECT AVG(salary) 
    FROM employees 
    WHERE dept_id = 1
) 
WHERE emp_id = 103;
```

##### 使用关联更新

```sql
-- 根据部门预算更新员工工资
UPDATE employees e
SET e.salary = e.salary * 1.15
WHERE e.dept_id IN (
    SELECT dept_id 
    FROM departments 
    WHERE budget > 2000000
);

-- 使用关联子查询更新
UPDATE employees e
SET e.salary = (
    SELECT AVG(salary) 
    FROM employees 
    WHERE dept_id = e.dept_id
)
WHERE e.salary < 8000;
```

##### 使用RETURNING字句(Oracle特有)

```sql
-- 插入并返回生成的值
INSERT INTO employees (emp_id, emp_name, salary, dept_id)
VALUES (112, '测试员工', 7500, 1)
RETURNING emp_id, hire_date INTO :new_id, :new_date;

-- 更新并返回受影响的行
UPDATE employees 
SET salary = salary * 1.1
WHERE dept_id = 2
RETURNING emp_id, emp_name, salary BULK COLLECT INTO emp_ids, emp_names, salaries;
```

#### 3、DELETE

##### 基本删除

```sql
-- 删除特定员工
DELETE FROM employees 
WHERE emp_id = 110;

-- 删除特定条件的记录
DELETE FROM employees 
WHERE salary < 8000 
AND hire_date < DATE '2020-01-01';

-- 删除所有记录（谨慎使用！）
-- DELETE FROM employees;
```

##### 使用子查询删除

```sql
-- 删除没有部门的员工
DELETE FROM employees 
WHERE dept_id IS NULL;

-- 删除预算最低部门的员工
DELETE FROM employees 
WHERE dept_id = (
    SELECT dept_id 
    FROM departments 
    ORDER BY budget ASC 
    FETCH FIRST 1 ROW ONLY
);

-- 使用关联删除
DELETE FROM employees e
WHERE e.hire_date < DATE '2018-01-01'
AND e.dept_id IN (
    SELECT dept_id 
    FROM departments 
    WHERE location = '北京'
);
```

#### 4、MERGE

##### 基本更新并插入

```sql
-- 创建员工更新表
CREATE TABLE employee_updates (
    emp_id NUMBER(10) PRIMARY KEY,
    emp_name VARCHAR2(100),
    salary NUMBER(10,2),
    dept_id NUMBER(5)
);

-- 插入一些测试数据
INSERT INTO employee_updates VALUES (101, '张三丰', 12000, 1);
INSERT INTO employee_updates VALUES (111, '新员工', 7000, 2);

-- 使用MERGE进行插入/更新
MERGE INTO employees e
USING employee_updates eu
ON (e.emp_id = eu.emp_id)
WHEN MATCHED THEN
    UPDATE SET e.emp_name = eu.emp_name, 
               e.salary = eu.salary,
               e.dept_id = eu.dept_id
WHEN NOT MATCHED THEN
    INSERT (emp_id, emp_name, salary, dept_id, hire_date)
    VALUES (eu.emp_id, eu.emp_name, eu.salary, eu.dept_id, SYSDATE);
```

## 四、DQL操作

#### 1、基本查询

```sql
-- 查询所有员工
SELECT * FROM employees;

-- 查询特定列
SELECT emp_id, emp_name, salary 
FROM employees;

-- 带条件的查询
SELECT emp_name, salary, hire_date
FROM employees
WHERE salary > 8000 
AND dept_id = 1;

-- 排序查询
SELECT emp_name, salary, hire_date
FROM employees
ORDER BY salary DESC, hire_date ASC;

-- 分组统计
SELECT dept_id, 
       COUNT(*) as emp_count,
       AVG(salary) as avg_salary,
       MAX(salary) as max_salary
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 8000;
```

#### 2、连接查询

```sql
-- 内连接
SELECT e.emp_name, e.salary, d.dept_name, d.location
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

-- 左外连接
SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
-- 或使用Oracle特有符号(+)
SELECT e.emp_name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id(+) = d.dept_id;

-- 右外连接
SELECT e.emp_name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

-- 全连接
SELECT e.emp_name, d.dept_name
FROM employees e
FULL JOIN departments d ON e.dept_id = d.dept_id;

-- 使用USING子句（当列名相同时）
SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d USING (dept_id);
```

**注意**：`SELECT e.emp_name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id AND e.dept_id = 1;`与`SELECT e.emp_name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id WHERE e.dept_id = 1;`的结果集是不一样的。

## 一、 与MySQL概念差异

## 二、DDL操作

#### 1、CREATE

##### 创建表

**注意**：若要添加对表或字段的`COMMENTS`需要通过单独的`COMMENTS ON TABLE/COLUMN [表/字段名字] IS '注释'`

##### 创建索引

```sql
-- 创建普通索引
CREATE INDEX idx_student_name ON students(name);

-- 创建唯一索引
CREATE UNIQUE INDEX uk_student_email ON students(email);

-- 创建复合索引
CREATE INDEX idx_student_dept_birth ON students(department, birth_date);
```

##### 创建视图

```sql
-- 创建简单视图
CREATE VIEW student_info AS
SELECT student_id, name, email, birth_date
FROM students
WHERE birth_date > DATE '2000-01-01';

#### 2、ALTER

##### 修改表结构

```sql
-- 添加新列
ALTER TABLE students ADD (
    phone_number VARCHAR2(15),
    address VARCHAR2(200)
);

-- 修改列定义
ALTER TABLE students MODIFY (
    email VARCHAR2(150),
    name VARCHAR2(100)
);

-- 删除列
ALTER TABLE students DROP COLUMN phone_number;

-- 重命名列
ALTER TABLE students RENAME COLUMN email TO email_address;

-- 添加约束
ALTER TABLE students ADD CONSTRAINT chk_birth_date 
CHECK (birth_date > DATE '1900-01-01');

-- 删除约束
ALTER TABLE students DROP CONSTRAINT chk_birth_date;

-- 重命名表
ALTER TABLE students RENAME TO university_students;
```

#### 3、DROP

##### 删除操作

```sql
-- 删除表
DROP TABLE student_courses;

-- 删除表（如果存在）
DROP TABLE students CASCADE CONSTRAINTS;

-- 删除视图
DROP VIEW student_info;

-- 删除索引
DROP INDEX idx_student_name;
```

#### 4、TRUNCATE

##### 截断表操作

```sql
-- 清空表数据（保留表结构）
TRUNCATE TABLE student_courses;

-- 清空表数据并立即释放空间
TRUNCATE TABLE students DROP STORAGE;

-- 清空表数据但保留空间分配
TRUNCATE TABLE courses REUSE STORAGE;
```

#### 5、其他操作

##### 注释操作

```sql
-- 为表添加注释
COMMENT ON TABLE students IS '学生基本信息表';

-- 为列添加注释
COMMENT ON COLUMN students.student_id IS '学生ID，主键';
COMMENT ON COLUMN students.name IS '学生姓名';
```

##### 序列创建（用于自增主键）

```sql
-- 创建序列
CREATE SEQUENCE student_seq
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;

-- 在插入时使用序列
INSERT INTO students (student_id, name, email)
VALUES (student_seq.NEXTVAL, '张三', 'zhangsan@email.com');
```

#### 6、DDL操作特点

## 三、DML操作

#### 0、创建示例表

```sql
-- 创建部门表
CREATE TABLE departments (
    dept_id NUMBER(5) PRIMARY KEY,
    dept_name VARCHAR2(100) NOT NULL,
    location VARCHAR2(100),
    budget NUMBER(12,2)
);

#### 1、INSERT

##### 基本插入

```sql
-- 插入部门数据
INSERT INTO departments (dept_id, dept_name, location, budget)
VALUES (1, '技术部', '北京', 5000000);

INSERT INTO departments (dept_id, dept_name, location, budget)
VALUES (2, '销售部', '上海', 3000000);

INSERT INTO departments (dept_id, dept_name, location, budget)
VALUES (3, '人事部', '广州', 1000000);

-- 插入员工数据
INSERT INTO employees (emp_id, emp_name, salary, hire_date, dept_id, email)
VALUES (101, '张三', 8000, DATE '2020-03-15', 1, 'zhangsan@company.com');

INSERT INTO employees (emp_id, emp_name, salary, hire_date, dept_id, email)
VALUES (102, '李四', 7500, DATE '2019-07-20', 1, 'lisi@company.com');

INSERT INTO employees (emp_id, emp_name, salary, hire_date, dept_id, email)
VALUES (103, '王五', 9000, DATE '2018-11-10', 2, 'wangwu@company.com');
```

##### 插入带默认值的数据

```sql
-- 使用SYSDATE作为默认值
INSERT INTO employees (emp_id, emp_name, salary, dept_id, email)
VALUES (104, '赵六', 8500, 2, 'zhaoliu@company.com');

-- 使用DEFAULT关键字
INSERT INTO employees (emp_id, emp_name, salary, hire_date, dept_id)
VALUES (105, '钱七', 8200, DEFAULT, 3);
```

##### 插入多行数据

##### 从其他表插入数据

```sql
-- 创建临时表并插入数据
CREATE TABLE temp_employees AS 
SELECT * FROM employees WHERE 1=0;  -- 只复制结构，不复制数据

-- 从employees表插入数据到temp_employees
INSERT INTO temp_employees 
SELECT * FROM employees WHERE salary > 8000;

-- 插入特定列
INSERT INTO temp_employees (emp_id, emp_name, salary)
SELECT emp_id, emp_name, salary 
FROM employees 
WHERE dept_id = 1;
```

#### 2、UPDATE

##### 基本更新

```sql
-- 更新单个员工的工资
UPDATE employees 
SET salary = 9000 
WHERE emp_id = 102;

-- 更新多个列
UPDATE employees 
SET salary = salary * 1.1,  -- 涨薪10%
    email = 'new_email@company.com'
WHERE emp_id = 101;

-- 基于子查询更新
UPDATE employees 
SET salary = (
    SELECT AVG(salary) 
    FROM employees 
    WHERE dept_id = 1
) 
WHERE emp_id = 103;
```

##### 使用关联更新

```sql
-- 根据部门预算更新员工工资
UPDATE employees e
SET e.salary = e.salary * 1.15
WHERE e.dept_id IN (
    SELECT dept_id 
    FROM departments 
    WHERE budget > 2000000
);

-- 使用关联子查询更新
UPDATE employees e
SET e.salary = (
    SELECT AVG(salary) 
    FROM employees 
    WHERE dept_id = e.dept_id
)
WHERE e.salary < 8000;
```

##### 使用RETURNING字句(Oracle特有)

```sql
-- 插入并返回生成的值
INSERT INTO employees (emp_id, emp_name, salary, dept_id)
VALUES (112, '测试员工', 7500, 1)
RETURNING emp_id, hire_date INTO :new_id, :new_date;

-- 更新并返回受影响的行
UPDATE employees 
SET salary = salary * 1.1
WHERE dept_id = 2
RETURNING emp_id, emp_name, salary BULK COLLECT INTO emp_ids, emp_names, salaries;
```

#### 3、DELETE

##### 基本删除

```sql
-- 删除特定员工
DELETE FROM employees 
WHERE emp_id = 110;

-- 删除特定条件的记录
DELETE FROM employees 
WHERE salary < 8000 
AND hire_date < DATE '2020-01-01';

-- 删除所有记录（谨慎使用！）
-- DELETE FROM employees;
```

##### 使用子查询删除

```sql
-- 删除没有部门的员工
DELETE FROM employees 
WHERE dept_id IS NULL;

-- 删除预算最低部门的员工
DELETE FROM employees 
WHERE dept_id = (
    SELECT dept_id 
    FROM departments 
    ORDER BY budget ASC 
    FETCH FIRST 1 ROW ONLY
);

-- 使用关联删除
DELETE FROM employees e
WHERE e.hire_date < DATE '2018-01-01'
AND e.dept_id IN (
    SELECT dept_id 
    FROM departments 
    WHERE location = '北京'
);
```

#### 4、MERGE

##### 基本更新并插入

```sql
-- 创建员工更新表
CREATE TABLE employee_updates (
    emp_id NUMBER(10) PRIMARY KEY,
    emp_name VARCHAR2(100),
    salary NUMBER(10,2),
    dept_id NUMBER(5)
);

-- 插入一些测试数据
INSERT INTO employee_updates VALUES (101, '张三丰', 12000, 1);
INSERT INTO employee_updates VALUES (111, '新员工', 7000, 2);

## 四、DQL操作

#### 1、基本查询

```sql
-- 查询所有员工
SELECT * FROM employees;

-- 查询特定列
SELECT emp_id, emp_name, salary 
FROM employees;

-- 带条件的查询
SELECT emp_name, salary, hire_date
FROM employees
WHERE salary > 8000 
AND dept_id = 1;

-- 排序查询
SELECT emp_name, salary, hire_date
FROM employees
ORDER BY salary DESC, hire_date ASC;

-- 分组统计
SELECT dept_id, 
       COUNT(*) as emp_count,
       AVG(salary) as avg_salary,
       MAX(salary) as max_salary
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 8000;
```

#### 2、连接查询

```sql
-- 内连接
SELECT e.emp_name, e.salary, d.dept_name, d.location
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

-- 右外连接
SELECT e.emp_name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

-- 全连接
SELECT e.emp_name, d.dept_name
FROM employees e
FULL JOIN departments d ON e.dept_id = d.dept_id;

-- 使用USING子句（当列名相同时）
SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d USING (dept_id);
```

最后修改：2025 年 12 月 02 日

👆请简单粗暴地爱我。

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

Spring框架依赖注入

MySQL知识扩展

Day12 - JDBC驱动安装及其简单使用方法

Java常用注解表

Electron的简介

Vue3可复用解决方案

[工具类] Collections类常用的方法

Spring框架依赖注入

DeepSeek R1/V3 使用本地知识库

集合的forEach方法使用

Oracle基本操作

发表评论 取消回复 使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

Oracle基本操作

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款