MySQL 总结

核心说明：覆盖MySQL基础、SQL查询、MySQL高级、JDBC核心，重点标注面试高频考点，适配基础Java+MySQL开发岗位面试，简洁清晰、可直接背诵。

一、MySQL基础

1. 数据库基础概念

• 数据库（DB）：结构化数据的存储仓库（如MySQL、Oracle）。

• 数据库管理系统（DBMS）：管理数据库的软件，MySQL是常用关系型DBMS。

• SQL：操作数据库的标准语言，分DQL（查询）、DML（增删改）、DDL（定义）、DCL（控制）。

• 关系型数据库：以表为基础，遵循ACID（如MySQL）；非关系型（NoSQL）：键值对/文档存储（如Redis、MongoDB）。

2. MySQL安装与基础配置

• 安装：记住安装路径、root密码；Windows需配置环境变量（bin目录入PATH）。

• 常用CMD命令：

  • 启动/停止服务：net start/stop mysql

  • 登录：mysql -u 用户名 -p；退出：exit/quit

• 配置：my.ini（Windows）/my.cnf（Linux），可改端口（3306）、字符集（utf8mb4）。

3. 数据库与数据表操作（DDL）

（1）数据库操作

• 创建：CREATE DATABASE IF NOT EXISTS 数据库名 DEFAULT CHARSET utf8mb4;

• 查看：SHOW DATABASES; 使用：USE 数据库名;

• 删除：DROP DATABASE IF EXISTS 数据库名;（谨慎）

• 代码示例：

  -- 创建数据库
  
  CREATE DATABASE IF NOT EXISTS mysql_learn DEFAULT CHARSET utf8mb4;
  
  -- 使用数据库
  
  USE mysql_learn;
  
  -- 删除数据库（谨慎）
  
  DROP DATABASE IF EXISTS mysql_learn;

（2）数据表操作

• 创建：CREATE TABLE IF NOT EXISTS 表名(字段名 类型 约束) DEFAULT CHARSET utf8mb4;

• 查看结构：DESC 表名; 查看所有表：SHOW TABLES;

• 修改：ALTER TABLE 表名 ADD/MODIFY/CHANGE/DROP 字段名;（添加/修改/改字段名/删除）

• 删除：DROP TABLE IF EXISTS 表名;（谨慎）

• 代码示例：

  -- 创建学生表
  
  CREATE TABLE IF NOT EXISTS student(
  
  sid INT PRIMARY KEY AUTO_INCREMENT,
  
  sname VARCHAR(20) NOT NULL,
  
  sage TINYINT,
  
  ssex VARCHAR(2) DEFAULT '男'
  
  ) DEFAULT CHARSET utf8mb4;
  
  -- 查看表结构
  
  DESC student;
  
  -- 添加字段
  
  ALTER TABLE student ADD saddr VARCHAR(50);
  
  -- 删除表（谨慎）
  
  DROP TABLE IF EXISTS student;

  ​

4. MySQL数据类型

• 整数：INT（常用）、TINYINT（布尔/状态）、BIGINT（订单号）

• 字符串：VARCHAR(n)（可变，常用）、CHAR(n)（固定长度）、TEXT（长文本）

• 日期：DATETIME（常用）、TIMESTAMP（自动同步时间）、DATE（仅日期）

• 小数：DECIMAL(m,d)（精准，金额）、FLOAT/DOUBLE（非精准）

5. 数据表约束

• 主键（PRIMARY KEY）：唯一标识，非空，可自增（INT+AUTO_INCREMENT）

• 非空（NOT NULL）：字段必填；唯一（UNIQUE）：值不重复，可空

• 默认值（DEFAULT）：未填写时自动赋值

• 外键（FOREIGN KEY）：关联两表，保证参照完整性，字段类型与主表主键一致

6. DML语句（增删改）

• 插入：INSERT INTO 表名(字段) VALUES(值);（批量可多组括号）

• 修改：UPDATE 表名 SET 字段=值 WHERE 条件;（必加WHERE，否则全表修改）

• 删除：DELETE FROM 表名 WHERE 条件;（TRUNCATE清空全表，重置自增，不可回滚）

• 代码示例：

  -- 插入（单条+批量）
  
  INSERT INTO student(sname, sage) VALUES('张三', 20);
  
  INSERT INTO student(sname, sage) VALUES('李四', 21),('王五', 19);
  
  -- 修改（仅修改sid=1的学生）
  
  UPDATE student SET sage=22 WHERE sid=1;
  
  -- 删除（仅删除sid=3的学生）
  
  DELETE FROM student WHERE sid=3;
  
  -- 清空全表（重置自增）
  
  TRUNCATE TABLE student;
  

  ​

