电子科技大学-数据库系统-期末汇总-完全体（概念+图片+习题+代码）

写在前面

从2023年开始，学院对各课程考核题型形式提出了统一要求，取消单选题、判断题的测试形式，更多采用综合考核能力的题型形式。

所用教材：数据库系统原理
本篇博客主要记录的是老师PPT上的重点。个人复习使用，仅供参考。由于某些老师后来给画了重点，就又删减了少许。
对于电子科大的朋友，如果你们任课老师给了复习要点，跟着复习要点有针对性的复习就行。

一、数据库及其系统概念

• 了解数据库、数据库管理系统、数据库系统等基本概念
• 理解数据模型与数据库类型关系
• 熟悉数据库系统组成及其各部分作用

数据库：按特定数据模型组织存储管理数据的文件集合

特点

• 数据不重复
• 支持并发
• 数据结构独立于应用程序
• 数据的增删改查由数据库系统管理软件管理

数据模型：事物对象数据特征结构的形式化表示，包括数据结构、数据操作、数据约束

数据结构：对象静态特征

数据操作：对象动态特征

数据约束：数据结构中数据之间的联系制约关系，数据动态变化的规则

数据模型-关系数据模型：采用“二维表”结构组织、存储和管理数据，并以关联列实现表之间的联系。

数据库系统（DS）

数据库应用程序：在DBMS支持下对数据库中数据进行访问处理

关系数据库数据内容：用户表（用户数据），系统表（元数据、索引数据、运行数据）

关系数据库对象组织：

• 用户表：存储用户的应用数据
• 系统表：存储数据库系统自身数据
• 视图：通过虚拟表实现数据查询处理
• 索引：通过目录数据结构支持快速的数据查询
• 约束：对关系表及其数据施加规则
• 存储过程：在数据库内部实现特定功能程序的数据处理
• 触发器：在数据库内部实现数据操作事件触发自动执行的过程程序

二、关系模型

2.1 关系相关概念

键：关系中可以唯一标识元组的属性列，其他的为非键列

候选键：多个列都可以作为键时，它们每一个均为候选键

主键：候选键中最具代表性的，唯一

复合键：关系中必须用多列才能唯一标识元组时，为复合主键

代理键：DBMS自动生成的数字序列主键，可替代复合主键

外键：关系中的关键字为另一个关系的主键，此时称该关系为另一个关系的从表

关系模式：关系名（主键属性，属性2，…，属性x）

2.2 关系模型原理

数据模型-关系模型：基于二维表结构存储数据实体及实体间联系

关系模型数据操作

集合：选择（select）、投影（project）、连接（join）、交（intersection）、并（union）、差（difference）

元组：行插入（Insert）、 修改（Update）、删除（Delete）

交并差：关系R与关系S需有相同属性组成（简单不赘述）

笛卡尔积×：R的属性列+S的属性列=新表属性列，元组进行排列组合

选择：σF®：在关系R中选出满足条件F的元组形成新的关系。(F：条件表达式)；对应where子句

投影：πA（R）：在R中选出若干属性列组成一个新关系。(A:属性组)；对应select子句

连接：从两个关系的笛卡尔积中选取属性间满足条件AθB的元组组成新的关系

条件连接

等值连接-条件连接的特例：选取指定属性列的值相等的行，可以选择多个条件

SELECT 列表
FROM 表1
JOIN 表2 ON 表1.列 = 表2.列;

自然连接-等值连接的特例：根据两个表中的所有相同属性列进行连接，连接后多个的相同属性列只保留一个

SELECT 列表
FROM 表1
NATURAL JOIN 表2;

外连接-自然连接的特例：自然连接只有相同属性列的值相同才返回，外连接中若有一方的行的共同属性列没有找到与之匹配的行，则也保留该行，以NULL填充。左外连接、右外连接、全外连接

除（了解；t代表元组、XY代表属性）

关系模型完整性约束：实体完整性约束（主键非空且不唯一）、参照完整性约束（主从表的外键主键一致）、用户自定义完整性约束

三、SQL语句

3.1 sql概述

数据定义语言（DDL）：创建修改删除数据库对象；create/drop/alter database/table/index

数据操纵语言（DML）：增删改数据；insert/update/delete

数据查询语言（DQL）：数据查询；

数据控制语言（DCL）：数据库对象访问控制；grant/deny/revoke

事物处理语言（TPL）：事物处理；begin transaction/commit/rollback

游标控制语言（CCL）：游标操作；declare cursor/fetch into/close cursor

