SQLite Introduction
This tutorial helps you understand what SQLite is, the differences between it and SQL, why you need it, and how its applications handle databases.
SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the fastest-growing database engine, in terms of popularity, regardless of its size. SQLite source code is not subject to copyright restrictions.
What is SQLite?
SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. It is a zero-configuration database, which means that unlike other databases, you do not need to configure it in the system.
Like other databases, the SQLite engine is not a standalone process; it can be statically or dynamically linked into an application as needed. SQLite directly accesses its storage files.
Why use SQLite?
No separate server process or operating system is required (serverless).
SQLite requires no configuration, which means no installation or administration is needed.
A complete SQLite database is stored in a single cross-platform disk file.
SQLite is very small and lightweight; it is less than 400KiB when fully configured, and less than 250KiB when optional features are omitted.
SQLite is self-contained, meaning it requires no external dependencies.
SQLite transactions are fully ACID-compliant, allowing safe access from multiple processes or threads.
SQLite supports most of the query language features of the SQL92 (SQL2) standard.
SQLite is written in ANSI-C and provides a simple and easy-to-use API.
SQLite runs on UNIX (Linux, Mac OS-X, Android, iOS) and Windows (Win32, WinCE, WinRT).
History
2000 -- D. Richard Hipp designed SQLite to enable programs to operate without requiring administration.
2000 -- In August, SQLite 1.0 was released as the GNU Database Manager.
2011 -- Hipp announced the addition of the UNQl interface to SQLite DB, developing UNQLite (a document-oriented database).
SQLite Limitations
In SQLite, the SQL92 features that are not supported are as follows:
| Feature | Description |
|---|---|
| RIGHT OUTER JOIN | Only LEFT OUTER JOIN is implemented. |
| FULL OUTER JOIN | Only LEFT OUTER JOIN is implemented. |
| ALTER TABLE | Supports the RENAME TABLE and ALTER TABLE ADD COLUMN variants commands, but does not support DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT. |
| Trigger support | Supports FOR EACH ROW triggers, but not FOR EACH STATEMENT triggers. |
| VIEWs | In SQLite, views are read-only. You cannot execute DELETE, INSERT, or UPDATE statements on views. |
| GRANT and REVOKE | The only access permissions that can be applied are the normal file access permissions of the underlying operating system. |
SQLite Commands
The standard SQLite commands for interacting with relational databases are similar to SQL. The commands include CREATE, SELECT, INSERT, UPDATE, DELETE, and DROP. Based on the nature of their operations, these commands can be divided into the following categories:
DDL - Data Definition Language
| Command | Description |
|---|---|
| CREATE | Creates a new table, a view of a table, or other objects in the database. |
| ALTER | Modifies an existing database object in the database, such as a table. |
| DROP | Deletes an entire table, a view of a table, or other objects in the database. |
DML - Data Manipulation Language
| Command | Description |
|---|---|
| INSERT | Creates a record. |
| UPDATE | Modifies records. |
| DELETE | Deletes records. |
DQL - Data Query Language
| Command | Description |
|---|---|
| SELECT | Retrieves certain records from one or more tables. |