49_MySQL基础与JDBC操作

MySQL基础与JDBC操作

前言

数据库是软件开发中最基础也是最核心的技能之一。无论你学了多少框架，最终数据都要落地到数据库中。本文将分为两大部分：前半部分讲MySQL基础，包括建库建表和SQL语句；后半部分讲JDBC操作，通过纯Java代码连接MySQL并执行查询。

学好SQL和JDBC的价值：即使现在大部分项目使用MyBatis或JPA，SQL知识仍然是必须掌握的——你看似在使用ORM框架，但出了问题还是得看生成的SQL对不对、索引是否生效、查询为什么慢。而JDBC是所有ORM框架的底层基础，理解JDBC后你才能真正理解MyBatis的SqlSession在做什么、Hibernate的Session是怎么管理事务的。面试中，手写SQL（多表JOIN、分组统计）和JDBC执行流程是经常被考察的内容。

打好数据库基础，后续学习MyBatis、JPA等ORM框架才能举重若轻。

一、MySQL 安装与连接

1.1 下载安装

MySQL是目前最流行的开源关系型数据库，可以从官网 https://dev.mysql.com/downloads/mysql/ 下载安装。Windows用户推荐下载MSI安装包，安装时选择 MySQL Server 组件，设置root密码并记住。

1.2 命令行连接

# 连接本地MySQL
mysql -u root -p

# 连接远程MySQL
mysql -h 192.168.1.100 -P 3306 -u root -p

1.3 可视化工具推荐

• Navicat：功能强大的商业工具（收费）
• DBeaver：开源免费的数据库管理工具
• MySQL Workbench：MySQL官方工具
• IDEA Database：IDEA内置数据库工具（推荐）

在IDEA右侧边栏打开 Database 面板，点击 + → Data Source → MySQL，填写连接信息即可。

二、数据库与表操作

2.1 数据库操作

-- 创建数据库
CREATE DATABASE mydb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 查看所有数据库
SHOW DATABASES;

-- 使用数据库
USE mydb;

-- 删除数据库（危险操作）
DROP DATABASE mydb;

charset选择：utf8mb4 支持emoji表情，推荐使用。MySQL的 utf8 实际是阉割版，不推荐。

utf8和utf8mb4的坑：MySQL的utf8编码最多只支持3个字节的字符，而emoji表情（如😀）需要4个字节存储。如果你用utf8建表，插入emoji时会报错Incorrect string value。utf8mb4才是真正的UTF-8完整实现。在新项目中，一律使用utf8mb4，避免后期数据迁移的麻烦。

2.2 数据类型

2.3 创建表

-- 学生表
CREATE TABLE t_student (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '学生ID',
    name VARCHAR(50) NOT NULL COMMENT '姓名',
    gender TINYINT DEFAULT 0 COMMENT '性别: 0未知 1男 2女',
    birthday DATE COMMENT '生日',
    score DECIMAL(5,2) COMMENT '分数',
    class_id BIGINT COMMENT '班级ID',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    is_deleted TINYINT DEFAULT 0 COMMENT '逻辑删除: 0未删除 1已删除',
    INDEX idx_class_id (class_id),
    INDEX idx_name (name)
) COMMENT '学生表';

-- 班级表
CREATE TABLE t_class (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '班级ID',
    name VARCHAR(50) NOT NULL COMMENT '班级名称',
    grade VARCHAR(20) COMMENT '年级'
) COMMENT '班级表';

2.4 表结构操作

-- 查看表结构
DESC t_student;

-- 添加字段
ALTER TABLE t_student ADD COLUMN phone VARCHAR(20) COMMENT '手机号';

-- 修改字段
ALTER TABLE t_student MODIFY COLUMN score DECIMAL(5,2) DEFAULT 0;

-- 删除字段
ALTER TABLE t_student DROP COLUMN phone;

-- 添加索引
ALTER TABLE t_student ADD INDEX idx_score (score);

-- 删除表
DROP TABLE IF EXISTS t_student;

三、核心SQL操作（CRUD）

3.1 INSERT 插入数据

-- 单条插入
INSERT INTO t_student (name, gender, birthday, score, class_id) 
VALUES ('张三', 1, '2000-01-15', 89.5, 1);

-- 批量插入
INSERT INTO t_student (name, gender, birthday, score, class_id) VALUES
('李四', 2, '1999-06-20', 92.0, 1),
('王五', 1, '2000-03-10', 78.5, 2),
('赵六', 1, '1998-11-05', 85.0, 2);

