本文源碼:GitHub·點這里 || GitEE·點這里
創(chuàng)新互聯(lián)從2013年成立,先為九原等服務建站,九原等地企業(yè),進行企業(yè)商務咨詢服務。為九原企業(yè)網(wǎng)站制作PC+手機+微官網(wǎng)三網(wǎng)同步一站式服務解決您的所有建站問題。
存儲程序是被存儲在服務器中的組合SQL語句,經編譯創(chuàng)建并保存在數(shù)據(jù)庫中,用戶可通過存儲過程的名字調用執(zhí)行。存儲過程核心思想就是數(shù)據(jù)庫SQL語言層面的封裝與重用性。使用存儲過程可以較少應用系統(tǒng)的業(yè)務復雜性,但是會增加數(shù)據(jù)庫服務器系統(tǒng)的負荷,所以在使用時需要綜合業(yè)務考慮。
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
-- 創(chuàng)建存儲過程
DROP PROCEDURE IF EXISTS p01_discount ;
CREATE PROCEDURE p01_discount(IN consume NUMERIC(5,2),OUT payfee NUMERIC(5,2))
BEGIN
-- 判斷收費方式
IF(consume>100.00 AND consume<=300.00) THEN
SET payfee=consume*0.8;
ELSEIF (consume>300.00) THEN
SET payfee=consume*0.6;
ELSE
SET payfee = consume;
END IF;
SELECT payfee AS result;
END ;
-- 調用存儲過程
CALL p01_discount(100.0,@discount);
提供一張數(shù)據(jù)表
CREATE TABLE `t03_proced` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
`temp_name` varchar(20) DEFAULT NULL COMMENT '名稱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='觸發(fā)器寫數(shù)據(jù)';
存儲程序
根據(jù)傳入的參數(shù),判斷寫入t03_proced表的數(shù)據(jù)條數(shù)。
DROP PROCEDURE IF EXISTS p02_batch_add ;
CREATE PROCEDURE p02_batch_add(IN count INT(11))
BEGIN
DECLARE temp int default 0;
WHILE temp < count DO
INSERT INTO t03_proced(temp_name) VALUES ('pro_name');
SET temp = temp+1 ;
END WHILE;
END ;
-- 測試:寫入10條數(shù)據(jù)
call p02_batch_add(10);
存儲過程在實際開發(fā)中的應用不是很廣泛,通常復雜的業(yè)務場景都在應用層面開發(fā),可以更好的管理維護和優(yōu)化。
假如在單表數(shù)據(jù)寫入的簡單場景下,基于應用程序寫入,或者數(shù)據(jù)庫連接的客戶端寫入,相比存儲過程寫入的速度就會慢很多,存儲過程在很大程度上沒有網(wǎng)絡通信開銷,解析開銷,優(yōu)化器開銷等。
視圖本身是一張?zhí)摂M表,不存放任何數(shù)據(jù)。在使用SQL語句訪問視圖的時候,獲取的數(shù)據(jù)是MySQL從其它表中生成的,視圖和表在同一個命名空間。視圖查詢數(shù)據(jù)相對安全,視可以隱藏一些數(shù)據(jù)和結構,只讓用戶看見權限內的數(shù)據(jù),使復雜的查詢易于理解和使用。
現(xiàn)在基于用戶和訂單管理演示視圖的基本用法。
CREATE TABLE v01_user (
id INT(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
user_name VARCHAR(20) DEFAULT NULL COMMENT '用戶名',
phone VARCHAR(20) DEFAULT NULL COMMENT '手機號',
pass_word VARCHAR(64) DEFAULT NULL COMMENT '密碼',
card_id VARCHAR(18) DEFAULT NULL COMMENT '身份證ID',
pay_card VARCHAR(25) DEFAULT NULL COMMENT '卡號',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '用戶表';
CREATE TABLE v02_order (
id INT(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
user_id INT(11) NOT NULL COMMENT '用戶ID',
order_no VARCHAR(32) DEFAULT NULL COMMENT '訂單編號',
good_name VARCHAR(60) DEFAULT NULL COMMENT '商品名稱',
good_id INT(11) DEFAULT NULL COMMENT '商品ID',
num INT(11) DEFAULT NULL COMMENT '購買數(shù)量',
total_price DECIMAL(10,2) DEFAULT NULL COMMENT '總價格',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '訂單表';
CREATE OR REPLACE VIEW view_name
AS select_statement
注意事項:表和視圖共享數(shù)據(jù)庫中相同的名稱空間,因此,數(shù)據(jù)庫不能包含具有相同名稱的表和視圖。
CREATE OR REPLACE
VIEW user_order_view AS SELECT
t1.id,t1.user_name,t2.order_no,t2.good_id,
t2.good_name,t2.num,t2.total_price
FROM v01_user t1
LEFT JOIN v02_order t2 ON t2.user_id = t1.id;
這里和MySQL的表查詢基本一致,可以使用各種查詢條件。
SELECT * FROM user_order_view WHERE user_name='Cicada';
SHOW CREATE VIEW user_order_view ;
ALTER VIEW view_name AS select_statement ;
DROP VIEW [IF EXISTS] view_name ;
在指定條件允許的情況下,可以通過在視圖上操作更新,刪除,甚至寫入數(shù)據(jù),進而更新視圖所涉及的相關表。
UPDATE user_order_view SET user_name='smile' WHERE id='1';
這里就通過對視圖執(zhí)行更新操作,進而更新v01_user
表數(shù)據(jù)。如果視圖定義時使用聚合函數(shù),分組等特殊操作,則無法更新。MySQL不支持在視圖上創(chuàng)建觸發(fā)器。
服務器會把視圖查詢SQL的數(shù)據(jù)保存在臨時表中,臨時表的結構和視圖字段結構一致,這樣是SQL查詢優(yōu)化中最忌諱的操作,數(shù)據(jù)量稍微偏大,就會嚴重影響性能。如果視圖無法和原有表產生一對一的映射關系,就會產生臨時表,由此也可見視圖并不是很簡單,甚至是非常復雜的功能。
服務器基于視圖中使用的表執(zhí)行查詢,最后把查詢結構合并后返回給客戶端。
執(zhí)行如下查詢語句,可以分析執(zhí)行的性能參數(shù)。
EXPLAIN SELECT * FROM user_order_view ;
觀察查詢結果中select_type
字段,如果是DERIVED
則說明使用臨時表。這里SQL執(zhí)行分析的語法后面優(yōu)化部分再詳解。
MySQL并不支持在視圖中創(chuàng)建索引,使用視圖的時候可能會引發(fā)很多查詢性能問題,所以建議使用的時候要慎重,多角度審視和測試。
基于視圖的查詢,可以修改部分表結構,只要不是在視圖中使用的字段,就不會影響視圖的查詢。
GitHub·地址
https://github.com/cicadasmile/mysql-data-base
GitEE·地址
https://gitee.com/cicadasmile/mysql-data-base
分享標題:MySQL基礎篇(04):存儲過程和視圖,用法和特性詳解
網(wǎng)站地址:http://bm7419.com/article32/pschsc.html
成都網(wǎng)站建設公司_創(chuàng)新互聯(lián),為您提供網(wǎng)站內鏈、做網(wǎng)站、虛擬主機、Google、動態(tài)網(wǎng)站、ChatGPT
聲明:本網(wǎng)站發(fā)布的內容(圖片、視頻和文字)以用戶投稿、用戶轉載內容為主,如果涉及侵權請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內容未經允許不得轉載,或轉載時需注明來源: 創(chuàng)新互聯(lián)