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

本文全面解析SQL命令的五种类型：数据定义语言(DDL)、数据操作语言(DML)、数据查询语言(DQL)、数据控制语言(DCL)和事务控制语言(TCL)，包含具体语法示例、安全实践建议以及不同SQL方言的比较，帮助开发者高效管理关系型数据库。

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;
-- 此命令忽略重复行或多个值，仅从指定列（如表中的名称列）返回唯一值

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 设计
本站总访问量次 本站访客数人次