在数据库管理中，SQL（Structured Query Language）是操作关系型数据库的核心工具。本文将深入探讨MySQL中的SQL基础知识，从数据库类型选择、设计范式、连接查询、数据插入、字符串类型、外键约束、关键字使用、内置函数、查询执行顺序、请求执行过程到存储引擎等多个方面进行详细解析。

SQL与NoSQL数据库

概述

SQL数据库

SQL（Structured Query Language）代表关系型数据库管理系统（RDBMS），常见的代表包括SQL Server、Oracle、MySQL和PostgreSQL。关系型数据库以结构化方式存储数据，数据逻辑通过二维表（即行和列）来表示。每一列代表数据的属性，每一行则代表一个数据实体。

NoSQL数据库

NoSQL（Not Only SQL）代表非关系型数据库，主要代表有MongoDB和Redis。NoSQL数据库不使用传统的二维表结构，而是采用如JSON文档、键值对、列族、图等多种数据模型来存储数据。

选择数据库的考量因素

ACID与BASE

关系型数据库通常支持ACID特性（原子性、一致性、隔离性、持久性），确保数据的强一致性和事务的完整性。而NoSQL数据库通常采用BASE模型（基本可用性、软状态、最终一致性），提供更高的灵活性和可扩展性，但可能在一致性方面有所妥协。

选择数据库时需根据具体应用场景来决定。例如，银行系统需要严格遵守ACID特性以防止资金重复使用，而社交软件则可以容忍一定程度的延迟和数据不一致。

扩展性

NoSQL数据库由于其非关系型数据结构，数据之间通常不存在强关联，因此更容易实现水平扩展。例如，Redis提供了主从复制、哨兵模式和切片集群等扩展机制。

相比之下，SQL数据库中的数据可能存在复杂的关联关系，扩展时需要解决分布式事务等复杂问题。

数据库设计三大范式

第一范式（1NF）

第一范式要求数据库表中的每一列都必须是不可再分的原子项。这意味着每一列中的数据项必须是单一的、不可分割的值。

举例说明

假设有一张表存储用户信息，其中包含“联系方式”列，该列包含电话和电子邮件。为了满足1NF，应将“联系方式”拆分为“电话”和“电子邮件”两列。

不满足1NF的表结构

满足1NF的表结构

第二范式（2NF）

第二范式在满足1NF的基础上，要求非主属性必须完全依赖于候选码（即主键）。换句话说，非主属性不能部分依赖于主键的一部分。

举例说明

假设有一张订单表，主键为“订单ID”和“产品ID”。如果表中有一列“产品名称”，它只依赖于“产品ID”，而不依赖于“订单ID”，则该表不满足2NF。应将“产品名称”移到另一张产品表中。

不满足2NF的表结构

满足2NF的表结构

订单表

产品表

第三范式（3NF）

第三范式在满足2NF的基础上，要求非主属性不依赖于其他的非主属性（即消除传递依赖）。

举例说明

假设有一张员工表，包含“员工ID”、“员工姓名”、“部门ID”和“部门名称”。如果“部门名称”依赖于“部门ID”，而“部门ID”依赖于“员工ID”，则存在传递依赖，不满足3NF。应将“部门名称”移到另一张部门表中。

不满足3NF的表结构

满足3NF的表结构

员工表

部门表

数据库连接查询

在数据库查询中，连接（Join）操作用于将两个或多个表中的数据组合在一起。常见的连接类型包括内连接、左外连接、右外连接和全外连接。以下是每种连接类型的详细说明及其SQL语法示例。

1. 内连接（Inner Join）

内连接返回两个表中具有匹配关系的行。只有当两个表中的记录在连接条件上匹配时，才会返回结果。

示例解释

假设有两个表：Orders（订单表）和Customers（客户表），我们希望查询所有有订单的客户信息。

1
2
3
4

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

2. 左外连接（Left Outer Join）

左外连接返回左表中的所有行，即使右表中没有匹配的行。对于右表中没有匹配的行，结果集中对应的数据为NULL。

示例解释

假设我们希望查询所有客户及其订单信息，包括那些没有订单的客户。

1
2
3
4

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

3. 右外连接（Right Outer Join）

右外连接与左外连接相似，返回右表中的所有行，即使左表中没有匹配的行。对于左表中没有匹配的行，结果集中对应的数据为NULL。

示例解释

假设我们希望查询所有订单及其对应的客户信息，包括那些没有客户的订单。

1
2
3
4

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
RIGHT JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

4. 全外连接（Full Outer Join）

全外连接返回两个表中的所有行，包括那些在另一个表中没有匹配的行。对于没有匹配的行，结果集中对应的数据为NULL。

示例解释

假设我们希望查询所有客户和订单的信息，包括那些没有订单的客户和没有客户的订单。

