第9章 服务器环境下的SQL

数据库原理复习专栏

• 本章介绍在服务器环境下的$SQL$工作，包括其工作架构(说白了就是这个装数据的仓库是如何严格地控制数据的读取、写入)及如何与外界交互数据(具体地，如在$C\textrm{++}$中如何访问数据库)。

• 玛雅，这章太繁杂了，写不动了。如需掌握，写一发代码理解会深刻许多。

三层体系结构

• 考虑设计维护数据库的初衷，是因数据需要保存而设立，不仅仅是为了想图书馆保存图书那样方便整理和查找，也是处于对数据安全性的考虑。典型的数据库应用场景均发生一个相似的情形下：

  • 用户(对数据而言，其“用户”包括正在购买商品的买家、正在选课的学生、正在办公网添加一个课程讨论区的老师等等)需要获得诸如“查询价格低于5元的手机贴膜”这样的信息，但这些用户本身并不适合自己不断地编写$~SQL\;select~$语句来获得这些信息。
  • 数据库系统$(DBMS)$作为一个仓库一般的存在，需要自己打理自己的内务，如“应该把这把数据扔进垃圾桶还是直接塞进下水道”。
  • 上述二者之间直接沟通并不方便，没有人愿意在网购时不断地编写$select ...$来查商品信息，也没有多少用户能够绞尽脑汁为了更快地查到想要的火车票而丧心病狂地优化自己的查询语句。所以自然的办法就是引入一个“中间层”。

  就这样，很自然地就有了三层体系结构$\to$

• 三层体系结构，也称三阶体系结构。

  • $a)~$$web~$服务器。进程管理与用户的交互。
    
    • 它实际上是上述的“用户”层，因为真正意义上的用户是直接操控$~web~$的。
    • 严格意义上和用户交互的是$~web~$前端。
  • $b)~$应用服务器。将数据作为响应从数据库发回给从$~web~$服务器获得的请求。运行“交易逻辑”。
    
    • 就是上述的“中间件”。把来自用户的如“查询$\,95\,$年出生的妹子姓名”转换成类似SELECT name FROM students WHERE YEAR(birthDate) = 1995 AND gender = 'M';的语句，并负责向数据库提交，然后把结果或直接或处理后返回给$~web~$端，最后$~web~$便可以展示给用户看了。
  • $c)~$数据库服务器。运行$~DBMS$。

• 下述部分待修正

$SQL~$环境

• $SQL~$环境的标准结构

  • 模式$(schema)$：表(关系)、视图、断言、触发器等等的集合。
    
    • 进入$~MySQL~$的命令行后，执行use employees;，employees中包含的所有表、视图、触发器等就是一个模式。
  • 目录$(catelog)$：模式的集合。
    
    • 进入$~MySQL~$命令行后，执行show databases;列出的每一项都可以认为是一个模式( 注意其中的$information\_schema$等模式)，所有这些模式加起来，就是目录。
  • 簇$(cluster)$：目录的集合。

• 连接

  • 建立一个连接：CONNECT TO <服务器名> AS <连接名> AUTHORIZATION <名字和密码>
  • 切换连接：SET CONNECTION conn1
  • 断开连接：DISCONNECT conn1
    
    • 在本地使用MySQL时，类似mysql -u root -p这样的命令会自动建立相应的连接。另外，如果在本地环境下，可以在MySQL中使用如connect employees;来使用一个新连接连接到指定的schema(database)，可以看到关于连接$~id~$的提示。
• 会话：连接有效时，执行的$SQL$操作形成一个会话。
  
  • 会话和连接共生死。
  • 每个连接需要指定：连接的服务器名，(连接名可省略)以及连接的模式名，用户名和密码。每个会话是在有效连接后的具体的$SQL$操作，因此相应地包含当前操作的目录以及其下的模式，另外还有一个授权用户。
• 模块$(module)$
  
  • 普通$SQL$界面：用户可直接键入$SQL$语句的界面
    
    • 做上机作业时在$shell(linux下的如bash,windows下的如cmd)$内的操作用的就是普通$SQL$界面模块。
  • 嵌套$SQL$：预处理器将嵌套的$SQL$语句转变为$SQL$系统可接受的对应函数或过程调用。
    
    • 第二次上机中的$jdbc$实验就用这个。如$select$查询时在$Java$程序中并不直接写$select...$而一般是$select ? ... ?$，有一堆问号，然后通过$mysql\textrm{-}connector$的如$setString()$函数来设置，这些语句在编译时才被转换成最后的类似$select\ name\dots$的语句，注意，原来语句中的问号此时就被替换掉了。
  • 真模块：模块的最一般形式，即一个含有存储函数或过程集合的模块。

