Oracle数据库表操作主要分为DDL（数据定义语言）和DML（数据操作语言）两大类。

---

DDL用于管理表结构，包括CREATE（创建表）、ALTER（修改表结构）、DROP（删除表）等操作，执行后自动提交。

---

DML用于管理表数据，包含INSERT（插入数据）、UPDATE（更新数据）、DELETE（删除数据）和SELECT（查询数据）等操作，需要显式COMMIT提交事务。

---

关键区别在于：DDL操作不可回滚，而DML操作在COMMIT前可通过ROLLBACK撤销。

---

特别注意DELETE与DROP的区别：DELETE删除数据保留表结构，DROP则完全删除表。

---

日期数据建议使用TO_DATE函数处理，子查询插入需确保字段数据类型、数量和顺序匹配。

---

Oracle 数据库表操作总结（新建表，删除表，表的增删改查等）

---

Oracle数据库的操作可以分为两大类：DDL（数据定义语言） 用于定义或删除表结构，DML（数据操作语言） 用于管理表中的数据。

---

Oracle 数据库表操作总结表

操作类别	操作名称	核心SQL语法与示例	重要注意事项与最佳实践
DDL (结构定义)	新建表 (CREATE TABLE)	语法: CREATE TABLE 表名 ( 列名1 数据类型 [约束], 列名2 数据类型 [约束], ... ); 示例: sql<br>CREATE TABLE employees (<br> employee_id NUMBER(6) PRIMARY KEY,<br> first_name VARCHAR2(50) NOT NULL,<br> hire_date DATE<br>);<br>	- 需指定列名、数据类型（如 VARCHAR2, NUMBER, DATE）及可选的约束（如 PRIMARY KEY, NOT NULL）。 - 表名和列名应遵循命名规范，见名知意。
	删除表 (DROP TABLE)	语法: DROP TABLE 表名 [PURGE]; 示例: sql<br>-- 删除表，并放入回收站(可恢复)<br>DROP TABLE employees;<br><br>-- 永久删除，不进回收站<br>DROP TABLE employees PURGE;<br>	- 危险操作，执行前务必确认。 - 默认情况（不加PURGE）会将表放入回收站(Recycle Bin)，可使用 FLASHBACK 命令恢复。 - 加上 PURGE 会永久删除，无法恢复，适用于明确不再需要的表，以立即释放空间。
DML (数据管理)	插入数据 (INSERT)	语法: INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...); 示例: sql<br>-- 指定列插入<br>INSERT INTO employees (employee_id, first_name, hire_date)<br>VALUES (101, 'John Doe', SYSDATE);<br><br>-- 不指定列(需提供所有列的值)<br>INSERT INTO employees VALUES (102, 'Jane Smith', SYSDATE);<br>	- 字符串和日期用单引号括起来。 - 事务操作：执行后需 COMMIT 提交事务，才能使更改对其他会话可见；若操作有误，可执行 ROLLBACK 撤销。 - 可使用 INSERT ... SELECT ... 语句从一个表批量复制数据到另一个表。
	修改数据 (UPDATE)	语法: UPDATE 表名 SET 列1 = 新值1, 列2 = 新值2, ... WHERE 条件; 示例: sql<br>-- 更新特定行的薪水<br>UPDATE employees<br>SET salary = salary * 1.1<br>WHERE employee_id = 101;<br><br>-- 更新多列<br>UPDATE employees<br>SET first_name = 'Peter', salary = 60000<br>WHERE employee_id = 103;<br>	- WHERE子句极其重要。缺少 WHERE 子句将更新表中所有行。 - 建议先执行相同条件的 SELECT 语句确认目标范围。 - 事务操作：更改后需要使用 COMMIT 提交。
	删除数据 (DELETE)	语法: DELETE FROM 表名 WHERE 条件; 示例: sql<br>-- 删除特定员工<br>DELETE FROM employees WHERE employee_id = 110;<br><br>-- 删除薪水低于5000的所有员工<br>DELETE FROM employees WHERE salary < 5000;<br><br>-- 删除表中所有数据<br>DELETE FROM employees;<br>	- WHERE 子句同样极其重要，缺少它将删除所有行。 - DELETE 是 DML 操作，会触发触发器、维护索引，并可以通过事务 ROLLBACK 恢复。 - 事务操作：执行后需 COMMIT 或 ROLLBACK。
	查询数据 (SELECT)	语法: SELECT {*, 列名1, 列名2, ...} FROM 表名 [WHERE 条件] [GROUP BY 分组列] [HAVING 分组条件] [ORDER BY 排序列 [ASC|DESC]]; 示例: sql<br>-- 查询特定列<br>SELECT employee_id, first_name FROM employees;<br><br>-- 条件查询与排序<br>SELECT * FROM employees<br>WHERE department_id = 90<br>ORDER BY hire_date DESC;<br><br>-- 使用聚合函数分组查询<br>SELECT department_id, COUNT(*)<br>FROM employees<br>GROUP BY department_id;<br>	- 执行顺序为：FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY。 - 使用 * 通配符选择所有列，在生产环境中建议明确列出所需列以提高性能。 - 可使用 DISTINCT 去除重复行。 - 使用 ROWNUM 伪列可实现分页查询。

