数据库概论
数据库的4个基本概念
-
数据（Data）
- 定义：描述事物的符号记录。
- 数据的含义称为数据的语义，数据的语义是指对具体数值进行的解释说明。数据与其语义是不可分的。
- 数据是数据库中存储的基本对象。
-
数据库（Database，DB）
- 定义：数据库是长期储存在计算机内、有组织的、可共享的大量数据的集合。
数据厍的基本特征
- 数据按一定的数据模型组织、描述和储存
- 可为各种用户共享、冗余度较小、易扩展
- 数据独立性较高
-
数据库管理系统（DataBase Management System，DBMS)
- 位于用户应用与操作系统之间的一层数据管理软件；
- 跟操作系统一样是计算机的基础软件，是一个大型复杂的软件系统。
主要功能：
- 数据定义功能：提供数据定义语言（DDL）；定义数据库中的数据对象；
- 数据组织、存储和管理：分类组织、存储和管理各种数据；确定数据在存储级别上的结构和存储方式；实现数据之间的练习；提供多种存取方法提高存取效率；
- 数据操纵功能：提供数据操纵语言（DML）；实现对数据库的基本操作（查询、插入、删除和修改）；
- 数据库的事务管理和运行管理：数据的安全性、完整性、多种对用户数据的并发使用；发生故障后的系统恢复数据库；
- 数据库的建立和维护功能：提供实用程序工具，完成数据库数据批量装载，数据库转储，介质故障恢复，数据库的重组织和性能监视等。
-
数据库系统（DataBase System，DBS）
- 是由数据库、数据库管理系统（及其应用开发工具）、应用程序和**数据库管理员（DBA）**组成的存储、管理、处理和维护数据的系统。
- Q: 什么是数据库系统？
- 是指在计算机系统中引入数据库后的系统构成；
- 在不引起混淆的情况下常常把数据库系统简称为数据库。
- 数据库系统的特点？
- 数据结构化；
- 数据的共享性高，冗余度小且易扩充；
- 数据独立性高；
- 数据由数据库管理系统统一管理和控制；
数据库的发展阶段
- 人工管理阶段：数据不保存、不共享，不具有独立性。
- 文件系统阶段：可保存，但共享性差，冗余度大，独立性差
- 数据库管理系统：数据库系统的出现使信息系统从以 加工数据的程序 为中心转向围绕 共享的数据库 为中心的新阶段
- 数据的共享性高、冗余度低且易扩充：数据共享可以大大减少数据冗余，节约存储空间数据独立性高
- 物理独立性：是指用户的应用程序与数据库中数据的物理存储是相互独立的。
逻辑独立性：是指用户的应用程序与数据库的逻辑结构是相互独立的。
数据模型
数据模型是对现实世界数据特征的抽象。
数据模型分为两类（两个不同的层次）
-
概念模型，也称信息模型；
它是按用户的观点来对数据和信息建模，用于数据库设计
- 实体Entity：客观存在并相互区别的事物称为实体。
- 属性：实体所具有的某一特性称为属性。一个实体可以由若干个属性来刻画。
- 码：唯一标识实体的属性集称为码。
- 实体型：用实体名及其属性名集合来抽象和刻画同类实体称为实体型。
- 实体集：同一类型实体的集合称为实体集
- 联系Relation：现实世界中事物内部以及事物之间的联系在信息世界中反映为实体（型）内部的联系和实体（型）之间的联系；
- 实体内部的联系：是指组成实体的各属性之间的联系；
- 实体之间的联系：通常是指不同实体集之间的联系；实体之间的联系有一对一（1：1），一对多（1：n），多对多（m：n）。
-
逻辑模型和物理模型。
-
逻辑模型∶它是按计算机系統的观点对数据建模，主要用于数据库管理系统的实现。主要包括网状模型、层次模型、关系模型、面向对象数据模型、对象关系数据模型、半结构化数据模型等
按计算机系统的观点对数据建模，用于DBMS实现。
-
物理模型：是对数据最底层的抽象，它描述数据在系统內部的表示方法和存取方法，或在磁盘或磁带上的存储方式和存取方法，是面向计算机系统的。
-
数据模型由三部分组成:
-
数据结构–描述系统的静态特征；
-
数据操作–描述系统的动态特征；
-
完整性约束。
完整性规则：给定的数据模型中数据及其联系所具有的制约和依存规则。
层次模型，网状模型
常用的数据模型：
- 格式化模型：层次模型，网状模型。
- 关系模型：最常用的模型。
- 对象模型：面向对象数据模型，对象关系数据模型。
层次模型
用树形结构来表示各类实体以及实体间的联系。
表示方式：
- 实体型：用记录类型描述每个结点表示一个记录类型（实体）；
- 属性：用字段描述每个记录类型可包含若干个字段；
- 联系：用结点之间的连线表示记录类型之间的一对多的父子联系
层次模型的优缺点
1）优点
层次模型的数据结构比较简单清晰；
查询效率高，性能优于关系模型，不低于网状模型；
层次数据模型提供了良好的完整性支持；
2）缺点
结点之间的多对多联系表示不自然；
对插入和删除操作的限制多，应用程序的编写比较复杂；
查询子女结点必须通过双亲结点；
层次数据库的命令趋于程序化。
关系模型
- 在用户的观点下，关系模型中数据的逻辑结构是一张二维表。
- 关系必须是规范化的，满足一定的规范条件。
- 最基本的规范条件是关系的每一个分量必须是一个不可再分的数据项，不允许表中还有表。
注：关系数据模型是目前最重要的一种数据模型，它的三个要素分别是数据结构、关系操作、完整性约束
数据结构
关系：一个关系对应通常说的一张表。
元组：表中的一行。
属性：表中的一列。
码：也称码键，表中的某个属性组
域：是一组具有相同数据类型的值的集合：学生年龄属性的域（15-45岁）、性别的域（男、女）
分量：元组中的一个属性。
关系模式：对关系的描述
注：**关系数据模型的基本数据结构是？**A:关系
关系必须是规范化的，满足一定的规范条件。最基本的规范条件：关系的每一个 分量必须是一个不可分的数据项，不允许表中还有表。
关系模型的操纵与完整性约束
（1）数据结构是集合操作集合操作，操作对象和操作结果都是关系。
（2）提高了数据的独立性，提高了用户生产率。
（3）关系的完整性约束条件？
实体完整性，参照完整性；（前两者为关系的两个不变性）用户定义的完整性。
关系模型的优缺点
（1）优点
- 建立在严格的数学概念的基础上；
- 概念单一；
- 实体和各类联系都用关系来表示
- 对数据的检索结果也是关系
- 关系模型的存取路径对用户透明；
- 具有更高的数据独立性，更好的安全保密性
- 简化了程序员的工作和数据库开发建立的工作
（2）缺点
- 存取路径对用户透明，查询效率往往不如格式化数据模型；
- 为提高性能，必须对用户的查询请求进行优化，增加了开发数据库管理系统的复杂性。
数据库系统的结构
从数据库应用开发人员角度看：数据库系统采用三级模式结构，是数据库系统内部的系统结构
从数据库最终用户角度看：数据库系统的结构有：
- 单用户结构
- 主从式结构
- 分布式结构
- 客户-服务器
- 浏览器-应用服务器/数据服务器
三级模式-二级映像
模式(Schema):
- 是对数据库逻辑结构和特征的描述
- 是型的描述，不涉及具体值
- 模式是相对稳定的
实例（Instance）
- 数据库某一时刻的状态——模式的一个具体值
- 同一个模式可以有很多实例
- 实例随数据库中的数据的更新而变动
三级模式：
- 外模式（子模式or用户模式）
- 数据库用户使用的局部数据的逻辑结构和特征的描述
数据库用户的数据视图，是与某一应用有关的数据的逻辑表示 
- 数据库用户使用的局部数据的逻辑结构和特征的描述
- 模式（逻辑模式）
- 数据库中全体数据的逻辑结构和特征的描述
所有用户的公共数据视图 
- 数据库中全体数据的逻辑结构和特征的描述
- 内模式（存储模式）
- 是数据物理结构和存储方式的描述
- 是数据在数据库内部的表示方式
二级映射
-
外模式/模式 映射：保证数据的逻辑独立性
- 当模式改变时，数据库管理员对外模式/模式映像作相应改变，使外模式保持不变
- 应用程序是依据数据的外模式编写的，应用程序不必修，保证了数据与程序的逻辑独立性，简称数据的逻辑独立性
-
模式/内模式 映射：保证数据的物理独立性
-
定义了数据全局逻辑结构与存储结构之间的对应关系。
如，说明某个逻辑记录对应何种存储结构。 -
数据库中模式/内模式映象是唯一的。
-
保证数据的物理独立性
当数据库的存储结构改变了（例如选用了另一种存储结构），数据库管理员修改模式/内模式映象，使模式保持不变。模式不变，则应用程序不变。保证了数据与程序的物理独立性，简称数据的物理独立性。
-
码
- 候选码（超级码）就就是可以区别一个元组（即表中的一行数据）的属性或属性的集合。候选码是最小的超码
- 如何选出候选码？
- 只出现在左边的一定是候选码
- 只出现在右边的一定不是候选码
- 左右都出现的不一定
- 左右都不出现的一定是候选码
- 再求确定的候选码的闭包，如果可以推出全部，那么当前确定的就是候逐码，否则你要吧每一个可能的值放进当前确定的候选码里面进行求闭包
- 如何选出候选码？
- 主码：从候选码里面任意跳出一个作为主码
- 主属性：包含在所有候选码的属性比如 ABCDE
- 非主属性：不包含在候选码中的属性，上题为G
- 全码：所有的属性都是主码
范式
Q:为什么要引入范式？
1数据冗余
2更新异常
3插入异常
4删除异常
A: 设计关系数据库时，遵从不同的规范要求，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式（4NF）和第五范式（5NF，又称完美范式）。
一般来说，数据库只需满足第三范式（3NF）就行了。
- 1NF:所有字段值都是不可分解的原子值
- 2NF:不包含非主属性对码的部分函数依赖：一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中
- 3NF:不包含非主属性对码的传递函数依赖确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
- BCNF:消除每一属性对候选键的传递依赖，BCNF是修正的第三范式
函数依赖简单点说就是：某个属性集决定另一个属性集时，称另一属性集依赖于该属性集。
公理系统
Armstrong公理系统（ Armstrongs axiom）设U为属性集总体，F是U上的一组函数依赖，于是有关系模式R(U,F)，对R<U,F>来说有以下的推理规则
- A1自反律（ reflexivity rule）：若 Y∈X∈U，则X→Y为F所蕴涵。
- A2增广律（ augmentation rule）：若X→y为F所蕴涵，且Z∈U，则XZ→Yz为F所蕴涵。
- A3传递律（ transitivity rule）：若X→Y及P→z为F所蕴涵，则X→Z为F所蕴涵
根据A1、A2、A3这三条推理规则可以得到下面三条很有用的推理规则。
- 合并规则（ union rule）：由X→Y,X→Z，有X→YZ
- 伪传递规则（ pseudo transitivity rule）：由X→Y,WY→Z，有X→乙
- 分解规则（ decomposition rule）：由X→Y及Z∈Y，有X→Z。
依赖
Q：什么是依赖？
A：依赖是指关系中一个或一组属性的值可以决定其它属性的值比如A→>B这就是一个依赖
Q: 如何求最小依赖集？
1）拆右边为多个元素的比如A->BCH拆为A>B和A->C
2）出去当前元素，求它的闭包，把集合里面所有元素都弄完
3）左边最小化（通过遮住元素来看能不能退岀其他元素）比如BCD，遮住B看能退出CD吗，遮住C能退出BD吗遮住D看能退出BC吗
关系型数据库
关系模型
关系数据结构
关系模式（ Relation Schema）是型；关系是值
关系模式是对关系的描述
- 元组集合的结构
●属性构成
●属性来自的域
●属性与域之间的映象关系 - 完整性约束条件
关系模式可以形式化地表示为：R（U, D, DOM, F）
| R | 关系名 |
|---|---|
| U | 组成该关系的属性名集合 |
| D | U中属性所来自的域 |
| DOM | 属性向域的映象集合 |
| F | 属性间数据的依赖关系的集合 |
关系模式
■对关系的描述
■静态的、稳定的
关系
■关系模式在某一时刻的状态或内容
■动态的、随时间不断变化的
关系完整性约束
- 实体完整性和参照完整性
关系模型必须满足的完整性约束条件称为关系的两个不变性，应该由关系系统自动支持- 实体完整性（ Entity Integrity）
- 关系的主属性不能取空值
- 参照完整性：
- 若属性（或属性组）F是基本关系R的外码，他与基本关系S的主码K相对应（基本关系R和S也可以是同一个关系），则对于R中每个元组在F上的取值必须为：或取空值（F的每个属性值均为空值）；或者等于S中某个元组的主码值。
- 案例
- 1:学生(学号，姓名，性别，年龄，系号)
2:系(系号，系名，系主任)
学生关系的属性“系号”与系关系的主码“系号”相对应，因此，“系号”属性是学生
关系的外码。这里系关系是被参照关系，学生关系为参照关系；学生关系中的每个元组的“系号”属性只能取下面两类值：空值或系关系中“系号”已经存在的值。 - 1.学生(学号，姓名，性别，年龄，系号，班长学号)
学生关系的“班长学号”与其主码“学号”形成参照和被参照的自身参照关系，即“班
长学号”为学生关系的外码。学生关系中的每个组的“班长学号”属性只能取下面两类值：
空值或学生关系中“学号”已经存在的值。
- 1:学生(学号，姓名，性别，年龄，系号)
- 实体完整性（ Entity Integrity）
- 用户定义的完整性
- 应用领域需要遵循的约束条件，体现了具体领域中的语义约束
- 用户对数据自定义的域约束值，如“学分”属性只能取值{1,2,3,4}
域–>笛卡尔积
所有域的所有取值的任意组合
笛卡尔积可以看着是关系的“域”。
笛卡尔积的表示方法
- 笛卡尔积可表示为一张二维表
- 表中的每行对应一个元组，表中的每列对应一个域
关系代数
关系代数语言:用对关系的运算来表达查询要求
关系演算语言：用谓词来表达查询要求
- 元组关系演算语言
●谓词变元的基本对象是元组变量
●代表：APLHA,QUEL - 域关系演算语言
●谓词变元的基本对象是域变量
●代表：QBE
具有关系代数和关系演算双重特点的语言
■代表：SQL（ Structured Query Language）
关系代数是一种抽象的查询语言，它用对关系的运算来表达查询
运算对象是关系
运算结果亦为关系
关系代数的运算符有两类：集合运算符和专门的关系
运算符
关系操作(关系代数运算)
插入、查询、删除、修改
其中查询操作分为：选择、投影、连接、除法、并、差、交、笛卡尔积
五种基本关系代数运算：选择、投影、并、差、笛卡尔积
- ∪
- ∩
-
以上三个都需要具有相同的属性（相同目），结果的属性也是一样的
- 笛卡尔积
两个分别为n目和m目的关系R和S的笛卡尔积是一个（n + m）列的元组的集合。若R有k1个元组，S有k2个元组，则R和S的笛卡尔积有k1 * k2个元组。
选择和投影的区别
选择的是一行记录，投影是你所需的某个字段，两者相结合为最终结果.
选择是从行的角度进行的运算；投影操作是从列的角度进行的运算。选择是在关系R中选择满足给定条件的诸元组，投影是从R中选择出若干属性列组成新的关系。
连接：
- 自然连接：把共同的属性进行等值连接。
- ★先把所有属性去重列出来，将等值的共同属性填入后，补充其他属性值
- 悬浮元组（ Dangling tuple）：两个关系R和S在做自然连接时，关系R中某些元组有可能在S中不存在公共属性上值相等的元组，从而造成R中这些元组在操作时被舍弃了，这些被舍弃的元组称为悬浮元组。
- 外连接（ Outer Join）：如果把悬浮元组也保存在结果关系中，而在其他属性上填空值（Nu1U），就叫做外连接
- 左外连接（ LEFT OUTER J0IN或 LEFT J0IN）：只保留左边关系R中的悬浮元组
- 右外连接（ RIGHT0 DUTER J0IN或 RIGHT J0IN）：只保留右边关系S中的悬浮元组
除
保留R中满足S的，而且R中列要去掉S的列
注：
- 关系中，属性个数称为「度」或「目」，3度表示这个表有3列。
- 关系中元组的个数称为「基数」。
关系完整性
- 实体完整性：主码唯一且非空
- 参照完整性：外码要么为空，要么对应另一表的主码
- 用户定义完整性：你自己写的要完整比如学号多少姓名班级专业之类
关系代数
是一种抽象的查询语言，它用对关系的运算来表达查询。
三大要素：运算对象（关系）、运算符（集合运算符和专门的关系运算符）和运算结果（关系）
SQL语言
Structure Query Language，结构化查询语言，
- 数据定义语言DDL（ Data Ddefinition Language）sQL数据定义语言主要用来定义逻辑结构，包括定义基表，视图和索引。
删除表、定义表、修改表 - 数据查询语言DQL（ Data Query Language）：s0L的数据查询语言主要用来对数据库中的各种数据对象进行查询。
- 数据操纵语言DML（ Data Manipulation Language）：s0L的数据操纵语言，用于改变数据库中的数据，包括插入，删除，修改
- 数据控制功能DCL（ Data Control Language）：s0L的数据控制语言，对表和视图的授权，完整性规则的描述以及事务开始和结束等控制语句。
SQL语言特点
- 综合统一（独立完成数据库生命周期中的全部活动，包括定义关系模式、录入数据、建立数据库、査询、更新、维护、数据库重构、数据库安全）
- 高度非过程化（用户只需提出“做什么”，而不必指明“怎么做”。）
- 非关系数据模型的数据操纵语言“面向过程”，必须指定存取路径。
- SQL只要提出“做什么”，无须了解存取路径
- 存取路径的选择以及SQL的操作过程由系统自动完成
- 面向集合的操作方式（SQL采用集合操作方式）
- 非关系数据模型采用面向记录的操作方式，操作对象是一条记录
- SQL采用集合操作方式
- 操作对象、查找结果可以是元组的集合
- 一次插入、删除、更新操作的对象可以是元组的集合
- 以同一种语法结构提供两种使用方式（S0L既是自含式（独立的）语言，又是嵌入式语言。SQ语句能够嵌入到高级语言程序中，）
- 语言简洁，易学易用（s0L语言语法简单，接近英语口语，因此容易学习，也容易使用。）
- SQL功能性极强，完成核心功能只用了9个动词：SELECT（数据查询）；CREATE、ALERT、INSERT（数据定义）；DROP、UPDATE、DELETE（数据操纵）；GRANT、REVOKE(数据控制)
SQL的基本概念
基本表
- 本身独立存在的表
- SQL中一个关系就对应一个基本表
- 一个（或多个）基木表对应一个存储文件
- 一个表可以带若干索引
视图
概念
- 从一个或几个基本表导出的表
- 数据库中只存放视图的定义而不存放视图对应的数据
- 视图是一个虚表
- 用户可以在视图上再定义视图
数据库安全性
数据库的不安全因素
- 非授权用户对数据库的恶意存取和破坏
- 一些黑客（ Hacker）和犯罪分子在用户存取数据库时猎取用户名和用户口令，然后假冒合法用户偷取、修改甚至破坏用户数据。
- 数据库管理系统提供的安全措施主要包括用户身份鉴别、存取控制和视图等技术。
- 数据库中重要或敏感的数据被泄露
- 黑客和敌对分子千方百计盗窃数据库中的重要数据，些机密信息被暴露
- 数据库管理系统提供的主要技术有强制存取控制、数据加密存储和加密传输等。
- 审计日志分析
- 安全环境的脆弱性
安全性控制
用户身份鉴别
- 静态口令
- 动态口令
- 智能卡鉴别
- 生物特征鉴别
存取控制
机制组成
- 定义用户权限
- 合法权限检查
自主存取控制（DAC）：
- 用户对不同的数据对象有不同的存取权限
- 不同的用户对同一对象也有不同的权限
- 用户还可将其拥有的存取权限转授给其他用户
通过SQL的 GRANT语句（权限授予）和 REVOKE语句（权限回收）实现
-
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18GRANT语句的一般格式
GRANT<权限>[<权限>]
ON<对象类型><对象名><对象类型><对象名
To<用户>,【<用户>】
【WITH GRANT OPTION】； /** 指定该语句表示可以再转授权限*/
GRANT ALL PRIVILEGES
ON TABLE Student Course
TO U2. U3
REVOKE语句的一般格式为：
REVOKE<权限>[,<权限>]
ON<对象类型><对象名>【<对象类型><对象名>】
FROM<用户>【<用户>】. [CASCADE| RESTRICT]；
REVOKE UPDATE（Sno）
ON TABLE Student
FROM U4
数据库角色：被命名的一组与数据库操作相关的权限
- 角色是权限的集合
- 可以为一组具有相同权限的用户创建一个角色
- 简化授权的过程
使用角色管理数据库权限
1 | /** S1.角色创建*/ |
存在的缺点：可能存在数据的无意泄露：自主存取控制仅仅通过对数据的存取权限来进行安全控制，而数据本身并无安全性标记
强制存取控制（MAC）：
- 保证更高程度的安全性
- 用户不能直接感知或进行控制
- 适用于对数据有严格而固定密级分类的部门
- 军事部门
- 政府部门
在强制存取控制中，数据库管理系统所管理的全部实体被分为主体和客体两大类
- 主体是系统中的活动实体
■数据库管理系统所管理的实际用户
■代表用户的各进程 - 客体是系统中的被动实体，受主体操纵
■文件、基本表、索引、视图
敏感度标记（ Label）
■对于主体和客体，DBMS为它们每个实例（值）指派一个敏感度标记（Labe）
■敏感度标记分成若干级别
●绝密（ Top Secret,Ts）
●机密（ Secret,S）
●可信（ Confidential,C）
●公开（ Public,P）
●Ts>=S>=C>=P
数据库安全性机制——视图机制
授予用户查询某些行的权限？
-
需要用存取谓词来定义用户权限
-
无法直接用 GRANT语句实现
-
可以用视图机制间接地实现
-
1
2
3
4
5
6
7
8
9/** 授予用户查询整个表的权限 */
GRANT SELECT
ON TABLE Student
TO U1
/** 授予用户查询某些列的权限 */
GRANT SELECT（Sno, Sname）
ON TABLE Student
TO U2 -
使用视图
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16/** Step1. 从基本表上导出数据建立视图 */
先建立计算机系学生的视图 Cs Student
CREATE VIEW CS_Student
AS
SELECT
FROM Student
WHERE Sdept='CS'
GRANT SELECT
ON CS_STUDENT TO U1
/** Step2. 在视图上进一步定义存取权限 */
GRANT SELECT
oN CS_Student
To 王平
GRANT ALL PRIVILIGES
oN CS Student
To张明；
-
审计
- 用一个专用的审计日志（ Audit Log）
将用户对数据库的所有操作记录在上面 - 审计员利用审计日志
监控数据库中的各种行为
发现非法存取，发现潜在威胁 - C2以上安全级别的DBMS必须具有审计功能
可以被审计的事件：
- 服务器事件
- 系统权限
- 对系统拥有的结构或模式对象进行操作的审计
- 要求该操作的权限是通过系统权限获得的
- 语句事件
●对SQL语句，如DDL、DML、DQL及DCL语句的审计 - 模式对象事件
●对特定模式对象上进行的 SELECT或DML操作的审计
审计功能的可选性
■审计很费时间和空间
■DBA可以根据应用对安全性的要求，灵活地打开或关
闭审计功能
■审计功能主要用于安全性要求较高的部门
demo
1 | // 对修改SC表结构或修改SC表数据的操作进行审计 |
数据加密
加密方法
- 存储加密
- 透明存储加密： 内核级加密保护方式（性能好，安全完备性高），对用户完全透明
- 非透明存储加密：通过多个加密函数实现
- 传输加密
- 链路加密：报文、报头都加密
- 端到端加密：只加密报文
其他安全性保护
- 推理控制、
- 隐蔽信道
数据定义
定义数据库的各种“对象”
-
模式定义
-
定义模式实际上定义了一个命名空间（或者说目录）
-
在这个空间中可以定义该模式包含的数据库对象，例如基本表、视图、索引等。
-
在 CREATE SCHEMA中可以接受 CREATE TABLE，CREATE VIEW和 GRANT子句。
-
创建：
CREATE SCHEMA<模式名> AUTHORIZATION<用户名>【<表定义子句>|<视图定义子句>|<授权定义子句> -
删除：
DROP SCHEMA<模式名>< CASCADE | RESTRICT>
■ CASCADE（级联）- 删除模式的同时把该模式中所有的数据库对象全部删除
■ RESTRICT（限制）
- 如果该模式中定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。
- 仅当该模式中没有任何下属的对象时才能执行。
-
-
表定义
-
1
2
3
4
5CREATE TABLE<表名> /*基本表的名称*/
(<列名><数据类型>【<列级完整性约束条件>】/*组成该表的列*/
【，<列名><数据类型【<列级完整性约束条件】】
...
【，<表级完整性约束条件>】) -
<列级完整性约束条件>：涉及相应属性列的完整性约束条件
<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件
如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上。
-
-
视图定义
-
索引定义
-
建立索引的目的：加快查询速度
-
关系数据库管理系统中常见索引
-
顺序文件上的索引
-
B+树索引（参见爱课程网3.2节动画《B+树的增删改》）
-
散列（hash）索引
-
位图索引
-
-
特点
- B+树索引具有动态平衡的优点
- HASH索引具有查找速度快的特点
-
关系数据库管理系统自动选择合适的索引作为存取路径，用户不必也不能显式地选择索引
-
CREATE[UNIQUE] |[CLUSTER] INDEX <索引名> ON <表名>（<列名>【<次序>】【列名>【<次序】】）- <表名>：要建索引的基本表的名字
- 索引：可以建立在该表的一列或多列上，各列名之间用逗号分隔
- 次序>：指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：AsC
- UNIQUE：此索引的每一个索引值只对应唯一的数据记录
- CLUSTER：表示要建立的索引是聚簇索引
-
ALTER INDEX<旧索引名> RENAME TO<新索引名>
-
数据字典
数据字典是关系数据库管理系统内部的一组系统表，它记录了数据库中所有对象的定义信息以及一些统计信息
关系数据库管理系统在执行SQL的数据定义语句时，实际上就是在更新数据字典表中的相应信息。
数据库的完整性
实体完整性
参照完整性
用户定义的完整性
完整性约束命名字句
CONSTRAINT<完整性约束条件名><完整性约束条件>- <完整性约束条件>包括 NOT NULL、 UNIQUE、PRIMARY KEY短语、 FOREIGN KEY短语、 CHECK短语等
- 删除：
ALTER TABLE Student DROP CONSTRAINT C1 - 增加:
ALTER TABLE Student ADD CONSTRAINT C1 CHECK（Sno BETWEEN 900000 AND 999999
域中的完整性限制
断言
- sQL中，可以使用 CREATE ASSERTION语句，通过声明性断言来指定更具一般性的约束。
- 可以定义涉及多个表的或聚集操作的比较复杂的完整性约束。
- 断言创建以后，任何对断言中所涉及的关系的操作都会触发关系数据库管理系统对断言的检查，任何使断言不为真值的操作都会被拒绝执行
CREATE ASSERTION<断言名>< CHECK子句>
触发器
- 是用户定义在关系表上的一类由事件驱动的特殊过程
- 任何用户对表的增、删、改操作均由服务器自动激活相应的触发器
- 触发器可以实施更为复杂的检查和操作，具有更精细和更强大的数据控制能力
定义：
- 当特定的系统事件发生时，对规则的条件进行检查，如果条件成立则执行规则中的动作，否则不执行该动作。规则中的动作体可以很复杂，通常是一段SQL存储过程。
- 触发器又叫做事件-条件-动作（ event-condition-action）规则
1 | CREATE TRIGGER<触发器名> {BEFORE| AFTER} <触发事件>ON<表名> |
- 只有表的拥有者才能在表上创建触发器
- 触发事件：INSERT、UPDATE、DELETE； 或者以上操作的组合； UPDATE OF<触发列>
- 触发器类型
- 行级触发器（ FOR EACH ROW）
- 语句级触发器（ FOR EACH STATEMENT）
- 例子：UPDATE TEACHER SET Deptno=5，假设表 TEACHER有1000行。如果是语句级触发器，那么执行完该语句后，触发动作只发生一次；如果是行级触发器，触发动作将执行1000次
- 触发条件：
- 触发器被激活时，只有当触发条件为真时触发动作体才执行；否则触发动作体不执行。
- 如果省略WHEN触发条件，则触发动作体在触发器激活后立即无条件执行】、
- 触发动作体：
- 触发动作体可以是一个匿名 PL/SQL过程块，也可以是对已创建存储过程的调用
- 如果是行级触发器，用户都可以在过程体中使用NEW和OLD引用事件之后的新值和事件之前的旧值如果是语句级触发器，则不能在触发动作体中使用NEW或OLD进行引用
- 如果触发动作体执行失败，激活触发器的事件就会终止执行，触发器的目标表或触发器可能影响的其他对象不发生任何变化
Demo Code:
1 | /** 当对表Sc的 Grade属性进行修改时，若分数增加了10%则将此次操作记录到下面表中：*/ |
数据查询语句
连接
自然连接的结果：是采用SELECT去掉重复字段的方式实施的
- 嵌套循环法：
- 排序合并法：
- 索引连接（嵌套循环的一个变种）
外连接与普通连接的区别
- 普通连接操作只输出满足连接条件的元组
- 外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出
- 左外连接
●列出左边关系中所有的元组 - 右外连接
●列出右边关系中所有的元组
- 左外连接
嵌套查询
一个 SELECT-FROM-WHERE语句称为一个查询块
将一个查询块嵌套在另一个查询块的 WHERE子句或 HAVING短语的条件中的查询称为嵌套查询
子查询的限制
●不能使用 ORDER BY子句
嵌套查询求解方法：
- 不相关子查询：子查询的查询条件不依赖于父查询
- 由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件
- 相关子查询：子查询的查询条件依赖于父查询 （嵌套循环依次从外解决）：demo讲解
- 首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层査询，若 WHERE子句返回值为真，则取此元组放入结果表
- 然后再取外层表的下一个元组
- 重复这一过程，直至外层表全部检查完为止
- 带有N谓词的子查询
- 带有比较运算符的子查询
- 带有ANY或ALL谓词的子查询
- 带有 EXISTS谓词的子查询
数据更新语句
-
插入 : INSERT INTO <Table_name>[<属性名>, <属性名>…] VALUES(<常量1>, …)
- 指定要插入数据的表名及属性列
- 属性列的顺序可与表定义中的顺序不一致
- 没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致
- 只指定部分属性列：插入的元组在其余属性列上取空值
- 指定要插入数据的表名及属性列
-
更新：UPDATE<表名>SET<列名>=<表达式>【<列名>=<表达式>】 【WHERE<条件习】；
■修改指定表中满足 WHERE子句条件的元组
■SET子句给出<表达式>的值用于取代相应的属性列
■如果省略 WHERE子句，表示要修改表中的所有元组关系数据库管理系统在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则
■实体完整性
■主码不允许修改
■用户定义的完整性- NOT NULL约束
- UNIQUE约束
- 值域约束
-
删除: DELETE FROM <表名> [WHERE<条件>]；
■删除指定表中满足 WHERE子句条件的元组WHERE子句
■指定要删除的元组
■无该子句将会删除表中的全部元组
附录
HAVING短语与 WHERE子句的区别：
- 作用对象不同
- WHERE子句作用于基表或视图，从中选择满足条件的元组
- HAVING短语作用于组，从中选择满足条件的组。、
Q:怎么理解“自然连接”，它与等值连接有什么不同？
1、自然连接一定是等值连接，但等值连接不一定是自然连接。
2、等值连接要求相等的分量，不一定是公共属性；而自然连接要求相等的分量必须是公共属性。
3、等值连接不把重复的属性除去；而自然连接要把重复的属性除去。
Q:嵌套查询瑞和转换为连接查询？
不相关子查询，内查询不依赖于外层。执行过程中，首先执行内查询，内查询得到结果不被显示出来，而是传递给外层查询作为外层查询的条件来使用，然后执行外层查询，并显示查询结果。 转为连接查询就是把子查询内表和外部表连接。
Q:两个查询在什么情况下可以自行UNION操作？
UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。 该命令连接的结果集中有重复的值不会被显示。想忽略重复值，可以使用 union all
Q:关系R中能唯一标识一个元组的是它的
- A.主码
- B.候选码
- D.全码
Q:实体完整性规则检查的内容包括
- 检查主码的各个属性值是否为空，只要有一个为空就拒绝插入或修改
- 检查主码值是否唯一，如果不唯一就拒绝插入或修改
在关系数据库中，模式对应的是___C___。
A.视图和所有基本表 B.视图和部分基本表 C.基本表 D.索引 7 在数据
数据库常见的四种故障
(1) 事务内部的故障：事务内部故障可分为预期的和非预期的，其中大部分的故障都是非预期的。预期的事务内部故障是指可以通过事务程序本身发现的事务内部故障; 非预期的事务内部故障是不能由事务程序处理的，如运算溢出故障、并发事务死锁故障、违反了某些完整性限制而导致的故障等。
(2) 系统故障：系统故障也称为软故障，是指数据库在运行过程中，由于硬件故障、数据库软件及操作系统的漏洞、突然停电灯情况，导致系统停止运转，所有正在运行的事务以非正常方式终止，需要系统重新启动的一类故障。这类事务不破坏数据库，但是影响正在运行的所有事务。
(3) 介质故障：介质故障也称为硬故障，主要指数据库在运行过程中，由于磁头碰撞、磁盘损坏、强磁干扰、天灾人祸等情况，使得数据库中的数据部分或全部丢失的一类故障。
(4) 计算机病毒故障：计算机病毒故障是一种恶意的计算机程序，它可以像病毒一样繁殖和传播，在对计算机系统造成破坏的同时也可能对数据库系统造成破坏(破坏方式以数据库文件为主) 。
Q:要实现记录的物理顺序与索引项次序一致，应选择的索引类型是（）。
A.HASH 索引
B.聚簇索引
C.B+树索引
D.单一索引
数据库编码问题
1 | 1、查看 MySQL 数据库服务器和数据库字符集 |
修改数据库默认的字符集
- 通过命令
1 | set character_set_client="utf8"; //客户端字符集 |
- 修改配置文件： LINUX 修改
vi/etc/my.cnf;WINDOWS my.ini
修改数据库的字符集
- ` mysql>use mydb``
- `` mysql>ALTER DATABASE mydb CHARACTER SET utf-8(utf8) COLLATE utf8_general_ci`
1 | 1》mysql> set names gbk; |
MySQL中的字符集转换过程
1. MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection;
2. 进行内部操作前将请求数据从character_set_connection转换为内部操作字符集，其确定方法如下：
使用每个数据字段的CHARACTER SET设定值;
- 若上述值不存在，则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展，非SQL标准);
- 若上述值不存在，则使用character_set_server设定值。
3. 将操作结果从内部操作字符集转换为character_set_results。
MySQL默认字符集
MySQL对于字符集的指定可以细化到一个数据库，一张表，一列，应该用什么字符集。 但是，传统的程序在创建数据库和数据表时并没有使用那么复杂的配置，它们用的是默认的配置，那么，默认的配置从何而来呢？
- 编译MySQL 时，指定了一个默认的字符集，这个字符集是 latin1；
- 安装MySQL 时，可以在配置文件 (my.ini) 中指定一个默认的的字符集，如果没指定，这个值继承自编译时指定的；
- 启动mysqld 时，可以在命令行参数中指定一个默认的的字符集，如果没指定，这个值继承自配置文件中的配置,此时 character_set_server 被设定为这个默认的字符集；
- 当创建一个新的数据库时，除非明确指定，这个数据库的字符集被缺省设定为character_set_server；
- 当选定了一个数据库时，character_set_database被设定为这个数据库默认的字符集；
- 在这个数据库里创建一张表时，表默认的字符集被设定为 character_set_database，也就是这个数据库默认的字符集；
- 当在表内设置一栏时，除非明确指定，否则此栏缺省的字符集就是表默认的字符集；
utf8mb4
MySQL的“utf8”实际上不是真正的UTF-8: MYSQL中的“utf8”字符集只支持每个字符最多三个字节，而真正的UTF-8是每个字符最多四个字节。(MySQL一直没有修复这个bug，他们在2010年(mysql 5.5.3)发布了一个叫作“utf8mb4”的字符集(Most byte 4用来兼容4字节的万国码)，绕过了这个问题。)
原来mysql支持的 utf8 编码最大字符长度为 3 字节，如果遇到 4 字节的宽字符就会插入异常了。三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xffff，也就是 Unicode 中的基本多文种平面（BMP）。也就是说，任何不在基本多文本平面的 Unicode字符，都无法使用 Mysql 的 utf8 字符集存储。包括 Emoji 表情（Emoji 是一种特殊的 Unicode 编码，常见于 ios 和 android 手机上），和很多不常用的汉字，以及任何新增的 Unicode 字符等等。
然而对于新字符集utf8mb4的出现，他们并没有广而告之（可能是因为这个bug让他们觉得很尴尬），以致于现在网络上仍然在建议开发者使用“utf8”，但这些建议都是错误的，当然真正做开发的人是知道的，这也是很多sql文件中看到建库建表都出现了utf8mb4的原因。
官方手册 https://dev.mysql.com/doc/refman/5.6/en/charset-unicode-utf8mb4.html 的说明：
1 | The character set named utf8 uses a maximum of three bytes per character and contains only BMP characters. The utf8mb4 character set uses a maximum of four bytes per character supports supplementary characters: |
MySQL在 5.5.3 之后增加了 utf8mb4 字符编码，mb4即 most bytes 4。简单说 utf8mb4 是 utf8 的超集并完全兼容utf8，能够用四个字节存储更多的字符。
但抛开数据库，标准的 UTF-8 字符集编码是可以用 1~4 个字节去编码21位字符，这几乎包含了是世界上所有能看见的语言了。然而在MySQL里实现的utf8最长使用3个字节，也就是只支持到了 Unicode 中的 基本多文本平面（U 0000至U FFFF），包含了控制符、拉丁文，中、日、韩等绝大多数国际字符，但并不是所有，最常见的就算现在手机端常用的表情字符 emoji和一些不常用的汉字，如 “墅” ，这些需要四个字节才能编码出来。
注：QQ里面的内置的表情不算，它是通过特殊映射到的一个gif图片。一般输入法自带的就是。
也就是当你的数据库里要求能够存入这些表情或宽字符时，可以把字段定义为 utf8mb4，同时要注意连接字符集也要设置为utf8mb4，否则在 严格模式 下会出现 Incorrect string value: /xF0/xA1/x8B/xBE/xE5/xA2… for column 'name'这样的错误，非严格模式下此后的数据会被截断。
提示：另外一种能够存储emoji的方式是，不关心数据库表字符集，只要连接字符集使用 latin1，但相信我，你绝对不想这个干，一是这种字符集混用管理极不规范，二是存储空间被放大（读者可以想下为什么）。
总结后的结论：
- MySQL的“utf8mb4”是真正的“UTF-8”。
- MySQL的“utf8”是一种“专属的编码”，它能够编码的Unicode字符并不多。
from:
utf8mb4_general_ci
字符除了需要存储，还需要排序或比较大小，涉及到与编码字符集对应的 排序字符集（collation）。ut8mb4对应的排序字符集常用的有 utf8mb4_unicode_ci、utf8mb4_general_ci，到底采用哪个在 stackoverflow 上有个讨论，What’s the difference between utf8_general_ci and utf8_unicode_ci
主要从排序准确性和性能两方面看：
- 准确性
utf8mb4_unicode_ci是基于标准的Unicode来排序和比较，能够在各种语言之间精确排序
utf8mb4_general_ci没有实现Unicode排序规则，在遇到某些特殊语言或字符是，排序结果可能不是所期望的。
但是在绝大多数情况下，这种特殊字符的顺序一定要那么精确吗。比如Unicode把ß、Œ当成ss和OE来看；而general会把它们当成s、e，再如ÀÁÅåāă各自都与A相等。 - 性能
utf8mb4_general_ci在比较和排序的时候更快
utf8mb4_unicode_ci在特殊情况下，Unicode排序规则为了能够处理特殊字符的情况，实现了略微复杂的排序算法。
但是在绝大多数情况下，不会发生此类复杂比较。general理论上比Unicode可能快些，但相比现在的CPU来说，它远远不足以成为考虑性能的因素，索引涉及、SQL设计才是。 我个人推荐是utf8mb4_unicode_ci，将来 8.0 里也极有可能使用变为默认的规则。相比选择哪一种collation，使用者应该更关心字符集与排序规则在db里要统一就好。
这也从另一个角度告诉我们，不要可能产生乱码的字段作为主键或唯一索引。我遇到过一例，以 url 来作为唯一索引，但是它记录的有可能是乱码，导致后来想把它们修复就特别麻烦。
from: mysql使用utf8mb4经验吐血总结
MySQL大表优化方案 Mysql的row_format(fixed与dynamic)
已有建库SQL：
autoplan
1 |
|
-
建表末尾的AUTO_INCREMENT：指定一个自增的初始值， 比如
AUTO_INCREMENT = 7则是要求ID从7开始自增（初始值为7) -
Mysql Row_Format 参数讲解
在MYSQL中， 若一张表里面不存在varchar、text以及其变形、blob以及其变形的字段的话，那么张这个表其实也叫静态表，即该表的row_format是fixed，就是说每条记录所占用的字节一样。其优点读取快，缺点浪费额外一部分空间。
么实际开发中,这种表很少,大部分表的字段类型都是有很多种的,那么这种表就叫做:dynamic :动态表 ,优点是节省空间,缺点是读取的时间的开销
ROW_FORMAT 的值如下:DEFAULT、FIXED、DYNAMIC、COMPRESSED、REDUNDANT、COMPACT
lottery
1 | /* |
Author: Mrli
Link: https://nymrli.top/2021/10/12/ZJU开学摸底考试——数据库概论复习/
Copyright: All articles in this blog are licensed under CC BY-NC-SA 3.0 unless stating additionally.