MySQL4 -----事务、索引、权限管理和备份、数据库规范三大范式、JDBC、数据库连接池

6. 事务

---

6.1 什么是事务？

要么都成功，要么都失败

---

SQL语句1
.
.
.
SQL语句n

---

事务的特性：ACID原则，也伴随一些问题（脏读、幻读、）

• 原子性(Atomicity)：要么全部执行成功，要么全部执行失败，不会只执行一部分。
• 一致性(Consistency)：针对一个事务操作前后的状态保持一致。
• 持久性（Durability）：事务结束后的数据不会因为外界原因导致丢失。 事务一旦提交就不可逆。
• 隔离性(Isolation):多个事务同时运行，彼此互不干扰。

隔离导致的问题

事务的隔离级别：

• 脏读：一个事务执行中读取到了另一个事务未提交的数据。
• 不可重复读：事务A在已使用一个数据一次后，这时事务B进行修改该数据，接着事务A再次读取该数据，导致问题，事务A前后读取到的数据不一致。
• 幻读：在查询方面前后搜索出来的结果不一致。

执行事务

 -- ========= 事务 ==========
 -- MySQL 默认提交事务
 SET autocommit = 0  -- 关闭自动提交
 SET autocommit = 1  -- 开启自动提交（默认开启）
 
 -- 手动处理事务
 SET autocommit = 0  -- 关闭自动提交 
 
 -- 开启事务
 START TRANSACTION -- 标记一个事务的开始
 
 -- 提交事务，进行持久化
 COMMIT
 
 -- 回滚事务，恢复原样
 ROLLBACK

 -- 事务结束
 SET autocommit = 1  -- 开启自动提交
 
 --  以下为了解
 SAVEPOINT 保存点名 -- 在事务中可以设置一个保存点
 
 ROLLBACK TO SAVEPOINT 保存点名 -- 可以回滚到保存点名
 
 RELEASE SAVEPOINT 保存点名 -- 可以删除保存点

模拟场景

-- 模拟
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop

INSERT INTO `account`(`name`,`money`) VALUES('小明',2000.00),
('小森',10000.00)

 -- 模拟 一组转账事务
 SET autocommit = 0 ; -- 关闭自动提交

 START TRANSACTION  -- 开启事务
 
 UPDATE `account` SET money = money - 500 WHERE `name` = 'a'
 UPDATE `account` SET money = money + 500 WHERE `name` = 'b'
 
 COMMIT; -- 提交事务
 
 ROLLBACK; -- 回滚事务
  
 SET autocommit = 1; -- 开启自动提交

7. 索引

MySQL官方对索引的解释：索引（Index）是帮助MySQL高效获取数据的数据结构。

得出结论：索引是一种数据结构，作用是帮助MySQL高效获取数据。

7.1 索引的分类

• 主键索引（PRIMARY KEY）：
  • 唯一标识，主键不可重复，只能有一个列作为主键。
• 唯一索引（UNIQUE KEY）:
  • 避免重复的列 ，一张表里可以有多个唯一索引
