4 視圖
4.1介紹以及基本文法
- 介紹
- 概念：視圖是一種虛拟的存在的表。視圖的行和列資料來自定義視圖的查詢中使用的表，并且是使用視圖時動态生成的。
- 建立
create view 視圖名稱 as select 語句；
create or replace view course_v_1 as
select name from course where name = 'MySQL';
- 查詢視圖
檢視建立視圖語句：show create view 視圖名稱；
檢視視圖資料：select * from 視圖名稱；
show create view course_v_1;
select * from course_v_1;//視圖查詢也可以加上where條件
- 修改視圖
方式一：create or replace view 視圖名稱 as select 語句；
方式二：alter view 視圖名稱 as select 語句；
create or replace view course_v_1 as
select id,name from course where name = 'MySQL';
alter view course_v_1 as
select id,name from course where name = 'MySQL';
- 删除視圖名稱
drop view [if exist]視圖名稱；
drop view if exists course_v_1;
4.2 檢查選項
- 視圖的增與改
insert into 視圖名字 values (字段值，字段值)；
- 檢查項
- 建立視圖時候增加檢查項
create or replace view 視圖名稱 as select 語句 with cascaded/local check option;
create or replace view course_v_1 as
select id,name from course where id <=20
with cascaded check option;
- 當插入不符合視圖條件的值的時候，便會提示阻止插入
- cascaded檢查項
-- 建立視圖
create or replace view course_v_1 as
select id,name from course where id <=20;
create or replace view course_v_2 as
select id,name from course_v_1 where id >=10
with cascaded check option;
-- 插入資料
insert into course_v_2 values (6,'Airflow');
insert into course_v_2 values (25,'GO');
insert into course_v_2 values (15,'VB');
- 插入(6,'Airflow')不行是因為視圖2 有檢查項進行了限制
- 插入(25,'GO')不行是因為，cascaded是級聯，也會根據視圖1中的條件進行限制
- 不含cascaded級聯檢查項
-- 建立視圖
create or replace view course_v_1 as
select id,name from course where id <=20;
create or replace view course_v_2 as
select id,name from course_v_1 where id >=10
with cascaded check option;
create or replace view course_v_3 as
select id,name from course_v_2 where id <=15;
insert into course_v_3 values (11,'Python');
insert into course_v_3 values (17,'Python');
insert into course_v_3 values (28,'Python');
- 插入(11,'Python')成功是因為，每個視圖條件都滿足
- 插入 (17,'Python')成功是因為，視圖3沒有檢查項，不滿足視圖3的條件也可以，但是視圖2有檢查項，進而會去查找視圖2中的檢查項是否滿足
- 插入(28,'Python')不可以是因為，不滿足視圖2中檢查項級聯到視圖1 的條件：id<=20
- local檢查項
- 插入(13,'SpringBoot')：會檢查本視圖中的條件，向上檢查依賴的視圖時，取決于依賴的視圖是否還有檢查項，如果有，就繼續，如果沒有，就不檢查，此案例沒有，隻檢查id>=10
- 插入(14,'SpringBoot')成功，是因為視圖6的條件符合，查找依賴的視圖5，發現有local檢查項，那檢查，繼續往上視圖4沒local檢查項，是以不檢查
4.3 視圖的更新與作用
- 不可更新的場景
- 存在聚合函數和視窗函數
create or replace view course_v_count as
select COUNT(*) from course;
-- 執行失敗，報錯：不可執行插入操作
insert into course_v_count values(11);
[HY000][1471] The target table course_v_count of the INSERT is not insertable-into
- DISTINCT
- GROUP BY
- HAVING
- UNION和UNION ALL
- 可更新條件
- 想要視圖可更新，視圖中的行和基礎中的行之間必須存在一一對一的關系
- 視圖的作用
- 操作簡單。針對經常被使用的查詢可以定義為視圖，進而使得使用者不必為以後的操作每次指定全部的條件
- 安全。通過視圖使用者隻能查詢和修改他們所見的資料(可以控制到字段)
- 資料獨立。可以幫助使用者屏蔽真實表結構帶來的影響。例如字段名稱的改變，可以根據對視圖進行更新操作即可，可以起别名的方法屏蔽基表的變化，減少對業務的影響。
4.4 視圖的案例
- 為了保證資料庫表的安全性，開發人員在操作tb_user表的時候，隻能看到使用者的基本字段，屏蔽手機号和郵箱兩個字段
create or replace view tb_user_view as
select
id,name,profession,age,gender,status,createtime
from
tb_user;
-- 後續直接查詢視圖
select * from tb_user_view;
- 查詢每個學生所選修的課程(三張表聯查)，這個功能在很多的業務中都有使用到，為了簡化操作，定義一個視圖。
create view tb_stu_course as
select
s.name studentname,s.no student_no,c.name course_name
from
student s ,student_course sc,course c
when
s.id = sc.studentid
and
sc.courseid = c.id;
-- 後續直接查詢視圖
select * from tb_stu_course;
5 存儲過程
5.1 介紹以及基本文法
- 介紹
- 存儲過程是事先經過編譯在資料庫中的一段SQL語句的集合，調用存儲過程可以簡化應用人員開發的很多工作。
- 思想就是将資料庫SQL語言層面的代碼封裝與重用
- 特點
- 封裝，複用
- 可以接收參數，也可以傳回資料
- 減少網絡互動，效率提升
- 基本文法
- 建立存儲過程
create procedure 存儲過程名稱([參數清單])
begin
-- SQL語句
end;
create procedure p1()
begin
select count(*) from tb_user;
end;
- 指令行下建立存儲過程
-- 因為指令行中見到分号預設結束，是以先定義結束符
mysql> delimiter $$
mysql> create procedure p1()
mysql> begin
mysql> select count(*) from tb_user;
mysql> end
mysql>$$
- 調用
call 名稱([參數]);
call p1();
- 檢視
select * from information_schema.ROUTINES
where ROUTINE_SCHEMA = 'itcast';
-- information_schema是庫，routines是表，routine_schema是字段
select * from information_schema.routines where routine_schema = 'xxx';
show create procedure p1;
- 删除
drop procedure [if exist] 存儲過程名稱；
5.2 系統變量
- 變量的分類
- 系統變量：MySQL伺服器提供的，不是使用者定義的，屬于伺服器層面。分為全局變量(GLOBAL),會話變量(SESSION)
- 全局變量：在所有的會話都是有效的
- 會話變量：僅僅在此會話中有效
- 檢視系統變量
show [session/global] variables; --檢視所有系統變量
show [session/global] variables like '......' -- 可以通過like模糊比對方式查找變量
select @@[session/global] 系統變量名 -- 檢視指定變量的值
- 文法
show session variables;
- 查詢事務自動送出的變量
show session variables like 'auto%';
- 如果記得名稱，就用select+@@全名
select @@session.autocommit;
- 設定系統變量
- 文法
set [session/global] 系統變量名 = 值；
set @@[session/global] 系統變量名 = 值；
- 把自增改成0
set session autocommit = 0;
set @@session.autocommit = 0;
- 自動送出關閉後，插入語句發現表還是沒有變化，因為需要手動commit送出下
- 但是改的是會話變量，别的會話框查還是1
春華秋實之MySQL進階-04 視圖/存儲過程/觸發器4 視圖5 存儲過程6 觸發器 - 改成全局變量看看，發現重新開機後不生效,變回了1
- 如果要改，去/etc/my.cnf配置檔案中改
set @@global.autocommit = 0;
select @@global.autocommit;
5.3 使用者定義變量
- 概念
- 是使用者根據需要自己定義的變量，使用者變量不同提前聲明，用的時候@變量名使用即可。其作用域為目前連接配接。
- 指派
- 文法
set @變量名 = 值；
set @變量名 := 值；
select @變量名 := 值；
select 字段名 into @變量名 from 表名；
- 案例：指派可以多個一起
set @myname = '芬芬';
set @myage := '18';
set @mygender := '女',@myhobby:='大資料';
select @mycolor := 'red';
select COUNT(*) into @mycount from tb_user;
- 檢視
- 文法
select @變量名;
- 案例
select @myname,@myage,@mygender,@myhobby;
select @mycolor,@mycount;
-- 就算沒指派，也可以查，就是0而已
select @abc;
5.4 局部變量
- 含義
- 是根據需要定義的局部生效的變量，通路之前，需要declare聲明。可用作存儲過程内的局部變量和輸入參數，局部變量的範圍是在其聲明的begin...and之間
- 聲明
declare 變量名 變量類型[default...];
變量類型有：int,bigint,char,varchar,date,time等
- 指派
set 變量名 = 值；
set @變量名 := 值；
select @變量名 := 值；
- 案例
-- 聲明
-- 指派
create procedure p2()
begin
declare course_count int default 0;
set course_count :=100;
select course_count;
end;
-- 調用
call p2();
5.5 if判斷
- 文法
if 條件1 then
...
elseif 條件2 then
...
else
...
end if;
- 案例
- score是58 寫shi了
create procedure p3 ()
begin
declare score int default 58;
declare result varchar(10);
if score >= 85 then
set result := '優秀';
elseif score >= 60 then
set result := '及格';
else
set result := '不及格';
end if;
select result;
end;
call p3();
- 傳個參數的寫法
見下章5.5 參數的傳參寫法
5.5 參數(in,out,inout)
- 參數分類
- in：作為輸入
- out：作為輸出
- inout：作為輸入輸出
- 文法
create procedure 存儲構成名稱(in/out/inout 參數名 參數類型)
begin
--sql語句
end；
- 案例
- 需求1：
create procedure p4 (in score int,out result varchar(10))
begin
-- declare score int default 58;
-- declare result varchar(10);
if score >= 85 then
set result := '優秀';
elseif score >= 60 then
set result := '及格';
else
set result := '不及格';
end if;
-- select result;
end;
-- 第二個參數需要一個變量來接收，然後列印出來
call p4(68,@result);
select @result;
- 需求2：
将傳入的200分制的分數，進行換算，換算成百分制，然後傳回分數
分析：分數即使傳入值，也是傳出值，用inout
create procedure p5 (inout score int)
begin
set score := score * 0.5;
end;
set @score := 178; -- 初始化
call p5(@score); -- 調用
select @score; -- 列印
5.6 case判斷
- 文法
case 字段
when 條件 then 程式
when 條件 then 程式
else 程式
end case；
- 案例
create procedure p6(in month int)
begin
declare result varchar(10);
case
when month >=1 and month <=3 then
set result = '第一季度';
when month >=4 and month <=6 then
set result = '第二季度';
when month >=7 and month <=9 then
set result = '第三季度';
when month >=10 and month <=12 then
set result = '第四季度';
else
set result:= '非法參數';
end case;
select concat('你輸入的月份為',month,'所屬的季度為',result);
end;
call p6(4);
5.7 while循環
- 文法
while 條件 do
SQL邏輯
end while；
- 案例
- 計算從1累加到n的值，n為傳入的參數值
create procedure p7 (in n int)
begin
declare total int default 0;
while n > 0 do
set total:= total + n;
set n := n - 1;
end while;
select total ;
end;
call p7(10);
5.8 repeat 循環
- 概念：repeat 是有條件的循環控制語句，當滿足條件的時候退出循環
- 文法：有dowhile那味兒
repeat
SQL邏輯
until 條件
end repeat；
- 案例
- 計算從1累加到n的值，n為傳入的參數值
create procedure p8(in n int)
begin
declare total int default 0;
repeat
set total:= total+n;
set n:=n-1;
until n<=0
end repeat;
select total;
end;
call p8(10);
call p8(100);
5.9 loop循環
- 概念
如果不在SQL邏輯中增加退出循環的條件，可以用來實作簡單的死循環，LOOP一般配合
- leave：退出
- iterate：跳過目前循環剩下的語句，直接進入下一次循環
- 類比break和continue
- 文法
begin_label :LOOP
SQL邏輯
END LOOP end_label;
- 案例
- 計算從1累加到n的值，n為傳入的參數值
create procedure p9(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;
call p9(10);
call p9(100);
- 計算從1到n之間的偶數累加的值，n為傳入的參數值
create procedure p10(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; -- 這部是因為奇數的時候回跳過下面的n-1，是以上面也減一次
iterate sum;
end if;
set total:=total + n;
set n := n-1;
end loop sum;
select total;
end;
call p10(10);
call p10(100);
5.10 cursor 遊标
- 場景
- select*指派給局部變量，報錯，原因是把整張表指派給到int類型的變量，變量是隻能接收單行單列的資料
- 針對這種情況，遊标出場啦
- 概念和内容
- 概念：是用來存儲查詢結果集的資料類型，在存儲過程和函數中可以使用遊标對結果集進行循環的處理。
- 内容：遊标的聲明、OPEN、CLOSE
- 遊标的文法
- 遊标的聲明
declare 遊标名 cursor for 查詢語言;
- 打開遊标
open 遊标名；
- 擷取遊标記錄
fetch 遊标名 into 變量;
- 關閉遊标
close 遊标名；
- 案例
create procedure p11(in uage int)
begin
-- 聲明兩個變量,友善後續指派,普通變量聲明要在聲明遊标之前
declare uname varchar(100);
declare upro varchar(100);
-- 聲明遊标
declare u_cursor cursor for select name,profession from tb_user where age <= uage; -- 邏輯寫這邊
-- 建立一張表
drop table if exists tb_user_pro;
create table if not exists tb_user_pro(
id int primary key auto_increment,
name varchar(100),
profession varchar(100)
);
-- 打開遊标
open u_cursor;
-- 擷取遊标記錄
-- 循環周遊集合并指派
while true do
fetch u_cursor into uname,upro;
-- 插入表結構
insert into tb_user_pro values(null,uname,upro);-- null是自增的id，不用慌
end while;
-- 關閉遊标
close u_cursor;
end;
- 但是會報錯，因為循環那邊循環第一次後，遊标的數值給了新表，遊标裡面沒資料，第二次循環的時候從遊标中擷取的記錄為空
5.11 handler 條件處理過程
-
概念
可以用來定義在流程控制結構執行構成中遇到問題時相應的處理步驟,類比異常處理
- 文法
declare handler_action HANDLER FOR condition_value [condition_value]...statment;
handler_action:
continue:繼續執行目前程式
exit:終止執行目前程式
condition_value：
如下圖一部分，如有興趣去MySQL官方文檔那邊看下各個狀态碼的用途
- 案例
- 承上例,多了個handler，聲明一個條件處理程式，當滿足SQL狀态碼02000的時候觸發退出操作，退出的時候并且關閉遊标
create procedure p11(in uage int)
begin
-- 聲明兩個變量,友善後續指派,普通變量聲明要在聲明遊标之前
declare uname varchar(100);
declare upro varchar(100);
-- 聲明遊标
declare u_cursor cursor for select name,profession from tb_user where age <= uage;
-- 聲明handler
declare exit handler for SQLSTATE '02000' close u_cursor;
-- 也可以 declare exit handler for not found close u_cursor;表示處理02開頭的狀态碼
-- 建立一張表
drop table if exists tb_user_pro;
create table if not exists tb_user_pro(
id int primary key auto_increment,
name varchar(100),
profession varchar(100)
);
-- 打開遊标
open u_cursor;
-- 擷取遊标記錄
-- 循環周遊集合并指派
while true do
fetch u_cursor into uname,upro;
-- 插入表結構
insert into tb_user_pro values(null,uname,upro);
end while;
-- 關閉遊标
close u_cursor;
end;
call p11(40);
5.12 存儲函數
- 概念
- 存儲函數是有傳回值的存儲過程，存儲函數的參數隻能是in類型。
- 文法
create function 存儲函數名稱[參數清單]
returns type [參數]
begin
--SQL語句
return...;
end;
參數：
deterministic:相同的輸入參數總是産生相同的結果
no sql：不包含SQL
read sql data:包含讀取資料的語句，但不包含
- 案例
- 計算從1累加到n的值，n為傳入的參數值
create function fun1(n int )
returns int deterministic
begin
declare total int default 0;
while n>0 do
set total:=total+n;
set n := n-1;
end while;
return total;
end;
select fun1(100);
6 觸發器
6.1 介紹
- 觸發器是與表有關的資料庫對象，指在insert/update/delete之前或之後，觸發并執行觸發器中定義的SQL語句集合
- 可以協助應用在資料庫段確定資料的完整性，日志記錄，資料校驗等操作。
- 使用别名OLD和NEW來引用觸發器中發生變化的記錄内容
6.2 文法
- 建立
create trigger trigger_name
before/after/ insert/update/delete --指定類型觸發器
on tbl_name for each row --行級觸發器
begin
trigger_stmt;
end
- 檢視
show triggers;
- 删除
drop trigger trigger_name;
6.3 案例
- 新增資料的觸發器案例
通過觸發器記錄tb_user的資料的變更日志，将變更日志插入到日志表user_logs表中，包含增，改，删
- 建立表結構
create table user_logs(
id int(11) not null auto_increment,
operation varchar(20) not null comment '操作類型, insert/update/delete',
operate_time datetime not null comment '操作時間',
operate_id int(11) not null comment '操作的ID',
operate_params varchar(500) comment '操作參數',
primary key(`id`)
)engine=innodb default charset=utf8;
- 建立觸發器
-- 插入資料時的觸發器
create trigger tb_user_insert_trigger
after insert on tb_user for each row
begin
insert into user_logs(id, operation, operate_time, operate_id, operate_params) values
(null,'insert',now(),new.id,-- null 表示預設id自增
concat('插入資料的内容為：id=',NEW.id,',name=',new.name,',phone=',new.phone,',email=',new.email,',profession=',new.profession));
end;
- 檢視觸發器
show triggers ;
- 在tb_user表中插入資料，驗證觸發器
insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime)
VALUES (25,'二皇子','18809091212','[email protected]','軟體工程',23,'1','1',now());
- 發現user_logs日志表中有資料
- 修改資料的觸發器
- 建立修改的觸發器operate_params有區分OLD和NEW之分
-- 和insert觸發器不同的點是：
create trigger tb_user_update_trigger
after update on tb_user for each row
begin
insert into user_logs(id, operation, operate_time, operate_id, operate_params) values
(null,'update',now(),new.id,-- null 表示預設id自增
concat('更新之前的資料：id=',OLD.id,',name=',OLD.name,',phone=',OLD.phone,',email=',OLD.email,',profession=',OLD.profession,
'|更新之後的資料：id=',NEW.id,',name=',new.name,',phone=',new.phone,',email=',new.email,',profession=',new.profession));
end;
- 檢視觸發器
show triggers ;
- 在tb_user表中更新資料，驗證觸發器
update tb_user set age = 20 where id = 23;
update tb_user set age = 20 where id <= 5; -- 因為是行級觸發器，是以回觸發5次
- 更新後，發現user_logs日志表多了5行資料
- 删除的觸發器
- 建立，注意隻有OLD沒有NEW了
-- 删除資料時的觸發器
create trigger tb_user_delete_trigger
after delete on tb_user for each row
begin
insert into user_logs(id, operation, operate_time, operate_id, operate_params) values
(null,'delete',now(),OLD.id,-- null 表示預設id自增
concat('更新之前的資料：id=',OLD.id,',name=',OLD.name,',phone=',OLD.phone,',email=',OLD.email,',profession=',OLD.profession));
end;
- 檢視下建立的觸發器
show triggers ;
- 在tb_user表中删除資料，驗證觸發器
delete from tb_user where id = 25;
- 删除後，發現user_logs日志表多了資料