python 全棧 資料庫（二）MySQL資料庫進階

標籤：userinfo   alter   標點符號   ref   long   加速   not found   改變   select 函數   

MySQL 進階左右連表： join上下連表： union   #自動去重  （當兩張表裡的資料，有重複的才會自動去重）   union all  #不去重例如：select sid,sname from snameunionselect tid,tname from teacherselect sid,sname from studentUNION ALLselect sid,sname from student1、視圖 （不常用，開發過程中不長用，在開發語句中寫，不要在資料庫中寫）視圖是一個虛擬表（非真實存在），其本質是【根據SQL語句擷取動態資料集，並為其命名】，使用者使用時只需使用【名稱】即可擷取結果集，並可以將其當作表來使用。在一張物理表中，通過某種條件查詢到的資訊，重建一個新的虛擬表，這個表是通過建立時的語句，從物理表中即時拿取資料。視圖不能進行增 改 操作，視圖對應的原物理表資料變更的話，視圖表內的資料也會改變！
建立視圖：文法：create view 視圖名 AS sql語句例子：create view v1 as select * from teacher where tid >2;刪除視圖：drop view 視圖名更改視圖：文法:alter view 視圖名 AS sql語句更改視圖其實更改的就是對應的sql語句使用視圖:使用視圖時，將其當作表進行操作即可，由於視圖是虛擬表，所以無法使用其對真實表進行建立、更新和刪除操作，僅能做查詢用。select * from v1;2、觸發器 （查詢不會引發此操作！）當對某表裡的某行，做增刪改操作時，可在完成之前或是之後去觸發執行某個操作當對某表裡的某行，做增刪改操作時，可以使用觸發器自訂關聯線為當有相關的操作時候，每執行一次相關操作，觸發器也會執行一次。觸發器操作，分為執行前觸發和執行之後觸發！CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW文法：　　增#在執行增加操作前：create trigger 操作名字（自訂，不重複） before insert on 要操作的表名 for each rowbegin函數體，執行sql操作的語句end#在執行增加操作後：create trigger 操作名字（自訂，不重複） after insert on 要操作的表名 for each rowbegin函數體，執行sql操作的語句end

