SQL数据插入操作详解，让你不再迷茫

TechTornado

已于 2024-03-29 15:35:11 修改

阅读量6.2k

点赞数 68

分类专栏：科学计算数据库操作文章标签： sql 数据库 mysql

于 2024-03-29 15:34:12 首次发布

本文链接：https://blog.csdn.net/techtornado/article/details/137146868

版权

科学计算同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

数据库操作

1 篇文章 0 订阅

订阅专栏

一、SQL数据插入操作简介

1.1 数据插入的作用

SQL数据插入操作是SQL语言最基础、也是最常用的数据库操作之一。它用于向表中插入新的数据行或更新现有的数据行，是构建和维护数据库的重要手段之一。

1.2 插入数据的基本语法

要执行数据插入操作，需使用INSERT INTO语句，语法如下：

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

其中，表名是要插入数据的表名，列1、列2、列3...是表中要插入数据的列名，值1、值2、值3...是要插入的数据值。

1.3 常用的数据插入方式

在数据插入过程中，常用的方式包括：

（1）插入一条数据：使用INSERT语句插入一条数据，即向指定表的指定列插入一行数据。

（2）插入多条数据：使用INSERT语句一次性向表的指定列插入多行数据。

（3）插入特定列的数据：在插入数据时，可以只插入表中的特定列，这样更加灵活，也更容易处理数据。

（4）插入默认值：在插入数据时，可以留空某些数据列，让数据库自动分配默认值。

（5）插入查询结果：在插入数据时，可以使用SELECT语句查询出需要插入的数据，然后将查询结果存储到指定的表中。

通过以上几种常见的数据插入方式，可以很方便地增加和修改数据库的数据，为后续的数据处理和维护提供有力的支持。

二、使用INSERT语句插入数据

数据插入操作是SQL的基础操作之一，而使用INSERT语句是最常见的数据插入方式之一。下面将详细介绍如何使用INSERT语句插入数据。

2.1 插入一行数据

最简单的数据插入操作是插入一行数据到数据表中，通常使用INSERT INTO语句完成，示例如下：

INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …);

其中，table_name是要插入数据的数据表名，括号中的column1,column2等为列名，values后的value1,value2等为对应列的数据值。

举个例子，假设有一张名为employee的表，其包含姓名（name），年龄（age），性别（gender）三列，即employee(name, age, gender)，数据类型如下：

CREATE TABLE employee(name VARCHAR(20), age INT(2), gender VARCHAR(8));

现在向表中插入一行数据，可以使用以下语句：

INSERT INTO employee (name, age, gender) VALUES ('Tom', 24, 'male');

语句意思是向employee表中插入一行数据，其中name为"Tom", age为24，gender为"male"。

2.2 插入多行数据

如果要一次性插入多行数据到数据表中，则需要使用INSERT INTO语句和VALUES子句，示例如下：

INSERT INTO table_name (column1, column2, …) VALUES
(value1, value2, …),
(value3, value4, …),
......
(valueN, valueM, …);

该语句中，要插入的数据以逗号分隔，并用括号括起来。每行数据都以逗号分隔，而行之间则以英文逗号隔开。

举个例子，要向employee表插入两行数据，可以使用以下语句：

INSERT INTO employee (name, age, gender) VALUES
('Tom', 24, 'male'),
('Lucy', 23, 'female');

语句意思是向employee表中插入两行数据，第一行数据为name为"Tom", age为24，gender为"male"，第二行数据为name为"Lucy", age为23，gender为"female"。

2.3 插入特定列的数据

在某些情况下，只需要向数据表中的特定列插入数据。此时，可以使用INSERT INTO语句的具体列名的语法，具体语法如下：

INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …);

也就是说，在插入数据时，只需要制定需要插入的列名即可。

例如，要向employee表的第一列（name）和第三列（gender）插入数据，可以使用以下语句：

INSERT INTO employee (name, gender) VALUES ('Tom', 'male');

2.4 插入默认值

在某些情况中，在插入数据时，如果某些列没有数据，可以向数据库表中插入默认值，例如使用SQL保留字DEFAULT或下列关键字：

INSERT INTO table_name VALUES(DEFAULT, value2, …);

或者你可以使用以下语句：

INSERT INTO table_name (column1, column2, …) VALUES (NULL, value2, …);

