sql-quick-guide

SQL：现代速查指南

SQL - 概述

结构化查询语言 (SQL) 是用于管理和操作关系型数据库中数据的标准语言。从创建数据库和表到获取和修改数据，SQL 是数据专业人员的基本工具。虽然 SQL 是一个 ANSI/ISO 标准，但大多数数据库系统都有自己的方言和特定的扩展。

什么是 SQL？

SQL 是一种声明性语言，旨在用于存储、操作和检索关系型数据库中的数据。与命令式语言（如 Python 或 Java）不同，在命令式语言中您描述 如何 做某事，而在 SQL 中您声明 想要 什么，数据库引擎会找出获取它的最有效方法。

所有主要的关系型数据库管理系统 (RDBMS) 都使用 SQL 作为其核心语言。这包括 PostgreSQL、MySQL、SQL Server、Oracle 和 SQLite 等系统。

常见的 SQL 方言包括：

• T-SQL (Transact-SQL)： 由 Microsoft SQL Server 使用。
• PL/pgSQL (Procedural Language/PostgreSQL)： 由 PostgreSQL 使用。
• PL/SQL (Procedural Language/SQL)： 由 Oracle 使用。

为什么 SQL 仍然至关重要？

在大数据和 NoSQL 时代，SQL 凭借其强大、简洁和无处不在的特性，仍然比以往任何时候都更加重要。

• 通用数据访问： 它是与结构化数据交互的标准，是数据分析、数据科学、后端开发和数据库管理所需的一项技能。
• 强大的数据操作： 允许使用简洁的语法进行复杂的数据定义、查询和修改。
• 现代数据栈的基础： 许多现代数据仓库（例如 Snowflake、BigQuery）和流处理系统（例如 Flink）都使用 SQL 或类似 SQL 的接口。
• 健壮且成熟： 几十年的发展使 SQL 数据库变得可靠、安全且性能卓越。

SQL 查询过程

当您执行 SQL 查询时，数据库引擎会执行一系列步骤来返回结果：

1. 1. 解析 (Parsing)： 引擎检查 SQL 语法是否正确。拼写是否正确？关键字顺序是否正确？
2. 2. 绑定（验证）(Binding (Validation))： 它验证查询中提到的表和列确实存在，并且您拥有访问它们的权限。
3. 3. 优化 (Optimization)： 这是“神奇”的步骤。查询优化器分析您的查询并评估许多不同的执行方式（例如，使用哪个索引，哪种连接算法最好）。然后它选择最有效的执行计划。
4. 4. 执行 (Execution)： 引擎运行选定的执行计划，从存储中检索数据，执行连接、过滤、排序，最后将结果集返回给您。

SQL 命令分类

SQL 命令通常根据其功能分组为子语言：

DDL - 数据定义语言

用于定义或修改数据库结构。

命令	描述
CREATE	创建数据库对象，如表、视图和索引。
ALTER	修改现有对象的结构，例如向表中添加列。
DROP	删除整个数据库对象。
TRUNCATE	快速删除表中的所有记录。

DML - 数据操作语言

用于管理对象中的数据。

命令	描述
SELECT	从一个或多个表中检索数据。
INSERT	向表中添加新行数据。
UPDATE	修改表中的现有记录。
DELETE	从表中删除记录。

DCL - 数据控制语言

用于管理用户访问和权限。

命令	描述
GRANT	授予用户对数据库对象的特定权限（例如，SELECT、INSERT）。
REVOKE	撤销用户的权限。

TCL - 事务控制语言

用于管理事务以确保数据完整性。

命令	描述
COMMIT	保存当前事务中完成的所有工作。
ROLLBACK	撤销当前事务中完成的所有工作。
SAVEPOINT	在事务中设置一个点，您可以在以后回滚到该点。

SQL - RDBMS 概念

什么是 RDBMS？

关系型数据库管理系统 (RDBMS) 是一种用于创建、管理和管理关系型数据库的软件。它基于关系模型，将数据组织成列和行的表（或“关系”）。现代 RDBMS，如 PostgreSQL、MySQL 和 SQL Server，是无数应用程序的基础。

核心构建块

RDBMS 中的数据使用一些关键概念进行结构化：

• 表 (Table)： 相关数据条目的集合，组织成列和行。例如，一个 Users 表将存储所有用户的信息。
• 列（或字段）(Column (or Field))： 表中的垂直实体，包含每个记录的特定属性。在 Users 表中，您将有 UserID、Username 和 Email 等列。
• 行（或记录）(Row (or Record))： 表中的水平实体，表示单个条目。Users 表中的一行代表一个特定的用户。
• NULL 值 (NULL Value)： 一个特殊的标记，用于指示数据库中不存在数据值。重要的是要理解 NULL 与零或空字符串不同。它表示值的缺失。这导致了比较中的三值逻辑：TRUE（真）、FALSE（假）和 UNKNOWN（未知）。

