mobile wallpaper 1mobile wallpaper 2mobile wallpaper 3mobile wallpaper 4mobile wallpaper 5mobile wallpaper 6mobile wallpaper 7mobile wallpaper 8mobile wallpaper 9mobile wallpaper 10mobile wallpaper 11mobile wallpaper 12mobile wallpaper 13mobile wallpaper 14
Profile Image of the Author
WYZ
欢迎来到 WYZ の拾忆录
这里是我的私人记忆博物馆。 我会在这里整理技术学习的点滴、记录生活中的灵光一闪，也会写下那些不想被时间冲走的随笔与感悟。愿这些文字，能成为你我共鸣的频率。
标签
3100 字
8 分钟
MySQL 与 JDBC
2026-07-02
后端
MySQL
/
JDBC
/
SQL

MySQL 与 JDBC#

一、SQL 语句五大分类#

SQL 可以按用途分成五类，学习时先记住每类语句解决什么问题：

类型全称常用语句作用
DDL数据定义语言CREATE、ALTER、DROP、TRUNCATE建库、建表、修改表结构
DML数据操纵语言INSERT、UPDATE、DELETE增删改数据
DQL数据查询语言SELECT、FROM、WHERE、JOIN查询数据
DCL数据控制语言GRANT、REVOKE权限管理
TCL事务控制语言COMMIT、ROLLBACK、SAVEPOINT事务提交、回滚与保存点

常见示例：

CREATE TABLE t_student (...);             -- DDL：创建表
ALTER TABLE t_student ADD email VARCHAR(50); -- DDL：修改表结构
INSERT INTO t_student VALUES (...);       -- DML：插入数据
UPDATE t_student SET age = 20 WHERE id = 1; -- DML：修改数据
SELECT * FROM t_student WHERE age > 18;   -- DQL：查询数据

二、MySQL 字段类型选择#

建表时字段类型要和业务场景匹配，核心分为数值、字符串、日期时间和其他类型。

1. 数值类型#

类型特点适用场景
TINYINT范围小状态、开关、标志位
INT约正负 21 亿普通主键、数量
BIGINT范围更大超大表主键、大 ID
DECIMAL(M,D)精确小数金额、GPA、需要精度的业务数据
FLOAT / DOUBLE近似浮点科学计算，不适合金额

重点：金额必须使用 DECIMAL，不要用 FLOAT 或 DOUBLE，避免精度丢失。

2. 字符串类型#

类型特点适用场景
CHAR(N)定长，最大 255身份证号、固定长度编码
VARCHAR(N)变长，最大长度受行大小限制姓名、标题、描述
TEXT / LONGTEXT大文本文章、日志、长内容
ENUM枚举值性别、固定状态

定长字段选 CHAR，长度差异较大的字段选 VARCHAR。

3. 日期时间类型#

类型格式适用场景
DATEYYYY-MM-DD生日、入学日期
TIMEHH:MM:SS上课时间
DATETIMEYYYY-MM-DD HH:MM:SS普通业务时间戳
TIMESTAMP类似 DATETIME，受时区影响跨时区创建/更新时间
YEARYYYY年份

一般业务优先使用 DATETIME；跨时区场景再考虑 TIMESTAMP。

4. 其他类型#

类型说明适用场景
BOOLEAN本质是 TINYINT(1)是否、开关
BLOB二进制大对象文件、图片，但实际项目通常只存文件路径
JSONMySQL 5.7+ 支持结构化配置、扩展字段
SET集合类型标签、权限位

建表经验：主键通常用 INT AUTO_INCREMENT，超大表使用 BIGINT；姓名、描述类字段用 VARCHAR，长度按业务上限设置；字符集统一使用 utf8mb4，支持中文和 emoji。

三、数据库与学生表准备#

示例库名为 student_manage，主表为 t_student。

CREATE DATABASE student_manage DEFAULT CHARSET utf8mb4;
USE student_manage;


