MySql创建函数与过程，触发器， shell脚本与sql的相互调用。

2022-02-19 12:30:33

一：函数

1：创建数据库和表deptartment，

mysql> use DBSC;

Database changed

mysql> create table deptartment(dept_name varchar(20),

    -> budget bigint(20),

    -> building varchar(20));

Query OK, 0 rows affected

mysql> insert into deptartment values('电子系',10000,'2号楼');

Query OK, 1 row affected

mysql> insert into deptartment values('通信系',40000,'3号楼');

Query OK, 1 row affected

mysql> insert into deptartment values('计算机系',100000,'6号楼');

2:创建表 instructor

create table instructor(id int,
salary int,
dept_name varchar(20),
foreign key(dept_name) references deptartment(dept_name)) ENGINE=innodb  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

提示错误：1005 - Can't create table 'dbsc.instructor' (errno: 150)

修改数据表deptartment 的执行引擎：

alter table deptartment engine=innodb;

依然提示错误，因为外键约束的字段必须为被应用的表的主键。修改 deptartment 中dept_name的定义。

alter table deptartment modify dept_name varchar(20) primary key;

3：向表instructor中添加数据。

mysql> insert into instructor values(1,1000,'电子系');

Query OK, 1 row affected

mysql> insert into instructor values(2,1000,'电子系');

Query OK, 1 row affected

mysql> insert into instructor values(3,1000,'电子系');

Query OK, 1 row affected

mysql> insert into instructor values(4,1000,'电子系');

Query OK, 1 row affected

mysql> insert into instructor values(5,1000,'电子系');

Query OK, 1 row affected

mysql> insert into instructor values(1,1000,'通信系');

Query OK, 1 row affected

mysql> insert into instructor values(2,1000,'通信系');

Query OK, 1 row affected

mysql> insert into instructor values(3,1000,'通信系');

Query OK, 1 row affected

mysql> insert into instructor values(1,1000,'计算机系');

Query OK, 1 row affected

4：创建函数

创建函数提示错误： https://blog.csdn.net/topasstem8/article/details/8216740/

1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

修改

mysql> set global log_bin_trust_function_creators=TRUE;

创建有参函数：数据库默认语句分隔符为;，DELIMITER // 将数据库语句执行分隔符改为//。

DELIMITER //

create function dept_count(deptName varchar(20))

    returns integer

    begin

    return(

        select count(*)

        from instructor

        where instructor.dept_name = deptName);

    end//

DELIMITER ;

5：使用函数，求出instructor中院系教师大于2的deptartment

mysql> select * from deptartment where dept_count(dept_name)>2;

+-----------+--------+----------+

| dept_name | budget | building |

+-----------+--------+----------+

| 电子系    |  10000 | 2号楼    |

| 通信系    |  40000 | 3号楼    |

+-----------+--------+----------+

2 rows in set

https://www.cnblogs.com/taiguyiba/p/6619027.html

6：在函数中定义变量，返回赋值后的变量。

mysql> DELIMITER //

create function dept_count(deptName varchar(20))

    returns integer

    begin

    declare d_count integer ;

        select count(*) into d_count

        from instructor

        where instructor.dept_name = deptName;

    return d_count;

    end//

DELIMITER ;

Query OK, 0 rows affected

mysql> select dept_count('电子系');

+----------------------+

| dept_count('电子系') |

+----------------------+

|                    5 |

+----------------------+

1 row in set

7：创建无参函数

mysql> CREATE FUNCTION simpleFun()RETURNS VARCHAR(20) RETURN "电子系";

1304 - FUNCTION simpleFun already exists

mysql> select * from deptartment where dept_name=simpleFun();

+-----------+--------+----------+

| dept_name | budget | building |

+-----------+--------+----------+

| 电子系    |  10000 | 2号楼    |

+-----------+--------+----------+

1 row in set

二：过程：使用一中的表 https://www.cnblogs.com/oskyhg/p/7679962.html

1：创建无参数的过程，查询的值直接返回。

