當(dāng)前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

MariaDB 视图与触发器(11)

發(fā)布時(shí)間：2025/7/14 编程问答 24 豆豆

生活随笔收集整理的這篇文章主要介紹了 MariaDB 视图与触发器(11) 小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

MariaDB數(shù)據(jù)庫管理系統(tǒng)是MySQL的一個(gè)分支,主要由開源社區(qū)在維護(hù),采用GPL授權(quán)許可MariaDB的目的是完全兼容MySQL,包括API和命令行,MySQL由于現(xiàn)在閉源了,而能輕松成為MySQL的代替品.在存儲(chǔ)引擎方面,使用XtraDB來代替MySQL的InnoDB,MariaDB由MySQL的創(chuàng)始人Michael Widenius主導(dǎo)開發(fā),他早前曾以10億美元的價(jià)格,將自己創(chuàng)建的公司MySQL AB賣給了SUN,此后,隨著SUN被甲骨文收購MySQL的所有權(quán)也落入Oracle的手中.MariaDB名稱來自MichaelWidenius的女兒Maria的名字.

數(shù)據(jù)庫中的視圖是一個(gè)虛擬表,同真實(shí)的表一樣,視圖包含一系列帶有名稱的行和列數(shù)據(jù),行和列數(shù)據(jù)來自由定義視圖查詢所引用的表,并且在引用視圖時(shí)動(dòng)態(tài)生成,視圖是從一個(gè)或者多個(gè)表中導(dǎo)出的,視圖的行為與表非常相似,但視圖是一個(gè)虛擬表.在視圖中用戶可以使用SELECT語句查詢數(shù)據(jù),以及使用INSERT、UPDATE和DELETE修改記錄,視圖可以使用戶操作方便,而且可以保障數(shù)據(jù)庫系統(tǒng)的安全.

觸發(fā)器和存儲(chǔ)過程一樣,都是嵌入到MySQL的一段程序,觸發(fā)器是由事件來觸發(fā)某個(gè)操作,這些事件包括INSERT、UPDATAE和DELETE語句.如果定義了觸發(fā)程序,當(dāng)數(shù)據(jù)庫執(zhí)行這些語句的時(shí)候就會(huì)激發(fā)觸發(fā)器執(zhí)行相應(yīng)的操作,觸發(fā)程序是與表有關(guān)的命名數(shù)據(jù)庫對(duì)象,當(dāng)表上出現(xiàn)特定事件時(shí),將激活該對(duì)象.

MariaDB 視圖

視圖的含義:

視圖是一張?zhí)摂M表,是從數(shù)據(jù)庫中一個(gè)或多個(gè)表中導(dǎo)出來的表,視圖還可以從已經(jīng)存在的視圖基礎(chǔ)上定義,視圖一經(jīng)定義便存儲(chǔ)在數(shù)據(jù)庫中,與其相對(duì)應(yīng)的數(shù)據(jù)并沒有像表那樣在數(shù)據(jù)庫中再存儲(chǔ)一份,通過視圖看到的數(shù)據(jù)只是存放在基本表中的數(shù)據(jù).對(duì)視圖的操作與對(duì)表的操作一樣,可以對(duì)其進(jìn)行查詢、修改和刪除.當(dāng)對(duì)通過視圖看到的數(shù)據(jù)進(jìn)行修改時(shí),相應(yīng)的基本表的數(shù)據(jù)也要發(fā)生變化.同時(shí),若基本表的數(shù)據(jù)發(fā)生變化,則這種變化也可以自動(dòng)地反映到視圖中.

如下小例子:下面有個(gè)student表和 info表,現(xiàn)在我分別只想去除其中的ID號(hào),姓名,班級(jí),此時(shí)我們就需要用一個(gè)視圖來解決,取出指定的字段.

create table student (s_id int,name varchar(40)); create table info (s_id int,glass varchar(40),addr varchar(90));

視圖提供了一個(gè)很好的解決方法,創(chuàng)建視圖的信息來自表的部分信息,只取出需要的信息,這樣既能滿足需求也不破壞原有的表結(jié)構(gòu).

