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
Ïà¹ØÎĵµ£º
SQL code
SQL ServerÊý¾Ýµ¼Èëµ¼³ö¹¤¾ßBCPÏê½â
BCPÊÇSQL ServerÖиºÔðµ¼Èëµ¼³öÊý¾ÝµÄÒ»¸öÃüÁîÐй¤¾ß£¬ËüÊÇ»ùÓÚDB
-
LibraryµÄ£¬²¢ÇÒÄÜÒÔ²¢Ðеķ½Ê½¸ßЧµØµ¼Èëµ¼³ö´óÅúÁ¿µÄÊý¾Ý¡£BCP¿ÉÒÔ½«Êý¾Ý¿âµÄ±í»òÊÓͼֱ½Óµ¼³ö£¬Ò²ÄÜͨ¹ýSELECT fromÓï¾ä¶Ô±í»òÊÓͼ½øÐйýÂ˺󵼳ö¡£ÔÚµ¼Èëµ¼³öÊý¾Ýʱ£¬¿ÉÒÔʹÓÃĬÈÏÖµ»òÊÇʹÓÃÒ»¸ö¸ ......
select * from pet;
insert into pet values('Liujingwei','Liuchao','cat','f','1984-04-18',null);
UPDATE pet set birth='1989-08-31' WHERE name='Slim';
select * from pet WHERE birth>'1998-1-1';
SELECT * from pet WHEREselect * from pet;
insert into pet values('Liujingwei','Liuchao','cat','f','198 ......
ÔÚʹÓÃNHibernate¹ý³ÌÖо³£»áʹÓõ½¸´ÔÓµÄsql²éѯ£¬µ«ÊÇʹÓÃhqlÓֱȽÏÂé·³µÄÇé¿öÏ£¬ÎÒÃÇÍùÍù¶¼»áÏëµ½²ÉÓÃÔʼµÄsqlÀ´Ö´ÐС£µ«ÊÇÈçºÎÀûÓÃNHibernateÀ´Ö´ÐÐsqlÄØ£¿ÎÊÌâÀ´ÁË£¬ÔÚNHibernateÖÐÒ²ÓÐAdoTemplateµÄ·½·¨¿ÉÒÔÖ´ÐÐsqlµÄ£¬µ«ÊÇÕâÀïÒª½éÉܵÄÊÇÁíÍâÒ»ÖÖ·½·¨£ºCreateSQLQuery¡£ÒÔϲ¿·ÖÀý×ÓÔ´×ÔÓÚÍøÂç¡£
ʵÀýÒ»£¨Ô´×ÔÓÚ ......
Ò»¡¢±í½á¹¹²éѯ
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Óï¾äÀ´½éÉÜÊý¾Ý¿â£¬Êý¾Ý±í£¬ÊÓͼµÈµÈ¡£µ±ÎÒÃÇÔÚʹÓòéѯ²éѯ²Ù×÷ʱÕâЩsqlÓï¾ä¶¼ÊǷdz£ÓÐÓõġ£ËäÈ»ÔÚsql server¶ÔÏóä¯ÀÀÆ÷ÖÐÎÒÃÇÒ²¿ÉÒÔ»ñµÃÕâЩÓï¾ä£¬µ«ÊÇÈç¹ûÎÒÃÇдÕâЩÓï¾äʱÎÒÃÇ¿ÉÒÔ½«Ëü×Ô¶¨Òå¡£Õâ¾ÍÒâζ×ÅÎÒÃÇ¿ÉÒÔ¸øÓè×Ô¼ºµÄÐèÇóÀ´¹ýÂ˽á¹û¡£
sqlÓï¾äÁбí
ÈçºÎÁоÙsql serverµ±Ç°Á ......