The most important operation you can perform on databases is reading data from it. The whole reason you need a database in the first place, is so that you can do something useful with the information it provides. Because you’re usually not reading everything within the database, we call this process selecting. You select the information you’re looking for from the database, and then you use some other programming language to do whatever you want with it. In this chapter I will provide the very basic syntax for reading from a database, in the rest of this course many additions and variations will be discussed.
Syntax
The very basic syntax for selecting entries from a table is
SELECT column_1, …, column_n FROM someTableName;
This selects the columns you specified of all the rows in the table someTableName.
If you want to select all columns as well, use the asterisk ( *
) character:
SELECT * FROM someTableName;
-- Selects the columns username and email from every entry in the table
SELECT username, email FROM accounts;
username | |
Panda | [email protected] |
Ginty | |
Juan | [email protected] |
Gandalf |
Finding Rows
You usually don’t need all the rows. Instead, you’re looking for rows that fulfil a certain condition; for example, rows that belong to the same category, or all rows that were inserted by a certain user.
To modify our selection to find only the entries that we want, use
SELECT __ FROM __ WHERE condition;
Again, SQL always tries to make queries feel like a real English sentence. “Select these columns, from this table, where this condition is true” is a simple way to remember the syntax.
-- Selects all accounts with an email address set SELECT * FROM accounts WHERE email NOT IS NULL;
id | username | |
1 | Panda | [email protected] |
2 | Ginty | |
3 | Juan | [email protected] |
4 | Gandalf |