其中DEFAULT表示column1对应的默认值，NULL表示该列的该行值为空。

2.5 插入查询结果

SQL的强大之处就是可以联合查询多张表，然后将查询结果插入到一张表中。这种操作使用的是 INSERT INTO SELECT结构，该结构可以从一个或多个现有的数据表中选择行并将其插入到指定的表中。

INSERT INTO table_name (column1, column2, …) SELECT column1, column2, … FROM table_name WHERE condition;

其中，table_name为插入数据的目标数据表名，column1, column2, …为插入时指定的列名，SELECT语句则为从源数据表中查询出需要插入的数据行，并提取对应列的数据。

例如，假设要从名为employee的数据表中选择对应年龄为“24”的所有行，然后将结果插入名为employee_1的数据表，可以使用以下语句：

INSERT INTO employee_1 (name, age, gender)
SELECT name, age, gender FROM employee WHERE age = 24;

以上SQL语句的意思是从employee表中选择年龄为24的行，并提取对应的name, age, gender列，然后将这些数据插入到employee_1表中。

总之，数据插入操作使用INSERT INTO语句来实现，它可以插入一行或多行数据，也可以插入指定的列或运用SELECT语句选择行并将其插入到指定的表中。

三、插入数据时常见错误

在进行SQL数据插入操作时，可能出现一些常见的错误，下面将详细介绍这些错误及其解决方法。

3.1 列和值的数量不匹配

该错误通常会出现在插入数据时，指定的列数量与插入的值数量不匹配时。例如：

INSERT INTO employee(name, age, gender) VALUES ('George', 22);

上述SQL语句表示想向employee表中插入一行数据，包括name，age和gender三列，但是实际只给出了两个值，这将导致数量不匹配的错误。

解决方法是使用正确数量的列和值。在插入数据时应该确保列和值的数量一致。

3.2 错误的数据类型

使用INSERT语句向表中插入数据时，应该确保列的数据类型与插入的值的数据类型匹配。如果插入的值的数据类型与表列的数据类型不匹配，将会导致数据类型错误。

例如，若employee表列定义是如下：

CREATE TABLE employee(
  name VARCHAR(20) NOT NULL,
  age INT(2) NOT NULL,
  gender VARCHAR(8) NOT NULl
);

而插入以下数据：

INSERT INTO employee VALUES ('Mike', 18, 'Male');

就会抛出错误：

ERROR 1265 (01000): Data truncated for column age

解决方法是确保插入的数据类型和表列定义一致。

3.3 主键或唯一索引重复

一些表可能存在主键（PRIMARY KEY）或唯一索引（UNIQUE INDEX）的限制，这意味着唯一的值必须通过插入才能保证数据表的正确性。如果插入的值已经存在于PRIMARY KEY或UNIQUE INDEX中，则会收到以下错误信息：

ERROR 1062 (23000): Duplicate entry 'value' for key 'PRIMARY'

解决方法是检查表结构并更新数据，以确保插入的值是唯一的。

3.4 表不存在或名字错误

如果要插入的数据表不存在或表名拼写错误，INSERT语句将无法执行并出现以下错误提示：

ERROR 1146 (42S02): Table 'database.table' doesn't exist

解决方法是确认表名是否正确，并确保数据表已存在于所选的数据库中。

总之，插入数据时常见的错误包括列和值的数量不匹配、错误的数据类型、主键或唯一索引重复和表不存在或名字错误等，需要认真检查并修正错误。

四、使用INSERT INTO SELECT语句插入数据

除了使用INSERT INTO语句插入数据外，还可以使用INSERT INTO SELECT语句从现有的表、查询、视图和子查询中选择数据，并将这些数据插入到另一个表中。下面将详细介绍如何使用INSERT INTO SELECT语句插入数据。

4.1 插入全部列

使用INSERT INTO SELECT语句插入所有列的数据，需要使用以下语法：

INSERT INTO table1 SELECT * FROM table2;

其中table1是要插入数据的目标表名，table2是提供数据的源表名。

举个例子，在两个名为employee和employee_1的表中，employee表中包含name、age和gender三列数据，employee_1表为空，可以使用以下语句将employee表的数据插入到employee_1表中：

INSERT INTO employee_1 SELECT * FROM employee;

4.2 插入特定列

如果只想选择表的特定列来进行插入操作，可以使用以下语法：

