数据库原理及应用

总结►数据库 评论

关系模型

关系模型是一种采用关系二维表的数据结构形式存储实体及其实体间联系的数据模型。关系模型采用具有关系特征的二维表来组织与存储数据，并采用关系运算来操作数据。通常把具有关系特征的二维表称为“关系。具体来讲，关系是一种由行和列组成的、用于组织存储实体数据的二维表，满足

  • 表中每行存储实体的一个实例数据
  • 表中每列表示实体的一项属性
  • 表中单元格只能存储单个值
  • 表中不允许有重复行
  • 表中不允许有重复列
  • 表中行顺序任意
  • 表中列顺序任意

关系模型的基本概念

定义1：域指一组具有相同数据类型的值的集合。关系模型用域表示实体属性的取值范围。通常用 表示某个域

定义2：给定一组域，这组域的笛卡尔积为 其中每一个向量 称为一个 元组，简称元组。向量中的每个 称为分量。

定义3：关系是 笛卡尔积元组集合中有特定意义的子集合。它表示为 其中 为关系的名称， 分别为 关系的属性， 为关系属性的个数，称为“元数”或“度数”

关系特性

在关系的数学定义中，关系可以是一个无限的原阻击和。此外笛卡尔积不满足交换律。这些特性不适合数据库实际应用处理要求，因此，需要对关系特性进行如下限制与约束

  1. 无限元组集合的关系在数据库系统中无实际意义。关系模型中的关系必须是有限的元组集合
  2. 为了使关系中的属性列可允许任意顺序，可给个属性列定义不同列;,b;消除元组属性列的有序性

关系模型的组成

  1. 数据结构 在关系模型中，采用具有关系特征的二维表数据结构来组织存储数据。一个关系数据库由若干关系表组成，并且表之间存在一定的关联
  2. 操作方式 关系模型中，对关系表的数据操作按照集合关系运算方式进行。常用的关系运算包括选择、投影、连接、除、并、交、差等数据查询操作，也包括插入、更新、删除等数据操作
  3. 数据约束 在关系模型中，关系模型的数据约束包括实体完整性约束、参照完整性约束和用户自定义约束

关系模型的操作

  1. 并运算

关系 与 并运算的结果集合由属于 或属于 的元组组合而成，其运算结果仍为 元关系

其中， 为元组

  1. 差运算 关系 与 差运算的结构有属于 且不属于 的所有元组组成，其运算结果仍为 元关系

  1. 交运算 关系 与 的交运算结构集合由既属于 又属于 的所有元组组成，其结果关系仍为 元关系

  1. 广义笛卡尔积 假设 有 个属性， 有 个属性，则关系 和 的广义笛卡尔积是一个 列 的元组集合。若 有 个元组， 有 个元组，则其广义笛卡尔积有 个元组

关系运算的数学符号定义

  1. 设关系模式为 ， 表示的是 是 的一个元组， 则表示元组 中对应属性 的一分量
  2. 设 ，其中 是 中的一部分，则 成为部分属性列或部分域列。 则表示 去掉 后剩余的部分列， 表示元组 在部分属性列 上各分量的集合
  3. 设 为 元关系， 为 元关系。若 ，则 表示元祖的连接，它是一个 列的元组，前 个来自 后 个来自
  4. 给定一个关系 ， 分别为 的属性组。当 时， 在 中的像集记为

该式表示 中属性组 上值为 的各元组在 上分量的集合

  1. 选择运算 选择运算是在关系 中选择出满足给定条件 的元祖集，记作

其中 表示选择条件，是一个值为“真”或“假”的逻辑表达式； 是 中任意一个元组，若代入 的结果为真，则这个元组就是 的一个元组，反之则不在结果集

  1. 投影运算 投影运算是从关系 中选择出部分属性列组成一个新的关系，记作

式中， 为 的部分属性列

  1. 连接运算

    1. 连接

      连接是 和 的笛卡尔积中选取满足比较关系 的元组集合，组成新的关系，记作 式中， 分别为关系 上度数相等且有可以比较的属性组， 为比较运算符。当其取 时，又称为等值连接运算

    2. 自然连接

      自然连接是一种特殊的等值连接运算，它要求两个关系中进行比较的分量必须是相同的属性组，而且还要在结果中去除重复列

    3. 外连接

      1. 左外连接：用空值填补与左侧关系不匹配的右侧关系元组
      2. 右外连接：同理
      3. 全外连接：同时完成左右两边

  2. 除运算

    给定关系 和关系 中， 分别为部分属性组。 和 中的 可以由不同属性名，但必须来自相同的域集。 与 的除运算得到一个新的关系 ，记为

SQL 语句

数据定义、数据操纵、数据查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
CREATE DATABASE DB;

CREATE TABLE Student
(
    StudentID VARCHAR(20) PRIMARY KEY,
    NAME VARCHAR(20) NOT NULL,
    Birth DATE NOT NULL,
  	Sex CHAR(2) NOT NULL CHECK(Sex IN('男','女')),
    Grade INT DEFAULT 2
);


