SQLite | 第2部分

SQLite权限管理 (SQLite Rights Management)

SQLite does not include management rights to access and modify data. The management is done by the file system of the operating system: if the file containing the database is not writable by a user, it also can not edit records and structure of the database data.

SQLite不包括访问和修改数据的管理权限。 管理由操作系统的文件系统完成：如果包含数据库的文件不可被用户写入，则它也无法编辑数据库数据的记录和结构。

Rights management with GRANT and REVOKE is nonexistent, although they are part of the SQL-92 specification.

尽管GRANT和REVOKE是SQL-92规范的一部分，但是不存在使用GRANT和REVOKE进行的权限管理。

Using a SQLite database requires no installation or configuration.

使用SQLite数据库不需要安装或配置。

SQLite可移植性 (SQLite Portability)

The library is entirely written in ANSI-C, the standard version of the C programming language, and uses no external libraries other than the standard library of language. This makes SQLite compile without modification on all major computer architectures by providing a compiler complying with ANSI C.

该库完全用ANSI-C(C编程语言的标准版本)编写，除标准语言库外不使用任何外部库。 通过提供符合ANSI C的编译器，SQLite可以在所有主要计算机体系结构上进行修改而无需修改。

The file database SQLite is entirely independent of the operating system and architecture on which they are used. The same database file can be used on two architectures with radically different operation, SQLite, providing an abstraction layer totally transparent to the developer. The files are compatible with each other for each major version of the library since version 3.0.0 of SQLite, so a file created with version 3.0.0 will be available with version 3.6.19 and vice versa, the files created between two different major versions (2.0.0 and 3.0.0 for example) may be compatible (especially in backward compatibility), but this is not always the case.

文件数据库SQLite完全独立于使用它们的操作系统和体系结构。 可以在操作完全不同的两种体系结构SQLite上使用同一数据库文件，从而为开发人员提供了完全透明的抽象层。 自SQLite 3.0.0版以来，库的每个主要版本文件都相互兼容，因此使用3.0.0版创建的文件将在3.6.19版中可用，反之亦然，这是在两个不同的主要版本之间创建的文件版本(例如2.0.0和3.0.0)可能是兼容的(尤其是向后兼容)，但并非总是如此。

SQLite数据类型 (SQLite Data Types)

SQLite uses dynamic typing for the content of cells, unlike almost all DBMS’ that use static typing: when creating a new table in the database, it is a recommended standard or affinity, not forced to store the data in the column is filled, not a type that defines how it will be represented in memory, this being reserved for the cell itself. When data are entered into the database, SQLite will attempt to convert the new data to the type recommended but will not do so if this is impossible.

SQLite对单元格的内容使用动态类型，这与几乎所有使用静态类型的DBMS不同：在数据库中创建新表时，建议使用标准或相似性，而不是强制将数据存储在填充的列中，而不是定义如何在内存中表示的类型，该类型保留给单元本身。 将数据输入数据库后，SQLite会尝试将新数据转换为推荐的类型，但如果不可能，则不会这样做。

There are several types of affinity in SQLite, the latter defining how SQLite will work at the entry of new data:

SQLite中的亲缘关系有几种类型，后者定义SQLite在输入新数据时的工作方式：

• TEXT: Saves the data as a string of unlimited size. If a number is entered in a column of this type, it will automatically be converted into a string;

  文本：将数据另存为大小不受限制的字符串。 如果在此列中输入数字，它将自动转换为字符串。

• NUMERIC: attempts to save the data as a whole or as a real, but if this proves impossible, the data will be recorded as a string;

  NUMERIC：尝试将数据整体或真实保存，但是如果证明不可能，则将数据记录为字符串；

• INTEGER: Saves the data as a whole if it can be encoded without loss, but can use REAL or TEXT types if it can be done;

  整数：如果可以对数据进行无损编码，则将其整体保存，但是如果可以的话，可以使用REAL或TEXT类型。

• REAL: saves the data as a real, even if it is an integer. If the value is too large, the data will be converted to string;

  REAL：将数据保存为实数，即使它是整数。 如果值太大，则数据将转换为字符串；

• NONE the data is stored as is, without conversion.

  NONE的数据被存储为是，无需转换。

Thus, each type of affinity can accept any data type, the only exception is the particular type INTEGER PRIMARY KEY, when applied to a single column because it is not a usual type but an alias for the ROWID column inside the motor that matches the address of record, unique across the table.

因此，每种亲缘关系类型都可以接受任何数据类型，唯一的例外是特殊类型的INTEGER PRIMARY KEY，应用于单个列时，因为它不是通常的类型，而是与地址匹配的马达内ROWID列的别名记录，在整个表中都是唯一的。

The use of dynamic typing improves consistency between data from the database and the types of language used to query if it is also a dynamically typed language to (like Python, PHP, Perl or Ruby ) pose no real problems with using static typing languages (like C / C + + or Java).

动态类型化的使用提高了数据库中的数据与查询语言的类型之间的一致性，该语言类型也是一种动态类型化的语言(例如Python，PHP，Perl或Ruby)，而使用静态类型化的语言(例如C / C ++或Java)。

