【目錄】（其餘均為瞭解知識） 一 視圖 二 觸發器 三 事務（掌握） 四 存儲過程 五 函數 六 流程式控制制 七、索引理論 一、視圖 1、什麼是視圖 視圖是一個虛擬表（非真實存在），其本質是【根據SQL語句獲取動態的數據集，併為其命名】，用戶使用時只需使用【名稱】即可獲取結果集，可以將該結果集當做表來 ...
【目錄】（其餘均為瞭解知識）
一 視圖
二 觸發器
三 事務（掌握）
四 存儲過程
五 函數
六 流程式控制制
七、索引理論
一、視圖
1、什麼是視圖
視圖是一個虛擬表（非真實存在），其本質是【根據SQL語句獲取動態的數據集，併為其命名】，用戶使用時只需使用【名稱】即可獲取結果集，可以將該結果集當做表來使用。
即 視圖就是通過查詢得到一張虛擬表，然後保存下來，下次可以直接使用，其實視圖也是表
2、為何要用視圖
使用視圖我們可以把查詢過程中的臨時表摘出來，用視圖去實現，這樣以後再想操作該臨時表的數據時就無需重寫複雜的sql了，直接去視圖中查找即可。
但視圖有明顯地效率問題，並且視圖是存放在資料庫中的，如果我們程式中使用的 sql 過分依賴資料庫中的視圖，即強耦合，那就意味著擴展sql極為不便，因此並不推薦使用
3、如何使用視圖
（1）臨時表應用舉例：
#兩張有關係的表 mysql> select * from course; +-----+--------+------------+ | cid | cname | teacher_id | +-----+--------+------------+ | 1 | 生物 | 1 | | 2 | 物理 | 2 | | 3 | 體育 | 3 | | 4 | 美術 | 2 | +-----+--------+------------+ 4 rows in set (0.00 sec) mysql> select * from teacher; +-----+-----------------+ | tid | tname | +-----+-----------------+ | 1 | 張磊老師 | | 2 | 李平老師 | | 3 | 劉海燕老師 | | 4 | 朱雲海老師 | | 5 | 李傑老師 | +-----+-----------------+ 5 rows in set (0.00 sec) #查詢李平老師教授的課程名 mysql> select cname from course where teacher_id = (select tid from teacher where tname='李平老師'); +--------+ | cname | +--------+ | 物理 | | 美術 | +--------+ 2 rows in set (0.00 sec) #子查詢出臨時表，作為teacher_id等判斷依據 select tid from teacher where tname='李平老師'
（2）創建視圖—— CREATE VIEW 視圖名稱 AS SQL語句
#語法：CREATE VIEW 視圖名稱 AS SQL語句 create view teacher_view as select tid from teacher where tname='李平老師'; #於是查詢李平老師教授的課程名的sql可以改寫為 mysql> select cname from course where teacher_id = (select tid from teacher_view); +--------+ | cname | +--------+ | 物理 | | 美術 | +--------+ 2 rows in set (0.00 sec) #！！！註意註意註意： #1. 使用視圖以後就無需每次都重寫子查詢的sql，但是這麼效率並不高，還不如我們寫子查詢的效率高 #2. 而且有一個致命的問題： 視圖是存放到資料庫里的，如果我們程式中的sql過分依賴於資料庫中存放的視圖，那麼意味著，一旦sql需要修改且涉及到視圖的部分，則必須去資料庫中進行修改， 而通常在公司中資料庫有專門的DBA負責，你要想完成修改，必須付出大量的溝通成本DBA可能才會幫你完成修改，極其地不方便
（3）使用視圖
#修改視圖，原始表也跟著改 mysql> select * from course; +-----+--------+------------+ | cid | cname | teacher_id | +-----+--------+------------+ | 1 | 生物 | 1 | | 2 | 物理 | 2 | | 3 | 體育 | 3 | | 4 | 美術 | 2 | +-----+--------+------------+ 4 rows in set (0.00 sec) mysql> create view course_view as select * from course; #創建表course的視圖 Query OK, 0 rows affected (0.52 sec) mysql> select * from course_view; +-----+--------+------------+ | cid | cname | teacher_id | +-----+--------+------------+ | 1 | 生物 | 1 | | 2 | 物理 | 2 | | 3 | 體育 | 3 | | 4 | 美術 | 2 | +-----+--------+------------+ 4 rows in set (0.00 sec) mysql> update course_view set cname='xxx'; #更新視圖中的數據 Query OK, 4 rows affected (0.04 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql> insert into course_view values(5,'yyy',2); #往視圖中插入數據 Query OK, 1 row affected (0.03 sec) mysql> select * from course; #發現原始表的記錄也跟著修改了 +-----+-------+------------+ | cid | cname | teacher_id | +-----+-------+------------+ | 1 | xxx | 1 | | 2 | xxx | 2 | | 3 | xxx | 3 | | 4 | xxx | 2 | | 5 | yyy | 2 | +-----+-------+------------+ 5 rows in set (0.00 sec)
註意：我們不應該修改視圖中的記錄，而且在涉及多個表的情況下是根本無法修改視圖中的記錄的。
（4）修改視圖
修改視圖 語法：ALTER VIEW 視圖名稱 AS SQL語句 mysql> alter view teacher_view as select * from course where cid>3; Query OK, 0 rows affected (0.04 sec) mysql> select * from teacher_view; +-----+-------+------------+ | cid | cname | teacher_id | +-----+-------+------------+ | 4 | xxx | 2 | | 5 | yyy | 2 | +-----+-------+------------+ 2 rows in set (0.00 sec)
（5）刪除視圖
語法：DROP VIEW 視圖名稱
DROP VIEW teacher_view
二、觸發器
1、什麼是觸發器
使用觸發器可以定製用戶對錶進行【增、刪、改】操作時前後的行為，註意：沒有查詢
使用觸發器可以幫助我們實現監控、日誌...
2、基本語法結構
create trigger 觸發器的名字 before/after insert/update/delete on 表名 for each row begin sql語句 end # 具體使用 針對觸發器的名字 我們通常需要做到 見名知意
# 針對增 create trigger tri_before_insert_t1 before insert on t1 for each row begin sql語句 end create trigger tri_after_insert_t1 after insert on t1 for each row begin sql語句 end """針對刪除和修改 書寫格式一致""" ps:修改MySQL預設的語句結束符 只作用於當前視窗 delimiter $$ 將預設的結束符號由;改為$$ delimiter ;
# 案例 CREATE TABLE cmd ( id INT PRIMARY KEY auto_increment, USER CHAR (32), priv CHAR (10), cmd CHAR (64), sub_time datetime, #提交時間 success enum ('yes', 'no') #0代表執行失敗 ); CREATE TABLE errlog ( id INT PRIMARY KEY auto_increment, err_cmd CHAR (64), err_time datetime ); """ 當cmd表中的記錄succes欄位是no那麼就觸發觸發器的執行去errlog表中插入數據 NEW指代的就是一條條數據對象 """ delimiter $$ create trigger tri_after_insert_cmd after insert on cmd for each row begin if NEW.success = 'no' then insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time); end if; end $$ delimiter ; # 朝cmd表插入數據 INSERT INTO cmd ( USER, priv, cmd, sub_time, success ) VALUES ('jason','0755','ls -l /etc',NOW(),'yes'), ('jason','0755','cat /etc/passwd',NOW(),'no'), ('jason','0755','useradd xxx',NOW(),'no'), ('jason','0755','ps aux',NOW(),'yes'); # 刪除觸發器 drop trigger tri_after_insert_cmd;# 案例
# 插入前 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六種觸發情況的基本語法
使用觸發器：
觸發器無法由用戶直接調用，而知由於對錶的【增/刪/改】操作被動引發的。
刪除觸發器：
drop trigger tri_after_insert_cmd;
三、事務
1、什麼是事務
事務用於將某些操作的多個SQL作為原子性操作
（開啟一個事務可以包含多條sql語句，這些sql語句要麼同時成功，要麼一個都別想成功 ，稱之為事務的原子性），
一旦有某一個出現錯誤，即可回滾到原來的狀態，從而保證資料庫數據完整性。
2、事務的作用
【保證對數據操作的安全性】
例子：還錢
付錢方和收款方，存在多條操作；
在操作多條數據的時候，可能會出現某些操作不成功的情況
【事務的四大特性——ACID】
A：原子性 atomicity
一個事務是不可分割的單位，事務中包含的諸多操作，要麼同時成功，要麼同時失敗
C：一致性 consistency
事務必須是使資料庫從一個一致性的狀態 變為另外一個 一致性的狀態
一致性跟原子性是密切相關的
I：隔離性 isolation
一個事務的執行不能被其他事務干擾
（即 一個事務內部的操作以及使用的數據，對併發的其他事務是隔離的，併發執行的事務之間也是互不幹擾的）
D：持久性 durability
也叫"永久性"。一個事務一旦提交成功執行成功，那麼它對資料庫中數據的修改應該是永久的，
接下來的其他操作或者故障不應該對其有任何的影響
3、如何使用事務
# 事務相關的關鍵字
# 1 開啟事務
start transaction;# 2 回滾(回到事務執行之前的狀態)
rollback;# 3 確認(確認之後就無法回滾了)
commit;
"""模擬轉賬功能""" create table user( id int primary key auto_increment, name char(16), balance int ); insert into user(name,balance) values ('jason',1000), ('egon',1000), ('tank',1000); # 1 先開啟事務 start transaction; # 2 多條sql語句 update user set balance=900 where name='jason'; update user set balance=1010 where name='egon'; update user set balance=1090 where name='tank';
總結
當你想讓多條sql語句保持一致性 要麼同時成功要麼同時失敗
你就應該考慮使用事務
四、存儲過程
1、什麼是存儲過程
2、基本使用
create procedure 存儲過程的名字(形參1,形參2,...) begin sql代碼 end # 調用 call 存儲過程的名字();
3、三種開發模型
第一種基本不用。一般都是第三種，出現效率問題再動手寫sql
【第一種】
應用程式:程式員寫代碼開發
MySQL:提前編寫好存儲過程，供應用程式調用
好處:開發效率提升了 執行效率也上去了
缺點:考慮到認為元素、跨部門溝通的問題 後續的存儲過程的擴展性差
【第二種】
應用程式:程式員寫代碼開發之外 設計到資料庫操作也自己動手寫
優點:擴展性很高
缺點:
開發效率降低
編寫sql語句太過繁瑣 而且後續還需要考慮sql優化的問題
【第三種】
應用程式:只寫程式代碼 不寫sql語句 基於別人寫好的操作MySQL的python框架直接調用操作即可——如：ORM框架
優點:開發效率比上面兩種情況都要高
缺點:語句的擴展性差 可能會出現效率低下的問題
4、存儲過程的具體演示
delimiter $$ create procedure p1( in m int, # 只進不出 m不能返回出去 in n int, out res int # 該形參可以返回出去 ) begin select tname from teacher where tid>m and tid<n; set res=666; # 將res變數修改 用來標識當前的存儲過程代碼確實執行了 end $$ delimiter ; # 針對形參res 不能直接傳數據 應該傳一個變數名 # 定義變數 set @ret = 10; # 查看變數對應的值 select @ret;
5、在pymysql 模塊中，如何調用存儲過程
import pymysql conn = pymysql.connect( host = '127.0.0.1', port = 3306, user = 'root', passwd = '123456', db = 'day48', charset = 'utf8', autocommit = True ) cursor = conn.cursor(pymysql.cursors.DictCursor) # 調用存儲過程 cursor.callproc('p1',(1,5,10)) """ @_p1_0=1 @_p1_1=5 @_p1_2=10 """ # print(cursor.fetchall()) cursor.execute('select @_p1_2;') print(cursor.fetchall())
五、函數
跟存儲過程是有區別的，存儲過程是自定義函數，函數就類似於是內置函數
('jason','0755','ls -l /etc',NOW(),'yes') CREATE TABLE blog ( id INT PRIMARY KEY auto_increment, NAME CHAR (32), sub_time datetime ); INSERT INTO blog (NAME, sub_time) VALUES ('第1篇','2015-03-01 11:31:21'), ('第2篇','2015-03-11 16:31:21'), ('第3篇','2016-07-01 10:21:31'), ('第4篇','2016-07-22 09:23:21'), ('第5篇','2016-07-23 10:11:11'), ('第6篇','2016-07-25 11:21:31'), ('第7篇','2017-03-01 15:33:21'), ('第8篇','2017-03-01 17:32:21'), ('第9篇','2017-03-01 18:31:21'); select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
六、流程式控制制
# 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 ; # 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 ;
七、索引理論
1、索引
索引:就是一種數據結構，類似於書的目錄。意味著以後在查詢數據的應該先找目錄再找數據，而不是一頁一頁的翻書，從而提升查詢速度降低IO操作
索引在MySQL中也叫“鍵”,是存儲引擎用於快速查找記錄的一種數據結構
primary key
unique key
index key
註意foreign key不是用來加速查詢用的，不在我們的而研究範圍之內
通過不斷的縮小想要的數據範圍篩選出最終的結果，同時將隨機事件(一頁一頁的翻)
變成順序事件(先找目錄、找數據)
1 當表中有大量數據存在的前提下 創建索引速度會很慢
2 在索引創建完畢之後 對錶的查詢性能會大幅度的提升 但是寫的性能也會大幅度的降低
"""
索引不要隨意的創建！！！
2、
只有葉子節點存放的是真實的數據 其他節點存放的是虛擬數據 僅僅是用來指路的
樹的層級越高查詢數據所需要經歷的步驟就越多(樹有幾層查詢數據就需要幾步)一個磁碟塊存儲是有限制的
為什麼建議你將id欄位作為索引
占得空間少 一個磁碟塊能夠存儲的數據多
那麼久降低了樹的高度 從而減少查詢次數
3、
聚集索引指的就是主鍵
Innodb 只有兩個文件 直接將主鍵存放在了idb表中
MyIsam 三個文件 單獨將索引存在一個文件
4、
葉子節點存放的是數據對應的主鍵值
先按照輔助索引拿到數據的主鍵值
之後還是需要去主鍵的聚集索引裡面查詢數據
5、
# 給name設置輔助索引 select name from user where name='jason'; # 非覆蓋索引 select age from user where name='jason';
6、
**準備** ```mysql #1. 準備表 create table s1( id int, name varchar(20), gender char(6), email varchar(50) ); #2. 創建存儲過程，實現批量插入記錄 delimiter $$ #聲明存儲過程的結束符號為$$ create procedure auto_insert1() BEGIN declare i int default 1; while(i<3000000)do insert into s1 values(i,'jason','male',concat('jason',i,'@oldboy')); set i=i+1; end while; END$$ #$$結束 delimiter ; #重新聲明分號為結束符號 #3. 查看存儲過程 show create procedure auto_insert1\G #4. 調用存儲過程 call auto_insert1(); ``` ``` mysql # 表沒有任何索引的情況下 select * from s1 where id=30000; # 避免列印帶來的時間損耗 select count(id) from s1 where id = 30000; select count(id) from s1 where id = 1; # 給id做一個主鍵 alter table s1 add primary key(id); # 速度很慢 select count(id) from s1 where id = 1; # 速度相較於未建索引之前兩者差著數量級 select count(id) from s1 where name = 'jason' # 速度仍然很慢 """ 範圍問題 """ # 並不是加了索引，以後查詢的時候按照這個欄位速度就一定快 select count(id) from s1 where id > 1; # 速度相較於id = 1慢了很多 select count(id) from s1 where id >1 and id < 3; select count(id) from s1 where id > 1 and id < 10000; select count(id) from s1 where id != 3; alter table s1 drop primary key; # 刪除主鍵 單獨再來研究name欄位 select count(id) from s1 where name = 'jason'; # 又慢了 create index idx_name on s1(name); # 給s1表的name欄位創建索引 select count(id) from s1 where name = 'jason' # 仍然很慢！！！ """ 再來看b+樹的原理，數據需要區分度比較高，而我們這張表全是jason，根本無法區分 那這個樹其實就建成了“一根棍子” """ select count(id) from s1 where name = 'xxx'; # 這個會很快，我就是一根棍，第一個不匹配直接不需要再往下走了 select count(id) from s1 where name like 'xxx'; select count(id) from s1 where name like 'xxx%'; select count(id) from s1 where name like '%xxx'; # 慢 最左匹配特性 # 區分度低的欄位不能建索引 drop index idx_name on s1; # 給id欄位建普通的索引 create index idx_id on s1(id); select count(id) from s1 where id = 3; # 快了 select count(id) from s1 where id*12 = 3; # 慢了 索引的欄位一定不要參與計算 drop index idx_id on s1; select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx'; # 針對上面這種連續多個and的操作，mysql會從左到右先找區分度比較高的索引欄位，先將整體範圍降下來再去比較其他條件 create index idx_name on s1(name); select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx'; # 並沒有加速 drop index idx_name on s1; # 給name，gender這種區分度不高的欄位加上索引並不難加快查詢速度 create index idx_id on s1(id); select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx'; # 快了 先通過id已經講數據快速鎖定成了一條了 select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 慢了 基於id查出來的數據仍然很多，然後還要去比較其他欄位 drop index idx_id on s1 create index idx_email on s1(email); select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 快 通過email欄位一劍封喉 ``` #### 聯合索引 ```mysql select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 如果上述四個欄位區分度都很高，那給誰建都能加速查詢 # 給email加然而不用email欄位 select count(id) from s1 where name='jason' and gender = 'male' and id > 3; # 給name加然而不用name欄位 select count(id) from s1 where gender = 'male' and id > 3; # 給gender加然而不用gender欄位 select count(id) from s1 where id > 3; # 帶來的問題是所有的欄位都建了索引然而都沒有用到，還需要花費四次建立的時間 create index idx_all on s1(email,name,gender,id); # 最左匹配原則，區分度高的往左放 select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 速度變快 ``` 總結:上面這些操作，你感興趣可以敲一敲，不感興趣你就可以不用敲了，權當看個樂呵。理論掌握了就行了 慢查詢日誌 設定一個時間檢測所有超出該時間的sql語句，然後針對性的進行優化感興趣就康康
