Basic SQL Select queries
In this video, I want to introduce the basic SQL syntax for Select statements. In all likelihood, 90% of your queries you’ll need for your dynamic website can be handled by knowing just a very few SQL statements.
The most basic Select statement is:
SELECT * FROM `Table`
This returns a result set that contains every column of every record in `Table`. Now, using PHP, you could slice, sort, and filter those results any which way you please. And there are some rare situations where that’s probably your best bet. But for 99% of all cases, you’re much better off, performance-wise, doing that sort of manipulation to your data directly in your SQL query.
For instance, suppose we only need a subset of the results in a table. There are 2 ways we can limit our results. The first way is with a WHERE clause. That might look like this:
SELECT * FROM `Table` WHERE `productID` = 15
In this case, only records with a produceID column value of 15 will be returned. And filters can be as simple as the one above or much more complex. In addition to the ‘=’ operator, you can use != to specify that a column should not match the expression, as well as ‘LIKE’ or NOT LIKE with text values to do the same thing. Your WHERE clause can even use regular expressions with the EREG operator. And you can combine as many different WHERE clauses as you like using AND, OR, and XOR, along with parenthesis. For now though, we’re keeping things simple.
Now, one other way we can limit our results is to use a LIMIT clause. A LIMIT clause will work with or without a WHERE clause, and it will simply return up to the number of results specified. Here’s one example:
SELECT * FROM `Table` LIMIT 10
This result set will contain only the first 10 records in Table. And combining it with the WHERE clause from before, we get:
SELECT * FROM `Table` WHERE `productID` = 15 LIMIT 10
Just so you’re aware, the order of these fields does matter. A LIMIT clause always comes at the end of a SELECT statement. In this case, we’ll get a result of just the first 10 records where productID is 15. And if there are less than 10 records that match, all of them get returned.
Now sometimes, in fact usually, we’ll use a LIMIT clause that lets us specify records other than just the first ones that match. For example:
SELECT * FROM `Table` WHERE `productID` = 15 LIMIT 20, 10
In this case, we get records 20-29, instead of 0-9. The first number tells MySQL which starting record to return, and the second number is how many records to return from that point.
However, sometimes we want to order our results by a specific column, and MySQL lets us do that too with the ORDER clause. The ORDER clause always comes near the end of a SELECT statement. Only the LIMIT clause can come past it. Here’s a simple example:
SELECT * FROM `Table` ORDER BY `name`
The result set will be all of the columns and all of the rows, ordered by the column `name`. Assuming `name` is a string, such as a VARCHAR, the results will be ordered alphabetically. If we want to have them ordered reverse alphabetically, we can instead use:
SELECT * FROM `Table` ORDER BY `name` DESC
And the ORDER BY clause can actually specify more than one column. For instance, suppose there was a match in more than one record. To determine the order of records that have the same value for the primary sorting column, you can specify a secondary sorting column. So, let’s say we want to order `Table` by `productID` first, and by `name` second. That just looks like this:
SELECT * FROM `Table` ORDER BY `productID`, `name`
And now let’s combine the ORDER BY clause with WHERE and LIMIT
SELECT * FROM `Table` WHERE `productID` = 15 ORDER BY `name` LIMIT 10, 5
What happens here is that MySQL pulls out all the records that have a productID value of 15, orders them alphabetically by their name columns, and then from that set, returns the 5 records 10-14.
Now, supposing you only wanted a few of the columns returned, and not every column in every record for your result set. Just replace the * with the names of the columns you actually want. For example:
SELECT `name`, `productID`, `date`, `supplierID` FROM `TABLE`
Now I’m back to getting every record in my table, but for each one I only get the columns for `name`, `productID`, `date`, and `supplierID`.
That’s it for part 1 of building SQL SELECT queries. In part 2 we’re going to look at what you do when you want to get data from multiple tables instead of just 1.
January 30 2010 07:05 pm | Schmategories