3.2 数据定义语句

数据库

CREATE DATABASE CourseDB;

ALTER DATABASE CourseDB RENAME TO CourseManageDB;

DROP DATABASE CourseManageDB；

数据库表

格式：列名+数据类型+列完整性约束

列完整行约束

PRIMARY KEY——单列主键

NOT NULL——非空值

NULL——空值

UNIQUE——值唯一

CHECK——有效性检查

DEFAULT——缺省值

表约束：可定义复合主键、代理键、外键，可以命名约束

CREATE  TABLE  Student
(StudentID      char(13)    PRIMARY  KEY,
 StudentName    varchar(10)	NOT NULL UNIQUE,
 StudentGender  char(2)     NULL     CHECK(StudentGender IN('男','女')),
 BirthDay       date        NULL,
 Major          varchar(30) NULL     DEFAULT '本科生',
 StudentPhone	char(11)    NULL
 --或者
 CONSTRAINT Student_PK PRIMARY Key(StudentID,StudentName)
);
 
ALTER TABLE STUDENT ADD <.><.>[..];
ALTER TABLE STUDENT DROP COLUMN<.>;
ALTER TABLE STUDENT DROP CONSTRAINT<.>;
ALTER TABLE STUDENT RENAME TO<>;
ALTER TABLE STUDENT RENAME <> TO <>;
ALTER TABLE STUDENT ALTER COLUMN<> TYPE<>;
 
DROP TABLE <>;

表约束定义代理键：有时为了方便数据处理，可以使用代理键去替代复合主键

CREATE  TABLE  Plan
( CoursePlanID	serial		    NOT  NULL,
  CourseID  	char(4)  		NOT  NULL,
  TeacherID  	char(4)  		NOT  NULL,
  CourseRoom  	varchar(30),
  CourseTime  	varchar(30),
  Note  		varchar(50),
  CONSTRAINT	CoursePlan_PK	PRIMARY Key(CoursePlanID)
);

表约束定义外键

CREATE  TABLE  Register
( CourseRegID  	serial	    NOT  NULL,
  CoursePlanID  Int  		NOT  NULL,
  StudentID  	char(13),
  Note  		varchar(30),
  CONSTRAINT	CourseRegID_PK	PRIMARY Key(CourseRegID),
  CONSTRAINT	CoursePlanID_FK	FOREIGN Key(CoursePlanID)
	REFERENCES  Plan(CoursePlanID)
    ON DELETE CASCADE,
  CONSTRAINT	StudentID_FK	FOREIGN KEY(StudentID)
	REFERENCES  Student(StudentID)
    ON DELETE CASCADE
);

数据库索引

索引（index）：将关系表按照指定列的取值顺序组织元组数据的数据结构，加快查询，占用额外存储空间、开销较大

CREATE INDEX Birthday_Idx ON  STUDENT(Birthday);

ALTER INDEX Birthday_Idx RENAME TO Bday_Idx;

DROP INDEX bday_idx;

3.3 数据操纵语句

数据插入

INSERT INTO Student VALUES('2017220101105','柳因','女','1999-04-23','软件工程', 'liuyin@163.com');

数据修改

UPDATE  Student
SET  Email='zhaodong@163.com'
WHERE   StudentName='赵东';

数据删除

DELETE 
FROM  STUDENT
WHERE   StudentName='张亮';

3.4 数据查询语句

单表查询

-- 通用结构
SELECT  [ALL|DISTINCT]  <目标列>[，<目标列>…]
[ INTO <新表> ]
FROM  <表名|视图名>[，<表名|视图名>…]
[ WHERE  <条件表达式> ]
[ GROUP BY  <列名> [HAVING <条件表达式> ]]
[ ORDER BY  <列名> [ ASC | DESC ] ];

-- BETWEEN AND 限制列值范围
SELECT  *
FROM  STUDENT
WHERE BirthDay BETWEEN ‘2000-01-01’ AND ‘2000-12-30’;

-- LIKE 通配符'_'代表一个字符'%'代表一个或多个字符 
SELECT  *
FROM  STUDENT
WHERE  Email  LIKE  ’%@163.com’;

-- AND\OR\NOT 逻辑运算符
SELECT  StudentID, StudentName, StudentGender, Major
FROM  STUDENT
WHERE  Major=’软件工程’  AND  StudentGender=’男’;

-- IN 限定范围
SELECT  StudentID, StudentName, StudentGender, Major
FROM  STUDENT
WHERE  Major IN  ('计算机应用');

