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:
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 Satriani 2|Steve Vai 3|The Tea Party 4|Noiseworks 5|Wayne Jury 6|Mr Percival 7|Iron Maiden 8|Atmasphere 9|Ian Moss 10|Magnum 11|Strapping Young Lad 12|Slayer 13|Primus 14|Pat Metheny 15|Frank Gambale 16|Frank Zappa 17|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.
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.
Add Headers
You can also use .headers to specify whether or not to display column headers.
To display headers, use this:
You can remove them with .headers off.
Show Settings
You can review these, and other settings at any time, by typing .show
sqlite> .show
echo: off
eqp: off
explain: off
headers: on
mode: column
nullvalue: ""
output: stdout
separator: "|"
stats: off
width: 12 20
New Format
Here's what the previous example looks like using columns and headers mode:
ArtistId ArtistName ---------- ------------ 1 Joe Satriani 2 Steve Vai 3 The Tea Part 4 Noiseworks 5 Wayne Jury 6 Mr Percival 7 Iron Maiden 8 Atmasphere 9 Ian Moss 10 Magnum 11 Strapping Yo 12 Slayer 13 Primus 14 Pat Metheny 15 Frank Gambal 16 Frank Zappa 17 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:
Result:
ArtistId ArtistName ------------ -------------------- 6 Mr Percival
To find all artists whose name begins with the letter "S", you could do this:
Result:
ArtistId ArtistName ------------ -------------------- 2 Steve Vai 11 Strapping Young Lad 12 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:
Result:
ArtistId ArtistName ------------ -------------------- 1 Joe Satriani 2 Steve Vai 3 The Tea Party 4 Noiseworks 5 Wayne Jury
Here's an example of selecting only those records with an ArtistId within a certain range:
Result:
ArtistId ArtistName ------------ -------------------- 5 Wayne Jury 6 Mr Percival 7 Iron Maiden 8 Atmasphere 9 Ian Moss 10 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:
Result:
ArtistName ------------ Wayne Jury Mr Percival Iron Maiden Atmasphere Ian Moss Magnum
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:
Result:
ArtistId ArtistName ------------ -------------------- 8 Atmasphere 15 Frank Gambale 16 Frank Zappa 9 Ian Moss 7 Iron Maiden 1 Joe Satriani 10 Magnum 6 Mr Percival 4 Noiseworks 14 Pat Metheny 13 Primus 12 Slayer 2 Steve Vai 11 Strapping Young Lad 3 The Tea Party 17 The Wiggles 5 Wayne Jury
And if we switch to descending order:
Result:
ArtistId ArtistName ------------ -------------------- 5 Wayne Jury 17 The Wiggles 3 The Tea Party 11 Strapping Young Lad 2 Steve Vai 12 Slayer 13 Primus 14 Pat Metheny 4 Noiseworks 6 Mr Percival 10 Magnum 1 Joe Satriani 7 Iron Maiden 9 Ian Moss 16 Frank Zappa 15 Frank Gambale 8 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:
Result:
ArtistId ArtistName ------------ -------------------- 1 Joe Satriani 2 Steve Vai 3 The Tea Party 4 Noiseworks 5 Wayne Jury