Small. Fast. Reliable.
Choose any three.
关于 网站地图 文档 下载 许可 新闻 支持

SQLite里的SQL
SQL As Understood By SQLite

[Top]

CREATE TABLE

create-table-stmt:

syntax diagram create-table-stmt

column-def:

select-stmt:

table-constraint:

"CREATE TABLE"命令用于在SQLite数据库中建立一张新表。CREATE TABLE 命令可以为新表指定如下属性：
The "CREATE TABLE" command is used to create a new table in an SQLite database. A CREATE TABLE command specifies the following attributes of the new table:

每条CREATE TABLE声明都必须为新表指定一个名字。“sqlite_”开头的表名都保留为内部使用。如果尝试创建一个"sqlite_"开头的表名会出现一个错误。
Every CREATE TABLE statement must specify a name for the new table. Table names that begin with "sqlite_" are reserved for internal use. It is an error to attempt to create a table with a name that starts with "sqlite_".

如果指定了<database-name>那么它只能是"main"、"temp"或者附加库的名字，这种情况下新表会创建在指定的库上。如果"CREATE" 和 "TABLE"关键词之间加入 "TEMP" 或 "TEMPORARY"关键词，那么会在临时库上创建新表。除非<database-name> 是"temp"，否则不允许同时指定<database-name> 和TEMP 或 TEMPORARY关键词。如果既没有指定<database-name> 也没有加入TEMP关键词，那么默认会在主库上创建新表。
If a <database-name> is specified, it must be either "main", "temp", or the name of an attached database. In this case the new table is created in the named database. If the "TEMP" or "TEMPORARY" keyword occurs between the "CREATE" and "TABLE" then the new table is created in the temp database. It is an error to specify both a <database-name> and the TEMP or TEMPORARY keyword, unless the <database-name> is "temp". If no database name is specified and the TEMP keyword is not present then the table is created in the main database.

通常尝试在数据库里新创建的表面和库里已有的表、索引、视图的名字相同时会引发错误。不过，如果在CREATE TABLE声明中加入"IF NOT EXISTS"子句，那么当出现重名的表和视图时CREATE TABLE命令会简单的将本声明忽略（并且不会返回错误信息）。但是如果是与索引重名，那么即使加了"IF NOT EXISTS"子句，也依然会返回一个错误。
It is usually an error to attempt to create a new table in a database that already contains a table, index or view of the same name. However, if the "IF NOT EXISTS" clause is specified as part of the CREATE TABLE statement and a table or view of the same name already exists, the CREATE TABLE command simply has no effect (and no error message is returned). An error is still returned if the table cannot be created because of an existing index, even if the "IF NOT EXISTS" clause is specified.

创建一个与已有触发器（trigger）同名的表是不会产生错误的。
It is not an error to create a table that has the same name as an existing trigger.

使用DROP TABLE声明来移除表。
Tables are removed using the DROP TABLE statement.

CREATE TABLE ... AS SELECT声明
CREATE TABLE ... AS SELECT Statements

"CREATE TABLE ... AS SELECT"声明会基于一个SELECT语句的结果来创建一个数据库表。这张表的列数与SELECT语句返回的每行数据一样。每一列的名字都和SELECT语句结果集中对于的列的名字一致。每一列的类型根据SELECT语句返回的结果集中对应列的表达式由词句亲和力（Expression Affinity）来决定，如下：
A "CREATE TABLE ... AS SELECT" statement creates and populates a database table based on the results of a SELECT statement. The table has the same number of columns as the rows returned by the SELECT statement. The name of each column is the same as the name of the corresponding column in the result set of the SELECT statement. The declared type of each column is determined by the expression affinity of the corresponding expression in the result set of the SELECT statement, as follows:

词句亲和力（Expression Affinity ） 列声明类型
TEXT "TEXT"
NUMERIC "NUM"
INTEGER "INT"
REAL "REAL"
NONE "" (空字符串)

使用CREATE TABLE AS创建的表没有主键，也没有任何类型的约束。每一列的默认值是NULL，每列默认的排序器都是BINARY。
A table created using CREATE TABLE AS has no PRIMARY KEY and no constraints of any kind. The default value of each column is NULL. The default collation sequence for each column of the new table is BINARY.

使用CREATE TABLE AS 创建的表的初始内容来自于SELECT语句返回的每行数据。每行分配的rowid值按照SELECT语句返回的顺序，从1开始连续增长。
Tables created using CREATE TABLE AS are initially populated with the rows of data returned by the SELECT statement. Rows are assigned contiguously ascending rowid values, starting with 1, in the order that they are returned by the SELECT statement.