-- 通过子查询插入
INSERT INTO t_student (name, score, class_id)
SELECT name, score, class_id FROM t_student_backup WHERE score > 80;

3.2 SELECT 查询数据

-- 基本查询
SELECT * FROM t_student;
SELECT name, score FROM t_student;

-- 条件查询（WHERE）
SELECT * FROM t_student WHERE score >= 80 AND gender = 1;
SELECT * FROM t_student WHERE class_id IN (1, 2);
SELECT * FROM t_student WHERE name LIKE '张%';       -- 模糊查询
SELECT * FROM t_student WHERE birthday BETWEEN '2000-01-01' AND '2000-12-31';

-- 排序（ORDER BY）
SELECT * FROM t_student ORDER BY score DESC;
SELECT * FROM t_student ORDER BY class_id ASC, score DESC;

-- 分页（LIMIT）
SELECT * FROM t_student LIMIT 10;                -- 前10条
SELECT * FROM t_student LIMIT 10, 5;             -- 跳过10条取5条
SELECT * FROM t_student LIMIT 5 OFFSET 10;       -- 同上（标准写法）

-- 聚合函数
SELECT COUNT(*) FROM t_student;
SELECT AVG(score) FROM t_student;
SELECT MAX(score), MIN(score) FROM t_student;
SELECT SUM(score) FROM t_student;

-- 分组（GROUP BY）
SELECT class_id, COUNT(*) AS num, AVG(score) AS avg_score
FROM t_student
GROUP BY class_id
HAVING AVG(score) > 80;   -- SQL对分组后的结果进行过滤

-- 联表查询
SELECT s.name, s.score, c.name AS class_name
FROM t_student s
LEFT JOIN t_class c ON s.class_id = c.id
WHERE s.score >= 80;

JOIN类型对比：

LEFT JOIN的常见误区：很多人在LEFT JOIN之后加了WHERE条件过滤右表，结果把LEFT JOIN变成了INNER JOIN的效果。例如LEFT JOIN t_class c ON s.class_id = c.id WHERE c.name = '一班'——如果c.name为NULL（没有匹配到班级），WHERE条件会将其过滤掉，此时LEFT JOIN退化为INNER JOIN。正确的做法是将条件放在ON子句中：LEFT JOIN t_class c ON s.class_id = c.id AND c.name = '一班'。

3.3 UPDATE 更新数据

-- 更新单条记录
UPDATE t_student SET score = 95 WHERE id = 1;

-- 批量更新
UPDATE t_student SET class_id = 3 WHERE score < 60;

-- 更新多字段
UPDATE t_student SET score = score + 5, update_time = NOW() WHERE class_id = 1;

重要：执行UPDATE/DELETE时必须带WHERE条件！在生产环境强烈建议先写SELECT确认要修改的数据。

3.4 DELETE 删除数据

-- 物理删除
DELETE FROM t_student WHERE id = 1;

-- 逻辑删除（推荐）
UPDATE t_student SET is_deleted = 1 WHERE id = 1;

-- 截断表（清空所有数据，不可回滚）
TRUNCATE TABLE t_student;

四、JDBC操作

JDBC（Java Database Connectivity） 是Java连接数据库的标准API。虽然现在开发中很少直接使用JDBC，但理解它是学习ORM框架的基础。

为什么要学JDBC？ 你可能觉得"反正都是用MyBatis，学JDBC干嘛"。但当你遇到以下场景时，JDBC知识就派上用场了：1）排查MyBatis执行SQL的性能问题，你需要理解Connection、Statement、ResultSet的底层开销；2）自定义数据库连接池或数据源路由；3）执行一些ORM框架不支持的DDL操作；4）理解事务的底层实现（JDBC的autoCommit/commit/rollback）。而且JDBC的六步标准流程（加载驱动→获取连接→创建Statement→执行查询→处理结果→释放资源）是面试中的高频考点。

4.1 JDBC连接步骤

import java.sql.*;