SQL 约束

约束是强制执行在数据列上的规则，以确保数据的准确性、可靠性和完整性。

• NOT NULL： 确保列不能有 NULL 值。
• UNIQUE： 确保列（或一组列）中的所有值都是唯一的。
• PRIMARY KEY（主键）： NOT NULL 和 UNIQUE 的组合。它唯一标识表中的每一行。一个表只能有一个主键。
• FOREIGN KEY（外键）： 用于连接两个表的键。它是一个表中的字段（或字段集合），引用另一个表中的 PRIMARY KEY。这强制执行引用完整性。
• CHECK： 确保列中的值满足特定条件（例如，Price > 0）。
• DEFAULT： 当未指定值时，为列提供一个默认值。

数据库规范化

规范化是组织关系型数据库中的列和表的过程，以最小化数据冗余并提高数据完整性。它涉及将大型表划分为更小、结构良好的表，并在它们之间定义关系。

主要目标是：

• 消除冗余： 将相同的数据只存储在一个地方。
• 确保逻辑数据依赖： 确保相关数据存储在一起。

规范化通常以“范式”的形式进行讨论。对于大多数应用程序，实现第三范式 (3NF) 是一个不错的选择：

• 第一范式 (1NF)： 确保表具有主键，并且每个单元格包含一个单一的原子值（没有列表或重复组）。
• 第二范式 (2NF)： 必须符合 1NF。所有非键属性必须完全依赖于整个主键（与复合主键相关）。
• 第三范式 (3NF)： 必须符合 2NF。所有属性必须只依赖于主键，而不依赖于其他非键属性。

现代 RDBMS 一览

虽然存在数十种关系型数据库管理系统 (RDBMS)，但有少数几种占据主导地位。以下是当今最受欢迎的一些选择的简要概述。

PostgreSQL

通常被称为 ‘Postgres’，它是一个强大、开源的对象关系型数据库系统，以其对 SQL 标准的严格遵循、可靠性和丰富的功能集而闻名。它是创业公司和复杂数据密集型应用的首选。

主要特点

• 高级数据类型： 对 JSONB（一种二进制 JSON 格式）、数组和几何数据提供出色的支持。
• 可扩展性： 用户可以定义自己的数据类型、函数和运算符。
• 并发性： 健壮的多版本并发控制 (MVCC) 机制，可处理大量并发读写操作。
• 开源： 拥有一个充满活力的社区和宽松的许可证。

MySQL

作为世界上最受欢迎的开源数据库，MySQL 是 Web 领域的基石，它以著名的 LAMP（Linux、Apache、MySQL、PHP/Python/Perl）技术栈而闻名。它以易用性、速度和可靠性著称。现由 Oracle 公司拥有。

主要特点

• 高性能： 非常适合读密集型工作负载，使其成为许多 Web 应用程序的理想选择。
• 易用性： 设置和管理简单。
• 庞大的生态系统： 受到托管服务提供商、工具和框架的广泛支持。
• 存储引擎： 支持不同的存储引擎（如 InnoDB 和 MyISAM），适用于不同的用例。

Microsoft SQL Server

微软出品的全面企业级关系型数据库管理系统 (RDBMS)。传统上是 Windows 独占产品，现在已支持 Linux 和 Docker，扩大了其影响力。它在企业环境中，特别是在投资于微软生态系统的企业中，扮演着主导角色。

主要特点

• 深度集成： 与 .NET、Azure 和 Power BI 等其他 Microsoft 产品无缝集成。
• 商业智能： 强大的内置工具，用于报告、分析和机器学习。
• 性能和安全性： 以其高性能和健壮的安全功能而闻名。
• T-SQL： 一种强大且功能丰富的 SQL 方言。

SQLite

SQLite 独一无二：它不是一个客户端-服务器数据库引擎。相反，它是一个进程内库，实现了自包含、无服务器、零配置的事务性 SQL 数据库引擎。它是世界上部署最广泛的数据库引擎，被无数移动应用、网络浏览器和嵌入式设备使用。

主要特点

• 无服务器： 整个数据库（定义、表、索引和数据）存储在主机上的单个文件中。
• 零配置： 无需安装或管理。
• 轻量级： 占用空间极小，非常适合资源受限的环境。
• 可靠： 完全兼容 ACID 特性，是本地数据存储的可靠选择。

Oracle Database

