sql的准备

• SQL的应用场景是关系型数据库，比如我们常用的Oracle、MySQL，这些就是关系型数据库。但是为什么它会被叫做关系型数据库，而不是表结构数据库呢？
  因为在数据库当中，关系要比表结构更重要。表结构只是一种形式，而数据库当中核心的设计理念其实是关系。这也是为什么我们学习数据库的时候都需要从ER图开始，而不是上来就讲数据库使用的方法，或者是SQL语言的细节。
• 如果是目的是为了面试（这里指大数据开发岗位）的话，先别急着sql。主要先去理解：“Hive 是如何实现将 SQL 生成 MapReduce 可执行代码”，需要去理解 map端和reduce端的两个join；接着去了解下hive的四种数据模型，其中table(内外部表区别)，partition（分区分桶区别）重点掌握；最后再扩展下和Hbase的对比。最主要的是和前面的mapreduce串起来，融入hadoop体系里；
• 如果是为了工作用的话，就直接磕SQL题目吧，另外就是把hive遇到数据倾斜的情况准备下；mysql 5.1官方中文文档。

文章目录

• sql的准备
• 0.数据库和其他课程关系
• 1. DB和DBMS
• • （1）数据库Database（DB）
  • （2）数据库管理系统DBMS：
  • • DBMS的主要功能
• 2.概念设计中的数据描述
• 3.主键、外键、索引
• • A.背景
  • B.分析：
  • C.总结
  • D.索引和主键
• 4.逻辑设计中的数据描述
• 5.数据模型
• • • 层次模型
    • 网状模型
• 6.三大逻辑数据模型
• 7.概念模型
• 8.关系模型
• 9.关键码
• 10.SQL的特点：
• 11.SQL语句完成核心功能的9个动词
• 12.SQL的组成
• 13.软件生存期
• 14.数据库的管理
• 15.
• 16.关系的规范性限制
• 17.关系模型的3类完整性规则
• 18.启发式优化算法
• 19.关系模式的外延和内涵
• 20.关系模式的范式：
• • （1）第一范式：
  • （2）第二范式：
  • • 2NF的栗子
  • （3）第三范式：
  • • 3NF的栗子
  • （4）BCNF：
  • 范式之间的区别
• 21.用过的数据库
• 22.关系模式的分解：
• 23.三级模式&两级映像
• • 三级模式
  • 两级映像
• 24.数据库设计的步骤
• 25.数据库的独立性
• 26.数据库的恢复技术
• 27.视图&基本表
• • （1）视图：
  • （2）基本表
• 28.封锁协议
• 29.数据库运行中产生的故障分类
• 30.数据库系统相比文件系统的优点
• 31.并发执行不加控制后果
• 32.E-R图
• 33.死锁&活锁
• 34.数据库的安全性控制
• 35.索引的分类&优缺点
• 36.查询优化
• • 简述查询优化中的启发式规则
• 37.事务隔离分几种
• 38.索引
• 39.redis的可持久化
• • （1）RDB持久化
  • （2）AOF持久化
• reference

0.数据库和其他课程关系

1. DB和DBMS

（1）数据库Database（DB）

是长期存储在计算机内、有组织、同一管理的相关数据的集合。
特点：
能为各种用户共享、
具有较小冗余度、
数据间联系紧密又有较高的数据独立性。

（2）数据库管理系统DBMS：

是位于用户和操作系统OS之间的一层数据管理软件，
为用户或应用程序提供访问DB的方法——DB的建立、查询、更新及各种数据控制。
DBMS基于某种数据模型，可分为层次型、网状型、关系型和面向对象型。

DBMS的主要功能

数据库的定义、操纵、保护、维护功能，数据字典。

2.概念设计中的数据描述

实体：客观存在、可以相互区别的事物。
实体集：性质相同的同类实体的集合。

属性：实体能有很多特性，一个特性称为一个属性。
实体标志符：能唯一标识实体的属性或属性集。有时也称为关健码key或键。

