Defining a database

DDL (Data Definition Language) statements define and change a database's structure.

Creating and choosing a database:

• CREATE DATABASE StudentAttendance; makes a new database.
• USE StudentAttendance; makes it the active database; SHOW TABLES; lists its tables.

Creating a table: CREATE TABLE STUDENT(RollNumber INT, SName VARCHAR(20), SDateofBirth DATE, GUID CHAR(12), PRIMARY KEY (RollNumber)); Each attribute has a name and data type, separated by commas, and constraints can be given in the statement.

Viewing a table's structure: DESCRIBE STUDENT; or its short form DESC STUDENT; shows each field with its type, whether NULL is allowed, its key and its default.

Changing a table with ALTER TABLE:

• ADD PRIMARY KEY(GUID); for a composite key, ADD PRIMARY KEY(AttendanceDate, RollNumber).
• ADD FOREIGN KEY(GUID) REFERENCES GUARDIAN(GUID): the referenced table must already exist, with that attribute as its primary key.
• ADD UNIQUE(GPhone) adds a UNIQUE constraint.
• ADD income INT adds a new column.
• MODIFY changes a column's data type (for example GAddress VARCHAR(30) to VARCHAR(40)), or adds NOT NULL or a DEFAULT. With MODIFY, the data type must be written again with the new constraint.
• DROP income removes a column; DROP PRIMARY KEY removes the primary key (for example to set a correct one).

Removing tables and databases: DROP TABLE tablename; and DROP DATABASE databasename; delete them permanently, with all their data. Use them with great care.

Example (new): for a sports club database: CREATE DATABASE Club; USE Club; CREATE TABLE MEMBER(MemberID INT, MName VARCHAR(25), JoinDate DATE, PRIMARY KEY(MemberID)); and later ALTER TABLE MEMBER ADD Sport VARCHAR(15); adds a column for each member's sport.