CRUD and SQL - the basics!
Tags: webdev, sql, beginners, programming
Published: 2024-01-08 20:27:50

**What the heck is CRUD?**

Well it is used as a polite swear word in the UK, but in the context of software development it stands for:

- Create
- Read
- Update
- Delete

It forms the basis of a lot of software.

Let’s learn the basics of CRUD in SQL:

## Introduction

![CRUD - what the heck is it? Graphic with 4 icons representing Create, Read, Update and Delete in a neon pink, yellow, red and green colour scheme and style.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bfm4f3fswu5kj1bvbhxc.jpg)

To make this easy to understand, let’s imagine we have a list of people and their email addresses. 

Let’s assume we already have a table in our Database called “contacts” that has 4 columns:

- ID (primary key, auto increment)
- firstName
- lastName
- Email

We also already have two records in that table:

| ID | firstName | lastName | email |
| --- | --- | --- | --- |
| 1 | Mary | Simmons | mary.s@hotmail.com | 
| 2 | Fred | Griffith | fredgriffith@protonmail.com |

Let’s see how we could add a record to our table (Create)

## C - Create

![Create (INSERT) - adding data to a table. Graphic with a SQL insert statement and a table of data with 3 records below in a neon pink, yellow, red and green colour scheme and style. The last row has a plus symbol by it.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/41cziyxj2qrs9m6wpssz.jpg)

To create a record in SQL we use the `INSERT` command.

Let’s assume we have been given the following information to add to our database:
- **First name:** John
- **Last name:** Smith
- **Email:** JohnSmith@googlemail.com

We can add that to our database table using the following SQL statement:

``` sql
INSERT INTO `contacts` (firstName, lastName, email)
VALUES (‘John’, ‘Smith’, ‘JohnSmith@googlemail.com’);
```

Let’s break this down:

** INSERT INTO** 
is our command that let’s us create a new row in our table

**`contacts`** 
is the table we want to insert into

**(firstName, lastName, email)**
are the columns that we want to add data to

**VALUES ('John', 'Smith', 'John....com');** 
is the data we want to add.

Notice that there are 3 columns in the first set of parentheses and those correspond to the values in our second set of parentheses.

We could also do:

```sql
INSERT INTO `contacts` (lastName, email, firstName)
VALUES (‘Smith’, ‘JohnSmith@googlemail.com’, ‘John’);
```

And this would also work as the order of the columns specified in the first part still match the data in the second part. (note that it is not a good practice to put columns in a different order to how they appear in the table, and this was just for demonstration purposes).

One thing to note, our table has 4 columns, but we only added 3 values, what happened to the ID?

Well typical (and in this instance) the ID on a table would have “auto increment” set.

This means that it will automatically add a value here that is 1 higher than the table’s current max index.

In fact, we can now Read the table data using SQL to check the result of our INSERT statement.

## R - READ

![Read (SELECT) - retrieving data in a table. Graphic with a SQL select statement and a table of data with 3 records below with a magnifying glass over them. Neon pink, yellow, red and green colour scheme and style. ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0nwy4be3qv4i136yi2ng.jpg)


Once a table has data in it we want to be able to retrieve it.

For that we use `SELECT` in SQL.

And in it’s simplest form a `SELECT` statement looks like this:

```sql
SELECT * FROM `contacts`;
```

The `*` symbol is shorthand for “every column”.

It is equivalent to doing:

``` sql
SELECT id, firstName, lastName, email FROM `contacts`;
```

Let’s check out our data.

| ID | firstName | lastName | email |
| --- | --- | --- | --- |
| 1 | Mary | Simmons | mary.s@hotmail.com | 
| 2 | Fred | Griffith | fredgriffith@protonmail.com |
| 3 | John | Smith | JohnSmith@googlemail.com |

If you remember we didn’t specify an ID when we added John’s record, but because of auto-increment we were automatically given an ID number for that row / record.

And that is it, we have successfully added a row to our table and retrieved the information!

But, what happens if someone changes their email address? How do we update a record?

## U - UPDATE

![UPDATE - Updating data in a table Graphic with a SQL update statement and a table of data with 3 records below with a pointer on the second row and two fields highlighted. Neon pink, yellow, red and green colour scheme and style. ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8kvntac6zahkjzolxkxs.jpg)


We just found out that Fred has a new email address, which is **freddy@gmail.com**, so we need to fix that! 

To change an existing record in a table we use the `UPDATE` command.

Now, before we can use this command effectively we also have to introduce a second Command: `WHERE`.

`WHERE` is used to set some criteria on our query. Think of it like a filter. 

In this instance we want to update the record with an ID of “2” as that is Fred’s record.

So our UPDATE statement would look like this: 

```sql
UPDATE `contacts`
SET email = “freddy@gmail.com”
WHERE id = 2;
```

**Let’s break that down:**

**UPDATE `contacts`** 
tells the database which table we want to update.

**SET field/column = “value”** 
tells the database that we want to set a certain column to a new value.

**WHERE** 
is our other new command that let’s us specify that we only want to update the column in the row with an ID of 2.

It is worth noting that if we found out that Fred would also prefer to be called “Freddy”, we could update both columns at the same time like this:

```sql
UPDATE 
  `contacts`
SET 
  firstName = “Freddy”,
  email = “freddy@gmail.com”
WHERE 
  id = 2;
```

And now that we have learned the basics of `WHERE` we can also check that the row has updated correctly:

```sql
SELECT 
  * 
FROM 
  `contacts`
WHERE
  id = 2;
```

And would you look at that:

| ID | firstName | lastName | email |
| --- | --- | --- | --- |
| 2 | Freddy | Griffith | freddy@gmail.com |

Everything is updated!

Now the final part of CRUD - deletion!

## D - DELETE

![DELETE - removing data from a table. Graphic with a SQL delete statement and a table of data with 3 records below. The second row has a cross by it and a strike through and is highlighted in red. Neon pink, yellow, red and green colour scheme and style. ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m6oljnma5w4ct5nq2ash.jpg)

Deleting can only happen at a row level (i.e. you cannot just delete a `firstName` for somebody…you would do that with an `UPDATE` and set the value to `null`).

Let’s assume that after a while Freddy decides that he does not want to be on our contacts list anymore and wants his data deleted. 

Let’s remove Freddy from the table.

Delete statements are really straight forward:

```sql
DELETE FROM `contacts` WHERE id = 2;
```

Let's break it down again:


**DELETE FROM `contacts`**
specifies the table we want to delete from

**WHERE id = 2**
specifies which row we want to delete.

And this time, that is all there is to it!

After running our `DELETE` command the table looks like this:

| ID | firstName | lastName | email |
| --- | --- | --- | --- |
| 1 | Mary | Simmons | mary.s@hotmail.com |
| 3 | John | Smith | JohnSmith@googlemail.com |

Notice how the ID numbers do not change. It is also worth noting that if we inserted a new row the ID of that row would be set to 4, it is always from the current index of the database (and if we deleted all the rows and added a new row it would still be an ID of 4!).

## Wrapping up.

CRUD forms the basis of a lot of software applications, and learning the basics like we did here can get you quite a long way!

We obviously didn’t cover a lot of things in SQL such as `JOIN`, `DISTINCT` etc. but hopefully this will give you a quick introduction to SQL and CRUD operations that is enough to build your first CRUD application or API.

Happy new year and even happier coding everyone! 💪🏼💗