“

教程所用示例表：关注并回复关键字“sql教程示例数据”即可获取，需要的朋友请自取。

引言（Preface）

哈喽，各位朋友(^.^)Y Ya!!欢迎来到我的SQL教程系列！今天我们主要讲数据库中的插入操作（Inserting）。在SQL数据库管理中，插入数据是一项基础而又关键的操作。无论是向数据库中单个表添加新记录，还是将现有数据复制到其他表中，插入操作都扮演着至关重要的角色。本文将专注于数据插入操作，从向单表插入新纪录到一次性向多个表中插入数据，以及限制插入目标列等多个方面展开探讨。通过本教程，您将了解如何灵活运用SQL语法，高效地插入和管理数据，为您的数据库管理工作提供有力的支持。

插入操作（Inserting）

单表数据插入

• Problem：假设你想向单个表插入新纪录。比如，向员工表dept中插入新纪录。
• Solution：可以通过INSERT INTO table_name(field_list) VALUES(value_list)语法实现。

-- 单行插入
INSERT INTO dept(deptNo, dName, loc)
VALUES (50, 'PROGRAMMING', 'BALTIMORE');

-- 插入结果验证
SELECT * FROM dept;

对于DB2、SQL Server、PostgreSQL和MySQL，还可以一次性插入多条记录，VALUES后的值列表之间用逗号分隔：

-- 多行插入
INSERT INTO dept(deptNo, dName, loc)
VALUES (60, 'BIGDATA', 'LOS ANGELES'),
       (70, 'DATA ANALYSE', 'HOUSTON'),
       (80, 'PRODUCT', 'PHOENIX');

-- 插入结果验证
SELECT * FROM dept;

• Discussion：INSERT语句允许你向数据库表中插入新的行。插入单行的语法对所有DBMS都适用。field_list可以省略，但此时的value_list中各个值的顺序就必须与表定义中字段的顺序完全一致，否则就可以插入错误的数据。所以，建议明确给出字段列表，哪怕要插入的是所有字段。此外，在插入数据时，还应该注意列约束。比如，若在定义表时设置某列不可为空，则插入数据时该列就必须给一个明确的值，否则插入操作会报错。

插入默认值

• Problem：在表定义时，可以给列指定默认值。如果你想向包含默认值的列插入数据，则在INSERT语句中可以不用为这些列指定任何值。考虑以下表：

-- MySQL
CREATE TEMPORARY TABLE IF NOT EXISTS
    temp_table
(
    id INTEGER DEFAULT 0
);

如果你想向该表中插入0，则可以不用在INSERT语句的值列表中显式地指定0。当然，你也可以显式地指定默认值，不管默认值是多少。

• Solution：所有DBMS都支持在表定义时使用DEFAULT关键字为列指定默认值。下面的示例说明了DEFAULT关键字的用法：

INSERT INTO temp_table VALUES (DEFAULT);

-- 插入结果验证
SELECT * from temp_table;

你也可以显式地指定列名，并且任何时候，只要你不对所有列做插入操作，都需要这样做：

INSERT INTO temp_table(id)
VALUES (DEFAULT);

在MySQL中，如果所有列都定义了默认值，则可以指定一个空的值列表，表示为所有列插入默认：

INSERT INTO temp_table VALUES ();

如果你是PostgreSQL或SQL Server用户，则用DEFAULT VALUES子句插入默认值：

INSERT INTO temp_table DEFAULT VALUES;

• Discussion：值列表中的DEFAULT关键字将会为列插入表定义时指定的默认值。该关键字适用于所有DBMS。对于既有默认值列，也有非默认值列的表，在做插入操作时，只需要从字段列表中排除设置了默认值的列，执行插入操作时就会自动给那些列插入默认值。

使用NULL覆盖默认值

• Problem：当你在对含有默认值的列执行插入时，如何覆盖已定义的默认值？考虑以下表：

CREATE TEMPORARY TABLE IF NOT EXISTS
    temp_table2
(
    id INTEGER DEFAULT 0,
    name VARCHAR(10)
);

• Solution：可以在值列表中给含有默认值的列显式地指定NULL或与默认值不同的值即可覆盖该列的默认值：

INSERT INTO temp_table2 (id, name)
VALUES (NULL, 'Database');

-- 插入结果验证
SELECT * from temp_table2;

• Discussion：也许有些人并不知道可以在INSERT语句的值列表中指定NULL。通常，如果你不想为某列指定插入值时，将其从INSERT语句的字段和值列表中移除即可。

INSERT INTO temp_table2 (name)
VALUES ('Database');

将一个表的数据复制到另一个表

• Problem：假设你想通过一个查询将一个表的数据复制到另一个表，也就是将查询结果集拷贝到另一个表。比如，存在一个表结构和dept完全一样的表dept_v2，如何将dept表的数据插入到dept_v2中？
• Solution：在INSERT语句后紧跟着一个查询即可。

INSERT INTO dept_v2 (deptNo, dName, loc)
SELECT *
FROM dept
WHERE loc IN ('NEW YORK', 'BOSTON');

-- 插入结果验证
SELECT * from dept_v2;

• Discussion：在INSERT语句后紧跟着一个查询即可将其结果集插入到目标表中。与常规的插入方式一样，你可以不用显式指定要插入的字段列表。但是，如果不指定目标列的话，你就必须注意值列表和表定义中字段列表的顺序保持一致。

表结构拷贝

• Problem：假设你想创建一个新表，其表结构与现有表完全一样。比如，想创建部门表dept的拷贝dept_v2，你不想通过常规的表定义语句创建，想直接拷贝dept表的表结构，该如何实现呢？
• Solution：不同的DBMS支持不同的表结构拷贝方法。

