数据库系统知识点整理

数据库系统概论

1.1 数据库系统概述

• 数据：描述事物的符号记录称为数据。
• 数据库：存放数据的仓库。数据库是长期储存在计算机内，有组织的，可共享的大量数据的集合。
• 数据库管理系统：是位于用户与操作系统之间的的一层数据管理软件。主要功能有：数据定义功能；数据组织，存储和管理；数据操纵功能；数据库的事务管理和运行管理；数据库的建立和维护功能；其他功能。
• 数据库系统：是由数据库、数据库管理系统（及其应用开发工具）、应用程序和数据库管理员（DataBase Administrator）组成的存储、管理、处理和维护数据的系统。

• 数据库管理技术经历了三个阶段：人工管理、文件系统、数据库系统。

• 人工管理阶段

  1. 数据不保存
  2. 应用程序管理数据
  3. 数据不共享
  4. 数据不具有独立性

• 文件系统阶段

  1. 数据可以长期保存
  2. 由文件系统管理数据
  3. 数据共享性差，冗余度大
  4. 数据独立性差

• 数据库系统阶段

  1. 数据结构化。数据库系统实现整体数据的结构化，这是数据库的主要特征之一，也是数据库系统与文件系统的本质区别
  2. 数据的共享性高、冗余度低且抑郁扩充。数据共享可以大大减少数据冗余，节约存储空间，数据共享还能避免数据之间的不相容性与不一致性
  3. 数据独立性高。数据独立性包括物理独立性、逻辑独立性。物理独立性是指用户的应用程序与数据库中的物理存储是相互独立的。逻辑独立性是指用户的应用程序与数据库的逻辑结构是相互独立的。
  4. 数据由数据库管理系统统一管理和控制。数据库管理系统还必须提供以下几个方面的数据控制功能：
     
     1. 数据的安全性保护：是指保护数据以防止不合法使用造成的数据泄密和破坏。
     2. 数据的完整性检查：是指数据的正确性】有效性和相容性。
     3. 并发控制
     4. 数据库恢复

1.2 数据模型

• 数据模型是对现实世界数据特征的抽象，也就是说数据模型是用来描述数据、组织数据和对数据进行操作的；数据模型是数据库系统的核心和基础。
• 数据模型应满足三方面的要求：一是比较真实的模拟现实世界，二是容易为人所理解，三是便于在计算机上实现。

• 根据模型应用的不同目的，可以将这些模型分为两大类：第一类是概念模型，第二类是逻辑模型和物理模型。

  1. 第一类概念模型，也称信息模型，它是按用户的观点来对数据和信息建模，主要用于数据库设计。
  2. 第二类的逻辑模型主要包括层次模型、网状模型、关系模型、面向对象数据模型和对象关系数据模型、半结构化数据模型等。它是按计算机系统的观点对数据建模，主要用于数据库管理系统的实现。
  3. 第二类中的物理模型是对数据最底层的抽象，它描述数据在系统内部的表示方式和存取方法，是面向计算机系统的。

• 实体：客观存在并可相互区别的事物称为实体

• 属性：实体具有的某一特征称为属性
• 码：唯一标识实体的属性集称为码
• 实体型：具有相同属性的实体必然具有共同的特征和性质，用实体名及其属性名集合来抽象和刻画同类实体，称为实体型。
• 实体集：同一类型实体的集合称为实体集。

• 联系：实体内部以及实体之间是有联系的。实体内部的联系通常是指组成实体的个属性之间的联系，实体之间的联系通常是指不同实体集之间的联系。

  • 实体之间的联系有一对一、一对多和多对多等多种联系。

• 概念模型的一种表示方法：实体-联系方法（Entity-Relationship approach）。该方法用 E - R 图（E - R diagram）来描述现实世界的概念模型，E - R 方法也称为 E - R 模型。