1
2
3
4

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

MySQL如何避免插入重复数据

在MySQL中，有多种方法可以避免插入重复数据。以下是三种常见的方法及其详细说明。

方式一：使用UNIQUE约束

UNIQUE约束用于确保表中某一列或一组列的值是唯一的。如果在插入数据时违反了UNIQUE约束，MySQL将拒绝插入该数据。

创建表时添加UNIQUE约束

1
2
3
4
5

CREATE TABLE Users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    name VARCHAR(255)
);

如果尝试插入重复的email，MySQL将返回错误。

方式二：使用 INSERT ... ON DUPLICATE KEY UPDATE

INSERT ... ON DUPLICATE KEY UPDATE语句在插入数据时，如果遇到重复键值（如UNIQUE约束或PRIMARY KEY），可以选择更新原有记录为新插入的记录。

插入或更新数据

1
2
3

INSERT INTO Products (product_id, product_name, price)
VALUES (1, 'Product A', 10.99)
ON DUPLICATE KEY UPDATE product_name = VALUES(product_name), price = VALUES(price);

如果product_id为1的记录已经存在，则更新其product_name和price字段。

方式三：使用 INSERT IGNORE

INSERT IGNORE语句在插入数据时，如果遇到重复键值（如UNIQUE约束或PRIMARY KEY），将忽略该插入操作，不会抛出错误。

1
2

INSERT IGNORE INTO Customers (customer_id, customer_name)
VALUES (1, 'Customer A');

如果customer_id为1的记录已经存在，则忽略该插入操作，不会抛出错误。

MySQL中的字符串类型

在MySQL中，字符串类型用于存储文本数据。常见的字符串类型包括CHAR、VARCHAR和TEXT。每种类型都有其特定的用途和存储特性。

CHAR

CHAR是一种固定长度的字符串类型。在定义时需要指定长度，存储时会在末尾填充空格以达到指定的长度。

特点

• 固定长度：无论实际存储的字符串长度如何，都会占用指定长度的存储空间。
• 存储效率：对于长度固定的字符串（如国家代码、性别等），CHAR类型可以提高存储效率。

VARCHAR

VARCHAR是一种可变长度的字符串类型。在定义时需要指定最大长度，存储时根据实际长度占用存储空间。

特点

• 可变长度：实际存储的字符串长度决定了占用的存储空间，节省存储空间。
• 灵活性：适用于长度不固定的字符串（如用户名、地址等）。

TEXT

TEXT用于存储超长文本数据。TEXT类型有多种子类型，包括TEXT、MEDIUMTEXT和LONGTEXT，分别用于存储不同大小的文本。

特点

• 大容量存储：适用于存储大段文本（如文章内容、日志等）。

• 存储限制：

  • TEXT：最大存储65,535字节（约64KB）。
  • MEDIUMTEXT：最大存储16,777,215字节（约16MB）。
  • LONGTEXT：最大存储4,294,967,295字节（约4GB）。

• **CHAR**：适用于固定长度的字符串，存储效率高。

• **VARCHAR**：适用于长度不固定的字符串，节省存储空间。

• **TEXT**：适用于存储超长文本，有多种子类型满足不同存储需求。

根据具体需求选择合适的字符串类型，可以优化数据库的存储和查询性能。

外键约束

外键约束（Foreign Key Constraint）是关系型数据库中用于维护表与表之间关系的一种机制。它确保数据的完整性和一致性，防止在关联表中插入无效数据。

作用

1. 维护数据完整性：外键约束确保在子表中插入或更新的记录在父表中存在对应的记录。如果父表中不存在对应的记录，数据库将拒绝插入或更新操作。
2. 防止无效数据：外键约束防止在子表中插入或更新无效数据，从而保持数据的正确性和一致性。
3. 级联操作：外键约束可以配置级联操作，如级联删除（CASCADE DELETE）或级联更新（CASCADE UPDATE），以确保在父表中的记录被删除或更新时，子表中的相关记录也相应地被删除或更新。

假设有两个表：Orders（订单表）和Customers（客户表），我们希望在Orders表中定义一个外键约束，确保每个订单都关联到一个有效的客户。

1
2
3
4
5
6
7
8
9
10
11

CREATE TABLE Customers (
    CustomerID INT AUTO_INCREMENT PRIMARY KEY,
    CustomerName VARCHAR(255)
);

