【MySQL 进阶之路】基础语法及优化技巧

MySQL DML 基础语法及优化技巧

一、DML（数据操作语言）概述

DML 是数据库操作语言的子集，用于数据的增、删、改、查四个基本操作。MySQL 中的 DML 操作通常是指以下四种基本操作：

• INSERT：插入数据
• SELECT：查询数据
• UPDATE：更新数据
• DELETE：删除数据

二、增（INSERT）语法

在 MySQL 中，插入数据的语法使用 INSERT INTO 关键字。

语法：

-- 向表中插入一行数据
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

示例：

INSERT INTO employees (name, age, position)
VALUES ('Alice', 28, 'Engineer');

如果插入所有列，可以省略列名：

INSERT INTO employees
VALUES (1, 'Alice', 28, 'Engineer');

三、删（DELETE）语法

删除表中的记录使用 DELETE 语句。

语法：

DELETE FROM table_name WHERE condition;

示例：

DELETE FROM employees WHERE name = 'Alice';

如果没有 WHERE 条件，DELETE 会删除表中所有记录。注意： 这种操作是不可恢复的。

四**、改（UPDATE）语法**

更新表中现有的数据使用 UPDATE 语句。

语法：

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

示例：

UPDATE employees
SET position = 'Senior Engineer'
WHERE name = 'Alice';

五、查（SELECT）语法

MySQL 中的查询操作用于从表中检索数据，通常使用 SELECT 语句。

单表查询：

SELECT column1, column2 FROM table_name WHERE condition;

示例：

SELECT name, age FROM employees WHERE position = 'Engineer';

联合查询（联表查询）：
联表查询是指从多个表中检索数据，最常见的类型是 内连接（INNER JOIN） 和 外连接（LEFT JOIN, RIGHT JOIN）。

内连接（INNER JOIN）： 返回两个表中匹配的记录。

SELECT A.name, B.department_name
FROM employees A
INNER JOIN departments B
ON A.department_id = B.id;

左外连接（LEFT JOIN）： 返回左表的所有记录，以及右表中匹配的记录。如果右表没有匹配，则返回 NULL。

SELECT A.name, B.department_name
FROM employees A
LEFT JOIN departments B
ON A.department_id = B.id;

右外连接（RIGHT JOIN）： 返回右表的所有记录，以及左表中匹配的记录。如果左表没有匹配，则返回 NULL。

SELECT A.name, B.department_name
FROM employees A
RIGHT JOIN departments B
ON A.department_id = B.id;

全外连接（FULL JOIN）： 返回两个表的所有记录。如果没有匹配的记录，另一个表会返回 NULL（MySQL 不直接支持 FULL OUTER JOIN，但可以使用 UNION 来模拟）。

六、聚合函数（Aggregation Functions）

MySQL 提供了一些聚合函数来对数据进行汇总处理，常见的聚合函数包括：

• COUNT：计算记录数
• SUM：计算和
• AVG：计算平均值
• MAX：查找最大值
• MIN：查找最小值

示例：

SELECT AVG(age) FROM employees WHERE position = 'Engineer';

七、GROUP BY 和 HAVING

GROUP BY 用于将查询结果按某列进行分组，而 HAVING 用于过滤分组后的结果。

示例：

SELECT department_id, COUNT(*) AS num_employees
FROM employees
GROUP BY department_id
HAVING num_employees > 5;

• WHERE：用于对原始数据进行过滤，应用于 GROUP BY 前。
• HAVING：用于过滤分组后的结果，应用于 GROUP BY 后。

八、连接优化：

联表查询中，尤其是当数据量大的时候，如何优化查询性能是非常重要的。常见的优化技巧包括：

1. 使用合适的索引：

   • 索引可以显著提高查询速度，尤其是联表查询时。
   • 确保联接条件（ON 或 WHERE 条件中）涉及的字段有索引。

2. 小表驱动大表：

   • 在联表查询时，尽量使用小表作为驱动表（外层表），减少查询中涉及大表的扫描次数。

3. 避免使用 SELECT *：

   • 使用 SELECT * 会返回表中的所有字段，而如果只需要部分字段，应该显式列出。这不仅可以提高查询效率，还能减少内存使用。
   • 示例：

   SELECT name, age FROM employees WHERE position = 'Engineer';
   

4. 优化连接算法：

   • 嵌套循环连接（Nested Loop Join）：这是最常见的连接算法，但性能较低，尤其是当表非常大的时候。
   • 块嵌套循环连接（Block Nested Loop Join）：通过缓存小表的数据，减少对大表的多次扫描。
   • 哈希连接（Hash Join）：适用于没有索引的连接操作，通过构建哈希表进行连接。

5. 使用合适的连接类型：

   • 内连接（INNER JOIN）：在大多数情况下，使用内连接可以减少返回的结果集，优化查询性能。
   • 外连接（LEFT JOIN, RIGHT JOIN）：当需要保留一个表中的所有记录时，使用外连接，尽量避免不必要的外连接。

6. 避免笛卡尔积：

   • 笛卡尔积发生在没有连接条件时，会返回两个表中所有可能的组合，结果集行数等于两个表行数的乘积，这通常不是我们想要的结果。

九、DML 的事务控制

对于数据库操作中的增、删、改等 DML 操作，MySQL 提供了事务控制，可以确保数据的一致性和完整性。常见的事务控制语句有：

• BEGIN TRANSACTION：开始一个事务。
• COMMIT：提交事务，永久保存数据变更。
• ROLLBACK：回滚事务，撤销事务中的所有操作。

示例：

BEGIN TRANSACTION;UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 2;COMMIT;

结论

熟悉并掌握 MySQL 的 DML 基础语法对于数据库开发至关重要。通过合理运用 INSERT、SELECT、UPDATE、DELETE，以及各种查询优化技巧，可以显著提高数据库的性能和查询效率。在实际开发过程中，合理的索引设计、联表查询优化以及事务管理是提升应用性能和数据一致性的重要手段。

---

希望这个笔记对你有所帮助！如果你有任何问题或需要进一步探讨某个内容，欢迎随时提问。