MySQL DML

Introduction to MySQL DML

DML stands for Data Manipulation Language which basically deals with the modification of data in the database. DML statements include structured query statements like select, insert, update, delete, etc. The manipulation of data includes following operations like storing, modifying, retrieving, deleting, and updating data in a database.

It basically manages and modifies the data in the database.

• select: select keyword in mysql is used to retrieve data from a table.
• insert: insert or add data into a table.
• update: update the existing data in a table.
• delete: Delete all records from a database table.
• merge: It is a kind of insert or update operation.
• call: call statement is used to call a PL/SQL or any subprogram.
• explain plan: interpretation of the data access path.
• lock table: concurrency Control.

Example to Demonstrate DML Statements

Following are the examples are given below:

1. INSERT

It is used to insert or add new rows or records in the existing table.

Syntax:

Insert into <table_name>values(<value1>,<value2>,<value3>…….,<valuen>);

Where,

• table name: The name of the table In which the data needs to be inserted.
• values: values for each column of the table.

To insert values in the table we first need to create a table which is a DDL(Data definition language) statement. Here, in the below all the examples we have created a table named students. we will show a demonstration of the DML statement in this table only. So, let’s start with creating the students table.

Below is the query for creating statement:

create table students (roll_no int,student_name varchar(150),course varchar(150));

Once the table is created we can now insert values into it.

Below is the query for insert statement:

insert into students values(1,'ashish','java');
Insert into students values(2,’rahul’,’C++’);
select * from students;

Output:

We have inserted 2 rows in the table. To view the entire table with records we use select statement with an asterisk (*) Again, added a third row in the table

insert into students values(3,'divya','Arch');
select * from students;

Output:

We can also populate one table using another table with the help of select statement. The only condition is that the table must have the same sets of attributes.

Syntax to populate the table:

Insert into table_no_first [(column1, column 2…column n)] select column1, column 2…column n from table_no_two [where condition];

2. SELECT

It used to display the contents of the tables. It is also used to select data from the database. Below is the syntax to select specified columns and records from the table.

Select column1,column2,…..column n from table_table;

Where, column 1, column 2….column n are the attributes of the table

Example to demonstrate the above syntax:

select student_name from students;

Output:

Below is the query of selecting all records and columns from table:

Select statement with a where clause:

Syntax:

select column 1,column 2,….column n from table_name where [condition]

Where the condition is the specified condition on which basis data is to be fetched. In the condition we can specify logical operators like >,<,=,LIKE,NOT and etc.

Example to demonstrate select statement with where clause

select roll_no, student_name, course from students where roll_no=3;

Output:

3. Update

It is used to change the existing values of the column i.e, changing the name of the student or changing the course of any student.

Syntax of update statement:

Update<table_name> set <column_name>=value where <condition>;

• Table_name: The name of the table in which the value is to be changed.
• condition: condition to get the specified row

Below is the query of the update statement:

update students set roll_no=roll_no+10 where student_name='ashish';

Output:

update students12 set student_name='aman' where roll_no=2;
select * from students;

Output:

4. Delete

Delete statement is used to delete rows of the table based on the specified conditions.

Syntax:

delete from <table_name> where <condition>;

• table_name: Name of the table from which the data needs to be deleted.
• condition: Condition based on which the data is to be deleted.

select * from students;

Output:

delete from students where roll_no=11;
select * from students;

Output:

delete from students where student_name= 'divya';

Output:

delete from students12 where course='Arch';

Output:

The above example tells that, when delete command is performed on table students and wants to delete students_name=’aman’, then it deletes the entire details of ‘aman’ and gives the output of remaining students in the table. Here, one by one we have deleted all the rows of the table. In the end, the table is only left with the column’s name and its schema. To delete the schema we have to use a DROP statement which is a DDL statement.

Conclusion – MySQL DML

In this article, we have learned about mysql data manipulation language (DML). We have also learned about all the statements that come under DML and what are all its purposes. we have explained each and every DML statement with the query. The entire query is explained with the examples along with the screenshots of the output console for a better understanding of the reader. A proper syntax of all the statements is also there in the article.

Recommended Articles

This is a guide to MySQL DML. Here we also discuss the introduction and syntax along with an example to demonstrate the DML statements. You may also have a look at the following articles to learn more –

1. MySQL TRUNCATE()
2. MySQL REGEXP_REPLACE()
3. MySQL Root Password
4. MySQL Root