CREATE TABLE t_student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL COMMENT '姓名',
    age INT COMMENT '年龄',
    gender VARCHAR(4) COMMENT '性别',
    class_name VARCHAR(30) COMMENT '班级',
    enroll_date DATE COMMENT '入学日期'
);


INSERT INTO t_student (name, age, gender, class_name, enroll_date) VALUES
('张三', 18, '男', '计算机1班', '2024-09-01'),
('李四', 19, '女', '软件工程2班', '2023-09-01'),
('王五', 18, '男', '计算机1班', '2024-09-01');

建表时重点关注三件事：字段类型是否合适、字符集是否统一、主键是否使用 AUTO_INCREMENT 自动维护。

四、多表建模：学生、科目与成绩#

学生和科目是多对多关系：一个学生可以选多门课，一门课也可以被多个学生选择。因此需要中间关系表 t_score 桥接。

关系结构：

  • t_student：学生主表。
  • t_subject：科目主表。
  • t_score：成绩关系表，通过 student_id 和 subject_id 关联学生与科目。
CREATE TABLE t_subject (
    id INT PRIMARY KEY AUTO_INCREMENT,
    subject_name VARCHAR(30) NOT NULL COMMENT '科目名',
    credit DOUBLE COMMENT '学分',
    teacher VARCHAR(30) COMMENT '任课老师'
);


INSERT INTO t_subject (subject_name, credit, teacher) VALUES
('Java程序设计', 4.0, '王老师'),
('数据库原理', 3.0, '李老师'),
('高等数学', 5.0, '张老师');


CREATE TABLE t_score (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL COMMENT '学生ID',
    subject_id INT NOT NULL COMMENT '科目ID',
    score DOUBLE COMMENT '分数',
    exam_date DATE COMMENT '考试日期',
    FOREIGN KEY (student_id) REFERENCES t_student(id),
    FOREIGN KEY (subject_id) REFERENCES t_subject(id)
);

关系理解：t_student 到 t_score 是一对多，t_subject 到 t_score 也是一对多，两个一对多组合起来实现学生和科目的多对多。

五、SQL 进阶查询#

1. JOIN 关联查询#

JOIN 适合把多张表按关联字段拼成一张结果表。

查询每位学生每门科目的得分：

SELECT s.name, sub.subject_name, sc.score
FROM t_student s
INNER JOIN t_score sc ON s.id = sc.student_id
INNER JOIN t_subject sub ON sub.id = sc.subject_id;

列出全部学生及其成绩，即使没有成绩也显示学生：

SELECT s.name, sc.score
FROM t_student s
LEFT JOIN t_score sc ON s.id = sc.student_id;

INNER JOIN 只返回匹配成功的数据；LEFT JOIN 会保留左表全部数据。

2. 子查询#

子查询适合用一个查询结果驱动另一个查询。

查询高于全班平均分的成绩：

SELECT student_id, subject_id, score
FROM t_score
WHERE score > (
    SELECT AVG(score) FROM t_score
);

查询至少选修过 Java 程序设计或数据库原理的学生姓名：

SELECT DISTINCT s.name
FROM t_student s
WHERE s.id IN (
    SELECT student_id
    FROM t_score
    WHERE subject_id IN (
        SELECT id
        FROM t_subject
        WHERE subject_name IN ('Java程序设计', '数据库原理')
    )
);

3. 聚合、排序与分页#

常见组合包括 GROUP BY 分组统计、ORDER BY 排序、LIMIT 分页、DISTINCT 去重。

SELECT subject_id, AVG(score) AS avg_score, COUNT(*) AS exam_count
FROM t_score
GROUP BY subject_id
ORDER BY avg_score DESC
LIMIT 10;

六、MySQL 8 开窗函数#

开窗函数在保留原始行的基础上，为每一行计算分组内的排名、统计值或累计值。普通聚合会把多行合并成一行，开窗函数不会。

