SQL 命令：DQL、DDL、DML、DCL 和 TCL 及示例

什么是 SQL？

SQL 是一种用于检索和管理关系数据库中的数据的数据库语言。

SQL 是数据库管理的标准语言。所有 RDBMS 系统，如 MySQL、MS Access、 Oracle, Sybase, Postgres, and SQL Server use SQL as their standard database language. The SQL programming language uses various commands for different operations. We will learn about the DCL, TCL, DQL, DDL, and DML commands in SQL with examples.

为什么使用 SQL？

Here are important reasons for using SQL:

• 它帮助用户访问数据。 RDBMS 系统.
• 它可以帮助你描述数据。
• 它允许您定义数据库中的数据并操作特定的数据。
• With the help of SQL commands, you can create and drop databases and tables.
• SQL lets you use functions in a database, and create views and stored procedures.
• 您可以设置表、过程和视图的权限。

SQL简史

Here are important landmarks from the SQL的历史:

• 1970 年——Edgar F.“Ted”Codd 博士描述了数据库的关系模型。
• 1974 – Structured Query Language appeared, originally named SEQUEL at IBM.
• 1979 - Oracle released the first commercially available SQL-based database, based on IBM’s System R research prototype.
• 1986 – ANSI published the first SQL standard, SQL-86.
• 1989 – SQL-89 arrived as the first revision of the standard.
• 1992 – SQL-92 (SQL2) became the major baseline still referenced by most databases today.
• 1999 – SQL:1999 (SQL3) launched with features like triggers, recursive queries, and object-orientation.
• 2003 – SQL:2003 added window functions, sequence generators, and XML-related features.
• 2006 – SQL:2006 added support for XML Query Language (XQuery).
• 2008 – SQL:2008 introduced the TRUNCATE statement and INSTEAD OF triggers.
• 2011 – SQL:2011 improved support for temporal databases.
• 2016 – SQL:2016 added JSON support and row pattern matching (MATCH_RECOGNIZE).
• 2019 – SQL/MDA (Part 15) added multi-dimensional array support to the standard.
• 2023 – SQL:2023 added a native JSON data type and property graph queries (SQL/PGQ).
• 2024 – GQL, the first new ISO database language since SQL, was published as a graph query companion standard.
• 2026 – SQL:2023 remains the current standard, with the next revision under development at ISO/IEC.

Types of SQL Commands

Here are five types of widely used SQL commands:

• 数据定义语言（DDL）
• 数据操纵语言（DML）
• 数据控制语言（DCL）
• 事务控制语言 (TCL)
• 数据查询语言 (DQL)

The table below summarises the five categories before each is covered in detail.

什么是 DDL？

Data Definition Language helps you define the database structure or schema. Let us learn about the DDL commands with syntax. The main DDL commands in SQL are CREATE, DROP, ALTER, and TRUNCATE.

CREATE

The CREATE statement is used to define the database structure or schema:

语法：

CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);

例如：

Create database university;
Create table students;
Create view for_students;

下降

The DROP command removes tables and databases from the RDBMS.

语法：

DROP TABLE ;

例如：

Drop object_type object_name;
Drop database university;
Drop table student;

改变

The ALTER command allows you to alter the structure of the database.

语法： to add a new column in the table:

ALTER TABLE table_name ADD column_name COLUMN-definition;

要修改表中现有的列：

ALTER TABLE MODIFY(COLUMN DEFINITION....);

例如：

Alter table guru99 add subject varchar;

截短

此命令用于从表中删除所有行并释放包含表的空间。

语法：

TRUNCATE TABLE table_name;

计费示例：

TRUNCATE table students;

什么是数据操作语言？

Data Manipulation Language (DML) allows you to modify the database instance by inserting, modifying, and deleting its data. It is responsible for performing all types of data modification in a database. The important DML commands in SQL are:

• 插入
• 更新
• 删除

插入

This statement is used to insert data into the rows of a table.

语法：

INSERT INTO TABLE_NAME (col1, col2, col3,.... col N)
VALUES (value1, value2, value3, .... valueN);
Or
INSERT INTO TABLE_NAME
VALUES (value1, value2, value3, .... valueN);

例如：

INSERT INTO students (RollNo, FIrstName, LastName) VALUES ('60', 'Tom', Erichsen');

更新

此命令用于更新或修改表中某一列的值。

语法：

UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CONDITION]

例如：

UPDATE students
SET FirstName = 'Jhon', LastName= 'Wick'
WHERE StudID = 3;

删除

此命令用于从表中删除一行或多行。

语法：

DELETE FROM table_name [WHERE condition];

例如：

DELETE FROM students
WHERE FirstName = 'Jhon';

什么是DCL？

DCL (Data Control Language) includes commands like GRANT and REVOKE, which are useful to give rights and permissions. Other commands control parameters of the database system.

格兰特

This command is used to give a user access privileges to a database.

语法：

GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;

例如：

GRANT SELECT ON Users TO'Tom'@'localhost;

Rev好

This command is useful to take back permissions from the user.

语法：

REVOKE privilege_nameON object_nameFROM {user_name |PUBLIC |role_name}

例如：

REVOKE SELECT, UPDATE ON student FROM BCA, MCA;

TCL是什么？

Transaction Control Language, or TCL commands, deal with the transaction within the database. They work directly with the states described under 交易管理.

承诺

此命令用于将所有交易保存到数据库。

语法：

Commit;

例如：

DELETE FROM Students
WHERE RollNo =25;
COMMIT;

回滚

The Rollback command allows you to undo transactions that have not already been saved to the database.

语法：

ROLLBACK;

计费示例：

DELETE FROM Students
WHERE RollNo =25;

保存点

This command helps you set a savepoint within a transaction.

语法：

SAVEPOINT SAVEPOINT_NAME;

计费示例：

SAVEPOINT RollNo;

什么是DQL？

Data Query Language (DQL) is used to fetch data from the database. It uses only one command, SELECT.

选择

This command helps you select the attributes based on the condition described by the WHERE clause.

语法：

SELECT expressions
FROM TABLES
WHERE conditions;

例如：

SELECT FirstName
FROM Student
WHERE RollNo > 15;

DELETE vs TRUNCATE vs DROP

These three commands all remove data, but they differ in what they remove and whether the action can be rolled back. Confusing them is a common beginner mistake.

In short, use DELETE to remove specific rows, TRUNCATE to empty a table quickly, and DROP to discard the table entirely.

常见问题