Selecting data

The next step is being able to extract data from your database - this uses the "SELECT" SQL command. Here is a basic example:

SELECT ID, LastName FROM usertable;

SELECT takes the form, "SELECT field1 , field2 , ... FROM table ;" as you can see above. You can select as many (or as few) fields as you like. To select all fields from a table, use "SELECT * FROM table ". As your skills progress with SQL, you can also do more complicated data queries like selecting multiple records from across different data sets, perform functions on data before its returned, and such.

At this point you know enough to select some data from the usertable table you created and added data to. Type these three queries in, pressing Enter between each one to see the results:

SELECT ID FROM usertable;
SELECT LastName, FirstName, Age FROM usertable;
SELECT * FROM usertable;

Here is what MySQL should show you for the output of the last query:

Each of the other queries should show the requested data, and will be different to each other. There are three key things to note here:

1. MySQL draws each field with its own header so you can see what's in there. This becomes very important later on, particularly when you start running functions on data.

2. MySQL shows all fields in the order you SELECTed them.

3. If you run SELECT *, MySQL selects all fields from the table, then shows them in the order they were created in the CREATE TABLE call.

SELECT is probably the most complex SQL statement of them all - we will be covering much more of its power later on. Right now, there is just one last thing you need to learn before you can continue: how to limit your result. SELECT has a number of "clauses" you can add to it that affect the data selected. One of them is "WHERE", and it allows you to force MySQL to return only rows that match certain criteria.

Our test entry in the database has an Age of 29, so what do you think this next query will do?

SELECT * FROM usertable WHERE Age > 30;

Naturally it will look through all of usertable, checking the Age values against 30, and only return rows where the Age field is greater than 30. If you try that on your database, you will find MySQL returns no rows as expected. Now try this:

SELECT * FROM usertable WHERE Age < 30;

This time Jack Black should be back in the rows returned, because his record matches the WHERE clause we specified. You can provide much more specific WHERE clauses, such as "WHERE ID = 1", or "WHERE FirstName= 'Jack' ". However, you should be aware that when you use "AND" with MySQL it will match both WHERE clauses, which isn't quite how English works. For example, if you say "show me all the bears and tigers" you probably mean "show me all animals that are either bears OR tigers" but MySQL would read that to mean "animals that are bears and are also tigers," which is clearly no animal - or, if it is, is a pretty darn terrifying animal!

The other popular clause is "LIMIT", which allows you to force MySQL to only return a certain number of results. LIMIT can be used in two ways: "LIMIT n " and "LIMIT m , n ". The first way allows you force MySQL to only return the first n rows that match your WHERE clause (if you have one), whereas the second option allows you to force MySQL to return the first n rows after the first m that match.

If we had hundred rows of matching data, here are three possible SELECT statements:

SELECT * FROM SomeTable LIMIT 1;
SELECT * FROM SomeTable LIMIT 10;
SELECT * FROM SomeTable LIMIT 20, 10;

The first one will return just the first row, the second one will return the first ten rows, and the last one will skip the first twenty rows, and return the next ten. Each type of LIMIT has its own use - the last option, for example, allows you to do search engine-style results, "See next ten matches".

Copyright ©2015 Paul Hudson. Follow me: @twostraws.