Talk to databases
Using SQL (Structured Query Language) we can talk to (relational) databases.
For example we can ask (query) the database to retrieve certain bits of information from tables, or we can insert, update or delete data. And SQL is also the language that is used to define the database structure in the first place.
SQL was invented in the 1970s, and it’s quite ugly to look at. However, lots of database systems support it, and so it’s quite common to use SQL in web applications in some way.
For example, we could retrieve all fields in the first row from our
table like this:
SELECT * FROM members WHERE id = 1;
SELECT tells the database that we’d like to retrieve data (as
opposed to, for example,
INSERT which inserts a new row, or
updates an existing row). The star
* means “all fields”.
FROM specifies the
table that we want to look at, and
WHERE specifies a condition that this row
needs to match: We’d like to retrieve the row where the value in the
So our query above would return a result containing one row:
1 | Anja | 2013-06-24
However, when we ask for all rows that have the
we’d get back two rows:
SELECT * FROM members WHERE joined_on = '2013-06-24';
This would return:
1 | Anja | 2013-06-24 2 | Carla | 2013-06-24
Instead of asking for all fields per row, we could also just ask for a certain column that we are interested in:
SELECT name FROM members WHERE joined_on = '2013-06-24';
This would return just the names:
In order to insert a new row to the table we could use an SQL statement like
this (assumning our
id column auto-increments, i.e. automatically assigns
the next number to the new row):
INSERT INTO members (name, joined_at) VALUES('Maren', '2013-06-24');
Updating looks like this:
UPDATE members SET joined_on = '2013-06-24' WHERE id = 3;
And deleting like this:
DELETE FROM members WHERE id = 3;
As you can see these statements all look somewhat similar, starting with a certain
command, naming the table, and ending with a semicolon. However, they also don’t
really look very consistent. For example, why does the
INSERT statement separate
the column names and inserted values, while the
UPDATE statement pairs them?
On the other hand, even though it’s a little weird, it’s also a very powerful language, and being able to figure out some SQL and manually writing it can be very useful when you have access to a database, and you want to find out some bits of information that cannot be retrieved with the application that is using the database: You’d just directly talk to the database, and ask it for the information you need.
Of course there are tools for this. We’ll look at libraries that make it easy to talk to databases in the chapter about ORMs.
Let’s play with a real database, and run some SQL statements next.