All things CRUD

CRUD stands for Create, Read, Update and Delete and these four functions make up the basis of persistent storage in the land of computers. By storing a state as data, we can ensure that this state will remain even if the process that created it does not. For example, I developed a web app at the weekend where you could play rock, paper, scissors, and I used a singleton class to store my game. As soon as I stop running the app though, that data vanishes. I can’t recall the game history. It’s gone and that’s not ok. There’s an obvious need to store information. This is where CRUD comes in.

What do I mean by CRUD?

There are four things we need to be able to do to our data that we want to store in our database or to data that is already sitting in our database.

  • Create new entries
  • Read existing entries
  • Update existing entries
  • Delete existing entries

What is SQL?

SQL, pronounced ‘sequel’, or Standard Query Language, is a programming language used to communicate with databases. As a result, it has functions which satisfy all CRUD elements.

This table nicely summarises the equivalent syntax in SQL and HTTP.

Function
SQL 
HTTP
Create
INSERT
PUT / POST
Read
SELECT
GET
Update
UPDATE
POST / PUT / PATCH
Delete
DELETE
DELETE
How to use SQL
Let’s say I run a veterinary surgery and this is my database, “patients”, of all registered pets and their owners:
PetID PetName PetType Owner Address
1 Jack Monkey H. Barbossa Black Pearl
2 Mr Bigglesworth Cat D. Evil Virtucon Worldwide HQ
3 Santa’s Little Helper Dog H. Simpson 742 Evergreen Terrace
Adding a new entry:
INSERT INTO "patients" ( PetName, PetType, Owner, Address ) 
VALUES ( 'Pascal', 'Chameleon', 'Rapunzel', 'Hidden Tower');

Now let’s read our updated table, using * to select all entries:

SELECT * FROM patients;
PetID PetName PetType Owner Address
1 Jack Monkey H. Barbossa Black Pearl
2 Mr Bigglesworth Cat D. Evil Virtucon Worldwide HQ
3 Santa’s Little Helper Dog H. Simpson 742 Evergreen Terrace
4 Pascal Chameleon Rapunzel Hidden Tower

Let’s update one of our entries:

UPDATE "patients" SET PetName='Snowball II', PetType='Cat' WHERE ID = 3;

This gives us:

PetID PetName PetType Owner Address
1 Jack Monkey H. Barbossa Black Pearl
2 Mr Bigglesworth Cat D. Evil Virtucon Worldwide HQ
3 Snowball II Cat H. Simpson 742 Evergreen Terrace
4 Pascal Chameleon Rapunzel Hidden Tower

And finally let’s delete an entry:

DELETE FROM "patients" WHERE PetID = 1;
Pet ID Pet Name Pet Type Owner Address
1 Mr Bigglesworth Cat D. Evil Virtucon Worldwide HQ
2 Santa’s Little Helper Dog H. Simpson 742 Evergreen Terrace
3 Pascal Chameleon Rapunzel Hidden Tower

And there we have it – a brief introduction to CRUD and SQL!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s