INSERT ,UPDATE and DELETE Query in MySQL

INSERT ,UPDATE and DELETE Query in MySQL | In this tutorial we will learn how to insert value in MySQL using insert query,update value in MySQL using update query,delete row in MySQL using delete query.

INSERT Query in MySQL

Insert query in mySql is very important staff for an administrator in an organization when a employee join an organization his or her record has to be maintained by an organization or a student got admission to school his or her has to be maintained in that place insert query is required.

Syntax for INSERT Query in MySQL

mysql> INSERT INTO tablename( column 1,column 2...column N) VALUES ("value 1" ,"value 2",...value N) ;

From the above syntax we can know few details.

1. INSERT INTO statement is used to insert records in the tables of the database.

2. In the Tablename field we use the tablename in which table records to be inserted.

3. Column 1,Column 2...Column N indicates here column name of the table.

4. Need to write VALUES statement to insert the values in the column of the table.

5."value 1" ,"value 2",...value N indicates the values according to the column name. If datatype of the column is varchar then we use "" sign and if datatype of the column is integer then we need not use "" sign.

To insert value into mysql table we use “insert into” command. First we need to login mysql server, then select mysql database and insert the value into the specific table using the below command.

mysql> INSERT INTO student (studentfirstname, studentlastname,studentage) VALUES ("Raktim", "Sharma",23);

Query OK, 1 row affected, 1 warning (0.00 sec)

We can not define student roll no because it will be incremented automatically.Now data has been inserted into mysql table named student.

INSERT Query in MySQL using MySQL workbench

We can also insert query using the MySQL workbench. First login the workbench using the credentials and then find the schemas or database and also the table in which data to be inserted. Then we need to go the query section and execute the insert query and after executing the query we got the output in the output section.

INSERT Query in MySQL using MySQL workbench

We can also check the output from MySQL promt from the server that data is inserted or not.

INSERT Query in MySQL

UPDATE Query in MySQL

Update query in MySQL is also very important for a database admin when an employee promoted then as per his designation record should be updated. In that place update query is required.

To update record into mysql table we use “update” command. Lets take an example to update data in the student table which we previously created.

Before update to view the table data using the below command.

mysql> select * from student;

+----------------+------------------+-----------------+------------+

+----------------+------------------+-----------------+------------+

| 0 | Raktim | Sharma | 23 |

| 1 | ashish | khan | 25 |

+----------------+------------------+-----------------+------------+

2 rows in set (0.00 sec)

Syntax for update Query in MySQL

mysql> UPDATE Tablename SET Columnname =Value WHERE [Condition];

From the above syntax we can know few details.

1. UPDATE statement is used to update record in the tables of the database.

2. In the Tablename field we use the tablename in which table records to be updated.

3. Column name indicates here column name of the table which column will be updated.

4. Then we use WHERE clause along with the condition to filter the data which row will be updated.

Now we are going to update the last name of the student whose roll no is 1. To do this we use the below command.

mysql> UPDATE student SET studentlastname = 'Ali' WHERE studentnrollno = 1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Here we used where clause to update record for a particular student.

After modified the date we need to view the data using the below command.

UPDATE Query in MySQL

UPDATE Query in MySQL using MySQL workbench

We can also update query using the MySQL workbench. First login the workbench using the credentials and then find the schemas or database and also the table in which data to be updated. Then we need to go the query section and execute the update query and after executing the query we got the output in the output section.

UPDATE Query in MySQL using MySQL workbench

DELETE Query in MySQL

Delete query is also very important for a database admin when an employee leave the organization or a student leave from school ,in that case delete query is required.

To delete record from the mysql table using delete query we use”DELETE FROM”command as below.

Lets take an example to delete the date which we previously created.

Before delete to view the table data using the below command.

+----------------+------------------+-----------------+------------+

+----------------+------------------+-----------------+------------+

| 0 | Raktim | Sharma | 23 |

| 1 | ashish | Ali | 25 |

+----------------+------------------+-----------------+------------+

2 rows in set (0.00 sec)

Syntax for Delete Query in MySQL

mysql> DELETE FROM Tablename WHERE [Condition];

From the above syntax we can know few details.

1. DELETE FROM statement is used to update record in the tables of the database.

2. In the Tablename field we use the tablename from which table records to be deleted.

3. Then we use WHERE clause along with the condition to filter the data which row will be deleted.

Now we are going to delete the data of the student whose roll no is 1. To do this we use the below command.

mysql> DELETE FROM student WHERE studentnrollno=1;

Query OK, 1 row affected (0.00 sec)

After deleted the date we need to view the data using the below command.

DELETE Query in MySQL

DELETE Query in MySQL using MySQL workbench

We can also delete query using the MySQL workbench. First login the workbench using the credentials and then find the schemas or database and also the table in which data to be deleted. Then we need to go the query section and execute the delete query and after executing the query we got the output in the output section.

DELETE Query in MySQL using MySQL workbench

That’s all.If this article is helpful to know about INSERT ,UPDATE and DELETE Query in MySQL please share and subscribe the article!!!!

INSERT ,UPDATE and DELETE Query in MySQL

INSERT Query in MySQL

Syntax for INSERT Query in MySQL

INSERT Query in MySQL using MySQL workbench