发表于 2009-1-5 19:48
| 
SQLite璇硶 SELECT
鍙戝竷: 2008-7-01 14:16 | 浣滆: 缃戠粶杞浇 | 鏉ユ簮: 缃戠粶杞浇 | 鏌ョ湅: 31娆
SQLite璇硶 SELECT
SELECT
sql-statement ::=
SELECT [ALL | DISTINCT] result [FROM table-list]
[WHERE expr]
[GROUP BY expr-list]
[HAVING expr]
[compound-op select]*
[ORDER BY sort-expr-list]
[LIMIT integer [( OFFSET | , ) integer]]
result ::=
result-column [, result-column]*
result-column ::=
* | table-name . * | expr [ [AS] string ]
table-list ::=
table [join-op table join-args]*
table ::=
table-name [AS alias] |
( select ) [AS alias]
join-op ::=
, | [NATURAL] [LEFT | RIGHT | FULL] [OUTER | INNER | CROSS] JOIN
join-args ::=
[ON expr] [USING ( id-list )]
sort-expr-list ::=
expr [sort-order] [, expr [sort-order]]*
sort-order ::=
[ COLLATE collation-name ] [ ASC | DESC ]
compound_op ::=
UNION | UNION ALL | INTERSECT | EXCEPT
SELECT璇彞鐢ㄤ簬鏌ヨ鏁版嵁搴撱備竴鏉ELECT鍛戒护鐨勮繑鍥炵粨鏋滄槸闆舵垨澶氳姣忚鏈夊浐瀹氬瓧娈垫暟鐨勬暟鎹傚瓧娈电殑鏁扮洰鐢卞湪SELECT鍜孎ROM涔嬮棿鐨勮〃杈惧紡鍒楄〃瀹氫箟銆備换鎰忕殑琛ㄨ揪寮忛兘鍙互琚敤浣滅粨鏋溿傝嫢琛ㄨ揪寮忔槸 *鍒欒〃绀烘墍鏈夎〃鐨勬墍鏈夊瓧娈点傝嫢琛ㄨ揪寮忔槸琛ㄧ殑鍚嶅瓧鍚庢帴.*鍒欑粨鏋滀负璇ヨ〃涓殑鎵鏈夊瓧娈点
DISTINCT鍏抽敭瀛楃殑浣跨敤浼氫娇杩斿洖鐨勭粨鏋滄槸鍘熺粨鏋滅殑涓涓笉鍚浉鍚岃鐨勫瓙闆嗐侼ULL鍊艰璁や负鏄浉鍚岀殑銆傜己鐪佽涓烘槸杩斿洖鎵鏈夌殑琛岋紝涓烘竻妤氳捣瑙佸彲浠ヤ娇鐢ㄥ叧閿瓧ALL銆
鏌ヨ瀵笷ROM涔嬪悗瀹氫箟鐨勪竴涓垨澶氫釜琛ㄨ繘琛屻傝嫢澶氫釜琛ㄧ敤閫楀彿杩炴帴锛屽垯鏌ヨ閽堝瀹冧滑鐨勪氦鍙夎繛鎺ャ傛墍鏈夌殑SQL-92杩炴帴璇硶鍧囧彲浠ョ敤浜庡畾涔夎繛鎺ャ傚渾鎷彿涓殑鍓煡璇㈠彲鑳借FROM瀛愬彞涓殑浠绘剰琛ㄥ悕鏇夸唬銆傚綋缁撴灉涓粎鏈変竴琛屽寘鍚〃杈惧紡鍒楄〃涓殑缁撴灉鐨勮鏃讹紝鏁翠釜鐨凢ROM瀛愬彞浼氳蹇界暐銆
WHERE瀛愬彞鍙互闄愬畾鏌ヨ鎿嶄綔鐨勮鏁扮洰銆
GROUP BY瀛愬彞灏嗕竴琛屾垨澶氳缁撴灉鍚堟垚鍗曡杈撳嚭銆傚綋缁撴灉鏈夎仛闆嗗嚱鏁版椂杩欏皢灏ゅ叾鏈夌敤銆侴ROUP BY瀛愬彞鐨勮〃杈惧紡涓嶉』鏄嚭鐜板湪缁撴灉涓殑琛ㄨ揪寮忋侶AVING瀛愬彞涓嶹HERE鐩镐技锛屽彧鏄疕AVING鐢ㄤ簬杩囨护鍒嗙粍鍒涘缓鐨勮銆侶AVING瀛愬彞鍙兘鍖呭惈鍊硷紝鐢氳嚦鏄笉鍑虹幇鍦ㄧ粨鏋滀腑鐨勮仛闆嗗嚱鏁般
ORDER BY瀛愬彞瀵规墍寰楃粨鏋滄牴鎹〃杈惧紡鎺掑簭銆傝〃杈惧紡鏃犻』鏄畝鍗昐ELECT鐨勭粨鏋滐紝浣嗗湪澶嶅悎SELECT涓瘡涓〃杈惧紡蹇呴』绮剧‘瀵瑰簲涓涓粨鏋滃瓧娈点傛瘡涓〃杈惧紡鍙兘璺熼殢涓涓彲閫夌殑COLLATE鍏抽敭瀛椾互鍙婄敤浜庢帓搴忔枃鏈殑姣旇緝鍑芥暟鍚嶇О鍜/鎴栧叧閿瓧ASC鎴朌ESC锛岀敤浜庤鏄庢帓搴忚鍒欍
LIMIT瀛愬彞闄愬畾琛屾暟鐨勬渶澶у笺傝礋鐨凩IMIT琛ㄧず鏃犱笂闄愩傚悗璺熷彲閫夌殑OFFSET璇存槑璺宠繃缁撴灉闆嗕腑鐨勫墠澶氬皯琛屻傚湪涓涓鍚堟煡璇腑锛孡IMIT瀛愬彞鍙厑璁稿嚭鐜板湪鏈缁圫ELECT璇彞涓傞檺瀹氬浜庢墍鏈夌殑鏌ヨ鍧囬傜敤锛岃屼笉浠呬粎鏄坊鍔犱簡LIMIT瀛愬彞鐨勯偅涓琛屻傛敞鎰廜FFSET鍏抽敭瀛楃敤浜嶭IMIT瀛愬彞涓紝鍒欓檺鍒跺兼槸绗竴涓暟瀛楋紝鑰屽亸绉婚噺(offset)鏄浜屼釜鏁板瓧銆傝嫢鐢ㄩ楀彿鏇夸唬OFFSET鍏抽敭瀛楋紝鍒欏亸绉婚噺鏄涓涓暟瀛楄岄檺鍒跺兼槸绗簩涓暟瀛椼傝繖鏄负浜嗗姞寮哄閬楃暀鐨凷QL鏁版嵁搴撶殑鍏煎鑰屾湁鎰忛犳垚鐨勭煕鐩俱
澶嶅悎鐨凷ELECT鐢变袱涓垨鏇村绠鍗昐ELECT缁忕敱UNION, UNION ALL, INTERSECT, EXCEPT涓殑涓涓繍绠楃杩炴帴鑰屾垚銆傚湪涓涓鍚圫ELECT涓紝鍚勪釜SELECT闇鎸囧畾鐩稿悓涓暟鐨勭粨鏋滃瓧娈点備粎鍏佽涓涓狾RDER BY瀛愬彞鍑虹幇鍦⊿ELECT鐨勬湯灏俱俇NION鍜孶NION ALL杩愮畻绗︿粠宸﹁嚦鍙冲皢鎵鏈塖ELECT鐨勭粨鏋滃悎鎴愪竴涓ぇ鐨勮〃銆備簩鑰呯殑鍖哄埆鍦ㄤ簬UNION鐨勬墍鏈夌粨鏋滆鏄笉鐩稿悓鐨勮 UNION ALL鍏佽閲嶅琛屻侷NTERSECT杩愮畻绗﹀彇宸﹀彸涓や釜SELECT缁撴灉鐨勪氦銆侲XCEPT浠庡乏杈筍ELECT鐨勭粨鏋滀腑闄ゆ帀鍙宠竟SELECT鐨勭粨鏋溿備笁涓垨鏇村SELECT澶嶅悎鏃讹紝瀹冧滑浠庡乏鑷冲彸缁撳悎銆