二、SQL核心查询（DQL）

1. 基础查询

• 语法：SELECT 字段 FROM 表名 WHERE 条件 ORDER BY 字段 ASC/DESC LIMIT 起始索引, 条数

• 关键字：WHERE（过滤）、ORDER BY（排序）、LIMIT（分页）

• 常用条件：BETWEEN...AND...、IN、LIKE（%任意字符）、IS NULL

• 代码示例：

  -- 基础查询
  
  SELECT sname, sage FROM student WHERE sage BETWEEN 18 AND 22;
  
  -- 模糊查询（姓名含"张"）
  
  SELECT * FROM student WHERE sname LIKE '%张%';
  
  -- 排序+分页（按年龄降序，第1页，10条/页）
  
  SELECT * FROM student ORDER BY sage DESC LIMIT 0,10;

  ​

2. 聚合与分组查询（GROUP BY + HAVING）

• 聚合函数：COUNT（统计）、SUM（求和）、AVG（平均）、MAX/MIN（最值）

• 分组：GROUP BY 字段，搭配聚合函数使用

• 过滤分组：HAVING（在GROUP BY后，可使用聚合函数；WHERE过滤行）

• 代码示例：

  -- 统计各性别学生人数，过滤人数>1的性别
  
  SELECT ssex, COUNT(sid) AS count FROM student GROUP BY ssex HAVING count > 1;
  
  -- 求学生平均年龄
  
  SELECT AVG(sage) AS avg_age FROM student;

3. 多表查询

• 内连接（INNER JOIN）：只返回匹配数据

• 左连接（LEFT JOIN）：返回左表全部，右表匹配数据（无匹配为NULL）

• 右连接（RIGHT JOIN）：返回右表全部，左表匹配数据

• 全连接：MySQL不直接支持，用UNION拼接左/右连接

• 代码示例：

  -- 左连接（查询所有学生及成绩，无成绩显示NULL）
  
  SELECT s.sname, sc.score FROM student s LEFT JOIN score sc ON s.sid = sc.sid;
  
  -- 内连接（只查询有成绩的学生）
  
  SELECT s.sname, sc.score FROM student s INNER JOIN score sc ON s.sid = sc.sid;

4. 子查询与结果合并

• 子查询：嵌套在其他SQL中，作为条件（搭配IN、EXISTS），复杂查询可替换为多表连接

• UNION（去重，低效）、UNION ALL（不去重，高效）：合并结果集，字段需一致

• 代码示例：

  -- 子查询（查询年龄>平均年龄的学生）
  
  SELECT sname FROM student WHERE sage > (SELECT AVG(sage) FROM student);
  
  -- UNION ALL（合并两个查询结果）
  
  SELECT sname FROM student WHERE ssex='男' UNION ALL SELECT sname FROM student WHERE sage>20;

5. 常用函数

• 字符串：CONCAT（拼接）、LENGTH（长度）、SUBSTRING（截取）

• 数值：ROUND（四舍五入）、CEIL/FLOOR（上下取整）

• 日期：NOW（当前时间）、DATE_FORMAT（格式化）、DATEDIFF（天数差）

• 流程：IF（简单判断）、CASE WHEN（多条件判断）

三、MySQL高级核心

1. 索引

• 定义：类似目录，加速查询，减少全表扫描；缺点：占空间，增删改变慢

• 分类：主键索引、唯一索引、普通索引、联合索引（遵循最左前缀原则）

• 创建/删除：CREATE [UNIQUE] INDEX 索引名 ON 表名(字段); DROP INDEX 索引名 ON 表名;

• 底层：B+树，有序平衡树，叶子节点存数据，查询时间复杂度O(logn)

• 代码示例：

  -- 创建普通索引（姓名字段）
  ​
  CREATE INDEX idx_sname ON student(sname);
  ​
  -- 创建唯一索引（手机号字段，假设存在sphone）
  ​
  CREATE UNIQUE INDEX idx_sphone ON student(sphone);
  ​
  -- 创建联合索引（姓名+年龄）
  ​
  CREATE INDEX idx_sname_sage ON student(sname, sage);
  ​
  -- 删除索引
  ​
  DROP INDEX idx_sname ON student;

2. 事务（ACID）

• 定义：不可分割的SQL操作，要么全成，要么全败（转账、下单场景）

