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、索引

• 索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构
• 大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升

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', '[e-mail]');-- 创建保留点 updateA
SAVEPOINT updateA;-- 插入操作 B
INSERT INTO `user`
VALUES (2, 'root2', 'root2', '[e-mail]');-- 回滚到保留点 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：只要其中一个表中存在匹配，则返回行；