💎 核心概念区分

• DDL vs. DML：DDL（如 CREATE, DROP）用于定义或删除数据库对象的结构，执行时通常伴随隐式提交（Implicit Commit）。DML（如 INSERT, UPDATE, DELETE）用于管理数据，其变更需要用户通过 COMMIT 或 ROLLBACK 显式结束事务。

• DELETE vs. DROP：DELETE 用于删除表中的数据行，表结构依然存在。DROP 则用于彻底移除整个表结构及其中的数据。TRUNCATE 是另一种DDL操作，用于快速删除表中所有数据并释放空间，但无法回滚，执行速度比 DELETE 快。

---

Oracle数据库表操作完整总结（基于课堂笔记补充）

---

一、表的创建 (CREATE TABLE)

建表时需要定义的三个核心因素：表名 | 列名 | 列的数据类型

数据类型详解

数据类型	语法格式	说明	取值范围/限制
NUMBER	NUMBER	常规数值，不限定长度	任意数值
NUMBER(p)	NUMBER(4)	整数类型，p表示总位数	-9999 ~ 9999
NUMBER(p,s)	NUMBER(7,3)	小数类型，p总位数，s小数位数	整数位: 7-3=4位，小数位: 3位 范围: -9999.999 ~ 9999.999
VARCHAR2(X)	VARCHAR2(20)	变长字符串，X为最大长度	X最大值为4000
CHAR(X)	CHAR(10)	定长字符串，固定X长度	不足部分自动填充空格
DATE	DATE	日期类型	包含年月日时分秒

建表示例

sql

-- 创建学生信息表
CREATE TABLE STUDENT_INFO(
    SNAME VARCHAR2(20),
    SEX VARCHAR2(10),
    AGE NUMBER(3),
    BIRTHDAY DATE
);

---

二、表的结构维护 (ALTER / RENAME)

操作类型	语法	示例
表重命名	RENAME 原表名 TO 新表名;	RENAME STUDENT_INFO TO T_STUDENT;
列重命名	ALTER TABLE 表名 RENAME COLUMN 原列名 TO 新列名;	ALTER TABLE T_STUDENT RENAME COLUMN SNAME TO STUDENT_NAME;
添加列	ALTER TABLE 表名 ADD 列名 数据类型;	ALTER TABLE T_STUDENT ADD ADDRESS VARCHAR2(100);
删除列	ALTER TABLE 表名 DROP COLUMN 列名;	ALTER TABLE T_STUDENT DROP COLUMN ADDRESS;
修改列数据类型	ALTER TABLE 表名 MODIFY (列名 新数据类型);	ALTER TABLE T_STUDENT MODIFY(STUDENT_NAME VARCHAR2(400));

