insert语句添加条件 mysql 利用insert语句添加一条记录

转载

索姆拉 2023-07-14 14:14:42

文章标签 insert语句添加条件 mysql SQL 数据 ci 文章分类 MySQL 数据库

一、数据插入

1.1 插入完整的行
1.2 插入部分行
1.3 插入检索出的数据

二、从一个表复制到另一个表
三、小结

本文介绍如何使用 SQL INSERT 语句将数据插入到表中，如何用 INSERT SELECT 从其他表中导入行，如何用 SELECT INTO 将行导出到一个新表。

一、数据插入

毫无疑问，SELECT 是最常用的 SQL 语句了。但是，还有其他 3 个常用的 SQL 语句需要学习。第一个就是 INSERT（如何使用 SQL UPDATE 和 DELETE 语句更新或删除表数据介绍另外两个）。

顾名思义，INSERT 用来将行插入（或添加）到数据库表。插入有几种方式：

插入完整的行；
插入行的一部分；
插入某些查询的结果。

下面逐一介绍这些内容。

提示：插入及系统安全
使用 INSERT 语句可能需要客户端/服务器 DBMS 中的特定安全权限。在你试图使用 INSERT 前，应该保证自己有足够的安全权限。

1.1 插入完整的行

把数据插入表中的最简单方法是使用基本的 INSERT 语法，它要求指定表名和插入到新行中的值。下面举一个例子：

INSERT INTO Customers
VALUES(1000000006,
       'Toy Land',
       '123 Any Street',
       'New York',
       'NY',
       '11111',
       'USA',
       NULL,
       NULL);

这个例子将一个新顾客插入到 Customers 表中。

存储到表中每一列的数据在 VALUES 子句中给出，必须给每一列提供一个值。

如果某列没有值，如上面的 cust_contact 和 cust_email 列，则应该使用 NULL 值（假定表允许对该列指定空值）。各列必须以它们在表定义中出现的次序填充。

提示：INTO 关键字
在某些 SQL 实现中，跟在 INSERT 之后的 INTO 关键字是可选的。
但是，即使不一定需要，最好还是提供这个关键字，这样做将保证 SQL 代码在 DBMS 之间可移植。

虽然这种语法很简单，但并不安全，应该尽量避免使用。

上面的 SQL 语句高度依赖于表中列的定义次序，还依赖于其容易获得的次序信息。即使可以得到这种次序信息，也不能保证各列在下一次表结构变动后保持完全相同的次序。

因此，编写依赖于特定列次序的 SQL 语句是很不安全的，这样做迟早会出问题。

编写 INSERT 语句的更安全（不过更烦琐）的方法如下：

INSERT INTO Customers(cust_id,
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state,
                      cust_zip,
                      cust_country,
                      cust_contact,
                      cust_email)
VALUES(1000000006,
       'Toy Land',
       '123 Any Street',
       'New York',
       'NY',
       '11111',
       'USA',
       NULL,
       NULL);

这个例子与前一个 INSERT 语句的工作完全相同，但在表名后的括号里明确给出了列名。

在插入行时，DBMS 将用 VALUES 列表中的相应值填入列表中的对应项。VALUES 中的第一个值对应于第一个指定列名，第二个值对应于第二个列名，如此等等。

因为提供了列名，VALUES 必须以其指定的次序匹配指定的列名，不一定按各列出现在表中的实际次序。其优点是，即使表的结构改变，这条 INSERT 语句仍然能正确工作。

说明：不能插入同一条记录两次
如果你尝试了这个例子的两种方法，会发现第二次生成了一条出错消息，说 ID 为 1000000006 的顾客已经存在。
在学习 SQL 之前需要了解的基础知识我们说过，主键的值必须有唯一性，而 cust_id 是主键，DBMS 不允许插入相同 cust_id 值的新行。
下一个例子也是同样的道理。要想再尝试另一种插入方法，需要首先删除掉已经插入的记录（请参阅如何使用 SQL UPDATE 和 DELETE 语句更新或删除表数据）。
要么就别尝试新方法了，反正记录已经插入好，你可以继续往下学习。

下面的 INSERT 语句填充所有列（与前面的一样），但以一种不同的次序填充。因为给出了列名，所以插入结果仍然正确：

INSERT INTO Customers(cust_id,
                      cust_contact,
                      cust_email,
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state,
                      cust_zip)
VALUES(1000000006,
       NULL,
       NULL,
       'Toy Land',
       '123 Any Street',
       'New York',
       'NY',
       '11111');

提示：总是使用列的列表
不要使用没有明确给出列的 INSERT 语句。给出列能使 SQL 代码继续发挥作用，即使表结构发生了变化。
注意：小心使用 VALUES
不管使用哪种 INSERT 语法，VALUES 的数目都必须正确。
如果不提供列名，则必须给每个表列提供一个值；如果提供列名，则必须给列出的每个列一个值。
否则，就会产生一条错误消息，相应的行不能成功插入。

1.2 插入部分行

正如所述，使用 INSERT 的推荐方法是明确给出表的列名。使用这种语法，还可以省略列，这表示可以只给某些列提供值，给其他列不提供值。

