mysql中函数的应用.pptVIP

函数,1,mysql中函数的应用,什么是函数,2,函数是事先经过编译并存储在数据库中的一段sql语句的集合 调用函数可以简化应用开发人员的很多工作 减少数据在数据库和应用服务器之间的传输 对于提高数据处理的效率是有好处的 函数必须有返回值,函数基本结构,3,说明: mysql函数的语法比较严格，说明如下: 1, 定义分割符 防止遇到”;”语句结束 2, 创建函数名 注意myfun() 不要忘记 () 3, 函数返回类型 只能返回mysql 列所具有 的数据类型，注意: 是returns 不是return 4, 函数主体开始 注意：以上4句后面都没有分号 5, 所执行的业务 注意这句后面有分号 6,此函数的返回值 要和3定义的类型匹配 后面有分号结束 7,函数主题结束 后面有分割符 8,函数结束 注意后面有分号,没有参数的函数,调用自定义函数:和调用普通的函数一样,如: select myfun(); 会返回一个字符串,函数基本结构,4,说明: 1: 所接受的参数,有参数的函数,DELIMITER $ CREATE FUNCTION myfun( str VARCHAR(20) RETURNS VARCHAR(20) BEGIN RETURN str ; END $ DELIMITER ;,-1,变量的定义,5,说明: 通过DECLARE可以定义一个局部变量,该变量的作用范围只能在GEGINEND块中,可以用在嵌套的块中.变量的定义必须写在复合语句的开头,并且在任何其他语句的前面,如果需要可以使用DEFAULT赋默认值 例如:DECLARE munum INT DEFAULT 10;,定义变量,BEGIN DECLARE temp VARCHAR(20); END,变量的赋值,6,说明: 通过DECLARE可以定义一个局部变量,该变量的作用范围只能在GEGINEND块中,可以用在嵌套的块中.变量的定义必须写在复合语句的开头,并且在任何其他语句的前面,如果需要可以使用DEFAULT赋默认值 例如:DECLARE munum INT DEFAULT 10;,变量赋值,BEGIN DECLARE temp VARCHAR(20); SET temp = hello world; END,使用SET赋值,案例分析1-1,7,问题:,自定义函数完成类似REPLACE()函数的功能,案例分析1-2,8,分析: 函数需要3个参数: 1,原始字符串 2,源字符串 3,目标字符串,案例分析1-3,9,DELIMITER $ CREATE FUNCTION myreplace(str VARCHAR(50),ystr VARCHAR(50),mstr VARCHAR(50) RETURNS VARCHAR(50) BEGIN RETURN REPLACE(str,ystr,mstr); END $ DELIMITER ;,案例分析2-1,10,大家知道mysql中字符串转int会发生自动类型转换 例如: select 123+0 返回int 那么如何将int转为字符串呢? 自定义函数完成.,案例分析2-2,11,可以利用concat()函数将数值和空字符串连接出来,DELIMITER $ CREATE FUNCTION inttostring(temp INT) RETURNS VARCHAR(50) BEGIN RETURN CONCAT(temp,); END $ DELIMITER ;,测试:,SELECT inttostring(123),存储过程,12,mysql中存储过程的应用,什么是存储过程,13,定义： 将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来, 那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时,只需调用存储过程,即可自动完成命令,14,存储过程 - - -,单个 SELECT 语句,SELECT 语句块,SELECT语句与逻辑控制语句,可以包含,什么是存储过程,存储过程可以包含数据操纵语句、变量、逻辑 控制语句等,什么是存储过程,15,存储过程的优点： 1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。 2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。 3.存储过程可以重复使用,可减少数据库开发人员的工作量,存储过程的基本结构,16,DELIMITER $ CREATE PROCEDURE myprotest() BEGIN SELECT * FROM stuinfo; END $ DELIMITER ;,和函数的语法很像,如何调用存储过程? 使用CALL 关键字,CALL myprotest();,sql编程-局部变量示例,17,问题： 编写SQL查找李文才的左右同桌 ？,学员信息表,分析： 第一步，找出“李文才“的座位号； 第二步，李文才的座位号加1或减1,局部变量示例,参考语句,18,DELIMITER $ DROP PROCEDURE IF EXISTS myprot $ CREATE PROCEDURE myprot() BEGIN DECLARE mname VARCHAR(10); DECLARE seat INT; SET mname = 李文才; SET seat = (SELECT stuSeat FROM stuInfo WHERE stuName=mname ); SELECT * FROM stuinfo WHERE (stuSeat = seat+1) OR (stuSeat = seat-1); END $ DELIMITER ;,演示：使用局部变量,逻辑控制语句,IFELSE语句,19,MySQL中的IF-ELSE语句 IF(条件1) THEN 语句1； ELSEIF(条件2) THEN 语句2; ELSE 语句3 END IF ;,ELSE是可选部分,逻辑控制语句,示例,20,DELIMITER $ DROP PROCEDURE IF EXISTS myifelse $ CREATE PROCEDURE myifelse() BEGIN DECLARE temp INT; SET temp = 1; IF (temp = 10 AND temp = 5) THEN SELECT 中等; ELSE SELECT 差; END IF ; END$ DELIMITER ;,IF-ELSE示例,21,问题： 统计并显示本班笔试平均分，如果平均分在70以上，显示“成绩优秀“，并显示前三名学员的考试信息；如果在70以下，显示“本班成绩较差“，并显示后三名学员的考试信息 。,学员成绩表,分析： 第一步，统计平均成绩存入临时变量； 第二步，用IFELSE判断；,IF-ELSE示例,22,参考代码:,DELIMITER $ DROP PROCEDURE IF EXISTS myproifelse $ CREATE PROCEDURE myproifelse() BEGIN DECLARE temp FLOAT; SET temp = (SELECT AVG(writtenExam) FROM stumarks); IF (temp 70) THEN SELECT * FROM stumarks ORDER BY writtenExam DESC LIMIT 0,3; ELSE SELECT * FROM stumarks ORDER BY writtenExam ASC LIMIT 0,3; END IF ; END $ DELIMITER ;,逻辑控制语句while循环,WHILE循环语句,23,MySQL中的WHILE语句 WHILE (条件) DO 语句 END WHILE;,循环体中要控制条件 避免死循环,逻辑控制语句while循环,WHILE循环语句,24,MySQL中的WHILE语句 WHILE (条件) DO 语句 END WHILE;,循环体中要控制条件 避免死循环,逻辑控制语句while循环,WHILE示例,25,DELIMITER $ DROP PROCEDURE IF EXISTS mywhile $ CREATE PROCEDURE mywhile() BEGIN DECLARE t INT; SET t = 1; WHILE(t 15) DO SET t = t + 1; END WHILE; SELECT t; END$ DELIMITER ;,WHILE示例,26,问题： 本次考试成绩较差，假定要提分，确保每人笔试都通过。提分规则很简单，先每人都加2分，看是否都通过，如果没有全部通过，每人再加2分，再看是否都通过，如此反复提分，直到所有人都通过为止 。,学员成绩表,分析： 第一步，统计没通过的人数 ； 第二步，如果有人没通过，加分； 第三步，循环判断。,逻辑控制语句while循环,参考代码逻辑,27,DELIMITER $ DROP PROCEDURE IF EXISTS myf $ CREATE PROCEDURE myf() BEGIN DECLARE a INT DEFAULT 1; DECLARE t INT DEFAULT 1; WHILE(a 10) THEN SET a = 6; END IF ; END WHILE; SELECT t; END$ DELIMITER ;,逻辑控制语句while循环,参考代码,28,DELIMITER $ DROP PROCEDURE IF EXISTS myfa $ CREATE PROCEDURE my