The SELECT, INSERT, DELETE, UPDATE, COMMIT, and ROLLBACK statements described in Parts II and III of this book are all concerned with manipulating the data in a database. These statements collectively are called the SQL Data Manipulation Language, or DML. The DML statements can modify the data stored in a database, but they cannot change its structure. None of these statements creates or deletes tables or columns, for example.
Changes to the structure of a database are handled by a different set of SQL statements, usually called the SQL Data Definition Language, or DDL. Using DDL statements, you can:
- Define and create a new table
- Remove a table that’s no longer needed
- Change the definition of an existing table
- Define a virtual table (or view) of data
- Establish security controls for a database
- Build an index to make table access faster
- Control the physical storage of data by the DBMS
For the most part, the DDL statements insulate you from the low-level details of how data is physically stored in the database. They manipulate abstract database objects, such as tables and columns. However, the DDL cannot avoid physical storage issues entirely, and by necessity, the DDL statements and clauses that control physical storage vary from one DBMS to another.
The core of the Data Definition Language is based on three SQL verbs:
- CREATE. Defines and creates a database object
- DROP. Removes an existing database object
- ALTER. Changes the definition of a database object
In all major SQL-based DBMS products, these three DDL verbs can be used while the DBMS is running. The database structure is thus dynamic. The DBMS can be creating, dropping, or changing the definition of the tables in the database, for example, while it is simultaneously providing access to the database for its users. This is a major advantage of SQL and relational databases over earlier systems, where the DBMS had to be stopped before you could change the structure of the database. It means that a relational database can grow and change easily over time. Production use of a database can continue while new tables and applications are added.
Although the DDL and DML are two distinct parts of the SQL language, in most SQL-based DBMS products, the split is only a conceptual one. Usually, the DDL and DML statements are submitted to the DBMS in exactly the same way, and they can be freely intermixed in both interactive SQL sessions and programmatic SQL applications. If a program or user needs a table to store its temporary results, it can create the table, populate it, manipulate the data, and then delete the table. Again, this is a major advantage over earlier data models, in which the structure of the database was fixed when the database was created.
Although virtually all commercial SQL products support the DDL as an integral part of the SQL language, the SQL1 standard did not require it. In fact, the SQL1 standard implies a strong separation between the DML and the DDL, allowing vendors to achieve compliance with the DML part of the standard through a SQL layer on top of a non-SQL underlying database. The SQL2 standard still differentiates between different types of SQL statements. (It calls the DDL statements SQL-schema statements and the DML statements SQL-data statements and SQL-transaction statements.) However, it brings the standard into alignment with the actual implementation of popular SQL products by requiring that DDL statements be executed interactively and by a program.
The SQL2 standard specifies only the parts of the DDL that are relatively independent of physical storage structures, operating system dependencies, and other DBMS brand-specific capabilities. In practice, all DBMS brands include significant extensions to the standard DDL to deal with these issues and other enhanced database capabilities. The differences between the ANSI/ISO standard and the DDL as implemented in popular SQL products are described for each SQL statement through the remainder of this chapter.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.