CRUD operations explained: Create, read, update, and delete

What are CRUD operations?

Any organization that tracks data (such as user accounts, payment information, or other records) needs systems that provide persistent storage, which is usually organized as a database.

A relational database consists of data organized in tables where each table comprises rows and columns. A table can be connected to other tables using primary and foreign keys.

CRUD refers to the four operations we use to implement persistent storage applications like relational databases. Examples of relational databases include Oracle, Microsoft SQL Server, and MySQL.

Operations such as security control, transaction control, access, and permission, and performance optimization are all based on CRUD.

Why are CRUD operations so important?

You can’t interact with databases without using CRUD. CRUD is essential for anything requiring persistent storage. Without CRUD, web developers wouldn’t be able to use REST (Representational State Transfer), which is a superset of CRUD used to access HTTP resources. REST APIs are some of the most commonly used in web development.

Basically, software developers wouldn’t be able to complete any project requiring persistent storage without CRUD operations.

On the other end, CRUD is just as crucial for end-users as it is for application developers. The end-users access CRUD operations through an application’s interface. Without CRUD, actions like registering for websites, creating blogs, or using bookmarks would be impossible.

Benefits of CRUD

• Facilitates security control by satisfying the various access requirements
• Simplifies application design and makes it more scalable
• Better performance compared to ad-hoc SQL statements

CRUD with SQL

SQL is a standard language used to store, manipulate, and retrieve data from databases. Let’s go over a few different ways you can use CRUD SQL queries to create a database and table, insert data into tables, and perform basic operations on data within tables.

CREATE DATABASE db_name
Similarly, you can create a new table using the `CREATE TABLE` keyword.

CREATE TABLE table_name (col_1, col_2, ...)

INSERT INTO table_name
 VALUES (value1, value2, value3, ...);

INSERT INTO table_name (column1, column2, column3, ...)
 VALUES (value1, value2, value3, ...);

CREATE DATABASE bakery;

CREATE TABLE menu (id Int, item varchar(10), date DATE);

INSERT INTO menu
VALUES (1, 'croissant', '2020-12-16');

INSERT INTO menu
VALUES 
(2, 'bread', '2020-12-16' ),
(3, 'eclairs', '2020-12-16' );

READ operation

The READ function is similar to a search function, allowing you to retrieve specific records and read their values. In SQL, the read function uses the SELECT keyword.

For example, let’s look at menu items sold in a bakery.

To view the complete list of baked goods, you need to display all the data in your menu table using:

SELECT * FROM menu;

This will not change the menu table. Instead, it will display all of the records within that table.

Here’s an example of how the SELECT keyword can be used to retrieve more specific data:

 SELECT id, item, date
    FROM   menu  
    WHERE  id = '3'; 

In this SQL query, a menu item is selected using the unique ID number assigned to it. This query will display the complete row from the menu table, where the id is 3.

UPDATE table_name
 SET column1 = value1, column2 = value2, ...
 WHERE condition;

UPDATE menu
 SET item = 'chocolate croissant', date = 2022-12-16
 WHERE id = 2;

DELETE operation

The SQL DELETE operation can remove one or more records from the table of a database. Some relational database applications may permit a hard delete (permanent delete) or soft delete (update row status).

The syntax for the DELETE operation is as follows:

DELETE FROM table_name WHERE condition;

If you want to remove only one item from the table, you can specify which row to delete. You can also use a combination of conditions if multiple rows need deletion.

DELETE FROM menu WHERE item_name='bread';

This will remove the row with the item bread from the table. If you want to delete all the records from the table, you can use:

DELETE FROM menu;

Wrapping up and next steps

In this article, we went over what CRUD is and how it’s used in SQL. By understanding CRUD operations and APIs, developers can build powerful web applications that can store and retrieve data from databases quickly and easily. This knowledge is essential for building successful web applications today. It’s also a great way to bolster your understanding of computer programming and databases.

You’ll use CRUD in all sorts of applications, databases, and general programming tasks, so there’s a lot to learn, but don’t be intimidated!

If you want to learn more about SQL, you can start with the following topics:

• Basic SQL
• Joins
• Stored Procedures
• Triggers
• Programming languages like Python, PHP, Java, and JavaScript

To get started, check out Educative’s Introductory Guide to SQL. You’ll learn about all the basics of SQL and get hands-on with CRUD operations. You will cover everything from creating and updating databases to joins, nested queries, stored procedures, and triggers, all in a hands-on environment.

Happy learning!

Continue reading about SQL

• What are database schemas? 5 minute guide with examples
• What are SQL Joins? Types of SQL joins explained
• What is a Database Query? SQL and NoSQL queries explained