一杨名、視圖
1. 問題
對于復雜的查詢哼转，往往是有多個數(shù)據(jù)表進行關聯(lián)查詢而得到磷蛹，如果數(shù)據(jù)庫因為需求等原因發(fā)生了改變疚脐，為了保證查詢出來的數(shù)據(jù)與之前相同生宛，則需要在多個地方進行修改县昂，維護起來非常麻煩
解決辦法：定義視圖
2. 視圖是什么
通俗的講，視圖就是一條SELECT語句執(zhí)行后返回的結果集陷舅。所以我們在創(chuàng)建視圖的時候倒彰，主要的工作就落在創(chuàng)建這條SQL查詢語句上。
視圖是對若干張基本表的引用莱睁，一張?zhí)摫砝瓴担樵冋Z句執(zhí)行的結果，不存儲具體的數(shù)據(jù)（基本表數(shù)據(jù)發(fā)生了改變缩赛，視圖也會跟著改變）耙箍；
方便操作，特別是查詢操作酥馍，減少復雜的SQL語句辩昆，增強可讀性；
3. 定義視圖
建議以v_開頭
createview視圖名稱asselect語句;
4. 查看視圖
查看表會將所有的視圖也列出來
showtables;
5. 使用視圖
視圖的用途就是查詢
select*fromv_stu_score;
6. 刪除視圖
dropview視圖名稱;例：
dropviewv_stu_sco;
7. 視圖demo
8. 視圖的作用
提高了重用性旨袒，就像一個函數(shù)
對數(shù)據(jù)庫重構汁针，卻不影響程序的運行
提高了安全性能，可以對不同的用戶
讓數(shù)據(jù)更加清晰
二砚尽、事務
1. 為什么要有事務
事務廣泛的運用于訂單系統(tǒng)施无、銀行系統(tǒng)等多種場景
例如：
A用戶和B用戶是銀行的儲戶，現(xiàn)在A要給B轉賬500元必孤，那么需要做以下幾件事：
檢查A的賬戶余額>500元猾骡；
A 賬戶中扣除500元;
B 賬戶中增加500元;
正常的流程走下來，A賬戶扣了500敷搪，B賬戶加了500兴想，皆大歡喜。
那如果A賬戶扣了錢之后赡勘，系統(tǒng)出故障了呢嫂便？A白白損失了500，而B也沒有收到本該屬于他的500闸与。
以上的案例中毙替，隱藏著一個前提條件：A扣錢和B加錢，要么同時成功践樱，要么同時失敗厂画。事務的需求就在于此
所謂事務,它是一個操作序列，這些操作要么都執(zhí)行映胁，要么都不執(zhí)行木羹，它是一個不可分割的工作單位。
例如，銀行轉帳工作：從一個帳號扣款并使另一個帳號增款坑填，這兩個操作要么都執(zhí)行抛人，要么都不執(zhí)行。所以脐瑰，應該把他們看成一個事務妖枚。事務是數(shù)據(jù)庫維護數(shù)據(jù)一致性的單位，在每個事務結束時苍在，都能保持數(shù)據(jù)一致性
2.事務四大特性(簡稱ACID)
原子性(Atomicity)
一致性(Consistency)
隔離性(Isolation)
持久性(Durability)
以下內(nèi)容出自《高性能MySQL》第三版绝页，了解事務的ACID及四種隔離級有助于我們更好的理解事務運作。
下面舉一個銀行應用是解釋事務必要性的一個經(jīng)典例子寂恬。假如一個銀行的數(shù)據(jù)庫有兩張表：支票表（checking）和儲蓄表（savings）⌒現(xiàn)在要從用戶Jane的支票賬戶轉移200美元到她的儲蓄賬戶，那么至少需要三個步驟：
檢查支票賬戶的余額高于或者等于200美元初肉。
從支票賬戶余額中減去200美元酷鸦。
在儲蓄帳戶余額中增加200美元。
上述三個步驟的操作必須打包在一個事務中牙咏，任何一個步驟失敗臼隔，則必須回滾所有的步驟。
可以用START TRANSACTION語句開始一個事務妄壶，然后要么使用COMMIT提交將修改的數(shù)據(jù)持久保存摔握，要么使用ROLLBACK撤銷所有的修改。事務SQL的樣本如下：
start transaction;
select balance from checking where customer_id = 10233276;
update checking set balance = balance - 200.00 where customer_id = 10233276;
update savings set balance = balance + 200.00 where customer_id = 10233276;
commit;
一個很好的事務處理系統(tǒng)丁寄，必須具備這些標準特性：
原子性（atomicity）
一個事務必須被視為一個不可分割的最小工作單元氨淌，整個事務中的所有操作要么全部提交成功，要么全部失敗回滾狡逢，對于一個事務來說宁舰，不可能只執(zhí)行其中的一部分操作，這就是事務的原子性
一致性（consistency）
數(shù)據(jù)庫總是從一個一致性的狀態(tài)轉換到另一個一致性的狀態(tài)奢浑。（在前面的例子中，一致性確保了腋腮，即使在執(zhí)行第三雀彼、四條語句之間時系統(tǒng)崩潰，支票賬戶中也不會損失200美元即寡，因為事務最終沒有提交徊哑，所以事務中所做的修改也不會保存到數(shù)據(jù)庫中。）
隔離性（isolation）
通常來說聪富，一個事務所做的修改在最終提交以前莺丑，對其他事務是不可見的。（在前面的例子中，當執(zhí)行完第三條語句梢莽、第四條語句還未開始時萧豆，此時有另外的一個賬戶匯總程序開始運行，則其看到支票帳戶的余額并沒有被減去200美元昏名。）
持久性（durability）
一旦事務提交涮雷，則其所做的修改會永久保存到數(shù)據(jù)庫。（此時即使系統(tǒng)崩潰轻局，修改的數(shù)據(jù)也不會丟失洪鸭。）
3.事務命令
表的引擎類型必須是innodb類型才可以使用事務，這是mysql表的默認引擎
查看表的創(chuàng)建語句仑扑，可以看到engine=innodb
-- 選擇數(shù)據(jù)庫usejd;-- 查看goods表showcreatetablegoods;
開啟事務览爵，命令如下：
開啟事務后執(zhí)行修改命令，變更會維護到本地緩存中镇饮，而不維護到物理表中
begin;或者starttransaction;
提交事務拾枣，命令如下
將緩存中的數(shù)據(jù)變更維護到物理表中
commit;
回滾事務，命令如下：
放棄緩存中變更的數(shù)據(jù)
rollback;
注意
修改數(shù)據(jù)的命令會自動的觸發(fā)事務盒让，包括insert梅肤、update、delete
而在SQL語句中有手動開啟事務的原因是：可以進行多次數(shù)據(jù)的修改邑茄，如果成功一起成功姨蝴，否則一起會滾到之前的數(shù)據(jù)
三、索引
1. 思考
在圖書館中是如何找到一本書的肺缕？
一般的應用系統(tǒng)對比數(shù)據(jù)庫的讀寫比例在10:1左右(即有10次查詢操作時有1次寫的操作)左医，
而且插入操作和更新操作很少出現(xiàn)性能問題，
遇到最多同木、最容易出問題還是一些復雜的查詢操作浮梢，所以查詢語句的優(yōu)化顯然是重中之重
2. 解決辦法
當數(shù)據(jù)庫中數(shù)據(jù)量很大時，查找數(shù)據(jù)會變得很慢
優(yōu)化方案：索引
3. 索引是什么
索引是一種特殊的文件(InnoDB數(shù)據(jù)表上的索引是表空間的一個組成部分)彤路，它們包含著對數(shù)據(jù)表里所有記錄的引用指針秕硝。
更通俗的說，數(shù)據(jù)庫索引好比是字典前面的目錄洲尊，能加快數(shù)據(jù)庫的查詢速度
4. 索引目的
索引的目的在于提高查詢效率远豺，可以類比字典，如果要查“mysql”這個單詞坞嘀，我們肯定需要定位到m字母躯护，然后從下往下找到y(tǒng)字母，再找到剩下的sql丽涩。如果沒有索引棺滞，那么你可能需要把所有單詞看一遍才能找到你想要的，如果我想找到m開頭的單詞呢？或者ze開頭的單詞呢继准？是不是覺得如果沒有索引枉证，這個事情根本無法完成？
5. 索引原理
除了詞典锰瘸，生活中隨處可見索引的例子刽严，如火車站的車次表、圖書的目錄等避凝。它們的原理都是一樣的舞萄，通過不斷的縮小想要獲得數(shù)據(jù)的范圍來篩選出最終想要的結果，同時把隨機的事件變成順序的事件管削，也就是我們總是通過同一種查找方式來鎖定數(shù)據(jù)倒脓。
數(shù)據(jù)庫也是一樣，但顯然要復雜許多含思，因為不僅面臨著等值查詢崎弃，還有范圍查詢(>、<含潘、between饲做、in)、模糊查詢(like)遏弱、并集查詢(or)等等盆均。數(shù)據(jù)庫應該選擇怎么樣的方式來應對所有的問題呢？我們回想字典的例子漱逸，能不能把數(shù)據(jù)分成段泪姨，然后分段查詢呢？最簡單的如果1000條數(shù)據(jù)饰抒，1到100分成第一段肮砾，101到200分成第二段，201到300分成第三段……這樣查第250條數(shù)據(jù)袋坑，只要找第三段就可以了仗处，一下子去除了90%的無效數(shù)據(jù)。
6. 索引的使用
查看索引
showindexfrom表名;
創(chuàng)建索引
如果指定字段是字符串咒彤，需要指定長度疆柔，建議長度與定義字段時的長度一致
字段類型如果不是字符串，可以不填寫長度部分
createindex索引名稱on表名(字段名稱(長度))
刪除索引：
dropindex索引名稱on表名;
7. 索引demo
7.1. 創(chuàng)建測試表test_index
createtabletest_index(titlevarchar(10));
7.2 使用python程序（ipython也可以）通過pymsql模塊 向表中加入十萬條數(shù)據(jù)
7.3. 查詢
開啟運行時間監(jiān)測：
setprofiling=1;
查找第1萬條數(shù)據(jù)ha-99999
select*fromtest_indexwheretitle='ha-99999';
查看執(zhí)行的時間：
showprofiles;
為表title_index的title列創(chuàng)建索引：
createindextitle_indexontest_index(title(10));
執(zhí)行查詢語句：
select*fromtest_indexwheretitle='ha-99999';
再次查看執(zhí)行的時間
showprofiles;
8. 注意：
要注意的是镶柱，建立太多的索引將會影響更新和插入的速度，因為它需要同樣更新每個索引文件模叙。對于一個經(jīng)常需要更新和插入的表格歇拆，就沒有必要為一個很少使用的where字句單獨建立索引了，對于比較小的表，排序的開銷不會很大故觅，也沒有必要建立另外的索引厂庇。
建立索引會占用磁盤空間
9. 哪些情況需要創(chuàng)建索引
