INSERT
| sql-statement ::= | INSERT [OR conflict-algorithm] INTO [database-name .] table-name [(column-list)] VALUES(value-list) | INSERT [OR conflict-algorithm] INTO [database-name .] table-name [(column-list)] select-statement |
The INSERT statement comes in two basic forms. The first form (with the "VALUES" keyword) creates a single new row in an existing table. If no column-list is specified then the number of values must be the same as the number of columns in the table. If a column-list is specified, then the number of values must match the number of specified columns. Columns of the table that do not appear in the column list are filled with the default value, or with NULL if not default value is specified.
The second form of the INSERT statement takes it data from a SELECT statement. The number of columns in the result of the SELECT must exactly match the number of columns in the table if no column list is specified, or it must match the number of columns name in the column list. A new entry is made in the table for every row of the SELECT result. The SELECT may be simple or compound. If the SELECT statement has an ORDER BY clause, the ORDER BY is ignored.
The optional conflict-clause allows the specification of an alternative constraint conflict resolution algorithm to use during this one command. See the section titled ON CONFLICT for additional information. For compatibility with MySQL, the parser allows the use of the single keyword REPLACE as an alias for "INSERT OR REPLACE".
INSERT函数有两种基本形式
1.有"VALUES"关键字
如果没有限制列，value参数的个数＝表的列数；如果限制了列，value参数的个数＝限制的列数，这时表中没有填充的列将为默认值（当没有默认值时为NULL）。
2.从SELECT中获取数据，数据个数限制同上。表中一行保存一个SELECT结果。同时忽略SELECT语句中的“ORDER BY”语句（该语句是干什么的，哪位高手指点一下）。
可选项[conflict-algorithm]：允许定义一个an alternative constraint conflict resolution algorithm to use during this one command(谁会翻译？)。在ON CONFLICT你可以获得更多信息。为了兼容MySQL，“REPLACE”等价于"INSERT OR REPLACE"。
另外我在网上看到以下评论：
和其它数据库不同的是 sqlite在开启事物的时候要远快于未开启的时候
原因如下：
因为sqlite的事务处理机制是通过文件锁和临时文件来实现的 并没有像其它的数据库一样加入checkpoint来进行二次提交以及多步回滚，所以在未开启事物的情况下，每次操作数据库之次sqlite都会释放文件写锁，以获得对并发的支持，而开启事物后它就不需要再考虑并发了，到事物结束前，它都一直会用写文件锁保持对数据库文件的占用，所以这个时候性能会远大于未开启事物，我做的测试，对1W条数据，未用事务，7~8s 和access相仿，开启事物，0.1~0.3S
而只是对数据读取(select)时 就不用考虑上述问题了，所以性能相差无几
所以sqlite是一个另类的数据库，它将改变我们很多时候对数据库的认识，如事务处理，物理文件设计。
想问一下，如何开启“事务处理”？
示例：INSERT INTO existWord VALUES("W")(向exsitWord的表插入“W”)
