【MySQL学习笔记】(下)

第三阶段：MySQL 进阶特性（28-36）

核心：数据库性能优化与设计规范

3.1事务概述

ACID 原则

• 原子性：操作不可分割

• 一致性：事务执行前后，数据始终合法

• 隔离性：多个事务同时运行时，互相不干扰

• 持久性：事务一旦提交，数据永久保存

隔离级别

• 脏读：一个事务读到了另一个事务未提交的数据

• 不可重复读：一个事务内，多次读取同一行数据的结果不同

• 虚读：一个事务内，多次查询的记录数量不一致（其他事务插入 / 删除导致）

3.2事务操作+实战

注：MySQL是默认开启事务

 — 方式1：手动开启事务（最常用）
 START TRANSACTION;
 ​
 — 方式2：关闭自动提交（等同于开启事务）
 SET autocommit = 0;

 UPDATE account SET money = money – 100 WHERE id=1;
 UPDATE account SET money = money + 100 WHERE id=2;

 COMMIT;   –成功: 提交保存
 ROLLBACK; –失败: 回滚恢复

4.恢复默认

 SET autocommit = 1; — 重新开启自动提交

拓展

 SAVEPOINT 保存点名 –设置一个事务的保存点
 ROLLBACK TO SAVEPOINT 保存点名 –回滚到保存点
 RELEASE SAVEPOINT 保存点名 –撤销保存点

实战：模拟转账

 SET autocommit=0 –关闭自动提交
 START TRANSACTION –开启事务
 UPDATE `account` SET money=money-1000 WHERE id=1
 UPDATE `account` SET money=money+1000 WHERE id=2
 COMMIT; –提交，持久化
 ROLLBACK;–回滚
 ​
 SET autocommit=1 –开启自动提交，关闭事务

3.3索引概述

主键索引就一个，唯一索引可多个

四种索引类型

• 主键索引 (PRIMARY KEY) 唯一标识，不可重复

• 唯一索引 (UNIQUE KEY) 字段中（比如手机号身份证号)数据不能重复

  • 当多个列都是唯一索引=联合唯一索引时，单个值可以重复，组合不能重复

• 普通索引(INDEX / KEY) 仅用于加速查询，没有任何约束。

• 全文索引 (FULLTEXT INDEX) 用于快速搜索长文本，比 LIKE '%关键词%' 快很多

注意事项

索引原理分析：MySQL索引背后的数据结构及算法原理 – 一刹流云散 – 博客园

3.4索引的使用

 SHOW INDEX FROM student –显示所有的索引信息
 ​
 –索引的三种创建方式
 FULLTEXT INDEX 索引名(字段名)   –创建表时直接加索引
 ​
 CREATE FULLTEXT INDEX 索引名 ON 表(字段名); –CREATE添加索引
 ​
 ALTER TABLE 表名 ADD FULLTEXT INDEX 字段名(索引名);   –ALTER添加索引
 ​
 EXPLAIN SELECT *FROM student; –EXPLAIN分析sql执行状况

3.5用户管理

可视化操作

SQL命令操作

MySQL 用户数据确实存在系统表里，但官方不让你直接 INSERT / UPDATE / DELETE！必须用专门的命令：CREATE USER、DROP USER、RENAME USER、GRANT、REVOKE

 –创建用户
 CREATE USER 用户名 IDENTIFIED BY '密码'
 ​
 –修改密码
 SET PASSWORD FOR 用户名='密码'
 ​
 –重命名
 RENAME USER 用户名 TO 新用户名
 ​
 –用户授予所有权限
 GRANT ALL PRIVILEGES ON *.* TO 用户名
 （默认不包括GRANT权限，不可以创建新用户）
 （*.*代表所有表）
 ​
 –查询用户/ROOT用户权限
 SHOW GRANTS FOR 用户名
 ​
 –撤销权限
 REVOKE ALL PRIVILEGES ON *.* FROM 用户名
 ​
 –删除用户
 DROP USER 用户名

3.6数据库备份

导出

1.直接拷贝物理文件–data文件夹

2.在Sqlyog可视化工具手动导出

3.使用命令行 mysqldump 导出

 #mysqldump -h主机 -u用户名 -p密码 数据库 表名1 表名2 >位置/文件名
 ​
 mysqldump -hlocalhost -uroot -p123456 school result >D:a.sql
 ​
 ​
 ​

