Introduction
What is a Database Definition Language?
This is the programming language used to construct and alter the design of tables contained within a database. The most common form of DDL is Structured Query Language(SQL).
This language is not used to directly alter the data stored within a table, rather it is used to construct the framework within which data is contained – this type of data that is used to define other data is known as metadata – data about data.
CREATE DATABASE
Creating a Database – CREATE DATABASE
The syntax to create a database in SQL is simple:
CREATE DATABASE databasename; e.g. CREATE DATABASE library.db
To do the same in SQLite then you would use the shell command
sqlite3 library.db <- if library.db already exists it will open the database, otherwise it will create it.
CREATE TABLE
Creating a Table – CREATE TABLE
Example 1 – Simple Non Relational Table
CREATE TABLE users(firstname varchar(255), lastname varchar(255), age integer, dateofbirth date);
Example 2 – Create a table with a primary key
CREATE TABLE Orders (OrderID integer NOT NULL PRIMARY KEY, OrderNumber integer NOT NULL, CustomerID integer FOREIGN KEY REFERENCES Customers(CustomerID));
SQL Data Types
- Char – Fixed Length Text
- Varchar(255) – Variable Length (For SQLite use – Text)
- Integer – Store a whole number
- Boolean – Stores 1 or 0 for Boolean
- Real – Floating point number
- Date / Time – (SQLite has no date or time data type so use Text / Float / Integer and use something like Epoch Time)
ALTER TABLE
Modifying a table – ALTER TABLE
If you want to alter the structure of a table (change column names / data types) then you can use the ALER table command.
Examples:
ALTER TABLE username ADD COLUMN height real;
ALTER TABLE username MODIFY COLUMN height integer
ALTER TABLE username DROP COLUMN email;
DROP TABLE
Deleting a table – DROP TABLE
Deleting a table is easily with the drop table command.
DROP TABLE users;
Warning – this will drop the table straight away without warning – A costly mistake that many an unwary junior developer has made..