通用语法：

函数名() OVER (
    PARTITION BY 分组列
    ORDER BY 排序列
    ROWS BETWEEN ...
)

查询各科前三名：

SELECT *
FROM (
    SELECT
        s.name,
        sub.subject_name,
        sc.score,
        ROW_NUMBER() OVER (
            PARTITION BY sc.subject_id
            ORDER BY sc.score DESC
        ) AS rn
    FROM t_score sc
    JOIN t_student s ON s.id = sc.student_id
    JOIN t_subject sub ON sub.id = sc.subject_id
) t
WHERE rn <= 3;

排名函数区别：

函数并列时表现适用场景
ROW_NUMBER()严格 1、2、3，不并列每组取前 N 条
RANK()并列同名次，下一名跳号，如 1、1、3比赛排名
DENSE_RANK()并列同名次，下一名连续，如 1、1、2连续排名

组内平均分和学生总分：

SELECT
    s.name,
    sub.subject_name,
    sc.score,
    AVG(sc.score) OVER (PARTITION BY sc.subject_id) AS subject_avg,
    SUM(sc.score) OVER (PARTITION BY sc.student_id) AS student_total
FROM t_score sc
JOIN t_student s ON s.id = sc.student_id
JOIN t_subject sub ON sub.id = sc.subject_id;

七、MySQL 索引：概念、分类与优化#

索引可以理解为数据库为某些列维护的一份“排好序的目录”，让查询不用全表扫描就能快速定位数据。

1. 为什么索引快#

  • 无索引：全表扫描，逐行比较，复杂度接近 O(N)。
  • 有索引：通过 B+Tree 从根节点到叶子节点定位数据，复杂度接近 O(logN)。
  • InnoDB 默认使用 B+Tree 索引，天然支持等值查询、范围查询和排序。

索引不是越多越好。索引会占用空间，INSERT、UPDATE、DELETE 时还要维护索引树，写入会变慢。

2. 常见索引分类#

类型说明场景
主键索引PRIMARY KEY，非空且唯一；InnoDB 中也是聚簇索引主键 ID
唯一索引UNIQUE，值不能重复，可以为 NULL手机号、身份证号
普通索引INDEX / KEY，只加速查询高频查询字段
联合索引多列组成一个索引，如 (a, b, c)多条件组合查询
全文索引FULLTEXT，用于长文本搜索替代低效的 LIKE '%关键词%'

3. 最左前缀原则#

假设存在联合索引 idx_abc(a, b, c)：

  • 可以命中：WHERE a = ?、WHERE a = ? AND b = ?、WHERE a = ? AND b = ? AND c = ?。
  • 不容易命中：WHERE b = ?、WHERE c = ?、WHERE b = ? AND c = ?。

联合索引必须从最左列开始连续使用。

4. 建索引语法与 EXPLAIN#

CREATE TABLE t_student (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    stu_no VARCHAR(20) NOT NULL,
    name VARCHAR(30) NOT NULL,
    class_id BIGINT,
    UNIQUE KEY uk_stu_no (stu_no),
    KEY idx_name (name),
    KEY idx_cls_name (class_id, name)
);

用 EXPLAIN 检查 SQL 是否走索引：

EXPLAIN SELECT *
FROM t_student
WHERE class_id = 1 AND name = '张三';

重点看这些列：

字段含义
type访问类型，const / ref / range 通常说明走索引，ALL 表示全表扫描
possible_keys优化器认为可能使用的索引
key实际使用的索引，为 NULL 表示没用索引
rows预估扫描行数，越小越好
Extra额外信息，如是否使用临时表、文件排序等

经验法则：写完复杂 SQL 先 EXPLAIN 看一眼，如果 type = ALL 且 rows 很大，就要考虑加索引或改写 SQL。

5. 常见索引失效场景#

