《数据库系统概论》知识点归纳——第三章

最新推荐文章于 2023-03-22 14:15:44 发布

VictorKa

最新推荐文章于 2023-03-22 14:15:44 发布

阅读量2.8k

点赞数 6

分类专栏：数据库文章标签：数据库

本文链接：https://blog.csdn.net/VictorKa/article/details/113144223

版权

数据库专栏收录该内容

5 篇文章 2 订阅

订阅专栏

3.1SQL概述

结构化查询语言（SQL）是关系数据库的标准语言。

SQL的特点：

综合统一
- SQL集数据定义语言、数据操纵语言、数据控制语言的功能于一体，可以独立完成数据库生命周期中的全部活动。
- 关系模型中数据结构的单一性带来了数据操作符的统一性，克服了非关系系统由于信息表示方式的多样性带来的操作复杂性。
高度非过程化
- 用SQL进行数据操作时，无需了解存取路径，存取路径的选择以及SQL的操作过程由系统自动完成。
面向集合的操作方式
- 非关系数据模型采用面向记录的操作方式，操作对象是一条记录。
- SQL采用集合操作方式，不仅操作对象、查找结果可以是元组的集合，一次插入、删除、更新操作的对象也可以是元组的集合。
以同一种语法结构提供多种使用方式
- SQL既能独立地用于联机交互的使用方式，又能够嵌入到高级语言程序中。
语言简洁，易学易用
- SQL的动词：

SQL的动词

SQL的基本概念：

支持SQL的关系数据库的外模式包括若干视图（view）和部分基本表（base table），模式包括若干基本表，内模式包括若干存储文件。

SQL关系数据库

可以用SQL对基本表和视图进行查询或其他操作，基本表和视图都是关系。
- 基本表是本身独立存在的表，在关系数据库管理系统中一个关系对应一个基本表。
- 一个或多个基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。
存储文件的逻辑结构组成了关系数据库的内模式。
存储文件的物理结构对最终用户是隐蔽的。
视图是从一个或几个基本表导出的表，数据库中只存放视图的定义不存放视图对应的数据，这些数据存放在到处视图的基本表中，因此视图是一个虚表，可以在视图上再定义视图。

3.3数据定义

SQL的数据定义功能包括模式定义、表定义、视图和索引的定义：

SQL不提供修改模式定义和修改视图定义的操作。
一个关系数据库管理系统的实例中可以建立多个数据库，一个数据库中可以建立多个模式，一个模式下通常包括多个表、视图和索引等数据库对象。

定义模式：

模式定义语句：CREATE SCHEMA<模式名>AUTHORIZATION<用户名>;
- 如果没有指定<模式名>，那么<模式名>隐含为<用户名>。
- 创建模式的用户必须拥有数据库管理员权限，或者获得了管理员授予的CREATE SCHEMA的权限。
定义模式即定义一个命名空间，在这个空间里可以进一步定义该模式包含的数据库对象，如基本表、视图、索引等
- 可以在创建模式的同时在这个模式定义中进一步创建基本表、视图，定义授权。

删除模式：

删除模式语句：DROP SCHEMA<模式名><CASCADE|RESTRICT>;
- CASCADE 和 RESTRICT 必须二选一。
- CASCADE（级联）：在删除模式的同时把该模式中所有的数据库对象全部删除，
- RESTRICT（限制）：如果该模式已经定义了下属的数据库对象，则拒绝该删除语句的执行。只有当该模式中没有任何下属的对象时才能执行该语句。

定义基本表：

定义基本表语句：

CREATE TABLE<表名>（<列名><数据类型>[列级完整性约束条件]，

<列名><数据类型>[列级完整性约束条件]，

……

<表级完整性约束条件>

）;

如果完整性约束条件涉及多个属性列，那么必须定义在表级上。
与表有关的完整性约束条件存入系统的数据字典中。

完整性约束：

列级完整性约束：
- 标记某属性为主码：Sno CHAR(9) PRIMARY KEY
- 标记某属性取唯一值：Sname CHAR(20) UNIQUE
- 标记某属性不能取空值：Cname CHAR(40) NOT NULL

表级完整性约束：
- 标记外码：FOREIGN KEY(Cpno)REFERENCES Course(Cno) //Cpno是外码，被参照表是Course，被参照列是Cno
- 主码由两个属性构成时标记主码：PRIMARY KEY(Sno,Cno)