　　刪#在執行刪除操作前：create trigger 操作名字（自訂，不重複） before drop on 要操作的表名 for each rowbegin函數體，執行sql操作的語句end#在執行刪除操作後：create trigger 操作名字（自訂，不重複） after drop on 要操作的表名 for each rowbegin函數體，執行sql操作的語句end　　改#在執行更改操作前：create trigger 操作名字（自訂，不重複） before update on 要操作的表名 for each rowbegin函數體，執行sql操作的語句end#在執行更改操作後：create trigger 操作名字（自訂，不重複） after update on 要操作的表名 for each rowbegin函數體，執行sql操作的語句end 　　在定義觸發器之前，可以先把sql語句終止時用分號觸發執行的操作改成別的符號，然後再更改回來　　當函數體中有多條更改語句的時候，每條語句之後需要用分號結尾。
　　　　　　 若想不與系統衝突，用delimiter 更改sql操作終止的標點符號，修改完之後，等操作結束再改回原分號“;” 不能影響別的操作行為例如：delimiter //觸發器舉例：delimiter //create trigger t1 BEFORE INSERT on student for EACH ROWBEGININSERT into teacher(tname) values(NEW.sname);INSERT into teacher(tname) values(NEW.sname); INSERT into teacher(tname) values(NEW.sname); INSERT into teacher(tname) values(NEW.sname);END //delimiter ;insert into student(gender,class_id,sname) values(‘男‘,1,‘陳XX‘),(‘女‘,1,‘李XX‘);NEW 代表觸發器外執行對應操作，新插入代碼中的資料！ 常用於 insert into 插入新資料，new 擷取要插入的內容OLD 代表觸發器外執行對應操作，對應的原代碼中要變更或是要刪除的資料！ 常用於 刪和改 drop和update刪除觸發器：drop trigger 觸發器名3、函數函數執行方法：select 函數名內建函數時間格式化函數 date_format()文法：date_format(具體時間,格式化操作時間結構,不同的預留位置)例如: date_format(ctime,"%Y-%m")  顯示年月自訂函數（存在於資料庫中，設定好後，在python中直接調用即可）文法：資料類型 是強制型的，建立時指定的什麼類型，就只能傳入什麼類型create function 函數名(參數1 類型1參數2 類型2...)returns 資料類型  #指定函數傳回值的資料類型begindeclare(聲明變數) 變數名 資料類型 default 初始值函數體，執行sql操作的語句end例子：delimiter \create function f1(i1 int,i2 int)returns intBEGINdeclare num int;set num = i1 + i2;return(num);END \delimiter ;注意：函數必須有傳回值函數內部不能寫 select * from tb1 這種sql語句刪除函數：drop function 函數名4、預存程序預存程序是一個SQL語句集合，當主動去調用預存程序時，其中內部的SQL語句會按照邏輯執行。預存程序 可以寫SQL語句！！！是儲存在MySQL上的一個別名，他代指著很多的SQL語句操作，只要使用這個別名就能查到結果！用於替代程式員寫SQL語句為了職責更明確，可以把SQL語句放在程式裡邊，有以下幾種操作方式：方式一：MySQL: 預存程序程式：調用預存程序方式二：MySQL：。。（不操作）程式：SQL語句方式三：MySQL：。。（不操作）程式：類和對象（SQL語句）（ORM架構）建立預存程序：文法：簡單類型：delimiter //create procedure 名字()beginSQL語句end//delimiter ;傳參數：（in,out,inout）<參數不管是否使用，都必須傳值,而且預存程序是沒有傳回值的！！！>delimiter //create procedure p2(in n1 int,in n2 int)BEGINsql語句END//delimiter ;執行預存程序：SQL:call 名字()py：cursor.callproc("名字",(傳參))cursor.commit() 一定要提交操作cursor.fetchall() 擷取查詢到的結果集刪除預存程序：drop procedure 名字;
分類：1. 簡單create procedure p1()BEGINselect * from student;INSERT into teacher(tname) values("ct");ENDcall p1()cursor.callproc(‘p1‘)2. 傳參數  in  調用執行時加括弧是為了預留傳參（in,out,inout）delimiter //create procedure p2(in n1 int,in n2 int)BEGINselect * from student where sid > n1;END //delimiter ;sql:    call p2(12,2)py:     cursor.callproc(‘p2‘,(12,2))3. 參數 out  用以類比傳回值 只能往回傳，擷取不到傳入的值delimiter //create procedure p3(in n1 int,inout n2 int)BEGINset n2 = 123123;select * from student where sid > n1;END //delimiter ;sql:set @v1 = 10;建立並設定回話層級的變數      建立session層級變數 @    set 設定變數call p3(12,@v1)select @v1;  列印 執行結束後的 全域變數py:cursor.callproc(‘p3‘,(12,2)) 傳參r1 = cursor.fetchall() 拿結果集print(r1)cursor.execute(‘select @_p3_0,@_p3_1‘) 固定寫法 拿取預存程序的執行結果r2 = cursor.fetchall()print(r2)(等同於 sql 中的以下操作！set @_p3_0 = 12 session 層級的變數ser @_p3_1 = 2call p3(@_p3_0,@_p3_1)select @_p3_0,@_p3_1)總結：=======>特性：a. 可傳參： in   out   inoutb. pymysql1.拿結果集cursor.callproc(‘p3‘,(12,2))r1 = cursor.fetchall()print(r1)2.拿傳回值cursor.execute(‘select @_p3_0,@_p3_1‘)r2 = cursor.fetchall()print(r2)*為什麼有結果集又有out偽造的傳回值？（主要原因：是用於判斷執行SQL語句的結果！）delimiter //create procedure p3(in n1 int,out n2 int  設定一個值，用於標識預存程序的執行結果  1,2)BEGIN  -- 執行居多的增加語句insert into 表名(列名) values();insert into 表名(列名) values();insert into 表名(列名) values();insert into 表名(列名) values();insert into 表名(列名) values();END //delimiter ;4. 事務事務用於將某些操作的多個SQL作為原子性操作，一旦有某一個出現錯誤，即可復原到原來的狀態，從而保證資料庫資料完整性。判斷操作完成狀態：執行完成就結束，有錯誤就復原虛擬碼：delimiter //create procedure p4(out status int)BEGIN1. 聲明如果出現異常則執行{set status = 1; 檢測到錯誤 返回的狀態值rollback;  必須加上 復原操作！}開始事務-- 某1賬戶減去100  操作-- 某2賬戶加90-- 某3賬戶加10commit;  事物執行完，正確會提交！結束set status = 2; 正常執行成功，返回狀態值END //delimiter ;===============================delimiter //create PROCEDURE p5(OUT p_return_code tinyint)BEGIN   DECLARE exit handler for sqlexception    BEGIN -- ERROR set p_return_code = 1; rollback;   END;    START TRANSACTION; -- 開始事務DELETE from tb1;insert into tb2(name)values(‘seven‘);  COMMIT;    -- SUCCESS   set p_return_code = 2;    END//delimiter ;擷取out返回結果：sql:set @v2 = 0;call p5(@v2);select @v2;py:cursor.execute(‘select @_p5_0‘)r5 = cursor.fetchall()print(r5)5. 遊標 cursor (跟迴圈有關) 注意：效能不高！對每一行資料，分門別類的要進行計算的時候，才會使用！1、聲明遊標2、擷取A表中的資料my_cursor select id ,num from A3、迴圈操作！for row_id,row_num in my_cursor: （不智能，不知道什麼時候迴圈結束）#檢測迴圈時候還有資料，如果沒有資料就退出！insert into B(num) values (row_id+row_num)4、要想使用變數得先聲明delimiter //create procedure p6()begin #聲明變數declare row_id int; -- 自訂變數1  declare row_num int; -- 自訂變數2 declare done INT DEFAULT FALSE;declare temp int;#建立遊標declare my_cursor CURSOR FOR select id,num from A; -- 聲明遊標類型declare CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 遊標內部 沒有值 done設定為True#開始操作open my_cursor; -- 開啟遊標xxoo: LOOP  文法：迴圈名:LOOP 開始迴圈fetch my_cursor into row_id,row_num;if done then leave xxoo;END IF;set temp = row_id + row_num;insert into B(number) values(temp);end loop xxoo;close my_cursor; -- 關閉遊標end  //delimter ;6. 動態執行SQL（防SQL注入）虛擬碼：delimiter //create procedure p7(in tpl varchar(255), # 接收一大堆的字串in arg int)begin 1. 預檢測某個東西 SQL語句合法性  做防sql注入的問題2. SQL =格式化 tpl + arg 把傳入的兩個參數進行字串格式化3. 執行SQL語句     -----> 拿取結果集set @xo = arg;PREPARE xxx(變數，隨便起名字) FROM tpl (‘select * from student where sid > ?‘);EXECUTE xxx USING @xo;   -- 注意：格式化的時候，傳入的值必須是session層級的變數DEALLOCATE prepare xxx; -- 執行格式化完成的SQL語句end  //delimter ;call p7("select * from tb where id > ?",9)  # ？表示預留位置======> 樣本 <======delimiter \CREATE PROCEDURE p8 (in nid int)BEGINset @nid = nid;PREPARE prod FROM ‘select * from student where sid > ?‘;EXECUTE prod USING @nid;DEALLOCATE prepare prod; END\delimiter ;資料庫相關操作：1. SQL語句 *****    優先使用   （要求速度！）- select xx() from xx ;  2.先尋找內建函數，有就用！   利用MySQL內部提供的功能  （效能要求不高！）5、 索引索引是表的目錄，在尋找內容之前可以先在目錄中尋找索引位置，以此快速定位查詢資料。對於索引，會儲存在額外的檔案中。作用：- 約束- 加速尋找索引：- 主鍵索引：加速尋找 + 不可為空 + 不能重複- 普通索引：加速尋找- 唯一索引：加速尋找 + 不能重複- 聯合索引（多列）：- 聯合主鍵索引- 聯合唯一索引- 聯合普通索引加速尋找：快：select * from tb where name=‘asdf‘select * from tb where id=999假設：id  name  email........無索引：對整個表從前到後依次尋找索引：id   建立額外檔案(某種格式儲存)name 建立額外檔案(某種格式儲存)email 建立額外檔案(某種格式儲存)  create index ix_name on userinfo3(email);name  email 建立額外檔案(某種格式儲存)索引種類（某種格式儲存）：hash索引： （儲存是對錶中的資料進行儲存，無序）單值速度快範圍速度慢  對於連續型的或是有範圍的資料會慢，原因是hash表記憶體儲的資料與原表中的資料不對稱btree索引： 二叉樹 索引   金字塔結構，從頂到底尋找，左支比當前數小，右支比當前數大========》 結果：快 《========建立索引：必須注意的三點：- a. 額外的檔案儲存特殊的資料結構  （建立索引會重新建立一個檔案，以儲存對應的關係）- b. 查詢快；但是執行 插入 更新 刪除 操作時慢- c. 命中索引 （利用建立的索引進行查詢，建立了不使用就是浪費）select * from userinfo3 where email=‘[email protected]‘;select * from userinfo3 where email like ‘alex%‘; 慢 (模糊比對，索引沒用，還是會在原資料中一個一個去找)主鍵索引：主鍵有兩個功能：加速查詢 和 唯一約束普通索引：- create index 索引名稱 on 表名(列名,)- drop index 索引名稱 on 表名唯一索引：- create unique index 索引名稱 on 表名(列名)- drop unique index 索引名稱 on 表名複合式索引：（最左首碼匹配）複合式索引是將n個列組合成一個索引。其應用情境為：頻繁的同時使用n列來進行查詢遵循：最左首碼匹配規則，跟索引順序有關，條件中第一個判斷必須是建立複合式索引的第一個值。- create unique index 索引名稱 on 表名(列名1,列名2)- drop unique index 索引名稱 on 表名例：- create index ix_name_email on test(name,email,)- 最左首碼匹配select  * from test where name=‘alex666‘; 會select  * from test where name=‘alex666‘ and email=‘[email protected]‘; 會select  * from test where email=‘[email protected]‘; 不會複合式索引效率 > 索引合并 （具體還是以使用環境決定）複合式索引 （當查詢的資訊需要多個條件時）- (name,email,)select * from test where name=‘alex666‘ and email=‘[email protected]‘; 快select * from test where name=‘alex666‘; 索引合并：（平常查詢就用一個條件判斷，多個條件不常用的情況）- name- emailselect * from test where name=‘alex666‘; 快select * from test where email=‘[email protected]‘; 快select * from test where name=‘alex666‘ and email=‘[email protected]‘; 相對快名詞：虛擬存在的索引覆蓋索引：- 直接在索引檔案中擷取資料 (不是真實的索引)select的資料列只用從索引中就能夠取得，不必讀取資料行，換句話說查詢列要被所建的索引覆蓋例：select email from test where email="[email protected]"索引合并：- 把多個單列索引合并使用例：select email from test where email="[email protected]" and tid=2222222文本索引：對整個資料表做成索引，不常用到，一般是用第三方軟體去調用由於索引是專門用於加速搜尋而生，所以加上索引之後，查詢效率會快到飛起來。資料庫表中添加索引後確實會讓查詢速度起飛，但前提必須是正確的使用索引來查詢，如果以錯誤的方式使用，則即使建立索引也會不奏效。2. 為頻繁尋找的列建立索引 但是有些文法不會對索引加速若想快速查詢，我們的操作是這樣的：- 建立索引- 命中索引 *****大資料量下注意，小資料沒事！但是以下方式不會命中索引：- like ‘%xx‘ 模糊尋找 select * from test where email like ‘alex%‘;可用第三方工具去加速！- 使用函數 取反 倒序select * from test where reverse(email) = ‘[email protected]‘;若想翻轉尋找，就在python中先把要查詢的資料反轉，再去和資料庫內容匹配- or  索引 or 非索引 就無法命中索引操作select * from test where tid = 1 or name = ‘[email protected]‘;特別的：當or條件中有未建立索引的列才失效，以下會走索引select * from test where tid = 1 or name = ‘alex2666666‘ and email = ‘[email protected]‘ #會忽略非索引- 尋找條件，填寫的資料類型不一致如果列是字串類型，傳入條件是必須用引號引起來，若是寫成數字類型，這裡邊就涉及轉碼的過程，肯定會拖慢查詢速度select * from test where email = 999;- !=  不等於 說白了就是取反select * from test where email != ‘alex‘特別的：如果是主鍵，則還是會走索引select * from test where tid != 123- > 大於select * from test where email > ‘[email protected]‘特別的：如果是主鍵或索引是整數類型，則還是會走索引select * from test where tid > 123select * from test where num > 123- order by 排序注意：當根據索引排序時候，選擇的映射（要顯示的列）如果不是索引，則不走索引select name from test order by email desc;特別的：如果對主鍵排序，則還是走索引：select * from test order by nid desc; - 複合式索引最左首碼如果複合式索引為：(name,email)name and email       -- 使用索引name                 -- 使用索引email                -- 不使用索引3. 時間   explain + 查詢SQL - 用於顯示SQL執行資訊參數，根據參考資訊可以進行SQL最佳化      MySQL有執行計畫：在真正執行操作之前，先讓mysql預估執行操作(一般正確) 看看費時和type類型！好做判斷！文法：explain SQL語句     SQL 預估操作時間（僅作參考）type類型：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/constall 是全域掃描ref 是索引掃描id,email慢：select * from test where name=‘alex2666666‘explain select * from test where name=‘alex2666666‘type: ALL(全表掃描)select * from test limit 1; #limit  分頁擷取是個例外，此種操作擷取前面的資料會很快（因為操作的資料少啊），若起始是尋找後邊好幾百萬的資料行，照樣飛不起來！快：select * from test where email=‘alex2666666‘type: const(走索引)4. DBA工作慢日誌- 執行時間 > 10- 未命中索引- 記錄檔路徑配置：（修改設定檔之前，一定要注意先備份！要不然就得跑路！！！）- 程式執行暫存在記憶體中，同時配置資訊也在！- 查看當前配置資訊：（記憶體內的變數）　　     show variables like ‘%query%‘  #查看有 query  的變數 （主要是看配置資訊，檔案路徑）　　     show variables like ‘%queries%‘　#查看有 queries 的變數（主要是看索引）- 修改當前配置：　　　　set global 變數名 = 值set slow_query_log = ON      開啟慢日誌記錄set slow_query_log_file =  D:\mysql\setup\mysql-5.7.18-winx64\data\ZYJ-PC-slow.log   記錄檔 預設在data目錄中set long_query_time = 2      時間限制，超過此時間，則記錄set log_queries_not_using_indexes = ON    為使用索引的搜尋記錄- 設定檔寫好設定檔，然後再開啟資料庫的時候一起執行生效，這樣就省的在每次開啟之後，再去配置！開啟執行：    mysqld --defaults-file=‘設定檔的絕對路徑+設定檔名‘建立設定檔   --->隨意設定檔內容：slow_query_log = ON      開啟慢日誌記錄slow_query_log_file =  D:\mysql\setup\mysql-5.7.18-winx64\data\ZYJ-PC-slow.log   記錄檔 預設在data目錄中long_query_time = 2      時間限制，超過此時間，則記錄log_queries_not_using_indexes = ON    為使用索引的搜尋記錄注意：修改設定檔之後，需要重啟服務生效（修改只是修改在硬碟內的檔案，內容內的檔案沒有修改！）5. ******分頁*******a. 限制頁數，只能查看多少頁select * from test limit 20,10;b.三種方法- 直接不讓看- 索引表中掃：（覆蓋索引）SELECT * FROM test WHERE tid in (SELECT N.tid FROM (SELECT * FROM test LIMIT 2900000,10) AS N)- 方案：相對尋找主要是 ------> 記錄當前頁最大或最小ID1. 頁面只有上一頁，下一頁# max_id# min_id下一頁：tid > max_idselect * from test where tid > 2900000 limit 10;上一頁：tid < min_idSELECT * FROM (select * from test where tid < 2899991 ORDER BY tid DESC limit 10) AS N ORDER BY N.tid ASC; 2. 跳頁 上一頁 192 193  [196]  197  198  199 下一頁SELECT * FROM(SELECT * FROM (SELECT * FROM test WHERE tid > 2800000 LIMIT 40) AS N ORDER BY N.tid DESC LIMIT 10) AS S ORDER BY S.tid ASCc. *****為什麼不用 between and *****：最致命的原因：若id不連續，無法直接使用id範圍進行尋找，擷取的資料就不在是定值！

python 全棧 資料庫（二）MySQL資料庫進階