SQL涓储寮曠殑鍘熺悊一
SQL涓储寮曠殑鍘熺悊1
锛堜竴锛夋繁鍏ユ祬鍑虹悊瑙ｇ储寮曠粨鏋?nbsp;
瀹為檯涓婏紝鎮ㄥ彲浠ユ妸绱㈠紩鐞嗚В涓轰竴绉嶇壒娈婄殑鐩綍銆傚井杞殑SQL SERVER鎻愪緵浜嗕袱绉嶇储寮曪細鑱氶泦绱㈠紩锛坈lustered index锛屼篃绉拌仛绫荤储寮曘€佺皣闆嗙储寮曪級鍜岄潪鑱氶泦绱㈠紩锛坣onclustered index锛屼篃绉伴潪鑱氱被绱㈠紩銆侀潪绨囬泦绱㈠紩锛夈€備笅闈紝鎴戜滑涓句緥鏉ヨ鏄庝竴涓嬭仛闆嗙储寮曞拰闈炶仛闆嗙储寮曠殑鍖哄埆锛?nbsp;
鍏跺疄锛屾垜浠殑姹夎瀛楀吀鐨勬鏂囨湰韬氨鏄竴涓仛闆嗙储寮曘€傛瘮濡傦紝鎴戜滑瑕佹煡鈥滃畨鈥濆瓧锛屽氨浼氬緢鑷劧鍦扮炕寮€瀛楀吀鐨勫墠鍑犻〉锛屽洜涓衡€滃畨鈥濈殑鎷奸煶鏄€渁n鈥濓紝鑰屾寜鐓ф嫾闊虫帓搴忔眽瀛楃殑瀛楀吀鏄互鑻辨枃瀛楁瘝鈥渁鈥濆紑澶村苟浠モ€渮鈥濈粨灏剧殑锛岄偅涔堚€滃畨鈥濆瓧灏辫嚜鐒跺湴鎺掑湪瀛楀吀鐨勫墠閮ㄣ€傚鏋滄偍缈诲畬浜嗘墍鏈変互鈥渁鈥濆紑澶寸殑閮ㄥ垎浠嶇劧鎵句笉鍒拌繖涓瓧锛岄偅涔堝氨璇存槑鎮ㄧ殑瀛楀吀涓病鏈夎繖涓瓧锛涘悓鏍风殑锛屽鏋滄煡鈥滃紶鈥濆瓧锛岄偅鎮ㄤ篃浼氬皢鎮ㄧ殑瀛楀吀缈诲埌鏈€鍚庨儴鍒嗭紝鍥犱负鈥滃紶鈥濈殑鎷奸煶鏄€渮hang鈥濄€備篃灏辨槸璇达紝瀛楀吀鐨勬鏂囬儴鍒嗘湰韬氨鏄竴涓洰褰曪紝鎮ㄤ笉闇€瑕佸啀鍘绘煡鍏朵粬鐩綍鏉ユ壘鍒版偍闇€瑕佹壘鐨勫唴瀹广€?nbsp;
鎴戜滑鎶婅繖绉嶆鏂囧唴瀹规湰韬氨鏄竴绉嶆寜鐓т竴瀹氳鍒欐帓鍒楃殑鐩綍绉颁负鈥滆仛闆嗙储寮曗€濄€?nbsp;
濡傛灉鎮ㄨ璇嗘煇涓瓧锛屾偍鍙互蹇€熷湴浠庤嚜鍔ㄤ腑鏌ュ埌杩欎釜瀛椼€備絾鎮ㄤ篃鍙兘浼氶亣鍒版偍涓嶈璇嗙殑瀛楋紝涓嶇煡閬撳畠鐨勫彂闊筹紝杩欐椂鍊欙紝鎮ㄥ氨涓嶈兘鎸夌収鍒氭墠鐨勬柟娉曟壘鍒版偍瑕佹煡鐨勫瓧锛岃€岄渶瑕佸幓鏍规嵁鈥滃亸鏃侀儴棣栤€濇煡鍒版偍瑕佹壘鐨勫瓧锛岀劧鍚庢牴鎹繖涓瓧鍚庣殑椤电爜鐩存帴缈诲埌鏌愰〉鏉ユ壘鍒版偍瑕佹壘鐨勫瓧銆備絾鎮ㄧ粨鍚堚€滈儴棣栫洰褰曗€濆拰鈥滄瀛楄〃鈥濊€屾煡鍒扮殑瀛楃殑鎺掑簭骞朵笉鏄湡姝ｇ殑姝ｆ枃鐨勬帓搴忔柟娉曪紝姣斿鎮ㄦ煡鈥滃紶鈥濆瓧锛屾垜浠彲浠ョ湅鍒板湪鏌ラ儴棣栦箣鍚庣殑妫€瀛楄〃涓€滃紶鈥濈殑椤电爜鏄?72椤碉紝妫€瀛楄〃涓€滃紶鈥濈殑涓婇潰鏄€滈┌鈥濆瓧锛屼絾椤电爜鍗存槸63椤碉紝鈥滃紶鈥濈殑涓嬮潰鏄€滃缉鈥濆瓧锛岄〉闈㈡槸390椤点€傚緢鏄剧劧锛岃繖浜涘瓧骞朵笉鏄湡姝ｇ殑鍒嗗埆浣嶄簬鈥滃紶鈥濆瓧鐨勪笂涓嬫柟锛岀幇鍦ㄦ偍鐪嬪埌鐨勮繛缁殑鈥滈┌銆佸紶銆佸缉鈥濅笁瀛楀疄闄呬笂灏辨槸浠栦滑鍦ㄩ潪鑱氶泦绱㈠紩涓殑鎺掑簭锛屾槸瀛楀吀姝ｆ枃涓殑瀛楀湪闈炶仛闆嗙储寮曚腑鐨勬槧灏勩€傛垜浠彲浠ラ€氳繃杩欑鏂瑰紡鏉ユ壘鍒版偍鎵€闇€瑕佺殑瀛楋紝浣嗗畠闇€瑕佷袱涓繃绋嬶紝鍏堟壘鍒扮洰褰曚腑鐨勭粨鏋滐紝鐒跺悗鍐嶇炕鍒版偍鎵€闇€瑕佺殑椤电爜銆?nbsp;
鎴戜滑鎶婅繖绉嶇洰褰曠函绮规槸鐩綍锛屾鏂囩函绮规槸姝ｆ枃鐨勬帓搴忔柟寮忕О涓衡€滈潪鑱氶泦绱㈠紩鈥濄€?nbsp;
閫氳繃浠ヤ笂渚嬪瓙锛屾垜浠彲浠ョ悊瑙ｅ埌浠€涔堟槸鈥滆仛闆嗙储寮曗€濆拰鈥滈潪鑱氶泦绱㈠紩鈥濄€?nbsp;
杩涗竴姝ュ紩鐢充竴涓嬶紝鎴戜滑鍙互寰堝鏄撶殑鐞嗚В锛氭瘡涓〃鍙兘鏈変竴涓仛闆嗙储寮曪紝鍥犱负鐩綍鍙兘鎸夌収涓€绉嶆柟娉曡繘琛屾帓搴忋€?nbsp;
锛堜簩锛変綍鏃朵娇鐢ㄨ仛闆嗙储寮曟垨闈炶仛闆嗙储寮?nbsp;
涓嬮潰鐨勮〃鎬荤粨浜嗕綍鏃朵娇鐢ㄨ仛闆嗙储寮曟垨闈炶仛闆嗙储寮曪紙寰堥噸瑕侊級銆?nbsp;
浜嬪疄涓婏紝鎴戜滑鍙互閫氳繃鍓嶉潰鑱氶泦绱㈠紩鍜岄潪鑱氶泦绱㈠紩鐨勫畾涔夌殑渚嬪瓙鏉ョ悊瑙ｄ笂琛ㄣ€傚锛氳繑鍥炴煇鑼冨洿鍐呯殑鏁版嵁涓€椤广€傛瘮濡傛偍鐨勬煇涓〃鏈変竴涓椂闂村垪锛屾伆濂芥偍鎶婅仛鍚堢储寮曞缓绔嬪湪浜嗚鍒楋紝杩欐椂鎮ㄦ煡璇?004骞?鏈?鏃ヨ嚦2004骞?0鏈?鏃ヤ箣闂寸殑鍏ㄩ儴鏁版嵁鏃讹紝杩欎釜閫熷害灏卞皢鏄緢蹇殑锛屽洜涓烘偍鐨勮繖鏈瓧鍏告鏂囨槸鎸夋棩鏈熻繘琛屾帓搴忕殑锛岃仛绫荤储寮曞彧闇€瑕佹壘鍒拌妫€绱㈢殑鎵€鏈夋暟鎹腑鐨勫紑澶村拰缁撳熬鏁版嵁鍗冲彲锛涜€屼笉鍍忛潪鑱氶泦绱㈠紩锛屽繀椤诲厛鏌ュ埌鐩綍涓煡鍒版瘡涓€椤规暟鎹搴旂殑椤电爜锛岀劧鍚庡啀鏍规嵁椤电爜鏌ュ埌鍏蜂綋鍐呭銆?nbsp;
锛堜笁锛夌粨鍚堝疄闄咃紝璋堢储寮曚娇鐢ㄧ殑璇尯
鐞嗚鐨勭洰鐨勬槸搴旂敤銆傝櫧鐒舵垜浠垰鎵嶅垪鍑轰簡浣曟椂搴斾娇鐢ㄨ仛闆嗙储寮曟垨闈炶仛闆嗙储寮曪紝浣嗗湪瀹炶返涓互涓婅鍒欏嵈寰堝鏄撹蹇借鎴栦笉鑳芥牴鎹疄闄呮儏鍐佃繘琛岀患鍚堝垎鏋愩€備笅闈㈡垜浠皢鏍规嵁鍦ㄥ疄璺典腑閬囧埌鐨勫疄闄呴棶棰樻潵璋堜竴涓嬬储寮曚娇鐢ㄧ殑璇尯锛屼互渚夸簬澶у鎺屾彙绱㈠紩寤虹珛鐨勬柟娉曘€?nbsp;
1銆佷富閿氨鏄仛闆嗙储寮?nbsp;
杩欑鎯虫硶绗旇€呰涓烘槸鏋佺閿欒鐨勶紝鏄鑱氶泦绱㈠紩鐨勪竴绉嶆氮璐广€傝櫧鐒禨QL SERVER榛樿鏄湪涓婚敭涓婂缓绔嬭仛闆嗙储寮曠殑銆?nbsp;
閫氬父锛屾垜浠細鍦ㄦ瘡涓〃涓兘寤虹珛涓€涓狪D鍒楋紝浠ュ尯鍒嗘瘡鏉℃暟鎹紝骞朵笖杩欎釜ID鍒楁槸鑷姩澧炲ぇ鐨勶紝姝ラ暱涓€鑸负1銆傛垜浠殑杩欎釜鍔炲叕鑷姩鍖栫殑瀹炰緥涓殑鍒桮id灏辨槸濡傛銆傛鏃讹紝濡傛灉鎴戜滑灏嗚繖涓垪璁句负涓婚敭锛孲QL SERVER浼氬皢姝ゅ垪榛樿涓鸿仛闆嗙储寮曘€傝繖鏍峰仛鏈夊ソ澶勶紝灏辨槸鍙互璁╂偍鐨勬暟鎹湪鏁版嵁搴撲腑鎸夌収ID杩涜鐗╃悊鎺掑簭锛屼絾绗旇€呰涓鸿繖鏍峰仛鎰忎箟涓嶅ぇ銆?nbsp;
鏄捐€屾槗瑙侊紝鑱氶泦绱㈠紩鐨勪紭鍔挎槸寰堟槑鏄剧殑锛岃€屾瘡涓〃涓彧鑳芥湁涓€涓仛闆嗙储寮曠殑瑙勫垯锛岃繖浣垮緱鑱氶泦绱㈠紩鍙樺緱鏇村姞鐝嶈吹銆?nbsp;
浠庢垜浠墠闈㈣皥鍒扮殑鑱氶泦绱㈠紩鐨勫畾涔夋垜浠彲浠ョ湅鍑猴紝浣跨敤鑱氶泦绱㈠紩鐨勬渶澶уソ澶勫氨鏄兘澶熸牴鎹煡璇㈣姹傦紝杩呴€熺缉灏忔煡璇㈣寖鍥达紝閬垮厤鍏ㄨ〃鎵弿銆傚湪瀹為檯搴旂敤涓紝鍥犱负ID鍙锋槸鑷姩鐢熸垚鐨勶紝鎴戜滑骞朵笉鐭ラ亾姣忔潯璁板綍鐨処D鍙凤紝鎵€浠ユ垜浠緢闅惧湪瀹炶返涓敤ID鍙锋潵杩涜鏌ヨ銆傝繖灏变娇璁㊣D鍙疯繖涓富閿綔涓鸿仛闆嗙储寮曟垚涓轰竴绉嶈祫婧愭氮璐广€傚叾娆★紝璁╂瘡涓狪D鍙烽兘涓嶅悓鐨勫瓧娈典綔涓鸿仛闆嗙储寮曚篃涓嶇鍚堚€滃ぇ鏁扮洰鐨勪笉鍚屽€兼儏鍐典笅涓嶅簲寤虹珛鑱氬悎绱㈠紩鈥濊鍒欙紱褰撶劧锛岃繖绉嶆儏鍐靛彧鏄拡瀵圭敤鎴风粡甯镐慨鏀硅褰曞唴瀹癸紝鐗瑰埆鏄储寮曢」鐨勬椂鍊欎細璐熶綔鐢紝浣嗗浜庢煡璇㈤€熷害骞舵病鏈夊奖鍝嶃€?nbsp;
鍦ㄥ姙鍏嚜鍔ㄥ寲绯荤粺涓紝鏃犺鏄郴缁熼椤垫樉绀虹殑闇€瑕佺敤鎴风鏀剁殑鏂囦欢銆佷細璁繕鏄敤鎴疯繘琛屾枃浠舵煡璇㈢瓑浠讳綍鎯呭喌涓嬭繘琛屾暟鎹煡璇㈤兘绂讳笉寮€瀛楁鐨勬槸鈥滄棩鏈熲€濊繕鏈夌敤鎴锋湰韬殑鈥滅敤鎴峰悕鈥濄€?nbsp;
閫氬父锛屽姙鍏嚜鍔ㄥ寲鐨勯椤典細鏄剧ず姣忎釜鐢ㄦ埛灏氭湭绛炬敹鐨勬枃浠舵垨浼氳銆傝櫧鐒舵垜浠殑where璇彞鍙互浠呬粎闄愬埗褰撳墠鐢ㄦ埛灏氭湭绛炬敹鐨勬儏鍐碉紝浣嗗鏋滄偍鐨勭郴缁熷凡寤虹珛浜嗗緢闀挎椂闂达紝骞朵笖鏁版嵁閲忓緢澶э紝閭ｄ箞锛屾瘡娆℃瘡涓敤鎴锋墦寮€棣栭〉鐨勬椂鍊欓兘杩涜涓€娆″叏琛ㄦ壂鎻忥紝杩欐牱鍋氭剰涔夋槸涓嶅ぇ鐨勶紝缁濆ぇ澶氭暟鐨勭敤鎴?涓湀鍓嶇殑鏂囦欢閮藉凡缁忔祻瑙堣繃浜嗭紝杩欐牱鍋氬彧鑳藉緬澧炴暟鎹簱鐨勫紑閿€鑰屽凡銆備簨瀹炰笂锛屾垜浠畬鍏ㄥ彲浠ヨ鐢ㄦ埛鎵撳紑绯荤粺棣栭〉鏃讹紝鏁版嵁搴撲粎浠呮煡璇㈣繖涓敤鎴疯繎3涓湀鏉ユ湭闃呰鐨勬枃浠讹紝閫氳繃鈥滄棩鏈熲€濊繖涓瓧娈垫潵闄愬埗琛ㄦ壂鎻忥紝鎻愰珮鏌ヨ閫熷害銆傚鏋滄偍鐨勫姙鍏嚜鍔ㄥ寲绯荤粺宸茬粡寤虹珛鐨?骞达紝閭ｄ箞鎮ㄧ殑棣栭〉鏄剧ず閫熷害鐞嗚涓婂皢鏄師鏉ラ€熷害8鍊嶏紝鐢氳嚦鏇村揩銆?
鍦ㄨ繖閲屼箣鎵€浠ユ彁鍒扳€滅悊璁轰笂鈥濅笁瀛楋紝鏄洜涓哄鏋滄偍鐨勮仛闆嗙储寮曡繕鏄洸鐩湴寤哄湪ID杩欎釜涓婚敭涓婃椂锛屾偍鐨勬煡璇㈤€熷害鏄病鏈夎繖涔堥珮鐨勶紝鍗充娇鎮ㄥ湪鈥滄棩鏈熲€濊繖涓瓧娈典笂寤虹珛鐨勭储寮曪紙闈炶仛鍚堢储寮曪級銆備笅闈㈡垜浠氨鏉ョ湅涓€涓嬪湪1000涓囨潯鏁版嵁閲忕殑鎯呭喌涓嬪悇绉嶆煡璇㈢殑閫熷害琛ㄧ幇锛?涓湀鍐呯殑鏁版嵁涓?5涓囨潯锛夛細
锛?锛変粎鍦ㄤ富閿笂寤虹珛鑱氶泦绱㈠紩锛屽苟涓斾笉鍒掑垎鏃堕棿娈碉細
Select gid,fariqi,neibuyonghu,title from tgongwen
鐢ㄦ椂锛?28470姣锛堝嵆锛?28绉掞級
锛?锛夊湪涓婚敭涓婂缓绔嬭仛闆嗙储寮曪紝鍦╢ariq涓婂缓绔嬮潪鑱氶泦绱㈠紩锛?nbsp;
select gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi> dateadd(day,-90,getdate())
鐢ㄦ椂锛?3763姣锛?4绉掞級
锛?锛夊皢鑱氬悎绱㈠紩寤虹珛鍦ㄦ棩鏈熷垪锛坒ariqi锛変笂锛?nbsp;
select gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi> dateadd(day,-90,getdate())
鐢ㄦ椂锛?423姣锛?绉掞級
铏界劧姣忔潯璇彞鎻愬彇鍑烘潵鐨勯兘鏄?5涓囨潯鏁版嵁锛屽悇绉嶆儏鍐电殑宸紓鍗存槸宸ㄥぇ鐨勶紝鐗瑰埆鏄皢鑱氶泦绱㈠紩寤虹珛鍦ㄦ棩鏈熷垪鏃剁殑宸紓銆備簨瀹炰笂锛屽鏋滄偍鐨勬暟鎹簱鐪熺殑鏈?000涓囧閲忕殑璇濓紝鎶婁富閿缓绔嬪湪ID鍒椾笂锛屽氨鍍忎互涓婄殑绗?銆?绉嶆儏鍐碉紝鍦ㄧ綉椤典笂鐨勮〃鐜板氨鏄秴鏃讹紝鏍规湰灏辨棤娉曟樉绀恒€傝繖涔熸槸鎴戞憭寮僆D鍒椾綔涓鸿仛闆嗙储寮曠殑涓€涓渶閲嶈鐨勫洜绱犮€?nbsp;
寰楀嚭浠ヤ笂閫熷害鐨勬柟娉曟槸锛氬湪鍚勪釜select璇彞鍓嶅姞锛歞eclare @d datetime
set @d=getdate()
骞跺湪select璇彞鍚庡姞锛?nbsp;
select [璇彞鎵ц鑺辫垂鏃堕棿(姣)]=datediff(ms,@d,getdate())
2銆佸彧瑕佸缓绔嬬储寮曞氨鑳芥樉钁楁彁楂樻煡璇㈤€熷害
浜嬪疄涓婏紝鎴戜滑鍙互鍙戠幇涓婇潰鐨勪緥瀛愪腑锛岀2銆?鏉¤鍙ュ畬鍏ㄧ浉鍚岋紝涓斿缓绔嬬储寮曠殑瀛楁涔熺浉鍚岋紱涓嶅悓鐨勪粎鏄墠鑰呭湪fariqi瀛楁涓婂缓绔嬬殑鏄潪鑱氬悎绱㈠紩锛屽悗鑰呭湪姝ゅ瓧娈典笂寤虹珛鐨勬槸鑱氬悎绱㈠紩锛屼絾鏌ヨ閫熷害鍗存湁鐫€澶╁￥涔嬪埆銆傛墍浠ワ紝骞堕潪鏄湪浠讳綍瀛楁涓婄畝鍗曞湴寤虹珛绱㈠紩灏辫兘鎻愰珮鏌ヨ閫熷害銆?nbsp;
浠庡缓琛ㄧ殑璇彞涓紝鎴戜滑鍙互鐪嬪埌杩欎釜鏈夌潃1000涓囨暟鎹殑琛ㄤ腑fariqi瀛楁鏈?003涓笉鍚岃褰曘€傚湪姝ゅ瓧娈典笂寤虹珛鑱氬悎绱㈠紩鏄啀鍚堥€備笉杩囦簡銆傚湪鐜板疄涓紝鎴戜滑姣忓ぉ閮戒細鍙戝嚑涓枃浠讹紝杩欏嚑涓枃浠剁殑鍙戞枃鏃ユ湡灏辩浉鍚岋紝杩欏畬鍏ㄧ鍚堝缓绔嬭仛闆嗙储寮曡姹傜殑锛氣€滄棦涓嶈兘缁濆ぇ澶氭暟閮界浉鍚岋紝鍙堜笉鑳藉彧鏈夋瀬灏戞暟鐩稿悓鈥濈殑瑙勫垯銆傜敱姝ょ湅鏉ワ紝鎴戜滑寤虹珛鈥滈€傚綋鈥濈殑鑱氬悎绱㈠紩瀵逛簬鎴戜滑鎻愰珮鏌ヨ閫熷害鏄潪甯搁噸瑕佺殑銆?nbsp;
3銆佹妸鎵€鏈夐渶瑕佹彁楂樻煡璇㈤€熷害鐨勫瓧娈甸兘鍔犺繘鑱氶泦绱㈠紩锛屼互鎻愰珮鏌ヨ閫熷害
涓婇潰宸茬粡璋堝埌锛氬湪杩涜鏁版嵁鏌ヨ鏃堕兘绂讳笉寮€瀛楁鐨勬槸鈥滄棩鏈熲€濊繕鏈夌敤鎴锋湰韬殑鈥滅敤鎴峰悕鈥濄€傛棦鐒惰繖涓や釜瀛楁閮芥槸濡傛鐨勯噸瑕侊紝鎴戜滑鍙互鎶婁粬浠悎骞惰捣鏉ワ紝寤虹珛涓€涓鍚堢储寮曪紙compound index锛夈€?nbsp;
寰堝浜鸿涓哄彧瑕佹妸浠讳綍瀛楁鍔犺繘鑱氶泦绱㈠紩锛屽氨鑳芥彁楂樻煡璇㈤€熷害锛屼篃鏈変汉鎰熷埌杩锋儜锛氬鏋滄妸澶嶅悎鐨勮仛闆嗙储寮曞瓧娈靛垎寮€鏌ヨ锛岄偅涔堟煡璇㈤€熷害浼氬噺鎱㈠悧锛熷甫鐫€杩欎釜闂锛屾垜浠潵鐪嬩竴涓嬩互涓嬬殑鏌ヨ閫熷害锛堢粨鏋滈泦閮芥槸25涓囨潯鏁版嵁锛夛細锛堟棩鏈熷垪fariqi棣栧厛鎺掑湪澶嶅悎鑱氶泦绱㈠紩鐨勮捣濮嬪垪锛岀敤鎴峰悕neibuyonghu鎺掑湪鍚庡垪锛?nbsp;
锛?锛塻elect gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5'
鏌ヨ閫熷害锛?513姣
锛?锛塻elect gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5' and neibuyonghu='鍔炲叕瀹?
鏌ヨ閫熷害锛?516姣
锛?锛塻elect gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu='鍔炲叕瀹?
鏌ヨ閫熷害锛?0280姣
浠庝互涓婅瘯楠屼腑锛屾垜浠彲浠ョ湅鍒板鏋滀粎鐢ㄨ仛闆嗙储寮曠殑璧峰鍒椾綔涓烘煡璇㈡潯浠跺拰鍚屾椂鐢ㄥ埌澶嶅悎鑱氶泦绱㈠紩鐨勫叏閮ㄥ垪鐨勬煡璇㈤€熷害鏄嚑涔庝竴鏍风殑锛岀敋鑷虫瘮鐢ㄤ笂鍏ㄩ儴鐨勫鍚堢储寮曞垪杩樿鐣ュ揩锛堝湪鏌ヨ缁撴灉闆嗘暟鐩竴鏍风殑鎯呭喌涓嬶級锛涜€屽鏋滀粎鐢ㄥ鍚堣仛闆嗙储寮曠殑闈炶捣濮嬪垪浣滀负鏌ヨ鏉′欢鐨勮瘽锛岃繖涓储寮曟槸涓嶈捣浠讳綍浣滅敤鐨勩€傚綋鐒讹紝璇彞1銆?鐨勬煡璇㈤€熷害涓€鏍锋槸鍥犱负鏌ヨ鐨勬潯鐩暟涓€鏍凤紝濡傛灉澶嶅悎绱㈠紩鐨勬墍鏈夊垪閮界敤涓婏紝鑰屼笖鏌ヨ缁撴灉灏戠殑璇濓紝杩欐牱灏变細褰㈡垚鈥滅储寮曡鐩栤€濓紝鍥犺€屾€ц兘鍙互杈惧埌鏈€浼樸€傚悓鏃讹紝璇疯浣忥細鏃犺鎮ㄦ槸鍚︾粡甯镐娇鐢ㄨ仛鍚堢储寮曠殑鍏朵粬鍒楋紝浣嗗叾鍓嶅鍒椾竴瀹氳鏄娇鐢ㄦ渶棰戠箒鐨勫垪銆?nbsp;
锛堝洓锛夊叾浠栦功涓婃病鏈夌殑绱㈠紩浣跨敤缁忛獙鎬荤粨
1銆佺敤鑱氬悎绱㈠紩姣旂敤涓嶆槸鑱氬悎绱㈠紩鐨勪富閿€熷害蹇?nbsp;
涓嬮潰鏄疄渚嬭鍙ワ細锛堥兘鏄彁鍙?5涓囨潯鏁版嵁锛?nbsp;
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'
浣跨敤鏃堕棿锛?326姣
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid<=250000
浣跨敤鏃堕棿锛?470姣
杩欓噷锛岀敤鑱氬悎绱㈠紩姣旂敤涓嶆槸鑱氬悎绱㈠紩鐨勪富閿€熷害蹇簡杩?/4銆?nbsp;
2銆佺敤鑱氬悎绱㈠紩姣旂敤涓€鑸殑涓婚敭浣渙rder by鏃堕€熷害蹇紝鐗瑰埆鏄湪灏忔暟鎹噺鎯呭喌涓?nbsp;
select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by fariqi
鐢ㄦ椂锛?2936
select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by gid
鐢ㄦ椂锛?8843
杩欓噷锛岀敤鑱氬悎绱㈠紩姣旂敤涓€鑸殑涓婚敭浣渙rder by鏃讹紝閫熷害蹇簡3/10銆備簨瀹炰笂锛屽鏋滄暟鎹噺寰堝皬鐨勮瘽锛岀敤鑱氶泦绱㈠紩浣滀负鎺掑簭鍒楄姣斾娇鐢ㄩ潪鑱氶泦绱㈠紩閫熷害蹇緱鏄庢樉鐨勫锛涜€屾暟鎹噺濡傛灉寰堝ぇ鐨勮瘽锛屽10涓囦互涓婏紝鍒欎簩鑰呯殑閫熷害宸埆涓嶆槑鏄俱€?nbsp;
3銆佷娇鐢ㄨ仛鍚堢储寮曞唴鐨勬椂闂存锛屾悳绱㈡椂闂翠細鎸夋暟鎹崰鏁翠釜鏁版嵁琛ㄧ殑鐧惧垎姣旀垚姣斾緥鍑忓皯锛岃€屾棤璁鸿仛鍚堢储寮曚娇鐢ㄤ簡澶氬皯涓?nbsp;
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1'
鐢ㄦ椂锛?343姣锛堟彁鍙?00涓囨潯锛?nbsp;
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-6-6'
鐢ㄦ椂锛?170姣锛堟彁鍙?0涓囨潯锛?nbsp;
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'
鐢ㄦ椂锛?326姣锛堝拰涓婂彞鐨勭粨鏋滀竴妯′竴鏍枫€傚鏋滈噰闆嗙殑鏁伴噺涓€鏍凤紝閭ｄ箞鐢ㄥぇ浜庡彿鍜岀瓑浜庡彿鏄竴鏍风殑锛?nbsp;
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' and fariqi<'2004-6-6'
鐢ㄦ椂锛?280姣
4 銆佹棩鏈熷垪涓嶄細鍥犱负鏈夊垎绉掔殑杈撳叆鑰屽噺鎱㈡煡璇㈤€熷害
涓嬮潰鐨勪緥瀛愪腑锛屽叡鏈?00涓囨潯鏁版嵁锛?004骞?鏈?鏃ヤ互鍚庣殑鏁版嵁鏈?0涓囨潯锛屼絾鍙湁涓や釜涓嶅悓鐨勬棩鏈燂紝鏃ユ湡绮剧‘鍒版棩锛涗箣鍓嶆湁鏁版嵁50涓囨潯锛屾湁5000涓笉鍚岀殑鏃ユ湡锛屾棩鏈熺簿纭埌绉掋€?nbsp;
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' order by fariqi
鐢ㄦ椂锛?390姣
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi<'2004-1-1' order by fariqi
鐢ㄦ椂锛?453姣
锛堜簲锛夊叾浠栨敞鎰忎簨椤?nbsp;
鈥滄按鍙浇鑸燂紝浜﹀彲瑕嗚垷鈥濓紝绱㈠紩涔熶竴鏍枫€傜储寮曟湁鍔╀簬鎻愰珮妫€绱㈡€ц兘锛屼絾杩囧鎴栦笉褰撶殑绱㈠紩涔熶細瀵艰嚧绯荤粺浣庢晥銆傚洜涓虹敤鎴峰湪琛ㄤ腑姣忓姞杩涗竴涓储寮曪紝鏁版嵁搴撳氨瑕佸仛鏇村鐨勫伐浣溿€傝繃澶氱殑绱㈠紩鐢氳嚦浼氬鑷寸储寮曠鐗囥€?nbsp;
鎵€浠ヨ锛屾垜浠寤虹珛涓€涓€滈€傚綋鈥濈殑绱㈠紩浣撶郴锛岀壒鍒槸瀵硅仛鍚堢储寮曠殑鍒涘缓锛屾洿搴旂簿鐩婃眰绮撅紝浠ヤ娇鎮ㄧ殑鏁版嵁搴撹兘寰楀埌楂樻€ц兘鐨勫彂鎸ャ€?nbsp;
褰撶劧锛屽湪瀹炶返涓紝浣滀负涓€涓敖鑱岀殑鏁版嵁搴撶鐞嗗憳锛屾偍杩樿澶氭祴璇曚竴浜涙柟妗堬紝鎵惧嚭鍝鏂规鏁堢巼鏈€楂樸€佹渶涓烘湁鏁堛€?nbsp;
浜屻€佹敼鍠凷QL璇彞
寰堝浜轰笉鐭ラ亾SQL璇彞鍦⊿QL SERVER涓槸濡備綍鎵ц鐨勶紝浠栦滑鎷呭績鑷繁鎵€鍐欑殑SQL璇彞浼氳SQL SERVER璇В銆傛瘮濡傦細
select * from table1 where name='zhangsan' and tID > 10000
鍜屾墽琛?
select * from table1 where tID > 10000 and name='zhangsan'
涓€浜涗汉涓嶇煡閬撲互涓婁袱鏉¤鍙ョ殑鎵ц鏁堢巼鏄惁涓€鏍凤紝鍥犱负濡傛灉绠€鍗曠殑浠庤鍙ュ厛鍚庝笂鐪嬶紝杩欎袱涓鍙ョ殑纭槸涓嶄竴鏍凤紝濡傛灉tID鏄竴涓仛鍚堢储寮曪紝閭ｄ箞鍚庝竴鍙ヤ粎浠呬粠琛ㄧ殑10000鏉′互鍚庣殑璁板綍涓煡鎵惧氨琛屼簡锛涜€屽墠涓€鍙ュ垯瑕佸厛浠庡叏琛ㄤ腑鏌ユ壘鐪嬫湁鍑犱釜name='zhangsan'鐨勶紝鑰屽悗鍐嶆牴鎹檺鍒舵潯浠舵潯浠秚ID>10000鏉ユ彁鍑烘煡璇㈢粨鏋溿€?nbsp;
浜嬪疄涓婏紝杩欐牱鐨勬媴蹇冩槸涓嶅繀瑕佺殑銆係QL SERVER涓湁涓€涓€滄煡璇㈠垎鏋愪紭鍖栧櫒鈥濓紝瀹冨彲浠ヨ绠楀嚭where瀛愬彞涓殑鎼滅储鏉′欢骞剁‘瀹氬摢涓储寮曡兘缂╁皬琛ㄦ壂鎻忕殑鎼滅储绌洪棿锛屼篃灏辨槸璇达紝瀹冭兘瀹炵幇鑷姩浼樺寲銆?nbsp;
铏界劧鏌ヨ浼樺寲鍣ㄥ彲浠ユ牴鎹畐here瀛愬彞鑷姩鐨勮繘琛屾煡璇紭鍖栵紝浣嗗ぇ瀹朵粛鐒舵湁蹇呰浜嗚В涓€涓嬧€滄煡璇紭鍖栧櫒鈥濈殑宸ヤ綔鍘熺悊锛屽闈炶繖鏍凤紝鏈夋椂鏌ヨ浼樺寲鍣ㄥ氨浼氫笉鎸夌収鎮ㄧ殑鏈剰杩涜蹇€熸煡璇€?nbsp;
鍦ㄦ煡璇㈠垎鏋愰樁娈碉紝鏌ヨ浼樺寲鍣ㄦ煡鐪嬫煡璇㈢殑姣忎釜闃舵骞跺喅瀹氶檺鍒堕渶瑕佹壂鎻忕殑鏁版嵁閲忔槸鍚︽湁鐢ㄣ€傚鏋滀竴涓樁娈靛彲浠ヨ鐢ㄤ綔涓€涓壂鎻忓弬鏁帮紙SARG锛夛紝閭ｄ箞灏辩О涔嬩负鍙紭鍖栫殑锛屽苟涓斿彲浠ュ埄鐢ㄧ储寮曞揩閫熻幏寰楁墍闇€鏁版嵁銆?nbsp;
SARG鐨勫畾涔夛細鐢ㄤ簬闄愬埗鎼滅储鐨勪竴涓搷浣滐紝鍥犱负瀹冮€氬父鏄寚涓€涓壒瀹氱殑鍖归厤锛屼竴涓€煎緱鑼冨洿鍐呯殑鍖归厤鎴栬€呬袱涓互涓婃潯浠剁殑AND杩炴帴銆傚舰寮忓涓嬶細
鍒楀悕 鎿嶄綔绗?nbsp; <甯告暟 鎴?nbsp; 鍙橀噺>
鎴?nbsp;
<甯告暟 鎴?nbsp; 鍙橀噺> 鎿嶄綔绗﹀垪鍚?nbsp;
鍒楀悕鍙互鍑虹幇鍦ㄦ搷浣滅鐨勪竴杈癸紝鑰屽父鏁版垨鍙橀噺鍑虹幇鍦ㄦ搷浣滅鐨勫彟涓€杈广€傚锛?nbsp;
Name=鈥欏紶涓夆€?nbsp;
浠锋牸>5000
5000<浠锋牸
Name=鈥欏紶涓夆€?nbsp; and 浠锋牸>5000
濡傛灉涓€涓〃杈惧紡涓嶈兘婊¤冻SARG鐨勫舰寮忥紝閭ｅ畠灏辨棤娉曢檺鍒舵悳绱㈢殑鑼冨洿浜嗭紝涔熷氨鏄疭QL SERVER蹇呴』瀵规瘡涓€琛岄兘鍒ゆ柇瀹冩槸鍚︽弧瓒砏HERE瀛愬彞涓殑鎵€鏈夋潯浠躲€傛墍浠ヤ竴涓储寮曞浜庝笉婊¤冻SARG褰㈠紡鐨勮〃杈惧紡鏉ヨ鏄棤鐢ㄧ殑銆?nbsp;
浠嬬粛瀹孲ARG鍚庯紝鎴戜滑鏉ユ€荤粨涓€涓嬩娇鐢⊿ARG浠ュ強鍦ㄥ疄璺典腑閬囧埌鐨勫拰鏌愪簺璧勬枡涓婄粨璁轰笉鍚岀殑缁忛獙锛?nbsp;
1銆丩ike璇彞鏄惁灞炰簬SARG鍙栧喅浜庢墍浣跨敤鐨勯€氶厤绗︾殑绫诲瀷
濡傦細name like 鈥樺紶%鈥?nbsp; 锛岃繖灏卞睘浜嶴ARG
鑰岋細name like 鈥?寮犫€?nbsp; ,灏变笉灞炰簬SARG銆?nbsp;
鍘熷洜鏄€氶厤绗?鍦ㄥ瓧绗︿覆鐨勫紑閫氫娇寰楃储寮曟棤娉曚娇鐢ㄣ€?nbsp;
2銆乷r 浼氬紩璧峰叏琛ㄦ壂鎻?nbsp;
Name=鈥欏紶涓夆€?nbsp; and 浠锋牸>5000 绗﹀彿SARG锛岃€岋細Name=鈥欏紶涓夆€?nbsp; or 浠锋牸>5000 鍒欎笉绗﹀悎SARG銆備娇鐢╫r浼氬紩璧峰叏琛ㄦ壂鎻忋€?nbsp;
銆侀潪鎿嶄綔绗︺€佸嚱鏁板紩璧风殑涓嶆弧瓒砈ARG褰㈠紡鐨勮鍙?nbsp;
涓嶆弧瓒砈ARG褰㈠紡鐨勮鍙ユ渶鍏稿瀷鐨勬儏鍐靛氨鏄寘鎷潪鎿嶄綔绗︾殑璇彞锛屽锛歂OT銆?=銆?lt;>銆?<銆?>銆丯OT EXISTS銆丯OT IN銆丯OT LIKE绛夛紝鍙﹀杩樻湁鍑芥暟銆備笅闈㈠氨鏄嚑涓笉婊¤冻SARG褰㈠紡鐨勪緥瀛愶細
ABS(浠锋牸)<5000
Name like 鈥?涓夆€?nbsp;
鏈変簺琛ㄨ揪寮忥紝濡傦細
WHERE 浠锋牸*2>5000
SQL SERVER涔熶細璁や负鏄疭ARG锛孲QL SERVER浼氬皢姝ゅ紡杞寲涓猴細
WHERE 浠锋牸>2500/2
浣嗘垜浠笉鎺ㄨ崘杩欐牱浣跨敤锛屽洜涓烘湁鏃禨QL SERVER涓嶈兘淇濊瘉杩欑杞寲涓庡師濮嬭〃杈惧紡鏄畬鍏ㄧ瓑浠风殑銆?nbsp;
4銆両N 鐨勪綔鐢ㄧ浉褰撲笌OR
璇彞锛?nbsp;
Select * from table1 where tid in (2,3)
鍜?nbsp;
Select * from table1 where tid=2 or tid=3
鏄竴鏍风殑锛岄兘浼氬紩璧峰叏琛ㄦ壂鎻忥紝濡傛灉tid涓婃湁绱㈠紩锛屽叾绱㈠紩涔熶細澶辨晥銆?nbsp;
5銆佸敖閲忓皯鐢∟OT
6銆乪xists 鍜?nbsp; in 鐨勬墽琛屾晥鐜囨槸涓€鏍风殑
寰堝璧勬枡涓婇兘鏄剧ず璇达紝exists瑕佹瘮in鐨勬墽琛屾晥鐜囪楂橈紝鍚屾椂搴斿敖鍙兘鐨勭敤not exists鏉ヤ唬鏇縩ot in銆備絾浜嬪疄涓婏紝鎴戣瘯楠屼簡涓€涓嬶紝鍙戠幇浜岃€呮棤璁烘槸鍓嶉潰甯︿笉甯ot锛屼簩鑰呬箣闂寸殑鎵ц鏁堢巼閮芥槸涓€鏍风殑銆傚洜涓烘秹鍙婂瓙鏌ヨ锛屾垜浠瘯楠岃繖娆＄敤SQL SERVER鑷甫鐨刾ubs鏁版嵁搴撱€傝繍琛屽墠鎴戜滑鍙互鎶奡QL SERVER鐨剆tatistics I/O鐘舵€佹墦寮€銆?nbsp;
锛?锛塻elect title,price from titles where title_id in (select title_id from sales where qty>30)
璇ュ彞鐨勬墽琛岀粨鏋滀负锛?nbsp;
琛?nbsp; 'sales'銆傛壂鎻忚鏁?nbsp; 18锛岄€昏緫璇?nbsp; 56 娆★紝鐗╃悊璇?nbsp; 0 娆★紝棰勮 0 娆°€?nbsp;
琛?nbsp; 'titles'銆傛壂鎻忚鏁?nbsp; 1锛岄€昏緫璇?nbsp; 2 娆★紝鐗╃悊璇?nbsp; 0 娆★紝棰勮 0 娆°€?nbsp;
锛?锛塻elect title,price from titles where exists (select * from sales where sales.title_id=titles.title_id and qty>30)
绗簩鍙ョ殑鎵ц缁撴灉涓猴細
琛?nbsp; 'sales'銆傛壂鎻忚鏁?nbsp; 18锛岄€昏緫璇?nbsp; 56 娆★紝鐗╃悊璇?nbsp; 0 娆★紝棰勮 0 娆°€?nbsp;
琛?nbsp; 'titles'銆傛壂鎻忚鏁?nbsp; 1锛岄€昏緫璇?nbsp; 2 娆★紝鐗╃悊璇?nbsp; 0 娆★紝棰勮 0 娆°€?nbsp;
鎴戜滑浠庢鍙互鐪嬪埌鐢╡xists鍜岀敤in鐨勬墽琛屾晥鐜囨槸涓€鏍风殑銆?nbsp;
7銆佺敤鍑芥暟charindex()鍜屽墠闈㈠姞閫氶厤绗?鐨凩IKE鎵ц鏁堢巼涓€鏍?nbsp;
鍓嶉潰锛屾垜浠皥鍒帮紝濡傛灉鍦↙IKE鍓嶉潰鍔犱笂閫氶厤绗?锛岄偅涔堝皢浼氬紩璧峰叏琛ㄦ壂鎻忥紝鎵€浠ュ叾鎵ц鏁堢巼鏄綆涓嬬殑銆備絾鏈夌殑璧勬枡浠嬬粛璇达紝鐢ㄥ嚱鏁癱harindex()鏉ヤ唬鏇縇IKE閫熷害浼氭湁澶х殑鎻愬崌锛岀粡鎴戣瘯楠岋紝鍙戠幇杩欑璇存槑涔熸槸閿欒鐨勶細
select gid,title,fariqi,reader from tgongwen where charindex('鍒戜睛鏀槦',reader)>0 and fariqi>'2004-5-5'
鐢ㄦ椂锛?绉掞紝鍙﹀锛氭壂鎻忚鏁?nbsp; 4锛岄€昏緫璇?nbsp; 7155 娆★紝鐗╃悊璇?nbsp; 0 娆★紝棰勮 0 娆°€?nbsp;
select gid,title,fariqi,reader from tgongwen where reader like '%' + '鍒戜睛鏀槦' + '%' and fariqi>'2004-5-5'
鐢ㄦ椂锛?绉掞紝鍙﹀锛氭壂鎻忚鏁?nbsp; 4锛岄€昏緫璇?nbsp; 7155 娆★紝鐗╃悊璇?nbsp; 0 娆★紝棰勮 0 娆°€?nbsp;
8銆乽nion骞朵笉缁濆姣攐r鐨勬墽琛屾晥鐜囬珮
鎴戜滑鍓嶉潰宸茬粡璋堝埌浜嗗湪where瀛愬彞涓娇鐢╫r浼氬紩璧峰叏琛ㄦ壂鎻忥紝涓€鑸殑锛屾垜鎵€瑙佽繃鐨勮祫鏂欓兘鏄帹鑽愯繖閲岀敤union鏉ヤ唬鏇縪r銆備簨瀹炶瘉鏄庯紝杩欑璇存硶瀵逛簬澶ч儴鍒嗛兘鏄€傜敤鐨勩€?nbsp;
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16' or gid>9990000
鐢ㄦ椂锛?8绉掋€傛壂鎻忚鏁?nbsp; 1锛岄€昏緫璇?nbsp; 404008 娆★紝鐗╃悊璇?nbsp; 283 娆★紝棰勮 392163 娆°€?nbsp;
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'
union
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid>9990000
鐢ㄦ椂锛?绉掋€傛壂鎻忚鏁?nbsp; 8锛岄€昏緫璇?nbsp; 67489 娆★紝鐗╃悊璇?nbsp; 216 娆★紝棰勮 7499 娆°€?nbsp;
鐪嬫潵锛岀敤union鍦ㄩ€氬父鎯呭喌涓嬫瘮鐢╫r鐨勬晥鐜囪楂樼殑澶氥€?nbsp;
浣嗙粡杩囪瘯楠岋紝绗旇€呭彂鐜板鏋渙r涓よ竟鐨勬煡璇㈠垪鏄竴鏍风殑璇濓紝閭ｄ箞鐢╱nion鍒欏弽鍊掑拰鐢╫r鐨勬墽琛岄€熷害宸緢澶氾紝铏界劧杩欓噷union鎵弿鐨勬槸绱㈠紩锛岃€宱r鎵弿鐨勬槸鍏ㄨ〃銆?nbsp;
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16' or fariqi='2004-2-5'
鐢ㄦ椂锛?423姣銆傛壂鎻忚鏁?nbsp; 2锛岄€昏緫璇?nbsp; 14726 娆★紝鐗╃悊璇?nbsp; 1 娆★紝棰勮 7176 娆°€?nbsp;
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'
union
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-2-5'
鐢ㄦ椂锛?1640姣銆傛壂鎻忚鏁?nbsp; 8锛岄€昏緫璇?nbsp; 14806 娆★紝鐗╃悊璇?nbsp; 108 娆★紝棰勮 1144 娆°€?nbsp;
9銆佸瓧娈垫彁鍙栬鎸夌収鈥滈渶澶氬皯銆佹彁澶氬皯鈥濈殑鍘熷垯锛岄伩鍏嶁€渟elect *鈥?nbsp;
鎴戜滑鏉ュ仛涓€涓瘯楠岋細
select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc
鐢ㄦ椂锛?673姣
select top 10000 gid,fariqi,title from tgongwen order by gid desc
鐢ㄦ椂锛?376姣
select top 10000 gid,fariqi from tgongwen order by gid desc
鐢ㄦ椂锛?0姣
鐢辨鐪嬫潵锛屾垜浠瘡灏戞彁鍙栦竴涓瓧娈碉紝鏁版嵁鐨勬彁鍙栭€熷害灏变細鏈夌浉搴旂殑鎻愬崌銆傛彁鍗囩殑閫熷害杩樿鐪嬫偍鑸嶅純鐨勫瓧娈电殑澶у皬鏉ュ垽鏂€?nbsp;
10銆乧ount(*)涓嶆瘮count(瀛楁)鎱?nbsp;
鏌愪簺璧勬枡涓婅锛氱敤*浼氱粺璁℃墍鏈夊垪锛屾樉鐒惰姣斾竴涓笘鐣岀殑鍒楀悕鏁堢巼浣庛€傝繖绉嶈娉曞叾瀹炴槸娌℃湁鏍规嵁鐨勩€傛垜浠潵鐪嬶細
select count(*) from Tgongwen
鐢ㄦ椂锛?500姣
select count(gid) from Tgongwen
鐢ㄦ椂锛?483姣
select count(fariqi) from Tgongwen
鐢ㄦ椂锛?140姣
select count(title) from Tgongwen
鐢ㄦ椂锛?2050姣
浠庝互涓婂彲浠ョ湅鍑猴紝濡傛灉鐢╟ount(*)鍜岀敤count(涓婚敭)鐨勯€熷害鏄浉褰撶殑锛岃€宑ount(*)鍗存瘮鍏朵粬浠讳綍闄や富閿互澶栫殑瀛楁姹囨€婚€熷害瑕佸揩锛岃€屼笖瀛楁瓒婇暱锛屾眹鎬荤殑閫熷害灏辫秺鎱€傛垜鎯筹紝濡傛灉鐢╟ount(*)锛?nbsp; SQL SERVER鍙兘浼氳嚜鍔ㄦ煡鎵炬渶灏忓瓧娈垫潵姹囨€荤殑銆傚綋鐒讹紝濡傛灉鎮ㄧ洿鎺ュ啓count(涓婚敭)灏嗕細鏉ョ殑鏇寸洿鎺ヤ簺銆?nbsp;
11銆乷rder by鎸夎仛闆嗙储寮曞垪鎺掑簭鏁堢巼鏈€楂?nbsp;
鎴戜滑鏉ョ湅锛氾紙gid鏄富閿紝fariqi鏄仛鍚堢储寮曞垪锛?nbsp;
select top 10000 gid,fariqi,reader,title from tgongwen
鐢ㄦ椂锛?96 姣銆?nbsp; 鎵弿璁℃暟 1锛岄€昏緫璇?nbsp; 289 娆★紝鐗╃悊璇?nbsp; 1 娆★紝棰勮 1527 娆°€?nbsp;
select top 10000 gid,fariqi,reader,title from tgongwen order by gid asc
鐢ㄦ椂锛?720姣銆?nbsp; 鎵弿璁℃暟 1锛岄€昏緫璇?nbsp; 41956 娆★紝鐗╃悊璇?nbsp; 0 娆★紝棰勮 1287 娆°€?nbsp;
select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc
鐢ㄦ椂锛?736姣銆?nbsp; 鎵弿璁℃暟 1锛岄€昏緫璇?nbsp; 55350 娆★紝鐗╃悊璇?nbsp; 10 娆★紝棰勮 775 娆°€?nbsp;
select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi asc
鐢ㄦ椂锛?73姣銆?nbsp; 鎵弿璁℃暟 1锛岄€昏緫璇?nbsp; 290 娆★紝鐗╃悊璇?nbsp; 0 娆★紝棰勮 0 娆°€?nbsp;
select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi desc
鐢ㄦ椂锛?56姣銆?nbsp; 鎵弿璁℃暟 1锛岄€昏緫璇?nbsp; 289 娆★紝鐗╃悊璇?nbsp; 0 娆★紝棰勮 0 娆°€?nbsp;
浠庝互涓婃垜浠彲浠ョ湅鍑猴紝涓嶆帓搴忕殑閫熷害浠ュ強閫昏緫璇绘鏁伴兘鏄拰鈥渙rder by 鑱氶泦绱㈠紩鍒椻€?nbsp; 鐨勯€熷害鏄浉褰撶殑锛屼絾杩欎簺閮芥瘮鈥渙rder by 闈炶仛闆嗙储寮曞垪鈥濈殑鏌ヨ閫熷害鏄揩寰楀鐨勩€?nbsp;
鍚屾椂锛屾寜鐓ф煇涓瓧娈佃繘琛屾帓搴忕殑鏃跺€欙紝鏃犺鏄搴忚繕鏄€掑簭锛岄€熷害鏄熀鏈浉褰撶殑銆?nbsp;
12銆侀珮鏁堢殑TOP
浜嬪疄涓婏紝鍦ㄦ煡璇㈠拰鎻愬彇瓒呭ぇ瀹归噺鐨勬暟鎹泦鏃讹紝褰卞搷鏁版嵁搴撳搷搴旀椂闂寸殑鏈€澶у洜绱犱笉鏄暟鎹煡鎵撅紝鑰屾槸鐗╃悊鐨処/0鎿嶄綔銆傚锛?nbsp;
select top 10 * from (
select top 10000 gid,fariqi,title from tgongwen
where neibuyonghu='鍔炲叕瀹?
order by gid desc) as a
order by gid asc
杩欐潯璇彞锛屼粠鐞嗚涓婅锛屾暣鏉¤鍙ョ殑鎵ц鏃堕棿搴旇姣斿瓙鍙ョ殑鎵ц鏃堕棿闀匡紝浣嗕簨瀹炵浉鍙嶃€傚洜涓猴紝瀛愬彞鎵ц鍚庤繑鍥炵殑鏄?0000鏉¤褰曪紝鑰屾暣鏉¤鍙ヤ粎杩斿洖10鏉¤鍙ワ紝鎵€浠ュ奖鍝嶆暟鎹簱鍝嶅簲鏃堕棿鏈€澶х殑鍥犵礌鏄墿鐞咺/O鎿嶄綔銆傝€岄檺鍒剁墿鐞咺/O鎿嶄綔姝ゅ鐨勬渶鏈夋晥鏂规硶涔嬩竴灏辨槸浣跨敤TOP鍏抽敭璇嶄簡銆俆OP鍏抽敭璇嶆槸SQL SERVER涓粡杩囩郴缁熶紭鍖栬繃鐨勪竴涓敤鏉ユ彁鍙栧墠鍑犳潯鎴栧墠鍑犱釜鐧惧垎姣旀暟鎹殑璇嶃€傜粡绗旇€呭湪瀹炶返涓殑搴旂敤锛屽彂鐜癟OP纭疄寰堝ソ鐢紝鏁堢巼涔熷緢楂樸€備絾杩欎釜璇嶅湪鍙﹀涓€涓ぇ鍨嬫暟鎹簱ORACLE涓嵈娌℃湁锛岃繖涓嶈兘璇翠笉鏄竴涓仐鎲撅紝铏界劧鍦∣RACLE涓彲浠ョ敤鍏朵粬鏂规硶锛堝锛歳ownumber锛夋潵瑙ｅ喅銆傚湪浠ュ悗鐨勫叧浜庘€滃疄鐜板崈涓囩骇鏁版嵁鐨勫垎椤垫樉绀哄瓨鍌ㄨ繃绋嬧€濈殑璁ㄨ涓紝鎴戜滑灏卞皢鐢ㄥ埌TOP杩欎釜鍏抽敭璇嶃€?nbsp;
锛堜竴锛夋繁鍏ユ祬鍑虹悊瑙ｇ储寮曠粨鏋?nbsp;
瀹為檯涓婏紝鎮ㄥ彲浠ユ妸绱㈠紩鐞嗚В涓轰竴绉嶇壒娈婄殑鐩綍銆傚井杞殑SQL SERVER鎻愪緵浜嗕袱绉嶇储寮曪細鑱氶泦绱㈠紩锛坈lustered index锛屼篃绉拌仛绫荤储寮曘€佺皣闆嗙储寮曪級鍜岄潪鑱氶泦绱㈠紩锛坣onclustered index锛屼篃绉伴潪鑱氱被绱㈠紩銆侀潪绨囬泦绱㈠紩锛夈€備笅闈紝鎴戜滑涓句緥鏉ヨ鏄庝竴涓嬭仛闆嗙储寮曞拰闈炶仛闆嗙储寮曠殑鍖哄埆锛?nbsp;
鍏跺疄锛屾垜浠殑姹夎瀛楀吀鐨勬鏂囨湰韬氨鏄竴涓仛闆嗙储寮曘€傛瘮濡傦紝鎴戜滑瑕佹煡鈥滃畨鈥濆瓧锛屽氨浼氬緢鑷劧鍦扮炕寮€瀛楀吀鐨勫墠鍑犻〉锛屽洜涓衡€滃畨鈥濈殑鎷奸煶鏄€渁n鈥濓紝鑰屾寜鐓ф嫾闊虫帓搴忔眽瀛楃殑瀛楀吀鏄互鑻辨枃瀛楁瘝鈥渁鈥濆紑澶村苟浠モ€渮鈥濈粨灏剧殑锛岄偅涔堚€滃畨鈥濆瓧灏辫嚜鐒跺湴鎺掑湪瀛楀吀鐨勫墠閮ㄣ€傚鏋滄偍缈诲畬浜嗘墍鏈変互鈥渁鈥濆紑澶寸殑閮ㄥ垎浠嶇劧鎵句笉鍒拌繖涓瓧锛岄偅涔堝氨璇存槑鎮ㄧ殑瀛楀吀涓病鏈夎繖涓瓧锛涘悓鏍风殑锛屽鏋滄煡鈥滃紶鈥濆瓧锛岄偅鎮ㄤ篃浼氬皢鎮ㄧ殑瀛楀吀缈诲埌鏈€鍚庨儴鍒嗭紝鍥犱负鈥滃紶鈥濈殑鎷奸煶鏄€渮hang鈥濄€備篃灏辨槸璇达紝瀛楀吀鐨勬鏂囬儴鍒嗘湰韬氨鏄竴涓洰褰曪紝鎮ㄤ笉闇€瑕佸啀鍘绘煡鍏朵粬鐩綍鏉ユ壘鍒版偍闇€瑕佹壘鐨勫唴瀹广€?nbsp;
鎴戜滑鎶婅繖绉嶆鏂囧唴瀹规湰韬氨鏄竴绉嶆寜鐓т竴瀹氳鍒欐帓鍒楃殑鐩綍绉颁负鈥滆仛闆嗙储寮曗€濄€?nbsp;
濡傛灉鎮ㄨ璇嗘煇涓瓧锛屾偍鍙互蹇€熷湴浠庤嚜鍔ㄤ腑鏌ュ埌杩欎釜瀛椼€備絾鎮ㄤ篃鍙兘浼氶亣鍒版偍涓嶈璇嗙殑瀛楋紝涓嶇煡閬撳畠鐨勫彂闊筹紝杩欐椂鍊欙紝鎮ㄥ氨涓嶈兘鎸夌収鍒氭墠鐨勬柟娉曟壘鍒版偍瑕佹煡鐨勫瓧锛岃€岄渶瑕佸幓鏍规嵁鈥滃亸鏃侀儴棣栤€濇煡鍒版偍瑕佹壘鐨勫瓧锛岀劧鍚庢牴鎹繖涓瓧鍚庣殑椤电爜鐩存帴缈诲埌鏌愰〉鏉ユ壘鍒版偍瑕佹壘鐨勫瓧銆備絾鎮ㄧ粨鍚堚€滈儴棣栫洰褰曗€濆拰鈥滄瀛楄〃鈥濊€屾煡鍒扮殑瀛楃殑鎺掑簭骞朵笉鏄湡姝ｇ殑姝ｆ枃鐨勬帓搴忔柟娉曪紝姣斿鎮ㄦ煡鈥滃紶鈥濆瓧锛屾垜浠彲浠ョ湅鍒板湪鏌ラ儴棣栦箣鍚庣殑妫€瀛楄〃涓€滃紶鈥濈殑椤电爜鏄?72椤碉紝妫€瀛楄〃涓€滃紶鈥濈殑涓婇潰鏄€滈┌鈥濆瓧锛屼絾椤电爜鍗存槸63椤碉紝鈥滃紶鈥濈殑涓嬮潰鏄€滃缉鈥濆瓧锛岄〉闈㈡槸390椤点€傚緢鏄剧劧锛岃繖浜涘瓧骞朵笉鏄湡姝ｇ殑鍒嗗埆浣嶄簬鈥滃紶鈥濆瓧鐨勪笂涓嬫柟锛岀幇鍦ㄦ偍鐪嬪埌鐨勮繛缁殑鈥滈┌銆佸紶銆佸缉鈥濅笁瀛楀疄闄呬笂灏辨槸浠栦滑鍦ㄩ潪鑱氶泦绱㈠紩涓殑鎺掑簭锛屾槸瀛楀吀姝ｆ枃涓殑瀛楀湪闈炶仛闆嗙储寮曚腑鐨勬槧灏勩€傛垜浠彲浠ラ€氳繃杩欑鏂瑰紡鏉ユ壘鍒版偍鎵€闇€瑕佺殑瀛楋紝浣嗗畠闇€瑕佷袱涓繃绋嬶紝鍏堟壘鍒扮洰褰曚腑鐨勭粨鏋滐紝鐒跺悗鍐嶇炕鍒版偍鎵€闇€瑕佺殑椤电爜銆?nbsp;
鎴戜滑鎶婅繖绉嶇洰褰曠函绮规槸鐩綍锛屾鏂囩函绮规槸姝ｆ枃鐨勬帓搴忔柟寮忕О涓衡€滈潪鑱氶泦绱㈠紩鈥濄€?nbsp;
閫氳繃浠ヤ笂渚嬪瓙锛屾垜浠彲浠ョ悊瑙ｅ埌浠€涔堟槸鈥滆仛闆嗙储寮曗€濆拰鈥滈潪鑱氶泦绱㈠紩鈥濄€?nbsp;
杩涗竴姝ュ紩鐢充竴涓嬶紝鎴戜滑鍙互寰堝鏄撶殑鐞嗚В锛氭瘡涓〃鍙兘鏈変竴涓仛闆嗙储寮曪紝鍥犱负鐩綍鍙兘鎸夌収涓€绉嶆柟娉曡繘琛屾帓搴忋€?nbsp;
锛堜簩锛変綍鏃朵娇鐢ㄨ仛闆嗙储寮曟垨闈炶仛闆嗙储寮?nbsp;
涓嬮潰鐨勮〃鎬荤粨浜嗕綍鏃朵娇鐢ㄨ仛闆嗙储寮曟垨闈炶仛闆嗙储寮曪紙寰堥噸瑕侊級銆?nbsp;
浜嬪疄涓婏紝鎴戜滑鍙互閫氳繃鍓嶉潰鑱氶泦绱㈠紩鍜岄潪鑱氶泦绱㈠紩鐨勫畾涔夌殑渚嬪瓙鏉ョ悊瑙ｄ笂琛ㄣ€傚锛氳繑鍥炴煇鑼冨洿鍐呯殑鏁版嵁涓€椤广€傛瘮濡傛偍鐨勬煇涓〃鏈変竴涓椂闂村垪锛屾伆濂芥偍鎶婅仛鍚堢储寮曞缓绔嬪湪浜嗚鍒楋紝杩欐椂鎮ㄦ煡璇?004骞?鏈?鏃ヨ嚦2004骞?0鏈?鏃ヤ箣闂寸殑鍏ㄩ儴鏁版嵁鏃讹紝杩欎釜閫熷害灏卞皢鏄緢蹇殑锛屽洜涓烘偍鐨勮繖鏈瓧鍏告鏂囨槸鎸夋棩鏈熻繘琛屾帓搴忕殑锛岃仛绫荤储寮曞彧闇€瑕佹壘鍒拌妫€绱㈢殑鎵€鏈夋暟鎹腑鐨勫紑澶村拰缁撳熬鏁版嵁鍗冲彲锛涜€屼笉鍍忛潪鑱氶泦绱㈠紩锛屽繀椤诲厛鏌ュ埌鐩綍涓煡鍒版瘡涓€椤规暟鎹搴旂殑椤电爜锛岀劧鍚庡啀鏍规嵁椤电爜鏌ュ埌鍏蜂綋鍐呭銆?nbsp;
锛堜笁锛夌粨鍚堝疄闄咃紝璋堢储寮曚娇鐢ㄧ殑璇尯
鐞嗚鐨勭洰鐨勬槸搴旂敤銆傝櫧鐒舵垜浠垰鎵嶅垪鍑轰簡浣曟椂搴斾娇鐢ㄨ仛闆嗙储寮曟垨闈炶仛闆嗙储寮曪紝浣嗗湪瀹炶返涓互涓婅鍒欏嵈寰堝鏄撹蹇借鎴栦笉鑳芥牴鎹疄闄呮儏鍐佃繘琛岀患鍚堝垎鏋愩€備笅闈㈡垜浠皢鏍规嵁鍦ㄥ疄璺典腑閬囧埌鐨勫疄闄呴棶棰樻潵璋堜竴涓嬬储寮曚娇鐢ㄧ殑璇尯锛屼互渚夸簬澶у鎺屾彙绱㈠紩寤虹珛鐨勬柟娉曘€?nbsp;
1銆佷富閿氨鏄仛闆嗙储寮?nbsp;
杩欑鎯虫硶绗旇€呰涓烘槸鏋佺閿欒鐨勶紝鏄鑱氶泦绱㈠紩鐨勪竴绉嶆氮璐广€傝櫧鐒禨QL SERVER榛樿鏄湪涓婚敭涓婂缓绔嬭仛闆嗙储寮曠殑銆?nbsp;
閫氬父锛屾垜浠細鍦ㄦ瘡涓〃涓兘寤虹珛涓€涓狪D鍒楋紝浠ュ尯鍒嗘瘡鏉℃暟鎹紝骞朵笖杩欎釜ID鍒楁槸鑷姩澧炲ぇ鐨勶紝姝ラ暱涓€鑸负1銆傛垜浠殑杩欎釜鍔炲叕鑷姩鍖栫殑瀹炰緥涓殑鍒桮id灏辨槸濡傛銆傛鏃讹紝濡傛灉鎴戜滑灏嗚繖涓垪璁句负涓婚敭锛孲QL SERVER浼氬皢姝ゅ垪榛樿涓鸿仛闆嗙储寮曘€傝繖鏍峰仛鏈夊ソ澶勶紝灏辨槸鍙互璁╂偍鐨勬暟鎹湪鏁版嵁搴撲腑鎸夌収ID杩涜鐗╃悊鎺掑簭锛屼絾绗旇€呰涓鸿繖鏍峰仛鎰忎箟涓嶅ぇ銆?nbsp;
鏄捐€屾槗瑙侊紝鑱氶泦绱㈠紩鐨勪紭鍔挎槸寰堟槑鏄剧殑锛岃€屾瘡涓〃涓彧鑳芥湁涓€涓仛闆嗙储寮曠殑瑙勫垯锛岃繖浣垮緱鑱氶泦绱㈠紩鍙樺緱鏇村姞鐝嶈吹銆?nbsp;
浠庢垜浠墠闈㈣皥鍒扮殑鑱氶泦绱㈠紩鐨勫畾涔夋垜浠彲浠ョ湅鍑猴紝浣跨敤鑱氶泦绱㈠紩鐨勬渶澶уソ澶勫氨鏄兘澶熸牴鎹煡璇㈣姹傦紝杩呴€熺缉灏忔煡璇㈣寖鍥达紝閬垮厤鍏ㄨ〃鎵弿銆傚湪瀹為檯搴旂敤涓紝鍥犱负ID鍙锋槸鑷姩鐢熸垚鐨勶紝鎴戜滑骞朵笉鐭ラ亾姣忔潯璁板綍鐨処D鍙凤紝鎵€浠ユ垜浠緢闅惧湪瀹炶返涓敤ID鍙锋潵杩涜鏌ヨ銆傝繖灏变娇璁㊣D鍙疯繖涓富閿綔涓鸿仛闆嗙储寮曟垚涓轰竴绉嶈祫婧愭氮璐广€傚叾娆★紝璁╂瘡涓狪D鍙烽兘涓嶅悓鐨勫瓧娈典綔涓鸿仛闆嗙储寮曚篃涓嶇鍚堚€滃ぇ鏁扮洰鐨勪笉鍚屽€兼儏鍐典笅涓嶅簲寤虹珛鑱氬悎绱㈠紩鈥濊鍒欙紱褰撶劧锛岃繖绉嶆儏鍐靛彧鏄拡瀵圭敤鎴风粡甯镐慨鏀硅褰曞唴瀹癸紝鐗瑰埆鏄储寮曢」鐨勬椂鍊欎細璐熶綔鐢紝浣嗗浜庢煡璇㈤€熷害骞舵病鏈夊奖鍝嶃€?nbsp;
鍦ㄥ姙鍏嚜鍔ㄥ寲绯荤粺涓紝鏃犺鏄郴缁熼椤垫樉绀虹殑闇€瑕佺敤鎴风鏀剁殑鏂囦欢銆佷細璁繕鏄敤鎴疯繘琛屾枃浠舵煡璇㈢瓑浠讳綍鎯呭喌涓嬭繘琛屾暟鎹煡璇㈤兘绂讳笉寮€瀛楁鐨勬槸鈥滄棩鏈熲€濊繕鏈夌敤鎴锋湰韬殑鈥滅敤鎴峰悕鈥濄€?nbsp;
閫氬父锛屽姙鍏嚜鍔ㄥ寲鐨勯椤典細鏄剧ず姣忎釜鐢ㄦ埛灏氭湭绛炬敹鐨勬枃浠舵垨浼氳銆傝櫧鐒舵垜浠殑where璇彞鍙互浠呬粎闄愬埗褰撳墠鐢ㄦ埛灏氭湭绛炬敹鐨勬儏鍐碉紝浣嗗鏋滄偍鐨勭郴缁熷凡寤虹珛浜嗗緢闀挎椂闂达紝骞朵笖鏁版嵁閲忓緢澶э紝閭ｄ箞锛屾瘡娆℃瘡涓敤鎴锋墦寮€棣栭〉鐨勬椂鍊欓兘杩涜涓€娆″叏琛ㄦ壂鎻忥紝杩欐牱鍋氭剰涔夋槸涓嶅ぇ鐨勶紝缁濆ぇ澶氭暟鐨勭敤鎴?涓湀鍓嶇殑鏂囦欢閮藉凡缁忔祻瑙堣繃浜嗭紝杩欐牱鍋氬彧鑳藉緬澧炴暟鎹簱鐨勫紑閿€鑰屽凡銆備簨瀹炰笂锛屾垜浠畬鍏ㄥ彲浠ヨ鐢ㄦ埛鎵撳紑绯荤粺棣栭〉鏃讹紝鏁版嵁搴撲粎浠呮煡璇㈣繖涓敤鎴疯繎3涓湀鏉ユ湭闃呰鐨勬枃浠讹紝閫氳繃鈥滄棩鏈熲€濊繖涓瓧娈垫潵闄愬埗琛ㄦ壂鎻忥紝鎻愰珮鏌ヨ閫熷害銆傚鏋滄偍鐨勫姙鍏嚜鍔ㄥ寲绯荤粺宸茬粡寤虹珛鐨?骞达紝閭ｄ箞鎮ㄧ殑棣栭〉鏄剧ず閫熷害鐞嗚涓婂皢鏄師鏉ラ€熷害8鍊嶏紝鐢氳嚦鏇村揩銆?
鍦ㄨ繖閲屼箣鎵€浠ユ彁鍒扳€滅悊璁轰笂鈥濅笁瀛楋紝鏄洜涓哄鏋滄偍鐨勮仛闆嗙储寮曡繕鏄洸鐩湴寤哄湪ID杩欎釜涓婚敭涓婃椂锛屾偍鐨勬煡璇㈤€熷害鏄病鏈夎繖涔堥珮鐨勶紝鍗充娇鎮ㄥ湪鈥滄棩鏈熲€濊繖涓瓧娈典笂寤虹珛鐨勭储寮曪紙闈炶仛鍚堢储寮曪級銆備笅闈㈡垜浠氨鏉ョ湅涓€涓嬪湪1000涓囨潯鏁版嵁閲忕殑鎯呭喌涓嬪悇绉嶆煡璇㈢殑閫熷害琛ㄧ幇锛?涓湀鍐呯殑鏁版嵁涓?5涓囨潯锛夛細
锛?锛変粎鍦ㄤ富閿笂寤虹珛鑱氶泦绱㈠紩锛屽苟涓斾笉鍒掑垎鏃堕棿娈碉細
Select gid,fariqi,neibuyonghu,title from tgongwen
鐢ㄦ椂锛?28470姣锛堝嵆锛?28绉掞級
锛?锛夊湪涓婚敭涓婂缓绔嬭仛闆嗙储寮曪紝鍦╢ariq涓婂缓绔嬮潪鑱氶泦绱㈠紩锛?nbsp;
select gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi> dateadd(day,-90,getdate())
鐢ㄦ椂锛?3763姣锛?4绉掞級
锛?锛夊皢鑱氬悎绱㈠紩寤虹珛鍦ㄦ棩鏈熷垪锛坒ariqi锛変笂锛?nbsp;
select gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi> dateadd(day,-90,getdate())
鐢ㄦ椂锛?423姣锛?绉掞級
铏界劧姣忔潯璇彞鎻愬彇鍑烘潵鐨勯兘鏄?5涓囨潯鏁版嵁锛屽悇绉嶆儏鍐电殑宸紓鍗存槸宸ㄥぇ鐨勶紝鐗瑰埆鏄皢鑱氶泦绱㈠紩寤虹珛鍦ㄦ棩鏈熷垪鏃剁殑宸紓銆備簨瀹炰笂锛屽鏋滄偍鐨勬暟鎹簱鐪熺殑鏈?000涓囧閲忕殑璇濓紝鎶婁富閿缓绔嬪湪ID鍒椾笂锛屽氨鍍忎互涓婄殑绗?銆?绉嶆儏鍐碉紝鍦ㄧ綉椤典笂鐨勮〃鐜板氨鏄秴鏃讹紝鏍规湰灏辨棤娉曟樉绀恒€傝繖涔熸槸鎴戞憭寮僆D鍒椾綔涓鸿仛闆嗙储寮曠殑涓€涓渶閲嶈鐨勫洜绱犮€?nbsp;
寰楀嚭浠ヤ笂閫熷害鐨勬柟娉曟槸锛氬湪鍚勪釜select璇彞鍓嶅姞锛歞eclare @d datetime
set @d=getdate()
骞跺湪select璇彞鍚庡姞锛?nbsp;
select [璇彞鎵ц鑺辫垂鏃堕棿(姣)]=datediff(ms,@d,getdate())
2銆佸彧瑕佸缓绔嬬储寮曞氨鑳芥樉钁楁彁楂樻煡璇㈤€熷害
浜嬪疄涓婏紝鎴戜滑鍙互鍙戠幇涓婇潰鐨勪緥瀛愪腑锛岀2銆?鏉¤鍙ュ畬鍏ㄧ浉鍚岋紝涓斿缓绔嬬储寮曠殑瀛楁涔熺浉鍚岋紱涓嶅悓鐨勪粎鏄墠鑰呭湪fariqi瀛楁涓婂缓绔嬬殑鏄潪鑱氬悎绱㈠紩锛屽悗鑰呭湪姝ゅ瓧娈典笂寤虹珛鐨勬槸鑱氬悎绱㈠紩锛屼絾鏌ヨ閫熷害鍗存湁鐫€澶╁￥涔嬪埆銆傛墍浠ワ紝骞堕潪鏄湪浠讳綍瀛楁涓婄畝鍗曞湴寤虹珛绱㈠紩灏辫兘鎻愰珮鏌ヨ閫熷害銆?nbsp;
浠庡缓琛ㄧ殑璇彞涓紝鎴戜滑鍙互鐪嬪埌杩欎釜鏈夌潃1000涓囨暟鎹殑琛ㄤ腑fariqi瀛楁鏈?003涓笉鍚岃褰曘€傚湪姝ゅ瓧娈典笂寤虹珛鑱氬悎绱㈠紩鏄啀鍚堥€備笉杩囦簡銆傚湪鐜板疄涓紝鎴戜滑姣忓ぉ閮戒細鍙戝嚑涓枃浠讹紝杩欏嚑涓枃浠剁殑鍙戞枃鏃ユ湡灏辩浉鍚岋紝杩欏畬鍏ㄧ鍚堝缓绔嬭仛闆嗙储寮曡姹傜殑锛氣€滄棦涓嶈兘缁濆ぇ澶氭暟閮界浉鍚岋紝鍙堜笉鑳藉彧鏈夋瀬灏戞暟鐩稿悓鈥濈殑瑙勫垯銆傜敱姝ょ湅鏉ワ紝鎴戜滑寤虹珛鈥滈€傚綋鈥濈殑鑱氬悎绱㈠紩瀵逛簬鎴戜滑鎻愰珮鏌ヨ閫熷害鏄潪甯搁噸瑕佺殑銆?nbsp;
3銆佹妸鎵€鏈夐渶瑕佹彁楂樻煡璇㈤€熷害鐨勫瓧娈甸兘鍔犺繘鑱氶泦绱㈠紩锛屼互鎻愰珮鏌ヨ閫熷害
涓婇潰宸茬粡璋堝埌锛氬湪杩涜鏁版嵁鏌ヨ鏃堕兘绂讳笉寮€瀛楁鐨勬槸鈥滄棩鏈熲€濊繕鏈夌敤鎴锋湰韬殑鈥滅敤鎴峰悕鈥濄€傛棦鐒惰繖涓や釜瀛楁閮芥槸濡傛鐨勯噸瑕侊紝鎴戜滑鍙互鎶婁粬浠悎骞惰捣鏉ワ紝寤虹珛涓€涓鍚堢储寮曪紙compound index锛夈€?nbsp;
寰堝浜鸿涓哄彧瑕佹妸浠讳綍瀛楁鍔犺繘鑱氶泦绱㈠紩锛屽氨鑳芥彁楂樻煡璇㈤€熷害锛屼篃鏈変汉鎰熷埌杩锋儜锛氬鏋滄妸澶嶅悎鐨勮仛闆嗙储寮曞瓧娈靛垎寮€鏌ヨ锛岄偅涔堟煡璇㈤€熷害浼氬噺鎱㈠悧锛熷甫鐫€杩欎釜闂锛屾垜浠潵鐪嬩竴涓嬩互涓嬬殑鏌ヨ閫熷害锛堢粨鏋滈泦閮芥槸25涓囨潯鏁版嵁锛夛細锛堟棩鏈熷垪fariqi棣栧厛鎺掑湪澶嶅悎鑱氶泦绱㈠紩鐨勮捣濮嬪垪锛岀敤鎴峰悕neibuyonghu鎺掑湪鍚庡垪锛?nbsp;
锛?锛塻elect gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5'
鏌ヨ閫熷害锛?513姣
锛?锛塻elect gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5' and neibuyonghu='鍔炲叕瀹?
鏌ヨ閫熷害锛?516姣
锛?锛塻elect gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu='鍔炲叕瀹?
鏌ヨ閫熷害锛?0280姣
浠庝互涓婅瘯楠屼腑锛屾垜浠彲浠ョ湅鍒板鏋滀粎鐢ㄨ仛闆嗙储寮曠殑璧峰鍒椾綔涓烘煡璇㈡潯浠跺拰鍚屾椂鐢ㄥ埌澶嶅悎鑱氶泦绱㈠紩鐨勫叏閮ㄥ垪鐨勬煡璇㈤€熷害鏄嚑涔庝竴鏍风殑锛岀敋鑷虫瘮鐢ㄤ笂鍏ㄩ儴鐨勫鍚堢储寮曞垪杩樿鐣ュ揩锛堝湪鏌ヨ缁撴灉闆嗘暟鐩竴鏍风殑鎯呭喌涓嬶級锛涜€屽鏋滀粎鐢ㄥ鍚堣仛闆嗙储寮曠殑闈炶捣濮嬪垪浣滀负鏌ヨ鏉′欢鐨勮瘽锛岃繖涓储寮曟槸涓嶈捣浠讳綍浣滅敤鐨勩€傚綋鐒讹紝璇彞1銆?鐨勬煡璇㈤€熷害涓€鏍锋槸鍥犱负鏌ヨ鐨勬潯鐩暟涓€鏍凤紝濡傛灉澶嶅悎绱㈠紩鐨勬墍鏈夊垪閮界敤涓婏紝鑰屼笖鏌ヨ缁撴灉灏戠殑璇濓紝杩欐牱灏变細褰㈡垚鈥滅储寮曡鐩栤€濓紝鍥犺€屾€ц兘鍙互杈惧埌鏈€浼樸€傚悓鏃讹紝璇疯浣忥細鏃犺鎮ㄦ槸鍚︾粡甯镐娇鐢ㄨ仛鍚堢储寮曠殑鍏朵粬鍒楋紝浣嗗叾鍓嶅鍒椾竴瀹氳鏄娇鐢ㄦ渶棰戠箒鐨勫垪銆?nbsp;
锛堝洓锛夊叾浠栦功涓婃病鏈夌殑绱㈠紩浣跨敤缁忛獙鎬荤粨
1銆佺敤鑱氬悎绱㈠紩姣旂敤涓嶆槸鑱氬悎绱㈠紩鐨勪富閿€熷害蹇?nbsp;
涓嬮潰鏄疄渚嬭鍙ワ細锛堥兘鏄彁鍙?5涓囨潯鏁版嵁锛?nbsp;
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'
浣跨敤鏃堕棿锛?326姣
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid<=250000
浣跨敤鏃堕棿锛?470姣
杩欓噷锛岀敤鑱氬悎绱㈠紩姣旂敤涓嶆槸鑱氬悎绱㈠紩鐨勪富閿€熷害蹇簡杩?/4銆?nbsp;
2銆佺敤鑱氬悎绱㈠紩姣旂敤涓€鑸殑涓婚敭浣渙rder by鏃堕€熷害蹇紝鐗瑰埆鏄湪灏忔暟鎹噺鎯呭喌涓?nbsp;
select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by fariqi
鐢ㄦ椂锛?2936
select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by gid
鐢ㄦ椂锛?8843
杩欓噷锛岀敤鑱氬悎绱㈠紩姣旂敤涓€鑸殑涓婚敭浣渙rder by鏃讹紝閫熷害蹇簡3/10銆備簨瀹炰笂锛屽鏋滄暟鎹噺寰堝皬鐨勮瘽锛岀敤鑱氶泦绱㈠紩浣滀负鎺掑簭鍒楄姣斾娇鐢ㄩ潪鑱氶泦绱㈠紩閫熷害蹇緱鏄庢樉鐨勫锛涜€屾暟鎹噺濡傛灉寰堝ぇ鐨勮瘽锛屽10涓囦互涓婏紝鍒欎簩鑰呯殑閫熷害宸埆涓嶆槑鏄俱€?nbsp;
3銆佷娇鐢ㄨ仛鍚堢储寮曞唴鐨勬椂闂存锛屾悳绱㈡椂闂翠細鎸夋暟鎹崰鏁翠釜鏁版嵁琛ㄧ殑鐧惧垎姣旀垚姣斾緥鍑忓皯锛岃€屾棤璁鸿仛鍚堢储寮曚娇鐢ㄤ簡澶氬皯涓?nbsp;
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1'
鐢ㄦ椂锛?343姣锛堟彁鍙?00涓囨潯锛?nbsp;
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-6-6'
鐢ㄦ椂锛?170姣锛堟彁鍙?0涓囨潯锛?nbsp;
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'
鐢ㄦ椂锛?326姣锛堝拰涓婂彞鐨勭粨鏋滀竴妯′竴鏍枫€傚鏋滈噰闆嗙殑鏁伴噺涓€鏍凤紝閭ｄ箞鐢ㄥぇ浜庡彿鍜岀瓑浜庡彿鏄竴鏍风殑锛?nbsp;
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' and fariqi<'2004-6-6'
鐢ㄦ椂锛?280姣
4 銆佹棩鏈熷垪涓嶄細鍥犱负鏈夊垎绉掔殑杈撳叆鑰屽噺鎱㈡煡璇㈤€熷害
涓嬮潰鐨勪緥瀛愪腑锛屽叡鏈?00涓囨潯鏁版嵁锛?004骞?鏈?鏃ヤ互鍚庣殑鏁版嵁鏈?0涓囨潯锛屼絾鍙湁涓や釜涓嶅悓鐨勬棩鏈燂紝鏃ユ湡绮剧‘鍒版棩锛涗箣鍓嶆湁鏁版嵁50涓囨潯锛屾湁5000涓笉鍚岀殑鏃ユ湡锛屾棩鏈熺簿纭埌绉掋€?nbsp;
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' order by fariqi
鐢ㄦ椂锛?390姣
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi<'2004-1-1' order by fariqi
鐢ㄦ椂锛?453姣
锛堜簲锛夊叾浠栨敞鎰忎簨椤?nbsp;
鈥滄按鍙浇鑸燂紝浜﹀彲瑕嗚垷鈥濓紝绱㈠紩涔熶竴鏍枫€傜储寮曟湁鍔╀簬鎻愰珮妫€绱㈡€ц兘锛屼絾杩囧鎴栦笉褰撶殑绱㈠紩涔熶細瀵艰嚧绯荤粺浣庢晥銆傚洜涓虹敤鎴峰湪琛ㄤ腑姣忓姞杩涗竴涓储寮曪紝鏁版嵁搴撳氨瑕佸仛鏇村鐨勫伐浣溿€傝繃澶氱殑绱㈠紩鐢氳嚦浼氬鑷寸储寮曠鐗囥€?nbsp;
鎵€浠ヨ锛屾垜浠寤虹珛涓€涓€滈€傚綋鈥濈殑绱㈠紩浣撶郴锛岀壒鍒槸瀵硅仛鍚堢储寮曠殑鍒涘缓锛屾洿搴旂簿鐩婃眰绮撅紝浠ヤ娇鎮ㄧ殑鏁版嵁搴撹兘寰楀埌楂樻€ц兘鐨勫彂鎸ャ€?nbsp;
褰撶劧锛屽湪瀹炶返涓紝浣滀负涓€涓敖鑱岀殑鏁版嵁搴撶鐞嗗憳锛屾偍杩樿澶氭祴璇曚竴浜涙柟妗堬紝鎵惧嚭鍝鏂规鏁堢巼鏈€楂樸€佹渶涓烘湁鏁堛€?nbsp;
浜屻€佹敼鍠凷QL璇彞
寰堝浜轰笉鐭ラ亾SQL璇彞鍦⊿QL SERVER涓槸濡備綍鎵ц鐨勶紝浠栦滑鎷呭績鑷繁鎵€鍐欑殑SQL璇彞浼氳SQL SERVER璇В銆傛瘮濡傦細
select * from table1 where name='zhangsan' and tID > 10000
鍜屾墽琛?
select * from table1 where tID > 10000 and name='zhangsan'
涓€浜涗汉涓嶇煡閬撲互涓婁袱鏉¤鍙ョ殑鎵ц鏁堢巼鏄惁涓€鏍凤紝鍥犱负濡傛灉绠€鍗曠殑浠庤鍙ュ厛鍚庝笂鐪嬶紝杩欎袱涓鍙ョ殑纭槸涓嶄竴鏍凤紝濡傛灉tID鏄竴涓仛鍚堢储寮曪紝閭ｄ箞鍚庝竴鍙ヤ粎浠呬粠琛ㄧ殑10000鏉′互鍚庣殑璁板綍涓煡鎵惧氨琛屼簡锛涜€屽墠涓€鍙ュ垯瑕佸厛浠庡叏琛ㄤ腑鏌ユ壘鐪嬫湁鍑犱釜name='zhangsan'鐨勶紝鑰屽悗鍐嶆牴鎹檺鍒舵潯浠舵潯浠秚ID>10000鏉ユ彁鍑烘煡璇㈢粨鏋溿€?nbsp;
浜嬪疄涓婏紝杩欐牱鐨勬媴蹇冩槸涓嶅繀瑕佺殑銆係QL SERVER涓湁涓€涓€滄煡璇㈠垎鏋愪紭鍖栧櫒鈥濓紝瀹冨彲浠ヨ绠楀嚭where瀛愬彞涓殑鎼滅储鏉′欢骞剁‘瀹氬摢涓储寮曡兘缂╁皬琛ㄦ壂鎻忕殑鎼滅储绌洪棿锛屼篃灏辨槸璇达紝瀹冭兘瀹炵幇鑷姩浼樺寲銆?nbsp;
铏界劧鏌ヨ浼樺寲鍣ㄥ彲浠ユ牴鎹畐here瀛愬彞鑷姩鐨勮繘琛屾煡璇紭鍖栵紝浣嗗ぇ瀹朵粛鐒舵湁蹇呰浜嗚В涓€涓嬧€滄煡璇紭鍖栧櫒鈥濈殑宸ヤ綔鍘熺悊锛屽闈炶繖鏍凤紝鏈夋椂鏌ヨ浼樺寲鍣ㄥ氨浼氫笉鎸夌収鎮ㄧ殑鏈剰杩涜蹇€熸煡璇€?nbsp;
鍦ㄦ煡璇㈠垎鏋愰樁娈碉紝鏌ヨ浼樺寲鍣ㄦ煡鐪嬫煡璇㈢殑姣忎釜闃舵骞跺喅瀹氶檺鍒堕渶瑕佹壂鎻忕殑鏁版嵁閲忔槸鍚︽湁鐢ㄣ€傚鏋滀竴涓樁娈靛彲浠ヨ鐢ㄤ綔涓€涓壂鎻忓弬鏁帮紙SARG锛夛紝閭ｄ箞灏辩О涔嬩负鍙紭鍖栫殑锛屽苟涓斿彲浠ュ埄鐢ㄧ储寮曞揩閫熻幏寰楁墍闇€鏁版嵁銆?nbsp;
SARG鐨勫畾涔夛細鐢ㄤ簬闄愬埗鎼滅储鐨勪竴涓搷浣滐紝鍥犱负瀹冮€氬父鏄寚涓€涓壒瀹氱殑鍖归厤锛屼竴涓€煎緱鑼冨洿鍐呯殑鍖归厤鎴栬€呬袱涓互涓婃潯浠剁殑AND杩炴帴銆傚舰寮忓涓嬶細
鍒楀悕 鎿嶄綔绗?nbsp; <甯告暟 鎴?nbsp; 鍙橀噺>
鎴?nbsp;
<甯告暟 鎴?nbsp; 鍙橀噺> 鎿嶄綔绗﹀垪鍚?nbsp;
鍒楀悕鍙互鍑虹幇鍦ㄦ搷浣滅鐨勪竴杈癸紝鑰屽父鏁版垨鍙橀噺鍑虹幇鍦ㄦ搷浣滅鐨勫彟涓€杈广€傚锛?nbsp;
Name=鈥欏紶涓夆€?nbsp;
浠锋牸>5000
5000<浠锋牸
Name=鈥欏紶涓夆€?nbsp; and 浠锋牸>5000
濡傛灉涓€涓〃杈惧紡涓嶈兘婊¤冻SARG鐨勫舰寮忥紝閭ｅ畠灏辨棤娉曢檺鍒舵悳绱㈢殑鑼冨洿浜嗭紝涔熷氨鏄疭QL SERVER蹇呴』瀵规瘡涓€琛岄兘鍒ゆ柇瀹冩槸鍚︽弧瓒砏HERE瀛愬彞涓殑鎵€鏈夋潯浠躲€傛墍浠ヤ竴涓储寮曞浜庝笉婊¤冻SARG褰㈠紡鐨勮〃杈惧紡鏉ヨ鏄棤鐢ㄧ殑銆?nbsp;
浠嬬粛瀹孲ARG鍚庯紝鎴戜滑鏉ユ€荤粨涓€涓嬩娇鐢⊿ARG浠ュ強鍦ㄥ疄璺典腑閬囧埌鐨勫拰鏌愪簺璧勬枡涓婄粨璁轰笉鍚岀殑缁忛獙锛?nbsp;
1銆丩ike璇彞鏄惁灞炰簬SARG鍙栧喅浜庢墍浣跨敤鐨勯€氶厤绗︾殑绫诲瀷
濡傦細name like 鈥樺紶%鈥?nbsp; 锛岃繖灏卞睘浜嶴ARG
鑰岋細name like 鈥?寮犫€?nbsp; ,灏变笉灞炰簬SARG銆?nbsp;
鍘熷洜鏄€氶厤绗?鍦ㄥ瓧绗︿覆鐨勫紑閫氫娇寰楃储寮曟棤娉曚娇鐢ㄣ€?nbsp;
2銆乷r 浼氬紩璧峰叏琛ㄦ壂鎻?nbsp;
Name=鈥欏紶涓夆€?nbsp; and 浠锋牸>5000 绗﹀彿SARG锛岃€岋細Name=鈥欏紶涓夆€?nbsp; or 浠锋牸>5000 鍒欎笉绗﹀悎SARG銆備娇鐢╫r浼氬紩璧峰叏琛ㄦ壂鎻忋€?nbsp;
銆侀潪鎿嶄綔绗︺€佸嚱鏁板紩璧风殑涓嶆弧瓒砈ARG褰㈠紡鐨勮鍙?nbsp;
涓嶆弧瓒砈ARG褰㈠紡鐨勮鍙ユ渶鍏稿瀷鐨勬儏鍐靛氨鏄寘鎷潪鎿嶄綔绗︾殑璇彞锛屽锛歂OT銆?=銆?lt;>銆?<銆?>銆丯OT EXISTS銆丯OT IN銆丯OT LIKE绛夛紝鍙﹀杩樻湁鍑芥暟銆備笅闈㈠氨鏄嚑涓笉婊¤冻SARG褰㈠紡鐨勪緥瀛愶細
ABS(浠锋牸)<5000
Name like 鈥?涓夆€?nbsp;
鏈変簺琛ㄨ揪寮忥紝濡傦細
WHERE 浠锋牸*2>5000
SQL SERVER涔熶細璁や负鏄疭ARG锛孲QL SERVER浼氬皢姝ゅ紡杞寲涓猴細
WHERE 浠锋牸>2500/2
浣嗘垜浠笉鎺ㄨ崘杩欐牱浣跨敤锛屽洜涓烘湁鏃禨QL SERVER涓嶈兘淇濊瘉杩欑杞寲涓庡師濮嬭〃杈惧紡鏄畬鍏ㄧ瓑浠风殑銆?nbsp;
4銆両N 鐨勪綔鐢ㄧ浉褰撲笌OR
璇彞锛?nbsp;
Select * from table1 where tid in (2,3)
鍜?nbsp;
Select * from table1 where tid=2 or tid=3
鏄竴鏍风殑锛岄兘浼氬紩璧峰叏琛ㄦ壂鎻忥紝濡傛灉tid涓婃湁绱㈠紩锛屽叾绱㈠紩涔熶細澶辨晥銆?nbsp;
5銆佸敖閲忓皯鐢∟OT
6銆乪xists 鍜?nbsp; in 鐨勬墽琛屾晥鐜囨槸涓€鏍风殑
寰堝璧勬枡涓婇兘鏄剧ず璇达紝exists瑕佹瘮in鐨勬墽琛屾晥鐜囪楂橈紝鍚屾椂搴斿敖鍙兘鐨勭敤not exists鏉ヤ唬鏇縩ot in銆備絾浜嬪疄涓婏紝鎴戣瘯楠屼簡涓€涓嬶紝鍙戠幇浜岃€呮棤璁烘槸鍓嶉潰甯︿笉甯ot锛屼簩鑰呬箣闂寸殑鎵ц鏁堢巼閮芥槸涓€鏍风殑銆傚洜涓烘秹鍙婂瓙鏌ヨ锛屾垜浠瘯楠岃繖娆＄敤SQL SERVER鑷甫鐨刾ubs鏁版嵁搴撱€傝繍琛屽墠鎴戜滑鍙互鎶奡QL SERVER鐨剆tatistics I/O鐘舵€佹墦寮€銆?nbsp;
锛?锛塻elect title,price from titles where title_id in (select title_id from sales where qty>30)
璇ュ彞鐨勬墽琛岀粨鏋滀负锛?nbsp;
琛?nbsp; 'sales'銆傛壂鎻忚鏁?nbsp; 18锛岄€昏緫璇?nbsp; 56 娆★紝鐗╃悊璇?nbsp; 0 娆★紝棰勮 0 娆°€?nbsp;
琛?nbsp; 'titles'銆傛壂鎻忚鏁?nbsp; 1锛岄€昏緫璇?nbsp; 2 娆★紝鐗╃悊璇?nbsp; 0 娆★紝棰勮 0 娆°€?nbsp;
锛?锛塻elect title,price from titles where exists (select * from sales where sales.title_id=titles.title_id and qty>30)
绗簩鍙ョ殑鎵ц缁撴灉涓猴細
琛?nbsp; 'sales'銆傛壂鎻忚鏁?nbsp; 18锛岄€昏緫璇?nbsp; 56 娆★紝鐗╃悊璇?nbsp; 0 娆★紝棰勮 0 娆°€?nbsp;
琛?nbsp; 'titles'銆傛壂鎻忚鏁?nbsp; 1锛岄€昏緫璇?nbsp; 2 娆★紝鐗╃悊璇?nbsp; 0 娆★紝棰勮 0 娆°€?nbsp;
鎴戜滑浠庢鍙互鐪嬪埌鐢╡xists鍜岀敤in鐨勬墽琛屾晥鐜囨槸涓€鏍风殑銆?nbsp;
7銆佺敤鍑芥暟charindex()鍜屽墠闈㈠姞閫氶厤绗?鐨凩IKE鎵ц鏁堢巼涓€鏍?nbsp;
鍓嶉潰锛屾垜浠皥鍒帮紝濡傛灉鍦↙IKE鍓嶉潰鍔犱笂閫氶厤绗?锛岄偅涔堝皢浼氬紩璧峰叏琛ㄦ壂鎻忥紝鎵€浠ュ叾鎵ц鏁堢巼鏄綆涓嬬殑銆備絾鏈夌殑璧勬枡浠嬬粛璇达紝鐢ㄥ嚱鏁癱harindex()鏉ヤ唬鏇縇IKE閫熷害浼氭湁澶х殑鎻愬崌锛岀粡鎴戣瘯楠岋紝鍙戠幇杩欑璇存槑涔熸槸閿欒鐨勶細
select gid,title,fariqi,reader from tgongwen where charindex('鍒戜睛鏀槦',reader)>0 and fariqi>'2004-5-5'
鐢ㄦ椂锛?绉掞紝鍙﹀锛氭壂鎻忚鏁?nbsp; 4锛岄€昏緫璇?nbsp; 7155 娆★紝鐗╃悊璇?nbsp; 0 娆★紝棰勮 0 娆°€?nbsp;
select gid,title,fariqi,reader from tgongwen where reader like '%' + '鍒戜睛鏀槦' + '%' and fariqi>'2004-5-5'
鐢ㄦ椂锛?绉掞紝鍙﹀锛氭壂鎻忚鏁?nbsp; 4锛岄€昏緫璇?nbsp; 7155 娆★紝鐗╃悊璇?nbsp; 0 娆★紝棰勮 0 娆°€?nbsp;
8銆乽nion骞朵笉缁濆姣攐r鐨勬墽琛屾晥鐜囬珮
鎴戜滑鍓嶉潰宸茬粡璋堝埌浜嗗湪where瀛愬彞涓娇鐢╫r浼氬紩璧峰叏琛ㄦ壂鎻忥紝涓€鑸殑锛屾垜鎵€瑙佽繃鐨勮祫鏂欓兘鏄帹鑽愯繖閲岀敤union鏉ヤ唬鏇縪r銆備簨瀹炶瘉鏄庯紝杩欑璇存硶瀵逛簬澶ч儴鍒嗛兘鏄€傜敤鐨勩€?nbsp;
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16' or gid>9990000
鐢ㄦ椂锛?8绉掋€傛壂鎻忚鏁?nbsp; 1锛岄€昏緫璇?nbsp; 404008 娆★紝鐗╃悊璇?nbsp; 283 娆★紝棰勮 392163 娆°€?nbsp;
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'
union
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid>9990000
鐢ㄦ椂锛?绉掋€傛壂鎻忚鏁?nbsp; 8锛岄€昏緫璇?nbsp; 67489 娆★紝鐗╃悊璇?nbsp; 216 娆★紝棰勮 7499 娆°€?nbsp;
鐪嬫潵锛岀敤union鍦ㄩ€氬父鎯呭喌涓嬫瘮鐢╫r鐨勬晥鐜囪楂樼殑澶氥€?nbsp;
浣嗙粡杩囪瘯楠岋紝绗旇€呭彂鐜板鏋渙r涓よ竟鐨勬煡璇㈠垪鏄竴鏍风殑璇濓紝閭ｄ箞鐢╱nion鍒欏弽鍊掑拰鐢╫r鐨勬墽琛岄€熷害宸緢澶氾紝铏界劧杩欓噷union鎵弿鐨勬槸绱㈠紩锛岃€宱r鎵弿鐨勬槸鍏ㄨ〃銆?nbsp;
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16' or fariqi='2004-2-5'
鐢ㄦ椂锛?423姣銆傛壂鎻忚鏁?nbsp; 2锛岄€昏緫璇?nbsp; 14726 娆★紝鐗╃悊璇?nbsp; 1 娆★紝棰勮 7176 娆°€?nbsp;
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'
union
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-2-5'
鐢ㄦ椂锛?1640姣銆傛壂鎻忚鏁?nbsp; 8锛岄€昏緫璇?nbsp; 14806 娆★紝鐗╃悊璇?nbsp; 108 娆★紝棰勮 1144 娆°€?nbsp;
9銆佸瓧娈垫彁鍙栬鎸夌収鈥滈渶澶氬皯銆佹彁澶氬皯鈥濈殑鍘熷垯锛岄伩鍏嶁€渟elect *鈥?nbsp;
鎴戜滑鏉ュ仛涓€涓瘯楠岋細
select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc
鐢ㄦ椂锛?673姣
select top 10000 gid,fariqi,title from tgongwen order by gid desc
鐢ㄦ椂锛?376姣
select top 10000 gid,fariqi from tgongwen order by gid desc
鐢ㄦ椂锛?0姣
鐢辨鐪嬫潵锛屾垜浠瘡灏戞彁鍙栦竴涓瓧娈碉紝鏁版嵁鐨勬彁鍙栭€熷害灏变細鏈夌浉搴旂殑鎻愬崌銆傛彁鍗囩殑閫熷害杩樿鐪嬫偍鑸嶅純鐨勫瓧娈电殑澶у皬鏉ュ垽鏂€?nbsp;
10銆乧ount(*)涓嶆瘮count(瀛楁)鎱?nbsp;
鏌愪簺璧勬枡涓婅锛氱敤*浼氱粺璁℃墍鏈夊垪锛屾樉鐒惰姣斾竴涓笘鐣岀殑鍒楀悕鏁堢巼浣庛€傝繖绉嶈娉曞叾瀹炴槸娌℃湁鏍规嵁鐨勩€傛垜浠潵鐪嬶細
select count(*) from Tgongwen
鐢ㄦ椂锛?500姣
select count(gid) from Tgongwen
鐢ㄦ椂锛?483姣
select count(fariqi) from Tgongwen
鐢ㄦ椂锛?140姣
select count(title) from Tgongwen
鐢ㄦ椂锛?2050姣
浠庝互涓婂彲浠ョ湅鍑猴紝濡傛灉鐢╟ount(*)鍜岀敤count(涓婚敭)鐨勯€熷害鏄浉褰撶殑锛岃€宑ount(*)鍗存瘮鍏朵粬浠讳綍闄や富閿互澶栫殑瀛楁姹囨€婚€熷害瑕佸揩锛岃€屼笖瀛楁瓒婇暱锛屾眹鎬荤殑閫熷害灏辫秺鎱€傛垜鎯筹紝濡傛灉鐢╟ount(*)锛?nbsp; SQL SERVER鍙兘浼氳嚜鍔ㄦ煡鎵炬渶灏忓瓧娈垫潵姹囨€荤殑銆傚綋鐒讹紝濡傛灉鎮ㄧ洿鎺ュ啓count(涓婚敭)灏嗕細鏉ョ殑鏇寸洿鎺ヤ簺銆?nbsp;
11銆乷rder by鎸夎仛闆嗙储寮曞垪鎺掑簭鏁堢巼鏈€楂?nbsp;
鎴戜滑鏉ョ湅锛氾紙gid鏄富閿紝fariqi鏄仛鍚堢储寮曞垪锛?nbsp;
select top 10000 gid,fariqi,reader,title from tgongwen
鐢ㄦ椂锛?96 姣銆?nbsp; 鎵弿璁℃暟 1锛岄€昏緫璇?nbsp; 289 娆★紝鐗╃悊璇?nbsp; 1 娆★紝棰勮 1527 娆°€?nbsp;
select top 10000 gid,fariqi,reader,title from tgongwen order by gid asc
鐢ㄦ椂锛?720姣銆?nbsp; 鎵弿璁℃暟 1锛岄€昏緫璇?nbsp; 41956 娆★紝鐗╃悊璇?nbsp; 0 娆★紝棰勮 1287 娆°€?nbsp;
select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc
鐢ㄦ椂锛?736姣銆?nbsp; 鎵弿璁℃暟 1锛岄€昏緫璇?nbsp; 55350 娆★紝鐗╃悊璇?nbsp; 10 娆★紝棰勮 775 娆°€?nbsp;
select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi asc
鐢ㄦ椂锛?73姣銆?nbsp; 鎵弿璁℃暟 1锛岄€昏緫璇?nbsp; 290 娆★紝鐗╃悊璇?nbsp; 0 娆★紝棰勮 0 娆°€?nbsp;
select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi desc
鐢ㄦ椂锛?56姣銆?nbsp; 鎵弿璁℃暟 1锛岄€昏緫璇?nbsp; 289 娆★紝鐗╃悊璇?nbsp; 0 娆★紝棰勮 0 娆°€?nbsp;
浠庝互涓婃垜浠彲浠ョ湅鍑猴紝涓嶆帓搴忕殑閫熷害浠ュ強閫昏緫璇绘鏁伴兘鏄拰鈥渙rder by 鑱氶泦绱㈠紩鍒椻€?nbsp; 鐨勯€熷害鏄浉褰撶殑锛屼絾杩欎簺閮芥瘮鈥渙rder by 闈炶仛闆嗙储寮曞垪鈥濈殑鏌ヨ閫熷害鏄揩寰楀鐨勩€?nbsp;
鍚屾椂锛屾寜鐓ф煇涓瓧娈佃繘琛屾帓搴忕殑鏃跺€欙紝鏃犺鏄搴忚繕鏄€掑簭锛岄€熷害鏄熀鏈浉褰撶殑銆?nbsp;
12銆侀珮鏁堢殑TOP
浜嬪疄涓婏紝鍦ㄦ煡璇㈠拰鎻愬彇瓒呭ぇ瀹归噺鐨勬暟鎹泦鏃讹紝褰卞搷鏁版嵁搴撳搷搴旀椂闂寸殑鏈€澶у洜绱犱笉鏄暟鎹煡鎵撅紝鑰屾槸鐗╃悊鐨処/0鎿嶄綔銆傚锛?nbsp;
select top 10 * from (
select top 10000 gid,fariqi,title from tgongwen
where neibuyonghu='鍔炲叕瀹?
order by gid desc) as a
order by gid asc
杩欐潯璇彞锛屼粠鐞嗚涓婅锛屾暣鏉¤鍙ョ殑鎵ц鏃堕棿搴旇姣斿瓙鍙ョ殑鎵ц鏃堕棿闀匡紝浣嗕簨瀹炵浉鍙嶃€傚洜涓猴紝瀛愬彞鎵ц鍚庤繑鍥炵殑鏄?0000鏉¤褰曪紝鑰屾暣鏉¤鍙ヤ粎杩斿洖10鏉¤鍙ワ紝鎵€浠ュ奖鍝嶆暟鎹簱鍝嶅簲鏃堕棿鏈€澶х殑鍥犵礌鏄墿鐞咺/O鎿嶄綔銆傝€岄檺鍒剁墿鐞咺/O鎿嶄綔姝ゅ鐨勬渶鏈夋晥鏂规硶涔嬩竴灏辨槸浣跨敤TOP鍏抽敭璇嶄簡銆俆OP鍏抽敭璇嶆槸SQL SERVER涓粡杩囩郴缁熶紭鍖栬繃鐨勪竴涓敤鏉ユ彁鍙栧墠鍑犳潯鎴栧墠鍑犱釜鐧惧垎姣旀暟鎹殑璇嶃€傜粡绗旇€呭湪瀹炶返涓殑搴旂敤锛屽彂鐜癟OP纭疄寰堝ソ鐢紝鏁堢巼涔熷緢楂樸€備絾杩欎釜璇嶅湪鍙﹀涓€涓ぇ鍨嬫暟鎹簱ORACLE涓嵈娌℃湁锛岃繖涓嶈兘璇翠笉鏄竴涓仐鎲撅紝铏界劧鍦∣RACLE涓彲浠ョ敤鍏朵粬鏂规硶锛堝锛歳ownumber锛夋潵瑙ｅ喅銆傚湪浠ュ悗鐨勫叧浜庘€滃疄鐜板崈涓囩骇鏁版嵁鐨勫垎椤垫樉绀哄瓨鍌ㄨ繃绋嬧€濈殑璁ㄨ涓紝鎴戜滑灏卞皢鐢ㄥ埌TOP杩欎釜鍏抽敭璇嶃€?nbsp;