MySQL學習筆記（二）_ZenDei技術網路在線

一、概述本篇文章主要介紹MySQL視圖，觸發器，存儲過程，函數，事務，索引等內容，本節內容在Navicat上進行試驗，具體軟體安裝及操作不再贅述。二、視圖視圖是一個虛擬表（非真實存在），其本質是【根據SQL語句獲取動態的數據集，併為其命名】，用戶使用時只需使用【名稱】即可獲取結果集，可以將該結 ...

一、概述

　　本篇文章主要介紹MySQL視圖，觸發器，存儲過程，函數，事務，索引等內容，本節內容在Navicat上進行試驗，具體軟體安裝及操作不再贅述。

二、視圖　

　　視圖是一個虛擬表（非真實存在），其本質是【根據SQL語句獲取動態的數據集，併為其命名】，用戶使用時只需使用【名稱】即可獲取結果集，可以將該結果集當做表來使用。使用視圖我們可以把查詢過程中的臨時表摘出來，用視圖去實現，這樣以後再想操作該臨時表的數據時就無需重寫複雜的sql了，直接去視圖中查找即可，但視圖有明顯地效率問題，並且視圖是存放在資料庫中的，如果我們程式中使用的sql過分依賴資料庫中的視圖，即強耦合，那就意味著擴展sql極為不便，因此並不推薦使用

　　如某個SQL語句獲取的動態結果集重覆使用，使用視圖可以很大程度上減少我們的SQL語句代碼量

創建部門表
create table dep(
id int primary key auto_increment,
name char(32)
);

創建用戶表
create table user(
id int primary key auto_increment,
name char(32),
dep_id int,
foreign key(dep_id) references dep(id)
);

數據插入

INSERT into dep(name) VALUES('財政部');
INSERT into dep(name) VALUES('後勤部');
INSERT into dep(name) VALUES('研發部');
INSERT into dep(name) VALUES('採購部');

INSERT into user(name,salary,dep_id) VALUES('小花','5000','1');
INSERT into user(name,salary,dep_id) VALUES('太陽','8000','3');
INSERT into user(name,salary,dep_id) VALUES('豆豆','12000','2');
INSERT into user(name,salary,dep_id) VALUES('小虎','11000','2');

試驗表創建

如我們要查詢工資高於所有人員平均工資的人員姓名，通常我們這樣寫

SELECT name from user WHERE salary >(SELECT avg(salary) from user);

此時我們可以使用視圖查詢

CREATE VIEW salary_avg_view AS SELECT avg(salary) from user;

SELECT name from user WHERE salary >(SELECT * from salary_avg_view);

1、創建視圖（--格式：CREATE VIEW 視圖名稱 AS SQL語句）

CREATE VIEW salary_avg_view AS SELECT avg(salary) from user;

2、刪除視圖（--格式：DROP VIEW 視圖名稱）

DROP VIEW salary_avg_view;

3、修改視圖（-- 格式：ALTER VIEW 視圖名稱 AS SQL語句）

ALTER VIEW salary_avg_view AS SELECT avg(salary) from user;


#此處並未做修改，實際操作可根據需要修改

4、使用視圖（當作普通表使用即可）

SELECT * from salary_avg_view

三、觸發器

對某個表進行【增/刪/改】操作的前後如果希望觸發某個特定的行為時，可以使用觸發器，觸發器用於定製用戶對錶的行進行【增/刪/改】前後的行為。註：沒有查

現在我們新建一個user人數統計的表user_count，設置user_total欄位

CREATE TABLE user_count(
    id int NOT NULL auto_increment PRIMARY KEY,
    user_total int DEFAULT 0
);

user表中已有4人，這裡就偷懶直接設置下user_total值為4

UPDATE user_count SET user_total=4;

下麵定義一個觸發器，規定當user表中新增時，user_count加1，當user表中有刪除行時，user_count減1

CREATE TRIGGER tigger_user_count_insert AFTER INSERT
ON user FOR EACH ROW
UPDATE user_count SET user_total=user_total+1 WHERE id=1;

CREATE TRIGGER tigger_user_count_delete AFTER DELETE
ON user FOR EACH ROW
UPDATE user_count SET user_total=user_total-1 WHERE id=1;

