sql原理基础概念

基本概念

数据库（Database）：存放和提供数据的库房

数据(Data)：数据库中存储的基本对象

数据库管理系统(DBMS)：位于用户和操作系统之间的一层数据管理软件

数据库系统：包括数据库，数据库管理系统，应用程序，数据库管理员

主键：用于唯一标识表中的某一条记录的属性或者属性的集合

外键：用于与另一张表关联，外键为灵一张表的主键

超键：超键是唯一能区分元组的属性和属性的集合

实例：数据库在某一时刻存储的真实数据

数据操纵语言：增删改查

数据定义语言：定义、删除、修改数据库中的对象

数据控制语言：用于控制用户对数据库操纵的权限

数据模型：现实世界数据特征的抽象，用来定于数据如何组织，数据之间的关系是怎么样的

并相容性：两个关系具有并相容性需要保证具有相同的属性个数，以及对于每个属性都有相同的域

视图：视图时虚拟的表，并不是物理存储的数据。是基本表或其他视图派生的数据。对视图的更新，实际上转换为对实际基表的更新。

数据模型

现实世界数据特征的抽象，用来定义数据如何组织，数据之间的关系怎样。

层次：

概念模型：按用户的观点来对数据或者信息进行建模

逻辑/实现模型：层次模型，网状模型，关系模型

物理模型：数据在具体DBMS产品中的物理存储方式

数据库系统

三级模式结构：

  • 内模式：数据物理结构和储存方式的描述，是数据在数据库内部的表示方式

  • 概念模式：是对数据库中全体数据的逻辑结构和特征的描述

  • 外模式：数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述

两级映射：

  • 概念模式/内模式映射
  • 外模式/概念模式映射

数据的物理独立性：

内模式与概念模式之间的映射提供了数据的物理独立性。当数据的物理结构发生变化时，只需要修改内模式与概念模式之间的映射即可。

数据的逻辑独立性：

概念模式与外模式之间的映射提供了数据的逻辑独立性。当数据的整体逻辑结构发生变化时，只需要修改各个外模式与概念模式之间的映射即可保证应用程序不受影响。

约束

完整性约束：

  1. 域约束：对属性取值范围的约束
  2. 键约束：每个关系必须要有主键，且每个主键必须不相同
  3. 非空约束：属性值不能为NULL
  4. 实体完整性约束：主键值不能为空
  5. 参照完整性约束：外键可以取NULL值，但若外键为另一关系主键，则不能为NULL
  6. 用户定义的完整性

数据的操作违反的完整性约束：

插入操作：域约束、键约束、非空约束、实体完整性约束、参照完整性约束

删除操作：参照完整性约束

更新操作：域约束、键约束、非空约束、实体完整性约束、参照完整性约束

关系

关系看上去像一张二维表，每个表由行和列组成

行代表一个元组，即数据记录。列代表属性，即字段

关系的域为一组原子值（不可再分割的值）

关系中的元组必须各不相同（元组的唯一性）

五种基本运算：

并，差，笛卡尔积，选择，投影

关系代数是用对关系的运算来表达查询，这个时候可以使用关系代数解释器来模拟关系代数。

并

运算符∪

并运算将两个关系的所有元组合并为一个新关系，前提是两个关系必须有相同的属性（列），且每个元组在结果中只出现一次（去重）。

R1(A, B)

A B
1 2
3 4

R2(A, B)

A B
5 6
3 4

R1 ∪ R2 的结果是：

A B
1 2
3 4
5 6

相当于增加行

差

运算符：-

差运算返回一个关系中有而另一个关系中没有的元组，前提是两个关系有相同的属性。

R1(A, B)

A B
1 2
3 4

R2(A, B)

A B
3 4
5 6

R1 - R2 的结果是：

A B
1 2

相当于删减行

笛卡尔积

运算符：×

笛卡尔积运算将两个关系中的每一对元组组合成一个新的元组，其中一个关系的所有元组与另一个关系的每个元组组合形成一个新的元组。它的结果是一个新关系，包含了两个关系中所有属性的组合。

两个集合相乘的结果

R1(A, B)

A B
1 2
3 4

R2(C, D)

C D
5 6
7 8

R1 × R2 的结果是：

A B C D
1 2 5 6
1 2 7 8
3 4 5 6
3 4 7 8

相当于增加列

选择

运算符：σ

选择运算用于从关系中选择满足特定条件的元组。选择操作是一种过滤操作，它根据指定的条件返回满足条件的元组。

R(A, B)

A B
1 2
3 4
5 6
7 8

σ(A > 4)(R) 的结果是：

