Introduction

Let us learn what are DML Commands. Data is an integral part of the software that we are using today. What kind of data and from where the data is got would be the questions that arise as soon as you think about data and databases. Let’s take a simple example of entering details while creating an email id. The user interface asks the user to enter first name, last name, date of birth, etc. 

This data is stored in databases in the form of rows and columns. The databases are a collection of data that is organized and electronically stored. These data stored in databases in the form of rows and columns are complex to access, update, and manage. SQL has different commands to access, manage, and update the data in databases. DML is a computer programming language. 

DML Commands in SQL are used to manage data in the data tables.

The data is stored in the rows and columns of the tables. So, we need commands to create the tables, insert data to tables, and update the data along with deleting the data also. Data Definition Language or DDL aids in the creation of a database, users and involves many more functions. The DML commands full form is Data Manipulation Language commands. The DML commands assist in managing data within the database. Let’s take a look at what are the DML commands in SQL and the syntax of their usage.

  1. What are DML Commands?
  2. List of Commands
  3. Examples
  4. Syntax to insert data to the table
  5. Syntax to update data to the table
  6. Syntax to delete data from the table
  7. Difference between DDL and DML commands

1. What are DML Commands?

The dml commands in SQL are used to manage data in the rows and columns of the data tables. As the word manipulation defines the data in the tables are manipulated. The manipulation of the data happens with the list of dml commands in sql to update, delete, and insert, and merging.

2. List of Commands

The dml commands in SQL are:

  • SELECT – The select command is used to retrieve the data from the database.
  • INSERT– The insert command is used to enter fresh data into the database.
  • UPDATE– The update command is used to update the existing data within the database.
  • DELETE– The delete command is used to remove the existing data from the database.
  • MERGE– The merge command is used to merge the data of two or more tables within the database.

The above-mentioned dml queries in sql are used to manage data within the database. 

3. Examples

The dml commands in sql with examples for INSERT, SELECT, UPDATE, DELETE and MERGE commands.

Consider a database with a table name as TEACHERS. The table has two columns to fill the data of the teachers. The two columns are the teacher name and subject name. The table created is as below.

Teacher_NameSubject_Name

The rows and columns below the column names will hold the data. Below is the dml commands syntax and explanation to dml commands with syntax and examples for inserting and updating data into the database.

4. Syntax to insert data to the table

INSERT INTO <table_name> (column_1, column_2,column_3 …column_n ) VALUES (<value 1>, <value 2>, ….<value n>)

  • Here insert into table_name informs to add a new row to the data table.
  • Columns specified column_1, column_2 mention the columns to which the data needs to be updated.
  • Values (value1, value2,..) mention the values that need to be added to the row.

While specifying the data values to be inserted in the data table, the user should specify the kind of data type. The data types can be either string, numerical value, or a date.

The String data types should be enclosed within the single quotes.

The numerical data types can be directly entered without any quotes.

The date values need to be in single quotes in the ‘YYYY-MM-DD’ format

Now, let’s explain dml commands with example. 

While inserting data to the above TEACHERS table, the dml commands in mysql are as below.

INSERT INTO <TEACHERS>(‘Teacher_Name’,’ Subject_Name’,  ) VALUES( ‘Sudha’, ‘Physics’)

The structure of the values is as per the table definition. If the table definition has numerical value then the value needs to be a number, if the value is a string then it needs to be in alphabets.

Now, let’s take a look at dml commands in sql with examples to update and delete the data present within the table.

Below is the syntax of dml queries to update the data in the database.

5. Syntax to update data to the table

UPDATE <table name> SET <attributes> = <expression> WHERE <condition>

UPDATE TEACHERS SET Subject_Name = ‘Chemistry’ where Teachers_Name = ’Sudha’

6. Syntax to delete data from the table

DELETE FROM <table name> WHERE <condition>;

DELETE FROM TEACHERS WHERE Teacher_Name =’Sudha’;

The above delete statement will delete the rows where the teacher name is ‘Sudha’.

7. Difference between DDL and DML commands

DDL is a Data definition language and it defines the database. DML is a Data Manipulation language that manipulates the data within the database by inserting, updating, and deleting the records in the database.

NumberBased onDDLDML
1Full-FormData Definition LanguageData Manipulation Language
2Used ForDDL commands are used to create users, databases, tables, users, and schema.DML commands are used to insert, update, delete data from database tables.
3CommandsCREATE, DROP, ALTER, and RENAMEINSERT, UPDATE, DELETE, and MERGE

All dml commands in sql are commonly used by database administrators while working with data tables. The list of dml commands in sql aid while verifying the data. The above-mentioned syntax and dml commands with examples must have given a sneak peek into how data is stored. 

DML comes under DBMS that is a Database Management System. Along with DML commands, the management system provides controlled access to data.

Conclusion

Software is designed mainly to make life easy. The database is an integral part of the software.  Any user interface that collects data from the user is stored in a database. This database is used for further reference. We have all received customer calls if our accounts have not been active for some time. The customer care officials take the data from the database from the column ‘last active’ in the table and call the customers. 

It is also important to find the right place to learn and become proficient in all these skills and languages. Jigsaw Academy, recognized as one of the Top 10 Data Science Institutes in India, is the right place for you. Jigsaw Academy offers an Integrated Program In Business Analytics for enthusiasts in this field. The course runs for 10 months and is conducted live online. Learners are offered a joint certificate by the Indian Institute of Management, Indore, and Jigsaw Academy.

Also Read

SHARE
share

Are you ready to build your own career?