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
Ïà¹ØÎĵµ£º
1 :ÆÕͨSQLÓï¾ä¿ÉÒÔÓÃExecÖ´ÐÐ
Àý: Select * from tableName
Exec('select * from tableName')
& ......
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 [ ......
Ò»¡¢¼òµ¥²éѯ
¡¡¡¡ ¼òµ¥µÄTransact-SQL²éѯֻ°üÀ¨Ñ¡ÔñÁÐ±í¡¢from×Ó¾äºÍWHERE×Ӿ䡣
ËüÃÇ·Ö±ð˵Ã÷Ëù²éѯÁС¢²éѯµÄ
±í»òÊÓͼ¡¢ÒÔ¼°ËÑË÷Ìõ¼þµÈ¡£
ÀýÈ磬ÏÂÃæµÄÓï¾ä²éѯtesttable±íÖÐÐÕÃûΪ“ÕÅÈý”µÄnickname×ֶκÍemail×ֶΡ£
SELECT nickname,email
from testtable WHERE name='ÕÅÈý'
(Ò»)Ñ¡ÔñÁбí
¡ ......
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¸ö
  ......