前言

--下文為個人在學(xué)習(xí)和工作中的簡單總結(jié)齐媒，供個人參考用，如有問題和建議歡迎指正和指導(dǎo)纷跛！

正文索引

一喻括、sql優(yōu)化
二、觸發(fā)器
三贫奠、窗口函數(shù)
四唬血、存儲過程通過參數(shù)生成動態(tài)表名
五、字段類型轉(zhuǎn)換及日期格式函數(shù)
六叮阅、union和union all 的區(qū)別

一刁品、sql優(yōu)化

（1）整體思路

梳理sql邏輯泣特，縮小查詢范圍浩姥，減少不必要的計算量；在where状您、group by勒叠、order by兜挨、join等語句涉及的字段設(shè)置索引，避免全表掃描眯分，提升查詢效率拌汇。

（2）創(chuàng)建表時

盡量不給數(shù)據(jù)庫留NULL值，盡量使用NOT NULL進(jìn)行填充
盡量使用varchar弊决，nvarchar噪舀，代替char，nchar飘诗，節(jié)省字段存儲空間

（3）寫SQL腳本時

1.寫明查詢具體列与倡，避免使用，表名過長時使用表的別名*
2.模糊查詢like昆稿，后的關(guān)鍵字少用%纺座；
3.盡量使用join，避免子查詢溉潭；
4.對查詢進(jìn)行優(yōu)化净响，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引喳瓣；
--使用短索引馋贤。如果在前N個字符內(nèi)，多數(shù)值是唯一的畏陕，那么就不要對整個列進(jìn)行索引掸掸。短索引不僅可--以提高查詢速度而且可以節(jié)省磁盤空間和I/O操作
--索引列排序。mysql查詢只使用一個索引蹭秋，因此如果where子句中已經(jīng)使用了索引的話扰付，那么order by中的列是不會使用索引的。
--因此數(shù)據(jù)庫默認(rèn)排序可以符合要求的情況下不要使用排序操作仁讨，盡量不要包含多個列的排序羽莺，如果需要最好給這些列建復(fù)合索引。
--like語句操作洞豁。一般情況下不鼓勵使用like操作盐固，如果非使用不可，注意正確的使用方式丈挟。like ‘%aaa%’不會使用索引刁卜，而like ‘a(chǎn)aa%’可以使用索引。
--不要在列上進(jìn)行運算曙咽。
--不使用NOT IN 蛔趴、<>、例朱！=操作孝情，但<,<=鱼蝉，=，>,>=,BETWEEN,IN是可以用到索引的
--索引要建立在經(jīng)常進(jìn)行select操作的字段上箫荡。
--索引要建立在值比較唯一的字段上魁亦。
--對于那些定義為text、image和bit數(shù)據(jù)類型的列不應(yīng)該增加索引羔挡。因為這些列的數(shù)據(jù)量要么相當(dāng)大洁奈，要么取值很少。
5.不走索引的語句如下
--where的查詢條件里有不等號(where column != …),mysql將無法使用索引绞灼。
--如果where字句的查詢條件里使用了函數(shù)(如：where DAY(column)=…),mysql將無法使用索引睬魂。
--在join操作中(需要從多個數(shù)據(jù)表提取數(shù)據(jù)時)，mysql只有在主鍵和外鍵的數(shù)據(jù)類型相同時才能使用索引镀赌，否則及時建立了索引也不會使用氯哮。
--在使用索引字段作為條件時，如果該索引是復(fù)合索引商佛，那么必須使用到該索引中的第一個字段作為條件時才能保證系統(tǒng)使用該索引喉钢，否則該索引將不會被使用，并且應(yīng)盡可能的讓字段順序與索引順序相一致良姆。
6.多用內(nèi)部函數(shù)
7.where子句后少用肠虽！=，<>,not in 以及對字段進(jìn)行 null 值判斷玛追，or 來連接條件税课；
8.很多時候用 exists 代替 in 是一個好的選擇；
9.并不是所有索引對查詢都有效痊剖，SQL是根據(jù)表中數(shù)據(jù)來進(jìn)行查詢優(yōu)化的韩玩，當(dāng)索引列有大量數(shù)據(jù)重復(fù)時，SQL查詢可能不會去利用索引陆馁；
--如一表中有字段sex找颓，male、female幾乎各一半叮贩，那么即使在sex上建了索引也對查詢效率起不了作用击狮。
10.索引并不是越多越好，索引固然可以提高相應(yīng)的 select 的效率益老，但同時也降低了 insert 及 update 的效率彪蓬；
--一個表的索引數(shù)最好不要超過6個，若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有必要捺萌。
11.避免頻繁創(chuàng)建和刪除臨時表档冬，以減少系統(tǒng)表資源的消耗；
--臨時表并不是不可使用，適當(dāng)?shù)厥褂盟鼈兛梢允鼓承├谈行У方迹纾?dāng)需要重復(fù)引用大型表或常用表中的某個數(shù)據(jù)集時慈参。但是呛牲，對于一次性事件，最好使用導(dǎo)出表驮配。

（4）性能調(diào)優(yōu)時娘扩。定位慢查詢

--long_qurry_time 設(shè)置慢查詢閾值，過濾慢查詢語句壮锻，便于查找慢查詢琐旁；
--sql語句前+explain 查看sql腳本的處理流程，查看具體全表掃描語句猜绣；

二灰殴、觸發(fā)器

觸發(fā)器：某個表發(fā)生一個事件（增刪改操作），然后自動的執(zhí)行預(yù)先編譯好的SQL語句掰邢，執(zhí)行相關(guān)操作牺陶。
觸發(fā)器事件跟觸發(fā)器中的SQL語句是原子性的（要么同時執(zhí)行，要么同時不執(zhí)行）辣之，這樣保證了數(shù)據(jù)的完整性掰伸。
是與表事件相關(guān)的特殊的存儲過程，用于加強(qiáng)數(shù)據(jù)的完整性約束和業(yè)務(wù)規(guī)則

