MySQL（下）

最新推荐文章于 2024-10-01 17:33:05 发布

xxmuq

最新推荐文章于 2024-10-01 17:33:05 发布

阅读量116

点赞数

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/xxmuq/article/details/113866707

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

9、事务

9.1、ACID原则

原子性：要么都成功，要么都失败。
一致性：事务前后数据完整性一致。
隔离性：多个用户访问同个数据库，数据库为每个用户开启单独的事务，这些事务互不影响。
持久性：事务一旦提交不可逆转，持久化到数据库。

9.2、SQL执行事务

MySQL默认自动提交事务。

--关闭事务自动提交，1则开启。
SET autocommit=0
--开启事务
START TRANSACTION
--提交
COMMIT
--回滚
ROLLBACK
--事务结束
SET autocommit=1

--创建保存点
SAVEPOINT 保存点名
--回滚保存点
ROLLBACK TO SAVEPOINT 保存点名
--删除保存点
RELEASE SAVEPOINT 保存点名
--一般不使用保存点

9.3、JDBC事务

//这一段可以先看12、JDBC再回来看。
static void login(String username, String password) {
    Connection conn = null;
    PreparedStatement pstm = null;
    ResultSet rs = null;
    try {
        conn = util.getConnection();
        //关闭数据库自动提交，开启事务
        conn.setAutoCommit(false);

        String sql1 = "UPDATE account set money = money-100 where name = 'a'";
        pstm = conn.prepareStatement(sql1);
        pstm.executeQuery();

        String sql2 = "UPDATE account set money = money-100 where name = 'a'";
        pstm = conn.prepareStatement(sql2);
        pstm.executeQuery();

        conn.commit();		//提交事务
    } catch (SQLException e) {
        try {
            conn.rollback();		//失败回滚
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }finally {
        util.release(conn,pstm,rs);
    }
}

10、索引

10.1、索引分类

主键索引（PRIMARY KEY）

唯一标识，主键不可重复。
唯一索引（UNIQUE KEY）

避免重复列出现，可以多个列都是唯一索引。

一个表中可以有多个唯一索引，但只能有一个主键索引。
常规索引（KEY/INDEX）

默认的索引。
全文索引（FULLTEXT）

快速定位数据，只有MyISAM引擎才有。

10.2、索引的使用

--创建表时给字段增加索引。

--显示所有索引信息
SHOW INDEX FROM 表名
--增加一个全文索引
ALTER TABLE 数据库.表 ADD FULLTEXT INDEX 索引名(列名)
--分析sql语句执行情况
EXPLAIN sql语句

10.3、索引的设计原则

选择唯一性索引
为经常需要排序、分组和联合操作的字段建立索引
为常作为查询条件的字段建立索引
限制索引的数目
尽量使用数据量少的索引
数据量小的表最好不要使用索引
尽量使用前缀来索引
删除不再使用或者很少使用的索引

11、数据库三大范式

第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项。

第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）

第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。

第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

第二第三范式的区别应该是：

第二范式要求将完全与主键相关的和部分与主键相关的表分成两张表，两张表的主键相同。

第三范式要求将与主键相关的和与主键无关的表分成两张表，两张表的主键不同

12、JDBC

用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。

12.1、JDBC使用

public static final String URL = "jdbc:mysql://localhost:3306/DatabaseName?useUnicode=true&characterEncoding=utf8&useSSL=true";//支持中文编码，使用utf8编码，安全连接
public static final String USER = "root";
public static final String PASSWORD = "123456";

public static void main(String[] args) throws Exception {
    //1.加载驱动程序
    Class.forName("com.mysql.jdbc.Driver");
    //2. 获得数据库连接
    Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
    //3.创建SQL对象
    Statement stmt = conn.createStatement();
    //4.执行SQL，返回结果集
    String sql1 = "SELECT * FROM user";
    String sql1 = "INSERT INTO `user` VALUE ('a',123456)";
    //查询使用executeQuery
    ResultSet rs1 = stmt.executeQuery(sql1);
    //增删改使用Update
    ResultSet rs2 = stmt.executeUpdate(sql2);
    //如果有数据，rs.next()返回true
    while(rs1.next()){
        //不知道返回的是什么类型可以使用getObject
        System.out.println("用户名："+rs1.getString("username")+" 密码："+rs1.getInt("pwd"));
    }
    //释放连接
    rs1.close();
    rs2.close();
    stmt.close();
    conn.close();
}

12.2、封装工具类

public class util {
    private static String dirver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    static {
        try {
            InputStream in = util.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);
            dirver = properties.getProperty("dirver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            Class.forName(dirver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    //释放连接
    public static void release(Connection conn, Statement stmt, ResultSet rs) {
        try {
            if (rs != null)
                rs.close();
            if (stmt != null)
                stmt.close();
            if (conn != null)
                conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

工具类使用

public static void main(String[] args) {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
        conn = util.getConnection();
        stmt = conn.createStatement();
        String sql = "INSERT INTO `user` VALUE ('a',123456)";
        int i = stmt.executeUpdate(sql);
        if (i > 0) {
            System.out.println("插入成功！");
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        util.release(conn, stmt, rs);
    }
}

12.3、SQL注入

SQL存在漏洞，对用户输入数据的合法性没有判断或过滤不严，在应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，实现非法操作，实现欺骗数据库服务器执行非授权的任意查询，得到相应的数据信息

//这样将会输出所有的用户信息。

public static void login(String username, String password) {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
        conn = util.getConnection();
        stmt = conn.createStatement();
        String sql = "SELECT * FROM users WHERE `username` = '" + username + "'AND `password` = '" + password + "'";
        rs = stmt.executeQuery(sql);
        while (rs.next()) {
            System.out.println("用户名：" + rs.getString("username") + " 密码：" + rs.getInt("pwd"));
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        util.release(conn, stmt, rs);
    }
}

public static void main(String[] args) {
    login(" ' or '1=1", " ' or '1=1");
}

12.4、PreparedStatement对象

和Statement类似，但防止SQL注入，效率更高。

public class DbDelete {
    public void delGoddess(){
        //获取连接
        Connection conn = DbUtil.getConnection();
        //sql, 每行加空格
        String sql = "delete from imooc_goddess where id=?";
        //预编译SQL，减少sql执行
        PreparedStatement ptmt = conn.prepareStatement(sql);

        //传参
        ptmt.setInt(1, id);

        //执行
        ptmt.execute();
    }
}
//增加和修改类似

PreparedStatement防止SQL注入问题，把传递的参数当做字符，忽略转义字符。

//这样会不会有任何输出
static void login(String username, String password) {
    Connection conn = null;
    PreparedStatement pstm = null;
    ResultSet rs = null;
    try {
        conn = util.getConnection();
        String sql = "SELECT * FROM users WHERE `username`=? AND `password` =?";
        pstm = conn.prepareStatement(sql);
        pstm.setString(1, username);
        pstm.setString(2, password);
        rs = pstm.executeQuery();
        while (rs.next()) {
            System.out.println("用户名：" + rs.getString("username") + " 密码：" + rs.getInt("pwd"));
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        util.release(conn, stmt, rs);
    }
}

public static void main(String[] args) {
    login(" ' or '1=1", " ' or '1=1");
}

12.5、数据库连接池

用户每次请求都需要向数据库获得链接，而数据库创建连接通常需要消耗相对较大的资源，创建时间也较长。假设网站一天10万访问量，数据库服务器就需要创建10万次连接，极大的浪费数据库的资源，并且极易造成数据库服务器内存溢出、拓机。

数据库连接池负责分配，管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个。

数据库连接池的最小连接数和最大连接数的设置要考虑到以下几个因素：

最小连接数：是连接池一直保持的数据库连接，所以如果应用程序对数据库连接的使用量不大，将会有大量的数据库连接资源被浪费。
最大连接数：是连接池能申请的最大连接数，如果数据库连接请求超过次数，后面的数据库连接请求将被加入到等待队列中，这会影响以后的数据库操作。
如果最小连接数与最大连接数相差很大，那么最先连接请求将会获利，之后超过最小连接数量的连接请求等价于建立一个新的数据库连接。不过，这些大于最小连接数的数据库连接在使用完不会马上被释放，他将被放到连接池中等待重复使用或是空间超时后被释放。

常用数据库连接池有，JDCP，C3P0。