• ACID：原子性（不可分割）、一致性（数据完整）、隔离性（互不干扰）、持久性（提交后永久）

• 操作：START TRANSACTION（开启）、COMMIT（提交）、ROLLBACK（回滚）

• 隔离级别：默认REPEATABLE READ（可重复读），解决脏读、不可重复读，MySQL用MVCC解决幻读

• 代码示例：

  -- 事务操作（转账场景）
  
  START TRANSACTION;
  
  -- 扣钱
  
  UPDATE account SET balance=balance-100 WHERE id=1;
  
  -- 加钱
  
  UPDATE account SET balance=balance+100 WHERE id=2;
  
  -- 提交（成功则生效）
  
  COMMIT;
  
  -- 若出错，执行回滚（恢复到开启前状态）
  
  -- ROLLBACK;

3. 视图与存储过程

• 视图：虚拟表，存储SQL，简化查询、隐藏敏感字段，仅用于查询

• 存储过程：预编译SQL集合，类似方法，提高效率、减少网络传输，可调用执行

• 代码示例：

  -- 1. 视图CREATE VIEW v_student AS SELECT sname, sage FROM student WHERE sage>18;-- 查询视图SELECT * FROM v_student;
  
  -- 2. 存储过程（无参数，查询所有学生）
  
  DELIMITER //
  
  CREATE PROCEDURE proc_get_all_student()
  
  BEGIN
  
  SELECT * FROM student;
  
  END //
  
  DELIMITER ;
  
  -- 调用存储过程
  
  CALL proc_get_all_student();

4. 其他高频

• 用户权限：CREATE USER、GRANT（授权）、REVOKE（撤销）、DROP USER

• 备份恢复：备份mysqldump -u 用户名 -p 库名 > 路径；恢复mysql -u 用户名 -p 库名 < 路径

• 规范：表名/字段小写+下划线，字段选最小合适类型，避免SELECT *，增删改加WHERE

四、JDBC核心知识

1. 基础定义

• JDBC：Java操作数据库的标准接口，依赖MySQL驱动（mysql-connector-java.jar）

2. 核心六步

1. 加载驱动（MySQL8.0+可省略）：Class.forName("com.mysql.cj.jdbc.Driver")

2. 获取连接：DriverManager.getConnection(url, user, password)

3. 获取预处理对象：conn.prepareStatement(sql)

4. 设置参数（占位符?）：setInt/setString等

5. 执行SQL：executeQuery（查询，返回ResultSet）、executeUpdate（增删改，返回受影响行数）

6. 关闭资源：ResultSet → PreparedStatement → Connection（先开后关）

3. 四大核心对象

• DriverManager：获取连接（getConnection）

• Connection：连接对象，管理事务（setAutoCommit、commit、rollback）

• PreparedStatement：预编译，防注入，执行SQL

• ResultSet：查询结果集，next()遍历，getXXX()获取字段值

4. 实战核心代码

查询、新增、修改、删除核心逻辑一致，差异在SQL语句和执行方法，事务控制需关闭自动提交，try-catch中提交/回滚。

核心代码示例：

import java.sql.*;

public class JDBCDemo {
    public static void main(String[] args) {
        // 1. 连接信息
        String url = "jdbc:mysql://localhost:3306/mysql_learn?useSSL=false&serverTimezone=UTC";
        String user = "root";
        String password = "你的密码";

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            // 2. 获取连接
            conn = DriverManager.getConnection(url, user, password);

            // 示例1：查询
            String sqlSelect = "SELECT sname, sage FROM student WHERE sid=?";
            pstmt = conn.prepareStatement(sqlSelect);
            pstmt.setInt(1, 1);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString("sname") + "," + rs.getInt("sage"));
            }

            // 示例2：新增
            String sqlInsert = "INSERT INTO student(sname, sage) VALUES(?, ?)";
            pstmt = conn.prepareStatement(sqlInsert);
            pstmt.setString(1, "赵六");
            pstmt.setInt(2, 23);
            pstmt.executeUpdate();

            // 示例3：事务控制
            conn.setAutoCommit(false);
            String sqlUpdate = "UPDATE student SET sage=24 WHERE sid=1";
            pstmt = conn.prepareStatement(sqlUpdate);
            pstmt.executeUpdate();
            conn.commit(); // 提交事务

        } catch (SQLException e) {
            try {
                if (conn != null) conn.rollback(); // 回滚事务
            } catch (SQLException ex) {ex.printStackTrace();}
            e.printStackTrace();
        } finally {
            // 3. 关闭资源
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {e.printStackTrace();}
        }
    }
}