DDL — Creating and Using a Database and Tables

Before storing any data, we must first create a database to hold our tables, then create the tables themselves. These jobs are done with DDL commands.

1. Creating a database — CREATE DATABASE:

CREATE DATABASE SchoolDB;

This makes a new, empty database called SchoolDB.

2. Selecting a database to work in — USE: A MySQL server can hold many databases, so we tell it which one we want to work with:

USE SchoolDB;

All following commands now apply to SchoolDB.

3. Creating a table — CREATE TABLE: This command defines a new table by listing its attributes and the data type of each, and can mark the primary key.

CREATE TABLE Student (
    RollNo INT PRIMARY KEY,
    Name VARCHAR(50),
    Class VARCHAR(10),
    Marks INT
);

This creates an empty Student table with four attributes, where RollNo is the primary key.

4. Viewing a table's structure — DESCRIBE: To check the columns and types of an existing table:

DESCRIBE Student;

(This may also be written DESC Student;.)

5. Changing a table's structure — ALTER TABLE: Once a table exists, ALTER TABLE can add, change or remove a column. For example, to add a new column Phone:

ALTER TABLE Student
ADD Phone VARCHAR(15);

To remove that column again:

ALTER TABLE Student
DROP COLUMN Phone;
``` …