• 常规索引 (KEY/INDEX）
  • 默认值是KEY，常规值是INDEX ，可以通过key和index关键字来设置
• 全文索引（FULLTEXT）
  • 在特定的数据库引擎中才有， 如：MyISAM
  • 快速定位数据

7.2 测试索引

-- 创建app用户表
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '' COMMENT '用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
`phone` VARCHAR(20) DEFAULT '' COMMENT '手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别(男0，女1)',
`password` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '密码',
`age` TINYINT(4) DEFAULT '0' NULL COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'

 -- 插入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`,`password`,`age`)VALUES(CONCAT('用户',i),'19224305@qq.com',CONCAT('18',FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*(2)),UUID(),FLOOR(RAND())*100);
	SET i=i+1;
END WHILE;
RETURN i;
END;

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

-- 不建立索引时，执行时间
SELECT * FROM `app_user` WHERE `name`= '用户19999' -- 执行耗时  : 0.629 sec
EXPLAIN SELECT * FROM `app_user` WHERE `name`= '用户19999'

-- 索引命名格式 ： id_表名_字段名
-- create index 索引名 on 表(`字段`)
CREATE INDEX `id_appuser_name` ON `app_user`(`name`);

-- 建立索引后，执行时间。
SELECT * FROM `app_user` WHERE `name`= '用户19999' -- 执行耗时  : 0.002 sec
 
EXPLAIN SELECT * FROM `app_user` WHERE `name`= '用户19999'

索引 在数据量小的时候 效果不明显，在数据量大的时候效果十分明显。

7.3. 索引原则

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表不用加索引
• 索引一般加在常用来查询的字段上

索引的数据类型

InnoDB的默认数据结构是B+树

---

8 权限管理和备份

8.1 用户管理

SQL命令

存放用户信息的表 : mysql.user

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

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

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

-- 重命名  RENAME USER 原名 TO 新名
RENAME USER swrici TO swrici2

-- 用户授权 授予全部的权限 GRANT ALL PRIVILEGES
-- 不能给别人授权
GRANT ALL PRIVILEGES ON *.* TO swrici2

-- 查看权限
SHOW GRANTS FOR swrici2   -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost

-- 撤销权限 revoke 哪些权限  on 哪个库 from  给谁撤销
REVOKE ALL PRIVILEGES  ON *.* FROM swrici2

-- 删除用户
DROP USER swrici2

---

8.2 SQL备份

为什么要备份？

• 保证重要的数据不丢失。
• 数据转移

MySQL数据库备份的方式：

• 直接拷贝物理文件
• 在SQL yog 这种可视化工具中 手动导出
• mysqldump 在命令行中使用 ，cmd

--导出 在命令行中
-- mysqldump -h主机 -u用户名 -p密码 库名 表名 >哪个盘:/文件名.sql
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql

-- mysqldump -h主机 -u用户名 -p密码 库名 表名1 表名2 表名3>哪个盘:/文件名.sql
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql

-- 导入 在sql中
--source 文件路径
source d:/a.sql

9 规范数据库设计

9.1 为什么要有数据库设计

当数据库比较复杂的时候，我们就需要设计
数据库设计对比：

• 糟糕的数据库设计：
  • 数据冗余,浪费空间。
  • 数据库插入和删除都很麻烦、异常（屏蔽使用物理外键）
  • 程序的性能差
• 良好的数据库设计:
  • 节省空间
  • 保证了数据库的完整性
  • 方便我们开发系统

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

• 分析需求：分析业务和需要处理的数据库的需求
• 概要设计：设计关系图 E-R图

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

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

9.2 三大范式

为什么需要数据规范化？

• 避免信息重复
• 更新数据可能会导致一些异常
• 插入数据可能会导致一些异常
  • 可能会有一些表能没有插入信息
• 删除数据可能会导致一些异常
  • 存在一些表删不干净

三大范式

第一范式（1NF）

• 保证每一列不可再分，每一列只能由一个数据，不能一列出现两个数据的情况。

第二范式（2NF）
前提：满足第一范式
每张表只描述一件事。可能存在一个主键对应多类东西的表，但是这几种类之间并没有关系，所以要把这几类东西分别划分成几个表，让他们和主键一一对应。
第三范式（3NF）
前提：满足第二范式
在一个表中不能存在 不依赖该表主键 而去 依赖其他主键 的字段

规范 和性能 的问题
阿里规范，关联查询的表不能超过三张表

• 在考虑商业化的需求和目标，（成本，用户体验），数据库的性能更重要。
• 在规范性能问题的时候，需要适当考虑一下 规范性
• 有时也会故意增加一些冗余的字段，从多表查询变成了单表查询
• 也可能会故意增加一些计算列（从大数据量变成了小数据量的查询，索引）

10 JDBC

10.1 数据库驱动

应用程序要通过数据库驱动才能访问数据库

10.2 JDBC

SUN公司为了简化操作人员（对数据库的统一）操作，提供了一个（Java操作数据库的）规范，俗称JDBC

10.3 第一个JDBC程序

创建测试数据库

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcStudy;

CREATE TABLE `users`(
id INT PRIMARY KEY,
NAME VARCHAR(40),
PASSWORD VARCHAR(40),
email VARCHAR(60),
birthday DATE
);

INSERT INTO `users`(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')

编写Java程序进行连接

public class JDBCTestDemo {
    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.链接成功，获得数据库对象
        Connection connection = DriverManager.getConnection(url, username, password);
        //4.执行SQL的对象
        Statement statement = connection.createStatement();

        //5. 使用该对象去执行SQL
        String sql = "select * from users";
        ResultSet rs = statement.executeQuery(sql);
        while (rs != null && rs.next()) {
            System.out.println(rs.getString(1) + ":" + rs.getString(2) + ":" + rs.getString(3));
        }
        //6. 释放链接
        rs.close();
        statement.close();
        connection.close();
    }
}

步骤：

1. 加载驱动
2. 连接数据库DriverManeger
3. 获得连接 Connection
4. 获得执行SQL的对象Statement
5. 执行SQL获得结果集对象ResultSet
6. 依次释放连接（rs,state,connect）

---

//等同于DriverManager.registerDriver(new com.mysql.jdbc.Driver())
class.forName(“com.mysql.jdbc.Driver”);//固定写法，加载驱动

---

url

String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";

//mysql 默认端口 3306
//jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2

//Oracle 默认端口： 1521
//jdbc:oracle:thin:@localhost:1521:sid

---

获得数据库对象

//connection 代表数据库，设置自动提交，进行提交，进行回滚
Connection connection = DriverManager.getConnection(url, username, password);
connection.commit();
connection.rollback();
connection.setAutoCommit();

Statement 执行SQL的类 prepareStatement

//sql具体要看什么操作
statement.executeQuery(sql);//执行查询操作
statement.execute(sql);//执行所有操作
statement.executeUpdate(sql);//执行更新，增加，删除操作

ResultSet rs结果集对象：分装了所有的查询结果

获得指定的数据类型：

//不知道类型的时候 rs是ResultSet的实例化对象
 rs.getObject();
//知道具体的类型的时候
 rs.getString();
 rs.getInt();
 rs.getFloat();
 rs.getArray();

遍历

rs.next()//移动到下一个
rs.previous();//移动到前一个
rs.afterLast();//移动到最后面
rs.beforeFirst();//移动到最前面
rs.absolute(row);//移动到指定行

释放连接，因为连接很耗资源

10.4 statement 对象

jdbc中的statement对象用于向数据库发送SQL语句，对数据库进行增删改查
主要方法：
excuteQuery(),执行查询，返回一个结果集，包含了查询的结果。
exucteUpdate(),执行更新操作，返回一个int值，进行更新的条数。（更新指的是增加、删除、修改)

SQL注入的问题

SQL语句存在漏洞，被攻击

10.5 prepareStatement

prepareStatement 可以防止SQL注入，并且效率更高

1. 新增
2. 删除
3. 修改
4. 查询

10.6 事务

package com.swrici.jdbcDemo.transaction;

import com.swrici.jdbcDemo.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TransactionDemo {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;


        try{
            connection = JdbcUtils.getConnection();
            //关闭数据库的自动提交，自动开启事务
            connection.setAutoCommit(false);
            String sql1  = "update account set money = money - 100 where name = 'A';";
            preparedStatement = connection.prepareStatement(sql1);

            preparedStatement.executeUpdate();
            int a = 1/0;
            String sql2  = "update account set money = money + 100 where name = 'B';";

            preparedStatement = connection.prepareStatement(sql2);

            preparedStatement.executeUpdate();
            //业务完毕，提交事务
            connection.commit();
            System.out.println("成功");


        } catch (Exception e) {
            connection.rollback();
            e.printStackTrace();
        }finally {
            JdbcUtils.realase(connection,preparedStatement,resultSet);
        }

    }
}

手动执行事务的时候，要先关闭自动提交事务的按钮，然后在事务结束时要提交事务，再开启自动提交事务。

10.7 数据库连接池

数据据库的连接到释放是十分耗费资源的，如果频繁的调用数据库连接释放就会产生大量的系统开销。
池化技术，就是为了解决这样的麻烦，通过预先准备好一些资源让其连接。
最小连接数：
最大连接数：业务最高承载上限，一旦连接数超过，就让多余的进入等待。
等待超时：时间
编写连接池，只需要一个接口DataSource

开源数据库实现(拿来即用)

DBCP，需要用到的jar包

• commons-dbcp-1.4.jar
• commons-pool-1.6.jar

C3P0，需要用到的jar包

• c3p0-0.9.5.2.jar
• mchange-commons-java-0.2.15.jar

Druid:
在使用这些连接池技术之后，项目开发就中就不需要编写连接数据库的代码了。

结论： 都要实现DataSource接口，所以提供的方法不会变，进和出层面的。