""" 視圖： 兩張有關系的表 course表：cid?? cname?? teacher_id(teacher表中的tid) teacher表：tid? tname """ #查詢tom教授的課程名 select cname from course where teacher_id = (select tid from teacher where tname='tom'); #創建視圖 create view teacher_view as select tid from teacher where tname='tom'; #于是查詢tom教授的課程名的sql可以改寫為 select cname from course where teacher_id = (select tid from teacher_view); #更新視圖中的數據 update teacher_view set tname='xxx'; #往視圖中插入數據 insert into teacher_view values('yyy'); #修改視圖，原始表也跟著改,我們不應該修改視圖中的記錄，而且在涉及多個表的情況下是根本無法修改視圖中的記錄的 #修改視圖 alter view teacher_view as select * from course where cid>3; #刪除視圖 DROP VIEW teacher_view
""" 特別的：NEW表示即將插入的數據行，OLD表示即將刪除的數據行。 觸發器無法由用戶直接調用，而由表的【增/刪/改】操作被動引發的。 """ #插入后觸發觸發器 #準備表 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 BEGIN ????IF 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 ????('tom','0755','ls -l /etc',NOW(),'yes'), ????('tom','0755','cat /etc/passwd',NOW(),'no'), ????('tom','0755','useradd xxx',NOW(),'no'), ????('tom','0755','ps aux',NOW(),'yes'); #查詢錯誤日志，發現有兩條 mysql> select * from errlog; +----+-----------------+---------------------+ | id | err_cmd???????? | err_time??????????? | +----+-----------------+---------------------+ |? 1 | cat /etc/passwd | 2017-09-14 22:18:48 | |? 2 | useradd xxx???? | 2017-09-14 22:18:48 | +----+-----------------+---------------------+ 2 rows in set (0.00 sec) #刪除觸發器： drop trigger tri_after_insert_cmd; # 插入前 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 user( id int primary key auto_increment, name char(32), balance int ); insert into user(name,balance) values ('tom',1000), ('jack',1000), ('rose',1000); #原子操作 start transaction; update user set balance=900 where name='rose'; #買支付100元 update user set balance=1010 where name='tom'; #中介拿走10元 update user set balance=1090 where name='jack'; #賣家拿到90元 commit; #出現異常，回滾到初始狀態 start transaction; update user set balance=900 where name='rose'; #買支付100元 update user set balance=1010 where name='tom'; #中介拿走10元 uppdate user set balance=1090 where name='jack'; #賣家拿到90元,出現異常沒有拿到 rollback; commit; mysql> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ |? 1 | tom? |??? 1000 | |? 2 | jack |??? 1000 | |? 3 | rose? |??? 1000 | +----+------+---------+ 3 rows in set (0.00 sec)
delimiter // create procedure p1() BEGIN ????select * from blog; ????INSERT into blog(name,sub_time) values("xxx",now()); END // delimiter ; #查看存儲過程 show create procedure p1\G ?? #在mysql中調用 call p1() ?? #在python中基于pymysql調用 cursor.callproc('p1') print(cursor.fetchall())
二、創建存儲過程（有參）
對于存儲過程，可以接收參數，其參數有三類：
#in 僅用于傳入參數用
#out 僅用于返回值用
#inout 既可以傳入又可以當作返回值
1、in:傳入參數
?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
delimiter // create procedure p2( ????in n1 int, ????in n2 int ) BEGIN ????? ????select * from blog where id > n1; END // delimiter ; #在mysql中調用 call p2(3,2) #在python中基于pymysql調用 cursor.callproc('p2',(3,2)) print(cursor.fetchall())
#out：返回值 delimiter // create procedure p3( ????in n1 int, ????out res int ) BEGIN ????select * from blog where id > n1; ????set res = 1; END // delimiter ; #在mysql中調用 set @res=0; #0代表假（執行失敗），1代表真（執行成功） call p3(3,@res); select @res; #在python中基于pymysql調用 cursor.callproc('p3',(3,0)) #0相當于set @res=0 print(cursor.fetchall()) #查詢select的查詢結果 cursor.execute('select @_p3_0,@_p3_1;') #@p3_0代表第一個參數，@p3_1代表第二個參數，即返回值 print(cursor.fetchall())
""" 自定義函數: 函數中不要寫sql語句（否則會報錯），函數僅僅只是一個功能，是一個在sql中被應用的功能 若要想在begin...end...中寫sql，請用存儲過程 """ delimiter // create function f1( ????i1 int, ????i2 int) returns int BEGIN ????declare num int; ????set num = i1 + i2; ????return(num); END // delimiter ; ### delimiter // create function f5( ????i int ) returns int begin ????declare res int default 0; ????if i = 10 then ????????set res=100; ????elseif i = 20 then ????????set res=200; ????elseif i = 30 then ????????set res=300; ????else ????????set res=400; ????end if; ????return res; end // delimiter ; #刪除函數 drop function func_name; #執行函數 # 獲取返回值 select UPPER('egon') into @res; SELECT @res; # 在查詢中使用 select f1(11,nid) ,name from tb2;
流程控制
一、條件語句
?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
delimiter // CREATE PROCEDURE proc_if () BEGIN ????? ????declare i int default 0; ????if i = 1 THEN ????????SELECT 1; ????ELSEIF i = 2 THEN ????????SELECT 2; ????ELSE ????????SELECT 7; ????END IF; END // delimiter ;
#while循環 delimiter // CREATE PROCEDURE proc_while () BEGIN ?? ????DECLARE num INT ; ????SET num = 0 ; ????WHILE num < 10 DO ????????SELECT ????????????num ; ????????SET num = num + 1 ; ????END WHILE ; ?? END // delimiter ; ?? ?? #repeat循環 delimiter // CREATE PROCEDURE proc_repeat () BEGIN ?? ????DECLARE i INT ; ????SET i = 0 ; ????repeat ????????select i; ????????set i = i + 1; ????????until i >= 5 ????end repeat; ?? END // delimiter ; #Loop BEGIN ????? ????declare i int default 0; ????loop_label: loop ????????? ????????set i=i+1; ????????if i<8 then ????????????iterate loop_label; ????????end if; ????????if i>=10 then ????????????leave loop_label; ????????end if; ????????select i; ????end loop loop_label; END
