DDL stands for Data Defination Language.
These commands are used to change the structure of a database and database objects.For example, DDL commands can be used to add, remove, or modify tables with in a database.
The DDL commands are:
1. CREATE :
This command is used to create table in the relational database .
This can be done by specifying the names and datatypes of various columns.
CREATE TABLE TABLE_NAME ( column_name1 datatype1, column_name2 datatype2, column_name3 datatype3, column_name4 datatype4 );
The column_name in create table command will tell the name of the column and corresponding datatype will specify the datatype of that column.Here in this table the three column_names namely – Student_id is of type int ,Name is of type varchar and Marks is of type int.
CREATE TABLE Employee (Student_id INT, Name VARCHAR(100), Marks INT);
2. ALTER :
Alter command is used for altering the table in many forms like:
- Add a column
- Rename existing column
- Drop a column
- Modify the size of the column or change datatype of the column
- ADD using ALTER –
Syntax to add column :
ALTER TABLE table_name ADD( column_name datatype);
The above command will add a new column to the table.And the resulting table will have one more column like this:
ALTER TABLE Student ADD (Address VARCHAR(200));
Here this command will add a new column “Address” in the table Student of datatype varchar(200);
- RENAME using ALTER –
Syntax to rename column :
ALTER TABLE table_name RENAME old_column_name TO new_column_name;
The above command will rename the existing column to new column.
ALTER TABLE Employee RENAME Marks TO Age;
The command above will change the column_name from Marks to Age;
- DROP using ALTER –
Syntax to Drop a column :
ALTER TABLE table_name DROp (column_name);
The above command will delete the existing column.
ALTER TABLE Employee DROP (Age);
Here the column_name =”Age”, has been deleted by this command;
- MODIFY using ALTER –
Syntax to Modify a column :
ALTER TABLE Employee MODIFY (column_name datatype);
The above command will modify the existing column .
ALTER TABLE student MODIFY (name varchar(300));
The above command will modify the column_name “Name” by changing the size of that column.
3. TRUNCATE :
This command removes all the records from a table. But this command will not destroy the table’s structure.
TRUNCATE TABLE table_name
This will delete all the records from the table.For example the below command will remove all the records from table student.
TRUNCATE TABLE Student;
4. DROP :
This command completely removes the table from the database along with the destruction of the table structure.
DROP TABLE table_name
This will delete all the records as well as the structure of the table.
This is the main difference between TRUNCATE AND DROP.-TRUNCATE only removes the records whereas DROP completely destroys the table.
DROP TABLE Student;
This command will remove the table records as well as destroys the schema too.
This is all about the DDL commands.