mysql高级操作小结（存储过程，视图，游标，存储函数，触发器，事务）

/***

数据库存储过程控制流程篇

**
delimiter $$createprocedurehell{o}_{p}rocedure()beginselec{t}^{\prime }hell{o}^{\prime };end$$
call hello_procedure();
delimiter $$createprocedures{p}_{v}ar()begindeclareva{r}_{n}amevarchar(32)defaul{t}^{\prime }S{F}^{\prime };setva{r}_{n}ame{=}^{\prime }U{G}^{\prime };selectva{r}_{n}ame;end$$
call sp_var()
delimiter $$createproceduretes{t}_{A}ll()beginset@se{t}_{a}ll{=}^{\prime }12^{\prime };end$$
call test_All()$$select@se{t}_{a}ll$$
DELIMITER $$CREATEPROCEDUREtes{t}_{A}ll2()BEGINselectu.nameinto@tes{t}_{a}ll2fromuseruwhereu.id=2;END$$
CALL test_All2()$$SELECT@tes{t}_{a}ll2$$
DELIMITER $$CREATEPROCEDUREtes{t}_{l}oop()BEGINdeclarenumintdefault0;myloop:loopselectnum;ifnum>9thenleavemyloop;endif;setnum=num+1;endloopmyloop;END$$
CALL test_loop()$$/
/
loop 和 leave(相当于break) iterate(相当于continue) 搭配使用
repeat和 until 搭配使用
while 和do搭配使用
case语句和when then搭配使用，在begin end中需要加上case
cursor 游标，指定结果集，相当于（resttype），必须在局部变量声明后面
游标使用过程：声明 打开 获取数据 关闭
handle句柄声明，必须在游标声明后面

DELIMITER $$CREATEPROCEDUREtes{t}_{i}terate()BEGINdeclarenumintdefault0;DECLAREstrvarchar(256)DEFAUL{T}^{\prime }{0}^{\prime };myloop:loopsetnum=num+1;setstr=concat(str{,}^{\prime }{,}^{\prime },num);ifnum<10theniteratemyloop;endif;leavemyloop;endloopmyloop;selectstr;END$$
CALL test_iterate()$$\ast /DELIMITER$$
SELECT bookCounts,#在查询中使用
CASE bookCounts
WHEN 3 THEN ‘书的数量较少’
WHEN 20 THEN ‘书的数量充足’
ELSE ‘其它’
END
FROM book
#在存储函数中使用
DELIMITER $$
CREATE PROCEDURE test_case(IN num INT)
BEGIN

   CASE num
      WHEN 3 THEN SELECT'书的数量较少';
      WHEN 20 THEN SELECT'书的数量充足';
      ELSE SELECT '其它';
   END CASE;

ENDKaTeX parse error: Expected 'EOF', got '#' at position 22: …test_case(20); #̲case的第二种用法，类似于多…
CREATE PROCEDURE test_case1(IN num INT)
BEGIN
CASE
WHEN num<5 THEN SELECT’书的数量较少’;
WHEN num BETWEEN 5 AND 19 THEN SELECT’书的数量适中’;
WHEN num>19 THEN SELECT’书的数量充足’;
ELSE SELECT’其它’;
END CASE;
END$$
CALL test_case1(10);

#**

游标篇

**
DELIMITER $$CREATEPROCEDUREcurso{r}_{t}est()BEGINDECLAREnumINTDEFAULT0;DECLARErINTDEFAULT0;DECLAREmycursorCURSORFORSELECTbookCountsFROMbook;OPENmycursor;REPEATSETr=r+1;FETCHmycursorINTOnum;SELECTnum;UNTILr>2ENDREPEAT;CLOSEmycursor;END$$
CALL cursor_test()
/*
delimiter $$createproceduretes{t}_{d}o()begindeclarenumintdefault0;declarestrvarchar(256)defaul{t}^{\prime }{0}^{\prime };whilenum<10dosetnum=num+1;setstr=concat(str{,}^{\prime }{,}^{\prime },num);endwhile;selectstr;end$$
call test_do()$$\ast //\ast delimiter$$

create procedure show_book()
begin
declare b_name varchar(100);
declare b_count int(11);
declare b_flag boolean default true;

 declare b_cursor cursor for
         select bookName,bookCounts from book;
 DECLARE CONTINUE HANDLER FOR 1329 SET b_flag=FALSE;创建handler 句柄，必须声明在游标后面，当错误码为1329时，将b_flag设置为false然后继续执行
 
 open b_cursor;
 myloop:loop
    fetch b_cursor into b_name,b_count;
    if b_flag then
      select b_name,b_count;
    else
      leave myloop;
    end if;
    end Loop myloop;
 close b_cursor;

end$$callsho{w}_{b}ook()$$/
/ 创建下个月每天的表*/
/*select Last_day(Date_add(now(),interval 1 month));
select Year(LAST_DAY(DATE_ADD(NOW(),INTERVAL 1 MONTH)));
SELECT month(LAST_DAY(DATE_ADD(NOW(),INTERVAL 1 MONTH)));
SELECT dayofmonth(LAST_DAY(DATE_ADD(NOW(),INTERVAL 1 MONTH)));

delimiter KaTeX parse error: Expected group after '_' at position 767: …cat(year_next,'_̲',month_str,'_'…
delimiter $$createprocedureinser{t}_{t}est(in@bookNameVARCHAR(100),in@bookCountsINT(11),in@detailVARCHAR(200))begininsertintobook(bookName,bookCounts,detail)values(@bookName,@bookCounts,@detail);end$$
*/
#--------------------

