SQLite 3 Beyond the Basics
In the introductory post to SQLite 3, we covered some basic commands, database and table creation, and more. In this one, we’ll go into more depth – exploring both familiar features from other databases and features that make SQLite stand out.
It is assumed you went through the introductory post before continuing with this one, or that you are comfortable with the basics of SQLite 3.
SELECT is a standard SQL command and belongs in the Data Query Language (or DQL). It lets you perform queries on a database and fetch required records. The results can be further filtered using various clauses provided by SQLite.
Open up the terminal and type:
This would create a database named Library.db in the current directory. Now, let us create a table that stores information about library users. A library user must have a name, an ID provided by the library which is unique, a user’s age and their date of joining. The following SQL query will create this table:
CREATE TABLE Users ( SerialNo INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, Name TEXT NOT NULL, Id TEXT NOT NULL UNIQUE, Age INTEGER NOT NULL, DOJ TEXT NOT NULL );
SerialNo is the Primary Key.
Remember: There can be any number of
UNIQUEcolumns in a table but there must only be one
Insert a record into the table using the following query:
INSERT INTO Users ( Name, Id, Age, DOJ) VALUES ( 'Shivam', 'U123', 19, '2015-01-31' );
Note: SQLite does not have a different datatype just for dates. To store Dates in SQLite you must enter them in
YYYY-MM-DDformat or they will be treated as strings.
You can insert more records using the above command, but it would be tedious for a large set of data. We will use the
.read meta-command. It executes SQL queries from the specified file. Save the following to a file called
BEGIN TRANSACTION; CREATE TABLE NewUsers ( SerialNo INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, Name TEXT NOT NULL, Id TEXT NOT NULL UNIQUE, Age INTEGER NOT NULL, DOJ TEXT NOT NULL ); INSERT INTO NewUsers VALUES (1, 'Abraham', 'U123', 19, '2015-02-28'); INSERT INTO NewUsers VALUES (2, 'Michael', 'U124', 23, '2014-12-14'); INSERT INTO NewUsers VALUES (3, 'Steve', 'U125', 35, '2012-11-20'); INSERT INTO NewUsers VALUES (4, 'Ben', 'U126', 15, '2013-05-12'); INSERT INTO NewUsers VALUES (5, 'Alice', 'U127', 17, '2013-03-01'); INSERT INTO NewUsers VALUES (6, 'Christopher', 'U128', 19, '2014-05-03'); INSERT INTO NewUsers VALUES (7, 'Elena', 'U129', 27, '2011-07-14'); INSERT INTO NewUsers VALUES (8, 'Daniel', 'U130', 43, '2010-08-14'); INSERT INTO NewUsers VALUES (9, 'Candice', 'U131', 18, '2014-02-05'); INSERT INTO NewUsers VALUES (10, 'Billy', 'U132', 20, '2015-01-24'); INSERT INTO NewUsers VALUES (11, 'Michael', 'U133', 19, '2013-02-12'); INSERT INTO NewUsers VALUES (12, 'Alice', 'U134', 25, '2010-07-14'); INSERT INTO NewUsers VALUES (13, 'Richard', 'U135', 40, '2011-12-14'); INSERT INTO NewUsers VALUES (14, 'Lester', 'U136', 18, '2014-09-05'); INSERT INTO NewUsers VALUES (15, 'Malvo', 'U137', 21, '2015-01-04'); INSERT INTO NewUsers VALUES (16, 'Alice', 'U138', 19, '2013-02-02'); INSERT INTO NewUsers VALUES (17, 'Drako', 'U139', 24, '2010-12-02'); COMMIT;
and read it from the sqlite command line using:
If everything goes well, you will have a NewUsers table with new records. Use the SELECT query to display them.
SELECT Id, Name, Age, DOJ FROM NewUsers;
By default, column headers are not displayed and display mode is set to line. Switch on the header display by entering
.header ON and change mode style to column using
.mode column. Enter the SELECT query again.
Tip: Use the
.show command to display values of various settings. You can also specify the column width manually using the
You can change the number of columns to be displayed and specify another name (alias) for a header. For example:
SELECT Id AS 'User ID', Name, DOJ AS 'Date of Joining' FROM NewUsers;
You can use the
.schema command to display all the SQL statements that were used to create the database. You can optionally provide a table name to it, to display the schema of the particular table.
The WHERE clause specifies a condition to fetch the results. For instance, to fetch user IDs and names with age 20 or above we can enter the following query.
SELECT Id, Name FROM NewUsers WHERE Age >= 20;
SQLite3 provides a number of operators to optimize the WHERE clause. Binary operators in the descending order of their precedence are:
|| * / % + - << >> & | < <= > >= = == != <> IS IS NOT IN LIKE GLOB MATCH REGEXP AND OR
SQLite3 supports four unary prefix operators,
- + ~ NOT.
Besides these, there are some other operators like
Provides a range of values (minimum to maximum) to operate on. For example, to select users that have a date of joining between
12-30-2014 and with age between 17 and 27 we can enter the following query:
SELECT * FROM NewUsers WHERE DOJ BETWEEN '2011-12-30' AND '2014-12-30' AND Age BETWEEN 17 AND 27;
AND, OR and NOT
The AND operator provides an intersection of two conditions, where as the OR operator provides a union of the two conditions. For example, to select users with Age greater than 20 and with SerialNo greater than 5, enter the query:
SELECT * FROM NewUsers WHERE Age > 20 AND SerialNo > 5;
Now, to select users with Age > 20 or users with SerialNo > 5, enter:
SELECT * FROM NewUsers WHERE Age > 20 OR SerialNo > 5;
The NOT operator is simply the negation of the corresponding condition. Users that do not have Ages between 20 and 30 can be extracted using:
SELECT * FROM NewUsers WHERE Age NOT BETWEEN 20 AND 30;
IS and IS NOT
IS and IS NOT work same as = (equals to) and != (not equals to) respectively.
IN and NOT IN
The IN operator searches for a value in a list of literals. For example, to search for users with library IDs U124, U127 and U129:
SELECT * FROM NewUsers WHERE Id IN ('U124', 'U127', 'U129');
LIKE and GLOB
LIKE does a pattern matching comparison using wildcards
% wildcard matches zero or more characters and
_ matches any single character. To select users with names beginning with ‘B’, ending with ‘y’ and the third last character as ‘l’ we may enter:
SELECT * FROM NewUsers WHERE Name LIKE "B%l_y";
Note: LIKE is case insensitive, so “B%” and “b%” work the same when used with LIKE.
GLOB is similar to LIKE except it is case sensitive and uses Unix File Globbing syntax.
The EXISTS operator evaluates either to 0 or to 1. If the query to the right of EXISTS returns no rows then EXISTS returns zero and if the query returns one or more rows then EXISTS returns one.
SELECT EXISTS ( SELECT * FROM NewUsers WHERE Age < 10 );
This would return a zero since there are no users with Age less than 10.
|| operator concatenates the specified strings. The below query would give the result: “sitepoint.com”.
SELECT 'sitepoint' || '.' || 'com';
ORDER BY Clause
As it implies, the ORDER BY clause sorts the records in either ascending or descending order. It operates on a particular column and accepts
ASC for ascending order and
DESC for descending order.
For instance, to sort all user records in ascending order of Name.
SELECT * FROM NewUsers ORDER BY Name ASC;
To sort user records in descending order of Age and with Date of Joining after 2013-05-12, you must enter the following query:
SELECT * FROM NewUsers WHERE DOJ > '2013-05-12' ORDER BY Age DESC;
LIMIT clause limits the number of results of a query to the specified number. It comes with an optional OFFSET flag that declares the number of records to be skipped. For example to select the sixth, seventh and eighth record we need to put a limit of three and an offset of 5.
SELECT * FROM NewUsers LIMIT 3 OFFSET 5;
Note: The above query can also be written as:
SELECT * FROM NewUsers LIMIT 5, 3;
GROUP BY Clause
GROUP BY combines multiple records and groups them using one or more columns. For example, to count the number of users with a particular name we may enter the query:
SELECT Name, COUNT(Name) FROM NewUsers GROUP BY Name;
If we need to specify a condition on groups we cannot use the WHERE clause because the WHERE clause specifies conditions on columns. To provide conditions on groups the HAVING clause is used. In the above example let us extract users with same names.
SELECT Name, COUNT(Name) FROM NewUsers GROUP BY Name HAVING COUNT(Name) > 1;
The DISTINCT keyword retrieves unique records. To fetch unique names enter the following query:
SELECT DISTINCT Name FROM NewUsers ORDER BY Name;
Attaching and Detaching Databases
When operating with multiple databases, an alias can be attached to each database. The
ATTACH DATABASE command attaches a name to a database. Enter
.databases. You will get a list of database names and their respective files. The main database is the primary database. Besides main, there is a temporary, hidden database temp. Both of these can neither be attached nor be detached from an alias. Let us attach Library.db to an alias LIB.
ATTACH DATABASE 'Library.db' AS 'LIB';
Consider another database Students.db. Attach it to STU.
ATTACH DATABASE 'Students.db' AS 'STU';
.databases. You will get something like this:
seq column does not have the record 1 between 0 and 2. It is actually the hidden database temp.
Now you can execute queries on both databases in the same sqlite session. To select top ten records from LIB we may enter the following:
SELECT * FROM LIB.NewUsers WHERE 1 LIMIT 10;
Suppose STU contains a table Students with the student names. We need to find out details of library users that are students (consider names to be a column in both Students and NewUsers for this example). We may enter the following query:
SELECT LIB.NewUsers.Id, LIB.NewUsers.Name, LIB.NewUsers.Age FROM LIB.NewUsers INNER JOIN STU.Students ON STU.Students.Name = LIB.NewUsers.Name;
To detach Library.db from its alias LIB enter the following:
DETACH DATABASE 'LIB';
Once the database has been created we can perform queries to create and modify records in tables. One or more of these queries that change the previous state of the database give rise to a Transaction. So a transaction can include inserting, updating and deleting records. It also includes creation, modification and deletion of tables. A transaction can have several smaller executions inside – consider a bank’s database. A user withdraws money, their account status is updated, the bank’s status is updated, the log database gets a new record, etc – all this needs to happen flawlessly, otherwise the money will be missing in one table, creating a problem for everyone.
When you perform queries in the form of a transaction, the changes are not added directly to the database. You have to commit them yourself. This helps in case some error occurs or the system fails during a transaction. In such a situation, the database will not change its state and will not be left with partial changes.
SQLite is a Transactional database i.e. it is ACID compliant. ACID is an acronym for Atomicity, Consistency, Isolation and Durability. These terms are discussed below.
Atomicity makes transactions indivisible. Either changes are committed all at once or the entire transaction fails.
Every transaction is consistent i.e. the final state reached after a transaction will also be a valid state.
Transactions are separated from one another in such a way that during their concurrent execution the failure of one will not affect the execution of others.
Once a transaction has been committed successfully, the changes in the database will be permanent and immune to any system failures.
SQLite offers commands to control transactions. The following table lists the control commands.
|BEGIN TRANSACTION||Marks the beginning of a transaction.|
|ROLLBACK||Erases current transaction. If it is used with the TO keyword, it rollbacks to a particular savepoint.|
|COMMIT||Commits a transaction to database. It is another alias for END TRANSACTION.|
Besides these there are two more commands: SAVEPOINT and RELEASE. SAVEPOINT is similar to BEGIN except that you must provide a name to the beginning transaction, thus allowing you to nest transactions. RELEASE removes a predefined savepoint and merges a named transaction into its parent transaction.
Let us continue with Library.db. Start a transaction by entering:
BEGIN TRANSACTION;. Insert some valid records in the table:
INSERT INTO NewUsers VALUES (18, 'Frank', 'U140', 45, '2009-11-02'); INSERT INTO NewUsers VALUES (19, 'Claire', 'U141', 43, '2009-11-01');
To save these changes you may enter
COMMIT;. Or if you want to discard them enter
SAVEPOINT statement starts the transaction with a name. Create a savepoint named SPT1 and add a new record.
SAVEPOINT SPT1; INSERT INTO NewUsers VALUES (20, 'Ricky', 'U142', 26, '2011-07-22');
Create another savepoint SPT2 and add another record.
SAVEPOINT SPT2; INSERT INTO NewUsers VALUES (21, 'Sundar', 'U143', 31, '2011-09-21');
Both these savepoints are nested (SPT2 in SPT1). You can commit both of them by entering:
RELEASE SAVEPOINT SPT1;
When RELEASE is applied on the outermost savepoint of the nested savepoints, it commits all the savepoints starting with the most recent one. Thus SPT2 and SPT1 both are committed.
RELEASE SAVEPOINT SPT2;will release SPT2. But since it is child to the parent transaction SPT1, its fate depends on it. If SPT1 is rolled back so is SPT2.
To discard both of them, you can rollback to the outermost savepoint i.e. SPT1.
ROLLBACK TO SAVEPOINT SPT1;
If you execute queries without explicitly defining a transaction, each query is wrapped in its own transaction and then executed. You can think of it as each query with a particular BEGIN TRANSACTION and a COMMIT statement. This is the autocommit mode. Autocommit mode is switched on by default.
If you explicitly start a transaction, the autocommit mode is turned off. After a commit (or rollback) to the same transaction the autocommit mode is again turned on.
At the beginning of the tutorial we imported a table to the database using the
.read command. To export a database or table we use the
.dump displays the database in SQL text format. You can use the redirection operator in the command line to save the dump to a file.
sqlite3 Library.db '.dump' > LibBackup.sql
If a file LibBackup.sql already exists, it is overwritten. The above command will dump the entire database to the file. You can optionally provide a table name as an argument to
.dump. It will export that particular table. To dump NewUsers enter:
sqlite3 Library.db '.dump NewUsers' > LibBackup.sql
By default the output generated through a query is written on the standard output. SQLite provides the
.output command to write the output of one or more queries to a file.
.output output.txt and a few queries. The results will be written to
output.txt stored in the current working directory. You can exit the sqlite session and view the file.
.output stdoutdisplays output to the standard output.
We’ve learned about SQLite operators and expressions. The tutorial also includes transactions and a brief discussion about SQLite’s transactional nature. I hope you found it useful; if you have any questions feel free to ask them in the comments and I will do my best to answer them.
.widthsets column width for column mode. Provide the width of characters in comma separated values. e.g.
.width 10, 20, 15↩