Mysql視圖、觸發器、事務、儲存過程、函數

一、視圖

什麼是視圖

視圖是有一張表或多張表的查詢結果構成的一張虛擬表

為什麼使用視圖

當我們在使用多表查詢時 我們的sql語句可能會非常的複雜,如果每次都編寫一遍sql'的話無疑是一件麻煩的事情,這時候就可以使用視圖來避免多次編寫sql的問題;

簡答的說可以幫我們節省sql的編寫,

視圖的另一個作用是,可以不同的視圖來展示開放不同數據的訪問

例如,同一張工資表,老闆可以查看全部,部門主管可以查看該部門所有人,員工只能看自己的一條記錄

使用方法

創建視圖

CREATE [OR REPLACE] VIEW view_name [(column_list)]
AS select_statement

加上OR REPLACE 時如果已經存在相同視圖則替換原有視圖

column_list指定哪些字段要出現在視圖中

注意:由於是一張虛擬表,視圖中的數據實際上來源於其他其他表,所以在視圖中的數據不會出現在硬盤上

使用視圖

視圖是一張虛擬表 所以使用方式與普通表沒有任何區別

查看視圖

1.desc view_name; //查看數據結構

2.show create view view_name;//查看 創建語句

修改視圖

alter view_name select_statement

刪除視圖

drop view view_name

案例1: 簡化多表sql語句

#準備數據
create database db02 charset utf8;
use db02 

create table student(
 s_id int(3),
 name varchar(20),
 math float,
 chinese float 
);
insert into student values(1,'tom',80,70),(2,'jack',80,80),(3,'rose',60,75);
create table stu_info(
 s_id int(3),
 class varchar(50),
 addr varchar(100)
);
insert into stu_info values(1,'二班','安徽'),(2,'二班','湖南'),(3,'三班','黑龍江');

​

#創建視圖包含 編號 學生的姓名 和班級
create view stu_v (編號,姓名,班級) as 
select student.s_id,student.name ,stu_info.class
from student,stu_info where student.s_id=stu_info.s_id;
# 查看視圖中的數據
select *from stu_v; 

案例2: 隔離數據

# 創建工資表
create table salarys(
id int primary key,
name char(10),
salary double,
dept char(10)
);
insert into salarys values
(1,"劉強東",900000,"市場"),
(2,"馬雲",800090,"市場"),
(3,"李彥宏",989090,"財務"),
(4,"馬化騰",87879999,"財務");

​

# 創建市場部視圖
create view dept_sc as select *from salarys where dept = "市場";
# 查看市場部視圖
select *from dept_sc;

注意 對視圖數據的insert update delete 會同步到原表中,但由於視圖可能是部分字段,很多時候會失敗

總結:mysql可以分擔程序中的部分邏輯,但這樣一來後續的維護會變得更麻煩

如果需要改表結構,那意味著視圖也需要相應的修改,沒有直接在程序中修改sql來的方便

二、觸發器

什麼是觸發器

觸發器是一段與表有關的mysql程序 當這個表在某個時間點發生了某種事件時 將會自動執行相應的觸發器程序

何時使用觸發器

當我們想要在一個表記錄被更新時做一些操作時就可以使用觸發器

但是我們完全可以在python中來完成這個事情,因為python的擴展性更強,語法更簡單

創建觸發器

語法:
CREATE TRIGGER t_name t_time t_event ON table_name FOR EACH ROW
begin
stmts.....
end 

支持的時間點(t_time):時間發生前和發生前後 before | after

支持的事件(t_event): update insert delete

在觸發器中可以訪問到將被修改的那一行數據 根據事件不同 能訪問也不同 update 可用OLD訪問舊數據 NEW訪問新數據 insert 可用NEW訪問新數據 delete 可用OLD訪問舊數據

可以將NEW和OLD看做一個對象其中封裝了這列數據的所有字段

案例:

有cmd表和錯誤日誌表,需求:在cmd執行失敗時自動將信息存儲到錯誤日誌表中

