数据库学习笔记-ch1-3

Ch1-引言 #

数据库的发展历程 #

• 人工管理
  • 早期数据存储的外村载体是磁带、卡片、纸带等顺序存取设备，完全由人工完成对数据的存储与维护。
  • 当时不存在OS，更别提DBMS
  • 都是把程序、数据写到纸带上。磁带先放入IO系统，IO系统读取程序和数据将它们放到磁带上，再把磁带放到计算机上运行，结果会输出到磁带上，再把磁带放到IO系统，IO系统打出结果纸带，一次计算就算结束了。得到数据和程序是绑定的，数据不具有共享性（只能为一名用户所用，也就是写这段程序的这个用户），数据和程序不具有独立性。
• 文件系统
  • 出现了随机存取的外存储器（磁盘），可以方便地根据地址访问，支持了链接、索引、散列等非顺序存储方式
  • 出现了文件系统File System，数据的存取以记录为单位，支持对文件的增删改查操作
  • 数据和程序具有了但是又没完全具有独立性，而且其共享性差。虽然能够对应一个或多个程序，但是只有全权管理它的程序能够解释其中的含义，如果别的应用要使用它，则需要与它沟通，获取语义。
  • 文件系统只负责存取，不懂文件的含义
  • 用户无需考虑存取的物理细节，程序员无需自行实现物理的存取
  • 冗余性严重：很多文件中可能保存了同样的信息，但是它们却无法自发关联。
  • 完整性差：无法确保数据的完整性，例如使用了不存在的外码、性别不仅是男女、取值范围超出等。
  • 难以并发访问：例如我写的MistyForestBotMsg。
  • 原子性问题难以做到原子性：例如转账操作。（需要程序员自行实现相关逻辑，保证原子性问题的正确执行）
  • 数据分散，联系困难，读取困难
• 数据库
  • 概念创新
    • 数据不是程序的附属品，而是现实中==真正存在的==对象
  • 有独立性
    • 与程序真正独立开来，支持任何程序而不是有限个程序。数据是为整个系统服务的，而不是为某几个程序服务。
  • 有统一的管理系统
    • 由DBMS统一进行操作
  • 效率高
    • 运行效率、开发效率高于文件系统
  • 有组织
    • 数据反映了客观世界事物的本质联系，无论多少个表格，它们之间都可以是有联系的；而文件系统做不到这一点：文件之间没有任何关系。
  • 易扩展
    • 一个应用一般选择DB的一个子集进行使用。如果该应用有了新的功能，则可以重新选一个子集或者在原先基础上加新的数据==即可满足新需求==。
  • 具有安全性控制
    • 可以保护数据，防止不合法使用的泄露和破坏
    • 措施：用户标识与鉴定、存取控制
  • 具有完整性控制
    • 保证数据的正确性、有效性、相容性
    • 措施：完整性约束的定义、完整性约束的检查
  • 具有并发控制
    • 防止多用户互相干扰，得到错误结果
    • 措施：封锁措施
  • 具有恢复控制
    • 可以使数据库从故障中恢复到某个状态
    • 措施：冗余
  • 和文件系统的区别（为什么通用性这么高）
    • 数据库的管理系统是介于Application和File System之间的一个中间层，因此具有非常好的通用性。而文件系统方式，每个Application自带一个数据管理逻辑，直接对File System进行操作，所以显然每个Application都有自己的数据管理方式，难以通用。
• 数据仓库与数据挖掘
  • 应用技术从大型数据库提取人们感兴趣的信息和知识，这些信息和知识是隐含的，是事先位置和潜在有用的。
• 云计算与大数据处理

DB相关基本概念 #

• 数据Data
  • 数据是==不可再分的==描述事物的符号记录，可以是多媒体也可以只是文字信息。
  • 数据本身没有意义，应该加上“语义”予以解释。“语义”一般就是列名称，也就是属性。
• 数据结构DS
  • 逻辑结构
    • 即数据结构课程中的那些。
  • 物理结构
    • 数据在计算机内的存储方式
    • 顺序存储方式
      • 逻辑上相邻的节点在物理位置上也是相邻的。类似于==数组==。
    • 链接存储方式（非顺序方式1）
      • 逻辑上相邻的节点不要求物理上相邻。其附带引用字段用于指向下一个节点，类似于==链表==。
    • 索引存储方式（非顺序方式2）
      • 建立索引表，指向每个节点的存储位置或者一组顺序节点的起始存储位置。类似于==map==。
    • 散列存储方式（非顺序方式3）
      • 根据节点关键字直接算出该节点存储的位置。类似于==unordered_map==。
• 数据库DB
  • 产生
    • 人们收集了数据，希望能妥善保存起来，并能进行进一步的加工处理，提取有用信息。
    • 数据挖掘：从大量无意义的数据中赋予合适的语义，总结出新规律。
  • 定义
    • 长期存储的、有组织的、可共享的数据集合。
    • 数据是按一定的模型组织、存储的，可以为多个用户所共享。
    • 其具有较低的冗余度，其数据具有较高的独立性。
    • 易于扩展。
• 数据库管理系统DBMS
  • 定义
    • 由相互关联的数据集合和一组用于访问它们的程序组成。
    • 即：一组相关数据集合+程序。
    • 位于用户和操作系统之间。基于操作系统，并为用户所用。
  • 功能
    • 数据定义功能——DDL语言
      • 定义数据对象
    • 数据操纵功能——DML语言
      • 增删改查
    • 数据库的运行管理
      • 保证数据的安全与完整性
      • 提供多用户并发支持
      • 故障恢复
    • 数据库的建立和维护
      • 略
