SQL Server SQLÓï¾äµ¼Èëµ¼³ö´óÈ«
/******* µ¼³öµ½excel
EXEC master..xp_cmdshell ’bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""’
/*********** µ¼ÈëExcel
SELECT *
from OpenDataSource( ’Microsoft.Jet.OLEDB.4.0’,
’Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0’)...xactions
SELECT cast(cast(¿ÆÄ¿±àºÅ as numeric(10,2)) as nvarchar(255))+’¡¡’ ת»»ºóµÄ±ðÃû
from OpenDataSource( ’Microsoft.Jet.OLEDB.4.0’,
’Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0’)...xactions
/** µ¼ÈëÎı¾Îļþ
EXEC master..xp_cmdshell ’bcp "dbname..tablename" in c:\DT.txt -c -Sservername -Usa -Ppassword’
/** µ¼³öÎı¾Îļþ
EXEC master..xp_cmdshell ’bcp "dbname..tablename" out c:\DT.txt -c -Sservername -Usa -Ppassword’
»ò
EXEC master..xp_cmdshell ’bcp "Select * from dbname..tablename" queryout c:\DT.txt -c -Sservername -Usa -Ppassword’
µ¼³öµ½TXTÎı¾£¬ÓöººÅ·Ö¿ª
exec master..xp_cmdshell ’bcp "¿âÃû..±íÃû" out "d:\tt.txt" -c -t ,-U sa -P password’
BULK INSERT ¿âÃû..±íÃû
from ’c:\test.txt’
WITH (
FIELDTERMINATOR = ’;’,
ROWTERMINATOR = ’\n’
)
--/* dBase IVÎļþ
select * from
OPENROWSET(’MICROSOFT.JET.OLEDB.4.0’
,’dBase IV;HDR=NO;IMEX=2;DATABASE=C:\’,’select * from [¿Í»§×ÊÁÏ4.dbf]’)
--*/
--/* dBase IIIÎļþ
select * from
OPENROWSET(’MICROSOFT.JET.OLEDB.4.0’
,’dBase III;HDR=NO;IMEX=2;DATABASE=C:\’,’select * from [¿Í»§×ÊÁÏ3.dbf]’)
--*/
--/* FoxPro Êý¾Ý¿â
select * from openrowset(’MSDASQL’,
’Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\’,
’select * from [aa.DBF]’)
--*/
/**************µ¼ÈëDBFÎļþ****************/
select * from openrowse
Ïà¹ØÎĵµ£º
ÎÒÃÇÔÚ¿ª·¢¹ý³ÌÖУ¬¾³£Óöµ½ÕâÑùÎÊÌ⣬¾ÍÊÇÒªÇó¶¨ÆÚ½øÐÐÊý¾Ý¿âµÄ¼ì²é£¬Èç¹û·¢ÏÖÌض¨Êý¾Ý£¬ÄÇô¾ÍÒª½øÐÐijÏî²Ù×÷£¬Õâ¸öÐèÇóÄØ£¬¿ÉÒÔÀûÓÃWindowsµÄ¼Æ»®ÈÎÎñ£¬¶¨ÆÚÖ´ÐÐijһ¸öÓ¦ÓóÌÐò£¬È¥¼ìË÷Êý¾Ý£»Ò²¿ÉÒÔÈóÌÐò×Ô¼º¿ØÖÆ¡£ÆäʵSQL Server×Ô¼ºÒ²¿ÉÒÔ´´½¨¼Æ»®ÈÎÎñ£¬¶¨ÆÚ½øÐÐÖ´ÐС£Èç¹ûÊý¾Ý¿â·þÎñÆ÷ÔÊÐí£¬¿ÉÒÔ¿¼ÂDzÉÓÃÕâÖÖ·½Ê½¡£
......
1¡¢ÎļþºÍÎļþ×éµÄº¬ÒåÓë¹Øϵ
ÿ¸öÊý¾Ý¿âÓÐÒ»¸öÖ÷Êý¾ÝÎļþ.ºÍÈô¸É¸ö´ÓÎļþ¡£ÎļþÊÇÊý¾Ý¿âµÄÎïÀíÌåÏÖ¡£ Îļþ×é¿ÉÒÔ°üÀ¨·Ö²¼ÔÚ¶à¸öÂß¼·ÖÇøµÄÎļþ£¬ÊµÏÖ¸ºÔØƽºâ¡£Îļþ×éÔÊÐí¶ÔÎļþ½øÐзÖ×飬ÒÔ±ãÓÚ¹ÜÀíºÍÊý¾ÝµÄ·ÖÅ䣯·ÅÖá£ÀýÈ磬¿ÉÒÔ·Ö±ðÔÚÈý¸öÓ²ÅÌÇý¶¯Æ÷ÉÏ´´½¨Èý¸öÎļþ£¨Data1.ndf¡¢Data2.ndf ºÍ Data3.ndf£©£¬²¢½«ÕâÈý¸ ......
¡¡¡¡µ±Ö´ÐÐÒ»ÌõDMLÓï¾äºó£¬DMLÓï¾äµÄ½á¹û±£´æÔÚËĸöÓαêÊôÐÔÖУ¬ÕâЩÊôÐÔÓÃÓÚ¿ØÖƳÌÐòÁ÷³Ì»òÕßÁ˽â³ÌÐòµÄ״̬¡£µ±ÔËÐÐDMLÓï¾äʱ£¬PL/SQL´ò¿ªÒ»¸öÄÚ½¨Óα겢´¦Àí½á¹û£¬ÓαêÊÇά»¤²éѯ½á¹ûµÄÄÚ´æÖеÄÒ»¸öÇøÓò£¬ÓαêÔÚÔËÐÐDMLÓï¾äʱ´ò¿ª£¬Íê³Éºó¹Ø±Õ¡£ÒþʽÓαêֻʹÓÃSQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNTÈý¸öÊôÐÔ.SQL%FOUND,SQ ......
1.ÈçºÎ½«Ò»¸öSQL Server±íµÄ½á¹¹¸´ÖƵ½ÁíÒ»¸öн¨µÄ±íÖÐ
select * into бí from ¾É±í where 0 = 1
2.ÐÞ¸Ä×Ö¶ÎĬÈÏÖµ
alter table ±íÃû add default ĬÈÏÖµ for ×Ö¶ÎÃû
3. È¡µÃÊý¾Ý¿â½á¹¹
SELECT name from sysdatabases
4.Ôö¼ÓÁÐ
......
±ê×¼µÄ SQL µÄ½âÎö˳ÐòΪ:
(
1
).
from
×Ó¾ä, ×é×°À´×Ô²»Í¬Êý¾ÝÔ´µÄÊý¾Ý
(
2
).
WHERE
×Ó¾ä, »ùÓÚÖ¸¶¨µÄÌõ¼þ¶Ô¼Ç¼½øÐÐɸѡ
(
3
).
GROUP
BY
×Ó¾ä, ½«Êý¾Ý»®·ÖΪ¶à¸ö·Ö×é
(
4
).ʹÓþۺϺ¯Êý½øÐмÆËã
(
5
).ʹÓÃ
HAVING
×Ó¾äɸѡ·Ö×é
(
6
).¼ÆËãËùÓеıí´ïʽ
......