【深入理解Mysql】
date: 2025-01-07 11:25深入理解Mysql
概述
MySQL 是一个开源的关系型数据库管理系统（RDBMS），它利用结构化查询语言（SQL）举行数据管理和操作。MySQL因其快速、可靠、易于利用和免费的特点而广受接待，广泛应用于Web应用程序、企业级应用以及嵌入式系统中。
主要特性
1. 开源与社区支持
[*]开源许可证：MySQL主要采用GPL（GNU General Public License）和贸易许可证双重许可模式。对于个人用户、非盈利组织或开源项目，可以免费利用；而对于需要专有软件的企业，则提供贸易版本，包含额外的支持和服务。
[*]活泼社区：拥有庞大的开辟者和用户社区，提供了丰富的文档、论坛、博客和技术交换运动。社区贡献者不但限于代码开辟，还包罗bug修复、功能建议和最佳实践分享。
2. 跨平台兼容性
[*]多操作系统支持：MySQL可以在Windows、Linux、macOS、Solaris、FreeBSD等多种操作系统上运行，这使得它可以或许适应不同的服务器情况和个人盘算机系统。
[*]硬件架构无关性：无论是在x86、ARM照旧其他范例的处理器架构上，MySQL都能精良运行，确保了广泛的适用性和移植性。
3. 高性能
[*]读取优化：MySQL对只读查询举行了特别优化，尤其是在Web应用中常见的大量读操作场景下表现优异。它通过索引、缓存和其他机制提高了查询效率。
[*]并发处理：采用多线程架构，每个连接对应一个线程，可以高效地处理高并发哀求。InnoDB存储引擎还支持行级锁，进一步提升了并发性能。
[*]内存管理和缓存：MySQL利用多种缓存战略（如查询缓存、表缓存、键缓存）来减少磁盘I/O，加快数据访问速度。此外，还可以根据硬件资源动态调整缓存大小以得到最佳性能。
4. 灵活性
[*]多种存储引擎：除了默认的InnoDB外，MySQL还支持MyISAM、MEMORY、ARCHIVE等多个存储引擎，每种引擎都有其独特的特性和应用场景。用户可以根据具体需求选择最符合的存储方式。
[*]插件化架构：MySQL答应通过插件扩展功能，例如添加新的存储引擎、审计日志、认证方法等。这种设计增长了系统的灵活性和可定制性。
5. 可扩展性
[*]复制和集群：MySQL提供了主从复制、半同步复制、组复制等多种复制方案，用于实现数据冗余、负载均衡和高可用性。此外，还有Galera Cluster等第三方办理方案可以构建MySQL集群。
[*]分片和分区：为了应对海量数据，MySQL支持水平分片（Sharding）和表分区，将大表拆分成更小的部分举行管理和查询，提高性能和管理便利性。
6. 安全性
[*]用户权限管理：MySQL有一套精细的权限控制系统，可以准确到表级别甚至列级别的权限设置，保障数据库的安全。
[*]SSL/TLS加密：支持通过SSL/TLS协议加密客户端与服务器之间的通信，掩护传输中的敏感信息。
[*]安全补丁和支持：官方定期发布安全更新，修复已知毛病，并为贸易用户提供及时的技术支持。
7. 易用性
[*]简单安装配置：MySQL的安装过程相对简单，官方提供了具体的安装指南和图形界面工具（如MySQL Installer），帮助用户快速完成摆设。
[*]图形化管理工具：MySQL Workbench是一个官方提供的图形化管理工具，集成了数据库设计、SQL开辟、性能分析等功能，极大地方便了数据库管理员的工作。
[*]命令行工具：对于熟悉命令行操作的用户，MySQL也提供了丰富的命令行工具，如mysql客户端、mysqldump备份工具等，方便举行各种管理和维护任务。
8. 生态系统
[*]丰富的API和驱动程序：MySQL支持几乎所有主流编程语言的API和驱动程序，如PHP、Python、Java、C#等，便于开辟者集成到本身的应用程序中。
[*]第三方工具和服务：有许多第三方工具和服务围绕MySQL构建，如监控工具（Percona Monitoring and Management）、备份工具（Percona XtraBackup）、云服务（Amazon RDS for MySQL, Google Cloud SQL for MySQL）等，丰富了MySQL的生态系统。
架构
1. 连接层 (Connection Layer)
[*]连接受理：负责处理客户端的连接哀求，验证用户身份，并为每个连接分配一个线程。MySQL 支持多种网络协议，包罗 TCP/IP 和 Unix 域套接字。
[*]线程池：对于高并发情况，MySQL 提供了线程池功能，可以更有用地管理和复用线程，减少线程创建和烧毁的开销。
[*]权限验证：在连接创建后，MySQL 会查抄用户的权限，确保只有授权用户才能实行特定的操作。
2. 服务层 (Service Layer)
[*]SQL 解析器：当客户端发送 SQL 语句时，解析器负责将 SQL 语句分解成内部格式，以便进一步处理。它还负责语法查抄，确保输入的 SQL 是合法的。
[*]预处理器：对解析后的 SQL 语句举行初步处理，例如添补默认值、解析表名和列名等。
[*]查询优化器：这是 MySQL 中非常关键的部分，它决定怎样最有用地实行查询。优化器会考虑多种因素，如索引可用性、表大小、连接条件等，以选择最优的实行计划。
[*]缓存机制：MySQL 包含多个缓存组件，如查询缓存（已废弃）、表缓存、键缓存等，用于加快数据访问。特别是 InnoDB 存储引擎中的缓冲池（Buffer Pool），它可以明显提高读写性能。
[*]事件管理：负责事件的开始、提交和回滚操作，确保事件的 ACID 属性（原子性、划一性、隔离性、长期性）。
[*]存储过程和触发器：MySQL 支持存储过程、函数和触发器，答应开辟者编写复杂的业务逻辑并在服务器端实行。
3. 引擎层 (Engine Layer)
[*]存储引擎 API：MySQL 利用插件式架构，不同的存储引擎通过一组标准的API与上层的服务层交互。这使得用户可以根据需求选择最适合的存储引擎。
[*]存储引擎特性：不同的存储引擎具有不同的特性和适用场景。例如：
[*]InnoDB：默认存储引擎，支持事件、行级锁、外键约束，适合高并发读写操作。
[*]MyISAM：早期默认引擎，提供快速的读取性能，但不支持事件和行级锁。
[*]MEMORY：所有数据都生存在内存中，适合临时表或高速缓存。
[*]Archive：用于归档旧数据，压缩比高，只支持插入和选择操作。
[*]其他：还有其他较少利用的存储引擎，如CSV、BLACKHOLE等，适用于特定场景。
1. InnoDB
[*]事件支持：InnoDB 是 MySQL 的默认存储引擎，完全支持 ACID（原子性、划一性、隔离性、长期性）事件特性。
[*]行级锁：与表级锁相比，行级锁可以明显提高并发性能，尤其是在高并发读写情况中。
[*]外键约束：InnoDB 支持外键，这有助于维护数据的完备性和划一性。
[*]MVCC (多版本并发控制)：通过利用回滚段和快照技术，答应读操作不被写操作壅闭，从而提高了并发性能。
[*]瓦解恢复：InnoDB 利用重做日志（Redo Log）和打消日志（Undo Log）来确保纵然在系统瓦解的情况下也能快速恢复数据。
[*]缓冲池 (Buffer Pool)：InnoDB 有一个专门的缓冲池用于缓存数据页和索引页，大大减少了磁盘 I/O 操作，提升了读写性能。
[*]双写缓冲 (Doublewrite Buffer)：为了防止部分页面写入失败导致的数据损坏，InnoDB 在写入数据时会先将数据写入一个临时区域，然后再写入现实位置。
[*]自适应哈希索引：根据查询模式自动创建哈希索引，加快某些范例的查询。
2. MyISAM
[*]高速读取：MyISAM 引擎对只读查询举行了特别优化，因此在以读为主的应用中表现优异。
[*]表级锁：所有写操作都会锁定整个表，这在高并发写入情况下可能导致瓶颈。
[*]全文搜刮：MyISAM 支持全文索引，适用于需要全文搜刮功能的应用场景。
[*]压缩表：MyISAM 表可以被压缩，节省磁盘空间，但压缩后的表只能举行读操作。
[*]修复工具：提供了 myisamchk 等工具用于查抄和修复损坏的表。
3. MEMORY (HEAP)
[*]内存存储：所有数据都生存在内存中，提供极高的读写速度，但数据在服务器重启后会丢失。
[*]哈希索引：除了 B-Tree 索引，MEMORY 引擎还支持哈希索引，对于等值查询非常高效。
[*]固定记录格式：每条记录占用相同的字节数，简化了内存管理。
[*]不适合大表：由于数据完全驻留在内存中，MEMORY 引擎不太适合处理大规模数据集。
4. Archive
[*]高压缩率：Archive 引擎专为归档大量汗青数据而设计，具有很高的压缩比，可以或许有用节省磁盘空间。
[*]只读操作：只支持插入和选择操作，不答应更新或删除。
[*]无索引：Archive 表不支持索引，这意味着所有的查询都是全表扫描，但在某些情况下，如日志分析，这可能是可接受的。
5. CSV
[*]CSV 文件格式：CSV 引擎将表数据存储为标准的逗号分隔值文件，便于与其他应用程序交换数据。
[*]简单易用：适合用于临时表或测试情况，因为它不需要复杂的配置。
[*]有限的功能：CSV 引擎不支持索引、事件、外键等高级特性。
6. BLACKHOLE
[*]数据黑洞：BLACKHOLE 引擎接收但不存储任何数据，所有插入、更新和删除操作都不会产生现实影响。
[*]日志记录：只管没有物理存储，BLACKHOLE 引擎仍然会记录二进制日志，这使其在某些复制场景下有用。
[*]轻量级：非常适合用于测试或作为代理服务器的一部分。
7. NDB Cluster (NDB)
[*]分布式数据库：NDB 是 MySQL Cluster 的核心存储引擎，提供高可用性和水平扩展本事。
[*]多主复制：所有节点都可以同时接受写操作，消除了单点故障。
[*]内存中数据：数据通常存储在内存中，保证了低延迟和高吞吐量。
[*]自动分区：NDB 引擎会自动将数据分布到多个节点上，实现负载均衡。
[*]复杂配置：设置和管理相对复杂，适合有经验的管理员。
8. FEDERATED
[*]远程表访问：FEDERATED 引擎答应你创建一个本地表，该表现实上指向另一个 MySQL 服务器上的表，从而实现跨服务器的数据访问。
[*]透明访问：对于应用程序来说，FEDERATED 表看起来就像普通表一样，但它现实上是通过网络哀求获取数据的。
[*]性能考虑：由于涉及网络通信，FEDERATED 引擎的性能可能不如本地表，特别是在高延迟或不稳固网络情况下。
9. TokuDB
[*]高压缩率：TokuDB 提供了比 InnoDB 更好的压缩算法，可以或许在保持高性能的同时大幅减少磁盘占用。
[*]快速插入：优化了批量插入操作，适用于需要频繁写入大量数据的应用。
[*]多版本并发控制：支持 MVCC，答应高效的并发读写。
[*]树形索引结构：采用 Fractal Tree 索引结构，不同于传统的 B-Tree，它在某些工作负载下表现更好。
选择符合的存储引擎
选择符合的存储引擎取决于具体的应用需求和技术要求。以下是一些考虑因素：
[*]事件需求：如果应用需要强划一性和事件支持，应该选择 InnoDB 或 TokuDB。
[*]读写比例：对于以读为主的场景，MyISAM 可能更符合；而对于高并发读写，则应选择 InnoDB。
[*]数据规模：处理海量数据时，要考虑存储引擎的压缩本事和扩展性，例如 Archive 和 TokuDB。
[*]性能和资源：MEMORY 引擎适合小规模数据和极高的读写速度，但要留意内存限制。
[*]特别功能：如果需要特定的功能，如全文搜刮（MyISAM）、分布式摆设（NDB）、远程表访问（FEDERATED）等，可以选择相应的存储引擎。
4. 存储层 (Storage Layer)
[*]数据文件：每个存储引擎都有本身独特的文件格式来存储数据。例如，InnoDB 利用 .ibd 文件存储表数据和索引，而 MyISAM 则利用 .MYD（数据）和 .MYI（索引）文件。
[*]日志文件：为了保证数据的划一性和长期性，MySQL 维护了几种范例的日志文件：
[*]重做日志 (Redo Log)：InnoDB 特有的日志范例，记录了对数据页的物理修改，用于瓦解恢复。
[*]打消日志 (Undo Log)：用于实现多版本并发控制（MVCC），支持事件的回滚操作。
[*]二进制日志 (Binary Log)：记录了所有更改数据库结构或内容的变乱，用于复制和点-in-time 恢复。
[*]错误日志 (Error Log)：记录了服务器运行期间碰到的错误信息。
[*]慢查询日志 (Slow Query Log)：记录了实行时间超过指定阈值的查询，帮助识别性能瓶颈。
5. 复制和高可用
[*]主从复制：MySQL 支持异步、半同步和组复制，答应从一个主服务器复制数据到一个或多个从服务器，用于负载均衡、故障转移等。
[*]高可用方案：除了传统的主从复制，还有 Galera Cluster 等第三方办理方案，提供了多主复制和自动故障切换功能，增强了系统的高可用性和容错本事。
6. 性能监控和调优
[*]性能模式 (Performance Schema)：这是一个内置的监控工具，提供了具体的性能统计数据，如线程状态、锁等待、IO操作等。
[*]慢查询日志：如前所述，慢查询日志可以帮助你找到需要优化的查询。
[*]EXPLAIN：利用 EXPLAIN 命令可以查看查询的实行计划，分析是否准确利用了索引。
[*]系统变量和状态变量：MySQL 内置了大量的系统变量和状态变量，可以通过调整这些参数来优化服务器性能。
7. 安全性和管理
[*]用户权限管理：MySQL 提供了一套精细的权限控制系统，可以准确到表级别甚至列级别的权限设置。
[*]SSL/TLS 加密：支持通过 SSL/TLS 协议加密客户端与服务器之间的通信，掩护传输中的敏感信息。
[*]备份和恢复：MySQL 提供了多种备份和恢复方法，如 mysqldump、物理备份、热备份等，确保数据的安全性和可恢复性。
8. 开辟者工具和支持
[*]MySQL Workbench：官方提供的图形化管理工具，集成了数据库设计、SQL 开辟、性能分析等功能。
[*]命令行工具：MySQL 提供了一系列强大的命令行工具，如 mysql 客户端、mysqldump 备份工具等，方便举行各种管理和维护任务。
[*]API 和驱动程序：MySQL 支持几乎所有主流编程语言的 API 和驱动程序，如 PHP、Python、Java、C# 等，便于开辟者集成到本身的应用程序中。
[*]
语句分类
1. 数据查询语言 (DQL, Data Query Language)
[*]主要用途：用于从数据库中检索数据。
[*]关键字：
[*]SELECT：这是 DQL 中最常用的命令，用于查询表中的数据。它可以与各种子句结合利用，如 FROM、WHERE、GROUP BY、HAVING 和 ORDER BY，以实现复杂的查询需求。
[*]EXPLAIN：虽然不是严酷意义上的 DQL 命令，但它可以帮助你理解 SELECT 语句的实行计划，从而优化查询性能。
示例：
SELECT * FROM employees WHERE department = 'Sales';
2. 数据利用语言 (DML, Data Manipulation Language)
[*]主要用途：用于插入、更新和删除数据。
[*]关键字：
[*]INSERT：用于向表中添加新记录。
[*]UPDATE：用于修改现有记录的数据。
[*]DELETE：用于从表中删除记录。
示例：
-- 插入新记录
INSERT INTO employees (name, department) VALUES ('Alice', 'HR');
-- 更新现有记录
UPDATE employees SET department = 'Marketing' WHERE name = 'Alice';
-- 删除记录
DELETE FROM employees WHERE name = 'Alice';
3. 数据定义语言 (DDL, Data Definition Language)
[*]主要用途：用于定义或修改数据库结构，如创建、修改和删除数据库对象（表、索引、视图等）。
[*]关键字：
[*]CREATE：用于创建数据库、表、索引、视图等。
[*]ALTER：用于修改现有的数据库对象结构。
[*]DROP：用于删除数据库、表、索引、视图等。
[*]TRUNCATE：用于快速删除表中的所有数据，但保存表结构。
示例：
-- 创建表
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
department VARCHAR(50)
);
-- 修改表结构
ALTER TABLE employees ADD COLUMN email VARCHAR(100);
-- 删除表
DROP TABLE employees;
-- 清空表数据
TRUNCATE TABLE employees;
4. 数据控制语言 (DCL, Data Control Language)
[*]主要用途：用于管理数据库用户的权限，确保数据的安全性和完备性。
[*]关键字：
[*]GRANT：授予用户对数据库对象的特定权限。
[*]REVOKE：打消用户对数据库对象的特定权限。
示例：
-- 授予权限
GRANT SELECT, INSERT ON employees TO 'user1'@'localhost';
-- 撤销权限
REVOKE SELECT ON employees FROM 'user1'@'localhost';
5. 事件控制语言 (TCL, Transaction Control Language)
[*]主要用途：用于管理事件，确保一系列操作作为一个整体实行，要么全部成功，要么全部失败。
[*]关键字：
[*]BEGIN 或 START TRANSACTION：开始一个新事件。
[*]COMMIT：提交当前事件，使所有更改永世见效。
[*]ROLLBACK：回滚当前事件，打消所有未提交的更改。
[*]SAVEPOINT：设置生存点，答应部分回滚到某个特定点。
示例：
-- 开始事务
START TRANSACTION;
-- 执行多个操作
INSERT INTO employees (name, department) VALUES ('Bob', 'IT');
UPDATE employees SET department = 'HR' WHERE name = 'Alice';
-- 提交事务
COMMIT;
-- 或者回滚事务
ROLLBACK;
-- 设置保存点
SAVEPOINT my_savepoint;
-- 回滚到保存点
ROLLBACK TO my_savepoint;
6. 其他 SQL 语句
除了上述五类主要的 SQL 语句外，还有一些其他范例的语句，它们虽然不完全属于上述种别，但在现实应用中也非常常见：
[*]存储过程和函数：用于封装一组 SQL 语句，以便重复利用和简化复杂操作。
[*]触发器：在特定变乱（如插入、更新或删除）发生时自动实行的一组 SQL 语句。
[*]视图：虚拟表，由查询效果集构成，可以简化复杂的查询逻辑。
[*]事件隔离级别：设置事件的隔离级别，以控制并发事件之间的可见性和划一性。
示例：
-- 创建存储过程
DELIMITER //
CREATE PROCEDURE InsertEmployee(IN emp_name VARCHAR(50), IN emp_dept VARCHAR(50))
BEGIN
INSERT INTO employees (name, department) VALUES (emp_name, emp_dept);
END //
DELIMITER ;
-- 调用存储过程
CALL InsertEmployee('Charlie', 'Finance');
-- 创建触发器
CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
SET NEW.hire_date = CURDATE();
-- 创建视图
CREATE VIEW hr_employees AS
SELECT id, name, department FROM employees WHERE department = 'HR';
数据范例
数值范例 (Numeric Types)
数据范例大小 (字节)有符号范围无符号范围阐明TINYINT1-128 到 1270 到 255小整数SMALLINT2-32,768 到 32,7670 到 65,535中等大小的整数MEDIUMINT3-8,388,608 到 8,388,6070 到 16,777,215较大的整数INT 或 INTEGER4-2,147,483,648 到 2,147,483,6470 到 4,294,967,295标准整数BIGINT8-9,223,372,036,854,775,808 到 9,223,372,036,854,775,8070 到 18,446,744,073,709,551,615非常大的整数FLOAT4 或 8取决于精度取决于精度单精度浮点数DOUBLE8-1.79E+308 到 -2.23E-308, 0, 2.23E-308 到 1.79E+3080, 2.23E-308 到 1.79E+308双精度浮点数DECIMAL 或 NUMERIC可变取决于精度和小数位数取决于精度和小数位数固定精度和小数位数的十进制数 字符串范例 (String Types)
数据范例描述最大长度阐明CHAR定长字符串0 到 255 字符存储固定长度的字符串，不敷部分用空格添补VARCHAR可变长字符串0 到 65,535 字符存储可变长度的字符串，节省空间BINARY定长二进制字符串0 到 255 字节类似 CHAR，但存储二进制数据VARBINARY可变长二进制字符串0 到 65,535 字节类似 VARCHAR，但存储二进制数据TINYTEXT短文本最大 255 字节存储较短的文本数据TEXT文本最大 65,535 字节存储中等长度的文本数据MEDIUMTEXT较长文本最大 16,777,215 字节存储较长的文本数据LONGTEXT非常长文本最大 4,294,967,295 字节存储非常长的文本数据ENUM罗列范例1 或 2 字节存储预定义的一组值SET集合范例最多 64 个成员存储多个预定义值的集合 日期和时间范例 (Date and Time Types)
数据范例格式范围阐明DATEYYYY-MM-DD1000-01-01 到 9999-12-31存储日期TIMEHH:MM:SS 或 HHH:MM:SS‘-838:59:59’ 到 ‘838:59:59’存储时间，支持负时间表示持续时间DATETIMEYYYY-MM-DD HH:MM:SS1000-01-01 00:00:00 到 9999-12-31 23:59:59存储日期和时间TIMESTAMPYYYY-MM-DD HH:MM:SS1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC存储带时区的时间戳，默认自动更新到当前时间YEARYYYY1901 到 2155存储年份，可以是两位或四位格式 二进制范例 (Binary Types)
数据范例描述最大长度阐明BLOB二进制大对象最大 65,535 字节存储较大的二进制数据，如图片、文件等TINYBLOB短二进制大对象最大 255 字节存储较短的二进制数据MEDIUMBLOB较长二进制大对象最大 16,777,215 字节存储较长的二进制数据LONGBLOB非常长二进制大对象最大 4,294,967,295 字节存储非常长的二进制数据 JSON 范例
数据范例描述阐明JSONJSON 对象存储有用的 JSON 数据，MySQL 提供了内置函数来操作 JSON 数据 空间范例 (Spatial Types)
数据范例描述阐明GEOMETRY几何数据存储几何对象，如点、线、多边形等POINT点存储二维坐标点LINESTRING线段存储一系列连接的点POLYGON多边形存储封闭的多边形区域MULTIPOINT多点存储多个点MULTILINESTRING多线段存储多个线段MULTIPOLYGON多多边形存储多个多边形GEOMETRYCOLLECTION几何集合存储多种几何对象的集合 数据表与数据库的基本操作
1. 数据库操作
创建数据库
CREATE DATABASE database_name;
[*]阐明：创建一个名为 database_name 的新数据库。
[*]示例：CREATE DATABASE my_database;
查看所有数据库
SHOW DATABASES;
[*]阐明：列出服务器上所有的数据库。
利用数据库
USE database_name;
[*]阐明：选择一个数据库作为当前工作情况。
[*]示例：USE my_database;
删除数据库
DROP DATABASE database_name;
[*]阐明：删除指定名称的数据库及其所有内容（表、视图等）。这是一个不可逆的操作，请审慎利用。
[*]示例：DROP DATABASE my_database;
2. 数据表操作
创建表
CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
...
);
[*]阐明：创建一个名为 table_name 的新表，并定义其列名、数据范例和约束条件。
[*]示例：CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT,
department VARCHAR(50)
);
查看表结构
DESCRIBE table_name;
-- 或者
SHOW COLUMNS FROM table_name;
[*]阐明：显示指定表的结构，包罗列名、数据范例、是否答应为空等信息。
[*]示例：DESCRIBE employees;
查看所有表
SHOW TABLES;
[*]阐明：列出当前数据库中的所有表。
修改表结构
添加新列
ALTER TABLE table_name ADD COLUMN new_column datatype constraints;
[*]示例：ALTER TABLE employees ADD COLUMN email VARCHAR(100);
修改现有列
ALTER TABLE table_name MODIFY COLUMN column_name new_datatype new_constraints;
[*]示例：ALTER TABLE employees MODIFY COLUMN age SMALLINT;
更改列名
ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name new_datatype new_constraints;
[*]示例：ALTER TABLE employees CHANGE COLUMN age employee_age SMALLINT;
删除列
ALTER TABLE table_name DROP COLUMN column_name;
[*]示例：ALTER TABLE employees DROP COLUMN email;
添加主键
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
[*]示例：ALTER TABLE employees ADD PRIMARY KEY (id);
删除主键
ALTER TABLE table_name DROP PRIMARY KEY;
[*]阐明：只有当表的主键是一个单独的列时，才能直接删除主键。如果主键是多个列的组合，则需要先删除整个索引再重新创建。
[*]示例：ALTER TABLE employees DROP PRIMARY KEY;
删除表
DROP TABLE table_name;
[*]阐明：删除指定名称的表及其所有数据。这是一个不可逆的操作，请审慎利用。
[*]示例：DROP TABLE employees;
清空表数据
TRUNCATE TABLE table_name;
[*]阐明：快速删除表中的所有数据，但保存表结构。与 DELETE 不同，TRUNCATE 是一个 DDL 操作，通常更快且不会触发触发器。
[*]示例：TRUNCATE TABLE employees;
3. 插入、查询、更新和删除数据
插入数据
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
[*]阐明：向表中插入一行或多行数据。
[*]示例：INSERT INTO employees (name, age, department) VALUES ('Alice', 30, 'HR');
查询数据
SELECT column1, column2, ... FROM table_name WHERE conditions;
[*]阐明：从表中检索数据，可以利用 WHERE 子句来指定筛选条件。
[*]示例：SELECT name, age FROM employees WHERE department = 'HR';
更新数据
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE conditions;
[*]阐明：更新表中满足条件的行的数据。
[*]示例：UPDATE employees SET age = 31 WHERE name = 'Alice';
删除数据
DELETE FROM table_name WHERE conditions;
[*]阐明：删除表中满足条件的行。
[*]示例：DELETE FROM employees WHERE name = 'Alice';
4. 索引操作
创建索引
CREATE INDEX index_name ON table_name (column1, column2, ...);
[*]阐明：为表中的一个或多个列创建索引，以加快查询。
[*]示例：CREATE INDEX idx_department ON employees (department);
删除索引
DROP INDEX index_name ON table_name;
[*]阐明：删除指定名称的索引。
[*]示例：DROP INDEX idx_department ON employees;
5. 视图操作
创建视图
CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE conditions;
[*]阐明：创建一个视图，视图是一个虚拟表，其内容由查询效果集构成。
[*]示例：CREATE VIEW hr_employees AS SELECT id, name, department FROM employees WHERE department = 'HR';
删除视图
DROP VIEW view_name;
[*]阐明：删除指定名称的视图。
[*]示例：DROP VIEW hr_employees;
高级操作
1. 复合查询 (Compound Queries)
复合查询答应你将多个 SELECT 语句的效果组合在一起。常见的复合查询操作符包罗 UNION、INTERSECT 和 EXCEPT（MySQL 不直接支持 INTERSECT 和 EXCEPT，但可以通过其他方式实现类似效果）。
UNION
[*] 阐明：将两个或多个 SELECT 语句的效果归并为一个效果集。默认情况下，UNION 会去除重复的行。
[*] 语法：
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;
[*] 示例：
SELECT name, department FROM employees WHERE department = 'HR'
UNION
SELECT name, department FROM employees WHERE department = 'IT';
[*] UNION ALL：保存所有重复的行，不举行去重。
[*]示例：SELECT name, department FROM employees WHERE department = 'HR'
UNION ALL
SELECT name, department FROM employees WHERE department = 'IT';
模拟 INTERSECT 和 EXCEPT
由于 MySQL 不直接支持 INTERSECT 和 EXCEPT，可以通过子查询和 JOIN 来实现类似的效果。
[*] 模拟 INTERSECT（交集）：
[*]示例：SELECT e1.name, e1.department
FROM employees e1
INNER JOIN employees e2 ON e1.name = e2.name AND e1.department = e2.department
WHERE e1.department = 'HR' AND e2.department = 'IT';
[*] 模拟 EXCEPT（差集）：
[*]示例：SELECT name, department FROM employees WHERE department = 'HR'
AND (name, department) NOT IN (
SELECT name, department FROM employees WHERE department = 'IT'
);
2. 子查询 (Subqueries)
子查询是在另一个查询内部实行的查询，可以嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句中。子查询可以返回单个值、一行或多行。
标量子查询
[*]阐明：子查询返回一个单一的值。
[*]语法：SELECT column1 FROM table1 WHERE column2 = (SELECT column2 FROM table2 WHERE condition);
[*]示例：SELECT name, salary FROM employees WHERE department = (SELECT id FROM departments WHERE name = 'HR');
行子查询
[*]阐明：子查询返回一行或多行。
[*]语法：SELECT column1 FROM table1 WHERE (column2, column3) = (SELECT column2, column3 FROM table2 WHERE condition);
[*]示例：SELECT name, salary FROM employees WHERE (department, position) = (SELECT id, position FROM positions WHERE title = 'Manager');
列子查询
[*]阐明：子查询返回一列或多列。
[*]语法：SELECT column1 FROM table1 WHERE column2 IN (SELECT column2 FROM table2 WHERE condition);
[*]示例：SELECT name, salary FROM employees WHERE department IN (SELECT id FROM departments WHERE name IN ('HR', 'IT'));
相干子查询
[*]阐明：子查询依赖于外部查询中的值，通常用于逐行处理。
[*]语法：SELECT column1 FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table2.column2 = table1.column2);
[*]示例：SELECT name, salary FROM employees e WHERE EXISTS (SELECT 1 FROM departments d WHERE d.id = e.department AND d.name = 'HR');
3. 团结查询 (JOIN)
团结查询用于从多个表中检索数据，并根据某些条件将它们组合在一起。MySQL 支持多种范例的 JOIN，包罗 INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL OUTER JOIN（MySQL 不直接支持 FULL OUTER JOIN，但可以通过 UNION 实现类似效果）。
INNER JOIN
[*]阐明：返回两个表中满足连接条件的匹配行。
[*]语法：SELECT t1.column1, t2.column2 FROM table1 t1
INNER JOIN table2 t2 ON t1.common_column = t2.common_column;
[*]示例：SELECT e.name, d.name AS department_name
FROM employees e
INNER JOIN departments d ON e.department = d.id;
LEFT JOIN (左连接)
[*]阐明：返回左表中的所有行，纵然右表中没有匹配的行。对于右表中没有匹配的行，效果集中右表的列将包含 NULL。
[*]语法：SELECT t1.column1, t2.column2 FROM table1 t1
LEFT JOIN table2 t2 ON t1.common_column = t2.common_column;
[*]示例：SELECT e.name, d.name AS department_name
FROM employees e
LEFT JOIN departments d ON e.department = d.id;
RIGHT JOIN (右连接)
[*]阐明：返回右表中的所有行，纵然左表中没有匹配的行。对于左表中没有匹配的行，效果集中左表的列将包含 NULL。
[*]语法：SELECT t1.column1, t2.column2 FROM table1 t1
RIGHT JOIN table2 t2 ON t1.common_column = t2.common_column;
[*]示例：SELECT e.name, d.name AS department_name
FROM employees e
RIGHT JOIN departments d ON e.department = d.id;
FULL OUTER JOIN (全外连接)
[*]阐明：返回两个表中的所有行，无论是否匹配。对于没有匹配的行，效果集中另一表的列将包含 NULL。MySQL 不直接支持 FULL OUTER JOIN，但可以通过 UNION 实现类似效果。
[*]示例：SELECT e.name, d.name AS department_nameFROM employees eLEFT JOIN departments d ON e.department = d.idUNIONSELECT e.name, d.name AS department_name
FROM employees e
RIGHT JOIN departments d ON e.department = d.id;
4. 分组和聚合 (Grouping and Aggregation)
分组和聚合操作用于对数据举行汇总和统计分析。常用的聚合函数包罗 COUNT、SUM、AVG、MIN 和 MAX。
GROUP BY
[*]阐明：将效果集按一个或多个列分组，通常与聚合函数一起利用。
[*]语法：SELECT column1, COUNT(column2) FROM table1 GROUP BY column1;
[*]示例：SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;
HAVING
[*]阐明：用于筛选分组后的效果，类似于 WHERE，但 HAVING 可以用于聚合函数。
[*]语法：SELECT column1, COUNT(column2) FROM table1 GROUP BY column1 HAVING COUNT(column2) > 10;
[*]示例：SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING employee_count > 5;
5. 窗口函数 (Window Functions)
窗口函数答应你在不改变效果集行数的情况下举行复杂的盘算，如排名、移动平均等。常用的窗口函数包罗 ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE()、LAG() 和 LEAD()。
ROW_NUMBER()
[*]阐明：为每一行分配一个唯一的行号，基于指定的排序规则。
[*]语法：SELECT column1, column2, ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2) AS row_num
FROM table1;
[*]示例：SELECT name, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;
RANK() 和 DENSE_RANK()
[*]阐明：为每一行分配一个排名，RANK() 会跳过重复排名，而 DENSE_RANK() 不会。
[*]语法：SELECT column1, column2, RANK() OVER (ORDER BY column2) AS rank
FROM table1;
[*]示例：SELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;
LAG() 和 LEAD()
[*]阐明：LAG() 用于访问当前行之前的行，LEAD() 用于访问当前行之后的行。
[*]语法：SELECT column1, column2, LAG(column2, 1) OVER (ORDER BY column1) AS prev_value
FROM table1;
[*]示例：SELECT name, department, salary, LAG(salary, 1) OVER (PARTITION BY department ORDER BY salary) AS prev_salary
FROM employees;
全文搜刮
MySQL 的全文搜刮功能是处理大量文本数据时非常有用的工具，特别适用于需要高效检索包含特定关键词的记录。全文搜刮不但支持简单的关键词匹配，还可以举行复杂的天然语言搜刮、布尔模式搜刮和查询扩展。以下是关于 MySQL 全文搜刮的具体介绍，包罗怎样创建全文索引、实行全文搜刮查询以及一些优化本事。
1. 创建全文索引 (FULLTEXT Index)
在 MySQL 中，全文搜刮依赖于 FULLTEXT 索引。只有为表中的文本列（如 CHAR、VARCHAR 和 TEXT）创建了 FULLTEXT 索引后，才能利用全文搜刮功能。FULLTEXT 索引可以在创建表时或之后通过 ALTER TABLE 语句添加。
1.1 在创建表时添加全文索引
CREATE TABLE articles (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255),
content TEXT,
FULLTEXT (title, content)-- 为多个列创建全文索引
);
1.2 在现有表中添加全文索引
ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title, content);
1.3 利用 ngram 解析器（针对中文等非英文语言）
对于中文、日语、韩语等非英文语言，MySQL 提供了 ngram 解析器来处理分词问题。ngram 解析器将文天职割成固定长度的字符序列（默以为 2 个字符），从而支持这些语言的全文搜刮。
[*]创建带有 ngram 解析器的全文索引：ALTER TABLE articles ADD FULLTEXT INDEX ft_index (content) WITH PARSER ngram;
2. 实行全文搜刮查询
MySQL 提供了 MATCH() ... AGAINST() 语法来举行全文搜刮。AGAINST() 子句可以接受不同的搜刮模式，包罗天然语言模式、布尔模式和查询扩展模式。
2.1 天然语言模式 (Natural Language Mode)
天然语言模式是最常用的搜刮模式，它将搜刮字符串解释为天然语言中的短语。默认情况下，MATCH() ... AGAINST() 利用天然语言模式。
[*] 示例：查找包含 “MySQL” 的文章
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('MySQL');
[*] 带停用词：MySQL 有一个内置的停用词列表，默认情况下会忽略这些常见的词汇（如 “the”、“is” 等）。你可以通过配置文件禁用停用词或自定义停用词列表。
2.2 布尔模式 (Boolean Mode)
布尔模式答应你利用特别的运算符来构建更复杂的查询条件。常见的运算符包罗：
[*] +：必须包含的词
[*] -：必须不包含的词
[*] >：增长词的权重
[*] <：减少词的权重
[*] ()：分组
[*] ~：否定词的意义
[*] *：通配符，表示词的前缀
[*] ""：短语匹配
[*] 示例：查找包含 “MySQL” 但不包含 “database” 的文章
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+MySQL -database' IN BOOLEAN MODE);
[*] 示例：查找以 “data” 开头的词
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('data*' IN BOOLEAN MODE);
2.3 查询扩展模式 (Query Expansion Mode)
查询扩展模式是一种改进的天然语言搜刮，它首先实行一次天然语言搜刮，然后根据返回的效果集中的相干词扩展原始查询，并再次实行搜刮。这可以帮助找到更多相干的文档。
[*]示例：利用查询扩展模式搜刮 “MySQL”SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('MySQL' WITH QUERY EXPANSION);
3. 全文搜刮的限制和留意事项
[*] 最小和最大词长：MySQL 有一个默认的最小词长设置（通常是 4 个字符），这意味着小于该长度的词不会被索引。你可以通过修改配置文件中的 innodb_ft_min_token_size 和 ft_min_word_len 参数来调整这个值。
[*] 停用词：MySQL 有一个内置的停用词列表，默认情况下会忽略这些常见的词汇。你可以通过修改配置文件中的 innodb_ft_default_stopword 和 ft_stopword_file 参数来自定义停用词列表。
[*] 全文索引的适用性：全文索引最适合用于大文本字段（如 TEXT 或 VARCHAR），而且在数据量较大时能明显提高搜刮性能。对于小表或少量数据，利用 LIKE 语句可能已经足够。
[*] 全文索引的更新：FULLTEXT 索引是自动维护的，但如果你有大量的插入或更新操作，可能会导致索引碎片化。定期优化表可以帮助保持索引的高效性。
[*] 全文索引的存储引擎支持：FULLTEXT 索引仅支持 InnoDB 和 MyISAM 存储引擎。InnoDB 是 MySQL 的默认存储引擎，保举利用 InnoDB 举行全文搜刮。
4. 优化全文搜刮
为了提高全文搜刮的性能，以下是一些建议：
[*] 选择符合的索引列：只对真正需要举行全文搜刮的列创建 FULLTEXT 索引，避免不必要的索引开销。
[*] 利用分区表：如果表的数据量非常大，可以考虑对表举行分区，按时间或其他条件举行分区，以减少每次查询时扫描的数据量。
[*] 缓存查询效果：对于频繁实行的全文搜刮查询，可以考虑利用查询缓存或应用层缓存来减少数据库的负载。
[*] 定期优化表：如果表中有大量的插入、更新或删除操作，定期利用 OPTIMIZE TABLE 语句来重组表并清理索引碎片。
[*] 调整配置参数：根据你的现实需求，调整 MySQL 的全文搜刮配置参数，如 innodb_ft_min_token_size、innodb_ft_max_token_size、innodb_ft_num_word_optimize 等，以优化性能。
5. 示例：综合利用全文搜刮
假设我们有一个 articles 表，包含 id、title 和 content 列。我们渴望实现以下功能：
[*]查找标题或内容中包含 “MySQL” 的文章。
[*]查找标题或内容中包含 “performance” 但不包含 “tuning” 的文章。
[*]查找标题或内容中包含 “data” 开头的词的文章。
[*]查找最近一个月内发布的、标题或内容中包含 “optimization” 的文章。
5.1 天然语言模式搜刮
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('MySQL');
5.2 布尔模式搜刮
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+performance -tuning' IN BOOLEAN MODE);
5.3 前缀搜刮
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('data*' IN BOOLEAN MODE);
5.4 结合日期过滤的全文搜刮
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('optimization')
AND order_date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH);
实践示例
场景描述
假设我们有一个电子商务平台，包含以下几张表：
[*] orders：订单表
[*]order_id (主键)
[*]customer_id (外键，关联 customers 表)
[*]order_date (订单日期)
[*]total_amount (订单总金额)
[*] customers：客户表
[*]customer_id (主键)
[*]name (客户姓名)
[*]email (客户邮箱)
[*]registration_date (注册日期)
[*] products：产品表
[*]product_id (主键)
[*]name (产品名称)
[*]description (产品描述)
[*]price (产品价格)
[*] order_items：订单项表
[*]order_item_id (主键)
[*]order_id (外键，关联 orders 表)
[*]product_id (外键，关联 products 表)
[*]quantity (购买数量)
[*]item_price (单个商品价格)
[*] reviews：批评表
[*]review_id (主键)
[*]product_id (外键，关联 products 表)
[*]customer_id (外键，关联 customers 表)
[*]rating (评分，1-5)
[*]comment (批评内容)
问题描述
我们需要生成一份陈诉，包含以下信息：
[*]每个客户的姓名、邮箱、注册日期。
[*]每个客户在过去一年内的订单总数和总金额。
[*]每个客户在过去一年内购买的产品数量最多的前 5 个产品及其购买数量。
[*]每个客户在过去一年内对产品留下的平均评分（如果有批评）。
[*]对于每个客户，显示他们最近一次的订单日期。
此外，我们还渴望对客户的批评举行全文搜刮，查找包含特定关键词（如 “great” 或 “excellent”）的批评，并盘算这些批评的数量。
综合查询
WITH
-- 1. 获取每个客户在过去一年内的订单统计
customer_orders AS (
SELECT
c.customer_id,
c.name,
c.email,
c.registration_date,
COUNT(o.order_id) AS order_count,
SUM(o.total_amount) AS total_spent,
MAX(o.order_date) AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
GROUP BY c.customer_id, c.name, c.email, c.registration_date
),
-- 2. 获取每个客户在过去一年内购买的产品数量最多的前 5 个产品
top_products AS (
SELECT
c.customer_id,
p.name AS product_name,
SUM(oi.quantity) AS total_quantity,
ROW_NUMBER() OVER (PARTITION BY c.customer_id ORDER BY SUM(oi.quantity) DESC) AS rank
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
GROUP BY c.customer_id, p.name
),
-- 3. 获取每个客户在过去一年内对产品的平均评分
average_ratings AS (
SELECT
r.customer_id,
AVG(r.rating) AS avg_rating
FROM reviews r
WHERE r.review_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
GROUP BY r.customer_id
),
-- 4. 全文搜索评论，查找包含特定关键词的评论数量
positive_reviews AS (
SELECT
r.customer_id,
COUNT(*) AS positive_review_count
FROM reviews r
WHERE MATCH(r.comment) AGAINST('great excellent' IN BOOLEAN MODE)
AND r.review_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
GROUP BY r.customer_id
)
-- 最终查询，组合所有信息
SELECT
co.customer_id,
co.name,
co.email,
co.registration_date,
co.order_count,
co.total_spent,
co.last_order_date,
ap.avg_rating,
pr.positive_review_count,
-- 使用字符串聚合函数拼接前 5 个产品及其购买数量
GROUP_CONCAT(
CONCAT(tp.product_name, ': ', tp.total_quantity)
ORDER BY tp.rank
SEPARATOR '; '
) AS top_5_products
FROM customer_orders co
LEFT JOIN average_ratings ap ON co.customer_id = ap.customer_id
LEFT JOIN positive_reviews pr ON co.customer_id = pr.customer_id
LEFT JOIN top_products tp ON co.customer_id = tp.customer_id AND tp.rank <= 5
GROUP BY co.customer_id, co.name, co.email, co.registration_date, co.order_count, co.total_spent, co.last_order_date, ap.avg_rating, pr.positive_review_count;
解释
[*] customer_orders CTE：利用 WITH 子句创建一个公共表表达式（CTE），盘算每个客户在过去一年内的订单总数、总金额以及最近一次的订单日期。
[*] top_products CTE：通过 ROW_NUMBER() 窗口函数为每个客户购买的产品按购买数量举行排名，并获取前 5 个产品及其购买数量。
[*] average_ratings CTE：盘算每个客户在过去一年内对产品的平均评分。
[*] positive_reviews CTE：利用全文搜刮功能，查找包含特定关键词（如 “great” 或 “excellent”）的批评，并盘算这些批评的数量。
[*] 最终查询：将所有 CTE 的效果结合起来，生成最终的陈诉。利用 GROUP_CONCAT() 函数将每个客户的前 5 个产品及其购买数量拼接成一个字符串，以便在效果集中显示。
优化建议
[*] 索引优化：确保在 orders、order_items、reviews 等表的相干列上创建适当的索引，以提高查询性能。例如，在 orders(order_date)、order_items(order_id, product_id) 和 reviews(review_date) 上创建索引。
[*] 分区表：如果数据量非常大，可以考虑对 orders 和 reviews 表举行分区，按年份或月份举行分区，以加快查询。
[*] 缓存：对于频繁实行的查询，可以考虑利用查询缓存或应用层缓存来减少数据库负载。
[*] 全文索引：确保在 reviews(comment) 字段上创建全文索引，以提高全文搜刮的效率。
总结
这个综合查询展示了怎样结合多种高级查询技术，如 CTE、窗口函数、全文搜刮和复杂连接，来办理复杂的业务需求。通过这种方式，你可以生成具体的陈诉，帮助你更好地理解客户行为和产品表现。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]