SQLite 的 INSERT INTO 语句用于向数据库的某个表中添加新的数据行。
INSERT INTO 语句有两种基本语法，如下所示：
|
1 2 |
<span class="pln">INSERT INTO TABLE_NAME </span><span class="pun">(</span><span class="pln">column1</span><span class="pun">,</span><span class="pln"> column2</span><span class="pun">,</span><span class="pln"> column3</span><span class="pun">,...</span><span class="pln">columnN</span><span class="pun">)]</span><span class="pln"> VALUES </span><span class="pun">(</span><span class="pln">value1</span><span class="pun">,</span><span class="pln"> value2</span><span class="pun">,</span><span class="pln"> value3</span><span class="pun">,...</span><span class="pln">valueN</span><span class="pun">);</span> |
在这里，column1, column2,…columnN 是要插入数据的表中的列的名称。
如果要为表中的所有列添加值，您也可以不需要在 SQLite 查询中指定列名称。但要确保值的顺序与列在表中的顺序一致。SQLite 的 INSERT INTO 语法如下：
|
1 |
<span class="pln">INSERT INTO TABLE_NAME VALUES </span><span class="pun">(</span><span class="pln">value1</span><span class="pun">,</span><span class="pln">value2</span><span class="pun">,</span><span class="pln">value3</span><span class="pun">,...</span><span class="pln">valueN</span><span class="pun">);</span> |
假设您已经在 testDB.db 中创建了 COMPANY表，如下所示：
|
1 2 3 4 5 6 7 |
<span class="pln">sqlite</span><span class="pun">></span><span class="pln"> CREATE TABLE COMPANY</span><span class="pun">(</span><span class="pln"> ID INT PRIMARY KEY NOT NULL</span><span class="pun">,</span><span class="pln"> NAME TEXT NOT NULL</span><span class="pun">,</span><span class="pln"> AGE INT NOT NULL</span><span class="pun">,</span><span class="pln"> ADDRESS CHAR</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln"> SALARY REAL </span><span class="pun">);</span> |
现在，下面的语句将在 COMPANY 表中创建六个记录：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
<span class="pln">INSERT INTO COMPANY </span><span class="pun">(</span><span class="pln">ID</span><span class="pun">,</span><span class="pln">NAME</span><span class="pun">,</span><span class="pln">AGE</span><span class="pun">,</span><span class="pln">ADDRESS</span><span class="pun">,</span><span class="pln">SALARY</span><span class="pun">)</span><span class="pln"> VALUES </span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span> <span class="str">'Paul'</span><span class="pun">,</span> <span class="lit">32</span><span class="pun">,</span> <span class="str">'California'</span><span class="pun">,</span> <span class="lit">20000.00</span> <span class="pun">);</span><span class="pln"> INSERT INTO COMPANY </span><span class="pun">(</span><span class="pln">ID</span><span class="pun">,</span><span class="pln">NAME</span><span class="pun">,</span><span class="pln">AGE</span><span class="pun">,</span><span class="pln">ADDRESS</span><span class="pun">,</span><span class="pln">SALARY</span><span class="pun">)</span><span class="pln"> VALUES </span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span> <span class="str">'Allen'</span><span class="pun">,</span> <span class="lit">25</span><span class="pun">,</span> <span class="str">'Texas'</span><span class="pun">,</span> <span class="lit">15000.00</span> <span class="pun">);</span><span class="pln"> INSERT INTO COMPANY </span><span class="pun">(</span><span class="pln">ID</span><span class="pun">,</span><span class="pln">NAME</span><span class="pun">,</span><span class="pln">AGE</span><span class="pun">,</span><span class="pln">ADDRESS</span><span class="pun">,</span><span class="pln">SALARY</span><span class="pun">)</span><span class="pln"> VALUES </span><span class="pun">(</span><span class="lit">3</span><span class="pun">,</span> <span class="str">'Teddy'</span><span class="pun">,</span> <span class="lit">23</span><span class="pun">,</span> <span class="str">'Norway'</span><span class="pun">,</span> <span class="lit">20000.00</span> <span class="pun">);</span><span class="pln"> INSERT INTO COMPANY </span><span class="pun">(</span><span class="pln">ID</span><span class="pun">,</span><span class="pln">NAME</span><span class="pun">,</span><span class="pln">AGE</span><span class="pun">,</span><span class="pln">ADDRESS</span><span class="pun">,</span><span class="pln">SALARY</span><span class="pun">)</span><span class="pln"> VALUES </span><span class="pun">(</span><span class="lit">4</span><span class="pun">,</span> <span class="str">'Mark'</span><span class="pun">,</span> <span class="lit">25</span><span class="pun">,</span> <span class="str">'Rich-Mond '</span><span class="pun">,</span> <span class="lit">65000.00</span> <span class="pun">);</span><span class="pln"> INSERT INTO COMPANY </span><span class="pun">(</span><span class="pln">ID</span><span class="pun">,</span><span class="pln">NAME</span><span class="pun">,</span><span class="pln">AGE</span><span class="pun">,</span><span class="pln">ADDRESS</span><span class="pun">,</span><span class="pln">SALARY</span><span class="pun">)</span><span class="pln"> VALUES </span><span class="pun">(</span><span class="lit">5</span><span class="pun">,</span> <span class="str">'David'</span><span class="pun">,</span> <span class="lit">27</span><span class="pun">,</span> <span class="str">'Texas'</span><span class="pun">,</span> <span class="lit">85000.00</span> <span class="pun">);</span><span class="pln"> INSERT INTO COMPANY </span><span class="pun">(</span><span class="pln">ID</span><span class="pun">,</span><span class="pln">NAME</span><span class="pun">,</span><span class="pln">AGE</span><span class="pun">,</span><span class="pln">ADDRESS</span><span class="pun">,</span><span class="pln">SALARY</span><span class="pun">)</span><span class="pln"> VALUES </span><span class="pun">(</span><span class="lit">6</span><span class="pun">,</span> <span class="str">'Kim'</span><span class="pun">,</span> <span class="lit">22</span><span class="pun">,</span> <span class="str">'South-Hall'</span><span class="pun">,</span> <span class="lit">45000.00</span> <span class="pun">);</span> |
您也可以使用第二种语法在 COMPANY 表中创建一个记录，如下所示：
|
1 |
<span class="pln">INSERT INTO COMPANY VALUES </span><span class="pun">(</span><span class="lit">7</span><span class="pun">,</span> <span class="str">'James'</span><span class="pun">,</span> <span class="lit">24</span><span class="pun">,</span> <span class="str">'Houston'</span><span class="pun">,</span> <span class="lit">10000.00</span> <span class="pun">);</span> |
上面的所有语句将在 COMPANY 表中创建下列记录。下一章会教您如何从一个表中显示所有这些记录。
|
1 2 3 4 5 6 7 8 9 |
<span class="pln">ID NAME AGE ADDRESS SALARY </span><span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="pun">----------</span> <span class="lit">1</span> <span class="typ">Paul</span> <span class="lit">32</span> <span class="typ">California</span> <span class="lit">20000.0</span> <span class="lit">2</span> <span class="typ">Allen</span> <span class="lit">25</span> <span class="typ">Texas</span> <span class="lit">15000.0</span> <span class="lit">3</span> <span class="typ">Teddy</span> <span class="lit">23</span> <span class="typ">Norway</span> <span class="lit">20000.0</span> <span class="lit">4</span> <span class="typ">Mark</span> <span class="lit">25</span> <span class="typ">Rich</span><span class="pun">-</span><span class="typ">Mond</span> <span class="lit">65000.0</span> <span class="lit">5</span> <span class="typ">David</span> <span class="lit">27</span> <span class="typ">Texas</span> <span class="lit">85000.0</span> <span class="lit">6</span> <span class="typ">Kim</span> <span class="lit">22</span> <span class="typ">South</span><span class="pun">-</span><span class="typ">Hall</span> <span class="lit">45000.0</span> <span class="lit">7</span> <span class="typ">James</span> <span class="lit">24</span> <span class="typ">Houston</span> <span class="lit">10000.0</span> |
您可以通过在一个有一组字段的表上使用 select 语句，填充数据到另一个表中。下面是语法：
|
1 2 3 4 |
<span class="pln">INSERT INTO first_table_name </span><span class="pun">[(</span><span class="pln">column1</span><span class="pun">,</span><span class="pln"> column2</span><span class="pun">,</span> <span class="pun">...</span><span class="pln"> columnN</span><span class="pun">)]</span><span class="pln"> SELECT column1</span><span class="pun">,</span><span class="pln"> column2</span><span class="pun">,</span> <span class="pun">...</span><span class="pln">columnN FROM second_table_name </span><span class="pun">[</span><span class="pln">WHERE condition</span><span class="pun">];</span> |
您暂时可以先跳过上面的语句，可以先学习后面章节中介绍的 SELECT 和 WHERE 子句。