Home Education What are DML Commands and DDL Commands in SQL?

What are DML Commands and DDL Commands in SQL?

Direct Switch to Contents:

  1. Learn SQL Command
  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.

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.

Mariadb (MySQL)

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

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;

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;

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.

Create & 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;

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 NameMovie YearActor NameActor age
Barfi 2012 Rabir Kapoor 37
Biwi no. 1 1999 Salman Khan 54
2 States 2014Arjun 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.

DESC command

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 NameMovie YearActor NameActor age
Barfi 2012 Rabir Kapoor 37
Biwi no. 1 1999 Salman Khan 54
2 States 2014Arjun 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 );

Insert Into movies

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.

insert into

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

1. What is the database language?

Database Languages is a SQL Commands that are categorized. A Database system provides appropriate languages and interfaces for each category of users to express database queries and updates. It is used to create and maintain a database on a computer.

2. Differentiate between Delete Command & Truncate Command ?

Delete is a DML Command whereas Truncate is a DDL Command. In Truncate Command, all the space will be delocated and it can not be rolled back after the query executed. In Delete Command it can be rolled back but Space dedication is not possible.

3. What is transaction control language?

It is a language used to manage transactions in the database. It actually manages the changes made to the data in a table by DML statements. TCL Commands are Commit: Save Work Done/Uptade
Rollback: Restore database to original since the last COMMIT

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

Previous Blog | What is MySQL Datatype? Overview And Understanding

RELATED ARTICLES

5 Best Laptops Under 60000 Suitable To Your Profession

Today we live in the era of the internet in which laptops and mobile phones play an important role. So if you...

5 Best Inverters to Buy This Year | How to Choose the Right Inverter

We all know inverters are useful for getting backup of electricity, but fewer people know about all features and types. If you...

Top Selling Best Budget Smartphones That You Can Buy This Year

Are you planning to buy a smartphone? Or Looking for a budget smartphones with the best features?  Almost everyone is stuck with...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

5 Best Laptops Under 60000 Suitable To Your Profession

Today we live in the era of the internet in which laptops and mobile phones play an important role. So if you...

5 Best Inverters to Buy This Year | How to Choose the Right Inverter

We all know inverters are useful for getting backup of electricity, but fewer people know about all features and types. If you...

Top Selling Best Budget Smartphones That You Can Buy This Year

Are you planning to buy a smartphone? Or Looking for a budget smartphones with the best features?  Almost everyone is stuck with...

Top 10 Network Marketing Companies in India | Future of MLM Business

Everybody knows about marketing that it is an essential part of a business. It is essential to promote our product to others...

Recent Comments