Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

SQL ÓÅ»¯¾­Ñé×ܽá34Ìõ

£¨1£© Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)£º
ORACLE µÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬from×Ó¾äÖÐдÔÚ×îºóµÄ±í(»ù´¡±í driving table)½«±»×îÏÈ´¦Àí£¬ÔÚfrom×Ó¾äÖаüº¬¶à¸ö±íµÄÇé¿öÏÂ,Äã±ØÐëÑ¡Ôñ¼Ç¼ÌõÊý×îÉٵıí×÷Ϊ»ù´¡±í¡£Èç¹ûÓÐ3¸öÒÔÉϵıíÁ¬½Ó²éѯ, ÄǾÍÐèҪѡÔñ½»²æ±í(intersection table)×÷Ϊ»ù´¡±í, ½»²æ±íÊÇÖ¸ÄǸö±»ÆäËû±íËùÒýÓõıí.
£¨2£© WHERE×Ó¾äÖеÄÁ¬½Ó˳Ðò£®£º
ORACLE²ÉÓÃ×Ô϶øÉϵÄ˳Ðò½âÎöWHERE×Ó¾ä,¸ù¾ÝÕâ¸öÔ­Àí,±íÖ®¼äµÄÁ¬½Ó±ØÐëдÔÚÆäËûWHEREÌõ¼þ֮ǰ, ÄÇЩ¿ÉÒÔ¹ýÂ˵ô×î´óÊýÁ¿¼Ç¼µÄÌõ¼þ±ØÐëдÔÚWHERE×Ó¾äµÄĩβ.
£¨3£© SELECT×Ó¾äÖбÜÃâʹÓà ‘ * ‘£º
ORACLEÔÚ½âÎöµÄ¹ý³ÌÖÐ, »á½«'*' ÒÀ´Îת»»³ÉËùÓеÄÁÐÃû, Õâ¸ö¹¤×÷ÊÇͨ¹ý²éѯÊý¾Ý×ÖµäÍê³ÉµÄ, ÕâÒâζ׎«ºÄ·Ñ¸ü¶àµÄʱ¼ä
£¨4£© ¼õÉÙ·ÃÎÊÊý¾Ý¿âµÄ´ÎÊý£º
ORACLEÔÚÄÚ²¿Ö´ÐÐÁËÐí¶à¹¤×÷: ½âÎöSQLÓï¾ä, ¹ÀËãË÷ÒýµÄÀûÓÃÂÊ, °ó¶¨±äÁ¿ , ¶ÁÊý¾Ý¿éµÈ£»
£¨5£© ÔÚSQL*Plus , SQL*FormsºÍPro*CÖÐÖØÐÂÉèÖÃARRAYSIZE²ÎÊý, ¿ÉÒÔÔö¼Óÿ´ÎÊý¾Ý¿â·ÃÎʵļìË÷Êý¾ÝÁ¿ ,½¨ÒéֵΪ200
£¨6£© ʹÓÃDECODEº¯ÊýÀ´¼õÉÙ´¦Àíʱ¼ä£º
ʹÓÃDECODEº¯Êý¿ÉÒÔ±ÜÃâÖظ´É¨ÃèÏàͬ¼Ç¼»òÖظ´Á¬½ÓÏàͬµÄ±í.
£¨7£© ÕûºÏ¼òµ¥,ÎÞ¹ØÁªµÄÊý¾Ý¿â·ÃÎÊ£º
Èç¹ûÄãÓм¸¸ö¼òµ¥µÄÊý¾Ý¿â²éѯÓï¾ä,Äã¿ÉÒÔ°ÑËüÃÇÕûºÏµ½Ò»¸ö²éѯÖÐ(¼´Ê¹ËüÃÇÖ®¼äûÓйØϵ)
£¨8£© ɾ³ýÖظ´¼Ç¼£º
×î¸ßЧµÄɾ³ýÖظ´¼Ç¼·½·¨ ( ÒòΪʹÓÃÁËROWID)Àý×Ó£º
DELETE from EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)
from EMP X WHERE X.EMP_NO = E.EMP_NO);
£¨9£© ÓÃTRUNCATEÌæ´úDELETE£º
µ±É¾³ý±íÖеļǼʱ,ÔÚͨ³£Çé¿öÏÂ, »Ø¹ö¶Î(rollback segments ) ÓÃÀ´´æ·Å¿ÉÒÔ±»»Ö¸´µÄÐÅÏ¢. Èç¹ûÄãûÓÐCOMMITÊÂÎñ,ORACLE»á½«Êý¾Ý»Ö¸´µ½É¾³ý֮ǰµÄ״̬(׼ȷµØ˵Êǻָ´µ½Ö´ÐÐɾ³ýÃüÁî֮ǰµÄ×´¿ö) ¶øµ±ÔËÓÃTRUNCATEʱ, »Ø¹ö¶Î²»ÔÙ´æ·ÅÈκοɱ»»Ö¸´µÄÐÅÏ¢.µ±ÃüÁîÔËÐкó,Êý¾Ý²»Äܱ»»Ö¸´.Òò´ËºÜÉÙµÄ×ÊÔ´±»µ÷ÓÃ,Ö´ÐÐʱ¼äÒ²»áºÜ¶Ì. (ÒëÕß°´: TRUNCATEÖ»ÔÚɾ³ýÈ«±íÊÊÓÃ,TRUNCATEÊÇDDL²»ÊÇDML)
£¨10£© ¾¡Á¿¶àʹÓÃCOMMIT£º
Ö»ÒªÓпÉÄÜ,ÔÚ³ÌÐòÖо¡Á¿¶àʹÓÃCOMMIT, ÕâÑù³ÌÐòµÄÐÔÄܵõ½Ìá¸ß,ÐèÇóÒ²»áÒòΪCOMMITËùÊͷŵÄ×ÊÔ´¶ø¼õÉÙ:
COMMITËùÊͷŵÄ×ÊÔ´:
a. »Ø¹ö¶ÎÉÏÓÃÓÚ»Ö¸´Êý¾ÝµÄÐÅÏ¢.
b. ±»³ÌÐòÓï¾ä»ñµÃµÄËø
c. redo log buffer ÖеĿռä
d. ORACLEΪ¹ÜÀíÉÏÊö3ÖÖ×ÊÔ´ÖеÄÄÚ²¿»¨·Ñ
£¨11£


