索引

幫助mysql高效獲取數據的數據結構

缺點：降低了更新表的效率，增加了磁盤開銷。

B樹和B+樹

m叉的B數每個節點最多包含m-1個key，而B+數最多m個key
B+樹的葉子節點保存了所有的key信息,數據全部存在葉子結點，所有非葉子節點都是key的索引部分

mysql的btree索引對b+樹做了優化，對每個葉子節點都增加了到下一個葉子節點的指針，是雙向鏈表的結構，便于范圍搜索

索引設計原則

對查詢次數高，數據量較大的表建索引
選取查詢條件中的字段建索引
盡量使用唯一索引，區分度越高，效率越好
索引不是越多越好，適當即可
盡量使用短索引（短字段的索引），節省磁盤空間，若構成索引的字段比較短，則在給定大小的存儲塊中可以存儲更多的索引值，
組合索引，n個列組成的復合索引，相當于建立了n個索引，比創建多個索引節約了開銷，例如對where子句中的三個條件col1、col2、col3創建了組合索引，相當于創建了（col1）、（col1，col2）、（col1，col2，col3）個索引，若對col1、col2、col3三列分別創建索引，MySQL只會選擇辨識度高的一列作為索引。假設有100w的數據，一個索引篩選出10%的數據，那么可以篩選出10w的數據；對于組合索引而言，可以篩選出100w*10%*10%*10%=1000條數據
最左匹配原則
假設創建（col1，col2，col3）這樣的一個組合索引，那么相當于對col1列進行排序，也就是我們創建組合索引，以最左邊的為準，只要查詢條件中帶有最左邊的列，那么查詢就會使用到索引

視圖

虛擬存在的表，只是一條select語句
創建視圖：

create view 視圖名字 as select語句

查看視圖：

select * from 視圖名字

修改視圖：

update 視圖名 set 字段="xxx" -- (修改視圖會修改原表中數據，不建議修改)

展示所有視圖：

show views

展示創建視圖語句：

show create view 視圖名

刪除視圖：

drop view (if exists) 視圖名

存儲過程和存儲函數

二者表示封裝了一堆sql的集合，可減少應用程序與數據庫的交互次數，存儲過程無返回值，而存儲函數有返回值，阿里規范中不建議使用，調試不便，移植性差，增加數據庫的負擔

存儲過程

創建存儲過程：

delimiter $ //用$表示';' create procedure 名稱(參數。。。) begin--sql end;

調用存儲過程：

call 存儲過程名()

查看存儲過程：

show procedure status; SELECT name FROM mysql.proc where db='數據庫名' show create procedure 存儲過程名

刪除存儲過程：

drop procedure (if exists) '名稱'

聲明變量

delimiter $ create PROCEDURE test01()BEGIN declare num int DEFAULT 10;-- declare 變量名 變量類型 DEFAULT 默認值;SELECT concat('num 的值為：',num);END$create PROCEDURE pro_test02()BEGIN declare num int DEFAULT 0;set num = num + 10;SELECT num;END$call pro_test02()delimiter $ create PROCEDURE pro_test03()BEGIN declare num int;select count(*) into num from user;SELECT concat('user表中的記錄數為：',num);END$call pro_test03()

條件判斷

delimiter $ create PROCEDURE pro_test04()BEGIN declare height int default 175;declare descr VARCHAR(50) default '';if height >= 180 then set descr='大高個';elseif height >= 170 and height < 180 then set descr='正常人';else set descr='小矮子';end if;SELECT concat('身高',height,'對應的是',descr);END$call pro_test04()

傳遞參數

delimiter $ create PROCEDURE pro_test05(in height int) -- in 輸入參數，out輸出參數，inout既是輸入又是輸出 BEGIN declare descr VARCHAR(50) default '';if height >= 180 then set descr='大高個';elseif height >= 170 and height < 180 then set descr='正常人';else set descr='小矮子';end if;SELECT concat('身高',height,'對應的是',descr);END$call pro_test05(180)delimiter $ create PROCEDURE pro_test06(in height int,out descr VARCHAR(50) )BEGIN if height >= 180 then set descr='大高個';elseif height >= 170 and height < 180 then set descr='正常人';else set descr='小矮子';end if;END$call pro_test06(122,@descr)SELECT @descr -- @表示的為用戶變量 @@系統變量

case結構

delimiter $ create PROCEDURE pro_test07(mon int,out result VARCHAR(10))BEGIN CASEWHEN mon>=1 and mon<=3 THENset result='第一季度';WHEN mon>=4 and mon<=6 THENset result='第二季度';WHEN mon>=7 and mon<=9 THENset result='第三季度';ELSEset result='第四季度'; END CASE;END$call pro_test07(5,@res);SELECT @res

while循環

delimiter $-- 從1累加到n create PROCEDURE pro_test08(n int)BEGIN DECLARE total int DEFAULT 0;DECLARE num int DEFAULT 1;WHILE num <= n DOset total = total + num;set num = num + 1; END WHILE;SELECT total;END$call pro_test08(3);

