DDL - Data Definition Language
Data Definition Language is used to define the structure of a table/schema. Which means we can create and modify the structure of a database objects.
Note: It is used to define and manipulate the structure of the database but not its data.
DDL defines the following Commands
- CREATE
- DROP
- TRUNCATE
- ALTER
- RENAME
Let's understand each command and their uses one by one:
1. CREATE Command:
To create a TABLE which consists of rows and columns inside an RDBMS (Relational Database) we use CREATE Command.
Syntax:
CREATE TABLE <table-name>(
<column-one-name> datatype,
<column-two-name> datatype,
...
)
Example:
Creating an Employee table
CREATE TABLE Employee(
emp_id INT,
emp_name VARCHAR(20),
dept VARCHAR(20),
salary FLOAT
);
2. DROP
Now if you want to delete the table(s) or a database you have created earlier permanently, you can use DROP command.
Syntax:
For Table:
DROP TABLE table_name;
For Database:
DROP DATABASE database_name;
Example:
DROP TABLE Employee;
--this command will permanently delete the Employee table
Note: Table or Database deleted using DROP command cannot be rolled back.
3. TRUNCATE
In case, if you don't want to delete the whole table or database, you can use TRUNCATE command. Instead of deleting the whole table, it will delete all the data/tuples stored inside the table.
Syntax:
For Table:
TRUNCATE TABLE table_name;
Note: truncate works faster as compared to drop command because truncate only deletes data of the table and preserve the structure while drop deleted the whole structure.
Example of CREATE, TRUNCATE and DROP:
CREATE TABLE Employee(
emp_id INT,
emp_name VARCHAR(20),
dept VARCHAR(20),
salary FLOAT
);
--Insert values inside the table
INSERT INTO Employee VALUES (1, "Joey", "CSE", 23000.00);
INSERT INTO Employee VALUES (2, "Ross", "CE", 30000.00);
INSERT INTO Employee VALUES (3, "Rachel", "ME", 25000.00);
--run the following query to check whether data is stored inside the table or not
SELECT * FROM Employee;
--Now to delete the data from the table
TRUNCATE TABLE Employee;
--Now if you'll run the following command, you will receive an empty table
SELECT * FROM Employee;
--Instead of TRUNCATE Command use DROP and observe the select query. Now you'll see that table doesn't exists inside the database.
4. ALTER
ALTER command is used to modify the structure of the table i.e. using this command, you can delete, update or drop the column from the table.
a. To add column in an existing table use ADD with ALTER as
Syntax:
ALTER TABLE table_name
ADD (column-one datatype, column-two datatype, .....)
Example:
To add branch column inside Employee table
ALTER TABLE Employee
ADD (branch VARCHAR(20));
b. To modify the existing column in a table use MODIFY with ALTER as
Syntax:
ALTER TABLE table_name
MODIFY column-name column-type
Example:
To modfy type of branch column from varchar to integer inside Employee table
ALTER TABLE Employee
MODIFY branch INT;
c. To delete column from an existing table, use DROP with ALTER as
Syntax:
ALTER TABLE table_name
DROP COLUMN column_name;
Example:
To delete branch column inside Employee table
ALTER TABLE Employee
DROP COLUMN branch;
5. RENAME
This command is used to rename column or table name.
Syntax:
Rename Table:
ALTER TABLE table_name
RENAME TO new_table_name;
Rename Column:
ALTER TABLE table_name
RENAME COLUMN column_name TO new_column_name;
Comments
Post a Comment