mysql中函数的应用.ppt

函数,1,mysql中函数的应用,什么是函数,2,函数是事先经过编译并存储在数据库中的一段sql语句的集合调用函数可以简化应用开发人员的很多工作减少数据在数据库和应用服务器之间的传输对于提高数据处理的效率是有好处的函数必须有返回值,函数基本结构,3,说明:mysql函数的语法比较严格，说明如下:1,定义分割符防止遇到”;”语句结束2,创建函数名注意myfun()不要忘记()3,函数返回类型只能返回mysql列所具有的数据类型，注意:是returns不是return4,函数主体开始注意：以上4句后面都没有分号5,所执行的业务注意这句后面有分号6,此函数的返回值要和3定义的类型匹配后面有分号结束7,函数主题结束后面有分割符8,函数结束注意后面有分号,没有参数的函数,调用自定义函数:和调用普通的函数一样,如:selectmyfun();会返回一个字符串,函数基本结构,4,说明:1:所接受的参数,有参数的函数,DELIMITER$CREATEFUNCTIONmyfun(strVARCHAR(20)RETURNSVARCHAR(20)BEGINRETURNstr;END$DELIMITER;,-1,变量的定义,5,说明:通过DECLARE可以定义一个局部变量,该变量的作用范围只能在GEGINEND块中,可以用在嵌套的块中.变量的定义必须写在复合语句的开头,并且在任何其他语句的前面,如果需要可以使用DEFAULT赋默认值例如:DECLAREmunumINTDEFAULT10;,定义变量,BEGINDECLAREtempVARCHAR(20);END,变量的赋值,6,说明:通过DECLARE可以定义一个局部变量,该变量的作用范围只能在GEGINEND块中,可以用在嵌套的块中.变量的定义必须写在复合语句的开头,并且在任何其他语句的前面,如果需要可以使用DEFAULT赋默认值例如:DECLAREmunumINTDEFAULT10;,变量赋值,BEGINDECLAREtempVARCHAR(20);SETtemp=helloworld;END,使用SET赋值,案例分析1-1,7,问题:,自定义函数完成类似REPLACE()函数的功能,案例分析1-2,8,分析:函数需要3个参数:1,原始字符串2,源字符串3,目标字符串,案例分析1-3,9,DELIMITER$CREATEFUNCTIONmyreplace(strVARCHAR(50),ystrVARCHAR(50),mstrVARCHAR(50)RETURNSVARCHAR(50)BEGINRETURNREPLACE(str,ystr,mstr);END$DELIMITER;,案例分析2-1,10,大家知道mysql中字符串转int会发生自动类型转换例如:select123+0返回int那么如何将int转为字符串呢?自定义函数完成.,案例分析2-2,11,可以利用concat()函数将数值和空字符串连接出来,DELIMITER$CREATEFUNCTIONinttostring(tempINT)RETURNSVARCHAR(50)BEGINRETURNCONCAT(temp,);END$DELIMITER;,测试:,SELECTinttostring(123),存储过程,12,mysql中存储过程的应用,什么是存储过程,13,定义：将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来,那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时,只需调用存储过程,即可自动完成命令,14,存储过程-,单个SELECT语句,SELECT语句块,SELECT语句与逻辑控制语句,可以包含,什么是存储过程,存储过程可以包含数据操纵语句、变量、逻辑控制语句等,什么是存储过程,15,存储过程的优点：1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。3.存储过程可以重复使用,可减少数据库开发人员的工作量,存储过程的基本结构,16,DELIMITER$CREATEPROCEDUREmyprotest()BEGINSELECT*FROMstuinfo;END$DELIMITER;,和函数的语法很像,如何调用存储过程?使用CALL关键字,CALLmyprotest();,sql编程-局部变量示例,17,问题：编写SQL查找李文才的左右同桌？,学员信息表,分析：第一步，找出“李文才“的座位号；第二步，李文才的座位号加1或减1,局部变量示例,参考语句,18,DELIMITER$DROPPROCEDUREIFEXISTSmyprot$CREATEPROCEDUREmyprot()BEGINDECLAREmnameVARCHAR(10);DECLAREseatINT;SETmname=李文才;SETseat=(SELECTstuSeatFROMstuInfoWHEREstuName=mname);SELECT*FROMstuinfoWHERE(stuSeat=seat+1)OR(stuSeat=seat-1);END$DELIMITER;,演示：使用局部变量,逻辑控制语句,IFELSE语句,19,MySQL中的IF-ELSE语句IF(条件1)THEN语句1；ELSEIF(条件2)THEN语句2;ELSE语句3ENDIF;,ELSE是可选部分,逻辑控制语句,示例,20,DELIMITER$DROPPROCEDUREIFEXISTSmyifelse$CREATEPROCEDUREmyifelse()BEGINDECLAREtempINT;SETtemp=1;IF(temp=10ANDtemp=5)THENSELECT中等;ELSESELECT差;ENDIF;END$DELIMITER;,IF-ELSE示例,21,问题：统计并显示本班笔试平均分，如果平均分在70以上，显示“成绩优秀“，并显示前三名学员的考试信息；如果在70以下，显示“本班成绩较差“，并显示后三名学员的考试信息。,学员成绩表,分析：第一步，统计平均成绩存入临时变量；第二步，用IFELSE判断；,IF-ELSE示例,22,参考代码:,DELIMITER$DROPPROCEDUREIFEXISTSmyproifelse$CREATEPROCEDUREmyproifelse()BEGINDECLAREtempFLOAT;SETtemp=(SELECTAVG(writtenExam)FROMstumarks);IF(temp70)THENSELECT*FROMstumarksORDERBYwrittenExamDESCLIMIT0,3;ELSESELECT*FROMstumarksORDERBYwrittenExamASCLIMIT0,3;ENDIF;END$DELIMITER;,逻辑控制语句while循环,WHILE循环语句,23,MySQL中的WHILE语句WHILE(条件)DO语句ENDWHILE;,循环体中要控制条件避免死循环,逻辑控制语句while循环,WHILE循环语句,24,MySQL中的WHILE语句WHILE(条件)DO语句ENDWHILE;,循环体中要控制条件避免死循环,逻辑控制语句while循环,WHILE示例,25,DELIMITER$DROPPROCEDUREIFEXISTSmywhile$CREATEPROCEDUREmywhile()BEGINDECLAREtINT;SETt=1;WHILE(t15)DOSETt=t+1;ENDWHILE;SELECTt;END$DELIMITER;,WHILE示例,26,问题：本次考试成绩较差，假定要提分，确保每人笔试都通过。提分规则很简单，先每人都加2分，看是否都通过，如果没有全部通过，每人再加2分，再看是否都通过，如此反复提分，直到所有人都通过为止。,学员成绩表,分析：第一步，统计没通过的人数；第二步，如果有人没通过，加分；第三步，循环判断。,逻辑控制语句while循环,参考代码逻辑,27,DELIMITER$DROPPROCEDUREIFEXISTSmyf$CREATEPROCEDUREmyf()BEGINDECLAREaINTDEFAULT1;DECLAREtINTDEFAULT1;WHILE(a10)THENSETa=6;ENDIF;ENDWHILE;SELECTt;END$DELIMITER;,逻辑控制语句while循环,参考代码,28,DELIMITER$DROPPROCEDUREIFEXISTSmyfa$C