Create Table in SQL
DML and DDL are two types of commands used in MySQL. The create and truncate commands are DDL commands that work on the structure of the table, and the delete command is a DML command that is used to manipulate the records.
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.
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.
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.
Rename the Table
To rename the table, we can use alter table command. Let's understand the syntax to rename the table.
Deleting the Column from The Existing Table.
The syntax for deleting a particular column from the table is given below.
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.
The above syntax is used to delete all the records without any changes in the structure of the table.
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.
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.
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.
The above syntax of the truncate table in SQL is used to delete the records from the table.
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
|Truncate table||Delete command|
|Truncate command is a DDL command.||Delete command is a DML command.|
|The where clause cannot use in the truncate command||The where clause can be used in the Delete command.|
|Truncate command cannot delete any particular records.||Delete command can delete specified records of the table.|
|Truncate command is faster than the delete command||Delete command is slower than the Truncate command.|
- CREATE TABLE command is used to create the structure of the table.
- TRUNCATE command is slower than the delete command.
- We can rename the table name using the ALTER TABLE command.