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

Dropping table deletes it

DROP TABLE testtable;

Example

Create table with columns for name, age and city.

CREATE TABLE people (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age INT UNSIGNED,
    city VARCHAR(255)
);

This sample table will be used in some of the examples. Add some data for the table

INSERT INTO people (name, age, city) VALUES
('John', 25, 'Helsinki'),
('Anna', NULL, 'Helsinki'),
('John', 42, 'Espoo'),
('Maria', 31, 'Tampere'),
('Anna', 28, 'Espoo'),
('John', NULL, 'Tampere'),
('Maria', NULL, 'Helsinki'),
('Anna', 35, 'Tampere'),
('John', 19, 'Espoo'),
('Maria', 44, 'Helsinki');

Auto increment columns do not need the values assigned in the insert. The id is set to be a auto increment column and it will get sequential value automatically.