SQLite is a database engine that directly uses a file, for example named
There is therefore no need to authenticate the database users in order for them to access the data: everything is managed by the access control of the file itself.
More precisely, when modifying the database, the directory that stores the database file needs also to be writable by the engine, in order to create and remove a journal file.
SQLite can be used by applications to store settings, user information, messages, etc. Its engine is also used by projects such as OSQuery in order to benefit from a SQL interpreter.
In order to dump the content of a database file named
db.sqlite from the command line:
sqlite3 < /dev/null -bail -batch -cmd .dump db.sqlite
From the interactive prompt given by
sqlite3, it is possible to run SQL statements as well as special SQLite commands, prefixed with a dot.
# Show available commands and some help .help # Show the version .version # Quit .exit # List tables from the database .tables # Dump the schema of every table of the database .schema # List tables and dump the schema, for tables which names start with "user" .tables user% .schema user% # Change the output mode of SELECT statement (default one is pretty): # * Each cell in a new line .mode line # * Comma-separated values .mode csv # * Fixed-wicth columns .mode column .width 10 20 50 50 5 # * Pretty tables (the default one in OSQuery) .mode pretty # * Pipe-separated values (the default one in SQLite) .mode list
SQLite supports standard SQL statements:
SELECT statements can be merged together using:
UNION ALLto concatenate the results ;
UNIONto concatenate the results, removing the duplicated rows ;
INTERSECTto only keep the rows in common ;
EXCEPTto exclude some rows.
It is also possible to join tables (
INNER JOIN, etc.) using conditions expressed as:
SELECT * FROM users LEFT JOIN user_groups ON users.uid = user_groups.uid; SELECT * FROM users LEFT JOIN user_groups USING (uid);
When performing a
SELECT statement, it is possible to group some rows with a common value using
GROUP BY column.
In order to filter results before grouping, it is possible to use a
WHERE condition clause.
To filter results after grouping, the clause to use is
The filters may use
LIKE to match cells according to a pattern.
The pattern can use
_ as a wildcard for a single character and
% as a wildcard for multiple characters.
In order to escape
_, engines such as MSSQL use a syntax like
[_], but SQLite uses something else:
SELECT * FROM objects WHERE name LIKE 'prefix\_%' ESCAPE '\';
To print timestamps as a human-readable date, there exists a function,
SELECT DATETIME(users.last_update_time, 'unixepoch') FROM users;