1. ç´¢å¼
1.1 ç´¢å¼æ¦è¿°
MySQLå®æ¹å¯¹ç´¢å¼çå®ä¹ä¸ºï¼ç´¢å¼ï¼indexï¼æ¯å¸®å©MySQLé«æè·åæ°æ®çæ°æ®ç»æï¼æåºï¼ãå¨æ°æ®ä¹å¤ï¼æ°æ®åºç³»ç»è¿ç»´æ¤è 满足ç¹å®æ¥æ¾ç®æ³çæ°æ®ç»æï¼è¿äºæ°æ®ç»æ以æç§æ¹å¼å¼ç¨ï¼æåï¼æ°æ®ï¼ è¿æ ·å°±å¯ä»¥å¨è¿äºæ°æ®ç»æä¸å®ç°é«çº§æ¥æ¾ç®æ³ï¼è¿ç§æ°æ®ç»æå°±æ¯ç´¢å¼ãå¦ä¸é¢çæ示 :
左边æ¯æ°æ®è¡¨ï¼ä¸å ±æ两åä¸æ¡è®°å½ï¼æ左边çæ¯æ°æ®è®°å½çç©çå°åï¼æ³¨æé»è¾ä¸ç¸é»çè®°å½å¨ç£çä¸ä¹å¹¶ä¸æ¯ä¸å®ç©çç¸é»çï¼ã为äºå å¿«Col2çæ¥æ¾ï¼å¯ä»¥ç»´æ¤ä¸ä¸ªå³è¾¹æ示çäºåæ¥æ¾æ ï¼æ¯ä¸ªèç¹åå«å å«ç´¢å¼é®å¼åä¸ä¸ªæå对åºæ°æ®è®°å½ç©çå°åçæéï¼è¿æ ·å°±å¯ä»¥è¿ç¨äºåæ¥æ¾å¿«éè·åå°ç¸åºæ°æ®ã
ä¸è¬æ¥è¯´ç´¢å¼æ¬èº«ä¹å¾å¤§ï¼ä¸å¯è½å ¨é¨åå¨å¨å åä¸ï¼å æ¤ç´¢å¼å¾å¾ä»¥ç´¢å¼æ件çå½¢å¼åå¨å¨ç£çä¸ãç´¢å¼æ¯æ°æ®åºä¸ç¨æ¥æé«æ§è½çæ常ç¨çå·¥å ·ã
2.2 ç´¢å¼ä¼å¿å£å¿
ä¼å¿
1ï¼ ç±»ä¼¼äºä¹¦ç±çç®å½ç´¢å¼ï¼æé«æ°æ®æ£ç´¢çæçï¼éä½æ°æ®åºçIOææ¬ã
2ï¼ éè¿ç´¢å¼å对æ°æ®è¿è¡æåºï¼éä½æ°æ®æåºçææ¬ï¼éä½CPUçæ¶èã
å£å¿
1ï¼ å®é ä¸ç´¢å¼ä¹æ¯ä¸å¼ 表ï¼è¯¥è¡¨ä¸ä¿åäºä¸»é®ä¸ç´¢å¼å段ï¼å¹¶æåå®ä½ç±»çè®°å½ï¼æ以索å¼åä¹æ¯è¦å ç¨ç©ºé´çã
2ï¼ è½ç¶ç´¢å¼å¤§å¤§æé«äºæ¥è¯¢æçï¼åæ¶å´ä¹éä½æ´æ°è¡¨çé度ï¼å¦å¯¹è¡¨è¿è¡INSERTãUPDATEãDELETEãå 为æ´æ°è¡¨æ¶ï¼MySQL ä¸ä» è¦ä¿åæ°æ®ï¼è¿è¦ä¿åä¸ä¸ç´¢å¼æ件æ¯æ¬¡æ´æ°æ·»å äºç´¢å¼åçå段ï¼é½ä¼è°æ´å 为æ´æ°æ带æ¥çé®å¼åååçç´¢å¼ä¿¡æ¯ã
1.3 ç´¢å¼ç»æ
ç´¢å¼æ¯å¨MySQLçåå¨å¼æå±ä¸å®ç°çï¼èä¸æ¯å¨æå¡å¨å±å®ç°çãæ以æ¯ç§åå¨å¼æçç´¢å¼é½ä¸ä¸å®å®å ¨ç¸åï¼ä¹ä¸æ¯ææçåå¨å¼æé½æ¯æææçç´¢å¼ç±»åçãMySQLç®åæä¾äºä»¥ä¸4ç§ç´¢å¼ï¼
- BTREE ç´¢å¼ ï¼ æ常è§çç´¢å¼ç±»åï¼å¤§é¨åç´¢å¼é½æ¯æ B æ ç´¢å¼ã
- HASH ç´¢å¼ï¼åªæMemoryå¼ææ¯æ ï¼ ä½¿ç¨åºæ¯ç®å ã
- R-tree ç´¢å¼ï¼ç©ºé´ç´¢å¼ï¼ï¼ç©ºé´ç´¢å¼æ¯MyISAMå¼æçä¸ä¸ªç¹æ®ç´¢å¼ç±»åï¼ä¸»è¦ç¨äºå°ç空é´æ°æ®ç±»åï¼é常使ç¨è¾å°ï¼ä¸åç¹å«ä»ç»ã
- Full-text ï¼å ¨æç´¢å¼ï¼ ï¼å ¨æç´¢å¼ä¹æ¯MyISAMçä¸ä¸ªç¹æ®ç´¢å¼ç±»åï¼ä¸»è¦ç¨äºå ¨æç´¢å¼ï¼InnoDBä»Mysql5.6çæ¬å¼å§æ¯æå ¨æç´¢å¼ã
ç´¢å¼ | InnoDBå¼æ | MyISAMå¼æ | Memoryå¼æ |
---|---|---|---|
BTREEç´¢å¼ | æ¯æ | æ¯æ | æ¯æ |
HASH ç´¢å¼ | ä¸æ¯æ | ä¸æ¯æ | æ¯æ |
R-tree ç´¢å¼ | ä¸æ¯æ | æ¯æ | ä¸æ¯æ |
Full-text | 5.6çæ¬ä¹åæ¯æ | æ¯æ | ä¸æ¯æ |
æ们平常æ说çç´¢å¼ï¼å¦æ没æç¹å«ææï¼é½æ¯æB+æ ï¼å¤è·¯æç´¢æ ï¼å¹¶ä¸ä¸å®æ¯äºåçï¼ç»æç»ç»çç´¢å¼ãå ¶ä¸èéç´¢å¼ãå¤åç´¢å¼ãåç¼ç´¢å¼ãå¯ä¸ç´¢å¼é»è®¤é½æ¯ä½¿ç¨ B+tree ç´¢å¼ï¼ç»ç§°ä¸º ç´¢å¼ã
1.3.1 BTREE ç»æ
BTreeåå«å¤è·¯å¹³è¡¡æç´¢æ ï¼ä¸é¢måçBTreeç¹æ§å¦ä¸ï¼
- æ ä¸æ¯ä¸ªèç¹æå¤å å«m个å©åã
- é¤æ ¹èç¹ä¸å¶åèç¹å¤ï¼æ¯ä¸ªèç¹è³å°æ[ceil(m/2)]个å©åã
- è¥æ ¹èç¹ä¸æ¯å¶åèç¹ï¼åè³å°æ两个å©åã
- ææçå¶åèç¹é½å¨åä¸å±ã
- æ¯ä¸ªéå¶åèç¹ç±n个keyä¸n+1个æéç»æï¼å ¶ä¸[ceil(m/2)-1] <= n <= m-1
以5åBTree为ä¾ï¼keyçæ°éï¼å ¬å¼æ¨å¯¼[ceil(m/2)-1] <= n <= m-1ãæ以 2 <= n <=4 ãå½n>4æ¶ï¼ä¸é´èç¹åè£å°ç¶èç¹ï¼ä¸¤è¾¹èç¹åè£ã
æå ¥ C N G A H E K Q M F W L T Z D P R X Y S æ°æ®ä¸ºä¾ã
æ¼åè¿ç¨å¦ä¸ï¼
1). æå ¥å4个åæ¯ C N G A
2). æå ¥Hï¼n>4ï¼ä¸é´å ç´ Gåæ¯åä¸åè£å°æ°çèç¹
3). æå ¥Eï¼Kï¼Qä¸éè¦åè£
4). æå ¥Mï¼ä¸é´å ç´ Måæ¯åä¸åè£å°ç¶èç¹G
5). æå ¥Fï¼Wï¼Lï¼Tä¸éè¦åè£
6). æå ¥Zï¼ä¸é´å ç´ Tåä¸åè£å°ç¶èç¹ä¸
7). æå ¥Dï¼ä¸é´å ç´ Dåä¸åè£å°ç¶èç¹ä¸ãç¶åæå ¥Pï¼Rï¼Xï¼Yä¸éè¦åè£
8). æåæå ¥Sï¼NPQRèç¹n>5ï¼ä¸é´èç¹Qåä¸åè£ï¼ä½åè£åç¶èç¹DGMTçn>5ï¼ä¸é´èç¹Måä¸åè£
å°æ¤ï¼è¯¥BTREEæ 就已ç»æ建å®æäºï¼ BTREEæ å äºåæ ç¸æ¯ï¼ æ¥è¯¢æ°æ®çæçæ´é«ï¼ å 为对äºç¸åçæ°æ®éæ¥è¯´ï¼BTREEçå±çº§ç»ææ¯äºåæ å°ï¼å æ¤æç´¢é度快ã
1.3.3 B+TREE ç»æ
B+Tree为BTreeçåç§ï¼B+Treeä¸BTreeçåºå«ä¸ºï¼
1). nåB+Treeæå¤å«æn个keyï¼èBTreeæå¤å«æn-1个keyã
2). B+Treeçå¶åèç¹ä¿åææçkeyä¿¡æ¯ï¼ä¾key大å°é¡ºåºæåã
3). ææçéå¶åèç¹é½å¯ä»¥çä½æ¯keyçç´¢å¼é¨åã
ç±äºB+Treeåªæå¶åèç¹ä¿åkeyä¿¡æ¯ï¼æ¥è¯¢ä»»ä½keyé½è¦ä»rootèµ°å°å¶åãæ以B+Treeçæ¥è¯¢æçæ´å 稳å®ã
1.3.3 MySQLä¸çB+Tree
MySqlç´¢å¼æ°æ®ç»æ对ç»å ¸çB+Treeè¿è¡äºä¼åãå¨åB+Treeçåºç¡ä¸ï¼å¢å ä¸ä¸ªæåç¸é»å¶åèç¹çé¾è¡¨æéï¼å°±å½¢æäºå¸¦æ顺åºæéçB+Treeï¼æé«åºé´è®¿é®çæ§è½ã
MySQLä¸ç B+Tree ç´¢å¼ç»æ示æå¾:
1.4 ç´¢å¼åç±»
1ï¼ åå¼ç´¢å¼ ï¼å³ä¸ä¸ªç´¢å¼åªå å«å个åï¼ä¸ä¸ªè¡¨å¯ä»¥æå¤ä¸ªååç´¢å¼
2ï¼ å¯ä¸ç´¢å¼ ï¼ç´¢å¼åçå¼å¿ é¡»å¯ä¸ï¼ä½å 许æ空å¼
3ï¼ å¤åç´¢å¼ ï¼å³ä¸ä¸ªç´¢å¼å å«å¤ä¸ªå
1.5 ç´¢å¼è¯æ³
ç´¢å¼å¨å建表çæ¶åï¼å¯ä»¥åæ¶åå»ºï¼ ä¹å¯ä»¥éæ¶å¢å æ°çç´¢å¼ã
åå¤ç¯å¢:
create database demo_01 default charset=utf8mb4;
use demo_01;
CREATE TABLE `city` (
`city_id` int(11) NOT NULL AUTO_INCREMENT,
`city_name` varchar(50) NOT NULL,
`country_id` int(11) NOT NULL,
PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `country` (
`country_id` int(11) NOT NULL AUTO_INCREMENT,
`country_name` varchar(100) NOT NULL,
PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `city` (`city_id`, `city_name`, `country_id`) values(1,'西å®',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(2,'NewYork',2);
insert into `city` (`city_id`, `city_name`, `country_id`) values(3,'å京',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(4,'ä¸æµ·',1);
insert into `country` (`country_id`, `country_name`) values(1,'China');
insert into `country` (`country_id`, `country_name`) values(2,'America');
insert into `country` (`country_id`, `country_name`) values(3,'Japan');
insert into `country` (`country_id`, `country_name`) values(4,'UK');
1.5.1 å建索å¼
è¯æ³ ï¼
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name(index_col_name,...)
index_col_name : column_name[(length)][ASC | DESC]
ç¤ºä¾ ï¼ ä¸ºcity表ä¸çcity_nameå段åå»ºç´¢å¼ ï¼
-- æ·»å ç´¢å¼ï¼åå¼ç´¢å¼
CREATE INDEX idx_city_name ON city(city_name);
1.5.2 æ¥çç´¢å¼
è¯æ³ï¼
示ä¾ï¼æ¥çcity表ä¸çç´¢å¼ä¿¡æ¯ï¼
1.5.3 å é¤ç´¢å¼
è¯æ³ ï¼
1.5.4 ALTERå½ä»¤
1). alter table tb_name add primary key(column_list);
该è¯å¥æ·»å ä¸ä¸ªä¸»é®ï¼è¿æå³çç´¢å¼å¼å¿
é¡»æ¯å¯ä¸çï¼ä¸ä¸è½ä¸ºNULL
2). alter table tb_name add unique index_name(column_list);
è¿æ¡è¯å¥å建索å¼çå¼å¿
é¡»æ¯å¯ä¸çï¼é¤äºNULLå¤ï¼NULLå¯è½ä¼åºç°å¤æ¬¡ï¼
3). alter table tb_name add index index_name(column_list);
æ·»å æ®éç´¢å¼ï¼ ç´¢å¼å¼å¯ä»¥åºç°å¤æ¬¡ã
4). alter table tb_name add fulltext index_name(column_list);
该è¯å¥æå®äºç´¢å¼ä¸ºFULLTEXTï¼ ç¨äºå
¨æç´¢å¼
1.6 ç´¢å¼è®¾è®¡åå
ç´¢å¼ç设计å¯ä»¥éµå¾ªä¸äºå·²æçååï¼å建索å¼çæ¶å请尽éèè符åè¿äºååï¼ä¾¿äºæåç´¢å¼ç使ç¨æçï¼æ´é«æç使ç¨ç´¢å¼ã
- 对æ¥è¯¢é¢æ¬¡è¾é«ï¼ä¸æ°æ®éæ¯è¾å¤§ç表建ç«ç´¢å¼ã
- ç´¢å¼å段çéæ©ï¼æä½³åéååºå½ä»whereåå¥çæ¡ä»¶ä¸æåï¼å¦æwhereåå¥ä¸çç»åæ¯è¾å¤ï¼é£ä¹åºå½æéæ常ç¨ãè¿æ»¤æææ好çåçç»åã
- 使ç¨å¯ä¸ç´¢å¼ï¼åºå度è¶é«ï¼ä½¿ç¨ç´¢å¼çæçè¶é«ã
- ç´¢å¼å¯ä»¥ææçæåæ¥è¯¢æ°æ®çæçï¼ä½ç´¢å¼æ°éä¸æ¯å¤å¤çåï¼ç´¢å¼è¶å¤ï¼ç»´æ¤ç´¢å¼ç代价èªç¶ä¹å°±æ°´æ¶¨è¹é«ã对äºæå ¥ãæ´æ°ãå é¤çDMLæä½æ¯è¾é¢ç¹ç表æ¥è¯´ï¼ç´¢å¼è¿å¤ï¼ä¼å¼å ¥ç¸å½é«çç»´æ¤ä»£ä»·ï¼éä½DMLæä½çæçï¼å¢å ç¸åºæä½çæ¶é´æ¶èãå¦å¤ç´¢å¼è¿å¤çè¯ï¼MySQLä¹ä¼ç¯éæ©å°é¾ç ï¼è½ç¶æç»ä»ç¶ä¼æ¾å°ä¸ä¸ªå¯ç¨çç´¢å¼ï¼ä½æ çæé«äºéæ©ç代价ã
- 使ç¨çç´¢å¼ï¼ç´¢å¼å建ä¹åä¹æ¯ä½¿ç¨ç¡¬çæ¥åå¨çï¼å æ¤æåç´¢å¼è®¿é®çI/Oæçï¼ä¹å¯ä»¥æåæ»ä½ç访é®æçãåå¦ææç´¢å¼çå段æ»é¿åº¦æ¯è¾çï¼é£ä¹å¨ç»å®å¤§å°çåå¨åå å¯ä»¥åå¨æ´å¤çç´¢å¼å¼ï¼ç¸åºçå¯ä»¥ææçæåMySQL访é®ç´¢å¼çI/Oæçã
-
å©ç¨æå·¦åç¼ï¼N个åç»åèæçç»åç´¢å¼ï¼é£ä¹ç¸å½äºæ¯å建äºN个索å¼ï¼å¦ææ¥è¯¢æ¶whereåå¥ä¸ä½¿ç¨äºç»æ该索å¼çåå 个å段ï¼é£ä¹è¿æ¡æ¥è¯¢SQLå¯ä»¥å©ç¨ç»åç´¢å¼æ¥æåæ¥è¯¢æçã
å建å¤åç´¢å¼:
CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);
å°±ç¸å½äº
对name åå»ºç´¢å¼ ;
对name , email å建äºç´¢å¼ ;
对name , email, status å建äºç´¢å¼ ;
2. è§å¾
2.1 è§å¾æ¦è¿°
è§å¾ï¼Viewï¼æ¯ä¸ç§èæåå¨ç表ãè§å¾å¹¶ä¸å¨æ°æ®åºä¸å®é åå¨ï¼è¡ååæ°æ®æ¥èªå®ä¹è§å¾çæ¥è¯¢ä¸ä½¿ç¨ç表ï¼å¹¶ä¸æ¯å¨ä½¿ç¨è§å¾æ¶å¨æçæçãéä¿ç讲ï¼è§å¾å°±æ¯ä¸æ¡SELECTè¯å¥æ§è¡åè¿åçç»æéãæ以æ们å¨å建è§å¾çæ¶åï¼ä¸»è¦çå·¥ä½å°±è½å¨å建è¿æ¡SQLæ¥è¯¢è¯å¥ä¸ã
è§å¾ç¸å¯¹äºæ®éç表çä¼å¿ä¸»è¦å æ¬ä»¥ä¸å 项ã
- ç®åï¼ä½¿ç¨è§å¾çç¨æ·å®å ¨ä¸éè¦å ³å¿åé¢å¯¹åºç表çç»æãå ³èæ¡ä»¶åçéæ¡ä»¶ï¼å¯¹ç¨æ·æ¥è¯´å·²ç»æ¯è¿æ»¤å¥½çå¤åæ¡ä»¶çç»æéã
- å®å ¨ï¼ä½¿ç¨è§å¾çç¨æ·åªè½è®¿é®ä»ä»¬è¢«å 许æ¥è¯¢çç»æéï¼å¯¹è¡¨çæé管ç并ä¸è½éå¶å°æ个è¡æ个åï¼ä½æ¯éè¿è§å¾å°±å¯ä»¥ç®åçå®ç°ã
- æ°æ®ç¬ç«ï¼ä¸æ¦è§å¾çç»æç¡®å®äºï¼å¯ä»¥å±è½è¡¨ç»æåå对ç¨æ·çå½±åï¼æºè¡¨å¢å å对è§å¾æ²¡æå½±åï¼æºè¡¨ä¿®æ¹ååï¼åå¯ä»¥éè¿ä¿®æ¹è§å¾æ¥è§£å³ï¼ä¸ä¼é æ对访é®è çå½±åã
2.2 å建æè ä¿®æ¹è§å¾
å建è§å¾çè¯æ³ä¸ºï¼
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
ä¿®æ¹è§å¾çè¯æ³ä¸ºï¼
ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
é项 :
WITH [CASCADED | LOCAL] CHECK OPTION å³å®äºæ¯å¦å
许æ´æ°æ°æ®ä½¿è®°å½ä¸å满足è§å¾çæ¡ä»¶ã
LOCAL ï¼ åªè¦æ»¡è¶³æ¬è§å¾çæ¡ä»¶å°±å¯ä»¥æ´æ°ã
CASCADED ï¼ å¿
须满足ææé对该è§å¾çææè§å¾çæ¡ä»¶æå¯ä»¥æ´æ°ã é»è®¤å¼.
ç¤ºä¾ , å建city_country_viewè§å¾ , æ§è¡å¦ä¸SQL :
create or replace view city_country_view
as
select t.*,c.country_name from country c , city t where c.country_id = t.country_id;
æ¥è¯¢è§å¾ :
select * from city_country_view
2.3 æ¥çè§å¾
ä» MySQL 5.1 çæ¬å¼å§ï¼ä½¿ç¨
SHOW TABLES
å½ä»¤çæ¶åä¸ä» æ¾ç¤ºè¡¨çååï¼åæ¶ä¹ä¼æ¾ç¤ºè§å¾çååï¼èä¸åå¨åç¬æ¾ç¤ºè§å¾ç SHOW VIEWS å½ä»¤ã
åæ ·ï¼å¨ä½¿ç¨
SHOW TABLE STATUS è§å¾å
å½ä»¤çæ¶åï¼ä¸ä½å¯ä»¥æ¾ç¤ºè¡¨çä¿¡æ¯ï¼åæ¶ä¹å¯ä»¥æ¾ç¤ºè§å¾çä¿¡æ¯ã
å¦æéè¦æ¥è¯¢æ个è§å¾çå®ä¹ï¼å¯ä»¥ä½¿ç¨
SHOW CREATE VIEW è§å¾å
å½ä»¤è¿è¡æ¥ç ã
2.4 å é¤è§å¾
è¯æ³ :
ç¤ºä¾ , å é¤è§å¾city_country_view :
3. åå¨è¿ç¨åå½æ°
3.1 åå¨è¿ç¨åå½æ°æ¦è¿°
åå¨è¿ç¨åå½æ°æ¯ äºå ç»è¿ç¼è¯å¹¶åå¨å¨æ°æ®åºä¸çä¸æ®µ SQL è¯å¥çéåï¼è°ç¨åå¨è¿ç¨åå½æ°å¯ä»¥ç®ååºç¨å¼å人åçå¾å¤å·¥ä½ï¼åå°æ°æ®å¨æ°æ®åºååºç¨æå¡å¨ä¹é´çä¼ è¾ï¼å¯¹äºæé«æ°æ®å¤ççæçæ¯æ好å¤çã
åå¨è¿ç¨åå½æ°çåºå«å¨äºå½æ°å¿
é¡»æè¿åå¼ï¼èåå¨è¿ç¨æ²¡æã
å½æ° ï¼ æ¯ä¸ä¸ªæè¿åå¼çè¿ç¨ ï¼
è¿ç¨ ï¼ æ¯ä¸ä¸ªæ²¡æè¿åå¼çå½æ° ï¼
3.2 å建åå¨è¿ç¨
CREATE PROCEDURE procedure_name ([proc_parameter[,...]])
begin
-- SQLè¯å¥
end ;
ç¤ºä¾ ï¼
delimiter $
create procedure pro_test1()
begin
select 'Hello Mysql' ;
end$
delimiter ;
ç¥è¯å°è´´å£«
DELIMITER
该å
³é®åç¨æ¥å£°æSQLè¯å¥çåé符 , åè¯ MySQL 解éå¨ï¼è¯¥æ®µå½ä»¤æ¯å¦å·²ç»ç»æäºï¼mysqlæ¯å¦å¯ä»¥æ§è¡äºãé»è®¤æ
åµä¸ï¼delimiteræ¯åå·;ãå¨å½ä»¤è¡å®¢æ·ç«¯ä¸ï¼å¦ææä¸è¡å½ä»¤ä»¥åå·ç»æï¼é£ä¹å车åï¼mysqlå°ä¼æ§è¡è¯¥å½ä»¤ã
3.3 è°ç¨åå¨è¿ç¨
3.4 æ¥çåå¨è¿ç¨
-- æ¥è¯¢db_nameæ°æ®åºä¸çææçåå¨è¿ç¨
select name from mysql.proc where db='db_name';
-- æ¥è¯¢åå¨è¿ç¨çç¶æä¿¡æ¯
show procedure status;
-- æ¥è¯¢æ个åå¨è¿ç¨çå®ä¹
show create procedure test.pro_test1 \G;
3.5 å é¤åå¨è¿ç¨
DROP PROCEDURE [IF EXISTS] sp_name ï¼
3.6 è¯æ³
åå¨è¿ç¨æ¯å¯ä»¥ç¼ç¨çï¼æå³çå¯ä»¥ä½¿ç¨åéï¼è¡¨è¾¾å¼ï¼æ§å¶ç»æ ï¼ æ¥å®ææ¯è¾å¤æçåè½ã
3.6.1 åé
-
DECLARE
éè¿ DECLARE å¯ä»¥å®ä¹ä¸ä¸ªå±é¨åéï¼è¯¥åéçä½ç¨èå´åªè½å¨ BEGINâ¦END åä¸ã
ç¤ºä¾ :
delimiter $
create procedure pro_test2()
begin
declare num int default 5;
select num+ 10;
end$
delimiter ;
- SET
ç´æ¥èµå¼ä½¿ç¨ SETï¼å¯ä»¥èµå¸¸éæè èµè¡¨è¾¾å¼ï¼å ·ä½è¯æ³å¦ä¸ï¼
SET var_name = expr [, var_name = expr] ...
ç¤ºä¾ :
DELIMITER $
CREATE PROCEDURE pro_test3()
BEGIN
DECLARE NAME VARCHAR(20);
SET NAME = 'MYSQL';
SELECT NAME ;
END$
DELIMITER ;
ä¹å¯ä»¥éè¿select ⦠into æ¹å¼è¿è¡èµå¼æä½ :
DELIMITER $
CREATE PROCEDURE pro_test5()
BEGIN
declare countnum int;
select count(*) into countnum from city;
select countnum;
END$
DELIMITER ;
3.6.2 ifæ¡ä»¶å¤æ
è¯æ³ç»æ :
if search_condition then statement_list
[elseif search_condition then statement_list] ...
[else statement_list]
end if;
éæ±ï¼
æ ¹æ®å®ä¹ç身é«åéï¼å¤å®å½å身é«çæå±ç身æç±»å
180 åä»¥ä¸ ----------> 身æé«æ
170 - 180 ---------> æ å身æ
170 ä»¥ä¸ ----------> ä¸è¬èº«æ
ç¤ºä¾ :
delimiter $
create procedure pro_test6()
begin
declare height int default 175;
declare description varchar(50);
if height >= 180 then
set description = '身æé«æ';
elseif height >= 170 and height < 180 then
set description = 'æ å身æ';
else
set description = 'ä¸è¬èº«æ';
end if;
select description ;
end$
delimiter ;
3.6.3 ä¼ éåæ°
è¯æ³æ ¼å¼ :
create procedure procedure_name([in/out/inout] åæ°å åæ°ç±»å)
...
IN : 该åæ°å¯ä»¥ä½ä¸ºè¾å
¥ï¼ä¹å°±æ¯éè¦è°ç¨æ¹ä¼ å
¥å¼ , é»è®¤
OUT: 该åæ°ä½ä¸ºè¾åºï¼ä¹å°±æ¯è¯¥åæ°å¯ä»¥ä½ä¸ºè¿åå¼
INOUT: æ¢å¯ä»¥ä½ä¸ºè¾å
¥åæ°ï¼ä¹å¯ä»¥ä½ä¸ºè¾åºåæ°
IN - è¾å ¥
éæ± :
æ ¹æ®å®ä¹ç身é«åéï¼å¤å®å½å身é«çæå±ç身æç±»å
ç¤ºä¾ :
delimiter $
create procedure pro_test5(in height int)
begin
declare description varchar(50) default '';
if height >= 180 then
set description='身æé«æ';
elseif height >= 170 and height < 180 then
set description='æ å身æ';
else
set description='ä¸è¬èº«æ';
end if;
select concat('èº«é« ', height , '对åºç身æç±»å为:',description);
end$
delimiter ;
OUT-è¾åº
éæ± :
æ ¹æ®ä¼ å
¥ç身é«åéï¼è·åå½å身é«çæå±ç身æç±»å
示ä¾:
create procedure pro_test5(in height int , out description varchar(100))
begin
if height >= 180 then
set description='身æé«æ';
elseif height >= 170 and height < 180 then
set description='æ å身æ';
else
set description='ä¸è¬èº«æ';
end if;
end$
è°ç¨:
call pro_test5(168, @description)$
select @description$
å°ç¥è¯
@description : è¿ç§åéè¦å¨åéå称åé¢å ä¸â@â符å·ï¼å«åç¨æ·ä¼è¯åéï¼ä»£è¡¨æ´ä¸ªä¼è¯è¿ç¨ä»é½æ¯æä½ç¨çï¼è¿ä¸ªç±»ä¼¼äºå
¨å±åéä¸æ ·ã
@@global.sort_buffer_size : è¿ç§å¨åéåå ä¸ "@@" 符å·, å«å ç³»ç»åé
3.6.4 caseç»æ
è¯æ³ç»æ :
æ¹å¼ä¸ :
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE;
æ¹å¼äº :
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE;
éæ±:
ç»å®ä¸ä¸ªæ份, ç¶å计ç®åºæå¨çå£åº¦
ç¤ºä¾ :
delimiter $
create procedure pro_test9(month int)
begin
declare result varchar(20);
case
when month >= 1 and month <=3 then
set result = '第ä¸å£åº¦';
when month >= 4 and month <=6 then
set result = '第äºå£åº¦';
when month >= 7 and month <=9 then
set result = '第ä¸å£åº¦';
when month >= 10 and month <=12 then
set result = '第åå£åº¦';
end case;
select concat('æ¨è¾å
¥çæ份为 :', month , ' , 该æ份为 : ' , result) as content ;
end$
delimiter ;
3.6.5 while循ç¯
è¯æ³ç»æ:
while search_condition do
statement_list
end while;
éæ±:
计ç®ä»1å å°nçå¼
ç¤ºä¾ :
delimiter $
create procedure pro_test8(n int)
begin
declare total int default 0;
declare num int default 1;
while num<=n do
set total = total + num;
set num = num + 1;
end while;
select total;
end$
delimiter ;
3.6.6 repeatç»æ
ææ¡ä»¶ç循ç¯æ§å¶è¯å¥, å½æ»¡è¶³æ¡ä»¶çæ¶åéåºå¾ªç¯ ãwhile æ¯æ»¡è¶³æ¡ä»¶ææ§è¡ï¼repeat æ¯æ»¡è¶³æ¡ä»¶å°±éåºå¾ªç¯ã
è¯æ³ç»æ :
REPEAT
statement_list
UNTIL search_condition
END REPEAT;
éæ±:
计ç®ä»1å å°nçå¼
ç¤ºä¾ :
delimiter $
create procedure pro_test10(n int)
begin
declare total int default 0;
repeat
set total = total + n;
set n = n - 1;
until n=0
end repeat;
select total ;
end$
delimiter ;
3.6.7 loopè¯å¥
LOOP å®ç°ç®åç循ç¯ï¼éåºå¾ªç¯çæ¡ä»¶éè¦ä½¿ç¨å ¶ä»çè¯å¥å®ä¹ï¼é常å¯ä»¥ä½¿ç¨ LEAVE è¯å¥å®ç°ï¼å ·ä½è¯æ³å¦ä¸ï¼
[begin_label:] LOOP
statement_list
END LOOP [end_label]
å¦æä¸å¨ statement_list ä¸å¢å éåºå¾ªç¯çè¯å¥ï¼é£ä¹ LOOP è¯å¥å¯ä»¥ç¨æ¥å®ç°ç®åçæ»å¾ªç¯ã
3.6.8 leaveè¯å¥
ç¨æ¥ä»æ 注çæµç¨æé ä¸éåºï¼é常å BEGIN ⦠END æè 循ç¯ä¸èµ·ä½¿ç¨ãä¸é¢æ¯ä¸ä¸ªä½¿ç¨ LOOP å LEAVE çç®åä¾å , éåºå¾ªç¯ï¼
delimiter $
CREATE PROCEDURE pro_test11(n int)
BEGIN
declare total int default 0;
ins: LOOP
IF n <= 0 then
leave ins;
END IF;
set total = total + n;
set n = n - 1;
END LOOP ins;
select total;
END$
delimiter ;
3.6.9 游æ /å æ
游æ æ¯ç¨æ¥åå¨æ¥è¯¢ç»æéçæ°æ®ç±»å , å¨åå¨è¿ç¨åå½æ°ä¸å¯ä»¥ä½¿ç¨å æ 对ç»æéè¿è¡å¾ªç¯çå¤çãå æ ç使ç¨å æ¬å æ ç声æãOPENãFETCH å CLOSEï¼å ¶è¯æ³åå«å¦ä¸ã
声æå æ ï¼
OPEN å æ ï¼
FETCH å æ ï¼
FETCH cursor_name INTO var_name [, var_name] ...
CLOSE å æ ï¼
ç¤ºä¾ :
åå§åèæ¬:
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);
-- æ¥è¯¢emp表ä¸æ°æ®, 并éè¡è·åè¿è¡å±ç¤º
create procedure pro_test11()
begin
declare 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$
éè¿å¾ªç¯ç»æ , è·å游æ ä¸çæ°æ® :
DELIMITER $
create procedure pro_test12()
begin
DECLARE id int(11);
DECLARE name varchar(50);
DECLARE age int(11);
DECLARE 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;
repeat
fetch emp_result into id , name , age , salary;
select concat('id为',id, ', name 为' ,name , ', age为 ' ,age , ', èªæ°´ä¸º: ', salary);
until has_data = 0
end repeat;
close emp_result;
end$
DELIMITER ;
3.7 åå¨å½æ°
è¯æ³ç»æ:
CREATE FUNCTION function_name([param type ... ])
RETURNS type
BEGIN
...
END;
æ¡ä¾ :
å®ä¹ä¸ä¸ªåå¨è¿ç¨, 请æ±æ»¡è¶³æ¡ä»¶çæ»è®°å½æ° ;
delimiter $
create function count_city(countryId int)
returns int
begin
declare cnum int ;
select count(*) into cnum from city where country_id = countryId;
return cnum;
end$
delimiter ;
è°ç¨:
select count_city(1);
select count_city(2);
4. 触åå¨
4.1 ä»ç»
触åå¨æ¯ä¸è¡¨æå ³çæ°æ®åºå¯¹è±¡ï¼æå¨ insert/update/delete ä¹åæä¹åï¼è§¦å并æ§è¡è§¦åå¨ä¸å®ä¹çSQLè¯å¥éåã触åå¨çè¿ç§ç¹æ§å¯ä»¥åå©åºç¨å¨æ°æ®åºç«¯ç¡®ä¿æ°æ®çå®æ´æ§ , æ¥å¿è®°å½ , æ°æ®æ ¡éªçæä½ ã
使ç¨å«å OLD å NEW æ¥å¼ç¨è§¦åå¨ä¸åçååçè®°å½å 容ï¼è¿ä¸å ¶ä»çæ°æ®åºæ¯ç¸ä¼¼çãç°å¨è§¦åå¨è¿åªæ¯æè¡çº§è§¦åï¼ä¸æ¯æè¯å¥çº§è§¦åã
触åå¨ç±»å | NEW å OLDçä½¿ç¨ |
---|---|
INSERT å触åå¨ | NEW 表示å°è¦æè å·²ç»æ°å¢çæ°æ® |
UPDATE å触åå¨ | OLD 表示修æ¹ä¹åçæ°æ® , NEW 表示å°è¦æå·²ç»ä¿®æ¹åçæ°æ® |
DELETE å触åå¨ | OLD 表示å°è¦æè å·²ç»å é¤çæ°æ® |
4.2 å建触åå¨
è¯æ³ç»æ :
create trigger trigger_name
before/after insert/update/delete
on tbl_name
[ for each row ] -- è¡çº§è§¦åå¨
begin
trigger_stmt ;
end;
示ä¾
éæ±
éè¿è§¦åå¨è®°å½ emp 表çæ°æ®åæ´æ¥å¿ , å
å«å¢å , ä¿®æ¹ , å é¤ ;
é¦å å建ä¸å¼ æ¥å¿è¡¨ :
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;
å建 insert å触åå¨ï¼å®ææå ¥æ°æ®æ¶çæ¥å¿è®°å½ :
DELIMITER $
create trigger emp_logs_insert_trigger
after insert
on emp
for each row
begin
insert 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 ;
å建 update å触åå¨ï¼å®ææ´æ°æ°æ®æ¶çæ¥å¿è®°å½ :
DELIMITER $
create trigger emp_logs_update_trigger
after update
on emp
for each row
begin
insert 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 ;
å建delete è¡ç触åå¨ , å®æå é¤æ°æ®æ¶çæ¥å¿è®°å½ :
DELIMITER $
create trigger emp_logs_delete_trigger
after delete
on emp
for each row
begin
insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'delete',now(),old.id,concat('å é¤å(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,')'));
end $
DELIMITER ;
æµè¯ï¼
insert into emp(id,name,age,salary) values(null, 'å
æ左使',30,3500);
insert into emp(id,name,age,salary) values(null, 'å
æå³ä½¿',33,3200);
update emp set age = 39 where id = 3;
delete from emp where id = 5;
4.3 å é¤è§¦åå¨
è¯æ³ç»æ :
drop trigger [schema_name.]trigger_name
å¦æ没ææå® schema_nameï¼é»è®¤ä¸ºå½åæ°æ®åº ã
4.4 æ¥ç触åå¨
å¯ä»¥éè¿æ§è¡ SHOW TRIGGERS å½ä»¤æ¥ç触åå¨çç¶æãè¯æ³çä¿¡æ¯ã
è¯æ³ç»æ ï¼
show triggers ï¼