854
MySQL實戰係列3：視圖、存儲過程、函數、索引全解析
作者介紹
索寧，擅長Python開發、MySQL、前端等眾多技術領域，曾負責眾多企業安全架構解決方案 ，涉獵行業有媒體、出版社、航空運輸、醫療、軍隊、政府、教育等。
一、視圖
視圖是查詢命令結果構成的一個虛擬表（非真實存在），其本質是【根據SQL語句獲取動態的數據集，並為其命名】，用戶使用時隻需使用【名稱】即可獲取結果集合，並可以當作表來查詢使用。
1創建視圖
--格式：CREATE VIEW 視圖名稱 AS SQL語句
CREATE VIEW v1 AS
SELET nid,
name
FROM
A
WHERE
nid > 4
2刪除視圖
--格式：DROP VIEW 視圖名稱
DROP VIEW v1
3修改視圖
-- 格式：ALTER VIEW 視圖名稱 AS SQL語句
ALTER VIEW v1 AS
SELET A.nid,
B. NAME
FROM
A
LEFT JOIN B ON A.id = B.nid
LEFT JOIN C ON A.id = C.nid
WHERE
A.id > 2
AND C.nid < 5
4使用視圖
視圖的使用和普通表一樣，由於視圖是虛擬表，所以無法對其真實表進行創建、更新和刪除操作，僅做查詢用。
select * from v1
二、觸發器
對某個表進行【增/刪/改】操作的前後觸發一些操作即為觸發器，如果希望觸發增刪改的行為之前或之後做操作時，可以使用觸發器，觸發器用於自定義用戶對表的行進行【增/刪/改】前後的行為。
1基本語法
# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
# 插入後
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
# 刪除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# 刪除後
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
# 更新後
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
2創建觸發器
創建觸發器基本語法是以下code：
但有一點要注意，觸發器內關鍵字NEW表示即將插入的數據行，OLD表示即將刪除的數據行。
# 插入前觸發器
delimiter //
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
IF NEW. NAME == 'nick' THEN
INSERT INTO tb2 (NAME)
VALUES
('aa')
END
END//
delimiter ;
# 插入後觸發器
delimiter //
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
IF NEW. num = 666 THEN
INSERT INTO tb2 (NAME)
VALUES
('666'),
('666') ;
ELSEIF NEW. num = 555 THEN
INSERT INTO tb2 (NAME)
VALUES
('555'),
('555') ;
END IF;
END//
delimiter
3刪除觸發器
DROP TRIGGER tri_after_insert_tb1;
4使用觸發器
觸發器是由於對表的增、刪、改操作時被動執行的。
insert into tb1(num) values(666)
三、存儲過程
存儲過程是一個SQL語句集合，類似函數，需要主動調用。
1創建存儲過程
# 無參數存儲過程
# 創建存儲過程
delimiter //
create procedure p1()
BEGIN
select * from t1;
END//
delimiter ;
# 執行存儲過程
call p1()
都說了類似函數，那必須得可以接收參數，且參數有三類：
-
in 僅用於傳入參數用
-
out 僅用於返回值用
-
inout 既可以傳入又可以當作返回值
# 有參數存儲過程
# 創建存儲過程
delimiter \\ # 結尾分號改為\\
create procedure p1(
in i1 int,
in i2 int,
inout i3 int,
out r1 int
)
BEGIN
DECLARE temp1 int; # 創建申明局部變量
DECLARE temp2 int default 0;
set temp1 = 1;
set r1 = i1 + i2 + temp1 + temp2;
set i3 = i3 + 100;
end\\
delimiter ;
# 執行存儲過程
DECLARE @t1 INT default 3;
DECLARE @t2 INT;
CALL p1 (1, 2 ,@t1, @t2);
SELECT @t1,@t2;
2刪除存儲過程drop procedure proc_name;
3執行存儲過程
執行為函數名加括號；
DECLARE代表創建一個局部變量
# 無參數
call proc_name()
# 有參數，全in
call proc_name(1,2)
# 有參數，有in，out，inout
DECLARE @t1 INT;
DECLARE @t2 INT default 3;
call proc_name(1,2,@t1,@t2)
-
pymysql執行存儲過程
四、函數
該講講真正的函數了，與存儲過程不同的是有return值。
1自定義函數
delimiter \\
create function f1(
i1 int,
i2 int)
returns int
BEGIN
declare num int;
set num = i1 + i2;
return(num);
END \\
delimiter ;
2刪除函數
drop function func_name;
3執行函數
# 獲取返回值
declare @i VARCHAR(32);
select UPPER('nick') into @i;
SELECT @i;
# 在查詢中使用
select f1(11,nid) ,name from tb2;
4內置函數
當然MySQL也不例外，有著非常好用的內置函數。
五、事務
事務用於將某些操作的多個SQL語句作為原子性操作，隻有出現錯誤，即可回滾到原來的狀態，從而保證數據庫數據完整性。
delimiter 為改變結束符號，默認";"
# 支持事務的存儲過程
delimiter \\
create PROCEDURE p1(
OUT p_return_code tinyint
)
BEGIN
DECLARE exit handler for sqlexception
BEGIN
-- ERROR
set p_return_code = 1;
rollback;
END;
DECLARE exit handler for sqlwarning
BEGIN
-- WARNING
set p_return_code = 2;
rollback;
END;
START TRANSACTION;
DELETE from tb1;
insert into tb2(name)values('seven');
COMMIT;
-- SUCCESS
set p_return_code = 0;
END\\
delimiter ;
# 執行存儲過程
DECLARE @i TINYINT;
call p1(@i);
select @i;
六、索引
1索引概述
-
索引是表的索引目錄，在查找內容之前先查目錄中查找索引位置，從而快速定位查詢數據；
-
可以理解成新華字典中的索引；
-
索引會保存在額外的文件中。
2索引種類
一般的索引種類及功能：
-
普通索引：僅加速查詢
-
唯一索引：加速查詢 + 列值唯一（可以有null）
-
主鍵索引：加速查詢 + 列值唯一 + 表中隻有一個（不可以有null）
-
組合索引：多列值組成一個索引，專門用於組合搜索，其效率大於索引合並
-
全文索引：對文本的內容進行分詞，進行搜索
-
索引合並：使用多個單列索引組合查詢搜索。
-
覆蓋索引：select的數據列隻用從索引中就能夠取得，不必讀取數據行，換句話說查詢列要被所建的索引覆蓋。
a、普通索引
# 創建表 + 索引
create table in1(
nid int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
extra text,
index ix_name (name)
)
# 創建索引
create index index_name on table_name(column_name)
# 刪除索引
drop index_name on table_name;
# 查看索引
show index from table_name;
#注意：對於創建索引時如果是BLOB 和 TEXT 類型，必須指定length。
create index ix_extra on in1(extra(32));
b、唯一索引
# 創建表 + 唯一索引
create table in1(
nid int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
extra text,
unique ix_name (name)
)
# 創建唯一索引
create unique index 索引名 on 表名(列名)
# 刪除唯一索引
drop unique index 索引名 on 表名
c、主鍵索引
# 創建表 + 創建主鍵
create table in1(
nid int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
extra text,
index ix_name (name)
)
OR
create table in1(
nid int not null auto_increment,
name varchar(32) not null,
email varchar(64) not null,
extra text,
primary key(ni1),
index ix_name (name)
)
# 創建主鍵
alter table 表名 add primary key(列名);
# 刪除主鍵
alter table 表名 drop primary key;
alter table 表名 modify 列名 int, drop primary key;
d、組合索引
組合索引是多個列組合成一個索引來查詢。
應用場景：頻繁的同時使用多列來進行查詢，如：where name = 'nick' and age = 18。
# 創建表
create table mess(
nid int not null auto_increment primary key,
name varchar(32) not null,
age int not null
)
# 創建組合索引
create index ix_name_age on mess(name,age);
如上創建組合索引之後，查詢一定要注意：
-
name and email -- >使用索引，name一定要放前麵
-
name -- >使用索引
-
email -- >不使用索引
注意：同時搜索多個條件時，組合索引的性能效率好過於多個單一索引合並。
3相關命令
# 查看索引
show index from 表名
# 查看執行時間
set profiling = 1; # 開啟profiling
SQL... # 執行SQL語句
show profiles; # 查看結果
4如何正確使用索引
# like '%xx'，避免%_寫在開頭
select * from tb1 where name like '%n';
# 使用函數
select * from tb1 where reverse(name) = 'nick';
# or
select * from tb1 where nid = 1 or email = '630571017@qq.com';
注：當or條件中有未建立索引的列才失效，否則會走索引
# 類型不一致
如果列是字符串類型，傳入條件是必須用引號引起來。
select * from tb1 where name = 999;
# !=，不等於
select * from tb1 where name != 'nick'
注：如果是主鍵，則還是會走索引
select * from tb1 where nid != 123
# >，大於
select * from tb1 where name > 'nick'
注：如果是主鍵或索引是整數類型，則還是會走索引
select * from tb1 where nid > 123
select * from tb1 where num > 123
# order by
select email from tb1 order by name desc;
當根據索引排序時候，選擇的映射如果不是索引，則不走索引
注：如果對主鍵排序，則還是走索引：
select * from tb1 order by nid desc;
# 組合索引最左前綴
如果組合索引為：(name,email)，查詢使用：
name and email -- 使用索引
name -- 使用索引
email -- 不使用索引
5注意事項
# 避免使用select *
# count(1)或count(列) 代替 count(*)
# 創建表時盡量時 char 代替 varchar
# 表的字段順序固定長度的字段優先
# 組合索引代替多個單列索引（經常使用多個條件查詢時）
# 盡量使用短索引
# 使用連接（JOIN）來代替子查詢(Sub-Queries)
# 連表時注意條件類型需一致
# 索引散列值（重複少）不適合建索引，例：性別不適合
6執行計劃
explain + 查詢SQL 用於顯示SQL執行信息參數，根據參考信息可以進行SQL優化。
id
查詢順序標識
特別的：如果使用union連接氣值可能為null
7慢日誌查詢
日誌可以記錄哪些查詢語句慢，沒有走索引，用戶發現問題，從而解決優化
a、配置MySQL自動記錄慢日誌
修改配置文件 my.ini，沒有則添加
slow_query_log = OFF # 是否開啟慢日誌記錄
long_query_time = 2 # 時間限製，超過此時間，則記錄
slow_query_log_file = /usr/slow.log # 日誌文件
log_queries_not_using_indexes = OFF # 為使用索引的搜索是否記錄
# 查看當前配置信息：
show variables like '%query%'
# 修改當前配置：
set global 變量名 = 值
b、查看MySQL慢日誌
七、其它
自然所有的東西都離不開基本的語法語句啦，循環語句與判斷語句
1條件語句
if then\ elseif then\ else\ end if
# if條件語句
delimiter \\
CREATE PROCEDURE proc_if ()
BEGIN
declare i int default 0;
if i = 1 THEN
SELECT 1;
ELSEIF i = 2 THEN
SELECT 2;
ELSE
SELECT 7;
END IF;
END\\
delimiter ;
2循環語句
分為三種循環語句：while循環、repeat循環、loop循環。
# while循環
delimiter \\
CREATE PROCEDURE proc_while ()
BEGIN
DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
SELECT
num ;
SET num = num + 1 ;
END WHILE ;
END\\
delimiter ;
# repeat循環，相當於 do while
delimiter \\
CREATE PROCEDURE proc_repeat ()
BEGIN
DECLARE i INT ;
SET i = 0 ;
repeat
select i;
set i = i + 1;
until i >= 5
end repeat;
END\\
delimiter ;
# loop
delimiter \\
CREATE PROCEDURE proc_loop ()
BEGIN
declare i int default 0;
loop_label: loop
select i;
set i=i+1;
if i>=5 then
leave loop_label;
end if;
end loop;
END\\
delimiter ;
3動態執行SQL語句
# 動態執行SQL
delimiter \\
DROP PROCEDURE IF EXISTS proc_sql \\
CREATE PROCEDURE proc_sql ()
BEGIN
declare p1 int;
set p1 = 11;
set @p1 = p1;
PREPARE prod FROM 'select * from tb2 where nid > ?';
EXECUTE prod USING @p1;
DEALLOCATE prepare prod;
END\\
delimiter ;
# 方式二
delimiter \\
DROP PROCEDURE IF EXISTS proc_sql \\ # 如果不存在則添加，否則不做操作
CREATE PROCEDURE proc_sql (
in strSQL char(128),
in nidd int
)
BEGIN
set @sqll = strSQL;
set @p1 = nidd;
PREPARE prod FROM @sqll;
EXECUTE prod USING @p1;
DEALLOCATE prepare prod;
END\\
delimiter ;
call proc_sql('select * from suoning2 where id > ?','2');
原文發布時間為：2016-11-21
本文來自雲棲社區合作夥伴DBAplus
最後更新：2017-05-11 12:01:17