CREATE TABLE Orders (
    OrderID INT AUTO_INCREMENT PRIMARY KEY,
    OrderDate DATE,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

MySQL关键字：IN 和 EXISTS

在MySQL中，IN和EXISTS是两个常用的关键字，用于在查询中进行条件判断。它们各自有不同的用途和特点。

IN 关键字

IN关键字用于检查左边的表达式是否存在于右边的列表或子查询中。如果存在，则返回true，否则返回false。

假设我们有一个Orders表，我们希望查询所有订单ID为1、2或3的订单。

1
2
3

SELECT *
FROM Orders
WHERE OrderID IN (1, 2, 3);

或者使用子查询：

1
2
3

SELECT *
FROM Orders
WHERE OrderID IN (SELECT OrderID FROM Orders WHERE OrderDate = '2023-10-01');

EXISTS 关键字

EXISTS关键字用于检查子查询是否至少返回一行数据。它不关心子查询返回的具体数据，只关注是否有返回数据。如果有返回数据，则返回true，否则返回false。

假设我们有一个Customers表和一个Orders表，我们希望查询所有有订单的客户。

1
2
3

SELECT *
FROM Customers c
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID);

区别与适用场景

IN 适用场景

• 简单列表匹配：当需要检查某个值是否存在于一个固定的列表中时，IN非常适用。
• 子查询结果匹配：当需要检查某个值是否存在于子查询的结果集中时，IN也很适用。

EXISTS 适用场景

• 存在性检查：当只需要检查是否存在满足条件的记录，而不关心具体返回的数据时，EXISTS非常适用。
• 性能优化：在某些情况下，EXISTS的性能可能优于IN，特别是在子查询返回大量数据时。

MySQL基本函数

MySQL提供了丰富的内置函数，用于处理字符串、数值、日期和聚合数据。以下是一些常用的MySQL函数及其说明。

字符串函数

CONCAT(str1, str2, ...)

连接多个字符串，返回拼接结果。

1

SELECT CONCAT('Hello', ' ', 'World');  -- 返回 'Hello World'

LENGTH(str)

返回字符串的长度（字符数）。

1

SELECT LENGTH('Hello World');  -- 返回 11

SUBSTRING(str, start, length)

从指定位置开始，截取指定长度的字符串。

1

SELECT SUBSTRING('Hello World', 7, 5);  -- 返回 'World'

REPLACE(str, from_str, to_str)

将字符串中的指定字符替换为另一个字符。

1

SELECT REPLACE('Hello World', 'o', 'a');  -- 返回 'Hella Warld'

数值函数

ABS(num)

返回数值的绝对值。

1

SELECT ABS(-10);  -- 返回 10

POWER(num, n)

返回数值的n次幂。

1

SELECT POWER(2, 3);  -- 返回 8

日期函数

NOW()

返回当前日期和时间。

1

SELECT NOW();  -- 返回当前日期和时间，例如 '2023-10-01 12:34:56'

CURDATE()

返回当前日期。

1

SELECT CURDATE();  -- 返回当前日期，例如 '2023-10-01'

聚合函数

COUNT(column)

计算指定列中的非NULL值的个数。

1

SELECT COUNT(OrderID) FROM Orders;  -- 返回订单表中OrderID列的非NULL值个数

SUM(column)

计算指定列的总和。

1

SELECT SUM(Price) FROM Products;  -- 返回产品表中Price列的总和

AVG(column)

计算指定列的平均值。

1

SELECT AVG(Price) FROM Products;  -- 返回产品表中Price列的平均值

MAX(column)

返回指定列的最大值。

1

SELECT MAX(Price) FROM Products;  -- 返回产品表中Price列的最大值

MIN(column)

返回指定列的最小值。

1

SELECT MIN(Price) FROM Products;  -- 返回产品表中Price列的最小值

SQL查询执行顺序

所有的查询语句都是从FROM开始执行，在执行过程中，每个步骤都会生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入，最后一个步骤产生的虚拟表即为输出结果。执行顺序如下：

sql查询语句执行顺序注释：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- (9) 选择要查询的列
SELECT
-- (10) 去重
DISTINCT <column>
-- (6) 聚合函数或表达式
AGG FUNC <column> or
<expression>
-- (1) 指定数据来源的左表
FROM <left table>
-- (3) 指定连接类型并连接右表
<join_type> JOIN <right table>
-- (2) 指定连接条件
ON <join_condition>
-- (4) 指定筛选条件
WHERE <where condition>
-- (5) 指定分组列
GROUP BY <group by list>
-- (7) 可选：使用 CUBE 或 ROLLUP 进行多维分析
WITH {CUBE | ROLLUP}
-- (8) 指定分组后的筛选条件
HAVING <having_condition>
-- (11) 指定排序方式
ORDER BY <order by list>
-- (12) 指定返回结果的数量限制
LIMIT <limit number>

SQL请求执行过程详解

1. 连接管理

连接器：客户端通过连接器与MySQL服务器建立连接。连接器负责验证客户端的身份和权限，并维护连接状态。

2. 查询缓存