列定义
Column Definitions

除非是使用CREATE TABLE ... AS SELECT语句，否则每条CREATE TABLE语句都会包含至少一个列定义，并且可以在后面跟随表约束列表。每一个列定义由列名字、可选的列类型定义、一个或多个可选的列约束（column constraints）组成。在列约束（column constraints）中，COLLATE和DEFAULT子句并不是真正意义上的约束，并不会对表中可能包含的数据做限制。其他的约束——NOT NULL、CHECK、UNIQUE、PRIMARY KEY 和 FOREIGN KEY约束会为表中的数据加入前置约束，在下面的SQL 数据约束中又详细描述。
Unless it is a CREATE TABLE ... AS SELECT statement, a CREATE TABLE includes one or more column definitions, optionally followed by a list of table constraints. Each column definition consists of the name of the column, optionally followed by the declared type of the column, then one or more optional column constraints. Included in the definition of "column constraints" for the purposes of the previous statement are the COLLATE and DEFAULT clauses, even though these are not really constraints in the sense that they do not restrict the data that the table may contain. The other constraints - NOT NULL, CHECK, UNIQUE, PRIMARY KEY and FOREIGN KEY constraints - impose restrictions on the tables data, and are are described under SQL Data Constraints below.

与大多数的SQL数据库不同，SQLite不会依照列的声明类型来限制插入数据的类型。相反，SQLite使用的是动态类型。列的声明类型仅仅是用于决定列的亲和力
Unlike most SQL databases, SQLite does not restrict the type of data that may be inserted into a column based on the columns declared type. Instead, SQLite uses dynamic typing. The declared type of a column is used to determine the affinity of the column only.

当用户做INSERT操作时没有为一列提供明确的值时，会使用DEFAULT子句指定的默认值。
如果在列定义上没有显示的添加DEFAULT子句，那么列的默认值则是NULL。DEFAULT子句可以指定的的默认值包含NULL、字符串常量、BLOB常量、有符号数或者包含在圆括号中的常量表达式。默认值还可以是下面这些大小写无关的关键字之一——CURRENT_TIME、CURRENT_DATE、CURRENT_TIMESTAMP。在DEFAULT子句中，表达式应该是一个经过深思熟虑的常量，不能包含子查询、列或表引用、包含在双引号中的字符串。
The DEFAULT clause specifies a default value to use for the column if no value is explicitly provided by the user when doing an INSERT. If there is no explicit DEFAULT clause attached to a column definition, then the default value of the column is NULL. An explicit DEFAULT clause may specify that the default value is NULL, a string constant, a blob constant, a signed-number, or any constant expression enclosed in parentheses. An explicit default value may also be one of the special case-independent keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. For the purposes of the DEFAULT clause, an expression is considered constant provided that it does not contain any sub-queries, column or table references, or string literals enclosed in double-quotes instead of single-quotes.

每当通过INSERT语句向表中插入一行记录时，对于所有没有明确提供值的表列，在新行中存储的数值是由默认值决定的。如下：
Each time a row is inserted into the table by an INSERT statement that does not provide explicit values for all table columns the values stored in the new row are determined by their default values, as follows:

COLLATE子句为列指定一个默认的排序器的名字。如果没有指定COLLATE子句，那么默认的排序器是BINARY
The COLLATE clause specifies the name of a collating sequence to use as the default collation sequence for the column. If no COLLATE clause is specified, the default collation sequence is BINARY.

一张表中列的数量被限制在了编译期参数SQLITE_MAX_COLUMN之内。一个独立行存储数据的最大长度为SQLITE_MAX_LENGTH字节。这两个限制都可以在运行期使用C/C++接口sqlite3_limit()来降低。
The number of columns in a table is limited by the SQLITE_MAX_COLUMN compile-time parameter. A single row of a table cannot store more than SQLITE_MAX_LENGTH bytes of data. Both of these limits can be lowered at runtime using the sqlite3_limit() C/C++ interface.

SQL数据约束
SQL Data Constraints

