Mysql笔记05：库表设计(API变形)

库表设计步骤：

1.整理需求

2.E-R 图（实体关系图）做辅助

E（entry）实体，设计实体就像定义一个类一样，指定从哪些方面描述对象，一个实体转换为数据库中的一个表。
R（relationship）关系，关系描述两个实体之间的对应规则，关系的类型包括包括一对一、一对多、多对多。

参考博客：E-R图

关系也是一种数据，需要通过一个字段存储在表中:
1) 实体A对实体B为1对1，则在表A或表B中创建一个字段，存储另一个表的主键值。
2) 实体A对实体B为1对多：在表B中创建一个字段，存储表A的主键值。
3) 实体A对实体B为多对多：新建一张表C，这个表只有两个字段，一个用于存储A的主键值，一个用于存储B的主键值。

参考博客：多对多处理

3.根据需求填写字段（字段类型、约束关系）

4.数据库设计的三大范式（减少数据存储的冗余度）

a) 表中的每个字段都是原子的，即每个字段不可再分。
字段是否可以再分
表：【联系人】（姓名，性别，电话） 如果在实际场景中，一个联系人有家庭电话和公司电话，那么这种表结构设计就没有达到 1NF。要符合 1NF 我们只需把列（电话）拆分，即：【联系人】（姓名，性别，家庭电话，公司电话）。1NF 很好辨别，但是 2NF 和 3NF 就容易搞混淆。

b) 有主键，每个字段应该是和主键有完整的依赖关系（没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分）
表是否可以再分

订单明细表：【OrderDetail】（OrderID，ProductID，UnitPrice，Discount，Quantity，ProductName）。 因为我们知道在一个订单中可以订购多种产品，所以单单一个 OrderID 是不足以成为主键的，主键应该是（OrderID，ProductID）。显而易见 Discount（折扣），Quantity（数量）完全依赖（取决）于主键（OderID，ProductID），而 UnitPrice，ProductName 只依赖于 ProductID。所以 OrderDetail 表不符合 2NF。不符合 2NF 的设计容易产生冗余数据。可以把【OrderDetail】表拆分为【OrderDetail】（OrderID，ProductID，Discount，Quantity）和【Product】（ProductID，UnitPrice，ProductName）来消除原订单表中UnitPrice，ProductName多次重复的情况。

c) 有主键，每个字段应该是和主键呈现直接关系，而不是间接关系。 即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。
表是否可以再分 (和第二范式比较类似，只不过第三范式是指对非主键的依赖，第二范式是指对主键（多个主键）的部分依赖)
一个订单表【Order】（OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity）主键是（OrderID）。 其中 OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity 等非主键列都完全依赖于主键（OrderID），所以符合 2NF。不过问题是 CustomerName，CustomerAddr，CustomerCity 直接依赖的是 CustomerID（非主键列），而不是直接依赖于主键，它是通过传递才依赖于主键，所以不符合 3NF。 通过拆分【Order】为【Order】（OrderID，OrderDate，CustomerID）和【Customer】（CustomerID，CustomerName，CustomerAddr，CustomerCity）从而达到 3NF。

注意：

• 第二范式（2NF）和第三范式（3NF）的概念很容易混淆，区分它们的关键点在于，2NF：非主键列是否完全依赖于主键，还是依赖于主键的一部分；3NF：非主键列是直接依赖于主键，还是直接依赖于非主键列。
• 有时候为了避免关联查询，可以违反第三范式

5. 生成建表语句

JDBC API 变形

1 Statement 变成 PrepareStatement（提高运行效率，防止SQL注入）

SQL 注入：用户输入特殊字符导致SQL不是按照作者期望的方式运行：
比如输入用户名: ‘ OR 1 = 1 OR ‘。

使用PrepareStatement防止sql注入：

String sql = "SELECT id, username FROM users WHERE username = ? AND password = ?";
try (PreparedStatement statement = con.prepareStatement(sql)) {
    下标从 1 开始
    statement.setString(1, username);
    statement.setString(2, password);
    
    MySQL Driver 打印 SQL 语句的小技巧
    com.mysql.jdbc.PreparedStatement mysqlStatement = (com.mysql.jdbc.PreparedStatement) statement;
    System.out.println(mysqlStatement.asSql());

    try (ResultSet resultSet = statement.executeQuery()) {
        if (!resultSet.next()) {
            System.out.println("登录失败");
        } else {
            int id = resultSet.getInt("id");
            String usernameInTable = resultSet.getString("username");
            System.out.println("登录成功: " + id + ", " + usernameInTable);
        }
    }
}

2 获取连接的方式

DriverManager.getConnection();弊端: 每次获取连接，都是一次网络请求，用完之后马上销毁，导致效率很低

DataSource：使用方式简单，可以带有连接池功能（建议但不强制）

连接池： 池化技术，提升效率

3 获取插入的自增 id

PreparedStatement statement = 
con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS)

ResultSet rs = statement.getGeneratedKeys()
rs.next();
rs.getInt(1);

4 总结（DataSource + PrepareStatement）

String sql = "SELECT id, username, password FROM users LIMIT ?";
MysqlDataSource mysqlDataSource = new MysqlDataSource();
mysqlDataSource.setServerName("127.0.0.1");
mysqlDataSource.setPort(3306);
mysqlDataSource.setUser("root");
mysqlDataSource.setPassword("");
mysqlDataSource.setDatabaseName("java20_0211");
mysqlDataSource.setUseSSL(false);
mysqlDataSource.setCharacterEncoding("utf8");
DataSource dataSource = mysqlDataSource;

try (Connection con = dataSource.getConnection()) {
    try (PreparedStatement statement = con.prepareStatement(sql)) {
        statement.setInt(1, 3);
        try (ResultSet rs = statement.executeQuery()) {
            while (rs.next()) {
                System.out.println(rs.getInt("id"));
                System.out.println(rs.getString("username"));
                System.out.println(rs.getString("password"));
            }
        }
    }
}

注意
Try-with-resource: 带着 Closable 的类都可以用

• 对象的创建
• 对象的使用
• 对象的关闭 close()

用于： Connection、Statement、PrepareStatement、ResultSet
InputStream、OutputStream

Try (对象的创建) {
对象的使用
} // 隐含着 对象的关闭，自动执行 对象.close()

小结
1. 库表设计（重点） 需求分析 -> E-R 辅助 -> 三大范式

2. JDBC 的使用套路

a)注册 Driver
b)获取 Connection
c)获取 Statement
d)执行 SQL
e)关闭资源

3. JDBC 的变形

a)Try-with-resource: 代码简单点
b)Statement 换成了 PrepareStatement
i.前置知识：SQL 注入
ii.预编译，速度快；防止 SQL 注入；代码简单点
c)DriverManager 换成了 DataSource
i.可以出现连接池
ii.代码简单点
d)INSERT 之后获取自增的 id

4. 复杂查询

a)聚合查询
i.GROUP BY
ii.HAVING
b)联表查询（关联查询）
i.笛卡尔积/结果的筛选