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.)

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));

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.