• 数据模型通常由数据结构、数据操作和数据的完整性约束条件三部分组成。

  1. 数据结构：数据结构描述数据库组成对象以及对象之间的联系。
  2. 数据操作：数据操作是指数据库中各种对象（型）的实例（值）允许执行的操作的集合，包括操作及有关的操作规则。数据库主要有查询和更新（包括插入、删除和修改）两大类操作。
  3. 数据的完整性约束条件：数据的完整性约束条件是一组完整性规则。完整性规则是给定的数据模型中数据及其联系所具有的制约和依存规则，用以限定符合数据模型的数据库状态以及状态的变化，以保证数据的正确，有效和相容。

• 数据库领域中主要的逻辑数据模型有：

  1. 层次模型
  2. 网状模型
  3. 关系模型
  4. 面向对象数据模型
  5. 对象关系数据模型
  6. 半结构化数据模型
     
     • 其中层次模型和网状模型统称为格式化模型

• 层次模型的数据结构：

  1. 有且只有一个节点没有双亲结点，这个结点称为根节点
  2. 根以外的其他结点有且只有一个双亲结点
     
     • 层次模型像一棵倒立的树，节点的双亲是唯一的

• 网状模型的数据结构：

  1. 允许一个以上的结点无双亲
  2. 一个结点可以有多于一个的双亲

• 关系模型数据结构：

  1. 关系（relation）：一个关系对应一张表。
  2. 元组（tuple）：表中的一行即为一个元祖。
  3. 属性（attribute）：表中的一列即为一个属性
  4. 码（key）：也称码键。表中的某个属性组，它可以唯一确定一个元祖。
  5. 域（domain）：域是一组具有相同数据类型的值的集合。属性的取值范围来自某个域。
  6. 分量：元组中的一个属性值。
     
     • 关系模式一般表示为：关系名（属性1，属性2，…，属性n）
     • 关系模型要求关系必须是规范化的，关系的每一个分量必须是一个不可分的数据项。

1.3 数据库系统的结构

• 数据库系统的三级模式结构是指数据库系统是由外模式、模式和内模式三机构成。

  1. 外模式：外模式也称子模式或用户模式，它是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示。外模式通常是模式的子集，一个数据库可以有多个外模式。数据库管理系统提供外模式数据定义语言（外模式DDL）来严格地定义外模式。
  2. 模式：模式也称逻辑模式，是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图。模式实际上是数据库数据在逻辑级上的视图，一个数据库只有一个模式。数据库管理系统提供模式数据定义语言（模式DDL）来严格定义模式。
  3. 内模式：内模式也称存储模式，一个数据库只有一个内模式，它是数据物理结构与存储方式的描述，是数据在数据库内部的组织方式。

• 数据库系统的三级模式是数据的三个抽象级别，他把数据的具体组织留给数据库管理系统管理，使用户能逻辑地、抽象地处理数据，而不关心数据在计算机中的具体表现方式与存储方式。为了能够在系统内部实现这三个抽象层次的联系和转换，数据库管理系统在这三级模式之间提供了两层映像：外模式 / 模式映像和模式/内模式映像。

• 两层映像保证了数据库系统中的数据能够具有较高的逻辑独立性和物理独立性
• 外模式 / 模式映像：当模式改变时，有数据库管理员对各个外模式/模式映像做相应的改变，可以是外模式保持不变。应用程序是依据数据的外模式编写的，从而应用程序不必修改，保证了数据与程序的逻辑独立性，简称数据的逻辑独立性。
• 模式 / 内模式映像：当数据库的存储结构改变时，由数据库管理员对模式/内模式映像作相应改变，可以使模式保持不变，从而应用程序也不比改变。保证了数据与程序的物理独立性，简称数据的物理独立性。

1.4 数据库系统的组成

1. 硬件平台级数据库
2. 软件
3. 人员：
   
   1. 数据库管理员：负责全面管理和控制数据库系统
      
      1. 决定数据库中的信息内容和结构
      2. 决定数据库的存储结构和存取策略
      3. 定义数据的安全性要求和完整性约束条件
      4. 监控数据库的使用和运行
      5. 数据库的改进和重组、重构
   2. 系统分析员：负责应用系统的需求分析和规范说明，要和用户及数据库管理员相结合，确定系统的硬件软件配置，并参与数据库系统的概要设计
   3. 数据库设计人员：负责数据库中数据的确定及数据库各级模式的设计。
   4. 应用程序员：应用程序员负责设计和编写应用系统的程序模块，并进行调试和安装
   5. 用户：这里的用户指最终用户。最终用户通过应用系统的用户接口使用数据库。常用的接口有浏览器、菜单驱动、表格操作、图形显示等。

