关于数据库学习的一些知识盲区

一.SQL拼接方法的安全风险

   1.  SQL注入问题（SQL Inject），使用字符串拼接构造SQL就会引起SQL注入。
   2. SQL注入是存在安全风险的
   3. 例如：在图书管理系统中，我们从下图可以看到数据库`db_11_29`的`book`表中现有信息如下：

我们在IDEA中写的代码如下：

在这里插入代码片!package com.youzhizhong.sql_inject;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;


/**
 * Created by Mark
 * Description:
 * User: 1
 * Date: 2021-11-29
 * Time: 19:14
 */
public class Demo {
    private static String url = "jdbc:mysql://127.0.0.1:3306/db_11_29?characterEncoding=utf8&severTimezone=Asia/Shanghai&useSSL=false";
    private static DataSource dataSource(){
        MysqlDataSource mysqlDataSource = new MysqlDataSource();
        mysqlDataSource.setUrl(url);
        mysqlDataSource.setUser("root");
        mysqlDataSource.setPassword("yyy990523");
        return mysqlDataSource;
    }
    
    public static void main(String[] args) throws SQLException {
        Scanner scanner = new Scanner(System.in);
        try (Connection c = dataSource().getConnection()) {
            System.out.print(" > ");
            while (scanner.hasNextLine()) {
                String keyword = scanner.nextLine();
                String sql = "select * from books where current_count > %s";
                sql = String.format(sql, keyword);
                System.out.println("    DEBUG: " + sql);

                try (PreparedStatement ps = c.prepareStatement(sql)) {
                    try (ResultSet rs = ps.executeQuery()) {
                        while (rs.next()) {
                            int id = rs.getInt("bid");
                            String name = rs.getString("name");
                            int total = rs.getInt("total_count");
                            int current = rs.getInt("current_count");
                            System.out.printf("    %3d %10s %3d %3d\n", id, name, total, current);
                        }
                    }
                }

                System.out.print(" > ");
            }
        }
    }
}

     程序中期望查询的结果是 book表中当前数量大于某个值的书籍，程序员期望用户输入的是书籍当前的存量，而用户传入什么就会在程序运行中被拼接成什么，例如图中用户输入了一个非常隐秘的字符串 1000 or 1=1，由于1=1恒为真，此时导致SQL的拼接出问题了，用户利用了程序猿写的程序中的SQL拼接的漏洞，查询到的是表中所有的书籍，用户就没有按照程序中期望查询的方式进行查询，这就是一个非常典型的SQL注入问题.

注意:此处使用字符串拼接构造SQL是为了让我们了解其存在的风险，在以后的工作和学习中，这种方法是被完全禁止的。否则很容易出现SQL注入的安全风险很大。

二、PreparedStatement(sql)的参数写法

     正是因为字符串拼接构造SQL存在安全风险，所以在这儿我们引入了另外一种不存在安全风险的方法（带参数的SQL处理），这是PreparedStatement更应该使用的方案：

PreparedStatement的参数写法：将带有通配符的SQL作为参数传入,具体用法如下面的代码所示。

SQL的写法：“select * from books where current_count > ？”；     

?是参数占位符（parameter placehold),并不代表真正的参数。

package com.youzhizhong.sql_inject;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;


import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

/**
 * Created by Mark
 * Description:
 * User: 1
 * Date: 2021-11-29
 * Time: 21:46
 */

public class Demo2 {
    private static String url = "jdbc:mysql://127.0.0.1:3306/db_11_29?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";

    private static DataSource dataSource() {
        MysqlDataSource mysqlDataSource = new MysqlDataSource();
        mysqlDataSource.setUrl(url);
        mysqlDataSource.setUser("root");
        mysqlDataSource.setPassword("yyy990523");

        return mysqlDataSource;
    }

