Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

SQL Server³£Óú¯Êý

ÔÚÊý¾Ý¿â²éѯÊý¾Ýʱ£¬ÎÒÃǾ­³£Ê¹ÓÃһЩº¯Êý£¬Ê¹ÎÒÃǵIJéѯ¸ü¼Ó·½±ã¿ì½Ý£¬ÏÂÃæ¾Í°ÑSQL ServerÖÐÎÒÃdz£Óõļ¸¸öº¯Êý¸øÁоٳöÀ´£¬¹©²Î¿¼¡£
1.×Ö·û´®º¯ÊýÓû§¿ØÖÆ·µ»Ø¸øÓû§µÄ×Ö·û´®£¬ÕâЩ¹¦ÄܽöÓÃÓÚ×Ö·ûÐÍÊý¾Ý¡£
2.ÈÕÆÚº¯ÊýÓÃÓÚ²Ù×÷ÈÕÆÚÖµ£¬ÎÒÃDz»ÄÜÖ±½Ó¶ÔÈÕÆÚÔËÓÃÊýѧº¯Êý¡£
3.Êýѧº¯ÊýÓÃÓÚ¶ÔÊýÖµ½øÐдúÊýÔËËã¡£
4.ϵͳº¯ÊýÓÃÀ´»ñÈ¡ÓйØSQL ServerÖжÔÏóºÍÉèÖõÄϵͳÐÅÏ¢¡£
 
 
º¯ Êý Ãû
Ãè    Êö
¾Ù    Àý
²¿·Ö³£ÓõÄ×Ö·û´®º¯Êý
Charindex
ÓÃÀ´Ñ°ÕÒÒ»¸öÖ¸¶¨µÄ×Ö·û´®ÔÚÁíÒ»¸ö×Ö·û´®ÖеÄÆðʼλÖÃ
Select charindex(‘accp’,’my accp course’,1) 
·µ»Ø£º4
Len
·µ»Ø¸øËüµÄ×Ö·û´®³¤¶È
Select len(‘SQL server ¿Î³Ì’)
·µ»Ø£º12
Upper(lower)
°Ñ´«µÝ¸øËüµÄ×Ö·û´®×ª»»Îª´óд
Select upper(‘SQL server ¿Î³Ì’)
·µ»Ø£ºSQL SERVER ¿Î³Ì
Ltrim(rtrim)
Çå³ý×Ö·û×ó±ßµÄ¿Õ¸ñ
Select ltrim(‘ ×Ö·û ’)
·µ»Ø£º×Ö·û (ºóÃæµÄ¿Õ¸ñ±£Áô)
Right
´Ó×Ö·ûÓұ߷µ»ØÖ¸¶¨ÊýÄ¿µÄ×Ö·û
Select right(‘1234567890’,5)
·µ»Ø£º67890
Replace
Ìæ»»Ò»¸ö×Ö·û´®µÄ×Ö·û
Select replace(‘ÎÒ°®Äã’,’Äã’,’妳’)
·µ»Ø£ºÎÒ°®妳
Stuff
ÔÚÒ»¸ö×Ö·û´®ÖÐ,ɾ³ýÖ¸¶¨³¤¶ÈµÄ×Ö·û,²¢ÔÚ¸ÃλÖòåÈëÒ»¸öеÄ×Ö·û´®
Select stuff(‘ABCDEFG’,2,3,’À¶À¶µÄÌìÀ¶À¶µÄÃÎ’)
·µ»Ø£ºAÀ¶À¶µÄÌìÀ¶À¶µÄÃÎG
²¿·Ö³£ÓõÄÈÕÆÚº¯Êý
Getdate
È¡µÃµ±Ç°µÄϵͳÈÕÆÚ
Select getdate()
·µ»Ø£ºµ±Ç°µÄÈÕÆÚ,ʱ¼ä
Dateadd
½«Ö¸¶¨µÄÊýÖµÌí¼Óµ½Ö¸¶¨µÄÈÕÆÚ²¿·ÖºóµÄÈÕÆÚ
Select dateadd(mm,4,’2009-6-24’)
·µ»Ø£ºÒÔµ±Ç°ÈÕÆÚ¸ñʽ·µ»Ø2009-10-24
Datediff
Á½¸öÈÕÆÚÖ®¼äµÄÖ¸¶¨ÈÕÆÚ²¿·ÖµÄÇø±ð
Select datediff(mm,’2009-5-24’,’2009-10-24’)
·µ»Ø£º5
Datename
ÈÕÆÚÖÐÖ¸¶¨ÈÕÆÚ²¿·ÖµÄ×Ö·û´®ÐÎʽ
Select datename(dw,2009-10-24)
·µ»Ø£ºÐÇÆÚÁù
Datepart
ÈÕÆÚÖÐÖ¸¶¨ÈÕÆÚ²¿·ÖµÄÕûÊýÐÎʽ
Select datepart(day,’2009-10-24’)
·µ»Ø£º24
²¿·Ö³£ÓõÄÊýѧº¯Êý
Abs
È¡ÊýÖµ±í´ïʽµÄ¾ø¶ÔÖµ
Select abs(-60)
·µ»Ø£º60
Ceiling
È¡´óÓÚ»òµÈÓÚÖ¸¶¨ÊýÖµ¡¢±í´ïÖ®µÄ×îСÕûÊý
Select ceiling(19.5)
·µ»Ø£º20
Floor
ȡСÓÚ»òµÈÓÚÖ¸¶¨±í´ïʽµÄ×î´óÕ