#準備數據
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 trigger1 after insert on cmd for each row
begin
if new.success = "no" then
 insert into errlog values(null,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');
# 查看錯誤日誌表中的記錄是否有自動插入
select *from errlog;

delimiter 用於修改默認的行結束符 ,由於在觸發器中有多條sql語句他們需要使用分號來結束,但是觸發器是一個整體,所以我們需要先更換默認的結束符,在觸發器編寫完後在將結束符設置回分號
 

注意:

外鍵不能觸發事件 主表刪除了某個主鍵 從表也會相應刪除 但是並不會執行觸發器 觸發器中不能使用事務 相同時間點的相同事件的觸發器 不能同時存在

刪除觸發器

語法:
drop trigger trigger_name;
案例:
drop trigger trigger1; 

同樣的這種需求我們完全可以在python中來完成! mysql最想完成的事情是講所有能處理的邏輯全部放到mysql中,那樣一來應用程序開發者的活兒就變少了,相應的數據庫

管理員的工資就高了,可惜大多中小公司都沒有專門的DBA;

三、事務（重點）

什麼是事務

事務是邏輯上的一組操作,要麼都成功,要麼都失敗

為什麼需要事務

很多時候一個數據操作,不是一個sql語句就完成的,可能有很多個sql語句,如果部分sql執行成功而部分sql執行失敗將導致數據錯亂！

例如轉賬操作,

1.從原有賬戶減去轉賬金額

2.給目標賬戶加上轉賬金額

若中間突然斷電了或系統崩潰了,錢就不翼而飛了!

使用事務

start transaction; --開啟事物,在這條語句之後的sql將處在同一事務,並不會立即修改數據庫

commit;--提交事務,讓這個事物中的sql立即執行數據的操作,

rollback;--回滾事務,取消這個事物,這個事物不會對數據庫中的數據產生任何影響

案例：轉賬過程中發生異常

#準備數據 

create table account(
 id int primary key auto_increment,
 name varchar(20),
 money double
);
insert into account values(1,'趙大兒子',1000);
insert into account values(2,'劉大牛',1000);
insert into account values(3,'豬頭三',1000);
insert into account values(4,'王進',1000);
insert into account values(5,'黃卉',1000);
# 趙大兒子劉大牛佳轉賬1000塊
# 未使用事務
update account set money = money - 1000 where id = 1;
update account set moneys = money - 1000 where id = 1; # money打錯了導致執行失敗
​# 在python中使用事務處理
sql = 'update account set money = money - 1000 where id = 1;'
sql2 = 'update account set moneys = money + 1000 where id = 2;' # money打錯了導致執行失敗
try:
 cursor.execute(sql)
 cursor.execute(sql2)
 conn.commit()
except:
 conn.rollback() 

注意:事務的回滾的前提是能捕捉到異常,否則無法決定何時回滾,Python中很簡單就實現了,另外mysql中需要使用存儲過程才能捕獲異常!

事務的四個特性 :

原子性: 事務是一組不可分割的單位,要麼同時成功,要麼同時不成功

隔離性: 事物的隔離性是指多個用戶併發訪問數據時,一個用戶的事物不能被其它用戶的事務所幹擾,多個併發事務之間數據要相互隔離

持久性: 持久性是指一個事物一旦被提交,它對數據的改變就是永久性的,接下來即使數據庫發生故障也不應該對其有任何影響

事務的用戶隔離級別:

數據庫使用者可以控制數據庫工作在哪個級別下,就可與防止不同的隔離性問題

read uncommitted --不做任何隔離,可能髒讀,幻讀

read committed----可以防止髒讀,不能防止不可重複讀,和幻讀,

Repeatable read --可以防止髒讀,不可重複讀,不能防止幻讀

Serializable--數據庫運行在串行化實現,所有問題都沒有,就是性能低

修改隔離級別:

select @@tx_isolation;--查詢當前級別

set[session|global] transaction isolation level .... ;修改級別

實例:

set global transaction isolation level Repeatable read ;

四、存儲過程（重點）

什麼是存儲過程

存儲過程是一組任意的sql語句集合,存儲在mysql中,調用存儲過程時將會執行其包含的所有sql語句;與python中函數類似;

為什麼使用存儲過程

回顧觸發器與視圖都是為了簡化應用程序中sql語句的書寫,但是還是需要編寫,而存儲過程中可以包含任何的sql語句,包括視圖,事務,流程控制等,這樣一來,應用程序可以從sql語句中完全解放,mysql可以替代應用程序完成數據相關的的邏輯處理!

那我們以後都是用存儲過程不就完了?

三種開發方式對比

1.應用程序僅負責業務邏輯編寫,所有與數據相關的邏輯都交給mysql來完成,通過存儲過程(推薦使用)

優點:

應用程序與數據處理完解耦合,一堆複雜的sql被封裝成了一個簡單的存儲過程,考慮到網絡環境因素,效率高

應用程序開發者不需要編寫sql語句,開發效率高

缺點:

python語法與mysql語法區別巨大,學習成本高

並且各種數據庫的語法大不相同,所以移植性非常差

應用程序開發者與BDA的跨部門溝通成本高,造成整體效率低

2.應用程序不僅編寫業務邏輯,還需要編寫所有的sql語句

優點:擴展性高,對於應用程序開發者而言,擴展性和維護性相較於第一種都有所提高

缺點:sql語句過於複雜,導致開發效率低,且需要考慮sql'優化問題

3.應用程序僅負責業務邏輯,sql語句的編寫交給ORM框架,(常用解決方案)

優點:應用程序開發者不需要編寫sql語句,開發效率高

缺點:執行效率低,由於需要將對象的操作轉化為sql語句,且需要通過網絡發送大量sql

創建存儲過程

create procedure pro_name(p_Type p_name data_type)
begin
sql語句......流程控制
end

p_type 參數類型

in 表示輸入參數

out 表示輸出參數

inout表示既能輸入又能輸出

p_name 參數名稱

data_type 參數類型 可以是mysql支持的數據類型

案例:使用存儲過程完成對student表的查詢

delimiter //
create procedure p1(in m int,in n int,out res int)
begin
 select *from student where chinese > m and chinese < n; 

 #select *from student where chineseXXX > m and chinese < n; 修改錯誤的列名以測試執行失敗
 set res = 100;
end//
delimiter ;
set @res = 0;
#調用存儲過程
call p1(70,80,@res);
#查看執行結果
select @res; 

需要注意的是,存儲過程的out類參數必須是一個變量,不能是值;

在python中調用存儲過程

import pymysql
#建立連接
conn = pymysql.connect(
 host="127.0.0.1",
 user="root",
 password="admin",
 database="db02"
)
# 獲取遊標
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 調用用存儲過程
cursor.callproc("p1",(70,80,0)) #p1為存儲過程名 會自動為為每個值設置變量,名稱為 @_p1_0,@_p1_1,@_p1_2
# 提取執行結果是否有結果取決於存儲過程中的sql語句
print(cursor.fetchall())
# 獲取執行狀態
cursor.execute("select @_p1_2")
print(cursor.fetchone())

此處pymysql會自動將參數都設置一個變量所以可以直接傳入一個值,當然值如果作為輸出參數的話,傳入什麼都無所謂!

刪除存儲過程

drop procedure 過程名

修改存儲過程意義不大,不如刪除重寫!

查看存儲過程

 #當前庫所有存儲過程名稱
 select `name` from mysql.proc where db = 'db02' and `type` = 'PROCEDURE';
 #查看創建語句
 show create procedure p1; 

存儲過程中的事務應用

存儲過程中支持任何的sql語句包括事務!

案例:模擬轉賬中發送異常,進行回滾

delimiter //
create PROCEDURE p5(
 OUT p_return_code tinyint
)
BEGIN 
 DECLARE exit handler for sqlexception 
 BEGIN 
 -- ERROR 
 set p_return_code = 1; 
 rollback; 
 END; 
 # exit 也可以換成continue 表示發送異常時繼續執行
 DECLARE exit handler for sqlwarning 
 BEGIN 
 -- WARNING  

 set p_return_code = 2; 
 rollback; 
 END; 
 START TRANSACTION; 
 update account set money = money - 1000 where id = 1;
 update account set moneys = money - 1000 where id = 1; # moneys字段導致異常
 COMMIT; 
​ -- SUCCESS 
 set p_return_code = 0; #0代表執行成功
END //
delimiter ;
#在mysql中調用存儲過程
set @res=123;
call p5(@res);
select @res;

總結:拋開溝通成本,學習成本,存儲過程無疑是效率最高的處理方式,面試會問,一些公司也有一些現存的存儲過程,重點掌握!

五、函數

函數與python中的定義一致

內置函數

日期相關:

字符串相關:

數字相關:

其他函數:

當然也包括聚合函數

自定義函數

語法:

CREATE FUNCTION f_name(paramters)
returns dataType;
return value; 

說明: paramters 只能是in 輸入參數 參數名 類型 必須有返回值 不能呢加begin 和end returns 後面是返回值的類型 這裡不加分號 return 後面是要返回的值

案例: 將兩數相加

create function addfuntion(a int,b int)
returns int return a + b;
#執行函數
select addfuntion(1,1);

注意:

函數只能返回一個值 函數一般不涉及數據的增刪改查 就是一個通用的功能 調用自定義的函數與調用系統的一致 不需要call 使用select 可獲得返回值 函數中不能使用sql語句 就像在java中不能識別sql語句一樣

六、數據備份與恢復

使用mysqldump程序進行備份

mysqldump -u -p db_name [table_name,,,] > fileName.sql 

可以選擇要備份哪些表 如果不指定代表 全部備份

單例 單庫備份

mysqldump -uroot -p123 db1 > db1.sql
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql​
#多庫備份
mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql
#備份所有庫
mysqldump -uroot -p123 --all-databases > all.sql

使用 mysql 進行恢復

1.退出數據庫後

mysql -u -p < filename.sql;

2.不用退出數據庫

2.1 創建空數據庫

2.2選擇數據庫

2.3然後使用source filename; 來進行還原

use db1;
source /root/db1.sql

數據庫遷移

務必保證在相同版本之間遷移
# mysqldump -h 源IP -uroot -p123 --databases db1 | mysql -h 目標IP -uroot -p456

七、流程控制

if語句的使用

if 條件 then 語句; end if; 第二種 if elseif if 條件 then 語句1; elseif 條件 then 語句2; else 語句3; end if;

案例:編寫過程 實現 輸入一個整數type 範圍 1 - 2 輸出 type=1 or type=2 or type=other;

create procedure showType(in type int,out result char(20))
begin
if type = 1 then 
set result = "type = 1";
else
if type = 2 then 
set result = "type = 2";
else 
set result = "type = other";
end if;
end

CASE 語句

大體意思與Swtich一樣的 你給我一個值 我對它進行選擇 然後執行匹配上的語句 語法:

create procedure caseTest(in type int)
begin
CASE type 
when 1 then select "type = 1";
when 2 then select "type = 2";
else select "type = other";
end case;
end

定義變量

declare 變量名 類型 default 值; 例如: declare i int default 0;

WHILE循環

循環輸出10次hello mysql
create procedure showHello()
begin 
declare i int default 0;
while i < 10 do
select "hello mysql";
set i = i + 1;
end while;
end

LOOP循環的

沒有條件 需要自己定義結束語句 語法:

輸出十次hello mysql;
create procedure showloop()
begin 
declare i int default 0;
aloop: LOOP
select "hello loop";
set i = i + 1;
if i > 9 then leave aloop; 

end if;
end LOOP aloop;
end

REPEAT循環

#類似do while
#輸出10次hello repeat
create procedure showRepeat()
begin
declare i int default 0;
repeat
select "hello repeat";
set i = i + 1;
until i > 9
end repeat;
end
#輸出0-100之間的奇數
create procedure showjishu()
begin
declare i int default 0;
aloop: loop
set i = i + 1;
if i >= 101 then leave aloop; end if;
if i % 2 = 0 then iterate aloop; end if;
select i;
end loop aloop;
end

閱讀更多 鈺兒愛編程 的文章

關鍵字: SQL 數據結構 視圖