[大数据]数据库复习：SQL语句整理

SQL语句复习

1.Data Definition Construct

1.1 Create table Construct

1.1.1 建表语句

create table instructor
( ID char(5),
 name varchar(20),
 dept_name varchar(20),
 salary numeric(8,2)
);

-----------------------------------
create table r 
( A1 D1,
  A2 D2,
  ...
  An,Dn,
  (integrity-constraint_k)
);

1.1.2 integrity constraint

（1）not null

把空值排除在属性的域之外。

（2）primary key（A1,…,An）

属性列表中的属性构成主码，主码包含了两方面的信息：

• 非空
• 没有两个元组的主码相同

（3）foreign key（Am,…,An） references r

表明关系中的任意元组在属性$A_m,...,A_n$上的取值必须对应关系r中的某元组在主码上的取值

（4）Unique

任意两行不可以相同。

（5）Default

给与默认取值

（6）Check

create table department
( dept_name varchar(20),
  building varchar(15),
  budget decimal(12,2),
  primary key(dept_name)
);

create table instructor
( ID char(5),
  name varchar(20) not null,
  dept_name varchar(20),
  salary numeric(8,2),
  gender varchar(6),
  phone int(11) unique,
  age int(3) default 30,
  primary key(ID),
  foreign key(dept_name) references department,  // 标准sql写法，mysql的department后需要列出属性名
  check (gender in ("male", "female"))
);

1.2 Update to Table

1.2.1 数据新增 insert

insert into department values('Biology', "海棠", 8000);

1.2.2 数据删除 delete

delete from instructor where ID = "00001";
delete from instructor;

1.2.3 删除表 drop

drop table instructor;

1.2.4 更改 Alter

（1）新增完整性约束

alter table r add constraint e_name Foreign key(employee_name) references employee(employee);

（2）新增属性

alter table r add age int(3);

（3）删除属性

alter table r drop A;

2. 基本查询结构

2.1 单关系查询

2.1.1 select

select distinct dept_name from instructor; // 强制去重
select all dept_name from instructor;	// 强制不去重
select dept_name from instructor; 	// 默认不去重

select ID,name,dept_name, sal* 12 as 'year_sal' from instructor;  // as 用法
select 'A' from instructor; 	// 返回instructor的行数的A

2.1.2 where

select name from instructor where salary between 90000 and 100000;  // between
select name, course_Id
from instructor, teaches
where (instructor.ID, dept_name) = (teaches.ID, 'Biology');

2.2 多关系查询

（1）自连接

select S.supervisor from emp_super E, emp_super S where E.person = "Bob" and E.supervisor = S.person;

2.3 字符串操作

%：匹配任意个字符

_：一个字符

select name from instructor where name like '%dar%';
select name from instructor where name = '100\%'; //转义字符

select distinct name from instructor order by name desc;	// 降序排列，默认升序

2.4 集合运算

自动去重：

找出在2009年秋季或者是2010年春季开课的课程：

(select course_id from section where year = 2009 and semester = "Fall") 
union 
(select course_id from section where year = 2010 and semester = "Spring");

找出那些既在2009年秋季开课又在2010年春季开课的课程

(select course_id from section where year = 2009 and semester = "Fall") 
intersect
(select course_id from section where year = 2010 and semester = "Spring");

找出在2009年秋季开课但不在2010年春季开课的课程

(select course_id from section where year = 2009 and semester = "Fall") 
except
(select course_id from section where year = 2010 and semester = "Spring");

在上面的筛选中，不会出现重复的课程号，因为intersect，union和except三个操作自动去重。假如存在某一个课程在2009年秋季的三个时段均有开课，那么也只会出现一次。若想保留重复，必须加上(操作名) all，比如union all的all关键字。

• 找出所有教职工工资中比最大工资小的值

  select distinct salary from instructor I1, instructor I2 
  where I1.salary < I2.salary;
  

• 找出所有的可能薪资

  select distinct salary from instructor;
  

• 找出教职工的最大薪资

  (select distinct salary from instructor)
  except 
  (select distinct salary from instructor I1, instructor I2
  where I1.salary < I2.salary);
  

2.5 空值

select name 
from instructor 
where salary is null;

2.6 聚集操作

2.6.1 基本聚集函数

• min
• max
• avg
• count
• sum

求计算机部门的平均薪资

select avg(salary) from instructor where dept_name = 'Comp.Sci';