数据类型：

定义表的各种属性时需要指明其数据类型及长度。
常用数据类型：

数据类型

数据类型补表

模式与表：

每一个基本表都属于一个模式，一个模式包含多个基本表。
在模式中定义基本表：
- 方法一：在表名中明显地给出模式名：CREATE TABLE "S-T".Student(...); // Student 所属的模式是 S-T
- 方法二：在创建模式语句中同时创建表。
- 方法三：设置所属模式，这样创建表时表名中不必给出模式名。
若用户创建数据库对象时没有指定模式，系统会根据搜索路径来确定该对象所属的模式。
- 显示当前的搜索路径：SHOW search_path;
- 搜索路径默认值是$user, PUBLIC。其含义是首先搜索与用户名相同的模式名，如果该模式名不存在，则使用PUBLIC模式。
- 数据库管理员也可以设置搜索路径：SET search_path TO "S-T", PUBLIC; （这样建表后发现搜索路径第一个模式名S-T存在，所以会为S-T模式建表）

修改基本表：

修改基本表的格式为：

ALTER TABLE<表名>

[ADD [COLUMN]<新列名><数据类型>[完整性约束]]

[ADD<表级完整性约束>]

[DROP [COLUMN]<列名>[CASCADE|RESTRICT]]

[DROP CONSTRAINT<完整性约束>[RESTRICT|CASCADE]]

ALTER COLUMN<列名><数据类型>];

其中ADD子句用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件。
- 不论基本表中原来是否有数据，新增加的列一律为空值。
DROP COLUMN子句用于删除表中的列，如果指定了CASCADE短语，则自动删除引用了该列的其他对象；如果指定了RESTRICT短语，则如果该列被其他对象引用，则将拒绝删除该列。
DROP CONSTRAINT子句用于删除指定的完整性约束条件。
ALTER COLUMN子句用于修改原有的列定义，包括修改列名和数据类型。

删除基本表：

删除基本表的格式为：DROP TABLE<表名>[RESTRICT|CASCADE]
- 若选择RESTRICT，则该表的删除是有限制条件的。欲删除的基本表不能被其他表的约束所引用，不能有视图，不能有触发器，不能有存储过程或函数等。如果存在这些依赖该表的对象，则不能删除该表。
- 若选择CASCADE，则该表的删除没有限制条件。删除基本表的同时，相关的依赖对象都将被一起删除。
- 默认情况是RESTRICT。

建立索引：

建立索引是加快查询速度的有效手段。
常见的数据索引包括顺序文件上的索引、B+树索引、散列索引、位图索引等。
- 顺序文件上的索引：针对按指定属性值升序或降序存储的关系，在该属性上建立一个顺序索引文件，索引文件由属性值和相应的元组指针组成。
- B+树索引：将索引属性组织成B+树形式，B+树的叶结点为属性值和相应的元组指针。B+树索引具有动态平衡的优点。
- 散列索引：建立若干个桶，将索引属性按照其散列函数值映射到相应桶中，桶中存放索引属性值和相应的元组指针。散列索引具有查找速度快的特点。
- 位图索引：用位向量记录索引属性中可能出现的值，每个位向量对应一个可能值。
用户不必也不能显式地选择索引。
索引是关系数据库管理系统的内部实现技术，属于内模式的范畴。
建立索引的格式为：CREATE [UNIQUE][CLUSTER] INDEX <索引名> ON <表名>(<列名> [<次序>] [，<列名> [<次序>] ] ...);
- <表名>是要建索引的基本表的名字
- 索引可以建立在该表的一列或多列上，各列名之间用逗号分隔。
- 每个<列名>后面可以用<次序>指定索引值的排列次序，可选ASC（升序）或DESC（降序），默认值为ASC。
- UNIQUE 表明此索引的每一个索引值只对应唯一的数据记录。
- CLUSTER 表示要建立的索引是聚簇索引。
- 例如：CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC);

修改索引：

修改索引的格式为：ALTER INDEX <旧索引名> RENAME TO <新索引名>;

删除索引：

删除索引的格式为：DROP INDEX <索引名>;
删除索引时，系统会同时从数据字典中删去有关该索引的描述。

数据字典：

