MySQL命令详解

本文最后更新于：4 年前

引言

在日常使用 MySQL 数据库时，我们会频繁接触到不同类别的 SQL 命令，如增删改查（DML）、定义和管理数据库对象（DDL）、控制权限（DCL）以及管理事务（TCL）等。除了这些通用的 SQL 语法，MySQL 还提供了一系列针对自身特性的命令和工具，用于执行数据库的优化、备份、恢复、用户管理、数据导入导出以及性能监控等操作。本文将按照常见分类，对 MySQL 中的各类命令进行详细的介绍与示例说明。

DDL

数据定义语言。

CREATE DATABASE - 创建数据库

语法格式

1
2
3

CREATE DATABASE [IF NOT EXISTS] database_name
    [DEFAULT CHARACTER SET charset_name]
    [COLLATE collation_name];

常见参数

IF NOT EXISTS：如果数据库已存在，则不报错。
DEFAULT CHARACTER SET：指定数据库默认字符集。
COLLATE：指定字符集对应的校对规则。

示例

1
2
3

CREATE DATABASE IF NOT EXISTS mydb
    DEFAULT CHARACTER SET utf8mb4
    COLLATE utf8mb4_general_ci;

注意事项

数据库名不能与已有的数据库重复（使用 IF NOT EXISTS 可避免错误）。
修改数据库默认字符集和校对规则只影响新建的数据表，已存在的表不会更改。
执行该命令需要相应的权限（如 CREATE 权限）。

DROP DATABASE - 删除数据库

语法格式

1	`DROP DATABASE [IF EXISTS] database_name;`

常见参数

IF EXISTS：如果数据库不存在，则不报错。

示例

1	`DROP DATABASE IF EXISTS mydb;`

注意事项

删除数据库会同时删除该数据库中的所有数据和对象，操作不可逆。
建议在删除前先备份数据，避免误删。
执行该命令需要相应的权限（如 DROP 权限）。

ALTER DATABASE - 修改数据库属性

语法格式

1
2
3

ALTER DATABASE database_name
    [DEFAULT CHARACTER SET charset_name]
    [COLLATE collation_name];

常见参数

DEFAULT CHARACTER SET：修改数据库的默认字符集。
COLLATE：修改数据库的校对规则。

示例

1
2
3

ALTER DATABASE mydb
    DEFAULT CHARACTER SET utf8
    COLLATE utf8_general_ci;

注意事项

该命令主要用于修改数据库的默认设置，对已存在的表不会产生直接影响。
修改字符集和校对规则时需谨慎，避免数据出现编码问题。
执行该命令需要相应的权限。

CREATE TABLE - 创建数据表

语法格式

CREATE TABLE [IF NOT EXISTS] table_name (
    column1 datatype [column_constraint],
    column2 datatype [column_constraint],
    ...
    [table_constraint]
) [table_options];

常见参数

IF NOT EXISTS：如果表已存在，则不报错。
datatype、constraint：数据类型和约束。
table_options：如 ENGINE、DEFAULT CHARSET 等选项。

示例

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

注意事项

表名和字段名建议遵循命名规范，避免使用 MySQL 保留关键字。
选择合适的数据类型和存储引擎，能提高性能。
确保设计的主键、索引满足查询需求。

DROP TABLE - 删除数据表

语法格式

1	`DROP TABLE [IF EXISTS] table_name [, table_name2, ...];`

常见参数

IF EXISTS：如果表不存在，则不报错。

示例

1	`DROP TABLE IF EXISTS users;`

注意事项

删除表会同时删除表中的所有数据和结构，操作不可逆。
建议在删除前备份重要数据。
执行该命令需要有 DROP 权限。

ALTER TABLE - 修改表结构

语法格式

1 2	`ALTER TABLE table_name alteration_action [, alteration_action2, ...];`

常见参数及操作

ADD COLUMN：添加新列

1	`ALTER TABLE table_name ADD COLUMN column_name datatype [constraint];`

DROP COLUMN：删除已有列

1	`ALTER TABLE table_name DROP COLUMN column_name;`

MODIFY COLUMN：修改列的数据类型或约束

1	`ALTER TABLE table_name MODIFY COLUMN column_name new_datatype [new_constraint];`

CHANGE COLUMN：重命名列并修改数据类型

1	`ALTER TABLE table_name CHANGE COLUMN old_name new_name new_datatype [constraint];`

ADD INDEX/PRIMARY KEY：添加索引或主键
1
ALTER TABLE table_name ADD INDEX IDX_X;
DROP INDEX/PRIMARY KEY：删除索引或主键
1
ALTER TABLE table_name DROP INDEX IDX_X;

RENAME INDEX：重命名索引（MySQL 8.0 及以上）

1	`ALTER TABLE table_name RENAME INDEX old_index_name TO new_index_name;`

示例

# 添加新列
ALTER TABLE users ADD COLUMN email VARCHAR(100) NOT NULL;

# 修改列数据类型
ALTER TABLE users MODIFY COLUMN age SMALLINT;

# 重命名列并修改类型
ALTER TABLE users CHANGE COLUMN name username VARCHAR(60) NOT NULL;

注意事项

ALTER 操作可能会锁定表，影响并发性能，建议在低峰期操作。
修改列的类型时要确保新数据类型兼容已有数据，避免数据丢失。
某些 ALTER 操作可能无法撤销，操作前建议备份数据。
若需要修改索引的列或类型，建议先 DROP 索引，再重新 CREATE 新索引。

TRUNCATE TABLE - 清空表数据

语法格式

1	`TRUNCATE TABLE table_name;`

常见参数

该命令没有其他参数，直接指定需要清空数据的表。

示例

1	`TRUNCATE TABLE users;`

注意事项

TRUNCATE TABLE 会快速删除所有记录，并重置 AUTO_INCREMENT 计数器。
与 DELETE 不同，TRUNCATE 通常不能被回滚（依赖存储引擎），操作不可逆。
执行该命令需要相应的权限，同时要谨慎使用以免误删数据。

CREATE INDEX - 创建索引

语法格式

1 2	`CREATE [UNIQUE] INDEX index_name ON table_name (column1 [ASC\|DESC], column2 [ASC\|DESC], ...);`

常见参数

UNIQUE：创建唯一索引，保证索引列中所有值唯一。
ASC/DESC：指定列的排序顺序（默认通常为 ASC）。
USING BTREE/FULLTEXT/HASH：指定索引类型，可根据存储引擎支持情况决定。

示例

1	`CREATE INDEX idx_username ON users (username);`

注意事项

索引能提高查询性能，但过多或不合理的索引会影响写操作（如 INSERT、UPDATE、DELETE）的效率。
在创建唯一索引时，要确保数据中没有重复值，否则会导致创建失败。
部分存储引擎支持的索引类型有限，需参考具体引擎文档。

DROP INDEX - 删除索引

语法格式

1	`DROP INDEX index_name ON table_name;`

常见参数

需要指定 index_name 及其所在的 table_name。

示例

1	`DROP INDEX idx_username ON users;`

注意事项

删除索引会影响查询优化计划，操作前应评估索引对系统性能的影响。
删除索引不会影响表中实际数据，但可能影响外键约束（如涉及唯一性要求时）。

CREATE VIEW - 创建视图

语法格式

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION];

常见参数

OR REPLACE：如果视图已存在，则替换原视图定义。
ALGORITHM：指定视图处理算法，常用值包括 MERGE（将视图查询合并到主查询中）和 TEMPTABLE（使用临时表存储中间结果）。
column_list：定义视图中各列的名称（可选，如果不指定则使用查询中返回的列名）。
WITH [CASCADED | LOCAL] CHECK OPTION：用于限制通过视图插入或更新数据时，必须满足视图定义的条件。

