SQL | DDL，DML，TCL和DCL命令

The most commonly used language to create, operate, update, manipulate and communicate with the database, usually a relational database, is called SQL (Structured Query Language). It is pronounced as "ees-que-el'' or "sequel". It is a standard language adopted for all RDBMS with 4GL(Fourth Generation Language) capability. It caters to most of the commonly used queries by the user on the database.

与数据库 (通常是关系数据库) 创建，操作，更新，操作和通信的最常用语言称为SQL(结构化查询语言)。 它的发音为“ ees-que-el”或“ sequel”，是所有具有4GL(第四代语言)功能的RDBMS所采用的标准语言，可满足用户对数据库的大多数常用查询。

MySQL

MySQL

MySQL is an open-source and freely available Relational Database Management System (RDBMS) that uses Structured Query Language (SQL). It provides excellent features for creating, storing, maintaining, and accessing data, stored in the form of databases and their respective tables.

MySQL是使用结构化查询语言(SQL)的免费开放源代码关系数据库管理系统(RDBMS)。 它为创建，存储，维护和访问以数据库及其各自表形式存储的数据提供了出色的功能。

Being open-source software, MySQL can be freely and easily downloaded from www.mysql.org. It is fully secured, reliable and fast, and possesses far better functionalities than many other commercial RDBMSs available in the market.

作为开源软件，MySQL可以从www.mysql.org免费下载。 它是完全安全，可靠和快速的，并且具有比市场上许多其他商业RDBMS更好的功能。

SQL语句的分类 (Classification of SQL Statements)

1. Data Definition Language (DDL) Commands

   数据定义语言(DDL)命令

2. Data Manipulation Language (DML) Commands

   数据操作语言(DML)命令

3. Transaction Control Language (TCL) Commands

   事务控制语言(TCL)命令

4. Data Control Language (DCL) Commands

   数据控制语言(DCL)命令

1)数据定义语言(DDL)命令 (1) Data Definition Language (DDL) Commands)

A language that helps a user to define a database schema or database structure by a set of definitions is termed as Data Definition Language. It contains the necessary statements for creating, manipulating, altering and deleting the table. The compilation of DDL statements results in a set of tables that are stored in a special file known as Data Dictionary.

一种可以帮助用户通过一组定义定义数据库模式或数据库结构的语言称为“ 数据定义语言” 。 它包含用于创建，操作，更改和删除表的必要语句。 DDL语句的编译产生一组表，这些表存储在一个称为数据字典的特殊文件中。

The DDL provides a set of definitions to specify the storage structure and access methods used by the database system. DDL Performs the following functions:
It defines the different types of database elements such as data-item, segment record, table, and databases.

DDL提供了一组定义，以指定数据库系统使用的存储结构和访问方法。 DDL执行以下功能：
它定义了不同类型的数据库元素，例如数据项，段记录，表和数据库。

• DDL provides data elements such as data-item-type, record-type, file-type, database, etc., with a unique name.

  DDL提供具有唯一名称的数据元素，例如数据项类型，记录类型，文件类型，数据库等。

• It defines proper and relevant data types.

  它定义了正确且相关的数据类型。

• It defines and specifies how the records are logically related to each other.

  它定义并指定记录在逻辑上如何相互关联。

Data Definition Language (DDL) commands allow us to perform the tasks related to data definition or data structure.

数据定义语言(DDL)命令使我们能够执行与数据定义或数据结构有关的任务。

These commands include:

这些命令包括：

• CREATE: This command is used to create database objects such as a database, table, view, etc.

  CREATE ：此命令用于创建数据库对象，例如数据库，表，视图等。

• ALTER: This command is used to change or modify database objects such as a database, table, etc.

  ALTER ：此命令用于更改或修改数据库对象，例如数据库，表等。

• DROP: This command is used to remove or delete database objects such as a database, table, etc.

  DROP ：此命令用于删除或删除数据库对象，例如数据库，表等。

2)数据操作语言(DML)命令 (2) Data Manipulation Language (DML) Commands)