由 Oracle Corporation 生产和销售的多模型数据库管理系统。它在企业市场中占据主导地位，以其可扩展性、性能和庞大的功能集而闻名，特别是在大规模事务处理和数据仓库方面表现出色。

主要特点

• 可扩展性： 诸如 Real Application Clusters (RAC)（真实应用集群）之类的功能实现了高可用性和可扩展性。
• 健壮性： 以其稳定性和管理任务关键型应用程序的全面工具集而闻名。
• 功能丰富： 包含了用于数据仓库、安全性和高可用性的广泛功能。

SQL - 语法

SQL 遵循一套特定的规则和准则，称为语法。虽然大多数命令在数据库之间是标准的，但某些函数和数据类型可能会有所不同。本节提供了最常见 SQL 语句的快速参考。

需要记住的关键点：SQL 关键字通常不区分大小写（SELECT 与 select 相同），但表名和列名通常区分大小写，尤其是在基于 Linux 的系统上。语句以分号 (;) 结尾，某些工具要求这样做，并且在所有情况下都是良好的实践。

常用 SQL 语句语法

这些示例使用标准 SQL 语法。具体的实现可能略有不同。

数据查询语言 (DQL)

-- 从表中选择所有列
SELECT * FROM table_name;

-- 选择特定列
SELECT column1, column2 FROM table_name;

-- 使用 WHERE 子句过滤行
SELECT column1, column2 FROM table_name WHERE condition;

-- 从结果中删除重复行
SELECT DISTINCT column1 FROM table_name;

-- 排序结果
SELECT column1, column2 FROM table_name ORDER BY column1 ASC, column2 DESC;

-- 限制返回的行数 (标准 SQL)
SELECT column1, column2 FROM table_name FETCH FIRST 10 ROWS ONLY;
-- (MySQL/PostgreSQL 中为 LIMIT 10, SQL Server 中为 SELECT TOP 10)

-- 分组行并使用聚合函数
SELECT column1, COUNT(*) FROM table_name GROUP BY column1;

-- 使用 HAVING 子句过滤组
SELECT column1, COUNT(*) FROM table_name GROUP BY column1 HAVING COUNT(*) > 5;

数据操作语言 (DML)

-- 插入新行
INSERT INTO table_name (column1, column2) VALUES (value1, value2);

-- 更新现有行
UPDATE table_name SET column1 = new_value1 WHERE condition;

-- 删除行
DELETE FROM table_name WHERE condition;

数据定义语言 (DDL)

-- 创建新表
CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    PRIMARY KEY (column1)
);

-- 向现有表添加新列
ALTER TABLE table_name ADD COLUMN new_column_name datatype;

-- 修改现有列
ALTER TABLE table_name ALTER COLUMN column_name TYPE new_datatype;

-- 删除现有表
DROP TABLE table_name;

-- 创建索引以提高查询性能
CREATE INDEX index_name ON table_name (column_to_index);

SQL - 数据类型

数据类型是一个属性，它指定列可以存储的数据类型，例如整数数据、字符数据或日期和时间数据。选择正确的数据类型对于数据完整性和性能至关重要。虽然许多数据类型是标准化的，但不同的关系型数据库管理系统 (RDBMS) 之间存在差异。

常见 SQL 数据类型

数值类型

用于存储数值数据。

数据类型	描述
INTEGER 或 INT	一个标准的整数。典型范围是 -2,147,483,648 到 2,147,483,647。
BIGINT	一个大的整数，用于超出 INTEGER 范围的值。
SMALLINT	一个小的整数。当已知值较小时，通常用于优化。
DECIMAL(p, s) 或 NUMERIC(p, s)	精确的定点数。p 是总位数（精度），s 是小数点后的位数（小数位数）。非常适合财务数据。
FLOAT(p), REAL	近似浮点数。用于不需要精确精度的科学计算。

字符（串）类型

用于存储文本数据。

数据类型	描述
CHAR(n)	长度为 n 个字符的定长字符串。如果字符串短于 n，则用空格填充。用于固定长度的代码，如国家代码 (‘US’, ‘DE’)。
VARCHAR(n)	最大长度为 n 个字符的变长字符串。仅使用实际字符串所需的空间。文本最常见的选择。
TEXT	用于存储大量文本的变长字符串。最大长度取决于关系型数据库管理系统 (RDBMS)，但通常非常大。

关于 Unicode 的说明： 现代数据库默认或通过排序规则设置处理 Unicode 编码（如 UTF-8）。SQL Server 具有针对 Unicode 数据的特定类型，如 NVARCHAR 和 NCHAR。

日期和时间类型

用于存储时间信息。

