数据库系统原理与实践 笔记 #5

数据库系统原理与实践 笔记 #5

高级SQL(续)

报表查询（高级聚集查询）

报表汇总查询

• group by子句中的rollup与cube

group by 分组列表达式1[,...分组列表达式n]
[with {cube|rollup}]

• 如果是group by rollup(a, b, c), 首先会对(a, b, c)进行group by，然后对(a, b)进行group by，然后是(a)进行group by，最后对全表进行group by操作
• 如果是group by cube(a, b, c)，首先会对(a, b, c)进行group by，然后是(a,b),(a,c),(b,c),(a),(b),©，最后对全表进行group by

游标和触发器

游标

• 游标从本质上是系统为用户开设的一个数据缓冲区，用于存放T-SQL语句从数据库检索出来的结果集
• 当应用程序需要对结果集进行处理时，使用游标可以从结果集中一条条地提取记录，为此每个游标必须有一个名字

使用游标

• 利用基于便令的select into语句，仅能处理一条记录的数据，通过游标可以对查询的结果集进行循环处理。
• 基本步骤：

-- 声明游标
declare cursor_name cursor for select_statement;

-- 打开游标
open cursor_name;

-- 使用游标
fetch cursor_nam into var_name[, var_name]...;

--关闭游标
close cursor_name;

• 说明：游标必须在声明处理程序之前被声明，并且在声明变量和条件之后；select语句中不能有into子句

• 游标允许应用程序读查询语句select返回的行结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作

• MySQL游标为只读，不可滚动和敏感：

  • 只读：无法通过游标更新基础表中的数据
  • 不可滚动：只能按照select语句确定的顺序获取行，不能以相反的顺序获取行；此外，不能跳过行或跳转到结果集中的特定行
  • 敏感：有两种游标：敏感游标和不敏感游标；敏感游标指向实际数据，而不敏感游标使用数据的临时副本。敏感游标比一个不敏感的游标执行得更快，因为他不需要临时拷贝数据。但是，对其他连接的数据所做的任何更改都将影响由敏感游标使用的数据。因此，如果不更新敏感游标所使用的数据，则更安全。((MySQL游标是敏感的))。

触发器

• 触发器定义了一系列操作，这一系列操作称为触发程序，当触发时间发生时，触发程序会自动运行。
• 触发器主要用于监视某个表的插入、更新和删除等更新操作，这些操作可以分别激活该表的insert、update和delete类型的触发程序运行，从而实现数据的自动维护。

触发器的功能

• 安全性：可以基于数据库的值是用户具有操作数据库的某种权利。可以基于时间限制用户的操作，例如：不允许下班后和节假日修改数据库数据等。可以基于数据库中的数据限制用户的操作，例如：不允许学生的分数大于满分等。
• 审计：可以跟踪用户对数据库的操作，审计用户操作数据库的语句，把用户对数据库更新写入审计表。
• 实现复杂的数据完整性规则：实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同，触发器可以一弄列或者数据库对象。例如，触发器可回退任何企图吃进超过自己保证金的期货。
• 实现复杂的非标准的数据库相关完整性规则：在修改或者删除时，级联修改或者删除企图表中与之匹配的行。在修改或者是删除时把其他表中与之匹配的行设成null值。在修改或者删除时把其他的表中与之匹配的行级联设成默认值。触发器能够拒绝或者回退破坏相关完整性的变化，取消试图进行数据更新的事务。当插入一个与其主键不匹配的外键时，这种触发器起作用。

创建并使用触发器

创建触发器

• 触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象在MySQL中，可以使用create trigger语句创建触发器，具体语法格式为：

create trigger trigger_name trigger_time trigger_event
on tbl_name for each row trigger_stmt;

• trigger_name: 触发器的名称，如果要在某个特定数据库中创建，名称前面应该加上数据库的名称
• trigger_time：是触发器被触发的时间，它可以是before或者after，以知名触发器是在激活它的语句之前或之后触发。如果希望验证新数据是否满足使用的限制，可以使用before；如果希望在激活触发器的语句执行之后完成几个或更多的改变，可以用after。
• trigger_event：指明了激活触发器的语句的类型
• tbl_name：与触发器相关联的表名，必须是永久性表，不能将处罚程序与temporary表或视图滚连起来。在表上出发时间发生时才会激活触发器，同一个表不能拥有两个具有相同触发时刻和事件的触发器。
• for each row：用来指定对于受才触发事件影响的每一行都要激活触发器的动作
• trigger_stmt：当触发程序激活时执行的语句，如果你打算执行多个语句，可使用begin…end符合语句结构。

使用触发器

• 创建并使用触发器实现检查约束，保证课程的人数上限up_limit字段值在(60,150,230)范围内：

delimiter $$
create trigger course_insert before_trigger before insert
on course for each row
begin
if (new.up_limit = 60 || new.up_limit = 150 || new.up_limit = 230) then
set new.up_limit=new.up_limit;
else insert into mytable values(0);
end if;
end;
$$
delimiter;

• 使用old(旧值)和new(新值)关键字，能够访问受触发程序影响的行中的字段值
• 在insert触发程序中，仅能使用new.col_name，没有旧值
• 在delete触发程序中，仅能使用old.col_name，没有新值
• 在update触发程序中，可以使用old.col_nae来引用更新前的旧值，也能使用new.col_name来引用更新后的行中的新值
• old列是只读的，可以引用它，但不能更改它
• 在前例之上，创建course_update_before_trigger触发器，负责进行修改检查：

