SQL触发器和视图的使用

数据表结构：

1. 选课表：

学号：采用定长串表示，即char型

课号：采用定长串表示，即char型

成绩：采用短整数表示，即smallint型

create table sel_course_tb
(
    Snum char(10),
    Cnum  char(4),
    Ccredit smallint,
    constraint course_tb_pk PRIMARY KEY(Snum,Cnum),
    constraint sel_course_tb_1 foreign KEY(Snum) references student_tb(Snum),
    constraint sel_course_tb_2 foreign KEY(Cnum) references course_tb(Cnum)
);

2. 学生表：

学号：采用定长串表示，即char型

姓名：采用可变长串表示，即varchar型

出生年月：采用日期表示，即date型

电话：采用定长串描述，即char型

create table student_tb
(
    Snum char(10),
    Sname varchar(20),
    Sdate date,
    Sphone char(11),
    constraint student_tb_pk PRIMARY KEY(Snum),
    constraint student_tb_uk UNIQUE(Snum)
);

3. 课程表：

课号：采用定长串表示，即char型

课程名：采用可变长串表示，即varchar型

学分：采用短整数表示，即smallint型

create table course_tb
(
    Cnum char(4),
    Cname varchar(30),
    Ccredit smallint,
    constraint course_tb_pk PRIMARY KEY(Cnum),
    constraint course_tb_uk UNIQUE(Cnum)
);

插入数据：

向学生表插入数据：

insert into student_tb values('0201121514', '张三','2002-01-01','15624626879');

向课程表插入数据：

insert into course_tb values('01','数据通信原理',5);

向选课表插入数据：

insert into sel_course_tb values('0201121514','01',80);

题目一：学分必须为1～8之间的数，不能为空。要求用触发器实现

基本思路：判断学分是否不在1~8，若不在，抛出一个异常

delimiter 
create trigger is_credit_right before insert on course_tb 
for each row 
begin 
    if new.Ccredit > 8 or new.Ccredit < 0 or new.Ccredit is null then 
	SIGNAL SQLSTATE '97875'  SET MESSAGE_TEXT = "学分必须为1～8之间的数，不能为空";
	end if;
end;

插入一个数据检查一下触发器是否正常工作

insert into course_tb
values('10','线性代数',null);

发现一个异常被抛出，数据没有被插入表

题目二：定义视图，统计某课程成绩最高的前三名学生信息。字段为学号，姓名，课号，课名，成绩

这里统计课程号为 01 的成绩最高的前三名学生信息

create view view_q3_1 as
select student_tb.Snum,student_tb.Sname,course_tb.Cnum,course_tb.Cname,sel_course_tb.Ccredit
from student_tb,course_tb,sel_course_tb
where sel_course_tb.Cnum = '01' and 
   course_tb.Cnum = sel_course_tb.Cnum and 
   student_tb.Snum = sel_course_tb.Snum
order by sel_course_tb.Ccredit desc limit 3;

题目三：记录选课表的更新、删除和插入元组的时间和用户及相应的值，使用触发器实现

先建一个表，统计对某个数据的操作

create table uid_time_tb
(
    op_Snum char(10) not null,
    op_Cnum char(4) not null,
    op_user varchar(120),
    insert_time datetime,
    update_time datetime,
    constraint uid_pk primary key(op_Snum,op_Cnum)
)

建一个插入数据的触发器，使用 after 触发器

current_user获取系统用户，current_timestamp获取系统时间

delimiter 
create trigger insert_time after 
insert on sel_course_tb
for each row
begin
     insert into uid_time_tb
    values(new.Snum,new.Cnum,current_user,CURRENT_TIMESTAMP,null);
end;
drop trigger insert_time;

最后检查一下执行结果

insert into sel_course_tb
values('0201121521','09',70);
select*from uid_time_tb;

记录更新、删除操作的触发器道理和insert触发器一样，直接粘贴代码

delimiter 
create trigger update_time after 
update on sel_course_tb
for each row
begin
	update uid_time_tb set update_time = CURRENT_TIMESTAMP
	where uid_time_tb.op_Snum = new.Snum and uid_time_tb.op_Cnum = new.Cnum;
end;

delimiter 
create trigger delete_time before
delete on sel_course_tb
for each row
begin
	update uid_time_tb set delete_time = CURRENT_TIMESTAMP
	where uid_time_tb.op_Snum = old.Snum and uid_time_tb.op_Cnum = old.Cnum;
end;
drop trigger delete_time;

题目四：定义视图，显示学生基本信息，字段为：学号，姓名，年龄，电话。

建立视图很简单，直接 create view 即可，计算年龄的话，用系统时间减去学生的生日即可，注意要用year函数获得年份!直接 paste 代码

create view view_all(学号,姓名,年龄,电话) as
select 
	student_tb.Snum,
       student_tb.Sname,
       year(current_date())-year(student_tb.Sdate),
       student_tb.Sphone
from student_tb;
select *from view_all;