数据类型	描述
DATE	存储日期（年、月、日）。
TIME	存储时间（小时、分钟、秒）。
TIMESTAMP	同时存储日期和时间。TIMESTAMP WITH TIME ZONE（或 TIMESTAMPTZ）是推荐的类型，因为它存储时区信息，防止歧义。

布尔类型

用于存储真/假值。

数据类型	描述
BOOLEAN	存储 TRUE、FALSE 或 NULL。一些数据库（如 MySQL）使用 TINYINT(1) 作为它的别名。

现代和专用类型

现代关系型数据库管理系统 (RDBMS) 支持更复杂的数据结构。

数据类型	描述
JSON / JSONB	存储 JSON（JavaScript 对象表示法）数据。JSONB（在 PostgreSQL 中）是一种二进制、可索引的格式，对于查询非常高效。
UUID	存储通用唯一标识符 (UUID)。非常适合分布式系统中的主键，以避免冲突。
XML	存储 XML 数据。现在不如 JSON 常见，但在某些企业系统中仍在使用。
ARRAY	存储另一种类型的数组值（例如，INTEGER[]）。PostgreSQL 和其他一些系统支持。

SQL - 运算符

什么是运算符？

运算符是主要用于 SQL WHERE 子句中的保留字或字符，用于执行比较和算术等操作。运算符用于指定条件和连接语句中的多个条件。

SQL 算术运算符

对数值数据执行数学运算。

运算符	描述	示例
+ (加法)	将两个数值相加。	Price + Tax 将给出总成本。
- (减法)	从左操作数中减去右操作数。	Salary - Deduction 将给出净工资。
* (乘法)	将两个数值相乘。	Quantity * UnitPrice 将给出总价。
/ (除法)	将左操作数除以右操作数。	TotalSales / NumberOfUnits 将给出平均价格。
% (取模)	返回除法的整数余数。	10 % 3 将给出 1。

SQL 比较运算符

用于比较两个表达式。结果是一个布尔值（TRUE、FALSE 或 UNKNOWN）。

运算符	描述	示例
=	检查两个值是否相等。	Country = 'USA'
<> 或 !=	检查两个值是否不相等。<> 是 SQL 标准。	Status <> 'Completed'
>	检查左值是否大于右值。	Price > 100.00
<	检查左值是否小于右值。	StockLevel < 10
>=	检查左值是否大于或等于右值。	Age >= 18
<=	检查左值是否小于或等于右值。	Discount <= 0.5

SQL 逻辑运算符

用于组合 WHERE 子句中的多个条件。

运算符	描述
ALL	如果所有子查询值都满足条件，则返回 true。
AND	如果所有连接的条件都为真，则返回 true。
ANY / SOME	如果任何子查询值满足条件，则返回 true。
BETWEEN	如果值在给定范围（包含边界值）内，则返回 true。age BETWEEN 18 AND 30。
EXISTS	如果子查询返回一个或多个记录，则返回 true。
IN	如果值与列表中的任何值匹配，则返回 true。Country IN ('USA', 'Canada', 'Mexico')。
LIKE	用于字符串中的模式匹配，支持通配符（% 表示多个字符，_ 表示单个字符）。
NOT	反转任何其他逻辑运算符的结果（例如，NOT IN、NOT BETWEEN、NOT EXISTS）。
OR	如果任何连接的条件为真，则返回 true。
IS NULL	检查值是否为 NULL。
IS NOT NULL	检查值是否不为 NULL。

SQL - 表达式

SQL 表达式是值、运算符和 SQL 函数的组合，其评估结果为单个值。表达式在 SQL 中广泛使用，从 SELECT 列表到 WHERE 和 HAVING 子句。

语法

表达式可以在查询的许多部分中使用。例如，在 SELECT 列表和 WHERE 子句中：

SELECT expression1, expression2, ...
FROM table_name
WHERE condition_expression;

SQL 表达式有几种类型：

数值表达式

这些表达式执行数学计算并返回数值。

-- 简单的计算与别名
SELECT (Price * 1.08) AS PriceWithTax FROM Products;

-- 使用聚合函数
SELECT AVG(Salary) AS AverageSalary FROM Employees;

-- 计算满足条件的行数
SELECT COUNT(*) FROM Orders WHERE Status = 'Shipped';

字符串表达式

这些表达式操作字符串数据。

-- 连接字符串 (标准 SQL 使用 ||)
SELECT FirstName || ' ' || LastName AS FullName FROM Users;

-- 在 SQL Server 中，您可能使用 +
-- SELECT FirstName + ' ' + LastName AS FullName FROM Users;

-- 使用字符串函数
SELECT UPPER(ProductName) FROM Products;

日期表达式

这些表达式返回日期/时间值或对其执行计算。

