What are DML Commands and DDL Commands in SQL?

Direct Switch to Contents:

1. Learn SQL Command
   • DDL Commands?
   • DML Commands?
   • DQL Commands?
2. Create Command
3. DROP Command
4. ALTER Command
5. Use Command
6. DESC command
7. Insert into Command
8. Conclusion
9. FAQs

Learn DDL, DML, DCL & TCL commands in SQL

Before going to Learn the DML commands and DDL commands in SQL, let us have a classification of SQL commands.

So Here SQL Commands are divided or classified into five types. Some of The Most Important SQL Commands are like SELECT, CREATE, ALTER, INSERT INTO, etc. Therefore from above all, We will cover here only three DDL commands, DML commands, and DQL Commands.

DDL Commands?

DML stands for data definition language. It is used to define and modify the database structure of the table. In other word, commands which are used for designing the schema of the table are called DDL Commands.
DDL commands are Create, Alter, Drop, Truncate, Rename.
So here we will learn each commands one by one later.

DML Commands?

DML stands for Data Manipulation Language. It is used to act on the data in the fields of the table. In other words, It acts on rows of a table.
DML commands are Insert, Update, Delete.

DQL Commands?

DQL stands for Data Query Language. It is used to retrieve data from the table and show us as an output. All Select Statements Come under this.
DML command is Select.

Create Command:

This Create command is used to create a new table in the database. So just suppose we have an existing database and we need a new table in it then with this command we can have a new table in it.

Syntax of the command:

Create Table (column_1 Column_1_datatype, column_2 Column_2_datatype,….,);
So here we can see that column name and column data type is required while creating the table. So we can not create a table without having column fields.
This Create command is also used to create a new database in the database server.

Syntax: Create Database <Database_name>;
So here we do not need any details about the database while creating a new database just needs the name of the database.
Therefore, Now we are in a position to create a DATABASE and a TABLE in it.

So now let us create it. Open your MariaDB (MySQL ) by command Prompt or MySQL command-line client. Check the blog 2 where I Have explained how to open it.

So, from here we will start, Now I am creating a new DATABASE name Movies.
Syntax:> Create Database Movies;

Here we can see that It is successfully executed. 1 row affected. We have just created a database Name- Movies. But there is nothing in this database.
Before going to create the Tables in it, let us learn some basic functions and commands such as SHOW, USE, SELECT Databaes(), DESC.
In addition, the SHOW command is used to show the existing databases and tables in it in the database server.

Syntax> Show databases;
Syntax> Show Tables;

In conclusion, it is not case sensitive, either write SHOW databases or show databases, both are the same.

USE command

It is used to change the current Database or use that database further.
In the Above snap, we have 9 databases such as Bollywood, movies, school, etc. Now we are going to use the Bollywood database and will see the tables in it.
So here the syntax of USE> use Bollywood;

So, Here we can see that the Database changed the messaged show. It means now we are using the Bollywood database. And the second command shows an empty set which means there is nothing in the database Bollywood as a result, there are no tables in it.

We have a database Bollywood which is empty, so now let us create a table into it.
Let us name a table is Movies in the Bollywood database. Don’t get confused between table Movies and A database Movies. So, here we are going to create the table Movies in the Bollywood Database.
Syntax:> Create Table Movies
-> (
-> Movie_name varchar(20),
-> Movie_year int(2),
-> Actor varchar(10),
-> );

So, here we have created a Table in which three fields or columns are Movie name, Move year, and actor. So, now let us try show tables.

So, now we have created a table in the database Bollywood.
Let us go to the SECOND DDL command.

DROP Command:

Drop command is used to drop a table from the database and also can drop the column from the Table.
Syntax: Drop Table ;
So, now we’re going to drop a table “MOVIES” from the database Bollywood.

>Drop table movies;

So, Here we have dropped the table from the Bollywood database. Now again the database is empty.

To drop a column from the table we need to again create the table with some fields. But First, we need to understand Alter Command.

ALTER Command:

Alter command is used to alter or modify your table.
Any changes to the structure of the table’s column or field we need to alter commands.

Syntax: Alter Table drop ;
So, here now we simply learned to drop a column from the table. In addition, here giving below the basic command use of Alter.

• Alter > modify > {columns datatype, default values, not null,}
• Alter > add > {adding PK}
• Alter > drop > {drop PK, drop column,}
• Alter > Change > {Rename the column name,}

So, we will learn each in the next blog where we will address queries and subqueries.

So far we have learned how to create databases and tables in it also. Let’s have a Table for better understanding. For instance, Suppose we have a database. And also have table ‘Movies; in it, as shown below.

Movie Name	Movie Year	Actor Name	Actor age
Barfi	2012	Rabir Kapoor	37
Biwi no. 1	1999	Salman Khan	54
2 States	2014	Arjun Kapoor	34

So, now how can we insert data as all above in the table Movies?

DESC-command

Before going to insert data we need to need to understand the DESC command.
DESC command is a stand for Describe Command which is used to see the structure (schema) of the table that includes Fields name, its data type, and some more information.
Syntax: DESC <Table_name>;
So, now let us have a table “movies” in the database Bollywood.

Now just type:

>DESC movies;

So, here we can see that the fields and the details associated with it weather its data type, null values, keys, or default values. So we will go deep and discuss it later in the next blog.

Insert into Command:

Insert Into command is used to insert data (records) in the table.
We can insert data into the table in two different ways.

Syntax 1: Insert into ( ) Values(Column_name_1_value, Column_name_2_value);

Syntax 2: Insert into values (Column_name_1_value, Column_name_2_value);

It is looking too complex, Therefore let us take an example to understand and We will insert data in our table created previously.
For instance, We add values to fields.

Movie Name	Movie Year	Actor Name	Actor age
Barfi	2012	Rabir Kapoor	37
Biwi no. 1	1999	Salman Khan	54
2 States	2014	Arjun Kapoor	34

So now we need exactly the same table with data in our database table.
Going to insert the first-row record in our database table movies.

SYNTAX 1: Insert Into movies(Movie_name, Movie_year, Actor_name, Actor_age) values(‘Barfi’,2012,’Ranbir Kapoor’,37 );

SYNTAX 2: Insert Into movies values(‘Barfi’,2012,’Ranbir Kapoor’,37 );
So, here we have noticed that if the data type value is a string then we use this ‘’ and if not simply integer values.

One more important thing is here needs to focus on that while inserting values in syntax 1 its corresponding fields should be in the same order. Movie_name, Movie_year, Actor_name, Actor_age these sequences of the field must follow the same in values sequence Barfi’,2012,’Ranbir Kapoor’,37.

In syntax 2 we haven’t written the field name but just put the values in the same sequence corresponding to the table’s field as in a database.

Anyone of the above all syntax we can use to insert a record into the table.
In the same way, we will insert other records as well.

So That’s all for now and more command will discuss in the next blog where we will first understand what is query and subquery. Then we go on further commands.

Conclusion

Here, we learned about various commands in SQL, classified into five types such as DDL DML DCL TCL DQL.
SQL uses certain commands like create drop insert etc. to carry out the required tasks.

FAQs

Next Blog | What are the Queries and Subquery in MySQL Database?

Previous Blog | What is MySQL Datatype? Overview And Understanding