視圖的作用:

視圖不僅可以簡(jiǎn)化用戶對(duì)于數(shù)據(jù)的理解,也可以簡(jiǎn)化他們的操作,那些被經(jīng)常使用的查詢可以定義為視圖,從而使得用戶不必為以后的操作每次指定全部條件.

通過視圖用戶只能查詢和修改他們所能見到的數(shù)據(jù),數(shù)據(jù)庫中的其他數(shù)據(jù)則既看不見也取不到,數(shù)據(jù)庫授權(quán)命令可以使每個(gè)用戶對(duì)數(shù)據(jù)庫的檢索限制到特定的數(shù)據(jù)庫對(duì)象上,但不能授權(quán)到數(shù)據(jù)庫特定行和特定的列上.通過視圖,用戶可以被限制在數(shù)據(jù)的不同子集上.

◆創(chuàng)建視圖◆

在單表上創(chuàng)建視圖:

1.首先創(chuàng)建一個(gè)基本表table1并插入測(cè)試數(shù)據(jù).

MariaDB [lyshark]> create table table1(quantity INT,price INT); Query OK, 0 rows affected (0.02 sec)MariaDB [lyshark]> insert into table1 values(1,10); Query OK, 1 row affected (0.00 sec)MariaDB [lyshark]> insert into table1 values(2,30); Query OK, 1 row affected (0.00 sec)MariaDB [lyshark]> insert into table1 values(3,50); Query OK, 1 row affected (0.00 sec)

2.創(chuàng)建視圖,在table1表上創(chuàng)建一個(gè)view_tab1視圖,其中代碼有三個(gè)字段x,y,totle,SQL語句如下:

MariaDB [lyshark]> create view view_tab1(x,y,totle)-> AS SELECT quantity,price,quantity * price-> FROM table1; Query OK, 0 rows affected (0.00 sec)

3.緊接著我們使用視圖來查詢創(chuàng)建的新表格.

在多張表上創(chuàng)建視圖:

1.首先創(chuàng)建兩個(gè)測(cè)試表并插入一些數(shù)據(jù),這里我們就創(chuàng)建要給student和info兩個(gè)表,SQL語句如下:

MariaDB [lyshark]> create table student-> (-> s_id INT,-> name VARCHAR(40)-> ); Query OK, 0 rows affected (0.01 sec)MariaDB [lyshark]> create table info-> (-> s_id INT,-> glass VARCHAR(40),-> addr VARCHAR(90)-> ); Query OK, 0 rows affected (0.00 sec)MariaDB [lyshark]> insert into student values(1,'wang'),(2,'rui'); Query OK, 2 rows affected (0.33 sec) Records: 2 Duplicates: 0 Warnings: 0MariaDB [lyshark]> insert into info values(1,'wuban','henan'),(2,'sanban','hebei'),(3,'yiban','s handong'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0

2.接下來我們創(chuàng)建一個(gè)視圖stu_glass,其中有三個(gè)參數(shù)(id,name,glass),分別對(duì)應(yīng)兩個(gè)表的不同字段,并且通過where條件限定ID號(hào)相同的關(guān)聯(lián)在一起.

MariaDB [lyshark]> create view stu_glass(id,name,glass)-> AS select student.s_id,student.name,info.glass-> FROM student,info where student.s_id = info.s_id;Query OK, 0 rows affected (0.00 sec)MariaDB [lyshark]> select * from stu_glass; +------+------+--------+ | id | name | glass | +------+------+--------+ | 1 | wang | wuban | | 2 | rui | sanban | +------+------+--------+ 2 rows in set (0.01 sec)

以上例子就解決了剛開始那個(gè)問題,通過這個(gè)視圖可以很好地保護(hù)基本表中的數(shù)據(jù).

◆查看視圖◆

使用desc查看視圖表結(jié)構(gòu):

使用show table status查看視圖:

MariaDB [lyshark]> show table status like 'stu_glass' \G *************************** 1. row ***************************Name: stu_glassEngine: NULLVersion: NULLRow_format: NULLRows: NULLAvg_row_length: NULLData_length: NULL Max_data_length: NULLIndex_length: NULLData_free: NULLAuto_increment: NULLCreate_time: NULLUpdate_time: NULLCheck_time: NULLCollation: NULLChecksum: NULLCreate_options: NULLComment: VIEW 1 row in set (0.00 sec)

使用show create view查看視圖詳細(xì)信息:

MariaDB [lyshark]> show create view stu_glass \G *************************** 1. row ***************************View: stu_glassCreate View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `stu_glass` AS select `student`.`s_id` AS `id`,`student`.`name` AS `name`,`info`.`glass` AS `glass` from (`student` join `info`) where (`student`.`s_id` = `info`.`s_id`) character_set_client: utf8 collation_connection: utf8_general_ci 1 row in set (0.00 sec)MariaDB [lyshark]>

在views表中查看視圖詳細(xì)信息:

◆更新與刪除視圖◆

alter語句修改視圖:

使用alter語句,修改視圖view_tab1,SQL語句如下:

update更新視圖:

MariaDB [lyshark]> select * from stu_glass; +------+------+--------+ | id | name | glass | +------+------+--------+ | 1 | wang | wuban | | 2 | rui | sanban | +------+------+--------+ 2 rows in set (0.00 sec)MariaDB [lyshark]> update stu_glass SET id=3 where name="rui"; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0MariaDB [lyshark]> select * from stu_glass; +------+------+-------+ | id | name | glass | +------+------+-------+ | 1 | wang | wuban | | 3 | rui | yiban | +------+------+-------+ 2 rows in set (0.00 sec)

刪除視圖中指定字段:注意,聯(lián)合字段的視圖無法刪除.

MariaDB [lyshark]> select * from view_tab1; +------+------+-------+ | x | y | totle | +------+------+-------+ | 1 | 10 | 10 | | 2 | 30 | 60 | | 3 | 50 | 150 | +------+------+-------+ 3 rows in set (0.00 sec)MariaDB [lyshark]> delete from view_tab1 where x=1; Query OK, 1 row affected (0.00 sec)MariaDB [lyshark]> select * from view_tab1; +------+------+-------+ | x | y | totle | +------+------+-------+ | 2 | 30 | 60 | | 3 | 50 | 150 | +------+------+-------+ 2 rows in set (0.00 sec)

刪除一個(gè)視圖:

1.查詢一下,我們比如要?jiǎng)h除view_tab1和view_tab2兩個(gè)視圖.

2.通過drop view語句直接刪除掉.

MariaDB [lyshark]> drop view if exists view_tab1; Query OK, 0 rows affected (0.00 sec)MariaDB [lyshark]> drop view if exists view_tab2; Query OK, 0 rows affected (0.00 sec)

3.再次查詢,發(fā)現(xiàn)沒有了,刪除成功.

MariaDB觸發(fā)器

觸發(fā)器(Trigger)是個(gè)特殊的存儲(chǔ)過程,不同的是,執(zhí)行存儲(chǔ)過程要使用CALL語句來調(diào)用,而觸發(fā)器的執(zhí)行不需要使用CALL語句來調(diào)用,也不需要手工啟動(dòng),只要當(dāng)一個(gè)預(yù)定義的事件發(fā)生的時(shí)候,就會(huì)被MySQL自動(dòng)調(diào)用,觸發(fā)器可以查詢其他表,而且可以包含復(fù)雜的SQL語句,它們主要用于滿足復(fù)雜的業(yè)務(wù)規(guī)則或要求.

一般來說創(chuàng)建觸發(fā)器的基本語法如下:

create trigger trigger_name trigger_time trigger_event ON table_name FOR EACH ROW trigger_stmt#---------------------------------------------------------------- #[參數(shù)解釋]trigger_name #觸發(fā)器名稱 trigger_time #標(biāo)識(shí)觸發(fā)時(shí)機(jī)(befor/after) trigger_event #標(biāo)識(shí)觸發(fā)事件 table_name #建立觸發(fā)器表名,即在那張表上建立觸發(fā)器 trigger_stmt #觸發(fā)器執(zhí)行語句