INSERT INTO table1 (column1, column2, …) SELECT column1, column2, … FROM table2;

其中，table1是目标表，column1，column2是要插入的目标表列名，table2是数据源表名，column1、column2是数据源表中要插入的列名。

例如，对于上述例子，只需要向employee_1表中插入employee表的name和age两列数据，可以使用以下语句：

INSERT INTO employee_1 (name, age) SELECT name, age FROM employee;

4.3 插入多个表的数据

如果要从多个表中选择数据并将这些数据插入到一个表中，可以将多个SELECT语句组合在一个INSERT INTO SELECT语句中。具体语法如下：

INSERT INTO table1 (column1, column2, …) 
SELECT column1, column2, … FROM table2 
UNION 
SELECT column1, column2, … FROM table3 
UNION 
.............
SELECT column1, column2, … FROM tableN;

其中，table1是目标数据表，column1,column2,... 是要插入到数据表的列名，table2, table3, ..., tableN是提供数据的源表名。

例如，如果有employee_1和employee_2两个表，要把这两个表中age大于24的员工信息插入到employee表中，可以使用以下语句：

INSERT INTO employee (name, age, gender) 
SELECT name, age, gender FROM employee_1 WHERE age>24 
UNION 
SELECT name, age, gender FROM employee_2 WHERE age>24;

以上SQL语句的意思是从employee_1表中选择年龄大于24的行并提取对应的name, age, gender列，然后与employee_2中相同的行合并。最终将这些记录插入到employee表中，包括name, age和gender三列。

总之，使用INSERT INTO SELECT语句插入数据可以从一个或多个表中选择数据并将其插入到其他表中，这是一个十分有用的功能，能够大大提高数据插入的灵活性。

五、如何优化SQL插入数据的性能

在面对大量数据需要插入的时候，SQL的插入操作的效率将会成为问题，插入速度变缓可能会对系统造成很大的压力。这时候，需要优化插入操作的性能。下面将详细介绍如何优化SQL插入操作的性能。

5.1 使用单个事务插入多行数据

在大量数据需要插入时，采用使用单个事务插入多行数据的方式，可以明显提高插入性能。默认情况下，每次执行插入操作都会对表格进行一次写入，并提交这个操作到数据库，如果有大量记录，这将导致数据库服务器性能的下降。

实际上，使用单个事务将所有插入操作在一次提交中完成，可以减轻插入的巨大压力，并大大提高插入速度。例如，在MySQL中，可以使用以下语句：

START TRANSACTION; 
INSERT INTO employee(name, age, gender) VALUES('John', '25', 'male');
INSERT INTO employee(name, age, gender) VALUES('Kate', '26', 'female');
INSERT INTO employee(name, age, gender) VALUES('Mary', '27', 'female');
...
COMMIT;

其中START TRANSACTION表示启动一个事务，INSERT INTO employee表示插入的数据表名，每个INSERT语句插入一行数据，COMMIT表示提交事务。

使用事务可以减少开销，确保操作的原子性、一致性和隔离性，避免了数据的不一致和无效提交，同时也显著提高插入性能。

5.2 使用预处理语句

使用预处理语句是通过预编译SQL语句并绑定变量来实现的，这可以减少重复的SQL解析，提高应用系统的执行效率和管理效率。通常，预处理语句的效率会比普通的SQL语句高出很多，这在插入大量数据时非常有用。

例如，在MySQL中，可以使用以下语句使用预处理语句：

PREPARE stmt FROM 'INSERT INTO employee(name, age, gender) VALUES(?, ?, ?)';
SET @name = 'John', @age = '25', @gender = 'male';
EXECUTE stmt USING @name, @age, @gender;
SET @name = 'Kate', @age = '26', @gender = 'female';
EXECUTE stmt USING @name, @age, @gender;
SET @name = 'Mary', @age = '27', @gender = 'female'; 
...
DEALLOCATE PREPARE stmt;

其中，PREPARE stmt表示准备多个插入操作的预处理语句，SET表示分别为变量@name，@age和@gender赋值，EXECUTE用于执行预处理语句并将变量作为参数传递，DEALLOCATE PREPARE用于完成操作。

使用预处理语句可以一次性执行多个结果集插入操作，减少SQL查询解析代码和处理指令，可以大幅度缩短插入时间，提高性能。