// 重命名
select avg(salary) as avg_sal from instructor where dept_name = 'Comp.Sci';

求在2010年春季学期教授一门课程的老师的数量

select count(distinct(ID)) from teaches where year = 2010 and semester = "Spring";
// 注意去重

求表格有多少行

select count(*) from instructor;

2.6.2 分组聚集

查找每个系的平均工资

select dept_name, avg(salary) as avg_sal from instructor group by dept_name;

2.6.3 having子句

• 不能使用where子句的情况

select dept_name, avg(salary) as avg_sal 
from instructor group by dept_name 
having avg(salary) > 42000;

2.6.4 对空值和布尔值的聚集

（1）空值

• sum求值时，null值被忽略

• count求值是，null不会忽略

（2）布尔值

3. 嵌套子查询

3.1 集合成员资格

（1）in和not in

找出在2009年秋季和2010年春季同时开课的所有课程

• 法1：intersect
• 法2：嵌套子查询

select course_id from section
where year = 2009 and semester = "Fall" and
course_id in (select course_id from section
             where year = 2010 and semester = "Spring");

找出在2009年秋季开课但不在2010年春季开课的所有课程

• 法1：except
• 法2：嵌套子查询

select course_id from section 
where year = 2009 and semester = "Fall" and
course_id not in (select course_id from section
                  where year = 2010 and semester = "Spring");

枚举集合的用法：

select distinct name from instructor where name not in ('Mozart', 'Einstein');

找出学生总数：他们选修了ID为10101的教师所讲授的课程段

select count(distinct ID) from takes
where (course_id, sec_id, semester, year) in (select course_id, sec_id, semester, year
                                              from teaches
                                              where teaches.ID = 10101);

select count(distinct T1.ID) from takes T1 
natural left outer join teaches T2
on T1.course_id = T2.course_id and T1.sec_id = T2.sec_id and T1.semester = T2.semester and T1.year = T2.year
where T2.ID = 10101;

（2）some

例：找出满足下面条件的所有教师的姓名，他们的工资至少比Biology系某一个教师的工资要高。

select name from instructor I1
where salary > some(select salary from instructor I2
                   where dept_name = "Biology");
                   
// 不用some的写法
select distinct T1.name from instructor I1, instructor I2
where I1.salary > I2.salary and I2.dept_name = "Biology";

• some的一些等价表达
  • = some < ==== > in
  • <> some <==\==> not in

（3）all

all的等价表示

• =all ===>in
• ≠all <====> not in

找出有最高平均余额的支行

// --------- all 关键字 -------------
select branch_name 
from account
group by branch_name
having avg(amount) >= all(select avg(amount) as avg_amount
                         from branch_name from account
                         group by branch_name);
                         
// --------- 聚集函数 -----------  无法显示支行名
select max(avg_amount)		
from (select branch_name, avg(amount) as avg_amount
     from account
     group by branch_name);

（4）空关系测试

• exists结构

exists结构测试子查询中是否存在元组。

exists结构在作为子查询，非空时返回true，为空时返回false

not exists与之相反

找出在2009年秋季和2010年春季同时开课的所有课程：

select course_id
from section S1
where year = 2009 and semester = "Fall" and 
exists(select course_id 
       from section S2
       where S2.semester = "Spring" and S2.year = 2010 and 
       S2.course_id = S1.course_id);

注意in结构和exists结构的区别：

• in是对course_id是否在查找结果中的判断
• exists结构是对子查询结果是否为空的判断

上述查询的说明：

• 外层查询的相关名称S可以用在where子句的查询中，这里使用了外层相关名称的子查询称为相关子查询。
• 子查询中使用的别名，不能在外层查询中使用。
• 子查询中使用的别名不能在别的子查询中使用

not exists结构

找出在2009 年秋季开课但是不在2010年春季开课的所有课程：

select course_id
from section S1
where semester = "Fall" and year = 2009 and 
not exists(select course_id from section S2 where 
           S2.semester = "Spring" and S2.year = 2010 and
           S1.course_id = S2.course_id);

找出所有选修了Biology系开设的所有课程的学生。

select distinct ID 
from takes T1
where not exists(select course_id 
                 from course 
                 where dept_name = 'Biology'
                 except
                 select course_id 
                 from takes T2 
                 where T2.ID = T1.ID);

（5）重复元组存在性测试

SQL提供了一个布尔函数，测试子查询的结果是否存在重复元组——unique结构

