SQL¿ç·þÎñÆ÷²éѯ
[SQL SERVER] ¿ç·þÎñÆ÷²éѯ
[SQL SERVER] ¿ç·þÎñÆ÷²éѯ
·½·¨Ò»:
ÓÃOPENDATASOURCE
ÏÂÃæÊǸö¿çSQLServer²éѯµÄʾÀý
Select TableA.*,TableB.* from OPENDATASOURCE(
'SQLOLEDB',
'Data Source=ServerA;User ID=UserID;Password=Password'
).databaseAName.dbo.TableA
Left Join
OPENDATASOURCE(
'SQLOLEDB',
'Data Source=ServerB;User ID=UserID;Password=Password'
).databaseBName.dbo.TableB On TableA.key=TableB.key
ÏÂÃæÊǸö²éѯµÄʾÀý£¬Ëüͨ¹ýÓÃÓÚ Jet µÄ OLE DB Ìṩ³ÌÐò²éѯ Excel µç×Ó±í¸ñ¡£
SELECT *
from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
·½·¨¶þ(Ò²¿ÉÒÔÔÚÆóÒµ¹ÜÀíÆ÷ÀïÌí¼Ó linkServer):
sp_addlinkedserver
´´½¨Ò»¸öÁ´½ÓµÄ·þÎñÆ÷£¬Ê¹ÆäÔÊÐí¶Ô·Ö²¼Ê½µÄ¡¢Õë¶Ô OLE DB Êý¾ÝÔ´µÄÒìÀà²éѯ½øÐзÃÎÊ¡£ÔÚʹÓà sp_addlinkedserver ´´½¨Á´½ÓµÄ·þÎñÆ÷Ö®ºó£¬´Ë·þÎñÆ÷¾Í¿ÉÒÔÖ´Ðзֲ¼Ê½²éѯ¡£Èç¹ûÁ´½Ó·þÎñÆ÷¶¨ÒåΪ Microsoft? SQL Server?£¬Ôò¿ÉÖ´ÐÐÔ¶³Ì´æ´¢¹ý³Ì¡£
Óï·¨
sp_addlinkedserver [ @server = ] 'server'
[ , [ @srvproduct = ] 'product_name' ]
[ , [ @provider = ] 'provider_name' ]
[ , [ @datasrc = ] 'data_source' ]
[ , [ @location = ] 'location' ]
[ , [ @provstr = ] 'provider_string' ]
[ , [ @catalog = ] 'catalog' ]
ȨÏÞ
Ö´ÐÐÐí¿ÉȨÏÞĬÈÏÊÚÓè sysadmin ºÍ setupadmin ¹Ì¶¨·þÎñÆ÷½ÇÉ«µÄ³ÉÔ±¡£
¼òµ¥Ê¾Àý:
//´´½¨linkServer
exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','·þÎñÆ÷Ãû'
//怫linkServer
exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'Óû§Ãû','ÃÜÂë'
//²éѯlinkServerµÄÊý¾Ý¿âDataBaseAµÄ±íTableA
Select * from srv_lnk.DataBaseA.dbo.TableA
//List the tables in the
Ïà¹ØÎĵµ£º
SQLÓï¾äÓÅ»¯¼¼Êõ·ÖÎö
²Ù×÷·ûÓÅ»¯
IN ²Ù×÷·û
ÓÃINд³öÀ´µÄSQLµÄÓŵãÊDZȽÏÈÝÒ×д¼°ÇåÎúÒ׶®£¬Õâ±È½ÏÊʺÏÏÖ´úÈí¼þ¿ª·¢µÄ·ç¸ñ¡£
µ«ÊÇÓÃINµÄSQLÐÔÄÜ×ÜÊDZȽϵ͵ģ¬´ÓORACLEÖ´ÐеIJ½ÖèÀ´·ÖÎöÓÃINµÄSQLÓë²»ÓÃINµÄSQLÓÐÒÔÏÂÇø±ð£º
¡¡¡¡¡¡ORACLEÊÔͼ½«Æäת»»³É¶à¸ö±íµÄÁ¬½Ó£¬Èç¹ûת»»²»³É¹¦ÔòÏÈÖ´ÐÐINÀïÃæµÄ×Ó²éѯ£¬ ......
DDL£ºÊý¾Ý¿â¶¨ÒåÓïÑÔ£¨create ,alter,modify£©¿É×Ô¶¯Ìá½»¡£
DML£ºÊý¾Ý¿â²Ù×÷ÓïÑÔ£¨insert, update,alter£©Ó¦ÉèSET AUTOCOMMIT ON
DCL£ºÊý¾Ý¿â¿ØÖÆÓïÑÔ£¨grant,deny,revoke£©
create table table_name (column_name type [not null] [constraint constraint_definition default XXXXXX] )
[primary key (column_name,col ......
SQLÓï¾äÏÈǰдµÄʱºò£¬ºÜÈÝÒ×°ÑһЩÌØÊâµÄÓ÷¨Íü¼Ç£¬ÎÒÌØ´ËÕûÀíÁËÒ»ÏÂSQLÓï¾ä²Ù×÷¡£
Ò»¡¢»ù´¡
1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
CREATE DATABASE database-name
2¡¢ËµÃ÷£ºÉ¾³ýÊý¾Ý¿â
drop database dbname
3¡¢ËµÃ÷£º±¸·Ýsql server
--- ´´½¨ ±¸·ÝÊý¾ÝµÄ device
USE master
EXEC sp_addumpdevice 'd ......
1. SQL Server 2000Êý¾Ý¿âÁ¬½Ó×Ö·û´®
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
Connection conn = java.sql.DriverManager.getConnection("jdbc:microsoft:sqlserver://Êý¾Ý¿â·þÎñÆ÷Ãû»òµØÖ·:1433; DatabaseName=Êý¾Ý¿âÃû", "Óû§Ãû", "ÃÜÂë");
Ëù ......
SQL SERVER 2005 ͬ²½¸´ÖƼ¼Êõ
ÒÔÏÂʵÏÖ¸´ÖƲ½Ö裨ÒÔ¿ìÕÕ¸´ÖÆΪÀý£©
ÔËÐÐƽ̨SQL SERVER 2005
Ò»¡¢×¼±¸¹¤×÷£º
1£®½¨Á¢Ò»¸ö WINDOWS Óû§£¬ÉèÖÃΪ¹ÜÀíԱȨÏÞ£¬²¢ÉèÖÃÃÜÂ룬×÷Ϊ·¢²¼¿ìÕÕÎļþµÄÓÐЧ·ÃÎÊÓû§¡£
2£®ÔÚSQL SERVERÏÂʵÏÖ·¢²¼·þÎñÆ÷ºÍ¶©ÔÄ·þÎñÆ÷µÄͨÐÅÕý³££¨¼´¿ÉÒÔ»¥·Ã£©¡£´ò¿ª1433¶Ë¿Ú£¬ÔÚ·À»ðǽÖÐÉèÌØÀ ......