sql 2008 ¶ÔÔ¶³ÌÊý¾Ý¿âµÄ²Ù×÷
1£ºexec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '192.168.*.12'
exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, 'sa ', 'F00000'
2£º
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
3£º
--²éѯʾÀý
select * from ITSV.Êý¾Ý¿âÃû.dbo.±íÃû
--µ¼ÈëʾÀý
select * into ±í from ITSV.Êý¾Ý¿âÃû.dbo.±íÃû
--ÒÔºó²»ÔÙʹÓÃʱɾ³ýÁ´½Ó·þÎñÆ÷
exec sp_dropserver 'ITSV ', 'droplogins '
--Á¬½ÓÔ¶³Ì/¾ÖÓòÍøÊý¾Ý(openrowset/openquery/opendatasource)
--1¡¢openrowset
--²éѯʾÀý
select * from openrowset( 'SQLOLEDB ', 'sql·þÎñÆ÷Ãû '; 'Óû§Ãû '; 'ÃÜÂë ',Êý¾Ý¿âÃû.dbo.±íÃû)
--Éú³É±¾µØ±í
select * into ±í from openrowset( 'SQLOLEDB ', 'sql·þÎñÆ÷Ãû '; 'Óû§Ãû '; 'ÃÜÂë ',Êý¾Ý¿âÃû.dbo.±íÃû)
--°Ñ±¾µØ±íµ¼ÈëÔ¶³Ì±í
insert openrowset( 'SQLOLEDB ', 'sql·þÎñÆ÷Ãû '; 'Óû§Ãû '; 'ÃÜÂë ',Êý¾Ý¿âÃû.dbo.±íÃû)
select *from ±¾µØ±í
--¸üб¾µØ±í
update b
set b.ÁÐA=a.ÁÐA
from openrowset( 'SQLOLEDB ', 'sql·þÎñÆ÷Ãû '; 'Óû§Ãû '; 'ÃÜÂë ',Êý¾Ý¿âÃû.dbo.±íÃû)as a inner join ±¾µØ±í b
on a.column1=b.column1
--openqueryÓ÷¨ÐèÒª´´½¨Ò»¸öÁ¬½Ó
--Ê×ÏÈ´´½¨Ò»¸öÁ¬½Ó´´½¨Á´½Ó·þÎñÆ÷
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', 'Ô¶³Ì·þÎñÆ÷Ãû»òipµØÖ· '
--²éѯ
select *
from openquery(ITSV, 'SELECT * from Êý¾Ý¿â.dbo.±íÃû ')
--°Ñ±¾µØ±íµ¼ÈëÔ¶³Ì±í
insert openquery(ITSV, 'SELECT * from Êý¾Ý¿â.dbo.±íÃû ')
select * from ±¾µØ±í
--¸üб¾µØ±í
update b
set b.ÁÐB=a.ÁÐB
from openquery(ITSV, 'SELECT * from Êý¾Ý¿â.dbo.±íÃû ') as a
inner join ±¾µØ±í b on a.ÁÐA=b.ÁÐA
--3¡¢opendatasource/openrowset
SELECT *
from opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=µÇ½Ãû;Password=ÃÜÂë ' ).test.dbo.roy_ta
--°Ñ±¾µØ±íµ¼ÈëÔ¶³Ì±í
insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=µÇ½Ãû;Password=ÃÜÂë ').Êý¾Ý¿â.dbo.±íÃû
select * from
²»Í¬Êý¾Ý¿âÖ®¼ä¸´ÖƱíµÄÊý¾ÝµÄ·½·¨£º
µ±±íÄ¿±ê±í´æ
Ïà¹ØÎĵµ£º
ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
¡¡¡¡SQL·ÖÀࣺ
¡¡¡¡DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
¡¡¡¡DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
¡¡¡¡DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
¡¡¡¡Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
¡¡¡¡1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
......
RANK ( ) OVER ( [query_partition_clause] order_by_clause )
DENSE_RANK ( ) OVER ( [query_partition_clause] order_by_clause )
¿ÉʵÏÖ°´Ö¸¶¨µÄ×ֶηÖ×éÅÅÐò£¬¶ÔÓÚÏàͬ·Ö×é×ֶεĽá¹û¼¯½øÐÐÅÅÐò,
ÆäÖÐPARTITION BY Ϊ·Ö×é×ֶΣ¬ORDER BY Ö¸¶¨ÅÅÐò×Ö¶Î
over²»Äܵ¥¶ÀʹÓã¬ÒªºÍ·ÖÎöº¯Êý£ºrank(),dense_rank(),row_n ......
ÎÒÃÇÒª×öµ½²»µ«»áдSQL£¬»¹Òª×öµ½Ð´³öÐÔÄÜÓÅÁ¼µÄSQLÓï¾ä¡£
£¨1£©Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)£º
OracleµÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬from×Ó¾äÖÐдÔÚ×îºóµÄ±í(»ù´¡±í driving
table)½«±»×îÏÈ´¦Àí£¬ÔÚfrom×Ó¾äÖаüº¬¶à¸ö±íµÄÇé¿öÏÂ,Äã±ØÐëÑ¡Ôñ¼Ç¼ÌõÊý×îÉٵıí×÷Ϊ»ù´¡±í¡£È ......
SQLÓë¹ý³Ì»¯³ÌÐòÉè¼ÆÓïÑÔ
SQLÊÇÒ»ÖÖµäÐ͵ķǹý³Ì»¯³ÌÐòÉè¼ÆÓïÑÔ£¬ÕâÖÖÓïÑÔµÄÌصãÊÇ£º
Ö»Ö¸¶¨ÄÄЩÊý¾Ý±»²Ù×Ý£¬ÖÁÓÚ¶ÔÕâЩÊý¾ÝÒªÖ´ÐÐÄÄЩ²Ù×÷£¬ÒÔ¼°Õâ
Щ²Ù×÷ÊÇÈçºÎ
Ö´ÐÐµÄ ......
SQL ServerµÄÐÔÄÜÖ÷Ҫȡ¾öÓÚ´ÅÅÌI/OЧÂÊ£¬Ìá¸ßI/OЧÂÊijÖÖ³ÌÐòÉϾÍÒâζ×ÅÌá¸ßÐÔÄÜ¡£SQL Server 2008ÌṩÁËÊý¾ÝѹËõ¹¦ÄÜÀ´Ìá¸ß´ÅÅÌI/O¡£
Êý¾ÝѹËõÒâζ׿õСÊý¾ÝµÄÓдÅÅÌÕ¼ÓÃÁ¿£¬ËùÒÔÊý¾ÝѹËõ¿ÉÒÔÓÃÔÚ±í£¬¾Û¼¯Ë÷Òý£¬·Ç¾Û¼¯Ë÷Òý£¬ÊÓͼË÷Òý»òÊÇ·ÖÇø±í£¬·ÖÇøË÷ÒýÉÏ¡£
Êý¾ÝѹËõ¿ÉÒÔÔÚÁ½¸ö¼¶±ðÉÏʵÏÖ£ºÐ춱ðºÍÒ³¼¶±ð¡£Ò³¼¶±ðѹ ......