DML Commands in SQL
FREE
Overview
Structured Query Language (SQL) commands are used to create and maintain databases. DML commands in SQL are used to change the data present in the database tables, views, etc. These commands deal with inserting data into the tables, updating the data according to the conditions, and removing the data from the tables.
DML commands manipulate the data being stored in the tables (the tables that are defined using Data Definition Language Commands(DDL) commands). CREATE, ALTER, DROP, and TRUNCATE are DDL commands that are used to create the table structures.
What are DML Commands in SQL?
Structured Query Language(SQL) is a standard language used to create, access, and manipulate databases. It is an integral part of Database Management Systems(DBMS). SQL uses various commands to manipulate the database, and these commands are divided into 5 parts as follows:
- DDL:
Data Definition Language(DDL) is used to define database schema. - DML:
Data Manipulation Language(DML) is used to manipulate data present in the database. - DCL:
Data Control Language(DCL) deals with access rights and data control on the data present in the database. - TCL:
Transaction Control Language(TCL) deals with the transactions happening in the database. - DQL:
Data Query Language(DQL) is used to retrieve data from the database using SQL queries.
Data Manipulation Language(DML), as the name suggests, this subset of SQL commands is used to manipulate the data present in the database. We can add, remove or change the data present in tables using DML commands INSERT, UPDATE, DELETE, and SELECT.
When a table is created, we need to add data to it, this data is added using the INSERT DML command. Another scenario where DML commands are used is when we need to update or delete existing data based on some conditions. In this case, DML commands like UPDATE, and DELETE can be used.
SELECT command in SQL is considered in both DML and DQL(Data Query Language). Strictly speaking, it's part of DQL, but it is mostly considered under DML. SELECT is used to retrieve/fetch data from tables. We can give conditions to filter out the data being displayed.
Types of DML Commands in SQL
The following are some properties of DML commands in SQL:
- DML is a language that is used to manipulate data stored in the database.
- These commands are used to manipulate table structures in a database by updating or deleting, or retrieving existing data or inserting new data into them.
- We can update specific data according to given conditions using DML commands. But while doing that, users need to take care that all the integrity constraints are being followed. Integrity constraints are rules set for the data being stored in the table.
- DML commands are not auto-committed, which means the changes done using these commands are not saved into the database automatically. Users need to commit them manually. Changes done using DML commands in SQL can be rolled back.
The DML commands in SQL are as follows:
- INSERT
- UPDATE
- DELETE
- SELECT
INSERT
INSERT is a DML command in SQL that is used to insert new records into database tables. We can insert data in all the columns of a table or some specific columns using the INSERT command. While inserting records, the user should check if there are any integrity constraints like PRIMARY KEY, UNIQUE, NOT NULL, etc. on the table and insert records accordingly.
Syntax:
Example 1: Let's consider there is an empty table Students which stores data of Students in a school. The structure of the Student's table is as shown below.
| Roll_no | Name | Age | Address | Date_Of_Birth |
Here, the Roll_no column has an integrity constraint PRIMARY KEY, which means Roll_no must be unique for every record.
We have to add records of students to the table using the INSERT DML command. Let's see how we can do it.
To display the display use the following query:
Code:
Output:
In the above code, we have inserted 5 new records in the Students table using the INSERT DML command. The output displays the table after inserting records into it.
Notes for INSERT in DML:
- The data type of the column should be kept in mind while inserting data into the table. A string should be enclosed in single quotes as ' '. A date should be enclosed in single quotes in the format YYYY/MM/DD.
- Data should follow all the INTEGRITY Constraints.
- A record in a table means a row in that table.
Example 2:
Now let's insert one more record using a different format of the INSERT command. When we want to insert a record that has data of all the columns of the table, in such a case, we don't need to specify columns. Directly values can be inserted using the command which by default considers all the columns.
Output:
Explanation: As you can see, in the above command, we have inserted records in all the columns of the table using the INSERT command without specifying the columns. The output section displays a new record inserted successfully.
Example 3: In some cases, we don't have data for all the columns. We can add such data using the INSERT command by specifying the columns in which data is to be inserted. The remaining columns have NULL values. For e.g., we have to insert a student record in the Students table, but we don't have the Address details of the student. Let's see how we can do it.
Output:
Explanation: In this scenario, we have inserted data in only 4 columns out of 5. The data has been inserted successfully, as displayed in the output section. The column whose data was not provided stores a NULL value by default.
UPDATE
UPDATE is a DML command in SQL used to update existing records in the table. We need to specify which records we want to update using the WHERE condition. WHERE is a clause in SQL used to filter data depending on the condition. The UPDATE command can update single or multiple records according to WHERE conditions.
Syntax:
Example:
Consider students who live in Pune and have moved to a new city and want to update their address to 'Nashik'. Let's achieve this using the UPDATE DML command.
The current data in the Students table is as follows:
Query:
Output:
Explanation: This UPDATE command states to update the address of the students to 'Nashik', whose address is currently 'Pune'. The output section displays the updated records in the Students table.
DELETE
DELETE is a DML command used to delete existing records from the table. This command is used to delete single or multiple records from the tables based on the condition given in the WHERE clause.
Syntax:
Example: Let's delete the record of a student with Roll_no 2 from the Students table using the DELETE DML command.
The current data in the Students table is as follows :
Query:
Output:
Explanation: We have deleted the record of a student whose Roll_no is 2 using the DELETE command. The output section displays the records left after executing this command on the Students table.
SELECT
SELECT is a DML command in SQL used to retrieve data from database tables. SELECT is one of the most important and most used commands in SQL. Although SELECT is most often considered as a DML command in SQL strictly, it is part of DQL as it is used to query the database. We can select the columns being displayed and apply conditions on the records being displayed using the SELECT DML command in SQL.
The SELECT command does not manipulate any data in the table It is used to see the existing records present in the tables. We can filter the records being displayed using the WHERE clause. Whenever we need to see/retrieve data from any table SELECT query is used.
Syntax:
Example 1:
Let's see all the records in all the columns of the Students table using SELECT. In this case, there is no need to specify column names, the * asterisk mark is used to specify all columns in SQL. Using * with SELECT, we can retrieve data in all the columns in any database table.
Query:
Output:
Explanation: In this example, the output of the query SELECT * FROM Students is all columns and the records stored in that table.
Example 2:
For e.g., a teacher wants to see only the name, roll_no, and address of the students. Let's see how we can retrieve just these three columns using the SELECT DML command.
Query
Output:
Explanation: In this example, we have retrieved the output to display only 3 columns from the Students table using the SELECT query.
Example 3:
Now, let's retrieve specific records from all the columns of the Students table using the SELECT query. For instance, we want to see records of the students who belong to 'Nashik' and 'Mumbai'.
Let's see how we can achieve it:
Query:
Output:
Explanation: In this example, data is retrieved from all the columns from the Students table, and the records are filtered using the WHERE clause. The IN condition is used to give a range of inputs to the WHERE clause. The command states that it displays data from columns in the Students table whose Address is 'Mumbai' or 'Nashik'. The output section displays the records retrieved using this query.
Conclusion
- Data Manipulation Language is a subset of SQL used to manipulate the existing data in the database.
- DML commands are not auto-committed. Users have to commit (i.e. save all the changes permanently in the database) them manually.
- INSERT, UPDATE, DELETE and SELECT are some of the common DDL commands.
- SELECT is both DML and DQL command.
- WHERE clause is used with the SELECT command to apply filter conditions on the records being retrieved.
- * with SELECT query means select all columns from the table.