非常实用的MySQL函数全面总结详解示例分析教程

1.MySQL涓�鍏充簬鍑芥暟鐨勮�存槑

"姒傚康"锛氱被浼糺ava銆乸ython涓�鐨勬柟娉曪紝灏嗕竴缁勯�昏緫璇�鍙ュ皝瑁呭湪鏂规硶浣撲腑锛屽�瑰�栨毚闇叉柟娉曞悕锛�

"濂藉��"锛氣厾闅愯棌浜嗗疄鐜扮粏鑺傦紱鈪℃彁楂樹唬鐮佺殑閲嶇敤鎬э紱

"璋冪敤"锛歴elect 鍑芥暟鍚?瀹炲弬鍒楄〃) [from 琛╙锛?/p>

"鐗圭偣"锛氣厾鍙�浠�涔?鍑芥暟鍚?锛涒叀骞蹭粈涔?鍑芥暟鍔熻兘)锛?/p>

"鍒嗙被"锛氣厾鍗曡�屽嚱鏁帮紱鈪″垎缁勫嚱鏁帮�?/p>

"浠�涔堟槸鍗曡�屽嚱鏁帮�?锛氫綔鐢ㄤ簬琛ㄤ腑鐨勬瘡涓�琛岃�板綍锛屼竴鏉¤�板綍鍑烘潵涓�涓�缁撴灉锛�

"浠�涔堟槸鑱氬悎鍑芥暟锛?锛氫綔鐢ㄤ簬涓�琛屾垨鑰呭�氳�岋紝鏈�缁堣繑鍥炰竴涓�缁撴灉锛�

2.鍗曡�屽嚱鏁板垎绫�

瀛楃�﹀嚱鏁帮�?/p>

鏁板�﹀嚱鏁帮�?/p>

鏃ユ湡鍑芥暟锛?/p>

鍏朵粬鍑芥暟锛?/p>

娴佺▼鎺у埗鍑芥暟锛?/p>

3.瀛楃�﹀嚱鏁�

1锛?code>length(str)锛氳幏鍙栧弬鏁板�肩殑瀛楄妭涓�鏁帮�?/p>

瀵逛簬utf8瀛楃�﹂泦鏉ヨ�达紝涓�涓�鑻辨枃鍗�1涓�瀛楄妭锛涗竴涓�涓�鏂囧崰3涓�瀛楄妭锛�

瀵逛簬gbk瀛楃�﹂泦鏉ヨ�达紝涓�涓�鑻辨枃鍗�1涓�瀛楄妭锛涗竴涓�涓�鏂囧崰2涓�瀛楄妭锛�

鎿嶄綔濡備笅 锛?/p>

