#字符串函数 #字符串拼接 SELECT CONCAT('中国','必胜') AS str #字符串截取 SELECTLEFT('hello',2) AS str SELECTRIGHT('hello',2) AS str SELECTSUBSTRING('hello',2) AS str SELECTSUBSTRING('hello',2,2) AS str #案例 SET@STR='ABCDEFG'; SELECTLEFT(@STR,2),RIGHT(@STR,3),SUBSTRING(@STR,2,4); #字符串格式化 SELECT FORMAT(RAND()*100,2) AS num
#声明一个整型变量和两个字符变量。 #局部变量只能在BEGIN…END语句块中声明 DECLARE num INT(4); DECLARE str1, str2 VARCHAR(6); #局部变量赋值 SET num=1, str1='hello';
案例
1 2 3 4 5 6 7 8 9 10 11 12 13 14
#在存储过程体中将Book表中的书名为“计算机应用基础”的作者姓名和出版社的值 #分别赋给变量name和publish,并输出出版社变量值 DELIMITER $$ CREATEPROCEDURE sp_vtest (OUT p VARCHAR(20)) BEGIN DECLARE v_name,v_publish VARCHAR(20); SELECT author,publisher INTO v_name, v_publish FROM Book WHERE bookname='计算机应用基础'; SET p = v_publish; END $$ DELIMITER ;
CALL sp_vtest(@p); SELECT@p;
3-2-2 语句结构
3-2-2-1 判断结构赏析
案例1-两数相比
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
DELIMITER $$ CREATEPROCEDURE sp_compare (IN K1 INTEGER, IN K2 INTEGER, OUT K3 CHAR(6) ) BEGIN IF K1>K2 THEN SET K3='大于'; ELSEIF K1=K2 THEN SET K3='等于'; ELSE SET K3='小于'; END IF; END $$ DELIMITER ;
CALL sp_compare(3, 6, @K); SELECT@K;
案例2-
形态1
1 2 3 4 5 6 7 8 9 10 11 12 13 14
DELIMITER $$ CREATEPROCEDURE sp_updown1(IN str VARCHAR(1), OUT direct VARCHAR(4) ) BEGIN CASE str WHEN'U'THENSET direct ='上升'; WHEN'D'THENSET direct ='下降'; ELSESET direct ='不变'; ENDCASE; END $$ DELIMITER;
CALL sp_updown1('s',@d); SELECT@d;
形态2
1 2 3 4 5 6 7 8 9 10 11 12 13 14
DELIMITER $$ CREATEPROCEDURE sp_updown2(IN str VARCHAR(1), OUT direct VARCHAR(4) ) BEGIN CASE WHEN str ='U'THENSET direct ='上升'; WHEN str ='D'THENSET direct ='下降'; ELSESET direct ='不变'; ENDCASE; END $$ DELIMITER;
CALL sp_updown2('s',@d); SELECT@d;
3-2-2-2 循环结构
案例1- WHILE …DO
1 2 3 4 5 6 7 8 9 10 11 12 13 14
#WHILE ...DO循环结构 DELIMITER $$ CREATEPROCEDURE sp_while() BEGIN DECLARE v1 INTDEFAULT5; WHILE v1 >0 DO SET v1 = v1-1; SELECT v1; END WHILE; END $$ DELIMITER ;
CALL sp_while()
案例2-REPEAT…UNTIL - 类似do…while结构
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
#REPEAT...UNTIL - 类似do...while结构 DELIMITER $$ CREATEPROCEDURE sp_repeat() BEGIN DECLARE v1 INTDEFAULT5; REPEAT SET v1=v1-1; SELECT v1; UNTIL v1<1 END REPEAT; END $$ DELIMITER ;
CALL sp_repeat();
案例3 数据库存储结构独特结构:LOOP…LEAVE 类似 for…break…continue
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
#数据库存储结构独特结构:LOOP...LEAVE 类似 for...break...continue DELIMITER $$ CREATEPROCEDURE sp_sum(IN n INT) BEGIN DECLARE v_sum INTDEFAULT0; #label是做一个标识,写法是必须在后面带冒号“label:”方便后面写leave,iterate; label:LOOP IF n <=0THEN LEAVE label; END IF; SET v_sum = v_sum + n; SET n = n -1; END LOOP label; SELECT v_sum; END $$ DELIMITER ;
#数据库存储结构独特结构:LOOP...LEAVE 类似 for...break...continue DELIMITER $$ CREATEPROCEDURE sp_sum(IN n INT) BEGIN DECLARE v_sum INTDEFAULT0; #label是做一个标识,写法是必须在后面带冒号“label:”方便后面写leave,iterate; label:LOOP IF n <=0THEN LEAVE label; END IF; IF n%2=1THEN SET n = n-1; ITERATE label; END IF; SET v_sum = v_sum + n; SET n = n -1; END LOOP label; SELECT v_sum; END $$ DELIMITER ;
#根据用户姓名和书名查询订单,如果订购册数小于5本不打折, #订购册数在5-10本之间,订购单价打九折,订购册数大于10本,订购单价打八折。 DELIMITER $$ CREATEPROCEDURE sp_updateSellPrice(IN m_name CHAR(10), IN b_name VARCHAR(40)) BEGIN DECLARE v_bookcode CHAR(20); DECLARE v_mcode CHAR(10); DECLARE v_amount TINYINT; SELECT mcode INTO v_mcode FROMmember WHERE NAME=m_name; SELECT bookcode INTO v_bookcode FROM Book WHERE bookname=b_name; SELECT amount INTO v_amount FROM sell WHERE mcode=v_mcode AND bookcode=v_bookcode; SELECT v_bookcode,v_mcode,v_amount;
IF v_amount>=5AND v_amount<=10THEN UPDATE sell SET price=price*0.9 WHERE mcode=v_mcode AND bookcode=v_bookcode; ELSEIF v_amount>10THEN UPDATE sell SET price=price*0.8 WHERE mcode=v_mcode AND bookcode=v_bookcode;
END IF; END$$ DELIMITER ;
3-2-4 中级考试案例
案例1:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
DELIMITER // CREATEPROCEDURE insertProc(IN p INT) BEGIN DECLARE var INT; SET var = p+1; CASE var WHEN0THEN INSERTINTO B VALUES(20); WHEN1THEN INSERTINTO B VALUES(21); ELSE INSERTINTO B VALUES(22); ENDCASE; END; // DELIMITER;
DELIMITER // CREATEPROCEDURE proc() BEGIN DECLARE var INT; SET var =0; WHILE var <4 DO INSERTINTO B VALUES(var); SET var = var +1; END WHILE; END; // DELIMITER; #关于上述存储过程,执行insert语句几次?
4 存储函数
创建
参考代码1
1 2 3 4 5 6 7 8
#创建一个存储函数,它返回Book表中图书数目作为结果。 DELIMITER $$ CREATEFUNCTION fun_bookCount() RETURNSINTEGER BEGIN RETURN (SELECTCOUNT(*) FROM Book); END$$ DELIMITER ;
参考代码2
1 2 3 4 5 6 7 8 9 10 11
#创建一个存储函数,返回Book表中某本书的作者姓名。 DELIMITER $$ CREATEFUNCTION fun_bookAuthor(b_name VARCHAR(40)) RETURNSCHAR(10) BEGIN DECLARE res_author CHAR(10); SELECT author INTO res_author FROM book WHERE bookname= b_name; RETURN res_author; END$$ DELIMITER ;
#当删除表book表中某图书的信息时,同时将sell表中与该图书有关的数据全部删除 DELIMITER $$ CREATETRIGGER t_book_del AFTER DELETE ON Book FOREACHROW BEGIN DELETEFROM sell WHERE bookcode=old.bookcode; END$$ DELIMITER ;
#现在验证一下触发器的功能: DELETEFROM book WHERE bookcode ='TP23/6625';
#使用SELECT语句查看Sell表中的情况: SELECT*FROM sell WHERE bookcode ='TP23/6625';