mysq 基本知识(存储过程和函数)

1.自定义变量

1.1用户变量

  /**
  		作用域： 针对于当前会话（连接） 有效，同于会话变量的作用域
  		应用在任何地方， 也就是 begin end 里面或begin end
  */

赋值的操作符 = 或 ：=

   1.声明并初始化
     set @用户变量名 = 值; 或
     set @用户变量名 := 值; 或
     select @用户变量名 := 值；
    
   2. 赋值 （更新用户变量的值）
       a. 通过set 或 select  
     	set @用户变量名 = 值; 或
	     set @用户变量名 := 值; 或
	     select @用户变量名 := 值；
	   b. 通过 select into 
	      select 字段 into  变量名  from  表
   3 使用
     select @用户变量名

1.2 局部变量

/**
作用域： 仅仅在定义它的 begin end 中有效
应用在 begin end 中的第一句话
*/

    a. 声明
     declare 变量名 类型；
     declare 变量名 类型 default 值；
    
    b. 赋值
        1. 通过set 或 select  
     	set 局部用户变量名 = 值; 或
	     set 局部用户变量名 := 值; 或
	     select 局部用户变量名 := 值；
	   2. 通过 select into 
	      select 字段 into  局部变量名  from  表
     c.使用
       select 局部变量名
       

1.3 局部变量和用户变量的不同

a.作用域 b.定义和使用的位置 c.语法

2. 存储过程

  含义： 一组预先编译好的sql 语句的集合，理解成批处理语句
	1.提高代码的重用性，
	2.简化操作
	3.减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

2.1 创建语法

create prodecure 存储过程名（参数列表）
begin
      存储过程体
end

注意：
 1.参数列表包含三部分
 参数模式  参数名  参数类型
  举例：
   in stuname varchar(20)
参数模式
in: 该参数可以作为输入, 也就是该参数需要调用方法入值
out: 该参数可以作为输入, 也就是该参数作为返回值
inout : 该参数可以作为输入又可以作为输出

2. 如果存储过程题仅仅只有一句话。 begin end 可以省略
 存储过程体中的每条sql 语句的结尾要求必需加分号；
 存储过程题中的结尾可以视同delemiter 重新设置
delimiter 结束标记
delimieter $

2.2 调用语法

call 存储过程名（实参列表）；

2.3 创建带in 模式参数的存储过程

  create procedure myp2(in beautyName  varchar2(20))
  begin 
     select name from a where a.name = beautyName
  end

   # 调用
   call myp2('刘岩');

2.3 创建带out 模式的存储过程

  # 单个out

DELIMITER $
CREATE PROCEDURE myp5(IN id INT,OUT two VARCHAR(30))
BEGIN 
    SELECT b.two INTO two FROM zq_one b WHERE b.one = id;
END $

  # 多个 out
  DELIMITER $
	CREATE PROCEDURE mp6(IN id INT, OUT two VARCHAR(40) , OUT three VARCHAR(50) )
	BEGIN
		SELECT a.two , a.three INTO two,three FROM zq_one a WHERE a.one = id;	
	
	END $

CALL mp6(1, @12, @4434);
SELECT @12, @4434;

2.4 创建inout 模式参数的存储过程

DELIMITER $
CREATE PROCEDURE myp7(INOUT a INT , INOUT b INT)
BEGIN
	SET a = a*2;
	SET b = b*2;
END $

SET @b :=2;
SET @dc := 3 ;
CALL myp7(@b, @dc);
SELECT @b, @dc ;

2.5 删除存储过程

drop procedure 存储名

2.6 查看存储过程的信息

show create procedrue myp2;

3 函数

3.1 创建语法

create function 函数名（参数列表） returns 返回类型
begin 
		函数体
end 

 函数体： 肯定会有return 语句， 如果没有会报错
如果return 语句没有放在函数体的最后，也不报错，但不建议
return 值

使用delimiter 语句设置结束标记

3.2 调用语法

select 函数名（参数列表）

1.无参有返回


	DELIMITER $
	CREATE FUNCTION myf1() RETURNS INT
	BEGIN
	  DECLARE c INT ;
	  SELECT COUNT(*) INTO c FROM zq_one;
	  RETURN c;
	END $

2.  有参有返回
DELIMITER $
		CREATE FUNCTION myf2( a INT) RETURNS VARCHAR(30)
		BEGIN 
		   DECLARE c VARCHAR(20) DEFAULT '';
		   SELECT c.two INTO c FROM zq_one c WHERE c.one = a;
		
		    RETURN c;
		END $
		
		SELECT myf2(4);

3.3 查看函数

show create function myf3;

3.4 删除函数

drop function myf3;

4 流程控制结构

4.1 顺序结构

4.2 分支结构

4.2.1 if 函数

语法
  IF(表达式1， 表达式2， 表达式 3)
  如果表达式1成立， 则 if 函数返回表达式2的值
应用： 在任何地方

4.2.2 case 结构

