MySQL3>mysql進階

標籤：需要   proc   pytho   使用者   觸發器   注意   視圖   end   Nid   

　　部分內容轉自：https://www.cnblogs.com/wupeiqi/articles/5713323.html

★視圖

1，有點類似給暫存資料表起個別名，然後儲存在資料庫裡，下一次可以直接通過視圖名字訪問
2，視圖是一個【虛擬表】
3，調用的時候得把視圖當成一個【表】來操作

建立視圖:create view v1 as select * from student where sid > 10;修改視圖：alter view v1 as select * from student where sid < 10;刪除視圖：drop view v1

★觸發器

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

刪除

drop trigger t1;

調用

無法自己調用，由於對錶的某些操作【增刪改】觸發

建立

# 插入前CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROWBEGIN    ...END# 插入後CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROWBEGIN    ...END# 刪除前CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROWBEGIN    ...END# 刪除後CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROWBEGIN    ...END# 更新前CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROWBEGIN    ...END# 更新後CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROWBEGIN    ...END產生觸發器

觸發器文法

DELIMITER //CREATE TRIGGER demo BEFORE insertON tb1 FOR EACH ROWBEGINinsert into tb2(name) values(‘hello‘);END//DELIMITER ;

插入資料前觸發器

delimiter //create trigger t1 after insert on tb1 for each rowbegininsert into tb2(name) values(new.name);    # new代指新插入的資料end//delimiter ;

插入資料後觸發器

new：    代指新插入的資料    插入的時候old：    代指老資料    刪除的時候new/old：    新/老資料    更新的時候

關於new old★函數◇內建函數

select curdate();    時間select char_length(‘hello‘);    長度select concat(‘hello‘,‘world‘);        拼接select date_format(‘2017/5/6‘,‘%Y-%m‘)    格式化時間

◇自訂函數

delimiter \create function f1(i1 int,i2 int)returns intbegin    declare num int default 0;    set num = i1 + i2;    return(num);end \delimiter ; 

自訂函數★預存程序

一組為了完成特定功能的【SQL語句集】
1 把SQL語句封裝成預存程序
2 將SQL語句和程式解耦
3 預存程序寫在資料庫服務端裡

◇調用

mysql:call p1();pymysql:cursor.callproc(‘p1‘) ---> 有結果集-->通過fetchall()取值

◇刪除

drop procedure p1;

◇建立

1，普通的預存程序

delimiter //create procedure p1()begin    select * from student;    insert into teacher(tname) values(‘ct‘);end//delimiter ;

簡單的預存程序

2，帶參數的預存程序

in 僅用於傳入參數用out 僅用於傳回值用inout 既可以傳入又可以當作傳回值刪除：drop procedure p1;調用：-- 無參數call proc_name()-- 有參數，全incall proc_name(1,2)-- 有參數，有in，out，inoutset @t1=0;set @t2=3;call proc_name(1,2,@t1,@t2)

delimiter //create procedure p2(in n1 int,in n2 int    # 沒有用到)beginselect * from student where sid > n1;    # 參數傳給SQL語句做條件判斷end //delimiter ;調用:mysql:    call p2(12,2);pymysql:    cursor.callproc(‘p2‘,(12,2))---> 有結果集-->通過fetchall()取值

參數IN

delimiter //create procedure p3(in n1 int,out n2 int        # 引用)begin    set n2 = 123;    # 引用改變    select * from student where sid > n1;end //delimiter ;調用：mysql:    需要先建立傳進去的變數：set @v1 = 0;    call p3(10,@v1);    # 返回結果集    select @v1;            # 查看傳回值pymysql：    結果集：        cursor.callproc(‘p3‘,(12,2))        r1 = cursor.fetchall()        print(r1)    傳回值：        cursor.execute(‘select @_p3_0,@_p3_1‘)        r2 = cursor.fetchall()        print(r2)a

參數OUT

可傳進去，在裡面可以用，還可以在外面用delimiter //create procedure p4(in i1 int,inout i2 int,out r1 int)begin    declare temp1 int;    declare temp2 int default 0;        set temp1 = 1;        set r1 = i1 + temp1 + temp2;    set i2 = i2 + 100;end //delimiter ;調用：mysql：    需要先建立(不建立的話隨便一個變數預設為Null)傳進去的變數：        set @i2 = 4; set @r1 = 0;    call p4(10,@i2,@r1);    查看傳回值：select @i2,@r1;

參數INOUT

3，事務操作的預存程序

