A collection of related data is called a database. Database Management System is the most popular way to handle these databases. A relational database management system is used to store data in the form of tables. SQL (Structured Query Language) is the language through which the users can interact with the database systems.
In this post, we will focus on the difference between DDL and DML.
DML vs. DDL
SQL commands are used to perform operations on the database such as create, insert, delete, or drop data tables. These commands are categorized into different categories. They are DDL, DQL, DCL, and DML.
Below is a comparison chart to help to understand the difference between DDL and DML.
| Basis of comparison | DDL | DML |
|---|---|---|
| Basic function | This language is used for describing the structure of a database. | This language is used for modifying and fetching data from a database. |
| Categorization | The DDL commands cannot be classified further. | The DML commands can be divided into Procedural DML and Declarative DML. |
| Commonly used commands | The commands under DDL are Create, Drop, Truncate, Alter, and Rename. | The commands under DDL are Select, Update, Delete, and Insert. |
| Rollback | The statements cannot be rolled back. | Rollback is possible for the DML statements. |
| Where clause | There is no such use of the Where clause. | The Where clause is used for specifying conditions while manipulating data in a database. |
| Effect | These commands affect the complete database. | These commands will affect only one or more records in the table. |
Types of SQL Commands
Developed by IBM way back in 1970, SQL is the standard programming language for handling databases. It has different types of commands to handle database queries. The major types are:
DDL
DDL stands for Data Definition Language. The various commands in the language are used for defining the structure or schema of a database. The SQL commands that fall under DDL are:
- Create
- Alter
- Drop
- Comment
- Truncate
- Rename
DML
DML stands for Data Manipulation Language. The commands in this language are used for modifying or altering the contents of the database. The SQL commands within DML are as follows:
- Insert
- Delete
- Select
- Update
TCL (Transaction Control Language)
The commands that come under this language can only use the DML commands for its operations. You can manage the transactions of the database using these commands. Additionally, you can group Query statements into logical statements. The commands that fall under this category are:
- Commit
- Rollback
- Savepoint
DCL (Data Control Language)
This language provides commands that you can use to control the data access within a database. These commands are used to offer or take back privileges from other users of the database:
- Grant
- Revoke
DQL (Data Query Language)
Data queries can be performed within the schema objects using DQL. These commands used for pulling data from databases. The fetching of data and its corresponding results will depend upon the conditions provided by the user. The only command that comes under DQL is:
- Select
The following sections will help you get a clear idea about the difference between DDL and DML.
What is DDL?
DDL is used to develop the structure of the database objects and the database itself. The changes made by the commands are saved permanently within the database. This is because the commands are all auto committed. The database objects DDL handles include views, tables, schemas, and indexes.
They are considered a subset of SQL. The commands under DDL are as follows:
Create
This command is used to create a new table within the database. It also creates objects within the database. The syntax for this command is:
CREATE TABLE <tablename>
( column_name 1 data type,
column_name 2 data type,
.
.
.
column_name 10 data type
);
Drop
You can use this command to remove or drop an entire table or database. The command destroys the structure of the table and the data within it. The syntax is as follows:
DROP TABLE < name_of_table>;
OR
DROP DATABASE <name_of_database>;
Truncate
This command is used for deleting all the records from a table. The spaces assigned for the records are also removed. But, this command does not destroy the structure of the database. If this command is used, the primary key of the table is initialized. The syntax is:
TRUNCATE table_name;
Rename
The Rename command is used for renaming the name of an existing table. The syntax is as follows:
RENAME old_name to new_name;
Comment
Comments can be written in SQL in two ways:
- Single line comment – These comments start and end within the same line. The syntax is:
--this is a single line comment
- Multi-line comment – These comments can occupy multiple lines. The syntax is:
/* This is a multi-line comment
about a Select statement*/
Alter
This command is used for altering the structure of the table within a database. This can be used to add, delete, or modify the columns of a table. The syntax is:
1.ALTER TABLE <table_name>
ADD <column_name datatype>;
2.ALTER TABLE <table_name>
DROP COLUMN <column_name datatype>;
3.ALTER TABLE <table_name>
MODIFY <column_name column_type>;
What is DML?
DML is used for manipulating and managing the data within a database. It allows you to select, delete, modify, and insert information. The commands in DML are not auto committed. So, the modifications made within the database are not saved permanently.
Therefore, performing rollback operations is possible.
There are two types of DML - Procedural DML and Declarative DML. In Procedural DML, the process of retrieving data is defined. But in the case of Declarative DML, which particular data needs to be obtained, is described.
It is easier for any user to use Declarative DML as they can only specify what type of data they require.
Different type of DML commands
Select
This command is used for fetching some specific data from the database. A condition can also be mentioned for retrieving data from the database. The result may be in the form of one or more tables.
The syntax is:
SELECT * FROM employee
where Salary > 50,000;
Here, * is the universal selector that indicates all rows and columns matching the criteria has to be selected.
The command has some option clauses. They are Where, Order By, Group By, As, and Having.
Insert
You can use this command to add some data to an existing table. The syntax is as follows:
INSERT INTO <table_name> (`column_name A` <datatype>, `column_name B` <datatype> <database_name>)
VALUES (`value 1`, `value 2`);
Update
The Update command is used for updating and modifying the current records in a table. Using this command, you can alter the data for one or more records. The syntax for the command is:
UPDATE Employee
SET salary = 78,000
WHERE emp_name='Bruce';
Here, Employee is the table name, and salary is the name of the column. The condition is specified using the emp_name.
Delete
The Delete command is used for deleting specific records from a table. This removal will be based on a certain condition. The command can delete all records matching the specified condition. The syntax is as follows:
DELETE FROM Students
WHERE Stud_ID = '001';
If we do not provide the Where condition, all the records in the table will be deleted.
Conclusion
By now, you would have wrapped your head around the difference between DDL and DML. When handling an extensive database consisting of multiple records, you need to use them properly. While filtering data from a table, make sure you have mentioned the condition properly in the Where clause. If you do not specify a condition, the entire table will be deleted.
For making any structural modifications, the commands mentioned under DDL will come in handy.