在Oracle数据库中，ALTER TABLE ADD 操作默认将新列添加到最后。

---

新增列的位置控制语法

添加位置	语法	示例
默认（末尾）	ALTER TABLE 表名 ADD 列名 数据类型;	ALTER TABLE T_STUDENT ADD ADDRESS VARCHAR2(100);
指定插入位置（需要先添加后移动）	Oracle不直接支持 AFTER 或 BEFORE 子句，需间接实现	见下方方案

通过中间表重建（推荐）

-- 步骤1：创建新表，按想要的列顺序定义结构
CREATE TABLE T_STUDENT_NEW (
    STUDENT_NAME VARCHAR2(20),   -- 第1列
    ADDRESS VARCHAR2(100),       -- 第2列（新增列放在这里）
    SEX VARCHAR2(10),            -- 第3列
    AGE NUMBER(3),               -- 第4列
    BIRTHDAY DATE                -- 第5列
);

-- 步骤2：将旧表数据按顺序迁移
INSERT INTO T_STUDENT_NEW (STUDENT_NAME, SEX, AGE, BIRTHDAY)
SELECT STUDENT_NAME, SEX, AGE, BIRTHDAY FROM T_STUDENT;

-- 步骤3：删除旧表，重命名新表
DROP TABLE T_STUDENT;
RENAME T_STUDENT_NEW TO T_STUDENT;

-- 步骤4：重建索引、约束等对象

---

三、表的增删改查 (DML操作)

3.1 增 (INSERT) — 两种语法

语法1：直接插入值

sql

-- 插入单行数据（需注意日期格式转换）
INSERT INTO T_STUDENT VALUES ('张三','男',20,TO_DATE('20060101','YYYYMMDD'));
COMMIT;  -- 提交事务，数据写入表中

-- 可回滚的插入
INSERT INTO T_STUDENT VALUES('李四','女',18,TO_DATE('20040101','YYYYMMDD'));
ROLLBACK;  -- 回滚当前事务，取消数据写入

语法2：通过SELECT语句插入（子查询插入）

sql

-- 将EMP表的内容插入到T_STUDENT表
-- 注意：字段个数、数据类型需与SELECT后面保持一致
INSERT INTO T_STUDENT
SELECT 
    ENAME,
    JOB,
    DEPTNO,
    HIREDATE
FROM EMP;
COMMIT;

INSERT 语法对比

语法类型	格式	特点	适用场景
直接插入	INSERT INTO 表 VALUES (值1, 值2...);	需要按表结构顺序提供所有列的值	插入单行或少量的具体数据
子查询插入	INSERT INTO 表 SELECT 语句;	需要字段个数、数据类型与SELECT一致	批量复制数据

3.2 查 (SELECT)

sql

-- 查询表中所有数据
SELECT * FROM T_STUDENT;

---

SELECT 1 FROM EMP 不会返回表中的任何列，而是返回一个“常数 1”。

---

具体来说：

• 结果是：EMP 表中有多少行数据，查询结果就有多少行。每一行的唯一一列的值都是数字 1。

• 它不操作表里的列：这里的 1 是一个常量表达式，与表里的 empno、ename 等列无关。

举例：如果 EMP 表有 14 行数据，SELECT 1 FROM EMP 会返回 14 行，每行只有一个值 1。

在您之前场景中的常见用途（结合 EXISTS 判断空表）：

sql

-- 判断 EMP 表是否有数据（比 SELECT * 效率高）
SELECT * FROM DUAL WHERE EXISTS (SELECT 1 FROM EMP);
-- 只要 EMP 有至少一行，SELECT 1 就会返回一行 1，整个 EXISTS 判断为 TRUE

为什么用 SELECT 1 而不是 SELECT *？

• 性能好：数据库不需要读取表的实际数据行和列，直接返回常数。

• 意图清晰：只关心“有没有记录”，不关心具体内容。

---

所以简单记：SELECT 1 就是“数人头，每个人头上都贴个数字 1”，不关心表里具体存了什么。

---

