第一章：基础与核心概念

【MySQL连接与进入】

作用：通过命令行连接并进入MySQL交互界面。

语法：

mysql -u [用户名] -p

示例：

mysql -u root -p

说明：

属于系统命令行指令，非SQL语句，输入后按提示输入密码。

【数据库、表、字段架构】

作用：建立“数据库（仓库）-> 表（货架）-> 字段（列）”的层级结构。

语法：

# 创建数据库
CREATE DATABASE [库名];
# 选择数据库
USE [库名];

示例：

# 创建数据库 shop_db
CREATE DATABASE shop_db;
# 选择数据库 shop_db
USE shop_db;

说明：

必须先执行 USE [库名];，才能对该库内的表和字段进行操作。

【常见数据类型】

作用：定义字段能存储的数据格式及大小限制。

语法：

[字段名][数据类型(大小/长度限制)]

示例：

id INT
price DECIMAL(10,2)
username VARCHAR(50)
created_at DATETIME

说明：

INT：整数；DECIMAL(10,2)：最大10位数字，包含2位小数。
VARCHAR(50)：可变长度字符串，最大50个字符；DATETIME：年月日时分秒。

【基础字段约束 (NOT NULL / UNIQUE / DEFAULT)】

作用：限制录入字段的数据，保证数据有效性与默认规则。

语法：

[字段名] [数据类型] [约束条件]

示例：

status INT NOT NULL DEFAULT 1
email VARCHAR(100) UNIQUE

说明：

NOT NULL：必须填值（不能为空）；DEFAULT：不填时的默认值；UNIQUE：全表该列的值不可重复。

【主键约束 (PRIMARY KEY)】

作用：唯一且绝对不为空地标识表中的每一行记录。

语法：

[字段名][数据类型] PRIMARY KEY AUTO_INCREMENT

示例：

id INT PRIMARY KEY AUTO_INCREMENT

说明：

每张表只能有一个主键。
强烈建议配合 AUTO_INCREMENT（自增）使用，交由数据库自动分配编号。

【外键约束 (FOREIGN KEY)】

作用：关联另一张表的主键，防止出现“孤儿数据”，保证引用完整性。

语法：

FOREIGN KEY ([当前表字段]) REFERENCES [外表名]([外表主键])

示例：

FOREIGN KEY (user_id) REFERENCES users(id)

说明：

大型高并发系统通常在代码逻辑层维护关系，避免物理外键带来的性能下降和死锁问题。

【索引 (INDEX)】

作用：为特定字段建立底层目录，极大提升检索速度。

语法：

CREATE INDEX [索引名] ON [表名]([字段名]);

示例：

CREATE INDEX idx_user_email ON users(email);

说明：

频繁作为查询条件（WHERE 后面的字段）的列最适合建索引。
索引会占用磁盘空间，且降低增删改的速度，不可滥用。

【用户与权限分配】

作用：创建独立的数据库账号并精细控制其可操作的范围。

语法：

CREATE USER '[用户名]'@'[允许登录的IP]' IDENTIFIED BY '[密码]';
GRANT [权限列表] ON [库名].[表名] TO '[用户名]'@'[允许登录的IP]';

示例：

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'Pass123!';
GRANT SELECT, INSERT, UPDATE ON shop_db.* TO 'app_user'@'localhost';

说明：

修改权限后，通常需要执行 FLUSH PRIVILEGES; 使其立即生效。

【数据库备份与恢复】

作用：将数据导出为SQL文件防止丢失，或从SQL文件恢复整个库。

语法：

备份：mysqldump -u [用户名] -p [库名] > [文件路径.sql]
恢复：mysql -u [用户名] -p [库名] < [文件路径.sql]

示例：

mysqldump -u root -p shop_db > /backup/shop_backup.sql
mysql -u root -p shop_db < /backup/shop_backup.sql

说明：

这是操作系统终端（CMD/Shell）命令，绝对不要在MySQL的 mysql> 交互界面内执行。

第二章：增（CREATE / INSERT）

【创建带有字符集的数据库】

作用：创建新数据库并强制指定字符集，彻底杜绝中文或特殊符号乱码。

语法：

CREATE DATABASE [库名] DEFAULT CHARACTER SET [字符集] COLLATE [排序规则];

示例：

CREATE DATABASE shop_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

说明：

现代项目强制推荐 utf8mb4，完美支持Emoji表情与多国语言。