2.1 关系数据结构及形式定义

• 域：域是一组具有相同数据类型的值的集合
• 笛卡尔积：笛卡尔积是域上的一种集合运算
• 一个域允许的不同取值个数称为这个域的基数
• 若关系中的某一属性组的值能唯一标识一个元祖，而其子集不能，则称该属性组为候选码，若一个关系有多个候选码，则选定其中一个为主码
• 关系有三种类型：基本关系（通常又称为基本表或基表）、查询表和视图表。
• 关系数据库中，关系模式是型，关系是值，关系模式是对关系的描述。

2.2 关系操作

• 关系模式中常用的关系操作包括查询（query）操作和插入（insert）、删除（delete）、修改（update）操作两大部分
• 查询操作又可以分为选择（select）、投影（project）、连接（join）、除（divide）、并（union）、差（except）、交（intersection）、笛卡尔积等。其中选择、投影、并、差、笛卡尔积是 5 种基本操作。
• 关系数据语言分为：
  
  1. 关系代数语言
  2. 关系演算语言
     
     1. 元组关系演算语言
     2. 域关系演算语言
  3. 具有关系代数和关系演算双重特点的语言-结构化查询语言

2.3 关系的完整性

• 关系模型中有三类完整性约束：实体完整性、参照完整性和用户自定义完整性。其中实体完整性和参照完整性是关系模型必须满足的完整性约束条件，被称作是关系的两个不变性。
• 实体完整性规则：若属性（指一个或一组属性）A 是基本关系 R 的主属性，则 A 不能取空值（nullvalue）。所谓空值就是“不知道”或“不存在”或“无意义”的值。

• 参照完整性规则：若属性（或属性组）F 是基本关系 R 的外码，它与基本关系 S 的主码 K 相对应（基本关系 R 和 S 不一定是不同的关系），则对于 R 中每个元组在 F 上的值必须：

  1. 或者取空值（F 的每个属性值均为空值）
  2. 或者等于 S 中某个元组的主码值

• 用户自定义完整性就是针对某一具体关系数据库的约束条件，它反应某一具体应用所涉及的数据必须满足的语义要求。

2.4 关系代数

• 传统的集合运算是二目运算，包括并、差、交、笛卡尔积 4 种运算。
• 专门的关系运包括选择、投影、连接、除等运算。

3.1 SQL 概述

• SQL 集数据查询、数据操纵、数据定义和数据控制功能于一体，其主要特点包括：

  1. 综合统一
  2. 高度非过程化
  3. 面向集合的操作方式
  4. 以同一种语法结构提供多种使用方式
  5. 语言简洁，易学易用

• 支持 SQL 的关系数据库管理系统同样支持关系数据库三级模式结构。其中外模式包括若干视图和部分基本表，数据库模式包括若干基本表，内模式包括若干存储文件。

3.2 学生-课程数据库

• 学生表：Student（Sno，Sname，Ssex，Sage，Sdept）
• 课程表：Course（Cno，Cname，Cpno，Ccredit）
• 学生选课表：SC（Sno，Cno，Grade）

3.3 数据定义

-	创建	删除	修改
模式	CREATE SCHEMA	DROP SCHEMA
表	CREATE TABLE	DROP TABLE	ALTER TABLE
视图	CREATE VIEW	DROP VIEW
索引	CREATE INDEX	DROP INDEX	ALTER INDEX

- 一个关系数据库管理系统的实例中可以建立多个数据库，一个数据库中可以建立多个模式，一个模式下通常包括多个表、视图和索引等数据库对象。
- 定义模式：
CREATE SCHEMA <模式名> AUTHORIZATION <用户名>

• 删除模式：
  DROP SCHEMA <模式名><CASCADE | RESTRICT>

