Java知识复习（十三）数据库和SQL

奥妙无穹

已于 2023-03-15 17:23:38 修改

阅读量855

点赞数 1

分类专栏： Java一阶知识点总结文章标签：数据库 java sql

于 2023-03-06 17:14:18 首次发布

本文链接：https://blog.csdn.net/qq_44648936/article/details/129365516

版权

Java一阶知识点总结专栏收录该内容

21 篇文章 5 订阅

订阅专栏

1、主键和外键

主键也叫主码。主键用于唯一标识一个元组，不能有重复，不允许为空。一个表只能有一个主键。
外键也叫外码。外键用来和其他表建立联系用，外键是另一表的主键，外键是可以有重复的，可以是空值。一个表可以有多个外键

2、ER图

ER 图全称是 Entity Relationship Diagram（实体联系图），提供了表示实体类型、属性和联系的方法

实体：通常是现实世界的业务对象，当然使用一些逻辑对象也可以。比如对于一个校园管理系统，会涉及学生、教师、课程、班级等等实体。在 ER 图中，实体使用矩形框表示
属性：即某个实体拥有的属性，属性用来描述组成实体的要素，对于产品设计来说可以理解为字段。在 ER 图中，属性使用椭圆形表示
联系：即实体与实体之间的关系，这个关系不仅有业务关联关系，还能通过数字表示实体之间的数量对照关系。例如，一个班级会有多个学生就是一种实体间的联系

3、数据库有哪三种范式？

1NF(第一范式)：属性不可再分，是所有关系型数据库的最基本要求
2NF(第二范式)：在 1NF 的基础之上，消除了非主属性对于码的部分函数依赖。即其他非主属性必须完全依赖于主键，而不能只是依赖于主键的一部分（主键可以不止一个）
3NF(第三范式)：3NF 在 2NF 的基础之上，消除了非主属性对于码的传递函数依赖。

4、函数依赖

函数依赖：简单来说就是，两个属性一一对应，知其一必知另一个
部分函数依赖：简单来说就是一个主键对应一堆信息，比如知道学号肯定知道人名，但知道人名不一定能知道学号
完全函数依赖：简单来说就是必须知道一组属性才能对应身份，比如假设一个班的人不能重名，那我们知道班级和姓名就能找到这个人，但只知道班级或姓名就不能确定人
传递函数依赖：简单来说就是x确定y，y确定z，比如知道学号就可以知道系名，知道系名就知道系主任，它们完全可以合并到一个表里

5、SQL中的增删改查CRUD

INSERT INTO 语句用于向表中插入新记录
UPDATE 语句用于更新表中的记录
DELETE 语句用于删除表中的记录
TRUNCATE TABLE 可以清空表，也就是删除所有行
SELECT 语句用于从数据库中查询数据
DISTINCT 用于返回唯一不同的值。简单来说就是去重。
LIMIT 限制返回的行数。可以有两个参数，第一个参数为起始行，从 0 开始；第二个参数为返回的总行数
ORDER BY 用于对结果集按照一个列或者多个列进行排序。默认按照升序对记录进行排序
ASC ：升序（默认）
DESC ：降序

SELECT * FROM products
ORDER BY prod_price DESC, prod_name ASC;

GROUP BY：将记录分组到汇总行中，并为每个组返回一个记录，可以按一列或多列进行分组。我的理解是类似于分组后变成了主键，如果group by后面不止一个参数，那么就是多个主键，其他属性完全函数依赖于他们，只有多个主键都一一相等时才能对应一列字段

SELECT cust_name, COUNT(cust_address) AS addr_num
FROM Customers GROUP BY cust_name
ORDER BY cust_name DESC;

HAVING 用于对汇总的 group by 结果进行过滤，必须要与 group by 连用

SELECT cust_name, COUNT(*) AS num
FROM Customers
WHERE cust_email IS NOT NULL
GROUP BY cust_name
HAVING COUNT(*) >= 1;

WHERE 子句用于过滤记录，即缩小访问数据的范围。可以使用运算符例如：