-- ORDER BY <> ASC/DESC 默认升序ASC
-- 多个列排序时，写在前面排序的基础上满足后面的
SELECT  *
FROM  STUDENT
ORDER  BY  Birthday DESC ,  StudentName  ASC;

-- 内置函数
SELECT  COUNT（*） AS  学生人数
FROM  Student;
SELECT  Min（Birthday） AS 最大年龄，Max（Birthday） AS 最小年龄
FROM  Student;

-- GROUP BY <> HAVING 
-- 专业统计STUDENT表中男生人数，但限定只显示人数大于2的人数
SELECT  Major  AS 专业,  COUNT（StudentID） AS 学生人数
FROM  Student
WHERE  StudentGender=’男’
GROUP  BY  Major
HAVING  COUNT(*)>2;

多表关联查询

子查询

SELECT  TeacherID, TeacherName, TeacherTitle
FROM  Teacher
WHERE  CollegeID  IN
        (SELECT  CollegeID  
     FROM  College
     WHERE  CollegeName='计算机学院');

连接查询

SELECT  B.CollegeName AS 学院名称,  A.TeacherID  AS 编号, A.TeacherName  AS 姓名,  A.TeacherGender  AS 性别,  A. TeacherTitle  AS 职称
FROM  Teacher  AS  A，College  AS  B
WHERE  A.CollegeID=B.CollegeID 
ORDER  BY  B.CollegeName, A.TeacherID;

-- JOIN ON 内连接
SELECT  B.CollegeName AS 学院名称,  A.TeacherID  AS 编号, A.TeacherName  AS 姓名,  A.TeacherGender  AS 性别,  A. TeacherTitle  AS 职称
FROM  TEACHER  AS  A  JOIN  COLLEGE  AS  B
ON  A.CollegeID=B.CollegeID 
ORDER  BY  B.CollegeName, A.TeacherID;

-- LEFT JOIN/RIGHT JOIN/FULL JOIN 外连接
SELECT C.CourseName AS 课程名称, T.TeacherName AS 教师, 
COUNT  (R.CoursePlanID)  AS 选课人数
FROM  COURSE  AS  C  JOIN  PLAN  AS  P  
ON  C.CourseID=P.CourseID 
JOIN  TEACHER  AS  T  ON  P.TeacherID=T.TeacherID
LEFT  JOIN  REGISTER  AS  R  ON  P.CoursePlanID=R.CoursePlanID
GROUP  BY C.CourseName, T.TeacherName;

四、数据库设计

4.1 概述

数据库开发过程

数据库结构模型设计-数据库结构模型

概念数据模型 Conceptual Data Model (CDM)	从用户角度所建模的系统数据对象及其关系，它帮助用户分析信息系统的数据结构关系。
逻辑数据模型 Logic Data Mode(LDM)	从系统分析员角度所建模的系统数据对象逻辑结构关系，它帮助开发人员分析信息系统的逻辑数据结构。
物理数据模型 Physical Data Model (PDM)	从系统设计人员角度所建模的系统数据物理存储及结构关系，它针对设计者具体定义信息系统的数据库表结构。

4.2 E-R模型方法

“实体-联系模型”（Entity-Relationship Model）的简称。它是一种描述现实世界概念数据模型、逻辑数据模型的有效方法，基本元素有实体、属性、标识符和联系

联系度数：联系中关联的实体数目

二元联系-基数：实体的实例与另一实体实例存在的数量对应关系，1/n，表示最大基数

实体参与关系：可选/强制，表示最小基数

实体继承联系：表示实体的相似性，有公共属性的是父实体，有特殊性的是子实体

强弱实体联系：弱实体的存在必须以强实体的存在为前提，这是一个相对的概念

扩展建模实例

4.3 数据库建模设计

概念数据模型

概念数据模型设计一般是采用E-R模型方法进行建模设计

CDM/LDM/PDM模型转换设计

当使用关系数据库时，物理数据模型（PDM）即为关系模型。CDM/LDM到PDM的转换其实就是E-R模型到关系模型的转换。

CDM/LDM转换

1. LDM将CDM的多对多实体联系转化为易于关系数据库实现的一对多实体联系
2. LDm将CDM中的标识符依赖实体进一步细化，并区分主键标识符< pi >和外键标识符< fi >，以便数据模型规范化处理

E-R模型到关系模型转换原理：