• 查询结果中没有重复元组返回true
• 有重复元组返回false

找到所有在2009年最多开设一次的课程：

select S1.course_id
from section S1
where unique(select S2.course_id
             from section S2
             where S1.course_id = S2.course_id and 
             year = 2009);

unique在mysql中未实现，unique在mysql中的等价写法：

select S1.course_id
from course S1
where 1 >= (select count(S2.course_id)
             from section S2
             where S1.course_id = S2.course_id and 
             year = 2009);

找出所有在2009年最少开设两次的课程

select S1.course_id
from course S1
where not unique(select S2.course_id
                from course S2
                where S2.course_id = S1.course_id and 
                S2.year = 2009);
                
select S1.course_id
from course S1
where 2 <= (select S2.course_id
                from course S2
                where S2.course_id = S1.course_id and 
                S2.year = 2009);             

3.2 最值查询方法小结

例：找到最大的账户余额

3.2.1 使用关系代数

$${\Pi }_{balance}(account)?{\Pi }_{A_1.balance}({\sigma }_{A_1.balance<A_2.balance}({\rho }_{A_1}(account)\times {\rho }_{A2}(account)))$$

翻译上述的表达式到SQL得：

select balance
from account 
except
(select A1.balance
from account A1, account A2
where A1.balance < A2.balance);

3.2.2 使用集合操作

（1）except

select balance
from account 
except
(select A1.balance
from account A1, account A2
where A1.balance < A2.balance);

3.2.3 使用嵌套子查询

（1）all关键字

select balance 
from account A
where A.account >= all(select balance
                      from account A2);

（2）exists关键字

select balance
from account A1
where not exists(select balance from account A2
                where A1.balace < A2.balance);

3.2.4 聚集函数

select max(balance)
from account;

3.3 from子句中的子查询

找出系平均工资超过42000美元的那些系中的教师的平均工资。

-----------  having 子句写法 ------------
select avg(salary) 
from instructor
group by dept_name
having avg(salary) > 42000;

-----------  from 子查询  ---------------
select S.avg_sal
from (select avg(salary) as avg_sal from instructor
      group by dept_name) as S
where S.avg_sal > 42000;

找出所有系中工资总额最大的系：

select dept_name
from instructor
group by dept_name
having sum(salary) >= all(select sum(salary) 
                         from instructor
                         group by dept_name);

打印每位老师的姓名，以及他们的工资和所在的系的平均工资。

select I.name, I.salary, A.avg_sal
from instructor I
left outer join (select dept_name, avg(salary) as avg_sal from instructor
     			group by dept_name) as A
on I.dept_name = A.dept_name;

此外，使用lateral关键字可以实现from子句中的变量访问外部的变量

select I.name, I.salary, A.avg_sal
from instructor I, lateral(select avg(salary) as avg_sal
                          from instructor I2
                          where I2.dept_name = I.dept_name);

3.4 with子句

找出具有最大预算值的系：

• 法1：关系代数翻译

select D.dept_name
from department D
except
(select dept_name
from department D1, department D2
where D1.budget < D2.budget);

• 法2：all关键字

select D.dept_name
from department D1
where D1.budget >= all(select D2.budget
                      from department D2);

• 法3：not exists关键字

select D.dept_name
from department D1
where not exists(select D2.budget
                from department D2
                where D1.budget < D2.budget);

• 法4：with关键字

with max_budget(value) as 
    (select max(budget)
    from department)
select budget
from department D1, max_budget M
where D1.budget = M.value;

找出所有的工资总额大于所有系工资总额的平均值的系：

• having写法

select dept_name,sum(salary) 
from instructor
group by dept_name
having sum(salary) > all(select avg(A.sum_sal)
                         from (select sum(salary) as sum_sal
                               from instructor
                               group by dept_name) as A);

• with子句写法

with sum_sal(dept_name, value) as
(select dept_name, sum(salary)
from instructor
group by dept_name),
avg_sum_sal(value) as
(select avg(value)
 from sum_sal)
select sum_sal.dept_name
from sum_sal
where sum_sal.value > all(select * from avg_sum_sal);

3.5 Scalar Subquery

SQL允许子查询出现在返回单个值的表达式能够出现的任何地方，只要该子查询值返回包含单个属性的单个元组。

这样的子查询称为标量子查询。

列举出所有的系以及他们拥有的教师数：

// 标量子查询写法
select dept_name, (select count(*) from instructor
                  where department.dept_name = instructor.dept_name) as num_instructors
       from department;