• 数据库系统DBS
  • 定义
    • 在计算机系统中引入DB后，该计算机系统称为DBS。
    • 数据库系统DBS简称数据库DB。
  • 构成
    • DB+DBMS+User（Including Administrator）+Application。
  • 体系结构
    • Hardware- OS- DBMS & Compiler - Application Dev Tools- Application-User
    • 其中，数据库管理员可对DBMS、OS进行操作。User可对应用程序Application进行操作。
• 数据管理技术
  • 概念
    • 对数据进行分类、组织、编码、存储、检索、维护，是其中心问题。
  • 发展动力
    • 应用需求的推动
    • 计算机软硬件的发展

数据视图 #

数据库系统为用户提供数据的==抽象视图==。所谓抽象视图就是隐藏数据存储和数据维护的细节，==只保留==用户所关心的那部分数据的视图。

• 实例与模式
  • 即数据库范畴的“类与对象”的关系。是“型”与“值”的关系。
  • 实例是模式在某一时刻时，其存储的信息。类似于一个==快照==。
  • 模式就是数据库的整体设计模式。
• 数据抽象（模式的三层体系结构、模式层间的两个映象）
  • 模式定义了整个数据库的存储格式，其负责与物理层交互进行存取。每个用户的外模式只是在模式上取了一个子集，从而看到数据库中他们应该看的那部分。
  • 物理层（内模式、存储模式）
    • 是数据实际存储的方式。
    • 内模式层只有一个。
  • 逻辑层（模式）
    • 是数据库全局逻辑结构的描述。
    • 是所有用户的公共数据视图。
    • 模式只有一个。（注意，“模式只有一个”的意思是可以由很多表组成，这些表构成的集合只能有一种。不能存在多种构成方法。可以随意往这个集合里面增加或删除表。）
    • 由DB Administrator（DBA，数据库管理员）使用。
  • 视图层（外模式）
    • 外模式是某个用户的数据视图
    • 是模式的子集合，描述了数据的某一部分
    • 外模式可以有多个。（一般是一个用户一个或多个）
    • 由User使用。
  • 内模式-模式映像
    • 定义了数据逻辑结构和物理存储结构的对应关系。
    • 实现了物理数据独立性
  • 外模式-模式映像
    • 定义了某一个外模式和模式间的对应关系。
    • 该映像定义往往包含在外模式的定义中。
    • 实现了逻辑数据独立性
  • 好处
    • 将用户的视图与实际存储的数据库、实际的物理存储完全独立开来。这三层如果某一层发生逻辑改变，只需要修改此处映像，使得在其他层看来，其仍然和以前一样即可。
• 数据模型
  • 定义
    • 描述数据、数据联系、数据语义、数据一致性约束的概念工具的集合。
  • 分类
    • 关系模型
      • 以“表”来表示一些数据之间存在的关系。满足一种关系的若干属性可以写在一张表里面。
      • 例如学生选课表，则学生和所选的课之间存在选择与被选择的关系。
      • 例如学生信息表，则学生的学号和姓名之间存在一一对应的关系。
    • E-R模型
      • 实体和其联系构成的模型。贴近现实世界。
    • 基于对象数据模型
      • 在E-R模型的基础上封装了更多方法和对象标识的扩展。
      • 面向对象数据模型、对象-关系数据模型
    • 半结构化数据模型XML
      • 允许相同类型的数据包含不同属性集的数据说明。

关系模型 #

关系模型是当今大量数据库采用的模型。优点在于其十分简单。

• 特点
  • 基于记录的模型（所谓记录就是一张表中的一行）
  • 用表==的集合==来表示数据之间的关系，==每个表==包含了某种==特定类型的记录==
  • 每个记录包含了固定数目的==字段==（字段也称为属性、列）。规定了这种这种字段组合的记录就是一种特定类型的记录啦！这种字段组合又称为==元组==。
  • 即：每个表格由若干行由特定字段构成的记录构成，这些表格的总和称为==关系模型==。
• 优点
  • 简单
  • 数据请求是非过程化的。用户不需要提供查询的具体算法实现。只需要提出需求。

E-R模型 #

利用现实世界中，一组实体之间存在关系。我们刻画这种关系即可。 E-R模型也常用于数据库设计。

• E-R表示
  • 棱形：关系
  • 矩形：实体
  • 椭圆：属性

数据库语言 #

• DDL Data Definition Language 数据定义语言
  • 定义数据库的外模式、模式、内模式（又称源模式）
  • 翻译内模式功能：可将内模式翻译成目标模式，存入数据字典（包含元数据MetaData）
  • 实现一致性约束定义
    • 域约束（即取值范围）
    • 参照完整性（后续会介绍）
    • 断言（即报错）
    • 授权（安全）
  • 例子

	create table department(
	dept_name char(20);
	building char(15);
	) 

• DML Data Manipulation Language 数据操纵语言
  • 增删改查
  • 过程化DML和非过程化（声明式）DML
  • 查询语言Query Language
    • 查询处理器将DML翻译成物理层的动作序列
  • 例子

	select instructor.name
	from instructor,department
	where instructor.dept_name=department.dept_name
		and department.budget>95000 