1. 将每一个实体转换成一个关系表，实体属性转换为关系表的列，实体标识符转换为关系表的主键或外键。
2. 将实体之间的联系转化为关系表之间的参照完整性约束。

弱实体转换为关系表

实体联系转换

实体继承联系转换

将父表中的主键放置到子表中，既做主键又做外键。

实体递归联系转换

4.4 数据库规范化设计

通常在LDM时期进行规范化设计完善，减少冗余数据，设计合理依赖约束关系，降低维护数据完整性一致性的工作量，访问更高效

问题：一个表中存在多个主题的数据，且大量重复出现

函数依赖理论

在关系模式R（U）中， U 为关系R的属性集合，X和Y为属性U的子集。设t，s是关系R中的任意两个元组，如果t[X] = s[X]，则t[Y] = s[Y]。那么称Y函数依赖于X，表示为X→Y。此时函数依赖的左部称为决定因子，右部称为依赖函数。决定因子和依赖函数都是属性的集合。函数依赖反映属性或属性组之间相互依存、互相制约的关系，即关系表中属性之间的依赖关系。

完全函数依赖

设X、Y是某关系的不同属性集，如X→Y，且不存在X中的子集 X’使X’→Y，则Y称完全函数依赖，否则称Y部分函数依赖。

对于关系R（X, Y, N, O, P），其中（X，Y）为复合主键，若其它属性N,O,P都完整依赖于该复合主键，则称关系R为完全函数依赖。反之，其它属性N,O,P仅依赖于X，或仅依赖于Y，则称R为部分函数依赖。

函数传递依赖

对于关系R（X, N, O, P），其中X为主键，若属性N依赖于X，而X不依赖于N，属性O依赖于N。则属性O函数传递依赖于X。

多值函数依赖

对于教学关系R（课程, 教师, 课程参考书），一门课程可以有多个任课教师，也可以有多本参考书；每个任课教师可以任意选择他的参考书。该关系存在多值函数依赖。

关系规范化范式

1NF：关系表中的属性不可再细分，否则该表不是关系表

2NF：1NF基础上，消除关系中的属性部分函数依赖

有一个关系（A,B,N,O,P）,其复合主键为（A,B）,那么N,O,P这三个非键属性都不存在只依赖A或只依赖B情况，则该关系满足第2范式,反之,不满足第2范式。

3NF：2NF基础上，切断属性传递函数依赖

若有一个关系（A,N,O,P）,主键为（A）,那么非键属性N,O或P都不能由单个的N,O或P或它们的组合所确定。该关系满足第3范式。

BCNF：所有函数依赖的决定因子都是候选键

4NF：BCNF基础上，消除了多值函数依赖

逆规范化处理

规范化过高也会导致数据库性能降低，因此要适当降低规范化范式约束，允许适当的数据冗余性，以获取数据访问性能。可以通过增加冗余列或派生列，多个表合并为一个表进行处理。

五、数据库管理

5.1 概述

包括性能索引查询并发管理、角色用户对象权限管理、安全备份恢复等。

pgadmin4-Postgresql的数据库管理工具

5.2 事务管理

防止业务处理单元的一组操作中某一步出现错误导致混乱，事务是DBMS执行的最小任务单元、故障恢复单元、并发控制任务单元

生命周期状态变迁图

事务ACID特性

• 原子性（Atomicity）：事务所有操作在数据库中要么全部执行，要么全部不执行。
• 一致性（Consistency）：事务多次执行，其结果应一致。
• 隔离性（Isolation）：事务与事务之间隔离，并发执行透明。
• 持续性（Durability ）：事务完成后，数据改变必须是永久的。

START TRANSACTION;
INSERT  INTO college( collegeID, collegename)VALUES ('004', '外语学院');
INSERT  INTO college( collegeID, collegename)VALUES ('005', '数学学院');
INSERT  INTO college( collegeID, collegename)VALUES ('006', '临床医学院');
COMMIT;

5.3 并发控制

事务调度

多个事务在DBMS同时运行可能对共享数据同时访问，需要加以约束控制按照恰当顺序访问，否则导致数据不一致和死锁。在DBMS中，为使并发事务调度实现的处理结果与串行化调度处理结果一致，事务管理器将并发执行事务的SQL数据操作请求提交给并发控制调度器。由并发控制调度器将各个事务的SQL数据操作请求按照一定顺序进行调度执行，并完成对数据库缓冲区的读写操作。

数据不一致问题

丢失更新