失效写法问题推荐写法
WHERE name LIKE '%张%'前缀模糊，普通索引难命中LIKE '张%' 或使用全文索引
WHERE YEAR(create_time) = 2026对列使用函数WHERE create_time >= '2026-01-01' AND create_time < '2027-01-01'
WHERE age + 1 = 20对列做运算WHERE age = 19
WHERE stu_no = 12345字符串字段发生隐式类型转换WHERE stu_no = '12345'
WHERE a = ? OR b = ? 且 b 无索引OR 分支无法走索引给 b 建索引，或改写为 UNION
联合索引不满足最左前缀跳过最左列调整条件或索引列顺序

八、JDBC 基础#

JDBC 全称是 Java Database Connectivity，是 Java 操作数据库的标准 API。它位于 java.sql 包中，用来屏蔽不同数据库的底层差异。

JDBC 和驱动的关系：

  • JDBC 是一组标准接口。
  • 数据库驱动是接口的具体实现，由数据库厂商提供。
  • MySQL 驱动类为 com.mysql.cj.jdbc.Driver。
  • 应用调用 JDBC 接口，JDBC 通过驱动访问数据库。

Maven 引入 MySQL 驱动：

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.0.33</version>
</dependency>

九、JDBC 四大核心接口#

接口职责常用方法
DriverManager驱动管理器，负责获取连接getConnection(url, user, password)
Connection数据库连接，代表一次数据库会话createStatement()、prepareStatement(sql)、commit()、rollback()、close()
Statement / PreparedStatementSQL 执行器executeQuery()、executeUpdate()、execute()
ResultSet查询结果集next()、getInt()、getString()、getDouble()、getDate()

一次完整访问链路：

Connection conn = DriverManager.getConnection(url, user, password);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM t_student");


while (rs.next()) {
    String name = rs.getString("name");
}

十、Maven 项目结构建议#

标准 Java + JDBC 项目可以按分层组织：

student-jdbc/
├── pom.xml
├── src/
│   ├── main/
│   │   ├── java/
│   │   │   └── com/example/
│   │   │       ├── entity/Student.java
│   │   │       ├── dao/StudentDao.java
│   │   │       ├── util/JdbcUtil.java
│   │   │       └── Main.java
│   │   └── resources/
│   │       └── db.properties
│   └── test/
│       └── java/com/example/StudentDaoTest.java
└── target/

搭建步骤：

  1. 创建 Maven 工程。
  2. 在 pom.xml 中加入 mysql-connector-j 依赖。
  3. 建立 entity、dao、util 等包。
  4. 可选：用 db.properties 外置化 url、user、password，避免硬编码。

十一、第一个 JDBC 程序六步流程#

JDBC 入门流程可以记为：参数、驱动、连接、SQL、结果、释放。

String url = "jdbc:mysql://localhost:3306/student_manage?useSSL=false&serverTimezone=Asia/Shanghai";
String user = "root";
String password = "123456";


Connection conn = null;
Statement stmt = null;
ResultSet rs = null;


try {
    Class.forName("com.mysql.cj.jdbc.Driver");
    conn = DriverManager.getConnection(url, user, password);
    stmt = conn.createStatement();
    rs = stmt.executeQuery("SELECT * FROM t_student");


    while (rs.next()) {
        System.out.println(rs.getInt("id") + " " + rs.getString("name"));
    }
} catch (Exception e) {
    e.printStackTrace();
} finally {
    if (rs != null) {
        try { rs.close(); } catch (SQLException e) { e.printStackTrace(); }
    }
    if (stmt != null) {
        try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); }
    }
    if (conn != null) {
        try { conn.close(); } catch (SQLException e) { e.printStackTrace(); }
    }
}

释放资源必须放在 finally 中，并且按 ResultSet -> Statement -> Connection 倒序关闭。

十二、PreparedStatement 必须优先使用#

不要使用字符串拼接 SQL，因为它容易造成 SQL 注入。

危险写法：

