Data definition language: Difference between revisions

Revision as of 09:49, 30 January 2020

Latest revision as of 19:09, 27 November 2024

(34 intermediate revisions by 26 users not shown)

Line 1:

{{short description|Syntax for defining data structures}}

{{Distinguish|Data manipulation language}}

{{Refimprove|date=December 2012}}

{{Multiple issues|

A '''data definition''' or '''data description language''' ('''DDL''') is a syntax similar to a computer [[programming language]] for defining [[data structure]]s, especially [[database schema]]s. DDL statements create and modify database objects such as tables, indexes, and users. Common DDL statements are <code>CREATE</code>, <code>ALTER</code>, and <code>DROP</code>.

{{Refimprove|date=December 2012}}

{{Cleanup|reason=the article focuses almost entirely on SQL.|date=June 2020}}

}}

[[File:UY3OsG1vuT-saving-a-ddl-file-in-Oracle-Developer.png|alt=Saving a ddl file in Oracle SQL Developer|thumb|Saving a ddl file in Oracle SQL Developer]]

In the context of [[SQL]], '''data definition''' or '''data description language''' ('''DDL''') is a syntax for creating and modifying database objects such as tables, indices, and users. DDL statements are similar to a computer [[programming language]] for defining [[data structure]]s, especially [[database schema]]s. Common examples of DDL statements include <code>CREATE</code>, <code>ALTER</code>, and <code>DROP</code>. If you see a .ddl file, that means the file contains a statement to create a table. Oracle SQL Developer contains the ability to export from an ERD generated with Data Modeler to either a .sql file or a .ddl file.

==History==

The concept of the data definition language and its name was first introduced in relation to the [[Codasyl]] database model, where the schema of the [[database]] was written in a [[Syntax (programming languages)|language syntax]] describing the [[Record (computer science)|records]], [[Field (computer science)|fields]], and [[Set (abstract data type)|sets]] of the user [[data model]].<ref>{{cite book|last=Olle|first=T. William|title=The Codasyl Approach to Data Base Management|year=1978|publisher=Wiley|isbn=0-471-99579-7|url-access=registration|url=https://archive.org/details/codasylapproacht00olle}}</ref> Later it was used to refer to a subset of [[Structured Query Language]] (SQL) for declaring [[Table (database)|tables]], columns, data types and [[Integrity constraints|constraints]]. [[SQL-92]] introduced a schema manipulation language and schema information tables to query schemas.<ref name="SQL92">{{cite web |title=Information Technology - Database Language SQL |url=http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt |website=SQL92 |publisher=Carnegie Mellon |accessdateaccess-date=12 November 2018}}</ref>. These information tables were specified as [[SQL/Schemata]] in [[SQL:2003]]. The term DDL is also used in a generic sense to refer to any [[formal language]] for describing data or information structures.

==Structured Query Language (SQL)==

Line 14 ⟶ 18:

The ''create'' command is used to establish a new database, table, index, or [[stored procedure]].

The ''CREATE'' statement in [[SQL]] creates a component in a [[relational database management system]] (RDBMS). In the SQL 1992 specification, the types of components that can be created are schemas, [[table (database)|tables]], [[View (database)|views]], domains, [[character set]]s, [[collation]]s, translations, and assertions.<ref name="SQL92" />. Many implementations extend the syntax to allow creation of additional elements, such as [[Database index|indexes]] and user profiles. Some systems, such as [[PostgreSQL]] and [[Microsoft SQL Server|SQL Server]], allow ''CREATE'', and other DDL commands, inside a [[database transaction]] and thus they may be [[rollback (data management)|rolled back]].<ref>{{cite web |last1=Laudenschlager |first1=Douglas |last2=Milener |first2=Gene |last3=Guyer |first3=Craig |last4=Byham |first4=Rick |title=Transactions (Transact-SQL) |url=https://docs.microsoft.com/en-us/sql/t-sql/language-elements/transactions-transact-sql?view=sql-server-2017 |website=Microsoft Docs |publisher=Microsoft |accessdateaccess-date=12 November 2018}}</ref><ref>{{cite web |title=PostgreSQL Transactions |url=https://www.postgresql.org/docs/8.3/tutorial-transactions.html |website=PostgreSQL 8.3 Documentation |date=7 February 2013 |publisher=PostgreSQL |accessdateaccess-date=12 November 2018}}</ref>

====CREATE TABLE statement====

Line 34 ⟶ 38:

first_name VARCHAR(50) not null,

last_name VARCHAR(75) not null,

fname mid_name VARCHAR(50) not null,

dateofbirth DATE not null

);

</syntaxhighlight>

Some forms of ''CREATE TABLE DDL'' may incorporate DML ([[data manipulation language]])-like constructs, such as the ''CREATE TABLE AS SELECT'' (CTASCTaS) syntax of SQL.<ref>

{{cite book

| last = Allen

Line 46 ⟶ 50:

| title = The Definitive Guide to SQLite

| url = https://books.google.com/books?id=WLinoJaOUCwC

| accessdateaccess-date = 2012-10-02

| edition = 2

| series = Apresspod

Line 53 ⟶ 57:

| isbn = 9781430232254

| pages = 90–91

| quote = The ''create table'' statement has a special syntax for creating tables from ''select'' statements. [...]: [...] ''create table foods2 as select * from foods;'' [...] Many other databases refer to this approach as ''CTASCTaS'', which stands for Create Table Asas Select, and that phrase is not uncommon among SQLite users.

}}

</ref>

Line 66 ⟶ 70:

For example, the command to drop a table named '''employees''' is:

<syntaxhighlight lang="textsql">

DROP TABLE employees;

</syntaxhighlight>

Line 81 ⟶ 85:

For example, the command to add (then remove) a column named '''bubbles''' for an existing table named '''sink''' is:

<syntaxhighlight lang="textsql">

ALTER TABLE sink ADD bubbles INTEGER;

ALTER TABLE sink DROP COLUMN bubbles;

Line 89 ⟶ 93:

The ''TRUNCATE'' statement is used to delete all data from a table. It's much faster than ''DELETE''.

<syntaxhighlight lang="textsql">

TRUNCATE TABLE table_name;

</syntaxhighlight>

Line 98 ⟶ 102:

==Other languages==

* [[XML Schema (W3C)|XML Schema]] is an example of a DDL for [[XML]].

* [[JSON#JSON_SchemaMetadata_and_schema|JSON Schema]] is an example of a DDL for [[JSON]].

* [[Data_Format_Description_Language|DFDL schema]] is an example of a DDL that can describe many text and binary formats.

==See also==

Line 114 ⟶ 119:

==External links==

* [https://oracletutorial.net/alter-table-modify-column-oracle.html Oracle ALTER TABLE MODIFY column] {{Webarchive|url=https://web.archive.org/web/20210421200756/https://oracletutorial.net/alter-table-modify-column-oracle.html |date=2021-04-21 }}

* [https://oracletutorial.net/dml-ddl-commands-in-oracle.html#ddl-commands-in-oracle DDL Commands In Oracle] {{Webarchive|url=https://web.archive.org/web/20210421203338/https://oracletutorial.net/dml-ddl-commands-in-oracle.html#ddl-commands-in-oracle |date=2021-04-21 }}

{{Database}}

{{databases}}

[[Category:Articles with example SQL code]]