SQL Server Ë÷Òý½á¹¹¼°ÆäʹÓã¨Èý£©
ʵÏÖСÊý¾ÝÁ¿ºÍº£Á¿Êý¾ÝµÄͨÓ÷ÖÒ³ÏÔʾ´æ´¢¹ý³Ì
¡¡¡¡½¨Á¢Ò»¸ö Web Ó¦Ó㬷ÖÒ³ä¯ÀÀ¹¦Äܱز»¿ÉÉÙ¡£Õâ¸öÎÊÌâÊÇÊý¾Ý¿â´¦ÀíÖÐÊ®·Ö³£¼ûµÄÎÊÌâ¡£¾µäµÄÊý¾Ý·ÖÒ³·½·¨ÊÇ:ADO ¼Í¼¼¯·ÖÒ³·¨£¬Ò²¾ÍÊÇÀûÓÃADO×Ô´øµÄ·ÖÒ³¹¦ÄÜ£¨ÀûÓÃÓα꣩À´ÊµÏÖ·ÖÒ³¡£µ«ÕâÖÖ·ÖÒ³·½·¨½öÊÊÓÃÓÚ½ÏСÊý¾ÝÁ¿µÄÇéÐΣ¬ÒòΪÓα걾ÉíÓÐȱµã£ºÓαêÊÇ´æ·ÅÔÚÄÚ´æÖУ¬ºÜ·ÑÄÚ´æ¡£ÓαêÒ»½¨Á¢£¬¾Í½«Ïà¹ØµÄ¼Ç¼Ëøס£¬Ö±µ½È¡ÏûÓαꡣÓαêÌṩÁ˶ÔÌض¨¼¯ºÏÖÐÖðÐÐɨÃèµÄÊֶΣ¬Ò»°ãʹÓÃÓαêÀ´ÖðÐбéÀúÊý¾Ý£¬¸ù¾ÝÈ¡³öÊý¾ÝÌõ¼þµÄ²»Í¬½øÐв»Í¬µÄ²Ù×÷¡£¶ø¶ÔÓÚ¶à±íºÍ´ó±íÖж¨ÒåµÄÓα꣨´óµÄÊý¾Ý¼¯ºÏ£©Ñ»·ºÜÈÝÒ×ʹ³ÌÐò½øÈëÒ»¸öÂþ³¤µÄµÈ´ýÉõÖÁËÀ»ú¡£
¡¡¡¡¸üÖØÒªµÄÊÇ£¬¶ÔÓڷdz£´óµÄÊý¾ÝÄ£ÐͶøÑÔ£¬·ÖÒ³¼ìË÷ʱ£¬Èç¹û°´ÕÕ´«Í³µÄÿ´Î¶¼¼ÓÔØÕû¸öÊý¾ÝÔ´µÄ·½·¨ÊǷdz£ÀË·Ñ×ÊÔ´µÄ¡£ÏÖÔÚÁ÷ÐеķÖÒ³·½·¨Ò»°ãÊǼìË÷Ò³Ãæ´óСµÄ¿éÇøµÄÊý¾Ý£¬¶ø·Ç¼ìË÷ËùÓеÄÊý¾Ý£¬È»ºóµ¥²½Ö´Ðе±Ç°ÐС£
¡¡¡¡×îÔç½ÏºÃµØʵÏÖÕâÖÖ¸ù¾ÝÒ³Ãæ´óСºÍÒ³ÂëÀ´ÌáÈ¡Êý¾ÝµÄ·½·¨´ó¸Å¾ÍÊÇ“¶íÂÞ˹´æ´¢¹ý³Ì”¡£Õâ¸ö´æ´¢¹ý³ÌÓÃÁËÓα꣬ÓÉÓÚÓαêµÄ¾ÖÏÞÐÔ£¬ËùÒÔÕâ¸ö·½·¨²¢Ã»Óеõ½´ó¼ÒµÄÆÕ±éÈÏ¿É¡£
¡¡¡¡ºóÀ´£¬ÍøÉÏÓÐÈ˸ÄÔìÁË´Ë´æ´¢¹ý³Ì£¬ÏÂÃæµÄ´æ´¢¹ý³Ì¾ÍÊǽáºÏÎÒÃǵİ칫×Ô¶¯»¯ÊµÀýдµÄ·ÖÒ³´æ´¢¹ý³Ì£º
CREATE procedure pagination1
(@pagesize int, --Ò³Ãæ´óС£¬Èçÿҳ´æ´¢20Ìõ¼Ç¼
@pageindex int --µ±Ç°Ò³Âë
)
as
set nocount on
begin
declare @indextable table(id int identity(1,1),nid int) --¶¨Òå±í±äÁ¿
declare @PageLowerBound int --¶¨Òå´ËÒ³µÄµ×Âë
declare @PageUpperBound int --¶¨Òå´ËÒ³µÄ¶¥Âë
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
insert into @indextable(nid) select gid from TGongwen
¡¡¡¡¡¡¡¡¡¡¡¡where fariqi >dateadd(day,-365,getdate()) order by fariqi desc
select O.gid,O.mid,O.title,O.fadanwei,O.fariqi from TGongwen O,@indextable t
where O.gid=t.nid and t.id>@PageLowerBound
and t.id<=@PageUpperBound order by t.id
end
set nocount off
¡¡¡¡ÒÔÉÏ´æ´¢¹ý³ÌÔËÓÃÁËsql s
Ïà¹ØÎĵµ£º
1.Óòéѯ·ÖÎöÆ÷ÖØÃüÃû
exec sp_rename ÔÃû³Æ, ÐÂÃû³Æ
sp_rename ÊÇ SQL Server™ ×Ô´øµÄÒ»¸ö´æ´¢¹ý³Ì£¬ÓÃÓÚ¸ü¸Äµ±Ç°Êý¾Ý¿âÖÐÓû§´´½¨µÄ¶ÔÏóµÄÃû³Æ£¬Èç±íÃû¡¢ÁÐ±í¡¢Ë÷ÒýÃûµÈ¡£
2.ÓÃÆóÒµ¹ÜÀíÆ÷ÖØÃüÃû
ÔÚ±íÉϵãÓÒ¼ü->“ËùÓÐÈÎÎñ”->“¹ÜÀí´¥·¢Æ÷”£¬Ñ¡ÖÐËùÒªÖØÃüÃûµÄ´¥·¢Æ÷£¬Ð޸Ĵ¥·¢Æ÷Óï ......
--µÚÒ»²½
--ÔÚmaster¿âÖн¨Á¢Ò»¸ö±¸·ÝÊý¾Ý¿âµÄ´æ´¢¹ý³Ì.
USE master
GO
CREATE PROC p
@db_name sysname, --Êý¾Ý¿âÃû
@bk_path NVARCHAR(1024) --±¸·ÝÎļþµÄ·¾¶
A ......
1.²éÕÒÖظ´Êý¾Ý±íµÄidÒÔ¼°Öظ´Êý¾ÝµÄÌõÊý
select max(id) as nid,count(id) as Öظ´ÌõÊý from tableName
group by linkname Having Count(*) > 1
2.²éÕÒÖظ´Êý¾Ý±íµÄÖ÷¼ü
select max(id) as nid from tableName
group by linkname Having Count(id) > 1
3.ɾ³ýÖظ´µÄÊý¾Ý
delete from table ......
¹ØÓÚSQL Prompt
µÄÆƽâÎÄÕÂÔ°×ÓÀïÒѾÓв»ÉÙÈË·¢¹ýÁË¡£Æƽâ˼·ÎÒÒ²²»ÏëÖظ´£¬²»ÖªµÀµÄ¿ÉÒԲο¼£ºhttp://www.cnblogs.com/jintan/archive/2008/06/16/1223282.html
¹Ù·½ÏÂÔصØÖ·ÊÇ£ºhttp://downloads.red-gate.com/SQLToolbelt.exe
½ÓÏÂÀ´ÎÒ¾Í˵˵¾ßÌåÔõô×ö°É¡£ÎÒ¾Í˵˵×î¼òµ¥×îɵ¹ÏµÄ×ö·¨£¬²»ÐèÒª¶®ILÕÕÑùÆƽ⣡
× ......
SQL Server 2005Ïà¶ÔÓÚSQL Server 2000À´Ëµ£¬ÎÞÂÛÊÇÐÔÄÜ»¹Êǹ¦Äܶ¼ÓÐÒ»¸öÏ൱´óµÄÌá¸ß,ÉõÖÁ¿ÉÒÔÓÓ¸ïÃü”À´ÐÎÈÝÕâÒ»´ÎÉý¼¶¡£SQL Server 2005ʹ SQL Server õÒÉíÓÚÆóÒµ¼¶Êý¾Ý¿âÐÐÁС£ÔÚÊý¾Ý¸ß¿ÉÓÃÐÔ·½Ã棬SQL Server 2005ΪÓû§ÌṩÁËÊý¾Ý¾µÏñ¡¢¸´ÖÆ¡¢¹ÊÕÏתÒÆȺ¼¯¡¢ÈÕÖ¾´«Ë͹¦ÄÜ¡£±¾ÎÄÏò¶ÁÕß¼òµ¥½é½áSQL Serve ......