文章目录
数据库系统原理与实践 笔记 #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 ρ
选择运算
- 作用:选择满足给定谓词条件的元组
- 符号表示形式: σ p ( r ) \sigma_p(r) σ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)}
投影运算
- 作用:过滤掉特定的属性
- 符号表示形式: ∏ A 1 , A 2 , . . . , A m ( r ) \prod_{A_1,A_2,...,A_m}(r) ∏A1,A2,...,Am(r),其中 A 1 , A 2 , . . . , A m A_1, A_2, ..., A_m A1,A2,...,Am是属性名,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 ⋈ s = σ θ ( r × s ) r \bowtie s = \sigma_\theta(r\times s) r⋈s=σθ(r×s), θ \theta θ是 R ∪ S R\cup S R∪S模式属性上的一个选择谓词
集合并运算
- 符号表示: r ∪ s r\cup s r∪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
∪
s
r\cup s
r∪s有意义,我们要求以下两个条件必须同时成立:
- 关系r和s必须同元,即它们的属性数目必须相同
- 属性域必须相同
集合差运算
- 符号表示: r − 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
∪
s
r\cup s
r∪s有意义,我们要求以下两个条件必须同时成立:
- 关系r和s必须同元,即它们的属性数目必须相同
- r和s的属性域必须相同
更名运算
- 通过更名运算,可以用一个新的名称来指代一个关系:例如 ρ x ( E ) \rho_x(E) ρx(E),返回表达式E的结果,并把名字x赋给它
- 假设关系代数表达式E是多元的,则表达式:
ρ x ( A 1 , A 2 , . . . , A n ) ( E ) \rho_{x(A_1,A_2,...,A_n)}(E) ρx(A1,A2,...,An)(E) - 返回表达式E的结果,并赋给它名字x,同时将各属性命名为 A 1 , A 2 , . . . , A n A_1,A_2,...,A_n A1,A2,...,An
基本关系代数总结
- 关系代数中基本的表达式是如下二者之一:
- 数据库中的一个关系
- 一个常数关系
集合交运算
- 符号表示: r ∩ s r\cap s r∩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
∪
s
r\cup s
r∪s有意义,我们要求以下两个条件必须同时成立:
- 关系r和s必须同元,即它们的属性数目必须相同
- 属性域必须相同
自然连接运算
- 已知关系
r
(
R
)
r(R)
r(R)和
s
(
S
)
s(S)
s(S),那么,
r
⋈
s
r\bowtie s
r⋈s是在
R
∪
S
R\cup S
R∪S模式下获得的关系,具体如下:
- 依次比较关系r的元组 t r t_r tr和关系s的元组 t s t_s ts
- 如果在 R ∩ S R\cap S R∩S模式下,元组 t r t_r tr和 t s t_s ts
赋值运算
- 赋值操作( ← \leftarrow ←)可以使复杂查询的表达变得简单,即将查询表达为一个顺序程序,包括:一系列的复制,一个值被作为查询结果显示的表达式
删除
- 只可以删除整个元组,而无法删除特定的属性值
- 关系代数表达式: r ← r − E r\leftarrow r-E r←r−E,其中r是关系,E是一个关系代数查询