SQL命令全解析：DDL、DML、DQL、DCL与TCL详解

本文全面解析SQL命令的五大类型：DDL、DML、DQL、DCL和TCL，涵盖创建、查询、更新、删除数据及权限控制等核心操作，帮助开发者高效管理关系型数据库并确保数据安全。

SQL命令：基本SQL语言命令列表

SQL（结构化查询语言）是一种编程语言，用于在关系型数据库管理系统中进行数据管理和操作。如今，所有大小企业都依赖SQL进行数据存储和转换。在大多数情况下，仅学习基本命令就足以有效管理数据库。

关键要点

  • SQL命令分为五类：DDL、DML、DCL、DQL和TCL，每类服务于特定数据库需求。
  • SQL命令范围从基本查询（如CREATE和UPDATE）到复杂功能（如聚合函数和表连接以创建复杂查询）。
  • 选择正确的SQL方言取决于应用需求、预算和集成能力。
  • 开发者可以通过将SQL与编程语言和商业智能（BI）工具集成来构建数据驱动应用，以管理数据并提取有意义的见解。
  • 安全认证方法、访问控制和加密保护数据库免受未经授权的访问。

什么是SQL命令？

SQL提供全面的SQL命令列表与数据库通信。我们可以将SQL视为传递给数据库的指令集。这些指令（称为SQL语言命令）使我们能够执行广泛的操作。例如，我们可以使用SQL命令创建数据库结构、创建表或临时表、填充数据库、检索特定信息、修改数据以及控制访问和安全。

基本SQL命令

以下是文章中解释的基本SQL命令的快速概述。初学者可以学习这些命令以理解SQL基础。

  • SELECT
  • INSERT
  • UPDATE
  • DELETE

SQL语句

SQL语句是一种用于与数据库通信的结构化查询。它遵循特定语法，包括子句、关键字和条件来编写查询。用户可以根据其特定数据库需求和执行的操作自定义SQL语句。

基本SQL语句结构 

1
2

SELECT column_name;
FROM table_name WHERE condition;

SQL命令类型

以下是不同类型的SQL命令：

DDL（数据定义语言）命令

DDL包含数据库级命令以更改数据库结构。这些DDL命令定义、修改和删除数据库表、视图、索引和数据库模式。此外，DDL命令是自动提交的，确保更改永久保存在数据库中，并且无法回滚到先前的更改。

CREATE

此命令创建新的数据库对象。对象可以是表或数据库，如下所示。

1
2
3
4
5

CREATE DATABASE database_db;
-- 此SQL语句创建新数据库database_db。

CREATE TABLE PERSONS (id INT, name VARCHAR(255));
-- 此SQL语句创建新表PERSONS，包含列id和name。

ALTER

此命令通过添加、更改或删除表列、更改数据类型或重命名对象来修改现有对象的结构。

1
2

ALTER TABLE PERSONS ADD COLUMN address VARCHAR(255);
-- 此SQL命令向PERSONS表添加新列ADDRESS。

DROP

DROP命令删除现有数据库对象。

1
2
3
4
5

DROP DATABASE database_db;
-- 此SQL语句删除整个数据库database_db。

DROP TABLE PERSONS;
-- 此删除语句从数据库中删除现有表PERSONS。

TRUNCATE

此命令删除表中的所有现有数据，同时保留原始表结构。TRUNCATE通常比DELETE更快，因为它不记录单个行删除。

1
2

TRUNCATE TABLE PERSONS;
-- 上述SQL语句从PERSONS表中移除所有记录/行。

注意：如果我们要截断包含在其他表中用作外键的主键的表，则需要CASCADE关键字。它将截断所有依赖表。

COMMENT

此SQL语句向特定数据库对象的定义添加注释，这对于文档目的至关重要。

1

COMMENT ON TABLE PERSONS IS 'Table contains persons information';

DML（数据操作语言）命令

DML包含用于操作数据库中数据的SQL主要命令。例如，这些SQL命令列表包括插入、修改和删除数据的命令。DML命令不是自动提交的，确保更改不会永久保存在数据库中，我们可以回滚到先前状态。例如，我们可以使用ROLLBACK语句检索已删除的行。

INSERT

此命令向表添加新数据。以下命令向PERSONS表添加新行。

1

INSERT INTO PERSONS (id, name) VALUES (10, 'Alice');

UPDATE

此命令更新表中的现有数据。如下所示，UPDATE命令更新ID为10的PERSONS名称。

1

UPDATE PERSONS SET name = 'Alice' WHERE id = 10;

DELETE

此命令根据某些条件删除现有数据。

1
2

DELETE FROM PERSONS WHERE id = 5;
-- 删除语句从PERSONS表中移除ID为5的人员。

DQL（数据查询语言）命令

DQL命令是SQL命令的子集，专门设计用于从数据库查询和检索数据。DQL命令（SELECT）对模式对象内的数据执行特定任务，并根据传递给它的查询提取模式关系。它利用各种子句、函数和关键字来过滤和操作数据，从而增强其功能。

