数据库理论基础

  1. 核心概念

    • 数据：描述事物的符号记录（如文本、数值、图像），需数字化后存储。

    • 数据库（DB）：结构化数据仓库，由

      数据库管理系统（DBMS）

      统一管理，核心特点：

      • 结构化：数据按预设模型组织（如二维表）。

      • 高共享性：多用户 / 应用可同时访问同一数据。

      • 低冗余度：避免数据重复存储，减少不一致风险。

      • 易扩充性：可按需新增表 / 字段，不影响现有应用。

      • 高独立性：含物理独立性（存储路径 / 格式变化不影响应用）和逻辑独立性（表结构调整可通过视图隔离）。

    • 数据库系统（DBS）：由数据、DB、DBMS、应用程序、用户组成的完整体系。

  2. 数据库管理系统 (DBMS)

    • 定义：管理数据库的核心软件，负责数据的存储、安全、一致性、并发控制、故障恢复和访问接口。

    • 核心组件：

      • 数据字典：存储元数据（如表结构、字段类型、索引信息）。

      • 查询处理器：解析、优化 SQL 语句（如执行计划生成）。

      • 存储管理器：管理数据存储（如缓存、磁盘 I/O）。

    • 支持的数据模型：

      模型类型结构特点优点缺点代表产品
      层次模型树形结构（父 - 子关系）适合层级数据（如部门）多对多关系难实现IBM IMS
      网状模型图形结构（多对多）支持复杂关系结构复杂，维护困难CODASYL
      关系模型二维表（行 = 记录，列 = 字段）简洁直观，支持 SQL大数据场景性能有限MySQL、Oracle、SQL Server
      面向对象模型以对象为核心（含属性 / 方法）适合复杂数据（如 GIS）兼容性差，学习成本高ObjectDB

  3. 常见数据库分类

    • 关系型数据库（RDBMS）：基于关系模型，依赖 SQL，支持事务 ACID，适合结构化数据存储（如用户信息、订单）。

      • 代表：MySQL、Oracle、SQL Server、PostgreSQL。

    • 非关系型数据库（NoSQL）：不依赖关系模型，适合非结构化 / 半结构化数据、高并发场景。

      类型特点代表产品适用场景
      键值存储以 “key-value” 存储，高效读写Redis、Memcached缓存、会话存储、计数器
      文档存储存储 JSON/BSON 格式文档MongoDB内容管理（如博客、商品描述）
      列族存储按列存储，适合批量查询HBase、Cassandra大数据分析（如日志、时序数据）
      图形数据库存储节点与关系（图结构）Neo4j、NebulaGraph社交网络、路径分析（如好友推荐）

MySQL 简介

  1. 发展历程

    • 1995 年：瑞典 MySQL AB 公司发布首个稳定版。

    • 2008 年：Sun 公司收购 MySQL AB。

    • 2009 年：Oracle 收购 Sun，接管 MySQL 版权。

    • 现状：社区版（开源免费）与企业版（付费支持）并行，MySQL 8.0 为当前主流版本（支持 UTF8mb4、窗口函数、CTE 等）。

  2. 核心特性

    • 跨平台：支持 Windows、Linux、macOS 等。

    • 多语言 API：支持 Python、Java、PHP、C++ 等。

    • 性能优化：多线程模型、查询缓存（MySQL 8.0 移除，推荐用 Redis）、索引优化。

    • 安全特性：支持 SSL 加密、用户权限控制、数据脱敏。

    • 编码支持：默认 UTF8mb4（兼容 emoji 和所有 Unicode 字符）。

    • 可扩展性：支持主从复制、读写分离、分库分表（如 ShardingSphere）。

  3. 应用场景

    • 互联网 Web：用户系统、订单系统、内容管理（如博客、电商）。

    • 数据仓库：日志存储、离线分析（搭配 Hadoop）。

    • 嵌入式系统：轻量级场景（如物联网设备数据存储）。

    • 代表用户：Google、腾讯、百度、阿里巴巴、Facebook。

  4. 架构组成（四层架构）

    架构层级核心功能
    网络连接层处理客户端 TCP 连接，管理连接池（避免频繁创建 / 销毁线程），支持 SSL 加密。
    数据库服务层核心逻辑层：SQL 接口（接收 SQL 语句）、解析器（语法校验）、优化器（生成最优执行计划）、缓存（MySQL 8.0 前的 Query Cache）。
    存储引擎层可插拔设计，负责数据的实际存储与读取，常用引擎：InnoDB（默认）、MyISAM。
    系统文件层存储物理文件： - 数据文件（.ibd：InnoDB 数据 + 索引；.MYD：MyISAM 数据） - 日志文件（binlog：二进制日志；redo log：重做日志；undo log：回滚日志） - 配置文件（my.ini/my.cnf）

