SQLite Introduction
This tutorial will help you understand what is SQLite, it is different between SQL, why need it, as well as its application database approach.
SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is a fastest growing database engine, which is an increase in terms of popularity, regardless of its size. SQLite source code is not subject to copyright restrictions.
What is SQLite?
SQLite is a in-process library, to achieve self-sufficiency, serverless, zero-configuration, transactional SQL database engine. It is a zero-configuration database, which means that other databases, you do not need to configure the system.
Like other database, SQLite engine is not a separate process, can be static or dynamic connections by application requirements. SQLite direct access to its stored files.
Why use SQLite?
It does not require a separate server process or operating system (no server).
SQLite does not require configuration, which means that no installation or management.
A complete SQLite database is a single disk file stored in the cross-platform.
SQLite is very small, lightweight, and when less than fully configured 400KiB, omit the optional function configuration when less than 250KiB.
SQLite is self-sufficient, which means that no external dependencies.
SQLite transaction is fully ACID-compliant, allowing access from multiple processes or threads safety.
SQLite query language support most SQL92 (SQL2) standards.
SQLite written using ANSI-C, and provides a simple and easy to use API.
SQLite can run UNIX (Linux, Mac OS-X, Android, iOS) and Windows (Win32, WinCE, WinRT) in.
history
2000 - D. Richard Hipp design SQLite is not required in order to manage the procedure.
2000-- In August, SQLite1.0 release GNU Database Manager (GNU Database Manager).
2011 - Hipp announced, DB UNQl add interfaces to SQLite, development UNQLite (document-oriented database).
SQLite limitations
In SQLite in, SQL92 unsupported features are as follows:
| 特性 | 描述 |
|---|---|
| RIGHT OUTER JOIN | 只实现了 LEFT OUTER JOIN。 |
| FULL OUTER JOIN | 只实现了 LEFT OUTER JOIN。 |
| ALTER TABLE | 支持 RENAME TABLE 和 ALTER TABLE 的 ADD COLUMN variants 命令，不支持 DROP COLUMN、ALTER COLUMN、ADD CONSTRAINT。 |
| Trigger 支持 | 支持 FOR EACH ROW 触发器，但不支持 FOR EACH STATEMENT 触发器。 |
| VIEWs | 在 SQLite 中，视图是只读的。您不可以在视图上执行 DELETE、INSERT 或 UPDATE 语句。 |
| GRANT 和 REVOKE | 可以应用的唯一的访问权限是底层操作系统的正常文件访问权限。 |
SQLite command
Interact with a relational database SQLite commands are similar to standard SQL. Commands include CREATE, SELECT, INSERT, UPDATE, DELETE, and DROP. These commands are based on the nature of their operations can be divided into the following categories:
DDL - Data Definition Language
| 命令 | 描述 |
|---|---|
| CREATE | 创建一个新的表，一个表的视图，或者数据库中的其他对象。 |
| ALTER | 修改数据库中的某个已有的数据库对象，比如一个表。 |
| DROP | 删除整个表，或者表的视图，或者数据库中的其他对象。 |
DML - Data Manipulation Language
| 命令 | 描述 |
|---|---|
| INSERT | 创建一条记录。 |
| UPDATE | 修改记录。 |
| DELETE | 删除记录。 |
DQL - Data Query Language
| 命令 | 描述 |
|---|---|
| SELECT | 从一个或多个表中检索某些记录。 |