BETWEEN	在某个范围内
LIKE	搜索某种模式
IN		指定针对某个列的多个可能值

LIKE 操作符在 WHERE 子句中使用，作用是确定字符串是否匹配模式，LIKE 支持两个通配符匹配选项：% 和 _。

% 	表示任何字符出现任意次数。
_ 	表示任何字符出现一次

JOIN 子句用于将两个或者多个表联合起来进行查询，ON表示连接的条件

select c.cust_name, o.order_num
from Customers c
inner join Orders o
on c.cust_id = o.cust_id
order by c.cust_name;

UNION 运算符将两个或更多查询的结果组合起来，并生成一个结果集，其中包含来自 UNION 中参与查询的提取行（没怎么用过）

6、where和having的区别

where：过滤过滤指定的行，后面不能加聚合函数（分组函数）
having：过滤分组，必须要与 group by 连用，不能单独使用

7、日期和时间

日期格式：YYYY-MM-DD
时间格式：HH:MM:SS

8、索引

1、概念

索引是存储引擎用于提高数据库表的访问速度的一种数据结构，其本质可以看成是一种排序好的数据结构。它可以比作一本字典的目录，可以帮你快速找到对应的记录
大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升
建立索引需要占用物理空间，并且会降低表的增删改的效率，因为每次对表记录进行增删改，需要进行动态维护索引，导致增删改时间变长

2、索引的数据结构

B+树索引：基于B 树和叶子节点顺序访问指针进行实现，它具有B树的平衡性，并且通过顺序访问指针来提高区间查询的性能
哈希索引：哈希索引是基于哈希表实现的，对于每一行数据，存储引擎会对索引列进行哈希计算得到哈希码，并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的，将哈希码的值作为哈希表的key值，将指向数据行的指针作为哈希表的value值。这样查找一个数据的时间复杂度就是O(1)，一般多用于精确查找

9、约束类型

SQL 约束用于规定表中的数据规则。如果存在违反约束的数据行为，行为会被约束终止，通常在创建表时规定
NOT NULL - 指示某列不能存储 NULL 值。
UNIQUE - 保证某列的每行必须有唯一的值。
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合，主键
FOREIGN KEY - 外键
CHECK - 保证列中的值符合指定的条件。
DEFAULT - 规定没有给列赋值时的默认值。

10、事务处理

MySQL 默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交
当出现 START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交

11、指令

START TRANSACTION - 指令用于标记事务的起始点。
SAVEPOINT - 指令用于创建保留点。
ROLLBACK TO - 指令用于回滚到指定的保留点；如果没有设置保留点，则回退到 START
COMMIT - 提交事务

-- 开始事务
START TRANSACTION;

-- 插入操作 A
INSERT INTO `user`
VALUES (1, 'root1', 'root1', 'xxxx@163.com');

-- 创建保留点 updateA
SAVEPOINT updateA;

-- 插入操作 B
INSERT INTO `user`
VALUES (2, 'root2', 'root2', 'xxxx@163.com');

-- 回滚到保留点 updateA
ROLLBACK TO updateA;

-- 提交事务，只有操作 A 生效
COMMIT;
------

12、权限控制

要授予用户帐户权限，可以用GRANT命令。有撤销用户的权限，可以用REVOKE命令

-- 授予权限
GRANT privilege,[privilege],.. ON privilege_level
TO user [IDENTIFIED BY password]
[REQUIRE tsl_option]
[WITH [GRANT_OPTION | resource_option]];

-- 撤销权限
REVOKE   privilege_type [(column_list)]
        [, priv_type [(column_list)]]...
ON [object_type] privilege_level
FROM user [, user]...

13、触发器

触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行。
我们可以使用触发器来进行审计跟踪，把修改记录到另外一张表中
CREATE TRIGGER 指令用于创建触发器

14、四种JOIN的区别

这张图描述了left join（左连接）、right join（右连接）、inner join（内连接）、outer join（外连接）相关的7种用法
在这里插入图片描述

