Oracle
Oracle è½å¨ææ主æµå¹³å°ä¸è¿è¡ï¼å
æ¬Windowsï¼ãå®å
¨æ¯æææçå·¥ä¸æ åãéç¨å®å
¨å¼æ¾çç¥ãå¯ä»¥ä½¿å®¢æ·éæ©æéåç解å³æ¹æ¡ã对å¼ååå
¨åæ¯æï¼Oracle并è¡æå¡å¨éè¿ä½¿ä¸ç»ç»ç¹å
±äº«åä¸ç°ä¸çå·¥ä½æ¥æ©å±Windows NTçè½åï¼æä¾é«å¯ç¨æ§åé«ä¼¸ç¼©æ§çç°ç解å³æ¹æ¡ãå¦æWindows NTä¸è½æ»¡è¶³éè¦ï¼ç¨æ·å¯ä»¥ææ°æ®åºç§»å°UNIXä¸ãOracleç并è¡æå¡å¨å¯¹åç§UNIXå¹³å°çé群æºå¶é½æçç¸å½é«çéæ度ãOracleè·å¾æé«è®¤è¯çº§å«çISOæ å认è¯.Oracleæ§è½æé«ï¼ä¿æå¼æ¾å¹³å°ä¸çTPC-DåTPC-Cçä¸çè®°å½Oracleå¤å±æ¬¡ç½ç»è®¡ ç®ï¼æ¯æå¤ç§å·¥ä¸æ åï¼å¯ä»¥ç¨ODBCãJDBCãOCIçç½ç»å®¢æ·è¿æ¥ã
Oracle å¨å
¼å®¹æ§ãå¯ç§»æ¤æ§ãå¯èç»æ§ãé«ç产çä¸ãå¼æ¾æ§ä¹åå¨ä¼ç¹ãOracle产åéç¨æ åSQLï¼å¹¶ç»è¿ç¾å½å½å®¶æ åææ¯æï¼NISTï¼æµè¯ãä¸ IBM SQL/DSï¼DB2ï¼INGRESï¼IDMS/Rçå
¼å®¹ããOracleç产åå¯è¿è¡äºå¾å®½èå´ç硬件ä¸æä½ç³»ç»å¹³å°ä¸ãå¯ä»¥å®è£
å¨70ç§ä»¥ä¸ ä¸åç大ãä¸ãå°åæºä¸ï¼å¯å¨VMSãDOSãUNIXãWINDOWSçå¤ç§æä½ç³»ç»ä¸å·¥ä½ãè½ä¸å¤ç§é讯ç½ç»ç¸è¿ï¼æ¯æåç§åè®®ï¼TCP/IPã DECnetãLU6.2çï¼ãæä¾äºå¤ç§å¼åå·¥å
·ï¼è½æ大çæ¹ä¾¿ç¨æ·è¿è¡è¿ä¸æ¥çå¼åãOracleè¯å¥½çå
¼å®¹æ§ãå¯ç§»æ¤æ§ãå¯è¿æ¥æ§åé«ç产çæ¯ Oracle RDBMSå
·æè¯å¥½çå¼æ¾æ§ã
Oracleä»·æ ¼æ¯æ¯è¾æè´µçãæ®è¯´ä¸å¥æ£ççOracle软件æ©å¨2006å¹´å¹´åºçæ¶åå¨å¸åºä¸çä»·æ ¼å·²ç»è¾¾å°äº6ä½æ°ãæ以å¦æä½ ç项ç®ä¸æ¯é£ç§è¶
级大ç项ç®ï¼è¿æ¯æ¾å¼Oracleå§ã
SQL Server
SQL Server æ¯ Microsoftæ¨åºä¸å¥äº§åï¼å®å
·æ使ç¨æ¹ä¾¿ãå¯ä¼¸ç¼©æ§å¥½ãä¸ç¸å
³è½¯ä»¶éæç¨åº¦é«çä¼ç¹ï¼éæ¸æ为Windowså¹³å°ä¸è¿è¡æ°æ®åºåºç¨å¼åè¾ä¸ºçæ³ç éæ©ä¹ä¸ãSQLServeræ¯ç®åæµè¡çæ°æ®åºä¹ä¸ï¼å®å·²å¹¿æ³åºç¨äºéèãä¿é©ãçµåãè¡æ¿ç®¡ççä¸æ°æ®åºæå
³çè¡ä¸ãèä¸ï¼ç±äºå
¶ææä½æ§åå好çç é¢,èµ¢å¾äºå¹¿å¤§ç¨æ·çéç,å°¤å
¶æ¯SQLServerä¸å
¶å®æ°æ®åº,å¦AccessãFoxProãExcelçæè¯å¥½çODBCæ¥å£ï¼å¯ä»¥æä¸è¿°æ°æ®åº 转æSQLServerçæ°æ®åº,å æ¤ç®åè¶æ¥è¶å¤ç读è
æ£å¨ä½¿ç¨SQLServerã
SQL Serverç±äºæ¯å¾®è½¯ç产åï¼åæçå¦æ¤å¼ºå¤§çåè½ï¼æ以ä»çå½±ååæ¯å ç§æ°æ®åºç³»ç»ä¸æ¯è¾å¤§ï¼ç¨æ·ä¹æ¯æ¯è¾å¤çãå®ä¸è¬æ¯ååæ¯å¾®è½¯äº§åç.netå¹³å°ä¸èµ·æé
使ç¨ãå½ç¶å
¶ä»çåç§å¼åå¹³å°ï¼é½æä¾äºä¸å®ç¸å
³çæ°æ®åºè¿æ¥æ¹å¼ãå æ¤ï¼å¼å软件ç¨SQL Serveråæ°æ®åºæ¯ä¸ä¸ªæ£ç¡®çéæ©ã
MySQL
MySQLä¸æ¯æäºå¡å¤çï¼æ²¡æè§å¾ï¼æ²¡æåå¨è¿ç¨å触åå¨ï¼æ²¡ææ°æ®åºç«¯çç¨æ·èªå®ä¹å½æ°ï¼ä¸è½å®å
¨ä½¿ç¨æ åçSQLè¯æ³ã
ä»æ°æ®åºè¡å®¶å¬è¯´ç第ä¸ä»¶äºå°±æ¯MySQL缺ä¹transactionsï¼rollbacksï¼ åsubselectsçåè½ãå¦æä½ è®¡å使ç¨MySQLåä¸ä¸ªå
³äºé¶è¡ãä¼è®¡çåºç¨ç¨åºï¼æè
计åç»´æ¤ä¸äºéæ¶éè¦çº¿æ§éå¢çä¸åç±»ç计æ°å¨ï¼ä½ å°ç¼ºä¹transactionsåè½ãå¨ç°æçåå¸çæ¬ç MySQLä¸ï¼è¯·ä¸è¦æä»»ä½çè¿äºæ³æ³ãï¼è¯·æ³¨æï¼MySQLçæµè¯ç3.23.xç³»åç°å¨å·²ç»æ¯ætransactionsäºï¼ã
å¨é常å¿
è¦çæ
åµä¸ï¼MySQLçå±éæ§å¯ä»¥éè¿ä¸é¨åå¼åè
çåªåå¾å°å
æãå¨MySQLä¸ä½ 失å»ç主è¦åè½æ¯subselectè¯å¥ï¼èè¿æ£æ¯å
¶å®çæææ°æ®åºé½å
·æçãæ¢èè¨ä¹ï¼è¿ä¸ªå¤±å»çåè½æ¯ä¸ä¸ªçè¦ã
MySQL没æ³å¤çå¤æçå
³èæ§æ°æ®åºåè½ï¼ä¾å¦ï¼åæ¥è¯¢ï¼subqueriesï¼ï¼è½ç¶å¤§å¤æ°çåæ¥è¯¢é½å¯ä»¥æ¹åæjoin
å¦ä¸ä¸ªMySQL没ææä¾æ¯æçåè½æ¯äºå¡å¤çï¼transactionï¼ä»¥åäºå¡çæ交ï¼commitï¼/æ¤éï¼rollbackï¼ãä¸ä¸ªäºå¡æçæ¯è¢«å½ä½ä¸ä¸ªåä½æ¥å
±åæ§è¡çä¸ç¾¤æä¸å¥å½ä»¤ãå¦æä¸ä¸ªäºå¡æ²¡æ³å®æï¼é£ä¹æ´ä¸ªäºå¡éé¢æ²¡æä¸ä¸ªæ令æ¯çæ£æ§è¡ä¸å»çã对äºå¿
é¡»å¤ç线ä¸è®¢åçåä¸ç½ç«æ¥è¯´ï¼MySQL没ææ¯æè¿é¡¹åè½ï¼ç确让人è§å¾å¾å¤±æãä½æ¯å¯ä»¥ç¨MaxSQLï¼ä¸ä¸ªåå¼çæå¡å¨ï¼å®è½éè¿å¤æçè¡¨æ ¼æ¥æ¯æäºå¡åè½ãã
å¤é®ï¼foreignkeyï¼ä»¥ååèå®æ´æ§éå¶ï¼referentialintegrityï¼å¯ä»¥è®©ä½ å¶å®è¡¨æ ¼ä¸èµæé´ç约æï¼ç¶åå°çº¦æ ï¼constraintï¼å å°ä½ æè§å®çèµæéé¢ãè¿äºMySQL没æçåè½è¡¨ç¤ºä¸ä¸ªæèµå¤æçèµæå
³ç³»çåºç¨ç¨åºå¹¶ä¸éå使ç¨MySQLãå½æ们说 MySQLä¸æ¯æå¤é®æ¶ï¼æ们æçå°±æ¯æ°æ®åºçåèå®æ´æ§éå¶--MySQL并没ææ¯æå¤é®çè§åï¼å½ç¶æ´æ²¡ææ¯æè¿éå é¤ï¼cascadingdeleteï¼çåè½ãç®çç说ï¼å¦æä½ çå·¥ä½éè¦ä½¿ç¨å¤æçèµæå
³èï¼é£ä½ è¿æ¯ç¨åæ¥çAccesså§ãã
ä½ å¨MySQLä¸ä¹ä¸ä¼æ¾å°åå¨è¿ç¨ï¼storedprocedureï¼ä»¥å触åå¨ï¼triggerï¼ãï¼é对è¿äºåè½ï¼å¨Accessæä¾äºç¸å¯¹çäºä»¶è¿ç¨ï¼eventprocedureï¼ã
MySQL+php+apacheä¸è
被软件å¼åè
称为âphpé»éç»åâã
OracleåMySQLç主è¦åºå«
Oracle:客æ·ç«¯åå½ä»¤çªå£ï¼é½æ¯ç±ç¨æ·å³å®å
容-> conn user_name/password;
MySQL:客æ·ç«¯åå½ä»¤çªå£ï¼é½æ¯ç±æ°æ®åºå³å®å
容-> use datebase;
é½å¯ä»¥å建å¤æ°æ®åºå¤ç¨æ·ï¼ä¸ªäººå¾åäºOracleä¸ä¸ªæ°æ®åºä¸å¤ä¸ªç¨æ·çå½¢å¼ï¼MySQLå¤ä¸ªæ°æ®åºå¤ä¸ªç¨æ·å½¢å¼ï¼æ好æ¯ä¸ªæ°æ®åºå¯¹åºä¸ä¸ªç¨æ·ï¼
Oracleæ¯å¤§åæ°æ®åºèMySQLæ¯ä¸å°åæ°æ®åºï¼Oracleå¸åºå æçè¾¾40%ï¼MySQLåªæ20%å·¦å³ï¼åæ¶MySQLæ¯å¼æºçèOracleä»·æ ¼é常é«ã
Oracleæ¯æ大并åï¼å¤§è®¿é®éï¼æ¯OLTPï¼On-Line Transaction Processingèæºäºå¡å¤çç³»ç»ï¼æ好çå·¥å
·ã
å®è£
æç¨ç空é´å·®å«ä¹æ¯å¾å¤§çï¼MySQLå®è£
å®åæ152MèOracleæ3Gå·¦å³ï¼ä¸ä½¿ç¨çæ¶åOracleå ç¨ç¹å«å¤§çå
å空é´åå
¶ä»æºå¨æ§è½ã
Oracleä¹ä¸MySQLæä½ä¸çä¸äºåºå«
ç»å½æ°ç¨æ³è§å
MySQLä¸ç»å½æ°å¨selectè¯å¥ä¸å¯ä»¥éæ使ç¨ï¼ä½å¨Oracleä¸å¦ææ¥è¯¢è¯å¥ä¸æç»å½æ°ï¼é£å
¶ä»ååå¿
é¡»æ¯ç»å½æ°å¤çè¿çï¼æè
æ¯group byåå¥ä¸çåå¦åæ¥é
egï¼
select name,count(money) from userï¼è¿ä¸ªæ¾å¨MySQLä¸æ²¡æé®é¢å¨Oracleä¸å°±æé®é¢äºã
èªå¨å¢é¿çæ°æ®ç±»åå¤ç
MySQLæèªå¨å¢é¿çæ°æ®ç±»åï¼æå
¥è®°å½æ¶ä¸ç¨æä½æ¤å段ï¼ä¼èªå¨è·å¾æ°æ®å¼ãOracle没æèªå¨å¢é¿çæ°æ®ç±»åï¼éè¦å»ºç«ä¸ä¸ªèªå¨å¢é¿çåºåå·ï¼æå
¥è®°å½æ¶è¦æåºåå·çä¸ä¸ä¸ªå¼èµäºæ¤å段ã
CREATE SEQUENCEåºåå·çå称(æ好æ¯è¡¨å+åºåå·æ è®°)INCREMENT BY 1 START WITH 1 MAXVALUE 99999 CYCLE NOCACHE;
å
¶ä¸æ大çå¼æå段çé¿åº¦æ¥å®ï¼å¦æå®ä¹çèªå¨å¢é¿çåºåå·NUMBER(6)ï¼æ大å¼ä¸º999999
INSERTè¯å¥æå
¥è¿ä¸ªå段å¼ä¸ºï¼åºåå·çå称.NEXTVAL
åå¼å·çå¤ç
MySQLéå¯ä»¥ç¨åå¼å·å
èµ·å符串ï¼Oracleéåªå¯ä»¥ç¨åå¼å·å
èµ·å符串ãå¨æå
¥åä¿®æ¹å符串åå¿
é¡»ååå¼å·çæ¿æ¢ï¼æææåºç°çä¸ä¸ªåå¼å·æ¿æ¢æ两个åå¼å·ã
翻页çSQLè¯å¥çå¤ç
MySQLå¤ç翻页çSQLè¯å¥æ¯è¾ç®åï¼ç¨LIMITå¼å§ä½ç½®ï¼è®°å½ä¸ªæ°ï¼PHPéè¿å¯ä»¥ç¨SEEKå®ä½å°ç»æéçä½ç½®ãOracleå¤ç翻页çSQLè¯å¥å°±æ¯è¾ç¹çäºãæ¯ä¸ªç»æéåªæä¸ä¸ªROWNUMå段æ æå®çä½ç½®ï¼å¹¶ä¸åªè½ç¨ROWNUM<100ï¼ä¸è½ç¨ROWNUM>80ã
以ä¸æ¯ç»è¿åæåè¾å¥½ç两ç§Oracle翻页SQLè¯å¥(IDæ¯å¯ä¸å
³é®åçå段å)ï¼
è¯å¥ä¸ï¼
SELECT ID, [FIELD_NAME,...] FROM TABLE_NAME WHERE ID IN ( SELECT ID FROM (SELECT ROWNUM AS NUMROW, ID FROM TABLE_NAME WHERE æ¡ä»¶1 ORDER BY æ¡ä»¶2) WHERE NUMROW > 80 AND NUMROW < 100 ) ORDER BY æ¡ä»¶3;
è¯å¥äºï¼
SELECT * FROM (( SELECT ROWNUM AS NUMROW, c.* from (SELECT [FIELD_NAME,...] FROM TABLE_NAME WHERE æ¡ä»¶1 ORDER BY æ¡ä»¶2) c) WHERE NUMROW > 80 AND NUMROW < 100 ) ORDER BY æ¡ä»¶3;
é¿å符串çå¤ç
é¿å符串çå¤çOracleä¹æå®ç¹æ®çå°æ¹ãINSERTåUPDATEæ¶æ大å¯æä½çå符串é¿åº¦å°äºçäº4000个ååèï¼å¦æè¦æå
¥æ´é¿çå符串ï¼è¯·èèå段ç¨CLOBç±»åï¼æ¹æ³åç¨Oracleéèªå¸¦çDBMS_LOBç¨åºå
ãæå
¥ä¿®æ¹è®°å½åä¸å®è¦åè¿è¡é空åé¿åº¦å¤æï¼ä¸è½ä¸ºç©ºçå段å¼åè¶
åºé¿åº¦å段å¼é½åºè¯¥æåºè¦åï¼è¿åä¸æ¬¡æä½ã
æ¥æå段çå¤ç
MySQLæ¥æå段åDATEåTIME两ç§ï¼Oracleæ¥æå段åªæDATEï¼å
å«å¹´ææ¥æ¶åç§ä¿¡æ¯ï¼ç¨å½åæ°æ®åºçç³»ç»æ¶é´ä¸ºSYSDATEï¼ç²¾ç¡®å°ç§ï¼æè
ç¨å符串转æ¢ææ¥æåå½æ°TO_DATE('2001-08-01','YYYY-MM-DD')å¹´-æ-æ¥24å°æ¶:åé:ç§çæ ¼å¼YYYY-MM-DD HH24:MI:SS TO_DATE()è¿æå¾å¤ç§æ¥ææ ¼å¼ï¼å¯ä»¥åçOracle DOC.
æ¥æåå段转æ¢æå符串å½æ°TO_CHAR('2001-08-01','YYYY-MM-DD HH24:MI:SS')
æ¥æå段çæ°å¦è¿ç®å
¬å¼æå¾å¤§çä¸åãMySQLæ¾å°ç¦»å½åæ¶é´7天ç¨DATE_FIELD_NAME > SUBDATE(NOW()ï¼INTERVAL 7 DAY)Oracleæ¾å°ç¦»å½åæ¶é´7å¤©ç¨ DATE_FIELD_NAME >SYSDATE - 7;
MySQLä¸æå
¥å½åæ¶é´çå 个å½æ°æ¯ï¼NOW()å½æ°ä»¥`'YYYY-MM-DD HH:MM:SS'è¿åå½åçæ¥ææ¶é´ï¼å¯ä»¥ç´æ¥åå°DATETIMEå段ä¸ãCURDATE()以'YYYY-MM-DD'çæ ¼å¼è¿åä»å¤©çæ¥æï¼å¯ä»¥ç´æ¥åå°DATEå段ä¸ãCURTIME()以'HH:MM:SS'çæ ¼å¼è¿åå½åçæ¶é´ï¼å¯ä»¥ç´æ¥åå°TIMEå段ä¸ãä¾ï¼insert into tablename (fieldname) values (now())
èOracleä¸å½åæ¶é´æ¯sysdate
空å符çå¤ç
MySQLçé空å段ä¹æ空çå
容ï¼Oracleéå®ä¹äºé空å段就ä¸å®¹è®¸æ空çå
容ãæMySQLçNOT NULLæ¥å®ä¹Oracle表ç»æï¼å¯¼æ°æ®çæ¶åä¼äº§çé误ãå æ¤å¯¼æ°æ®æ¶è¦å¯¹ç©ºå符è¿è¡å¤æï¼å¦æ为NULLæ空å符ï¼éè¦æå®æ¹æä¸ä¸ªç©ºæ ¼çå符串ã
å符串ç模ç³æ¯è¾
MySQLéç¨å段ålike%'å符串%'ï¼Oracleéä¹å¯ä»¥ç¨å段ålike%'å符串%'ä½è¿ç§æ¹æ³ä¸è½ä½¿ç¨ç´¢å¼ï¼é度ä¸å¿«ï¼ç¨å符串æ¯è¾å½æ°instr(å段åï¼'å符串')>0ä¼å¾å°æ´ç²¾ç¡®çæ¥æ¾ç»æã
ç¨åºåå½æ°éï¼æä½æ°æ®åºçå·¥ä½å®æå请注æç»æéåæéçéæ¾ã
主é®
MySQLä¸è¬ä½¿ç¨èªå¨å¢é¿ç±»åï¼å¨å建表æ¶åªè¦æå®è¡¨ç主é®ä¸ºauto increment,æå
¥è®°å½æ¶ï¼ä¸éè¦åæå®è¯¥è®°å½ç主é®å¼ï¼MySQLå°èªå¨å¢é¿ï¼Oracle没æèªå¨å¢é¿ç±»åï¼ä¸»é®ä¸è¬ä½¿ç¨çåºåï¼æå
¥è®°å½æ¶å°åºåå·çä¸ä¸ä¸ªå¼ä»ç»è¯¥å段å³å¯ï¼åªæ¯ORMæ¡æ¶æ¯åªè¦æ¯native主é®çæçç¥å³å¯ã
Oracleå®ç°äºANSIISQLä¸å¤§é¨ååè½ï¼å¦ï¼äºå¡çé离级å«ãä¼ æç¹æ§çèMySQLå¨è¿æ¹é¢è¿æ¯æ¯è¾çå¼±
温馨提示:内容为网友见解,仅供参考