public class JdbcDemo {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        
        try {
            // 1. 加载驱动（新版JDBC可省略）
            Class.forName("com.mysql.cj.jdbc.Driver");
            
            // 2. 获取连接
            String url = "jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=Asia/Shanghai";
            String user = "root";
            String password = "123456";
            conn = DriverManager.getConnection(url, user, password);
            
            // 3. 创建Statement
            stmt = conn.createStatement();
            
            // 4. 执行查询
            rs = stmt.executeQuery("SELECT id, name, score FROM t_student WHERE score > 80");
            
            // 5. 处理结果集
            while (rs.next()) {
                long id = rs.getLong("id");
                String name = rs.getString("name");
                double score = rs.getDouble("score");
                System.out.printf("ID: %d, 姓名: %s, 分数: %.1f%n", id, name, score);
            }
        } catch (ClassNotFoundException e) {
            System.out.println("驱动类未找到: " + e.getMessage());
        } catch (SQLException e) {
            System.out.println("数据库操作异常: " + e.getMessage());
        } finally {
            // 6. 释放资源（倒序关闭）
            try { if (rs != null) rs.close(); } catch (SQLException e) {}
            try { if (stmt != null) stmt.close(); } catch (SQLException e) {}
            try { if (conn != null) conn.close(); } catch (SQLException e) {}
        }
    }
}

JDBC连接URL参数详解：常用的连接参数包括：

• useSSL=false：开发环境下关闭SSL连接（生产环境建议配置SSL证书后开启）
• serverTimezone=Asia/Shanghai：指定时区，避免时间存储和读取时的时区偏差（这是新手常遇到的问题——数据库时间和Java时间差了8小时）
• characterEncoding=utf8mb4：指定字符编码，与数据库的charset保持一致
• rewriteBatchedStatements=true：批量插入时需要，开启后批量性能大幅提升
• allowPublicKeyRetrieval=true：MySQL 8.0+使用caching_sha2_password认证时需要

4.2 JDBC工具类

把重复的连接和资源释放代码封装成工具类：

import java.sql.*;
import java.util.Properties;

public class JdbcUtils {
    private static final String DRIVER;
    private static final String URL;
    private static final String USER;
    private static final String PASSWORD;
    
    static {
        try {
            Properties props = new Properties();
            props.load(JdbcUtils.class.getClassLoader()
                .getResourceAsStream("db.properties"));
            DRIVER = props.getProperty("jdbc.driver");
            URL = props.getProperty("jdbc.url");
            USER = props.getProperty("jdbc.user");
            PASSWORD = props.getProperty("jdbc.password");
            Class.forName(DRIVER);
        } catch (Exception e) {
            throw new RuntimeException("数据库配置加载失败", e);
        }
    }
    
    // 获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }
    
    // 释放资源
    public static void close(Connection conn, Statement stmt, ResultSet rs) {
        try { if (rs != null) rs.close(); } catch (SQLException e) {}
        try { if (stmt != null) stmt.close(); } catch (SQLException e) {}
        try { if (conn != null) conn.close(); } catch (SQLException e) {}
    }
}

配置文件 db.properties：

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=Asia/Shanghai
jdbc.user=root
jdbc.password=123456

4.3 PreparedStatement（防SQL注入）

public class PreparedStatementDemo {
    
    // 使用PreparedStatement查询（防止SQL注入）
    public Student findByName(String name) {
        String sql = "SELECT id, name, score FROM t_student WHERE name = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            conn = JdbcUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, name);  // 设置参数，索引从1开始
            
            rs = pstmt.executeQuery();
            if (rs.next()) {
                Student student = new Student();
                student.setId(rs.getLong("id"));
                student.setName(rs.getString("name"));
                student.setScore(rs.getDouble("score"));
                return student;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn, pstmt, rs);
        }
        return null;
    }
    
    // 使用PreparedStatement插入
    public int insert(String name, double score, Long classId) {
        String sql = "INSERT INTO t_student (name, score, class_id) VALUES (?, ?, ?)";
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = JdbcUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, name);
            pstmt.setDouble(2, score);
            pstmt.setLong(3, classId);
            
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn, pstmt, null);
        }
        return 0;
    }
    
    // 批量插入
    public void batchInsert(List<Student> students) {
        String sql = "INSERT INTO t_student (name, score) VALUES (?, ?)";
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = JdbcUtils.getConnection();
            conn.setAutoCommit(false);  // 关闭自动提交
            pstmt = conn.prepareStatement(sql);
            
            for (Student s : students) {
                pstmt.setString(1, s.getName());
                pstmt.setDouble(2, s.getScore());
                pstmt.addBatch();  // 加入批处理
            }
            
            pstmt.executeBatch();   // 执行批量
            conn.commit();          // 提交事务
        } catch (SQLException e) {
            try { if (conn != null) conn.rollback(); } catch (SQLException ex) {}
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn, pstmt, null);
        }
    }
}

