一、視圖

視圖是一個虛擬表并不是（正實存在的）

創建老師表create table teacher( id int primary key auto_increment, tname varchar(10) );創建課程表 create table course( id int primary key auto_increment, cname varchar(10), teacher_id int, foreign key(teacher_id) references teacher(id) on delete cascade on update cascade );插入數據 insert into teacher(tname)values ('張磊老師'), ('李平老師'), ('劉海燕老師'), ('朱云海老師'), ('李杰老師');insert into course(cname,teacher_id)values ('生物',1), ('物理',2), ('體育',3), ('美術',2);

兩張有關系的表

#查詢李平老師教授的課程名
#子查詢出臨時表，作為teacher_id等判斷依據 select tid from teacher where tname='李平老師' （1）創建視圖
#語法：CREATE VIEW 視圖名稱 AS SQL語句

#于是查詢李平老師教授的課程名的sql可以改寫為


#！！！注意注意注意： #1. 使用視圖以后就無需每次都重寫子查詢的sql，但是這么效率并不高，還不如我們寫子查詢的效率高 #2. 而且有一個致命的問題：視圖是存放到數據庫里的，如果我們程序中的sql過分依賴于數據庫中存放的視圖，
那么意味著，一旦sql需要修改且涉及到視圖的部分，則必須去數據庫中進行修改，而通常在公司中數據庫有專門的DBA負責，
你要想完成修改，必須付出大量的溝通成本DBA可能才會幫你完成修改，極其地不方便
（2）、使用視圖
#修改視圖，原始表也跟著改

（3）修改視圖

語法：ALTER VIEW 視圖名稱 AS SQL語句

（4）刪除視圖

語法：DROP VIEW 視圖名稱 DROP VIEW teacher_view 二、觸發器
使用觸發器可以定制用戶對表進行【增、刪、改】操作時前后的行為，注意：沒有查詢
（1）創建觸發器
# 插入前 CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW BEGIN... END# 插入后 CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW BEGIN... END# 刪除前 CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW BEGIN... END# 刪除后 CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW BEGIN... END# 更新前 CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW BEGIN... END# 更新后 CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW BEGIN... END

?

#準備表 CREATE TABLE cmd (id INT PRIMARY KEY auto_increment,USER CHAR (32),priv CHAR (10),cmd CHAR (64),sub_time datetime, #提交時間success enum ('yes', 'no') #0代表執行失敗 );CREATE TABLE errlog (id INT PRIMARY KEY auto_increment,err_cmd CHAR (64),err_time datetime );#創建觸發器 delimiter // CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW BEGINIF NEW.success = 'no' THEN #等值判斷只有一個等號INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; #必須加分號END IF ; #必須加分號 END// delimiter ;#往表cmd中插入記錄，觸發觸發器，根據IF的條件決定是否插入錯誤日志 INSERT INTO cmd (USER,priv,cmd,sub_time,success ) VALUES('egon','0755','ls -l /etc',NOW(),'yes'),('egon','0755','cat /etc/passwd',NOW(),'no'),('egon','0755','useradd xxx',NOW(),'no'),('egon','0755','ps aux',NOW(),'yes'); #查詢錯誤日志，發現有兩條

特別的：NEW表示即將插入的數據行，OLD表示即將刪除的數據行。

（2）、 使用觸發器

觸發器無法由用戶直接調用，而知由于對表的【增/刪/改】操作被動引發的。

（3）、 刪除觸發器

drop trigger tri_after_insert_cmd;

三、事務

事務用于將某些操作的多個SQL作為原子性操作，一旦有某一個出現錯誤，

即可回滾到原來的狀態，從而保證數據庫數據完整性。

create table user( id int primary key auto_increment, name char(32), balance int );insert into user(name,balance) values ('wsb',1000), ('egon',1000), ('ysb',1000);#原子操作 start transaction; update user set balance=900 where name='wsb'; #買支付100元 update user set balance=1010 where name='egon'; #中介拿走10元 update user set balance=1090 where name='ysb'; #賣家拿到90元 commit;#出現異常，回滾到初始狀態 start transaction; update user set balance=900 where name='wsb'; #買支付100元 update user set balance=1010 where name='egon'; #中介拿走10元 uppdate user set balance=1090 where name='ysb'; #賣家拿到90元,出現異常沒有拿到 rollback; commit;

