DDL vs DML
What is DDL?
Data Definition Language helps you to define the database structure or schema. DDL commands help you to create the structure of the database and the other database objects. Its commands are auto-committed so, the changes are saved in the database permanently. The full form of DDL is Data Definition Language.
What is DML?
DML commands it to allow you to manage the data stored in the database, although DML commands are not auto-committed. Moreover, they are not permanent. So, It is possible to roll back the operation. The full form of DML is Data Manipulation Language.
Difference Between DDL and DML in DBMS
Here is the main difference between DDL and DML Command in DBMS:
Why DDL?
Here, are reasons for using DDL method:
Allows you to store shared data Data independence improved integrity Allows multiple users Improved security efficient data access
Why DML?
Here, benefits/ pros of DML:
The DML statements allow you to modify the data stored in a database. Users can specify what data is needed. DML offers many different flavors and capabilities between database vendors. It offers an efficient human interaction with the system.
Commands for DDL
Five types of DDL commands are:
CREATE
CREATE statements is used to define the database structure schema: Syntax: For example:
DROP
Drops commands remove tables and databases from RDBMS. Syntax: For example:
ALTER
Alters command allows you to alter the structure of the database. Syntax: To add a new column in the table To modify an existing column in the table: For example:
TRUNCATE:
This command used to delete all the rows from the table and free the space containing the table. Syntax: Example:
Commands for DML
Here are some important DML commands:
INSERT UPDATE DELETE
INSERT:
This is a statement that is a SQL query. This command is used to insert data into the row of a table. Syntax: For example:
UPDATE:
This command is used to update or modify the value of a column in the table. Syntax: For example:
DELETE:
This command is used to remove one or more rows from a table. Syntax: For example:
DDL Command Example
CREATE
Syntax: Here,
The parameter tableName denotes the name of the table that you are going to create. The parameters column_1, column_2… denote the columns to be added to the table. A column should be specified as either NULL or NOT NULL. If you don’t specify, SQL Server will take NULL as the default
Example:
ALTER
Syntax: Example:
DROP
Syntax: The parameter tableName is the name of the table that is to be deleted. Example:
DML Command Example
INSERT
In PL/SQL, we can insert the data into any table using the SQL command INSERT INTO. This command will take the table name, table column, and column values as the input and insert the value in the base table. The INSERT command can also take the values directly from another table using ‘SELECT’ statement rather than giving the values for each column. Through ‘SELECT’ statement, we can insert as many rows as the base table contains. Syntax: The above syntax shows the INSERT INTO command. The table name and values are mandatory fields, whereas column names are not mandatory if the insert statements have values for all the columns of the table. The keyword ‘VALUES’ is mandatory if the values are given separately, as shown above. Syntax: The above syntax shows the INSERT INTO command that takes the values directly from the <table_name2> using the SELECT command. The keyword ‘VALUES’ should not be present in this case, as the values are not given separately.
DELETE
Below is the Syntax to delete table Syntax: The parameter TableName is the name of the table that is to be deleted. Example:
SELECT
To view data in SQL Server, we use the SELECT statement. Syntax: Example:
