我们平时用的APP（微信、淘宝）、网站，背后的数据（好友列表、订单记录）都存在类似MySQL的数据库里。它就像一个“智能管家”，帮你管理所有数据，你只需要用SQL指令“指挥”它，不用自己管数据存在电脑的哪个角落。把 MySQL 想象成「电脑里的智能文件柜」—— 数据库是「整个文件柜」，表是「文件柜里的每一本Excel表」，数据是「Excel表里的每一行内容」，SQL语句是「你给文件柜的操作指令」（比如找文件、放文件、改文件）。MySQL 是开源的「关系型数据库管理系统」，本质是「帮你高效存、取、改、删结构化数据」，解决“数据太多存不下、找不着、改易错”的问题。

一、基础概念

1. 数据库与关系型数据库

  • 数据库（DB）：长期存储在计算机内、有组织、可共享的数据集合。
  • 关系型数据库（RDBMS）：基于二维表结构存储数据，用 SQL（结构化查询语言）操作，核心特性是ACID（原子性、一致性、隔离性、持久性）。
  • 常见代表：MySQL、Oracle、PostgreSQL、SQL Server。
  • 核心比喻：
    • 数据库 = 图书馆的 “藏书楼”
    • 关系型数据库 = 把书按 “分类、作者、书名” 等表格形式整理的藏书楼（Excel 就是最直观的关系型数据库雏形）
  • 本质：用 “二维表格” 存数据，表格之间可以通过 “关联字段”（比如 “用户 ID”）建立关系。

2. MySQL 的特点与应用场景

  • 特点：开源免费、跨平台（Windows/Linux/macOS）、性能优异、社区活跃、InnoDB 引擎支持高并发。
  • 应用场景：Web 应用（电商、社交）、企业级系统、轻量级数据分析、嵌入式系统。
  • 特点：
    • 开源免费（像 Linux 一样）、性能强、社区活跃
    • 轻量级，适合从小网站到大型系统
  • 应用场景：
    • 网站后台（电商、社交、博客）、企业管理系统、数据分析平台

3. MySQL 的版本与分支

  • 官方版本：MySQL 5.7（稳定）、MySQL 8.0（主流，新特性丰富）。
  • 分支：
    • MariaDB：MySQL 创始人创建，完全兼容 MySQL，性能优化更激进。
    • Percona Server：专注性能与高可用，适合企业级场景。

二、安装与配置

1. 不同操作系统下的安装方法

  • Windows：下载 MySQL Installer，图形化安装（推荐 “Developer Default”）。
  • Linux（CentOS/Ubuntu）：
    • CentOS： 
      yum install mysql-server
      （或官方 YUM 源）。
    • Ubuntu： 
      apt-get install mysql-server。
  • macOS：DMG 包安装或 Homebrew：brew install mysql。

2. 初始化配置与安全设置

  • 初始化：Linux 下执行mysqld --initialize（临时密码在日志中）。
  • 启动服务：systemctl start mysqld（Linux）或服务面板（Windows）。
  • 安全加固：执行mysql_secure_installation（设置 root 密码、删除匿名用户、禁止 root 远程登录、删除测试库）。

3. 常见安装问题排查

  • 端口被占用：检查 3306 端口（netstat -anp | grep 3306），修改my.cnf的port参数。
  • 忘记 root 密码：停止服务→mysqld --skip-grant-tables启动→连接后修改密码：

    sql

    UPDATE mysql.user SET authentication_string=PASSWORD('新密码') WHERE User='root';

4.命令行连接MySQL（打开文件柜的钥匙）

        连接MySQL，就是用“账号密码”验证身份，获得操作“文件柜”的权限，常用命令如下（复制就能用）：

基础连接（最常用，输入命令后回车，再输密码即可） 

mysql -u root -p

指定主机和端口（比如远程连接别人的“文件柜”） 

mysql -h 127.0.0.1 -P 3306 -u root -p

新手易误解点：① 输入密码时，屏幕不会显示任何字符（不是没输入，是故意隐藏，防止别人看到）；② 端口号3306是MySQL的“默认门牌号”，如果修改过，必须写修改后的端口，否则连不上。

三、数据库操作

1. 创建、删除数据库

  • 创建： 
    CREATE DATABASE 数据库名 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    ⚠️ 新手提示：推荐用 utf8mb4（支持 emoji，utf8 是其子集）。
  • 删除： 
    DROP DATABASE [IF EXISTS] 数据库名;
  • 查看 / 切换：SHOW DATABASES; → USE 数据库名;

2. 字符集与排序规则

  • 字符集：定义数据编码（如utf8mb4、latin1）。决定数据库能存哪些文字（比如 utf8mb4 支持 emoji，像 “📚”）
  • 排序规则：定义数据比较规则（如utf8mb4_unicode_ci不区分大小写，utf8mb4_bin区分大小写）。决定文字怎么排序
  • 别用旧的 utf8（最多存 3 字节，emoji 会报错），统一用 utf8mb4

3. 数据库备份与恢复

  • 逻辑备份（mysqldump）：
    • 备份： 
      mysqldump -u root -p 数据库名 > 备份文件.sql
    • 恢复： 
      mysql -u root -p 数据库名 < 备份文件.sql（先创建空库）。
  • 物理备份：直接复制/var/lib/mysql数据目录（适合大库，需停服务）。

四、表与数据类型

1. 表的创建、修改与删除

  • 创建表： 
    CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    age INT,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  • 修改表：
    • 添加列：ALTER TABLE 表名 ADD COLUMN 列名 数据类型;
    • 修改列：ALTER TABLE 表名 MODIFY COLUMN 列名 新数据类型;
    • 删除列：ALTER TABLE 表名 DROP COLUMN 列名;
  • 删除表：DROP TABLE [IF EXISTS] 表名;
  • 查看结构：DESC 表名; / SHOW CREATE TABLE 表名;

2. 常见数据类型

        建表时，每个表头（字段）都要指定“数据类型”，类比成“Excel表头的格式”——比如“年龄”表头只能填数字，“姓名”表头只能填文字，否则会乱套。数据类型是「约束数据格式」，确保数据统一，同时节省存储空间（比如存年龄用1个字节就够，不用浪费更多空间）。

分类 具体类型 详细说明 通俗比喻 核心适用场景
整数 TINYINT 1 字节，小范围整数 - 状态标识、短枚举值
整数 INT 4 字节，最常用整数 书的数量 年龄、ID、常规数量统计
整数 BIGINT 8 字节，超大范围整数 - 超大 ID、海量数据计数
字符串 VARCHAR(n) 可变长度字符串，n 为最大长度 书名 姓名、标题、地址、用户名
字符串 TEXT 不限定长度的长文本 书的简介 文章内容、详细备注、大段文字
日期 DATE 仅日期，格式 YYYY-MM-DD 出版日期 生日、订单日期、纯日期记录
日期 DATETIME 日期 + 时间，YYYY-MM-DD HH:MM:SS，最常用 带时间的出版记录 订单创建时间、日志时间、操作时间
小数 DECIMAL(m,d) 精确小数，m 总位数、d 小数位（例：DECIMAL (10,2)） 商品价格 金额、薪资、高精度价格 / 数值

新手易错点：

  • VARCHAR(n) 里的 n 是 “字符数” 不是 “字节数”（比如 VARCHAR(10) 能存 10 个汉字）
  • 金额别用 FLOAT/DOUBLE（会有精度丢失），必须用 DECIMAL

具体介绍

2.1. 数值类型（只能存数字，对应Excel的“数字格式”）

类型

通俗说明

类比

适用场景

新手避坑

INT

普通整数，范围-21亿~21亿，占4个字节

Excel里的“常规数字”（不存小数）

年龄、数量、普通ID（比如学生ID）

别用INT存超大数字（比如订单号超过21亿），会存不下，改用BIGINT

BIGINT

大整数，范围极大，占8个字节

Excel里的“长数字”

订单号、用户ID（数据量大时）

不用盲目用BIGINT，存小数字用INT更省空间

DECIMAL(M,D)

精确小数，M=总位数，D=小数位数（比如DECIMAL(10,2)就是最多10位，2位小数）

Excel里的“货币格式”“保留2位小数”

金额、价格（比如39.99元、100.00元）

别用INT存金额（会丢小数），别用FLOAT/DOUBLE（有精度误差，比如0.1+0.2≠0.3）

2.2. 字符串类型（存文字/符号，对应Excel的“文本格式”）

类型

通俗说明

类比

适用场景

新手避坑

VARCHAR(n)

变长字符串，n是最大长度，存多少内容占多少空间（比如存“张三”占2个字符，存“李四”也占2个）

Excel里的“可变长度文本”（写多少算多少）

姓名、地址、手机号（长度不固定）

n别设太大（比如VARCHAR(1000)存姓名），浪费空间；也别设太小（比如VARCHAR(2)存“张伟”，会存不下）

CHAR(n)

定长字符串，n是固定长度，不管存多少内容，都占n个空间（比如CHAR(2)存“张”，也占2个字符，多余的补空格）

Excel里的“固定长度文本”（必须填够长度，不够补空格）

性别（男/女，固定1个字符）、状态（0/1，固定1个字符）

别用CHAR存长度不固定的内容（比如地址），会浪费大量空间

TEXT

长文本，最多存65535个字符（大概几万字）

Excel里的“备注栏”（存很长的文字）

文章内容、备注、详细描述

别用TEXT存短内容（比如姓名），查询速度比VARCHAR慢

2.3. 日期时间类型（存日期/时间，对应Excel的“日期时间格式”）

类型

格式

通俗说明

适用场景

DATE

YYYY-MM-DD

只存日期，不存时间（比如2026-02-15）

生日、注册日期（只需要知道哪一天）

DATETIME

YYYY-MM-DD HH:MM:SS

存日期+时间（比如2026-02-15 14:30:00），不会自动更新

订单创建时间、用户登录时间（需要精确到分秒，且固定不变）

TIMESTAMP

YYYY-MM-DD HH:MM:SS

存日期+时间，会自动更新（比如修改数据时，自动改成当前时间），占空间比DATETIME小

数据最后修改时间（不需要手动改，自动记录）

3. 约束

        约束核心本质是「给表的字段加“限制条件”」，保证数据准确、不混乱——类比成Excel表的“数据验证”（比如年龄只能填1-100，姓名不能空）。约束是“建表时/修改表时”加的，不是给数据加的；加了约束后，不符合规则的数据，无法插入/修改。

约束

关键字

通俗类比

核心作用（本质）

新手避坑

主键

PRIMARY KEY

Excel表的“身份证号”列

唯一标识每一行数据（非空且唯一），一张表只能有1个主键

主键不能重复、不能空；常用INT+自增（AUTO_INCREMENT）当主键

自增

AUTO_INCREMENT

Excel表的“自动编号”（1、2、3...）

主键自动增长，不用手动输入（插入数据时，主键填NULL即可）

自增只能用于“数值类型的主键”（比如INT、BIGINT），不能用于VARCHAR

非空

NOT NULL

Excel表的“必填项”

字段值不能为空（必须填内容）

别给“可选字段”加非空约束（比如学生的“备注”，可能没人填），否则会插入失败

唯一

UNIQUE

Excel表的“手机号”列（不能有重复）

字段值必须唯一（不能重复），但可以空

唯一≠非空（比如手机号可以空，但如果填了，就不能和别人重复）

默认

DEFAULT

Excel表的“默认值”（比如性别默认填“男”）

字段未赋值时，自动使用默认值

默认值要和字段类型匹配（比如性别是CHAR(1)，默认值不能是“男性”，太长）

外键

FOREIGN KEY

两个Excel表的“关联列”（比如学生表的“班级ID”和班级表的“班级ID”）

关联两个表，保证数据一致性（比如不能给学生填一个不存在的班级ID）

新手初期可先不深入，重点掌握前5个；外键会影响删除/修改，容易出错

五、SQL 基本语法

        SQL是和MySQL沟通的“语言”，所有操作都靠它，按功能分4类，类比成“对文件柜的4种操作”，一看就懂：

分类

全称

核心类比

作用（本质）

常用关键字

DDL

数据定义语言

给文件柜“搭架子”

定义数据库、表的结构（比如新建文件柜、新建Excel表、修改Excel表头）

CREATE（新建）、ALTER（修改）、DROP（删除）

DML

数据操作语言

给Excel表“填/改/删内容”

操作表中的具体数据（新增一行内容、修改某行、删除某行）

INSERT（新增）、UPDATE（修改）、DELETE（删除）

DQL

数据查询语言

从Excel表里“找内容”

查询表中的数据（最常用，比如找所有18岁的学生、找张三的手机号）

SELECT（查询）

DCL

数据控制语言

给别人“开文件柜权限”

控制用户的操作权限（比如允许某人看表、禁止某人改数据）

GRANT（授权）、REVOKE（回收权限）

新手易误解点：分不清DDL和DML——DDL改“架子”（比如给Excel表加个表头），DML改“内容”（比如给Excel表填一行数据）；DDL操作后，表结构会变，DML操作后，表结构不变，只变内容。

1. DML（数据操作语言）

SELECT（查询）

比喻：从书架上 “找书”

-- 基础查询
SELECT username, age FROM users WHERE age >= 18 ORDER BY age DESC LIMIT 0, 10;

-- 聚合函数
SELECT COUNT(*) AS 总人数, AVG(age) AS 平均年龄 FROM users;

-- 连接查询
SELECT u.username, o.order_id FROM users u INNER JOIN orders o ON u.id = o.user_id;

代码示例：

SELECT * FROM books WHERE author = '金庸';  -- 查金庸的所有书
SELECT title, price FROM books ORDER BY price DESC LIMIT 10;  -- 查最贵的 10 本书

INSERT（插入）

比喻：往书架上 “放书”

代码：

INSERT INTO books (title, author, price) 
VALUES ('射雕英雄传', '金庸', 59.9);

UPDATE（更新）

比喻：把书的 “价格标签” 改了

代码：

UPDATE books SET price = 49.9 WHERE id = 1;

新手易错点：UPDATE 必须加 WHERE，否则会把全表都改了！

DELETE（删除）

比喻：把书从书架上 “拿走”

代码：

DELETE FROM books WHERE id = 1;

新手易错点：DELETE 必须加 WHERE，否则会把全表数据都删了！

2. DDL（数据定义语言）

        DDL核心：操作“结构”，不碰“内容”——比如新建文件柜（数据库）、新建Excel表（表）、修改Excel表头（字段）、删除文件柜/表。DDL操作不可逆（比如删除表，表和里面的所有数据都会消失，无法恢复），操作前一定要确认！

  • 创建 / 修改 / 删除数据库、表（见前文）。
  • 创建索引：CREATE INDEX idx_username ON users (username);
  • 前面 “数据库操作” 和 “表操作” 里的 CREATE、ALTER、DROP 都是 DDL

1. 数据库操作（管理“文件柜”）

-- 1. 新建数据库（文件柜）：IF NOT EXISTS 表示“如果没有这个文件柜，再新建”，避免报错 CREATE DATABASE IF NOT EXISTS mydb; 
-- 2. 使用数据库（打开文件柜，后续所有操作都针对这个文件柜） USE mydb; 
-- 3. 删除数据库（删除整个文件柜，里面的所有表和数据都会消失，慎用！） DROP DATABASE IF EXISTS mydb;

2. 表操作（管理“Excel表”）

（1）创建表（新建Excel表，指定表头和规则）

类比：新建一张Excel表，先写好表头（比如id、姓名、年龄），再规定每个表头的规则（比如姓名不能为空、年龄只能是数字）。

-- 示例：创建“学生表”（student），包含5个表头（字段） CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT,
-- 主键（身份证号），自增（自动编号，不用手动填） name VARCHAR(20) NOT NULL,
-- 姓名，非空（必须填，不能空着） age INT, 
-- 年龄，可空（可以不填） gender CHAR(1) DEFAULT '男', 
-- 性别，默认值（不填的话，自动填“男”） phone VARCHAR(11) UNIQUE, 
-- 手机号，唯一（不能有两个一样的手机号） create_time DATETIME -- 创建时间 );
（2）修改表（修改Excel表的表头）
-- 1. 给表加一个表头（字段）：给学生表加“地址”字段 ALTER TABLE student ADD address VARCHAR(50); 
-- 2. 修改表头规则（字段类型/约束）：把“年龄”改成更小的类型（TINYINT，占1个字节） ALTER TABLE student MODIFY age TINYINT;
-- 3. 删除表头（字段）：删除“地址”字段 ALTER TABLE student DROP COLUMN address; 
-- 4. 修改表名（Excel表重命名）：把student改成stu ALTER TABLE student RENAME TO stu;
（3）删除表（删除Excel表）
-- 删除学生表，IF EXISTS 避免“表不存在”报错（删除后，表中所有数据都消失） DROP TABLE IF EXISTS student;

3. DCL（数据控制语言）

比喻：给不同的人 “发借书证”，有的只能看书，有的能买书

  • 授权：
  • GRANT SELECT, INSERT ON mydb.* TO 'user'@'localhost' IDENTIFIED BY '密码';
FLUSH PRIVILEGES;
  • 撤销：
  • REVOKE INSERT ON mydb.* FROM 'user'@'localhost';

  • 代码示例： 
    CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY '123456';  -- 创建用户
GRANT SELECT, INSERT ON my_library.* TO 'zhangsan'@'localhost';  -- 给用户授权
REVOKE INSERT ON my_library.* FROM 'zhangsan'@'localhost';  -- 收回权限

4.DML 操作：（数据增删改）

        DML核心操作“数据内容”，不碰“表结构”——类比成给Excel表填一行内容、改一行内容、删一行内容，表的表头不变。修改/删除数据时，务必加WHERE条件，否则会操作全表数据！

1. 插入数据（INSERT，给Excel表填内容）
-- 1. 插入单条数据（指定字段，推荐！不容易出错） INSERT INTO student (name, age, phone) VALUES ('张三', 18, '13800138000'); 
-- 2. 插入单条数据（所有字段，自增主键填NULL，会自动编号） INSERT INTO student VALUES (NULL, '李四', 20, '女', '13900139000', NOW()); -- NOW() 是MySQL自带函数，自动获取当前日期时间 
-- 3. 批量插入（一次填多行，比单条插入高效） INSERT INTO student (name, age) VALUES ('王五', 19), ('赵六', 21);

新手易误解点：① 插入的数据类型要和字段类型匹配（比如给age填“十八”，会报错，因为age是INT类型）；② 非空字段必须填（比如name加了NOT NULL，插入时不填name，会报错）。

2. 修改数据（UPDATE，改Excel表的内容）
-- 1. 修改单条数据（务必加WHERE！只改“张三”的年龄） UPDATE student SET age = 22 WHERE name = '张三'; 
-- 2. 修改多个字段（改“张三”的年龄和性别） UPDATE student SET age = 23, gender = '女' WHERE id = 1; 
-- ❌ 错误示例（没有WHERE，会修改表中所有学生的年龄！） UPDATE student SET age = 22;

新手易误解点：① 没有WHERE条件，会修改全表数据（比如上面的错误示例，所有学生年龄都会变成22）；② 可以同时修改多个字段，用逗号分隔。

3. 删除数据（DELETE，删Excel表的内容）
-- 1. 删除单条数据（务必加WHERE！只删id=1的学生） DELETE FROM student WHERE id = 1; 
-- 2. 删除所有数据（慎用！删完表结构还在，数据全没，可回滚） DELETE FROM student; 
-- 3. 清空表（TRUNCATE，比DELETE快，删完重置自增ID，不可回滚） TRUNCATE TABLE student;

核心区别（新手必记）：DELETE 是“逐行删除”（可撤销，比如删错了能恢复），TRUNCATE 是“直接删表重建”（不可撤销，且自增ID会重置为1）；两者都不会删除表结构，只删数据。

5.DQL 操作：（数据查询）

DQL核心用SELECT查询数据，本质是「从表中“筛选、提取”符合条件的内容」——类比成Excel的“筛选”“排序”“求和”功能，是MySQL最常用、最重要的操作。查询不会修改任何数据（只是“看”数据），放心大胆练；所有查询语句，核心都是“SELECT 要查的内容 FROM 表名 WHERE 筛选条件”。

1. 基础查询（简单“看”数据）

-- 1. 查询所有字段（* 代表所有表头，简单但效率低，不推荐大量数据使用） SELECT * FROM student; 
-- 2. 查询指定字段（推荐！只查需要的内容，效率高） SELECT name, age FROM student; 
-- 3. 字段别名（AS 可省略，给表头起个好懂的名字，方便查看） SELECT name AS 姓名, age 年龄 FROM student; -- 查出来的表头是“姓名”“年龄” 
-- 4. 去重查询（DISTINCT，去掉重复的内容） SELECT DISTINCT gender FROM student; -- 只查“男”“女”，不会重复显示

2. 条件查询（WHERE，筛选符合条件的内容）

类比：Excel的“筛选”功能，只显示符合条件的行（比如只显示年龄>18的学生）。

-- 1. 比较运算符（=、>、<、>=、<=、!= 或 <>） SELECT * FROM student WHERE age > 18; -- 查所有18岁以上的学生 SELECT * FROM student WHERE gender != '男'; -- 查所有不是男性的学生 
-- 2. 逻辑运算符（AND 且、OR 或、NOT 非） SELECT * FROM student WHERE age > 18 AND gender = '女'; -- 18岁以上且是女生 SELECT * FROM student WHERE age < 18 OR age > 20; -- 18岁以下或20岁以上 
-- 3. 模糊查询（LIKE，查“相似”的内容，%匹配任意字符，_匹配单个字符） SELECT * FROM student WHERE name LIKE '张%'; -- 姓张的（张一、张三、张三丰都能查到） SELECT * FROM student WHERE name LIKE '张_'; -- 姓张且名字只有2个字（张三、张伟，张小三查不到） 
-- 4. 范围查询（BETWEEN...AND 范围内，IN 固定集合） SELECT * FROM student WHERE age BETWEEN 18 AND 20; -- 18≤年龄≤20 SELECT * FROM student WHERE age IN (18, 20, 22); -- 年龄是18、20、22中的一个 
-- 5. 空值判断（IS NULL 是空，IS NOT NULL 不是空） SELECT * FROM student WHERE phone IS NULL; -- 查手机号为空的学生 SELECT * FROM student WHERE phone IS NOT NULL; -- 查手机号不为空的学生

新手避坑：判断空值不能用 = NULL 或 != NULL，必须用 IS NULL / IS NOT NULL（因为NULL不是“空字符串”，是“没有值”，无法用等于/不等于判断）。

3. 排序查询（ORDER BY，给查询结果排序）

类比：Excel的“排序”功能，按某一列升序/降序排列（比如按年龄从小到大排）。

-- 1. 按年龄升序（ASC 可省略，默认升序：从小到大） SELECT * FROM student ORDER BY age ASC;
 -- 2. 按年龄降序（DESC，从大到小） SELECT * FROM student ORDER BY age DESC; 
-- 3. 多字段排序（先按年龄降序，年龄相同再按ID升序） SELECT * FROM student ORDER BY age DESC, id ASC;

4. 聚合函数（统计数据，类比Excel的“求和、平均值、计数”）

核心：对一列数据进行“统计计算”，自动忽略NULL值（比如某行age为空，不会计入统计）。

函数

通俗作用

示例（查学生表）

COUNT(字段)

统计非空记录数（比如统计有多少个学生填了年龄）

SELECT COUNT(age) FROM student;

COUNT(*)

统计所有记录数（不管有没有空值，最常用，比如统计学生总数）

SELECT COUNT(*) FROM student;

SUM(字段)

求和（比如统计所有学生的年龄总和）

SELECT SUM(age) FROM student;

AVG(字段)

求平均值（比如统计学生的平均年龄）

SELECT AVG(age) FROM student;

MAX(字段)

求最大值（比如统计最大年龄）

SELECT MAX(age) FROM student;

MIN(字段)

求最小值（比如统计最小年龄）

SELECT MIN(age) FROM student;

新手易误解点：COUNT(*) 和 COUNT(字段) 的区别——COUNT(*) 统计所有行（包括空值），COUNT(字段) 只统计该字段非空的行（比如age有3行空值，就会少统计3行）。

5. 分组查询（GROUP BY，按“类别”统计）

类比：Excel的“分类汇总”（比如按性别分组，统计男生多少人、女生多少人）。

核心规则（新手必记）：分组后，SELECT 后面只能写「分组字段」和「聚合函数」，不能写其他字段；分组前过滤用WHERE，分组后过滤用HAVING。

-- 1. 按性别分组，统计每组人数（最常用） SELECT gender, COUNT(*) FROM student GROUP BY gender; 
-- 2. 按性别分组，统计每组平均年龄，且只显示平均年龄>19的组 SELECT gender, AVG(age) avg_age -- avg_age 是别名，方便后续过滤 FROM student GROUP BY gender -- 按性别分组 HAVING avg_age > 19; -- 分组后过滤（只留平均年龄>19的组）

新手避坑：WHERE 和 HAVING 的区别——① WHERE 用在 GROUP BY 前面，过滤“分组前的数据”，不能用聚合函数；② HAVING 用在 GROUP BY 后面，过滤“分组后的数据”，可以用聚合函数。

6. 分页查询（LIMIT，分批查看数据）

类比：Excel的“分页预览”（比如数据太多，一页显示不下，分两页看），常用于APP/网站的“分页”（比如第1页显示10条，第2页显示下10条）。

语法：LIMIT 偏移量, 每页条数（偏移量 = (页码-1)*每页条数，新手直接记这个公式）

-- 1. 查询第1页（每页2条，偏移量=0，(1-1)*2=0） SELECT * FROM student LIMIT 0, 2; 
-- 2. 查询第2页（每页2条，偏移量=2，(2-1)*2=2） SELECT * FROM student LIMIT 2, 2; 
-- 3. 查询第3页（每页2条，偏移量=4，(3-1)*2=4） SELECT * FROM student LIMIT 4, 2;

6.常用函数（MySQL的“小工具”，简化操作）

类比：Excel的“函数”（比如SUM、ROUND），MySQL也有自带的小工具，帮你快速处理数据，重点记常用的即可。

1. 字符串函数（处理文字）

-- 1. 拼接字符串（CONCAT，比如把“姓名：”和name拼接） SELECT CONCAT('姓名：', name) FROM student; 
-- 2. 字符串长度（LENGTH，比如查姓名有几个字） SELECT LENGTH(name) FROM student; 
-- 3. 转大写/小写（UPPER大写，LOWER小写） SELECT UPPER(name), LOWER(name) FROM student;

2. 数值函数（处理数字）

-- 1. 四舍五入（ROUND(数字, 保留小数位数)） SELECT ROUND(3.1415, 2); -- 结果：3.14（保留2位小数） 
-- 2. 向上取整（CEIL，比如3.1取4） SELECT CEIL(3.1); -- 结果：4 
-- 3. 向下取整（FLOOR，比如3.9取3） SELECT FLOOR(3.9); -- 结果：3

3. 日期函数（处理日期时间，最常用）

-- 1. 获取当前日期+时间（NOW()，常用在插入数据时） SELECT NOW(); -- 结果：2026-02-15 14:30:00 
-- 2. 获取当前日期（CURDATE()） SELECT CURDATE(); -- 结果：2026-02-15 
-- 3. 获取年、月、日 SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW()); -- 结果：2026、2、15

六、索引与优化

1. 索引类型

  • B-Tree 索引：InnoDB 默认，适合范围查询、排序（最常用）。就像书的 “目录”，通过目录快速找到页码（最常用的索引）
  • 哈希索引：就像“字典的哈希表”，查找速度极快，但不支持范围查询（比如 “查价格在 50-100 之间的书”）
  • 全文索引：用于全文搜索（如文章内容）。就像“搜索引擎”，用于快速搜索文章里的关键词（比如搜 “射雕” 能找到《射雕英雄传》）
  • 复合索引：多列索引（遵循最左前缀原则，如(username, age)）。

2. 索引创建与管理

CREATE INDEX idx_username_age ON users (username, age); -- 复合索引
SHOW INDEX FROM users; -- 查看索引
DROP INDEX idx_username ON users; -- 删除索引

新手易错点：

  • 索引不是越多越好！索引会占用空间，且会减慢 INSERT/UPDATE/DELETE 的速度（像给书编目录会花时间）
  • 只给经常查询的字段建索引（比如 “作者”、“书名”）

3. 查询优化（EXPLAIN）

  • 核心工具：EXPLAIN
  • 比喻：给查询 “拍 X 光片”，看 MySQL 是怎么执行的
  • 代码示例： 
    EXPLAIN SELECT * FROM books WHERE author = '金庸';
  • 重点关注：
    • type：const（主键 / 唯一索引）> ref（普通索引）> ALL（全表扫描，需优化）。
    • key：实际使用的索引（NULL 表示未用到）。
    • Extra：Using index（覆盖索引，好）；Using filesort（需优化）。