四、存儲過程

存儲過程包含了一系列可執行的sql語句，存儲過程存放于MySQL中，

通過調用它的名字可以執行其內部的一堆sql

使用存儲過程的優點
#1 程序與數據實現解耦
#2 減少網絡傳輸的數據量
#=============================================== #創建無參存儲過程 delimiter // create procedure p3() beginselect * from user;insert into user(name,balance) values('wsb1',2); end // delimiter ;#調用存儲過程 call p3(); #在mysql中調用

cursor.callproc('p3') #在python中通過pymysql模塊調用#=============================================== #創建有參存儲過程之in的使用 delimiter // create procedure p4(in m int,in n int ) beginselect * from user where id between m and n; end // delimiter ;#調用存儲過程 call p4(3,7); #在mysql中調用
cursor.callproc('p4',args=(3,7)) #在python中通過pymysql模塊調用#=============================================== #創建有參存儲過程之out的使用 delimiter // create procedure p5(in m int,in n int,out res int ) beginselect * from user where id between m and n;set res=1; end // delimiter ;#調用存儲過程 #在mysql中 set @x=11111111111 call p5(3,7,@x); #在mysql中調用, 查看結果：select @x;
#在python中 res=cursor.callproc('p5',args=(3,7,123)) #@_p3_0=3,@_p3_1=7,@_p3_2=123 print(cursor.fetchall()) #只是拿到存儲過程中select的查詢結果 cursor.execute('select @_p5_0,@_p5_1,@_p5_2') print(cursor.fetchall()) #可以拿到的是返回值#=============================================== #創建有參存儲過程之inout的使用delimiter // create procedure p6(inout m int ) beginselect * from user where id > m;set m=1; end // delimiter ;#在mysql中 set @x=2; call p6(@x); select @x;
delimiter // create procedure p8(inout m int ) beginselect * from user111 where id > m;set m=1; end // delimiter ;set @x=2; call p8(@x); select @x;#====================捕捉異常+事務=========================== delimiter // create PROCEDURE p9(OUT p_return_code tinyint ) BEGINDECLARE exit handler for sqlexceptionBEGIN-- ERRORset p_return_code = 1;rollback;END;DECLARE exit handler for sqlwarningBEGIN-- WARNINGset p_return_code = 2;rollback;END;START TRANSACTION;insert into user(name,balance) values('egon',1);DELETE from tb1111111; #執行失敗COMMIT;-- SUCCESSset p_return_code = 0; #0代表執行成功END // delimiter ;#用python模擬 try:START TRANSACTION;DELETE from tb3; #執行失敗insert into blog(name,sub_time) values('yyy',now());COMMIT;set p_return_code = 0; #0代表執行成功 except sqlexception:set p_return_code = 1;rollback; except sqlwaring:set p_return_code = 2;rollback;mysql> show procedure status like 'p3%'; #查看某一類存儲過程

?五、流程控制

?

#函數中不要寫sql語句，它僅僅只是一個功能，是一個在sql中被應用的功能
#若要想在begin...end...中寫sql，請用存儲過程
#while循環 delimiter // CREATE PROCEDURE proc_while () BEGINDECLARE num INT ;SET num = 0 ;WHILE num < 10 DOSELECTnum ;SET num = num + 1 ;END WHILE ;END // delimiter ;

?

?

?

?

?

?

?

?

?

?

?

? ?

?

? ?

?

?

?

轉載于:https://www.cnblogs.com/mengqingjian/p/7543808.html

《新程序員》：云原生和全面數字化實踐50位技術專家共同創作，文字、視頻、音頻交互閱讀

總結

以上是生活随笔為你收集整理的mysql:视图，触发器，事务，存储过程，函数的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。