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
Ïà¹ØÎĵµ£º
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 ......
Select CONVERT(varchar(100), GETDATE(), 23)£»
·µ»ØÐÎʽ£º2008-11-29
Select CONVERT(varchar(100), GETDATE(), 102)
·µ»ØÐÎʽ£º2008.11.29
Select CONVERT(varchar(100), GETDATE(), 101)
·µ»ØÐÎʽ£º11/29/2008
¸ü¶àÏêÇéÇë²Î¼ûÈçÏÂÁÐ±í£º
Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 ......
½¨±í£º
CREATE TABLE [DB.dbo].tableName
(Stud_id int CONSTRAINT constraintName1 not null primary key,
Name nvarchar(5) not null,
Birthday datetime,
Gender nchar(1),
Telcode char(12),
Zipcode char(6) CONSTRAINT constraintName2 CHECK(zipcode like [ ......
¼ò½é
ÔÚÕâÆªÎÄÕÂÖУ¬ÎÒÁоÙһЩsqlÓï¾äÀ´½éÉÜÊý¾Ý¿â£¬Êý¾Ý±í£¬ÊÓͼµÈµÈ¡£µ±ÎÒÃÇÔÚʹÓòéѯ²éѯ²Ù×÷ʱÕâЩsqlÓï¾ä¶¼ÊǷdz£ÓÐÓõġ£ËäÈ»ÔÚsql server¶ÔÏóä¯ÀÀÆ÷ÖÐÎÒÃÇÒ²¿ÉÒÔ»ñµÃÕâЩÓï¾ä£¬µ«ÊÇÈç¹ûÎÒÃÇдÕâЩÓï¾äʱÎÒÃÇ¿ÉÒÔ½«Ëü×Ô¶¨Òå¡£Õâ¾ÍÒâζ×ÅÎÒÃÇ¿ÉÒÔ¸øÓè×Ô¼ºµÄÐèÇóÀ´¹ýÂ˽á¹û¡£
sqlÓï¾äÁбí
ÈçºÎÁоÙsql serverµ±Ç°Á ......