SQLite - Select Data
To select data from an SQLite database, use the SELECT statement.
When you use this statement, you specify which table/s to select data from, as well as the columns to return from the query.
You can also provide extra criteria to further narrow down the data that is returned.
Simple SELECT Statement
We've already used a SELECT statement previously, when we inserted data.
The statement we used was this:
SELECT * FROM Artists;
This is quite self-explanatory — it is telling SQLite to select all columns from the Artists table. The asterisk (*) is a shorthand way of saying "all columns". It saves us writing out the names of all the columns.
Therefore, this query returns all records and all columns. Like this:
1|Joe Satriani2|Steve Vai3|The Tea Party4|Noiseworks5|Wayne Jury6|Mr Percival7|Iron Maiden8|Atmasphere9|Ian Moss10|Magnum11|Strapping Young Lad12|Slayer13|Primus14|Pat Metheny15|Frank Gambale16|Frank Zappa17|The Wiggles
Formatting
Let's format the output so that our results are a bit easier to read.
Use Columns
You can use .mode to change the output mode. The above example uses .mode list, which displays the results as a list.
Let's change the mode to use columns.
.mode column
When you do this, you may need to adjust the column widths (any data that is too wide for the column will be truncated).
Adjust Column Widths
To adjust the column widths, use the .width command, followed by widths for each column.
The following example sets the first column to 12 and the second column to 20.
.width 12 20
Add Headers
You can also use .headers to specify whether or not to display column headers.
To display headers, use this:
.headers on
You can remove them with .headers off.
Show Settings
You can review these, and other settings at any time, by typing .show
sqlite> .showecho: offeqp: offexplain: offheaders: onmode: columnnullvalue: ""output: stdoutseparator: "|"stats: offwidth: 12 20
New Format
Here's what the previous example looks like using columns and headers mode:
ArtistId ArtistName---------- ------------1 Joe Satriani2 Steve Vai3 The Tea Part4 Noiseworks5 Wayne Jury6 Mr Percival7 Iron Maiden8 Atmasphere9 Ian Moss10 Magnum11 Strapping Yo12 Slayer13 Primus14 Pat Metheny15 Frank Gambal16 Frank Zappa17 The Wiggles
The WHERE Clause
You can use the WHERE clause to narrow the results down. The clause allows you to stipulate specific criteria for which to apply to the query.
For example, you could specify that only records, where a certain field contains a certain value should be returned.
Here's a very specific example. It specifies that only the record with an ArtistId equal to 6 should be returned:
SELECT * FROM Artists
WHERE ArtistId = 6;
Result:
ArtistId ArtistName------------ --------------------6 Mr Percival
To find all artists whose name begins with the letter "S", you could do this:
SELECT * FROM Artists
WHERE ArtistName LIKE 'S%';
Result:
ArtistId ArtistName------------ --------------------2 Steve Vai11 Strapping Young Lad12 Slayer
The percent sign (%) is a wildcard character that can be used to specify any character. So, in this example, we are specifying that the artist's name must be like this pattern (i.e. the pattern starting with the letter S and following with any other character).
Another example of using the WHERE clause is to retrieve a range of values. For example, we could look for records where the value is less than a number, greater than a number, or within a certain range.
Here's an example of selecting only those records with an ArtistId less than a number:
SELECT * FROM Artists
WHERE ArtistId < 6;
Result:
ArtistId ArtistName------------ --------------------1 Joe Satriani2 Steve Vai3 The Tea Party4 Noiseworks5 Wayne Jury
Here's an example of selecting only those records with an ArtistId within a certain range:
SELECT * FROM Artists
WHERE ArtistId BETWEEN 5 AND 10;
Result:
ArtistId ArtistName------------ --------------------5 Wayne Jury6 Mr Percival7 Iron Maiden8 Atmasphere9 Ian Moss10 Magnum
Selecting Columns
You can specify that only certain columns are returned in the result set. Simply write the column names in the query. Multiple column names need to be separated by a comma.
It is good practice to select ony those columns you need. Using *, while convenient, can cause extra overhead if it returns more columns than is required.
So let's run the same query again, but this time, only select the ArtistName column:
SELECT ArtistName FROM Artists
WHERE ArtistId BETWEEN 5 AND 10;
Result:
ArtistName------------Wayne JuryMr PercivalIron MaidenAtmasphereIan MossMagnum
The ORDER BY Clause
You can use the ORDER BY clause to limit the number of records returned.
You can add ASC for ascending order, or DESC for descending order. If you don't add anything, it will use ascending.
Here, we order by the ArtistName field in ascending order:
SELECT * FROM Artists
ORDER BY ArtistName;
Result:
ArtistId ArtistName------------ --------------------8 Atmasphere15 Frank Gambale16 Frank Zappa9 Ian Moss7 Iron Maiden1 Joe Satriani10 Magnum6 Mr Percival4 Noiseworks14 Pat Metheny13 Primus12 Slayer2 Steve Vai11 Strapping Young Lad3 The Tea Party17 The Wiggles5 Wayne Jury
And if we switch to descending order:
SELECT * FROM Artists
ORDER BY ArtistName DESC;
Result:
ArtistId ArtistName------------ --------------------5 Wayne Jury17 The Wiggles3 The Tea Party11 Strapping Young Lad2 Steve Vai12 Slayer13 Primus14 Pat Metheny4 Noiseworks6 Mr Percival10 Magnum1 Joe Satriani7 Iron Maiden9 Ian Moss16 Frank Zappa15 Frank Gambale8 Atmasphere
The LIMIT Clause
You can use the LIMIT clause to limit the number of records returned. This can be handy if your table contains a large number of records but you only want to see a handful.
Here, we limit the record set to only five records:
SELECT * FROM Artists LIMIT 5;
Result:
ArtistId ArtistName------------ --------------------1 Joe Satriani2 Steve Vai3 The Tea Party4 Noiseworks5 Wayne Jury