drop procedure if exists pro1;
mysql> create procedure pro1()

    select 5

    -> ;

Query OK, 0 rows affected

mysql> call pro1();

+---+

| 5 |

+---+

| 5 |

+---+

1 row in set

Query OK, 0 rows affected

mysql>

mysql> drop procedure if exists pro1;
mysql> create procedure pro1()

    select * from instructor;

Query OK, 0 rows affected

mysql> call pro1();

+----+--------+-----------+

| id | salary | dept_name |

+----+--------+-----------+

|  1 |   1000 | 电子系    |

|  2 |   1000 | 电子系    |

|  3 |   1000 | 电子系    |

|  4 |   1000 | 电子系    |

|  5 |   1000 | 电子系    |

|  1 |   1000 | 通信系    |

|  2 |   1000 | 通信系    |

|  3 |   1000 | 通信系    |

|  1 |   1000 | 计算机系  |

+----+--------+-----------+

9 rows in set

Query OK, 0 rows affected

mysql>

2：创建有输入输出参数的过程。 mysql中的变量 https://blog.csdn.net/qq_34531925/article/details/79483312

mysql> -- 定义执行语句的分割符，遇到//就执行。

DELIMITER //

drop procedure if exists pro3 //

-- in输入参数和类型，out输出参数和类型

create procedure pro3(in parm1 int, out parm2 int)

begin

-- 定义自定义变量，局部变量

declare parm3 int;

-- 对输入参数值进行判断

if parm1=10 then

set parm3=parm1; -- 对parm3进行赋值

else

set parm3=20;

end if;

-- 插入操作，自定义变量的值做为插入值

insert into instructor(id) values(parm3);

-- 查询结果赋值给输出参数

select count(*) into parm2 from instructor;

end //

-- 调用过程，将输出值，赋值给outValue变量

call pro3(10,@outValue) //

-- 查询输出值，@outValue 是用户变量

select @outValue //

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 1 row affected

+-----------+

| @outValue |

+-----------+

|        12 |

+-----------+

1 row in set

mysql>

3：当没有输出参数时，会将过程中最后的select查询结果作为过程的结果

mysql> create procedure pro1(name varchar(20))

    select * from instructor where dept_name=name;//

Query OK, 0 rows affected

mysql> call pro1('电子系');//

+----+--------+-----------+

| id | salary | dept_name |

+----+--------+-----------+

|  1 |   1000 | 电子系    |

|  2 |   1000 | 电子系    |

|  3 |   1000 | 电子系    |

|  4 |   1000 | 电子系    |

|  5 |   1000 | 电子系    |

+----+--------+-----------+

5 rows in set

Query OK, 0 rows affected

三：函数和过程支持for while语句。

创建工资表

mysql> create table salarie(name varchar(20),salary int(11));

mysql> insert into salarie values('zhangsan',2000);

mysql> insert into salarie values('lisi',2500);

mysql> insert into salarie values('wangwu',3000);

1：使用过程中的while语句向sql表中添加数据

mysql> delimiter //

drop procedure if exists salary //

create procedure salary()

         begin

         declare i int default 0;

         while i<10 do

               insert into salarie values('wangwu',100);

               set i=i+1;

        end while;

       end //

Query OK, 0 rows affected

Query OK, 0 rows affected

mysql> call salary();//

Query OK, 1 row affected

mysql> select * from salarie; //

+----------+--------+

| name     | salary |

+----------+--------+

| zhangsan |   2000 |

| lisi     |   2500 |

| wangwu   |   3000 |

| wangwu   |    100 |

| wangwu   |    100 |

| wangwu   |    100 |

| wangwu   |    100 |

| wangwu   |    100 |

| wangwu   |    100 |

| wangwu   |    100 |

| wangwu   |    100 |

| wangwu   |    100 |

| wangwu   |    100 |

+----------+--------+

13 rows in set

2:使用repeat语句向数据库中添加数据

delimiter //

drop procedure if exists salary //