而創(chuàng)建多個(gè)執(zhí)行語句的觸發(fā)器的語法如下:

create trigger trigger_name trigger_time trigger_event ON table_name FOR EACH ROW BEGIN 執(zhí)行語句塊... END

◆創(chuàng)建觸發(fā)器◆

創(chuàng)建只有一條執(zhí)行語句的觸發(fā)器:

1.首先創(chuàng)建一個(gè)account表,表中有兩個(gè)字段,分別是acct_num字段(INT),amount字段(float).

2.創(chuàng)建一個(gè)名為ins_sum的觸發(fā)器,觸發(fā)條件是向數(shù)據(jù)表account插入數(shù)據(jù)之前,對(duì)新插入的amount字段值進(jìn)行求和計(jì)算.

MariaDB [lyshark]> create trigger ins_sum BEFORE INSERT ON account-> FOR EACH ROW SET @sum=@sum+NEW.amount; Query OK, 0 rows affected (0.00 sec)MariaDB [lyshark]> set @sum=0; Query OK, 0 rows affected (0.00 sec)MariaDB [lyshark]> insert into account values(1,1.00),(2,2.00); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0MariaDB [lyshark]> select @sum; +------+ | @sum | +------+ | 3.00 | +------+ 1 row in set (0.00 sec)

以上例子,首先創(chuàng)建一個(gè)account表,再向表account插入數(shù)據(jù)之前,計(jì)算所有新插入的account表的amount值之和,觸發(fā)器的名稱為ins_sum,條件是在向表中插入數(shù)據(jù)之前觸發(fā).

創(chuàng)建具有多條執(zhí)行語句的觸發(fā)器:

1.首相創(chuàng)建4個(gè)測(cè)試表格,并寫入以下測(cè)試字段.

MariaDB [lyshark]> create table test1(a1 INT); Query OK, 0 rows affected (0.00 sec)MariaDB [lyshark]> create table test2(a2 INT); Query OK, 0 rows affected (0.00 sec)MariaDB [lyshark]> create table test3(a3 INT NOT NULL AUTO_INCREMENT primary key); Query OK, 0 rows affected (0.00 sec)MariaDB [lyshark]> create table test4(-> a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,-> b4 INT DEFAULT 0-> ); Query OK, 0 rows affected (0.07 sec)

2.創(chuàng)建一個(gè)包含多個(gè)執(zhí)行語句的觸發(fā)器,當(dāng)test1有數(shù)據(jù)插入時(shí),執(zhí)行觸發(fā)語句,代碼如下:

MariaDB [lyshark]> DELIMITER // MariaDB [lyshark]> create trigger testref BEFORE INSERT ON test1-> FOR EACH ROW-> BEGIN-> insert into test2 set a2=NEW.a1;-> delete from test3 where a3=NEW.a1;-> update test4 set b4=b4+1 where a4=NEW.a1;-> END-> // MariaDB [lyshark]> DELIMITER ;;

以上代碼創(chuàng)建了一個(gè)名為testref的觸發(fā)器,這個(gè)觸發(fā)器的觸發(fā)條件是在向表test1插入數(shù)據(jù)前執(zhí)行觸發(fā)器的語句,具體執(zhí)行代碼如下:

MariaDB [lyshark]> insert into test1 values (1),(3),(1),(7),(1),(4); Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0

當(dāng)test1表格插入數(shù)據(jù)后,其他表格也會(huì)出現(xiàn)相同的數(shù)據(jù),這就是觸發(fā)器所做的貢獻(xiàn).

MariaDB [lyshark]> select * from test1; +------+ | a1 | +------+ | 1 | | 3 | | 1 | | 7 | | 1 | | 4 | +------+ 6 rows in set (0.00 sec)MariaDB [lyshark]> select * from test2; +------+ | a2 | +------+ | 1 | | 3 | | 1 | | 7 | | 1 | | 4 | +------+ 6 rows in set (0.00 sec)