此處補張圖

此時，user_total值為4，我們試著插入一個值

測試代碼：

INSERT INTO user(name,salary,dep_id) VALUES('小明',8000,4);

執行後user_total欄位變為5

同理，刪除的觸發器的測試

測試代碼：

DELETE from user WHERE name='小明';

執行後user_total欄位變為4

下麵具體介紹下觸發器的使用

1、創建觸發器

CREATE

    [DEFINER = { user | CURRENT_USER }]

    TRIGGER trigger_name

    trigger_time trigger_event

    ON tbl_name FOR EACH ROW

    trigger_body

trigger_name：觸發器的名稱，不能與已經存在的觸發器重覆；

trigger_time：{ BEFORE | AFTER }，表示在事件之前或之後觸發；

trigger_event:：{ INSERT |UPDATE | DELETE }，觸發該觸發器的具體事件；

tbl_name：該觸發器作用在tbl_name上；

 1 # 插入前
 2 CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
 3 BEGIN
 4     ...
 5 END
 6 
 7 # 插入後
 8 CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
 9 BEGIN
10     ...
11 END
12 
13 # 刪除前
14 CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
15 BEGIN
16     ...
17 END
18 
19 # 刪除後
20 CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
21 BEGIN
22     ...
23 END
24 
25 # 更新前
26 CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
27 BEGIN
28     ...
29 END
30 
31 # 更新後
32 CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
33 BEGIN
34     ...
35 END

創建觸發器基本語法

如：

1 CREATE TRIGGER trigger_user_count_insert AFTER INSERT
2 ON user FOR EACH ROW
3 UPDATE user_count SET user_total=(user_total+1);
4 
5 CREATE TRIGGER trigger_user_count_delete AFTER DELETE
6 ON user FOR EACH ROW
7 UPDATE user_count SET user_total=user_total-1;

示例代碼

2、刪除觸發器

DROP TRIGGER 觸發器名稱;

如：

DROP TRIGGER trigger_user_count_delete;

示例代碼

3、使用觸發器

觸發器無法由用戶直接調用，而知由於對錶的【增/刪/改】操作被動引發的。

如：

INSERT INTO user(name,salary,dep_id) VALUES('小明',8000,4);

示例代碼

四、存儲過程

MySQL資料庫在5.0版本後開始支持存儲過程，存儲過程包含了一系列可執行的sql語句，存儲過程存放於MySQL中，通過調用它的名字可以執行其內部的一堆sql，類似於函數(方法),簡單的說存儲過程是為了完成某個資料庫中的特定功能而編寫的語句集合，該語句集包括SQL語句（對數據的增刪改查）、條件語句和迴圈語句等（流程式控制制見part 9）

存儲過程優點：
        1、存儲過程增強了SQL語言靈活性。

　　　　　　存儲過程可以使用控制語句編寫，可以完成複雜的判斷和較複雜的運算，有很強的靈活性；
        2、減少網路流量，降低了網路負載。

　　　　　　存儲過程在伺服器端創建成功後，只需要調用該存儲過程即可，而傳統的做法是每次都將大量的SQL語句通過網路發送至資料庫伺服器端然後再執行
        3、存儲過程只在創造時進行編譯，以後每次執行存儲過程都不需再重新編譯。

　　　　　　一般SQL語句每執行一次就編譯一次,所以使用存儲過程可提高資料庫執行速度。
存儲過程缺點：     

　 　1、擴展功能不方便

　　 2、不便於系統後期維護

存儲過程優缺點

1、查看存儲過程

SHOW PROCEDURE STATUS;

2、創建存儲過程

無參存儲過程　　

 1 -- 創建存儲過程
 2 
 3 delimiter //
 4 create procedure 存儲過程名稱()
 5 BEGIN
 6     SQL語句
 7 END//
 8 delimiter ;
 9 
10 
11 
12 -- 執行存儲過程
13 
14 call 存儲過程名稱()

對於存儲過程，可以接收參數，其參數有三類：