2锛?concat(str1,str2,鈥?锛氭嫾鎺ュ瓧绗︿覆锛?/p>

鎿嶄綔濡備笅锛?/p>

3锛?code>upper(str)锛氬皢瀛楃�︿腑鐨勬墍鏈夊瓧姣嶅彉涓哄ぇ鍐欙紱

鎿嶄綔濡備笅锛?/p>

4锛?code>lower(str) 锛氬皢瀛楃�︿腑鎵�鏈夊瓧姣嶅彉涓哄皬鍐欙紱

鎿嶄綔濡備笅锛?/p>

5锛?code>substr(str,start,len)锛氫粠start浣嶇疆寮�濮嬫埅鍙栧瓧绗︿覆锛宭en琛ㄧず瑕佹埅鍙栫殑闀垮害锛?娌℃湁鎸囧畾len闀垮害锛氳〃绀轰粠start寮�濮嬭捣锛屾埅鍙栧埌瀛楃�︿覆鏈�灏俱�傛寚瀹氫簡len闀垮害锛氳〃绀轰粠start寮�濮嬭捣锛屾埅鍙杔en涓�闀垮害銆?/p>

鎿嶄綔濡備笅锛?/p>

6锛?code>instr(str,瑕佹煡鎵剧殑瀛愪覆) 锛氳繑鍥炲瓙涓茬��涓�娆″嚭鐜扮殑绱㈠紩锛屽�傛灉鎵句笉鍒帮紝杩斿�?锛?褰撴煡鎵剧殑瀛愪覆瀛樺湪浜庡瓧绗︿覆涓�锛氳繑鍥炶�ュ瓙涓插湪瀛楃�︿覆涓�銆愮��涓�娆°�戝嚭鐜扮殑绱㈠紩銆傚綋鏌ユ壘鐨勫瓙涓蹭笉鍦ㄥ瓧绗︿覆涓�锛氳繑鍥�0銆?/p>

鎿嶄綔濡備笅锛?/p>

7锛?code>trim(str)锛氬幓鎺夊瓧绗︿覆鍓嶅悗鐨勭┖鏍硷紱 璇ュ嚱鏁板彧鑳藉幓鎺夊瓧绗︿覆鍓嶅悗鐨勭┖鏍硷紝涓嶈兘鍘绘帀瀛楃�︿覆涓�闂寸殑绌烘牸銆?/p>

鎿嶄綔濡備笅锛?/p>

8锛?code>lpad(str,len,濉�鍏呭瓧绗�)锛氱敤鎸囧畾鐨勫瓧绗︼紝瀹炵幇瀵瑰瓧绗︿覆宸﹀～鍏呮寚瀹氶暱搴︼紱

鎿嶄綔濡備笅锛?/p>

9锛?code>rpad(str,len,濉�鍏呭瓧绗�)锛氱敤鎸囧畾鐨勫瓧绗︼紝瀹炵幇瀵瑰瓧绗︿覆鍙冲～鍏呮寚瀹氶暱搴︼紱

鎿嶄綔濡備笅锛?/p>

10锛?replace(str,瀛愪覆,鍙︿竴涓�瀛楃�︿覆)锛氬皢瀛楃�︿覆str涓�鐨勫瓧涓诧紝鏇挎崲涓哄彟涓�涓�瀛楃�︿覆锛?/p>

鎿嶄綔濡備笅锛?/p>

4.鏁板�﹀嚱鏁�

1锛?code>round(x,[淇濈暀鐨勪綅鏁癩) 锛氬洓鑸嶄簲鍏ワ紱 褰撳�规�ｆ暟杩涜�屽洓鑸嶄簲鍏ワ細鎸夌収姝ｅ父鐨勮�＄畻鏂瑰紡锛屽洓鑸嶄簲鍏ュ嵆鍙�銆傚綋瀵硅礋鏁拌繘琛屽洓鑸嶄簲鍏ワ細鍏堟妸绗﹀彿涓㈠埌涓�杈癸紝瀵瑰幓鎺夎礋鍙峰悗鐨勬�ｆ暟杩涜�屽洓鑸嶄簲鍏ワ紝瀹屾垚浠ュ悗锛屽啀鎶婅繖涓�璐熷彿锛岃ˉ涓婂嵆鍙�銆?/p>

鎿嶄綔濡備笅锛?/p>

2锛?code>ceil(x)锛氬悜涓婂彇鏁达紝杩斿洖>=璇ュ弬鏁扮殑鏈�灏忔暣鏁般�?澶╄姳鏉垮嚱鏁? 澶╄姳鏉垮嚱鏁帮紝鍦╡xcel锛宲ython涓�鍧囧瓨鍦ㄨ繖涓�鍑芥暟銆備綘灏辨兂璞′竴涓嬩綘瀹剁殑澶╄姳鏉匡紝鎶婅繖涓�鏁板瓧涓㈠埌澶╄姳鏉夸笂锛屾眰鐨勬槸澶т簬绛変簬杩欎釜鏁板瓧鐨勬渶灏忔暣鏁般�?/p>

鎿嶄綔濡備笅锛?/p>

3锛?code>floor(x)锛氬悜涓嬪彇鏁达紝杩斿洖<=璇ュ弬鏁扮殑鏈�澶ф暣鏁般�傦紙鍦版澘鍑芥暟锛?鍦版澘鍑芥暟锛氬湪excel锛宲ython涓�鍧囧瓨鍦ㄨ繖涓�鍑芥暟銆備綘灏辨兂璞′竴涓嬩綘瀹剁殑鍦版澘锛屾妸杩欎釜鏁板瓧涓㈠埌鍦版澘涓婏紝姹傜殑鏄�灏忎簬绛変簬杩欎釜鏁板瓧鐨勬渶澶ф暣鏁般�?/p>

鎿嶄綔濡備笅锛?/p>

4锛?code>truncate(x,D) 锛氭埅鏂�锛� truncate鍑芥暟锛屼篃鏄�鍦╡xcel锛宲ython涓�鍧囧瓨鍦�锛屽惈涔夊熀鏈�閮芥槸涓�鑷寸殑銆備絾鏄�杩欎釜鍑芥暟鐞嗚В璧锋潵鏈夌偣闅撅紝鎴戣繖閲屽甫鐫�澶у�跺ソ濂藉�︿範涓�涓嬨�?/p>

鐞嗚В濡備笅锛?/p>

"鍙傝�冧笅闈㈢殑绀轰緥鍥撅紝浣撲細濡備笅鏂囧瓧"
1锛塂鏄�姝ｆ暟锛屾搷浣滅殑鏄�灏忔暟鐐瑰彸渚х殑灏忔暟閮ㄥ垎銆?nbsp;
      D=1锛岀洿鎺ヤ粠绗?涓�浣嶇疆澶勶紝鐮嶆帀鍚庨潰鐨勯儴鍒嗐�?br />       D=2锛岀洿鎺ヤ粠绗?涓�浣嶇疆澶勶紝鐮嶆帀鍚庨潰鐨勯儴鍒嗐�?br />  ......
  

2锛塂鏄?锛岀洿鎺ュ幓鎺夊皬鏁伴儴鍒嗐�?/p>

3锛塂鏄�璐熸暟锛屾搷浣滅殑鏄�灏忔暟鐐瑰乏渚х殑鏁存暟閮ㄥ垎銆?br />    D=-1锛岀洿鎺ヤ粠-1浣嶇疆澶勶紝鍏堢爫鎺夊悗闈㈢殑灏忔暟閮ㄥ垎锛?br />    骞朵笖"浠庡綋鍓嶄綅缃�璧�(鍖呮嫭褰撳墠浣嶇疆)锛屽悗闈㈡暣鏁伴儴鍒嗘浛鎹�涓�0"銆?br />    D=-2锛岀洿鎺ヤ粠-2浣嶇疆澶勶紝鍏堢爫鎺夊悗闈㈢殑灏忔暟閮ㄥ垎锛?br />    骞朵笖"浠庡綋鍓嶄綅缃�璧�(鍖呮嫭褰撳墠浣嶇疆)锛屽悗闈㈡暣鏁伴儴鍒嗘浛鎹�涓�0"銆?/p>

绀轰緥鍥撅細

鎿嶄綔濡備笅锛?/p>

5锛?code>mod(琚�闄ゆ�?闄ゆ暟) 锛氬彇浣欙紱 褰撹��闄ゆ暟涓烘�ｆ暟锛岀粨鏋滃氨鏄�姝ｆ暟銆傚綋琚�闄ゆ暟涓鸿礋鏁帮紝缁撴灉灏辨槸璐熸暟銆?/p>

鎿嶄綔濡備笅锛?/p>

5.鏃ユ湡鏃堕棿鍑芥暟

鏃ユ湡鐨勫惈涔夛細鎸囩殑鏄�鎴戜滑甯歌�寸殑骞淬�佹湀銆佹棩銆?/p>

鏃堕棿鐨勫惈涔夛細鎸囩殑鏄�鎴戜滑甯歌�寸殑鏃躲�佸垎銆佺�掋�?/p>

鍦ㄨ�茶堪涓嬮潰鍑芥暟涔嬪墠锛屾垜浠�鍏堣ˉ鍏呬竴涓�鐭ヨ瘑锛屼笉鍚屾椂闂存牸寮忕�﹁〃绀轰粈涔堝惈涔夊憿锛?/p>

1锛?code>now() 锛氳繑鍥炵郴缁熷綋鍓嶇殑鏃ユ湡鍜屾椂闂达紱

鎿嶄綔濡備笅锛?/p>

2锛?code>curdate() 锛氬彧杩斿洖绯荤粺褰撳墠鐨勬棩鏈燂紝涓嶅寘鍚�鏃堕棿锛�

鎿嶄綔濡備笅锛?/p>

3锛?code>curtime() 锛氬彧杩斿洖绯荤粺褰撳墠鐨勬椂闂达紝涓嶅寘鍚�鏃ユ湡锛�

鎿嶄綔濡備笅锛?/p>

4锛夎幏鍙栨棩鏈熷拰鏃堕棿涓�骞淬�佹湀銆佹棩銆佹椂銆佸垎銆佺�掞�?nbsp;

 鑾峰彇骞翠唤锛歽ear()锛?/p>

鑾峰彇鏈堜唤锛歮onth()锛?/p>

鑾峰彇鏃ワ細day()锛?/p>

鑾峰彇灏忔椂锛歨our()锛?/p>

鑾峰彇鍒嗛挓锛歮inute()锛?/p>

鑾峰彇绉掓暟锛歴econd()锛?/p>

5锛?code>weekofyear() 锛氳幏鍙栧綋鍓嶆椂鍒绘墍灞炵殑鍛ㄦ暟锛?/p>

鎿嶄綔濡備笅锛?/p>

6锛?code>quarter() 锛氳幏鍙栧綋鍓嶆椂鍒绘墍灞炵殑瀛ｅ害锛?/p>

鎿嶄綔濡備笅锛?/p>

7锛?code>str_to_date() 锛氬皢鏃ユ湡鏍煎紡鐨勫瓧绗︿覆锛岃浆鎹㈡垚鎸囧畾鏍煎紡鐨勬棩鏈燂紱

鎿嶄綔濡備笅锛?/p>

8锛?code>date_format() 锛氬皢鏃ユ湡杞�鎹㈡垚鏃ユ湡瀛楃�︿覆锛?%Y-%m-%d杩斿洖鐨勬湀浠芥槸01,02鈥﹁繖鏍风殑鏍煎紡銆?code>%Y-%c-%d杩斿洖鐨勬湀浠芥槸1,2鈥﹁繖鏍风殑鏍煎紡銆?/p>

鎿嶄綔濡備笅锛?/p>

9锛?code>date_add() + interval 锛氬悜鍓嶃�佸悜鍚庡亸绉绘棩鏈熷拰鏃堕棿锛?/p>

鎿嶄綔濡備笅锛?/p>

10锛?code>last_day() 锛氭彁鍙栨煇涓�鏈堟渶鍚庝竴澶╃殑鏃ユ湡锛?/p>

鎿嶄綔濡備笅锛?/p>

11锛?code>datediff(end_date,start_date) 锛氳�＄畻涓や釜鏃堕棿鐩稿樊鐨勫ぉ鏁帮�?/p>

鎿嶄綔濡備笅锛?/p>

12锛?code>timestampdiff(unit,start_date,end_date) 锛氳�＄畻涓や釜鏃堕棿杩斿洖鐨勫�?鏈?澶╂暟锛?/p>

unit鍙傛暟鏄�纭�瀹?start_date,end_date)缁撴灉鐨勫崟浣嶏紝琛ㄧず涓烘暣鏁帮紝浠ヤ笅鏄�鏈夋晥鍗曚綅锛�

year锛氬勾浠?/p>

month锛氭湀浠?/p>

day锛氬ぉ

hour锛氬皬鏃?/p>

minute 鍒嗛挓

second锛氱��

microsecond锛氬井绉?/p>

week锛氬懆鏁?/p>

quarter锛氬�ｅ�?/p>

YEAR锛氬勾浠?/p>

鎿嶄綔濡備笅锛?/p>

6.鍏跺畠甯哥敤绯荤粺鍑芥暟

7.娴佺▼鎺у埗鍑芥暟

1锛?code>if鍑芥暟锛氬疄鐜癷f-else鐨勬晥鏋滐紱

2锛?code>ifnull鍑芥暟锛氬垽鏂�鍊兼槸鍚︿负null锛屾槸null鐢ㄦ寚瀹氬�煎～鍏咃紱

3锛?code>case鈥�when鍑芥暟鐨勪笁绉嶇敤娉曪紱

case 鈥?when鍏辨湁涓夌�嶇敤娉曪紝鎴戠浉淇¤嚜宸辩殑鎬荤粨浼氱浉褰撳叏闈�銆傚笇鏈涘ぇ瀹朵竴瀹氳�佸ソ濂界爺绌惰繖鍑犵�嶇敤娉曪紝閮芥槸寰堟湁鐢ㄧ殑銆?/p>

绛夊�煎垽鏂�锛氱被浼间簬java涓璼witch case鐨勬晥鏋滐紱

鍖洪棿鍒ゆ柇锛氱被浼间簬python涓璱f-elif-else鐨勬晥鏋滐紱

case 鈥?when鍜岃仛鍚堝嚱鏁拌仈鐢�锛�

鈶?case 鈥?when鐢ㄤ綔绛夊�煎垽鏂�鐨勮��娉曟牸寮忥�?/p>

case 瑕佸垽鏂�鐨勫瓧娈垫垨琛ㄨ揪寮�
when 甯搁噺1 then 瑕佹樉绀虹殑鍊?鎴栬��鍙?
when 甯搁噺2 then 瑕佹樉绀虹殑鍊?鎴栬��鍙?
...
else 瑕佹樉绀虹殑鍊糿鎴栬��鍙�n
end

鎿嶄綔濡備笅锛?/p>

鈶?case 鈥?when鐢ㄤ綔鍖洪棿鍒ゆ柇鐨勮��娉曟牸寮忥紱

case
when 鏉′欢1 then 瑕佹樉绀虹殑鍊?鎴栬��鍙?
when 鏉′欢2 then 瑕佹樉绀虹殑鍊?鎴栬��鍙?
...
else 瑕佹樉绀虹殑鍊糿鎴栬��鍙�n
end

鎿嶄綔濡備笅锛?/p>

鈶?case鈥�when涓庤仛鍚堝嚱鏁扮殑鑱旂敤

鍒╃敤涓婅堪鍘熷�嬭〃锛屽畬鎴愬�備笅闂�棰橈�?/p>

-- 18銆佹煡璇㈠悇绉戞垚缁╂渶楂樺垎銆佹渶浣庡垎鍜屽钩鍧囧垎锛屼互濡備笅褰㈠紡鏄剧ず锛?br /> -- 璇剧▼ID锛岃�剧▼name锛屾渶楂樺垎锛屾渶浣庡垎锛屽钩鍧囧垎锛屽強鏍肩巼锛屼腑绛夌巼锛屼紭鑹�鐜囷紝浼樼��鐜?br /> -- 鍙婃牸涓猴細>=60锛屼腑绛変负锛?0-80锛屼紭鑹�涓猴�?0-90锛屼紭绉�涓猴細>=90

鎿嶄綔濡備笅锛?/p>

select sc.c,cname,
max(score) 鏈�楂樺垎,min(score) 鏈�浣庡垎,avg(score) 骞冲潎鍒?
sum(case when score>60 then 1 else 0 end)/count(*) 鍙婃牸鐜?
sum(case when score>=70 and score<80 then 1 else 0 end)/count(*) 涓�绛夌�?
sum(case when score>=80 and score<90 then 1 else 0 end)/count(*) 浼樿壇鐜?
sum(case when score>=90 then 1 else 0 end)/count(*) 浼樼��鐜?
from sc left join course 
on sc.c = course.c
group by sc.c;

缁撴灉濡備笅锛?/p>

8.鑱氬悎鍑芥暟

1锛夎仛鍚堝嚱鏁扮殑鍔熻兘鍜屽垎绫伙紱

鈶?鑱氬悎鍑芥暟鐨勫姛鑳斤紱

鐢ㄤ綔缁熻�′娇鐢�锛屽張绉颁负鑱氬悎鍑芥暟鎴栫粺璁″嚱鏁版垨缁勫嚱鏁般�?/p>

鈶?鑱氬悎鍑芥暟鐨勫垎绫伙紱 sum 姹傚拰avg 骞冲潎鍊糾ax 鏈�澶у�糾in 鏈�灏忓�糲ount 璁＄畻涓�鏁�

2锛夎仛鍚堝嚱鏁扮殑绠�鍗曚娇鐢?/h3>

3锛変簲涓�鑱氬悎鍑芥暟涓�浼犲叆鐨勫弬鏁帮紝鎵�鏀�鎸佺殑鏁版嵁绫诲瀷鏈夊摢浜涳�?/h3>

mysql涓嶆槸寮虹被鍨嬬殑缂栫▼璇�瑷�銆備篃灏辨槸璇达紝鏈変簺璇�鍙ユ墽琛岀粨鏋滃彲鑳戒笉鎶ラ敊锛屼絾鏄�鎵ц�岀粨鏋滄棤瀹為檯鎰忎箟锛屽洜姝わ紝鎴戜滑涔熻�や负鏄�涓嶆�ｇ‘鐨勩�?/p>

鈶?娴嬭瘯鏁版嵁锛?/p>

"寤鸿〃璇�鍙�"
create table test(
	id int primary key auto_increment,
	name varchar(20) not null,
	sal int,
	birth date)charset=utf8;

"鎻掑叆鏁版嵁"
 insert into test(name,sal,birth) values 
 ("Zoo",6500,'1993.3.20'),
 ("Hobby",4000,'1997.6.10'),
 ("Aline",5500,'2000.5.1'),
 ("Bob",10000,'2008.10.1');

鈶?sum()鍑芥暟鍜宎vg()鍑芥暟锛氫紶鍏ユ暣鍨?灏忔暟绫诲瀷鎵嶆湁鎰忎箟锛?/p>

缁撹�哄�備笅锛?/p>

sum()鍑芥暟鍜宎vg()鍑芥暟瀵逛簬瀛楃�︿覆绫诲瀷銆佹棩鏈?鏃堕棿绫诲瀷鐨勮�＄畻閮芥病鏈夊お澶ф剰涔夈�傚洜姝わ紝sum()鍑芥暟鍜宎vg()鍑芥暟锛屾垜浠�鍙�鐢ㄦ潵瀵瑰皬鏁扮被鍨嬪拰鏁村瀷杩涜�屾眰鍜屻�?/p>

鈶?max()鍑芥暟鍜宮in()鍑芥暟锛氫紶鍏ユ暣鍨?灏忔暟绫诲瀷銆佹棩鏈?鏃堕棿绫诲瀷鎰忎箟杈冨ぇ锛?/p>

缁撹�哄�備笅锛?/p>

max()鍜宮in()涓�浼犲叆鐨勬�?鏁村瀷/灏忔暟绫诲瀷"锛岃�＄畻鐨勬槸鏁板�肩殑鏈�澶у�?/code>鍜?code>鏈�灏忓�?/code>銆俶ax()鍜宮in()涓�浼犲叆鐨勬�?鏃ユ湡绫诲瀷"锛宮ax()璁＄畻鐨勬渶澶у�兼槸绂绘垜浠?code>鏈�杩戠殑閭ｄ釜鏃ユ湡锛宮in()璁＄畻鐨勬渶灏忓�兼槸绂绘垜浠?code>鏈�杩滅殑閭ｄ釜鏃ユ湡锛岃繖涓�鍙�浠ヨ�颁竴涓嬨�俶ax()鍜宮in()涓�浼犲叆鐨勬�?code>瀛楃�︿覆绫诲�?/code>锛宮ax()璁＄畻鐨勬渶澶у�兼槸鎸夌収鑻辨枃瀛楁瘝椤哄簭鏄剧ず鐨勶紝min()璁＄畻鐨勬渶灏忓�间篃鏄�鎸夌収鑻辨枃瀛楁瘝椤哄簭鏄剧ず鐨勶紝鎰忎箟涓嶅お澶с�?/p>

 鈶?count()鍑芥暟锛氬彲浠ヤ紶鍏ヤ换浣曟暟鎹�绫诲瀷锛屼絾鏄�纰板埌null瑕佹敞鎰忥紱

缁撹�哄�備笅锛?/p>

    count()鍑芥暟鍙�浠ヤ紶鍏ヤ换浣曟暟鎹�绫诲瀷锛岃〃绀哄�硅�岃�℃暟銆?br />     "浣嗘槸涓嬮潰鐨勭煡璇嗙偣闇�瑕佺壒鍒�娉ㄦ剰鐨�"
    棣栧厛鐪嬬湅count(sal) 锛?code>count(birth)杩欎袱鍙ヨ〃绀虹殑鏄�浠�涔堟剰鎬濓紵杩欎袱鍙ュ垎鍒�琛ㄧず鐨勬�?br /> 瀵箂al鍒楀瓧娈点�乥irth鍒楀瓧娈电殑琛屾暟锛岃繘琛岀粺璁°�傜敱浜庡叾涓�鏈変竴鏉¤�板綍鏄痭ull鍊硷紝鍥犳�や娇鐢╟ount()
鍑芥暟璁℃暟鐨勬椂鍊欙紝浼氬拷鐣ユ帀null琛屻�?br />     鍏舵�★紝瀵逛簬count(*)琛ㄧず鐨勬槸缁熻�°�愭暣涓�琛ㄣ�戞湁澶氬皯琛岋紝杩欎釜鑲�瀹氭槸瀵瑰師濮嬫暟鎹�鐨勮�屾暟鐨勬�ｇ‘
缁熻�★紝鍙�瑕佹暣寮犺〃鏌愪竴琛屾湁涓�涓�鍒楀瓧娈电殑鍊间笉鏄痭ull锛宑ount(*)灏变細璁や负璇ヨ�屼�?琛屻�傚綋鐒惰�佹槸涓�
鏁磋�岄兘鏄痭ull鍊硷紝浣犱篃娌″繀瑕佹彃鍏ヨ繖鏉¤�板綍銆?/p>

鎬荤粨:

   褰撴煇涓�瀛楁�靛垪涓�娌℃湁null鍊硷紝鍒?count(鍒楀瓧娈?=count(*)銆?
   褰撴煇涓�瀛楁�靛垪涓�鏈塶ull鍊硷紝鍒?count(鍒楀瓧娈?<count(*)銆?
   鍥犳�わ紝鍋囧�備綘鎯崇粺璁＄殑鏄�鏁村紶琛ㄧ殑琛屾暟锛岃�风敤count(*)銆?/p>

鍏跺疄鎵�鏈夌殑鍒嗙粍鍑芥暟閮藉拷鐣�null鍊肩殑锛屼絾涓婇潰閭ｄ釜count()鍑芥暟纰板埌null鍊艰�佺壒鍒�娉ㄦ剰銆?/p>

鈶?count()鍑芥暟纰板埌null鍊奸渶瑕佺壒鍒�娉ㄦ剰锛�

缁撹�哄�備笅锛?/p>

瀵逛簬avg(sal)姹傚钩鍧囧�兼潵璇达紝(6500+4000+5500+10000)/4=6500銆?/p>

瀵逛簬鍚庨潰杩欎釜sum()/count(*)姹傚钩鍧囧�兼潵璇达紝(6500+4000+5500+10000)/5=5200銆?/p>

濂藉ソ浣撲細涓婅堪渚嬪瓙锛屾湁鏃跺�欐煇浜烘垚缁╄櫧鐒惰�板綍鐨勬槸null锛屼絾鏄�浣犱粛鐒舵�?涓�浜哄瓨鍦�锛屾墍浠ヤ綘瑕佽�冭檻涓�涓嬫�庝箞浣跨敤鍚堥�傜殑鍑芥暟锛岃揪鍒颁綘鎯宠�佺殑缁撴灉銆?/p>

鈶?count(1),count(0)琛ㄧず鐨勬槸鍟ユ剰鎬濆憿锛?/p>

    鏃犺�烘槸sum(1),sum(0),count(1),count(0),avg(1),avg(0)锛屽師鐞嗛兘鏄�涓�鏍风殑锛岀浉褰撲簬鍦ㄥ師琛ㄤ腑鏂板�炰竴鍒椼�?/p>

    鍏舵�★紝鎴戜滑鐭ラ亾where鍚庨潰鎺ョ殑鏄�銆愰�昏緫鍊笺�戯紝褰撲娇鐢╳here 1鍜寃here 0鍘熺悊涔熻繕鏄�涓�鏍凤紝涔熺浉褰撲簬鍦ㄥ師琛ㄤ腑鏂板�炰竴鍒椼�?/p>

    鎴戜滑鍙�闇�瑕佽�颁綇鍦╩ysql涓�锛�"闈?鍗充负true锛?涓篺alse"銆備篃灏辨槸璇达紝涓嬮潰鐨勬墍鏈夋槸1鐨勫湴鏂癸紝浣犲彲浠ユ崲鎴愪换浣曢潪0鏁板瓧锛岄兘鏄�鍙�浠ョ殑銆?/p>

鍘熺悊鍥惧�備笅锛�

娴嬭瘯涓�涓嬶細

鈶?count(*)璁℃暟鐨勬晥鐜囬棶棰橈紱

MYISAM瀛樺偍寮曟搸涓嬶紝count(*)鐨勬晥鐜囬珮銆侷NNODB瀛樺偍寮曟搸涓嬶紝count(*)鍜宑ount(1)鐨勬晥鐜囧樊涓嶅�氾紝姣攃ount(瀛楁��)鏁堢巼瑕侀珮涓�浜涖�傜患涓婃墍杩帮細浼樺厛浣跨敤count(*)銆?/p>

4锛夎仛鍚堝嚱鏁板拰group by鐨勪娇鐢ㄢ�滄渶閲嶈�佲�濓紱

鍏充簬杩欎釜鐭ヨ瘑鐐癸紝鎴戜滑灏嗕細鍦ㄥ悗闈㈢殑鐭ヨ瘑鐐逛腑杩涜�岃�茶堪銆傚湪杩欓噷鎴戜滑鍙�闇�瑕佽�颁綇涓�鍙ヨ瘽锛氬綋SQL璇�鍙ヤ腑浣跨敤浜唃roup by鍒嗙粍鍑芥暟鍚庯紝select鍚庨潰鐨勫瓧娈靛繀椤绘槸group by鍚庨潰鐨勫瓧娈?+ 鑱氬悎鍑芥暟鐨勪娇鐢ㄣ�?/p>

浠ヤ笂灏辨槸闈炲父瀹炵敤鐨凪ySQL鍑芥暟鍏ㄩ潰鎬荤粨璇﹁В绀轰緥鍒嗘瀽鐨勮�︾粏鍐呭�癸紝鏇村�氬叧浜嶮ySQL鍑芥暟鐨勮祫鏂欒�峰叧娉ㄨ剼鏈�涔嬪�跺叾瀹冪浉鍏虫枃绔狅�?/p>