今日內容概要
今日內容基本都是了解知識點，你在作業中基本用不到
- 視圖
- 觸發器
- 事務（需要掌握）
- 存盤程序
- 內置函式
- 流程控制
- 索引理論
今日內容詳細
視圖
- 什么是視圖
視圖就是通過查詢得到一張虛擬表，然后保存下來，下次可以直接使用
其實視圖也就是表
- 為何要有視圖
如果頻繁的操作一張虛擬表，你就可以制作成視圖，后續直接操作
- 如何操作視圖
#固定語法 create view 視圖名 as 虛擬表的查詢sql陳述句 # 具體操作 create view teacher2course as select * from teacher innter join course on teacher.id = course.teacher_id;
- 注意
""" 1.創建視圖在硬碟上只有表結構，沒有表資料（資料還是來自于之前的表） 2.視圖一般只用來查詢，里面的資料不要進行修改，可能會影響真正的表 """ mysql> delete from teacher2course where teacher.teacher.id = 1; ERROR 1395 (HY000): Can not delete from join view 'day48.teacher2course'
- 視圖的使用頻率到底高不高呢？
""" 不高 當你創建了視圖之后，會造成表不好維護 """ #總結：視圖了解即可，基本不用！！！
觸發器
在滿足對表資料進行增、刪、改的情況下，自動觸發的功能
使用觸發器可以幫我們實作資料庫的監控、日志，自動處理例外等等
觸發器可以在6中情況下自動觸發，增前/后、刪前后、改前后
基本語法結構
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 before insert on t1 for each row begin sql 陳述句 end """針對洗掉和修改，書寫格式一致""" ps:修改MySQL默認的陳述句結束符，只作用于當前視窗 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') ); create table errlog( id int primary key auto_increment, err_cmd char(64), err_time datetime ); """ 當cmd表中的記錄success欄位時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/password',NOW(),'no'), ('jason','0755','useradd xxx',NOW(),'no'), ('jason','0755','ps aux',NOW(),'yes'); mysql> select * from cmd; +----+-------+------+-------------------+---------------------+---------+ | id | USER | priv | cmd | sub_time | success | +----+-------+------+-------------------+---------------------+---------+ | 1 | jason | 0755 | ls -l /etc | 2021-09-04 11:44:36 | yes | | 2 | jason | 0755 | cat /etc/password | 2021-09-04 11:44:36 | no | | 3 | jason | 0755 | useradd xxx | 2021-09-04 11:44:36 | no | | 4 | jason | 0755 | ps aux | 2021-09-04 11:44:36 | yes | +----+-------+------+-------------------+---------------------+---------+ 4 rows in set (0.00 sec) mysql> select * from errlog; +----+-------------------+---------------------+ | id | err_cmd | err_time | +----+-------------------+---------------------+ | 1 | cat /etc/password | 2021-09-04 11:44:36 | | 2 | useradd xxx | 2021-09-04 11:44:36 | +----+-------------------+---------------------+ 2 rows in set (0.00 sec) #洗掉觸發器 drop trigger tri_after_insert_cmd;
事務
- 什么是事務
""" 開啟一個事務可以包含多條SQL陳述句，這些SQL陳述句要么同時成功 要么同時失敗，一個也別想成功，稱之為事務的原子性 """
- 事務的作用
""" 保證了對資料操作的安全性 """ eg:還錢的例子 egon用銀行卡給我的支付寶轉賬 1.將egon銀行卡賬戶的資料減1000塊 2.將jason支付寶賬戶的資料加1000塊 你在操作多條資料的時候，可能會出現某幾條不成功的情況
- 事務的四大特性
""" ACID A (atomicity):原子性 一個事務就是一個不可分割的單位，事務包含諸多操作 要么同時成功，要么同時失敗 C(consistency): 一致性 事務必須是使資料庫從一個一致性的狀態，變到另一個一致性的狀態 一致性跟原子性是密切相關的 I(isolation)：隔離性 一個事務的執行，不能被其他事務干擾，即一個事務內部的操作，及 使用到的資料對并發的其他事務是隔離的，并發執行的事務之間也是 互相不干擾的 D(durability):持久性 也叫永久性，指一個事務一旦提交成功，執行成功，那么它對 資料庫中的資料修改應該是永久性的，接下來的其他操作或者故障 不應該對其有任何的影響 """
- 如何使用事務
#事務相關的關鍵字 #1.開啟事務 start transaction #2.回滾操作（回到事務執行之前的狀態） rollback #3.確認（確認之后，就無法回滾了） commit """模擬轉賬功能""" create table user( id int primary key auto_increment, name varchar(16), banlance int ); insert into user(name,banlance) values ('jason',1000), ('egon',1000), ('tank',1000); #1.先開啟事務 start transaction; #2.書寫多條sql陳述句 update user set banlance = 900 where name = 'jason'; update user set banlance = 1010 where name = 'egon'; update user set banlance = 1090 where name = 'tank'; """ mysql> # 1.先開啟事務 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> # 2.書寫多條sql陳述句 mysql> update user set banlance = 900 where name = 'jason'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update user set banlance = 1010 where name = 'egon'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update user set banlance = 1090 where name = 'tank'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> mysql> mysql> select * from user; +----+-------+----------+ | id | name | banlance | +----+-------+----------+ | 1 | jason | 900 | | 2 | egon | 1010 | | 3 | tank | 1090 | +----+-------+----------+ 3 rows in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.01 sec) mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from user; +----+-------+----------+ | id | name | banlance | +----+-------+----------+ | 1 | jason | 900 | | 2 | egon | 1010 | | 3 | tank | 1090 | +----+-------+----------+ 3 rows in set (0.00 sec) """ 總結： 當你想讓多條sql陳述句保持一致性，要么同時成功，要么同時失敗 你就應該考慮使用事務
存盤程序
存盤程序就類似于Python中的自定義函式
它的內部包含了一系列可以執行的sql陳述句，存盤程序存放于mysql服務端中，
你可以直接通過呼叫存盤程序觸發內部sql陳述句的執行
基本使用
create procedure 存盤程序的名字 (形參1,形參2,...)
begin
sql 陳述句
end
三種開發模型
第一種
""" 應用程式：程式猿寫代碼開發 MySQL：提前撰寫好存盤程序，供程式猿呼叫 好處：開發效率提升了 執行效率也上去了 壞處：考慮到人為因素，跨部門溝通問題，后續的存盤程序的擴展性變差 """
第二種
""" 應用程式：程式猿寫代碼開發,涉及到資料庫操作也自己動手寫 好處：擴展性很高 壞處： 開發效率降低 撰寫sql陳述句太過繁瑣，而且后續還需要考慮sql優化的問題 """
第三種
""" 應用程式：開發只寫程式代碼，不寫sql陳述句，基于別人寫好的操作MySQL的python框架直接呼叫操作即可 ORM框架 通過python代碼操作資料庫 好處：開發效率比前兩種情況都要高 壞處：陳述句的擴展性差，可能會出現效率低下的問題 """
第一種基本不用，一般都是用第三種，出現效率問題再動手寫sql
存盤程序具體演示
delimiter $$ create procedure p1( in m int #只進不出，m不能回傳 in n int out res int #out表示該形參可以回傳出去 ) begin select tname from teacher where tid > m and tid < n; set res = 666 #將res變數修改，用來標識當前的存盤程序代碼確實執行了 end $$ delimiter ; #呼叫存盤程序 call p1(1,5,10) mysql> call p1(1,5,10) -> ; ERROR 1414 (42000): OUT or INOUT argument 3 for routine day48.p1 is not a variab le or NEW pseudo-variable in BEFORE trigger #針對形參res,不能直接傳資料，應該要傳一個變數名 #定義變數 set @res = 10; #查看變數對應的值 select @ret; mysql> select @res; +------+ | @res | +------+ | 10 | +------+ 1 row in set (0.00 sec) mysql> call p1(1,5,@ret)
在pymysql模塊中如何呼叫存盤程序呢？
import pymysql conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', password='123', database='day48', charset='utf8', autocommit=True ) cursor = conn.cursor(cursor = pymysql.cursors.DictCursor) #呼叫存盤程序 cursor.callpro('p1',(1,5,10)) """ @_p1_0 = 1 @_p1_1 = 5 @_p1_2 = 10 """ 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 data_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; delimiter ;
索引
ps:資料都是存在于硬碟上的，查詢資料不可避免的需要進行IO操作 索引：就是一種資料結構，類似于書的目錄，意味著以后在查詢資料的時候，應該先找目錄再找資料，而不是一頁一頁的翻書，
從而提升查詢速度，降低IO操作 索引在MySQL中也叫”鍵“，是存盤引擎用于快速查找記錄的一種資料結構 * primary key * unique key * index key 注意 foreign key不是用來加速查詢用的，不在我們的研究范圍之內 上面的三種key，前面的兩種除了可以增加查詢速度之外，各自還具有約束條件，而最后一種index key沒有任何的約束條件，
只是用來幫助你快速查詢資料
本質
通過不斷地縮寫想要的資料范圍，篩選出最終的結果，同時將隨機事件（一頁頁的翻） 變成順序事件（先找目錄，找資料） 也就是說有了索引機制，我們可以總是用一種固定的方式查找資料 一張表中，可以有多個索引（多個目錄） 索引雖然能夠幫助你加快查詢速度，但是也是有缺點的 """ 1.當表中有大量的資料存在的前提下，創建索引速度會非常慢 2 在索引創建完畢之后，對表的查詢性能會大幅度的提升，但是寫的性能也會大幅度的降低 """ 索引不要隨意的創建！！！！
b+樹
""" 只有葉子節點存放的是真實的資料，其他結點存放的是虛擬資料，僅僅使用來指路的 樹的層級越高，查詢資料所經歷的步驟就越多，樹有幾層，查詢資料就有幾步 一個磁盤塊，存盤是有限制的 為什么建議你將id欄位作為索引 id占的空間少，一個磁盤塊能夠存數的資料多 那么就降低了樹的高度，從而減少查詢次數 """
聚集索引（primary key）
""" 聚集索引指的就是主鍵 Innodb 只有兩個檔案，直接將主鍵存放在了ibd表中 MyIsam 三個檔案，單獨將索引放在一個檔案中 """
輔助索引（unique，index）
查詢資料的時候,不可能一直使用到主鍵，也有可能會使用到其他欄位
那么這個時候，你是沒有辦法利用聚集索引，這個時候，你就可以根據情況給其他欄位設定輔助索引
輔助索引也是一個b+樹
where name = 'jason'
""" 葉子節點存放的是資料對應的主鍵值 先按照輔助索引拿到資料的主鍵值 之后還是需要去主鍵的聚集索引里面查詢資料 """
覆寫索引
在輔助索引的葉子節點，就已經拿到了想要的資料
# 給name設定輔助索引 select name from user where name = 'jason'; # 非覆寫索引 select age from user wherr name = 'jason';
測驗索引是否有效的代碼
感興趣的可以自己試試，不感興趣直接忽略
#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(); # 表沒有任何索引的情況下 select * from s1 where id=30000; # 1.4s # 避免列印帶來的時間損耗 select count(id) from s1 where id = 30000; # 1.4s select count(id) from s1 where id = 1; # 1.4s # 給id做一個主鍵 alter table s1 add primary key(id); # 速度很慢 7.1s select count(id) from s1 where id = 1; # 速度相較于未建索引之前兩者差著數量級 0s select count(id) from s1 where name = 'jason' # 速度仍然很慢 0.7s """ 范圍問題 """ # 并不是加了索引，以后查詢的時候按照這個欄位速度就一定快 select count(id) from s1 where id > 1; # 速度相較于id = 1慢了很多0.653s select count(id) from s1 where id >1 and id < 3; # 0.001s select count(id) from s1 where id > 1 and id < 10000; # 0.007s select count(id) from s1 where id != 3; # 0.657s alter table s1 drop primary key; # 洗掉主鍵 單獨再來研究name欄位7.884s select count(id) from s1 where name = 'jason'; # 又慢了1.167s create index idx_name on s1(name); # 給s1表的name欄位創建索引6.284s select count(id) from s1 where name = 'jason' # 仍然很慢！！！4s """ 再來看b+樹的原理，資料需要區分度比較高，而我們這張表全是jason，根本無法區分 那這個樹其實就建成了“一根棍子” """ select count(id) from s1 where name = 'xxx'; # 0s # 這個會很快，我就是一根棍，第一個不匹配直接不需要再往下走了 select count(id) from s1 where name like 'xxx'; # 0s select count(id) from s1 where name like 'xxx%'; # 0s select count(id) from s1 where name like '%xxx'; # 慢 最左匹配特性 1.352s # 區分度低的欄位不能建索引 drop index idx_name on s1; # 給id欄位建普通的索引 create index idx_id on s1(id); # 4.404s select count(id) from s1 where id = 3; # 快了 0s select count(id) from s1 where id*12 = 3; # 慢了 索引的欄位一定不要參與計算0.671s drop index idx_id on s1; select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx'; # 1.41s # 針對上面這種連續多個and的操作，mysql會從左到右先找區分度比較高的索引欄位，先將整體范圍降下來再去比較其他條件 create index idx_name on s1(name); # 6.559s select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx'; # 并沒有加速 4.895s drop index idx_name on s1; # 給name，gender這種區分度不高的欄位加上索引并不能加快查詢速度 create index idx_id on s1(id); # 4.625s select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';
# 快了 先通過id已經講資料快速鎖定成了一條了0.001s
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';
# 慢了 基于id查出來的資料仍然很多，然后還要去比較其他欄位1.949s drop index idx_id on s1; create index idx_email on s1(email); # 8.029s select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';
# 快 通過email欄位一劍封喉 0.004s # 聯合索引 select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 0s # 如果上述四個欄位區分度都很高，那給誰建都能加速查詢 # 給email加然而不用email欄位 select count(id) from s1 where name='jason' and gender = 'male' and id > 3; # 1.65s # 給name加然而不用name欄位 select count(id) from s1 where gender = 'male' and id > 3; # 1.416s # 給gender加然而不用gender欄位 select count(id) from s1 where id > 3; # 0.976s # 帶來的問題是所有的欄位都建了索引然而都沒有用到，還需要花費四次建立的時間 create index idx_all on s1(email,name,gender,id); # 最左匹配原則，區分度高的往左放9.734s select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 速度變快0.005s 總結: 搜索范圍大，耗時長 識別度低的索引：耗時比無該索引更長 慢查詢日志 設定一個時間檢測所有超出該時間的sql陳述句，然后針對性的進行優化！
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/297719.html
標籤：Python
上一篇：四十八、Navicat使用、pymysql模塊使用、SQL注入問題
下一篇：四十九、MySQL資料庫尾聲