SQLite中每个表最多包含一个PRIMARY KEY。如果在列的定义中加入关键字PRIMARY KEY，那么表的主键就由这一列构成。或者，将PRIMARY KEY作为一个表约束指定，那么表的主键就由PRIMARY KEY子句中指定的列列表构成。如果在一个CREATE TABLE语句中出现了一个以上的PRIMARY KEY子句，那么会抛出一个错误。PRIMARY KEY对于普通表来说是可选的，但是对于WITHOUT ROWID表则是必须的。
Each table in SQLite may have at most one PRIMARY KEY. If the keywords PRIMARY KEY are added to a column definition, then the primary key for the table consists of that single column. Or, if a PRIMARY KEY clause is specified as a table-constraint, then the primary key of the table consists of the list of columns specified as part of the PRIMARY KEY clause. An error is raised if more than one PRIMARY KEY clause appears in a CREATE TABLE statement. The PRIMARY KEY is optional for ordinary tables but is required for WITHOUT ROWID tables.

如果一个表只有一个申明类型为“INTEGER”的列是主键，并且不是一个WITHOUT ROWID表，那么这一列会被作为一个INTEGER PRIMARY KEY。关于INTEGER PRIMARY KEY相关的专用参数和行为，详见下文。
If a table has a single column primary key and the declared type of that column is "INTEGER" and the table is not a WITHOUT ROWID table, then the column is known as an INTEGER PRIMARY KEY. See below for a description of the special properties and behaviors associated with an INTEGER PRIMARY KEY.

每个包含了primary key的表都必须为主键列的值或联合值是全表唯一的。为了保证主键值的唯一性，NULL会被认为是有别于其他所有值的值，包括其他的NULL。视图通过INSERT 或 UPDATE语句将表中两个以上主键的内容修改成完全一样会导致约束违规。依照SQL标准，PRIMARY KEY是应当包含NOT NULL的。不幸的是，为了兼容一些早期版本的bug，所以这条在SQLite中没有支持。除非这一列是INTEGER PRIMARY KEY，或者是一个WITHOUT ROWID表，或者列被定义为NOT NULL才符合SQLite标准规范，但是这么做可能会破坏遗留程序。因此，这仅仅是文档中的定义，事实上SQLite是允许大多数的PRIMARY KEY列包含NULL的。
Each row in a table with a primary key must have a unique combination of values in its primary key columns. For the purposes of determining the uniqueness of primary key values, NULL values are considered distinct from all other values, including other NULLs. If an INSERT or UPDATE statement attempts to modify the table content so that two or more rows feature identical primary key values, it is a constraint violation. According to the SQL standard, PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a bug in some early versions, this is not the case in SQLite. Unless the column is an INTEGER PRIMARY KEY or the table is a WITHOUT ROWID table or the column is declared NOT NULL, SQLite allows NULL values in a PRIMARY KEY column. SQLite could be fixed to conform to the standard, but doing so might break legacy applications. Hence, it has been decided to merely document the fact that SQLite allowing NULLs in most PRIMARY KEY columns.

UNIQUE约束类似于PRIMARY KEY约束，不过一个表可以包含多个UNIQUE约束。对于每一条UNIQUE约束，都要保证指定列每行的值或者联合值在表中是唯一的。在UNIQUE约束中，NULL值被认为是有别于其它所有值的值，包括其他的NULL。
A UNIQUE constraint is similar to a PRIMARY KEY constraint, except that a single table may have any number of UNIQUE constraints. For each UNIQUE constraint on the table, each row must contain a unique combination of values in the columns identified by the UNIQUE constraint. For the purposes of UNIQUE constraints, NULL values are considered distinct from all other values, including other NULLs.

通常，UNIQUE和PRIMARY KEY 都是通过在数据库中创建一个唯一索引来实现的（除了INTEGER PRIMARY KEY 和 WITHOUT ROWID表上的PRIMARY KEY）。因此下面的表述在逻辑上是相同的。
In most cases, UNIQUE and PRIMARY KEY constraints are implemented by creating a unique index in the database. (The exceptions are INTEGER PRIMARY KEY and PRIMARY KEYs on WITHOUT ROWID tables.) Hence, the following schemas are logically equivalent:

  1. CREATE TABLE t1(a, b UNIQUE);

  2. CREATE TABLE t1(a, b PRIMARY KEY);

  3. CREATE TABLE t1(a, b);
    CREATE UNIQUE INDEX t1b ON t1(b);