CREATE TABLE Plan
(
    ID SERIAL NOT NULL,
    CID CHAR(4) NOT NULL,
    TID CHAR(4) NOT NULL,
    FOREIGN KEY(CID) REFERENCES Course(CID),
    CONSTRAINT CoursePlan_PK PRIMARY KEY(CID, TID)
);

ALTER TABLE Student ADD Email VARCHAR(20);

CREATE INDEX BirthDay_Idx ON Student(Birth);

INSERT INTO Student VALUES('1','张三','2021-04-01'，'男',2);

UPDATE Student
Set Email='[email protected]'
where id = 1;

DELETE FROM Student
where Name='张三';

SELECT * FROM Student WHERE Birth BETWEEN '2000-01-01' AND '2000-12-31'
ORDER BY Major ASC;

SELECT COUNT(DISTINCT Major) as 专业数 FROM Student;

SELECT Major as 专业, COUNT(SID) as 学生人数
FROM Student
Group BY Major
HAVING COUNT(*)>3
WHERE Sex='男'
;

Select * from Teacher
WHERE CID IN 
(
    SELECT CID FROM College WHERE CName='计算机学院'
);

SELECT C.CNAME, T.TNAME, P.CROOM, S.CNAME
FROM Course as C 
JOIN Plan as P ON C.CID=P.CID
JOIN Teacher AS T ON P.TID=T.TID
LEFT JOIN College AS S ON S.CID=T.CID;

数据控制语言

1
2
CREATE ROLE User_Student_2021090922011 LOG IN INHERIT;
GRANT SELECT, UPDATE ON Student_2021090922011 TO User_Student_2021090922011;

视图

1
2
3
4
CREATE VIEW BasicView as
SELECT * FROM TEACHER;

SELECT * FROM BasicView;

存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
CREATE OR REPLACE FUNCTION Pro_CurrentSale(OUT total_qty INTEGER, OUT total_amount money) AS $$
DECLARE
today DATE := CURRENT_DATE;
BEGIN
SELECT SUM(sale_qty), SUM(sale_amount) INTO total_qty, total_amount
FROM Sale
WHERE sale_date = today;
END;
$$ language plpgsql;

DO $$
DECLARE
    qty INTEGER;
    amount money;
BEGIN
    call pro_currentsale(qty,amount);
    RAISE NOTICE '今日销售数量：%，销售金额：%。', qty, amount;
END;
$$ LANGUAGE plpgsql;

-- -----------------------

CREATE OR REPLACE FUNCTION count_failing_students()
RETURNS TABLE (课程号 VARCHAR(20), 不及格人数 BIGINT) AS $$
BEGIN
RETURN QUERY
SELECT CID, COUNT(*) AS 不及格人数
FROM GRADE
WHERE Score < 60
GROUP BY CID;
END;
$$ LANGUAGE plpgsql;

触发器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE OR REPLACE FUNCTION Tri_InsertSale() RETURNS TRIGGER AS $$
BEGIN
	UPDATE Book SET stock_qty = stock_qty - NEW.sale_qty WHERE book_id = NEW.book_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER Tri_InsertSale
AFTER INSERT ON Sale
FOR EACH ROW
EXECUTE FUNCTION Tri_InsertSale();

-- ------------------------------

CREATE OR REPLACE FUNCTION grade_update_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
-- 插入修改前数据
INSERT INTO GradeLOG ( 操作用户 , 学号 , 课程号 , 修改时间 , 修改前成绩 修改后成绩)
VALUES (current_user, OLD.SID, OLD.CID, current_timestamp, OLD.Score, NEW.Score);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
                      
CREATE TRIGGER grade_update_trigger
AFTER UPDATE ON GRADE
FOR EACH ROW
EXECUTE FUNCTION grade_update_trigger_function();

E-R 模型

  1. 实体

    实体是对现实世界中描述事物数据对象的抽象概念，通常使用两层矩形方框表示，并在顶层方框注明实体名称，下栏显示实体属性

    作为标识符的属性需加上下划线。若是复合标识符，则构成复合标识符的所有属性都需加下划线。

  2. 联系

    可以使用联系表示一个或多个实体之间的关联关系，用形似鸟脚的连线符号表示实体之间的联系

    最小基数为 0/1 可以表示为圈或线

  3. 继承性分类

    半圆：非互斥继承联系

    半圆+X：互斥继承联系

    半圆+横线：完整继承联系

    半圆+横线+X：非完整继承联系

  4. 标识符依赖实体

    若弱实体的标识符含有所依赖实体的标识符，则该弱实体称为标识符依赖弱实体，连线在弱实体一侧有一个三角形。而非标识符依赖实体的联系连线符号，在弱实体一侧仅为基本鸟足符号