T1、T2两个事务并发执行，它们均对数据库共享数据A进行了非锁定资源的读写操作。当事务T1和T2均读入该共享数据A并修改，T2提交的结果破坏了T1提交的结果，导致T1的修改被丢失。

不可重复读

幻象读

事务T1按一定条件从数据库中读取某些数据记录后，事务T2在其中插入了一些记录，当T1再次按相同条件读取数据时，发现多了一些记录。称为幻象读取。与不可重复读类似。

脏数据读取

锁机制

事务对共享数据加锁访问，锁的粒度由数据库到表页面行

• 排它锁定(Lock-X)——锁定后，不允许其它事务对共享数据再加锁
• 共享锁定(Lock-S)——锁定后，只允许其它事务对共享数据添加读取锁

锁的相容性：排他锁与其他锁均不相容，共享锁之间相容

加锁协议

一级加锁协议：对共享数据修改时执行排它锁定指令，直到该事务处理完成才解锁。只能避免丢失更新问题。

二级加锁协议：一级协议基础上，对共享数据读操作进行共享锁定指令，读完数据立刻释放共享锁。避免丢失更新问题，脏读。

三级加锁协议：一级协议基础之上，对共享数据读操作进行共享锁定指令，直到事务处理结束才释放。避免丢失更新、脏读、不可重复读问题。

两阶段锁定协议

二阶段锁定协议可以保证可串行化调度

每个事务必须分两个阶段提出加锁和解锁申请:

• 增长阶段，事务只能获得锁，但不能释放锁。
• 缩减阶段，事务只能释放锁，但不能获得新锁。

死锁

事务同时锁定两个及以上资源可能出现彼此不能继续执行的状态

事务隔离

5.4 安全管理

存取安全模型

系统安全模型

用户管理

在DBMS中对每个用户进行管理

--创建用户
CREATE USER "USERA" WITH
 LOGIN
 INHERIT
 NOSUPERUSER
 NOCREATEDB
 NOCREATEROLE
 NOREPLICATION
 CONNECTION LIMIT -1
 PASSWORD '123456';
 
--修改用户
ALTER USER "USERA"
  CONNECTION LIMIT 10
  PASSWORD 'GRES123';
ALTER USER  <用户名>  [ [ WITH ] option [ ... ] ];              	--修改用户的属性
ALTER USER  <用户名>  RENAME TO <新用户名>;                          --修改用户的名称
ALTER USER  <用户名>  SET <参数项> { TO | = } { value | DEFAULT };  --修改用户的参数值  
ALTER USER  <用户名>  RESET <参数项>;			                      --重置用户参数值

--删除用户
DROP USER "USERA";

权限管理

DBA对用户进行数据库系统/数据库对象访问操作/数据库对象定义操作的授予/收回/拒绝权限

GRANT  SELECT  ON  Department  TO  userA;
GRANT  SELECT  ON  Employee    TO  userA;
GRANT  SELECT  ON  Project     TO  userA;
GRANT  SELECT  ON  Assignment  TO  userA;

GRANT  <权限名> ON  <对象名>  TO {数据库用户名|用户角色名}；
REVOKE  <权限名> ON  <对象名>  FROM {数据库用户名|用户角色名}；
DENY  <权限名> ON  <对象名> TO {数据库用户名|用户角色名}；

角色管理

将一组相同权限的用户定义为角色

CREATE  ROLE  <角色名> [ [ WITH ] option [ ... ] ]; 	               --创建角色
ALTER  ROLE  <角色名>  [ [ WITH ] option [ ... ] ]; 	               --修改角色属性
ALTER  ROLE  <角色名>  RENAME TO <新角色名>; 		                    --修改角色名称 
ALTER  ROLE  <角色名>  SET <参数项> { TO | = } { value | DEFAULT };	 --修改角色参数值
ALTER  ROLE  <角色名>  RESET <参数项>;	                    		 --复位角色参数值
DROP  ROLE  <角色名>;					                           	   --删除指定角色

CREATE  ROLE  "Role_Manager"  WITH
 LOGIN
 NOSUPERUSER
 NOCREATEDB
 NOCREATEROLE
 INHERIT
 NOREPLICATION
 CONNECTION LIMIT -1;

GRANT  SELECT,INSERT,UPDATE,DELETE  ON  Department  TO  "Role_Manager";
GRANT  SELECT,INSERT,UPDATE,DELETE  ON  Employee  TO  "Role_Manager";
GRANT  SELECT,INSERT,UPDATE,DELETE  ON  Project  TO  "Role_Manager";
GRANT  SELECT,INSERT,UPDATE,DELETE  ON  Assignment  TO  "Role_Manager";