命令行导入

 –登录
 –切换到数据库
 –导入文件
 ​
 >mysql -uroot -p123456
 ​
 >use 数据库名
 ​
 >source 位置/文件名

3.7规范数据库设计

当数据库比较复杂，需要设计

糟糕的数据库：

• 数据冗余，浪费空间

• 数据库插入和删除都会麻烦、使用物理外键导致异常报错

• 程序的性能差

良好的数据库：

• 节省内存空间

• 保证数据库的完整性

• 方便我们开发维护系统

软件开发时，关于数据库的设计

• 分析需求：分析业务和需要处理的数据库的需求

• 概要设计：设计E-R图

设计数据库的步骤（个人博客）

• 收集信息，分析需求

  • 用户表（用户登录注销，用户的个人信息，写博客，创建分类）

  • 分类表（文章分类，谁创建的） – 文章表（文章的信息）

  • 评论表

  • 友链表（友链信息）

  • 自定义表（系统信息，某个关键的字，或者一些主字段） key：value

  • 说说表（发表心情.. id…content…create_time）

• 标识实体（把需求落地到每个字段）

• 标识实体之间的关系 –

  • 写博客：user -> blog

  • 创建分类：user -> category

  • 关注：user -> user

  • 友链：links

  • 评论：user-user-blog

3.8三大范式

第一范式(1NF)：每一列属性不可分割

第二范式(2NF)：满足第一范式；且非主属性必须完全依赖于主键（联合主键），不能只依赖于主键的一部分

在上图所示的情况中，主键必须是 “订单号” 和 “产品号” 联合组成。

但是订单金额仅与 “订单号” 相关，与 “产品号” 无关，这样就不满足第二范式的要求，需分成两个表：

第三范式(3NF)：满足第二范式；且不存在传递依赖，即非主属性不能与非主属性之间有依赖关系，非主属性必须直接依赖于主属性，不能间接依赖主属性。（A -> B, B ->C, A -> C）

学号 姓名 性别 家庭人口 ——- 老师姓名——-老师性别 老师年龄

拆分成两张表即可

1学号 姓名 性别 家庭人口 老师姓名 2老师姓名 老师性别 老师年龄

注：规范性和性能的问题

• 关联查询的表不得超过三张表，以保障规范性和性能的均衡

• 有时故意给某些表增加冗余字段，可以从多表查询变为单表查询

第四阶段：Java 数据库编程（37-45）

核心：JDBC 核心技术与连接池

4.1JDBC环境搭建

因为市面上有很多种数据库（MySQL、Oracle、SQL Server…），每一种数据库的底层操作方式、驱动都不一样。

JDBC 的作用就是：提供一套统一的接口（标准）让 Java 用同一套代码就能连接所有数据库

Java 连接 MySQL 数据库的核心依赖：

• java.sql 和 javax.sql

  • 标准接口包（只定义了连接、执行 SQL、处理结果等接口和抽象类）

• mysql-connector-java

  • JDBC 驱动实现包（是 MySQL 官方提供的，用来实现 java.sql 里那些接口的具体代码。要选择与数据库版本一致的驱动包）

  • 官网（maven）：Maven Repository: mysql » mysql-connector-java

  • 官网（压缩包）：MySQL :: Download MySQL Connector/J (Archived Versions)

4.2第一个JDBC程序

1.创建一个普通项目

2.导入数据库驱动（新建lib文件夹；复制jar包；右键添加到库）

3.编写测试代码

 package com.xing;
 ​
 import java.sql.*;
 ​
 //第一个jdbc程序
 public class JdbcTest01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.固定方法，加载驱动
        Class.forName("com.mysql.jdbc.Driver");
 ​
        //2.用户信息和固定形式url(只换数据库名就可以)
        String url ="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "123456";
 ​
        //3.连接成功，Comection代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);
 ​
        //4.创建执行sql的对象
        Statement statement = connection.createStatement();
 ​
        //5.执行sql对象
        String sql="SELECT *FROM users";
        ResultSet resultSet = statement.executeQuery(sql);//执行查询函数，获取返回结果集
 ​
        while (resultSet.next()){
            System.out.println("id"+resultSet.getObject("id"));//根据列名获取列的值
            System.out.println("name"+resultSet.getObject("NAME"));
            System.out.println("password"+resultSet.getObject("PASSWORD"));
        }
 ​
        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
 }
 ​

步骤总结：

1、加载驱动

2、连接数据库 DriverManager