【创建数据表 (CREATE TABLE)】

作用：在当前数据库中定义新表结构、字段类型及约束。

语法：

CREATE TABLE [表名] (
    [字段名1] [数据类型] [约束条件],[字段名2] [数据类型] [约束条件],
    ...
);

示例：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    age INT DEFAULT 18,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

说明：

DEFAULT CURRENT_TIMESTAMP 可让系统自动记录当前插入时间。

【复制表结构建表 (CREATE TABLE LIKE)】

作用：快速克隆一张已有表的完整结构（含索引）。

语法：

CREATE TABLE [新表名] LIKE [旧表名];

示例：

CREATE TABLE users_bak LIKE users;

说明：

仅复制结构与约束，不会复制任何数据。

【插入单行数据 (INSERT INTO)】

作用：向表中指定字段写入一条新记录。

语法：

INSERT INTO [表名] ([字段1], [字段2]) VALUES ([值1], [值2]);

示例：

INSERT INTO users (username, age) VALUES ('Alice', 25);

说明：

字符串和日期类型的值必须加单引号 ''。
若省略 ([字段列表])，则 VALUES 中的值必须与表中所有字段的物理顺序严格一致。

【批量插入数据 (Batch INSERT)】

作用：一条语句同时写入多行数据，大幅降低网络开销与提升写入性能。

语法：

INSERT INTO [表名] ([字段1], [字段2]) VALUES 
([第一行值1], [第一行值2]),
([第二行值1], [第二行值2]);

示例：

INSERT INTO users (username, age) VALUES 
('Bob', 22),
('Charlie', 30),
('David', 28);

说明：

实际开发中，几千条数据的导入应优先采用批量插入。

【插入或忽略防报错 (INSERT IGNORE)】

作用：插入数据时，若遇主键或唯一索引冲突（数据已存在），则自动忽略当前条目且不中断执行。

语法：

INSERT IGNORE INTO [表名] ([字段列表]) VALUES ([值列表]);

示例：

INSERT IGNORE INTO users (id, username) VALUES (1, 'Alice');

说明：

冲突时保留旧数据，丢弃新数据。

【将查询结果插入另一表 (INSERT INTO ... SELECT)】

作用：将某张表的查询结果直接灌入另一张已存在的表中。

语法：

INSERT INTO [目标表名] ([目标字段列表]) SELECT [源字段列表] FROM [源表名] WHERE [条件];

示例：

INSERT INTO vip_users (username, age) 
SELECT username, age FROM users WHERE age >= 30;

说明：

目标表必须已经存在。
SELECT 查出的列数量和数据类型，必须与 INSERT 接收的列严格对应。

第三章：删（DROP / DELETE）

【删除数据库 (DROP DATABASE)】

作用：彻底销毁整个数据库及其内部所有的表、数据和结构。

语法：

DROP DATABASE [IF EXISTS][库名];

示例：

DROP DATABASE IF EXISTS shop_db;

说明：

IF EXISTS 可防止数据库不存在时报错中断脚本。
操作极其危险，不可逆且无法回滚。

【删除数据表 (DROP TABLE)】

作用：彻底删除指定数据表及其所有数据、结构和索引。

语法：

DROP TABLE [IF EXISTS] [表名];

示例：

DROP TABLE IF EXISTS users;

说明：

只要表被删除，依赖该表的外键或视图可能会失效。

【快速清空表数据 (TRUNCATE TABLE)】

作用：瞬间清空全表所有数据，并将自增主键（AUTO_INCREMENT）重置为初始值。

语法：

TRUNCATE TABLE [表名];

示例：

TRUNCATE TABLE users;

说明：

底层机制是“删表并重建”，速度远超 DELETE 全表。
不会记录单条数据删除的事务日志，无法通过事务回滚（Rollback）。

【条件删除行数据 (DELETE FROM)】

作用：根据指定条件，精准删除表中的单行或多行数据记录。

语法：

DELETE FROM [表名] WHERE [条件];

示例：

DELETE FROM users WHERE age < 18;

说明：

绝对不要漏写 WHERE 条件，否则会演变成极其缓慢的全表删除。
执行属于DML操作，在提交事务前可以回滚。

【限制删除数量 (DELETE LIMIT)】

作用：每次只删除匹配条件的前N条数据，防止一次性删除过多引发锁表或性能抖动。

语法：

DELETE FROM [表名] WHERE [条件] LIMIT [行数];

示例：

