»ùÓÚmssql °ÙÍò¼¶ Êý¾Ý ²éѯ ÓÅ»¯ ¼¼ÇÉÈýÊ®Ôò
1.¶Ô²éѯ½øÐÐÓÅ»¯£¬Ó¦¾¡Á¿±ÜÃâÈ«±íɨÃ裬Ê×ÏÈÓ¦¿¼ÂÇÔÚ where ¼° order by Éæ¼°µÄÁÐÉϽ¨Á¢Ë÷Òý¡£
2.Ó¦¾¡Á¿±ÜÃâÔÚ where ×Ó¾äÖжÔ×ֶνøÐÐ null ÖµÅжϣ¬·ñÔò½«µ¼ÖÂÒýÇæ·ÅÆúʹÓÃË÷Òý¶ø½øÐÐÈ«±íɨÃ裬È磺
select id from t where num is null
¿ÉÒÔÔÚnumÉÏÉèÖÃĬÈÏÖµ0£¬È·±£±íÖÐnumÁÐûÓÐnullÖµ£¬È»ºóÕâÑù²éѯ£º
select id from t where num=0
3.Ó¦¾¡Á¿±ÜÃâÔÚ where ×Ó¾äÖÐʹÓÃ!=»ò<>²Ù×÷·û£¬·ñÔò½«ÒýÇæ·ÅÆúʹÓÃË÷Òý¶ø½øÐÐÈ«±íɨÃè¡£
4.Ó¦¾¡Á¿±ÜÃâÔÚ where ×Ó¾äÖÐʹÓà or À´Á¬½ÓÌõ¼þ£¬·ñÔò½«µ¼ÖÂÒýÇæ·ÅÆúʹÓÃË÷Òý¶ø½øÐÐÈ«±íɨÃ裬È磺
select id from t where num=10 or num=20
¿ÉÒÔÕâÑù²éѯ£º
select id from t where num=10
union all
select id from t where num=20
5.in ºÍ not in Ò²ÒªÉ÷Ó㬷ñÔò»áµ¼ÖÂÈ«±íɨÃ裬È磺
select id from t where num in(1,2,3)
¶ÔÓÚÁ¬ÐøµÄÊýÖµ£¬ÄÜÓà between ¾Í²»ÒªÓà in ÁË£º
select id from t where num between 1 and 3
6.ÏÂÃæµÄ²éѯҲ½«µ¼ÖÂÈ«±íɨÃ裺
select id from t where name like '%abc%'
ÈôÒªÌá¸ßЧÂÊ£¬¿ÉÒÔ¿¼ÂÇÈ«ÎļìË÷¡£
7.Èç¹ûÔÚ where ×Ó¾äÖÐʹÓòÎÊý£¬Ò²»áµ¼ÖÂÈ«±íɨÃè¡£ÒòΪSQLÖ»ÓÐÔÚÔËÐÐʱ²Å»á½âÎö¾Ö²¿±äÁ¿£¬µ«ÓÅ»¯³ÌÐò²»Äܽ«·ÃÎʼƻ®µÄÑ¡ÔñÍƳٵ½ÔËÐÐʱ£»Ëü±ØÐëÔÚ±àÒëʱ½øÐÐÑ¡Ôñ¡£È»¶ø£¬Èç¹ûÔÚ±àÒëʱ½¨Á¢·ÃÎʼƻ®£¬±äÁ¿µÄÖµ»¹ÊÇδ֪µÄ£¬Òò¶øÎÞ·¨×÷ΪË÷ÒýÑ¡ÔñµÄÊäÈëÏî¡£ÈçÏÂÃæÓï¾ä½«½øÐÐÈ«±íɨÃ裺
select id from t where num=@num
¿ÉÒÔ¸ÄΪǿÖƲéѯʹÓÃË÷Òý£º
select id from t with(index(Ë÷ÒýÃû)) where num=@num
8.Ó¦¾¡Á¿±ÜÃâÔÚ where ×Ó¾äÖжÔ×ֶνøÐбí´ïʽ²Ù×÷£¬Õ⽫µ¼ÖÂÒýÇæ·ÅÆúʹÓÃË÷Òý¶ø½øÐÐÈ«±íɨÃè¡£È磺
select id from t where num/2=100
Ó¦¸ÄΪ:
select id from t where num=100*2
9.Ó¦¾¡Á¿±ÜÃâÔÚwhere×Ó¾äÖжÔ×ֶνøÐк¯Êý²Ù×÷£¬Õ⽫µ¼ÖÂÒýÇæ·ÅÆúʹÓÃË÷Òý¶ø½øÐÐÈ«±íɨÃè¡£È磺
select id from t where substring(name,1,3)='abc'--nameÒÔabc¿ªÍ·µÄid
select id from t where datediff(day,createdate,'2005-11-30')=0--‘2005-11-30’Éú³ÉµÄid
Ó¦¸ÄΪ:
select id from t where name like 'abc%'
select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'
10.²»ÒªÔÚ where ×Ó¾äÖеē=”×ó±ß½øÐк¯Êý¡¢ËãÊõÔËËã»òÆäËû±í´ïʽÔËË㣬·ñÔòϵͳ½«¿ÉÄÜÎÞ·¨ÕýȷʹÓÃË÷Òý¡£
11.ÔÚʹÓÃË÷Òý×Ö¶Î×÷ΪÌ
Ïà¹ØÎĵµ£º
create function dbo.regexReplace
(
@source varchar(5000), --Ô×Ö·û´®
@regexp varchar(1000), - ......
ÏÂÃæÊÇORACLEÐÐÁÐת»»Í¨Óô洢¹ý³Ì£º
1¡¢
Function Fun_Split_Str(p_Str Varchar2, p_Division Varchar2, p_Seq Int)
Return Varchar2 Is
v_First Int;
v_Last Int;
Begin
If p_Seq < 1 Then
&nbs ......
xtype ´ú±íÀàÐÍ
C = CHECK Ô¼Êø
D = ĬÈÏÖµ»ò DEFAULT Ô¼Êø
F = FOREIGN KEY Ô¼Êø
L = ÈÕÖ¾
FN = ±êÁ¿º¯Êý
IF = ÄÚǶ±íº¯Êý
P = ´æ´¢¹ý³Ì
PK = PRIMARY KEY Ô¼Êø£¨ÀàÐÍÊÇ K£©
RF = ¸´ÖÆɸѡ´æ´¢¹ý³Ì
S = ϵͳ±í
TF = ±íº¯Êý
TR = ´¥· ......
µ±Êý¾Ý·þÎñÆ÷ºÍWeb·þÎñÆ÷²¿ÊðÔÚ²»Í¬µÄ·þÎñÆ÷ÉÏʱ£¬»áÓõ½·Ö²¼Ê½ÊÂÎñ£¬ÐèÒª¶ÔÁ½¸ö·þÎñÆ÷µÄMSDTC½øÐÐÅäÖá£
´ò¿ª“¹ÜÀí¹¤¾ß¨D¨D×é¼þ·þÎñ”£¬ÒÔ´Ë´ò¿ª“×é¼þ·þÎñ¨D¨D¼ÆËã»ú”£¬ÔÚ“ÎҵĵçÄÔ”Éϵã»÷ÓÒ¼ü¡£ÔÚMSDTCÑ¡ÏÖУ¬µã»÷“°²È«ÅäÖÔ°´Å¥¡£ ......