Ïà¹ØÎĵµ£º

sql 2005 ´æ´¢¹ý³Ì·ÖÒ³ java ´úÂë

 create PROCEDURE pagelist
@tablename nvarchar(50),
@fieldname nvarchar(50)='*',         
@pagesize int output,--ÿҳÏÔʾ¼Ç¼ÌõÊý
@currentpage int output,--µÚ¼¸Ò³
@orderid nvarchar(50),--Ö÷¼üÅÅÐò
@sort int,--ÅÅÐò·½Ê½£¬1±íʾÉýÐò£¬0±íʾ½µÐòÅÅÁÐ ......

SQL SERVERÒìµØ±¸·Ý·½·¨(1)

д³É´æ´¢¹ý³Ì£¬½¨Á¢×÷Òµ¶¨Ê±±¸·Ý~~~
--ÔÚsqlÖÐÓ³ÉäһϾͿÉÒÔÁË
exec master..xp_cmdshell 'net use z: \\yizhi\D$ "ÃÜÂë" /user:yizhi\administrator'
/*--˵Ã÷:
exec master..xp_cmdshell 'net use z: \\xz\c$ "ÃÜÂë" /user:xz\administrator'
z:ÊÇÓ³ÉäÍøÂç·¾¶¶ÔÓ¦±¾»úµÄÅÌ·û,ÓëÏÂÃæµÄ±¸·Ý¶ÔÓ¦
\\xz\c$ÊÇÒªÓ³ÉäµÄÍ ......

sql serverÊÓͼµÄ×÷Óã¨×ª£©

    ËùνÊÓͼ£¨View£©ÆäʵÊÇÖ´ÐвéѯÓï¾äºóµÃµ½µÄ½á¹û£¬µ«Õâ¸ö²éѯ½á¹û¿ÉÒÔ·ÂÕæ³ÉÊý¾Ý±íÀ´Ê¹Óã¬ËùÒÔÓÐÈËÒ²³ÆËüΪ“ÐéÄâÊý¾Ý±í”£¬ÊÓͼÔÚ²Ù×÷ÉϺÍÊý¾Ý±íûÓÐʲôÇø±ð£¬µ«Á½ÕߵIJîÒìÊÇÆä±¾ÖÊÊDz»Í¬µÄ£ºÊý¾Ý±íÊÇʵ¼Ê´æ´¢¼Ç¼µÄµØ·½£¬È»¶øÊÓͼ²¢²»±£´æÈκμǼ£¬Ëü´æ´¢µÄʵ¼ÊÉÏÊDzéѯÓï¾ä£¬ÆäËù³ÊÏÖ³ ......

Mysql µ¼Èë .sqlÎļþÖÐÎÄÂÒÂ룿

 --default-character-set=utf8 ÓëÐÞ¸ÄsqlÎļþ¼ÓÈ룺SET NAMES 'utf8'; 
µ¼Èë½Å±¾Ê±£¬SET NAMES 'xxx' £¬xxxÓ¦¸ÃºÍsql½Å±¾ÎļþµÄʵ¼Ê±àÂëÒ»Ö£¬Èç¹ûÄãÉèÖõÄʱutf-8 £¬ÄÇôÇëÈ·ÈÏÄãµÄsql½Å±¾Îļþʱutf-8±£´æµÄ:
iconv -f UTF-8 xxx.sql
Èç¹ûÏÔʾÕý³££¬ÄÇôÕâÒ»²½¾ÍOKÁË
ÁíÍâ×¢ÒâÄãµÄ½Å±¾ÀÓÐûÓиø¿â±íÉèÖ ......

ORACLE·ÖÒ³²éѯSQLÓï·¨


--1:ÎÞORDER BYÅÅÐòµÄд·¨¡£(ЧÂÊ×î¸ß) ­
--(¾­¹ý²âÊÔ£¬´Ë·½·¨³É±¾×îµÍ£¬Ö»Ç¶Ì×Ò»²ã£¬ËÙ¶È×î¿ì£¡¼´Ê¹²éѯµÄÊý¾ÝÁ¿ÔÙ´ó£¬Ò²¼¸ºõ²»ÊÜÓ°Ï죬ËÙ¶ÈÒÀÈ»£¡) ­
SELECT * ­
  from (Select ROWNUM AS ROWNO, T.* ­
           from k_task T &s ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