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¡¢ ±È½ÏÈÕÆڵIJ»Í¬
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
Ïà¹ØÎĵµ£º
ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
¡¡¡¡SQL·ÖÀࣺ
¡¡¡¡DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
¡¡¡¡DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
¡¡¡¡DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
¡¡¡¡Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
¡¡¡¡1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
......
SQL code
SQL ServerÊý¾Ýµ¼Èëµ¼³ö¹¤¾ßBCPÏê½â
BCPÊÇSQL ServerÖиºÔðµ¼Èëµ¼³öÊý¾ÝµÄÒ»¸öÃüÁîÐй¤¾ß£¬ËüÊÇ»ùÓÚDB
-
LibraryµÄ£¬²¢ÇÒÄÜÒÔ²¢Ðеķ½Ê½¸ßЧµØµ¼Èëµ¼³ö´óÅúÁ¿µÄÊý¾Ý¡£BCP¿ÉÒÔ½«Êý¾Ý¿âµÄ±í»òÊÓͼֱ½Óµ¼³ö£¬Ò²ÄÜͨ¹ýSELECT fromÓï¾ä¶Ô±í»òÊÓͼ½øÐйýÂ˺󵼳ö¡£ÔÚµ¼Èëµ¼³öÊý¾Ýʱ£¬¿ÉÒÔʹÓÃĬÈÏÖµ»òÊÇʹÓÃÒ»¸ö¸ ......
Óŵã:×ֶνÏÉÙ£¬ÓÐÔöɾ¸Ä²é¹¦ÄÜ£¬²»¹ý²éѯ̫Áýͳ¡£
ȱµã:
1.²»ËãÊÇÔÚºÜÕýµÄÎÞÏÞ·ÖÀà,ClassPathÕâ¸ö×ֶζ¨ÒåÏÞÖÆ¡£
2.Ö÷¼üCLASSID²»ÊÇ×ÔÔöµÄ£¬Ê¹ÓÃCODESMITHÅúÁ¿Éú³É¶à²ã¼Ü¹¹´úÂëÖлᵼÖ³ö´í¡£
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ArticleClass]') and OBJECTPROPERTY(id, N'IsUse ......
Óï¾äÐÎʽ£º¡¡ SELECTTOP10*
fromTestTable
WHERE(ID>
¡¡¡¡¡¡¡¡¡¡(SELECTMAX(id)
¡¡¡¡¡¡¡¡from(SELECTTOP20id
¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡fromTestTable
¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡ORDERBYid)AST))
ORDERBYID
SELECTTOPÒ³´óС*
fromTestTable
WHERE(ID>
¡¡¡¡¡¡¡¡¡¡(SELECTMAX(id)
¡¡¡¡¡¡¡¡from(SELECTTOPÒ³´óС*Ò³Êýid
¡¡¡¡¡ ......
ÕÆÎÕSQLËÄÌõ×î»ù±¾µÄÊý¾Ý²Ù×÷Óï¾ä£ºInsert£¬Select£¬UpdateºÍDelete¡£
¡¡¡¡ Á·ÕÆÎÕSQLÊÇÊý¾Ý¿âÓû§µÄ±¦¹ó²Æ ¸»¡£ÔÚ±¾ÎÄÖУ¬ÎÒÃǽ«Òýµ¼ÄãÕÆÎÕËÄÌõ×î»ù±¾µÄÊý¾Ý²Ù×÷Óï¾ä—SQLµÄºËÐŦÄÜ—À´ÒÀ´Î½éÉܱȽϲÙ×÷·û¡¢Ñ¡Ôñ¶ÏÑÔÒÔ¼°ÈýÖµÂß¼¡£µ±ÄãÍê³ÉÕâЩѧϰºó£¬ÏÔÈ»ÄãÒѾ¿ªÊ¼ËãÊǾ«Í¨SQLÁË¡£
¡¡¡¡ÔÚÎÒÃÇ¿ªÊ¼Ö®Ç°£¬ÏÈ ......