SQL: reading and writing data

SQL is a declarative query language for relational databases: SELECT reads data, INSERT adds rows, and UPDATE and DELETE change and remove them.

The language for asking a database questions

We’ve learned to make tables. Now we need a way to ask them questions and to change what’s in them. That is what SQL was made for (said “S-Q-L” or “sequel”), the Structured Query Language. It was devised in the 1970s at IBM for relational databases, built on mathematician Edgar Codd’s ideas about tables and relations. Since then it has become an international standard, and you’ll meet it in almost every database: PostgreSQL, MySQL, SQLite, Oracle and others. The dialects differ slightly, but the core is the same.

SQL is declarative: you write what you want to get, not how to look for it. You don’t say “go through every row and compare the price”. You write “give me the services that cost more than a hundred”, and the database decides how. That is a great relief, and you pay for it with a new habit: SQL isn’t read top to bottom like ordinary code, but by meaning (we’ll get to that).

Creating a table

A table is described once, with the CREATE TABLE command.

CREATE TABLE items (
  id    serial PRIMARY KEY,
  title text NOT NULL,
  price integer,
  category text
);

For each column you write a name and a type: integer (a whole number), text, numeric (a number with a fractional part, for money), boolean (yes/no), date and timestamp (a date, and a moment in time). serial is a whole number that the database hands out itself, in order. PRIMARY KEY makes the column the primary key. NOT NULL forbids leaving the field empty.

Adding rows

INSERT INTO items (title, price, category) VALUES
  ('Portrait session', 300, 'main'),
  ('Photos for your website', 150, 'extra');

First the table’s name and the list of columns, then after VALUES the rows in parentheses. Text always goes in single quotes, numbers without them. We don’t name the id column: serial hands it out.

Choosing: SELECT

The most common command in SQL is SELECT: “show me”. Play with the sandbox below: it has a table made from your services (the prices and categories are invented) and some ready-made queries. Change the queries, break them, read the errors.

This is a teaching interpreter that understands a few main commands. It is not PostgreSQL: the messages look like real ones, but they are written here. The prices and categories are invented, and the titles come from your brief.

All rowsOver 100Two most expensiveBy categoryChange oneDelete oneUPDATE without WHERE

The items table

id	title	price	category

Reset the table

Query SELECT title, price FROM items WHERE price > 100

Runor Ctrl+Enter

Press “Run”.

Text alternative: on the left is a table of services with an id, a title, a price and a category; on the right are the query box and the result. Under the result the steps of running a SELECT are listed, with the number of rows left.

Now part by part.

SELECT title, price     -- which columns to show
FROM items              -- from which table
WHERE price > 100       -- which rows to keep
ORDER BY price DESC     -- how to sort (DESC: largest first)
LIMIT 3;                -- how many rows to keep

• SELECT * means “all columns”. It’s handy while you are looking around; in a real program it’s better to name the columns you need, so the query doesn’t break when someone adds a new column to the table.
• In WHERE you compare: =, <> (not equal), <, >, <=, >=. Conditions are joined with AND and OR. LIKE 'P%' finds text that starts with “P” (the % sign means “anything”), and IN ('a', 'b') checks whether a value is in a list.
• ORDER BY without the word DESC sorts in ascending order.
• LIMIT is useful when you need “the three most expensive” or one page of a long list.

NULL means “unknown”

If a column holds nothing, it holds the special value NULL. This is not zero and not an empty string: it means “the value is unknown”. A strange consequence follows: comparing with the unknown also gives “unknown”. So WHERE price = NULL finds no rows at all. You look for empty values with the words IS NULL, and for filled ones with IS NOT NULL. This is one of the most common traps for beginners.

Check yourself: how do you find the services with no price?

WHERE price IS NULL. Writing price = NULL returns nothing, because any comparison with NULL gives “unknown”.

The order in which SQL runs

Look at the strip “The order a SELECT runs in” under the result in the sandbox. The query is written top to bottom, but it runs differently:

1. FROM: take the table (or several).
2. WHERE: keep the matching rows.
3. GROUP BY: gather what’s left into groups.
4. SELECT: pick the columns and work out the expressions.
5. ORDER BY: sort.
6. LIMIT: cut.

Two things that trip everyone up depend on this order. First, you can’t write COUNT(*) in WHERE: when WHERE runs there are no groups yet, so there is nothing to count. Second, you can’t use an alias from SELECT in WHERE (SELECT price * 2 AS double and then WHERE double > 100 won’t work), because SELECT runs later.

Counting: GROUP BY

Suppose you need to know how many services each category has.

SELECT category, COUNT(*) AS n
FROM items
GROUP BY category
ORDER BY n DESC;

GROUP BY gathers rows with the same category into groups, and COUNT(*) counts how many rows each group has. Besides COUNT there are SUM (total), AVG (average), MIN and MAX. They are called aggregates: they fold many rows into one number.

Real PostgreSQL enforces a rule strictly: SELECT may contain only columns from GROUP BY and aggregates. Otherwise it is unclear which row of the group to show, and the database refuses. And if you need to put a condition on the counted result (“only categories with more than two services”), that is what HAVING is for, which works after grouping.

Changing and deleting

UPDATE items SET price = price + 50 WHERE id = 2;
DELETE FROM items WHERE id = 5;

Now pay attention: without WHERE both queries affect every row. DELETE FROM items; empties the whole table, and the database won’t ask twice. Rules that save you in practice:

• First write the same query as SELECT * FROM items WHERE id = 5 and make sure it finds exactly the row you mean. Then replace SELECT * with DELETE.
• Write the condition on the primary key (id), not on the name: a key never repeats.
• Before a risky change, wrap it in a transaction you can cancel (lesson 4).

In the sandbox the “UPDATE without WHERE” button shows what happens, and “Reset the table” puts everything back. In a real database there would be nothing to put it back with except a backup.

How not to open the door to an attacker

The best-known hole in sites with a database is called SQL injection. An example: a site looks up services by a word from the search box, and builds the query by gluing text together: the start of the query, then an opening single quote, then the visitor’s word, then a closing single quote. If the visitor types a word with a quote and then a command, the quote closes the query’s quotes, and the rest becomes part of the command. That is how data was stolen and tables deleted on thousands of sites.

There is one cure: parameterized queries. The command and the value are sent to the database separately (the command’s text holds a placeholder such as $1, and the visitor’s word is passed on its own), and the value can no longer become a command, whatever it contains. Every library for working with a database can do this. Never build a query out of visitors’ words: this rule has no exceptions.

What is real here and what isn’t

An honest caveat. The sandbox in this lesson (and the boxes in the practice below) is a teaching interpreter that understands the main commands. It was written for this course, lives in the page’s memory and disappears on reload. Real PostgreSQL can do incomparably more, and words its errors differently. If you want to try the real thing: PGlite is PostgreSQL compiled for the browser (the link is in the sources), and SQLBolt has interactive exercises with a dataset of its own. Everything you learn here (SELECT, WHERE, GROUP BY, UPDATE, DELETE) works there in the same way.

Practice

You’re in the era of 2010. Sites on PHP already keep their data in a MySQL database, and services and prices can be changed without editing HTML. Create a table of your project’s services and put questions to it.

The page has six boxes for queries. Fill them in order: create the table, add at least five rows (take the titles from your own services and invent the rest), filter and sort, count by category, change one record and delete another. Under each box the sandbox shows the result. In the last line, name the query that would be dangerous without WHERE.

What was wrong with 2010

In 2010 the most ordinary PHP code built a query by gluing: mysql_query("SELECT ... WHERE id = " . $_GET['id']). That is SQL injection, and it is why huge databases leaked over the decade. PHP’s mysql_* extension was deprecated in version 5.5 and removed in 7.0, and today working through parameters is the norm. The principle hasn’t changed: the command and the value are never mixed.