MySQL数据库（三）

事务

什么是事务

事务原则：ACID原则原子性，一致性，隔离性，持久性 (脏读，幻读...)

参考博客：https://blog.csdn.net/dengjili/article/details/82468576
原子性
要么都成功，要么都失败
一致性
事务前后数据完整性要一直，1000
持久性
事务一旦提交，被持久化到数据库中
隔离性
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

隔离所导致的一些问题

脏读
指一个事务读取了另外一个事务未提交的数据
不可重复读
在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
幻读
是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。

执行事务

-- MySQL默认开启事务自动提交
SET autocommit = 0 -- 关闭
SET autocommit = 1 -- 默认开启

-- 手动处理事务
SET autocommit = 0 -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从此之后sql都在同一个事务内

INSERT xx
INSERT xx

-- 提交：持久化（成功）
COMMIT
-- 回滚：回到原来的样子（失败）
ROLLBACK

-- 事务结束
SET autocommit = 1 -- 开启自动提交

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

模拟事务

-- 模拟事务：转账
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION; -- 开启一个事务

UPDATE `account` SET money = money-500 WHERE `name` = 'A'; -- A少500
UPDATE `account` SET money = money+500 WHERE `name` = 'B'; -- A多500

COMMIT; -- 提交事务
ROLLBACK; -- 回滚

SET autocommit = 1; -- 恢复默认值

索引

索引是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构

索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

主键索引 (PRIMARY KEY)
- 唯一的标识，主键不可重复，只能有一个主键
唯一索引 (UNIQUE KEY)
- 避免重复的列出现，可以重复，多个列都可以标识为唯一索引
常规索引 (KEY/INDEX)
- 默认的，index，key关键字设置
全文索引 (FullText)
- 在特定的数据库引擎中才有
- 快速定位数据

基础语法

-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后，增加索引

-- 显示所有的索引信息
SHOW INDEX FROM `student`

-- 增加一个索引
ALTER TABLE `student` ADD FULLTEXT INDEX `studentname`(`studentname`)

-- 分析sql执行状况
EXPLAIN SELECT * FROM `student` -- 非全文索引
EXPLAIN SELECT * FROM `student` WHERE MATCH(`studentname`) AGAINST('刘')

测试索引