觸發(fā)器語法：

drop trigger if exists 數(shù)據(jù)庫名.觸發(fā)器名
create trigger 觸發(fā)器名
after/before  insert/delete/update  on 表名
for each row   #這句話在mysql是固定的  
begin
sql語句;
end;

查看觸發(fā)器：show trigger from 數(shù)據(jù)庫名;
刪除觸發(fā)器：drop trigger if exists 數(shù)據(jù)庫名.觸發(fā)器名

三怀估、sql窗口函數(shù)：

--mysql 8.0 新增SQL語法對窗口函數(shù)和CTE的支持;
--在MSSQL和Oracle以及PostgreSQL完整支持窗口函數(shù)
常用的窗口函數(shù)：
--專用窗口函數(shù)--row_number()狮鸭，rank()，dense_rank()多搀，NTILE()歧蕉，PERCENT_RANK()。
--在MSSQL和Oracle以及PostgreSQL康铭，使用的語法和表達(dá)的邏輯廊谓，基本上完全一致。
窗口函數(shù)大體可以分為以下兩種：
1.能夠作為窗口函數(shù)的聚合函數(shù)（sum麻削，avg蒸痹，count，max呛哟，min）
2.rank叠荠，dense_rank。row_number等專用窗口函數(shù)扫责。
語法格式：

---<窗口函數(shù)名> over (partition by <列清單> order by <列清單> )

---partition by不是窗口函數(shù)所必須的
---窗口函數(shù)的適用范圍：只能在select子句中使用
專用函數(shù)的種類：
1.rank函數(shù)：計算排序時榛鼎，如果存在相同位次的記錄，則會跳過之后的位次。
2.dense_rank函數(shù)：同樣是計算排序者娱，即使存在相同位次的記錄抡笼，也不會跳過之后的位次。
3.row_number函數(shù)：賦予唯一的連續(xù)位次黄鳍。
4.取同一字段后n個的值函數(shù) lead（arg1推姻，n） arg1表示列名，arg2表示向后行偏移量框沟，默認(rèn)為1藏古。當(dāng)找不到值時返回null 。
5.取同一字段前n個的值函數(shù) lag（arg1忍燥，arg2）arg1表示列名拧晕，arg2表示向前行偏移量，默認(rèn)為1梅垄。當(dāng)找不到值時返回null 厂捞。

--lead('字段1',1) over(partition by '字段' order by '字段')
#lag 和lead 可以 獲取結(jié)果集中，按一定排序所排列的當(dāng)前行的上下相鄰若干offset 的某個行的某個列(不用結(jié)果集的自關(guān)聯(lián)）队丝；
#lag 蔫敲，lead 分別是向前，向后炭玫；
#lag 和lead 有三個參數(shù)奈嘿，第一個參數(shù)是列名，第二個參數(shù)是偏移的offset吞加，第三個參數(shù)是 超出記錄窗口時的默認(rèn)值）

四裙犹、存儲過程通過參數(shù)生成動態(tài)表名

DELIMITER//
DROP PROCEDURE IF EXISTS `根據(jù)參數(shù)變化的動態(tài)表名`;
CREATE PROCEDURE `根據(jù)參數(shù)變化的動態(tài)表名`(IN date1 date)
BEGIN
    SET @date1_0 = DATE_FORMAT(date1,'%Y%m%d');
    SET @table_name = CONCAT(@date1_0,'_用戶訂購記錄');
    SET @sql1_0 = CONCAT('DROP TABLE IF EXISTS ',@table_name,";");
    SET @sql1 = CONCAT("create table ",@table_name," 
                                        SELECT * FROM `用戶訂購記錄` WHERE `訂購日期` =",date1,';');
    PREPARE sql1_0 FROM  @sql1_0;
    PREPARE sql1 FROM  @sql1;
    EXECUTE sql1_0;
    EXECUTE sql1;
    DEALLOCATE PREPARE sql1_0;
    DEALLOCATE PREPARE sql1;
END//
DELIMITER;
--------------------------------------------------------------
CALL `根據(jù)參數(shù)變化的動態(tài)表名`('2019-07-26')；

五衔憨、字段類型轉(zhuǎn)換及日期格式函數(shù)

mysql類型轉(zhuǎn)換：
--CAST(value as type);
--CONVERT(value, type);

REPLACE(str,from_str,to_str)

DATE_FORMAT(date,format)
--%Y :4位叶圃，年
--%y :2位，年
--%M ：英文践图，月名
--%m ：數(shù)值（00：12）掺冠，月
--%D ：帶有英文前綴的月中的天
--%d ：月的天，數(shù)值(00-31)
--%e ：月的天码党，數(shù)值(0-31)

set @d = now();  #獲取現(xiàn)在時間
set @t = CURRENT_DATE();   #獲取當(dāng)前日期
set @t1=DATE_ADD(@t,INTERVAL -7 DAY);       #獲取上周同期
set @t2=DATE_ADD(@t,INTERVAL -day(@t)+1 day);       #獲取當(dāng)月第一天
set @t2_1=date_add(@t2,interval 1 month);       #獲取下月同期
set @t3=DATEDIFF(@t2_1,@t2);        #獲取當(dāng)月天數(shù)
SELECT @d,@t,@t1,@t2,@t3;

六德崭、union和union all 的區(qū)別

1.union去重并排序，union all直接返回合并的結(jié)果揖盘，不去重也不排序眉厨；
2.union all比union性能好；

工作學(xué)習(xí)中的一些sql總結(jié)