• DCL Data Control Language 数据库运行控制语言
  • 完整性检查
  • 安全性控制
    • 权限的转授和回收：允许用户把已获得的权限转授给其他用户，也可以把改权限回收回来
  • 并发控制
  • 事务管理
  • 运行日志管理
  • 可见，数据库相对于文件系统等的先进功能大都体现在了DCL中。

应用程序对数据库的访问 #

• 应用程序接口
  • ODBC 微软与数据库的API
  • JDBC Java与数据库的API
• 扩展宿主语言
• 嵌入式DML语言

数据的存储和查询 #

==DBS的功能部件==主要分为：

• 存储管理器
  • 在数据库中存储的底层数据与应用程序及向系统提交的查询直接提供接口的部件。
  • 权限及完整性管理器
  • 事务管理器
  • 文件管理器
  • 缓冲区管理器
  • 数据文件
  • 数据字典
  • 索引
• 查询管理器
  • DDL解释器
    • 解释DDL语句并将其定义的模式记录在数据字典中
  • DML编译器
    • 将DML语句翻译为一个可执行方案，即一个低级指令序列，供查询执行引擎执行。
    • 自动进行查询优化，更快地进行查询。
  • 查询执行引擎
    • 执行DML编译器生成的低级指令序列。

事务管理 #

• 什么是事务
  • 事务是数据库应用中完成单一逻辑功能的操作的集合。
  • 也就是完成一种特定逻辑功能所需要的这一系列操作，称为事务。
  • 例如银行转帐，读A，A-50，写A，然后读B，B+50，写B。这一串操作称为一个事务。它完成了银行转帐这一逻辑功能。
• 事务的特性（ACID）
  • 原子性Atomicity
    • 要么做完，要么不做
    • 由==恢复机制==实现
  • 一致性Consistency
    • 事务开始前后，数据库必须都保持一致性的状态；事务运行过程中，由于中间过程，允许暂时性的不一致
    • 例如转帐前后，这两个账户的总额应该不变
    • 由程序员用户负责，由==并发机制==实现
  • 隔离性Isolation
    • 多个事务同时运行时（即并发时），系统应当保证互不影响
    • 站在事务的角度，例如对于T1，T2，在T1看来，要么T2已经结束，要么T2还没开始运行。
    • 由==并发机制==实现
  • 持久性Durability
    • 一个事务一旦提交，其对数据库的影响应当是永久的
    • 系统发生故障，数据库的持久性不应当损害
    • 由==恢复机制==实现
• 事务管理器
  • 并发管理器
  • 恢复管理器
  • 显然，它们负责实现上述这些ACID特性（实现并发机制和恢复机制）。

用户使用DBMS查询的过程 #

1. 用户向DBMS发出命令
2. DBMS进行语法检查、权限检查，决定是否执行
3. DBMS进行查询优化，然后使用DML编译器转为操作序列
4. 执行操作序列（然后==重复==下列5-12步，直到语句对应的查询任务全部完成）
5. DBMS先找缓冲区，如果有所需记录，直接跳10，否则继续到6
6. DBMS查看模式，决定需要读取的文件
7. DBMS向OS发出读请求
8. OS读此文件
9. OS将数据送到DBMS的缓冲区
10. DBMS根据用户命令、数据字典，得出所需==模式==
11. DBMS将所需数据从缓冲区送到用户工作区
12. DBMS返回执行状态信息

数据库体系结构 #

• 客户机/服务器数据库系统
• 粗/细力度并行系统
• 分布式数据库系统

数据库的用户（User）和管理员（DBA） #

用户 #

• 无经验用户
• 富有经验的用户
• 应用程序员
• 专业用户

DBA #

• 作用
  • 模式定义、约束定义
  • 整理和装入数据
  • 数据访问授权
  • 日常维护、监控运行状况
  • 指定恢复策略
  • 改进性能

Ch2-关系数据库 #

关系数据库的结构 #

数据结构 #

• 关系：是一个二维的表格，这个表格的名称是其标识符。
• 元组/记录：表格的一行
• 属性/字段：表格的一列
• 域：属性的==取值范围==。
• 属性的取值：要求是==原子的==，因为单元格不可再分
• null：表示未知或不存在

关系数据库由表的集合构成 #

现实世界就是实体间的关系；逻辑上是一个二维表格。 表格是元组的集合。一个元组代表了这些属性之间的联系。

关系的性质 #

• 每列是是同域的。是同属性的。
• 不同列可以同域。但是不能同属性。
• 行、列的顺序无关。因为可以进行排序。
• 任何两行不能全相同，否则不能规定码了。
• 任何两列不能全相同，否则不能区分属性了。
• 单元格是原子的，是不可分的数据项。

数据库的模式（DB Schema）和实例、关系 #

• 模式是框架，规定了数据库的设计，是静态的，且没有内容；
• 实例是动态的，内容随时间变化；某一时刻的实例是数据库数据的一个快照；
• 一个模式可以建立多个关系，一个关系只能对应一个模式；前者是蓝图，后者是产品；
• ==类比C++，则模式是类，关系是对象变量，实例是这个变量的值。==

模式的表述 #

由关系名和属性序列构成：

Student（sno,sname,dept）

码Key #

• 定义
  • 能唯一==确定==一个==元组==的最小==属性集合==
