This article is a short list of useful SQLite commands to make your life easier.
SQLite is an SQL engine intended mainly for embed on systems. It’s serverless, there isn’t a client-server process but direct access to the database file. Also, there aren’t configuration files and the whole system only depends on the C-Library.
Surely you already have a lot of sqlite databases running on your computer or even your phone. But maybe you don’t have the tools to access them, you can download sqlite tools from https://www.sqlite.org/download.html
I think this is enough for an introduction, lets see how to…
Create a a new database
It’s as easy as run:
Then you can use the standard SQL commands like CREATE TABLE, INSERT, SELECT,…
Delete a database
Simply remove the file, on unix-like OS:
On Windows, click on the file and press the delete key.
Dump to SQL
Where object is optional and can be part of the name of table (treat it as sql LIKE statment with % as wildcard), a trigger, etc. By default, sqlite outputs to screen, even sql dumps, to output to a file you first need to run:
.output filename .dump
If you want only the schema but not the data, the dump command is
Restore a sql dump
To restore a backup from a sql dump, use the command:
Show tables of a database
To list every table of a database, use the command
On larger (and real) databases with several tables you can filter the output with LIKE wildcards:
Get database information
Run the command
.dbinfo to get some information about the database:
I think this is the most useful SQLite command in this article, because helps you with the available commands:
To get help on an specific command:
I’m not showing SQL statements like SELECT or UPDATE, because if you are here you probably already know them.
The ones I’ve showed are the most useful sqlite commands… to me; I think that with the .help command and a basic SQL knowledge you can do anything you need on your sqlite databases.