Data Definition Language (DDL)

DDL (Data Definition Language) is used to define the data structure in database. It defines the database columns.

Create database

CREATE DATABASE testdatabase;

Creates a database if the user has sufficient privileges.

Conditional creation:

CREATE DATABASE testdatabase IF NOT EXISTS;

Useful in longer SQL scripts to avoid errors if the database already exists.

Drop database

Dropping a database means deleting it and all data in it.

DROP DATABASE testdatabase;

Conditional drop:

DROP DATABASE IF EXISTS testdatabase;

Without IF EXISTS, an error is raised if the database does not exist.

WARNING

There is no undo for this operation, unless you have a backup of the data

Create table

Creates a new table and defines the columns

CREATE TABLE test (
  column1 INT,
  column2 VARCHAR(20),
  column3 TEXT
);

Syntax:

CREATE TABLE table_name (
  column_name data_type,
  ...
);

Copy tables

Copy structure only:

CREATE TABLE newtable LIKE oldtable;

Copy structure and data:

CREATE TABLE newtable AS SELECT * FROM oldtable;

Notes:

• AS is optional in MySQL
• Columns are created from the SELECT result
• You can copy only part of the structure/data (use where, limit etc.)

Alter table

ALTER TABLE modifies an existing table structure.

Examples:

ALTER TABLE testtable ADD COLUMN c5 INT AFTER c4;

• AFTER is optional
• FIRST can be used to add a column as the first column

ALTER TABLE testtable DROP COLUMN c5;

Modify existing column

ALTER TABLE testtable MODIFY c4 VARCHAR(10);

Drop table

DROP TABLE testtable;

Exercises

1. Create a database using CREATE DATABASE.
2. Create a table with at least three columns.
3. Copy the table structure using CREATE TABLE ... LIKE.
4. Copy structure and data using CREATE TABLE ... AS SELECT.
5. Modify the table:
   • Add a column
   • Change a column type (‘MODIFY’)
   • Drop a column
6. Drop the table and database.