数据库-第八/九章 数据库编程和优化【期末复习|考研复习】

前言

总结整理不易，希望大家点赞收藏。

给大家整理了一下数据库系统概论中的重点概念，以供大家期末复习和考研复习的时候使用。
参考资料是王珊老师和萨师煊老师的数据库系统概论(第五版)。

---

数据库系统概论系列文章传送门：
第一章 绪论
第二/三章 关系数据库和标准语言SQL
第四/五章 数据库安全性和完整性
第六/七章 关系数据理论和数据库设计
第八/九章 数据库编程和优化
第十章 数据库恢复技术
第十一章 并发控制
数据库系统概论-练手题集合

---

---

第八章 数据库编程

8.1 嵌入式SQL

作为非过程化的查询语言SQL：操作统一、面向集合、功能丰富、使用简单。
但是：由于这种特性也导致了他难以实现应用业务中的逻辑控制。为了克服这个弱点就有了SQL编程。
嵌入式SQL：将SQL嵌入到程序设计语句中，数据库管理系统一般采用预编译的方法处理。就是识别出嵌入式语言，然后转成主语言。区分SQL语句和主语言语句：加前缀（exec sql, #sql）

8.2 嵌入式sql语句与主语言之间的通信

（1） 嵌入式SQL——>主语言执行状态信息，SQL通信区
（2） 主语言——>sql提供参数，主变量
（3） SQL语句——>主语言查询结果，主变量和游标
整个流程：（C语言）
EXEC SQL BEGIN DECLARE SECTION；//主变量声明开始
EXEC SQL END DECALRE SECTION;//主变量声明结束
Long SQLCODE;
EXEC SQL INCLUDE SQLCODE;//定义通信区
//主程序开始
EXEC SQL CONNECT TO … //连接数据库
EXEC SQL DECLARE SX CURSOR FOR+后面接一个SQL语句 //定义游标
EXEC SQL OPEN SX； //打开游标
循环从游标中取值
EXEC SQL FETCH SX INTO +主变量
这个时候用SQLCA.SQLCODE判断游标中是否还有没取出的值。Sqlcode!=0则退出循环
EXEC SQL CLOSE SX; //关闭游标
EXEC SQL COMMIT WORK; //提交更新
EXEC SQL DISCOUNNECT … //断开数据库连接

8.3 游标

游标：是用来存放SQL语句执行结果的缓冲区。必须使用游标的情况：多条记录的select语句，current形式的update和delete语句。
（1） 说明游标 EXEC SQL DECLARE C1 (游标名) CURSOR FOR +select 语句
（2） 打开游标EXEC SQL OPEN C1‘
（3） 推进游标指针取当前记录 EXEC SQL FETCH C1 INTO 主变量1，主变量2…(主变量和指示变量前加冒号：)
（4） 关闭游标 EXEC SQL CLOSE C1

8.4 动态SQL

主变量里用一个字符串寸SQL语句，可以将某些参数设为？
之后准备，再执行
EXEC SQL BEGIN DECLARE SECTION
Const char *stmt=”insert into student(sno) values(?);” //主变量定义
EXEC SQL END DECLARE SECTION
EXEC SQL PREPARE mystmt FROM :stmt; //准备语句prepare，给这个语句起个名字叫 mystmt
EXEC SQL EXECUTE mystmt USING 100; //执行语句execute

8.5 过程化SQL

基本结构是块，所有的过程化SQL程序都是由块组成的。
先DECLARE 执行时用BEGIN +SQL语句/流程控制语句 EXCEPTION 异常处理部分 END;
流程控制语句：IF； IF-THEN; (条件) LOOP, WHILE-LOOP, FOR-LOOP（循环） 赋值语句 变量名：=表达式

8.6 过程化SQL块：命名块，匿名块。

匿名块：每次执行时都要进行编译，不能被存储到数据库里。也不能在其他块里调用。
命名块：过程和函数是命名块，被编译后保存在数据库中，称为持久性存储模块，可以反复调用。

8.7 存储过程：

过程化SQL经编译和优化存储在数据库服务器中，不用编译，调用就可以了
（1） 创建存储过程 Create or replace procedure 过程名(参数1，2，3) AS 过程化SQL块
（2） 执行存储过程 Call /perform procedure 过程名（参数1，2，3）
（3） 修改存储过程 Alter procedure 过程名1 rename to 过程名2 //改名
Alter procedure 过程名 compile //重新编译
（4） 删除 Drop procedure 过程名； 函数和存储过程差不多，不同之处是函数必须指定返回类型

第九章 关系查询处理和查询优化

9.1 查询处理

查询处理：关系数据库管理系统执行查询语句的过程。把用户提交给关系数据库管理系统的查询语句转换为高效的查询执行计划。
分为四个阶段：
1.查询分析：扫描，词法分析，语法分析。
2.查询检查：语义检查。
3.查询优化：代数优化、物理优化。
4. 查询执行
选择操作：全表扫描，索引扫描
连接操作：嵌套循环（暴力算法），排序——合并算法，索引连接算法，hash-join算法

9.2 查询优化

查询优化：提高查询效率，使查询代价更小
物理优化：对存取路径和底层操作算法的优化（基于规则，基于代价估算，两者结合）
代数优化：对代数表达式进行优化（等价变换规则、启发式规则，优化查询树）

9.3 SQL 优化

插入数据：
大量插入采用load指令
主键优化：
表数据都是按照逐渐顺序组织存放的，采用页分裂和页合并来优化主键的索引组织表。、
order by优化：
建立合适的索引，多字段排序遵循最左前缀法则。尽量使用覆盖索引，select*容易出现回表查询，降低效率。在出现大数据量排序时，适当增加缓冲区大小。（覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。）
group by优化：
建立合适的索引，遵循最左前缀法则。
count优化：
1）count(主键)：InnoDB引擎会遍历整张表，把每行的主键id值都取出来，返回给服务层，服务层拿到主键后，直接按行进行累加（主键不可能为空）。
2）count(字段)：没有not null约束的话，InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加；有not null约束的话，InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
3）count(1)：InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一层，放一个数字 1 进去，直接按行进行累加。
4）count(*)：InnoDB 引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。按效率排序：count(字段) < count(主键) < count(1) < count()，所以尽量使用 count()
update优化：InnoDB 的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。对于有主键索引的只会锁该行，没有索引的会把整张表加锁。

练手题

这里的练手题与第十一章并发控制一起做。

下一章传送门

第十章 数据库恢复技术