情况1：类似于java 中的多重IF语句，一般用于实现区间判断
语法： 
    case 变量| 表达式| 字段
    when 要判断的值 THEN 返回的值1
    when  要判断的值 then 返回的值2
    ....
    else 要返回的这n
    end;
    
情况2： 类似于java中的switch语句，一般用于实现等值判断
语法
   case 
   when 要判断得值 then 返回的值1 或语句1；
   when 要判断得值 then 返回的值2 或语句2；
   ...
	else 要返回的值n 或语句n;
	end case;
  
  特点：
     可以作为表达式，嵌套在其他语句中使用，可以放在任何地方，begin end 中或begin  end 外
     可以作为独立的语句去使用，只能放在begin end 中
如果wen中的值满足或条件成立，则执行对应then 后面的值或语句如果
都不满足，则执行else 中的语句或值
     else可以省略，如果else 省略，并且所有when 条件都不满足，则返回 null

案例

DELIMITER $
CREATE PROCEDURE test_case(IN score INT)
BEGIN
     CASE
     WHEN score >=90 AND score <=100 THEN SELECT 'a';
     WHEN score >=80 THEN SELECT 'c';
     ELSE SELECT 'd';
     END CASE;


END $
CALL test_case(3);

5.2.3 if 结构

语法

if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
【else 语句n;】
end if;
应用在begin end 中


	DELIMITER $
	CREATE FUNCTION if_fun(score INT) RETURNS CHAR
	BEGIN 
		IF  score >=90 AND score <=100 THEN RETURN 'A';
		ELSEIF score>=80 THEN RETURN 'b';
		ELSE RETURN 'f';
		END IF;
	END $
	
	SELECT if_fun(50);

4.3 循环结构

分类
while 、 loop 、 repeat

循环控制
iterate类似于continue, 继续，结束本次循环，继续下一次
leave 类似于break, 跳出， 结束当前所在的循环

4.3.1 while

 语法：
   while 循环条件 do
   		 循环体；
   end while 【标签】
   联想：

   while(循环条件)｛
		循环体；
｝

案例

DROP PROCEDURE while_test ;
DELIMITER $

CREATE PROCEDURE while_test(IN a INT)

BEGIN
	DECLARE i INT DEFAULT 1;
	
	WHILE i<= a DO
	    INSERT INTO zq_one(two,three) VALUES(CONCAT('12d',i),'three');
	    SET i = i+1;
	  END WHILE;
	  COMMIT;
END $

SELECT  * FROM zq_one;

4.3.2 loop

 语法：
 	【标签：】 loop
 	       循环体
 	  end loop 【标签】
 	  可以用来模拟简单的死循环 

4.4.3 repeat

语法：
 【标签： 】 repeat
           循环体；
         until 结束循环的条件
         end repeat 【标签】；

4.4.4 leave 关键字（相当于 java break）

DELIMITER $$

USE `school`$$

DROP PROCEDURE IF EXISTS `while_leave_test`$$

CREATE DEFINER=`zq`@`%` PROCEDURE `while_leave_test`(IN abc INT)
BEGIN
	DECLARE a INT DEFAULT 0;
	a: WHILE(a<=abc) DO
	INSERT INTO zq_one(two, three) VALUES(CONCAT('a==', a),'three');
	IF a>= 20 THEN LEAVE a;  # 大于20 ，就中断此次循环
	END IF;
	SET a = a+1;
	END WHILE a;
	COMMIT;
END$
DELIMITER ;

4.4.5 iterate （相当于java 中的 continue）

案例

奇数时才插入

DELIMITER $$

USE `school`$$

DROP PROCEDURE IF EXISTS `while_leave_test_two`$$

CREATE DEFINER=`zq`@`%` PROCEDURE `while_leave_test_two`(IN i INT)
BEGIN 

	DECLARE  a INT DEFAULT 0;
	a:WHILE  a <i DO
	  
	  SET a = a+1;
	  IF a%2=0 THEN  ITERATE a;
	  END IF;
	   INSERT INTO zq_one(two, three) VALUES(CONCAT('i==',a), 'three');
	  END WHILE a;
	  COMMIT;
	
END$$

DELIMITER ;

4.4.6 案例

插入随机数

DROP PROCEDURE while_test_three;
DELIMITER $
CREATE PROCEDURE while_test_three(IN insertCount INT) 
BEGIN
	
	DECLARE i INT DEFAULT 0;
	DECLARE str VARCHAR(100);
	DECLARE len INT DEFAULT 0;
	DECLARE startIndex INT DEFAULT 0;

	DECLARE  stra VARCHAR(200) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
	
	 WHILE i<=insertCount DO
	SET len = FLOOR(RAND()*(20-startIndex+1)+1);
	SET startIndex = FLOOR(RAND()*26+1);
	
	SET str = SUBSTR(stra,startIndex, len);
	INSERT INTO zq_one(two, three) VALUES(str,str);
	
	SET i = i+1;
	END WHILE ;
	COMMIT;
END $