MySQL 数据库（下）

事务

什么是事务

  • A给B转账，B收到A的钱

    要么都成功，要么都失败

    将一组SQL放到一个批次中去执行

  • ACID原则

    原子性Atomicity，要么都完成，要么都不完成

    一致性Consistency，针对一个事务操作前与操作后状态一直

    持久性Durability，表示事务结束后的数据不随着外界原因导致数据丢失（事务一旦提交不可逆）

    隔离性Isolation，针对多个用户同时操作，主要是排除其他事务对本次事务的影响

  • 隔离导致的问题

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

MySQL中的事务

  • 用法

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

-- 手动处理事务
SET autocommit = 0 -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在同一个事务内
INSERT XXX
INSERT XXX
-- 提交：持久化（成功）
COMMIT
-- 回滚：回到原来的样子（失败）
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启自动提交

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

  • 模拟场景 转账

    -- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop

CREATE TABLE `account`(
	`id` INT(3) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(30) NOT NULL,
	`money` DECIMAL(9,2) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO account(`name`,`money`)
VALUES ('A',2000.00),('B',10000.00)

-- 模拟转账：事务
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'; -- B加500

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

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

  • Java中操作事务

    方法(){
    try(){
        //正常的业务代码
        commit();
    }catch(){
        rollback();
    }
}

索引

MySQL官方对索引的定义为：索引(Index) 是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

  • 索引的分类

    主键索引 PRIMARY KEY：唯一的标识，不可重复，只有一个列作为主键

    唯一索引 UNIQUE KEY：避免重复的列（字段）出现，可以重复，多个列都可以标识为唯一索引

    常规索引 KEY / INDEX：默认的

    全文索引 FULLTEXT INDEX：在特定的数据库引擎下才有，快速定位数据

  • 索引的使用

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

-- 添加一个全文索引 索引名（列名）
ALTER TABLE school.student ADD FULLTEXT INDEX `name`(`name`);

-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引
SELECT * FROM student WHERE MATCH(`name`) 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`,`phone`,`password`,`age`) 
		VALUES(CONCAT('用户',i),CONCAT('18',FLOOR(1000000000-RAND()*999999999)),UUID(),FLOOR(RAND()*100)); -- 插入语句
		SET i = i+1;
	END WHILE;
	RETURN i;
END;

CREATE TABLE app_user(
		`id` INT(3) NOT NULL AUTO_INCREMENT,
		`name` VARCHAR(20) NOT NULL,
		`phone` VARCHAR(11) NOT NULL,
		`password` VARCHAR(50) NOT NULL,
		`age` INT(3) NOT NULL,
		PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

SELECT mock_data();

  • 测试

    SELECT * FROM app_user WHERE `name`='用户9999'; -- 将近1s才找到
CREATE INDEX id_app_user_name ON app_user(`name`); -- 添加索引，用1s多
SELECT * FROM app_user WHERE `name`='用户9999'; -- 添加索引后查询0.001s

  • 索引在小数据量的时候用处不大，大数据的时候效果明显变快

索引原则

  • 索引不是越多越好

  • 不要对经常变动数据加索引

  • 小数据量的表不需要加索引

  • 索引一般加在常用来查询的字段上

  • 索引的数据结构

    Hash类型的索引

    Btree：INNODB的默认数据结构

权限管理

用户管理

  • Navicat可视化管理

  • mysql.user 用户表

对用户表进行增删改查

-- 创建用户
CREATE USER peng IDENTIFIED BY '123456';

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

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

-- 重命名
RENAME USER peng TO pengpeng;

-- 授予权限  库.表  *.*表示全部库的全部表
GRANT ALL PRIVILEGES ON *.* TO pengpeng; -- 除了GRANT别人，其他事都能干

-- 查询权限
SHOW GRANT FOR pengpeng;
SHOW GRANT FOR root@localhost;

-- ROOT用户的权限
GRANT ALL PRIVILEGES ON *.* TO root@localhost WITH GRANT OPTION;

-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM pengpeng;

-- 删除用户
DROP USER pengpeng;

MySQL备份

  • why

    1. 保证重要的数据不丢失

    2. 数据转换

    3. 存起来比较小

  • 备份方式

    1. 直接拷贝物理文件

    2. 可视化工具：导出的是SQL语句，拉过来执行就行了

    3. 命令行 mysqlump 导出

      mysqlump -h127.0.0.1 -uroot -p123456 school [student] >D:/a.sql #导出
source D:/a.sql #导入(需要先登录)

规范数据库设计

为什么需要设计

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

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

三大范式

  • 问题

    信息会重复

    更新会导致异常

    插入异常：无法正常显示信息

    删除异常：丢失有效信息

  • 三大范式（规范数据库）

    1. 第一范式(1NF)：要求数据库表的每一列都是不可分割的原子数据项，原子性
    2. 第二范式(1NF)：前提，满足第一范式；每张表只描述一件事情
    3. 第三范式(1NF)：前提，满足第一、二范式；每一列都跟主键直接相关，不能间接相关

  • 规范性 和 性能 的问题

    一般地，关联查询的表不能超过三张表

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

JDBC

数据库驱动

  • 驱动：声卡，显卡，数据库

    程序通过数据库驱动连接数据库

  • JDBC

    为了简化开发人员的（对数据库的）操作，提供的一个（Java操作数据库的）规范

  • Java包

    java.sql

    javax.sql

    导入一个数据库驱动包 mysql-connector-java-5.1.47.jar

第一个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')

  • 创建一个普通项目

  • 导入数据库驱动

    1. 右键项目，新建一个 lib 目录
    2. 把 jar 包拷贝进来
    3. 右键 lib，选择Add as Library...

  • 编写测试代码

    import java.sql.*;

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

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

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

        //4.执行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("pwd="+resultSet.getObject("PASSWORD"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birth="+resultSet.getObject("birthday"));
            System.out.println("=============================");
        }

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

    • JDBC 对象

      DriverManager：Class.forName("com.mysql.jdbc.Driver"); //固定写法 加载驱动

      Connection：代表数据库，数据库能做的他都能做

      connection.rollback();
connection.commit();
connection.setAutoCommit();
...

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

      Statement：执行sql的对象

      statement.executeQuery(); //查
statement.execute(); //任何sql语句
statement.executeUpdate(); //增删改

      ResultSet：查询结果集

      resultSet.getObject(); //不知道什么类型
resultSet.getString();
resultSet.getFloat();
...
resultSet.beforeFirst(); //移动到最前面
resultSet.afterLast(); //移动到最后面
resultSet.next(); //移动到下一个数据
resultSet.previous(); //移动到前一行
resultSet.absolute(row); //移动到指定行

      释放资源

      resultSet.close();
statement.close();
connection.close(); //耗资源，用完关闭

statement对象

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

  • Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sq|语句，executeUpdate执行完后， 将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。

  • Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象。

  • CRUD操作-create

    使用executeUpdate(String sq|)方法完成数据添加操作，示例操作：

    Statement st = conn.createStatement();
Stirng sql = "insert into user(...) values(...)";
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println("插入成功！！");
}

  • CRUD操作-delete

    使用executeUpdate(String sq|)方法完成数据删除操作，示例操作：

    Statement st = conn.createStatement();
Stirng sql = "delete from user where id=1";
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println("删除成功！！");
}

  • CRUD操作-update

    使用executeUpdate(String sq|)方法完成数据修改操作，示例操作：

    Statement st = conn.createStatement();
Stirng sql = "update user set name='pengpeng' where name='peng'";
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println("修改成功！！");
}

  • CRUD操作-read

    使用executeQuery(String sq|)方法完成数据查询操作，示例操作：

    Statement st = conn.createStatement();
Stirng sql = "select * from user where id=1";
ResultSet rs = st.executeQuery(sql);
while(rs.next){
    //根据获取列的数据类型，分别调用rs的相应方法映射到java对象中
}

  • 代码

    1. 配置

  1. 工具类 JbdcUtils

    package lesson02.utils;

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

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");

            //1.驱动只用加载一次
            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 conn, Statement st, ResultSet rs){
        if (rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

  2. 测试类

    package lesson02;

import lesson02.utils.JdbcUtils;

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

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

        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection(); //获取数据库连接
            st = conn.createStatement(); //获得执行sql的对象
            String sql = "DELETE * FROM users WHERE id=1";
            int i=st.executeUpdate(sql);
            if (i>0){
                System.out.println("删除成功!!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

  • SQL注入的问题

    sql存在漏洞，会被攻击

    login(username=" ' or ' 1=1 ",password=" ' or ' 1=1 ")

PrepareStatement

  • 是Statement的子类，可以防止sql注入，并且效率更高

    package lesson03;

import lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();

            //区别
            //使用？占位符代替参数
            String sql = "INSERT INTO user(id,`name`,`password`,`birthday`) VALUES(?,?,?,?)";

            st = conn.prepareStatement(sql); //预编译sql，先写sql，然后不执行

            //手动给参数赋值
            st.setInt(1,2018215555);
            st.setString(2,"peng");
            st.setString(3,"8098");
            //注意点： sql.Date  数据库  java.sql.Date()转换
            //        util.Date  Java   new Date().getTime() 获得时间戳
            st.setDate(4,new java.sql.Date(new Date().getTime()));

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

  • 防注入本质

    传递过来的参数当作字符，有转义字符时直接忽略

IDEA连接数据库

事务

  • ACID原则

    原子性，一致性，隔离性，持久性

  • 隔离性的问题

    脏读：一个事务读取了另一个没有提交的事务
    不可重复读：在同一个事务内，重复读取表中的数据，表数据发生了改变
    虛读(幻读)：在一个事务内，读取到了别人插入的数据，导致前后读出来结果不一致

    package lesson04;

import lesson02.utils.JdbcUtils;

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

public class TestTransaction {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            //关闭数据库的自动提交，自动会开启事务
            conn.setAutoCommit(false); //开启事务

            String sql1 = "UPDATE account set money=money-100 WHERE name='A'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();

            String sql2 = "UPDATE account set money=money+100 WHERE name='B'";
            conn.prepareStatement(sql2);
            st.executeUpdate();

            //业务完毕，提交事务
            conn.commit();
            System.out.println("成功！！");
        } catch (SQLException e) {
            //conn.rollback(); //失败则回滚，可以不写，失败默认回滚
            e.printStackTrace();
        } finally {
            try {
                conn.setAutoCommit(true);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            JdbcUtils.release(conn,st,rs);
        }
    }
}

数据库连接池

连接 - - 释放，浪费系统资源

  • 池化技术

    准备一些预先的资源，过来就连接预先准备好的

    常用连接数 10个

    最小连接数 10

    最大连接数 15 业务最高承载上限

    等待超时 100ms

  • 编写连接池

    实现一个接口 DataSource

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

    DBCP，C3P0，Druid

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

  • DBCP

    需要用到的jar包

    commons-dbcp-1.4、commons-pool-1.6

  • C3P0

    需要用到的jar包

    c3p0-0.9.5.5、mchange-commons-java-0.2.19

  • 结论

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

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/peng8098/p/db_02.html