in 僅用於傳入參數用
out 僅用於返回值用
inout 既可以傳入又可以當作返回值

 1 -- 創建存儲過程
 2 delimiter \\
 3 create procedure p1(
 4     in i1 int,
 5     in i2 int,
 6     inout i3 int,
 7     out r1 int
 8 )
 9 BEGIN
10     DECLARE temp1 int;
11     DECLARE temp2 int default 0;
12     
13     set temp1 = 1;
14 
15     set r1 = i1 + i2 + temp1 + temp2;
16     
17     set i3 = i3 + 100;
18 
19 end\\
20 delimiter ;
21 
22 -- 執行存儲過程
23 set @t1 =4;
24 set @t2 = 0;
25 CALL p1 (1, 2 ,@t1, @t2);
26 SELECT @t1,@t2;

有參存儲過程

create procedure p2(in i int,out n varchar(50))
begin
 select name into n from account where id = i;
end
 
-- 調用
set @name =null;
CALL p2(1,@name);
select @name;

--into 關鍵字 可以 將前面欄位的查詢結果 執行 給 into 後面的變數.

into使用

 create procedure p3(in x int,in c char(1))
 begin
    if c ='d' then
         select * from account where money >x;
   else
         select * from account where money <x;     
　　end if;
end

if語句

 1 create procedure p4(inout n int)
 2 begin
 3       DECLARE sum int default 0; -- 設置總和變數,並且指定初始值0
 4       declare i int; -- 聲明變數
 5       set i = 0;    -- 通過set為變數設置值
 6 　　  while i<=n DO  -- 開始迴圈
 7             set sum = sum +i;
 8             set i = i+1;
 9       end while; -- 結束迴圈
10  
11 　　  select sum; -- 提供結果
12                      
13      set n = sum;--將計算結果提供給 輸出變數 n;
14 end;
15                  
16  -- 調用:
17  set @n = 100;
18  call p4(@n);
19  select @n;

使用迴圈計算1-100的和

3、刪除存儲過程

drop procedure proc_name;

4、執行存儲過程

-- 無參數
call proc_name()

-- 有參數，全in
call proc_name(1,2)

-- 有參數，有in，out，inout
set @t1=0;
set @t2=3;
call proc_name(1,2,@t1,@t2)

五、函數

