数据库系统-学习记录8

ch9.服务器环境下的SQL

三层环境

  大型数据库的安装具有通用的体系结构，这样的结构区分三种不同而又相互关联的功能：Web服务器、应用服务器和数据库服务器

  打开网址时，连接网页服务器，下载网页，生成为可显示的内容显示在ie浏览器上（ie浏览器仅用于显示）

  应用逻辑：业务，例如人民币转换为美元

  业务逻辑生成为SQL语句，递交给数据库服务器，数据库服务器将数据接收出来，再交给业务逻辑生成

SQL环境

  模式：是表、视图、断言、触发器和其他类型的集合

  目录：模式的集合，是支持唯一的可访问术语的基本单元

  簇：目录的集合，每个用户有一个关联的簇

SQL/宿主语言接口

  高级语言一次只能处理一个目标，而SQL一次能处理多个，两种语言通过共享变量进行连接

阻抗不匹配问题

  因为SQL数据模式和其他语言的模式差别很大，所以会导致SQL语句和常规的编程语言不匹配，致使连接出现问题——阻抗不匹配问题

  真正的数据库，既要有SQL，也要有宿主语言

SQL与宿主语言连接

  在宿主语言中使用SQL语句时，通过语句前面的关键字EXEC SQL提示预处理器将有SQL代码进入。数据库只能由SQL语句访问，而数据库和宿主语言程序之间通过共享变量实现信息交换

  SQL中共享变量前面要加上冒号作为前缀，而宿主语言则不需要

DECLARE节

  共享变量的声明，在如下两个嵌套SQL语句之间：

exec sql begin declare section;
...(声明节)
exec sql end declare section;

  声明节中声明的变量类型必须是宿主语言和SQL都可以处理的（例如整型、实型、字符型）

使用共享变量

  举例：在C语言中，向关系Studio中插入数据：

void getStudioO {
    EXEC SQL BEGIN DECLARE SECTION;
        char studioName[50], studioAddr[256]:
        char SqLSTATE[6];
    EXEC SQL END DECLARE SECTION;
    /* print request that studio name and address be entered and read response into variables studioName and studioAddr */
    EXEC SQL INSERT INTO Studio(name, address)
	    VALUES (:studioName, :studioAddr);
}

  对于不返回结果的SQL语句，可以直接使用EXEC SQL为前缀，嵌入到宿主语言中

  但由于“阻抗不匹配”，有返回值的select-from-where查询不能直接嵌入（查询产生的结果是元组包），为了能够连接上，需要从单元组选择语句和游标中选择一种机制

单元组选择语句

  类似于普通的select-from-where语句，单元组只是在select子句后紧跟关键字INTO和一连串的共享变量，例如：

EXEC SQL SELECT netWorth
    INTO :presNetWorth
    FROM Studio, MovieExec
    WHERE presC# = cert# AND
	    Studio.name = :studioName;

  在这种情况下，只产生单一的元组，且元组只有一个分量——属性netWorth，此分量被存储在共享变量presNetWorth中

  如果查询结果不是单一元组（没有元组或者多个元组），则会发生错误，不会分配给共享变量

游标

  缓冲区，用于存放查询结果

  游标可以遍历关系的元组，这个关系可以是一个被存储的表，也可以是由查询产生的结果

  游标的声明方式：

EXEC SQL DECLARE <游标名称> CURSOR FOR <查询>

  这里的查询可以是常规的select-from-where或关系名，游标的范围覆盖整个得到的关系元组

  初始化游标的位置，使游标指向其覆盖的那个关系中的第一个元组，并从那里开始检索：

EXEC SQL OPEN <游标名称>

  fetch子句：得到游标覆盖的那个关系中的下一个元组：

EXEC SQL FETCH FROM <游标名称> INTO <变量列表>

  关闭游标：

EXEC SQL CLOSE <游标名称>

游标更新

  游标不仅可以读取每个元组的值，还可以修改或者删除当前元组，例如：

EXEC SQL DELETE FROM MovieExec
	WHERE CURRENT OF execCursor;

EXEC SQL UPDATE MovieExec
    SET netWorth = 2 * netWorth
    WHERE CURRENT OF execCursor;

避免并发修改

动态SQL

存储过程

  持久性存储模块（PSM）是SQL标准最新版本的一部分，它允许用简单通用的语言编写过程，并且将它们存储在数据库中，作为模式的一部分

PSM函数和过程的创建

  PSM定义了模块，该模块是函数和过程定义、临时关系声明和其他可选声明的集合

  过程声明方式：

CREATE PROCEDURE <名字> (<参数>)
	<局部声明>
	<过程体>;

  函数定义方式：

CREATE FUNCTION <名字> (<参数>) RETURNS <类型>
	<局部声明>
	<函数体>;

  局部声明时，需要加上模式前缀IN/OUT/INOUT，分别表示参数是仅输入/仅输出/既可输入又可输出的

PSM简单语句格式

  调用语句：

CALL <过程名> (<参数>);

  返回语句：

RETURN <表达式>;

  局部变量声明：

DECLARE <名字> <类型>;

  赋值语句：