    public static void main(String[] args) throws SQLException {
        Scanner scanner = new Scanner(System.in);

        try (Connection c = dataSource().getConnection()) {
            System.out.print(">");
            while (scanner.hasNextLine()) {
                String keyword1 = scanner.nextLine();
                String keyword2 = scanner.nextLine();
                //通过？在SQL中进行占位
                String sql = "select * from books where current_count > ? and total_count > ?";
                try (PreparedStatement ps = c.prepareStatement(sql)) {
                    //ps.setString(   要替换第几个参数，要求从1开始算      String类型的要替换的值)
                    //通过ps.setXXX(...)使用真实参数替换
                    //由于keyword是String类型，所以使用setString;
                    ps.setString(1, keyword1);
                    ps.setString(2, keyword2);

                    System.out.println("    DEBUG: " + ps);

                    try (ResultSet rs = ps.executeQuery()) {
                        while (rs.next()) {
                            int id = rs.getInt("bid");
                            String name = rs.getString("name");
                            int currentCount = rs.getInt("current_count");
                            int total = rs.getInt("total_count");
                            System.out.printf("    %3d %10s %3d %3d", id, name, currentCount, total);
                            System.out.println();
                        }

                    }
                }
                System.out.print(">");
            }
        }
    }
}

注意：在该段程序中，不能把 try (ResultSet rs = ps.executeQuery()) {......} 这个代码段替换成 try (ResultSet rs = ps.executeQuery(sql)) {......} ,因为此时传入的sql是没有被赋过真实是的sql，传入的是"select * from books where current_count > ? and total_count > ?";

三.事务(Transaction)

3.1事务的概念：

    事务是属于计算机中一个很广泛的概念，一般是指要做或所做的事情。在关系数据库中，一个事务可以是一条SQL或者一组SQL语句或整个程序。
    在很多情况下是由多条SQL共同完成一个业务动作的，例如在借书这个业务动作中，就会有查阅书籍存量、修改书籍存量以及添加借阅记录这三条SQL等等；在还书这个业务动作中，也会有修改书籍存量和添加借阅记录等等SQL;这两个业务动作没办法用一条SQL就完成，在上述的业务动作中，我们就需要把借书和还书这两个动作分别看成两个事务。
    当我们把借书这个业务动作设置为一个事务时，这样的好处是，当系统执行出现了Bug导致后面的SQL无法执行时，数据会保持统一性，如果在修改完书籍存量后程序执行出了Bug导致没能添加上借阅记录，此时若没有把借书这个业务动作设置为一个事务时，就会导致书籍的量对不上了，这是万万不行的，所以需要事务这个概念，为了更深入了解事务这个概念，我们需要引入另一个概念，数据的一致性。

注意：书籍的总量 - 书籍的存量 = 借阅记录中未归还的书的量

3.2事务的四大属性：

    事务是恢复和并发控制的基本单位，它具有四个属性：原子性，一致性，持久性，隔离性。事务的核心是一致性。

3.2.1原子性

概念：

一个事务是一个不可分割的工作单位，事务中包括的操作要么都做，要么都不做。

原子性的核心是回退为中间状态，回退为中间状态的核心就是回滚，回滚的核心就是记住每步的操作。

3.2.2一致性

概念：

事务必须是使数据库从一个一致性（一致性的更具体概念会在下文讲到）状态变到另一个一致性状态。一致性与原子性是密切相关的。

执行事务之前和执行事务完成之后，当前表里的数据都是合理的状态。

3.2.3持久性

概念：

持久性也称永久性（permanence）,指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其有任何影响。

事务操作的数据都是直接操作硬盘，而硬盘的数据都是持久化的。

3.2.4隔离性

概念：

一个事务的一致性不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其它事务是隔离的，并发执行的各个事务之间不能互相干扰。

数据的一致性的理解
    概念：数据的一致性可以认为是在数据库保存的数据信息中最最核心的一个业务关系，也是事务的一个追求目标。对于数据的一致性，没有一个严格的定义，可以认为是DBMS用户根据其应用场景，对数据提出一系列约束要求，满足这个要求可以认为数据的一致性是满足的，满足不了，就说明是有问题的：
