数据库

一. 创建过程：封装select语句，参数类型可以是in, out , inout  

delimiter //
create procedure myproc()
begin
    select * from student;
end //
delimiter ;

其中调用用call

call myproc();

删除过程

drop procedure myproc if exists procedure;

题：创建一个过程，第一个参数是显示第几页，第二个参数是一页包括几行

delimiter //
create procedure myproc(a int, b int)
begin 
    declare c int default 0;
    set c = (a-1)*b;
    select * from student limit c,b;
    
end//
delimiter ;
call myproc(3,3);

 

改进：如果输入页数<0显示第一页，输入页数>最大页  ， 显示最后一页

ceiling函数为向上取整；

delimiter //
create procedure myproc(a int, b int)
begin 
    declare c int default 0;
    declare num int default 0;
    declare sumpage int default 0;
    set num = (select count(*) from student);
    if(num % b = 0) then set sumpage = num/b;
    else set sumpage = num/b+1;
    end if; 
    #set sumpage = ceiling(num/b);
    if(a <= 0) then set a = 1;
    elseif (a > sumpage) then set a = sumpage;
    end if;
    set c = (a-1)*b ;
    select * from student limit c,b;
end//
delimiter ;
call myproc(4,3);

 

 声明只能写在函数上面

二. 视图：由一张或者多张表导出的虚拟表,  一般只用于查询

create view myview as(select s.*,cnum,score from student s inner join sc on s.snum= sc.snum);

对视图插入的操作，视图不能分组（group by）和去重(distinct)生成，否则失败

更新视图时（update,insert）涉及到改变的变量，改变一张表可以，多个不可以

原始表什么都可以做，即一个表形成的视图

三 . 游标

drop procedure if exists myproc;
delimiter //
create procedure myproc()
begin
    declare mynum varchar(10) default '';
    declare myname varchar(10) default '';
    declare flag bool default true;
    #声明
    declare mycursor cursor for select snum,sname from student;
 
    declare continue handler for not found set flag = false;
    #打开
    open mycursor;
    create table temp(mynum varchar(10),myname varchar(10));
    fetch mycursor into mynum,myname;
    #遍历
    while flag = true do
        insert into temp values(mynum,myname);
        fetch mycursor into mynum,myname;
    end while;
    select * from temp;
    #关闭
    close mycursor;
    drop table temp;
end//
delimiter ;
call myproc();

四. 事务

如果没有提交，则可以rollback （回滚）；提交后则无效。

start transaction;

update sc set score = 50 where snum ='02'and cnum ='02';
commit;
select * from sc;
rollback;

五 . 触发器：是一种特殊的存储过程,当指定的事件发生时，系统自动调用

分为新表和旧表# new  old(改变的原来的数据)

1.如果删除了student的数据则删除sc的数据

测试语句：delete from student where snum = '01';

delimiter //
create trigger mytrig
after delete
on student
for each row
begin
    delete from sc where sc.snum = old.snum;
end//
delimiter ;

2.插入:如果在student表中插入学生，则默认他选中三门课程

测试语句：insert into student values('10','aa','1993-04-04','男');

drop trigger mytrig;
delimiter //
create trigger mytrig
after insert
on student
for each row
begin
    insert into sc values(new.snum,'01','');
    insert into sc values(new.snum,'02','');
    insert into sc values(new.snum,'03','');
end//
delimiter ;

3.更新：如果更新了student，那么更新sc

测试语句：update student set snum = 20 where sname = 'aa';

delimiter //
create trigger myupdate
after update 
on student
for each row
begin
      update sc set snum = new.Snum where snum = old.Snum;
end //

delimiter ;

外键：

乐观锁：

悲观锁：

推荐书籍：高性能mysql