String sql = "SELECT * FROM t_student WHERE name = '" + name + "'";

如果用户输入类似 ' OR '1'='1，可能导致条件恒成立，返回全部数据。

推荐写法：

String sql = "SELECT * FROM t_student WHERE name = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, name);
ResultSet rs = pstmt.executeQuery();

PreparedStatement 的优势：

  • 使用 ? 占位符和 setXxx 赋值。
  • 自动转义用户输入，防止 SQL 注入。
  • SQL 可预编译并缓存，批量操作性能更好。
  • 实际开发中应始终优先使用 PreparedStatement。

十三、封装 JDBC 工具类 JdbcUtil#

工具类的目标是抽取公共代码，减少重复。核心设计包括：

  • URL、用户名、密码集中维护。
  • static 代码块加载驱动，只执行一次。
  • getConnection() 统一获取连接。
  • close() 方法重载，查询场景关闭 3 个资源，增删改场景关闭 2 个资源。
public class JdbcUtil {
    private static final String URL = "jdbc:mysql://localhost:3306/student_manage?useSSL=false&serverTimezone=Asia/Shanghai";
    private static final String USER = "root";
    private static final String PASSWORD = "123456";


    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("驱动加载失败", e);
        }
    }


    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }


    public static void close(ResultSet rs, Statement stmt, Connection conn) {
        try {
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
            if (conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    public static void close(Statement stmt, Connection conn) {
        close(null, stmt, conn);
    }
}

封装后，每个 DAO 方法不用重复写加载驱动、获取连接和关闭资源的样板代码。

十四、为什么需要连接池#

没有连接池时，每次请求都新建数据库连接，会产生 TCP 握手、认证等开销，高并发下容易打满数据库连接数。如果忘记关闭连接，还会造成连接泄漏。

连接池的思想是：启动时预先创建一批连接，业务使用时从池中借，用完再归还。

优势：

  • 复用连接，减少创建连接开销。
  • 限制最大连接数，保护数据库。
  • 统一管理连接，降低泄漏风险。
  • 支持监控、限流、空闲回收。

常见参数：

参数含义建议
初始连接数启动时预建连接数量通常 5 到 10
最大连接数池中连接上限防止打垮数据库
空闲超时空闲多久后回收节省资源
获取超时借不到连接等待多久超时抛异常

十五、Druid 连接池接入#

Maven 依赖：

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.20</version>
</dependency>

resources/druid.properties 示例：

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/student_manage?useSSL=false&serverTimezone=Asia/Shanghai
username=root
password=123456
initialSize=5
minIdle=5
maxActive=20
maxWait=60000
validationQuery=SELECT 1
testWhileIdle=true

关键理解：连接池模式下调用 conn.close() 不是断开物理连接，而是把连接归还给池。

最大连接数不是越大越好。设置过大可能打垮数据库，普通项目一般 20 到 50 已经够用，生产环境应结合 QPS 和平均 SQL 耗时估算。

十六、CRUD 工具类封装总结#

从直接写 JDBC 到封装工具类，是从“能跑通”到“会复用”的过程。

BeforeAfter
每个方法都 Class.forNamestatic 块只加载一次
URL、用户名、密码散落各处配置集中维护，单点修改
close 三件套到处复制JdbcUtil.close() 一行调用
使用 Statement 拼接 SQL使用 PreparedStatement 防注入

三条设计原则：

  • PreparedStatement 优先：占位符 + setXxx，防止 SQL 注入。
  • 静态化 + 单点配置：工具方法全 static，调用简洁。
  • 资源释放统一封装：查询关闭 3 个资源，增删改关闭 2 个资源，始终倒序释放。

十七、StudentDao 五个核心方法#

DAO 层负责数据访问，常见核心方法包括：

方法作用JDBC 方法
insert(Student student)新增学生executeUpdate()
deleteById(int id)按 ID 删除executeUpdate()
update(Student student)修改学生信息executeUpdate()
findAll()查询全部学生executeQuery()，返回 List<Student>
findById(int id)按 ID 查询executeQuery()，返回 Student

findAll() 的核心是把 ResultSet 中的每一行映射成一个 Student 对象，再加入集合。

public List<Student> findAll() {
    List<Student> list = new ArrayList<>();
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;


    try {
        conn = JdbcUtil.getConnection();
        String sql = "SELECT id, name, age, gender, class_name, enroll_date FROM t_student";
        pstmt = conn.prepareStatement(sql);
        rs = pstmt.executeQuery();


        while (rs.next()) {
            Student stu = new Student();
            stu.setId(rs.getInt("id"));
            stu.setName(rs.getString("name"));
            stu.setAge(rs.getInt("age"));
            stu.setGender(rs.getString("gender"));
            stu.setClassName(rs.getString("class_name"));
            stu.setEnrollDate(rs.getDate("enroll_date"));
            list.add(stu);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        JdbcUtil.close(rs, pstmt, conn);
    }


    return list;
}

十八、复习主线#

学习顺序可以按这条线走：

  1. 先掌握 SQL 五大分类和字段类型选择。
  2. 用 student_manage 案例完成单表建模。
  3. 通过 t_student、t_subject、t_score 理解多对多关系。
  4. 学会 JOIN、子查询、聚合、排序分页和开窗函数。
  5. 用索引和 EXPLAIN 优化查询性能。
  6. 掌握 JDBC 四大接口和六步流程。
  7. 使用 PreparedStatement 替代 Statement。
  8. 封装 JdbcUtil，再进一步接入 Druid 连接池。
  9. 在 DAO 层实现完整 CRUD，把数据库操作组织成可复用代码。

这部分内容的核心目标不是背语法，而是形成完整链路：从 MySQL 建表、写 SQL，到 Java 通过 JDBC 操作数据库，再到工具类和连接池提升工程可维护性。

分享

如果这篇文章对你有帮助，欢迎分享给更多人！

MySQL 与 JDBC
https://wyz.sakura-v.cn/posts/mysql-and-jdbc/
作者
WYZ
发布于
2026-07-02
许可协议
CC BY-NC-SA 4.0

部分信息可能已经过时

React Router 路由入门教程
React + TypeScript 基础
相关文章 智能推荐
1
Spring 与 Spring Boot 核心笔记
后端 从 IoC、DI、配置注入、Bean 生命周期到 Spring Boot、AOP、事务与 Spring MVC，整理一篇适合入门和复习的 Spring 基础笔记。
2
Java基础与进阶
后端 从 JDK、JRE、JVM 到数据类型、运算符、流程控制与数组，整理一篇适合入门和复习的 Java 基础笔记。
3
React Router 路由入门教程
前端 从 SPA 为什么需要路由，到 React Router 的基础配置、动态路由、嵌套路由、编程式导航、404 页面、查询参数与数据加载，整理一篇适合入门和复习的路由基础笔记。
4
React + TypeScript 基础
前端 从 React 简介、项目结构、模块化到组件、状态与事件处理，整理一篇适合入门和复习的 React + TypeScript 基础笔记。
5
Web前端基础
前端 从 Web 与 HTTP、HTML、CSS 到 JavaScript 和 TypeScript，整理一篇适合入门与复习的 Web 前端基础笔记。
随机文章 随机推荐
1
Web前端基础
前端 2026-07-01
2
Ubuntu 多人协作权限管理最佳实践
运维 2026-06-06
3
React + TypeScript 基础
前端 2026-07-02
4
React Router 路由入门教程
前端 2026-07-03
5
为什么我花了这么多时间设计一个头像
随笔 2026-06-01
ICP备 湘ICP备2026022908号-1
© 2026 WYZ. All Rights Reserved. / RSS / Atom / Sitemap
Powered by Astro & Mizuki  Version 9.0

目录