示例

CREATE VIEW user_view AS
SELECT id, username, email
FROM users
WHERE status = 'active';

注意事项

视图本质上是存储的查询语句，不存储实际数据，修改视图后，查询结果会实时反映最新数据。
使用 CHECK OPTION 可防止插入不符合视图条件的数据，但要注意级联（CASCADED）和本地（LOCAL）选项的差异。
部分复杂视图（如包含聚合、子查询、联接等）可能会受到更新限制。

DROP VIEW - 删除视图

语法格式

1	`DROP VIEW [IF EXISTS] view_name [, view_name2, ...];`

常见参数

IF EXISTS：如果视图不存在，则不报错，可同时删除多个视图。

示例

1	`DROP VIEW IF EXISTS user_view;`

注意事项

删除视图只会移除视图定义，不会删除视图所引用的底层数据表。
视图被其他对象（如存储过程）引用时，删除前需评估可能带来的影响。

ALTER VIEW - 修改视图

语法格式

1
2
3

ALTER VIEW view_name
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION];

常见参数

参数基本与 CREATE VIEW 类似，主要用于修改视图的查询定义。
可使用 WITH CHECK OPTION 限制通过视图进行数据操作时的数据有效性。

示例

ALTER VIEW user_view AS
SELECT id, username, email, created_at
FROM users
WHERE status = 'active'
WITH CHECK OPTION;

注意事项

ALTER VIEW 会完全替换原有的视图定义，因此修改时应确保新定义与业务逻辑一致。
对于某些复杂视图，修改后可能导致权限、依赖关系或更新限制等问题，需要额外验证。
视图的更新性受限于其查询定义，若包含多表联接、聚合等，可能会导致视图不可更新。

CREATE TRIGGER - 创建触发器

语法格式

