EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL DML

MySQL DML

By Priya PedamkarPriya Pedamkar

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. Data manipulation follows operations like storing, modifying, retrieving, deleting, and updating data in a database.

It manages and modifies the data in the database.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

  • 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 calls a PL/SQL or any subprogram.
  • Explain plan: Interpretation of the data access path.
  • lock table: Concurrency Control

Example to Demonstrate DML Statements

Here are some examples:

1. INSERT

It inserts or adds 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 that is a DDL(Data definition language) statement. Here, in the examples below, 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 student’s table.

Below is the query for creating a 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 the insert statement:

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

Output:

MySQL DML-1.1

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

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

Output:

MySQL DML-1.2

We can also populate one table using another table with the help of a 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 is 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:

MySQL DML-1.3

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

Select a 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 data will be fetched, we can select logical operators like >,<,=, LIKE, NOT, etc.

Example to demonstrate select statement with where clause

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

Output:

MySQL DML-1.4

3. Update

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

Syntax of update statement:

Update<table_name> set <column_name>=value where <condition>;
  • Table_name: The table name 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:

MySQL DML-1.5

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

Output:

MySQL DML-1.6

4. Delete

A delete statement deletes table rows 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:

Output-1.7

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

Output:

Output-1.8

delete from students where student_name= 'divya';

Output:

Output-1.9

delete from students12 where course='Arch';

Output:

Output-1.10

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

Conclusion – MySQL DML

This article taught us about mysql data manipulation language (DML). We have also learned about all the statements that come under DML and what are all their purposes. We have explained every DML statement with the query. For a better understanding of the reader, I have explained the entire query with examples and provided screenshots of the output console. A proper syntax of all the statements is also there in the article.

Recommended Articles

We hope that this EDUCBA information on “MySQL DML” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. MySQL TRUNCATE()
  2. Working of MySQL REGEXP_REPLACE()
  3. MySQL Root Password
  4. MySQL Root
MICROSOFT POWER BI Training
48+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
CYBER SECURITY & ETHICAL HACKING Certification Course
89+ Hours of HD Videos
13 Courses
3 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
MICROSOFT AZURE
97+ Hours of HD Videos
15 Courses
12 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
KALI LINUX Certification Course
26+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
MYSQL Certification Course
 115+ Hours of HD Videos
18 Courses
3 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more