Data Definition Language (DDL) is used for creating and modifying database objects such as tables, indices, views, and users.
DDL Commands are used to define the structure and schema of the database.
All the commands of DDL are auto-committed, meaning they permanently save all the changes in the database.
1. CREATE - to create a new table or database.
2. ALTER - to alter (or modify) the structure of a table.
3. DROP - to delete a table from the database.
4. TRUNCATE - to delete all records from a table.
5. RENAME - to rename a table.
⇒ CREATE statement is used to create a database schema and to define the type and structure of the data to be stored in the database.
⇒ CREATE statement can be used for:
⇒ Creating a database.
⇒ Creating a table, etc.
Creating TABLE:statement is used to create a new table in a database. It specifies column names of the table, its data types (e.g., VARCHAR, INTEGER, DATE, etc.), and can also specify integrity constraints (e.g., PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE).
SQL Data Types
Each column in a database table is required to have a name and a data type. The data type is a guideline for SQL to understand what type of data is expected inside each column, and it also identifies how SQL will interact with the stored data.
Note: Data types might have different names in different databases. Even if the name is the same, the size and other details may differ! Always check the documentation!
SQL constraints are used to specify rules for the data in a table. Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.
⇒ Constraints can be column-level or table-level.
⇒ Column-level constraints apply to a column.
⇒ Table-level constraints apply to the whole table.
⇒ Constraints can be specified when a table is created with the CREATE TABLE statement, or we can use the ALTER TABLE statement to create constraints even after the table is created.
NOT NULL: Ensures that a column cannot have a NULL value.
DEFAULT: Provides a default value for a column when none is specified.
UNIQUE: Ensures that all values in a column are different.
PRIMARY KEY: A combination of NOT NULL and UNIQUE. Uniquely identifies each row in a table.
FOREIGN KEY: Uniquely identifies a row/record in another table.
CHECK: Ensures that all the values in a column satisfy certain conditions.
INDEX: Used to create and retrieve data from the database very quickly.
ALTER TABLE statement is used to modify the structure of an existing table. It is used to add, delete, modify, or rename columns in an existing table. It is also used to add and drop various constraints on an existing table.
a)ALTER TABLE - ADD COLUMN:For adding new columns to a table.
b)ALTER TABLE - DROP COLUMN:
For removing existing columns from a table.
c)ALTER TABLE - MODIFY COLUMN:
To modify existing columns in a table.
d)ALTER TABLE - RENAME COLUMN:
To rename an existing column in a table.
DROP TABLE statement completely removes a table from the database. This command will destroy the table structure and the data stored in it.
TRUNCATE TABLE statement is used to remove all rows (complete data) from a table. It is similar to the DELETE statement with no WHERE clause.
TRUNCATE TABLE vs DROP TABLE:
⇒ DROP TABLE command can also be used to delete a complete table, but it deletes the table structure too.
⇒ TRUNCATE TABLE doesn't delete the structure of the table.
RENAME TABLE statement is used to change the name of a table.