MySQL 安装部署

  1. 版本类型

    • 社区版（Community Server）：开源免费，适合个人 / 中小企业，无官方技术支持。

    • 企业版（Enterprise Edition）：付费，含官方技术支持、备份工具（MySQL Enterprise Backup）、监控工具（MySQL Enterprise Monitor）。

    • 集群版（MySQL Cluster）：开源免费，基于 NDB 存储引擎，支持高可用（多节点冗余）。

  2. 安装方式（分平台）

    （1）Windows 安装

    安装格式步骤注意事项
    MSI 格式1. 下载 MSI 安装包（MySQL 官网） 2. 选择 “Server Only” 或 “Full” 3. 配置端口（默认 3306，需避免冲突） 4. 设置 root 密码（建议复杂度：字母 + 数字 + 符号） 5. 安装服务（默认服务名：MySQL80）若提示 “缺少 VC++ 运行库”，需先安装Microsoft Visual C++ Redistributable。
    ZIP 格式1. 解压 ZIP 包到指定目录（如D:\MySQL-8.0） 2. 新增my.ini配置文件（指定 basedir、datadir） 3. 配置环境变量（将bin目录加入 PATH） 4. 命令行初始化：mysqld --initialize --console（记录临时密码） 5. 安装服务：mysqld --install MySQL80 6. 启动服务：net start MySQL80my.ini需指定编码（character-set-server=utf8mb4）；初始化后需修改临时密码（ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';）。

    （2）Linux 安装（以 CentOS 7 为例）

    • Yum 安装（推荐）

      ：

      1. 下载 Yum 源：wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

      2. 安装源：rpm -ivh mysql80-community-release-el7-3.noarch.rpm

      3. 安装服务：yum install -y mysql-community-server

      4. 启动服务：systemctl start mysqld

      5. 查看临时密码：grep 'temporary password' /var/log/mysqld.log

      6. 登录并修改密码：mysql -u root -p → 输入临时密码 → ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass@123';（MySQL 8.0 要求强密码）

    • 安全配置

      ：执行

      mysql_secure_installation

      ，按提示：

      • 重置 root 密码（可选）

      • 删除匿名用户（建议开启）

      • 禁止 root 远程登录（生产环境建议开启）

      • 删除 test 数据库（建议开启）

  3. 服务管理

    操作Windows 命令Linux 命令
    启动服务net start MySQL80（服务名需匹配）systemctl start mysqld
    停止服务net stop MySQL80systemctl stop mysqld
    重启服务net restart MySQL80systemctl restart mysqld
    开机自启sc config MySQL80 start= autosystemctl enable mysqld
    登录数据库mysql -u root -p（密码交互输入）mysql -u root -h localhost -p
    远程登录mysql -u root -h 192.168.1.100 -P 3306 -p同 Windows，需开放防火墙 3306 端口（firewall-cmd --add-port=3306/tcp --permanent）

