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

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：将记录分组到汇总行中，并为每个组返回一个记录，可以按一列或多列进行分组

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
• TRANSACTION 语句处。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、事务特性

• 原子性：指事务包含的所有操作要么全部成功，要么全部失败回滚。
• 一致性：指一个事务执行之前和执行之后都必须处于一致性状态。比如a与b账户共有1000块，两人之间转账之后无论成功还是失败，它们的账户总和还是1000。
• 隔离性：跟隔离级别相关，如read committed，一个事务只能读到已经提交的修改。
• 持久性：指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作

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 条数据