A Data Manipulation Language (DML) is a part of SQL that helps a user to access or manipulate data. The DML statements are executed in the form of queries which are handled by the DML compiler. It contains a set of statements to:

数据操作语言(DML)是SQL的一部分，可帮助用户访问或操作数据。 DML语句以DML编译器处理的查询形式执行。 它包含一组语句以：

• Retrieve data from the tables of the database.

  从数据库表中检索数据。

• Insert data into the tables of the database.

  将数据插入数据库的表中。

• Delete data from the tables of the database.

  从数据库表中删除数据。

• Update data among the rows/records in the tables of the database.

  更新数据库表中的行/记录中的数据。

DML commands carry out query processing operations and manipulate data in the database objects. Several DML commands available here:

DML命令执行查询处理操作并处理数据库对象中的数据。 几个DML命令在这里可用：

• SELECT statement: To extract information from the table may or may not be on the basis of certain conditions/criteria.

  SELECT语句 ：从表中提取信息可能基于或不基于某些条件/条件。

• Insert INTO statement: To insert new data (record) into a table.

  插入INTO语句 ：将新数据(记录)插入表中。

• UPDATE statement: To modify or change the data (tuple) in a table (not modifying the data type of column).

  UPDATE语句 ：修改或更改表中的数据(元组)(不修改列的数据类型)。

• DELETE: To delete data (tuple) from a table (not deleting a column).

  DELETE ：从表中删除数据(元组)(不删除列)。

DML can be further classified into two types:

DML可以进一步分为两种类型：

1. Procedural DMLs: These commands provide the procedures for information retrieval, i.e., the user has to specify "what data" is needed and "how-to" fetch it.

   程序DML ：这些命令提供了信息检索的过程，即，用户必须指定“需要什么数据”并“如何”获取它。

2. Non-Procedural DMLs: These commands require a user to indicate "what to do" and not "how to do" as it is done by the DBMS through its own set of procedures.

   非过程DML ：这些命令要求用户指出“该做什么”而不是“该怎么做”，这是DBMS通过自己的一套过程来完成的。

3)交易控制语言(TCL)命令 (3) Transaction Control Language (TCL) Commands)

TCL is a set of commands used to control and manage the transaction control statements. It includes commands for specifying the beginning and ending of transactions. These commands keep a check on other commands and their effect on the database. These commands can nullify the changes made by other commands by rolling back to the original state. It can also make the changes permanent.

TCL是用于控制和管理事务控制语句的一组命令。 它包括用于指定事务开始和结束的命令。 这些命令会检查其他命令及其对数据库的影响。 这些命令可以通过回滚到原始状态来使其他命令所做的更改无效。 它还可以使更改永久生效。

Transaction Control is done in four states:

事务控制在四种状态下完成：

• COMMIT: Whenever transaction is done in the table is permanent, i.e., changes are permanently saved in the tables/databases.

  COMMIT ：只要在表中完成事务，便会永久保存，即，更改会永久保存在表/数据库中。

• ROLLBACK: It allows you to undo the transaction,i.e.,undo the changes.

  ROLLBACK ：它允许您撤消事务，即撤消更改。

• SAVEPOINT: To save the changes made by a transaction temporarily until commit commands get executed.

  SAVEPOINT ：临时保存事务所做的更改，直到执行提交命令为止。

• SET TRANSACTION: Establishes properties for the current transactions. Thus, transactional language controls logical units of work.

  SET TRANSACTION ：为当前交易建立属性。 因此，交易语言控制着逻辑工作单元。

4)数据控制语言(DCL)命令 (4) Data Control Language (DCL) Commands)

It contains the commands used for controlling and specifying user access to the data in relations and views. It also provides a command to grant and take back access rights/authority. These are also termed as Authorization checks.

它包含用于控制和指定用户对关系和视图中数据的访问的命令。 它还提供了授予和收回访问权限的命令。 这些也称为授权检查 。

For example, commands like GRANT, REVOKE, etc., are available in DCL.

例如， DCL中提供了诸如GRANT ， REVOKE等命令。

翻译自: https://www.includehelp.com/sql/sql-ddl-dml-tcl-and-dcl-commands.aspx