If you don't already have a database named "library", then make sure to run
createdb library from the terminal before starting this chapter. The library database is needed to work through this chapter's content. The library database can be restored from a backup file. First download the backup file for this chapter from here.
Then run the following command:
psql -d library < select_data.sql
SELECT command lets us access data in a table. It is the most frequently used command by SQL developers, since developers are usually writing queries that show data to the user or provide data for an API endpoint. The command below performs a query on the
users table and displays all the contents of that table. Now, we can see data we inserted in the last chapter.
library=# SELECT * FROM users;
When the above command is entered into the psql console we get the following back.
id | username | enabled ---+---------------------------+--------- 20 | John Smith | f 1 | John Smith | t 2 | Harry Potter | f (3 rows)
Translated into plain English, the query we entered can be read as:
"Show me all the columns for all users in the table."
SELECT: The SQL command to retrieve data.
wild card character is an indicator to the SELECT command to return all the columns it finds in a given table(s).
FROM: The FROM Clause tells the SELECT statement which database table will be used.
users: This is the table from which data is retrieved.
We don't need to see all of the columns all of the time. For example, when we are writing code in production we may not even want to return the id of the record or some other columns. Also, returning all of the columns takes a longer time than returning just the columns we need.
If we only want to see the usernames, we could issue the command below:
library=# SELECT username FROM users; username --------------------------- John Smith John Smith Harry Potter (3 rows)
You can read that command as "Show me the username for all users in the table."
You can also return multiple columns from a table, by simply separating the column names by a comma in the SQL statement.
library=# SELECT username,id FROM users; username | id --------------------------+---- John Smith | 20 John Smith | 1 Harry Potter | 2 (3 rows)
This would translate in plain English to:
"Show me the username and id of all users."
The problem with the previous examples is that while we can select individual columns, the queries return all the rows in the table, something we might not want. This is where selection criteria come in. Since our table contains an
enabled column, a useful query might be one that fetches only the enabled users. In SQL this would be written as:
SELECT username FROM users WHERE enabled = true;
Translated into English this would read as:
"Show me the usernames of all the users who have the column named
enabledset to true." The diagram below explains in depth how the Select SQL statement works:
library=# SELECT username FROM users WHERE enabled = true; username --------------------------- John Smith (1 row)
Use of = sign in SQL
In the WHERE clause of SQL queries, = is treated as an 'equality' operator, in that it compares things. In this case, it's checking whether the column 'enabled' is equal to the value 'true'. In most programming languages, a single = operator is used for variable assignment, but not so in SQL.
SQL allows returning sorted data by adding the
ORDER BY [column name] clause to a query. By default, the sorting order is ascending. From the results below you can see that the usernames are in alphabetical order.
library=# SELECT username, id FROM users ORDER BY username; username | id ---------------------------+---- Harry Potter | 2 John Smith | 20 John Smith | 1 (3 rows)
You can further sort the
ORDER BY clause in either ascending or descending order, with the words
For example, to sort from larger to smaller values, you can use:
ORDER BY [field name] DESC
Here's how to use it in context of a complete query:
library=# SELECT username, id FROM users ORDER BY id DESC; username | id ---------------------------+---- John Smith | 20 Harry Potter | 2 John Smith | 1 (3 rows)
Conversely, you can go from smaller to larger values with:
ORDER BY [field name] ASC
library=# SELECT username, id FROM users ORDER BY id ASC; username | id ---------------------------+---- John Smith | 1 Harry Potter | 2 John Smith | 20 (3 rows)
That's it for querying the data for now.
Now, suppose we need to modify data in the table. For example, how do we
In the next chapter we will go over
DELETE commands that will let us modify existing data in a table.
Let's quickly recap again:
|SELECT * FROM users||Select all columns from table users.|
|SELECT username,id FROM users ORDER BY id ASC||Select the columns username, id from table users and sort the result in ascending order based on the id.|
|SELECT username FROM users WHERE enabled = true||Select the column username which is enabled.|