DELETE FROM log_table WHERE created_at < '2025-01-01' LIMIT 1000;

说明：

常用于定时清理海量历史数据的场景（分批删除）。

【多表关联删除 (DELETE JOIN)】

作用：基于与其他表的关联匹配条件，删除目标表中的数据。

语法：

DELETE [目标表别名] FROM [表A 别名] JOIN [表B 别名] ON [关联条件] WHERE [过滤条件];

示例：

DELETE u FROM users u JOIN blacklist b ON u.email = b.email;

说明：

语法中的 DELETE u 表示只删除 users 表（别名u）里的匹配数据，不会删除 blacklist 表的数据。

【删除索引 (DROP INDEX)】

作用：移除表中不需要的索引，释放磁盘空间并提升写入速度。

语法：

DROP INDEX [索引名] ON [表名];

示例：

DROP INDEX idx_user_email ON users;

说明：

无法直接删除主键索引，主键需通过 ALTER TABLE 移除。

【删除用户 (DROP USER)】

作用：注销数据库账户，同时自动剥夺其所有相关权限。

语法：

DROP USER '[用户名]'@'[允许登录的IP]';

示例：

DROP USER 'app_user'@'localhost';

说明：

删除后，该用户无法再发起新的连接，但当前已连接的会话不会被强行踢下线。

第四章：改（ALTER / UPDATE）

【修改表名 (RENAME TABLE)】

作用：更改已有数据表的名称。

语法：

RENAME TABLE [旧表名] TO[新表名];

示例：

RENAME TABLE users TO members;

【添加新字段 (ALTER TABLE ADD)】

作用：在已有表中增加新的数据列。

语法：

ALTER TABLE [表名] ADD [新字段名] [数据类型] [约束] [位置];

示例：

ALTER TABLE users ADD phone VARCHAR(20) NOT NULL AFTER email;

说明：

AFTER [已有字段名] 可精准控制新字段的插入位置，不写则默认添加在表末尾。

【修改字段名及类型 (ALTER TABLE CHANGE)】

作用：同时重命名列，并重新定义其数据类型与约束。

语法：

ALTER TABLE [表名] CHANGE[旧字段名] [新字段名] [新数据类型] [约束];

示例：

ALTER TABLE users CHANGE age user_age TINYINT DEFAULT 18;

说明：

即使只改名字不改类型，也必须在语句中写全原有的数据类型。

【修改字段类型或约束 (ALTER TABLE MODIFY)】

作用：在不改变列名的情况下，调整其数据类型或约束条件。

语法：

ALTER TABLE [表名] MODIFY [字段名][新数据类型] [约束];

示例：

ALTER TABLE users MODIFY username VARCHAR(100) NOT NULL;

说明：

若表中已有数据，缩小字段长度（如VARCHAR(100)改为VARCHAR(10)）可能会因截断数据报错。

【删除字段 (ALTER TABLE DROP)】

作用：彻底移除表中的某一列及其包含的所有数据。

语法：

ALTER TABLE[表名] DROP [字段名];

示例：

ALTER TABLE users DROP phone;

【条件更新数据 (UPDATE)】

作用：修改表中满足特定条件的一行或多行数据值。

语法：

UPDATE [表名] SET [字段名] = [新值] WHERE [条件];

示例：

UPDATE users SET status = 0 WHERE age < 18;

说明：

极其危险：绝对不能漏写 WHERE 条件，否则将覆盖全表每一行的数据！

【多字段同步更新 (UPDATE Multiple)】

作用：在一条语句中同时修改一行的多个字段。

语法：

UPDATE [表名] SET [字段1] =[值1], [字段2] = [值2] WHERE [条件];

示例：

UPDATE users SET status = 1, updated_at = NOW() WHERE id = 10;

说明：

多个字段之间只能用逗号 , 分隔，绝对不能使用 AND。

【多表关联更新 (UPDATE JOIN)】

作用：基于另一张表的匹配结果，更新目标表的字段。

语法：

UPDATE [表A别名] JOIN [表B别名] ON[关联条件] SET [表A别名.字段] = [新值] WHERE [过滤条件];

示例：

UPDATE users u JOIN orders o ON u.id = o.user_id SET u.is_vip = 1 WHERE o.total_amount > 1000;

第五章：查（SELECT，多表查询重点）

【基础查询 (SELECT)】

作用：检索表中指定列的数据。

语法：

SELECT [字段1], [字段2] FROM [表名];

