SqlServerÊý¾Ý¿â×ֵ䣨תÔØ£©
ÒýÓÃ×Ôhttp://blog.163.com/xing_aixin/blog/static/3723550520088189201623/
ÍøÉÏÓкܶàSQL ServerÊý¾Ý¿â×ÖµäµÄSQLÓï¾ä£¬ÆßÁã°ËÂ䣬ÎÒÔÚ¹¤×÷ÕûÀíÁËÒ»ÏÂ˼·£¬×ܽáSQL´úÂëÈçÏ¡£Êý¾Ý¿â×Öµä°üÀ¨±í½á¹¹(·Ö2KºÍ2005)¡¢Ë÷ÒýºÍÖ÷¼ü. Íâ¼ü.Ô¼Êø.ÊÓͼ.º¯Êý.´æ´¢¹ý³Ì.´¥·¢Æ÷¡£¿ÉÒÔÔÚÆóÒµ¹ÜÀíÆ÷¡¢²éѯ·ÖÎöÆ÷Öмòµ¥Ö´ÐУ¬Ö±½ÓÁ˵±µÄ²é³öSQL2K¼°SQL2005µÄËùÓÐÊý¾Ý×ֵ䣬·½±ãÎĵµµÄ±àд£¬Ï£Íû¶Ô´ó¼ÒÓаïÖú¡£
¡¡¡¡1. SqlServer2000Êý¾Ý¿â×Öµä--±í½á¹¹.sql
SELECT TOP 100 PERCENT --a.id,
CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS ±íÃû,
CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END AS ±í˵Ã÷,
a.colorder AS ×Ö¶ÎÐòºÅ, a.name AS ×Ö¶ÎÃû, CASE WHEN COLUMNPROPERTY(a.id,
a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS ±êʶ,
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.colid = sik.colid INNER JOIN
dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'
WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS Ö÷¼ü,
b.name AS ÀàÐÍ, a.length AS ³¤¶È, COLUMNPROPERTY(a.id, a.name, 'PRECISION')
AS ¾«¶È, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS СÊýλÊý,
CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS ÔÊÐí¿Õ, ISNULL(e.text, '')
Ïà¹ØÎĵµ£º
ÏÖÏó£º
ÔÚ×°SqlServer 2000ʱ£¬ÀÏÊǵ¯³ö“ÁíÒ»¸öʵÀýÕýÔÚÔËÐДµÄ´íÎóÐÅÏ¢£¬½áÊøÁ˽ø³ÌÀïsqlµÄÏà¹ØÎļþºÍɾ³ýÒÔÇ°°²×°¹ýµÄ°²×°Ä¿Â¼ÏµÄsqlÎļþºó£¬ÖØÆôµçÄÔºó°²×°»¹ÊdzöÏÖÕâÖÖÇé¿ö¡£
´¦Àí°ì·¨£º
ÕâÖÖÇé¿öÒ»°ãÊÇSqlServer 2000жÔز»ÍêÈ«Ôì³ÉµÄ¡£
ÔÚ“¿ªÊ¼-ÔËÐДÖÐÊäÈëregeditµ÷³ö×¢²á±í£¬ÕÒµ½£ ......
sqlserverºÍoracle³£Óú¯Êý¶Ô±È
Êýѧº¯Êý
¡¡¡¡1.¾ø¶ÔÖµ
¡¡¡¡S:select abs(-1) value
¡¡¡¡O:select abs(-1) value from dual
¡¡¡¡2.È¡Õû(´ó)
¡¡¡¡S:select ceiling(-1.001) value
¡¡¡¡O:select ceil(-1.001) value from dual
¡¡¡¡3.È¡Õû£¨Ð¡£©
¡¡¡¡S:select floor(-1.001) value
¡¡¡¡O:select floor(-1.0 ......
±íA
tmpA:
id name
00 Ãû×ÖA
01 Ãû×ÖB
02 Ãû×ÖC
03 Ãû×ÖD
04 Ãû×ÖE
tmpB:
id class
02 °à¼¶A
06 & ......
ÏÂÃæÊÇÎÒÔÚSQLServerÖÐÓõıȽ϶àµÄº¯Êý£¬Ä㻹ÓÃЩʲôº¯Êý£¿»òÕ߶ÔÄĸöº¯ÊýÓбȽϺÃÓ÷¨£¿»òÕßÄã²»ÓÃÄÄЩº¯Êý£¿
LEFT ( character_expression , integer_expression )
·µ»Ø´Ó×Ö·û´®×ó±ß¿ªÊ¼Ö¸¶¨¸öÊýµÄ×Ö·û¡£
Àý:
select left('my name is viwo',4)
my n
RIGHT ( character_expression , integer_expression )
·µ» ......
SQLServerÖÐDatetimeÀàÐÍÊý¾ÝµÄ±È½Ï
2009Äê02ÔÂ20ÈÕ ÐÇÆÚÎå 16:40
/* ¶¨Ò忪ʼÈÕÆںͽáÊøÈÕÆÚ*/
declare @date_a datetime,@date_b datetime
set @date_a='2009-2-10'
set @date_b='2009-2-12'
/* ÔÚÈÕÆÚdate_a(°üº¬) ºÍdate_b(²»°üº¬) Ö®¼ä*/
select * from ut_article where createtime between @ ......