What is data definition language

Data Definition Language (DDL) is a language responsible for data structure definition and database object definition in the SQL language. It consists of three syntaxes: CREATE, ALTER and DROP.

was originally started by the Codasyl (Conference on Data Systems Languages) data model, and is now incorporated into SQL instructions as a subset of it .

Most DBMS currently support DDL operations on database objects. Some databases (such as PostgreSQL) can put DDL in transaction instructions, which means it can be withdrawn (Rollback). Newer versions of DBMS will add DDL-specific triggers so that database administrators can track modifications from DDL.

Sql statements are divided into three categories:

Data definition language, responsible for creating, modifying, and deleting tables, indexes, views, functions, Objects such as stored procedures and triggers;

Data manipulation language, responsible for inserting, modifying, deleting and other operations of data in the database;

Data control language, used to grant and revoke user permissions.

Example

##CREATE

CREATE is responsible for the creation of database objects, including databases, data tables, database indexes, and pre-storage Objects such as programs, user functions, trigger programs, or user-defined types can all be created using the CREATE command. To account for the differences in various database objects, CREATE also has many parameters.

For example, the command of CREATE DATABASE (create database) is:

CREATE DATABASE Sales
ON ( NAME = Sales_dat, FILENAME = 'saledat.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 )
LOG ON ( NAME = Sales_log, FILENAME = 'salelog.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB )

where ON is the statement of the database file, and LOG ON is the statement of the transaction record file. If more advanced settings are required, there are FOR, WITH, COLLATE, etc.

Another example, the command of CREATE TABLE (create data table) is:

CREATE TABLE [dbo].[PurchaseOrderDetail]
(
[PurchaseOrderID] [int] NOT NULL REFERENCES Purchasing.PurchaseOrderHeader(PurchaseOrderID), -- 具引用完整性限制字段
[LineNumber] [smallint] NOT NULL,
[ProductID] [int] NULL REFERENCES Production.Product(ProductID), -- 具引用完整性限制字段
[UnitPrice] [money] NULL,
[OrderQty] [smallint] NULL,
[ReceivedQty] [float] NULL,
[RejectedQty] [float] NULL,
[DueDate] [datetime] NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_PurchaseOrderDetail_rowguid] DEFAULT (newid()), -- 具限制字段，并有默认值
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_PurchaseOrderDetail_ModifiedDate] DEFAULT (getdate()), -- 具限制字段，并有默认值
[LineTotal] AS (([UnitPrice]*[OrderQty])),
[StockedQty] AS (([ReceivedQty]-[RejectedQty])),
CONSTRAINT [PK_PurchaseOrderDetail_PurchaseOrderID_LineNumber] -- 主键宣告
PRIMARY KEY CLUSTERED ([PurchaseOrderID], [LineNumber])
WITH (IGNORE_DUP_KEY = OFF)
)
ON [PRIMARY]

Among them, the format of each field is defined, and a reference is established if necessary For complete links, REFERENCES can be used to declare, primary keys can be declared using PRIMARK KEY, and calculated fields can be directly given expressions, etc. The CREATE TABLE instruction is very commonly used, but it will be more complicated to set up. Complex, so many database managers will use GUI tools to design.

Others are:

CREATE INDEX: Create a data table index.

CREATE PROCEDURE: Create a stored program.

CREATE FUNCTION: Create a user function.

CREATE VIEW: Create a view table.

CREATE TRIGGER: Create a trigger program.

, etc., are all instructions used to create different database objects.

The above is the detailed content of What is data definition language. For more information, please follow other related articles on the PHP Chinese website!