DDL and DML in the database

In order to work with databases, We must know about the basic concept such as tables, databases, SQL, DBMS, Schema etc. We can determine entirely those like, our database is managed by Database management system(DBMS) via structured query language(SQL).

The SQL consists come commands like create, drop, insert, delete, update etc to complete the required tasks such as handling the database and manipulating data.

These SQL commands are categorized into four types.

1. Data definition language (DDL)
2. Data manipulation language (DML)
3. Data control language (DCL)
4. Transaction control language (TCL)

In this article, we are going to discuss DDL and DML. The below diagram shows the SQL commands type and command of each type.

https://www.javatpoint.com/types-of-sql-commands

Data Definition Language

DDL data definition language consists of a set of SQL commands that can be used to create, delete or modify the structure of a database not data of the database. The structure of the database(schema) means the objects of the database.

list of objects of a database:

1. tables
2. database
3. columns
4. index
5. view
6. triggers
7. stored procedures
8. functions
9. etc

List of DDL commands:

• CREATE: create command is used to create the database objects.

 CREATE DATABASE database_name;
 CREATE TABLE TableName (Column1 datatype, Column2 datatype, Column3 datatype);

• DROP: drop command is used to delete the objects of the database.

//delete a table from the datbase
DROP TABLE table_name;

• ALTER: alter command is used to alter/modify the structure of a database like adding new columns, removing a column, or changing the datatype of columns.

//adding a new coulmn in an existing table.
ALTER TABLE table_name ADD column_name datatype(size);

• TRUNCATE: truncate command is used to delete the data from a table. it means making an empty table and the structure of the database is still the same.

TRUNCATE TABLE table_name;

• COMMENT: comment command is used to put comments into the data dictionary
• RENAME: rename command is used to provide a new name for the existing database objects.

RENAME TABLE old_table_name TO new_table_name;

Q&A What is the difference between DROP and TRUNCATE?

• Drop command would delete the required object from the database Hence the structure of the database will be changed.
• The truncate command would delete the record of the table. Hence, the structure of the database remains the same and the particular table is there.

Data Manipulation Language

DML is a data manipulation language consisting of SQL commands that are used to have CRUD operation on the data present in a database. For example: insert /create data into a table, read data from a table, update the existing data from a table and delete data from a table

List of DML commands:

INSERT: insert command is used to insert data into a table

INSERT INTO table_name VALUES(column1_value, column2_value..);

SELECT: select command is used to read data

SELECT * FROM table-name WHERE conditions;

UPDATE: update command is used to update /change the existing data from a table

UPDATE table_name SET column_name = value WHERE conditions;

DELETE: delete all records from a table.

DELETE FROM table_name WHERE conditions;

Reference :

https://www.javatpoint.com/types-of-sql-commands