【Oracle11g SQL详解】INSERT INTO 的用法及插入数据注意事项 ...

INSERT INTO 的用法及插入数据注意事项

在 Oracle 11g 中，INSERT INTO 语句用于向表中插入数据，是数据写入操作中最常用的 SQL 语句之一。本文将详细介绍 INSERT INTO 的基本语法、常见场景、注意事项及常见错误处置惩罚。

---

一、INSERT INTO 的基本语法

INSERT INTO 支持两种基本用法：

• 指定列插入（推荐，明确指定列名，有助于维护性）。
  
• 完备插入（省略列名，插入所有列的值）。
  

---

1. 指定列插入

1. INSERT INTO 表名 (列名1, 列名2, ...)
2. VALUES (值1, 值2, ...);

复制代码

示例：向 employees 表插入一个新员工纪录

1. INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary)
2. VALUES (207, 'Tom', 'Hanks', TO_DATE('2024-11-26', 'YYYY-MM-DD'), 5500);

复制代码

说明：

• 必要严格按照列的顺序提供值。
  
• 数据类型必须匹配列界说（如日期、数字、字符等）。
  

---

2. 完备插入

1. INSERT INTO 表名
2. VALUES (值1, 值2, ...);

复制代码

示例：插入所有列数据

1. INSERT INTO departments
2. VALUES (280, 'HR', 101, 1700);

复制代码

注意：

• 此用法会插入表中所有列的数据，因此必须提供表中所有列的值。
  
• 表中界说了默认值或允许 NULL 的列可自动填充，但不推荐省略。
  

---

二、INSERT INTO 的变体

1. 插入多行数据

Oracle 支持通过多次 INSERT 实现批量插入，但不支持单条语句直接插入多行。
示例：批量插入多个员工纪录

1. INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary)
2. VALUES (208, 'John', 'Doe', TO_DATE('2024-11-26', 'YYYY-MM-DD'), 6000);
4. INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary)
5. VALUES (209, 'Jane', 'Smith', TO_DATE('2024-11-26', 'YYYY-MM-DD'), 7200);

复制代码

优化方式： 使用 PL/SQL 块或外部数据工具（如 SQL*Loader）实现批量插入。

---

2. 从其他表插入数据

通过 INSERT INTO ... SELECT 的方式将查询效果插入目标表。

1. INSERT INTO 表名 (列名1, 列名2, ...)
2. SELECT 列名1, 列名2, ...
3. FROM 另一个表
4. [WHERE 条件];

复制代码

示例：将 temp_employees 表中员工数据复制到 employees 表

1. INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary)
2. SELECT employee_id, first_name, last_name, hire_date, salary
3. FROM temp_employees
4. WHERE hire_date >= TO_DATE('2024-01-01', 'YYYY-MM-DD');

复制代码

说明：

• 确保目标表和源表的列类型匹配。
  
• 可以配合 WHERE 子句实现条件插入。
  

---

3. 使用子查询插入默认值

当必要插入某些列的默认值时，可以使用子查询。
示例：插入新部分并设置默认值

1. INSERT INTO departments (department_id, department_name, manager_id, location_id)
2. VALUES (290, 'Marketing', NULL, (SELECT location_id FROM locations WHERE city = 'New York'));

复制代码

---

三、INSERT INTO 的常见注意事项

• 插入列顺序与数据匹配
  列的顺序必须与提供的值一一对应，否则可能出现错误。
  错误示例：
  
  1. INSERT INTO employees (first_name, employee_id)
  2. VALUES (207, 'Tom'); -- 错误，列顺序不匹配

  复制代码
• 遵守表约束
  插入数据时需满意表的约束条件：
  
  
  
  • 主键 (PRIMARY KEY)：禁止重复。
    
  • 唯一性约束 (UNIQUE)：插入值必须唯一。
    
  • 非空约束 (NOT NULL)：禁止插入空值。
    
  • 外键约束 (FOREIGN KEY)：插入值必须在父表中存在。
    
  
  
• 数据类型匹配
  
  
  
  • 插入值需与列的数据类型同等，否则可能触发类型转换错误。
    
  • 日期类型值需使用 TO_DATE 转换。
    
  
  
• 性能优化建议
  
  
  
  • 批量插入建议使用 PL/SQL 批处置惩罚或外部工具，减少事件开销。
    
  • 插入前可禁用索引或触发器，完成后再启用，提升性能。
    
  
  

---

四、常见错误及解决方法

• 违反主键约束
  错误信息： ORA-00001: unique constraint violated
  原因： 插入了重复的主键值。
  解决方法：
  
  
  
  • 确认新插入的数据主键是否唯一。
    
  • 预先检查主键值是否已存在：
    1. SELECT employee_id FROM employees WHERE employee_id = 207;

    复制代码
  
  
• 违反非空约束
  错误信息： ORA-01400: cannot insert NULL into ("列名")
  原因： 未为 NOT NULL 列提供值。
  解决方法： 提供必要的非空列值或修改表结构允许空值。
  
• 数据类型不匹配
  错误信息： ORA-01722: invalid number
  原因： 插入的值类型与列界说类型不匹配。
  解决方法： 检查并转换插入数据的类型。
  

---

五、实践案例

• 向表中插入一条完备纪录
  
  1. INSERT INTO employees
  2. (employee_id, first_name, last_name, hire_date, salary, department_id)
  3. VALUES
  4. (210, 'Alice', 'Brown', TO_DATE('2024-11-26', 'YYYY-MM-DD'), 8000, 10);

  复制代码
• 复制符合条件的数据到新表
  
  1. INSERT INTO high_salary_employees (employee_id, first_name, last_name, salary)
  2. SELECT employee_id, first_name, last_name, salary
  3. FROM employees
  4. WHERE salary > 7000;

  复制代码
• 插入缺省值与盘算效果
  
  1. INSERT INTO departments (department_id, department_name, manager_id, location_id)
  2. VALUES (300, 'Research', NULL, (SELECT location_id FROM locations WHERE city = 'San Francisco'));

  复制代码

---

六、小结

INSERT INTO 是向表插入数据的核心 SQL 语句，实用于单行插入、批量插入以及基于查询的插入。正确使用 INSERT INTO 能有效地完成数据写入任务，而掌握其常见问题与优化方法则有助于避免错误并进步性能。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。