视图篇

CREATE VIEW myview
AS SELECT * FROM book;
SELECT FROM myview;
#试图张可添加非表结构中的字段，如下
CREATE VIEW myview1
AS SELECT ,‘12345’ FROM book
SELECT * FROM myview1
#可根据聚合统计的sql创建试图
CREATE VIEW myview2
AS SELECT bookID,bookName,SUM(bookCounts) FROM book GROUP BY bookID,bookName;
SELECT * FROM myview2
#创建多表查询的试图,注意视图的字段民不要重复
CREATE VIEW myview3
AS SELECT b.,c. FROM book b RIGHT JOIN classification c ON b.bookID=c.id
SELECT * FROM myview3
CREATE VIEW myview4
AS SELECT b.bookID,c.id FROM book b ,classification c
SELECT * FROM myview4
#基于视图创建视图
CREATE VIEW view_test AS
SELECT * FROM myview2
SELECT * FROM view_test

SHOW TABLES
DESC myview#查看视图结构
SHOW TABLE STATUS LIKE ‘myview’#查看视图信息(存储引擎，版本，数据行等)
SHOW CREATE VIEW myview#查看视图详细定义信息
#视图的修改
CREATE OR REPLACE VIEW myview2
AS SELECT *FROM book WHERE bookID=1

ALTER VIEW myview2
AS SELECT *FROM book WHERE bookID=2
#删除视图
DROP VIEW IF EXISTS myview2

/*

存储函数篇

*/

DELIMITER $$CREATEFUNCTIONtes{t}_1()RETURNSVARCHAR(30)BEGINRETURN(SELECTbookNameFROMbookWHEREbookID=1);END$$

SELECT test_1()

DELIMITER $$CREATEFUNCTIONtes{t}_2(IDINT)RETURNSVARCHAR(30)BEGINRETURN(SELECTbookNameFROMbookWHEREbookID=ID);END$$
SELECT test_2(1)
DROP FUNCTION test_2

DELIMITER $$CREATEFUNCTIONtes{t}_3()RETURNSINTBEGINRETURN(SELECTSUM(bookCounts)FROMbook);END$$
SELECT test_3()

SHOW PROCEDURE STATUS LIKE’test_%';
SHOW FUNCTION STATUS LIKE ‘test_%’;

/*

触发器篇

*/
DELIMITER KaTeX parse error: Expected 'EOF', got '#' at position 26: …IGGER mytrigger#̲创建触发器 AFTER INS…
INSERT INTO book(bookName,bookCounts,detail) VALUES(‘c#’,10,‘这个真垃圾’);
SELECT *FROM book
SELECT *FROM classification WHERE cname=‘书本’
#查看所有触发器
SHOW TRIGGERS
#查看自定义触发器
SHOW CREATE TRIGGER mytrigger
DROP TRIGGER IF EXISTS mytrigger
/*异常处理篇,类似于句柄，上面用过，
continue :遇到错误不处理继续执行
exit：遇到错误退出
undo：遇到错误撤回之前的操作，mysql中暂不支持这种操作

*/
DECLARE EXIT HANDLER FOR myerro SET @info=‘该值不存在’;

SELECT @info
SELECT *FROM book WHERE bookID=;

DELIMITER $$
CREATE PROCEDURE test_condition()
BEGIN

#DECLARE myerro CONDITION FOR 1054;#为1054错误码定义异常名myerro，方便处理
DECLARE EXIT HANDLER FOR 1054 SET @info=‘没有该列’;
UPDATE book SET books=1 WHERE bookID=1;
SELECT @info;
END$$
CALL test_condition()
SELECT @info

事务篇

#查看事务状态,mysql中事务默认为自动提交，回滚是在事务提交前回滚的，
#故使用回滚和提交时，先将自动提交改为手动提交
SHOW VARIABLES LIKE ‘%autocommit%’
#将事务设置为手动，1的话代表自动提交
SET autocommit=1;
SELECT* FROM book
UPDATE book SET bookCounts=bookCounts+20 WHERE bookID=1;
UPDATE book SET bookCounts=bookCounts-10 WHERE bookID=1;
UPDATE book SET bookCounts=bookCounts-10 WHERE bookID=1;
ROLLBACK