• 定义基本表：
  CREATE TABLE <表名> （<列名><数据类型> [列级完整性约束条件] ...）

• 建立一个 “学生” 表 Student

  CREATE TABLE Student (
      Sno CHAR(9) PRIMARY KEY,
      Sname CHAR(20) UNIQUE,
      Ssex CHAR(2),
      Sage SMALLINT,
      Sdept CHAR(20)
  );

• 数据类型

数据类型	含义
CHAR(n),character(n)	长度为 n 的定长字符串
varchar(n),charactervarying(n)	最大长度为 n 的变长字符串
CLOB	字符串大对象
BLOB	二进制大对象
INT, INTEGER	长整数
smallint	短整数
bigint	大整数
real	单精度浮点数
double persision	双精度浮点数
float(n)	可选精度的浮点数
boolean	逻辑布尔量
date	时间，年月日
time	时间，时分秒
timestamp	时间戳类型
interval	时间间隔类型

- 模式与表
CREATE TABLE “S-T”.Student(...) /* Student 所属的模式是 S-T */

• 修改基本表
  
ALTER TABLE <表名>
[ADD [COLUMN] <新列名><数据结构> [完整性约束]]
[DROP [COLUMN] <列名> [CASCADE | RESTRICT]]
[ALTER COLUMN <列名><数据结构>]；


• 向 Student 表增加“入学时间”列，其数据类型为日期
  ALTER TABLE Student ADD S_entrance DATE；

• 将年龄的数据类型由字符型改为整数
  ALTER TABLE Student ALTER COLUMN Sage INT；

• 增加课程名称必须取唯一值的约束条件
  ALTER TABLE Course ADD UNIQUE(Cname)；

• 删除基本表

  DROP TABLE <表名> [RESTRICT | CASCADE];

• 删除 Student 表
  DROP TABLE Student CASCADE;

• 建立索引

    CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名>(<列名> ...);

• 修改索引
  ALTER INDEX <旧索引名> RENAME TO <新索引名>;

• 删除索引
  DROP INDEX <索引名>

• 数据查询

  SELECT [ALL | DISTINCT] <目标表达式> [,<目标表达式列表>] ...
  FROM <表名或视图名> [,<表名或视图名> ...] | (<SELECT 语句>) [AS] <别名>
  [WHERE <条件表达式>]
  [GROUP BY <列名 1> [HAVING <条件表达式>]]
  [ORDER BY <列名 2> [ASC | DESC]];

• 查询全体学生的学号和姓名

  SELECT Sno, Sname
  FROM Student;

• 查询全体学生的姓名、学号、所在系。

    SELECT Sname, Sno, Sdept
    FROM Student;

• 查询全部列

    SELECT *
    FROM Student;

• 查询全体学生的姓名及其出生年份

    SELECT Sname, 2017-Sage
    FROM Student;

• 查询全体学生的姓名、出生年份和所在系，要求用小写字母表示系名

    SELECT Sname, 'Year of Brith:', 2017-Sage, LOWER(Sdept)
    FROM Student;

• 查询选修了课程的学生学号（去掉重复行）

    SELECT DISTINCT Sno
    FROM SC；

• 查询所有年龄在 20 岁以下的学生姓名及其年龄

  SELECT Sname，Sage
  FROM Student
  WHERE Sage < 20;

• 查询考试成绩不及格的学生的学号

  SELECT DISTINCT Sno
  FROM SC
  WHERE Grade < 60;

• 查询年龄在 20 ～ 23 岁之间的学生的姓名、系别和年龄

    SELECT Sname，Sdept，Sage
    FROM Student
    WHERE Sage BETWEEN 20 AND 23；

• 查询年龄不在 20 ～ 23 之间的学生的姓名、系别和年龄

    SELECT Sname，Sdept，Sage
    FROM Student
    WHERE Sage NOT BETWEEN 20 AND 23 ；

• 查询既不是计算机系、数学系、也不是信息系的学生的姓名和性别

  SELECT Sname，Ssex
  FROM Student
  WHERE Sdept NOT IN ('CS', 'MA', 'IS');

• 通配符 % 代表任意长度的字符串

• 通配符 _ 代表任意单个字符