示例：

SELECT id, username, email FROM users;

说明：

生产环境严禁使用 SELECT *，应明确列出所需字段以避免网络与内存浪费。

【字段与表别名 (AS)】

作用：为表或查询出的字段起一个临时的简短名称，简化代码或重命名输出列。

语法：

SELECT [字段名] AS [列别名] FROM[表名] AS [表别名];

示例：

SELECT username AS '用户名', email AS '邮箱' FROM users u;

说明：

AS 关键字可以省略，字段或表名后直接加空格写别名即可（如 users u）。
在多表关联（JOIN）中，强制推荐使用表别名以防止不同表的同名字段发生冲突。

【条件查询 (WHERE)】

作用：基于特定条件过滤检索出的数据。

语法：

SELECT[字段] FROM [表名] WHERE [条件];

示例：

SELECT id, username FROM users WHERE age >= 18 AND status = 1;

说明：

常用运算符包含 =, >, <, !=, AND, OR, IN(值1, 值2), BETWEEN A AND B。

【模糊查询 (LIKE)】

作用：根据特定模式匹配包含对应字符的数据。

语法：

SELECT [字段] FROM [表名] WHERE [字段] LIKE '[匹配模式]';

示例：

SELECT username FROM users WHERE username LIKE '张%';

说明：

% 代表任意数量字符，_ 代表单一字符。
前置百分号（如 '%三'）会导致索引失效，引发全表扫描。

【结果排序 (ORDER BY)】

作用：按指定字段的正序或倒序排列查询结果。

语法：

SELECT [字段] FROM[表名] ORDER BY [排序字段] [ASC/DESC];

示例：

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

说明：

ASC 为升序（默认可省略），DESC 为降序。
多字段排序时，优先按第一个字段排序，相同时再按第二个字段排序。

【分页截取 (LIMIT)】

作用：截取指定区间的数据行，常用于列表分页显示。

语法：

SELECT [字段] FROM [表名] LIMIT [偏移量], [返回行数];

示例：

SELECT id, username FROM users ORDER BY id DESC LIMIT 0, 10;

说明：

LIMIT 0, 10 表示从第1行起取10条。
偏移量越大查询越慢（深分页问题），海量数据需配合主键索引优化。

【聚合统计 (COUNT / SUM / AVG / MAX / MIN)】

作用：对多行数据进行数学计算并返回单一结果。

语法：

SELECT 聚合函数([字段]) FROM [表名];

示例：

SELECT COUNT(*), SUM(price), AVG(age), MAX(score) FROM orders;

说明：

COUNT(*) 统计总行数，包含NULL行；COUNT(字段) 不统计该字段为NULL的行。

【分组过滤 (GROUP BY & HAVING)】

作用：按某字段将数据分组后进行聚合统计，并对统计结果进行二次过滤。

语法：

SELECT [分组字段], 聚合函数 FROM [表名] GROUP BY [分组字段] HAVING [聚合条件];

示例：

SELECT role_id, COUNT(*) as total FROM users GROUP BY role_id HAVING total > 5;

说明：

WHERE 只能用于分组前的基础数据过滤，HAVING 专门用于分组后的聚合结果过滤。

【内连接 (INNER JOIN)】

作用：合并多表，仅返回关联字段值完全匹配的数据（交集）。

语法：

SELECT [字段] FROM [表A] INNER JOIN [表B] ON [关联条件];

示例：

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

说明：

INNER 关键字可省略，直接写 JOIN。

【左连接 (LEFT JOIN)】

作用：合并多表，完整保留左表所有数据，右表无匹配时补NULL。

语法：

SELECT [字段] FROM [表A] LEFT JOIN [表B] ON [关联条件];

示例：

SELECT u.username, o.order_no 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id;

说明：

以 LEFT JOIN 前面的表为驱动表，这是实际开发中使用频率最高的关联方式。

【子查询 (Subquery)】

作用：将一个查询的最终结果作为另一个查询的条件。

语法：

SELECT [字段] FROM [表A] WHERE [字段] IN (SELECT [字段] FROM [表B] WHERE [条件]);

示例：

SELECT username FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

说明：

嵌套深或数据量大时性能极差，生产环境中通常优先改写为 JOIN。

第六章：存储与进阶（存储过程、函数、触发器、流程控制等）

【修改语句分隔符 (DELIMITER)】

作用：临时更改SQL语句的结束符，防止编写复杂结构时因内部的分号被提前截断报错。