• 超码Super Key：包含码的一个属性集合。即码的==超集（但不一定是真超集）==。超码==一定能==用于标识元组。==最小的超码是候选码==。
• 候选码Candidate Key：==可以作为==该关系的码的属性集合。注意，仍然是==要求最小化==的。即：去掉一个属性就不能唯一标识了。其任意真子集都不能成为超码。候选码是最小的超码。
• 主码Primary Key：在==定义==模式==时==，从所有候选码中，==指定==一个作为码。应当选择那些几乎不会发生变化的属性构成主码。
• 外码Foreign Key：其他关系的==主码==出现在此关系中（可以不作为此关系的码），用于联接两个关系。
  • R1属性包含R2的主码，则称这个属性是R1的外码。
  • ==我们称被包含者的主码为包含者的外码。==
  • R1称为参照关系Referencing Relation，即包含外码的那个关系
  • R2称为被参照关系Referenced Relation，即提供主码的那个关系
  • 我们称R1通过外码参照关系R2.例如instructor通过dept_name参照关系department。
• 全码：如果表中找不出码，则整个元组都作为码。此时触碰了“不允许两行完全一样”的底线。
• 主属性：任一候选码中的属性。可以理解为==有机会参与==区分此元组的属性。
• 非主属性：不存在任何一个候选码的属性。可以理解为==不可能有资格==参与区分此元组的属性。

模式图Schema Diagram #

定义 #

含有主码和外码依赖的数据库模式

画法 #

• 矩形：关系
  • 矩形上部不同颜色划分：关系名
  • 下部其他部分：各属性
• 主码：标下划线的属性
• 外码依赖：从R1的外码所在属性指向R2的主码所在属性的箭头

完整性约束 #

参照完整性约束 Referential Integrity #

• R1的外码属性的取值必然要等于R2中相应主码属性的取值，且该值必须在R2中存在。
• R1的外码可为null。（如果实在在R2中找不到合适的去对应）
• 即：要么必须等于R2中存在的值，要么就得为null。
  • 小插曲：如何把多对多关系（难以处理，组合过多）变成多对一关系？
  • 答：加一个关系，作为第三者，用类似于上帝视角的方式。既然是上帝视角，就要求同时包含这两个关系。
  • 例如：学生选课的关系，学生可以选多门课，一门课有很多学生上，这是一个多对多关系。但是，如果再加上一个选修关系，其中包含了选课学号和课程号，那么就变成了一个学号对应多个选修，一个课程对应多个选修的情况。

实体完整性约束 #

即主码包含的~任何属性~都不能为空。否则不能区分开来。

用户定义完整性约束 #

用户可以根据实际情况，对属性规定其约束条件，例如SEX必须为男或女，不能有第三个值。

实现 #

数据库系统随时对实体完整性、参照完整性、用户定义完整性进行检查。

查询语言 #

查询语言是一组运算构成的集合。可以组成一系列表达式来表达需要的查询。查询语言一般比程序设计语言更加高级。包含过程化语言（给出查询过程）和非过程化语言（只需要给出请求描述）

• 关系代数
  • 以关系作为输入，指定查询过程，并==产生==一个新的关系作为结果。
  • 选择
    • 以~小写theta函数~记录。下标写谓词逻辑表达式，函数括号写待选择的关系。
    • 从某些关系里面找到满足谓词逻辑的行，组合出一个新关系。
  • 投影
    • 以~大写pi函数~记录。下标写待投影的列，函数括号写待投影的关系。
    • 在一个关系中拿出若干列，并拼合成一个新关系。（注意不满足实体完整性约束的则需要进行合并，一般是两个元组留下一个即可（因为完全相同））
    • 或者说，对输入的关系的所有行输出特定的属性构成的新关系，并删去重复元组。
  • 连接
    • 对于两个关系，把分别来自两个关系的元组合并成一个元组。
    • 自然连接：以参照的方式进行连接。凡是能参照的就都进行连接。~具体来说就是他们具有相同名字的所有属性上取值相同。计作|x|.~
    • 笛卡尔积：直接暴力地对所有元组进行两边的两两组合。~也就是输出所有元组对~。例如：a1+b1，a1+b2，a1+b3，a2+b1,a2+b2,a2+b3,a3+b1,a3+b2,a3+b3.~计作乘号~.
  • 除
  • 差
    • 对两个相同模式的关系，执行集合的差。
  • 并
    • 对两个相同模式的关系，执行集合的并。
  • 交、补
• 关系演算
  • 通过谓词逻辑描述需求即可。==产生==一个新的关系作为结果。
  • 包含元组关系演算和域关系演算。输入（即谓词变元）、输出的分别是关系和域。（注意：域（Domain）就是一个取值范围。和“定义域、值域”中的“域”是同义的。）
• SQL是结合了关系代数和关系演算的语言。是面向集合（即关系、域）的。
• 关系数据语言的特点
  • 一体化（集DDL、DML、DCL于一身）
  • 非过程化
  • 面向集合（即关系、域）的语言。其操作对象和结果都是关系或域。（非关系系统的操作对象是~记录~）
• 关系数据库操作
  • 关系数据库的操作是一次一集合（Set-at-a-time）的。这区别于非关系数据库一次一记录（Record-at-a-time）。
  • 关系数据库操作的方式可用关系代数和关系演算来==表示==。它们两种都是对集合的操作，是==等价的==。

Ch3-初级SQL #

SQL #

SQL概述 #

SQL的语言组成 #

• DDL
  • 完整性：包含了~定义~完整性约束的命令。
  • 视图定义：包含了定义视图的命令。
  • 事务控制：包含了定义事务开始和结束的命令。
  • 授权：定义对关系和视图的访问权限的命令。
• DML
• DCL

SQL的特点 #

