In the last part of this class we went over how to set up a database. In this part, we are going to use one!
To use a database, we have to:
As an acronym, these operations for a database are make up CRUD!
To create, read, update, or destroy database records, you have to tell the database in a language that it can understand, using a syntax that it likes and using a style that we like!
The language that relational database speak or at least what they understand is SQL: Structured Query Language.
Here is what the SQL would look like to add a record to the cat table:
INSERT INTO `cat` (`cat_id`, `name`, `breed`, `has_claws` ) VALUES (NULL, 'Haldeman', 'Japanese Bobtail', '0');
SQL doesn't care about line returns or whitespace. Sometimes you'll see SQL like this:
INSERT INTO `cat` (`cat_id`, `name`, `breed`, `has_claws` ) VALUES (NULL, 'Haldeman', 'Japanese Bobtail', 0);
And by custom rather than by necessity, the SQL keywords are capitalized.
The SQL statement is pretty legible. We are inserting some information into the
cat table. The columns are listed that we are going to insert values in and then there is a list of those values.
Because SQL doesn't care about white space (or returns) I'm putting in lots of returns to break the statement into smaller chunks. This will help me spot any typos.
VARCHAR values are surrounded by single quotes.
Also, the backticks that surround the column titles and the name of the table are not necessary, but they can prevent certain types of attacks on your database.
Note that in the SQL statement above, I put
NULL for the new cat's
cat_id . That's because the cat table is set up to generate the
cat_id automatically with the
Here is what the SQL looks like to read a complete row in the cat table:
SELECT * FROM `cat` WHERE `cat_id` = 2;
This statement is grabbing all the columns for this row. The asterisk is the wild card that selects everything.
If you want to just get the cat's name, you'd specify which column you want returned:
SELECT `name` FROM `cat` WHERE `cat_id` = 2;
This SQL statement assumes that you know the
cat_id of this cat. If you don't, you could search on the cat's name with the
SELECT * FROM `cat` WHERE `name` LIKE 'Harold';
Here is what the SQL looks like to change the name of a cat.
UPDATE `cat` SET `name` = 'Lester Pickles' WHERE `cat_id` = '2';
Here's the SQL to delete a record from the cat table:
DELETE FROM `cat` WHERE `cat_id` = 3;
There you have the basics of SQL CRUD. Now let's play CRUD with phpMyAdmin and we'll see how it generates SQL!