1、INNER JOIN：如果表中有至少一个匹配，则返回行，即交集；这个是默认的，单独的JOIN就表示INNER JOIN
2、LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行；
3、RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行；
4、FULL JOIN：只要其中一个表中存在匹配，则返回行，即并集；也就是先遍历一遍左表，如果有匹配的就输出查询的信息，没匹配到的就输出NULL，然后再遍历一遍右表，同样是有匹配输出，没匹配NULL

15、事务特性

原子性(atomicity)：指事务包含的所有操作要么全部成功，要么全部失败回滚。
一致性(consistency)：指一个事务执行之前和执行之后都必须处于一致性状态。当失败时，要对前面的操作进行回滚，无论是否成功。
隔离性(isolation)：当多个事务操作一个数据时，为防止数据损坏，需要将每个事务进行隔离，互不干扰。具体效果跟隔离级别相关，如read committed，一个事务只能读到已经提交的修改。
持久性(durability)：指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作

注意：所谓一致性是数据库处理前后结果应与其所抽象的客观世界中真实状况保持一致。这种一致性是一种需要管理员去定义的规则。管理员如何指定规则，数据库就严格按照这种规则去处理数据。

16、事务隔离级别

事务的隔离级别：指的是一个事务对数据的修改与另一个并行的事务的隔离程度，当多个事务同时访问相同数据时，如果没有采取必要的隔离机制，就可能发生以下问题

脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。
不可重复读是指在对于数据库中的某行记录，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，另一个事务修改了数据并提交了。
幻读是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录。对幻读的正确理解是一个事务内的读取操作的结论不能支撑之后业务的执行。假设事务要新增一条记录，主键为id，在新增之前执行了select，没有发现id为xxx的记录，但插入时出现主键冲突，这就属于幻读，读取不到记录却发现主键冲突是因为记录实际上已经被其他的事务插入了，但当前事务不可见。
注意：不可重复读注重的是修改，即对不对的问题，而幻读注重的是新增或删除，即有没有的问题

MySQL数据库为我们提供的四种隔离级别：

Serializable (串行化)：通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。（最高隔离级别）
Repeatable read(可重复读)：MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行，解决了不可重复读的问题。
Read committed (读已提交)：一个事务只能看见已经提交事务所做的改变。可避免脏读的发生。
Read uncommitted (读未提交)：所有事务都可以看到其他未提交事务的执行结果。

17、limit的用法

limit y 分句表示: 读取 y 条数据
limit x, y 分句表示: 跳过 x 条数据，读取 y 条数据
limit y offset x 分句表示: 跳过 x 条数据，读取 y 条数据

18、TIMESTAMPDIFF（timestampdiff）函数

语法：TIMESTAMPDIFF(unit,begin,end);
作用：计算时间差，TIMESTAMPDIFF函数返回end-begin的结果，其中begin和end是DATE或DATETIME表达式。

mysql> SELECT TIMESTAMPDIFF(MONTH, '2018-01-01', '2018-06-01') result;

+--------+
| result |
+--------+
|      5 |
+--------+
# 即月份值相减

unit参数是确定(end-begin)的结果的单位，常用有效单位例如：MICROSECOND（ms）、SECOND（s）、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR

19、MySQL常用函数

AVG(expression) 返回一个表达式的平均值，expression 是一个字段
MAX(expression)返回字段 expression 中的最大值
MIN(expression)返回字段 expression 中的最大值
SUM(expression)返回指定字段的总和
CONCAT(s1,s2…sn)字符串 s1,s2 等多个字符串合并为一个字符串
FORMAT(x,n)函数可以将数字 x 进行格式化 “#,###.##”, 将 x 保留到小数点后 n 位，最后一位四舍五入
TRIM(s)去掉字符串 s 开始和结尾处的空格
REVERSE(s)将字符串s的顺序反过来
CURDATE()/CURRENT_DATE()返回当前日期
CURRENT_TIME()/CURTIME()返回当前时间
CURRENT_TIMESTAMP()返回当前日期和时间
DAY(d)返回日期值 d 的日期部分（类似的还有MONTH和YEAR）
DAYOFYEAR(d)计算日期 d 是本年的第几天（类似的还有WEEK和MONTH）
DAYNAME(d)返回日期 d 是星期几，如 Monday,Tuesday