数据库规范化设计

  1. 第一范式

    在关系数据库中，第一范式是对关系标的基本要求。

    第一范式指关系表的属性列不能重复，并且每个属性列都是不可分割的基本数据项

    例如学生表中”联系方式“可能为邮箱或电话，不满足 1NF 范式，进行拆分即可

  2. 第二范式

    第二范式要求关系表中的所有数据都要和该关系表的主键有完全函数依赖。若一个关系中的某些属性数据只和主键的一部分存在依赖关系，则不符合第二范式

    例如关系 (A,B,N,O,P) 的复合主键为 (A,B) 那么 N，O，P 这三个非键属性都不存在只依赖 A 或 B 的情况，则该关系满足第二范式

  3. 第三范式

    第三范式要求关系先满足 2NF，并且所有非主键属性均不存在传递函数依赖

    例如学生（学号，系名，住址）有属性传递依赖学号→系名→住址，所以学号→住址。不满足 3NF，需要拆分为“学生”，“系信息”等新关系

  4. 巴斯-科德范式

    BCNF 范式要求关系中所有函数依赖的决定因子必须是候选键

  5. 第四范式

    当一个关系满足 BCNF 范式并消除了多值依赖时，满足第四范式

  6. 第五范式

    若一个关系为消除其中连接依赖，进行投影分解，所分解的各个关系均包含原关系的一个候选键，则这些分解后的关系满足 5NF

ODBC

使用 SQL 作为访问数据的标准，提供了最大限度的互操作性，应用程序可以通过 ODBC 访问不同的数据库管理系统

组成部分

  1. ODBC 数据库应用程序：用高级语言和 ODBC 函数编写的应用程序，用于访问数据库。主要任务是向 BMS 发出请求和处理数据库返回的结果
  2. 驱动程序管理器：被包含在 ODBC32.dll 中，对用户透明。任务是管理 ODBC 驱动软件，为应用程序加载、调用和卸载 DB 驱动程序，是 ODBC 中最重要的部件
  3. DBMS 驱动程序：提供了 ODBC 和数据库之间的接口，PostgreSQL 驱动程序封装在 posqlodbcx.dll 文件中
  4. 数据源：用于链接 DB 驱动程序与 DBS。数据源包含数据库位置和数据库类型等信息，实际是一种数据连接的抽象

JDBC

用于 Java 应用程序连接数据库的标准方法，是一种用于执行 SQL 语句的 Java API，可以为多种关系数据库提供统一的访问

JDBC 主要分为两部分：面相应用开发人员的应用开发 API 接口和面相数据库厂商的驱动程序开发者的 API 接口

JDBC 是一个类的集合，实现了 JDBC 所定义的类和接口，提供了一个能实现 java.sql.Driver 接口的类

访问数据库的步骤

  1. 加载 Java 包 java.sql.* 中的核心类和接口
  2. 创建数据库连接对象
  3. 创建 Statement 对象
  4. 调用Statement 对象的相关方法执行相对应的 SQL 语句
  5. 处理数据库的返回结果
  6. 关闭数据库连接

NoSQL

CAP

在分布式的环境下设计和部署系统时，有3个核心的需求，CAP对应： ① 一致性（Consistency）任何一个读操作总是能读取到之前完成的写操作结果，也就是在分布式环境中，多点的数据是一致的。 ② 可用性（Availability）每一个操作总是能够在确定的时间内返回，也就是系统随时都是可用的。 ③ 分区容忍性（Partition Tolerance）在出现网络分区（比如断网）的情况下，分离的系统也能正常运行

BASE

• Basically Available --基本可用；系统能够基本运行，一直提供服务。 • Soft-state --软状态/柔性事务。"Soft state" 可以理解为"无连接"的, 而"Hard state" 是"面向连接"的；系统不要求一直保持强一致状态。 • Eventual Consistency --最终一致性 系统在某个时刻达到最终一致性。 • BASE定义为CAP中AP的衍生，在分布式环境下， BASE是数据的属性，BASE强调基本的可用性，按照功能划分数据库.

5V

Volume 超量

Velocity 高速

Variety 异构

Veracity 真实

Value 价值

NoSQL 分类

四种

类型 部分代表 特点
列族数据库 Hbase
Cassandra
Hypertable		 顾名思义，是按列存储数据的。最大的特点是方便存储结构化和半结构化数据，方便做数据压缩，对针对某一列或者某几列的查询有非常大的IO优势。
文档数据库 MongoDB
CouchDB		 文档存储一般用类似json的格式存储，存储的内容是文档型的。这样也就有有机会对某些字段建立索引，实现关系数据库的某些功能。
键值数据库 Tokyo Cabinet / Tyrant
Berkeley DB
MemcacheDB
Redis		 可以通过key快速查询到其value。一般来说，存储不管value的格式，照单全收。
图数据库 Neo4J
FlockDB
InfoGrid		 图形关系的最佳存储。使用传统关系数据库来解决的话性能低下，而且设计使用不方便。

不知不觉Never Settle

Never Settle