CHECK约束可以附加在列定义上也可以作为一个表约束。在实践中这是没有区别的。每当向表中插入新行或者更新已存在的表时，每个CHECK约束指定的表达式就会被计算，并以CAST 表达式同样的方式转换到一个NUMERIC值。如果结果是零（整数0或者实数0.0），那么就会发生约束违规。如果CHECK表达式计算结果为NULL或者其它非零的值，则不会发生约束违规。CHECK约束表达式不允许包含子查询。
A CHECK constraint may be attached to a column definition or specified as a table constraint. In practice it makes no difference. Each time a new row is inserted into the table or an existing row is updated, the expression associated with each CHECK constraint is evaluated and cast to a NUMERIC value in the same way as a CAST expression. If the result is zero (integer value 0 or real value 0.0), then a constraint violation has occurred. If the CHECK expression evaluates to NULL, or any other non-zero value, it is not a constraint violation. The expression of a CHECK constraint may not contain a subquery.

CHECK约束是从3.3.0版才开始支持的。早于3.3.0的版本会解析CHECK约束，但是不会生效CHECK constraints have been supported since version 3.3.0. Prior to version 3.3.0, CHECK constraints were parsed but not enforced.

NOT NULL约束只能附加在列定义上，不能指定为表约束。显然，一个NOT NULL约束规定该列不能包含NULL值。视图在插入新行或者更新已有行时给该列设置NULL值会引发约束违规。
A NOT NULL constraint may only be attached to a column definition, not specified as a table constraint. Not surprisingly, a NOT NULL constraint dictates that the associated column may not contain a NULL value. Attempting to set the column value to NULL when inserting a new row or updating an existing one causes a constraint violation.

当遇到冲突违规时如何处理是由约束冲突解决算法来决定的。每一个PRIMARY KEY、 UNIQUE、 NOT NULL 和 CHECK约束都可以通过在定义中包含一个conflict-clause来明确指定一个默认的冲突解决算法。或者，如果是一个CHECK约束，在约束定义中没有包含conflict-clause，那么默认的约束解决算法是ABORT。在同一个表中，不同的约束可以有不同的默认冲突解决算法。详细信息参考ON CONFLICT一节。
Exactly how a constraint violation is dealt with is determined by the constraint conflict resolution algorithm. Each PRIMARY KEY, UNIQUE, NOT NULL and CHECK constraint has a default conflict resolution algorithm. PRIMARY KEY, UNIQUE and NOT NULL constraints may be explicitly assigned a default conflict resolution algorithm by including a conflict-clause in their definitions. Or, if a constraint definition does not include a conflict-clause or it is a CHECK constraint, the default conflict resolution algorithm is ABORT. Different constraints within the same table may have different default conflict resolution algorithms. See the section titled ON CONFLICT for additional information.

ROWID和INTEGER PRIMARY KEY
ROWIDs and the INTEGER PRIMARY KEY

在SQLie中，除了WITHOUT ROWID表，其他所有的表的所有行都会有一个64位的有符号整数键，用于唯一标识表中的每一行。这个整数通常叫做“rowid”。这个整数通常可以在列名字位置，通过“rowid”、“oid”、“_rowid_”这三个不区分大小写的特殊名字来访问。如果在一个表中，用户定义了名为“rowid”、“oid”、“_rowid_”的列，那么这个名字只会引用到明确定义的列，无法用来获取整数的rowid值。
Except for WITHOUT ROWID tables, all rows within SQLite tables have a 64-bit signed integer key that uniquely identifies the row within its table. This integer is usually called the "rowid". The rowid value can be accessed using one of the special case-independent names "rowid", "oid", or "_rowid_" in place of a column name. If a table contains a user defined column named "rowid", "oid" or "_rowid_", then that name always refers the explicitly declared column and cannot be used to retrieve the integer rowid value.

在 WITHOUT ROWID表中会忽略rowid（“oid”和“_rowid_”）。WITHOUT ROWID 表只在3.8.2版本及后续版本的SQLite中有效。不包含WITHOUT ROWID子句的表被称为“rowid table”。
The rowid (and "oid" and "_rowid_") is omitted in WITHOUT ROWID tables. WITHOUT ROWID tables are only available in SQLite version 3.8.2 and later. A table that lacks the WITHOUT ROWID clause is called a "rowid 表".

rowid表中的数据是存储在一个B-Tree结构中的，每行对应一个记录，使用rowid作为键。这意味着使用rowid来获取记录或者排序会很快。使用rowid来搜索记录，或者查找一个指定范围的记录的速度会比在PRIMARY KEY或者建立了索引的其它值上做类似的事情快一倍。
The data for rowid tables is stored as a B-Tree structure containing one entry for each table row, using the rowid value as the key. This means that retrieving or sorting records by rowid is fast. Searching for a record with a specific rowid, or for all records with rowids within a specified range is around twice as fast as a similar search made by specifying any other PRIMARY KEY or indexed value.

