【数据库】sql语句汇总（三）

/*数据库存储过程*/

*存储过程：

存储过程是一组为了完成特定功能的SQL语句集，存储在数据库中供调用执行，类似于一个函数或一个脚本；

①创建存储过程：

使用CREATE PROCEDURE语句创建存储过程，语法格式如下：

CREATE PROCEDURE 存储过程名([参数…])

[特征…] 主体

说明：存储过程参数---->参数： [IN|OUT|INOUT] 参数名 参数类型

注：系统默认在当前数据库中创建存储过程，如果需要在特定数据库中创建存储过程，要在名称前面加上数据库名称，数据库名.存储过程名

存储过程主体---->包含在过程调用时候必须执行的语句，以BEGIN开始，以END结束

存储过程体中的语句规范：

n 局部变量：DECLARE 变量名 类型 [默认值]

n 赋值语句：SET 变量名 = 表达式

n SELECT语句赋值：SELECT 列名 INTO 变量名 FROM … WHERE…;

扩展：

1>流程控制一 ‘IF语句’：

       IF 条件 THEN 语句

       [ELSEIF 条件 THEN 语句]

       [ELSE 语句]

       END IF

2>流程控制二 ‘循环语句’：

      WHILE 条件 DO 语句

      END WHILE

      REPEAT 语句

      UNTIL 条件

      END REPEAT

      LOOP 语句

      END LOOP（这里不常见，就不做过多表述；）

②存储过程的调用

直接调用：

       CALL 存储过程名([参数…]);

*比如要并输出该表员工人数结果，书写语句为：call employee_count (@N);

③存储过程的删除

使用DROP PROCEDURE语句删除存储过程，语法格式为：

       DROP PROCEDURE [IF EXISTS] 存储过程名;

#用delimiter来定义结束符：

一般存储过程都要用到多条语句共同起作用，所以正常的结束符’;’就不适合作为存储过程的结束符，所以用’$$’比较常见，此时语句遇到’$$’才会结束，使用方法为'delimiter $$'，要换回’;’作为结束符，就要再写 ’delimiter ;’

*比如要创建一个存储过程，比较两个员工的实际收入,书写语句为：

delimiter $$
CREATE PROCEDURE income_compare(in employeeID1 char(6),in employeeID2 char(6))
begin
DECLARE income1 float;
DECLARE income2 float;
DECLARE M int;
SELECT income into income1 from salary where employeeID=employeeID1;
SELECT income into income2 from salary where employeeID=employeeID2;
SELECT income1,income2;
if income1>income2 then
SET M=0;
select M AS M;
else 
SET M=1;
SELECT M AS M;
end if;
end $$

 *调用存储过程，若前者比后者高就输出0，否则输出1，员工用其员工编号识别；

delimiter ;
call income_compare('020010','020018');

call income_compare('020018','102201');

效果如下：

/*数据库触发器*/ 

1.创建触发器 
使用CREATE TRIGGER语句创建触发器，语法格式如下： 
CREATE TRIGGER 触发器名 触发时刻 触发事件 
ON 表名 FOR EACH ROW 触发器动作; 

注：FOR EACH ROW:这个声明用来指定，对于受触发事件影响的每一行，都要激活触发器的动作；
2.触发器中关联表中的列 
   在MySQL触发器中的SQL语句可以关联表中的任意列。但不能直接使用列的名称去标志，那会使系统混淆，因为激活触发器的语句可能已经修改、删除或添加了新的列名，而列的旧名同时存在。因此必须用这样的语法来标志：“NEW.column_name”或者 “OLD.column_name”。NEW.column_name用来引用新行的一列， OLD.column_name用来引用更新或删除它之前的已有行的一列。 对于INSERT语句，只有NEW是合法的；对于DELETE语句，只有OLD才合法；而UPDATE语句可以与NEW或OLD同时使用。
3.比如要创建触发器，在employee表中删除员工信息的同时将salary表中该员工的信息删除，书写语句为：

create trigger t_employee_delete after after delete
On employee for each row 
Begin 
Delete from salary
Where employeeID =old.employeeID;

后面删除一个employee表中的一个数据，选择第一行员工编号000001的数据，再查看employee表和salary表，发现都少了000001这个员工的数据；
4.比如要创建触发器，当修改employee表时，若将employee表中的员工工作时间增加一年，则将收入增加500，增加两年则收入增加1000，书写语句为：

create trigger t_salary_update after update
On employee for each row
Begin
Update salery set income =income+500*(NEW.workYear-OLD.workYear)
Where employeeID=old.employeeID;

查看salary表income的数据变化，更新数据，再加2年工作时间，发现数据正确变化；

 5.删除触发器 
使用DROP TRIGGER语句删除触发器，语法格式如下： 
DROP TRIGGER 触发器名;