• 20 hours
  • Medium

Free online content available in this course.

course.header.alt.is_video

course.header.alt.is_certifying

Got it!

Last updated on 11/25/19

Learn the CRUD operations

Log in or subscribe for free to enjoy all this course has to offer!

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:

  • Create data

  • Read data

  • Update data

  • Destroy data

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

SQL examples

Create

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.

The  text  or  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  AUTO_INCREMENT  function. 

Read

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  LIKE  keyword:

SELECT * FROM `cat` WHERE `name` LIKE 'Harold';

Update

Here is what the SQL looks like to change the name of a cat.

UPDATE `cat` SET `name` = 'Lester Pickles' WHERE `cat_id` = '2';

Destroy

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!

Example of certificate of achievement
Example of certificate of achievement