数据字典是关系数据库管理系统内部的一组系统表，它记录了数据库中所有的定义信息，包括关系模式定义、视图定义、索引定义、完整性约束定义、各类用户对数据库的操作权限、统计信息等。

3.4数据查询

数据查询的格式为：

SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>] ...

FROM <表名或视图名> [,<表名或视图名>...] | (<SELECT 语句>) [AS] <别名>

[WHERE <条件表达式>]

[GROUP BY <列名1> [HAVING <条件表达式>]]

[ORDER BY <列名2> [ASC | DESC]];

整个SELECT语句的含义是，根据WHERE子句的条件表达式从FROM子句指定的基本表、视图或派生表中找出满足条件的元组，再按SELECT子句中的目标列表达式选出元组中的属性值形成结果表。
如果有GROUP BY子句，则将结果按<列名1>的值进行分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。如果GROUP BY子句带 HAVING短语，则只有满足指定条件的组才予以输出。
如果有ORDER BY子句，则结果还要按<列名2>的值的升序或降序排序。

单表查询：

单表查询是指仅涉及一个表的查询。
查询指定列，例如：

SELECT Sname,Sno,Sdept

FROM Student;

<目标列表达式>中各个列的先后顺序可以与表中的顺序不一致。
查询全部列，例如：

SELECT *

FROM Student;

SELECT 子句的<目标表达式>不仅可以是表中的属性列，也可以是表达式，还可以是字符串常量、函数等。
两个本来并不完全相同的元组在投影到指定的某些列上后，可能会变成相同的行。可以用DISTINCT消除它们。
WHERE常用的查询条件：

查询条件

字符匹配时，用LIKE进行字符串的匹配，一般语法格式为：[NOT] LIKE '<匹配串>' [ ESCAPE '<换码字符>' ]
- 其含义是查找指定的属性列值与<匹配串>相匹配的元组。<匹配串>可以是一个完整的字符串，也可以含有通配符 % 和 _。
- %（百分号）代表任意长度（长度可以为0）的字符串。例如a%b，代表以a开头以b为结尾的任意长度的字符串。
- _（下横线）代表任意单个字符。
- 如果用户要查询的字符串本身含有通配符 % 或 _，这时要使用 ESCAPE '<换码字符>' 短语对通配符进行转义，例如：WHERE Cname LIKE 'DB\_Design' ESCAPE '\'; ESCAPE '\'表示“ \ ”为转码字符，使后面的“_”为普通的“_”字符。
逻辑运算符AND和OR可用来连接多个查询条件，AND的优先级高于OR，但用户可以用括号改变优先级。
用户可以用 ORDER BY 子句对查询结果按照一个或多个属性列的升序（ASC）或降序（DESC）排列，默认值为升序。
- 对于空值，排序时显示的次序由具体系统实现来决定。
SQL提供了许多聚集函数：
- 指定 DISTINCT 短语，则表示在计算时要取消指定列中的重复值。
- WHERE子句中是不能用聚集函数作为条件表达式的。聚集函数只能用于SELECT子句和GROUP BY中的HAVING子句。
GROUP BY子句将查询结果按某一列或多列的值分组，值相等的为一组。
- 分组后聚集函数将作用于每一个组，即每一个组都有一个函数值。
- 如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用HAVING短语指定筛选条件。
- WHERE子句与HAVING短语的区别在于作用对象不同。WHERE子句作用于基本表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。

连接查询：

连接查询：一个查询同时涉及两个以上的表。
- 包括等值连接查询、自然连接查询、非等值连接查询、自身连接查询、外连接查询和复合条件连接查询等。
连接查询的WHERE子句中用来连接两个表的条件称为连接条件或连接谓词，其一般格式为：[<表名1>.] <列名1><比较运算符> [<表名2>.] <列名2> 。
- 其中比较运算符主要有=、>、<、>=、<=、!=（或<>）等。
- 此外连接谓词还可以使用如下形式：[<表名1>.] <列名1>BETWEEN [<表名2>.] <列名2> AND [<表名2>.] <列名3>。
- 当连接运算符为=时，称为等值连接。使用其他运算符称为非等值连接。
- 连接谓词中的列名称为连接字段。连接条件中的各连接字段类型必须是可比的，但名字不必相同。
- 把目标列中重复的属性列去掉则为自然连接。
自身连接：一个表与其自己进行连接。
左外连接：SELECT Student.* FROM Student LEFT OUTER JOIN SC ON(Student.Sno=SC.Sno);
多表连接：两个以上的表进行连接。
- 关系数据库管理系统在执行多表连接时，通常是先进行两个表的连接操作，再将其连接结果与第三个表进行连接。