比如：图书管理系统中：当任取一本书时，要求：
           1）书的存量要大于等于0；
           2）书的存量要小于等于总量；
           3）书的总量 - 书的存量 = 被借出去的书的量；
再比如说银行转账系统中，当从账户A转钱到账户B时，此时无论转账是否成功，都要求：账户A的余额加上账户B的余额是一个恒定值(不考虑手续的问题等等）
此外，我们曾经学过的无头双向链表，任取链表中的节点（除头、尾节点外），都要求：
           1）当前节点的前驱指针和后继指针都不能为空指针；
           2）当前节点前驱的后继 = 当前节点后继的前驱 = 当前节点；
     这些都是一致性的体现，无论你怎么操作，这些核心约束不能被违背，不能被破坏，一旦被破坏，就应该认为整个系统的数据受到损坏了，程序应该停止工作了，不能再对外服务了。

    例如：在图书管理系统的借书的整个过程中，我们可以让借书这个程序阶段性的往前走，借书前数据库表中的数据应该满足一致性，借书后表中的数据也应该满足一致性，而在借书这个动作中，由于查阅书籍存量、修改书籍存量以及添加借阅记录这三条SQL是分别执行的，数据的一致性有可能被破坏，导致对不上账，为了让过程中一致性的破坏不影响数据整体的一致性，所以，需要事务概念来处理,如果事务处理成功，就会继续执行，如果事务处理失败，就会回退到借书前的状态，这样就不会造成表中数据对不上账!

下面我们再来探讨一下一般什么情况下数据的一致性可能被破坏：
第一种情况为：SQL执行过程中，程序出现了异常，导致SQL执行不下去了。
    （1）硬件原因：JDBC程序所在的电脑死机了。

                    MySQL 服务器所在的电脑死机了。

                    连接的网络中断了。

    （2）软件原因：前边的SQL执行成功了，后边的SQL执行异常。
                   比如SQL写错了

                   前边的SQL执行成功了，后边的SQL还没执行，程
                   序就遇到软件异常了。比如：空指针异常，数组下标越界异常

第二种情况可能是：当多个会话（Session）同时动一份数据，会造成相互干扰。
     比如你去银行柜台上办理业务，而你的女朋友此时正在家中通过手机正在进行相同的业务，此时就会造成互相干扰。

借书这个动作的部分代码实现如下：

public class Library {
    public Record borrowBook(int uid,int bid) throws SQLException {
        MysqlDataSource mysqlDataSource = new MysqlDataSource();
        mysqlDataSource.setServerName("127.0.0.1");
        mysqlDataSource.setPort(3306);
        mysqlDataSource.setUser("root");
        mysqlDataSource.setPassword("yyy990523");
        mysqlDataSource.setCharacterEncoding("utf8");
        mysqlDataSource.setServerTimezone("Asia/Shanghai");
        mysqlDataSource.setDatabaseName("db_11_26");
        mysqlDataSource.setUseSSL(false);
        Record record = new Record();
        Book book = new Book();
        User user = new User();
        try(Connection connection = mysqlDataSource.getConnection()){
      		connection.setAutoCommit(false); //auto commit 和 Cnnection对象绑定，再得到一个新的Connection对象，默认还是auto commit == true 的
            String sql = "select * from book where current_count >? and total_count > ?";
            System.out.println("DEBUG:"+sql);
            try(PreparedStatement preparedStatement = connection.prepareStatement(sql)){
                preparedStatement.setInt(1,19);
                preparedStatement.setInt(2,3);
                System.out.println("    DEBUG"+preparedStatement);
                try(ResultSet resultSet = preparedStatement.executeQuery()){
                    resultSet.next();
                    int currentCount = resultSet.getInt("current_count");
                    if(currentCount == 0){
                        return null;
                    }
                }
            }
            sql = String.format("update book set current_count = current_count-1 where bid = %d",bid);
            System.out.println("DEBUG:"+sql);
            try( PreparedStatement preparedStatement = connection.prepareStatement(sql)){
                preparedStatement.executeUpdate();
            }

            LocalDateTime now = LocalDateTime.now();
            String nowStr = now.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
            sql = String.format("insert into record(uid,bid,borrowed_at) values(%d,%d,'%s')",uid,bid,nowStr);
            System.out.println("DEBUG"+sql);
            try( PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)){
                preparedStatement.executeUpdate();
                try( ResultSet rs = preparedStatement.getGeneratedKeys()){
                    rs.next();
                    record.rid = rs.getInt(1);
                }
            }
            sql = String.format("select * from book where bid = %d",bid);
            System.out.println("DEBUG"+sql);
            try( PreparedStatement preparedStatement = connection.prepareStatement(sql)){
                try( ResultSet resultSet = preparedStatement.executeQuery()){
                    resultSet.next();
                    book.bid = bid;
                    book.name= resultSet.getString("name");
                    book.total_count = resultSet.getInt("total_count");
                    book.current_count= resultSet.getInt("current_count");
                }
            }
           
            cnnection.commit(); //进行事务的提交
            record.user = user;
            record.book = book;
            record.borrowed_at = now;
            record.returned = false;
            record.returned_at = null;
        }
        return record;
	}
}