-- 获取当前时间戳 (标准 SQL)
SELECT CURRENT_TIMESTAMP;

-- 从日期中提取一部分（例如，年份）
SELECT EXTRACT(YEAR FROM OrderDate) AS OrderYear FROM Orders;

-- 日期算术
SELECT OrderDate + INTERVAL '30 days' AS DueDate FROM Orders;

条件表达式 (CASE)

CASE 表达式是在 SQL 查询中实现 if-then-else 逻辑的强大工具。它允许您根据指定条件返回不同的值。

SELECT OrderID, TotalAmount,
       CASE
           WHEN TotalAmount > 1000 THEN 'High Value'
           WHEN TotalAmount > 500  THEN 'Medium Value'
           ELSE 'Low Value'
       END AS OrderCategory
FROM Orders;

SQL - CREATE Database

CREATE DATABASE 语句用于在您的关系型数据库管理系统 (RDBMS) 中创建一个新数据库。这通常是您创建表和存储数据之前的第一个步骤。

语法

CREATE DATABASE 的基本语法很简单：

CREATE DATABASE DatabaseName;

数据库名称在关系型数据库管理系统 (RDBMS) 实例中必须是唯一的。高级选项允许指定字符集、排序规则和所有者。

-- 更高级的示例 (PostgreSQL)
CREATE DATABASE my_app_db
    WITH
    OWNER = app_user
    ENCODING = 'UTF8'
    CONNECTION LIMIT = 100;

示例

要创建一个名为 my_store 的新数据库，您将运行：

CREATE DATABASE my_store;

您必须拥有适当的管理权限才能创建数据库。创建后，您通常可以通过列出所有可用数据库来查看它。

-- 在 MySQL 客户端中
SHOW DATABASES;

-- 在 PostgreSQL 的 psql 客户端中
\l

输出将列出 my_store 以及其他数据库。

数据库 vs. 模式 (Schemas)

注意术语上的区别很重要。在像 MySQL 这样的系统中，您通常会创建多个数据库来分离应用程序。而在 PostgreSQL 和 Oracle 中，更常见的是为一个环境（例如 ‘development’）拥有一个单一的数据库，并在该数据库中使用模式 (schemas) 来分离应用程序。模式 (schema) 是一个包含表、视图和其他对象的命名空间。

SQL - DROP Database

DROP DATABASE 语句用于永久删除现有数据库，包括其所有表、数据和其他对象。

语法

DROP DATABASE 的基本语法如下：

DROP DATABASE DatabaseName;

为避免在数据库不存在时报错，许多关系型数据库管理系统 (RDBMS) 支持 IF EXISTS 子句：

DROP DATABASE IF EXISTS DatabaseName;

示例

要删除我们之前创建的名为 my_store 的数据库：

DROP DATABASE my_store;

警告：这是一个不可逆操作

使用此命令时请极其谨慎。 删除数据库是一个永久性操作。该数据库中的所有数据、表、索引、视图和存储过程将永远丢失。在删除生产数据库之前，务必确保您拥有可靠的最新备份。您还必须拥有执行此操作的管理权限。

命令成功执行后，通过 SHOW DATABASES (MySQL) 或 \l (PostgreSQL) 进行验证将显示数据库不再在列表中。

SQL - 选择和使用数据库

当使用包含多个数据库的关系型数据库管理系统 (RDBMS) 时，您必须指定当前会话要使用的数据库。随后的命令，如 CREATE TABLE 或 SELECT，将在所选数据库的上下文中执行。

USE 语句 (MySQL)

在 MySQL 和其他一些系统（如 SQL Server）中，USE 语句选择一个数据库作为当前会话的默认数据库。

语法

USE DatabaseName;

示例

首先，列出可用的数据库以查看您的选项：

-- 在 MySQL 客户端中
SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| my_store           |
+--------------------+

要开始使用 my_store 数据库，您执行：

USE my_store;
-- 输出：数据库已更改

现在，任何 SELECT * FROM products; 查询都将在 my_store 数据库中查找 products 表。

\c 或 \connect 命令 (PostgreSQL)

在 PostgreSQL 中，您在启动 psql 客户端时会连接到特定的数据库。要在同一会话中切换到不同的数据库，您可以使用 \c 或 \connect 元命令。

语法

\c DatabaseName
-- 或者
\connect DatabaseName

示例

如果您当前连接到默认的 postgres 数据库，并想切换到 my_store：

postgres=# \c my_store
-- 输出：您现在已以用户 "your_user" 连接到数据库 "my_store"。

完全限定名