嵌套查询：

一个SELECT-FROM-WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。例如：

SELECT Sname

FROM Student

WHERE Sno IN

(SELECT Sno

FROM SC

WHERE Cno='2');

上层的查询块称为外层查询或父查询，下层查询块称为内层查询或子查询。
- SQL语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。
- 子查询的SELECT语句中不能使用ORDER BY子句，ORDER BY子句只能对最终查询结果排序。
- 以层层嵌套的方式来构造程序正是SQL中“结构化”的含义所在。
嵌套查询中，子查询的结果通常是一个集合，经常使用IN完成嵌套查询。
子查询的查询条件不依赖于父查询，称为不相关子查询。
子查询的查询条件依赖于父查询，这类子查询称为相关子查询，整个查询语句称为相关嵌套查询语句。
- 相关子查询由于内层查询与外层查询有关，因此必须反复求值。不能一次将子查询求解出来，再去求解父查询。
子查询返回单值时可以用比较运算符，但返回多值时要用ANY或ALL谓词修饰符。
- 而使用ANY或ALL谓词时必须同时使用比较运算符。

ANY或ALL与比较运算符

使用ANY或ALL的查询语句可以用聚集函数来实现，ANY、ALL与聚集函数的对应关系：

ANY、ALL与聚集函数的对应关系

EXISTS代表存在量词。带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
- 使用存在量词EXISTS后，若内层查询结果非空，则外层的WHERE子句返回真值，否则返回假值。
- 由EXISTS引出的子查询，其目标列表达式通常都用 *，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。
使用存在量词NOT EXISTS后，若内层查询结果为空，则外层的WHERE子句返回真值，否则返回假值。
一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换，但所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。
SQL没有全称量词，但是可以把带有全称量词的谓词转换成等价的带有存在量词的谓词。
- 典型例题1：查询选修了全部课程的学生姓名。
  - 可以将该题目转换成等价的题目：没有一门课程是他不选修的。
  - 理解：首先会选择一个Student中的元组，提取其Sno*，然后在Course中选择一个元组，提取其Cno*。进入最内层的选择，若在SC表中存在一个Sno*和Cno*的元组，则最内层的查询块会返回false，也就是不添加任何元组到中间层的结果集中；若在SC表中不存在这样的元组，则最内层的查询块会返回true，也就是会添加一个Course元组到中间层的结果集中，表示该Sno*代表的学生没有选修该Cno*代表的课程，即该学生没有选修全部课程。因为中间层结果集中存在元组，所以NOT EXIST会返回false，使得最外层的该Sno*代表的元组不会添加进结果集中。因此最终添加进结果集中的元组均代表选修了全部课程的学生。
- 典型例题2：查询至少选修了学生201215122选修的全部课程的学生号码。
  - 可以将该题目转换成等价的题目：不存在这样的课程y，学生201215122选修了y，而学生x没有选。
  - 理解：最外层查询块用了DISTINCT去重，实际上是对Sno进行了遍历，逐个传入内层查询。中间层查询块是对学生201215122选修的课的遍历，将某一学号Sno*和课程Cno*传入最内层的查询块，来判断该课程是否是Sno该学生选修的课程。如果是，该课程不会保留在中间层查询块的结果集中，若最终中间层的结果集为空，则最外层的NOT EXIST会返回true，使得该Sno保留在最外层的结果集中。
  - 该例中为SC表起了三个别名，这是为了在WHERE中区别来自同一个表的不同的元组。

集合查询：

SELECT语句的查询结果是元组的集合，所以多个SELECT语句的结果可进行集合操作。
集合操作主要包括并操作UNION、交操作INTERSECT和差操作EXCEPT。
- 参加集合操作的各查询结果的列数必须相同。
- 对应项的数据类型也必须相同。
使用UNION将多个查询结果合并起来时，系统会自动去掉重复元组。如果要保留重复元组则用UNION ALL操作符。

基于派生表的查询：

子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，这时子查询生成的临时派生表成为主查询的查询对象。
如果子查询中没有聚集函数，派生表可以不指定属性列，子查询SELECT子句后面的列名为其默认属性。
通过FROM子句生成派生表时，AS关键字可以省略，但必须为派生关系指定一个别名。