3.3如何使用事务（在workbench,可以执行SQL的客户端中使用）

    核心：如何让DBMS知道我们要执行的哪些SQL应该被当成一个整体（事务）来对待？                方法：通过明确开头和结尾，例如：
首先，start transaction : 开始事务，告诉DBMS，接下来之后的所有SQL应该被当成一个整体（事务）来对待；然后正常执行一条或者多条SQL；最后执行commit操作;(以成功的方式结束事务(提交事务)，告诉DBMS，这组SQL已经全部告诉DBMS了，在接下来就是下一组事务了），如果最后执行的是rollback;（回滚）操作，代表该事务的SQL语句全部执行失败。
workbench中使用事务代码如下：

```sql
-- 事务的执行，借书的事务

-- 开启事务
start transaction;

-- 查询某本书籍的存量
select current_count from books where bid = 3;

-- 修改书籍存量， -1
update books set current_count = current_count-1 where bid = 3;

-- 插入借阅记录
insert into records(uid,bid,borrowed_at) values(1,3, '2021-10-30 10:50:39');

-- 提交事务，代表本次事务结束
commit;

在JDBC中执行事务：
默认情况下，每条SQL都当成一个独立的事务对待，自动进行提交（auto commit）
关闭auto commit功能
明确进行commit；

四.索引

4.1索引的概念：

    索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。索引是针对表而建立的，它是由数据页面以外的索引页面组成的，每个索引页面中的行都会含有逻辑指针，以便加速检索物理数据。

在数据库关系图中，可以在选定表的“索引/键”属性页中创建、编辑或删除每个索引类型。当保存索引所附加到的表，或保存该表所在的关系图时，索引将保存在数据库中，也就是在磁盘上不仅以文件的形式保存了原始数据，也以文件的形式保存了一棵搜索树（是B树系列的树，不是红黑树），当进行查询操作时，先通设置了该索引的字段名称（key）在搜索树中找到对应主键(value），（该操作时间复杂度为log(n),n为表中的总记录数量），拿到主键后再去原始数据中读取我们想要的数据。

4.2 适用场景

1.查找的次数比较多，插入删除的次数较少适合用索引
2.由于索引本身也占据一定空间，如果磁盘紧张就不太适合用索引
3.索引是指定某个列来建立的，当某列的区分度比较大的时候，适合用索引，例如自增主键。

4.3 使用语句

4.3.1 查看索引
语法：

show index from 表名;

4.3.2 创建索引
语法：

create index 索引名 on 表名(字段名);

在workbench中创建索引的方式如图：

4.3.3 删除索引
语法：

drop index 索引名 on 表名;

注意：主索引不能删除，删除会报错

补充：创建主键约束（primary key）、唯一约束（unique）、外键约束（foreign key）时，会自动创建对应列的索引。

五.了解数据建模

      虽然数据建模目前专注于数据库领域，但数据建模（数据抽象）的价值不局限于数据库领域。

5.1数据建模的三大层次：

5.1.1 现实世界（概念层）

      现实世界中的概念包括：

5.1.2 开发人员头脑中的世界（逻辑层)/计算机软件世界

     万物皆对象就是指现实世界中的任何概念都可以被抽象成逻辑层的一个概念，对一个现实世界中的学生来说，要让它存储在计算机中，真正存储的是学生抽象出来的核心特征，如果从面向对象Java的角度来看这个学生，我们把学生看做成类和对象的关系，相当于学生这个名词是一个类，而一个个真实的学生我们就可以把它看做一个个对象，其核心特征就是类或者对象中的属性（如姓名，身高等等），如果从数据库的角度来看这个学生，相当于学生这个名词是一个表，而一个个真实的学生我们就可以把它看做一行行记录。
    对于学生来说，抽象出来的核心特征会有一个约束，这个约束就是它的数据类型，其核心特征就是属性，其行为就是方法。在MySQL中，主要是用到其核心特征。

5.1.3 计算机硬件世界（物理层）

在计算机硬件世界里，我们需要考虑的是如何存储才能更高效。例如，我们新建了一张表，应该通过那种方式存储（B+树或者是红黑树还是顺序存储），哪种存储方式更快更好，初学者阶段更多关注的是前两层，这一层不做过深的探索。

六.给定场景如何进行数据库表的设计

    首先，我们要找出实体（Entity）、关系（Relationship）以及实体和关系上的特征（也就是属性）,当然，有时候实体也可以看做是关系。从关系性数据库的角度来看，我们所有的表都是理论上的关系。

具体步骤为：

6.1我们要完成的系统用户有哪些角色，用户角色是我们思考问题的视角。

    例如，在图书管理系统中，用户大体上有两个角色，一个是图书管理员，一个是学生。

6.2以角色为视角，写用户故事，主要是需求动作。

    确认角色之后，完全站在该角色的角度，客观描述它的动作以及他的目标，但不要涉及到系统内部的实现。
    例如：作为XX角色，进行YY系列的动作，以达到我ZZ的目标;比如我们要发布文章，我们的身份就是作者，好的用户故事就是作为作者，我可以点击发文章按钮，进入发文章的界面，填写标题，正文，进行发表，同时，过段时间我会收到发表成功的短信，这就是一个较好的故事，不好的例子是作为系统的实现者，我要发表文章，将文章保存袋一个文件中，同时，调用第三方接口，发一条信息给作者，之所以它不好，就是因为他没有完全站在系统的实现者来写用户故事。

    6.2.1 给出故事的优先级：

        哪些故事是重要的，哪些故事是次要的，哪些故事是没必要的
        先完成重要的，然后完成次要的，有些没有必要的，可以讨论下有没有必要去实现，
        注意：故事集就是系统需求的总和。

6.3尝试从故事中找出实体（Entity）:实体基本上是故事里的名词

    找出来的实体就以数据库表的形式进行体现，当然，从故事中找出的实体我们不一定都用到，比如：在图书馆管理系统中，我们只需要一个管理员，则管理员不需要额外的表去保存。

6.4 借助思维工具（E-R）图，表现实体之间的关系

    实体用矩形框表示，属性用椭圆形来表示，关系用菱形来表示。
    在一个系统中，一个实体和其他实体之间必然产生必然的关系，且两个实体之间的关系可能不止一种。例如：假设A和B各是一个实体，他们的关系有以下几种：
1对1的关系：学生和该学生的档案只能是一对一，档案和学生也只能是一对一；
1对多的关系：一个用户可以发表多篇文章，但是，一篇文章只能有一个作者；
    在数据库中，如何表现一对多的关系？
    拿上述设个例子来说，就是在文章这个表中，添加关于作者这个表的主键字段，作为关系字段，将文章绑定在某一个作者上。

多对1的关系：多篇文章可以被一个用户发布，但是，文章的作者只能有一个；
多对多的关系：学生可以借阅多本书，书也可以被多个学生借；
    在数据库中，如何表现直接的多对多的关系？
    表示多对多的关系，需要借助关系的表示，把多对多的关系转换为1对多的关系。例如，学生和书籍之间是多对多的关系，通过借助借阅记录这个关系就可以把学生和书籍的关系间接的转换为学生和借阅记录的关系以及书籍和借阅记录的关系;一个学生可以借阅多本书（也就是一个学生可以对应多条借阅记录），但一条借阅记录只能和一个学生有关；书籍可以有多条借阅记录，但一条借阅记录只能和一本书有关，它们就被拆分成了一对多的关系。

两个实体之间肯有多个关系，借助E-R图把中间的关系表画出来，并其它把字段关系标注出来，例如在博客系统中，以用户和文章为例，我们可以想到两者有以下关系：

其画出的E-R图如图：

注：E-R图中的每个Entity和Relationship最终都可以通过DBMS中的一张表体现；实际中，还可能需要进一步思考（结合现实场景+优化思考），某些实体和关系就不表现为表了。

6.5借助三大范式为实体、关系确定属性

注意：
1）在进行上述操作时，主键必须明确出来，每张表必须有主键；
   一张表里选择主键时可能有多种选择，都是候选键；
2）确定了属性之后，通过数据库的三大范式，检查选择的字段是否合理；
   范式：一种经过前人总结出来的知道意见；
   （1）能遵守尽量遵守；
   （2）如果某些特殊情况下，有违反范式的需要了，是可以违反的，至少，一定是有强烈的需求的；
   （3）数据库设计中有很多范式，只是比较经典的是三大范式；

6.5.1 三大范式：

1)要求表中的字段是不可再分割的；
   违反情况举例：用户（用户id,用户名+密码），这使得字段含义不够量子化；
注：所谓的不可再分割，得根据山下文来确定：
   如一般的地址用一个字段来表示，一般是这样的：XX省XX市XX乡XX村
   而快递中用的地址使用四个字段来表示：XX省 XX市 XX乡 XX村

2）要求表中的字段，和该表的主键有直接的关系，而不是间接的一种关系；
   违反情况举例：以考试为记录，则主键为某次考试，而在其字段中我们只应该记录本次考试监考老师的id;而不应该记录监考老师的其他信息。

3）要求表中的字段，和该表的完整主键（一般是说复合主键的情况）有关系，而不是部分主键有关系；
结合用户故事为每个实体和关系找属性（每张表里有哪些列和字段）；
总结：（1）三大范式的目标，旨在消除数据的冗余，其优点为提升了查询的便捷性，其缺点为提升了增、删、改的复杂性；
      （2）为了通过添加适当的冗余，提升查询的便捷性，此时就会有意识的破坏范式。

6.5.2 确定属性：

以上面的博客系统为例：可以找出以下列和字段：

用户：主键（用户id）、用户名，密码；
文章：主键（文章id）、标题、正文
点赞：主键（点赞id或者复合主键（点赞的id+被点赞的文章id）） 一个人针对一篇文章只能点赞一次
评论：主键（评论id）、评论了什么、什么时候评论的
…

6. 6 为每个属性确定类型+约束

可以根据优化需求，适当调整结果。

6. 7完成建表

通过workbench建好表，复制出来；

建完表后，如何知道自己设计的表好不好？
（1）自己写出来的故事能不能写出SQL来，要是写不出来，要么是故事写的不好，要么需要再细化，要么就是表设计的不好；
（2）检查下数据冗余是否很厉害；