SET <变量> = <表达式>;

  语句组：置于保留字BEGIN和END之间

  语句标号：用名字（标号名）和冒号作为前缀来标识语句

分支语句

IF <condition> THEN
	<statement list>
ELSEIF <condition> THEN
	<statement list>
ELSEIF
	...
ELSE
	<statement list>
END IF;

PSM中的查询

  子查询可用于SQL中任何地方

  返回单一值的查询可用在赋值语句的右边

  使用单元组选择语句

  使用游标

PSM中的循环

LOOP
	<语句列表>
END LOOP;

  中断循环：

LEAVE <循环标识>;

for循环

FOR <loop name> AS <cursor name> CURSOR FOR
	<query>
DO
	<statement list>
END FOR;

异常处理

DECLARE <下一步到哪里> HANDLER FOR <条件列表>
	<语句>

使用PSM函数和过程

  用保留字CALL作为前缀来调用过程

  函数作为表达式的一部分出现

ch10.关系数据库的新课题

  grant、revoke授权语句

  循环递归

  数据库中，还有一种权限控制：密级

SQL中的安全机制和用户认证

权限

  SQL中共有九种类型的权限：SELECT、INSERT、DELETE、UPDATE、REFERENCE、USAGE、TRIGGER、EXECUTE、UNDER
前四种用于关系

  每条SQL语句都要有相应的权限才能被执行

  REFERENCE是约束权限

  USAGE主要用于关系和断言之外的多种元素上

  TRIGGER是定义关系上的触发器的权力

  EXECUTE是执行PSM过程或函数之类的代码的权力

  UNDER是创建给定子类型的权力

创建权限

  初始化，三种建立属主身份的情况：
  1、模式创建时，模式和该模式中所有的表以及其他的模式元素的所有权都属于创建这个模式的用户所有。这样这个用户拥有模式元素的所有可能的权限
  2、会话被CONNECT语句初始化时，有机会用AUTHORIZATION子句指定用户，例如：

CONNECT TD Starfleet-sql-server AS conn1
	AUTHORIZATION kirk;

  3、模块创建时，可通过AUTHORIZATION子句选择其属主，例如：

AUTHORIZATION picard；

检查授权的过程

  每个模块、模式和会话，都有一个相关的授权ID

  在执行SQL操作时，只要当前授权ID拥有执行操作所涉及的数据库元素所必需的权限，就可以执行这个SQL操作

授权

  授权语句：

grant <权限列表> on <数据库元素> to <用户列表>

  其后可以加上WITH GRANT OPTION

  授权的举例：

GRANT SELECT, INSERT(name) ON Studio TO sisko;
GRANT SELECT ON Movies TO sisko;

授权图

  用授权图来表示授权，是非常有意义的，如图1所示：

图1

  SQL系统维护这个图的表示，并跟踪权限和它们的起始点

权限收回

  权限的收回可能要求级联：在当收回已经被传递给其他用户的带授权选项的权限时，可能也要收回那些被此授权选项授予的权限

  收权语句：

REVOKE <权限列表> ON <数据库元素> FROM <用户列表>

  并以CASCADE或RESTRICT结束，前者表示级联收权，而后者则表示如果会对其他人造成级联影响的话，则不执行该收权语句

SQL中的递归

在SQL中定义递归关系

  SQL中的WITH语句允许定义递归或非递归的临时关系

  为了定义一个递归关系，可以在WITH语句本身使用该关系，WITH语句的一个简单形式：

WITH R AS <R的定义> <包含R的查询>

  先定义一个临时关系名R，再在某些查询中使用R，临时关系只能在WITH语句的查询中有效

  举例：

WITH RECURSIVE Reaches(frm, to) AS
		(SELECT frm, to FROM Flights)
	UNION
		(SELECT Rl.frm, R2.to
		FROM Reaches Rl, Reaches R2
		WHERE Rl.to = R2.frm)
SELECT * FROM Reaches;

有问题的递归SQL表达式

  作为一个合法的SQL递归，递归关系R的定义仅仅只涉及相互递归的关系S的“单调”运用：如果向S中添加一个任意的元组，将可能添加一个或多个R的元组，或者可能使R没有变化，但是它绝不能引起任何元组从中删除

联机分析处理

  数据库的一个重要应用是样本或趋势数据检查，此行为被称作联机分析处理OLAP

OLAP和数据仓库

  数据仓库：发生OLAP应用的主数据库的独立副本

OLAP数据的多维视图

  事实表：中心关系或数据集合，表示感兴趣的事件或对象

  数据空间被非正式地称为数据立方体，更精确地，被称为原始数据立方体

星型模式

  星型模式由事实表的模式组成，这个事实表链接到其他几个被称为“维表”的关系，事实表在星型的中心，星上的点是维表，如图2所示：

图2

切片和切块

  可以认为原始数据立方体上的点是在一些粒度级别上的基于每个维上的分割。例如：在时间维上，可以按年月日分割（或不分割）

  这种分割会对每个维将立方体切成小方块，结果是立方体被分为更小的立方体，这些小的立方体代表利用GROUP BY子句执行这样分割的点的统计汇总