Create Table in SQL

DML Commands and DDL commands

DML stands for Data Manipulation Language, and DDL stands for Data Definition Language. The DDL commands are used to define or alter the structure of the table. Some common examples of DDL commands are create table command, alter table command, etc.

The DML commands are used to insert or manipulate the records of the particular table. Some common examples of DML commands are the update command, the delete command, etc. Refer to this article on Scaler Topics to understand the difference between the DDL and DML commands in the SQL.

Create Table in sql

The create table command is the DDL command for creating a table in a MySQL database. Let's understand how to create a table in SQL.

Syntax:
To create a table in SQL

Each column in a table must have a name, data type, and size (size defines the maximum length of data that a given column may carry).

Example: To create a table in SQL.

The first command is creating a table name as CUSTOMER_ID having a field ID of type int and length as 10. Similarly, the second command creates a table name as SALES with field ID of type int and size as 10.

In this way, we can create a table in the SQL database.

Alter Table

The Alter table command is used to alter the existing table structure. For example, with the help of the alter table, we can perform the following operation easily:

• Adding a new column to the existing table.
• Rename the table.
• Deleting the column from the existing table.

These are some common operations we can perform using the alter table command. Let's understand these operations using examples.

Adding a New column to the Existing table.

We can add a new column to the table using alter table command. Let's understand the syntax for adding a new column.

Syntax:

Rename the Table

To rename the table, we can use alter table command. Let's understand the syntax to rename the table.

Syntax:

Deleting the Column from The Existing Table.

The syntax for deleting a particular column from the table is given below.

Syntax:

Delete Table

We can delete the table in two ways:

• Delete all records of the table without removing the table structure.
• Delete all records along with the structure of the table.

Let's understand both ways and the commands used to perform these operations.

Delete All Records of the Table Without Removing the Table Structure.

The delete command is a DML command that is used to delete all the table records by preserving the table's structure. We can also use the WHERE clause to delete particular records of the table.

The following is the syntax to delete a table in SQL.

Syntax:

The above syntax is used to delete all the records without any changes in the structure of the table.

Example:

TableName: Student

Selecting the Table Student using the Select command

The above command will show all the records of the table. Now let's see how to delete a table in SQL using the delete command.

Deleting the table, student

DELETE FROM student;

Selecting the Student Table

We get an empty table after selecting the table student.

Delete All Records as well as the Structure of the table.

The DROP command is used to delete all records as well as the structure of the table.

Let's understand the syntax of how to delete a table in SQL using the drop command.

Syntax:

This syntax is used to delete all the records and the table's structure. This is the same as the delete command, but this will not preserve the structure of the table.

Truncate Table

The truncate table command is used to delete all the records from the table without deleting the structure of the table, but we cannot use the where clause in the truncate table command.

Let's understand the syntax of the truncate table in SQL.

Syntax:

The above syntax of the truncate table in SQL is used to delete the records from the table.

Example:

TableName: Student

Selecting the Table Student using SELECT command

SELECT * FROM student;

The above command will show all the records of the table. Now let's see what will happen if we truncate the Student table.

Truncating Student table:

After truncating the student table, the records will be deleted, but the structure will remain the same. We can check using the desc student command, which describes the structure of a table in SQL.

Truncate Table vs Delete