Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

SQLÖг£Óú¯ÊýµÄÕûÀí

¶ÔÓÚsqlÖеĺ¯Êý¿ÉνÊǶàµÄ²»Ê¤Ã¶¾Ù£¬±¾ÎÄ´Ó³£Óú¯ÊýµÄ½Ç¶È¶ÔÆäº¯Êý½øÐÐ×ܽ᣺1¡¢ÈÕÆÚºÍʱ¼äº¯Êý2¡¢×Ö·û´®º¯Êý3¡¢ÏµÍ³º¯ÊýÁ÷³Ì¿ØÖÆÓï¾ä
1¡¢  ÈÕÆÚºÍʱ¼äº¯Êý
¶ÔÓÚÈÕÆÚº¯ÊýÎÒÃÇ¿ÉÒÔ·ÖΪ2СÀà½øÐзÖÎö´¦Àí£¬
A¡¢ ÈÕÆÚµÄÕûÌå´¦Àíº¯Êý£¬¾ßÌåµÄº¬ÒåºÍÓï·¨ÈçÏÂËùʾ£º
DATEADD(datepart,number,date)
µÚÒ»¸ö²ÎÊý˵Ã÷ÒªÌí¼ÓµÄÈÕÆÚÀàÐÍ£¬µÚ¶þ¸ö²ÎÊýÊÇÖ¸Ìí¼ÓÀàÐ͵ÄÊýÁ¿£¬µÚÈý¸ö²ÎÊýÊÇÖ¸ËùÒªµÄ²ÎÊý¶ÔÏó
ÀýÈçÏÔʾ3Сʱ֮ǰµÄʱ¼ä
declare @OldTime datetime
set @oldTime =getdate()
select dateadd(hh,-3,@oldtime)
¶ÔÓÚµÚÒ»¸ö²ÎÊýµÄ±£Áô×ÖÈçÏÂËùʾ£º
MS    MILLISECOND
SS,S   SECOND
MI,N  MINUTE
HH    HOUR
DW,W     WEEKDAY
WK,WW   WEEK
DD,D  DAY
DY,Y   DAY
DY,Y   DAY OF YEAR
MM,N  MONTH
QQ,Q  QUARTER
YY,YYYY  YEAR
 
B¡¢ ±È½ÏÈÕÆÚµÄ²»Í¬
DETEDIFF(DATEPART,STARTDATE,ENDDATE)
µÚÒ»¸ö²ÎÊýͬÉÏ£¬µÚ¶þ¸ö²ÎÊýÊÇ¿ªÊ¼µÄʱ¼ä£¬µÚ¶þ¸ö²ÎÊýÊǽáÊøµÄʱ¼ä
C¡¢ È·¶¨ÐÇÆÚ¼¸µÄº¯Êý
DATENAME(DATEPART,DATETOINSPECT)
2¡¢  ÓÃÓÚ»ñȡʱ¼äºÍ²¿·Öʱ¼äµÄÁ½¸öº¯Êý
A¡¢ DATEPART()ÓÃÓÚ»ñÈ¡²¿·Öʱ¼äµÄº¯Êý
B¡¢ GETDATE()ÓÃÓÚ»ñÈ¡Èͮ򵀼¯Êý
µÚ¶þÀàÊÇ×Ö·û´®º¯Êý
1¡¢  µ¥¸ö×Ö·ûµÄº¯Êý£¬ÓëASCIIÂëµÄÏ໥ת»¯
ASCII() ºÍCHAR()Á½¸öº¯Êý
2¡¢  ×Ö·û´®¸ñʽµÄÏ໥ת»¯º¯Êý
LOWER()  LTRIM()
UPPER()   RTRIM()
3¡¢  ×Ö·ûµÄ»ñÈ¡º¯Êý
LEFT(STR,LEN)
RIGHT(STR,LEN)
SUBSTRING(ORIGINAL,START,LEN)
4¡¢  ¸ñʽµÄÏ໥ת»¯º¯Êý
Str() ½«ÊýÖµÀàÐÍת»¯Îª¿É±ä³¤¶ÈµÄ×Ö·û´®
Cast()
Convert(type,original)
µÚÈýÀàÊÇsqlµÄÅжϺ¯Êý
ISDATE()  ISNULL
µÚËÄÀàÊÇÊý¾ÝÁ÷³ÌÓï¾ä
Case when…then…else…end


Ïà¹ØÎĵµ£º