• 一体化（同上一chap中的一体化概念）
• 只有“关系”这一数据结构（更加方便）
• 面向集合的操作方式（同上一chap中的概念）
• 主要为非过程化

SQL数据定义 #

基本数据类型 #

基本模式定义 #

create table 名称，左括号，后面是若干条属性定义，如果有多条则中间用逗号连接；再后面是若干条约束性定义，如果有多条则中间用逗号连接（属性定义和约束性定义之间也用逗号连接），然后最后右括号结尾。

• 属性定义
  • not null关键字
    • 紧跟着某条属性定义
    • 规定属性不能为null。
• 约束性定义
  • 约束性定义是跟在所有的属性定义之后的，意在等这些属性都定义好后，加以修饰。
  • primary key关键字
    • 该关键字给出该关系的实体完整性约束的定义。
    • 可以定义多个属性为主码。如果需要，则用逗号连接。
  • foreign key关键字
    • 该关键字给出该关系的参照完整性约束的定义。
    • 可以定义多个属性为外码。如果需要，则用逗号连接。
    • 可以有多个foreign key关键字。每个都代表该关系~的一个~参照完整性定义。
    • 每个foreign key定义的结尾都会跟一个references关键字，后面加所参照的关系名称。
  • check关键字
    • 该关键字给出该关系的用户定义完整性约束的定义。
    • 后加条件（谓词逻辑表达）。

域（Domain）定义 #

• 域可以认为是C++中的typedef，即用户自己根据已有类型又加入自己的解释而给予的别名。
• 注意，由于SQL中的基本类型是存在传入参数的，所以domain还自带了当时定义用它domain时传入的参数。以后使用此domain，就自带那个参数了，不需要传入了。例如：

create domain  person_name  char(20);
       create table   PROF
           ( PNO   char(10),
            PNAME  person_name   not null,
             SAL      int,
             PAGE   int,
             DNO  char(10),
             primary key (PNO),
             check (SAL > 0));