缓存查询：在MySQL 5.7及之前版本中，查询缓存功能默认关闭，而在MySQL 8.0中已被移除。如果查询缓存功能开启且命中缓存，服务器将直接返回缓存结果，从而提高查询效率。

3. 语法解析

解析器：解析器负责对SQL语句进行词法和语法分析。词法分析确保SQL语句中的关键词和标识符正确无误，语法分析则验证SQL语句的结构是否符合语法规则。解析完成后，生成一棵语法树（AST）。

4. 预处理

预处理阶段：预处理器对语法树进行进一步处理，检查表和字段是否存在，并验证SQL语句的语义正确性。此阶段还会处理SQL语句中的占位符，确保查询的完整性和一致性。

5. 查询优化

优化器：优化器是SQL执行过程中的核心组件，负责选择最优的执行计划。优化器会评估多种可能的执行路径，并根据成本模型选择查询成本最小的执行计划。优化过程包括但不限于索引选择、表连接顺序优化、子查询优化等。

6. 执行引擎

执行阶段：执行引擎根据优化器生成的执行计划，从存储引擎中读取数据。执行过程中，数据可能被缓存以提高后续查询的效率。最终，执行引擎将结果集返回给客户端。

MySQL存储引擎

InnoDB

• 默认引擎：InnoDB是MySQL的默认存储引擎，适用于高并发场景。
• 事务支持：提供ACID（原子性、一致性、隔离性、持久性）事务特性，确保数据的一致性和完整性。
• 锁机制：支持行级锁，减少锁冲突，提高并发性能。
• 外键约束：支持外键约束，确保表之间的数据完整性。

MyISAM

• 存储优化：MyISAM具有较低的存储和内存消耗，适用于大量读操作的场景。
• 性能特点：不支持事务，没有行级锁和外键约束，因此在高并发写操作场景下性能较差。
• 适用场景：适合读密集型应用，如数据仓库、日志记录等。

Memory

• 内存存储：Memory引擎将数据存储在内存中，适用于需要快速读取的场景。
• 性能优势：由于数据存储在内存中，读取速度极快，适合临时数据存储和高频读取操作。
• 数据持久性：不支持事务、行级锁和外键索引。数据在服务器重启或崩溃时会丢失，因此不适合需要持久化的数据存储。

通过选择合适的存储引擎，可以根据应用的具体需求优化数据库性能和数据管理策略。InnoDB适用于需要高并发和事务支持的场景，MyISAM适合读密集型应用，而Memory引擎则适用于需要快速读取的临时数据存储。

数据文件

在MySQL中，我们可以创建一个测试数据库test_db和一张测试表test_order。在数据库的存储路径下，可以看到以下三个文件：

1. db.opt：该文件记录了当前数据库的默认字符集和校验规则。
2. test_order.frm：此文件存储了表的结构信息，包括字段定义、索引等，主要用于描述表的结构。
3. test_order.idb：所有的元数据和实际数据都存储在这个文件中，包括表的数据、索引等。

联合索引

联合索引是由多个字段共同组成的索引。例如，索引可以基于字段A、B和C创建。联合索引的构建遵循最左匹配原则，即首先按照联合索引中的第一个字段进行分组排序，然后在此基础上按第二个字段分组排序，依此类推。

最左匹配原则

最左匹配原则意味着查询必须从联合索引的最左边的字段开始匹配。例如，如果联合索引是基于字段A、B和C创建的，那么查询条件中必须包含字段A，才能利用该索引。如果查询条件只包含字段B和C，则无法利用该联合索引，因为往后的索引是全局无序的，而只是相对上一个字段局部有序。

索引区分度的重要性

在构建联合索引时，优先选择区分度高的字段作为前缀。区分度的计算公式为：区分度 = (不同值的数量) / (总记录数)。区分度越高，索引的选择性越好，查询效率也越高。例如，使用性别作为联合索引的首个字段，由于其区分度极低（接近于0），即使通过索引找到了结果，由于是非聚簇索引，仍需执行回表操作，增加IO次数，因此不建议使用。

索引失效的情况

从索引的物理存储特性来看，以下行为可能导致索引失效：

• 模糊查询：使用通配符开头的模糊查询（如LIKE '%keyword'）通常会导致索引失效，因为数据库无法利用索引来快速定位匹配的记录。
• 范围查询：在联合索引中，范围查询（如>、<、BETWEEN等）可能会导致后续字段的索引失效。例如，如果查询条件是A > 10 AND B = 5，那么字段B的索引可能无法被有效利用。
• 函数操作：对索引字段进行函数操作（如LOWER(column)）会导致索引失效，因为数据库无法直接使用索引进行比较。

通过合理设计和使用联合索引，可以显著提高查询性能，减少数据库的I/O操作。