repeat循環

delimiter $ create PROCEDURE pro_test09(n int)BEGIN DECLARE total int DEFAULT 0;REPEATset total=total + n;set n = n - 1; UNTIL n=0 END REPEAT;SELECT total;END$call pro_test09(3);

loop+leave循環

delimiter$ create PROCEDURE pro_test10(n int) BEGINDECLARE total int default 0;con: LOOP-- con為循環的別名set total = total + n;set n = n - 1;IF n <= 0 THEN-- 退出循環的條件LEAVE con; END IF; END LOOP con; SELECT total; end$CALL pro_test10(100)

游標

游標是用來存儲查詢結果集的數據類型 , 在存儲過程和函數中可以使用光標對結果集進行循環的處理。光標的使用包括光標的聲明、OPEN、FETCH 和 CLOSE，其語法分別如下。

聲明游標：

DECLARE cursor_name CURSOR FOR select_statement ;

OPEN 游標：

OPEN cursor_name ;

FETCH 游標：

FETCH cursor_name INTO var_name [, var_name] ...

CLOSE 游標：

CLOSE cursor_name ;

示例

create table emp(id int(11) not null auto_increment ,name varchar(50) not null comment '姓名',age int(11) comment '年齡',salary int(11) comment '薪水',primary key(`id`) )engine=innodb default charset=utf8 ;insert into emp(id,name,age,salary) values(null,'金毛獅王',55,3800),(null,'白眉鷹王',60,4000),(null,'青翼蝠王',38,2800),(null,'紫衫龍王',42,1800);delimiter$ create PROCEDURE pro_test11() BEGINdeclare e_id int(11);declare e_name varchar(50);declare e_age int(11);declare e_salary int(11);DECLARE emp_result cursor for select * from emp;open emp_result;fetch emp_result into e_id,e_name,e_age,e_salary;select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為: ',e_salary);fetch emp_result into e_id,e_name,e_age,e_salary;select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為: ',e_salary);fetch emp_result into e_id,e_name,e_age,e_salary;select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為: ',e_salary);fetch emp_result into e_id,e_name,e_age,e_salary;select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為: ',e_salary);fetch emp_result into e_id,e_name,e_age,e_salary;select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為: ',e_salary);CLOSE emp_result;end$CALL pro_test11()//使用循環實現delimiter$ create PROCEDURE pro_test12() BEGINdeclare e_id int(11);declare e_name varchar(50);declare e_age int(11);declare e_salary int(11);DECLARE has_data int default 1;//注意聲明順序，否則報錯DECLARE emp_result CURSOR FOR select * from emp;DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;-- 句柄機制open emp_result;WHILE has_data > 0 DOfetch emp_result into e_id,e_name,e_age,e_salary;select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為: ',e_salary); END WHILE;CLOSE emp_result; end$CALL pro_test12();

存儲函數

語法結構

CREATE FUNCTION function_name([param type ... ]) RETURNS type BEGIN... END;

示例

delimiter$ create FUNCTION fun1(empid int) returns int BEGINDECLARE sss int;SELECT salary into sss from emp where id = empid;return sss;END$ -- 若創建失敗執行下面語句 set global log_bin_trust_function_creators=TRUE;SELECT fun1(2)

觸發器

介紹
觸發器是與表有關的數據庫對象，指在 insert/update/delete 之前或之后，觸發并執行觸發器中定義的SQL語句集合。觸發器的這種特性可以協助應用在數據庫端確保數據的完整性 , 日志記錄 , 數據校驗等操作 。

使用別名 OLD 和 NEW 來引用觸發器中發生變化的記錄內容，這與其他的數據庫是相似的。現在觸發器還只支持行級觸發，不支持語句級觸發。

創建觸發器

create trigger trigger_name before/after insert/update/deleteon tbl_name [ for each row ] -- 行級觸發器begintrigger_stmt ;end;

示例

create table emp_logs(id int(11) not null auto_increment,operation varchar(20) not null comment '操作類型, insert/update/delete',operate_time datetime not null comment '操作時間',operate_id int(11) not null comment '操作表的ID',operate_params varchar(500) comment '操作參數',primary key(`id`) )engine=innodb default charset=utf8;-- 添加觸發器 delimiter$ create TRIGGER emp_trigger_insert after insert on emp for each row begininsert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'insert',now(),new.id,concat('插入后(id:',new.id,', name:',new.name,', age:',new.age,', salary:',new.salary,')')); end$ --修改觸發器 delimiter$ create TRIGGER emp_trigger_update after UPDATE on emp for each row begininsert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'update',now(),new.id,concat('修改前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,'),修改后(id:',new.id,', name:',new.name,', age:',new.age,', salary:',new.salary,')')); end$-- 刪除觸發器 delimiter$ create TRIGGER emp_trigger_delete after DELETE on emp for each row begininsert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'update',now(),old.id,concat('刪除的數據(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,')')); end$

查看觸發器

show TRIGGERS

總結

以上是生活随笔為你收集整理的mysql高级学习笔记的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。