SQL¶ÁÈ¡EXCEL
Ö±½ÓÔÚSQL²éѯ·ÖÎöÆ÷ÖжÁÈ¡EXCELÎļþÐèҪʹÓõ½OPENDATASOURCE¡£
µ«ÊÇʹÓÃËü֮ǰÐèÒª½øÐÐÅäÖÃһϡ£¼ÇµÃÈçÏÂÅäÖÃÊDZØÐëµÄ£º
1¡¢Ö´ÐÐÕâÁ½¸ö´æ´¢¹ý³Ì£º
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
ËüµÄ×÷Óãº
µÚÒ»¸öÊÇ£ºÊÇ·ñÖ§³Ö¸ß¼¶Ñ¡ÏîµÄ£¬1Ϊ֧³Ö0Ϊ²»Ö§³Ö¡£
µÚ¶þ¸öÊÇ£ºÊÇ·ñÖ§³Ö·Ö²¼Ê½²éѯ£¬
1Ϊ֧³Ö0Ϊ²»Ö§³Ö¡£
¶øÇÒÒ»¶¨ÊÇÏÈÖ§³Ö¸ß¼¶Ñ¡ÏÔÙ¿ÉÒÔÉèÖ÷ֲ¼Ê½²éѯ£¬ÒòΪ·Ö²¼Ê½²éѯ±¾Éí¾ÍÊÇ
Ò»¸ö¸ß¼¶Ñ¡ÏîÀ´µÄ¡£
2¡¢Ê¹ÓÃOPENDATASOURCE£¬ËüÓÐÁ½ÖÖÓï·¨
£¨1£©SELECT * from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=D:\TempExcelData\Exl_Test_01.xls;Extended Properties=EXCEL 5.0')...[Student$]
£¨2£©select *
from Openrowset('Microsoft.Jet.OLEDB.4.0','EXCEL 8.0;HDR=YES;User id=admin;Password=;IMEX=1;
DATABASE=D:\TempExcelData\Exl_Test_01.xls', Student$)
3¡¢ÓÃÍêºóÒª¹Ø±ÕµÚÒ»²½´ò¿ªµÄ¶«Î÷
exec sp_configure 'show advanced options',0
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
°´Õմ˲½²»Ò»¶¨¿ÉÒÔ²éѯµ½ÄãEXCELµÄÊý¾Ý£¬¿ÉÄÜ»¹»áÓÐÆäËüµÄ´íÎ󣬱ÈÈç˵ȨÏÞ
²»×ã¹»Òý·¢ÆäËüµÄÎÊÌâ°¡£¬ÒªÔÚÍøÉÏÕÒ¶àһϣ¬¾ÍÈçÎÒÅäÖõÄʱºò£¬ÓõÄÊÇSA½øÈ¥µÄ£¬
µ«ÊÇSA²»ÊÇSYSADMINISTRATORÕâÒ»×飬Ҫ¼Ó½øÈ¥ºó²ÅÓÐȨÏÞ£¬²ÅÄÜ˳Àû²éµ½Êý¾Ý
Ïà¹ØÎĵµ£º
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 ......
1. SQL Server 2000Êý¾Ý¿âÁ¬½Ó×Ö·û´®
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
Connection conn = java.sql.DriverManager.getConnection("jdbc:microsoft:sqlserver://Êý¾Ý¿â·þÎñÆ÷Ãû»òµØÖ·:1433; DatabaseName=Êý¾Ý¿âÃû", "Óû§Ãû", "ÃÜÂë");
Ëù ......
1ÏÞÖÆ SQL Server·þÎñµÄȨÏÞ
¡¡¡¡
¡¡¡¡SQL Server 2000 ºÍ SQL Server Agent ÊÇ×÷Ϊ Windows ·þÎñÔËÐеġ£Ã¿¸ö·þÎñ±ØÐëÓëÒ»¸ö Windows ÕÊ»§Ïà¹ØÁª£¬²¢´ÓÕâ¸öÕÊ»§ÖÐÑÜÉú³ö°²È«ÐÔÉÏÏÂÎÄ¡£SQL ServerÔÊÐísa µÇ¼µÄÓû§£¨ÓÐʱҲ°üÀ¨ÆäËûÓû§£©À´·ÃÎʲÙ×÷ÏµÍ³ÌØÐÔ¡£ÕâЩ²Ù×÷ϵͳµ÷ÓÃÊÇÓÉÓµÓзþÎñÆ÷½ø³ÌµÄÕÊ»§µÄ°²È«ÐÔÉÏÏÂÎÄÀ´´ ......
Èç¹ûÏë²éÕÒ“_cs”½áβµÄµÄÕË»§
select * from [user] where loginname like '%_cs'ÊDz»Ðеģ¬_ ±»ÈÏΪÊÇÈÎÒâµÄ×Ö·û£¬
ËùÒÔÐèҪתÒå×Ö·û£¬ÓÐÁ½ÖÖд·¨£º
select * from [user] where loginname like '%[_]cs'
select * from [user] where loginname like '%/_cs' escape'/'
ͨÅä·ûº¬Òå
%
°üº¬Áã¸ö»ò¸ü¶à×Ö· ......