Choose any three.
|
|
SQLite里的SQL
SQL As Understood By SQLite
DELETE
DELETE命令用于删除qualified-table-name标识的表中的记录。
The DELETE command removes records from the table identified by the
qualified-table-name.
如果没有提供WHERE子句，表中的所有记录都会被删除。如果提供了WHERE子句，那么只有能使WHERE子句计算返回布尔表达式true的记录会被删除。
If the WHERE clause is not present, all records in the table are deleted.
If a WHERE clause is supplied, then only those rows for which the
result of evaluating the WHERE clause as a boolean expression is true are deleted.
在CREATE TRIGGER中DELETE语句的限制
Restrictions on DELETE Statements Within CREATE TRIGGER
在CREATE TRIGGER语句内使用DELETE语句需要遵循下列限制。
The following restrictions apply to DELETE statements that occur within the
body of a CREATE TRIGGER statement:
在触发器中的DELETE语句内的table-name必须是不加限定的。换句话说，在触发器中不允许在表名前使用database-name.前缀。如果触发器所属的表不是在临时库中，那么触发器中的DELETE语句只能操作同一个库中的表。如果触发器所属的表在临时库中，那么需要执行删除操作的未加限定的表名的寻找规则与顶级的DELETE语句一样（先搜索临时库，然后是主库，最后是其它附加库）。
The table-name specified as part of a DELETE statement within a trigger body must be unqualified. In other words, the database-name. prefix on the table name is not allowed within triggers. If the table to which the trigger is attached is not in the temp database, then DELETE statements within the trigger body must operate on tables within the same database as it. If the table to which the trigger is attached is in the TEMP database, then the unqualified name of the table being deleted is resolved in the same way as it is for a top-level statement (by searching first the TEMP database, then the main database, then any other databases in the order they were attached).触发器中的DELETE不允许使用 INDEXED BY 和 NOT INDEXED子句。
The INDEXED BY and NOT INDEXED clauses are not allowed on DELETE statements within triggers.触发器中的DELETE不支持LIMIT和ORDER BY（下面介绍的）。
The LIMIT and ORDER BY clauses (described below) are unsupported for DELETE statements within triggers.
可选的LIMIT 和 ORDER BY子句。
Optional LIMIT and ORDER BY clauses
如果SQLite使用SQLITE_ENABLE_UPDATE_DELETE_LIMIT编译选项编译而成，那么DELETE语句的语法额外增加了可选的ORDER BY和LIMIT子句：
If SQLite is compiled with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT
compile-time option, then the syntax of the DELETE statement is
extended by the addition of optional ORDER BY and LIMIT clauses:
如果一个DELETE语句中包含LIMIT子句，最多删除的行数是由LIMIT后面的表达式计算后转换成整数的值来指定的。如果LIMIT子句的计算结果无法无损的转换为整数，则会发生错误。LIMIT值为负数会被看做“无限制”。如果DELETE语句还有一个OFFSET子句，那么也会同样计算并转换为一个整数。类似的，如果返回的值无法无损的转换为整数，则会发生错误。如果没有OFFSET子句，或者计算结果为负数，则表示OFFSET的值是0。
If a DELETE statement has a LIMIT clause, the maximum number of rows that
will be deleted is found by evaluating the accompanying expression and casting
it to an integer value. If the result of the evaluating the LIMIT clause
cannot be losslessly converted to an integer value, it is an error. A
negative LIMIT value is interpreted as "no limit". If the DELETE statement
also has an OFFSET clause, then it is similarly evaluated and cast to an
integer value. Again, it is an error if the value cannot be losslessly
converted to an integer. If there is no OFFSET clause, or the calculated
integer value is negative, the effective OFFSET value is zero.
如果DELETE语句有一个ORDER BY子句，那么所有在没有LIMIT限制时将会被删除的行都会依照ORDER BY排序。
前 M行数据会被跳过，后续的N行数据会被删除， M是OFFSET子句表达式的计算结果，N是LIMIT表达式的计算结果。如果库中，在跳过了OFFSET指定的行数之后剩余的行数不足N行，或者LIMIT子句结果为负数，那么所有剩下的记录行都会被删除。
If the DELETE statement has an ORDER BY clause, then all rows that would
be deleted in the absence of the LIMIT clause are sorted according to the
ORDER BY. The first M rows, where M is the value found by
evaluating the OFFSET clause expression, are skipped, and the following
N, where N is the value of the LIMIT expression, are deleted.
If there are less than N rows remaining after taking the OFFSET clause
into account, or if the LIMIT clause evaluated to a negative value, then all
remaining rows are deleted.
如果DELETE语句没有ORDER BY子句，那么在没有LIMIT限制时所有需要被删除的行是按照随机顺序排列的，所以LIMIT和OFFSET子句决定出的需要被实际删除的子集也是随机的。
If the DELETE statement has no ORDER BY clause, then all rows that
would be deleted in the absence of the LIMIT clause are assembled in an
arbitrary order before applying the LIMIT and OFFSET clauses to determine
the subset that are actually deleted.
DELETE语句中的ORDER BY子句仅仅用于决定LIMIT中那些行会被删除。而实际删除行的顺序依然是随机的，不受ORDER BY子句的影响。
The ORDER BY clause on a DELETE statement is used only to determine which
rows fall within the LIMIT. The order in which rows are deleted is arbitrary
and is not influenced by the ORDER BY clause.
truncate 优化
The Truncate Optimization
当DELETE子句中没有WHERE子句，并且被删除的表上没有触发器，SQLite会优化为直接删除整个表的内容，而不用单独访问每条数据行。"truncate"优化项会使删除运行的更加快速。SQLite3.6.5之前的版本同时也意味着，sqlite3_changes() 和
sqlite3_total_changes() 接口 count_changes 指令都将无法返回实际删除的行数。这个问题在3.6.5版之后得以修复了。
When the WHERE is omitted from a DELETE statement and the table
being deleted has no triggers,
SQLite uses an optimization to erase the entire table content
without having to visit each row of the table individually.
This "truncate" optimization makes the delete run much faster.
Prior to SQLite version 3.6.5, the truncate optimization
also meant that the sqlite3_changes() and
sqlite3_total_changes() interfaces
and the count_changes pragma
will not actually return the number of deleted rows.
That problem has been fixed as of version 3.6.5.
truncate优化项可以通过使用SQLITE_OMIT_TRUNCATE_OPTIMIZATION编译开关重新编译SQLite来禁止所有的查询使用。
The truncate optimization can be permanently disabled for all queries
by recompiling
SQLite with the SQLITE_OMIT_TRUNCATE_OPTIMIZATION compile-time switch.
truncate优化项同样可以在运行时使用sqlite3_set_authorizer()接口来禁用。
对于一个SQLITE_DELETE操作码，如果认证回调返回SQLITE_IGNORE，那么DELETE操作将会执行，但是truncate优化项会被忽略，只能一行一行的删除数据。
The truncate optimization can also be disabled at runtime using
the sqlite3_set_authorizer() interface. If an authorizer callback
returns SQLITE_IGNORE for an SQLITE_DELETE action code, then
the DELETE operation will proceed but the truncate optimization will
be bypassed and rows will be deleted one by one.