SQLite –确定相似性的类型 (SQLite – Determining the type of affinity)

To maintain compatibility with other platforms, SQLite automatically converts the type names declared in the type affinity that best, thus:

为了保持与其他平台的兼容性，SQLite会自动转换以最佳类型相似性声明的类型名称，从而：

• All type names containing the keyword will be recognized as INT INTEGER fields. However, only the declaration INTEGER PRIMARY KEY will be recognized as an alias for the ROWID;
  所有包含关键字的类型名称都将被识别为INT INTEGER字段。 但是，只有声明INTEGER PRIMARY KEY将被识别为ROWID的别名。
• All type names containing the following keywords: CHAR (this includes VARCHAR), CLOB or TEXT fields will be recognized as TEXT;
  所有包含以下关键字的类型名称：CHAR(包括VARCHAR)，CLOB或TEXT字段将被识别为TEXT；
• All type names containing the keyword will be recognized as BLOB fields affinity NONE;
  所有包含关键字的类型名称都将被识别为BLOB字段亲和力NONE；
• All type names containing the following keywords: REAL, FLOAT or DOUBLE field will be recognized as REAL;
  所有包含以下关键字的类型名称：REAL，FLOAT或DOUBLE字段将被识别为REAL；
• In all other cases, or if the type is not specified, the affinity is NUMERIC will be used.
  在所有其他情况下，或者如果未指定类型，则将使用关联性NUMERIC。

SQLite自助存储 (SQLite Self Storage)

Although SQLite uses dynamic typing, the memory representation and processing performed on the data requires the use of different classes of storage. This is only valid for version 3 and later versions, because the data were stored as strings in the previous versions.

尽管SQLite使用动态类型，但是对数据执行的内存表示和处理需要使用不同的存储类别。 这仅对版本3和更高版本有效，因为数据在以前的版本中存储为字符串。

All the data handled by the engine of database use the following types:

数据库引擎处理的所有数据都使用以下类型：

• NULL: data is the special type NULL, which indicates the lack of information or an undefined value;

  NULL：数据是特殊的NULL类型，表示缺少信息或未定义的值；

• INTEGER *: the data is a signed integer and it is registered, according to the order of magnitude of 1, 2, 3, 4, 6 or 8 bytes;

  INTEGER *：数据是一个有符号整数，并按照1、2、3、4、6或8个字节的大小顺序进行注册；

• REAL: the data is a floating point number recorded by an 8-byte IEEE;

  REAL：数据是8字节IEEE记录的浮点数；

• TEXT: the data is a string, encoded in UTF-8 (default), UTF-16 or UTF-BE-16-LE;

  TEXT：数据是字符串，以UTF-8(默认)，UTF-16或UTF-BE-16-LE编码；

• BLOB: the data is recorded as it was given.

  BLOB：数据按给定记录。

空值 (NULL)

The standard does not define exactly how the treaty should be null.

该标准没有确切定义条约应如何无效。

Like most RDBMSs, NULL all records are considered distinct from the UNIQUE constraint, but are considered identical by the UNION operator and the keyword DISTINCT.

与大多数RDBMS一样，将NULL视为所有记录都不同于UNIQUE约束，但是UNION运算符和关键字DISTINCT认为它们相同。

Arithmetic operations including a NULL in their expression return the value “UNKNOWN” (indeterminate value). In the Boolean operations, the return value could be NULL if UNKNOWN occurs and the result can be determined with certainty: NULL OR gives a value of 1, but NULL OR 0 gives the value UNKNOWN because the operation can be resolved with certainty.

表达式中包含NULL的算术运算将返回值“ UNKNOWN”(不确定值)。 在布尔运算中，如果发生UNKNOWN，则返回值可以为NULL，并且可以确定地确定结果：NULL OR给出的值为1，但是NULL OR 0给出的值为UNKNOWN，因为可以确定地解析操作。

日期 (Dates)

SQLite does not have a type to represent dates. However, there is a set of functions for manipulating them. Storing a date can be a string in its ISO 8601 or an integer in the form of a UNIX timestamp.

SQLite没有代表日期的类型。 但是，有一组函数可以操纵它们。 存储日期可以是ISO 8601中的字符串，也可以是UNIX时间戳形式的整数。

约束条件 (Constraints)

SQLite manages constraints on one or more columns. The constraint NOT NULL, CHECK, DEFAULT and COLLATE are declared on the column while PRIMARY KEY, UNIQUE, CHECK and FOREIGN KEY can be reported on one or more columns.

SQLite管理一个或多个列上的约束。 在列上声明了约束NOT NULL，CHECK，DEFAULT和COLLATE，而可以在一个或多个列上报告PRIMARY KEY，UNIQUE，CHECK和FOREIGN KEY。

The UNIQUE constraint automatically creates an index on the column or columns on which it is applied.

UNIQUE约束会在应用约束的一个或多个列上自动创建一个索引。

Continued…

继续…

翻译自: https://www.eukhost.com/blog/webhosting/sqlite-part-2/