In the last chapter we created some records. In this chapter we are going to read records!
When asking querying a database, you always get a list back. It can be a list with no members in it which means nothing got returned or it can be a list with 1 or many members in it. In the rest of the CRUD operations ( which would be the RUD of CRUD), you can read, manipulate or eliminate records, more than one record at a time.
The first step for reading database records is kind of obvious - you have to tell it which records you want. In SQL this means you'd use the
Let's look at how phpMyAdmin creates the SQL to read records.
When I click on the apartment table in the left pane, we see a screen like this:
The SQL that phpMyAdmin created to view all the records in the apartment table is just below the neon green box.
SELECT * FROM `apartment`
The asterisk is a wildcard to select everything. And by everything, I mean both which columns to display and which rows to display. This command is going to show everything in the table!
Choosing a single record
To select one specific record, you'd use
SELECT on a field that contains unique content like the Primary Key.
Click the search tab at the top of the screen.
You'll see a screen like this:
Put in an
apt_id that you know don't have to see what search that returns nothing looks like, then try putting in an
apt_id that you do have.
Notice that the default operator for a column with the datatype
= . You can see the other operators for integers in the pulldown menu.
Check out the SQL that appears on the screen after you click the Go button.
Now let's try a search on the
apt_number is not the PK of the table, and it isn't an
Click on the pulldown menu the currently says
LIKE and select the
= operator. Then put in the value of one of your
apt_numbers for an apartment that you have already created, then click Go.
In the green box on the following screen you'll see SQL like this:
SELECT * FROM `apartment` WHERE `apt_number` = '2A'
Choosing multiple records with a wildcard
Now, let's go back to the
cat table example. In case you were wondering, I have five cats in my cat table.
LIKE allows you to make an SQL statement like this:
SELECT * FROM `cat` WHERE `name` LIKE 'Mand%'
The % sign is a wildcard used in LIKE statements. The % sign will match any number of character. The SQL statement above will return the cats named Mandy and Manheim.
This SQL will return all the cats that start with the letter M:
SELECT * FROM `cat` WHERE `name` LIKE 'M%'
This SQL will return all the cats with an 'a' in their name:
SELECT * FROM `cat` WHERE `name` LIKE '%a%'
This SQL will choose all the catID that are greater than 2:
SELECT * FROM `cat` where `cat_id` > 2;
This SQL will just return the name and the breed columns of the cat table for those cats whose cat_id is greater than 2:
SELECT `name`, `breed` FROM `cat` where `cat_id` > 2;
On the search page in phpMyAdmin (in the pulldown menu under = or LIKE), there is the list of all the operators that can be used in the SELECT statement.
Use the SQL window to try out these operators!
Click the word Options at the bottom of the screen.
Here you can select which columns you want to view when the rows are returned. You can also choose how you want the list to be ordered. Which brings us to the next topic:
Ordering a list
Look at either of the following SQL statements - ( they are the same except for the line breaks).
SELECT `catID`, `name`, `breed` FROM `cat` WHERE `catID` > 1 ORDER BY `catID` DESC;
SELECT `cat_id`, `name`, `breed` FROM `cat` WHERE `cat_id` > 1 ORDER BY `cat_id` DESC;
The new SQL keyword here is ORDER BY. Use it to choose which column you want to order the list by.
Furthermore, you can choose if you want the ordering to be in ascending values (a, b, e, j, r or 1, 2, 4, 6) or descending values with the DESC or ASC keyword.
The SQL above is ordering using the DESC keyword ( for descending). Ascending values are the default - so often it isn't specified but when it is, it looks like this:
SELECT `cat_id`, `name`, `breed` FROM `cat` WHERE `cat_id` > 1 ORDER BY `cat_id` ASC;
Guess what the SQL term is to limit the returned list to a certain number of records?
LIMIT , as in:
SELECT `cat_id`, `name`, `breed` FROM `cat` WHERE `cat_id` > 1 ORDER BY `cat_id` ASC LIMIT 2;
Play with the SQL tab to put in some SQL statement that work and that don't work. It's important to get used to spotting errors and to reading the error messages. The cool thing is that SQL is very readable.
And now on to the U of CRUD!