-- 插入100万数据.
DELIMITER $$ -- 写函数之前必须要写，标志
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	
	WHILE i<num DO
		INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`)VALUES(CONCAT('用户',i),'19224305@qq.com','123456789',FLOOR(RAND()*2));
		SET i=i+1;
	END WHILE;
	RETURN i;
END;

DROP FUNCTION mock_data

SELECT mock_data(); -- 执行此函数 生成一百万条数据

100万条数据测试索引

-- 创建索引
-- CREATE 索引名 on 表(字段)
CREATE INDEX id_app_user_name ON `app_user`(`name`)

SELECT * FROM `app_user` WHERE `name` = '用户9999'

索引原则

索引不是越多越好
不要对进程变动数据加索引
小数据量的表不需要加所以
索引一般加载常用来查询的字段上

参考博客：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

用户管理

SQLyog可视化管理

点击上方小人进行管理，方便快捷

SQL命令操作

-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER kuangshen IDENTIFIED BY '123456'

-- 修改密码(当前用户)
SET PASSWORD = PASSWORD('123456')

-- 修改密码(指定用户)
SET PASSWORD FOR kuangshen = PASSWORD('111')

-- 重命名
RENAME USER kuangshen TO logan

-- 用户授权 ALL PRIVILEGES 全部的权限 ON 库.表 TO 用户
GRANT ALL PRIVILEGES ON *.* TO logan

MySQL备份

MySQL数据库备份方式

直接拷贝物理文件
在SQLyog可视化工具中到处
- 在想要导出的表或库中，右键选择备份或导出
命令行导出，使用命令mysqldump

# mysqldump -h主机 -u用户名 -p密码 数据库 表明 > 位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student > D：/a.sql

规范数据库设计

为什么需要设计数据库

糟糕的数据库设计：

数据冗余，浪费空间
数据库插入和删除都会麻烦，异常
程序的性能差
良好的数据库设计：
节省内存空间
保证数据库的完整性
方便开发系统
软件开发中，关于数据库的设计：
分析需求：分析业务和需要处理的数据库需求
概要设计L设计关系图E-R图

设计数据库的步骤：(个人博客)

收集信息，分析需求
- 用户表(登录，注销，个人信息，博客，创建分类)
- 分类表(文章分类，谁创建的)
- 文章表(文章的信息)
- 评论表()
- 友链表(友链信息)
- 自定义表(系统信息，某个关键的字，或者一些主字段)
标识实体(把需求落地到每个字段)
标志实体之间的关系
- 写博客：user-->blog
- 创建分类：user-->catagory
- 关注：user-->user
- 友链：links
- 评论：user-user-blog

三大范式

为什么需要数据规范化？

信息重复
更新异常
插入异常
- 无法正常显示信息
删除异常
- 丢失有效信息

三大范式

第一范式
原子性：要求数据库表的每一列都是不可分割的原子数据项
第二范式
前提：满足第一范式
第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。
每张表只描述一件事情
第三范式
前提：满足第一第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

规范数据库的设计
规范性和性能
关联查询的表不得超过三张

考虑商业化的需求和目标，(成本，用户体验)数据库的性能更加重要
在规范性能问题时，需要适当的考虑规范性
故意给某些表增加一些冗余字段。
故意计算一些计算列(从大数据量降低为小数据量的查询：索引)

JDBC(重点)

第一个JDBC程序

下载数据库驱动jar包，并导入到lib文件夹中
编写测试代码

//我的第一个JDBC程序
public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver"); // 固定写法，加载驱动

        //2.用户信息和url
        //useUnicode=true&characterEncoding=utf8&useSSL=true
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "123456";

        //3.连接成功，返回数据库对象 Connection 代表数据库
        Connection connection = DriverManager.getConnection(url,username,password);

        //4.执行SQL的对象 Statement 执行sql的对象
        Statement statement = connection.createStatement();

        //5.执行SQL对象去执行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"));
            System.out.println("===============================");
        }

        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步骤总结：
1.加载驱动
2.连接数据库DriverMannager
3.获取执行sql的对象 Statement
4.获得返回的结果集
5.释放连接

statement对象

jdbc中statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可

代码实现

1.提取工具类：

public class JdbcUtils {

    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    static {
        try {
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            //驱动只用加载一次
            Class.forName(driver);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取链接
    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) {
                e.printStackTrace();
            }
        }
        if(statement!=null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(connection!=null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

"db.properties"文件内容

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

2.编写增删改方法
增：

public class TestInsert {
    public static void main(String[] args) {

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection(); // 获取数据库链接
            statement = connection.createStatement(); // 获得SQL的执行对象
            String sql = "INSERT INTO `users` VALUES" +
                    "(4,'logan','123456','logan@qq.com','1989-1-5')";

            int i = statement.executeUpdate(sql);
            if(i>0) System.out.println("插入成功！"+i);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection,statement,resultSet);
        }



    }
}

删：

public class TestDelete {
    public static void main(String[] args) {
        ResultSet resultSet = null;
        Statement statement = null;
        Connection connection = null;

        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            String sql = "DELETE FROM `users` WHERE id = 4";
            int i = statement.executeUpdate(sql);
            if(i>0) System.out.println("删除成功");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

改：

public class TestUpdate {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            String sql = "UPDATE `users` SET `NAME` = 'logan' WHERE id = 4";
            int i = statement.executeUpdate(sql);
            if(i>0) System.out.println("更新成功");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

3.查询

public class TestSelect {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            String sql = "SELECT `id`,`NAME`,`PASSWORD`" +
                    "FROM `users`" +
                    "WHERE id IN (1,3,4)";
            resultSet = statement.executeQuery(sql);
            while(resultSet.next()) {
                System.out.println("id="+resultSet.getInt("id"));
                System.out.println("name="+resultSet.getString("NAME"));
                System.out.println("password="+resultSet.getString("PASSWORD"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

PreparedStatement对象

SQL注入问题

使用PreparedStatement对象可以防止SQL注入问题
增

public class TestInsert {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();

            //区别
            String sql = "insert into `users` values(?,?,?,?,?)";
            statement = connection.prepareStatement(sql);

            //手动给参数赋值
            statement.setInt(1,5); // 给id赋值
            statement.setString(2, "qinjiang"); // 给name赋值
            statement.setString(3, "123456"); // 给password赋值
            statement.setString(4, "qinjiang@qq.com"); // 给email赋值
            // 注意点 sql.Date 数据库
            //       util.Date Java
            statement.setDate(5, new java.sql.Date(new Date().getTime())); // 给time赋值

            //执行
            int i = statement.executeUpdate();
            if(i>0) System.out.println("插入成功");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

删

public class TestDelete {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();

            String sql = "delete from `users` where id = ?";
            statement = connection.prepareStatement(sql);

            statement.setInt(1,5);

            int i = statement.executeUpdate();
            if(i>0) System.out.println("删除成功");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

改

public class TestUpdate {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();

            String sql = "update `users` set `NAME` = ? where id = ?";
            statement = connection.prepareStatement(sql);

            statement.setString(1,"秦将");
            statement.setInt(2,5);

            int i = statement.executeUpdate();
            if(i>0) System.out.println("更改成功");

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

查

public class TestSelect {

    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();

            String sql = "SELECT `id`,`NAME`,`password` from `users` where id = ?";
            statement = connection.prepareStatement(sql);

            statement.setInt(1,4);

            resultSet = statement.executeQuery();
            while(resultSet.next()) {
                System.out.println(resultSet.getInt("id"));
                System.out.println(resultSet.getString("name"));
                System.out.println(resultSet.getString("password"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

JDBC操作事务

public class TestTransaction1 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();

            //关闭事务自动提交，开启事务
            connection.setAutoCommit(false);

            String sql1 = "update `account` set `money` = `money`-1000 where `name` = 'A';";
            String sql2 = "update `account` set `money` = `money`-1000 where `name` = 'B';";
            statement = connection.prepareStatement(sql1);
            statement.executeUpdate();

            int a = 1/0;

            statement = connection.prepareStatement(sql2);
            statement.executeUpdate();

            //提交事务
            connection.commit();
            System.out.println("转账成功");

        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

数据库连接池

数据库链接--执行完毕--释放--链接--释放十分浪费资源
池化技术：预先准备一些资源，过来就连接预先准备好的

最小连接数：10
最大连接数：15
等待超时：100ms

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

开源数据源实现

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

DBCP

需要用到的jar包
commons-dbcp-1.4 commons-pool-1.6

posted @ 2021-10-08 17:20 OmegaGO 阅读(75) 评论(0) 编辑收藏举报

刷新页面返回顶部

OmegaGO

MySQL数据库（三）

MySQL数据库（三）

事务

什么是事务

索引

索引的分类

测试索引

索引原则

用户管理

用户管理

MySQL备份

规范数据库设计

为什么需要设计数据库

三大范式

JDBC(重点)

第一个JDBC程序

statement对象

PreparedStatement对象

JDBC操作事务

数据库连接池

公告