oracleÏòmysqlÒÆÖ²
ÓÐһЩûÓÐʹÓÃÊý¾Ý¿âÖмä¼þµÄÈí¼þ¿ÉÄÜ»áÅöµ½ÔÚ²»Í¬µÄÓ¦Óó¡¾°ÐèÒªÓò»Í¬µÄÊý¾Ý¿â£¬ÕâÑù¿ÉÄܾÍÐèÒª½«Êý¾Ý¿â´úÂë×öÒÆÖ²£¬ÎÒÕâÀïÕûÀíÁËһЩ´Óoracleµ½mysqlµÄÒÆÖ²¹ý³ÌÖеÄ×¢ÒâÊÂÏ
1¡£sequenceÉú³É
¿ÉÒÔÓÃÀàËÆµÄ¶«Î÷À´×ö,ÈçÏ£º
create table myseq ( seq varchar(8));
insert into myseq values('0');
update myseq set seq = last_insert_id(seq+1);
select last_insert_id();
Õâ¶«Î÷ÍêÈ«¸úoracleµÄsequenceÒ»Ö¡£¼´Ê¹ÔÚupdateÖ®ºó£¬select֮ǰÓÐÆäËüsessionÒ²updateÁË£¬±¾sessionµÃµ½µÄ£¬ÈÔÈ»ÊÇ×Ô¼ºupdateµÄÄÇÒ»¸öÖµ¡£
2¡£sysdateתΪsysdate()
3.substr×°»»£¬»ù±¾²»ÐèҪת»»
4¡£nvlת»»Îªifnull
5¡£Êý×Öת»»Îª×Ö·û´®µÄto_charº¯Êý²»Óã¬Ö±½ÓÓü´¿É£¬²»Óú¯Êýת»»
6¡£ÈÕÆÚ¸ñʽµÄto_charת»»Îªdate_format
oracle: to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
mysql: select date_format(now(),'%Y-%m-%d %H-%i-%S')
7.to_date()×°»»£¬É¾³ýת»»£¬mysqlÖ§³Ö×Ô¶¯×ª»»
8¡£decode£¨£©×ª»»£¬
case when flag=0 then '³É¹¦' else 'ʧ°Ü' end
9¡£rownumת»»£¬·Öҳת»»
querySql = "select * from ( select rownum rnm, a.* from ( " + strQuery
// + ") a where rownum <= " + getPageEndRow()
//+ ") where rnm > " + getPageStartRow();
ת»»Îª£º
querySql = "select * from ( select 1 rnm, page_a.* from ( " + strQuery
+ ") page_a LIMIT " + getPageStartRow() + "," + getPageSize() + " ) select_page";
10¡£from×Ó²éѯת»»£¬Îª×Ó²éѯ¶¨Òå±ðÃû
select count(*) from (
select a.login_no,a.LOGIN_NAME,a.EXPIRE_TIME,b.orgname,a.CONTRACT_PHONE,a.info_flag,a.login_level,a.group_id from DLOGINMSG a left outer join dchngroupinfo b on a.group_id=b.orgno where 1=1 and a.login_no like '%test%'
) subselect
Ïà¹ØÎĵµ£º
¶ÔÓÚÒ»¸ö¸ø¶¨µÄSQL, ºÍÏÖÓеÄMVIEW, Õâ¸öSQL¿ÉÄܱ»Rewrite, Ò²¿ÉÄܲ»Äܱ»Rewrite, ÈçºÎÕÒ³öÕâÆäÖеÄÔÒòÄØ, ¿¿¾Ñé¿ÉÒÔ½â¾öһЩÎÊÌâ, ²»¹ýËù»¨µÄʱ¼ä¾ÍÒª³¤Ò»µãÁË. ÆäʵÔÚOracleÖÐÌṩÁËÒ»¸öº¯Êý¿ÉÒÔÓÃÓÚ½âÊÍΪʲôij¸öSQL²»Äܱ»ÖØÐ´, Õâ¸ö¹ý³ÌλÓÚdbms_mviewÕâ¸ö°üÖÐ, ¹ý³ÌÃûΪexplain_rewrite, ÓÐÁËÕâ¸ö¹¤¾ß, ¿ÉÒ ......
1¡¢²é¿´±í¿Õ¼äµÄÃû³Æ¼°´óС
¡¡¡¡select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
¡¡¡¡from dba_tablespaces t, dba_data_files d
¡¡¡¡where t.tablespace_name = d.tablespace_name
¡¡¡¡group by t.tablespace_name;
¡¡¡¡
¡¡¡¡2¡¢²é¿´±í¿Õ¼äÎïÀíÎļþµÄÃû³Æ¼°´óС
¡¡¡¡select tablespace_ ......
Êý¾Ý¿â´æ´¢ÖеÄÊý¾ÝÀàÐÍÓë´óС¸÷Òì¡£ÓÐЩµØ·½Ö»´æ´¢Êý×ÖÀàÐÍ£¬ÓÐЩֻ´æ´¢Îı¾ÀàÐÍ£¬ÓÐЩ¶þÕß¶ø¼æÖ®¡£¶øºÜ¶àÊý¾Ý¿âÖ§³Ö¸÷ÖÖרÓÃÀàÐÍ£ºÈÕÆÚºÍʱ¼äÀàÐÍ£¬¶þ½øÖÆ×Ö·ûÀàÐÍÒÔ¼°²¼¶ûÀàÐÍ¡£Ñ¡ÔñÊý¾ÝÀàÐÍÓëÊý¾ÝÏàÆ¥ÅäÊÇÊý¾Ý¿âÉè¼ÆÖÐ×îÎªÖØÒªµÄ²¿·Ö£¬ÒòΪÕâÖÖÀàÐͽ«»áÓ°Ïìµ½RDBMSµÄЧÂÊÓëÐÔÄÜ¡£ËùÒÔ£¬¶ÔRDBMSµÄÊý¾ÝÀàÐÍÑ¡ÔñÓ¦ ......
ISAM
ISAMÊÇÒ»¸ö¶¨ÒåÃ÷È·ÇÒÀú¾Ê±¼ä¿¼ÑéµÄÊý¾Ý±í¸ñ¹ÜÀí·½·¨£¬ËüÔÚÉè¼ÆÖ®Ê±¾Í¿¼Âǵ½Êý¾Ý¿â±»²éѯµÄ´ÎÊýÒªÔ¶´óÓÚ¸üеĴÎÊý¡£Òò´Ë£¬ISAMÖ´ÐжÁÈ¡²Ù×÷µÄËٶȺܿ죬¶øÇÒ²»Õ¼ÓôóÁ¿µÄÄÚ´æºÍ´æ´¢×ÊÔ´¡£ISAMµÄÁ½¸öÖ÷Òª²»×ãÖ®´¦ÔÚÓÚ£¬Ëü²»Ö§³ÖÊÂÎñ´¦Àí£¬Ò²²»Äܹ»ÈÝ´í£ºÈç¹ûÄãµÄÓ²Å̱ÀÀ£ÁË£¬ÄÇôÊý¾ÝÎļþ¾ÍÎÞ·¨»Ö¸´ÁË¡£Èç¹ûÄãÕýÔÚ°Ñ ......
MySqlÊý¾Ý¿âµ¼³öcsvÎļþÃüÁ
mysql> select first_name,last_name,email from account into outfile 'e:\\output1.csv' fields terminated by ','optionally enclosed by ''lines terminated by '\n';
csvÎļþЧ¹û£º
sunny
Grigoryan
lovechoosesun@gmail.com
Jon
Siegal
sun@riliantech.net
Joe
S ......