MySQL之視圖、儲儲過程、觸發(fā)器使用及說明
一、視圖
視圖是一種虛擬存在的表。視圖中的數(shù)據(jù)并不在數(shù)據(jù)庫中真實存在，行和列數(shù)據(jù)來自定義視圖的查詢中使用的表，并且是在使用視圖時動態(tài)生成的。
通俗的講，視圖只保存了查詢的SQL邏輯，不保存查詢結(jié)果。所以我們在建立視圖的時候，主要的工作就落在創(chuàng)建這條SQL查詢語句上
創(chuàng)建或修改視圖語句:
查看創(chuàng)建視圖的語句：
show create view 視圖名稱;
查看視圖：
select * from 視圖名稱;
刪除視圖：
drop view if exists 視圖名稱;
with check option:當使用with check option子句創(chuàng)建視圖時，MySQL會通過視圖檢查正在更改的每個行，例如插入，更新，刪除，以使其符合視圖定義。MySQL允許基另一個視圖創(chuàng)建視圖，它還會檢查依賴圖中的規(guī)則保持一致性。
為了確定檢查的范圍，mysql提供了兩個選項：CASCADED和LOCAL,默認值為CASCADED。
- CASCADED:v2視圖是基于v1視圖的，如果在v2視圖創(chuàng)建的時候指定了檢查選項為 cascaded，但是v1視圖創(chuàng)建時未指定檢查選項。 則在執(zhí)行檢查時，不僅會檢查v2，還會級聯(lián)檢查v2的關聯(lián)視圖v1。
- LOCAL：v2視圖是基于v1視圖的，如果在v2視圖創(chuàng)建的時候指定了檢查選項為 local ，但是v1視圖創(chuàng) 建時未指定檢查選項。 則在執(zhí)行檢查時，知會檢查v2，不會檢查v2的關聯(lián)視圖v1。
無法更新的視圖：
作用：
1.視圖不僅可以簡化用戶對數(shù)據(jù)的理解，也可以簡化他們的操作。那些被經(jīng)常使用的查詢可以被定義為視圖，從而使用戶不必以后操作每次指定全部條件
2.數(shù)據(jù)庫可以授權，但不能授權到數(shù)據(jù)庫特定行和特定的列上。通過視圖用戶只能查詢和修改他們所能見到的數(shù)據(jù)
3.視圖可幫助用戶屏蔽真實表結(jié)構變化帶來的影響
二、存儲過程
存儲過程是事先經(jīng)過編譯并存儲在數(shù)據(jù)庫中的一段SQL語句的集合，調(diào)用存儲過程可以簡化應用開發(fā)人員的很多工作，減少數(shù)據(jù)在數(shù)據(jù)庫和應用服務器之間的傳輸，對于提高數(shù)據(jù)處理的效率是有好處的
創(chuàng)建存儲過程：
調(diào)用：
查詢指定數(shù)據(jù)庫的存儲過程及狀態(tài)信息：
select * from information_schema.ROUTINES where ROUTINE_SCHEMA=數(shù)據(jù)庫名稱;
查詢存儲過程定義：
show create procedure 存儲過程名稱;
刪除：
drop procedure if exists 存儲過程名稱;
注意：在命令行中，執(zhí)行創(chuàng)建存儲過程的SQL時，需要通過關鍵字delimiter指定SQL語句的結(jié)束符
系統(tǒng)變量：是MySQL服務器提供，不是用戶定義的，屬于服務器層面。分為全局變量(GLOBAL)、會話變量(SESSION)
查看所有系統(tǒng)變量：
show global /session variables ;
通過like模糊匹配方式查找變量：
show global variables like 模糊匹配類型;
查看指定變量：
select @@global.autocommit;
設置系統(tǒng)變量：
set session autocommit=1;
注意：如果沒有指定SESSION/GLOBAL，默認是SESSION，會話變量。mysql服務重新啟動之后，所設置的全局參數(shù)會失效，想要不失效，可在/etc/my.cnf中配置
用戶定義變量：是用戶根據(jù)需要自己定義的變量，用戶變量不用提前聲明，在用的時候直接用“@ 變量名”使用就可以。其作用域為當前連接。
賦值：
set @name:='李天天';
select sn into @sn_1 from tb_sku where id=1;
查找：
select @name;
注意：用戶定義的變量無需對其進行聲明或初始化，只不過獲取到地值未NULL
局部變量：是根據(jù)需要定義的在局部失效的變量，訪問之前，需要DECLARE聲明?？捎米鞔鎯^程內(nèi)的局部變量和輸入?yún)?shù)，局部變量的范圍是在其內(nèi)聲明的BEGIN...END塊
聲明：
declare total int default 0;
注意：變量類型就是數(shù)據(jù)庫字段類型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等
賦值：
set total:=90;
select sn into total from tb_sku where id=1;
if:
create procedure p1()
begin
declare score int default 44;
declare result char(10);
if score>=80 then
set result:='優(yōu)秀';
elseif score>=60 then
set result:='良好';
else
set result:='不及格';
end if;
select result;
end;參數(shù)：
示例：
create procedure p2(in score int,out result char(10))
begin
if score>=80 then
set result:='優(yōu)秀';
elseif score>=60 then
set result:='良好';
else
set result:='不及格';
end if;
end;create procedure p3(inout score double) begin set score:=score*0.5; end; set @result=89; call p3(@result); select @result;
case:
示例：
begin
declare season varchar(20);
case
when month >= 1 and month <= 3 then set season = '一季度';
when month >= 4 and month <= 6 then set season = '二季度';
when month >= 7 and month <= 9 then set season = '三季度';
when month >= 10 and month <= 12 then set season = '四季度';
else set season = '輸入錯誤';
end case;
select concat('當前月份為：', month, ';當前季度為:', season);
end;while:
示例：
create procedure p2(in n int)
begin
declare total int default 0;
while n > 0
do
set total := total + n;
set n = n - 1;
end while;
select concat('累加值為:',total);
end;
repeat:
create procedure p3(in n int)
begin
declare total int default 0;
repeat
set total := total + n;
set n = n - 1;
until n <= 0
end repeat;
select concat('累加值為:', total);
end;
loop:
loop實現(xiàn)簡單的循環(huán)，如果不在SQL邏輯中增加退出循環(huán)條件，可以用其來實現(xiàn)簡單的死循環(huán)。loop可以配合一下兩個語句使用：
LEAVE:配合循環(huán)使用，退出循環(huán)
ITERATE：必須用在循環(huán)中，作用是跳過當前循環(huán)剩下的語句，直接進入下一次循環(huán)
create procedure p4(in n int)
begin
declare total int default 0;
sum :
loop
if n <= 0 then
leave sum;
end if;
set total = total + n;
set n = n - 1;
end loop sum;
select total;
end;create procedure p6(in n int)
begin
declare total int default 0;
sum :
loop
if n <= 0 then
leave sum;
end if;
if n % 2 = 1 then
set n = n - 1;
iterate sum;
else
set total = total + n;
set n = n - 1;
end if;
end loop sum;
select total;
end;
游標：
游標是用來存儲查詢結(jié)果集的數(shù)據(jù)類型，在存儲過程和函數(shù)中可以使用游標對結(jié)果集進行循環(huán)的處理。游標的使用包括游標的聲明、open、fetch和clode。
聲明游標：
打開游標：
獲取游標記錄：
關閉游標：
條件處理程序：可以用來定義在流程控制結(jié)構執(zhí)行過程中遇到問題時相應的處理步驟
mysql異常狀態(tài)碼文檔：
https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html
示例：
create procedure p7()
begin
declare new_name varchar(10) default null;
declare new_no varchar(10) default null;
declare student_name cursor for select no,name from student;
declare exit handler for SQLSTATE '02000' close student_name;
open student_name;
create table if not exists tb_sku_name_price_100
(
id int primary key auto_increment,
no varchar(10),
name varchar(10)
);
while true
do
fetch student_name into new_no ,new_name ;
insert into tb_sku_name_price_100 values (null,new_no,new_name);
end while;
close student_name;
end;存儲函數(shù)：
存儲函數(shù)是有返回值的存儲過程，存儲函數(shù)的參數(shù)只能是IN類型
示例：
create function sun(n int)
returns int deterministic
begin
declare total int default 0;
while n>0 do
set total:=n+total;
set n:=n-1;
end while;
return total;
end;三、觸發(fā)器
觸發(fā)器是與表有關的數(shù)據(jù)庫對象，指在insert/update/delete之前或之后，觸發(fā)并執(zhí)行觸發(fā)器中定義的SQL語句集合。觸發(fā)器的這種特性可以協(xié)同應用在數(shù)據(jù)庫端確保數(shù)據(jù)的完整性，日志記錄，數(shù)據(jù)校驗等操作。
使用別名OLD和NEW來引用觸發(fā)器中發(fā)生變化的記錄內(nèi)容，這與其他數(shù)據(jù)庫是相似的?，F(xiàn)在數(shù)據(jù)庫還只支持行級觸發(fā)，不支持語句觸發(fā)。
創(chuàng)建：
查看：
刪除：
insert:
create trigger tb_student_insert_trigger
after insert on student for each row
begin
insert into user_logs (operation, operate_time, operate_id, operate_params) values
('insert',now(),new.id,concat('插入的數(shù)據(jù)內(nèi)容為:','id為',new.id,'name為',new.name,'no為',NEW.no));
end;deleter:
create trigger tb_student_deleter_trigger
after delete on student for each row
begin
insert into user_logs (operation, operate_time, operate_id, operate_params) values
('deleter',now(),old.id,concat('刪除的數(shù)據(jù)內(nèi)容為:','id為',old.id,'name為',old.name,'no為',old.no));
end;update:
create trigger tb_student_update_trigger
after update on student for each row
begin
insert into user_logs (operation, operate_time, operate_id, operate_params) values
('update',now(),old.id,concat('更新前數(shù)據(jù)內(nèi)容為:','id為',old.id,'name為',old.name,'no為',old.no,'|','更新后的數(shù)據(jù)內(nèi)容為:','id為',new.id,'name為',new.name,'no為',new.no));
end;
總結(jié)
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關文章
Mac操作系統(tǒng)下MySQL密碼忘記后重置密碼的方法
本文給大家介紹Mac下忘記MySQL密碼后重置密碼的方法，下面通過關閉mysql服務器，配置短命令相關操作，完成重置密碼功能，非常不錯，具有參考借鑒價值，感興趣的朋友可以參考下2016-06-06
MySQL索引的缺點以及MySQL索引在實際操作中有哪些事項
以下的文章主要介紹的是MySQL索引的缺點以及MySQL索引在實際操作中有哪些事項是值得我們大家注意的，我們大家可能不知道過多的對索引進行使用將會造成濫用,需要的朋友可以了解下2012-12-12
這篇文章主要介紹了MySql 存儲引擎和索引相關知識總結(jié)，文中講解非常細致，代碼幫助大家更好的理解和學習，感興趣的朋友可以了解下2020-06-06
這篇文章主要為大家詳細介紹了mysql 8.0.22.0 下載安裝配置方法圖文教程，文中安裝步驟介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2020-11-11
MYSQL 完全備份、主從復制、級聯(lián)復制、半同步小結(jié)
這篇文章主要介紹了MYSQL 完全備份、主從復制、級聯(lián)復制、半同步小結(jié)，小編覺得挺不錯的，現(xiàn)在分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2019-05-05
這篇文章主要為大家介紹了Centos6.5在線安裝 mysql 8.0詳細教程,具有一定的參考價值，感興趣的小伙伴們可以參考一下2016-11-11
淺談sql語句中GROUP BY 和 HAVING的使用方法
GROUP BY語句和HAVING語句，經(jīng)過研究和練習，終于明白如何使用了，在此記錄一下同時添加了一個自己舉的小例子，通過寫這篇文章來加深下自己學習的效果，還能和大家分享下，同時也方便以后查閱，一舉多得，下面由小編來和大家一起學習2019-05-05
MySQL數(shù)據(jù)庫在安裝或卸載的過程中,常常會出現(xiàn)一些錯誤,這是件讓我們頭疼的事,下面這篇文章主要給大家介紹了關于MySQL安裝常見報錯處理方法的相關資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下2022-07-07
本篇文章主要介紹了Mysql數(shù)據(jù)庫之主從分離實例代碼，MySQL數(shù)據(jù)庫設置讀寫分離，可以使對數(shù)據(jù)庫的寫操作和讀操作在不同服務器上執(zhí)行，提高并發(fā)量和相應速度。2017-03-03
MySQL 在 Windows 下不區(qū)分大小寫，但在 Linux 下默認是區(qū)分大小寫。如果你稍加不注意就會出現(xiàn)在本機開發(fā)的程序運行一切正常，發(fā)布到服務器行就出現(xiàn)表名找不到的問題，一頭霧水。2021-05-05