4.4 Statement vs PreparedStatement

SQL注入是什么？ 假设用户输入了一个用户名' OR '1'='1，使用Statement拼接的SQL会变成：SELECT * FROM t_user WHERE username = '' OR '1'='1'。'1'='1'永远为真，于是这个SQL会返回所有用户数据——这就是SQL注入攻击。PreparedStatement通过预编译将参数值作为数据而非SQL的一部分处理，从根本上避免了注入风险。

强烈建议：永远使用PreparedStatement代替Statement，防止SQL注入是最基本的安全要求。

4.5 事务管理

public void transferMoney(Long fromId, Long toId, double amount) {
    Connection conn = null;
    PreparedStatement pstmt1 = null;
    PreparedStatement pstmt2 = null;
    
    try {
        conn = JdbcUtils.getConnection();
        conn.setAutoCommit(false);           // 开启事务
        
        // 扣款
        pstmt1 = conn.prepareStatement(
            "UPDATE t_account SET balance = balance - ? WHERE id = ? AND balance >= ?");
        pstmt1.setDouble(1, amount);
        pstmt1.setLong(2, fromId);
        pstmt1.setDouble(3, amount);
        int affected1 = pstmt1.executeUpdate();
        
        // 收款
        pstmt2 = conn.prepareStatement(
            "UPDATE t_account SET balance = balance + ? WHERE id = ?");
        pstmt2.setDouble(1, amount);
        pstmt2.setLong(2, toId);
        int affected2 = pstmt2.executeUpdate();
        
        if (affected1 > 0 && affected2 > 0) {
            conn.commit();   // 提交
            System.out.println("转账成功");
        } else {
            conn.rollback(); // 回滚
            System.out.println("转账失败");
        }
    } catch (SQLException e) {
        try { if (conn != null) conn.rollback(); } catch (SQLException ex) {}
        e.printStackTrace();
    } finally {
        JdbcUtils.close(null, pstmt1, null);
        JdbcUtils.close(null, pstmt2, null);
        JdbcUtils.close(conn, null, null);
    }
}

总结

本文从MySQL建库建表讲起，覆盖了SELECT/INSERT/UPDATE/DELETE核心SQL操作，然后深入到JDBC的连接、查询、PreparedStatement和事务管理。掌握这些基础后，你会更深刻地理解MyBatis、Hibernate等框架在底层是如何与数据库交互的。

两个必须牢记的铁律：

1. SQL关键字大写增加可读性——这不是规矩而是习惯，SELECT FROM WHERE JOIN大写，表名字段名小写，让你的SQL一眼就能看出结构
2. 永远使用PreparedStatement防止注入——这是安全底线，任何拼接用户输入的SQL都是定时炸弹

面试高频考点：1）JOIN类型及区别（INNER/LEFT/RIGHT）；2）GROUP BY + HAVING vs WHERE的区别；3）JDBC六步标准流程；4）Statement vs PreparedStatement（SQL注入原理和防护）；5）数据库事务的ACID特性和JDBC事务管理方式。

✅ 亮点总结

• MySQL DDL：建库建表、数据类型选择、索引创建、表结构修改（ALTER）
• CRUD四大操作：SELECT条件查询/排序/分页、INSERT批量插入、UPDATE多字段、DELETE物理与逻辑删除
• 聚合函数与分组：COUNT/AVG/MAX/MIN + GROUP BY + HAVING 完成统计查询
• JDBC六步标准流程：加载驱动→获取连接→创建Statement→执行查询→处理结果→释放资源
• PreparedStatement防注入：预编译SQL + 参数占位符，配合批处理和事务管理

适用场景

• 新项目的数据库表结构设计，包括字段类型、索引和注释规范
• 数据分析场景的SQL查询编写，多表JOIN和聚合统计
• 排查ORM框架（MyBatis/Hibernate）生成的SQL性能问题，理解底层执行逻辑

扩展方向

• 学习数据库索引优化：B+Tree原理、覆盖索引、最左前缀法则，配合EXPLAIN分析执行计划
• 了解连接池（HikariCP/Druid）的工作原理、核心参数配置和监控
• 推荐阅读下一篇文章：Java面试常见基础问题汇总，助力求职面试