一、SQL 语句基础

  1. SQL 简介：结构化查询语言（Structured Query Language），用于关系型数据库的定义、操作、查询、控制，是数据库行业标准（ANSI SQL）。

  2. SQL 语句分类

    分类功能描述核心关键字
    DDL（数据定义）操作数据库对象（库、表、索引）CREATE（创建）、DROP（删除）、ALTER（修改）、SHOW（查看）、TRUNCATE（清空表）
    DML（数据操作）操作表中记录（增删改）INSERT（插入）、DELETE（删除）、UPDATE（更新）、REPLACE（替换）
    DQL（数据查询）查询表中数据SELECT（查询）、FROM（来源）、WHERE（条件）、ORDER BY（排序）、GROUP BY（分组）
    DCL（数据控制）控制用户权限GRANT（授权）、REVOKE（回收权限）、CREATE USER（创建用户）、DROP USER（删除用户）
    事务控制保证数据一致性START TRANSACTION（开启事务）、COMMIT（提交）、ROLLBACK（回滚）、SAVEPOINT（保存点）

  3. 书写规范

    • 大小写：SQL 关键字不区分大小写（建议大写，提高可读性），字符串常量区分大小写（如'abc'≠'ABC'）。

    • 结尾：语句以分号（;） 结束（部分客户端支持省略，但不推荐）。

    • 格式：关键词不跨多行，子句（如 FROM、WHERE）单独成行，用空格 / 缩进分层（例：

      SELECT id, name 