• 查询学号为 201215121 的学生的详细情况

  SELECT * 
  FROM Student
  WHERE Sno LIKE '201215121';  等价于 WHERE Sno = '201215121';

• 查询所有姓刘的学生的姓名、学号和性别

  SELECT Sname，Sno，Ssex
  FROM Student
  WHERE Sname LIKE ‘刘%’；

• 查询所有不是姓刘的学生的姓名、学号和性别

  SELECT Sname， Sno，Ssex
  FROM Student
  WHERE Sname NOT LIKE ‘刘%‘；

• 查询所有有成绩的学生的学号和课程号

    SELECT Sno，Cno
    FROM SC
    WHERE Grade IS NOT NULL；

• 查询选修了3号课程的学生的学号和成绩，查询结果按分数的降序排列

SELECT Sno，Grade
FROM SC
WHERE Cno = ’3‘
ORDER BY Grade DESC(default ASC);

• 查询全体学生情况，查询结果按所在系的系号升序排序，同一系中的学生按年龄降序排序。

SELECT * 
FROM Student
ORDER BY Sdept, Sage, DESC;

• 聚集函数

函数名	功能
COUNT([DISTINCT	ALL] <列名>)
COUNT(*)	统计元祖个数
SUM([DISTINCT	ALL]] <列名>)
AVG([DISTINCT	ALL]] <列名>)
MAX([DISTINCT	ALL]] <列名>)
MIN([DISTINCT	ALL]] <列名>)

- 查询学生总数

SELECT COUNT(*)
FROM Student;

• 查询选修了课程的学生人数

SELECT COUNT(DISTINCT Sno)
FROM SC;

• 计算选修了一号课程的学生平均成绩

SELECT AVG(Grade)
From SC
WHERE Cno='1';

• 查询选修1号课程的学生最高分数

SELECT MAX(Grade)
FROM SC
WHERE Cno='1';

• 查询学生201215012选修课程的总学分数。

SELECT SUM(Ccredit)
FROM SC,Course
WHERE Sno='201215012' AND SC.Cno = Course.Cno;

• 求各个课程号及相应的选课人数

SELECT Cno，COUNT(Sno)
FROM SC
GROUP BY Cno;

• 查询选修了三门以上课程的学生学号

SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*) > 3;

• 查询平均成绩大于等于 90 分的学生学号和平均成绩

SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade) >= 90;

• 查询每个学生及其选修课的情况

SELECT Student.*, SC.*
FROM Student, SC
WHERE Student.Sno = SC.Sno;

• 查询选修 2 号课程且成绩在 90 分以上的所有学生的学号和姓名

SELECT Student.Sno, Sname
FROM Student,SC
WHERE Student.Sno=SC.Sno AND SC.Cno = '2' AND SC.Grade > 90;

• 查询每个学生的学号、姓名、选修的课程名及成绩

SELECT Student.Sno, Sname, Cname, Grade
FROM Student, SC, Course
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;

3.7 视图

• 建立视图

CREATE VIEW <视图名> 
AS <子查询>
[WITH CHECK OPTION];

• 建立信息系学生的视图

CREATE VIEW IS_Student
AS
SELECT Sno,sname,Sage
FROM Student
WHERE Sdept='IS';

• 定义一个反映学生出生年份的视图

CREATE VIEW BT_S(Sno,Sname, Sbirth)
AS
SELECT Sno,Sname,2014-Sage
FROM Student;

数据库安全性

• 数据库的数据保护主要包括数据的安全性和完整性。
• 数据库的安全性是指保护数据库以防止不合法使用所造成的数据泄露、更改或破坏。
• 数据库的不安全因素
  
  1. 非授权用户对数据库的恶意存取和破坏
     
     • 以保证数据免受未经授权的访问和破坏，数据库管理系统提供的安全措施主要包括用户身份鉴别、存取控制和视图等技术。
  2. 数据库中重要或敏感的数据被泄露
     
     • 为防止数据泄露，数据库管理系统提供的主要技术有强制存取控制、数据加密存储和加密传输等。
  3. 安全环境的脆弱性