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
ȡСÓÚ»òµÈÓÚÖ¸¶¨±í´ïʽµÄ×î´óÕ
Ïà¹ØÎĵµ£º
¡¡¡¡µ±Ö´ÐÐÒ»ÌõDMLÓï¾äºó£¬DMLÓï¾äµÄ½á¹û±£´æÔÚËĸöÓαêÊôÐÔÖУ¬ÕâЩÊôÐÔÓÃÓÚ¿ØÖƳÌÐòÁ÷³Ì»òÕßÁ˽â³ÌÐòµÄ״̬¡£µ±ÔËÐÐDMLÓï¾äʱ£¬PL/SQL´ò¿ªÒ»¸öÄÚ½¨Óα겢´¦Àí½á¹û£¬ÓαêÊÇά»¤²éѯ½á¹ûµÄÄÚ´æÖеÄÒ»¸öÇøÓò£¬ÓαêÔÚÔËÐÐDMLÓï¾äʱ´ò¿ª£¬Íê³Éºó¹Ø±Õ¡£ÒþʽÓαêֻʹÓÃSQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNTÈý¸öÊôÐÔ.SQL%FOUND,SQ ......
Create PROCEDURE [dbo].[ChangeObjectOwner]
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS
DECLARE @Name as NVARCHAR(128)
DECLARE @Owner as NVARCHAR(128)
DECLARE @OwnerName as NVARCHAR(128)
DECLARE curObject CURSOR FOR
select 'Name' = name,
'Owner'&n ......
http://inthirties.com:90/thread-1202-1-1.html
v$sqltext
´æ´¢µÄÊÇÍêÕûµÄSQL,SQL±»·Ö¸î
SQL> desc v$sqltext
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDRESS ......
¡¡ÎÒ¹«Ë¾ÔÚ×齨¾ÖÓòÍøʱ£¬¿¼Âǵ½ÉÌÒµÆóÒµµÄÌص㣬×Ðϸ¿¼Á¿Á˹º¡¢Ïú¡¢´æÈý´ó»·½ÚÖз¢ÉúµÄ¸÷ÖÖÊý¾Ý¼°Æä´æ´¢ÎÊÌâºó£¬Ñ¡¶¨ÁËÒÔWindows 2000 ServerΪ²Ù×÷ϵͳ£¬SQL Server 2000ΪÊý¾Ý¿âƽ̨À´´î½¨¾ÖÓòÍøµÄÓ¦ÓÃϵͳµÄÈí¼þƽ̨£¬ÒÔÍøÏßΪÔØÌ彫¹º¡¢Ïú¡¢´æµÈºËÐIJ¿ÃŵļÆËã»úͨ¹ý¾ÖÓòÍøƽ̨½ôÃܵØÁ¬½ÓÆðÀ´¡£ÕâÑù£¬¸÷¸öºËÐIJ¿ÃÅÿÌì ......