-- DB2
CREATE TABLE dept_v2 LIKE dept;
-- Oracle, MySQL, and PostgreSQL
CREATE TABLE dept_v2
AS
SELECT *
FROM dept
WHERE 1 = 0;
-- SQL Server
SELECT * 
INTO dept_v2
FROM dept
WHERE 1 = 0;

• Discussion：虽然DB2和后面的几种数据库都支持表结构的拷贝，但是二者却有所不同，DB2中的CREATE TABLE target_table LIKE existing_table语法仅仅只是拷贝现有表的表结构。而后面的几种数据库支持的方法不进可以拷贝表结构，还可以同时将查询结果集插入到新表中，只要WHERE中的条件为真即可。

一次向多个表插入数据

• Problem：假设你想将查询结果集插入到多个目标表。比如，将dept表的数据同时插入到dept_v2、dept_v3和dept_v4，他们的表结构完全一致。
• Solution：目前，只有Oracle和DB2支持一次性将查询结果集插入到结构相同的多张表中。

-- Oracle
INSERT ALL
  WHEN loc in ('NEW YORK','BOSTON') THEN
  INTO dept_v2 (deptno,dname,loc) VALUES (deptno,dname,loc)
  WHEN loc = 'CHICAGO' THEN
  INTO dept_v3 (deptno,dname,loc) VALUES (deptno,dname,loc)
  ELSE
  INTO dept_v4 (deptno,dname,loc) VALUES (deptno,dname,loc)
  SELECT deptno,dname,loc
  FROM dept;
  
  -- DB2
  CREATE TABLE dept_v2
( deptno INTEGER,
  dname VARCHAR(10),
  loc VARCHAR(10) CHECK (loc in ('NEW YORK','BOSTON')))
CREATE TABLE dept_v3
( deptno INTEGER,
  dname VARCHAR(10),
  loc VARCHAR(10) CHECK (loc = 'CHICAGO'))
CREATE TABLE dept_v4
( deptno INTEGER,
  dname VARCHAR(10),
  loc VARCHAR(10) CHECK (loc = 'DALLAS'))
  INSERT INTO (
  SELECT * FROM dept_v2 UNION ALL
  SELECT * FROM dept_v3 UNION ALL
  SELECT * FROM dept_v4
  ) SELECT * FROM dept;

• Discussion：Oracle的多表插入使用的是WHEN-THEN-ELSE的逻辑计算，而子句来执行分支判断，然后按条件将数据插入到对应的表中。Oracle支持INSERT FIRST和INSERT ALL则会继续判断剩余的条件，即使前面已经有条件为true。所以推荐使用INSERT ALL方法。，在上述示例中，二者都可以产生相同的结果，但是常规情况下二者却存在差异。一旦遇到条件为true，INSERT FIRST会跳出WHEN-THEN-ELSE的逻辑计算，而INSERT ALL则会继续判断剩余的条件，即使前面已经有条件为true。所以推荐使用INSERT ALL方法。

  DB2的解决方案与Oracle有所不同，它是通过在表定义时设置检查约束实现数据的条件插入。这种技术其实是向通过UNION ALL定义的视图插入数据。此外，如果在INSERT语句的表中检查约束不是唯一的（即多个表具有相同的检查约束），则INSERT语句将不知道将数据插入到哪个表，并且插入将会失败。

插入目标列限制

• Problem：假设你想防止用户或不信任的软件应用程序向表的某些特定列中插入值。例如，你可以允许外部程序向emp表插入数据，但只能插入empNo、eName和job列。
• Solution：可以通过视图实现特定列的插入，从而起到数据安全的作用。创建一个视图，仅包含允许插入的列，然后强制所有对原表的插入操作只能通过该视图进行。比如，考虑以下视图：

CREATE VIEW v_emps
AS
SELECT empNo, eName, job
FROM emp;

给用户和程序授予该视图的插入权限，然后用户就可以通过该视图实现向emp表插入记录的目的。

• Discussion：当你通过上述视图的方法执行数据插入的时候，数据库服务器会将数据插入到底层表。比如，下面的插入：

INSERT INTO v_emps (empNo, eName, job)
VALUES (1, 'Jonathan', 'Editor');

在数据库服务器内部会被翻译为下面的操作：

INSERT INTO emp (empno, ename, job) 
VALUES (1, 'Jonathan', 'Editor');

总结（Conclusion）

在本教程中，我们深入探讨了SQL中插入数据的各种技术和方法。通过学习如何向单表插入新纪录，插入默认值，使用NULL重写默认值，以及将数据从一个表复制到另一个表中等技术，我们不仅提升了SQL操作的熟练程度，还增强了对数据库管理的理解和应用能力。同时，我们还探讨了表结构拷贝和一次性向多个表中插入数据的方法，为数据管理提供了更多选择和灵活性。最后，我们还介绍了如何限制插入目标列来提高数据安全性。希望本教程能够帮助您更好地掌握SQL插入数据操作，并在实际工作中取得更大的成就。

“

如果你觉得这篇文章对你有用，请“点赞➕关注”支持我（关注我，不迷路🤭🤭🤭），你的点赞和关注就是我持续创作分享的动力，让更多的朋友发现并加入我们，共同学习和进步！

Thank you for reading!

- END -

推荐阅读👇👇👇

SQL教程#4Day：多表关联查询#2

SQL教程#3Day：多表关联查询#1

SQL教程#2Day：查询结果排序，order by？

SQL教程#1Day：数据检索