语法：

DELIMITER [新符号]

示例：

DELIMITER //

说明：

编写完存储过程或触发器后，务必执行 DELIMITER ; 恢复默认分号。

【创建与调用存储过程 (PROCEDURE)】

作用：将复杂或多步的SQL逻辑打包封装在数据库中，通过单一命令反复执行。

语法：

CREATE PROCEDURE [过程名]([IN/OUT 参数名] [数据类型])
BEGIN
    [SQL语句];
END;
CALL [过程名]([参数]);

示例：

DELIMITER //
CREATE PROCEDURE AddUser(IN p_name VARCHAR(50))
BEGIN
    INSERT INTO users(username, created_at) VALUES(p_name, NOW());
END //
DELIMITER ;

CALL AddUser('Tom');

说明：

IN 表示外部传入的参数，OUT 表示处理后输出给外部的返回值。

【创建与调用自定义函数 (FUNCTION)】

作用：封装特定计算逻辑并强制返回单一结果，可直接内嵌在SQL查询语句中使用。

语法：

CREATE FUNCTION [函数名]([参数名] [数据类型]) RETURNS[返回类型]
BEGIN
    [逻辑语句];
    RETURN [结果];
END;

示例：

DELIMITER //
CREATE FUNCTION GetDiscountPrice(price DECIMAL(10,2)) RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN price * 0.8;
END //
DELIMITER ;

SELECT GetDiscountPrice(100.00);

说明：

定义时必须有 RETURNS 声明返回类型，且函数体内必须有 RETURN 返回结果。

【流程控制：条件判断 (IF)】

作用：在存储过程或函数内部，根据条件执行不同的SQL分支逻辑。

语法：

IF [条件] THEN
    [SQL语句];
ELSEIF [条件] THEN
    [SQL语句];
ELSE
    [SQL语句];
END IF;

示例：

IF v_score >= 90 THEN
    SET v_grade = 'A';
ELSE
    SET v_grade = 'B';
END IF;

说明：

只能在存储过程、函数或触发器的 BEGIN ... END 内部使用。

【流程控制：循环 (WHILE)】

作用：在满足条件时，重复执行某段SQL逻辑。

语法：

WHILE [条件] DO
    [SQL语句];
END WHILE;

示例：

DECLARE i INT DEFAULT 1;
WHILE i <= 10 DO
    INSERT INTO test_logs(num) VALUES(i);
    SET i = i + 1;
END WHILE;

说明：

务必在循环体内更新条件变量（如 SET i = i + 1），否则会导致数据库死循环。

【创建触发器 (TRIGGER)】

作用：绑定在特定表上，当发生增删改操作时，自动触发执行额外的SQL逻辑。

语法：

CREATE TRIGGER[触发器名] [AFTER/BEFORE] [INSERT/UPDATE/DELETE] ON [表名] FOR EACH ROW
BEGIN
    [SQL语句];
END;

示例：

DELIMITER //
CREATE TRIGGER after_user_insert AFTER INSERT ON users FOR EACH ROW
BEGIN
    INSERT INTO user_logs(action, user_id) VALUES('INSERT', NEW.id);
END //
DELIMITER ;

说明：

NEW.[字段] 用于获取操作后的新值，OLD.[字段] 用于获取操作前的旧值。
业务复杂时慎用触发器，极易引发难以排查的性能问题和隐式锁定。

【创建视图 (VIEW)】

作用：将复杂的查询SQL封装为一张“虚拟表”，极大简化后续的查询代码。

语法：

CREATE VIEW [视图名] AS [SELECT查询语句];

示例：

CREATE VIEW v_active_vip_users AS 
SELECT id, username, email FROM users WHERE status = 1 AND is_vip = 1;

SELECT * FROM v_active_vip_users;

说明：

视图本身不存储任何数据，每次查询视图底层都会实时执行其绑定的SQL。

【事务控制 (TRANSACTION)】

作用：将多条SQL打包为不可分割的整体，要么全部成功，要么全部撤销，保证数据绝对一致。

语法：

START TRANSACTION;[多条SQL语句];
COMMIT;   -- 成功则提交
-- ROLLBACK; -- 失败则回滚

示例：

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

说明：

COMMIT 会永久保存更改，ROLLBACK 会撤销 START TRANSACTION 之后的所有操作。
仅对支持事务的存储引擎（如默认的 InnoDB）有效。