为用户赋予角色

CREATE USER  "StudentUser"  WITH
  LOGIN
  CONNECTION LIMIT -1
  IN ROLE "R_Student"
  PASSWORD '123456';
CREATE USER  "TeacherUser"  WITH
  LOGIN
  CONNECTION LIMIT -1
  IN ROLE "R_Teacher"
  PASSWORD '123456';

5.5 数据库备份与恢复

数据库备份：对数据和状态（日志）进行副本复制

数据库恢复：从备份副本将数据库从错误状态恢复到某一正确状态

备份SAMPLE数据库到一个G磁盘的根目录文件Sample.bak中。

BACKUP DATABASE SAMPLE   
TO DISK = ‘G:\Sample.bak'; 

从存储备份文件中恢复SAMPLE数据库。

RESTORE DATABASE SAMPLE
FROM DISK = ‘G:\Sample.bak'; 

也可利用事务日志前滚或回滚方式进行数据库恢复

六、数据库应用编程

6.1 数据库连接技术ODBC

传统应用开发通常选用特定DBMS管理系统，Microsoft推出开放式数据库互连（Open DataBase Connectivity，简写为ODBC）技术。ODBC实现了应用程序对多种不同DBMS的数据库的访问，实现了数据库连接方式的变革。 ODBC使APP利用相同源码访问不同数据库系统。

ODBC层次结构

6.2 数据库连接技术（JDBC）

JDBC（Java DataBase Connectivity，Java数据库连接）技术的简称 ，是一种用于执行SQL语句的Java API。它由一组用Java编程语言编写的类和接口组成。这个API由java.sql.*包中的一些类和接口组成，它为数据库开发人员提供了一个标准的API，使他们能够用纯Java API 来编写数据库应用程序。使用JDBC访问数据库需要相应数据库的JDBC驱动程序

// 加载驱动
Class.forName("org.postgresql.Driver");
// 建立连接
String UrL = "jdbc:postgresql://localhost:5432/testdb";
String username = "myuser";
String password = "sa";
Connection conn = DriverManager.getConnection(URL,username,password);
// 创建Statement对象
Statement stmt = conn.createStatement();
// 执行sql语句
String sql = "INSERT INTO public.student (sid, sname, gender, birthday, major, phone)" + " VALUES ('2017001', '张山', '男', '1998-10-10','软件工程','13602810001')";
stmt.executeUpdate(sql);

创建了Statement对象 ,就可以向Statement对象发送SQL语句。主要掌握两种执行SQL语句的方法：executeQuery()、executeUpdate()

1. executeQuery()：返回语句执行后的单个结果集的，所以通常用于select语句
2. executeUpdate()返回值是一个整数，指示受影响的行数（可以用于update、insert、delete语句）

// 保存结果集
Statement stmt = conn.createStatement();
String sql = "SELECT id, name, age FROM company";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()){
    System.out.println(rs.getString("name"));
}
// 关闭连接
rs.close();
stmt.close();
conn.close();

6.3 数据库存储过程

存储过程，一种数据库对象，由一组能完成特定功能的SQL语句集构成，编译后存储在数据库服务端接受调用。能够减少网络通信量加快执行速度，不过开发调试复杂可移植性差。

CREATE [ OR REPLACE ] FUNCTION/PROCEDURE  name
    ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS retype | RETURNS TABLE ( column_name  column_type [, ...] ) ]
AS $$         //$$用于声明存储过程的实际代码的开始
DECLARE
        -- 声明段
BEGIN
        --函数体语句
END;
$$ LANGUAGE lang_name;  //$$ 表明代码的结束, LANGUAGE后面指明所用的编程语言

-- 创建一个名为countRecords()的存储过程统计STUDENT表的记录数。
CREATE OR REPLACE FUNCTION countRecords ()  
RETURNS integer AS $count$  
declare  
    count integer;  
BEGIN  
   SELECT count(*) into count FROM STUDENT;  
   RETURN count;  
END;  
$count$ LANGUAGE plpgsql;

-- 执行存储过程
SELECT * FROM countRecords();

-- 存储过程调用其他存储过程:select  into 自定义变量  from 存储过程名（参数）；
CREATE OR REPLACE FUNCTION testExec()  
returns integer AS $$ 
declare
   rec integer;  
