12
There are two main types of SQL statements that are executed within databases as described in What Is SQL. Before you can manipulate data residing in a database using SQL Data Manipulation Language (DML), you have to create the logical structure to store information.
Data Definition Language (DDL) is the portion of SQL that deals with how data should reside in the database at a logical level. Each database has its own set of object types that it allows. Most include tables, indexes, views, store procedures, functions, synonyms, and triggers. Each database has its own syntax for DDL statements and the clauses that can be included. There are some basic key words that you will find in almost every RDBMS.
CREATE
ALTER
DROP
TRUNCATE
You can probably guess the basic purpose of a statement that begins with each of those key words.
There are several sites that allow you to practice utilizing SQL. I will be utilizing SQL Fiddle. The syntax I will show was tested using the Oracle 11g R2 version. The SQL Fiddle area "Build Schema" is used for DDL statements. If you want to execute the statements yourself just append each new statement to the list of previous statements in SQL Fiddle and then click on the "Build Schema" button.
The basic building blocks of the Relational Database Management System are tables. I envision a table as a set of rows and columns. The columns represent fields of information. The rows represent records in the table. In following graphic, the persons table has four fields and four records.
You could simply create the table with the following statement:
1 2 3 4 5 6
CREATE TABLE books ( book_id VARCHAR(100), book_name VARCHAR(100), author_id NUMBER, editor_id NUMBER) ;
The problem with this table definition is that it allows rows to be created without concern for if the data makes any sense. Envision our table looking like this:
Your database design should make sure that data inserted into a table is sensible. Let us create a second table called the "persons" table. This time we will add constraints to make sure that data entered into the table will make sense. It makes sense that each entry in the table will be unique person so we give it a PRIMARY KEY
. We will also want to make sure to track when the table was last updated and who updated it by making those fields NOT NULL
.
1 2 3 4 5 6 7 8
CREATE TABLE persons ( person_id NUMBER NOT NULL PRIMARY KEY, name VARCHAR(100), birth_date DATE, gender VARCHAR(30), last_update DATE NOT NULL, updated_by NUMBER NOT NULL );
We have looked at just the creation of tables there are many other create type statements. Here are some other examples:
NOTE: The design of sqlfiddle.com does not allow these statements to be executed.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
CREATE FUNCTION get_bookcount(author IN NUMBER) RETURN NUMBER IS bookcount NUMBER(10); BEGIN SELECT count(1) INTO bookcount FROM books WHERE author_id = author; RETURN(bookcount); END; / CREATE INDEX editor_indx ON books(editor_id); CREATE ROLE book_reader; CREATE TRIGGER persons_update BEFORE INSERT OR UPDATE ON persons FOR EACH ROW BEGIN :new.last_update := sysdate; END; /
Other CREATE Statement Types |
---|
CREATE SCHEMA statement |
CREATE SEQUENCE statement |
CREATE SYNONYM statement |
CREATE TRIGGER statement |
CREATE TYPE statement |
CREATE VIEW statement |
The definition of an object in a database can be changed using the ALTER
statement. Example: Add constraints to the "books" table to assure the fields "book_name" and "author_id" contain data.
1 2
ALTER TABLE books MODIFY (book_name NOT NULL); ALTER TABLE books MODIFY (author_id NOT NULL);
A FOREIGN KEY
constraint can be added to the fields "author_id" and "the editor_id" limiting the available values to ones that currently exist in the persons table in the "person_id" field.
1 2 3 4 5
ALTER TABLE books ADD CONSTRAINT fk_author FOREIGN KEY (author_id) REFERENCES persons (person_id); ALTER TABLE books ADD CONSTRAINT fk_editor FOREIGN KEY (editor_id) REFERENCES persons (person_id);
What if we wanted to add a publication date to our books table? Use the 'ALTER' statement to add the field.
1
ALTER TABLE books ADD ( publish_date DATE);
You can alter more than just tables. Here are examples of some other ALTER
statements.
NOTE: The design of sqlfiddle.com does not allow these statements to be executed.
1 2 3 4 5
ALTER ROLE book_reader IDENTIFIED BY r2Xe135DEw; ALTER INDEX editor_indx DISABLE; ALTER TRIGGER persons_update RENAME TO persons_trig;
1
TRUNCATE TABLE books;
The TRUNCATE
statement removes all the data from a table. This is very similar to DML statement.
1
DELETE FROM books;
In the Oracle database, there is a difference between the two. TRUNCATE
removes all data where a DELETE
can be specific in the rows it wants to delete. Also, if you make a mistake with a DELETE
statement you can use the transactional control statement ROLLBACK
to remove the changes. The TRUNCATE
command has no rollback capability. The biggest positive to using the TRUNCATE
statement is that it can be faster than the DELETE
statement, especially if the table has numerous rows, triggers, indexes, and other dependencies.
Removing an object from the database accomplished with the DROP
statement.
1 2
DROP TABLE books; DROP TABLE persons;
When you drop a table it removes all the rows, invalidates dependent objects, removes indexes, constraints and privileges that anyone had on that table. Just as with the CREATE
and ALTER
statements, there are other DROP
statement types.
Other DROP Statement Types |
---|
DROP FUNCTION statement |
DROP INDEX statement |
DROP PROCEDURE statement |
DROP ROLE statement |
DROP SCHEMA statement |
DROP SEQUENCE statement |
DROP SYNONYM statement |
DROP TRIGGER statement |
DROP TYPE statement |
DROP VIEW statement |
That concludes our brief introduction to the SQL Data Description Language.
12