Discover how to use and probe a SQLite database
Operators and Functions
In all variants of SQL, operators are tools that let you compare data among records or process them in several ways (e.g., to make calculations or reformat text strings). SQLite supports all the basic arithmetic (e.g., equality, the four operators (+ - * /), inequalities, etc.) and logical operators (e.g., AND
and OR
), just to name the most common. Some operators are combinations of simpler ones, like BETWEEN
. For example, although the two statements
SELECT * FROM budget WHERE (cost BETWEEN 10 AND 20); SELECT * FROM budget WHERE ((cost >= 10) AND (cost <= 20);
both mean the same thing – that is, "show me all data of all the expenses in the table called budget whose cost is between 10 and 20 dollars" – the first version is faster both to write and execute.
The last SQLite operator to introduce is LIKE
, which is used to compare strings. In the statement
SELECT * FROM sales WHERE customer LIKE '%William_';
the final underscore means that any single character in this position is valid, whereas the percent sign means that any sequence of characters in this position is valid. Therefore, the statement will display the sales made to customers with names like John Williams, B. Williamx, Mike T. Williamm, and so on.
Functions are operators that directly "extract" one number or string from (combinations of) other numbers or strings, for example:
abs(X)
returns the absolute value of the number X.round(X,Y)
returns the number X rounded to Y decimal digits.substr(X,Y,Z)
returns Z consecutive characters of string X, starting from position Y.substr('Linux', 3,2)
returns the string 'ux'.
SQLite has many more core and aggregate functions that are available by default, as well as functions you can use by loading extensions.
Adding, Updating, and Selecting Records
The whole point of a database is to store data in an ordered way, keep it current, and above all search and filter the data with any combination of criteria. This work is done with the SQL commands INSERT
, UPDATE
, DELETE
, and SELECT
. In the next examples, assume you keep a catalog of all your books in the books table defined earlier. Listing 1 shows how you would add, update, and delete a book from the SQLite command prompt. (See the "Using SQLite at the Command Line" box.)
Listing 1
Simple SQLIte Statements
sqlite> INSERT INTO books(title, author, price) VALUES('Lord of the rings', 'Tolkien' 25.50); sqlite> UPDATE books SET price=20.50 WHERE bookid=21; sqlite> DELETE FROM books WHERE ((title='Lord of the rings') AND (price=20.50));
Using SQLite at the Command Line
To get a SQLite command prompt, enter sqlite3 <dbn>
, where <dbn>
is the name of the file with the database you want to access. If you don't specify a name, SQLite creates a temporary database; if the name you enter doesn't exist, SQLite creates it. Ctrl+D stops the program, and Ctrl+C stops a SQL statement.
The first command adds a book to the database, and the second command corrects its price (assuming the automatically generated ID of that book record is 21). The third command removes all the records in the table with the same title and updated price.
In the next statement, the SELECT
command, which has already been introduced, finds all the records that match certain conditions in the specified table and displays them grouped or sorted according to other criteria:
sqlite> SELECT title as TITLE, sum(price) AS 'total_expense' \ FROM books WHERE author='Tolkien' GROUP BY book_title \ ORDER BY total_expense DESC;
In this case, it produces a list of how much you have spent on each Tolkien book in your library:
- in total (courtesy of the
sum()
function) - even if you have more than one edition of the same book (thanks to the
GROUP BY
clause) - from the most to least expensive (because of the
ORDER BY
clause in descending,DESC
, order)
You can SELECT
from multiple tables simultaneously by combining them with JOIN
statements, as I will demonstrate in a moment.
Practical Examples
The first cool thing you might want to do is get all or part of your data out of the database, formatted in ways that you can then easily process and reuse with other software.
For example, you can save to a text file all the SQL statements that would create a perfect copy of your database by using the .output
and .dump
metacommands:
sqlite> .output mybackup.sql sqlite> .dump sqlite> .exit
Even better, you can save the same commands in a text file (or have other scripts generate them for you) and then run them with the sqlite3
tool from the Linux command line:
# sqlite3 < commands.sqlite
To restore or clone a SQLite database from the dump, type (or, again, put it into a shell script) this command, instead:
# sqlite3 new.db < mybackup.sql
The file passed to sqlite3
can contain any sequence of commands, including SELECT
statements. Also, the data written to the file set by .output
can be formatted in any of the ways supported by another very useful dot command called .mode
: You can export your data as HTML tables, comma- or tab-separated files that are immediately usable in any spreadsheet, and five or six other formats.
« Previous 1 2 3 4 Next »
Buy this article as PDF
(incl. VAT)
Buy Linux Magazine
Subscribe to our Linux Newsletters
Find Linux and Open Source Jobs
Subscribe to our ADMIN Newsletters
Support Our Work
Linux Magazine content is made possible with support from readers like you. Please consider contributing when you’ve found an article to be beneficial.
News
-
Red Hat Adds New Deployment Option for Enterprise Linux Platforms
Red Hat has re-imagined enterprise Linux for an AI future with Image Mode.
-
OSJH and LPI Release 2024 Open Source Pros Job Survey Results
See what open source professionals look for in a new role.
-
Proton 9.0-1 Released to Improve Gaming with Steam
The latest release of Proton 9 adds several improvements and fixes an issue that has been problematic for Linux users.
-
So Long Neofetch and Thanks for the Info
Today is a day that every Linux user who enjoys bragging about their system(s) will mourn, as Neofetch has come to an end.
-
Ubuntu 24.04 Comes with a “Flaw"
If you're thinking you might want to upgrade from your current Ubuntu release to the latest, there's something you might want to consider before doing so.
-
Canonical Releases Ubuntu 24.04
After a brief pause because of the XZ vulnerability, Ubuntu 24.04 is now available for install.
-
Linux Servers Targeted by Akira Ransomware
A group of bad actors who have already extorted $42 million have their sights set on the Linux platform.
-
TUXEDO Computers Unveils Linux Laptop Featuring AMD Ryzen CPU
This latest release is the first laptop to include the new CPU from Ryzen and Linux preinstalled.
-
XZ Gets the All-Clear
The back door xz vulnerability has been officially reverted for Fedora 40 and versions 38 and 39 were never affected.
-
Canonical Collaborates with Qualcomm on New Venture
This new joint effort is geared toward bringing Ubuntu and Ubuntu Core to Qualcomm-powered devices.