七、事务与锁机制

1. ACID 特性与隔离级别

  • ACID 特性（事务的 “四大金刚”）

    • 原子性（Atomicity）：比喻：“转账” 要么全成功（A 扣钱 B 加钱），要么全失败（回到原状）
    • 一致性（Consistency）：比喻：转账前后，A+B 的总钱数不变
    • 隔离性（Isolation）：比喻：两个转账操作互不干扰（像在两个独立的房间里操作）
    • 持久性（Durability）：比喻：转账成功后，数据永久保存（即使断电也不丢）
  • 事务控制： 
    START TRANSACTION; -- 开启事务
UPDATE accounts SET balance=balance-100 WHERE id=1;
UPDATE accounts SET balance=balance+100 WHERE id=2;
COMMIT; -- 提交（成功）/ ROLLBACK;（回滚，失败）
  • 隔离级别（从低到高）：
    • 读未提交 → 读已提交（Oracle 默认）→ 可重复读（MySQL InnoDB 默认） → 串行化。
级别 问题 说明
读未提交 脏读（读到未提交的数据） 几乎不用
读提交 不可重复读 Oracle 默认
可重复读 幻读（像幻觉一样多了行） MySQL 默认（InnoDB）
串行化 无问题，但性能差 像排队一样，一个一个来

2. 锁的类型

  • 表锁：锁定全表（MyISAM），并发差。就像把整个房间锁了，其他人都进不来（MyISAM 用）
  • 行锁：锁定行（InnoDB），并发好。只锁某一本书，其他人可以拿其他书（InnoDB 用）
  • 共享锁（S 锁）：读锁，SELECT ... LOCK IN SHARE MODE;
  • 排他锁（X 锁）：写锁，SELECT ... FOR UPDATE;（UPDATE/DELETE 自动加）。

3. 死锁分析与避免

  • 比喻：两个人互相等对方手里的书（A 等 B 的《射雕》，B 等 A 的《神雕》）
  • 分析：SHOW ENGINE INNODB STATUS;（查看死锁日志）。
  • 避免：事务小而快、按相同顺序访问行、用索引减少锁范围。

八、存储引擎

1. InnoDB 与 MyISAM 对比

特性 InnoDB MyISAM
事务 ✅ 支持 ❌ 不支持
锁 行锁（高并发） 表锁（并发差）
外键 ✅ 支持 ❌ 不支持
适用场景 高并发、电商、金融 读多写少、博客、新闻

⚠️ 新手提示：默认用 InnoDB（MySQL 5.5 + 默认）。

2. 引擎选择与切换

  • 选择原则：
    • 需要事务、外键、并发高 → 选 InnoDB（默认）
    • 只读、查询快 → 选 MyISAM
  • 建表指定： 
    CREATE TABLE 表名 (...) ENGINE=MyISAM;
  • 修改引擎： 
    ALTER TABLE 表名 ENGINE=InnoDB;（大表需谨慎）。

九、高级功能

1. 视图与存储过程

视图：虚拟表，简化查询：

比喻：“窗户”，透过窗户只能看到你想看的部分数据（虚拟表，不存实际数据）

CREATE VIEW v_users_adult AS SELECT username, age FROM users WHERE age >= 18;
SELECT * FROM v_users_adult;

存储过程：预编译 SQL，复用：

比喻：“预存的菜谱”，把一系列 SQL 语句存起来，调用时直接执行

DELIMITER //
CREATE PROCEDURE get_user_by_age(IN p_age INT)
BEGIN
    SELECT * FROM users WHERE age = p_age;
END //
DELIMITER ;
CALL get_user_by_age(20);

2. 触发器与事件调度

  • 触发器：表事件自动执行（慎用，影响性能）。就像“自动报警器”，当你插入 / 更新 / 删除数据时，自动执行一段 SQL
  • 事件调度：定时任务（类似 cron）： 
    CREATE EVENT delete_old_logs
ON SCHEDULE EVERY 1 DAY
DO DELETE FROM log WHERE create_time < DATE_SUB(NOW(), INTERVAL 7 DAY);

3. 分区表与分库分表

  • 分区表：大表拆小表（按日期 / 范围），对用户透明。
  • 分库分表：数据量亿级时用（如 ShardingSphere、MyCat）。

十、性能调优

1. 服务器参数优化（my.cnf）

  • innodb_buffer_pool_size：缓冲池大小（物理内存的 50%-75%）。
  • innodb_flush_log_at_trx_commit：1（安全）/ 0（性能）/ 2（折中）。
  • max_connections：最大连接数（500-1000）。

2. 慢查询日志分析

  • 开启（my.cnf）： 
    slow_query_log = ON
long_query_time = 2
  • 分析：mysqldumpslow -s t -t 10 /var/log/mysql/slow.log（取前 10 慢 SQL）。