select department.dept_name, dept_count
from department, (select dept_name, count(*) from instructor
                 group by dept_name) as inst_num(dept_name, dept_count)
where department.dept_name = inst_num.dept_name;

4. Modification of the Database

4.1 Deletion

删除只能删除整个元组，不能只删除某些属性上的值。同时关系本身不会被改变。

• 删除表中的元组

delete from instructor;

• 删除表中某些满足条件的元组

delete from instructor 
where dept_name = "Finance";

delete from instructor
where dept_name in (select dept_name 
                   from department
                   where building = "Watson");

例：
删除工资低于大学平均工资的所有的教师记录

delete from instructor
where salary < (select avg(salary)
               from instructor);

4.2 Insertion

（1）插入单个元组

• 不写属性，属性顺序与关系模式中的属性排列一致

insert into course
values('CS-437', 'Databasa Systems', 'Comp.Sci', 4)

• 写属性，可以按照属性的任意顺序

insert into course(title, course_id, credits, dept_name)
values('Database Systems', 'CS-437', 4, 'Comp.Sci')

（2）批量插入元组集合

想让Music系每个修满144学分的学生成为Music系的老师：

insert into instructor
select ID, name, dept_name, 18000
from student 
where dept_name = "Music" and tot_credits > 144;

需要说明的是插入操作是在select操作执行完之后再进行的，即筛选出所有满足条件的学生后，一把插入到instructor表中；这一点和删除操作类似。

（3）含有空值的insert

待插入元组中某些未被赋值属性被赋予空值，考虑请求：

insert into student
values('3003', 'Green', 'Finance', null);

4.3 Updates

需求：在不改变整个元组的情况下改变其部分属性值。使用update语句。

待更新的元组可以使用查询语句找出。

4.3.1 全部操作和部分操作

• 全部操作

update instructor
set salary = salary * 1.03;

• 条件操作

update instructor 
set salary = salary * 1.03
where salary > 10000;
------------ 要写两个update ------------
update instructor
set salary = salary * 1.05
where salary <= 10000;

• 标量子查询

update student S
set tot_cred = (select sum(credits)
               from takes T natural left outer join course
               where T.ID = S.ID and
               T.grades <> 'F' and
               T.grades is not null);

如果一个学生没有上完任何课，那么上述更新语句将把其tot_cred属性值设置为空。设置为0其实更满足我们的要求：

update student S
set tot_cred = (select 
                case
              	 	when sum(credits) is not null then sum(credits)
                	else 0
                end
               from takes T natural left outer join course
               where T.ID = S.ID and
               T.grades <> 'F' and
               T.grades is not null);

4.3.2 case结构

一般结构：

case
	when pred_1 then result_1
	when pred_2 then result_2
	......
	when pred_n then result_n
	else 
		result_0
end

上面的需求可以改成下面的写法：

update instructor
set salary = case
	when salary <= 10000 then salary * 1.05
	else salary * 1.05
end

5. Views

视图不是模型的一部分，作为虚关系对用户可见

5.1 视图定义

create view faculty as 
select ID, name, dept_name
from instructor;

在视图中找到某个部门的数据：

select name
from faculty
where dept_name = "Biology";

例：计算各部门的工资总和

create view department_total_salary(dept_name, total_salary) as 
select dept_name, sum(salary)
from instructor
group by dept_name;

5.2 SQL查询中使用视图

5.2.1 视图的直接使用

create view physics_fall_2009(course_id, sec_id, building, room_number) as
select course_id, sec_id, building, room_number
from course C, section S
where C.course_id = S.course_id and C.dept_name = "Physics" and semester = "Fall" and year = 2009;

找到所有于2009年秋季学期在Watson大楼开设的Physics课程。

select * 
physics_fall_2009
where building = "watson";

5.2.2 在另一个视图定义中使用视图

create view physics_fall_2009_watson(course_id, sec_id, room_number) as
select course_id, sec_id, room_number
from physics_fall_2009
where building = "watson";

6. Integrity Constraints

6.1 not null约束

name varchar(20) not null,
budget numeric(12,2) not null

6.2 unique约束

unique(A1,A2,...,Am);

unique声明指出$A_{i1},A_{j2},...,A_{jm}$形成了一个候选码：在关系中没有两个元组能在所有列出的属性上取值相同。

候选码与主码不同的地方：

• 候选码属性值可以是null，除非被显式的声明为not null