delimiter //create procedure p5(out p_return_code tinyint)begin    declare exit handler for sqlexception    # 聲明如果出現異常則執行以下代碼    begin        set p_return_code = 1;            rollback;    # 復原    end;start transaction;    # 開始事務    delete from tb1;    insert into tb2(name) values(‘seven‘);commit;set p_return_code = 2;end //delimiter ;

事務操作的預存程序

4，遊標的預存程序

delimiter //create procedure p6()begin                         # 開始    declare row_id int;                 # 聲明變數row_id    declare row_num int;                 # 聲明變數row_num    declare done int DEFAULT FALSE;        # 聲明done = False    declare temp int;                    # 聲明變數temp         declare my_cursor CURSOR FOR select id,num from A;            # 聲明遊標，並把資料放進遊標裡    declare CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;        # 聲明沒有沒有資料時，則break        open my_cursor;                            # 開啟遊標        xxoo: LOOP                                # 開始迴圈            fetch my_cursor into row_id,row_num;    # 從遊標裡取資料            if done then                             # 如果done是真的                leave xxoo;                            # 則break            END IF;                                    # 結束if            set temp = row_id + row_num;            # 計算temp            insert into B(number) values(temp);        # 往B表插入一條資料，值是temp        end loop xxoo;                            # 離開迴圈    close my_cursor;                        # 關閉遊標end  //                        # 結束delimiter ;

遊標的預存程序

5，動態執行SQL

此處應有內容

 

★索引◇作用

       約束

       加速尋找

◇索引的分類

       普通索引

       主鍵索引：不為空白，唯一約束（不可含null）

       唯一索引：唯一約束（可含null）

       聯合索引（多列）       ---> 最左首碼匹配

              聯合主鍵索引

              聯合唯一索引

              聯合普通索引

◇索引的種類

       hash索引：索引表

              單值速度快

              尋找範圍慢

       btree索引：二叉樹      （innodb預設）

◇SQL命令

建立索引：普通：create index 索引名 on 表名(列名);唯一：create unique index 索引名 on 表名(列名);主鍵：alter table 表名 add primary key(列名);聯合：create index 索引名 on 表名(列名,列名);聯合唯一：create unique index 索引名 on 表名(列名,列名);刪除索引：普通：drop index 索引名 on 表名;唯一：drop unique index 索引名 on 表名;查看索引：show index from 表名

◇名詞

       覆蓋索引:在索引檔案中直接擷取資料

              1，先把email建立成索引

              2，select email from userinfo where email = ‘sdfsd‘;     

                     # 去email列裡找email，只用在索引檔案裡就可以找到

       索引合并：把多個單列索引合并使用

              1，前提：id是索引，email是索引

              2，select * from userinfo where id < 50 and email = ‘sdf‘;

       複合式索引效率 > 索引合并

◇不正確使用索引

- like ‘%xx‘    select * from tb1 where name like ‘%cn‘;- 使用函數    select * from tb1 where reverse(name) = ‘wupeiqi‘;- or    select * from tb1 where nid = 1 or email = ‘[email protected]‘;    特別的：當or條件中有未建立索引的列才失效，以下會走索引            select * from tb1 where nid = 1 or name = ‘seven‘;            select * from tb1 where nid = 1 or email = ‘[email protected]‘ and name = ‘alex‘- 類型不一致    如果列是字串類型，傳入條件是必須用引號引起來，不然...    select * from tb1 where name = 999;- !=    select * from tb1 where name != ‘alex‘    特別的：如果是主鍵，則還是會走索引        select * from tb1 where nid != 123- >    select * from tb1 where name > ‘alex‘    特別的：如果是主鍵或索引是整數類型，則還是會走索引        select * from tb1 where nid > 123        select * from tb1 where num > 123- order by    select email from tb1 order by name desc;    當根據索引排序時候，選擇的映射如果不是索引，則不走索引    特別的：如果對主鍵排序，則還是走索引：        select * from tb1 order by nid desc; - 複合式索引最左首碼    如果複合式索引為：(name,email)    name and email       -- 使用索引    name                 -- 使用索引    email                -- 不使用索引

不正確使用索引

- 避免使用select *- count(1)或count(列) 代替 count(*)- 建立表時盡量時 char 代替 varchar- 表的欄位順序固定長度的欄位優先- 複合式索引代替多個單列索引（經常使用多個條件查詢時）- 盡量使用短索引- 使用串連（JOIN）來代替子查詢(Sub-Queries)- 連表時注意條件類型需一致- 索引散列值（重複少）不適合建索引，例：性別不適合

其他注意事項

 

MySQL3>mysql進階