關(guān)于觸發(fā)器的另一個(gè)小實(shí)驗(yàn):

1.先來創(chuàng)建一個(gè)數(shù)據(jù)表.

2.創(chuàng)建一個(gè)trig_insert的觸發(fā)器,在向表account插入數(shù)據(jù)之后會(huì)向表myevent插入一組數(shù)據(jù),代碼如下:

MariaDB [lyshark]> create trigger trig_insert AFTER INSERT ON account-> FOR EACH ROW INSERT INTO myevent values(2,'after insert'); Query OK, 0 rows affected (0.00 sec)

3.此時(shí)我們執(zhí)行插入語句,向account表插入數(shù)據(jù),查詢myevent表,發(fā)現(xiàn)自動(dòng)添加上了,說明觸發(fā)器生效了.

MariaDB [lyshark]> insert into account values(1,1.00),(2,2.00); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0MariaDB [lyshark]> select * from myevent; +------+--------------+ | id | name | +------+--------------+ | 2 | after insert | | 2 | after insert | +------+--------------+ 2 rows in set (0.00 sec)

◆查看與刪除觸發(fā)器◆

show triggers 查看所有觸發(fā)器:

MariaDB [lyshark]> show triggers \G; *************************** 1. row ***************************Trigger: ins_sumEvent: INSERTTable: accountStatement: SET @sum=@sum+NEW.amountTiming: BEFORECreated: NULLsql_mode:Definer: root@localhost character_set_client: utf8 collation_connection: utf8_general_ciDatabase Collation: latin1_swedish_ci *************************** 2. row ***************************Trigger: trig_insertEvent: INSERTTable: accountStatement: INSERT INTO myevent values(2,'after insert')Timing: AFTERCreated: NULLsql_mode:Definer: root@localhost character_set_client: utf8 collation_connection: utf8_general_ciDatabase Collation: latin1_swedish_ci

在triggers表中查看觸發(fā)器:

MariaDB [lyshark]> select * from information_schema.triggers \G;*************************** 1. row ***************************TRIGGER_CATALOG: defTRIGGER_SCHEMA: lysharkTRIGGER_NAME: ins_sumEVENT_MANIPULATION: INSERTEVENT_OBJECT_CATALOG: defEVENT_OBJECT_SCHEMA: lysharkEVENT_OBJECT_TABLE: accountACTION_ORDER: 0ACTION_CONDITION: NULLACTION_STATEMENT: SET @sum=@sum+NEW.amountACTION_ORIENTATION: ROWACTION_TIMING: BEFORE ACTION_REFERENCE_OLD_TABLE: NULL ACTION_REFERENCE_NEW_TABLE: NULLACTION_REFERENCE_OLD_ROW: OLDACTION_REFERENCE_NEW_ROW: NEWCREATED: NULLSQL_MODE:DEFINER: root@localhostCHARACTER_SET_CLIENT: utf8COLLATION_CONNECTION: utf8_general_ciDATABASE_COLLATION: latin1_swedish_ci

刪除觸發(fā)器: 刪除lyshark數(shù)據(jù)庫中的,ins觸發(fā)器,SQL如下:

1.先查詢一下觸發(fā)器的名稱,SQL語句如下:

2.一條命令刪除.

MariaDB [lyshark]> drop trigger lyshark.ins_sum; Query OK, 0 rows affected (0.00 sec)

轉(zhuǎn)載于:https://www.cnblogs.com/LyShark/p/10197881.html

《新程序員》：云原生和全面數(shù)字化實(shí)踐50位技術(shù)專家共同創(chuàng)作，文字、視頻、音頻交互閱讀

總結(jié)

以上是生活随笔為你收集整理的MariaDB 视图与触发器(11)的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

上一篇： JavaScript的几个概念简单理解（
下一篇： 2018总结及2019计划