create procedure salary()

         begin

         declare i int default 0;

         repeat

               insert into salarie values('lisi',100);

               set i=i+1;

        until i>5

        end repeat;

       end //

3: 使用loop循环插入。 read_loop为起的loop名字，可以为任意名。

delimiter //

drop procedure if exists StatisticStore; //

CREATE PROCEDURE StatisticStore()

BEGIN

    declare i int default 0;

    read_loop:loop

    if i>6 then

        leave read_loop;

    end if;

                insert into salarie values('zhangsan',300);

    set i=i+1;

    end loop;

END; //

四：使用游标，对查询出来的记录进行迭代操作。游标只能在存储过程中使用。使用三中的表 https://blog.csdn.net/liguo9860/article/details/50848216

向表中添加一个自增的字段，用于区分各个记录。

alter table `salarie` add `id` int AUTO_INCREMENT UNIQUE;//

mysql> select * from salarie;

+----------+--------+----+

| name     | salary | id |

+----------+--------+----+

| zhangsan |   2000 |  1 |

| lisi     |   2500 |  2 |

| wangwu   |   3000 |  3 |

| wangwu   |    100 |  4 |

| wangwu   |    100 |  5 |

| wangwu   |    100 |  6 |

| wangwu   |    100 |  7 |

| wangwu   |    100 |  8 |

| wangwu   |    100 |  9 |

| wangwu   |    100 | 10 |

| wangwu   |    100 | 11 |

| wangwu   |    100 | 12 |

| wangwu   |    100 | 13 |

| lisi     |    100 | 14 |

| lisi     |    100 | 15 |

| lisi     |    100 | 16 |

| lisi     |    100 | 17 |

| lisi     |    100 | 18 |

| lisi     |    100 | 19 |

| zhangsan |    300 | 20 |

| zhangsan |    300 | 21 |

| zhangsan |    300 | 22 |

| zhangsan |    300 | 23 |

| zhangsan |    300 | 24 |

| zhangsan |    300 | 25 |

| zhangsan |    300 | 26 |

+----------+--------+----+

26 rows in set

向工资低于2700的员工每人加一百块钱工资的工程如下，并将工资低于2700的员工的总额统计出来。

mysql>  delimiter //

drop procedure if exists addMoney; //

CREATE PROCEDURE addMoney()

BEGIN
    -- 定义的游标变量，用于接收查询出来的记录

    declare oldSalary int;

    declare nid int;

    declare total int default 0;

    declare done int default false;

    declare cur cursor for select salary,id from salarie where salary<2700;
    -- 定义的标记符done, 直到最后将游标中的数据全部取出，设置done为true

    declare continue HANDLER for not found set done = true;

    set total = 0;

    open cur;

    read_loop:loop

    fetch cur into oldSalary,nid;

    if done then

        leave read_loop;

    end if;

                update salarie set salary=salary+100 where id=nid;

    set total = total + oldSalary;

    end loop;

    close cur;

    select total;

END; //

Query OK, 0 rows affected

Query OK, 0 rows affected

mysql> call addMoney;//

+-------+

| total |

+-------+

|  8200 |

+-------+

1 row in set

Query OK, 0 rows affected

mysql> select * from salarie;

    -> //

+----------+--------+----+

| name     | salary | id |

+----------+--------+----+

| zhangsan |   2100 |  1 |

| lisi     |   2600 |  2 |

| wangwu   |   3000 |  3 |

| wangwu   |    200 |  4 |

| wangwu   |    200 |  5 |

| wangwu   |    200 |  6 |

| wangwu   |    200 |  7 |

| wangwu   |    200 |  8 |

| wangwu   |    200 |  9 |

| wangwu   |    200 | 10 |

| wangwu   |    200 | 11 |

| wangwu   |    200 | 12 |

| wangwu   |    200 | 13 |

| lisi     |    200 | 14 |

| lisi     |    200 | 15 |

| lisi     |    200 | 16 |

| lisi     |    200 | 17 |

