SQL's SELECT statement lets you query databases. It can do lots of things for you, like filter records, sort records in different ways, count records, and other things.
Getting started
Start with this one.
- SELECT * FROM wombats;
The case doesn't matter. This would work just as well:
- select * from wombats;
Some people like to CAPITALIZE words that are part of SQL, but it doesn't affect how programs run. It's up to you.
Click the SQL tab in one of your phpMyAdmins, and give the query a try.
Notice, you get back rows and columns. SELECTs always return rows and columns, that is, a table-like thing.
The * means "all columns." Since the query doesn't limit the rows, you get them all.
Sorting
Try this one:
- SELECT * FROM wombats ORDER BY name;
You can switch the sort order:
- SELECT * FROM wombats ORDER BY name DESC;
OK, the weight field has null values for records. If you sort by weight, will the records with null values be at the top of the list, the bottom, or omitted entirely?
Let's find out. Try this.
- SELECT * FROM wombats ORDER BY weight;
Now you know.
Choose your columns
We've used * to show all the columns. You can choose a subset, like this:
- SELECT wombat_id, name FROM wombats;
Try it.
Stats
You can get stats as well. Try this:
- SELECT COUNT(*) FROM wombats;
You'll get something like:
Earlier, I wrote: SELECTs always return rows and columns.
That's true. You get one row and one column, but you still get rows and columns.
When you access SELECT results in PHP, you'll use column names. Unfortunately, the column names from a COUNT or SUM query can be strange. It's best to tell MySQL what column names you want to use. Like this:
- SELECT COUNT(*) AS num_records FROM wombats;
AS lets you set the name of a column in SELECT's output, to make it more predictable. I got:
I always use AS when doing counts, totals, and such.
Another one:
- SELECT SUM(weight) AS total_weight FROM wombats;
Try it.
Filter with WHERE
The real power of SELECT is in the WHERE clause, which lets you select rows that meet conditions you give. For example:
- SELECT name, weight FROM wombats WHERE weight > 30;
Another one, but before you run it, try to predict: will it include the wombats with nulls for weights?
- SELECT name, weight FROM wombats WHERE weight < 30;
Ethan
It didn't include them.
Right. Null means the data is missing, so it can't be less than anything.
You can find those, records, though.
- SELECT name, weight FROM wombats WHERE weight IS NULL;
Try it.
Comparisons can use less than (<), less than or equal to (<=), greater than (>), greater than or equal to (>=), and not equal (!=).
You can use AND, OR, and NOT, as you expect. For example, if you want the middle-weight wombats:
- SELECT name, weight FROM wombats WHERE weight >= 28 AND weight <= 32;
WHERE with others
You can combine all of these clauses together. Especially, you can use WHERE with any of the others. For example:
- SELECT name, weight FROM wombats WHERE weight >= 28 AND weight <= 32 ORDER BY name;
Exercise
Wombat queries
Use your wombat database, with the records you downloaded.
You'll be given some questions to write SELECTs for. Make a webpage with the query number, question, SQL, a screenshot of the results. Put your name at the top. You can see an example of what it should look like.
- What are the ids and names of wombats with weights less than 25 kilos?
- What's the weight of wombats named Zora?
- What's the weight of wombats named either Zora, or Hawthorne?
- How many wombats weigh less than 30 kilos?
- What are the ids and names of wombats with ids less than 5, or weights more than 40 kilos?
- How many wombats have missing weight data?
- How many wombats have missing data for weights and comments?
- How many wombats with missing comments have ids less than 10?
Submit the URL of your page.
Up next
Let's do some selects in PHP.