小白MySQL学习-day06

函数：

SQL内部提供函数：

如reverse，concat，sleep之类的

MySQL :: MySQL 5.7 Reference Manual :: 12 Functions and Operators

自定义函数：

delimiter $$     --这一行是改变结束条件为输入$$
create function f1(i1 int, i2 int)
returns int        -- 返回值
BEGIN
    declare num int;
    declare maxId int;
    select max(id) from big into maxId;
    
    set num = i1 + i2 + maxId;
    return(num);
END $$
delimiter ;  --再改回来

执行函数：
select f1(11,22);        

删除函数：
drop function f1;

存储过程：

存储过程，是一个存储在MySQL中的SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。

1.创建存储过程

delimiter $$
create procedure p1()
BEGIN
    select * from d1;
END $$
delimiter ;

2.执行，py操作差不多相同

call p1();

3.删除

drop procedure proc_name;

参数类型：

int（传入当参数），out（传出当返回值），inout（both）

delimiter $$
create procedure p2(
    in i1 int,
    in i2 int,
    inout i3 int,
    out r1 int
)
BEGIN
    DECLARE temp1 int;
    DECLARE temp2 int default 0;
    
    set temp1 = 1;

    set r1 = i1 + i2 + temp1 + temp2;
    
    set i3 = i3 + 100;

end $$
delimiter ;

set @t1 =4;
set @t2 = 0;
CALL p2 (1, 2 ,@t1, @t2);
SELECT @t1,@t2;

返回值&结果集

delimiter $$
create procedure p3(
    in n1 int,
    inout n2 int,
    out n3 int
)
begin
    set n2 = n1 + 100;
    set n3 = n2 + n1 + 100;
    select * from d1;   --返回结果集
end $$
delimiter ;

事物&异常（不常用）

delimiter $$
create PROCEDURE p4(
    OUT p_return_code tinyint
)
BEGIN 
  DECLARE exit handler for sqlexception 
  BEGIN 
    -- ERROR 执行异常就执行这个
    set p_return_code = 1; 
    rollback;         --回滚，将成功的恢复原样
  END; 
 
  DECLARE exit handler for sqlwarning 
  BEGIN 
    -- WARNING 
    set p_return_code = 2; 
    rollback; 
  END; 
 
  START TRANSACTION;  -- 开启事务，要成功都成功，要失败都失败
    delete from d1;
    insert into tb(name)values('seven');
  COMMIT;  -- 提交事务
 
  -- SUCCESS 
  set p_return_code = 0; 
 
  END $$
delimiter ; 

游标（不常用）

delimiter $$
create procedure p5()
begin 
    declare sid int;
    declare sname varchar(50); 
    declare done int default false;


    declare my_cursor CURSOR FOR select id,name from d1;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; --此时如果已经执行完
    
    open my_cursor;
        xxoo: LOOP
            fetch my_cursor into sid,sname;
            IF done then 
                leave xxoo;
            END IF;
            insert into t1(name) values(sname);
        end loop xxoo;
    close my_cursor;
end $$
delimiter ; 

视图：

视图其实是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用。

SELECT
    *
FROM
    (SELECT nid,name FROM tb1 WHERE nid > 2) AS A
WHERE
    A.name > 'alex';

创建视图

create view v1 as select id,name from d1 where id > 1;

使用视图，等价于注释的那个

select * from v1;

-- select * from (select id,name from d1 where id > 1) as v1;

删除视图

drop view v1;

修改

alter view v1 as SQL语句

视图不能被修改，但是修改原表，也会改变视图

触发器（不常用）：

触发操作前先执行trigger

# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

NEW.id NEW.name  的NEW是新添加的

OLD是原来的删除前的