Data Definition Language

Data Definition Language

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.

Contents

History

The data definition language concept and name was first introduced in relation to the Codasyl database model, where the schema of the database was written in a language syntax describing the records, fields, and sets of the user data model.[citation needed] Later it was used to refer to a subset of Structured Query Language (SQL) for creating tables and constraints. SQL-92 introduced a schema manipulation language and schema information tables to query schemas. 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.

SQL

Unlike many data description languages, SQL uses a collection of imperative verbs whose effect is to modify the schema of the database by adding, changing, or deleting definitions of tables or other objects. These statements can be freely mixed with other SQL statements, so the DDL is not truly a separate language.

CREATE statements

Create - To make a new database, table, index, or stored query. A CREATE statement in SQL creates an object inside of a relational database management system (RDBMS). The types of objects that can be created depends on which RDBMS is being used, but most support the creation of tables, indexes, users, synonyms and databases. Some systems (such as PostgreSQL) allow CREATE, and other DDL commands, inside of a transaction and thus they may be rolled back.

CREATE TABLE statement

Perhaps the most common CREATE command is the CREATE TABLE command. The typical usage is:

CREATE [TEMPORARY] TABLE [table name] ( [column definitions] ) [table parameters].

Column Definitions: A comma-separated list consisting of any of the following

  • Column definition: [column name] [data type] {NULL | NOT NULL} {column options}
  • Primary key definition: PRIMARY KEY ( [comma separated column list] )
  • Constraints: {CONSTRAINT} [constraint definition]
  • RDBMS specific functionality

For example, the command to create a table named employees with a few sample columns would be:

CREATE TABLE employees (
    id            INTEGER      PRIMARY KEY,
    first_name    VARCHAR(50)  NULL,
    last_name     VARCHAR(75)  NOT NULL,
    dateofbirth   DATE         NULL
);

DROP statements

Drop - To destroy an existing database, table, index, or view.

A DROP statement in SQL removes an object from a relational database management system (RDBMS). The types of objects that can be dropped depends on which RDBMS is being used, but most support the dropping of tables, users, and databases. Some systems (such as PostgreSQL) allow DROP and other DDL commands to occur inside of a transaction and thus be rolled back. The typical usage is simply:

DROP objecttype objectname.

For example, the command to drop a table named employees would be:

DROP TABLE employees;

The DROP statement is distinct from the DELETE and TRUNCATE statements, in that they do not remove the table itself. For example, a DELETE statement might delete some (or all) data from a table while leaving the table itself in the database, whereas a DROP statement would remove the entire table from the database.

ALTER statements

Alter - To modify an existing database object.

An ALTER statement in SQL changes the properties of an object inside of a relational database management system (RDBMS). The types of objects that can be altered depends on which RDBMS is being used. The typical usage is:

ALTER objecttype objectname parameters.

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

ALTER TABLE sink ADD bubbles INTEGER;
ALTER TABLE sink DROP COLUMN bubbles;

Referential integrity statements

Finally, other kind of DDL sentence in SQL are the statements to define referential integrity relationships, usually implemented as primary key and foreign key tags in some columns of the tables.

These two statements can be included inside a CREATE TABLE or an ALTER TABLE sentence.

Other languages

See also

External links


Wikimedia Foundation. 2010.

Игры ⚽ Нужно решить контрольную?

Look at other dictionaries:

  • Data Definition Language — (DDL) (язык описания данных)  это семейство компьютерных языков, используемых в компьютерных программах для описания структуры баз данных. На текущий момент наиболее популярным языком DDL является SQL, используемый для получения и… …   Википедия

  • Data Definition Language — Die Data Definition Language (DDL) ist eine Computersprache, die verwendet wird, um Datenstrukturen und verwandte Elemente zu beschreiben, zu ändern oder zu entfernen. Ursprünglich bezog sich DDL auf Datenbanksysteme, der Begriff wird aber heute… …   Deutsch Wikipedia

  • Data definition language — Langage de définition de données Le langage de définition de données (LDD, ou Data Definition Language, soit DDL en anglais) est un langage orienté au niveau de la structure de la base de données. C est à dire que les commandes manipulent les… …   Wikipédia en Français

  • Data Manipulation Language — (DML) (язык управления (манипулирования) данными)  это семейство компьютерных языков, используемых в компьютерных программах или пользователями баз данных для получения, вставки, удаления или изменения данных в базах данных. На текущий… …   Википедия

  • Data Query Language — Die Data Manipulation Language (DML, englisch für „Datenbearbeitungssprache“) ist derjenige Teil einer Datenbanksprache, der verwendet wird, um Daten zu lesen, zu schreiben, zu ändern und zu löschen. DML ist die Datenver oder… …   Deutsch Wikipedia

  • Data Manipulation Language — A data manipulation language (DML) is a family of syntax elements similar to a computer programming language used for inserting, deleting and updating data in a database. Performing read only queries of data is sometimes also considered a… …   Wikipedia

  • Data Manipulation Language — Die Data Manipulation Language (DML, englisch für „Datenbearbeitungssprache“) ist derjenige Teil einer Datenbanksprache, der verwendet wird, um Daten zu lesen, zu schreiben, zu ändern und zu löschen. DML ist die Datenver oder… …   Deutsch Wikipedia

  • Data Description Language — Die Data Definition Language (DDL) ist eine Computersprache, die verwendet wird, um Datenstrukturen und verwandte Elemente zu beschreiben, zu ändern oder zu entfernen. Ursprünglich bezog sich DDL auf Datenbanksysteme, der Begriff wird aber heute… …   Deutsch Wikipedia

  • Data control language — A data control language (DCL) is a syntax similar to a computer programming language used to control access to data stored in a database. In particular, it is a component of Structured Query Language (SQL). Examples of DCL commands include: GRANT …   Wikipedia

  • Data Control Language — A Data Control Language (DCL) is a computer language and a subset of SQL, used to control access to data in a database. Examples of DCL commands include: * GRANT to allow specified users to perform specified tasks. * REVOKE to cancel previously… …   Wikipedia

Share the article and excerpts

Direct link
Do a right-click on the link above
and select “Copy Link”