下面有一个例外，如果一个rowid表有一个又唯一列构成的主键，并且这一列的类型声明为“INTEGER”（忽略大小写），那么这一列就是rowid的一个别名。这样的列通常称为“整数主键（integer primary key）”。一个主键列只有当声明类型名是“INTEGER”时才能变成一个整数主键。其它的整数类型名例如“INT”、“BIGINT”、“SHORT INTEGER”、 “UNSIGNED INTEGER”都会导致主键列成为一个普通的整数亲和力的表列和一个唯一索引，而不是rowid的别名。
With one exception noted below, if a rowid table has a primary key that consists of a single column and the declared type of that column is "INTEGER" in any mixture of upper and lower case, then the column becomes an alias for the rowid. Such a column is usually referred to as an "integer primary key". A PRIMARY KEY column only becomes an integer primary key if the declared type name is exactly "INTEGER". Other integer type names like "INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary key column to behave as an ordinary table column with integer affinity and a unique index, not as an alias for the rowid.

还有一个例外是如果一个列声明类型为“INTEGER”并且包含一个“PRIMARY KEY DESC”子句，那么这一列不会成为rowid的别名，也不能称为整数主键。这个怪异的情况不是专门设计的，而是为了兼容SQLite早期版本的bug。如果修复这个bug会导致向后不兼容。因此，原始的行为被保留下来了（并记录在文档中），因为小的特殊行为要比破坏兼容性好的多。下面的三个表声明，全都会使“x“成为rowid的别名（一个整数主键）：
The exception mentioned above is that if the declaration of a column with declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does not become an alias for the rowid and is not classified as an integer primary key. This quirk is not by design. It is due to a bug in early versions of SQLite. But fixing the bug could result in backwards incompatibilities. Hence, the original behavior has been retained (and documented) because behavior in a corner case is far better than a compatibility break. This means that the following three table declarations all cause the column "x" to be an alias for the rowid (an integer primary key):

但是下面的声明不会使“x”成为rowid的别名。
But the following declaration does not result in "x" being an alias for the rowid:

rowid值可以使用UPDATE语句以类似其他列的方式修改。既可以使用内置别名（“rowid”、“oid”或“_rowid_”）也可以使用整数主键创建的别名。同样，INSERT语句也可以在插入行的时候为rowid提供一个值，与通常的SQLite列不同，整数主键或者rowid列只能包含整数。整数主键和rowid不能存储浮点数、字符串、BLOB或者NULL。
Rowid values may be modified using an UPDATE statement in the same way as any other column value can, either using one of the built-in aliases ("rowid", "oid" or "_rowid_") or by using an alias created by an integer primary key. Similarly, an INSERT statement may provide a value to use as the rowid for each row inserted. Unlike normal SQLite columns, an integer primary key or rowid column must contain integer values. Integer primary key or rowid columns are not able to hold floating point values, strings, BLOBs, or NULLs.

当UPDATE语句试图将整数主键或rowid列修改为一个NULL、BLOB、字符串或者实数值，并且该值无法无损的转换为一个整数，那么就会引发一个“datatype mismatch”错误，并且语句会被忽略。当INSERT语句试图为整数主键或rowid列插入一个BLOB、字符串或者实数值，并且该值无法无损的转换为一个integer，那么就会引发一个“datatype mismatch”错误，并且语句会被忽略。
If an UPDATE statement attempts to set an integer primary key or rowid column to a NULL or blob value, or to a string or real value that cannot be losslessly converted to an integer, a "datatype mismatch" error occurs and the statement is aborted. If an INSERT statement attempts to insert a blob value, or a string or real value that cannot be losslessly converted to an integer into an integer primary key or rowid column, a "datatype mismatch" error occurs and the statement is aborted.

如果INSERT语句试图插入一个NULL值到rowid或者整数主键列中，系统会自动选择一个整数值作为rowid。详情参见这里。
If an INSERT statement attempts to insert a NULL value into a rowid or integer primary key column, the system chooses an integer value to use as the rowid automatically. A detailed description of how this is done is provided separately.

外键约束的父键不允许使用rowid。父键只能使用命名的列。
The parent key of a foreign key constraint is not allowed to use the rowid. The parent key must used named columns only.