A B
5 6
7 8

相当于过滤

投影

运算符：π

投影运算用于从关系中选择指定的列（属性）。它会返回包含指定列的所有元组，并且会去除重复的元组。

R(A, B, C)

A B C
1 2 3
4 5 6
7 8 9

π(A, B)(R) 的结果是：

A B
1 2
4 5
7 8

相当于删减列

连接

运算符：⨝

连接是将两个关系中的元组按照某些共享的属性进行组合，生成新的元组。它是关系代数中非常重要的运算，因为它能够合并来自不同表的数据。连接运算通常是基于一个公共的列（或多个列）进行的。

自然连接（Natural Join）：在自然连接中，连接操作自动寻找两个关系中相同名称的列，并将它们作为连接条件。自然连接将仅返回匹配的元组，并去除重复的列。

等值连接（Equi-Join）：等值连接是指使用等号（=）作为连接条件，将两个关系中某个或某些列的值相等的元组合并。

外连接（Outer Join）：外连接除了返回两个关系中匹配的元组外，还会保留在其中一个关系中没有匹配的元组，并用NULL填充缺失的值。外连接有三种类型：

  • 左外连接（Left Outer Join）：返回左表中所有元组，以及右表中匹配的元组。右边的补充null
  • 右外连接（Right Outer Join）：返回右表中所有元组，以及左表中匹配的元组。左边的补充null
  • 全外连接（Full Outer Join）：返回两个表中的所有元组，不论它们是否匹配。都补充null

R1(A, B)

A B
1 2
3 4

R2(B, C)

B C
2 5
4 6

如果我们进行自然连接：R1 ⨝ R2，连接条件是属性B，结果会是：

A B C
1 2 5
3 4 6

除

运算符：÷

除操作用于解决“对于所有”这种类型的查询问题，通常用于查找在某个关系中与所有其他元组匹配的元组。除运算的结果是返回那些“满足某个条件的所有值”的元组。

R1(Student, Course)

Student Course
Alice Math
Alice English
Bob Math
Bob History
Charlie Math

R2(Course)

Course
Math
English

R1 ÷ R2 的结果是：

Student
Alice

相当于找到某值

使用关系代数完成查询

假设有两个关系：

  1. 学生（Student）

    1
    Student(学号, 姓名, 年龄, 系别)
    学号 姓名 年龄 系别
    101 张三 20 计算机系
    102 李四 22 数学系
    103 王五 21 计算机系
    104 赵六 23 物理系

  2. 选课（CourseSelection）

    1
    CourseSelection(学号, 课程号, 成绩)
    学号 课程号 成绩
    101 C1 85
    101 C2 90
    102 C1 78
    103 C3 88
    104 C2 76

查询计算机系所有学生的姓名和年龄

1
π姓名, 年龄(σ系别=′计算机系′(Student))

查询选修了 C1 课程的学生的姓名
$$
π
姓名
​
(σ
课程号=
′
C1
′

​
(CourseSelection)⋈Student)
$$
查询至少选修了一门课程的学生信息
$$
π
学号, 姓名, 年龄, 系别
​
(Student⋈CourseSelection)
$$
其中π 为投影，代表的是保留哪一列

σ为选择，代表一个过滤的条件

后面括号代码查询的是哪个关系

进行自然连接（⨝），匹配相同“学号”的学生信息。

÷代表找到某值

x代表笛卡尔积，表示可能出现所有的一个集合

三值谓词逻辑

  • TRUE
  • FALSE
  • UNKNOWN

只有在比较结果为TRUE是才确定为真，e.g. （TRUE与UNKNOWN交为UNKNOWN， 此元组不会出现在结果中）

将E-R模型映射成逻辑模型的步骤

  1. 映射强实体型
  2. 映射弱实体型
  3. 映射1 : 1 二元联系型
  4. 映射1 : N 二元联系型
  5. 映射M : N二元联系型
  6. 映射多值属性
  7. 映射N元联系

关系模型的三个组成部分

  • 数据结构：基本的数据组织形式是关系，关系由元组和属性组成
  • 数据操作：包括查询，更新操作
  • 数据约束：包括实体完整性，参照完整性，用户定义完整性

关系键

主码：唯一标识元组的属性或属性组合，不可为空。

全码：能够唯一标识元组的属性集合，可能包含冗余属性。

候选码：最小的全码，不能再减少任何属性，否则就无法唯一标识元组。

外码：一个关系中的某个属性（或属性组），它引用另一个关系的主码。

外码的属性的值可以为空值

主属性：属于某个候选码的属性。

非主属性：不属于任何候选码的属性。