$SQL~$宿主语言接口

• 根据之前的学习，窝们已经可以完全编写语句来访问数据库了。为啥需要“宿主语言”？因为正常的生产环境(啥叫生产环境？比如在$web$开发时，前期开发时我们叫“测试环境”，或者叫“调试环境”($debug$)，正式投入使用对外开放后就叫“生产环境”($production$)。整个过程一般会设置一个全局的变量来控制对如出错时的输出的控制。在调试环境下，某个账户登录失败可以把$Authorization$打印出来方便调试，但在生产环境中则一般不会也一定不应该这么做。)下，对数据库的访问都是通过一个应用程序来完成的，那么编写这个应用程序的语言就是宿主语言(如$Java,C\textrm{++}$等)
• 根据上面的嵌套$SQL$模块，处理该嵌套有两种方式：
  
  • 下述样例待更正。
  • 调用层接口。提供一个函数库，由函数库向宿主语言中嵌入的$SQL$相关代码。如在$Java$中的代码：

String sql = "INSERT INTO departments_copy VALUES (?, ?)";
PreparedStatement stat = DBConnection.getConnection().prepareStatement(sql);
/* complete stat here ... */
/* then execute the statement here ... */

就使用了函数库中的如$PreparedStatement$类。

• 直接嵌套$SQL$

  • 就是直接由预处理器先将嵌入宿主语言中的$SQL$相关语句预处理成正常的有逻辑的宿主语言语句，然后统一编译运行或解释。当然，只有$C\textrm{++}$这样有预处理器的语言才能享受到这种处理方法。

• 阻抗不匹配问题：$SQL$的数据模式和其它预言的模式差别很大。

  • 比如，执行$select$后$SQL$会返回一个表，一个表中的各列可能会有不同的奇怪的数据类型，如$date$，$decimal$，但在如$C$或者$Java$中，不同的数据类型的数据是没法保存在同一个数组中的($C$中可以使用$void\textrm{*}$指针得到一个替代方案，但显然无法兼具灵活性和方便性)。
• $SQL$和宿主语言的连接
  
  • EXEC SQL提示预处理器$SQL$语句的嵌入。类似于$OpenGL$的glBegin()和glEnd()。
  • 后面可以看到，整个编程过程像极了$Assembly$...
• $DECLARE$节
  
  • 就像汇编的数据段。。。

EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL END DECLARE SECTION;

• 共享变量

void getStudio() {
    EXEC SQL BEGIN DECLARE SECTION;
        char studioName[50];
        char studioAddr[256];
        char SQLSTATE[6]; /* SQL状态结果 */
    EXEC SQL END DECLARE SECTION;
    EXEC SQL INSERT INTO Studio(name, address) VALUES (:studioName, :studioAddr);

上述一堆大写在预处理时会被替换成真正有效的语句。

习题9.4.1

• $a)$ $cert\#$到底是啥？！

create procedure solveA
(in name varchar(64))
begin
    select netWorth from MovieExec
    where cert in (select pres from Studio);
end

• $b)$

create function solveB
(name varchar(64), address varchar(255)) returns int
declare isStar boolean;
declare isProducer boolean;
begin
    if ((name, address) in (select name, address from MovieStar)) then
        isStar = true;
    else
        isStar = false;
    end if
    if ((name, address) in (select name, address from MovieExec)) then
        isProducer = true;
    else
        isProducer = false;
    end if
    if (isStar and not isProducer) then
        return 1;
    elseif (not isStar and isProducer) then
        return 2;
    elseif (isStar and isProducer) then
        return 3;
    else
        return 4;
    end if
end

• $c)$

create procedure solveC
(in sName varchar(64), out longest varchar(64), out second_longest varchar(64))
declare notFound condition for sqlstate '02000';
declare c cursor for select name from Movies where studioName = sName order by length desc;
begin
    fetch c into longest;
    if notFound then
        set longest = NULL;
        set second_longest = NULL;
    else
        fetch c into second_longest;
        if notFound then
            set second_longest = NULL;
        end if
    end if
end