If you’ve ever worked with databases, you’ve probably used CRUD operations. CREATE, READ, UPDATE and DELETE are the four basic RDBMS programming operations. CRUD operations are used to manipulate, read, insert, delete, and edit table data.
SQL is prominent in most industries, so it is crucial for developers everywhere to understand how CRUD operations work. In this article, we will introduce you to CRUD operations with SQL.
In this article, we’ll look at the following:
Any organizations that tracks data (like accounts, payment information, or other records) need systems that provide persistent storage, which is usually organized into a database. A relational database consists of data organized by rows and columns. They can be connected to other tables using primary and foreign keys.
CRUD (create, read, update, delete) is an acronym that refers to the four functions we use to implement persistent storage applications and relational database applications, including the Oracle Database, Microsoft SQL Server, and MySQL.
The table below summarizes what each CRUD operation means.
For SQL, CRUD maps to insert, select, update, and delete, respectively. Operations such as security control, transaction control, access and permission, and performance optimization are all based on CRUD.
CRUD is constantly used for anything related to database and database design. Software developers can’t get anything done without CRUD operations. Website development, for example, uses REST (Representational State Transfer), which is a superset of CRUD used for HTTP resources.
On the other end, CRUD is just as important for end-users. Without it, things like registering for websites, creating blogs, or bookmarks would be impossible. Most applications we use let us add or create new entries, search for existing ones, make changes to them or delete them.
CRUD offers many benefits including:
Create allows you to add new rows to your table. You can do that using the command,
INSERT INTO. The command starts with the
INSERT INTO keyword, followed by the table name, column names, and the values to be inserted.
INSERT INTO, you have two options:
INSERT INTO table_name VALUES (value1, value2, value3, ...);
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
In the example below, we will add data to our bakery table.
INSERT INTO menu VALUES (1, 'croissant', 1, '2020-12-16'); If we want to add multiple rows, we do that using: INSERT INTO menu VALUES (2, 'bread', 3, '2020-12-16' ), (3, 'eclairs', 2, '2020-12-16' );
This will add new rows to the menu table, and each entry will have a unique
Learn SQL without scrubbing through videos or documentation. Educative’s text-based courses are easy to skim and feature live coding environments, making learning quick and efficient.
The read function is similar to a search function, as it allows you to retrieve specific records and read their values. Read refers to
For example, let’s take a look at the items we sell at our bakery. To do that, we have to display all the data in our menu table using:
SELECT * FROM menu;
This will not make any changes to the menu table but will simply display all the records in that table.
Take a look at this example to see how
SELECT retrieves desired data:
IF OBJECT_ID('cusp_CustomerRead') IS NOT NULL BEGIN DROP PROC cusp_CustomerRead END GO CREATE PROC cusp_CustomerRead @CustomerID int AS BEGIN SELECT CustomerID, FirstName, LastName, Email, PhoneNumber FROM Customer WHERE (CustomerID = @CustomerID) END GO
Update is how we change an existing record in the table. We can use this to modify existing records that exist in the database. When performing
UPDATE, you need to define the target table and columns to be updated. You also need the associated values and sometimes the rows.
It is recommended to limit the number of rows, as this helps to avoid concurrency issues.
To update an existing record, use the following:
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
Let’s say we want to update the item name and price. We would use:
UPDATE menu SET item_name = 'chocolate croissant', price = 2.5 WHERE item_id = 1;
This will update the table so that the previous record with
id 1 will now be replaced with chocolate croissant with
Delete is used to remove a record from the table. SQL and has a built-in delete function for deleting one or more records from the database at a time. Some relational database applications may permit a hard delete (permanent delete) or soft delete (update row status).
The delete command is as follows:
DELETE FROM table_name WHERE condition;
If we want to remove one item from the table, we use:
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;
In this article, we went over what CRUD is and how it’s used in SQL. These operations will be essential to your SQL career. You’ll be using CRUD in all sorts of applications, databases, and general programming tasks.
If you want to learn more about SQL, you can start with the following topics:
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 be covering everything from creating and updating databases to joins, nested queries, stored procedures, triggers, all in a hands-on environment.
Join a community of more than 1.4 million readers. A free, bi-monthly email with a roundup of Educative's top articles and coding tips.