Creating and Modifying Tables (DDL)

Ever tried building something without a blueprint? It's tough. In SQL, the blueprint is your table structure. Today, we'll learn how to create and modify tables using Data Definition Language (DDL). Let's get started!

Table of Contents

1. CREATE TABLE Statement
2. ALTER TABLE Statement
3. DROP TABLE Statement

CREATE TABLE Statement

To store data, you need tables. Creating a table is like setting up a new folder to organize your files.

Basic Syntax

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    ...
);

Example: Let's create a simple Users table.

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    SignupDate DATE DEFAULT CURRENT_DATE
);

Here, we've defined a table with:

• A primary key UserID.
• A Username that can't be null.
• An Email that must be unique.
• A SignupDate with a default value.

ALTER TABLE Statement

Need to tweak your table? Maybe add a new column or change a data type? The ALTER TABLE statement is your friend.

Adding a Column

ALTER TABLE table_name
ADD column_name datatype;

Example: Add a LastLogin column to the Users table.

ALTER TABLE Users
ADD LastLogin DATETIME;

Modifying a Column

Want to change the data type or constraints of a column?

ALTER TABLE table_name
MODIFY column_name new_datatype;

Example: Change the Username column to allow up to 100 characters.

ALTER TABLE Users
MODIFY Username VARCHAR(100) NOT NULL;

Dropping a Column

No longer need a column? You can remove it.

ALTER TABLE table_name
DROP COLUMN column_name;

Example: Remove the LastLogin column.

ALTER TABLE Users
DROP COLUMN LastLogin;

DROP TABLE Statement

Sometimes, you need to delete an entire table. Maybe it's obsolete or was created by mistake. The DROP TABLE statement lets you do that.

Syntax

DROP TABLE table_name;

Example: Delete the Users table.

DROP TABLE Users;

Warning: Dropping a table permanently deletes all its data. Be cautious!