6.3 check子句

check(P)：P是一个谓词逻辑

例：保证semester是fall, winter, spring, summer中的一个

create table section
(course_id varchar(8),
 sec_id varchar(8),
 semester varchar(6),
 year numeric(4,0),
 building varchar(5),
 room_number varchar(7),
 time_slot_id varchar(4),
 primary key(course_id, sec_id, semester, year),
 check(semester in ("Spring", "Summer", "Fall", "Winter"))
);

6.4 参照完整性

6.4.1 参照完整性

Referential Integrity：保证一个关系中给定属性上的取值也在另一关系的特定属性的取值中出现。

6.4.2 外码

（1）外码声明

dept_name varchar(20) references department();

（2）违反参照完整性的处理

级联处理的写法：

create table course
(dept_name varchar(20),
 foreign key(dept_name) references department
 	on delete cascade
 	on update cascade,
 ...
);

置空的写法：

create table course
(dept_name varchar(20),
 foreign key(dept_name) references department
 	on delete set null
 ...
);

默认值的写法：

create table course
(dept_name varchar(20),
 foreign key(dept_name) references department
 	on delete set default
 ...
);

6.5 check与断言

6.5.1 复杂check

check(P)，谓词P可以是包含任意查询的谓词：

check (time_slot_id in (select time_slot_id from time_slot));

check子句的检查时刻：

• section中插入元组时需要检测
• time_slot改变时也需要检测

复杂check检测的开销会很大

6.5.2 断言

断言是一个谓词，表示希望数据库满足的条件：

断言的特殊形式：

• 域约束
• 参照完整性约束

特别的约束：

• student的所有元组，他在属性tot_cred上的值必须等于该学生成功修完所有课程的学分总和

  create assertion credits_earned_constraint check
  (not exists (select tot_credits
              from students S
              where S.tot_credits <> (select sum(credits) from takes T
                                     where S.ID = T.ID and
                                     T.grades <> 'F' and T.grades is not null)));
  

• 每位教师不能在同一学期的同一个时间段在两个不同的教室授课

  create assertion teach_time_constraint check
  (unique (select ID, semester, time_slot_id, year from takes))
  

7. Build in Type

7.1 内置数据类型

• date

  date '2005-7-27'

• time

  time '09:00:00'

• timestamp

  timestamp '2005-7-27 09:00:30'

• interval

8. Index

create index student_ID_index on student(ID);

9. User-Defined Type

9.1 用户定义类型

create type Dollars as numeric(12,2);

create table department
(dept_name varchar(10),
 building varchar(15),
 budget Dollars
);

• 删除类型：drop type
• 修改类型：alter type

9.2 域

域：在基本类型上施加完整性约束

create domain person_name varchar(20) not null;

create domain degree_level varchar(10)
constraint degree_level_test
check(value in ('Bachelors','Master','Doctorate'));

10. Authorization

10.1 授权

grant select on instructior to U1,U2;

update权限可以授权在某个属性上，也可以授权在整个关系上，若没有说明，那么授权在整个关系上：

grant update on instructor(dept_name) to U1,U2,U3;

10.2 权限的收回

revoke select, update(budget)
on department
from Aimit;

如果收回的权限的角色已经把权限转让给了别人，那么权限的收回将会变得困难。

10.3 角色

10.3.1 角色创建

create role instructor;
grant select on takes to instructor;

10.3.2 角色的转移

create role dean
grant instructor to dean;
grant dean to Satoshi;

10.3.3 视图上的权限

create view geo_instructor as
select * 
from instructor
where dept_name = 'Geology';

grant select on geo_instructor to Amit;

10.3.4 模式的授权

只有模式的拥有者才能执行对模式的修改，诸如创建或者删除关系的属性，增加或者删除索引。

grant references (dept_name) on department to Mario;

10.3.5 权限的转移

获得某些授权的用户允许将此权限传递给其他用户，默认情况下，权限不允许转移给其他的用户、角色。

grant select on instructor to Amit with grant option;

10.3.6 权限的收回

revoke表示权限的收回

• cascade表示级联收回
• restrict表示不需要级联收回

revoke select on department from Amit, Satoshi cascade; 
revoke select on department from Amit, Satoshi restrict;

11.声明和调用SQL函数和过程

11.1 返回数据库基本类型的函数

需求：给定一个系的名字，返回该系的教师数目。编写下面的所示的函数：