COUNT(1) 和 COUNT(*) 在逻辑上同理，都可以用来统计表的行数。

---

它们之间没有性能差异，现代Oracle优化器会自动将 COUNT(1) 和 COUNT(*) 转换为相同的最优执行计划。

---

COUNT(1) 的具体含义

• COUNT(1)：统计“常数1”出现的次数。它不会去检查任何列的值是否为 NULL，因为 1 永远非空。它对表中的每一行都计为1，最终返回总行数。

• 对比 COUNT(*)：COUNT(*) 统计所有行的数量，同样不关心列值是否为 NULL。Oracle优化器会把 COUNT(1) 内部重写为 COUNT(*)，两者执行计划相同。

• 对比 COUNT(列名)：COUNT(列名) 只统计该列不为 NULL 的行数。如果该列有许多 NULL 值，结果可能小于总行数。

总结

• COUNT(1) = COUNT(*)：都统计总行数，性能相同。

• 避免误解：COUNT(1) 不是统计“第一列”，而是统计“常数1”。

• 判空最佳实践：用 WHERE ROWNUM = 1 或 EXISTS，而不是 COUNT。

---

3.3 事务控制命令

命令	作用
COMMIT;	提交事务，将数据变更永久写入数据库
ROLLBACK;	回滚事务，撤销当前会话中未提交的所有数据变更

---

四、核心概念汇总对比

对比维度	DDL (数据定义语言)	DML (数据操作语言)
操作对象	表结构（表、列）	表内部的数据行
典型命令	CREATE, ALTER, DROP, RENAME	INSERT, UPDATE, DELETE, SELECT
事务控制	隐式提交，无法ROLLBACK	需要显式 COMMIT / ROLLBACK
是否可恢复	DROP 后默认进回收站可恢复	DELETE 后可通过 ROLLBACK 恢复（未提交时）

对比维度	COMMIT	ROLLBACK
作用	确认当前事务的所有变更	撤销当前事务的所有变更
执行后	数据永久写入，其他会话可见	数据恢复到事务开始前的状态
能否撤销	不可撤销	不可恢复（已撤销完成）

---

五、快速参考（所有SQL示例汇总）

sql

-- ========== 一、创建表 ==========
CREATE TABLE STUDENT_INFO(
    SNAME VARCHAR2(20),
    SEX VARCHAR2(10),
    AGE NUMBER(3),
    BIRTHDAY DATE
);

-- ========== 二、表结构维护 ==========
-- 表重命名
RENAME STUDENT_INFO TO T_STUDENT;

-- 列重命名
ALTER TABLE T_STUDENT RENAME COLUMN SNAME TO STUDENT_NAME;

-- 添加列
ALTER TABLE T_STUDENT ADD ADDRESS VARCHAR2(100);

-- 删除列
ALTER TABLE T_STUDENT DROP COLUMN ADDRESS;

-- 修改列数据类型
ALTER TABLE T_STUDENT MODIFY(STUDENT_NAME VARCHAR2(400));

-- ========== 三、数据操作 ==========
-- 插入数据（语法1：直接插入）
INSERT INTO T_STUDENT VALUES ('张三','男',20,TO_DATE('20060101','YYYYMMDD'));
COMMIT;

INSERT INTO T_STUDENT VALUES('李四','女',18,TO_DATE('20040101','YYYYMMDD'));
ROLLBACK;

-- 插入数据（语法2：子查询插入）
INSERT INTO T_STUDENT
SELECT ENAME, JOB, DEPTNO, HIREDATE FROM EMP;
COMMIT;

-- 查询数据
SELECT * FROM T_STUDENT;

---

💡 重点提醒

1. 日期处理：插入DATE类型数据时，推荐使用 TO_DATE('日期字符串','格式') 进行显式转换

2. 事务控制：INSERT / UPDATE / DELETE 操作后必须执行 COMMIT 才能让其他会话看到变更

3. 数据类型匹配：使用子查询插入时，务必确保SELECT的字段个数和数据类型与目标表一致