CREATE TRIGGER trigger_name
    {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
    ON table_name
    FOR EACH ROW
    trigger_body

常见参数

触发时间：BEFORE 或 AFTER，决定触发器在操作前还是操作后执行。
触发事件：INSERT、UPDATE 或 DELETE，指定触发器响应的数据修改操作。
FOR EACH ROW：MySQL 只支持逐行触发器，必须为每一行操作执行触发器体。
trigger_body：可以是一个简单的 SQL 语句或一个包含多条 SQL 语句的 BEGIN...END 块。

示例

CREATE TRIGGER before_insert_users
BEFORE INSERT ON users
FOR EACH ROW
SET NEW.created_at = NOW();

注意事项

每个表每个操作（INSERT、UPDATE、DELETE）只能有一个触发器。
触发器中的操作要尽量保持简单，以免影响数据修改性能。
触发器在执行过程中出现错误会导致原始操作失败，需谨慎调试和使用。

DROP TRIGGER - 删除触发器

语法格式

1	`DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;`

常见参数

IF EXISTS：如果触发器不存在则不报错。
可通过指定 schema_name 来明确触发器所属的数据库。

示例

1	`DROP TRIGGER IF EXISTS before_insert_users;`

注意事项

删除触发器将移除与之绑定的自动执行逻辑，操作不可逆。
执行该命令需要相应的权限。

CREATE PROCEDURE - 创建存储过程

语法格式

DELIMITER //
CREATE PROCEDURE procedure_name ([IN|OUT|INOUT] param1 datatype, ...)
BEGIN
    -- SQL 语句块
    statement;
    ...
END //
DELIMITER ;

常见参数

参数类型：IN（输入参数）、OUT（输出参数）、INOUT（输入输出参数）。
语句块：通常使用 BEGIN ... END 包含多条 SQL 语句。
需更改定界符（DELIMITER）以避免与存储过程内的语句结束符冲突。

示例

DELIMITER //
CREATE PROCEDURE sp_get_users()
BEGIN
    SELECT * FROM users;
END //
DELIMITER ;

注意事项

存储过程不会直接返回结果集，而是通过 SELECT 语句或输出参数传递数据。
调用时需要使用 CALL procedure_name();。
需要确保过程内的逻辑正确，避免影响事务和并发性能。

DROP PROCEDURE - 删除存储过程

语法格式

1	`DROP PROCEDURE [IF EXISTS] procedure_name;`

常见参数

IF EXISTS：如果存储过程不存在，则不报错。

示例

1	`DROP PROCEDURE IF EXISTS sp_get_users;`

注意事项

删除存储过程后，依赖该过程的业务逻辑将失效，操作前应确认。
需要相应的权限才能删除存储过程。

CREATE FUNCTION - 创建用户自定义函数

语法格式

DELIMITER //
CREATE FUNCTION function_name ([IN] param1 datatype, ...)
RETURNS return_data_type
    [characteristic ...]
BEGIN
    -- SQL 语句块
    RETURN expression;
END //
DELIMITER ;

常见参数

参数：函数通常只支持 IN 参数。
RETURNS：必须定义返回值的数据类型。
characteristic：可选特性如 DETERMINISTIC、NO SQL、READS SQL DATA 等，用以描述函数的行为和优化器提示。

示例

DELIMITER //
CREATE FUNCTION get_full_name(first_name VARCHAR(50), last_name VARCHAR(50))
RETURNS VARCHAR(101)
DETERMINISTIC
BEGIN
    RETURN CONCAT(first_name, ' ', last_name);
END //
DELIMITER ;

注意事项

用户自定义函数（UDF）可以在 SQL 语句中调用，但需保证逻辑简单且执行效率高。
函数必须返回一个值，且返回类型不可省略。
权限要求较高，需确保用户有创建函数的权限。

DROP FUNCTION - 删除用户自定义函数

语法格式

1	`DROP FUNCTION [IF EXISTS] function_name;`

常见参数

IF EXISTS：如果函数不存在，则不报错。

示例

1	`DROP FUNCTION IF EXISTS get_full_name;`

注意事项

删除函数后，所有调用该函数的 SQL 语句可能会失效。
需确认无业务依赖后再执行删除操作。

CREATE EVENT - 创建事件

语法格式

CREATE EVENT [IF NOT EXISTS] event_name
ON SCHEDULE {AT timestamp | EVERY interval [STARTS timestamp] [ENDS timestamp]}
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
DO
event_body;

常见参数

ON SCHEDULE：指定事件的调度方式，可以是单次执行（AT）或周期性执行（EVERY）。
STARTS/ENDS：定义周期性事件的起始和结束时间。
ON COMPLETION [NOT] PRESERVE：决定事件执行完毕后是否保留事件信息（默认一次性事件在执行后会被删除）。
ENABLE/DISABLE：控制事件是否启用。

示例

CREATE EVENT IF NOT EXISTS cleanup_event
ON SCHEDULE EVERY 1 DAY
DO
DELETE FROM logs WHERE log_date < NOW() - INTERVAL 30 DAY;

注意事项

必须启用事件调度器（SET GLOBAL event_scheduler = ON;）才能使事件生效。
周期性事件需要合理设置时间范围和间隔，避免对系统性能造成过大影响。
事件执行后可能影响数据，建议在生产环境前充分测试。

DROP EVENT - 删除事件

语法格式

1	`DROP EVENT [IF EXISTS] event_name;`

常见参数

IF EXISTS：如果事件不存在则不报错。

示例

1	`DROP EVENT IF EXISTS cleanup_event;`

注意事项

删除事件将停止定时任务，不再自动执行预定操作。
操作前应确认事件不再需要或已迁移至其他机制中。

DML

数据库操作语言，主要用于操作数据表。

INSERT INTO - 插入数据

语法格式

# 单条插入
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
INTO table_name
SET column1 = value1[, (column2 = value2), ...];

# 批量插入
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
INTO table_name [(column1, column2, ...)]
VALUES (value1, value2, ...)[, (value1, value2, ...)], ...;

常见参数

LOW_PRIORITY：在没有其他读取操作时再执行插入。
DELAYED：将插入操作延迟执行（注意：该选项在新版本中已不推荐使用）。
IGNORE：在遇到错误（如主键冲突）时跳过出错的行，而非终止整个操作。

示例

INSERT INTO users
SET username = 'alice', email = 'alice@example.com', age = 25;

INSERT INTO users (username, email, age)
VALUES ('alice', 'alice@example.com', 25),
       ('bob', 'bob@example.com', 30);

注意事项

如果不指定列名，则要求所有列都必须按表的列顺序提供值。
使用 IGNORE 可避免因重复键值导致整个语句失败，但也可能隐藏数据异常。
插入大量数据时，可采用批量插入以提高效率。

UPDATE - 修改数据

语法格式

UPDATE [LOW_PRIORITY] table_name
SET column1 = value1, column2 = value2, ...
[WHERE condition]
[ORDER BY ...]
[LIMIT row_count];

常见参数

LOW_PRIORITY：延迟更新，待没有读取操作时再执行。
WHERE：指定更新条件，避免对所有记录进行修改。
ORDER BY 与 LIMIT：配合使用时可限制更新的行数及更新顺序。

示例

1
2
3

UPDATE users
SET age = age + 1
WHERE username = 'alice';

注意事项

未加 WHERE 条件时，UPDATE 会作用于所有记录，可能造成数据错误。
更新过程中建议使用事务管理，确保数据一致性。
同一表上同时有多个更新操作时，要注意锁机制对并发性能的影响。

DELETE FROM - 删除数据

语法格式

DELETE [LOW_PRIORITY] FROM table_name
[WHERE condition]
[ORDER BY ...]
[LIMIT row_count];

常见参数

LOW_PRIORITY：延迟删除操作，待没有读取操作时再执行。
WHERE：指定删除条件，避免误删所有记录。
ORDER BY 与 LIMIT：用于限定删除的记录数及顺序。

示例

1 2	`DELETE FROM users WHERE age < 18;`

注意事项

同 UPDATE 一样，缺少 WHERE 条件会删除表中所有记录。
对大表执行 DELETE 操作时，可能会锁表或导致性能问题，必要时可分批删除。
删除操作建议做好数据备份以防误删。

REPLACE INTO - 插入数据

语法格式

1 2	`REPLACE [LOW_PRIORITY] INTO table_name [(column1, column2, ...)] VALUES (value1, value2, ...)[, (value1, value2, ...)], ...;`

常见参数

与 INSERT 类似，但 REPLACE 语句在遇到主键或唯一索引冲突时会先删除旧记录，再插入新记录。

示例

1 2	`REPLACE INTO users (id, username, email) VALUES (1, 'alice', 'alice_new@example.com');`

注意事项

REPLACE 的执行过程先执行删除操作，再插入新记录，因此可能引起触发器执行、外键约束及自动递增值重置等问题。
对于需要更新部分字段的场景，推荐使用 INSERT ... ON DUPLICATE KEY UPDATE 语法以减少副作用。
REPLACE 操作可能导致删除记录的副作用（例如级联删除）。

INSERT INTO … SELECT - 查询并插入

语法格式

INSERT [LOW_PRIORITY | IGNORE] INTO target_table [(column1, column2, ...)]
SELECT expression1, expression2, ...
FROM source_table
[WHERE condition];

常见参数

IGNORE：忽略因数据冲突引起的错误。
WHERE：筛选需要插入的数据行。

示例

INSERT INTO backup_users (username, email, age)
SELECT username, email, age
FROM users
WHERE created_at < '2022-01-01';

注意事项

源表和目标表的字段对应和数据类型要匹配。
INSERT INTO ... SELECT 常用于数据迁移和备份，操作前建议确认数据一致性。
如果目标表存在约束（如唯一性约束），可能导致部分行插入失败。

LOAD DATA INFILE - 批量导入数据

语法格式

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE table_name
[CHARACTER SET charset_name]
[FIELDS
    [TERMINATED BY 'string']
    [OPTIONALLY] ENCLOSED BY 'char'
    [ESCAPED BY 'char']
]
[LINES
    [STARTING BY 'string']
    [TERMINATED BY 'string']
]
[IGNORE number LINES]
[(column1, column2, ...)];

常见参数

LOCAL：文件位于客户端而非服务器。
REPLACE/IGNORE：指定遇到重复记录时的处理方式。
CHARACTER SET：指定文件的字符集。
FIELDS 和 LINES：定义字段和行的分隔符、包围符及转义字符。
IGNORE n LINES：跳过文件开头的 n 行数据（如标题行）。

示例

LOAD DATA LOCAL INFILE '/path/to/data.csv'
INTO TABLE users
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

注意事项

使用 LOAD DATA INFILE 时需确认文件权限及路径正确，MySQL 服务器需要有读取该文件的权限。
不同平台的换行符可能不同，需根据实际情况调整 LINES TERMINATED BY 的参数。
若数据中含有特殊字符，注意转义设置，确保数据正确导入。

DQL

数据查询语言。

SELECT - 查询数据

语法格式

1 2	`SELECT [ALL \| DISTINCT] select_expr [, select_expr ...] FROM table_reference`

常见参数

ALL：默认，返回所有记录（包括重复值）。
DISTINCT：用于去除重复行（见下一部分说明）。
select_expr：可以是一个或多个列、表达式，甚至是函数计算的结果。
可选的 FROM，用于进一步限定结果。

示例

1 2	`SELECT id, username, email FROM users;`

注意事项

如果省略 FROM 子句，MySQL 允许从表达式（如 SELECT 1+1;）中返回结果。
根据实际业务需要选择合适的列和子句，避免返回不必要的数据。
大型查询时注意优化（如使用索引、合理拆分子查询等）。

DISTINCT - 去重查询

语法格式

在 SELECT 语句中使用，格式如下：

1 2	`SELECT DISTINCT select_expr [, select_expr ...] FROM table_name;`

常见参数

DISTINCT：放在 SELECT 后，用于剔除结果集中的重复记录。

示例

1 2	`SELECT DISTINCT country FROM users;`

（返回 users 表中不同的 country 值。）

注意事项

DISTINCT 会对整个结果行进行判断，相比普通 SELECT 会增加额外的排序或去重开销。
对性能有一定影响，尤其在大数据量查询中，建议仅在必要时使用。

WHERE - 过滤查询条件

语法格式

1
2
3

SELECT columns
FROM table_name
WHERE condition;

常见参数

condition：可以包含比较运算符（=、<>、>、<、>=、<=）、逻辑运算符（AND、OR、NOT）、BETWEEN、LIKE、IN、IS NULL 等，用于限定返回的行。

示例

1
2
3

SELECT id, username, email
FROM users
WHERE age >= 18 AND status = 'active';

注意事项

WHERE 子句过滤的是行数据，在分组（GROUP BY）前执行。
对涉及大量数据的列，应考虑建立索引以提高过滤效率。
注意条件中数据类型的匹配和空值的处理。

ORDER BY - 对查询结果排序

语法格式

1
2
3

SELECT columns
FROM table_name
ORDER BY expression [ASC | DESC] [, expression [ASC | DESC] ...];

常见参数

expression：可以是列名、表达式或列的别名。
ASC（默认）或 DESC：指定升序或降序排序。

示例

1
2
3

SELECT id, username, created_at
FROM users
ORDER BY created_at DESC, username ASC;

注意事项

排序会增加额外的处理开销，尤其当数据量大且没有合适的索引时。
在使用 ORDER BY 时，建议对排序字段建立索引以提高性能。
排序顺序可以混合使用升序和降序，但要确保逻辑清晰。

GROUP BY - 对查询结果分组

语法格式

SELECT columns, aggregate_function(column)
FROM table_name
WHERE condition
GROUP BY expression [, expression ...];

常见参数

expression：通常为分组依据的列，可以是单个列或多个列。
常用聚合函数：如 COUNT、SUM、AVG、MIN、MAX 等，用于对每个分组进行统计计算。

示例

1
2
3

SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country;

注意事项

GROUP BY 在 WHERE 过滤之后、HAVING 过滤之前执行。
SELECT 中的非聚合列必须出现在 GROUP BY 子句中（或者依赖于 MySQL 的扩展特性，但建议遵守标准写法）。
对分组结果进行排序时，可结合 ORDER BY 使用。

HAVING - 过滤分组后的数据

语法格式

SELECT columns, aggregate_function(column)
FROM table_name
GROUP BY expression
HAVING aggregate_function(column) condition;

常见参数

condition：用于对分组后的结果进行过滤，通常基于聚合函数的计算结果（如 COUNT、SUM 等）。

示例

SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country
HAVING COUNT(*) > 10;

（返回用户数大于 10 的各 country 分组）

注意事项

HAVING 子句用于过滤经过 GROUP BY 分组后的数据，与 WHERE 子句作用的层次不同。
当不使用聚合函数时，HAVING 也可用于过滤，但建议使用 WHERE，一般情况下，HAVING 应仅用于聚合数据的过滤。
HAVING 会在分组计算后执行，故相对 WHERE 有额外的性能开销，尽量将非聚合的过滤条件放到 WHERE 子句中。

LIMIT - 限制查询结果的条数

语法格式

1
2
3

SELECT columns
FROM table_name
LIMIT [offset,] row_count;

常见参数

row_count：返回记录的最大行数。
offset：可选参数，表示从结果集的第几行开始返回数据（注意：offset 从 0 开始计数）。

示例

SELECT * FROM users
LIMIT 10;  -- 返回前 10 行记录

SELECT * FROM users
LIMIT 5, 10;  -- 从第 6 行开始返回 10 行记录

注意事项

在大数据量查询中，使用较大的 offset 会影响性能，因为 MySQL 需要扫描并丢弃 offset 之前的记录。
对结果顺序有要求时应配合 ORDER BY 子句使用。

JOIN - 连接多个表进行查询

语法格式

SELECT columns
FROM table1
[INNER | LEFT | RIGHT | CROSS] JOIN table2
ON join_condition;

常见参数

INNER JOIN：返回两个表中满足联接条件的交集记录，也可以直接写 JOIN，默认即为 INNER JOIN。
**LEFT JOIN (LEFT OUTER JOIN)**：返回左表的所有记录及右表中满足联接条件的记录，右表无匹配时返回 NULL。
**RIGHT JOIN (RIGHT OUTER JOIN)**：返回右表的所有记录及左表中满足联接条件的记录，左表无匹配时返回 NULL。
CROSS JOIN：返回两个表的笛卡尔积。

示例

SELECT u.username, o.order_date
FROM users u
INNER JOIN orders o
ON u.id = o.user_id;

注意事项

联接条件（ON 子句）必须正确指定，否则可能导致数据重复或返回不正确结果。
不同的 JOIN 类型会影响结果集内容和性能，选择时应根据业务逻辑和数据量进行判断。
多表联接时建议为关联字段建立索引，以提高查询效率。

UNION - 合并多个查询结果集

语法格式

1
2
3

SELECT columns FROM table1
UNION [ALL]
SELECT columns FROM table2;

常见参数

UNION ALL：返回所有记录，包括重复数据，不进行去重操作。默认 UNION 会自动去重，即返回不重复的记录集。

示例

1
2
3

SELECT username FROM users
UNION
SELECT username FROM admins;

注意事项

联合的各个 SELECT 语句的字段数及对应数据类型必须一致。
使用 UNION 时，默认进行去重处理，可能会增加额外的排序开销，若允许重复可使用 UNION ALL。
UNION 适用于合并来自不同表或查询的结果，但不支持 ORDER BY 用于每个子查询，只能在最外层使用。

EXISTS - 判断子查询是否返回结果

语法格式

1
2
3

SELECT columns
FROM table_name
WHERE EXISTS (subquery);

常见参数

subquery：子查询，通常返回一列数据，用于检查是否存在满足条件的记录。EXISTS 子句返回布尔值，如果子查询至少返回一行数据，则为 TRUE，否则为 FALSE。
NOT EXISTS：子查询逻辑取反。

示例

SELECT username
FROM users
WHERE EXISTS (
    SELECT 1
    FROM orders
    WHERE orders.user_id = users.id
);

注意事项

EXISTS 子查询通常用于相关子查询，能在检查条件时较快返回结果。
与 IN 子查询相比，EXISTS 在处理大量数据时可能更高效，尤其当子查询结果集较大时。
子查询中的 SELECT 子句一般不需要返回具体数据，常用 SELECT 1 或 SELECT *。

IN - 判断值是否属于某个集合

语法格式

1
2
3

SELECT columns
FROM table_name
WHERE column IN (value1, value2, ...);

常见参数

NOT IN：判断逻辑取反。
列表中的值可以是常量列表，也可以是子查询返回的结果集，支持嵌套使用，与 NOT IN 类似用于排除指定值。

示例

1
2
3

SELECT username
FROM users
WHERE country IN ('USA', 'Canada', 'UK');

注意事项

使用 IN (子查询) 时，子查询返回的值应与外层比较字段数据类型一致。
对于较大的常量列表，IN 子句可能影响查询效率，此时可以考虑将数据存储到临时表中进行联接查询。
当子查询返回 NULL 时，需注意与 NOT IN 的区别可能引发意外结果。

LIKE - 模糊匹配查询

语法格式

1
2
3

SELECT columns
FROM table_name
WHERE column LIKE pattern;

常见参数

NOT LIKE：模糊匹配逻辑取反。
pattern：指定匹配模式，可以包含通配符
- **%**：匹配任意数量（零个或多个）任意字符
- **_**：匹配单个任意字符

示例

1
2
3

SELECT username
FROM users
WHERE username LIKE 'A%';  -- 匹配以 A 开头的用户名

注意事项

LIKE 匹配是大小写敏感或不敏感取决于字符集和校对规则。
使用通配符开头（如 %abc）可能导致全表扫描，影响查询性能。
对需要频繁模糊查询的字段可考虑建立全文索引。

AS - 为字段或表设置别名

语法格式

1 2	`SELECT expression AS alias_name FROM table_name;`

常见参数

alias_name：指定给列或表的别名，便于结果集阅读或简化查询语句。别名可以用于列、表达式，甚至表名，便于后续引用。

示例

1 2	`SELECT username AS user, email AS contact_email FROM users;`

注意事项

AS 是可选关键字，可以直接用空格分隔表达式和别名。
在复杂查询中使用别名能提高 SQL 的可读性和维护性。
别名应避免与表中字段名冲突，且尽量采用易于理解的命名。

SUBQUERY（子查询） - 在查询中嵌套其他查询

语法格式

子查询可以出现在 SELECT、FROM、WHERE、HAVING 等子句中，基本格式如下：

1
2
3

SELECT columns
FROM table_name
WHERE column operator (SELECT sub_columns FROM sub_table WHERE condition);

常见参数

相关子查询：子查询中引用外层查询的列。
非相关子查询：子查询独立执行，与外层无直接关联。

（子查询可用于 EXISTS、IN、比较运算符等上下文中。）

示例

-- 非相关子查询示例
SELECT username, email
FROM users
WHERE id IN (SELECT user_id FROM orders WHERE order_date > '2023-01-01');

-- 相关子查询示例
SELECT username,
       (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count
FROM users;

注意事项

子查询应尽量避免返回大量数据，以免影响整体查询性能。
当子查询中使用相关列时，每行外层数据都需执行一次子查询，可能导致效率问题。
可使用 JOIN 或 EXISTS 等方式重构子查询以提高性能和可维护性。
复杂子查询可以先通过临时表或视图分解，降低调试难度和提高执行效率。

TCL

事务控制语言。

START TRANSACTION - 开启事务

语法格式

1	`START TRANSACTION;`

常见参数

与 BEGIN 作用相同，用于标识事务的开始。
可与 SET TRANSACTION 结合使用，指定事务的隔离级别及读写属性（须在事务开始后立即设置）。

示例

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

注意事项

事务一旦启动，后续的修改操作不会立即生效，只有提交（COMMIT）后才会永久保存，若出现错误可使用 ROLLBACK 进行回滚。
请确保使用的存储引擎（如 InnoDB）支持事务操作。
长事务可能占用资源和锁定数据，建议尽快提交或回滚。

COMMIT - 提交事务

语法格式

COMMIT;

常见参数

无额外参数，直接提交当前事务中所有已执行的修改操作。

示例

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

注意事项

COMMIT 会使当前事务中的所有修改永久生效，无法回退，在执行前请确保数据正确性。
对于长事务，建议及时提交以减少锁等待和资源占用。
某些存储引擎（如 InnoDB）支持事务，而其他（如 MyISAM）则不支持，使用前请确认存储引擎类型。

ROLLBACK - 回滚事务

语法格式

1	`ROLLBACK;`

常见参数

无额外参数，用于撤销当前事务中自上次 COMMIT 或事务开始以来的所有修改。

示例

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- 发现错误或业务逻辑有问题，执行回滚
ROLLBACK;

注意事项

ROLLBACK 只能撤销当前事务内尚未提交的操作，一旦 COMMIT 后，无法使用 ROLLBACK 恢复。
在使用事务时，应结合应用逻辑判断错误场景，及时执行 ROLLBACK 以保持数据一致性。
某些复杂操作（例如涉及外部系统的数据同步）可能无法简单回滚，需谨慎设计事务边界。

SAVEPOINT - 设置事务保存点

语法格式

1	`SAVEPOINT savepoint_name;`

常见参数

savepoint_name：自定义的保存点名称，用于标识当前事务中一个中间状态。

示例

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
SAVEPOINT sp1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
-- 若后续操作出错，只回滚到保存点 sp1
ROLLBACK TO SAVEPOINT sp1;
COMMIT;

注意事项

SAVEPOINT 允许在事务中设置多个回滚点，便于部分撤销操作而非整个事务回滚。
保存点只在当前事务有效，事务结束后自动失效。
SAVEPOINT 操作会增加事务管理的复杂度，使用时应明确各保存点对应的业务逻辑阶段。

RELEASE SAVEPOINT - 删除指定的保存点

语法格式

1	`RELEASE SAVEPOINT savepoint_name;`

常见参数

savepoint_name：指定需要删除的保存点名称。

示例

START TRANSACTION;
UPDATE orders SET status = 'processing' WHERE order_id = 123;
SAVEPOINT sp_order;
-- 执行部分操作后，确认不需要再回滚到该保存点时，释放它
RELEASE SAVEPOINT sp_order;
COMMIT;

注意事项

RELEASE SAVEPOINT 会删除指定保存点，释放与该保存点相关的资源，一旦释放，不能再回滚到该保存点。
删除保存点对事务后续操作没有影响，但应在确定不需要部分回滚后再执行。
释放保存点后，事务依然处于原来的状态，不会自动提交或回滚。

SET TRANSACTION - 设置事务隔离级别

语法格式

1	`SET TRANSACTION [transaction_option [, transaction_option] ...];`

常见参数

transaction_option 主要包括：
- ISOLATION LEVEL level：设置事务的隔离级别（如 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE）。
- READ WRITE 或 READ ONLY：指定事务是否允许写操作。
SET TRANSACTION 必须在事务开始之前（即在 START TRANSACTION 或 BEGIN 之后立即执行，否则不会生效）。

示例

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE;
START TRANSACTION;
-- 执行事务操作...
COMMIT;

注意事项

SET TRANSACTION 的设置仅影响当前事务，事务结束后恢复默认设置。
不同隔离级别对并发控制、锁策略及性能有较大影响，选择时应根据业务需求权衡一致性和性能。
一般建议在事务开始后立即设置事务属性，以确保整个事务按照预期运行，否则可能需要重启事务。

DCL

数据控制语言。

GRANT - 授予用户权限

语法格式

1	`GRANT privilege_list ON object TO 'username'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];`

常见参数

privilege_list：可以是单个权限（如 SELECT、INSERT 等）或 ALL PRIVILEGES。
object：通常指定数据库对象，如 . 表示所有数据库的所有对象，db_name.* 表示某个数据库。
WITH GRANT OPTION：允许被授权用户将其拥有的权限继续授权给其他用户。
可在创建用户时同时设置密码（IDENTIFIED BY）。

示例

1	`GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'alice'@'localhost' IDENTIFIED BY 'securePwd' WITH GRANT OPTION;`

注意事项

GRANT 命令不仅分配权限，还可能隐式创建用户（视 MySQL 版本而定）。
授予权限时要注意最小权限原则，避免授予不必要的高级权限。
授权后若修改了权限表，建议使用 FLUSH PRIVILEGES 使更改生效（某些情况下系统自动刷新）。

REVOKE - 撤销用户权限

语法格式

1	`REVOKE privilege_list ON object FROM 'username'@'host';`

常见参数

privilege_list：需要撤销的权限列表。
object：权限对应的数据库或表。
指定用户时要与 GRANT 命令中一致。

示例

1	`REVOKE INSERT, UPDATE ON mydb.* FROM 'alice'@'localhost';`

注意事项

撤销权限后，用户相应的操作将被禁止。
若用户同时拥有多个权限来源（如继承或通过角色），撤销时需核查各个来源的权限。
撤销权限后若有权限缓存，可能需要 FLUSH PRIVILEGES 来刷新。

CREATE USER - 创建新用户

语法格式

1	`CREATE USER 'username'@'host' IDENTIFIED BY 'password';`

常见参数

**‘username‘@’host’**：用户名及允许登录的主机，如 'bob'@'localhost' 或 'bob'@'%'（允许任意主机）。
**IDENTIFIED BY ‘password’**：指定用户的登录密码，部分版本支持更多认证插件设置。

示例

1	`CREATE USER 'bob'@'localhost' IDENTIFIED BY 'bobPwd';`

注意事项

用户名和主机名共同决定了一个唯一用户。
密码应足够复杂，保障安全性。
MySQL 8.0 及以上版本推荐使用更加灵活的认证插件设置。

DROP USER - 删除用户

语法格式

1	`DROP USER 'username'@'host'[, 'username'@'host'];`

常见参数

指定要删除的用户，必须与创建时的用户名及主机名匹配。

示例

1	`DROP USER 'bob'@'localhost';`

注意事项

删除用户会移除该用户所有的登录权限，注意核查是否有依赖关系（如定时任务、存储过程中的调用等）。
若用户不存在，建议使用 IF EXISTS（部分 MySQL 版本支持）以避免错误。
删除用户操作不可逆，需谨慎操作。

SET PASSWORD - 设置或修改用户密码

语法格式

1	`SET PASSWORD FOR 'username'@'host' = PASSWORD('new_password');`

常见参数

**‘username‘@’host’**：指定要修改密码的用户。
**PASSWORD(‘new_password’)**：对新密码进行加密，MySQL 早期版本常用该函数。

示例

1	`SET PASSWORD FOR 'alice'@'localhost' = PASSWORD('newSecurePwd');`

注意事项

旧版本中使用 PASSWORD() 函数，但在 MySQL 8.0 中已弃用，建议使用 ALTER USER … IDENTIFIED BY …。
修改密码后用户需要使用新密码登录。
修改密码操作应确保安全传输，避免泄露。

SHOW GRANTS - 查看用户的权限

语法格式

1	`SHOW GRANTS FOR 'username'@'host';`

常见参数

指定用户及主机，如 'alice'@'localhost'。若省略 FOR 子句，则显示当前登录用户的权限。

示例

1	`SHOW GRANTS FOR 'alice'@'localhost';`

注意事项

显示的权限信息包括所有通过 GRANT 命令赋予用户的权限。
该命令用于审核和确认用户权限，便于安全管理。
不同版本显示格式可能略有差异。

FLUSH PRIVILEGES - 刷新权限，使修改生效

语法格式

1	`FLUSH PRIVILEGES;`

常见参数

无其他参数，直接执行命令使权限表的更改在内存中生效。

示例

1	`FLUSH PRIVILEGES;`

注意事项

在直接修改 MySQL 权限表后，必须执行 FLUSH PRIVILEGES 才能使修改生效。
通过 GRANT、REVOKE、CREATE USER 等命令修改权限时，系统通常会自动刷新。
若修改权限文件后遇到权限不一致问题，可尝试执行此命令解决。

SCL

会话控制语言。

SET - 设置会话变量

语法格式

# 设置单个变量
SET variable_name = value;

# 设置多个变量
SET var1 = value1, var2 = value2, ...;

常见参数

系统变量：如 autocommit、sql_mode、character_set_server 等。
用户变量：前面加 @ 符号，例如 SET @var = 10;。

示例

# 设置系统变量
SET autocommit = 0;
SET sql_mode = 'STRICT_TRANS_TABLES';

# 设置用户变量
SET @counter = 100;

注意事项

SET 语句用于配置会话或全局级别的变量，影响当前连接的行为。
对于系统变量，若要全局生效，可使用 SET GLOBAL variable_name = value;（需要相应权限）。
设置用户变量时，变量作用域仅限当前会话，不会在多个连接间共享。

SHOW - 显示变量信息

语法格式

1	`SHOW [FULL] {DATABASES \| TABLES \| COLUMNS FROM table_name \| WARNINGS \| ...};`

常见参数

DATABASES：显示所有数据库。
TABLES：显示当前数据库中的所有表。
COLUMNS/FIELDS：显示指定表的列信息。
其他：还包括 SHOW INDEX、SHOW CREATE TABLE、SHOW ENGINE 等。

示例

# 显示所有数据库
SHOW DATABASES;

# 显示当前数据库中的所有表
SHOW TABLES;

# 显示指定表的字段信息
SHOW COLUMNS FROM users;

注意事项

SHOW 命令主要用于查看数据库的元数据和运行状态。
具体支持的选项和显示内容可能会因 MySQL 版本而有所不同。
加上 FULL 关键字可获得更详细的信息（如在 SHOW PROCESSLIST FULL 中显示完整查询）。

USE - 选择数据库

语法格式

1	`USE database_name;`

常见参数

仅需指定目标数据库的名称，切换当前会话的默认数据库。

示例

1	`USE mydb;`

注意事项

执行 USE 命令后，后续的 SQL 语句若不指定数据库名，则默认在该数据库中执行。
在多数据库环境中，确保在操作前已切换至正确的数据库。
若指定的数据库不存在，会返回错误信息。

DTL

用于导入、导出数据。

LOAD DATA INFILE - 从文件导入数据

语法格式

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_path'
[REPLACE | IGNORE]
INTO TABLE table_name
[CHARACTER SET charset_name]
[FIELDS
    [TERMINATED BY 'string']
    [OPTIONALLY] ENCLOSED BY 'char']
[LINES
    [STARTING BY 'string']
    [TERMINATED BY 'string']]
[IGNORE number LINES]
[(column1, column2, ...)];

常见参数

LOCAL：指定文件位于客户端。
REPLACE/IGNORE：定义遇到重复记录时的处理策略。
CHARACTER SET：设置文件字符集。
FIELDS 与 LINES：指定字段与行的分隔符、包围符和换行符。
IGNORE n LINES：跳过文件开头的 n 行（通常用于忽略标题行）。

示例

LOAD DATA LOCAL INFILE '/path/to/data.csv'
INTO TABLE users
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

注意事项

MySQL 服务器或客户端需要有相应的文件读取权限。
文件路径是基于服务器或客户端（加 LOCAL 时）的文件系统。
参数设置应与文件实际格式保持一致，否则可能导致数据导入错误。
大量数据导入时，注意事务控制和错误处理。

SELECT INTO OUTFILE - 导出数据到文件

语法格式

SELECT columns
FROM table_name
[WHERE condition]
[ORDER BY ...]
INTO OUTFILE 'file_path'
[FIELDS
    [TERMINATED BY 'string']
    [OPTIONALLY] ENCLOSED BY 'char']
[LINES
    [STARTING BY 'string']
    [TERMINATED BY 'string'];

常见参数

INTO OUTFILE：指定导出文件的完整路径（服务器端路径）。
FIELDS 与 LINES：用于定义导出文件中字段和行的格式、分隔符和换行符。

示例

SELECT username, email
FROM users
WHERE status = 'active'
ORDER BY username
INTO OUTFILE '/tmp/active_users.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

注意事项

文件将被写入服务器所在的文件系统，因此必须保证服务器对指定路径有写权限。
文件路径必须不存在，否则会报错。
导出时要确保字段分隔符、行终止符等与目标格式相符。
适用于数据备份和离线分析，但要注意文件安全性。

系统环境命令

用于管理 MySQL 服务器的状态、性能等。

SHOW PROCESSLIST - 查看当前正在执行的查询

语法格式

1	`SHOW [FULL] PROCESSLIST;`

常见参数

FULL：显示完整的查询信息（不截断长查询语句）。

示例

1
2
3

SHOW PROCESSLIST;

SHOW FULL PROCESSLIST;

注意事项

此命令用于查看当前 MySQL 服务器正在执行的线程和查询，便于诊断性能问题或死锁。
普通用户只能查看自己的线程信息，管理员用户可以查看所有线程。
监控信息中包括每个线程的 ID、用户、主机、数据库、命令、时间、状态和当前执行的 SQL 语句。
频繁执行可能会对性能产生轻微影响，建议合理使用。

SHOW STATUS - 查看服务器状态信息

语法格式

1	`SHOW [GLOBAL \| SESSION] STATUS;`

常见参数

GLOBAL：显示服务器级别的状态信息。
SESSION：显示当前会话的状态信息（默认）。

示例

# 查看全局状态
SHOW GLOBAL STATUS;

# 查看当前会话状态
SHOW SESSION STATUS;

注意事项

状态信息包括连接数、查询数、缓存命中率、慢查询等指标，有助于性能调优。
全局状态反映整个服务器的运行情况，SESSION 状态仅涉及当前连接。
数据较多时，可结合 LIKE 子句过滤关键字，如：
1
SHOW GLOBAL STATUS LIKE 'Connections';

SHOW VARIABLES - 查看服务器系统变量

语法格式

1	`SHOW [GLOBAL \| SESSION] VARIABLES;`

常见参数

GLOBAL：显示服务器全局变量。
SESSION：显示当前会话变量（默认）。
可结合 LIKE 子句筛选变量名，如：
1
SHOW VARIABLES LIKE 'max_connections';

示例

# 查看所有全局变量
SHOW GLOBAL VARIABLES;

# 查看特定变量
SHOW SESSION VARIABLES LIKE 'character_set%';

注意事项

变量用于控制 MySQL 的行为和配置，调整前建议详细了解含义。
GLOBAL 变量修改后需重启或执行相应命令使新值生效，SESSION 变量仅影响当前连接。
查看变量可以帮助确认当前服务器配置及诊断性能问题。

系统管理命令

mysql - 连接 MySQL

语法格式

1	`mysql -u username -p [options] [database_name]`

常见参数

-u username：指定登录用户名。
-p：提示输入密码。
-h host：指定服务器地址，默认 localhost。
-P port：指定端口号，默认 3306。
**-e “SQL语句”**：执行 “SQL 语句” 后退出。
–default-character-set：指定连接使用的字符集。

示例

# 交互式登录并切换到数据库
mysql -u root -p mydb

# 直接执行 SQL 语句
mysql -u root -p -e "SHOW DATABASES;"

注意事项

确保使用正确的用户名、密码和服务器地址。
使用 -e 参数时，执行后会自动退出客户端，适合脚本调用。
登录后可以使用 USE database_name; 切换数据库。

mysqladmin - 服务器管理

语法格式

1	`mysqladmin -u username -p [options] command`

常见参数及命令

status：显示服务器当前状态（连接数、查询数等）。
processlist：显示当前活动进程列表（类似 SHOW PROCESSLIST）。
variables：显示服务器变量信息。
shutdown：关闭 MySQL 服务器。
flush-hosts：刷新主机缓存，解除因连接过多引起的阻塞。
kill id：终止指定线程（id 为进程 ID）。

示例

# 查看服务器状态
mysqladmin -u root -p status

# 关闭服务器
mysqladmin -u root -p shutdown

注意事项

执行某些命令（如 shutdown）需要管理员权限。
普通用户通常只能查看自己的进程信息。
使用 kill 命令时请谨慎，误杀进程可能影响业务运行。

mysqlcheck - 检查与修复

语法格式

1	`mysqlcheck -u username -p [options] database_name [table_name ...]`

常见参数

–check：检查表是否损坏（默认操作）。
–repair：修复损坏的表（适用于 MyISAM 表）。
–optimize：优化表，重建索引。
–analyze：分析表数据，帮助优化查询计划。
–all-databases：对所有数据库执行操作。

示例

1 2	`# 优化 mydb 数据库中所有表 mysqlcheck -u root -p --optimize mydb`

注意事项

对于 InnoDB 表，通常建议使用在线 ALTER TABLE 操作。
运行 mysqlcheck 时建议在低峰期执行，避免对业务造成影响。
部分操作可能会加锁表，注意影响并发访问。

mysqlbinlog - 查看二进制日志

语法格式

1	`mysqlbinlog [options] binlog_file ...`

常见参数

**–start-datetime=”YYYY-MM-DD HH:MM:SS”**：指定起始时间。
**–stop-datetime=”YYYY-MM-DD HH:MM:SS”**：指定终止时间。
–start-position=pos、**–stop-position=pos**：以日志文件中的位置范围过滤。
–database=dbname：只解析指定数据库的日志记录。

示例

1 2	`# 查看指定时间段内的 binlog 内容 mysqlbinlog --start-datetime="2024-06-01 10:00:00" --stop-datetime="2024-06-01 12:00:00" /var/lib/mysql/binlog.000001`

注意事项

必须在 MySQL 配置中启用了 binlog（如在 my.cnf 中设置 log_bin）。
binlog 文件可能较大，解析时需注意系统资源。
输出的日志内容包含敏感操作，管理时注意权限控制。

mysqldump - 数据库备份

语法格式

1	`mysqldump -u username -p [options] database_name [table_name ...] > backup.sql`

常见参数

–databases：导出一个或多个数据库（包括 CREATE DATABASE 语句）。
–all-databases：导出所有数据库。
–no-data：只导出表结构，不包含数据。
–single-transaction：针对 InnoDB 表的事务性备份，保证数据一致性。
–routines：包含存储过程和函数。
–triggers：导出触发器（默认包含）。

示例

# 备份单个数据库 mydb
mysqldump -u root -p mydb > mydb_backup.sql

# 备份所有数据库
mysqldump -u root -p --all-databases > all_backup.sql

注意事项

mysqldump 适合逻辑备份，对于数据量较大时，可能备份速度较慢。
使用 --single-transaction 时，适用于 InnoDB 表，确保备份数据一致。
备份文件中包含敏感信息（如数据和结构），请注意文件权限和传输安全。

myisamchk - MyISAM 表维护

语法格式

1	`myisamchk [options] /path/to/database/table_name.MYI`

常见参数

-c 或 –check：检查表是否损坏。
-r 或 –recover：修复损坏的表。
-o 或 –optimize：优化表，提高查询速度。
-q：快速检查，速度较快但详细性较低。

示例

# 检查表是否有错误
myisamchk -c /var/lib/mysql/mydb/users.MYI

# 修复损坏的表
myisamchk -r /var/lib/mysql/mydb/users.MYI

注意事项

使用 myisamchk 前建议停止 MySQL 服务或确保表处于离线状态，否则可能引起数据不一致。
仅适用于 MyISAM 表，不适用于 InnoDB 表。
修复过程中请先备份数据，以免操作不当造成数据丢失。

mysqld_safe - 安全启动 MySQL

语法格式

1	`mysqld_safe [options] &`

常见参数

–skip-grant-tables：跳过权限表验证，常用于忘记密码时恢复。
–log-error=/path/to/error.log：指定错误日志文件。
–port=port_number：指定 MySQL 服务监听的端口。
–datadir：指定数据目录路径。

示例

1 2	`# 以安全模式启动并跳过权限验证（仅用于故障排查） mysqld_safe --skip-grant-tables &`

注意事项

使用 --skip-grant-tables 会使数据库在无权限保护状态下运行，应在维护期间短暂使用。
mysqld_safe 通常用作后台进程，确保使用合适的日志文件路径。
启动后建议检查错误日志，确认服务器运行正常。

mysqlslap - 压力测试

语法格式

1	`mysqlslap [options]`

常见参数

–user, –password：指定测试的数据库用户和密码。
–concurrency：模拟的并发连接数。
–iterations：测试运行的次数。
–query：指定要执行的 SQL 语句。
–create-schema：指定测试使用的数据库名称。
–engine：指定使用的存储引擎。

示例

1
2

# 对查询 “SELECT COUNT(*) FROM orders;” 进行压力测试，模拟 50 个并发连接，执行 10 次
mysqlslap --user=root --password=root --concurrency=50 --iterations=10 --query="SELECT COUNT(*) FROM orders;"

注意事项

mysqlslap 适用于测试环境，不建议在生产数据库上直接运行。
压力测试时可能对数据库性能产生较大影响，应在低峰期或专用测试服务器上执行。
调整 --concurrency 和 --iterations 参数以模拟不同的负载场景，并综合分析结果来定位性能瓶颈。

附录

字符集及其校对规则

定义与作用

字符集：是一组字符编码方案，用于存储文本数据，确保不同语言文字能正确编码和解码。
校对规则：定义了字符的比较、排序和匹配规则，比如是否区分大小写或重音符号。
作用：保证数据的存储、比较和排序遵循统一的编码标准，支持多语言环境，并影响查询时的排序和过滤行为。

常见的字符集及适用场景

latin1：适用于主要使用西欧语言（如英语、法语等）的场景，占用空间较小，但不支持中文。
utf8：支持 Unicode 字符，适用于多种语言，但最多支持 3 字节编码，对部分特殊字符（如部分表情）支持有限。
utf8mb4：完全支持 Unicode 字符集（4 字节编码），适用于需要存储表情符号、特殊字符或多语言内容的场景，是目前推荐的选择。

常见校对规则

如 utf8mb4_general_ci（通用、不区分大小写）和 utf8mb4_bin（按二进制比较，区分大小写），可根据具体业务对排序和比较规则的要求进行选择。

数据类型和约束

定义与作用

数据类型：指定字段存储的数据种类和存储空间，确保数据的格式和范围。
约束：对数据输入施加限制，保证数据的完整性和正确性，避免不合法或重复数据。

数据类型

数值类型
- INT：常用于存储整数。4 个字节长度。
- BIGINT：能存放更大范围的整数，常用于存储主键 ID。8 个字节长度。
- **DECIMAL(M, D)**：适合存储精确数值，如货币金额。M取值 1-65，D取值 0-30。
- FLOAT/DOUBLE：用于存储浮点数，但存在精度问题，适用于对精度要求不高的场景。FLOAT 为 4 个字节长度，DOUBLE 为 8 个字节长度。
- 其他：BIT（0 或 1，长度为 1 比特）、TINYINT（整数，长度为 1 个字节）、SMALLINT（整数，长度为 2 个字节）、MEDIUMINT（整数，长度为 3 个字节）。
字符串类型
- CHAR：固定长度字符串，适用于存储固定格式的数据（如国家代码）。最大 256 个字节长度。
- VARCHAR：可变长度字符串，适合存储长度不定的文本，如用户名、电子邮件。最大 65535 个字节长度。
- TEXT：用于存储大量文本数据，如文章内容。最大 65535 个字节长度。
- 其他：TINYTEXT（最大 256 个字节长度）、MEDIUMTEXT（最大 16MB）、LONGTEXT（最大 4GB）。
日期和时间类型
- YEAR、MONTH、DATE、TIME、DATETIME、TIMESTAMP：用于存储年、月、日期、时间或时间戳，适合记录事件发生时间等。
其他
- 枚举型：ENUM。
- 集合型：SET。
- 二进制：BINARY、VARBINARY、TINYBLOG、BLOG、MEDIUMBLOB、LONGBLOB。

约束

NOT NULL：确保字段必须有值，防止出现空数据。
DEFAULT：为字段设定默认值，保证插入数据时若未指定字段值时使用预设值。
PRIMARY KEY：定义主键，确保每条记录唯一，并常用于建立聚集索引以加速查询。
UNIQUE：确保字段值唯一，避免重复数据。
FOREIGN KEY：确保数据之间的参照完整性，维护表与表之间的关联关系。
AUTO_INCREMENT：自动生成唯一递增的数值，常用于主键字段。
CHECK（在部分版本中支持）：用来限制字段可接受的值范围或格式。
UNSIGNED：无符号约束，限制列的取值范围只能是非负数。
CHARACTER SET：字符集约束，用于定义值的字符集。

创建表选项

定义与作用

table_options：是在创建数据表时附加的选项，用来设定表级别的特性和属性，直接影响表的存储方式、性能及维护性。

类型

ENGINE
- 如 InnoDB（支持事务、行级锁、外键约束）适合高并发和数据一致性要求较高的应用。
- MyISAM（读性能较好，但不支持事务）适用于只读或对事务要求不高的场景。
DEFAULT CHARSET 和 COLLATE
- 决定表中字符数据的编码方式和比较规则，适用于确保数据在多语言环境下正确存储和排序。
AUTO_INCREMENT
- 定义自增字段的起始值和增长方式，常用于主键字段。
COMMENT
- 用于对表进行注释，说明表的用途，便于维护和团队协作。
ROW_FORMAT
- 如 COMPACT、DYNAMIC 等，可调整数据行的存储格式，影响空间利用率和 I/O 性能，通常根据实际存储需求选择。

常见变量

连接与会话相关

max_connections：控制允许同时连接 MySQL 的最大客户端数量。
wait_timeout：非交互式连接空闲超时时间，超过该时间未有活动，连接将被关闭。
interactive_timeout：交互式连接的超时时间，主要影响使用命令行客户端的连接。

SQL 模式和行为控制

sql_mode：指定 SQL 的行为模式，如严格模式（STRICT_TRANS_TABLES）、ANSI 模式等，决定数据验证、错误处理等细节。

字符集与排序规则

character_set_server：服务器默认的字符集，决定新建数据库、表的默认编码。
collation_server：服务器默认的排序规则，与字符集配合决定字符串比较和排序行为。

InnoDB 存储引擎相关

innodb_buffer_pool_size：InnoDB 缓冲池大小，用于缓存数据和索引，直接影响性能。
innodb_log_file_size：InnoDB 日志文件的大小，影响事务日志的写入和恢复性能。

内存与缓存设置

max_allowed_packet：单个数据包的最大允许大小，影响大数据传输和 BLOB 数据处理。
tmp_table_size 与 max_heap_table_size：控制临时表的大小，影响复杂查询（如排序、分组）时使用内存表的容量。
query_cache_size（在部分 MySQL 版本中有效）：指定查询缓存的大小，有助于加快相同查询的响应速度（注意 MySQL 8.0 已移除该功能）。

复制和服务器标识

server_id：在主从复制环境中，每个服务器的唯一标识符。

其他常见变量

port：MySQL 监听的端口号（通常为 3306）。
log_error：指定错误日志的存储位置，用于排查问题。
version：显示 MySQL 的版本信息。

总结

本文将常见的 MySQL 命令按照 DDL、DML、DQL、DCL、TCL、SCL、DTL 等类别进行了系统梳理，每部分都包含语法、参数、示例和注意事项，方便读者检索和记忆。另一方面，本文从基本的数据表操作（CREATE TABLE, ALTER TABLE 等）到高级的存储过程、触发器，再到服务器管理和备份恢复工具（mysqldump、mysqladmin 等），涵盖面全面，直接适用于日常开发和运维场景。通过深入了解并熟练运用本文所述命令，读者能够更高效地进行数据库建模、数据操作、性能调优以及安全管理。无论是开发还是运维层面，这些命令都是 MySQL 使用中不可或缺的基础工具。

MySQL

MySQL 手册

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议，转载请注明出处！

Java并发编程——线程上一篇

基于Hexo+GitHub的博客搭建下一篇