​```sql

关于SQL子句 #

逻辑顺序（非实际运算顺序，因为可能有其他优化） #

即先列出需要访问的关系列表（from），然后利用谓词提取需要的元组构成关系（where），然后保留此关系中的若干属性（select），然后输出该关系。

1. from 列出笛卡尔积，构成最初的关系
2. where 从上述关系中只留下满足谓词逻辑的元组
3. group by 从该关系中只留下选择的属性，并分组。（类似于select，但是比select先行一步。）如果没有group by语句，则整个关系是一个大分组。
   1. group by在where之后是因为：where中出现的属性不要求必须出现在group by中，且不要求必须以基本聚集函数的形式出现。
4. having语句从上述分组中筛选满足此关系的分组。不满足的分组将会被~整体丢弃~。
5. select 继续从该关系中只留下选择的属性，并应用基本聚集函数得到结果关系
6. order by从上述属性的基础上修改输出的元组的显示顺序

from子句 #

• from 给定了一个查询中所需要访问的关系列表。==是对关系的选择（限定）。==
• 给定了需要做出选择的关系的集合。这是我们接下来运算的全集了，不能再超出这个范围了。
• 自然连接 natural join
  • 只考虑连接在两个关系中在同名属性中的取值都相同的元组。
  • 例如from instructor natural join teaches，则具有相同教师ID的元组会被连接。
  • 和笛卡尔积的区别：例如from instructor natural join teaches, course，其中instructor natural join course是自然连接，变成了~单个~关系。然后后面的“，”的意思是这单个关系再和course做笛卡尔积。
  • 自然连接如何where限定：例如from instructor natural join teaches, course where teaches.course_id=course.course_id ，这个例子中的teaches.course_id就是自然连接得到的那个复合关系中的course_id。写成teaches.course_id是因为这个属性原本来自于teaches关系模式。
  • 连续自然连接：例如from instructor natral join teaches natral join course natral join department.就是对四个关系模式的连续自然连接，显然根据刚才的定义，则这看作单个关系。
• 规定属性的自然连接 join using
  • join using关键字允许只需要满足对于在using关键字后的属性表中的属性，在待连接的关系中的这些属性取值相同即可。
  • 有时候不希望要求所有在两个关系中同名的属性都要求相同，而允许只需要其中的一些相同即可了。因为比如所有那些相同的关系能构成超码，但是我们实际上只想让主码限定就够了。
  • 显然过多的相同属性要求会产出更少的查询结果——条件更加苛刻。
  • 例如：from instructor join course using (course_id,dept_name)
  • 如果要和natural join同时使用，要注意括号问题，因为natural join两端是一个整体：from (instructor natral join teaches) join course using(course_id).

select子句 #

• select用于列出查询结果中需要的属性。==是对列的选择（限定）。==
• select给出的结果默认不去重，因为比较浪费时间。
  • 如果要得出去重的结果，可以使用select distinct sth。
  • 如果强调不去重，可以使用select all sth。但是默认就是如此。
  • 当使用select distinct语句时，如果两个元组其他取值相同，且在同一个属性上也~都为null，则它们可以被判定为完全一致~，最终只输出其中之一。
• select支持对属性进行运算后，输出在原来该属性基础上运算后的该属性构成的关系的结果。
  • 注意，虽然是运算，但只是对select出来的东西进行运算。其结果不会对原关系做出任何修改。
  • 例如，select salary*1.1输出的各salary属性值就是原关系中此属性值的1.1倍。

where子句 #

• where作用在from子句中指定的那些关系上，选定那些满足其子句中谓词逻辑的元组来构成结果关系。==是对行的选择（限定）。==
• 如果省略where语句，则相当于谓词是true，即相当于where true。但是显然如果这样做，并且from还列出了很多关系，则会输出一个巨大的由笛卡尔积构成的关系！这将会是非常庞大的，因为是n^2数量级。
• between and谓词：即>=一个且<=另一个。用此谓词可以略微简化表达。
  • 例如：where salary between 90000 and 100000
• and谓词：即“与”逻辑。
  • 例如：where instructor.id=teaches.id and dept_name='biology'
• 技巧：n维元组来方便地描述多个属性关系
  • 可以把若干属性列为元组形式。两个元组之间也可以构成比较逻辑。例如：(a1,a2)<=(b1,b2)即当且仅当a1<=b1,a2<=b2.（即根据字典顺序来。）
  • 例如：where (instructor.id,dept_name)=(teaches.id,'biology')
• 谓词is null、is not null
  • 用于测试是否为空值，例如where salary is null
• 谓词 is unknown、is not unknown
  • 用于测试是否为unknown，例如where salary > cost is not unknown
• 谓词 in、not in
  • 用于测试元组的集合成员资格，限定元组是否应该出现在某个集合中（可以是关系查询结果关系，也可以是枚举集合）
  • 如果限定是否位于关系查询结果中，需要用到where子句中的嵌套子查询，例如：where course_id not in(select course_id from section where semester='spring')。注意嵌套需要加括号。
  • 如果限定是否位于枚举集合中，注意集合的写法，例如：where name not in ('Mike, Andrew')
  • 也可以让枚举集合（枚举元组）和嵌套子查询做限定，例如：where (course_id,sec_id,semester,year) in (select course_id,sec_id,semester,year from teaches)

order by子句 #

• 控制了元组的显示次序。
• 可以使用desc、asc来表示升序或者降序。
• 例如：select name from instructor order by salary desc,name asc.

group by子句 #

• 和select类似，也是==是对列的选择（限定）。== 但是再加上一步分组功能。且其执行顺序非常靠前，紧紧跟着where。（然而select是在最后执行。）
• 如果不加group by子句，则默认是类似于“group by *”的意思，即把整个关系作为一个组。否则，整个关系只会保留group by子句中的属性和其他基本聚集函数产生的新属性作为结果输出。
  • 因此，group by相当于一个自带分类功能的select。它运行在查询结果的一开始，将group by子句中列出的属性选中并分组。
  • 因此，select后面出现的属性必须是也出现在group by的，否则必须出现在基本聚集函数中。否则，该属性不存在，因为group by没有选择他们来构建初始关系。

having子句 #

• having子句对所有分组进行筛选，对不满足其后面的谓词逻辑的分组直接丢弃，==是对分组的选择（限定）==。
• 和select的注意要点类似，任何出现在having子句中的属性，必须也要么出现在group by子句中，要么被基本聚集函数所聚集。例如：group by course_id,semester,year,sec_id having count(ID)>2，其中，ID就属于不包含在group by子句中，因此只能以被聚集的形式出现。

附加运算 #

更名运算 as #

• as可以出现在 select、from子句中。在from的子句中出现，可以认为是对所选择的关系做了一个其副本进行笛卡尔积运算，==是关系的新名字==。在select子句中出现，可以认为是对输出的属性进行了更名，==是属性的新名字==。
• 无论怎么样，as都不会影响原关系模式的名称。
• 为什么要进行更名
  • 对于select
    • 结果可能有重名的属性
    • 如果在select中使用多属性进行数学计算、聚集函数，其结果构成一个新属性，则必须将其命名（否则谁也不属于）
  • 对于from
    • 对于同一关系中的不同元组，如果希望出现在同一谓词表达式中，就需要通过构造原关系的两个副本（不同名称），它们的笛卡尔积上面再使用where，才能实现。否则你无法区分它是来自哪一个副本。
• 更名的新名称又称为：
  • 相关名称/相关变量
  • 元组变量、表别名（在from的角度）

字符串运算 #

• SQL提供很多对字符串进行操作的函数。详见docs。例如lower（）、upper（）。
• 模式匹配 like /not like/similar to（similar to支持正则表达式）
  • %表示任意字符串
  • _表示任意字符
  • 例如：where building like '% Watson%'表示包含Watson的字符串。
  • 特殊用法：三个下划线表示三个字符构成的字符串；三个下划线+一个百分号表示至少三个字符构成的字符串。
• 模式匹配中的转义字符 escape
  • SQL需要自己定义转义字符，利用escape关键字即可。其紧紧跟在like子句后面。
  • 例如：where building like 'ab\%cd%' escape '\'就是设定了反斜杠为转义字符，筛选了ab%cd开头的字符串。
• select子句的属性说明
  • 在select子句中，可用*代表“所有属性”。也就是不做删改地输出所有属性构成的关系。
  • 例如：select instructor.*

集合运算 #

• 集合运算是对两个关系进行的，且直接输出结果，不需要select。
• 因此，union两边必须连接的都是select语句。
• 集合运算既然是对集合的运算，其运算结果都是按照集合的定义来处理的，所以都是==默认自动去重==的。这点和select恰好==相反==！！如果要不让他去重，则集合运算的关键词后加all即可。
  • 在集合运算的结果中，如果两个元组其他取值相同，~且在同一个属性上也都为null，则它们可以被判定为完全一致~，最终只输出其中之一。

三个运算 #

• union运算
  • union
  • 如果不去重就union all
  • 例如：

(select course_id 
from section
where semester='fall')
union                  //注意，union两边连接的都是select语句，它连接了两个集合。
(select course_id
from section
where semester='spring')

• 另外，intersect、intersect all、except、except all同理。

空值 #

算术运算中的null #

• 任意数字和null进行算术运算，其结果都是null。

逻辑运算中的null和unknown #

• 任何数字和null进行比较运算，其结果都是unknown。
• null的非都是unknown。
• null和任何属性的and、or都是对方为false、true时才为false、true，否则为unknown。
• unknown和false的意思都是谓词逻辑为假。因此如果where子句的谓词逻辑得出了unknown，其依旧不被选中。

基本聚集函数中的null #

• 除了语句count(*)外，任何基本聚集函数都会忽略null。例如有5个元组，但是其中3个的对应属性都是null，则count聚集函数的输出是2.
• 如果输入的集合全都是null构成的，则它们都被忽略，相当于空集。聚集函数对输入空集的处理是：聚集函数的输出值是null。

is null、is not null #

用于测试是否为空值，例如where salary is null SQL中不允许“=null”、“<>null”的判断。

is unknown、is not unknown #

用于测试是否为unknown，例如where salary > cost is not unknown

两个属性为null，则它们相等的两种情况 #

这两种情况都发生在去重时。前者是手动声明去重，后者是自动去重。

• 当使用select distinct语句时，如果两个元组其他取值相同，且在同一个属性上也都为null，则它们可以被判定为完全一致，最终只输出其中之一。
• 在集合运算的结果中，如果两个元组其他取值相同，且在同一个属性上也都为null，则它们可以被判定为完全一致，最终只输出其中之一。

聚集函数 #

聚集函数的特点是：

1. 基本聚集函数：以一个集合作为输入，以单个值作为输出。这就好像把很多值~聚集~成了一个值的浓缩。
2. 分组聚集函数：还有一种是用于分组group by。这种函数是把很多分散在各处的同类别的元组~聚集~到一起来有序地排在一起。

基本聚集函数 #

• avg（只对数集）
  • 可以在求了avg后进行更名，以便于给这个新数据一个名字。例如：select avg(salary) as avg_salary
  • 注意在求avg时最好不用distinct，因为否则就会不准确了，参与统计的对象变少了。必要时可以使用all修饰，虽然本身就默认all了。
• min/max
  • 可以加distinct，这是合法的，尽管这样做没有任何意义。
• sum（只对数集）
• count
  • 常使用count(*)来统计关系中元组的总数。注意，count(distinct *)是非法的。
  • 常使用count(distinct sth.)来统计一对多对象中的那个一的个数，比如从老师教课关系中统计老师的总数：select count (distinct ID) from teaches

分组聚集函数 group by #

• 见group by子句描述。

查询操作（续） #

为什么查询结果关系又会称为“集合” #

• 这里的集合是元组的集合。因为之前说过了，对于一个关系，其中元组的顺序是不重要的。显然，这也可以称作一个集合。即一个==元组的集合==。

空关系测试——from中的exists子句 #

• exists子句判断其后的子句中的关系是否为空集（该关系是否存在元组），如果非空集则该谓词逻辑为真。另外，还有not exists对应，如果空集则谓词为真。
• exists的本意是存在，也就是说==存在这样一个集合，使得xxx==。
  • 可以利用not exists来模拟“包含”这一集合关系的判断，因为 not exists (B except A)的意思是B-A是空集，也就是B中的元素A中都有，B包含于A。

重复性测试——where中的unique子句 #

• unique子句中的关系中如果有重复元组，则为假。
  • 对于空集，unique也为真。
• unique的实现原理是判断两个元组有 t1=t2 的关系。因此如果有两个元组，它们在某个属性的取值都是NULL，那么 t1=t2 不成立，unique仍为假。
• not unique同理。如果有重复元素则为真，例如“找出选择了多门课的学生”。则只需要找出这个学生的选课记录，然后not unique作为where的条件。

相关子查询 #

• 相关子查询允许外层查询的一个更名可以允许内层的子查询使用，==以链接这两层查询。==
• 可以理解为局部变量的作用域：在局部变量作用域中再嵌套一层代码块，则此代码块中的算法能使用上述局部变量。但是在作用域外的则不能。

select course_id
from section as S
where semester='fall' and year=2009 and
exists(select * from section as T
      where semester='spring' and year=2010
       and S.course_id=T.course_id //相关子查询将此两层查询连接在一起
      )

这里的exists也就是说，存在 S.course_id=T.course_id的课程。（也就是集合不为空。）

标量子查询 #

• 标量子查询是只返回一个数值作为结果的查询。或者说，是只返回一个一行一列（单元组，单属性）的结果关系的查询。

• 标量子查询可以出现在数值本应出现的地方，即“我需要一个描述问题的值，但是我要现去查他。”。如果标量子查询返回了多行多列关系，那么会产生运行时错误。

• 典型的标量子查询：不带group by的count(*)，例如：

  • select dept_name,
    	(select count(*)
         from instructor
         where department.dept_name=instructor.dept_name  //体现了相关子查询的思想，即内层使用外层（理解为变量作用域）
        ) as num_instructors //对标量子查询进行更名（别名）
    from department
    

from子句子查询和lateral关键词 #

• 即：直接新查询一个关系出来给from子句，作为外层查询的一个范围之一。

• 支持as命名，且能直接定义查询出来的新关系的一切属性的名字和这个新关系的名字，以便外层开展查询，例如：

  select dept_name,avg_salary
  from (select dept_name, avg(salary) //在from中嵌套一个子查询
       from instructor
        group by dept_name)
        as dept_avg(dept_name,avg_salary) //给查询到的新关系进行元组式的全面命名
  where avg_salary > 12000
  

• 如果希望from中的嵌套子查询的描述使用from中给出的其他关系进行描述，则可用lateral关键字表示这个子查询想要使用前面from声明了的其他关系。例如:

  • select name,salary
    from instructor I1, lateral (select salary as sl
                                from instructor I2)
    where I2.dept_name=I1.dept_name
    

with子句——声明临时关系 #

• with子句能够声明临时关系（注意，因为是声明关系，所以应当首先给予各个属性和整个关系的命名）。该关系通过其后面跟着的子查询进行赋值，并只能在本次查询中使用。因此他是临时的。

• with子句可以被from子查询完全替代。但是写成with更加清晰。因为他就像C++变量声明一样提前声明好了。

• with子句可以一次性声明很多个关系，with只需要写一次，as是每个关系写一次。多个关系中间用逗号连接。

• 例如：

  • with 
    	dept_total(dept_name,value) as (
        	select dept_name,sum(salary)
            from instructor
            group by dept_name
        ), dept_total_avg(value) as(
            select avg(value)
            from dept_total
        )  //这里连续声明了两个临时关系
    select dept_name
    from dept_total,dept_total_avg  //使用临时关系进行查询
    where dept_total.value >= dept_total_avg.value
    

数据库的修改 #

删除 #

• 删除操作由 delete from 和 where 两部分描述。表示从哪个关系删除满足哪些条件的元组。
• 注意，删除操作不支持删除属性。因为这样会殃及其他不想被删除的元组的属性，且删除属性就相当于动了这个关系的模式，对于其他想要调用它的应用程序来说是出错的。
• 就像select那样，如果where为空或者不写，那么就相当于where为永真。因此在这种情况下会==清空==这个关系内的所有==元组==。
• where 子句和 select 中的where子句是一样的。都支持那些复杂的查询。
• 注意删除的顺序性：例如我们要删除平均工资>12000的系的教师和删除平均工资>9000的系的教师，那么前者删除后会影响到后者，因为==人变少了==，平均工资会发生变化。
  • 因此，delete的处理结果取决于其处理顺序。

插入 #

• 插入语句由insert into和values或者select-from-where子查询构成。因此显然其支持插入==元组==或插入关系（即==元组集合==）。

• 插入语句是先执行select-from-where子查询，然后再执行insert。否则会出现死循环问题导致插入了无穷多的元组。例如：

  • insert into student
        select *
        from student
    

  • 这段代码的本意是复制student的所有元组到自己。但是如果执行顺序不是如上所说，那么很显然每次复制的元组个数是n+1,n+2,n+3,…直到无穷。

• 按属性排列的原顺序插入元组

  • 要求用户根据属性排列的原顺序填入新元组的值。例如：

  • insert into course
    	values ('CS-437','database systems','comp. sci.',4)
    

• 用户指定需要插入的属性以插入新元组

  • 不要求用户记住原来的属性排列，而是可以直接想到什么插入什么。这通过在目标关系的后面声明属性名称实现。例如：

  • insert into course (course_id,title,dept_name,credits)
    	values ('CS-437','database systems','comp.sci.',4)
    

• 直接插入元组集合（关系）

  • 不使用values关键字了，而是直接替换为select-from-where子查询。

  • 因此可以认为是通过嵌套子查询实现。例如：

  • insert into instructor
    	select ID,name,dept_name,18000 //直接指定属性的值。这样插入的元组在此属性上面都是18000的取值。
    	from student
    	where dept_name = 'music'
    

更新 #

• 更新操作可以理解为：对集合中某些元组单独进行数值上的修改，而不影响其他不参与修改的元组。

• 更新语句由update、set、where构成。update后面是目标关系，set是要设置的属性和其值，where用于限定update关系中需要更新的元组（取出需要更新的子集）。

  • set子句直接使用 A=b的形式。这里等号就不是判断了，而是赋值。

    • 由于这里出现了值，所以可以使用==标量子查询==和==case==结构。（这两种结构==都是==可以出现在数值需要出现的位置。）

  • update可以直接理解为from。因此其也支持==相关子查询==。

  • 相关子查询、标量子查询、case 结构的共存例子：统计学分

    • update student S
      set total_cred=( 
      	select case //case分支结构
              when sum(credits) is not null then sum(credits) //标量子查询
              else 0
          end
          from takes natural join course
          where S.id=takes.id and //S是相关子查询
          	takes.grade<>'F'
      )
      

• 需要注意update的顺序问题

  • 例如给工资超过10000的老师上涨3%工资，给工资小于等于10000的老师上涨5%的工资，则如果顺序对调，则会导致工资略小于10000的老师上涨了8%的工资。

case结构 #

case
	when pred1 then result1
	when pred2 then result2
	...
	else resultn
end

• 可以出现在任何出现数值的地方。
• when后面是谓词逻辑，和where后面的是一样的。

补缺 #

关系的模式和关系的实例、数据库的模式和数据库的实例 #

• 即关系的逻辑设计和关系在某一时刻的快照。
• 即数据库整个的逻辑设计和关系在某一时刻的快照。