3、获得执行sql的对象 Statement

4、获得返回的结果集 （查询才有）

5、释放连接

4.3JDBC 对象

DriverManger

 Class.forName("com.mysql.jdbc.Driver"); //1.固定方法，加载驱动
 ​
 Connection connection = DriverManager.getConnection(url, username, password);
 //Comection代表数据库
 ​
 //数据库设置自动提交，事务提交，事务回滚
 connection.setAutoCommit();
 connection.commit();
 connection.rollback();

URL唯一定位符

 jdbc:mysql://主机地址:3306/数据库名?useUNicode=true&charterEncoding=utf8&useSSL=true";

Statement是执行SQL的对象（❌不安全，会出现SQL注入）

 –创建statement对象
 Statement statement = connection.createStatement();
 ​
 –该对象的执行方法
 statement.executeQuery(sql);//仅查询
 statement.execute(sql);//任何函数
 statement.executeUpdate(sql);//仅更新，插入，删除

ResultSet是查询的结果集

 –该对象获取指定数据类型
 resultSet.getObject() //不知道类型时使用
 resultSet.getString()
 ….
 resultSet.getInt()
 ​
 –遍历方法
 while (resultSet.next())//循环遍历，移动到下一个
 resultSet.beforeFirst()//移动到第一个
 resultSet.afterLast()//移动到最后一个
 resultSet.previous()//移动到上一个

4.4构建工具类Utils

由于每次 JDBC 操作的流程（加载驱动、获取连接、释放资源）都高度重复，因此抽取为工具类，简化开发。

1.把配置类信息放到文件中，实现解耦合 以后改数据库地址、账号密码不用改Java代码，直接改文件就行

配置文件名：db.properties放在 src 根目录下

 driver=com.mysql.cj.jdbc.Driver
 url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
 username=root
 password=123456

