Defining databases

Data definition statements create and change the structure of a database.

• CREATE DATABASE name; creates a database.
• USE name; selects it for use.
• SHOW DATABASES; and SHOW TABLES; list what exists.
• CREATE TABLE name (attribute1 datatype constraint, attribute2 datatype constraint, ...); creates a table.
• DESCRIBE name; (or DESC) shows a table's structure: its columns, types and keys.

ALTER TABLE changes the structure of an existing table:

• ALTER TABLE name ADD PRIMARY KEY(column); For a composite key, list more than one column: ALTER TABLE ATTENDANCE ADD PRIMARY KEY(AttendanceDate, RollNumber);
• ALTER TABLE name ADD FOREIGN KEY(column) REFERENCES other_table(column);
• ALTER TABLE name ADD UNIQUE(column);
• ALTER TABLE name ADD column datatype; adds a new column.
• ALTER TABLE name MODIFY column new_datatype; changes a column's type.
• ALTER TABLE name DROP column; removes a column.

A foreign key can be added only if the referenced table already exists, and the column it refers to is that table's primary key.

DROP TABLE name; and DROP DATABASE name; remove a table or a whole database, with all its data, permanently. Use them with great care.