20、判断语句

case：多条件判断（类似于switch）

SELECT username,(CASE sex  WHEN 1 THEN '男'  WHEN 2 THEN  '女'  ELSE '未知' END) as sex FROM user;

if：语法格式：IF(condition,A,B)，真为A，假为B

select username,if(sex=1,'男','女') as sex from user;

elt：本质是字符串操作，作用是返回在参数列表中指定的索引号处的字符串。语法格式：ELT(N,str1,str2,str3,…)，如果 N = 1，返回 str1，如果N = 2，返回 str2，等等。如果 N 小于 1 或大于参数的数量，返回 NULL。

SELECT username,ELT(sex,'男','女','未知') as sex FROM user

21、MySQL中的is和=的区别

is可以测试一个布尔值（true/false/null)
is 一般情况下和 null 连用，比较该字段的值是否为空(is null / is not null)
= 用在2种情况下：一是判断值是否相等 where id=1；二是用于赋值set id=1

22、in和not in的使用

IN 运算符用来判断表达式的值是否位于给出的列表中；如果是，返回值为 1，否则返回值为 0。
NOT IN 的作用和 IN 恰好相反，NOT IN 用来判断表达式的值是否不存在于给出的列表中；如果不是，返回值为 1，否则返回值为 0。

# 例如以下SQL语句，用于查找列表中客户的推荐人的编号都不是 2 的客户
select name from customer 
where id not in (select id from customer where referee_id=2)

23、update的使用

注意 UPDATE 语句中的 WHERE 子句。 WHERE 子句指定应该更新哪些记录。如果省略WHERE子句，表中的所有记录都会更新！

# 格式如下
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

24、为什么要sql优化？

优化成本：硬件>系统配置>数据库表结构>SQL及索引。
优化效果：硬件<系统配置<数据库表结构<SQL及索引。
SQL优化在提升系统性能中是成本最低 && 优化效果最明显的途径

25、sql优化的原理

最大化利用索引；
尽可能避免全表扫描；
减少无效数据的查询；

26、sql优化的部分具体方式

1、避免不走索引进行全表扫描的场景

尽量避免使用in 和not in，如果是连续数值，可以用between；代替如果是子查询，可以用exists代替
尽量避免使用 or，可以用union代替or
尽量避免进行null值的判断，可以给字段添加默认值0，对0值进行判断
尽量避免在where条件中等号的左侧进行表达式、函数操作，例如SELECT * FROM T WHERE score/10 = 9
查询条件不能用 <> 或者 !=

2、select语句优化

避免出现select *，用指定列名取代
多表关联查询时，小表在前，大表在后
用where字句替换HAVING字句

3、其他

Sql语句全部大写，特别是列名大写，因为数据库的机制是这样的，sql语句发送到数据库服务器，数据库首先就会把sql编译成大写在执行，如果一开始就编译成大写就不需要了把sql编译成大写这个步骤了

27、SQL的执行顺序

FROM：将数据从硬盘加载到数据缓冲区，方便对接下来的数据进行操作。
ON：join on实现多表连接查询，推荐该种方式进行多表查询，不使用子查询。
JOIN（如right left 右连接-------从右边表中读取某个元组，并且找到该元组在左边表中对应的元组或元组集）
WHERE：从基表或视图中选择满足条件的元组。（不能使用聚合函数）
GROUP BY：分组，一般和聚合函数一起使用。
HAVING：在元组的基础上进行筛选，选出符合条件的元组。（一般与GROUP BY进行连用）
SELECT：查询到得所有元组需要罗列的哪些列。
DISTINCT：去重的功能。
UNION：将多个查询结果合并（默认去掉重复的记录）。
ORDER BY：进行相应的排序。
LIMIT 1：显示输出一条数据记录（元组）

28、SQL的书写顺序

select >> from >> join >> on >> where >> group by >> having >> order by >> limit

29、一次完整的JDBC操作流程