作为切换默认数据库的替代方案，您始终可以使用表的完全限定名来引用它，即 database_name.schema_name.table_name。这对于需要跨不同数据库连接数据的查询可能很有用（如果您的关系型数据库管理系统 (RDBMS) 支持）。

-- MySQL 示例
SELECT u.username, p.product_name
FROM my_app.users u
JOIN my_store.products p ON u.id = p.created_by;

SQL - CREATE TABLE

CREATE TABLE 语句用于在您的数据库中创建一个新表。这是一个基本的 DDL 操作，您在此操作中定义表的名称、其列以及每列的数据类型和约束。

语法

CREATE TABLE 语句的基本语法如下：

CREATE TABLE table_name (
   column1_name data_type [column_constraints],
   column2_name data_type [column_constraints],
   ...
   [table_constraints]
);

关键组成部分：

• table_name：新表的唯一名称。
• column_name：每列的名称。
• data_type：列将存储的数据类型（例如，INT、VARCHAR、TIMESTAMP）。
• column_constraints：应用于单个列的规则（例如，NOT NULL、UNIQUE、DEFAULT）。
• table_constraints：应用于一个或多个列的规则（例如，PRIMARY KEY、FOREIGN KEY、CHECK）。

示例：创建 Users 表

让我们创建一个结构良好的 Users 表，使用现代数据类型和约束。此示例尽可能使用标准 SQL 功能。

CREATE TABLE Users (
    UserID INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- 标准标识列和主键约束
    Username VARCHAR(50) NOT NULL UNIQUE,                 -- 必须提供且唯一
    Email VARCHAR(255) NOT NULL UNIQUE,                   -- 必须提供且唯一
    PasswordHash CHAR(60) NOT NULL,                       -- 用于存储哈希密码（例如，来自 bcrypt）
    IsActive BOOLEAN DEFAULT TRUE,                        -- 一个带有默认值的布尔标志
    CreatedAt TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, -- 自动记录创建时间

    -- 表级约束
    CHECK (length(Username) >= 3)
);

方言特定说明：

• MySQL： 将使用 INT AUTO_INCREMENT PRIMARY KEY 而不是 GENERATED BY DEFAULT AS IDENTITY。
• PostgreSQL： 通常使用 SERIAL PRIMARY KEY 作为标识列的简写。
• SQL Server： 将使用 INT IDENTITY(1,1) PRIMARY KEY。

验证表创建

执行 CREATE TABLE 语句后，大多数数据库客户端将确认其成功。您还可以使用特定命令检查表的结构：

-- 在 MySQL 客户端中
DESCRIBE Users;

-- 在 PostgreSQL 的 psql 客户端中
\d Users

获取表元数据的一种更标准的方法是查询 INFORMATION_SCHEMA，它在大多数关系型数据库管理系统 (RDBMS) 中都可用：

SELECT column_name, data_type, is_nullable
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'users';

SQL - DROP 或 DELETE 表

DROP TABLE 语句是 DDL 命令，用于永久删除表，包括其结构、数据、索引、触发器和约束。

语法

DROP TABLE 的基本语法非常简单：

DROP TABLE table_name;

为了防止表不存在时出错，最佳实践是使用 IF EXISTS 子句，大多数现代关系型数据库管理系统 (RDBMS) 都支持该子句。

DROP TABLE IF EXISTS table_name;

一些系统，如 PostgreSQL，还支持 CASCADE 选项，以自动删除依赖于表的任何对象（如视图或外键约束）。

-- PostgreSQL 示例
DROP TABLE IF EXISTS table_name CASCADE;

示例

假设我们有一个 Users 表，并且我们想将其从数据库中删除。

首先，我们可以验证它的存在：

-- 在 PostgreSQL 的 psql 客户端中
\dt

-- 列出表，应出现 'users'。

现在，我们执行 DROP TABLE 命令：

DROP TABLE Users;

命令执行后，尝试描述或从表中进行选择将导致错误，因为该表不再存在。

SELECT * FROM Users;

-- 错误：关系“users”不存在

关键警告

DROP TABLE 是一个强大且不可逆的操作。一旦表被删除，其所有数据和结构都将永久消失。没有简单的“撤销”命令。在生产环境中，请务必仔细检查您正在删除的表，并确保您有可靠的备份。

SQL - INSERT INTO 语句

INSERT INTO 语句是 DML 命令，用于向表中添加新行数据。

语法

使用 INSERT INTO 语句主要有两种方式。

1. 指定列名

这是最常用和推荐的语法，因为它明确且不受列顺序更改的影响。

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

2. 不指定列名

如果您为表中的所有列以正确顺序提供值，则可以省略列列表。这种做法很脆弱，不推荐用于生产代码。

INSERT INTO table_name VALUES (value1, value2, value3, ...);