FROM student 
WHERE age > 18 
ORDER BY age DESC;

    • 注释：

      • 单行注释：-- 注释内容（-- 后需加空格）或# 注释内容（MySQL 特有）。

      • 多行注释：/* 注释内容 */（支持跨多行）。

  4. 命名约束

    • 长度：库 / 表 / 字段名最长 64 个字符。

    • 字符：由字母、数字、下划线（_）、#、$ 组成，必须以字母开头（如student_info合法，123_stu非法）。

    • 关键字：避免使用 SQL 保留字（如user、table），若必须使用，需用 ** 反引号（）** 包裹（如CREATE TABLE user (...);`）。

    • 唯一性：同一数据库内，库名唯一；同一库内，表名唯一；同一表内，字段名唯一。

二、数据库操作

  1. 登录与退出

    • 登录语法：

      mysql -u用户名 -h服务器地址 -P端口 -p密码 -D数据库名

      • 选项说明：-u（指定用户）、-h（指定 IP，默认localhost）、-P（指定端口，默认 3306）、-p（指定密码，建议不直接写密码，仅用-p后交互输入，避免密码泄露）、-D（登录后直接切换到目标库）。

      • 示例：远程登录mysql -u root -h 192.168.1.100 -P 3306 -p。

    • 退出：exit、quit或\q（任意一个即可）。

  2. 核心操作语句

    操作需求SQL 语句说明
    查看所有数据库SHOW DATABASES;显示 MySQL 内置库（如 mysql、information_schema）和自定义库。
    模糊查看数据库SHOW DATABASES LIKE 'db%';%匹配任意长度字符，_匹配单个字符（如'db_'匹配db1、db2）。
    创建数据库CREATE DATABASE [IF NOT EXISTS] 库名 [CHARACTER SET 字符集] [COLLATE 排序规则];IF NOT EXISTS避免库已存在时报错；默认字符集 utf8mb4（MySQL 8.0）。
    查看数据库创建语句SHOW CREATE DATABASE 库名;显示库的完整定义（含字符集、排序规则）。
    切换数据库USE 库名;后续操作默认在该库下执行；查看当前库：SELECT DATABASE();。
    查看当前登录用户SELECT USER();显示格式：用户名@主机地址（如root@localhost）。
    删除数据库DROP DATABASE [IF EXISTS] 库名;谨慎操作：删除后数据无法恢复，建议先备份。
    修改数据库字符集ALTER DATABASE 库名 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;仅影响后续新建的表，已存在的表需单独修改。

三、MySQL 字符集

  1. 基本概念

    • 字符集（Character Set）：定义字符的编码规则（如将 “中” 编码为0xE4B8AD）。

    • 排序规则（Collation）：定义字符的比较 / 排序规则（如是否区分大小写），命名格式：字符集_区域_后缀（如utf8mb4_general_ci，ci= 大小写不敏感，bin= 二进制比较）。

  2. 常见字符集对比

    字符集支持范围存储长度适用场景注意事项
    latin1西欧、希腊字符1 字节纯英文场景MySQL 默认字符集（5.7 及之前），不支持中文。
    gbk简体中文、繁体中文（部分）1-2 字节中文场景（如早期网站）兼容 GB2312，不支持 emoji。
    big5繁体中文（台湾、香港）1-2 字节繁体中文场景不兼容 GBK，不支持 emoji。
    utf8MySQL 中为utf8mb3别名1-3 字节多数语言（不含 emoji）无法存储 4 字节字符（如 emoji😀、生僻字），不推荐使用。
    utf8mb4完整 Unicode（含 emoji、生僻字）1-4 字节全球多语言、需 emoji 场景MySQL 8.0 默认字符集，推荐优先使用。

  3. 字符集相关查询

    • 查看 MySQL 支持的所有字符集：SHOW CHARACTER SET;（或SHOW CHARSET;）。

    • 查看支持的排序规则：SHOW COLLATION;。

    • 查看当前字符集配置：SHOW VARIABLES LIKE 'character_set_%';（关键变量：character_set_client（客户端编码）、character_set_server（服务器编码））。

    • 查看当前排序规则配置：SHOW VARIABLES LIKE 'collation_%';。

  4. 字符集修改（关键场景）

    • 修改数据库字符集：ALTER DATABASE 库名 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;。

    • 修改表字符集：ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;（会同步修改表中所有字段的字符集）。

    • 修改字段字符集：ALTER TABLE 表名 MODIFY 字段名 VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;。

四、数据库对象与命名规范

  1. 核心数据库对象：数据库（DB）、表（Table）、索引（Index）、视图（View）、存储过程（Procedure）、函数（Function）、触发器（Trigger）、事件（Event）。

  2. 生产环境命名规范

    对象类型命名规则示例
    数据库项目名 + 库功能简写，全小写，下划线分隔，不超过 30 字符mall_db（商城数据库）、blog_user_db（博客用户库）
    表- 常规表：t_ + 模块名 + 表功能，全小写 - 临时表：temp_ + 功能 + 日期 - 备份表：bak_ + 原表名 + 日期t_user（用户表）、temp_order_202405、bak_user_20240520
    字段模块名（可选） + 字段含义，全小写，下划线分隔，避免冗余（如user_id无需写t_user_id）user_id（用户 ID）、order_total（订单金额）
    索引- 普通索引：idx_ + 表名 + 字段名 - 唯一索引：uniq_ + 表名 + 字段名 - 主键索引：默认PRIMARYidx_user_age（用户年龄索引）、uniq_user_phone（用户手机号唯一索引）
    视图v_ + 视图功能，全小写v_user_order（用户订单视图）
    存储过程proc_ + 功能，全小写proc_calculate_order（计算订单金额）

五、表的基本操作

5.1 数据类型详解

MySQL 数据类型按用途分为数值型、字符型、日期时间型、特殊类型，需根据业务场景选择（避免过度占用空间）。

（1）数值型
类型字节数取值范围（有符号）取值范围（无符号，unsigned）适用场景
tinyint1-128 ~ 1270 ~ 255状态（0 = 禁用，1 = 启用）、布尔值（tinyint (1)，0=false，1=true）
smallint2-32768 ~ 327670 ~ 65535数量较少的 ID（如分类 ID）
int4-2^31 ~ 2^31-1（约 - 21 亿～21 亿）0 ~ 2^32-1（约 42 亿）主键 ID（如用户 ID、订单 ID）
bigint8-2^63 ~ 2^63-10 ~ 2^64-1超大 ID（如日志 ID、分布式 ID）
float(m,d)4单精度浮点型，m = 总位数，d = 小数位存在精度丢失非精确数值（如温度、重量）
double(m,d)8双精度浮点型，精度高于 float仍有精度丢失非精确数值（如金额估算）
decimal(m,d)可变定点型，m = 总位数（最大 65），d = 小数位（最大 30）无精度丢失精确数值（如金额、税率）
（2）字符型
类型长度限制存储特点适用场景
char(n)n（1~255）固定长度，不足补空格短字符串（如手机号、身份证号、性别）
varchar(n)n（1~65535）可变长度，按实际内容存储长字符串（如用户名、地址、商品描述）
text最大 65535 字节存储长文本，不支持默认值超长文本（如文章内容、评论）
mediumtext最大 16MB比 text 更大大文本（如日志、富文本内容）
longtext最大 4GB超大文本极少用（如备份数据）
blob最大 65535 字节存储二进制数据（如图片）不推荐（建议存储图片 URL，而非二进制）
（3）日期时间型
类型字节数取值范围格式适用场景特点
date31000-01-01 ~ 9999-12-31YYYY-MM-DD生日、注册日期仅存日期，不受时区影响
time3-838:59:59 ~ 838:59:59HH:MM:SS时长、打卡时间可存负数（如跨天时间）
datetime81000-01-01 00:00:00 ~ 9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS订单创建时间、日志时间不受时区影响，默认值需显式指定
timestamp41970-01-01 00:00:01 ~ 2038-01-19 03:14:07YYYY-MM-DD HH:MM:SS最后更新时间受时区影响，默认自动更新（ON UPDATE CURRENT_TIMESTAMP）
year11901 ~ 2155YYYY年份（如毕业年份）存储紧凑，支持 2 位（如 99=1999）

5.2 表约束（核心）

约束用于保证表数据的完整性和一致性，创建表时可指定，也可后续修改。

约束类型关键字功能描述示例（创建表时）
主键约束PRIMARY KEY唯一标识表中记录，非空且唯一，一张表仅一个主键（可多字段联合主键）id INT PRIMARY KEY AUTO_INCREMENT（AUTO_INCREMENT：自增，仅数值型主键支持）
非空约束NOT NULL字段值不可为空name VARCHAR(50) NOT NULL COMMENT '用户名'
默认约束DEFAULT字段未赋值时，使用默认值status TINYINT DEFAULT 1 COMMENT '状态：1=正常，0=禁用'
唯一约束UNIQUE字段值唯一（允许 NULL，且多个 NULL 不冲突）phone VARCHAR(20) UNIQUE COMMENT '手机号，唯一'
外键约束FOREIGN KEY关联另一张表的主键，保证数据一致性（如订单表的 user_id 关联用户表的 id）user_id INT, FOREIGN KEY (user_id) REFERENCES t_user(id) ON DELETE CASCADE（ON DELETE CASCADE：主表记录删除时，从表关联记录也删除）
检查约束CHECK限制字段值的范围（MySQL 8.0 及以上支持）age INT CHECK (age > 0 AND age < 150) COMMENT '年龄需在0-150之间'

5.3 表操作核心语句

（1）创建表
CREATE TABLE [IF NOT EXISTS] t_user (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID（主键）',
    name VARCHAR(50) NOT NULL COMMENT '用户名',
    phone VARCHAR(20) UNIQUE COMMENT '手机号',
    age INT DEFAULT 0 CHECK (age >= 0 AND age <= 150) COMMENT '年龄',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

  • 说明：ENGINE=InnoDB指定存储引擎（默认），COMMENT为表 / 字段添加注释（便于维护）。

（2）查看表信息
需求SQL 语句
查看库中所有表SHOW TABLES;（当前库）或SHOW TABLES FROM mall_db;（指定库）
查看表结构DESC t_user;（或DESCRIBE t_user;、EXPLAIN t_user;）
查看表完整创建语句SHOW CREATE TABLE t_user\G;（\G：按行显示，避免列过长）
查看表状态SHOW TABLE STATUS LIKE 't_user'\G;（含引擎、字符集、行数等）
（3）修改表结构（ALTER TABLE）
需求SQL 语句
修改表名ALTER TABLE t_old RENAME TO t_new;（或RENAME TABLE t_old TO t_new;）
添加字段ALTER TABLE t_user ADD email VARCHAR(100) AFTER name;（AFTER 指定位置，FIRST 放第一列）
删除字段ALTER TABLE t_user DROP email;
修改字段名 + 类型ALTER TABLE t_user CHANGE age user_age INT DEFAULT 18;（CHANGE 需指定新旧字段名）
仅修改字段类型ALTER TABLE t_user MODIFY user_age TINYINT DEFAULT 18;
添加主键约束ALTER TABLE t_user ADD PRIMARY KEY (id);
添加唯一约束ALTER TABLE t_user ADD UNIQUE (phone);
删除主键约束ALTER TABLE t_user DROP PRIMARY KEY;（仅当主键无 AUTO_INCREMENT 时）
（4）删除表
DROP TABLE [IF EXISTS] t_user; -- IF EXISTS避免表不存在时报错

  • 注意：DROP TABLE会删除表结构和所有数据，无法恢复；若仅需清空数据（保留结构），用TRUNCATE TABLE t_user;（比 DELETE 更快，不触发事务）。

（5）表复制（备份场景）
需求SQL 语句特点
仅复制表结构CREATE TABLE t_user_bak LIKE t_user;复制结构、约束、索引，不复制数据
复制结构 + 全部数据CREATE TABLE t_user_bak SELECT * FROM t_user;不复制主键自增属性，需手动添加
复制结构 + 部分数据CREATE TABLE t_user_adult SELECT * FROM t_user WHERE age >= 18;按条件筛选数据
复制部分字段 + 数据CREATE TABLE t_user_name SELECT id, name FROM t_user;仅复制指定字段

六、SQL 查询进阶（DQL 补充）

DQL 是 SQL 中最常用的部分，核心是SELECT语句，支持复杂条件查询、排序、分组、分页等。

6.1 基础查询语法

SELECT [DISTINCT] 字段1, 字段2, 聚合函数(字段)
FROM 表名1
[INNER/LEFT/RIGHT JOIN 表名2 ON 关联条件] -- 多表连接
[WHERE 行过滤条件] -- 过滤行数据
[GROUP BY 分组字段] -- 按字段分组
[HAVING 组过滤条件] -- 过滤分组结果（需配合GROUP BY）
[ORDER BY 排序字段 ASC/DESC] -- 排序（ASC升序，默认；DESC降序）
[LIMIT 偏移量, 条数]; -- 分页（偏移量从0开始）

6.2 关键子句详解

（1）WHERE 条件（行过滤）

支持多种运算符：

  • 比较运算符：=（等于）、!=/<>（不等于）、>、<、>=、<=、BETWEEN ... AND ...（范围）、IN (值1,值2)（在集合中）、IS NULL（为空）、IS NOT NULL（不为空）。

  • 逻辑运算符：AND（且）、OR（或）、NOT（非）。

  • 模糊运算符：LIKE（配合%/_），如name LIKE '张%'（匹配姓张的所有名字）、phone LIKE '138____5678'（匹配 138 开头、尾号 5678 的手机号）。

示例：查询 18-30 岁、姓张的用户：

SELECT id, name, age 
FROM t_user 
WHERE age BETWEEN 18 AND 30 
  AND name LIKE '张%';
（2）聚合函数（统计数据）
函数功能描述示例
COUNT (字段)统计非 NULL 值的行数COUNT(id)（统计用户总数）
COUNT(*)统计所有行数（含 NULL）COUNT(*)（统计订单总数）
SUM (字段)计算数值字段的总和SUM(order_total)（订单总金额）
AVG (字段)计算数值字段的平均值AVG(age)（用户平均年龄）
MAX (字段)取字段最大值MAX(create_time)（最新注册时间）
MIN (字段)取字段最小值MIN(price)（最低商品价格）

示例：统计用户总数、平均年龄、最大年龄：

SELECT 
  COUNT(*) AS total_user, -- AS给字段起别名
  AVG(age) AS avg_age, 
  MAX(age) AS max_age 
FROM t_user;
（3）GROUP BY 分组

按指定字段分组，聚合函数对每组单独计算。 示例：按年龄分组，统计每组用户数：

SELECT age, COUNT(*) AS user_count 
FROM t_user 
GROUP BY age 
HAVING user_count > 5; -- HAVING过滤分组结果（用户数>5的年龄组）

  • 注意：WHERE过滤行数据（分组前），HAVING过滤分组结果（分组后），HAVING可使用聚合函数。

（4）ORDER BY 排序

按一个或多个字段排序，多个字段用逗号分隔。 示例：按年龄降序、创建时间升序排序：

SELECT id, name, age, create_time 
FROM t_user 
ORDER BY age DESC, create_time ASC;
（5）LIMIT 分页

用于分页查询，语法：LIMIT 偏移量, 每页条数（偏移量 =（页码 - 1）× 每页条数）。 示例：查询第 2 页数据（每页 10 条）：

SELECT id, name 
FROM t_user 
LIMIT 10, 10; -- 偏移量10（跳过前10条），取10条
（6）多表连接查询

假设有两张表：t_user（用户表，id 为主键）和t_order（订单表，user_id 为外键，关联 t_user.id）。

连接类型功能描述示例 SQL
INNER JOIN只返回两表匹配的记录SELECT u.name, o.order_no FROM t_user u INNER JOIN t_order o ON u.id = o.user_id;
LEFT JOIN返回左表所有记录，右表匹配不到则为 NULLSELECT u.name, o.order_no FROM t_user u LEFT JOIN t_order o ON u.id = o.user_id;
RIGHT JOIN返回右表所有记录，左表匹配不到则为 NULLSELECT u.name, o.order_no FROM t_user u RIGHT JOIN t_order o ON u.id = o.user_id;

七、事务与隔离级别

7.1 事务的 ACID 特性

事务是一组不可分割的 SQL 操作，要么全部执行成功，要么全部失败回滚，核心保证数据一致性，需满足 ACID：

  • 原子性（Atomicity）：事务中所有操作要么全成，要么全败（如转账：扣钱和加钱必须同时成功或失败）。

  • 一致性（Consistency）：事务执行前后，数据总状态不变（如转账前后，双方总金额不变）。

  • 隔离性（Isolation）：多个事务并发执行时，彼此不干扰（避免 “脏读”“不可重复读” 等问题）。

  • 持久性（Durability）：事务提交后，数据永久保存在磁盘（即使断电，数据也不丢失）。

7.2 事务控制语句

-- 1. 开启事务（默认自动提交，开启后需手动提交）
START TRANSACTION; -- 或 BEGIN;
​
-- 2. 执行DML操作（如插入、更新）
UPDATE t_user SET balance = balance - 100 WHERE id = 1; -- 用户1扣100
UPDATE t_user SET balance = balance + 100 WHERE id = 2; -- 用户2加100
​
-- 3. 提交事务（数据永久生效）
COMMIT;
​
-- 4. 若出错，回滚事务（恢复到事务开始前状态）
ROLLBACK;
​
-- 5. 保存点（可选，回滚到指定点，而非整个事务）
SAVEPOINT sp1; -- 创建保存点sp1
UPDATE t_user SET age = 20 WHERE id = 1;
ROLLBACK TO sp1; -- 回滚到sp1，上述UPDATE不生效

7.3 事务隔离级别

MySQL 默认隔离级别为可重复读（Repeatable Read），通过SET TRANSACTION ISOLATION LEVEL修改，各级别解决的并发问题如下：

隔离级别脏读（读未提交数据）不可重复读（同一事务内多次读结果不同）幻读（同一事务内多次读行数不同）适用场景
读未提交（Read Uncommitted）允许允许允许极少用（如临时统计，不要求准确性）
读已提交（Read Committed）禁止允许允许多数互联网场景（如电商订单）
可重复读（Repeatable Read）禁止禁止禁止（MySQL 通过 MVCC 实现）MySQL 默认，适合对一致性要求高的场景
串行化（Serializable）禁止禁止禁止极高一致性场景（如金融转账，性能低）

  • 查询当前隔离级别：SELECT @@transaction_isolation;（MySQL 8.0）或SELECT @@tx_isolation;（MySQL 5.7）。

  • 修改隔离级别（会话级，仅当前连接生效）：SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;。

八、索引基础

8.1 索引的作用

  • 优点：加速查询（如通过索引定位记录，避免全表扫描），加速排序 / 分组（索引本身有序）。

  • 缺点：占用额外存储空间；减慢写操作（INSERT/UPDATE/DELETE 需同步维护索引）。

8.2 索引类型

类型特点适用场景
主键索引自动创建，唯一且非空，名PRIMARY主键字段（如 user.id）
唯一索引字段值唯一（允许 NULL）唯一字段（如 user.phone、user.email）
普通索引无约束，仅加速查询频繁查询的非唯一字段（如 user.age）
联合索引基于多个字段的索引（如 (age, name)）多字段联合查询（如WHERE age=20 AND name LIKE '张%'）
全文索引用于全文搜索（如文章内容）text 类型字段（如 article.content），MySQL 5.6 + 支持 InnoDB 全文索引

8.3 索引操作语句

-- 1. 创建索引（创建表时或后续添加）
-- （1）创建表时添加
CREATE TABLE t_user (
    id INT PRIMARY KEY, -- 主键索引
    phone VARCHAR(20) UNIQUE, -- 唯一索引
    age INT,
    INDEX idx_user_age (age) -- 普通索引
);
​
-- （2）后续添加索引
CREATE INDEX idx_user_age ON t_user(age); -- 普通索引
CREATE UNIQUE INDEX uniq_user_phone ON t_user(phone); -- 唯一索引
CREATE INDEX idx_user_age_name ON t_user(age, name); -- 联合索引
​
-- 2. 查看索引
SHOW INDEX FROM t_user; -- 查看表中所有索引
SHOW KEYS FROM t_user; -- 与上等价
​
-- 3. 删除索引
DROP INDEX idx_user_age ON t_user; -- 删除普通索引
ALTER TABLE t_user DROP INDEX uniq_user_phone; -- 删除唯一索引
ALTER TABLE t_user DROP PRIMARY KEY; -- 删除主键索引（需无AUTO_INCREMENT）

8.4 索引使用注意事项

  • 最左前缀原则：联合索引（如 (age, name)）仅在查询条件包含左起第一个字段时生效（如WHERE age=20生效，WHERE name='张三'不生效）。

  • 避免索引失效

    ：

    • 不使用函数 / 表达式操作索引字段（如WHERE SUBSTR(name,1,1)='张'会导致idx_user_name失效）。

    • 不使用!=/<>/IS NOT NULL（可能导致全表扫描）。

    • 字符串不加引号（如WHERE phone=13800138000，若 phone 是 varchar 类型，会导致索引失效）。

  • 索引不是越多越好：单表索引建议不超过 5 个（过多影响写性能）。

九、存储引擎对比

MySQL 支持多种存储引擎，核心差异在事务支持、锁机制、存储方式，常用引擎对比：

特性InnoDB（默认）MyISAMMemory（内存引擎）
事务支持支持（ACID）不支持不支持
锁机制行级锁（支持高并发）表级锁（并发性能差）表级锁
外键支持支持不支持不支持
存储方式磁盘存储（.ibd 文件）磁盘存储（.MYD 数据 +.MYI 索引）内存存储（重启后数据丢失）
缓存机制缓存数据 + 索引仅缓存索引全表缓存（内存中）
崩溃恢复支持（通过 redo log/undo log）不支持（需手动修复）不支持（数据丢失）
适用场景事务场景（订单、用户）只读 / 少写场景（日志、报表）临时数据（会话缓存、临时表）

  • 查看当前存储引擎：SHOW ENGINES;。

  • 修改表存储引擎：ALTER TABLE t_user ENGINE=MyISAM;（仅当表无外键时，InnoDB 可转 MyISAM）。

# 数据库 # mysql
Logo
DAMO开发者矩阵

DAMO开发者矩阵，由阿里巴巴达摩院和中国互联网协会联合发起，致力于探讨最前沿的技术趋势与应用成果，搭建高质量的交流与分享平台，推动技术创新与产业应用链接，围绕“人工智能与新型计算”构建开放共享的开发者生态。

更多推荐

cover

2026岳麓大会在长沙举行，湖南亮出“人工智能+”“创业”双答卷

avatar DAMO开发者矩阵

Work Agent深度解读：AI从对话走向自主长程任务

AI交互形态正在发生一次底层转变。在早期阶段，AI产品以单轮问答为主，用户抛出问题，模型即时返回对应文本；随着技术迭代，多轮对话让上下文得以保留，能够在一次会话内承接连续提问。工具调用能力出现之后，AI不再局限于文本生成，可以调动外部检索、计算、文件读写等能力拓展输出边界。Work Agent则在这条演进路线上继续向前，将零散的工具动作串联为完整的任务链。长程任务执行，正是Work Agent与传

avatar DAMO开发者矩阵
cover

OpenAI CEO奥特曼在联合国就AI安全发言；诺因发布技术报告称机器人可一教就会；AI Hype Index专栏称AI正被优化用于作弊 | 科技日报0924

avatar DAMO开发者矩阵