create function dept_count(dept_name varchar(20))
	returns integer
	begin
	declare d_count integer;
		select count(*) into d_count
		from instructor
		where instructor.dept_name = dept_name
	return d_count;
	end

函数的使用：

select dept_name, budget
from department
where dept_count(dept_name) > 12;

11.2 表函数

SQL支持将关系作为结果的函数返回，这种函数称为表函数

例子：返回在一个给定部门中的所有教师

create function instructor_of(dept_name varchar(20))
	returns table(
        ID varchar(5),
        name varchar(20),
        dept_name varchar(20),
        salary numeric(8,2)
    )
    return table(select ID, name, dept_name, salary
                from instructor
                where instructor.dept_name = dept_name);

表函数可以在一个查询中使用：

select * 
from table(instructor_of('Finance'));

11.3 过程的定义

create procedure dept_count_proc(in dept_name varchar(20), out d_count integer)
	begin
	select count(*) into d_count
	from instructor
	where instuctor.dept_name = dept_count_proc.dept_name
	end

过程的使用

declare d_count integer;
call dept_count_proc('Physics', d_count);

11.4 支持过程和函数的语言构造

11.4.1 变量声明

declare d_count integer;

11.4.2 复合语句

复合语句使用begin end括起来

• begin atomic ... end保证所有语句按照单一事务来执行
• while和repeat

--- while ---
while 布尔表达式 do
	语句序列;
end while


--- repeat ---
repeat 
	语句序列;
	until 布尔表达式
end repeat

• for循环

declare n integer default 0;
for r as 
	select budget from department
	where dept_name = 'Music'
do 
	set n = n - r.budget
end for

• if语句

if 布尔表达式
	then ...
elseif 
	then ...
else 
	...
end if

• 异常处理

declare out_of_classroom_seats condition
declare exit handler for out_of_classroom_seats
begin 
sequence of statements
end

例：创建一个函数，在确保教室能容纳下的前提下注册一个学生

create function registerStudent(s_id varchar(5), s_courseid varchar(8), s_secid varchar(8),
                               s_semester varchar(6), s_year numeric(4,0))
returns integer
begin
	declare currEnrol integer;
	declare errorMessage varchar(20);
	
	select count(*) into currErol
	from takes
	where course_id = s_courseid and sec_id = s_secid
	and semester = s_semester and year = s_year;
	declare limit integer;
	
	select capacity into limit 
	from classroom natural join section
	on classroom.room_number = section.room_number
	where course_id = s_course_id and sec_id = s_secid
	and semester = s_semester and year = s_year;
	
	if (currEnrol < limit)
		begin 
			insert into takes values
				(s_id, s_courseid, s_secid, s_semester, s_year, null);
			return(0);
		end
	
	-- 否则达到上限
	set errorMessage = ''||''||'';
	return (-1);
end;

12. Triggers

12.1 after触发器

12.1.1 insert触发器

create trigger time_slot_check1 after insert on section
	referencing new row as nrow
	for each row
		when (nrow.time_slot_id not in (
        	select time_slot_id from time_slot))
        begin 
        	rollback
        end;

12.1.2 delete触发器

create trigger time_slot_check2 after delete on section
	referencing old row as orow
	for each row 
	when exists(select * from section
               	where section.time_slot_id = orow.time_slot_id) and
         not exists(select * from time_slot
                   where orow.time_slot_id = time_slot.time_slot_id)
    begin 
    	rollback
    end;

12.1.3 update触发器

create trigger time_slot_check3 after update of takes on (grade)
	referencing new row as nrow
	referencing old row as orow
	for each row
	when nrow.grade <> 'F' and nrow.grade is not null and (orow.grade = 'F' or orow.grade is null)
	begin atomic
        update student
        set tot_cred = tot_cred + (select credits
                                  from course
                                  where course.course_id = nrow.course_id)
        where student.id = nrow.id;
 	end;

12.2 before触发器

事件发生前的触发器的作用：

• 避免非法更新，插入，删除的额外约束

例：

• 把教师插入一个系，但是系的名称并不存在关系department中，触发器可以提前向department中插入该系的名称，以免产生外码冲突
• 当插入的分数是空白时，定义一个触发器，将这个值用Null代替

create trigger setnull before insert on instructor
	referencing new row as nrow
	for each row
	when nrow.dept_name not in (select dept_name from department)
		begin atomic
			insert into department
			values (nrow.dept_name, ....);
		end;