BEGIN  
   select  into rec countRecords();  
     //如果不关心countRecords()的返回值，则可用 PERFORM countRecords() 代替; 
   return  rec;  
END;  
$$ LANGUAGE plpgsql;

-- 删除存储过程
DROP FUNCTION [ IF EXISTS ] name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [ CASCADE | RESTRICT ]

DROP FUNCTION IF EXISTS testExec();

6.4 数据库触发器

触发器是一个定义在表或视图上的特殊类型的存储过程（不传递接受参数）、一个特殊的事务单位，由操作事件触发自动执行，可以实现比约束更复杂的数据完整性，用于加强数据完整性约束和业务规则

语句级触发器只执行一次（默认触发器）；行级触发器每有数据变化一行就执行一次

INSTEAD OF触发器：事件发生时只执行触发器不执行原本的sql语句，一个表或视图只能有一个INSTEAD OF触发器

触发器相关特殊变量

NEW：RECORD类型，对于行级触发器其存有INSERT或UPDATE操作产生的新数据行。对于语句级触发器其值为NULL

OLD：RECORD类型，对于行级触发器其存有DELETE或UPDATE操作修改或删除的旧数据行。对于语句级触发器其值为NULL

TG_OP:text类型，值为INSERT/UPDATE/DELETE，说明引发触发器的操作

创建触发器步骤：

1. 检查所依附的表或视图是否存在
2. 创建触发器执行的触发器函数，返回类型为TRIGGER
3. 创建触发器

CREATE  TRIGGER  触发器名   
    { BEFORE | AFTER | INSTEAD OF }
     ON 表名
     [ FOR [ EACH ] { ROW | STATEMENT } ]
     EXECUTE PROCEDURE 存储过程名 ( 参数列表 )

实例 为了防止非法修改stu_score表的课程成绩，创建audit_score表记录stu_score表的成绩变化

-- 创建表
CREATE TABLE stu_score
( 
    sid character(10) NOT NULL,
    cid character(10) NOT NULL,
    score numeric(5,1),
    CONSTRAINT stu_score_pkey PRIMARY KEY (sid, cid)
)
CREATE TABLE audit_score
(    username character(20) ,  --用户名
    sid character(10) ,
    cid character(10) ,
    updatetime text ,            --修改的时间
    oldscore numeric(5,1),   --修改前的成绩
    newscore numeric(5,1)  --修改后的成绩
)
-- 创建函数
CREATE OR REPLACE FUNCTION score_audit RETURNS TRIGGER 
AS $score_sudits$
BEGIN
	IF (TG_OP = 'DELETE') THEN
		INSERT INTO audit_score SELECT user,old.sid,old.cid,OLD.score ;
		RETURN OLD;
	ELSIF (TG_OP = 'UPDATE') THEN
		INSERT INTO audit_score
		SELECT user,old.sid,old.cid,now(),OLD.score,new.score
		WHERE old.sid=new.sid and old.cid=new.cid;
		RETURN NEW
	ELSIF (TG_OP = 'INSERT') THEN
		INSERT INTO audit_score SELECT user,new.sid,new.cid,now(),null,new.score;
		RETURN NEW;
	END IF;
	RETURN NULL;
END;
$score_audits$ LANGUAGE plpgsql;
-- 创建触发器
CREATE TRIGGER score_audit_triger
AFTER INSERT OR UPDATE OR DELETE ON stu_score
FOR EACH ROW EXECUTE PROCEDURE score_audit();
-- 修改触发器
ALTER TRIGGER score_audit_trigger ON stu_score  RENAME TO score_audit_trig;
-- 删除触发器
DROP TRIGGER [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]

DROP TRIGGER IF EXISTS score_audit_trig ON stu_score CASCADE;

6.5 数据库游标

游标是一种存放了查询数据库表返回的数据记录的临时的数据库对象，包含查询结果和指针，提供了处理结果集中每一条记录的机制，它总是与一条查询SQL语句相关联

声明游标

-- 声明游标
curStudent CURSOR FOR SELECT * FROM student;
curStudentOne CURSOR (key integer)7y IS SELECT * FROM student WHERE SID = key;

打开游标

使用游标

关闭游标

6.6 嵌入式sql编程

sql与宿主语言，混合编程

七、NoSQL数据库技术

CAP理论

在分布式的环境下设计和部署系统时，有3个核心的需求：CAP对应一致性（Consistency），可用性（Availability）和分区容忍性（Partition Tolerance）

CAP理论的核心：

