CRUD Operations In SQL

INTRO

In MySql database, data is stored within tables and there are many tables as per requirement. Within a table, with any data, we can do basic four operations. Like we can insert new data into a table, read data from tables, update data in tables, and delete data from a table. These four basic operations are called CRUD operations in SQL. All four operations are covered in an easy-to-understand manner one by one.

Before knowing CRUD operations one must know how to create a database and in that database how to create a table. One must practice the example given below steps on the MySql workbench to get the concepts clearly.

1. Let us create a database for a school named VIDYAPEETH (query: CREATE DATABASE VIDYAPEETH;).Now a database is created for the school.

2. As in MySql there can be many databases present .so to keep the VIDYAPEETH database in focus for performing crud operations we implement the following(query: USE VIDYAPEETH;).Now VIDYAPEETH database is selected.

1. Now we have to create a table for keeping students' general information. (query:

   Now we will see all four basic operations :

   

2. We have created a database name Vidyapeeth and created a table named student with some columns named id, first name, last name, age, city, and number(phone) inside the Vidyapeeth database. We will use this database and table in the entire blog to understand crud operations.

   NOTE: SQL is a case-insensitive language.

   Here come the crud operations :

   CREATE OPERATION(used for inserting data )

   The very first thing is to create a database and afterward create a table. After creating a table, now we can insert data into tables. Data in the table is stored in form of columns and rows in MySql.The query for the insertion of data is as:

   Query:

   INSERT INTO TABLE_NAME(COLUMN_NAMES) VALUES(VALUES_OF_COLUMNS);

Example: for Inserting data in table students we can write as follows:

Now, data is inserted with four rows. Now here comes the Read Operation.

READ OPERATION(for reading data from the table)

If we have to read the whole data we have inserted in the table, then we use the following (query: SELECT * FROM STUDENTS;)

important: In crud operations WHERE clause is very important .it helps to make choices such as from which condition u have to read, update, delete, etc.

eg: like I wish to read all the data of students above 21, then we can write (query: SELECT * FROM STUDENTS WHERE AGE>21;)

UPDATE OPERATION (for updation of data)

Whenever it is required to update the data in the table, we use the update operation. The query is very simple (query: UPDATE TABLE_NAME SET(WHATEVER U WANT TO UPDATE) WHERE ID=?;)

NOTE: in the where clause always try to use the column which has the primary key .it is easy to use in this way as the primary key is unique in a table and easy to find.

Example: Suppose in the 2nd row we want to update the city name from "Ranchi" to "Bengaluru".The query is (query: UPDATE STUDENTS SET CITY="BENGALURU" WHERE ID=2;)

DELETE OPERATION(for deletion of data )

Delete command or query is used to delete all the rows or a specific row/record from the table.

1. To delete all the rows from the table use :

Query: DELETE FROM STUDENTS;(this query will delete all the rows from the table.)

1. to delete a particular record from the table we use the WHERE clause.

   Query: delete from students where id=2;(we are deleting 2nd row in our example)

NOTE: Once you get a command of these queries written in this blog, it will be a cakewalk to understand the rest of all the modifications done on queries to get the desired data.