Databases: Data definition language
Data Definition Language (DDL) is a way to adjust the structure of a database. You might have created databases in the past using a GUI such as Access or even MySQL. DDL allows you to create databases from pure code including the ability to:
- Create tables: CREATE TABLE
- Change the structure of a table: ALTER
- Delete tables: DROP
You need to know what they all do (as listed above), though you only need to know how to implement the CREATE TABLE command. Let's look at how we could have made the crooks table above:
CREATE TABLE crooks ( ID INTEGER PRIMARY KEY, NAME VARCHAR(16), GENDER VARCHAR(6), DOB DATE, TOWN VARCHAR(20), NUMSCARS INTEGER )
CREATE TABLE customer ( CustomerID INTEGER PRIMARY KEY, CustomerName VARCHAR(16), Address VARCHAR(30), Postcode VARCHAR(8), Email VARCHAR(20) )
An ALTER statement in SQL changes the properties of a table in a relational database without the need to access the table manually.
ALTER TABLE crooks ADD convictions INTEGER ALTER TABLE crooks DROP COLUMN convictions
Dropping a table is like dropping a nuclear bomb. It is irreversible and is frowned upon in modern society.
DROP TABLE crooks
By running this line of code, the table "crooks" will be removed from the database with no chance of it being recovered unless backups have been previously made.