Sqlite 简明教程

SQLite - INSERT Query

SQLite INSERT INTO 语句用于将新数据行添加到数据库中的表中。

SQLite INSERT INTO Statement is used to add new rows of data into a table in the database.

Syntax

以下是 INSERT INTO 语句的两个基本语法：

Following are the two basic syntaxes of INSERT INTO statement.

INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]
VALUES (value1, value2, value3,...valueN);

这里，column1、column2、…columnN 是想要在其中插入数据表的列的名称。

Here, column1, column2,…columnN are the names of the columns in the table into which you want to insert data.

如果要为表的全部列添加值，则可能不需要在 SQLite 查询中指定列的名称。但是，确保值的顺序与表中的列顺序相同。SQLite INSERT INTO 语法如下所示：

You may not need to specify the column(s) name in the SQLite query if you are adding values for all the columns of the table. However, make sure the order of the values is in the same order as the columns in the table. The SQLite INSERT INTO syntax would be as follows −

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

Example

假设你已经按照以下方式在 testDB.db 中创建了 COMPANY 表：

Consider you already have created COMPANY table in your testDB.db as follows −

sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

现在，以下语句将在 COMPANY 表中创建六条记录。

Now, the following statements would create six records in COMPANY table.

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'David', 27, 'Texas', 85000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );

可以使用第二个语法在 COMPANY 表中创建记录，如下所示：

You can create a record in COMPANY table using the second syntax as follows −

INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );

以上所有语句将在 COMPANY 表中创建以下记录。在下一章中，你将学习如何从表中显示所有这些记录。

All the above statements would create the following records in COMPANY table. In the next chapter, you will learn how to display all these records from a table.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Populate One Table Using Another Table

可以通过针对另一张表的 SELECT 语句来填充数据到表中，前提是另一张表有一组字段，需要用来填充第一张表。语法如下：

You can populate data into a table through select statement over another table provided another table has a set of fields, which are required to populate the first table. Here is the syntax −

INSERT INTO first_table_name [(column1, column2, ... columnN)]
   SELECT column1, column2, ...columnN
   FROM second_table_name
   [WHERE condition];

现在，可以跳过上面的语句。首先，让我们学习 SELECT 和 WHERE 从句，它们将在后续章节中介绍。

For now, you can skip the above statement. First, let’s learn SELECT and WHERE clauses which will be covered in subsequent chapters.