| lisi     |    200 | 18 |

| lisi     |    200 | 19 |

| zhangsan |    400 | 20 |

| zhangsan |    400 | 21 |

| zhangsan |    400 | 22 |

| zhangsan |    400 | 23 |

| zhangsan |    400 | 24 |

| zhangsan |    400 | 25 |

| zhangsan |    400 | 26 |

+----------+--------+----+

26 rows in set

五：触发器

FOR EACH ROW，可以迭代取出每一行中的列的数据。

创建触发器，并使用。创建插入触发器，当新加入的薪水大于1000时，减去200。

mysql> drop trigger if exists BeforeInsert; //

CREATE TRIGGER BeforeInsert BEFORE insert ON salarie

FOR EACH ROW

BEGIN

    IF new.salary> 1000 THEN

        SET new.salary = new.salary-200;

    END IF;

END; //

Query OK, 0 rows affected

Query OK, 0 rows affected

mysql> insert into salarie(name,salary) values('zhaoliu',1500);//

Query OK, 1 row affected

mysql> select * from salarie;//

+----------+--------+----+

| name     | salary | id |

+----------+--------+----+

| zhangsan |   2100 |  1 |

| lisi     |   2600 |  2 |

| wangwu   |   3000 |  3 |

| wangwu   |    200 |  4 |

| wangwu   |    200 |  5 |

| wangwu   |    200 |  6 |

| wangwu   |    200 |  7 |

| wangwu   |    200 |  8 |

| wangwu   |    200 |  9 |

| wangwu   |    200 | 10 |

| wangwu   |    200 | 11 |

| wangwu   |    200 | 12 |

| wangwu   |    200 | 13 |

| lisi     |    200 | 14 |

| lisi     |    200 | 15 |

| lisi     |    200 | 16 |

| lisi     |    200 | 17 |

| lisi     |    200 | 18 |

| lisi     |    200 | 19 |

| zhangsan |    400 | 20 |

| zhangsan |    400 | 21 |

| zhangsan |    400 | 22 |

| zhangsan |    400 | 23 |

| zhangsan |    400 | 24 |

| zhangsan |    400 | 25 |

| zhangsan |    400 | 26 |

| zhaoliu  |   1300 | 28 |

+----------+--------+----+

27 rows in set

创建更新触发器，new 代表更新的数据，准备插入的，old代表原来的数据。

当给一个员工改变薪水时，如果改变的薪水大于4000，那么还按原来的薪水。

mysql> drop trigger if exists BeforeUpdate; //

CREATE TRIGGER BeforeUpdate BEFORE update ON salarie

FOR EACH ROW

BEGIN

    IF new.salary> 4000 THEN

        SET new.salary = old.salary;

    END IF;

END; //

Query OK, 0 rows affected

Query OK, 0 rows affected

mysql> update salarie set salary=7000 where id=28;//

Query OK, 0 rows affected

Rows matched: 1  Changed: 0  Warnings: 0

mysql> select * from salarie;//

+----------+--------+----+

| name     | salary | id |

+----------+--------+----+

| zhangsan |   2100 |  1 |

| lisi     |   2600 |  2 |

| wangwu   |   3000 |  3 |

| wangwu   |    200 |  4 |

| wangwu   |    200 |  5 |

| wangwu   |    200 |  6 |

| wangwu   |    200 |  7 |

| wangwu   |    200 |  8 |

| wangwu   |    200 |  9 |

| wangwu   |    200 | 10 |

| wangwu   |    200 | 11 |

| wangwu   |    200 | 12 |

| wangwu   |    200 | 13 |

| lisi     |    200 | 14 |

| lisi     |    200 | 15 |

| lisi     |    200 | 16 |

| lisi     |    200 | 17 |

| lisi     |    200 | 18 |

| lisi     |    200 | 19 |

| zhangsan |    400 | 20 |

| zhangsan |    400 | 21 |

| zhangsan |    400 | 22 |

| zhangsan |    400 | 23 |