关系型数据库

数据库管理的需要催生了DBMS，而关系型数据库管理为RDBMS

常见的数据模型有三种：

  1. 层次模型
  2. 网状模型
  3. 关系模型

文件管理数据的缺陷：

  1. 数据冗余和不一致性。数据冗余表示在每个shell脚本中基本上都是/bin/bash，但很多用户使用的都是bash，那么这个可能出现多次，冗余量很大；不一致性，比如有一个数据在多个文件中都被使用，那么修改的时候，需要把每个文件都手动修改。
  2. 数据访问困难。如果数据库有上千万行数据，那么我们使用时，需要把这上千万行数据全部载入内存中，再使用，对硬件的要求会很高而且载入时间很长，但其实我们需要比较的也只是那一行数据而已。
  3. 数据孤立。由于数据保存在不同的文件里面，所以这些文件之间的格式可能各不相同，所以后续开发一个软件来管理的话非常困难，因为它们不满足某一个规范，使得数据之间是相互孤立的。
  4. 完整性问题。例如两个转账的用户之间数额的总和应该是一样的，但如果在转账的过程中出现了问题，那么文件就无法解决，除非应用程序设计上解决，但应用程序本身并无法理解数据之间的逻辑问题。
  5. 原子性问题。类似于完整性问题，数据必须从一个稳定的状态转换为另外一个稳定的状态。
  6. 并发访问异常问题。按道理来讲，如果一个用户访问数据库文的前十行，另一个用户访问最后十行，本身他们俩并不会冲突，但如果某个用户需要修改的话，另外一个用户并不能访问。因为为了保证文件的完整性，我们会加锁的，但文件级别的锁安全性太高，并不适合这种访问
  7. 安全性问题。部分访问授权很难实现，很难使某一个用户只能看到其中指定的某些字段。

关系型数据库特点：

关系数据库想要解决数据冗余问题，它将每一个关系都看成了一个表，每一列都看成是一个属性，每一行看成是他的一个元组（也就是数据）如果有许多相同值的话，它将具有相同字段的值独立出来，把一张表拆分成两张表，降低所谓的冗余度，这种关系也叫做E-R关系模型（实体关系模型）

常见的关系模型：

  1. 关系模型：用一张二维表来表示关系模型
  2. 实体关系模型：将一张表拆分，让它们产生某种联系
  3. 对象关系模型：存储一些比较大的文件如何存储呢？可以将一个比较大的文件先存储在数据库文件管理系统当中的某一个存储空间上，表中用一个指针指向这么某一个数据，把它当作一个存储对象来进行管理。
  4. 半结构化数据模型：结构化是指每一行存储的都是一样的，比如/etc/passwd文件中，第一个字段为用户名，第二个为密码等。半结构化表示它并不是唯一的，可能第一行存储的是用户名和性别，第二行存储的是用户名和年龄这种，所以每一行存储不止存储它的数据，还存储它的标签。

SQL

SQL：Structure Query Language(关系结构化语言)

  • DML：数据操作语言，增删改查都是DML语句。
  • DDL：数据定义语言，实现数据对象的存储和定义。CREATE、DROP、ALTER
  • DCL：数据控制语言，GRANT、REVOKE，用来定义数据库中用户的访问权限

关系数据库RDB的对象基本有：库、表、索引、视图、用户、存储过程、触发器、事件调度器等。

实现定义的时候，一定要考虑数据的约束关系

域约束：数据类型的约束
外键约束：引用完整性约束
主键约束：某字段能唯一标识此字段所属的实体，并且不允许为空,一张表中只能有一个主键
唯一性约束：每一行的某字段都不允许出现相同值，可以为空，一张表中可以有多个
检查性约束：比如年龄只能时int型

结构

  • 表示层：表
  • 逻辑层：存储引擎，实现表和数据文件的解构
  • 物理层：数据文件

数据存储和查询

  • 存储管理器
  • 查询管理器

负责接收用户查询，理解用户查询，并将用户的查询包括存储转换为对应的存储管理器可以理解的语句，存储管理器将数据存储到磁盘上或从磁盘上删除的这么一种组件

存储管理器应该具有的组件（功能）：权限及完整性管理器、事务管理器、文件管理器、缓冲区管理器

查询管理器应该具有的组件（功能）：DML解释器、DDL解释器、查询执行引擎

数据存储和执行的过程：

  1. 查询管理器接受用户查询，并理解。使用DML和DDL解释器
  2. 将DML和DDL解释器的结果给查询执行引擎
  3. 查询执行引擎的结果给存储管理器
  4. 存储管理器将操作写入磁盘中