INSERT 插入

本文档介绍如何使用 SQL 语句在 MatrixOne 中插入数据。

开始前准备

已完成单机部署 MatrixOne

INSERT INTO 语句

INSERT INTO 语句有以下写法:

  1. 指定要插入的列名和值:

    1. INSERT INTO tbl_name (a,b,c) VALUES (1,2,3);
  2. 如果要为表的所有列添加值,则不需要在 SQL 查询中指定列名。必须确保值的顺序与表中列的顺序相同。INSERT INTO 语法如下:

    1. INSERT INTO tbl_name VALUES (1,2,3);
  3. 使用 INSERT...VALUES... 语句可以插入多行。语句中必须包含多个用逗号分隔的值列表,值列表用圆括号括起来,并用逗号分隔。示例如下:

    1. INSERT INTO tbl_name (a,b,c) VALUES(1,2,3), (4,5,6), (7,8,9);

数据库示例

以下是从 Northwind 示例数据库中的 Customers 表中选择的表示例:

  1. CREATE TABLE Customers (
  2. CustomerID INT AUTO_INCREMENT NOT NULL,
  3. CustomerName VARCHAR(40) NOT NULL,
  4. ContactName VARCHAR(30) NULL,
  5. Address VARCHAR(60) NULL,
  6. City VARCHAR(15) NULL,
  7. PostalCode VARCHAR(10) NULL,
  8. Country VARCHAR(15) NULL,
  9. PRIMARY KEY (CustomerID)
  10. );
CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
89White Clover MarketsKarl Jablonski305 - 14th Ave. S. Suite 3BSeattle98128USA
90Wilman KalaMatti KarttunenKeskuskatu 45Helsinki21240Finland
91WolskiZbyszekul. Filtrowa 68Walla01-012Poland

INSERT INTO 示例

下面的 SQL 语句在 Customers 表中插入了一条新记录:

Example

  1. INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
  2. VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

Customers 表展示出来如下所示:

CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
89White Clover MarketsKarl Jablonski305 - 14th Ave. S. Suite 3BSeattle98128USA
90Wilman KalaMatti KarttunenKeskuskatu 45Helsinki21240Finland
91WolskiZbyszekul. Filtrowa 68Walla01-012Poland
92CardinalTom B. ErichsenSkagen 21Stavanger4006Norway

仅在指定列中插入数据

MatrixOne 也支持使用 SQL 语句仅在特定列中插入数据。

示例

使用下面的 SQL 语句将插入一条新记录,但只插入 CustomerNameCityCountry 列中的数据,同时 CustomerID 将自动更新:

  1. INSERT INTO Customers (CustomerName, City, Country)
  2. VALUES ('Cardinal', 'Stavanger', 'Norway');

Customers 表展示出来如下所示:

CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
89White Clover MarketsKarl Jablonski305 - 14th Ave. S. Suite 3BSeattle98128USA
90Wilman KalaMatti KarttunenKeskuskatu 45Helsinki21240Finland
91WolskiZbyszekul. Filtrowa 68Walla01-012Poland
92CardinalnullnullStavangernullNorway

INSERT INTO…SELECT

使用 INSERT INTO SELECT,你可以从 SELECT 语句的结果中快速插入多行到表中,SELECT 语句可以从一个或多个表中进行选择。INSERT INTO SELECT 语句要求源表和目标表中的数据类型匹配。

INSERT INTO SELECT 语法解释

从一个表复制所有列到另一个表:

  1. INSERT INTO *table2*
  2. SELECT * FROM *table1
  3. *WHERE *condition*;

只从一个表复制一些列到另一个表:

  1. INSERT INTO *table2* (*column1*, *column2*, *column3*, ...)
  2. SELECT *column1*, *column2*, *column3*, ...
  3. FROM *table1*
  4. WHERE *condition*;

Northwind 数据库示例

以下是从 Northwind 示例数据库中的表中选择的表示例:

  1. CREATE TABLE Customers (
  2. CustomerID INT AUTO_INCREMENT NOT NULL,
  3. CustomerName VARCHAR(40) NOT NULL,
  4. ContactName VARCHAR(30) NULL,
  5. Address VARCHAR(60) NULL,
  6. City VARCHAR(15) NULL,
  7. PostalCode VARCHAR(10) NULL,
  8. Country VARCHAR(15) NULL,
  9. PRIMARY KEY (CustomerID)
  10. );
  11. CREATE TABLE Suppliers (
  12. SupplierID INT AUTO_INCREMENT NOT NULL,
  13. SupplierName VARCHAR(40) NOT NULL,
  14. ContactName VARCHAR(30) NULL,
  15. Address VARCHAR(60) NULL,
  16. City VARCHAR(15) NULL,
  17. PostalCode VARCHAR(10) NULL,
  18. Country VARCHAR(15) NULL,
  19. PRIMARY KEY (SupplierID)
  20. );

Customers 表展示出来如下所示:

CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
1Alfreds FutterkisteMaria AndersObere Str. 57Berlin12209Germany
2Ana Trujillo Emparedados y heladosAna TrujilloAvda. de la Constitución 2222México D.F.05021Mexico
3Antonio Moreno TaqueríaAntonio MorenoMataderos 2312México D.F.05023Mexico

Suppliers 表展示出来如下所示:

SupplierIDSupplierNameContactNameAddressCityPostalCodeCountry
1Exotic LiquidCharlotte Cooper49 Gilbert St.LondonaEC1 4SDUK
2New Orleans Cajun DelightsShelley BurkeP.O. Box 78934New Orleans70117USA
3Grandma Kelly’s HomesteadRegina Murphy707 Oxford Rd.Ann Arbor48104USA

示例

下面的 SQL 语句将 Suppliers 复制到 Customers 中,同时未填充数据的列将填充为 NULL

  1. INSERT INTO Customers (CustomerName, City, Country)
  2. SELECT SupplierName, City, Country FROM Suppliers;

Customers 表展示出来如下所示:

CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
1Alfreds FutterkisteMaria AndersObere Str. 57Berlin12209Germany
2Ana Trujillo Emparedados y heladosAna TrujilloAvda. de la Constitución 2222México D.F.05021Mexico
3Antonio Moreno TaqueríaAntonio MorenoMataderos 2312México D.F.05023Mexico
4Exotic LiquidnullnullLondonanullUK
5New Orleans Cajun DelightsnullnullNew OrleansnullUSA
6Grandma Kelly’s HomesteadnullnullAnn ArbornullUSA