一个分布式系统不可能同时很好的满足一致性、可用性和分区容错性这三个需求，最多只能同时较好的满足两个。

• CA - 单点集群，满足一致性，可用性的系统，
• CP - 满足一致性，分区容忍性的系统，
• AP - 满足可用性，分区容忍性的系统，

CAP目的：

• CAP是为了探索不同应用的一致性C与可用性A之间的平衡，
• 在网络或其他原因，通过牺牲一定的一致性C来获得更好的性能与扩展性
• 在有分隔发生，选择可用性A，集中关注分隔的恢复，需要分隔前、中、后期的处理策略， 及合适的补偿处理机制。
• 选择什么样的方式： 放弃Ｐ？放弃Ａ？放弃Ｃ？BASE

BASE

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

base特点

• ACID是事物的特征， A（原子性）C（一致性）I（隔离性）D（持久性），ACID的特点是强一致性、隔离性、采用悲观保守方法、难以变化；
• BASE的特点是弱一致性、可用性优先、采用乐观方法、适应变化并且简单快捷。
• 对数据不断增长的系统， 大数据环境下系统的可用性及分隔容忍性的要求要高于强一致性，很难满足事务要求的ACID特性。

最终一致性

• 强一致性： 要求无论更新操作实在哪一个副本执行，之后所有的读操作都要能获得最新的数据。
• 弱一致性：用户读到某一操作对系统特定数据的更新需要一段时间，称这段时间为“不一致性窗口”。
• 最终一致性： 弱一致性的一种特例，保证用户最终能够读取到某操作对系统特定数据的更新。

存储模型

• 列存储数据库，将同一列的数据存储在一起，可以存储结构化和半结构化数据
• 键值存储数据库，存储的数据是有键（key）和值（value）两部分组成，通过key快速查询到其value，value的格式可以根据具体应用来确定
• 文档存储数据库，存储的内容是文档型的，可以用格式化文件（类似json、XML等）的格式存储
• 图存储数据库，数据以有向加权图方式进行存储

分类	Examples****举例	典型应用场景	数据模型	优点	缺点
键值 （key-value）	Tokyo Cabinet/Tyrant, Redis, Voldemort, Oracle BDB	内容缓存，主要用于处理大量数据的高访问负载，也用于一些日志系统等等	Key 指向 Value 的键值对，通常用hash table来实现	查找速度快	数据无结构化，通常只被当作字符串或者二进制数据
列存储数据库	Cassandra, HBase, Riak	分布式的文件系统	以列簇式存储，将同一列数据存在一起	查找速度快，可扩展性强，更容易进行分布式扩展	功能相对局限
文档型数据库	CouchDB, MongoDb	与Key-Value类似，Value是结构化的，不同的是数据库能够了解Value的内容	Key-Value对应的键值对，Value为结构化数据	数据结构要求不严格，表结构可变，不需要像关系型数据库一样需要预先定义表结构	查询性能不高，而且缺乏统一的查询语法。
图形(Graph)数据库	Neo4J, InfoGrid, Infinite Graph	社交网络，推荐系统等。专注于构建关系图谱	图结构	利用图结构相关算法。比如最短路径寻址，N度关系查找等	很多时候需要对整个图做计算才能得出需要的信息，而且这种结构不太好做分布式的集群方案
-----
键值 （key-value）	Tokyo Cabinet/Tyrant, Redis, Voldemort, Oracle BDB	内容缓存，主要用于处理大量数据的高访问负载，也用于一些日志系统等等	Key 指向 Value 的键值对，通常用hash table来实现	查找速度快	数据无结构化，通常只被当作字符串或者二进制数据
列存储数据库	Cassandra, HBase, Riak	分布式的文件系统	以列簇式存储，将同一列数据存在一起	查找速度快，可扩展性强，更容易进行分布式扩展	功能相对局限
文档型数据库	CouchDB, MongoDb	与Key-Value类似，Value是结构化的，不同的是数据库能够了解Value的内容	Key-Value对应的键值对，Value为结构化数据	数据结构要求不严格，表结构可变，不需要像关系型数据库一样需要预先定义表结构	查询性能不高，而且缺乏统一的查询语法。
图形(Graph)数据库	Neo4J, InfoGrid, Infinite Graph	社交网络，推荐系统等。专注于构建关系图谱	图结构	利用图结构相关算法。比如最短路径寻址，N度关系查找等	很多时候需要对整个图做计算才能得出需要的信息，而且这种结构不太好做分布式的集群方案