一、視圖
視圖是查詢命令結果構成的一個虛擬表（非真實存在），其本質是【根據SQL語句獲取動態的數據集，併為其命名】，用戶使用時只需使用【名稱】即可獲取結果集合，並可以當作表來查詢使用。
1、創建視圖
--格式：CREATE VIEW 視圖名稱 AS SQL語句
CREATE VIEW v1 AS
SELET nid,
name
FROM
A
WHERE
nid > 4
2、刪除視圖
--格式：DROP VIEW 視圖名稱
DROP VIEW v1
3、修改視圖
-- 格式：ALTER VIEW 視圖名稱 AS SQL語句
ALTER VIEW v1 AS
SELET A.nid,
B. NAME
FROM
A
LEFT JOIN B ON A.id = B.nid
LEFT JOIN C ON A.id = C.nid
WHERE
A.id > 2
AND C.nid < 5
4、使用視圖
視圖的使用和普通表一樣，由於視圖是虛擬表，所以無法對其真實表進行創建、更新和刪除操作，僅做查詢用。
select * from v1
二、觸發器
對某個表進行【增/刪/改】操作的前後觸發一些操作即為觸發器，如果希望觸發增刪改的行為之前或之後做操作時，可以使用觸發器，觸發器用於自定義用戶對錶的行進行【增/刪/改】前後的行為。
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
2、創建觸發器
創建觸發器基本語法是以下code：
但有一點要注意，觸發器內關鍵字NEW表示即將插入的數據行，OLD表示即將刪除的數據行。
# 插入前觸發器
delimiter //
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
IF NEW. NAME == 'nick' THEN
INSERT INTO tb2 (NAME)
VALUES
('aa')
END
END//
delimiter ;
# 插入後觸發器
delimiter //
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
IF NEW. num = 666 THEN
INSERT INTO tb2 (NAME)
VALUES
('666'),
('666') ;
ELSEIF NEW. num = 555 THEN
INSERT INTO tb2 (NAME)
VALUES
('555'),
('555') ;
END IF;
END//
delimiter ;
3、刪除觸發器
DROP TRIGGER tri_after_insert_tb1;
4、使用觸發器
觸發器是由於對錶的增、刪、改操作時被動執行的
insert into tb1(num) values(666)
三、存儲過程
存儲過程是一個SQL語句集合，類似函數，需要主動調用。
1、創建存儲過程
# 無參數存儲過程
# 創建存儲過程
delimiter //
create procedure p1()
BEGIN
select * from t1;
END//
delimiter ;
# 執行存儲過程
call p1()
都說了類似函數，那必須得可以接收參數，且參數有三類：
- in 僅用於傳入參數用
- out 僅用於返回值用
- inout 既可以傳入又可以當作返回值
# 有參數存儲過程
# 創建存儲過程
delimiter \\ # 結尾分號改為\\
create procedure p1(
in i1 int,
in i2 int,
inout i3 int,
out r1 int
)
BEGIN
DECLARE temp1 int; # 創建申明局部變量
DECLARE temp2 int default 0;
set temp1 = 1;
set r1 = i1 + i2 + temp1 + temp2;
set i3 = i3 + 100;
end\\
delimiter ;
# 執行存儲過程
DECLARE @t1 INT default 3;
DECLARE @t2 INT;
CALL p1 (1, 2 ,@t1, @t2);
SELECT @t1,@t2;
2、刪除存儲過程
drop procedure proc_name;
3、執行存儲過程
執行為函數名加括號；
DECLARE代表創建一個局部變量
# 無參數
call proc_name()
# 有參數，全in
call proc_name(1,2)
# 有參數，有in，out，inout
DECLARE @t1 INT;
DECLARE @t2 INT default 3;
call proc_name(1,2,@t1,@t2)
代碼實例：
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 執行存儲過程
cursor.callproc('p1', args=(1, 22, 3, 4))
# 獲取執行完存儲的參數
cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
result = cursor.fetchall()
conn.commit()
cursor.close()
conn.close()
print(result)
分享到:
閱讀更多 im程序猿 的文章
