SQL DDL STRUCTURED QUERY LANGUAGE DATA DEFINITION LANGUAGE

SQL - DDL STRUCTURED QUERY LANGUAGE (DATA DEFINITION LANGUAGE)

Data Definition Language (SQL) A data definition language or data description language (DDL) is a syntax similar to a computer programming language for defining data structures, especially database schemas. D SQL (Structured Query Language) – has a confusing name because of the word Query. SQL – DML (Data Manipulation Language) allows the user/administrator to add, delete, modify and query data that is already in database tables. SQL – DDL (Data Definition Language) allows the user/administrator to create, delete and change the structure of the tables and there inter-connection. DDL doesn’t evolve querying and DML does a lot more that just querying. However SQL is the combination of DML and DDL.

ER – diagram

Creating A Table CREATE TABLE members ( user. ID varchar(40) NOT NULL, password varchar(20) NOT NULL, first varchar(20) NOT NULL, last varchar(30) NOT NULL, roles int, UNIQUE (user. ID), PRIMARY KEY (user. ID) )ENGINE=Inno. DB; Comments Making a field a primary key makes it UNIQUE and NOT NULL automatically so the NOT NULL and UNIQUE is overkill.

Foreign Key (total participation) CREATE TABLE members. Challange. Questions ( member. ID varchar(40) , question varchar(100) NOT NULL, answer varchar(50) NOT NULL, FOREIGN KEY (member. ID) REFERENCES members(user. ID), PRIMARY KEY (member. ID) )ENGINE=Inno. DB; Comments FOREIGN KEY requires that the member. ID can only contain values that are in the user. ID column of the MEMBERS table. To delete a member with a challenge question would now require the question get deleted first. This table must be created after the members table.

Alter an existing Table’s definition ALTER TABLE members ADD middle varchar(20); // add a middle name with 20 characters ALTER TABLE members ADD middle varchar(20) AFTER first; ALTER TABLE members ADD middle varchar(20) BEFORE last; ALTER TABLE members ALTER middle varchar(30); // change middle name to 30 characters ALTER TABLE members DROP middle; // get rid of middle name

Rename and create Like Table CREATE TABLE new. Members LIKE members // create an empty table with same attributes CREATE TABLE all. Members LIKE members // create a table with same attributes AS ( SELECT * FROM members ORDER BY last) // and populate with ordered data records RENAME TABLE members TO old. Members // archive members into old members

Change the name of a column with FK’s ALTER TABLE members RENAME COLUMN user. ID to member. ID // This will fail because members. Challange. Question required the user. ID field // We (the data base administrator DBA) need to do this in a few steps ALTER TABLE `memberschallangequestions` DROP FOREIGN KEY IF EXISTS `object_ibfk_1` ALTER TABLE members RENAME COLUMN user. ID to member. ID ALTER TABLE members. Challange. Questions ADD FOREIGN KEY (memberid) REFERENCES members(member. ID)

M-N (many to many relation) CREATE TABLE members. Phones ( member. ID varchar(40) NOT NULL, phone. ID varchar(20) NOT NULL, FOREIGN KEY (member. ID) REFERENCES members(user. ID), FOREIGN KEY (phone. ID) REFERENCES phones(phone. ID), PRIMARY KEY (member. ID, phone. ID), )ENGINE=Inno. DB; Comments A new table to allow for the diamond with M-N relationship connecting members phones Since the combination of member. ID and phone. ID is a Primary Key, duplicates pairs and NULLs will not be allowed.

Truncate a table TRUNCATE TABLE old. Members // SQL-DDL will delete all rows in the old. Members table DELETE FROM old. Members // SQL-DML will delete all rows in the old. Members table TRUNCATE is much faster than DELETE which will delete each row one at a time, checking for constraints, recalculating indexes each time etc.