Ïà¹ØÎĵµ£º

.netÁ¬½ÓÊý¾Ý¿â£¬Ö´ÐÐsql½Å±¾

1¡¢¶¨ÒåÖ¸Á£ºusing System.Data.SqlClient;
2¡¢¶¨ÒåsqlÁ¬½Ó£º SqlConnection conn= new SqlConnection("server=(local);database=colorring;uid=sa;pwd=;");
3¡¢´ò¿ªsqlÁ¬½Ó£º conn.Open();
4¡¢¶¨ÒåsqlÓï¾ä£ºstring sqlstr = "insert into test values (123321)";
5¡¢×é×°sqlÓï¾äºÍÁ¬½Ó£ºSqlCommand cmd = new SqlCo ......

SQL Server±¨´íChinese_PRC_90_CI_AS

SQL Server 2005±¨ÁËÕâô¸ö´í£ºCannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "Chinese_PRC_90_CI_AS" in the like operation.
ºó²éÖ¤£¬ÕâÊÇÓÉÓÚÁ½¸ö×ö±È½ÏµÄÁеÄÅÅÐò·½Ê½²»Í¬£¬ÖÐÎÄÓкܶàÖÖÅÅÐò·½Ê½£¬¶ÔÓ¦ÈçÏ£º
ÖÐÎÄ£¨Ïã¸ÛÌرðÐÐÕþÇø£©
Chinese_Hong_Kong_Stroke_90_CI_AS
ÖÐÎÄ£¨Ïã¸ÛÌرðÐ ......

SQLÓï¾ä²éѯÊÇ·ñΪ¿Õ =null¼°null

  a                  b                             c&nb ......

.net³ÌÐòÔ±ËùÐèÕÆÎÕµÄsql£¬»ù±¾¼¼ÇÉ(1)

1¡¢SessionÓÐʲôÖØ´óBUG£¬Î¢ÈíÌá³öÁËʲô·½·¨¼ÓÒÔ½â¾ö£¿
  ´ð£ºÊÇiisÖÐÓÉÓÚÓнø³Ì»ØÊÕ»úÖÆ£¬ÏµÍ³·±Ã¦µÄ»°Session»á¶ªÊ§£¬¿ÉÒÔÓÃSate server»òSQL ServerÊý¾Ý
¿âµÄ·½Ê½´æ´¢Session²»¹ýÕâÖÖ·½Ê½±È½ÏÂý£¬¶øÇÒÎÞ·¨²¶»ñSessionµÄENDʼþ¡£
2.²úÉúÒ»¸öintÊý×飬³¤¶ÈΪ100£¬²¢ÏòÆäÖÐËæ»ú²åÈë1-100£¬²¢ÇÒ²»ÄÜÖظ´¡£
C# ......

sql´æ´¢¹ý³Ì·ÖÒ³

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[pagination]
@tblName varchar(255), -- ±íÃû
@strGetFields varchar(1000), -- ÐèÒª·µ»ØµÄÁÐ
@fldName varchar(255), -- ÅÅÐòµÄ×Ö¶ÎÃû
@PageSize int, -- Ò³³ß´ç
@PageIndex int, -- Ò³Âë
@OrderType bit, -- ÉèÖÃÅÅÐòÀàÐÍ, ·Ç 0 Öµ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