请看下面的例子：

INSERT INTO Customers(cust_id,
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state,
                      cust_zip,
                      cust_country)
VALUES(1000000006,
       'Toy Land',
       '123 Any Street',
       'New York',
       'NY',
       '11111',
       'USA');

在本文前面的例子中，没有给 cust_contact 和 cust_email 这两列提供值。这表示没必要在 INSERT 语句中包含它们。因此，这里的 INSERT 语句省略了这两列及其对应的值。

注意：省略列
如果表的定义允许，则可以在 INSERT 操作中省略某些列。省略的列必须满足以下某个条件。
该列定义为允许 NULL 值（无值或空值）。
在表定义中给出默认值。这表示如果不给出值，将使用默认值。
注意：省略所需的值
如果表中不允许有 NULL 值或者默认值，这时却省略了表中的值，DBMS 就会产生错误消息，相应的行不能成功插入。

1.3 插入检索出的数据

INSERT 一般用来给表插入具有指定列值的行。

INSERT 还存在另一种形式，可以利用它将 SELECT 语句的结果插入表中，这就是所谓的 INSERT SELECT。

顾名思义，它是由一条 INSERT 语句和一条 SELECT 语句组成的。

假如想把另一表中的顾客列合并到 Customers 表中，不需要每次读取一行再将它用 INSERT 插入，可以如下进行：

INSERT INTO Customers(cust_id,
                      cust_contact,
                      cust_email,
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state,
                      cust_zip,
                      cust_country)
SELECT cust_id,
       cust_contact,
       cust_email,
       cust_name,
       cust_address,
       cust_city,
       cust_state,
       cust_zip,
       cust_country
FROM CustNew;

说明：新例子的说明
这个例子从一个名为 CustNew 的表中读出数据并插入到 Customers 表。为了试验这个例子，应该首先创建和填充 CustNew 表。
CustNew 表的结构与学习 SQL 所用到的样例表脚本中描述的 Customers 表相同。
在填充 CustNew 时，不应该使用已经在 Customers 中用过的 cust_id 值（如果主键值重复，后续的 INSERT 操作将会失败）。

这个例子使用 INSERT SELECT 从 CustNew 中将所有数据导入 Customers。

SELECT 语句从 CustNew 检索出要插入的值，而不是列出它们。

SELECT 中列出的每一列对应于 Customers 表名后所跟的每一列。这条语句将插入多少行呢？这依赖于 CustNew 表有多少行。

如果这个表为空，则没有行被插入（也不产生错误，因为操作仍然是合法的）。如果这个表确实有数据，则所有数据将被插入到 Customers。

提示：INSERT SELECT 中的列名
为简单起见，这个例子在 INSERT 和 SELECT 语句中使用了相同的列名。但是，不一定要求列名匹配。
事实上，DBMS 一点儿也不关心 SELECT 返回的列名。
它使用的是列的位置，因此 SELECT 中的第一列（不管其列名）将用来填充表列中指定的第一列，第二列将用来填充表列中指定的第二列，如此等等。

INSERT SELECT 中 SELECT 语句可以包含 WHERE 子句，以过滤插入的数据。

提示：插入多行
INSERT 通常只插入一行。要插入多行，必须执行多个 INSERT 语句。
INSERT SELECT 是个例外，它可以用一条 INSERT 插入多行，不管 SELECT 语句返回多少行，都将被 INSERT 插入。

二、从一个表复制到另一个表

有一种数据插入不使用 INSERT 语句。要将一个表的内容复制到一个全新的表（运行中创建的表），可以使用 CREATE SELECT 语句（或者在 SQL Server 里也可用 SELECT INTO 语句）。

说明：DB2 不支持
DB2 不支持这里描述的 CREATE SELECT。

与 INSERT SELECT 将数据添加到一个已经存在的表不同，CREATE SELECT 将数据复制到一个新表（有的 DBMS 可以覆盖已经存在的表，这依赖于所使用的具体 DBMS）。

下面的例子说明如何使用 CREATE SELECT：

CREATE TABLE CustCopy AS SELECT * FROM Customers;

若是使用 SQL Server，可以这么写：

SELECT * INTO CustCopy FROM Customers;

这条 SELECT 语句创建一个名为 CustCopy 的新表，并把 Customers 表的整个内容复制到新表中。

因为这里使用的是 SELECT *，所以将在 CustCopy 表中创建（并填充）与 Customers 表的每一列相同的列。要想只复制部分的列，可以明确给出列名，而不是使用 * 通配符。

在使用 SELECT INTO 时，需要知道一些事情：

任何 SELECT 选项和子句都可以使用，包括 WHERE 和 GROUP BY；
可利用联结从多个表插入数据；
不管从多少个表中检索数据，数据都只能插入到一个表中。

提示：进行表的复制
SELECT INTO 是试验新 SQL 语句前进行表复制的很好工具。先进行复制，可在复制的数据上测试 SQL 代码，而不会影响实际的数据。
说明：更多例子
如果想看 INSERT 用法的更多例子，请参阅学习 SQL 所用到的样例表脚本中给出的样例表填充脚本。