2.构建工具类：静态代码块加载驱动、获取连接、释放资源方法

 package com.xing.utils;
 import java.io.InputStream;
 import java.sql.*;
 import java.util.Properties;
 ​
 public class JdbcUtil {
    public static String driver;
    public static String url;
    public static String username;
    public static String password;
 ​
    static {
        try {
            //通过反射 + 类加载器 → 读取配置文件
            InputStream in = JdbcUtil.class.getClassLoader().getResourceAsStream("db.properties");
 ​
            //该输入流：Properties 就是专门用来读 .properties 配置文件的工具类，是 Java 自带的专门读取 key=value 配置文件的工具
            Properties properties = new Properties();
            properties.load(in);
 ​
            //用方法取出value值
            driver = properties.getProperty("driver");
            username = properties.getProperty("username");
            url = properties.getProperty("url");
            password = properties.getProperty("password");
 ​
            //驱动只加载一次
            Class.forName(driver);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
 ​
    //获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }
 ​
    //释放连接资源
    public static void release(Connection connection, Statement statement, ResultSet resultSet){
        if(resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if(statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if(connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
 }
 ​

3.测试类：使用工具类完成插入操作

 package com.xing;
 import com.xing.utils.JdbcUtil;
 import java.sql.Connection;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 ​
 public class UseUtils {
    public static void main(String[] args) {
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
        // 1. 从工具类获取连接
            connection = JdbcUtil.getConnection();
              // 2. 创建执行SQL的对象
            statement = connection.createStatement();
 
  // 3. 编写SQL
            String sql="INSERT INTO users VALUES(5,'丽丽','123456','dawd123@qq.com','1893-9-21'),\\n" +
                    "(6,'丽丽12','123456','dawd123@qq.com','1893-9-21')";
             
            // 4. 执行增删改操作
            int i = statement.executeUpdate(sql);
 ​
            // 5. 根据影响行数判断是否成功
            if(i>0){
                System.out.println("插入成功");
            }
 ​
 ​
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
       
        // 6. 最终一定要释放资源
            JdbcUtil.release(connection,statement,resultSet);//释放连接
        }
    }
 }

4.5PreparedStatement

PreparedStatement是Statement的子类，继承并改善了Statement的SQL 注入安全问题，属于预编译执行对象。

4.6安全与实战

1.SQL 注入问题

当使用statement查询时，需要用到查询语句与用户输入的拼接，此时攻击者通过在输入字段或请求中注入恶意的 SQL 语句，操控数据库执行非预期的操作，会导致数据泄露，称为SQL 注入

 package com.xing;
 ​
 import com.xing.utils.JdbcUtil;
 import java.sql.Connection;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 ​
 public class Login {
    public static void main(String[] args) {
        // 🔥 这是 SQL 注入的万能密码！
        login("' OR '1'='1", "123456");
    }
 ​
    //登陆业务
    public static void login(String username,String password){
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
            connection = JdbcUtil.getConnection();
            statement = connection.createStatement();
 ​
            String sql="SELECT * FROM users WHERE `NAME`='"+username+"' AND `PASSWORD`='"+password+"'";
 ​
            // 打印真实SQL，你就能看到注入效果！
            System.out.println("真实SQL：" + sql);
 ​
            resultSet=statement.executeQuery(sql);
 ​
            System.out.println("===== 登录成功，查到的数据 =====");
            while (resultSet.next()){
                System.out.println("用户名：" + resultSet.getString("NAME"));
                System.out.println("密码：" + resultSet.getString("password"));
                System.out.println("—————-");
            }
 ​
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.release(connection,statement,resultSet);
        }
    }
 }

2.IDEA 连接数据库

Database Navigator 是一个用于 IntelliJ IDEA 和相关产品的数据库开发和维护的插件。

安装 Database Navigator 插件有两种方法：

使用 Database Navigator

安装成功后，可以在 IDEA 的菜单栏找到 DB Navigator 菜单项。在侧边栏中也会出现 DB Browser。

以 MySQL 为例，以下是连接数据库的步骤：

连接成功后，可以查看数据库及表、字段等信息，并进行数据编辑和 SQL 语句的执行

3.Java实现事务

 package com.xing;
 ​
 import com.xing.utils.JdbcUtil;
 ​
 import java.sql.Connection;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 ​
 //测试事务 //修改了配置文件为shop数据库
 public class TransactionTest {
    public static void main(String[] args) {
        Connection connection=null;
        PreparedStatement ps=null;
        ResultSet resultSet=null;
 ​
        try {
            connection = JdbcUtil.getConnection();
 ​
            //关闭自动提交==开启事务
            connection.setAutoCommit(false);
 ​
            String sql1="UPDATE `account` set `money`=`money`-100 where id=1";
            ps=connection.prepareStatement(sql1);
            ps.executeUpdate();
 ​
 //           System.out.println(1 % 0);//异常判断事务是否回滚
 ​
 ​
            String sql2="UPDATE `account` set `money`=`money`+100 where id=2";
            ps=connection.prepareStatement(sql2);
            ps.executeUpdate();
 ​
            connection.commit();//业务完毕提交事务
 ​
 ​
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);//失败则回滚
            }
            throw new RuntimeException(e);
        }finally {
            JdbcUtil.release(connection,ps, resultSet);
        }
    }
 }
 ​

4.7连接池技术

1.连接池概述

由于数据库的连接释放开销很大，非常浪费资源

池化技术：

• 提前创建好一批连接，放在 “池子” 中

• 所有用户都从池子里拿连接

• 使用完归还连接，不关闭

• 其他人可以重复使用

连接池的核心参数：

• 常用连接数：12 //一直保存在池子里、不会被释放的连接数量

• 最小连接数：12

• 最大连接数：122 //业务承载上限，超过这个数，新请求会等待

• 等待超时时间：100ms //超时就报错，避免无限等待

编写连接池，实现一个接口 DataSource

开源数据源实现

• DBCP

• C3P0

• Druid：阿里巴巴

使用了这些数据库连接池之后，我们在项目开发中就不需要编写连接数据库的代码了！

2.DBCP实战

参考本站：JDBC之DBCP连接池下载，配置，使用-CSDN博客

下载以下三个jar包

commons-dbcp2-2.11.0.jar

commons-pool2-2.12.0.jar

commons-logging-1.3.0.jar

配置文件dbcpconfig.properties

 DriverClassName=com.mysql.cj.jdbc.Driver
 url=jdbc:mysql://localhost:3306/shop?useUnicode=true&characterEncoding=utf8&useSSL=true
 username=root
 password=123456
 ​
 #初始化数据池拥有的连接
 initialSize=10
 ​
 #最大连接
 maxActive=50
 ​
 #最小空闲连接
 minIdle=5
 ​
 #最大等待时间，超过这个时间等待队列中的连接就会失效
 maxWait=10000
 ​
 ​
 #设置是否自动提交,默认为true
 defaultAutoCommit=true
 ​
 #是否为只读 默认为false
 defaultReadOnly=false
 ​
 ​
 #从池中取出连接时完成校验 ，验证不通过销毁这个connection，默认为true，
 testOnBorrow=true
 ​
 #放入池中时完成校验，默认我fasle
 testOnReturn=false
 ​
 #校验语句，必须是查询语句，至少查询一列，设置了它onBorrow才会生效
 validationQuery=select 1
 ​
 #校验查询时长，如果超过，认为校验失败
 validationQueryTimeout=1
 ​
 #清除一个连接时是否需要校验
 testWhileIdle=false
 ​
 #DBCP默认有个回收器Eviction，这个为设置他的回收时间周期
 timeBetweenEvictionRunsMillis=30000
 ​
 #Eviction在运行时一次处理几个连接
 numTestsPerEvictionRun=3
 ​
 #是否缓存PreparedStatements
 poolPreparedStatements=true
 ​
 #缓存PreparedStatements的最大个数
 maxOpenPreparedStatements=50
 ​

构建DBCP的工具类

 package com.xing.utils;
 ​
 import org.apache.commons.dbcp2.BasicDataSource;
 import org.apache.commons.dbcp2.BasicDataSourceFactory;
 ​
 import javax.sql.DataSource;
 import java.io.InputStream;
 import java.sql.*;
 import java.util.Properties;
 ​
 public class JdbcUtil_DBCP {
    private static DataSource dataSource=null;
    static {
        try {
            //通过反射 + 类加载器 → 读取配置文件
            InputStream in = JdbcUtil.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
 ​
            //该输入流：Properties 就是专门用来读 .properties 配置文件的工具类，是 Java 自带的专门读取 key=value 配置文件的工具
            Properties properties = new Properties();
            properties.load(in);
 ​
            //创建数据源 工厂模式
            dataSource = BasicDataSourceFactory.createDataSource(properties);
 ​
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
 ​
    //获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();//从数据源获取连接
    }
 ​
    //释放连接资源
    public static void release(Connection connection, Statement statement, ResultSet resultSet){
        if(resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if(statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if(connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
 ​
    }
 ​
 ​
 ​
 }

测试代码时

 connection = JdbcUtil_DBCP.getConnection();//仅获取连接修改了

2.C3P0实战

参考本站:JDBC之C3P0连接池下载，配置，使用_c3p0jar包下载-CSDN博客

下载c3p0-bin/c3p0-0.9.5.5/c3p0-0.9.5.5.bin.zip，网址如下

c3p0:JDBC DataSources/Resource Pools download | SourceForge.net

解压后在lib目录中找到 c3p0-0.9.5.5.jar和mchange-commons-java-0.2.19.jar包

配置文件

 <?xml version="1.0" encoding="UTF-8"?>
 <c3p0-config>
 ​
    <!– 默认配置（自动读取，最常用） –>
    <default-config>
        <!–
    C3P0的缺省(默认)配置。
    如果在代码中"ComboPooledDataSource ds = new ComboPooledDataSource();"这样写就表示使用的是C3P0的缺省(默认)配置
    –>
        <!– 数据库连接信息 –>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/shop?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true</property>
        <property name="user">root</property>
        <property name="password">123456</property>
 ​
        <!– 连接池核心参数 –>
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
 ​
        <!– 超时控制（建议加上） –>
        <property name="checkoutTimeout">3000</property>
        <property name="maxIdleTime">300</property>
    </default-config>
 ​
 ​
    <!– 命名配置：可配置多套数据源（如 MySQL、Oracle、test库、prod库） –>
    <named-config name="MySQL">
        <!–
    C3P0的命名配置。
    如果在代码中"ComboPooledDataSource ds = new ComboPooledDataSource("MySQL");"这样写就表示使用的是name是MySQL的命名配置
    –>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/shop?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true</property>
        <property name="user">root</property>
        <property name="password">123456</property>
 ​
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
 ​
        <property name="checkoutTimeout">3000</property>
        <property name="maxIdleTime">300</property>
    </named-config>
 ​
 </c3p0-config>

工具类只改static处

 static {
    try {
        //xml不用读取
        //创建数据源 工厂模式
        dataSource = new ComboPooledDataSource();//默认配置
         
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
 }

测试代码时

 connection = JdbcUtil_C3P0.getConnection();//仅获取连接修改了

4.总结

无论使用什么数据源，本质还是一样的，DataSource接口不会变，方法就不会变