SQL LIKEµÄÓ÷¨
LIKE ÊÇÁíÒ»¸öÔÚ WHERE ×Ó¾äÖлáÓõ½µÄÖ¸Áî¡£»ù±¾ÉÏ£¬LIKE ÄÜÈÃÎÒÃÇÒÀ¾ÝÒ»¸öÌ×ʽ (pattern) À´ÕÒ³öÎÒÃÇÒªµÄ×ÊÁÏ¡£Ïà¶ÔÀ´Ëµ£¬ÔÚÔËÓà IN µÄʱºò£¬ÎÒÃÇÍêÈ«µØÖªµÀÎÒÃÇÐèÒªµÄÌõ¼þ£»ÔÚÔËÓà BETWEEN µÄʱºò£¬ÎÒÃÇÔòÊÇÁгöÒ»¸ö·¶Î§¡£ LIKE µÄÓï·¨ÈçÏ£º
SELECT "À¸Î»Ãû"
from "±í¸ñÃû"
WHERE "À¸Î»Ãû" LIKE {Ì×ʽ}
{Ì×ʽ} ¾³£°üÀ¨Ò°¿¨ (wildcard). ÒÔÏÂÊǼ¸¸öÀý×Ó£º
'A_Z': ËùÓÐÒÔ 'A' ÆðÍ·£¬ÁíÒ»¸öÈκÎÖµµÄ×ÖÔ£¬ÇÒÒÔ 'Z' Ϊ½áβµÄ×Ö´®¡£ 'ABZ' ºÍ 'A2Z' ¶¼·ûºÏÕâÒ»¸öģʽ£¬¶ø 'AKKZ' ²¢²»·ûºÏ (ÒòΪÔÚ A ºÍ Z Ö®¼äÓÐÁ½¸ö×ÖÔ£¬¶ø²»ÊÇÒ»¸ö×ÖÔ)¡£
'ABC%': ËùÓÐÒÔ 'ABC' ÆðÍ·µÄ×Ö´®¡£¾ÙÀýÀ´Ëµ£¬'ABCD' ºÍ 'ABCABC' ¶¼·ûºÏÕâ¸öÌ×ʽ¡£
'%XYZ': ËùÓÐÒÔ 'XYZ' ½áβµÄ×Ö´®¡£¾ÙÀýÀ´Ëµ£¬'WXYZ' ºÍ 'ZZXYZ' ¶¼·ûºÏÕâ¸öÌ×ʽ¡£
'%AN%': ËùÓк¬ÓÐ 'AN' Õâ¸öÌ×ʽµÄ×Ö´®¡£¾ÙÀýÀ´Ëµ£¬ 'LOS ANGELES' ºÍ 'SAN FRANCISCO' ¶¼·ûºÏÕâ¸öÌ×ʽ¡£
ÎÒÃǽ«ÒÔÉÏ×îºóÒ»¸öÀý×ÓÓÃÔÚÎÒÃÇµÄ Store_Information ±í¸ñÉÏ:
Store_Information ±í¸ñ
store_name
Sales
Date
LOS ANGELES
$1500
Jan-05-1999
SAN DIEGO
$250
Jan-07-1999
SAN FRANCISCO
$300
Jan-08-1999
BOSTON
$700
Jan-08-1999
ÎÒ們¾Í´òÈ룬
SELECT *
from Store_Information
WHERE store_name LIKE '%AN%'
結¹û:
store_name
Sales
Date
LOS ANGELES
$1500
Jan-05-1999
SAN FRANCISCO
$300
Jan-08-1999
SAN DIEGO
$250
Jan-07-1999
Ïà¹ØÎĵµ£º
ÏÖÔÚ·ÖÒ³·½·¨´ó¶à¼¯ÖÐÔÚselect top/not in/Óαê/row_number£¬¶øselect top·ÖÒ³(ÔÚÕâ»ù´¡ÉÏ»¹Óжþ·Ö·¨)·½·¨Ëƺõ¸üÊÜ´ó¼Ò»¶Ó£¬ÕâÆªÎÄÕ²¢²»´òËãÈ¥ÌÖÂÛÊÇ·ñͨÓõÄÎÊÌ⣬±¾×ÅʵÓõÄÔÔò£¬»¨ÁËһЩʱ¼äÈ¥²âÊÔrow_number()·ÖÒ³µÄÐÔÄÜ£¬¸Ð¾õ²¢²»ÏñÒ»²¿·ÖÈËËù˵µÄÄÇô¼¦Àߣ¬ÓÉÓÚ½Ó´¥Èí¼þ¿ª·¢²ÅÊ®¸öÔ£¬·½·½ÃæÃæµÄ¶«Î÷¶¼ÒªÑ§ ......
³£Óú¯Êý
select replicate('a1',3)--a1µÄÖµÖØ¸´Èý´Î
½á¹ûΪ£ºa1a1a1
select len('222')
--¼ÆËã×Ö·ûµÄ¸öÊý½á¹ûΪ
select datalength('222')
--¼ÆËã×Ö½ÚÊý£¬½á¹ûΪ
select datalength(N'222')
-- unicodeÀàÐÍ£¬Õ¼Óøö×Ö½Ú£¬½á¹ûΪ
select substring('abcde',1,3)
-- ¸ñʽ£ºsubstr ......
Èç¹ûÄãÕýÔÚ¸ºÔðÒ»¸ö»ùÓÚSQL ServerµÄÏîÄ¿£¬»òÕßÄã¸Õ¸Õ½Ó´¥SQL Server£¬Äã¶¼ÓпÉÄÜÒªÃæÁÙһЩÊý¾Ý¿âÐÔÄܵÄÎÊÌ⣬ÕâÆªÎÄÕ»áΪÄãÌṩһЩÓÐÓõÄÖ¸µ¼£¨ÆäÖдó¶àÊýÒ²¿ÉÒÔÓÃÓÚÆäËüµÄDBMS£©¡£
ÔÚÕâÀÎÒ²»´òËã½éÉÜʹÓÃSQL ServerµÄÇÏÃÅ£¬Ò²²»ÄÜÌṩһ¸ö°üÖΰٲ¡µÄ·½°¸£¬ÎÒËù×öµÄÊÇ×ܽáһЩ¾Ñé----¹ØÓÚÈçºÎÐγÉÒ»¸öºÃµÄÉè¼Æ¡£Õ ......
[Õª]ÔÚSQLÖÐɾ³ýÖØ¸´¼Ç¼µÄ¶àÖÖ·½·¨
±¾ÎÄ×ܽáÁËһЩɾ³ýÖØ¸´¼Ç¼µÄ·½·¨£¬ÔÚOracleÖУ¬¿ÉÒÔͨ¹ýΨһrowidʵÏÖɾ³ýÖØ¸´¼Ç¼£»»¹¿ÉÒÔ½¨ÁÙʱ±íÀ´ÊµÏÖ...ÕâÀïÖ»Ìáµ½ÆäÖеļ¸ÖÖ¼òµ¥ÊµÓõķ½·¨£¬Ï£Íû¿ÉÒԺʹó¼Ò·ÖÏí£¨ÒÔ±íemployeeΪÀý£©¡£
SQL> desc employee
Name   ......
IN Õâ¸öÖ¸Áî¿ÉÒÔÈÃÎÒÃÇÒÀÕÕÒ»»òÊý¸ö²»Á¬Ðø (discrete) µÄÖµµÄÏÞÖÆÖ®ÄÚ×¥³öÊý¾Ý¿âÖеÄÖµ£¬¶ø BETWEEN ÔòÊÇÈÃÎÒÃÇ¿ÉÒÔÔËÓÃÒ»¸ö·¶Î§ (range) ÄÚ×¥³öÊý¾Ý¿âÖеÄÖµ¡£BETWEENÕâ¸ö×Ó¾äµÄÓï·¨ÈçÏ£º
SELECT "À¸Î»Ãû"
from " ±í¸ñÃû"
WHERE "À¸Î»Ãû" BETWEEN 'ÖµÒ»' AND 'Öµ¶þ'
Õ⽫ѡ³öÀ¸Î»Öµ°üº¬ÔÚÖµÒ»¼°Öµ¶þÖ®¼äµÄÿһ±Ê× ......