SELECT（检索数据）

此命令从表中检索指定列（名称）：

1

SELECT name FROM PERSONS;

要检索所有列的数据，可以使用SELECT *（星号）：

1

SELECT * FROM PERSONS;

然而，通常不推荐使用*，因为它通过包含所有列（甚至不需要的列）来增加传输的数据量，这可能会影响查询性能。相反，最好明确列出所需的列：

1

SELECT id, name, email FROM PERSONS;

SELECT语句通常与其他子句和函数（如DISTINCT、AVG()、WHERE、ORDER BY、GROUP BY和HAVING）一起使用，以提取数据并聚合、过滤、排序或分组它以返回一列或多列。

DISTINCT 

1
2

SELECT DISTINCT name FROM PERSONS;
-- 此命令忽略重复行或多个值，仅从指定列（如PERSONS表中的名称列）返回唯一值。

WHERE 

1
2

SELECT column_name(s) FROM table_name WHERE column_name operator value;
-- WHERE子句根据指定条件过滤数据，例如WHERE name = ‘Alice’。

AND/OR 

1
2

SELECT column_name(s) FROM table_name WHERE column_1 = value_1 AND column_2 = value_2;
-- 这允许我们使用逻辑运算符组合多个条件。

LIKE 

1
2

SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;
-- 我们可以使用通配符（%表示任何字符串，_表示单个字符）与LIKE运算符执行模式搜索。

LIMIT 

1
2

SELECT column_name(s) FROM table_name LIMIT number;
-- 此子句限制返回的行数。

ORDER BY 

1
2

SELECT column_name FROM table_name ORDER BY column_name ASC | DESC;
-- 此子句根据指定表列按升序（ASC）或降序（DESC）排序结果。

GROUP BY 

1
2

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;
-- 此子句通常与聚合函数（如COUNT()）一起使用，根据指定列中的值对行进行分组。

HAVING 

1
2

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > value;
-- 此子句与GROUP BY一起使用以过滤分组结果。

INNER JOIN 

1
2

SELECT column_name(s) FROM table_1 JOIN table_2 ON table_1.column_name = table_2.column_name;
-- 此子句在连接条件为真时组合来自多个表的行。

OUTER JOIN 

1
2

SELECT column_name(s) FROM table_1 LEFT JOIN table_2 ON table_1.column_name = table_2.column_name;
-- 此子句从两个或多个表检索数据。这里，它组合了table_1的所有行和table_2的匹配行。如果table_2中没有匹配项，则使用NULL值。

AS 

1
2

SELECT column_name AS 'Alias' FROM table_name;
-- 此关键字使用临时列名显示结果。

WITH 

1
2

WITH temporary_name AS (SELECT  FROM table_name) SELECT  FROM temporary_name WHERE column_name operator value;
-- 此子句定义了一个可以在查询中引用的临时结果集。

聚合函数

我们还可以使用SELECT语句通过内置函数（如AVG()、SUM()、COUNT()等）从数据库提取和聚合数据。

AVG()

此函数从SQL语句中的选定列检索平均值。这里，AVG()计算学生表中marks列的平均值。

1

SELECT AVG(MARKS) as AVERAGE_SCORE from STUDENT;
SUM()

此函数从SQL语句中的选定列检索数字之和。这里，SUM()计算学生表中marks列的平均值。

1

SELECT SUM(MARKS) as TOTAL_MARKS from STUDENT;

SQL查询逻辑排序

基于上述SQL命令，每当检索表或表集时，都有一个逻辑顺序。下图显示了如何使用2个表基于多个SQL命令检索关系数据。

DCL（数据控制语言）命令

管理数据库中用户权利和权限的命令属于DCL。DCL命令通过授予或撤销用户特权来控制对数据库对象的访问，并控制用户对数据库不同部分的访问级别。

GRANT

此命令用于授予用户对数据库对象的特定特权。

1
2

GRANT SELECT, INSERT ON PERSONS TO admin; 
-- 这允许管理员在PERSONS表中选择和插入数据。

REVOKE

此命令用于撤销分配给用户的特权。

1
2

REVOKE INSERT ON PERSONS FROM admin; 
-- 这撤销了管理员对PERSONS表的插入权限。

TCL（事务控制语言）命令

TCL通过确保事务中的所有语句要么成功提交，要么都不应用来维护数据一致性。我们将TCL命令（如“COMMIT”和“ROLLBACK”）与DML命令（数据操作语言）结合使用。

COMMIT

此命令永久保存事务中所做的所有更改。

1
2
3
4
5

BEGIN TRANSACTION; 
UPDATE accounts SET balance = balance - 100 WHERE account_id = 123;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 456; 
COMMIT;  
-- 提交语句一起更新两个账户，确保数据一致。这确保事务数据无任何差异地推送。

ROLLBACK

此命令回滚自上次COMMIT或ROLLBACK以来事务中所做的所有更改。

1
2
3
4