1、MySQL提供的內建函數

  1 一、數學函數
  2     ROUND(x,y)
  3         返回參數x的四捨五入的有y位小數的值
  4         
  5     RAND()
  6         返回０到１內的隨機值,可以通過提供一個參數(種子)使RAND()隨機數生成器生成一個指定的值。
  7 
  8 二、聚合函數(常用於GROUP BY從句的SELECT查詢中)
  9     AVG(col)返回指定列的平均值
 10     COUNT(col)返回指定列中非NULL值的個數
 11     MIN(col)返回指定列的最小值
 12     MAX(col)返回指定列的最大值
 13     SUM(col)返回指定列的所有值之和
 14     GROUP_CONCAT(col) 返回由屬於一組的列值連接組合而成的結果    
 15     
 16 三、字元串函數
 17 
 18     CHAR_LENGTH(str)
 19         返回值為字元串str 的長度，長度的單位為字元。一個多位元組字元算作一個單字元。
 20     CONCAT(str1,str2,...)
 21         字元串拼接
 22         如有任何一個參數為NULL ，則返回值為 NULL。
 23     CONCAT_WS(separator,str1,str2,...)
 24         字元串拼接（自定義連接符）
 25         CONCAT_WS()不會忽略任何空字元串。 (然而會忽略所有的 NULL）。
 26 
 27     FORMAT(X,D)
 28         將數字X 的格式寫為'#,###,###.##',以四捨五入的方式保留小數點後 D 位， 並將結果以字元串的形式返回。若  D 為 0, 則返回結果不帶有小數點，或不含小數部分。
 29         例如：
 30             SELECT FORMAT(12332.1,4); 結果為： '12,332.1000'
 31     
 32     INSERT(str,pos,len,newstr)
 33         在str的指定位置插入字元串
 34             pos：要替換位置其實位置
 35             len：替換的長度
 36             newstr：新字元串
 37         例如:
 38             SELECT INSERT('abcd',1,2,'tt'); 結果為: 'ttcd'
 39             SELECT INSERT('abcd',1,4,'tt'); 結果為: 'tt'
 40         特別的：
 41             如果pos超過原字元串長度，則返回原字元串
 42             如果len超過原字元串長度，則由新字元串完全替換
 43     
 44     INSTR(str,substr)
 45         返回字元串 str 中子字元串的第一個出現位置。
 46 
 47     LEFT(str,len)
 48         返回字元串str 從開始的len位置的子序列字元。
 49         例如:
 50             SELECT INSTR('abc','c'); 結果為： 3
 51             SELECT INSTR('abc','d'); 結果為： 0
 52             
 53     LOWER(str)
 54         變小寫
 55 
 56     UPPER(str)
 57         變大寫
 58    
 59     REVERSE(str)
 60         返回字元串 str ，順序和字元順序相反。
 61         例如:
 62             SELECT REVERSE('1234567') 結果為：7654321
 63             
 64     SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
 65         不帶有len 參數的格式從字元串str返回一個子字元串，起始於位置 pos。帶有len參數的格式從字元串str返回一個長度同len字元相同的子字元串，起始於位置 pos。 使用 FROM的格式為標準 SQL 語法。也可能對pos使用一個負值。假若這樣，則子字元串的位置起始於字元串結尾的pos 字元，而不是字元串的開頭位置。在以下格式的函數中可以對pos 使用一個負值。
 66 
 67         mysql> SELECT SUBSTRING('Quadratically',5); -- 從第5位開始截取
 68             -> 'ratically'
 69 
 70         mysql> SELECT SUBSTRING('foobarbar' FROM 4); -- 從第4位開始截取
 71             -> 'barbar'
 72 
 73         mysql> SELECT SUBSTRING('Quadratically',5,6); --從第5位開始截取,截取6個長度
 74             -> 'ratica'
 75 
 76         mysql> SELECT SUBSTRING('Sakila', -3);    -- 從倒數第3位開始截取
 77             -> 'ila'
 78 
 79         mysql> SELECT SUBSTRING('Sakila', -5, 3); -- 從倒數第5位開始截取,截取3個長度
 80             -> 'aki'
 81             
 82 四、日期和時間函數
 83     CURDATE()或CURRENT_DATE() 返回當前的日期
 84     CURTIME()或CURRENT_TIME() 返回當前的時間
 85     DAYOFWEEK(date)   返回date所代表的一星期中的第幾天(1~7)
 86     DAYOFMONTH(date)  返回date是一個月的第幾天(1~31)
 87     DAYOFYEAR(date)   返回date是一年的第幾天(1~366)
 88     DAYNAME(date)   返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);
 89     FROM_UNIXTIME(ts,fmt)  根據指定的fmt格式，格式化UNIX時間戳ts
 90     HOUR(time)   返回time的小時值(0~23)
 91     MINUTE(time)   返回time的分鐘值(0~59)
 92     MONTH(date)   返回date的月份值(1~12)
 93     MONTHNAME(date)   返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);
 94     NOW()    返回當前的日期和時間
 95     QUARTER(date)   返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);
 96     WEEK(date)   返回日期date為一年中第幾周(0~53)
 97     YEAR(date)   返回日期date的年份(1000~9999)
 98     
 99     重點:
100     DATE_FORMAT(date,format) 根據format字元串格式化date值
101 
102        mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
103         -> 'Sunday October 2009'
104        mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
105         -> '22:23:00'
106        mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
107         ->                 '%D %y %a %d %m %b %j');
108         -> '4th 00 Thu 04 10 Oct 277'
109        mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
110         ->                 '%H %k %I %r %T %S %w');
111         -> '22 22 10 10:23:00 PM 22:23:00 00 6'
112        mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
113         -> '1998 52'
114        mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
115         -> '00'
116         
117 五、加密函數
118     MD5()    
119         計算字元串str的MD5校驗和
120         例如:
121             SELECT MD5('1234') 結果為：81dc9bdb52d04dc20036dbd8313ed055
122     PASSWORD(str)   
123         返回字元串str的加密版本，這個加密過程是不可逆轉的
124         例如:
125             SELECT PASSWORD('1234') 結果為:*A4B6157319038724E3560894F7F932C8886EBFCF
126         
127 六、控制流函數            
128     CASE WHEN[test1] THEN [result1]...ELSE [default] END
129         如果testN是真，則返回resultN，否則返回default
130     CASE [test] WHEN[val1] THEN [result]...ELSE [default]END  
131         如果test和valN相等，則返回resultN，否則返回default
132 
133     IF(test,t,f)   
134         如果test是真，返回t；否則返回f
135 
136     IFNULL(arg1,arg2) 
137         如果arg1不是空，返回arg1，否則返回arg2
138         例如:
139             SELECT IFNULL('bbb','abc'); 結果為: bbb
140             SELECT IFNULL(null,'abc');  結果為: abc
141 
142     NULLIF(arg1,arg2) 
143         如果arg1=arg2返回NULL；否則返回arg1
144         例如:
145             SELECT NULLIF('bbb','bbb');結果為： null
146             SELECT NULLIF('aaa','bbb');結果為： aaa

MySQL提供的內建函數

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('doudou') into @i;
SELECT @i;


# 在查詢中使用
select f1(11,nid) ,name from tb2;

執行函數

4、函數與存儲過程區別

六、事務

事務用於將某些操作的多個SQL作為原子性操作，一旦有某一個出現錯誤，即可回滾到原來的狀態，從而保證資料庫數據完整性。

1、事務的特性

　　原子性（Atomicity）

　　原子性是指事務包含的所有操作要麼全部成功，要麼全部失敗回滾，這和前面兩篇博客介紹事務的功能是一樣的概念，因此事務的操作如果成功就必須要完全應用到資料庫，如果操作失敗則不能對資料庫有任何影響。

　　一致性（Consistency）

　　一致性是指事務必須使資料庫從一個一致性狀態變換到另一個一致性狀態，也就是說一個事務執行之前和執行之後都必須處於一致性狀態。

　　拿轉賬來說，假設用戶A和用戶B兩者的錢加起來一共是5000，那麼不管A和B之間如何轉賬，轉幾次賬，事務結束後兩個用戶的錢相加起來應該還得是5000，這就是事務的一致性。

　　隔離性（Isolation）

　　隔離性是當多個用戶併發訪問資料庫時，比如操作同一張表時，資料庫為每一個用戶開啟的事務，不能被其他事務的操作所干擾，多個併發事務之間要相互隔離。

　　即要達到這麼一種效果：對於任意兩個併發的事務T1和T2，在事務T1看來，T2要麼在T1開始之前就已經結束，要麼在T1結束之後才開始，這樣每個事務都感覺不到有其他事務在併發地執行。

　　關於事務的隔離性資料庫提供了多種隔離級別，稍後會介紹到。

　　持久性（Durability）

　　持久性是指一個事務一旦被提交了，那麼對資料庫中的數據的改變就是永久性的，即便是在資料庫系統遇到故障的情況下也不會丟失提交事務的操作。

註意：

在 MySQL 中只有使用了 Innodb 資料庫引擎的資料庫或表才支持事務。
事務處理可以用來維護資料庫的完整性，保證成批的 SQL 語句要麼全部執行，要麼全部不執行。
事務用來管理 insert,update,delete 語句

2、事務的控制

BEGIN 或 START TRANSACTION；顯式地開啟一個事務；
COMMIT；也可以使用COMMIT WORK，不過二者是等價的。COMMIT會提交事務，並使已對資料庫進行的所有修改稱為永久性的；
ROLLBACK；有可以使用ROLLBACK WORK，不過二者是等價的。回滾會結束用戶的事務，並撤銷正在進行的所有未提交的修改；
SAVEPOINT : 保存點,可以把一個事物分割成幾部分.在執行ROLLBACK 時可以指定在什麼位置上進行回滾操作.

註意: SET AUTOCOMMIT=0 ;禁止自動提交和 SET AUTOCOMMIT=1 開啟自動提交.

3、應用舉例（雙方轉賬）

create table account(
    id int(50) not null auto_increment primary key,
    name VARCHAR(50) not null,
    money DOUBLE(5,2) not NULL
);

創建表