插入多行

大多数现代关系型数据库管理系统 (RDBMS) 允许您在单个语句中插入多行，以提高效率。

INSERT INTO table_name (column1, column2)
VALUES
    (value1a, value2a),
    (value1b, value2b),
    (value1c, value2c);

示例

让我们使用我们的 Users 表并插入几条记录。请注意，我们不需要为 UserID 提供值（它是自动生成的），也不需要为 CreatedAt 和 IsActive 提供值（它们有 DEFAULT 默认值）。

-- 插入单个用户
INSERT INTO Users (Username, Email, PasswordHash)
VALUES ('alice', 'alice@example.com', 'some_bcrypt_hash_string_1');

-- 一次性插入多个用户
INSERT INTO Users (Username, Email, PasswordHash, IsActive)
VALUES
    ('bob', 'bob@example.com', 'some_bcrypt_hash_string_2', TRUE),
    ('charlie', 'charlie@example.com', 'some_bcrypt_hash_string_3', FALSE);

在这些语句之后，Users 表将包含：

SELECT UserID, Username, Email, IsActive, CreatedAt FROM Users;

+--------+----------+-----------------------+----------+-------------------------------+
| UserID | Username | Email                 | IsActive | CreatedAt                     |
+--------+----------+-----------------------+----------+-------------------------------+
|      1 | alice    | alice@example.com     | true     | 2023-10-27 10:00:00.123456+00 |
|      2 | bob      | bob@example.com       | true     | 2023-10-27 10:00:01.654321+00 |
|      3 | charlie  | charlie@example.com   | false    | 2023-10-27 10:00:01.654321+00 |
+--------+----------+-----------------------+----------+-------------------------------+

从另一个表填充表

您可以使用 SELECT 语句为 INSERT 语句提供数据。这对于复制或归档数据非常有用。

语法：

INSERT INTO destination_table (column1, column2, ...)
SELECT columnA, columnB, ...
FROM source_table
WHERE condition;

此查询获取 SELECT 语句的结果并将其直接插入到 destination_table 中。

SQL - SELECT 查询

SELECT 语句是 SQL 中最常用的命令。它的目的是从一个或多个数据库表中检索数据。返回的数据存储在一个结果表（也称为结果集）中。

语法

SELECT 语句的基本语法是：

SELECT column1, column2, ...
FROM table_name;

这里，column1、column2 等是您想从中检索数据的表中的列名。

要不单独列出所有可用列就获取它们，您可以使用星号 (*) 通配符。虽然这对于探索很方便，但由于它可能损害性能并降低代码的可预测性，因此不建议用于生产代码。

SELECT *
FROM table_name;

示例

让我们使用 Customers 表作为示例。假设它有以下记录：

+----+----------+-----+-------------+----------+
| ID | Name     | Age | City        | Salary   |
+----+----------+-----+-------------+----------+
|  1 | Ramesh   |  32 | Ahmedabad   |  2000.00 |
|  2 | Khilan   |  25 | Delhi       |  1500.00 |
|  3 | Kaushik  |  23 | Kota        |  2000.00 |
|  4 | Chaitali |  25 | Mumbai      |  6500.00 |
|  5 | Hardik   |  27 | Bhopal      |  8500.00 |
|  6 | Komal    |  22 | Hyderabad   |  4500.00 |
|  7 | Muffy    |  24 | Indore      | 10000.00 |
+----+----------+-----+-------------+----------+

以下查询仅获取所有客户的 ID、Name 和 Salary。

SELECT ID, Name, Salary
FROM Customers;

这将产生以下结果集：

+----+----------+----------+
| ID | Name     | Salary   |
+----+----------+----------+
|  1 | Ramesh   |  2000.00 |
|  2 | Khilan   |  1500.00 |
|  3 | Kaushik  |  2000.00 |
|  4 | Chaitali |  6500.00 |
|  5 | Hardik   |  8500.00 |
|  6 | Komal    |  4500.00 |
|  7 | Muffy    | 10000.00 |
+----+----------+----------+

要获取所有客户的所有列，您将使用：

SELECT * FROM Customers;

这将返回如初始示例中所示的整个表。

最佳实践：在应用程序中避免 SELECT *

在应用程序代码中，始终指定您需要的确切列。原因有以下几点：

• 性能： 请求不必要的列（特别是大型 TEXT 或 BLOB 二进制大对象字段）会增加网络流量和数据库负载。
• 可读性： 明确列出列使您的查询意图对其他开发人员清晰明了。
• 稳定性： 如果底层表结构发生变化（例如，添加或删除了列），SELECT * 可能会破坏您的应用程序代码，而明确的列列表则不会。