3.主键、外键、索引

A.背景

（1）学生表(学号，姓名，性别，班级)
其中每个学生的学号是唯一的，学号就是一个主键

（2）课程表(课程编号,课程名,学分)
其中课程编号是唯一的,课程编号就是一个主键

（3）成绩表(学号,课程号,成绩)
成绩表中单一一个属性无法唯一标识一条记录，学号和课程号的组合才可以唯一标识一条记录，所以学号和课程号的属性组是一个主键

B.分析：

（1）成绩表中的学号不是成绩表的主键，但它和学生表中的学号相对应，并且学生表中的学号是学生表的主键，则称成绩表中的学号是学生表的外键

（2）成绩表中的课程号是课程表的外键

C.总结

（1）主键是能确定一条记录的唯一标识。
（2）外键用于与另一张表的关联。能确定另一张表记录的字段，用于保持数据的一致性。
——如A表中的一个字段，是B表的主键，则该字段是A表的外键。
一个表可以有多个外键。

D.索引和主键

• 主键唯一标识每条记录，而索引时用来提高查询效率的
• 主键是数据库的一部分，而索引是一个独立的数据结构，主键通常会自动创建一个索引，但索引不一定是主键；过多的索引可能会影响数据库的性能

-- 指定某列为主键，如ID
CREATE TABLE students (
    ID INT NOT NULL PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

-- 创建索引
CREATE INDEX idx_name ON students (name);

4.逻辑设计中的数据描述

（1）字段：
标识实体属性的命名单位。
是可以命名的最小信息单位，又称为数据元素或初等项。

（2）记录：
字段的有序集合。

（3）文件：
同一类记录的集合。

（4）关键码：
能唯一标识文件中每条记录的字段或字段集。

5.数据模型

数据模型所描述的内容包括三个部分：数据结构、数据操作、数据约束。

根据数据抽象的界别，将数据模型分为2大类（如下图所示）：
（1）概念模型：表达用户需求观点的数据全局逻辑结构的模型
（2）逻辑模型和物理模型：
逻辑模型：表达计算机实现观点的DB全局逻辑结构的模型
——包括层次、网状、关系、面向对象数据模型等，主要数据库管理系统的实现。

物理模型：对数据最底层的抽象，
——描述数据在系统内部的表示方法和存取方法，或在磁盘上的存储方式，是面向计算机系统的。

层次模型

数据库系统中最早出现的数据模型，采用层次模型作为数据库的组织方式。
用树形结构来表示各类实体以及实体间的联系。

网状模型

一种比层次模型更具有普遍性的结构，去掉了层次模型的两个限制，允许一个以上的节点无双亲，一个节点可由多个双亲节点。因此，网状模型能更直接的描述现实世界，层次模型只是网状模型的一个特例

6.三大逻辑数据模型

（1）关系模型：表的形式组织数据，关系模型的模式如下：

学生登记表( 学号 char(8), 姓名
char(10), 性别 Char(2), 出生年月
datetime, 入学日期 Datetime, 家庭住址
Char(40) )

（2）层次模型：树的形式组织数据
（3）网状模型：图的形式组织数据

7.概念模型

抽象级别最高，特点如下：
（1）表达了数据的整体逻辑结构
（2）独立于硬件和软件
（3）概念模型主要是实体联系（ER）模型。ER有3个基本成分
a.矩形框：用于表示实体类型（考虑问题的对象）
b.菱形框：用于表示联系类型（实体间联系）
c.椭圆形框：用于表示实体类型和联系类型的属性

相应的命名均记入各种框中。
对于实体标识符的属性，在属性名下画一条横线。实体与属性之间用直线连接；
联系类型与其涉及的实体类型之间也可以直线相连，用来表示它们之间的联系，并在直线端部标注联系的类型（1:1、1:N或M:N）。

8.关系模型

定义：用二维表格表示实体集，用关键码表示实体之间联系的数据模型

学号	姓名	年龄	性别
202101	小郭	1	男
202102	小白	2	女
202103	小猪	3	男
（1）元组：表中的一行
（2）属性：表中的一列即为属性，如学号为属性名
（3）码：即码键，即第3点的主键。表中的某个属性组，能唯一确定一个元组，如上表中的属性学号可以唯一确定一个学生，即学号为本关系（表）中的码。
（4）关系模式：对应关系的描述，一般表示为关系名（属性1，属性2,），如上表的关系的可描述为：学生（学号、姓名、年龄、姓名）

9.关键码

关键码有一个或多个属性组成。
（1）超键（super key）：在关系中能唯一标识元组的属性或属性集
（2）候选键（candidate key）：不含多余属性的超键
（3）主键（primary key）：用户选作元组标识的候选键
（4）外键（foreign key）：如果模式R中属性K是其他模式的主键，则K在模式R中称为外键

10.SQL的特点：

（1）SQL具有十分灵活和强大的查询功能，其SELECT语句能够完成相当复杂的查询操作，包括各种关系代数、统计、排序等操作；
（2）SQL不是一个应用开发语言，它只提供对数据库的操作功能，不能完成屏幕控制、菜单管理、报表生成等功能。但SQL既可作为交互式语言独立使用，也可作为子语言嵌入在主语言中使用，称为应用开发语言的一部分。
（3）SQL是国际标准语言，有利于各种数据库之间交换数据，有利于程序的移植，有利于实现高度的数据独立性，有利于实现标准化
（4）SQL的词汇不多，完成核心功能只用了9个英语动词（见下个），它的语法结构接近英语，因此容易学习和使用。
（5）SQL的组成部分：
数据库查询语言DQL，数据库操作语言DML，事务处理语言TPL，数据库控制语言DCL，数据定义语言DDL，指针控制语言CCL。

11.SQL语句完成核心功能的9个动词

数据查询：SELECT （查询出数据，也可用于变量赋值）
数据定义(表/视图/查询/存储过程/自定义函数/索引/触发器等)：CREATE (创建)、DROP(删除)、ALTER(修改)
数据操作：INSERT（插入）、UPDATE（更新）、DELETE（删除）
数据控制：Grant（授权）、revoke（回收权限）

12.SQL的组成

（1）数据定义语言，即SQL DDL，用于定义SQL模式、基本表、视图、索引等结构。
（2）数据操纵语言，即SQL DML，数据操作分成数据查询和数据更新两类。
（3）嵌入式SQL语言的使用规定
（4）数据控制语言，即SQL DCL，这一部分包括对基本表和视图的授权、完整性规则的描述、事务控制等内容。

13.软件生存期

软件开发和运营的全过程

14.数据库的管理

（1）数据库的管理：
在DBS运行时，DBMS要对DB进行监控
——以保证整个系统的正常运转，防止数据意外丢失和不一致数据的产生。

（2）实现途径：
数据库的回复、并发控制、完整性控制和安全性控制。

（3）每一方面构成了DBMS的一个子系统。
DBS运行的最小逻辑工作单位是“事务”，所以对数据库的操作，都要以事务作为一个整体单位来执行或撤销。

15.

16.关系的规范性限制

在关系模型中，对关系作了下列规范性限制：
（1）关系中每一个属性值都是不可分解的（即要求不允许表中有表，如在第8点中新增一列“成绩”，在成绩中又划分为数学、C语言等——这样就不满足规范化）。
（2）关系中不允许出现重复元组（即不允许出现相同的元组）
（3）由于关系是一个集合，因此不考虑元组间的顺序，即没有行序
（4）元组中的属性在理论上也是无序的，但使用时按习惯考虑列的顺序

17.关系模型的3类完整性规则

（1）实体完整性规则：主键的属性上不能有空值
（2）参照完整性规则：如果属性集K是关系模式R1的主键，K也是关系模式R2的外键，那么在R2的关系中，K的取值只有两种可能：
——或者为空值，或者等于R1关系中的某个主键值。（实质是不允许引用不存在的实体）
（3）用户定义的完整性规则

18.启发式优化算法

（1）尽可能早地执行选择操作
（2）尽可能早地执行投影操作
（3）避免直接做笛卡尔积

19.关系模式的外延和内涵

（1）外延：即通常所说的关系、表或当前值
（2）内涵：是与时间独立的，是对数据的定义以及数据完整性约束的定义

20.关系模式的范式：

（1）第一范式：

如果关系模式R的每个关系r的属性值都是不可分的原子值，那么称R是第一范式（1NF）的模式。
即第一范式要求数据库中的表都是二维表，
栗子：一个表【联系人】（姓名，性别，电话），如果一个联系人有家庭电话和公司电话，这种表的结构设计就没有达到1NF。

（2）第二范式：

如果关系模式R是1NF，且：
1）一个表必须有一个主键；
2）没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。
如果A是关系模式R的候选键中的属性，那么称A是R的主属性；否则称A为R的非主属性。
——不符合2NF的设计容易产生冗余数据。

2NF的栗子

考虑一个订单明细表：【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多次重复的情况。

（3）第三范式：

首先是 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。
即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。
考虑一个订单表【Order】（OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity）主键是（OrderID）。

3NF的栗子

其中 OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity 等非主键列都完全依赖于主键（OrderID），所以符合 2NF。
不过问题是 CustomerName，CustomerAddr，CustomerCity 直接依赖的是 CustomerID（非主键列），而不是直接依赖于主键，它是通过传递才依赖于主键，所以不符合 3NF。
通过拆分【Order】为【Order】（OrderID，OrderDate，CustomerID）和【Customer】（CustomerID，CustomerName，CustomerAddr，CustomerCity）从而达到 3NF。

（4）BCNF：

如果关系模式R是1NF，且每个属性都不传递依赖于R的候选键，那么称R是BCNF的模式。

范式之间的区别

1NF-2NF 消除了非主属性对码的部分函数依赖
2NF-3NF 消除了非主属性对码的传递函数依赖
3NF-BCNF 消除了主属性对码的部分和传递函数依赖
BCNF-4NF 消除了非平凡且非函数依赖的多值依赖

21.用过的数据库

在做私有协议FTP服务器项目时：
1）日志表（记录客户端和服务器操作信息）
2）用户信息表（ID，用户名，salt，登录密码）
3）虚拟文件系统的表（文件名，文件类型，所属用户，md5value），当时加入md5加密让用户在客户端注册是的密码先转成密文，再发给服务器。

22.关系模式的分解：

把一个关系模式分解成若干个关系模式的过程。

23.三级模式&两级映像

数据库系统的三级模式是对数据的三个抽象级别，它把数据的具体组织留给 DBMS 管理，使用户能逻辑抽象地处理数据，而不必关心数据在计算机中的表示和存储。
数据库系统的三级模式结构由外模式、模式和内模式组成。

三级模式

（1）外模式：子模式或用户模式
——是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述，
是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示。

（2）模式：逻辑模式
是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图。
模式描述的是数据的全局逻辑结构。
而外模式涉及的是数据的局部逻辑结构，通常是模式的子集。

（3）内模式：存储模式
数据在数据库系统内部的表示，即对数据的物理结构和存储方式的描述。

两级映像

为了能够在内部实现这三个抽象层次的联系和转换，数据库系统在这三级模式之间提供了两层映像。
正是这两层映像保证了数据库系统中的数据能够具有较高的逻辑独立性和物理独立性。

24.数据库设计的步骤

这里只概要列出数据库设计过程的六个阶段：
需求分析；概念结构设计；逻辑结构设计；
数据库物理设计；
数据库实施；数据库运行和维护。

设计一个完善的数据库应用系统往往是上述六个阶段的不断反复。

25.数据库的独立性

是指应用程序和数据库的数据结构之间相互独立，不受影响。
数据与程序的逻辑独立性：
当模式改变时（例如增加新的关系、新的属性、改变 属性的数据类型等），由数据库管理员对各个外模式／模式的映像做相应改变，可以使外模式保持不变。

应用程序是依据数据的外模式编写的，从而应用程序不必修改，保证了数据与程序的逻辑独立性，简称数据的逻辑独立性。

数据与程序的物理独立性：
当数据库的存储结构改变了，由数据库管理员对模式／内模式映 像做相应改变，可以使模式保持不变，从而应用程序也不必改变，保证了数据与程序的物理独立性，简称数据的物理独立性。

数据库管理系统在三级模式之间提供的两层映像保证了数据库系统中的数据能够具有较高的逻辑独立性和物理独立性。

26.数据库的恢复技术

事务：用户定义的一个数据库操作序列。要么全做，要么一个都不做。事务是一个不可分割的单位（是一个完整逻辑过程），ex：银行转账，从原账户扣除金额，以及向目标账户添加金额，这两个数据库操作的总和，就构成了一个完整的逻辑过程（一个事务）。

而数据库管理系统DBMS在写入or更新资料时，为保证事务（transaction）正确可靠，

ACID属性：原子性、一致性、隔离性、持续性。

27.视图&基本表

（1）视图：

栗子，在一个班级里，作为班主任需要知道全班同学所有课程的成绩，便于全面指导；而数学老师只需要知道全班学生的数学成绩就行了。即我们可以从原表中抽离出一个虚拟的子表（方便我们操作），视图的一个重要作用就是区分权限。
语句：create view 视图名 as select 字段名称 from 表名……

视图是从一个或几个基本表导出的表，视图本身不独立存储在数据库中，是一个虚表。
即数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中。
视图在概念上与基本表等同，用户可以如同基本表那样使用视图，可以在视图上再定义视图。

（2）基本表

基本表是本身独立存在的表，在 SQL 中一个关系就对应一个表。
即建数据库时用建的最开始的那个表，不是经过连接而得的。比如你要建一个学生管理数据库，其中学生表就是基本表。

28.封锁协议

29.数据库运行中产生的故障分类

(1)预期的事务内部故障：将事务回滚，撤销对数据库的修改。

(2)非预期的事务内部故障：
强制回滚事务，在保证该事务对其他事务没有影响的条件下，利用日志文件撤销其对数据库的修改。

(3)系统故障：
待计算机重新启动之后，对于未完成的事务可能写入数据库的内容，回滚所有未完成的事务写的结果; 对于已完成的事务可能部分或全部留在缓冲区的结果，需要重做所有已提交的事务(即撤销所有未提交的事务，重做所有已提交的事务)。

(4)介质故障的软件容错：
使用数据库备份及事务日志文件，通过恢复技术，恢复数据库到备份结束时的状态。

(5)介质故障的硬件容错：
采用双物理存储设备，使两个硬盘存储内容相同，当其中一个硬盘出现故障时，及时使用另一个备份硬盘。

(6)计算机病毒故障：
使用防火墙软件防止病毒侵入，对于已感染病毒的数据库文件，使用杀毒软件进行查杀，如果杀毒软件杀毒失败，此时只能用数据库备份文件，以软件容错的方式恢复数据库文件。

30.数据库系统相比文件系统的优点

（1）数据的共享性高，冗余度低，易扩充数据库的数据不再面向某个应用而是面向整个系统

（2）数据独立性高，数据独立性包括数据的物理独立性和数据的逻辑独立性。

（3）DBMS 必须提供统一的数据控制功能，包括数据的安全性保护、数据的完整性检查、并发控制和数据库恢复。

31.并发执行不加控制后果

可能导致3种情况：
数据丢失修改、读到脏数据、数据不可重复读。

32.E-R图

（1）E-R图的三大组成部分：实体、属性、关系

（2）ER图中可以表示出两者的关系吗?

学生实体和课程实体之间的关系。

（3）E-R图集成各子系统之间的冲突
属性冲突：属性域冲突，即属性值的类型、取值范围或取值集合不同。
命名冲突：同名异义，即不同意义的对象在不同的局部应用中具有相同的名字。
异名同义（一义多名），即同一意义的对象在不同的局部应用中具有不同的名字。
结构冲突：同一对象在不同应用中具有不同的抽象。

33.死锁&活锁

（1）死锁：是多个线程相相互想占用对方的资源，又相互等对方释放锁
（2）活锁和死锁的概念相反：死锁是大家都拿不到资源但又想占用对方的资源，而活锁是拿到资源却又相互释放不执行。
任务或者执行者没有被阻塞，由于某些条件没有满足，导致一直重复尝试，失败，尝试，失败。

活锁可能自行解开，而死锁不能。

34.数据库的安全性控制

35.索引的分类&优缺点

索引（加快对表中记录查找或排序）分为两种：
（1）聚簇索引：按照数据存放的物理位置为顺序，提高多行检索的速度；
（2）非聚簇索引（二级索引）：对单行的检索很快。

索引的代价：
（1）增加数据库的存储空间；
（2）在插入和修改数据时要花费较多时间（索引要随之变动）
数据库索引就是为了提高表的搜索效率而对某些字段中的值建立的目录。

36.查询优化

简述查询优化中的启发式规则

37.事务隔离分几种

首先回顾事务的4个特性ACID，然后再回答本问题（事务的隔离级别）

• Atomicity（原子性）：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别：
  • 读未提交（Read uncommitted）
  • 读提交（read committed）
  • 可重复读（repeatable read）
  • 串行化（Serializable）
• Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

38.索引

数据库索引功能索引的最大作用就是加快查询速度，它能从根本上减少需要扫表的记录/行的数量。比如有一张数据表Employee表内有3列：Employee_Name，Employee_Age，Employee_Address，表中有几十万条记录，现在如果通过如下sql语句查找所有名字为Jesus的员工：

SELECT * FROM Employee 
Where Employ_Name = 'Jesus'

缺点：如果没有索引功能，数据库就会朱行遍历整张表（全表扫描）。

创建索引：

CREATE INDEX name_index
ON Employee (Emlployee_Name, Employee_Age)

39.redis的可持久化

（1）RDB持久化

RDB其实就是把数据以快照的形式保存在磁盘上。RDB持久化是指在指定的时间间隔内将内存中的数据集快照写入磁盘。也是默认的持久化方式，这种方式是就是将内存中数据以快照的方式写入到二进制文件中,默认的文件名为dump.rdb。

既然RDB机制是通过把某个时刻的所有数据生成一个快照来保存，那么就应该有一种触发机制，是实现这个过程。对于RDB来说，提供了三种机制：save、bgsave、自动化。

（2）AOF持久化

redis会将每一个收到的写命令都通过write函数追加到文件中。通俗的理解就是日志记录。

两种机制的选择：

reference

（1）数据库思维导图-王珊版
（2）考研复试简略基础考纲
1、数据库系统基础知识
（1）数据库的4个基本概念；
（2）数据模型及其要素；
（3）关系模型的概念及相关数据结构定义
（4）数据库系统的三级模式与二级映像；
2、数据库完整性
（1）实体完整性
（2）参照完整性
3、数据库设计
（1）数据库设计的基本步骤；
（2）概念结构设计；
（3）逻辑结构设计；
（4）E-R图设计及E-R图向关系模型的转换；

（3）字节跳动一面数据库：ACID，隔离级别，常用语句，锁，B树B+树，磁盘操作，存储引擎，主从复制，读写分离，关系型/非关系型，事务/非事务，组合索引，二级索引，范式

[1] 详解Redis中两种持久化机制RDB和AOF