NHibernateÖ´ÐÐԭʼSQL´úÂëµÄ·½·¨Ð¡½á

ÔÚʹÓÃNHibernate¹ý³ÌÖо­³£»áʹÓõ½¸´ÔÓµÄsql²éѯ£¬µ«ÊÇʹÓÃhqlÓֱȽÏÂé·³µÄÇé¿öÏ£¬ÎÒÃÇÍùÍù¶¼»áÏëµ½²ÉÓÃԭʼµÄsqlÀ´Ö´ÐС£µ«ÊÇÈçºÎÀûÓÃNHibernateÀ´Ö´ÐÐsqlÄØ£¿ÎÊÌâÀ´ÁË£¬ÔÚNHibernateÖÐÒ²ÓÐAdoTemplateµÄ·½·¨¿ÉÒÔÖ´ÐÐsqlµÄ£¬µ«ÊÇÕâÀïÒª½éÉܵÄÊÇÁíÍâÒ»ÖÖ·½·¨£ºCreateSQLQuery¡£ÒÔϲ¿·ÖÀý×ÓÔ´×ÔÓÚÍøÂç¡£
ʵÀýÒ»£¨Ô´×ÔÓÚ ......

sql²éѯ±í½á¹¹£¬¹ý³Ì£¬ÊÓͼ£¬Ö÷¼ü£¬Íâ¼ü£¬Ô¼Êø

 Ò»¡¢±í½á¹¹²éѯ
SELECT TOP (100) PERCENT a.name AS zdm,COLUMNPROPERTY(a.id, a.name, 'IsIdentity') AS bs ,
CASE WHEN EXISTS (SELECT 1 from dbo.sysindexes si INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
 AND si.indid = sik.indid INNER JOIN dbo.syscolumns sc ON sc.id = sik.id AND sc. ......

SQL²éѯÓï¾äʹÓÃ

Ò»¡¢¼òµ¥²éѯ
¡¡¡¡ ¼òµ¥µÄTransact-SQL²éѯֻ°üÀ¨Ñ¡ÔñÁÐ±í¡¢from×Ó¾äºÍWHERE×Ӿ䡣
ËüÃÇ·Ö±ð˵Ã÷Ëù²éѯÁС¢²éѯµÄ
±í»òÊÓͼ¡¢ÒÔ¼°ËÑË÷Ìõ¼þµÈ¡£
ÀýÈ磬ÏÂÃæµÄÓï¾ä²éѯtesttable±íÖÐÐÕÃûΪ“ÕÅÈý”µÄnickname×ֶκÍemail×ֶΡ£
SELECT nickname,email
from testtable WHERE name='ÕÅÈý'
(Ò»)Ñ¡ÔñÁбí
¡ ......

sql È¥¿Õ¸ñÓëÁ¬½ÓÌØÊâ×Ö·û

ÔÚ·Ö×éÅÅÐò¹ý³ÌÖÐÐèҪн¨ÅÅÐòÁУ¬°´Ìõ¼þ:(ÎïÁÏ·ÖÀà+¿Í»§Ãû³Æ) ½øÐзÖ×é»ã×Ü È»ºó¾Í¸ù¾Ý¸ÃÌõ¼þ½øÐÐÅÅÐò
1.ÐèҪƴ´ÕÁ½ÁÐ ×÷ΪÅÅÐòÁÐ,ÈçÐè¼ÓÈëÌØÊâ×Ö·û.¾Í»á±¨´í.
   ÒòΪ±¾Éí¸Ä×ֶξÍÊÇvarcharÀàÐÍ ÐèÒª½«charÀàÐÍ'_' ת»»³Évarchar
2.Æ´´Õ¹ý³ÌÖÐÓпոñÐèҪȥ¿Õ¸ñ
 e.g£ºselect (trim(ÎïÁÏ·ÖÀà)+to_char( ......

sql²éÕÒij¸ö×Ö·û´®µÚN´Î³öÏÖµÄλÖõĺ¯Êý(תÌû)

if exists(select 1 from sysobjects where name='char_index')
drop function char_index
create function char_index(@string varchar(8000),@char varchar(10),@index smallint)
--@string:´ý²éÕÒ×Ö·û´®£¬@index:²éÕÒλÖÃ
returns smallint
as
begin
  declare
  @i tinyint,--µ±Ç°ÕÒµ½µÚ@i¸ö
  ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