使用JDBC的步骤：加载JDBC驱动程序 → 建立数据库连接Connection → 创建执行SQL的语句Statement → 处理执行结果ResultSet → 释放资源

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

public class DbUtil {

    public static final String URL = "jdbc:mysql://localhost:3306/imooc";
    public static final String USER = "liulx";
    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.操作数据库，实现增删改查
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT user_name, age FROM imooc_goddess");
        //如果有数据，rs.next()返回true
        while(rs.next()){
            System.out.println(rs.getString("user_name")+" 年龄："+rs.getInt("age"));
        }
    }
}

30、通过JDBC提交事务

// 参数：
// jdbc协议:postgresql子协议://主机地址:数据库端口号/要连接的数据库名
String url = "jdbc:postgresql://localhost:5432/test2";
// 数据库用户名
String user = "postgres";
// 数据库密码
String password = "123456";

// 1. 加载Driver类，Driver类对象将自动被注册到DriverManager类中
Class.forName("org.postgresql.Driver");

// 2. 连接数据库，返回连接对象
Connection conn = DriverManager.getConnection(url, user, password);

PreparedStatement pstmt1 = null;
PreparedStatement pstmt2 = null;
try {
    // 3. 关闭事务的自动提交机制
    conn.setAutoCommit(false);
    
    // 4. 预编译SQL
    // 从张三账户中减去100元
    String sql1 = "UPDATE bank SET money = money - 100 WHERE name = ?";	
    //prepareStatement预编译sql，减少sql执行
    pstmt1 = conn.prepareStatement(sql1);
    pstmt1.setString(1, "张三");
       // 往李四账户中增加100元
    String sql2 = "UPDATE bank SET money = money + 100 WHERE name = ?";
    pstmt2 = conn.prepareStatement(sql2);
    pstmt2.setString(1, "李四");
    
    // 5. 执行SQL语句
    pstmt1.executeUpdate();
    pstmt2.executeUpdate();
} catch (Exception e) {
    // 6. 如果发生异常，则回滚事务
    conn.rollback();
} finally {
    // 6. 如果没有发生异常，则提交事务
    conn.commit();
    
    // 7. 关闭资源
    pstmt1.close();
    pstmt2.close();
    conn.close();
}

31、JDBC中常用的接口和方法

Driver接口：在编程中要连接数据库，必须先装载特定厂商的数据库驱动程序，不同的数据库有不同的装载方法

装载MySql驱动：Class.forName(“com.mysql.jdbc.Driver”);
装载Oracle驱动：Class.forName(“oracle.jdbc.driver.OracleDriver”);

Connection接口：Connection与特定数据库的连接（会话），在连接上下文中执行sql语句并返回结果

createStatement()：创建向数据库发送sql的statement对象。
prepareStatement(sql) ：创建向数据库发送预编译sql的PrepareSatement对象。
prepareCall(sql)：创建执行存储过程的callableStatement对象。
setAutoCommit(boolean autoCommit)：设置事务是否自动提交。
commit() ：在链接上提交事务。
rollback() ：在此链接上回滚事务。
连接MySql数据库：Connection conn = DriverManager.getConnection(“jdbc:mysql://host:port/database”, “user”, “password”);

Statement接口：用于执行静态SQL语句并返回它所生成结果的对象。

execute(String sql):运行语句，返回是否有结果集
executeQuery(String sql)：运行select语句，返回ResultSet结果集。
executeUpdate(String sql)：运行insert/update/delete操作，返回更新的行数。
addBatch(String sql) ：把多条sql语句放到一个批处理中。
executeBatch()：向数据库发送一批sql语句执行。

有三种Statement类

Statement：由createStatement创建，用于发送简单的SQL语句（不带参数）。
PreparedStatement ：继承自Statement接口，由preparedStatement创建，用于发送含有一个或多个参数的SQL语句。PreparedStatement对象比Statement对象的效率更高，并且可以防止SQL注入，所以我们一般都使用PreparedStatement。
CallableStatement：继承自PreparedStatement接口，由方法prepareCall创建，用于调用存储过程。

ResultSet接口：ResultSet提供检索不同类型字段的方法