Access¡¢SQLServer¡¢Oracle³£¼ûSQLÓï¾äÓ¦ÓÃÇø±ð
Access¡¢SQLServer¡¢Oracle³£¼ûSQLÓï¾äÓ¦ÓÃÇø±ð
¹Ø¾¢ËÉ PMP
Èç¹ûÒª¼æÈÝAccess¡¢SQL Server¡¢OracleÈý¸öÊý¾Ý¿â°æ±¾£»ÎÒÃÇÔÚ±àдSQLÓï¾äµÄ¹ý³ÌÖУ¬¾¡Á¿Ê¹ÓÃһЩͨÓõÄÓï¾ä£¬µ«»¹ÊÇÓÐЩ·½ÃæÓÐÐ©Çø±ð±ÜÃâ²»ÁË£¬ÏÖ¼òµ¥×ܽáһϡ£
ÒÔÏÂA´ú±íAccess,S´ú±íSQL Server,O´ú±íOracle
1¡¢È¡µ±Ç°ÏµÍ³Ê±¼ä
A:Select Now()
S:Select Getdate()
O:Select Sysdate from Dual
¡¡¡¡
2¡¢Á¬½Ó×Ö·û´®
A:&
S:+
O:||
¡¡¡¡
3¡¢×Ö·û´®º¯Êý(½ØÈ¡×Ö·û´®¡¢´óд¡¢Ð¡Ð´¡¢²éÕÒ×Ö·û´®Î»ÖÃ)
A:Mid¡¢UCase¡¢LCase¡¢InStr
S:SubString¡¢Upper¡¢Lower¡¢CharIndex
O:SubStr¡¢Upper¡¢Lower¡¢InStr
4¡¢ÅжÏȡֵÓï¾ä
A:IIF
S:Case When Then
O:Decode
¡¡¡¡
5¡¢Ê±¼ä×Ö¶Î
A:ÒªÓÃ#ºÅ·Ö¸ô£¬»òÕßת»»ÎªÈÕÆÚ
¡¡¡¡SELECT * from Books WHERE RegDate = #2007-5-1# »ò
¡¡¡¡SELECT * from Books WHERE RegDate = CDate(’2007-5-1′)
S:¿ÉÒÔÖ±½ÓÓÃ''
¡¡¡¡SELECT * from Books WHERE RegDate = ‘2007-5-1′
O:To_Date
¡¡¡¡SELECT * from Books WHERE RegDate=To_Date('2007-05-01','yyyy-mm-dd')
6¡¢Êý¾ÝÀàÐÍת»»
A:CStr,CInt,CDate
S:Convert,CAST
O:To_Char,To_Number,To_Date
¡¡¡¡
7¡¢Inser Into …..Select …from Óï¾ä
¡¡¡¡Insert Into a(ii,jj,kk) (Select ii,jj,kk from b)
¡¡¡¡ÕâÑùµÄÓï¾ä£¬ÔÚSQL ServerÒÔ¼°OracleÖж¼²»»áÓÐ´í£¬µ«ÔÚAccessÖлᱨ:INSERT INTO Óï¾äµÄÓï·¨´íÎó¡£AccessÓ¦¸Ã°ÑºóÃæSelectÓï¾äµÄÀ¨ºÅÈ¥µô£¬ÈçÏÂ:
¡¡¡¡Insert Into a(ii,jj,kk) Select ii,jj,kk from b
¡¡¡¡Í¬Ê±ÕâÑùдÔÚSQL ServerºÍOracleÖж¼²»»áÓдí
¡¡¡¡
8¡¢¹Ø¼ü×Ö
¡¡¡¡ÓÐЩ×ÖĸÔÚAccess,SQL ServerÖÐÊǹؼü×Ö,ÐèÒªÓÃ[ ]½ç¶¨¿É½â¾öÎÊÌâ,ÀýÈç:at,name
¡¡¡¡
9¡¢±ðÃû
¡¡¡¡SQL ServerºÍOracleÖÐ×Ö¶ÎÆð±ðÃûʱas¿ÉÒÔÊ¡ÂÔ£¬Access²»ÄÜ
¡¡¡¡
10¡¢±íÃûǰ׺
¡¡¡¡Select *,1 from A
¡¡¡¡ÕâÑùµÄÓï¾äÔÚAccess,SQL ServerÖв»»áÓÐ´í£¬µ«ÔÚOracleÖÐÊDz»Ðе쬱ØÐëҪд³ÉÕâÑù:
¡¡¡¡Select A.*,1 from A
¡¡¡¡
11¡¢Èç¹ûÓï¾äÖÐÓÐ:(ðºÅ),ÀýÈçÓÐʱ¼ä×ֶεIJéѯ£ºCalcDate=#2008-05-01 11:00:00#,ÔÚAccessÖÐÒ»°ã»á³öÏÖÈçÏ´íÎ󣺲»Õý³£µØ¶¨Òå²ÎÊý¶ÔÏó¡£ÌṩÁ˲»Ò»Ö»ò²»ÍêÕûµÄÐÅÏ¢¡£½«ÏàÓ¦
QueryµÄParamCheck ÉèΪFalse¼´¿É¡£
¡¡¡¡
12¡¢AccessÖеÄÂß¼ÖµÔÚ¿âÖÐΪ-1ºÍ0,¶øSQL ServerÖÐΪ1ºÍ0,ËùÒÔдBoolField = 1ÕâÑùµÄÓï¾äÓмæÈÝÐÔÎÊÌ⣬
Ïà¹ØÎĵµ£º
1.¶àwhere£¬ÉÙhaving
whereÓÃÀ´¹ýÂËÐУ¬havingÓÃÀ´¹ýÂË×é
2.¶àunion all£¬ÉÙunion
unionɾ³ýÁËÖØ¸´µÄÐУ¬Òò´Ë»¨·ÑÁËһЩʱ¼ä
3.¶àExists£¬ÉÙin
ExistsÖ»¼ì²é´æÔÚÐÔ£¬ÐÔÄܱÈinÇ¿ºÜ¶à£¬ÓÐЩÅóÓѲ»»áÓÃExists£¬¾Í¾Ù¸öÀý×Ó
Àý£¬ÏëÒªµÃµ½Óе绰ºÅÂëµÄÈ˵Ļù±¾ÐÅÏ¢£¬table2ÓÐÈßÓàÐÅÏ¢
select * from table1;--(id,n ......
1 ½«Êý¾Ý¿âTESTÍêÈ«µ¼³ö,Óû§Ãûsystem ÃÜÂëmanager µ¼³öµ½D:daochu.dmpÖÐ
exp system/manager@TEST file=d:daochu.dmp full=y
2 ½«Êý¾Ý¿âÖÐsystemÓû§ÓësysÓû§µÄ±íµ¼³ö
exp system/manager@TEST file=d:daochu.dmp owner=(system,sys)
3 ½«Êý¾Ý¿âÖеıíinner_notify¡¢notify_ ......
ORACLEÅàѵ(OCA)ÈÏÖ¤½éÉÜ
Oracle10g Certified Associate (OCA) Oracle ÈÏ֤רԱ¡£
¿¼ÊԳɼ¨Í¨¹ýÄÜ»ñµÃOracle¹«Ë¾ÎªÄú°ä·¢µÄÈ«ÇòÈÏÖ¤µÄÓ¢ÎÄOCAÖ¤Êé¡£OCAÓÉOracle¹«Ë¾³öÌâ¡£
¸ÃÖ¤Êé¿É×÷Ϊ¸÷ÆóÊÂÒµµ¥Î»Êý¾Ý¿â¹ÜÀíÈËÔ±ÉϸڵÄÒÀ¾Ý¡£
ĿǰÒѳÉΪ¸÷IT¹«Ë¾¼°Ïà¹ØÆóÒµÕùÏྺƸµÄÊý¾Ý¿â¹ÜÀíά»¤È˲ţ¬ÊÇÊý¾Ý¿âά»¤¹ÜÀíÈËÔ ......
֨װ²Ù×÷ϵͳºó,Èç¹ûÊý¾ÝÎļþ,¿ØÖÆÎļþ,ÈÕÖ¾Îļþ¶¼ÍêºÃµÄ»°(ÔÚitpub¿´¹ýºÜ¶àÈËÌá¹ýÕâ¸ö»°Ìâ,¶àÊýÈ˶¼Êǽ«Õâ3¸öÎļþ·ÅÔÚͬһĿ¼oradata),Ö»ÐèÖØÐ°²×°oracle(¸ú֨װ²Ù×÷ϵͳǰͬ°æ±¾)µ½ÔĿ¼ºó,ÖØ½¨ÊµÀý·þÎñºÍÃÜÂëÎļþ,ÅäÖÃÒ»ÏÂlistenerºÍtns¼´¿ÉÕý³£Æô¶¯Êý¾Ý¿â.
¹ý³ÌÈçÏÂ(¼ÙÉèÔʵÀýÃûΪorcl,°æ±¾Îª9i):
1.½«ÔÀ´µÄor ......
Ò»¸öÀý×Ó£º´Ótszl±íÖвé³öÊý¾Ý£¬¸ù¾Ýcs×ֶεÄÖµ¾ö¶¨ÍùBOOK_SERIAL±íÖвåÈ뼸ÐÐÊý¾Ý¡£
declare @num int
declare @id varchar(60)
declare @classbm varchar(60)
set @classBm='101'
declare id cursor for select id,cs from [tszl]
open id
fetch next from id into @id,@num
while @@fetch_status!=-1
&nb ......