SQL - WHERE 子句

WHERE 子句用于筛选记录。它只提取满足指定条件的记录。这是 SQL 最关键的部分之一，允许您将大量数据缩小到仅您所需的信息。

WHERE 子句不仅与 SELECT 语句一起使用，还与 UPDATE 和 DELETE 语句一起使用，以指定要修改或删除的行。

语法

带有 WHERE 子句的 SELECT 语句的基本语法是：

SELECT column1, column2, ...
FROM table_name
WHERE condition;

condition 是一个表达式，对每一行评估为真、假或未知。您可以使用各种比较（=、>、<）和逻辑（AND、OR、NOT）运算符来构建您的条件。

示例

让我们再次使用 Customers 表：

+----+----------+-----+-------------+----------+
| ID | Name     | Age | City        | Salary   |
+----+----------+-----+-------------+----------+
|  1 | Ramesh   |  32 | Ahmedabad   |  2000.00 |
|  2 | Khilan   |  25 | Delhi       |  1500.00 |
|  3 | Kaushik  |  23 | Kota        |  2000.00 |
|  4 | Chaitali |  25 | Mumbai      |  6500.00 |
|  5 | Hardik   |  27 | Bhopal      |  8500.00 |
|  6 | Komal    |  22 | Hyderabad   |  4500.00 |
|  7 | Muffy    |  24 | Indore      | 10000.00 |
+----+----------+-----+-------------+----------+

以下查询检索薪资大于 2000 的客户的 ID、Name 和 Salary：

SELECT ID, Name, Salary
FROM Customers
WHERE Salary > 2000;

这将产生以下结果：

+----+----------+----------+
| ID | Name     | Salary   |
+----+----------+----------+
|  4 | Chaitali |  6500.00 |
|  5 | Hardik   |  8500.00 |
|  6 | Komal    |  4500.00 |
|  7 | Muffy    | 10000.00 |
+----+----------+----------+

下一个查询获取具有特定姓名的客户的详细信息。请注意，字符串字面量必须用单引号 (') 括起来。

SELECT ID, Name, Salary
FROM Customers
WHERE Name = 'Hardik';

这将产生以下结果：

+----+--------+---------+
| ID | Name   | Salary  |
+----+--------+---------+
|  5 | Hardik | 8500.00 |
+----+--------+---------+

SQL - AND、OR 和 NOT 运算符

AND、OR 和 NOT 运算符用于 WHERE 子句中，以组合或否定多个条件，从而实现更复杂的数据筛选。

AND 运算符

如果由 AND 分隔的所有条件都为 TRUE，则 AND 运算符显示一条记录。

语法

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND ...;

示例

让我们从 Customers 表中查找所有年龄在 25 岁以下且薪资大于 2000 的客户。

SELECT ID, Name, Salary, Age
FROM Customers
WHERE Age < 25 AND Salary > 2000;

根据我们的样本数据，这将产生以下结果：

+----+-------+----------+-----+
| ID | Name  | Salary   | Age |
+----+-------+----------+-----+
|  6 | Komal |  4500.00 |  22 |
|  7 | Muffy | 10000.00 |  24 |
+----+-------+----------+-----+

OR 运算符

如果由 OR 分隔的条件中任意一个为 TRUE，则 OR 运算符显示一条记录。

语法

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR ...;

示例

让我们查找所有来自 ‘Delhi’ 或薪资小于 2000 的客户。

SELECT ID, Name, City, Salary
FROM Customers
WHERE City = 'Delhi' OR Salary < 2000;

这将产生以下结果：

+----+--------+-------+---------+
| ID | Name   | City  | Salary  |
+----+--------+-------+---------+
|  2 | Khilan | Delhi | 1500.00 |
+----+--------+-------+---------+

NOT 运算符

如果条件不为 TRUE，则 NOT 运算符显示一条记录。它可用于否定表达式。

语法

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

示例

让我们查找所有不来自 ‘Mumbai’ 的客户。

SELECT ID, Name, City
FROM Customers
WHERE NOT City = 'Mumbai';

这等效于使用 <> 或 != 运算符。

组合运算符

您可以组合 AND、OR 和 NOT 运算符。在组合时，请使用括号 () 来确保条件以正确的顺序进行评估，因为 AND 具有比 OR 更高的优先级。

例如，要查找来自 ‘Mumbai’ 并且（年龄大于 25 岁或薪资超过 8000）的客户：

SELECT *
FROM Customers
WHERE City = 'Mumbai' AND (Age > 25 OR Salary > 8000);

如果没有括号，查询将被解释为 (City = 'Mumbai' AND Age > 25) OR Salary > 8000，这将产生不同的结果。