3. 连接池与缓存

  • 应用层用连接池（Druid、HikariCP）。
  • 热点数据用 Redis/Memcached 缓存，减少 MySQL 压力。

十一、安全与权限

1. 用户管理与权限分配

  • 创建用户：CREATE USER 'admin'@'%' IDENTIFIED BY '强密码';
  • 最小权限原则：只给需要的权限（如GRANT SELECT ON mydb.* TO 'user'@'localhost';）。

2. 数据加密与 SQL 注入防范

  • 密码加密：应用层用 bcrypt/Argon2（不要用 MySQL 的PASSWORD()）。
  • SQL 注入：用预编译语句（如 MyBatis 的#{}, 不要拼接 SQL）。就像防止 “坏人” 在借书证上写恶意代码（比如 ' OR '1'='1）
    • 防范方法：
    • 用 “预处理语句”（PreparedStatement），别拼接 SQL
    • 代码示例（Java JDBC）： 
      String sql = "SELECT * FROM books WHERE author = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "金庸");  // 自动转义，防止注入

十二、备份与高可用

1. 备份工具

  • mysqldump：逻辑备份（小库）。
  • XtraBackup：物理备份（大库，热备）。

2. 主从复制

  • 原理：Master 写 Binlog → Slave I/O 线程读入中继日志 → Slave SQL 线程重放。
  • 作用：读写分离、数据备份。

3. 集群方案

  • MySQL Group Replication（MGR）：官方多主集群。
  • Galera Cluster：成熟的同步多主集群。

十三、常见问题

  1. 连接数过多：修改max_connections，检查连接池释放。
  2. 大表优化：加索引、分区、分库分表、归档历史数据。
  3. 数据不一致：检查主从延迟、innodb_flush_log_at_trx_commit参数。

十四、工具与生态

  • 管理工具：MySQL Workbench（官方）、Navicat、DBeaver。
  • 监控：Prometheus + Grafana、Percona PMM。
  • ORM：MyBatis（国内常用）、Hibernate。

十五、未来趋势

  1. MySQL 8.0 新特性：窗口函数、CTE、隐藏索引、降序索引、角色。
  2. 云数据库：AWS RDS、阿里云 RDS（免运维、高可用）。
  3. 数据库对比：
    • PostgreSQL：适合复杂查询、数据分析。
    • MongoDB：适合灵活 schema、高并发写（日志、社交）。

新手避坑总结

  1. 坑1：修改/删除数据忘加WHERE条件 → 直接修改/删除全表数据，操作前先备份，务必加WHERE！

  2. 坑2：判断空值用 = NULL / != NULL → 错误！必须用 IS NULL / IS NOT NULL（NULL不是空字符串）。

  3. 坑3：用INT存金额、价格 → 错误！会丢小数，用DECIMAL(M,D)（精确小数）。

  4. 坑4：分不清VARCHAR和CHAR → 长度不固定用VARCHAR（省空间），长度固定用CHAR（查得快）。

  5. 坑5：分组查询时，SELECT后面写了非分组、非聚合的字段 → 报错！分组后只能查分组字段和聚合函数。

  6. 坑6：用WHERE过滤聚合函数结果 → 错误！WHERE不能用聚合函数，用HAVING（分组后过滤）。

  7. 坑7：自增主键手动填值 → 没必要！插入时填NULL，MySQL会自动编号，手动填容易重复。

  8. 坑8：删除表用DELETE → 删全表数据用DELETE（可回滚），清空表+重置自增用TRUNCATE（不可回滚）。

  9. 坑9：查询用SELECT * → 效率低！只查需要的字段（比如SELECT name, age），尤其是数据量大时。

  10. 坑10：非空字段插入时不赋值 → 报错！非空约束（NOT NULL）的字段，必须填内容，或设默认值。

# mysql # 数据库
Logo
AtomGit开源社区

AtomGit 是由开放原子开源基金会联合 CSDN 等生态伙伴共同推出的新一代开源与人工智能协作平台。平台坚持“开放、中立、公益”的理念，把代码托管、模型共享、数据集托管、智能体开发体验和算力服务整合在一起，为开发者提供从开发、训练到部署的一站式体验。

更多推荐

cover

AI Agent理论学习与生产级设计框架收集（二）

avatar AtomGit开源社区
cover

OpenClaw龙虾爆火，企业如何抓住这波AI红利？支持接入的SaaS直播平台有哪些？

avatar AtomGit开源社区

基于C#winform部署RealESRGAN的onnx模型实现超分辨率图片无损放大模糊图片变清晰

生成对抗网络（GAN）：采用生成器-判别器架构，生成器负责重建高分辨率图像，判别器评估图像真实性ESRGAN 基础：在 ESRGAN 基础上改进，专门针对真实场景下的退化图像进行优化高阶退化模拟：通过模拟真实世界中复杂的图像退化过程（模糊、噪声、压缩伪影等）进行训练U-Net 判别器：使用 U-Net 结构的判别器，提供像素级的真实感判断。

avatar AtomGit开源社区