delimiter $$
create trigger course_update_before_trigger before update
on course for each row
begin
if (new.up_limit != 60 || new.up_limit != 150 || new.up_limit != 230) then
set new.up_limit=old.up_limit;
end if;
end;
$$
delimiter; 

查看触发器

• 使用show triggers;命令查看触发器的定义，使用"show trigger \G"命令可以查看当前数据库中所有触发器的信息，还可以使用"show trigger like 模式\G"命令查看与模式模糊匹配的触发器信息。
• 通过查询information_schema数据库中的triggers表，可以查看触发器的定义：

select * from information_schema.triggers \G;

• "show create trigger"命令可以查看某一个触发器的定义
• 成功创建触发器之后，MySQL会自动在数据库目标下创建TRN以及TRG触发器文件，可以用记事本打开文件查看定义

删除触发器

• 与其他数据库对象一样，可以使用drop语句将触发器从数据库中删除：

drop trigger [schema_name.]trigger_name;

• schema_name：可选项，用于指定触发器所在的数据库的名称，如果没有指定，则为当前默认数据库
• trigger_name：要删除的触发器名称
• drop trigger语句需要super权限
• 当删除一个表的同时，也会自动删除表上的触发器。另外，触发器不能更新或者覆盖，为了修改一个触发器，必须先删除它，然后再重新创建。

关系代数

关系代数

• 所有的过程化查询语言都提供了一组关系运算，这些运算施加于单个关系上，或者一对关系上，运算结果总是单个关系
• 总共有6个基本关系运算：选择：$\sigma$、投影：$\prod$、集合并：$\cup$、集合差：-、笛卡尔积：$\times$、更名：$\rho$

选择运算

• 作用：选择满足给定谓词条件的元组
• 符号表示形式：${\sigma }_p(r)$，p是选择谓词
• 形式化定义： σ p ( r ) = { t ∣ t ∈ r ∧ p ( t ) } \sigma_p(r)=\{t|t\in r\land p(t)\} σp​(r)={t∣t∈r∧p(t)}

投影运算

• 作用：过滤掉特定的属性
• 符号表示形式：${\prod }_{A_1,A_2,...,A_m}(r)$，其中$A_1,A_2,...,A_m$是属性名，r是关系名
• 操作结果是通过去除未列出的列，获得的一个m列的关系
• 形式化定义：

笛卡尔积运算

• 作用：结合来自任意两个关系的信息
• 符号表示：r$\times$s
• 形式化定义： r × s = { < t , q > ∣ t ∈ r ∧ q ∈ s } r\times s = \{<t,q>|t\in r \land q\in s\} r×s={<t,q>∣t∈r∧q∈s}

连接运算

• 作用：可以将选择运算和笛卡尔积运算合并为一个运算操作，连接两个表的信息
• 符号表示： $r\bowtie s={\sigma }_{\theta }(r\times s)$，$\theta$是$R\cup S$模式属性上的一个选择谓词

集合并运算

• 符号表示：$r\cup s$
• 形式化定义： r ∪ s = { t ∣ t ∈ r ∨ t ∈ s } r\cup s = \{t|t\in r\lor t\in s\} r∪s={t∣t∈r∨t∈s}
• 要使$r\cup s$有意义，我们要求以下两个条件必须同时成立：
  • 关系r和s必须同元，即它们的属性数目必须相同
  • 属性域必须相同

集合差运算

• 符号表示：$r-s$
• 形式化定义： r − s = { t ∣ t ∈ r ∧ t ∉ s } r-s = \{t|t\in r\land t\notin s\} r−s={t∣t∈r∧t∈/s}
• 要使$r\cup s$有意义，我们要求以下两个条件必须同时成立：
  • 关系r和s必须同元，即它们的属性数目必须相同
  • r和s的属性域必须相同

更名运算

• 通过更名运算，可以用一个新的名称来指代一个关系：例如${\rho }_x(E)$，返回表达式E的结果，并把名字x赋给它
• 假设关系代数表达式E是多元的，则表达式：
  $${\rho }_{x(A_1,A_2,...,A_n)}(E)$$
• 返回表达式E的结果，并赋给它名字x，同时将各属性命名为$A_1,A_2,...,A_n$

基本关系代数总结

• 关系代数中基本的表达式是如下二者之一：
  • 数据库中的一个关系
  • 一个常数关系

集合交运算

• 符号表示：$r\cap s$
• 形式化定义： r ∩ s = { t ∣ t ∈ r ∧ t ∈ s } r\cap s =\{t|t\in r\land t\in s\} r∩s={t∣t∈r∧t∈s}
• 要使$r\cup s$有意义，我们要求以下两个条件必须同时成立：
  • 关系r和s必须同元，即它们的属性数目必须相同
  • 属性域必须相同

自然连接运算

• 已知关系$r(R)$和$s(S)$，那么，$r\bowtie s$是在$R\cup S$模式下获得的关系，具体如下：
  • 依次比较关系r的元组$t_r$和关系s的元组$t_s$
  • 如果在$R\cap S$模式下，元组$t_r$和$t_s$

赋值运算

• 赋值操作($\leftarrow$)可以使复杂查询的表达变得简单，即将查询表达为一个顺序程序，包括：一系列的复制，一个值被作为查询结果显示的表达式

删除

• 只可以删除整个元组，而无法删除特定的属性值
• 关系代数表达式：$r\leftarrow r-E$，其中r是关系，E是一个关系代数查询