| zhangsan |    400 | 24 |

| zhangsan |    400 | 25 |

| zhangsan |    400 | 26 |

| zhaoliu  |   1300 | 28 |

+----------+--------+----+

27 rows in set

mysql中不用给新的数据或旧的数据起别名，默认为new，old

其它数据库的更新或插入：

CREATE TRIGGER TestField1_BeforeInsert BEFORE INSERT ON salarie

-- 新插入的行或更新的行的别名为nrow, 相当于mysql中的new 。 例如：update salarie set salary=7000 where id=28;//

referencing new row as nrow

-- 数据库中原来的旧数据别名为orow，相当于mysql中的old 。例如：| zhaoliu  |   1300 | 28 |

referencing old row as orow

FOR EACH ROW

BEGIN

    IF nrow.salary> 1000 THEN

        SET nrow.salary = orow.salary-200;

    END IF;

END;

六：递归mysql不支持with recursive 递归查询语句，只能自己写循环语句迭代 https://blog.csdn.net/wickedvalley/article/details/78925041

七：sql与shell脚本的相互调用。

window上面sql与c++的相互调用。 https://blog.csdn.net/swotcoder/article/details/18524

https://blog.csdn.net/shaoyiwenet/article/details/53256103

触发器调用shell文件，shell读取mysql中最新插入的一行，读取邮箱用户名，然后发送邮件给客户。

触发器中执行shll命令， https://zhidao.baidu.com/question/2271230050936210028.html

八：循环中使用union

delimiter //

drop procedure if exists tile;//

drop table if exists tempTable;//

create temporary table tempTable as select * from salarie where id<3;//

create procedure tile(n int)

    -- set @tb:=table(id int(11),salary int(11), name varchar(20));

    begin

      while(n>0) do

         set n=n-1;

         select * from ((select * from tempTable) union (select * from salarie where id<(10+n)))t3;

      end while;

      -- select * from tempTable;

    end//

delimiter;

mysql> call tile(3);

+----------+--------+----+

| name     | salary | id |

+----------+--------+----+

| zhangsan |   2100 |  1 |

| lisi     |   2600 |  2 |

| wangwu   |   3000 |  3 |

| wangwu   |    200 |  4 |

| wangwu   |    200 |  5 |

| wangwu   |    200 |  6 |

| wangwu   |    200 |  7 |

| wangwu   |    200 |  8 |

| wangwu   |    200 |  9 |

| wangwu   |    200 | 10 |

| wangwu   |    200 | 11 |

+----------+--------+----+

11 rows in set

+----------+--------+----+

| name     | salary | id |

+----------+--------+----+

| zhangsan |   2100 |  1 |

| lisi     |   2600 |  2 |

| wangwu   |   3000 |  3 |

| wangwu   |    200 |  4 |

| wangwu   |    200 |  5 |

| wangwu   |    200 |  6 |

| wangwu   |    200 |  7 |

| wangwu   |    200 |  8 |

| wangwu   |    200 |  9 |

| wangwu   |    200 | 10 |

+----------+--------+----+

10 rows in set

+----------+--------+----+

| name     | salary | id |

+----------+--------+----+

| zhangsan |   2100 |  1 |

| lisi     |   2600 |  2 |

| wangwu   |   3000 |  3 |

| wangwu   |    200 |  4 |

| wangwu   |    200 |  5 |

| wangwu   |    200 |  6 |

| wangwu   |    200 |  7 |

| wangwu   |    200 |  8 |

| wangwu   |    200 |  9 |

+----------+--------+----+

9 rows in set

Query OK, 0 rows affected

九：事件，事件可以定时执行一些任务等。

如下：创建一个事件e1，每周执行一次过程procedure1

create event e1 on schedule every 1 week

    do

    call procedure1('hello');

mysql 误操作之后的回滚，当mysql误操作之后，又没有开启事务，可以用一下方法回滚。就是生成与误操作相反的语句。

https://www.jb51.net/article/99553.htm

码农公寓

相关文章