MySQLæ°æ®åºç´¢å¼å¤±æç10ç§åºæ¯
æç« ç®å½
- ââMySQLæ°æ®åºç´¢å¼å¤±æç10ç§åºæ¯ââ
- ââåè¨ââ
- ââ1. åå¤å·¥ä½ââ
- ââ1.1 å建user表ââ
- ââ1.2 æå ¥æ°æ®ââ
- ââ1.3 æ¥çæ°æ®åºçæ¬ââ
- ââ1.4 æ¥çæ§è¡è®¡åââ
- ââ2. ä¸æ»¡è¶³æå·¦å¹é ååââ
- ââ2.1 åªäºæ åµç´¢å¼ææï¼ââ
- ââ2.2 åªäºæ åµç´¢å¼å¤±æï¼ââ
- ââ3. 使ç¨äºselect *ââ
- ââ4. ç´¢å¼åä¸æ计ç®ââ
- ââ5. ç´¢å¼åç¨äºå½æ°ââ
- ââ6. å段类åä¸åââ
- ââ7. like左边å å«%ââ
- ââ8. å对æ¯ââ
- ââ9. 使ç¨orå ³é®åââ
- ââ10. not inånot existsââ
- ââ10.1 inå ³é®åââ
- ââ10.2 existså ³é®åââ
- ââ10.3 not inå ³é®åââ
- ââ10.4 not existså ³é®åââ
- ââ11. order byçåââ
- ââ11.1 åªäºæ åµèµ°ç´¢å¼ï¼ââ
- ââ11.1.1 满足æå·¦å¹é ååââ
- ââ11.1.2 é åwhereä¸èµ·ä½¿ç¨ââ
- ââ11.1.3 ç¸åçæåºââ
- ââ11.1.4 两è é½æââ
- ââ11.2 åªäºæ åµä¸èµ°ç´¢å¼ï¼ââ
- ââ11.2.1 没å whereælimitââ
- ââ11.2.2 对ä¸åçç´¢å¼åorder byââ
- ââ11.2.3 ä¸æ»¡è¶³æå·¦å¹é ååââ
- ââ11.2.4 ä¸åçæåºââ
- ââç»è¯ââ
åè¨
ä¸ç¥éä½ å¨å®é å·¥ä½ä¸ï¼æ没æéå°è¿ä¸é¢çè¿ä¸¤ç§æ åµï¼
- ææå¨æ个å段ä¸å äºç´¢å¼ï¼ä½å®é ä¸å¹¶æ²¡æçæã
- ç´¢å¼ææ¶åçæäºï¼ææ¶å没æçæã
ä»å¤©å°±è·å¤§å®¶ä¸èµ·èèï¼mysqlæ°æ®åºç´¢å¼å¤±æç10ç§åºæ¯ï¼ç»æ¾ç»è¸©è¿åï¼æè å³å°è¦è¸©åçæå们ä¸ä¸ªåèã
1. åå¤å·¥ä½
æè°ç©ºå£æ åï¼å¦ææç´æ¥æç´¢å¼å¤±æçè¿äºåºæ¯ä¸¢åºæ¥ï¼å¯è½æ²¡æä»»ä½è¯´æåã
æ以ï¼æå³å®å»ºè¡¨åé æ°æ®ï¼ç»å¤§å®¶ä¸æ¥æ¥æ¼ç¤ºææï¼å°½éåå°æçææ®ã
æç¸ä¿¡ï¼å¦æ大家èå¿ççå®è¿ç¯æç« ï¼ä¸å®ä¼æå¾å¤æ¶è·çã
1.1 å建user表
å建ä¸å¼ user表ï¼è¡¨ä¸å å«ï¼â
âidâ
ââãâ
âcodeâ
ââãâ
âageâ
ââãâ
ânameâ
ââåâ
âheightâ
âå段ã
CREATE TABLE `user` (
`id` int NOT NULL AUTO_INCREMENT,
`code` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
`age` int DEFAULT '0',
`name` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
`height` int DEFAULT '0',
`address` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_code_age_name` (`code`,`age`,`name`),
KEY `idx_height` (`height`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
æ¤å¤ï¼è¿å建äºä¸ä¸ªç´¢å¼ï¼
- â
âï¼æ°æ®åºç主é®âidâ
- â
âï¼ç±codeãageånameä¸ä¸ªå段ç»æçèåç´¢å¼ãâidx_code_age_nameâ
- â
âï¼æ®éç´¢å¼âidx_heightâ
1.2 æå ¥æ°æ®
为äºæ¹ä¾¿ç»å¤§å®¶åæ¼ç¤ºï¼æç¹æåuser表ä¸æå ¥äº3æ¡æ°æ®ï¼
INSERT INTO sue.user (id, code, age, name, height) VALUES (1, '101', 21, 'å¨æé©°', 175,'é¦æ¸¯');
INSERT INTO sue.user (id, code, age, name, height) VALUES (2, '102', 18, 'å¨æ°ä¼¦', 173,'å°æ¹¾');
INSERT INTO sue.user (id, code, age, name, height) VALUES (3, '103', 23, 'èä¸', 174,'æé½');
å¨æé©°åå¨æ°ä¼¦æ¯æå¶åï¼å¨è¿éèªæäºä¸æ¬¡ï¼æä»ä»¬åææ¾å°ä¸èµ·äºãåååã
1.3 æ¥çæ°æ®åºçæ¬
为äºé²æ¢ä»¥ååºç°ä¸å¿ è¦ç误ä¼ï¼å¨è¿éæå¿ è¦æ¥ä¸ä¸å½åæ°æ®åºççæ¬ãä¸è¯´çæ¬å°±ç´æ¥ç»ç»è®ºï¼æ¯èæµæ°ï¼åååã
select version();
æ¥åºå½åçmysqlçæ¬å·ä¸ºï¼â
â8.0.21â
â
1.4 æ¥çæ§è¡è®¡å
å¨mysqlä¸ï¼å¦æä½ æ³æ¥çææ¡sqlè¯å¥æ¯å¦ä½¿ç¨äºç´¢å¼ï¼æè 已建好çç´¢å¼æ¯å¦å¤±æï¼å¯ä»¥éè¿â
âexplainâ
âå ³é®åï¼æ¥ç该sqlè¯å¥çæ§è¡è®¡åï¼æ¥å¤æç´¢å¼ä½¿ç¨æ åµã
ä¾å¦ï¼
explain select * from user where id=1;
æ§è¡ç»æï¼
ä»å¾ä¸å¯ä»¥çåºï¼ç±äºidå段æ¯ä¸»é®ï¼è¯¥sqlè¯å¥ç¨å°äºâ
â主é®ç´¢å¼â
âã
2. ä¸æ»¡è¶³æå·¦å¹é åå
ä¹åæå·²ç»ç»codeãageånameè¿3个å段建好â
âèåç´¢å¼â
âï¼idx_code_age_nameã
该索å¼å段ç顺åºæ¯ï¼
- code
- age
- name
å¦æå¨ä½¿ç¨èåç´¢å¼æ¶ï¼æ²¡æ³¨ææå·¦åç¼ååï¼å¾æå¯è½å¯¼è´ç´¢å¼å¤±æåï¼ä¸ä¿¡æ们ä¸èµ·å¾ä¸çã
2.1 åªäºæ åµç´¢å¼ææï¼
å ççåªäºæ åµä¸ï¼è½èµ°ç´¢å¼ã
explain select * from user
where code='101';
explain select * from user
where code='101' and age=21
explain select * from user
where code='101' and age=21 and name='å¨æé©°';
æ§è¡ç»æï¼
ä¸é¢ä¸ç§æ åµï¼sqlé½è½æ£å¸¸èµ°ç´¢å¼ã
å ¶å®è¿æä¸ç§æ¯è¾ç¹æ®çåºæ¯ï¼
explain select * from user
where code = '101' and name='å¨æé©°';
æ§è¡ç»æï¼
æ¥è¯¢æ¡ä»¶åæ¬ç顺åºæ¯ï¼codeãageãnameï¼ä½è¿éåªæcodeånameä¸é´æå±äºï¼æäºageå段ï¼è¿ç§æ åµä¹è½èµ°codeå段ä¸çç´¢å¼ã
çå°è¿éï¼ä¸ç¥éèªæçä½ ï¼æ没æåç°è¿æ ·ä¸ä¸ªè§å¾ï¼è¿4æ¡sqlä¸é½æcodeå段ï¼å®æ¯ç´¢å¼å段ä¸ç第ä¸ä¸ªå段ï¼ä¹å°±æ¯æ左边çå段ãåªè¦æè¿ä¸ªå段å¨ï¼è¯¥sqlå·²ç»å°±è½èµ°ç´¢å¼ã
è¿å°±æ¯æ们æ说çâ
âæå·¦å¹é
ååâ
âã
2.2 åªäºæ åµç´¢å¼å¤±æï¼
åé¢æå·²ç»ä»ç»è¿ï¼å»ºç«äºèåç´¢å¼åï¼å¨æ¥è¯¢æ¡ä»¶ä¸æåªäºæ åµç´¢å¼æ¯ææçã
æ¥ä¸æ¥ï¼æ们éç¹ççåªäºæ åµä¸ç´¢å¼ä¼å¤±æã
explain select * from user
where age=21;
explain select * from user
where name='å¨æé©°';
explain select * from user
where age=21 and name='å¨æé©°';
æ§è¡ç»æï¼
ä»å¾ä¸çåºè¿3ç§æ åµä¸ç´¢å¼ç¡®å®å¤±æäºã
说æ以ä¸3ç§æ åµä¸æ»¡è¶³æå·¦å¹é ååï¼è¯´ç½äºæ¯å 为æ¥è¯¢æ¡ä»¶ä¸ï¼æ²¡æå å«ç»å®å段æ左边çç´¢å¼å段ï¼å³å段codeã
3. 使ç¨äºselect *
å¨ãé¿éå·´å·´å¼åæåãä¸æ确说è¿ï¼æ¥è¯¢sqlä¸ç¦æ¢ä½¿ç¨â
âselect *â
â ã
é£ä¹ï¼ä½ ç¥é为ä»ä¹åï¼
åºè¯ä¸å¤è¯´ï¼æç §å½é æ¯ä¾å ä¸ä¸æ¡sqlï¼
explain
select * from user where name='èä¸';
æ§è¡ç»æï¼
å¨è¯¥sqlä¸ç¨äºâ
âselect *â
âï¼ä»æ§è¡ç»æçï¼èµ°äºå ¨è¡¨æ«æï¼æ²¡æç¨å°ä»»ä½ç´¢å¼ï¼æ¥è¯¢æçæ¯é常ä½çã
å¦ææ¥è¯¢çæ¶åï¼åªæ¥æ们çæ£éè¦çåï¼èä¸æ¥ææåï¼ç»æä¼æä¹æ ·ï¼
é常快éçå°ä¸é¢çsqlæ¹æåªæ¥äºcodeånameåï¼å¤ªeasyäºï¼
explain
select code,name from user
where name='èä¸';
æ§è¡ç»æï¼
ä»å¾ä¸æ§è¡ç»æä¸é¾çåºï¼è¯¥sqlè¯å¥è¿æ¬¡èµ°äºâ
âå
¨ç´¢å¼æ«æâ
ââï¼æ¯â
âå
¨è¡¨æ«æâ
âæçæ´é«ã
å ¶å®è¿éç¨å°äºï¼â
âè¦çç´¢å¼â
âã
å¦æselectè¯å¥ä¸çæ¥è¯¢åï¼é½æ¯ç´¢å¼åï¼é£ä¹è¿äºå被称为è¦çç´¢å¼ãè¿ç§æ åµä¸ï¼æ¥è¯¢çç¸å ³å段é½è½èµ°ç´¢å¼ï¼ç´¢å¼æ¥è¯¢æçç¸å¯¹æ¥è¯´æ´é«ä¸äºã
è使ç¨â
âselect *â
âæ¥è¯¢ææåçæ°æ®ï¼å¤§æ¦çä¼æ¥è¯¢éç´¢å¼åçæ°æ®ï¼éç´¢å¼åä¸ä¼èµ°ç´¢å¼ï¼æ¥è¯¢æçé常ä½ã
4. ç´¢å¼åä¸æ计ç®
ä»ç»æ¬ç« èå 容åï¼å è·å¤§å®¶ä¸èµ·å顾ä¸ä¸ï¼æ ¹æ®idæ¥è¯¢æ°æ®çsqlè¯å¥ï¼
explain select * from user where id=1;
æ§è¡ç»æï¼
ä»å¾ä¸å¯ä»¥çåºï¼ç±äºidå段æ¯ä¸»é®ï¼è¯¥sqlè¯å¥ç¨å°äºâ
â主é®ç´¢å¼â
âã
ä½å¦æidåä¸é¢æ计ç®ï¼æ¯å¦ï¼
explain select * from user where id+1=2;
æ§è¡ç»æï¼
ä»ä¸å¾ä¸çæ§è¡ç»æï¼è½å¤éå¸¸æ¸ æ¥ççåºï¼è¯¥idå段ç主é®ç´¢å¼ï¼å¨æ计ç®çæ åµä¸å¤±æäºã
5. ç´¢å¼åç¨äºå½æ°
ææ¶åæ们å¨ææ¡sqlè¯å¥çæ¥è¯¢æ¡ä»¶ä¸ï¼éè¦ä½¿ç¨å½æ°ï¼æ¯å¦ï¼æªåæ个å段çé¿åº¦ã
åå¦ç°å¨æ个éæ±ï¼æ³æ¥åºææ身é«æ¯17å¼å¤´ç人ï¼å¦æsqlè¯å¥åæè¿æ ·ï¼
explain select * from user where height=17;
该sqlè¯å¥ç¡®å®ç¨å°äºæ®éç´¢å¼ï¼
ä½è¯¥sqlè¯å¥è¯å®æ¯æé®é¢çï¼å 为å®åªè½æ¥åºèº«é«æ£å¥½çäº17çï¼ä½å¯¹äº174è¿ç§æ åµï¼å®æ²¡åæ³æ¥åºæ¥ã
为äºæ»¡è¶³ä¸é¢çè¦æ±ï¼æ们éè¦æsqlè¯å¥ç¨ç¨æ¹é äºä¸ä¸ï¼
explain select * from user where SUBSTR(height,1,2)=17;
è¿æ¶éè¦ç¨å°â
âSUBSTRâ
âå½æ°ï¼ç¨å®æªåäºheightå段çåé¢ä¸¤ä½å符ï¼ä»ç¬¬ä¸ä¸ªå符å¼å§ã
æ§è¡ç»æï¼
ä½ æ没æåç°ï¼å¨ä½¿ç¨è¯¥å½æ°ä¹åï¼è¯¥sqlè¯å¥ç«ç¶èµ°äºå ¨è¡¨æ«æï¼ç´¢å¼å¤±æäºã
6. å段类åä¸å
å¨sqlè¯å¥ä¸å 为å段类åä¸åï¼è导è´ç´¢å¼å¤±æçé®é¢ï¼å¾å®¹æéå°ï¼å¯è½æ¯æ们æ¥å¸¸å·¥ä½ä¸æ容æ忽ç¥çé®é¢ã
å°åºæä¹åäºå¢ï¼
请大家注æè§å¯ä¸ä¸t_user表ä¸çcodeå段ï¼å®æ¯â
âvarcharâ
âå符类åçã
å¨sqlè¯å¥ä¸æ¥è¯¢æ°æ®æ¶ï¼æ¥è¯¢æ¡ä»¶æ们å¯ä»¥åæè¿æ ·ï¼
explain
select * from user where code="101";
æ§è¡ç»æï¼
ä»ä¸å¾ä¸çå°ï¼è¯¥codeå段走äºç´¢å¼ã
温馨æéä¸ä¸ï¼æ¥è¯¢å符å段æ¶ï¼ç¨åå¼å·âââååå¼å·â
âââ
âé½å¯ä»¥ã
â'â
ä½å¦æä½ å¨åsqlæ¶ï¼ä¸å°å¿æå¼å·å¼æäºï¼æsqlè¯å¥åæäºï¼
explain
select * from user where code=101;
æ§è¡ç»æï¼
ä½ ä¼æå¥çåç°ï¼è¯¥sqlè¯å¥ç«ç¶åæäºå ¨è¡¨æ«æãå 为å°åäºå¼å·ï¼è¿ç§å°å°ç失误ï¼ç«ç¶è®©codeå段ä¸çç´¢å¼å¤±æäºã
è¿æ¶ä½ å¿éå¯è½æä¸ä¸ä¸ªä¸ºä»ä¹ï¼å ¶ä¸æä¸ä¸ªè¯å®æ¯ï¼ä¸ºä»ä¹ç´¢å¼ä¼å¤±æå¢ï¼
çï¼å 为codeå段çç±»åæ¯varcharï¼èä¼ åçç±»åæ¯intï¼ä¸¤ç§ç±»åä¸åã
æ¤å¤ï¼è¿æä¸ä¸ªæ趣çç°è±¡ï¼å¦æintç±»åçheightå段ï¼å¨æ¥è¯¢æ¶å äºå¼å·æ¡ä»¶ï¼å´è¿å¯ä»¥èµ°ç´¢å¼ï¼
explain select * from user
where height='175';
æ§è¡ç»æï¼
ä»å¾ä¸çåºè¯¥sqlè¯å¥ç¡®å®èµ°äºç´¢å¼ãintç±»åçåæ°ï¼ä¸ç®¡å¨æ¥è¯¢æ¶å 没å å¼å·ï¼é½è½èµ°ç´¢å¼ã
è¿æ¯åéæ¯åï¼è¿ä¸ç§å¦åã
çï¼mysqlåç°å¦ææ¯â
âintâ
ââç±»åå段ä½ä¸ºæ¥è¯¢æ¡ä»¶æ¶ï¼å®ä¼èªå¨å°è¯¥å段çä¼ åè¿è¡â
âéå¼è½¬æ¢â
âï¼æå符串转æ¢æintç±»åã
mysqlä¼æä¸é¢ååä¸çå符串175ï¼è½¬æ¢ææ°å175ï¼æ以ä»ç¶è½èµ°ç´¢å¼ã
æ¥ä¸æ¥ï¼çä¸ä¸ªæ´æ趣çsqlè¯å¥ï¼
select 1 + '1';
å®çæ§è¡ç»ææ¯2ï¼è¿æ¯11å¢ï¼
好å§ï¼ä¸åå ³åäºï¼ç´æ¥å ¬å¸çæ¡æ§è¡ç»ææ¯2ã
mysqlèªå¨æå符串1ï¼è½¬æ¢æäºintç±»åç1ï¼ç¶ååæäºï¼1+1=2ã
ä½å¦æä½ ç¡®å®æ³æ¼æ¥å符串该æä¹åï¼
çï¼å¯ä»¥ä½¿ç¨â
âconcatâ
âå ³é®åã
å ·ä½æ¼æ¥sqlå¦ä¸ï¼
select concat(1,'1');
æ¥ä¸æ¥ï¼å ³é®é®é¢æ¥äºï¼ä¸ºä»ä¹å符串类åçå段ï¼ä¼ å ¥äºintç±»åçåæ°æ¶ç´¢å¼ä¼å¤±æå¢ï¼
çï¼æ ¹æ®mysqlå®ç½ä¸è§£éï¼å符串â1âãâ 1 'ã'1aâé½è½è½¬æ¢æintç±»åç1ï¼ä¹å°±æ¯è¯´å¯è½ä¼åºç°å¤ä¸ªå符串ï¼å¯¹åºä¸ä¸ªintç±»ååæ°çæ åµãé£ä¹ï¼mysqlæä¹ç¥é该æintç±»åç1转æ¢æåªç§å符串ï¼ç¨åªä¸ªç´¢å¼å¿«éæ¥å¼?
æå ´è¶£çå°ä¼ä¼´å¯ä»¥åççå®æ¹ææ¡£ï¼â
âhttps://dev.mysql.com/doc/refman/8.0/en/type-conversion.htmlâ
â
7. like左边å å«%
模ç³æ¥è¯¢ï¼å¨æ们æ¥å¸¸çå·¥ä½ä¸ï¼ä½¿ç¨é¢çè¿æ¯æ¯è¾é«çã
æ¯å¦ç°å¨æ个éæ±ï¼æ³æ¥è¯¢å§æçåå¦æåªäº?
使ç¨â
âlikeâ
âè¯å¥å¯ä»¥å¾å¿«çå®ç°ï¼
select * from user where name like 'æ%';
ä½å¦ælikeç¨çä¸å¥½ï¼å°±å¯è½ä¼åºç°æ§è½é®é¢ï¼å 为ææ¶åå®çç´¢å¼ä¼å¤±æã
ä¸ä¿¡ï¼æ们ä¸èµ·å¾ä¸çã
ç®ålikeæ¥è¯¢ä¸»è¦æä¸ç§æ åµï¼
- like â%aâ
- like âa%â
- like â%a%â
åå¦ç°å¨æ个éæ±ï¼æ³æ¥åºææcodeæ¯10å¼å¤´çç¨æ·ã
è¿ä¸ªéæ±å¤ªç®åäºå§ï¼sqlè¯å¥å¦ä¸ï¼
explain select * from user
where code like '10%';
æ§è¡ç»æï¼
å¾ä¸çåºè¿ç§â
â%â
ââå¨â
â10â
âå³è¾¹æ¶èµ°äºç´¢å¼ã
èå¦ææéæ±æ¹äºï¼æ³åºç°åºææcodeæ¯1ç»å°¾çç¨æ·ã
æ¥è¯¢sqlè¯å¥æ¹ä¸ºï¼
explain select * from user
where code like '%1';
æ§è¡ç»æï¼
ä»å¾ä¸çåºè¿ç§â
â%â
ââå¨â
â1â
â左边æ¶ï¼codeå段ä¸ç´¢å¼å¤±æäºï¼è¯¥sqlåæäºå ¨è¡¨æ«æã
æ¤å¤ï¼å¦æåºç°ä»¥ä¸sqlï¼
explain select * from user
where code like '%1%';
该sqlè¯å¥çç´¢å¼ä¹ä¼å¤±æã
ä¸é¢ç¨ä¸å¥è¯æ»ç»ä¸ä¸è§å¾ï¼å½â
âlikeâ
ââè¯å¥ä¸çâ
â%â
âï¼åºç°å¨æ¥è¯¢æ¡ä»¶ç左边æ¶ï¼ç´¢å¼ä¼å¤±æã
é£ä¹ï¼ä¸ºä»ä¹ä¼åºç°è¿ç§ç°è±¡å¢ï¼
çï¼å ¶å®å¾å¥½ç解ï¼ç´¢å¼å°±ååå ¸ä¸çç®å½ãä¸è¬ç®å½æ¯æåæ¯æè æ¼é³ä»å°å°å¤§ï¼ä»å·¦å°å³æåºï¼æ¯æ顺åºçã
æ们å¨æ¥ç®å½æ¶ï¼é常ä¼å ä»å·¦è¾¹ç¬¬ä¸ä¸ªåæ¯è¿è¡å¹å¯¹ï¼å¦æç¸åï¼åå¹å¯¹å·¦è¾¹ç¬¬äºä¸ªåæ¯ï¼å¦æåç¸åå¹å¯¹å ¶ä»çåæ¯ï¼ä»¥æ¤ç±»æ¨ã
éè¿è¿ç§æ¹å¼æ们è½å¿«ééå®ä¸ä¸ªå ·ä½çç®å½ï¼æè 缩å°ç®å½çèå´ã
ä½å¦æä½ ç¡¬è¦è·ç®å½ç设计åçæ¥ï¼å ä»åå ¸ç®å½å³è¾¹å¹é 第ä¸ä¸ªåæ¯ï¼è¿ç»é¢ä½ å¯ä»¥èªè¡èè¡¥ä¸ä¸ï¼ä½ ç¼ä¸å¯è½åªå©ä¸ç»æäºï¼ååã
8. å对æ¯
ä¸é¢çå 容é½æ¯å¸¸è§éæ±ï¼æ¥ä¸æ¥ï¼æ¥ç¹ä¸ä¸æ ·çã
åå¦æ们ç°å¨æè¿æ ·ä¸ä¸ªéæ±ï¼è¿æ»¤åºè¡¨ä¸æ两åå¼ç¸åçè®°å½ãæ¯å¦user表ä¸idå段åheightå段ï¼æ¥è¯¢åºè¿ä¸¤ä¸ªå段ä¸å¼ç¸åçè®°å½ã
è¿ä¸ªéæ±å¾ç®åï¼sqlå¯ä»¥è¿æ ·åï¼
explain select * from user
where id=height
æ§è¡ç»æï¼
æä¸æå¤ï¼æä¸æåï¼ç´¢å¼å¤±æäºã
为ä»ä¹ä¼åºç°è¿ç§ç»æï¼
idå段æ¬èº«æ¯æ主é®ç´¢å¼çï¼åæ¶heightå段ä¹å»ºäºæ®éç´¢å¼çï¼å¹¶ä¸ä¸¤ä¸ªå段é½æ¯intç±»åï¼ç±»åæ¯ä¸æ ·çã
ä½å¦ææ两个åç¬å»ºäºç´¢å¼çåï¼ç¨æ¥åå对æ¯æ¶ç´¢å¼ä¼å¤±æã
æå ´è¶£çæåå¯ä»¥æ¾æç§èã
9. 使ç¨orå ³é®å
æ们平æ¶å¨åæ¥è¯¢sqlæ¶ï¼ä½¿ç¨â
âorâ
âå ³é®åçåºæ¯é常å¤ï¼ä½å¦æä½ ç¨ä¸æ³¨æï¼å°±å¯è½è®©å·²æçç´¢å¼å¤±æã
ä¸ä¿¡ä¸èµ·å¾ä¸é¢çã
æå¤©ä½ éå°è¿æ ·ä¸ä¸ªéæ±ï¼æ³æ¥ä¸ä¸id=1æè height=175çç¨æ·ã
ä½ ä¸ä¸äºé¤äºå°±æsqlå好äºï¼
explain select * from user
where id=1 or height='175';
æ§è¡ç»æï¼
没éï¼è¿æ¬¡ç¡®å®èµ°äºç´¢å¼ï¼æåè¢«ä½ è对äºï¼å 为å好idåheightå段é½å»ºäºç´¢å¼ã
ä½æ¥ä¸æ¥çä¸ä¸ªå¤é»é£é«çæä¸ï¼éæ±æ¹äºï¼é¤äºåé¢çæ¥è¯¢æ¡ä»¶ä¹åï¼è¿æ³å ä¸ä¸ªaddress=âæé½âã
è¿è¿ä¸ç®åï¼sqlèµ°èµ·ï¼
explain select * from user
where id=1 or height='175' or address='æé½';
æ§è¡ç»æï¼
ç»ææ²å§äºï¼ä¹åçç´¢å¼é½å¤±æäºã
ä½ å¯è½ä¸è¸æµé¼ï¼ä¸ºä»ä¹ï¼æåäºä»ä¹ï¼
çï¼å ä¸ºä½ æåå çaddresså段没æå ç´¢å¼ï¼ä»è导è´å ¶ä»å段çç´¢å¼é½å¤±æäºã
注æï¼å¦æ使ç¨äºâ âorâ
âå
³é®åï¼é£ä¹å®åé¢ååé¢çå段é½è¦å ç´¢å¼ï¼ä¸ç¶ææçç´¢å¼é½ä¼å¤±æï¼è¿æ¯ä¸ä¸ªå¤§åã
10. not inånot exists
å¨æ们æ¥å¸¸å·¥ä½ä¸ç¨å¾ä¹æ¯è¾å¤çï¼è¿æèå´æ¥è¯¢ï¼å¸¸è§çæï¼
- in
- exists
- not in
- not exists
- between and
ä»å¤©éç¹èèåé¢åç§ã
10.1 inå ³é®å
åå¦æ们æ³æ¥åºheightå¨æäºèå´ä¹å çç¨æ·ï¼è¿æ¶sqlè¯å¥å¯ä»¥è¿æ ·åï¼
explain select * from user
where height in (173,174,175,176);
æ§è¡ç»æï¼
ä»å¾ä¸å¯ä»¥çåºï¼sqlè¯å¥ä¸ç¨â
âinâ
âå ³é®åæ¯èµ°äºç´¢å¼çã
10.2 existså ³é®å
ææ¶å使ç¨â
âinâ
ââå ³é®åæ¶æ§è½ä¸å¥½ï¼è¿æ¶å°±è½ç¨â
âexistsâ
âå ³é®åä¼åsqläºï¼è¯¥å ³é®åè½è¾¾å°inå ³é®åç¸åçææï¼
explain select * from user t1
where exists (select 1 from user t2 where t2.height=173 and t1.id=t2.id)
æ§è¡ç»æï¼
ä»å¾ä¸å¯ä»¥çåºï¼ç¨â
âexistsâ
âå ³é®ååæ ·èµ°äºç´¢å¼ã
10.3 not inå ³é®å
ä¸é¢æ¼ç¤ºç两个ä¾åæ¯æ£åçèå´ï¼å³å¨æäºèå´ä¹å ã
é£ä¹ååçèå´ï¼å³ä¸å¨æäºèå´ä¹å ï¼è½èµ°ç´¢å¼ä¸ï¼
è¯ä¸å¤è¯´ï¼å çç使ç¨â
ânot inâ
âçæ åµï¼
explain select * from user
where height not in (173,174,175,176);
æ§è¡ç»æï¼
ä½ æ²¡çéï¼ç´¢å¼å¤±æäºã
çå¦æç°å¨éæ±æ¹äºï¼æ³æ¥ä¸ä¸idä¸çäº1ã2ã3çç¨æ·æåªäºï¼è¿æ¶sqlè¯å¥å¯ä»¥æ¹æè¿æ ·ï¼
explain select * from user
where id not in (173,174,175,176);
æ§è¡ç»æï¼
ä½ å¯è½ä¼æå¥çåç°ï¼ä¸»é®å段ä¸ä½¿ç¨not inå ³é®åæ¥è¯¢æ°æ®èå´ï¼ä»»ç¶å¯ä»¥èµ°ç´¢å¼ãèæ®éç´¢å¼å段使ç¨äºnot inå ³é®åæ¥è¯¢æ°æ®èå´ï¼ç´¢å¼ä¼å¤±æã
10.4 not existså ³é®å
é¤æ¤ä¹å¤ï¼å¦æsqlè¯å¥ä¸ä½¿ç¨â
ânot existsâ
âæ¶ï¼ç´¢å¼ä¹ä¼å¤±æãå ·ä½sqlè¯å¥å¦ä¸ï¼
explain select * from user t1
where not exists (select 1 from user t2 where t2.height=173 and t1.id=t2.id)
æ§è¡ç»æï¼
ä»å¾ä¸çåºsqlè¯å¥ä¸ä½¿ç¨not existså ³é®åï¼t1表走äºå ¨è¡¨æ«æï¼å¹¶æ²¡æèµ°ç´¢å¼ã
11. order byçå
å¨sqlè¯å¥ä¸ï¼å¯¹æ¥è¯¢ç»æè¿è¡æåºæ¯é常常è§çéæ±ï¼ä¸è¬æ åµä¸æ们ç¨å ³é®åï¼â
âorder byâ
âå°±è½æå®ã
ä½æå§ç»è§å¾order byæºé¾ç¨çï¼å®è·â
âwhereâ
ââæè â
âlimitâ
âå ³é®åæå¾å¤åä¸ä¸ç¼çèç³»ï¼ä¸ä¸å°å¿å°±ä¼åºé®é¢ã
Let go
11.1 åªäºæ åµèµ°ç´¢å¼ï¼
é¦å å½ç¶è¦æ¸©æä¸ç¹ï¼ä¸èµ·ççorder byçåªäºæ åµå¯ä»¥èµ°ç´¢å¼ã
æä¹å说è¿ï¼å¨codeãageånameè¿3个å段ä¸ï¼å·²ç»å»ºäºèåç´¢å¼ï¼idx_code_age_nameã
11.1.1 满足æå·¦å¹é åå
order byåé¢çæ¡ä»¶ï¼ä¹è¦éµå¾ªèåç´¢å¼çæå·¦å¹é ååãå ·ä½æ以ä¸sqlï¼
explain select * from user
order by code limit 100;
explain select * from user
order by code,age limit 100;
explain select * from user
order by code,age,name limit 100;
æ§è¡ç»æï¼
ä»å¾ä¸çåºè¿3æ¡sqlé½è½å¤æ£å¸¸èµ°ç´¢å¼ã
é¤äºéµå¾ªæå·¦å¹é ååä¹å¤ï¼æ个éå¸¸å ³é®çå°æ¹æ¯ï¼åé¢è¿æ¯å äºâ
âlimitâ
âå ³é®åï¼å¦æä¸å å®ç´¢å¼ä¼å¤±æã
11.1.2 é åwhereä¸èµ·ä½¿ç¨
order byè¿è½é åwhereä¸èµ·éµå¾ªæå·¦å¹é ååã
explain select * from user
where code='101'
order by age;
æ§è¡ç»æï¼
codeæ¯èåç´¢å¼ç第ä¸ä¸ªå段ï¼å¨whereä¸ä½¿ç¨äºï¼èageæ¯èåç´¢å¼ç第äºä¸ªå段ï¼å¨order byä¸æ¥ç使ç¨ã
åå¦ä¸é´æå±äºï¼sqlè¯å¥åæè¿æ ·ï¼æ§è¡ç»æä¼æ¯ä»ä¹å¢ï¼
explain select * from user
where code='101'
order by name;
æ§è¡ç»æï¼
è½è¯´nameæ¯èåç´¢å¼ç第ä¸ä¸ªå段ï¼ä½æ ¹æ®æå·¦å¹é ååï¼è¯¥sqlè¯å¥ä¾ç¶è½èµ°ç´¢å¼ï¼å 为æ左边ç第ä¸ä¸ªå段codeï¼å¨whereä¸ä½¿ç¨äºãåªä¸è¿order byçæ¶åï¼æåºæçæ¯è¾ä½ï¼éè¦èµ°ä¸æ¬¡â
âfilesortâ
âæåºç½¢äºã
11.1.3 ç¸åçæåº
order byåé¢å¦æå å«äºèåç´¢å¼çå¤ä¸ªæåºå段ï¼åªè¦å®ä»¬çæåºè§å¾æ¯ç¸åçï¼è¦ä¹åæ¶ååºï¼è¦ä¹åæ¶éåºï¼ï¼ä¹å¯ä»¥èµ°ç´¢å¼ã
å ·ä½sqlå¦ä¸ï¼
explain select * from user
order by code desc,age desc limit 100;
æ§è¡ç»æï¼
该示ä¾ä¸order byåé¢çcodeåageå段é½ç¨äºéåºï¼æ以ä¾ç¶èµ°äºç´¢å¼ã
11.1.4 两è é½æ
å¦ææ个èåç´¢å¼å段ï¼å¨whereåorder byä¸é½æï¼ç»æä¼æä¹æ ·ï¼
explain select * from user
where code='101'
order by code, name;
æ§è¡ç»æï¼
codeå段å¨whereåorder byä¸é½æï¼å¯¹äºè¿ç§æ åµï¼ä»å¾ä¸çç»æçåºï¼è¿æ¯è½èµ°äºç´¢å¼çã
11.2 åªäºæ åµä¸èµ°ç´¢å¼ï¼
åé¢ä»ç»çé½æ¯æ£é¢çç¨æ³ï¼æ¯ä¸ºäºè®©å¤§å®¶æ´å®¹ææ¥åä¸é¢åé¢çç¨æ³ã
好äºï¼æ¥ä¸æ¥ï¼éç¹èèorder byçåªäºæ åµä¸ä¸èµ°ç´¢å¼ï¼
11.2.1 没å whereælimit
å¦æorder byè¯å¥ä¸æ²¡æå whereælimitå ³é®åï¼è¯¥sqlè¯å¥å°ä¸ä¼èµ°ç´¢å¼ã
explain select * from user
order by code, name;
æ§è¡ç»æï¼
ä»å¾ä¸çåºç´¢å¼çç失æäºã
11.2.2 对ä¸åçç´¢å¼åorder by
åé¢ä»ç»çåºæ¬é½æ¯èåç´¢å¼ï¼è¿ä¸ä¸ªç´¢å¼çæ åµãä½å¦æ对å¤ä¸ªç´¢å¼è¿è¡order byï¼ç»æä¼æä¹æ ·å¢ï¼
explain select * from user
order by code, height limit 100;
æ§è¡ç»æï¼
ä»å¾ä¸çåºç´¢å¼ä¹å¤±æäºã
11.2.3 ä¸æ»¡è¶³æå·¦å¹é åå
åé¢å·²ç»ä»ç»è¿ï¼order byå¦æ满足æå·¦å¹é ååï¼è¿æ¯ä¼èµ°ç´¢å¼ãä¸é¢ççï¼ä¸æ»¡è¶³æå·¦å¹é ååçæ åµï¼
explain select * from user
order by name limit 100;
æ§è¡ç»æï¼
nameå段æ¯èåç´¢å¼ç第ä¸ä¸ªå段ï¼ä»å¾ä¸çåºå¦æorder byä¸æ»¡è¶³æå·¦å¹é ååï¼ç¡®å®ä¸ä¼èµ°ç´¢å¼ã
11.2.4 ä¸åçæåº
åé¢å·²ç»ä»ç»è¿ï¼å¦æorder byåé¢æä¸ä¸ªèåç´¢å¼çå¤ä¸ªå段ï¼å®ä»¬å ·æç¸åæåºè§åï¼é£ä¹ä¼èµ°ç´¢å¼ã
ä½å¦æå®ä»¬æä¸åçæåºè§åå¢ï¼
explain select * from user
order by code asc,age desc limit 100;
æ§è¡ç»æï¼