/*1.根据生日计算年龄的函数*/ DELIMITER // DROP FUNCTION IF EXISTS getAage;// CREATE FUNCTION getAage(birth DATETIME) RETURNS INT BEGIN DECLARE age INT; DECLARE temp INT; SET temp=DATE_FORMAT(NOW(),'%m-%d')-DATE_FORMAT(birth,'%m-%d'); IF temp>=0 THEN SET age=DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(birth,'%Y'); ELSE SET age=DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(birth,'%Y')-1; END IF; RETURN age; END; // SELECT getAage('87-04-05'); //23 /*2.计算1到n的函数*/ /*(1).使用loop...end loop循环来实现*/ DELIMITER // DROP FUNCTION IF EXISTS total;// CREATE FUNCTION total(n INT) RETURNS INT BEGIN DECLARE result INT DEFAULT 0; loop_a:LOOP SET result=result+n; SET n=n-1; IF n=0 THEN LEAVE loop_a; END IF; END LOOP loop_a; RETURN result; END; // /*(2).使用while...do...end while循环来实现*/ DELIMITER // DROP FUNCTION IF EXISTS total;// CREATE FUNCTION total(n INT) RETURNS INT BEGIN DECLARE result INT DEFAULT 0; WHILE n>0 DO SET result=result+n; SET n=n-1; END WHILE; RETURN result; END; // SELECT total(100); /*(3).使用repeat...util...end repeat循环来实现*/ DELIMITER // DROP FUNCTION IF EXISTS total;// CREATE FUNCTION total(n INT) RETURNS INT BEGIN DECLARE result INT DEFAULT 0; REPEAT SET result=result+n; SET n=n-1; UNTIL n=0 END REPEAT; RETURN result; END; // SELECT total(100); /*3.使用游标和创建异常处理语句存储过程实例*/ DELIMITER // DROP PROCEDURE IF EXISTS cur_example;// CREATE PROCEDURE cur_example() BEGIN DECLARE temp INT; DECLARE done INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; OPEN cur; loop_a:LOOP FETCH cur INTO temp; SELECT temp; IF done=1 THEN LEAVE loop_a; END IF; END LOOP loop_a; CLOSE cur; END; // CALL cur_example(); /*4.如果表不存在就去创建表*/ CREATE TABLE IF NOT EXISTS b(b INT); /*5.MySQL相较于存储过程,对于存储函数有更严格的规则。函数必须声明不修改SQL(使用NO SQL或者READS SQL DATA子句) 或者声明为DETERMINISTIC(如果服务器被允许开启二进制日志)。这个限制是为了防止当函数返回不确定值时,数据库同步复制的不一致性, 我们的样例例程使用了“deterministic”,这样我们就能确保在提供了相同的参数的情况下返回相同的值*/ /*6.触发器*/ 触发器是一种用来相应数据库事件是自动回调的存储程序,在MySQL5的实现中,触发器将在特定表的DML (数据库操纵语言)激活时被回调,触发器可以用来自动计算引用值或者格式化值。下面的例子展示了用于维护引用值的触发器; 当员工salary的值被改变是,contrib_401K列将被自动修改为特定值。 CREATE TRIGGER employees_trg_bu BEFORE UPDATE ON employees FOR EACH ROW BEGIN IF NEW.salary <50000 THEN SET NEW.contrib_401K=500; ELSE SET NEW.contrib_401K=500+(NEW.salary-50000)*.01; END IF; END 练习: DELIMITER // DROP TRIGGER IF EXISTS a_trigger;// CREATE TRIGGER a_trigger BEFORE INSERT ON t FOR EACH ROW BEGIN UPDATE t SET s1=123456 ; END; // INSERT INTO t VALUES(33); /*7.mysql数据类型声明演示*/ DECLARE l_int1 INT DEFAULT -2000000; DECLARE l_int2 INT UNSIGNED DEFAULT 4000000; DECLARE l_bigint1 BIGINT DEFAULT 4000000000000000; DECLARE l_float FLOAT DEFAULT 1.8e8; DECLARE l_double DOUBLE DEFAULT 2e45; DECLARE l_numeric NUMERIC(8,2) DEFAULT 9.95; DECLARE l_date DATE DEFAULT '1999-12-31'; DECLARE l_datetime DATETIME DEFAULT '1999-12-31 23:59:59'; DECLARE l_char CHAR(255) DEFAULT 'This will be padded to 255 chars'; DECLARE l_varchar VARCHAR(255) DEFAULT 'This will not be padded'; DECLARE l_text TEXT DEFAULT 'This is a really long string. In stored programs we can use text columns fairly freely, but in tables there are some limitations regarding indexing and use in various expressions.'; /*8.逻辑语句实践的存储过程例子*/ DELIMITER // DROP FUNCTION IF EXISTS f_title;// CREATE FUNCTION f_title(in_gender CHAR(1), in_age INT, in_marital_status VARCHAR(7)) RETURNS VARCHAR(6) BEGIN DECLARE title VARCHAR(6); IF in_gender='F' AND in_age<16 THEN SET title='Miss'; ELSEIF in_gender='F' AND in_age>=16 AND in_marital_status='Married' THEN SET title='Mrs'; ELSEIF in_gender='F' AND in_age>=16 AND in_marital_status='Single' THEN SET title='Ms'; ELSEIF in_gender='M' AND in_age<16 THEN SET title='Master'; ELSEIF in_gender='M' AND in_age>=16 THEN SET title='Mr'; END IF; RETURN(title); END; // SELECT f_title('M',24,'Single'); /*9.经常被使用的MySQL函数*/ 函数 描述 ABS(number) 返回提供数字的绝对值。比方说, ABS(-2.3)=2.3. CEILING(number) 返回下一个最大整数,比方说, CEILING(2.3)=3. CONCAT(string1[,string2,string3,...]) 返回所有提供字符串的连接形式的值 CURDATE() 返回当前时间(不带秒) SELECT CURDATE(); DATE_ADD(DATE,INTERVAL amount_type) 给提供的时间值加上一个时间间隔并返回一个新时间。正确的形式有SECOND, MINUTE, HOUR, DAY, MONTH和YEAR DATE_SUB(DATE,INTERVAL interval_type) 从提供的时间值上减去一个时间间隔并返回一个新的时间。正确的形式有SECOND, MINUTE, HOUR, DAY, MONTH和YEAR FORMAT(number,decimals) 返回一个指定精确度的数值,并给与以1000为单位的分割(通常使用“,”) GREATEST(num1,num2[,num3, ... ]) 返回所有提供参数中的最大数 IF(test, value1,value2) 测试一个逻辑条件,如果为真则返回value1,如果为假返回value2,例如 SELECT IF(2>1,'123','322')返回123 IFNULL(VALUE,value2) 返回第一个值,除非第一个值为空;这样的话返回第二个值 INSERT(STRING,POSITION,LENGTH,NEW) 把一个字符串插入到另一个字符串中 INSTR(STRING,SUBSTRING) 返回一个字符串中子串的位置 ISNULL(expression) 如果参数为空则返回1,否则返回0 LEAST(num1,num2[,num3, ... ]) 返回参数列表中的最小值 LEFT(STRING,LENGTH) 返回字符串最左边的部分 LENGTH(STRING) 返回字符串中的字节数。CHAR_LENGTH可以被用来返回字符数(这会在你使用多字节字符集是产生差异) LOCATE(SUBSTRING,STRING[,number]) 返回字符串中子串的位置,可选的第三个参数为在父字符串中开始搜索的位置 LOWER(STRING) 返回给定字符串的小写形式 LPAD(STRING,LENGTH,padding) 返回字符串 str, 其左边由字符串padding 填补到LENGTH 字符长度,第三个参数为填充字符 LTRIM(STRING) 删除所有字符串中的前缀空格 MOD(num1,num2) 返回第一个数除于第二个数后的模(余数部分) NOW() 返回当前日期和时间 POWER(num1,num2) 返回num1的num2次方 RAND([seed]) 返回一个随机数。seed可被用于随机数生成器的种子数 REPEAT(STRING,number) 返回一个重复number次STRING的字符串 REPLACE(STRING,OLD,NEW) 用NEW替换所有出现OLD的地方 ROUND(number[,DECIMAL]) 舍去给定数值的指定精度的位数 RPAD(STRING,LENGTH,padding) 返回字符串 str, 其右边由字符串padding 填补到LENGTH 字符长度,第三个参数为填充字符 RTRIM(STRING) 删除字符串尾部的空格 SIGN(number) 如果number小于0则返回-1,如果大于0则返回1,如果为0则返回0 SQRT(number) 返回number的平方根 STRCMP(string1,string2) 如果两个值相同则返回0,若根据当前分类次序,第一个参数小于第二个,则返回 -1,其它情况返回 1 。 SUBSTRING(STRING,POSITION,LENGTH) 从字符串指定位置开始返回LENGTH个字符 UPPER(STRING) 将指定字符串转换为大写 VERSION 返回MySQL服务器当前版本号的字符串 /*在SET和IF子句中使用函数的例子*/ DELIMITER // DROP FUNCTION IF EXISTS function_example;// CREATE PROCEDURE function_example( ) BEGIN DECLARE TwentyYearsAgoToday DATE; DECLARE mystring VARCHAR(250); SET TwentyYearsAgoToday=DATE_SUB(CURDATE( ), INTERVAL 20 YEAR); SET mystring=CONCAT('It was ',TwentyYearsAgoToday, ' Sgt Pepper taught the band to play...'); SELECT mystring; IF (CAST(SUBSTR(VERSION( ),1,3) AS DECIMAL(2,1)) <5.0) THEN SELECT 'MySQL versions earlier than 5.0 cannot run stored programs - you must be hallucinating'; ELSE SELECT 'Thank goodness you are running 5.0 or higher!'; END IF; END; // CALL function_example(); /*10.使用DECLARE语句来定义游标*/ 它的语法如下: DECLARE cursor_name CURSOR FOR SELECT_statement; 游标的声明必须在我们所有的变量声明之后。在我们的变量之前定义游标会产生一个1337错误 /*11展示了在命令行客户端下使用预处理语句的例子*/ PREPARE prod_insert_stmt FROM "INSERT INTO product_codes VALUES(?,?)"; SET @CODE='QB'; SET @NAME='MySQL Query Browser'; EXECUTE prod_insert_stmt USING @CODE,@NAME; SET @CODE='AD'; SET @NAME='MySQL Administrator'; EXECUTE prod_insert_stmt USING @CODE,@NAME /*12.可以更新任何表中列值的存储过程*/ CREATE PROCEDURE set_col_value (in_table VARCHAR(128), in_column VARCHAR(128), in_new_value VARCHAR(1000), in_where VARCHAR(4000)) BEGIN DECLARE l_sql VARCHAR(4000); SET l_sql=CONCAT_WS(' ', 'UPDATE',in_table,'SET',in_column,'=',in_new_value, ' WHERE',in_where); SET @SQL=l_sql; PREPARE s1 FROM @SQL; EXECUTE s1; DEALLOCATE PREPARE s1; END; CALL set_col_value('employees','salary','0','employee_id=1') /*13.使用动态SQL的搜索过程*/ CREATE PROCEDURE sp_customer_search (in_customer_name VARCHAR(30), in_contact_surname VARCHAR(30), in_contact_firstname VARCHAR(30), in_phoneno VARCHAR(10)) BEGIN SELECT * FROM customers WHERE (customer_name LIKE in_customer_name OR in_customer_name IS NULL) AND (contact_surname LIKE in_contact_surname OR in_contact_surname IS NULL) AND (contact_firstname LIKE in_contact_firstname OR in_contact_firstname IS NULL) AND (phoneno LIKE in_phoneno OR in_phoneno IS NULL) ; END; /*14.使用动态SQL的搜索过程*/ CREATE PROCEDURE sp_customer_search_dyn (in_customer_name VARCHAR(30), in_contact_surname VARCHAR(30),in_contact_firstname VARCHAR(30), in_phoneno VARCHAR(10)) BEGIN DECLARE l_where_clause VARCHAR(1000) DEFAULT 'WHERE'; IF in_customer_name IS NOT NULL THEN SET l_where_clause=CONCAT(l_where_clause, ' customer_name="',in_customer_name,'"'); END IF; IF in_contact_surname IS NOT NULL THEN IF l_where_clause<>'WHERE' THEN SET l_where_clause=CONCAT(l_where_clause,' AND '); END IF; SET l_where_clause=CONCAT(l_where_clause, ' contact_surname="',in_contact_surname,'"'); END IF; IF in_contact_firstname IS NOT NULL THEN IF l_where_clause<>'WHERE' THEN SET l_where_clause=CONCAT(l_where_clause,' AND '); END IF; SET l_where_clause=CONCAT(l_where_clause, ' contact_firstname="',in_contact_firstname,'"'); END IF; IF in_phoneno IS NOT NULL THEN IF l_where_clause<>'WHERE' THEN SET l_where_clause=CONCAT(l_where_clause,' AND '); END IF; SET l_where_clause=CONCAT(l_where_clause, ' phoneno="',in_phoneno,'"'); END IF; SET @SQL=CONCAT('SELECT * FROM customers ', l_where_clause); PREPARE s1 FROM @SQL; EXECUTE s1; DEALLOCATE PREPARE s1; END;