MySQL入门很简单－学习笔记

mm111222

避免编写重复的语句
　　安全性可控
　　执行效率高
　　
14.1、创建存储过程和函数
14.1.1、创建存储过程
　　CREATE PROCEDUREsp_name ([proc_parameter[,...]])
　　[characteristic...] routine_body
　　
　　procedure 发音 [prə'si:dʒə]
　　
　　proc_parameter  IN|OUT|INOUT param_name type
　　characteristic  n. 特征；特性；特色
　　LANGUAGESQL  默认，routine_boyd由SQL组成
　　[NOT]DETERMINISTIC 指明存储过程的执行结果是否是确定的，默认不确定
　　CONSTAINSSQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA指定程序使用SQL语句的限制
　　CONSTAINS SQL 子程序包含SQL，但不包含读写数据的语句，默认
　　NO SQL   子程序中不包含SQL语句
　　READS SQL DATA  子程序中包含读数据的语句
　　MODIFIES SQL DATA 子程序中包含了写数据的语句
　　SQLSECURITY {DEFINER|INVOKER}，指明谁有权限执行。
　　DEFINER，只有定义者自己才能够执行，默认
　　INVOKER 表示调用者可以执行
　　COMMENT‘string’ 注释信息
　　
　　CREATEPROCEDURE num_from_employee (IN emp_id, INT, OUT count_num INT)
　　 READS SQL DATA
　　 BEGIN
　　  SELECTCOUNT(*) INTOcount_num
　　  FROMemployee
　　  WHEREd_id=emp_id;
　　 END
14.1.2、创建存储函数
　　CREATE FUNCTIONsp_name ([func_parameter[,...]])
　　RETURNS type
　　[characteristic...] routine_body
　　CREATEFUNCTION name_from_employee(emp_id INT)
　　 RETURNSVARCHAR(20)
　　 BEGIN
　　  RETURN (SELECT name FROM employee WHEREnum=emp_id);
　　 END
14.1.3、变量的使用
1．定义变量
　　DECLARE var_name[,…]type [DEFAULT value]
　　
　　DECLAREmy_sql INT DEFAULT 10;
　　
2．为变量赋值
　　SETvar_name=expr[,var_name=expr]…
　　
　　SELECT col_name[,…]INTO var_name[,…] FROM table_name WHERE condition
　　
14.1.4、定义条件和处理程序
1．定义条件
　　DECLARE condition_nameCONDITION FOR condition_value
　　condition value:
　　SQLSTATE[VALUE] sqlstate_value | mysql_error_code
　　
　　对于ERROR 1146(42S02)
　　sqlstate_value: 42S02
　　mysql_error_code:1146
　　//方法一
　　DECLARE can_not_find CONDITION FOR SQLSTATE ‘42S02’
　　//方法二
　　DECLARE can_not_find CONDITION FOR 1146
　　
2．定义处理程序
　　DECLAREhander_type HANDLER FOR condition_value[,…] sp_statement
　　
　　handler_type:
　　CONTINUE|EXIT|UNDO
　　condition_value:
　　SQLSTATE[VALUE] sqlstate_value | condition_name |SQLWARNING|NOTFOUND|SQLEXCEPTION|mysql_error_code
　　
　　UNDO目前MySQL不支持
　　
　　1、捕获sqlstate_value
　　DECLARE CONTINUE HANDLER FOR SQLSTATE ‘42S02’ SET @info=’CANNOT FIND’;
　　2、捕获mysql_error_code
　　DECLARE CONTINUE HANDLER FOR 1146 SET @info=’CAN NOT FIND’;
　　3、先定义条件，然后调用
　　DECLARE can_not_find CONDITION FOR 1146;
　　DECLARE CONTINUE HANDLER FOR can_not_find SET @info=’CANNOT FIND’;
　　4、使用SQLWARNING
　　DECLARE EXITHANDLER FOR SQLWARNING SET @info=’CANNOT FIND’;
　　5、使用NOT FOUND
　　DECLARE EXIT HANDLER FOR NOT FOUND SET @info=’CANNOT FIND’;
　　6、使用SQLEXCEPTION
　　DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info=’CANNOT FIND’;
　　
14.1.5、光标的使用
　　存储过程中对多条记录处理，使用光标
1．声明光标
　　DECLAREcousor_name COURSOR FOR select statement;
　　
　　DECLAREcur_employee CURSOR FOR SELECT name, age FROM employee;
　　
2．打开光标
　　OPENcursor_name;
　　
　　OPENcur_employee;
　　
3．使用光标
　　FETCHcur_employee INTO var_name[,var_name…];
　　
　　FETCH cur_employeeINTO emp_name, emp_age;
　　
4．关闭光标
　　CLOSEcursor_name
　　
　　CLOSE cur_employee
14.1.6、流程控制的使用
1．IF语句
　　IFsearch_condition THEN statement_list
　　[ELSEIF search_condition THENstatement_list]…
　　[ELSE statement_list]
　　END IF
　　
　　IF age>20THEN SET @count1=@count1+1;
　　 ELSEIFage=20 THEN @count2=@count2+1;
　　 ELSE @count3=@count3+1;
　　END
　　
2．CASE语句
　　CASE case_value
　　WHEN when_value THEN statement_list
　　[WHEN when_value THEN statement_list]…
　　[ELSE statement_list]
　　END CASE
　　
　　CASE
　　WHEN search_condition THENstatement_list
　　[WHEN search_condition THENstatement_list]…
　　[ELSE statement_list]
　　END CASE
　　
　　CASE age
　　 WHEN 20 THEN SET @count1=@count1+1;
　　 ELSE SET @count2=@count2+1;
　　END CASE;
　　
　　CASE
　　 WHERE age=20 THEN SET@count1=@count1+1;
　　 ELSE SET @count2=@count2+1;
　　END CASE;
　　
3．LOOP语句
　　[begin_label:]LOOP
　　statement_list
　　ENDLOOP[end_label]
　　
　　add_num:LOOP
　　 SET @count=@count+1;
　　END LOOPadd_num;
　　
4．LEAVE语句
　　跳出循环控制
　　LEAVE label
　　
　　add_num:LOOP
　　SET @count=@count+1;
　　LEAVE add_num;
　　END LOOPadd_num;
　　
5．ITERATE语句
　　跳出本次循环，执行下一次循环
　　ITERATE label
　　
　　add_num:LOOP
　　 SET @count=@count+1;
　　 IF @count=100 THEN LEAVE add_num;
　　 ELSEIF MOD(@count,3)=0 THEN ITERATEadd_num;
　　 SELECT * FROM employee;
　　END LOOPadd_num;
　　
6．REPEAT语句
　　有条件循环，满足条件退出循环
　　[begin_label:]REPEAT
　　statement_list
　　UNTIL search_condition
　　ENDREPEAT[end_label]
　　
　　REPEAT
　　 SET @count=@count+1;
　　 UNTIL @count=100;
　　ENDREPEAT;
　　
7．WHILE语句
　　[begin_label:]WHILEsearch_condition DO
　　statement_list
　　ENDREPEAT[end_label]
　　
　　WHILE@count<100 DO
　　 SET @count=@count+1;
　　ENDWHILE;
14.2、调用存储过程和函数
　　存储过程是通过CALL语句来调用的。而存储函数的使用方法与MySQL内部函数的使用方法是一样的。执行存储过程和存储函数需要拥有EXECUTE权限。EXECUTE权限的信息存储在information_schema数据库下面的USER_PRIVILEGES表中
14.2.1、调用存储过程
　　CALL sp_name([parameter[,…]]) ;
　　
14.2.2、调用存储函数
　　存储函数的使用方法与MySQL内部函数的使用方法是一样的
　　
14.3、查看存储过程和函数
　　SHOW { PROCEDURE| FUNCTION } STATUS [ LIKE ' pattern ' ];
　　SHOW CREATE {PROCEDURE | FUNCTION } sp_name ;
　　SELECT * FROMinformation_schema.Routines WHERE ROUTINE_NAME=' sp_name ' ;
　　
14.4、修改存储过程和函数
　　ALTER {PROCEDURE| FUNCTION} sp_name [characteristic ...]
　　characteristic:
　　{ CONTAINS SQL |NO SQL | READS SQL DATA | MODIFIES SQL DATA }
　　| SQL SECURITY {DEFINER | INVOKER }
　　| COMMENT'string'
　　
14.5、删除存储过程和函数
　　DROP {PROCEDURE| FUNCTION } sp_name;