BEGIN TRANSACTION; 
DELETE FROM accounts WHERE account_id = 555;
ROLLBACK;
-- 上述命令回滚删除，恢复账户。

SAVEPOINT

此命令定义一个事务点，可以在任何给定时间将表状态回滚到该点。

1
2
3
4
5
6

BEGIN TRANSACTION; 
UPDATE accounts SET balance = balance - 100 WHERE account_id = 123;
SAVEPOINT after_insert;
UPDATE accounts SET balance = balance + 50 WHERE account_id = 123;
ROLLBACK TO after_insert; 
-- 这在使用SAVEPOINT后回滚账户中的余额添加更新。

条件表达式

这些表达式向查询添加逻辑。可以使用IF、CASE和COALESCE语句编写条件表达式。

IF

此命令不是SQL命令，但可以在某些SQL方言（如MySQL、PostgreSQL等）中使用。它根据给定条件执行SQL语句：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- MySQL语法：
IF (Score > 50) THEN
    SELECT 'Pass' AS ExamStatus;
ELSE
    SELECT 'Fail' AS ExamStatus;
END IF;

-- PostgreSQL (PL/pgSQL) 语法：
IF Score > 50 THEN
    result := 'Pass';
ELSE
    result := 'Fail';
END IF;

重要说明：IF语句不能在常规SQL查询中使用。对于标准SQL查询中的条件逻辑，请改用CASE表达式。CASE被所有SQL数据库支持，并被认为是处理条件查询逻辑的标准方式。

CASE

此命令像编程语言中的if-else语句一样工作：

1
2
3
4
5
6

SELECT StudentID,
 CASE
           WHEN Score > 50 THEN 'Pass'
           ELSE 'Fail'
       END AS ExamStatus
FROM STUDENTS;

COALESCE

此SQL函数管理NULL值并返回第一个非NULL值。例如，如果我给它一个只有一个非NULL值的表达式列表，它将仅返回该值。在以下代码中，如果Score列有NULL值，此函数将其替换为零。

1
2
3

SELECT StudentID, 
       COALESCE(Score1, 0) AS FinalScore
FROM STUDENTS;

安全最佳实践

实践 优势
基于角色的访问控制 根据用户的访问需求负责任地分配用户角色
数据加密 加密敏感数据，如密码和银行卡详情
安全认证方法 使用OAuth 2.0防止未经授权的访问

SQL方言

以下是主要的SQL方言及其用途，它们被比较并用于各种数据库系统的开发目的。

方言 特性
PL/pgSQL (PostgreSQL) – 以高级功能（如JSON/JSONB支持、窗口函数和CTE）闻名。
– 支持全文搜索。
– 开源，具有广泛的社区支持。
– 支持各种堆栈，主要用于需要复杂查询和高性能分析的系统，如金融系统和数据仓库。
MySQL – 开源，广泛用于Web开发。
– 易于与Web技术（PHP、Python等）集成。
– 对高级分析功能支持有限。
– 主要用于电子商务平台。
TSQL (SQL Server) – 与Microsoft产品（如Azure）出色集成。
– 主要用于大规模应用。
– 提供高级调优选项。
– 可能是一个成本较高的选项，主要用于ERP系统。
PL/SQL (Oracle) – 为高容量应用设计。
– 出色的恢复和并发管理。
– 高成本，与Oracle生态系统最佳配合。
– 用于需要高可用性和可扩展性的行业。

SQL集成

SQL在与关系数据库交互中扮演关键角色，其与编程语言（如Python和JAVA）以及商业智能（BI）工具的集成增强了构建强大、数据驱动应用的能力。

假设数据库中有一个客户表，并且正在开发一个Python应用，该应用检索客户数据并使用它生成客户见解。以下是使用SQLite3或SQLalchemy库将SQL查询集成到Python中的方式。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

import sqlite3

# 连接到数据库
conn = sqlite3.connect('database.db')
cursor = conn.cursor()
# 构建查询
cursor.execute("SELECT * FROM CUSTOMERS")

# 获取所有数据

rows = cursor.fetchall()

结论

总之，SQL命令帮助我们有效管理数据库。用户可以从其类别和多种方言中选择以连接到数据库、执行操作并确保其数据完整性和安全。

关于SQL命令的常见问题

为什么我们应该使用SQL命令？ SQL命令用于与关系数据库通信以存储、检索和操作数据。

我可以在我的应用中使用SQL命令吗？ 我们可以使用各种内置库（如SQLalchemy）集成SQL。

SQL中的“DELETE”命令与“TRUNCATE”有何不同？ truncate命令删除所有表行，同时保持表结构完整。然而，delete命令根据查询中提供的某些用户条件或逻辑从表中移除数据。此外，已删除（DML命令）的对象可以回滚，而截断（DDL命令）的行被永久删除。

comments powered by Disqus
© 2020 - 2025 qife
使用 Hugo 构建
主题 Stack 由 Jimmy 设计
本站总访问量次 本站访客数人次