存储过程&函数&触发器@变量&权限

mysql 数据查询:

mysql> select name,subject,group_concat(id) from student group by subject;
+---------+---------+------------------+
| name    | subject | group_concat(id) |
+---------+---------+------------------+
| lisi    | english | 2,3,4            |
| lisheng | math    | 1                |
+---------+---------+------------------+
2 rows in set (0.01 sec)



存储过程&函数&触发器:

delimiter //

create procedure count_student(out num int)
    begin

        select count(*) into num from student;

    end
    //
    delimiter ;

mysql> call count_student(@num);
Query OK, 1 row affected (0.00 sec)

mysql> select @num;
+------+
| @num |
+------+
|    4 |
+------+
1 row in set (0.00 sec)


delimiter //

create function name_of_student(iid int) -- 这里就不要用in了，否则会报错!
    returns varchar(20)
    begin
        return (select name from student where id=iid);
    end
    //
delimiter ;

局部变量和全局变量:

delimiter //

create procedure p1()
    begin
        declare x char(10) default 'outer ';
        begin
            declare x char(10) default 'inner ';
            select x;
        end;

        select x;
    end//

delimiter ;


mysql> call p1();
+-------+
| x     |
+-------+
| inner |
+-------+
1 row in set (0.00 sec)

+-------+
| x     |
+-------+
| outer |
+-------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)



mysql中的全局变量不必要声明即可使用，变量在整个过程中有效,全局变量以字符 '@' 作为开起字符:

delimiter //

create procedure p2()
     begin
        set @t=1;
        begin
            set @t=2;
            select @t;
        end;
        select @t;
     end
     //
delimiter ;


mysql> call p2();
+------+
| @t   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

+------+
| @t   |
+------+
|    2 |
+------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)


delimiter //
create procedure p3()
    begin
        declare tmp_name varchar(20);
        declare i int default 1;
        declare test_cursor cursor for select name from student;
        declare continue handler for not found set i=0;

        open test_cursor;

         fetch test_cursor into tmp_name;   

         while i>0 do
         select tmp_name;

         fetch test_cursor into tmp_name;   
        end while;
        close test_cursor;

        set i=1;

open test_cursor;

         fetch test_cursor into tmp_name;   

         while i>0 do
         select tmp_name;

         fetch test_cursor into tmp_name;   
        end while;
        close test_cursor;
    end
//

delimiter ;


结果:一个游标可以打开多次.



触发器 ：

delimiter //

create trigger test_tri after insert on student 
    for each row

    begin
        call test_pro();
    end
//

create procedure test_pro()
    begin
        insert into test_student values('good man','math',6);
    end
//

delimiter ;


mysql 权限管理


用户---->通过user表来进行第一步验证,看是否具有连接权限---->
通过db验证


新增一个用户:

grant [权限1,权限2,权限3..] on [什么库].[什么表] to user@'host' identified by 'password';

常用的权限有 all,create,drop,insert,delete,update,select  

grant all on *.* to lisi@'192.168.1.%' identified by '111111';

-- 收回权限 

revoke all on *.* from lisi@'192.168.1.%';