SELECT语句的一般格式：

SELECT语句

3.5数据更新

插入数据：

插入元组的INSERT语句格式为：

INSERT

INTO <表名> [(<属性列1> [,<属性列2>] ...)]

VALUES(<常量1> [,<常量2>] ...);

INTO子句指出插入的表名，并指出新增加的元组在哪些属性上要赋值。
- INTO子句中没有出现的属性列，新元组在这些列上将取空值。
- 如果INTO子句中没有指明任何属性列名，则新插入的元组必须在每个属性列上均有值。
- INTO子句指出属性的顺序可以与表中属性顺序不一样。
VALUES子句对新元组的属性赋值
- 若INTO子句没有指明任何属性列名，则VALUES子句对新元组的各属性列都要赋值并要一一对应。
- 要赋值字符串常数时要用单引号括起来。
子查询可以嵌套在INSERT语句中用以生成要插入的批量数据。

修改数据：

修改操作又称为更新操作，其语句的一般格式为：

UPDATE <表名>

SET <列名>=<表达式> [，<列名>=<表达式>] ...

[WHERE <条件>];

其功能是修改指定表中满足WHERE子句条件的元组。其中SET子句给出<表达式>的值用于取代相应的属性列值。
如果省略WHERE子句，则表示要修改表中的所有元组。
子查询也可以嵌套在UPDATE语句中，用以构造修改的条件。

删除数据：

删除语句的一般格式为：

DELETE

FROM<表名>

[WHERE<条件>];

DELETE语句的功能是从指定表中删除满足WHERE子句条件的所有元组。
如果省略WHERE子句则表示删除表中全部元组，但表的定义没有被删除。
子查询也可以嵌套在DELETE语句中，用以构造执行删除操作的条件。

3.6空值的处理：

取空值的几种情况：
- 该属性应该有值，但目前不知道具体值。
- 该属性不应该有值。
- 由于某种原因不便于填写。
空值是一个含有不确定性的特殊值。
外连接会产生空值，空值的关系运算也会产生空值。
用IS NULL 或 IS NOT NULL来判断一个属性的值是否为空值。
不能取空值的几种情况：
- 属性定义中有NOT NULL约束条件
- 加了UNIQUE限制的属性不能取空值
- 码属性不能取空值
空值的运算：
- 空值与另一个值的算术运算的结果为空值。
- 空值与另一个值的比较运算的结果为UNKNOWN。
- 有了UNKNOWN后，传统的逻辑运算中二值逻辑就扩展成了三值逻辑：
- 在查询语句中，只有使WHERE和HAVING子句中的选择条件为TRUE的元组才被选出作为输出结果。

3.7视图

视图是从一个或几个基本表导出的表。
可以在一个视图之上再定义新的视图。

建立视图：

建立视图的语句格式为：

CREATE VIEW <视图名> [(<列名> [,<列名>] ...)]

AS <子查询>

[WITH CHECK OPTION];

其中，子查询可以是任意的SELECT语句。
不一定可以使用ORDER BY子句和DISTINCT短语。
WITH CHECK OPTION表示对视图进行UPDATE、INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）。
- 在定义视图的时候加上该子句，以后对视图进行操作的时候系统会自动加上子查询中的条件。
组成视图的属性列名或者全部省略或者全部指定。
必须明确指定组成视图的所有列名的三种情况：
- 某个目标列是聚集函数或列表达式
- 多表连接时选出了几个同名列作为视图的字段
- 需要在视图中为某个列启用新的更合适的名字
关系数据库管理系统执行CREATE VIEW语句的结果只是把视图的定义存入数据字典，并不执行其中的SELECT语句。只是对视图查询时，才按视图的定义从基本表中将数据查出。
行列子集视图：从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码
视图可以建立在一个或多个已定义好的视图上，或建立在基本表与视图上。
定义视图时可以根据应用的需要设置一些派生属性列，这些派生属性称为虚拟列。带虚拟列的视图也称为带表达式的视图。
可以用带有聚集函数和GROUP BY子句的查询来定义视图，这种视图称为分组视图。
定义视图后如果原表被破坏，该视图就不能正常工作了。要在修改基本表之后删除由该表导出的视图，然后重建这个视图。