mysql> SELECT name, COUNT(*) FROM myinfo GROUP BY name;
2.浣跨敤澶氫釜鏁版嵁琛?br />
]]>
1.閫夋嫨鎵鏈夋暟鎹?br /> SELECT * FROM tableName;
2.閫夋嫨鐗規(guī)畩鐨勮
SELECT * FROM myinfo WHERE name='shediao' [AND name='shediao'][OR name='shediao'][time>'2008-01-01'];
and or 鏄彲浠ユ販鐢ㄧ殑 浼樺厛綰?and>or
3.閫夋嫨鐗規(guī)畩鐨勫垪
SELECT name,age FROM myinfo;
璇鋒敞鎰忚鏌ヨ鍙槸綆鍗曞湴媯(gè)绱㈡瘡涓褰曠殑鍒楋紝騫朵笖浠栦滑涓殑涓浜涘嚭鐜板嬈°備負(fù)浜嗕嬌杈撳嚭鍑忓埌鏈灝戯紝澧炲姞鍏抽敭瀛?span>DISTINCT
媯(gè)绱㈠嚭姣忎釜鍞竴鐨勮緭鍑鴻褰曪細(xì)mysql> SELECT DISTINCT name FROM people;
4.鍒嗙被琛?/span> 褰撹鎸夋煇縐嶆柟寮忔帓搴忔椂錛屾鏌ユ煡璇㈣緭鍑洪氬父鏇村鏄撱備負(fù)浜嗘帓搴忕粨鏋滐紝浣跨敤ORDER BY瀛愬彞銆?br /> SELECT * FROM people ORDER BY name, time [DESC];/* DESC 琛ㄧず闄嶅簭鍙綔鐢ㄤ簬time , name榛樿鐨勬槸鍗囧簭*/
5.鏃ユ湡璁＄畻
SELECT YEAR(time) AS year FROM peopel;
YEAR( )銆?span>MONTH( )鍜?span>DAYOFMONTH( ) .
6.鍒ょ┖
is NULL , is NOT NULL
7.妯″紡鍖歸厤
SQL妯″紡鍖歸厤鍏佽浣犱嬌鐢?samp>“_”鍖歸厤浠諱綍鍗曚釜瀛楃錛岃?samp>“%”鍖歸厤浠繪剰鏁扮洰瀛楃(鍖呮嫭闆跺瓧絎?span>)銆傚湪 MySQL涓紝SQL鐨勬ā寮忛粯璁ゆ槸蹇界暐澶у皬鍐欑殑銆備笅闈㈢粰鍑轟竴浜涗緥瀛愩傛敞鎰忎嬌鐢?span>SQL妯″紡鏃訛紝涓嶈兘浣跨敤=鎴?span>!=錛涜屽簲浣跨敤LIKE鎴?span>NOT LIKE姣旇緝鎿嶄綔絎︺?br />
mysql> SELECT * FROM pet WHERE name LIKE 'b%';/*浠寮澶寸殑瀛楃紿?/mysql> SELECT * FROM pet WHERE name LIKE '%b';/*浠緇撳熬鐨勫瓧絎︿覆*/
mysql> SELECT * FROM pet WHERE name LIKE '%b%';/*鍚湁b鐨勫瓧絎︿覆*/
mysql> SELECT * FROM pet WHERE name LIKE '_____';/*鍚湁5涓瓧絎︾殑瀛楃涓?/
鐢?strong>MySQL鎻愪緵鐨勬ā寮忓尮閰嶇殑鍏跺畠綾誨瀷鏄嬌鐢ㄦ墿灞曟鍒欒〃杈懼紡銆傚綋浣犲榪欑被妯″紡榪涜鍖歸厤嫻嬭瘯鏃訛紝浣跨敤REGEXP鍜?span>NOT REGEXP鎿嶄綔絎?span>(鎴?span>RLIKE鍜?span>NOT RLIKE錛屽畠浠槸鍚屼箟璇?span>)銆?
鎵╁睍姝ｅ垯琛ㄨ揪寮忕殑涓浜涘瓧絎︽槸錛?
· ‘.’鍖歸厤浠諱綍鍗曚釜鐨勫瓧絎︺?/p>
· 瀛楃綾?samp>“[...]”鍖歸厤鍦ㄦ柟鎷彿鍐呯殑浠諱綍瀛楃銆備緥濡傦紝“[abc]”鍖歸厤“a”銆?samp>“b”鎴?samp>“c”銆備負(fù)浜嗗懡鍚嶅瓧絎︾殑鑼冨洿錛屼嬌鐢ㄤ竴涓?#8220;-”銆?samp>“[a-z]”鍖歸厤浠諱綍瀛楁瘝錛岃?samp>“[0-9]”鍖歸厤浠諱綍鏁板瓧銆?/p>
· “ * ”鍖歸厤闆朵釜鎴栧涓湪瀹冨墠闈㈢殑瀛楃銆備緥濡傦紝“x*”鍖歸厤浠諱綍鏁伴噺鐨?samp>“x”瀛楃錛?samp>“[0-9]*”鍖歸厤浠諱綍鏁伴噺鐨勬暟瀛楋紝鑰?samp>“.*”鍖歸厤浠諱綍鏁伴噺鐨勪換浣曞瓧絎︺?/p>
- 濡傛灉REGEXP妯″紡涓庤嫻嬭瘯鍊肩殑浠諱綍鍦版柟鍖歸厤錛屾ā寮忓氨鍖歸厤(榪欎笉鍚屼簬LIKE妯″紡鍖歸厤錛屽彧鏈変笌鏁翠釜鍊煎尮閰嶏紝妯″紡鎵嶅尮閰?span>)銆?
- 涓轟簡瀹氫綅涓涓ā寮忎互渚垮畠蹇呴』鍖歸厤琚祴璇曞肩殑寮濮嬫垨緇撳熬錛屽湪妯″紡寮濮嬪浣跨敤“^”鎴?samp>鍦ㄦā寮忕殑緇撳熬鐢?span>“$”銆?span>
涓轟簡璇存槑鎵╁睍姝ｅ垯琛ㄨ揪寮忓浣曞伐浣滐紝涓嬮潰浣跨敤REGEXP閲嶅啓涓婇潰鎵紺虹殑LIKE鏌ヨ錛?/p>
涓轟簡鎵懼嚭浠?samp>“b”寮澶寸殑鍚嶅瓧錛屼嬌鐢?samp>“^”鍖歸厤鍚嶅瓧鐨勫紑濮嬶細(xì)
mysql> SELECT * FROM pet WHERE name REGEXP '^b';
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
濡傛灉浣犳兂寮哄埗浣?span>REGEXP姣旇緝鍖哄垎澶у皬鍐欙紝浣跨敤BINARY鍏抽敭瀛椾嬌鍏朵腑涓涓瓧絎︿覆鍙樹負(fù)浜岃繘鍒跺瓧絎︿覆銆傝鏌ヨ鍙尮閰嶅悕縐伴瀛楁瘝鐨勫皬鍐?#8216;b’銆?/p>
mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';
涓轟簡鎵懼嚭浠?samp>“fy”緇撳熬鐨勫悕瀛楋紝浣跨敤“$”鍖歸厤鍚嶅瓧鐨勭粨灝撅細(xì)
mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';
+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+
涓轟簡鎵懼嚭鍖呭惈涓涓?samp>“w”鐨勫悕瀛楋紝浣跨敤浠ヤ笅鏌ヨ錛?/p>
mysql> SELECT * FROM pet WHERE name REGEXP 'w';
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+------------+
鏃㈢劧濡傛灉涓涓鍒欒〃杈懼紡鍑虹幇鍦ㄥ肩殑浠諱綍鍦版柟錛屽叾妯″紡鍖歸厤浜嗭紝灝變笉蹇呭湪鍏堝墠鐨勬煡璇腑鍦ㄦā寮忕殑涓や晶鏀劇疆涓涓氶厤絎︿互浣垮緱瀹冨尮閰嶆暣涓鹼紝灝卞儚浣犱嬌鐢ㄤ簡涓涓?span>SQL妯″紡閭ｆ牱銆?/p>
涓轟簡鎵懼嚭鍖呭惈姝ｅソ5涓瓧絎︾殑鍚嶅瓧錛屼嬌鐢?samp>“^”鍜?samp>“$”鍖歸厤鍚嶅瓧鐨勫紑濮嬪拰緇撳熬錛屽拰5涓?samp>“.”瀹炰緥鍦ㄤ袱鑰呬箣闂達(dá)細(xì)
mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
浣犱篃鍙互浣跨敤“{n}”“閲嶅n嬈?#8221;鎿嶄綔絎﹂噸鍐欏墠闈㈢殑鏌ヨ錛?/p>
mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
]]>