[SQLɾ³ý] SQLÓï¾äÖÐdelete, drop, truncate±È½Ï
DELETE from SCOTT.EMP;
DROP from SCOTT.EMP;
TRUNCATE from EMP;
Ïàͬµã
truncateºÍ²»´øwhere×Ó¾äµÄdelete, ÒÔ¼°drop¶¼»áɾ³ý±íÄÚµÄÊý¾Ý
²»Í¬µã:
1. truncateºÍ deleteֻɾ³ýÊý¾Ý²»É¾³ý±íµÄ½á¹¹(¶¨Òå)
dropÓï¾ä½«É¾³ý±íµÄ½á¹¹±»ÒÀÀµµÄÔ¼Êø(constrain),´¥·¢Æ÷(trigger),Ë÷Òý(index); ÒÀÀµÓڸñíµÄ´æ´¢¹ý³Ì/º¯Êý½«±£Áô,µ«ÊDZäΪinvalid״̬.
2.deleteÓï¾äÊÇdml,Õâ¸ö²Ù×÷»á·Åµ½rollback segementÖÐ,ÊÂÎñÌá½»Ö®ºó²ÅÉúЧ;Èç¹ûÓÐÏàÓ¦µÄtrigger,Ö´ÐеÄʱºò½«±»´¥·¢.
truncate,dropÊÇddl, ²Ù×÷Á¢¼´ÉúЧ,ÔÊý¾Ý²»·Åµ½rollback segmentÖÐ,²»Äܻعö. ²Ù×÷²»´¥·¢trigger.
3.deleteÓï¾ä²»Ó°Ïì±íËùÕ¼ÓõÄextent, ¸ßË®Ïß(high watermark)±£³ÖÔλÖò»¶¯
ÏÔÈ»dropÓï¾ä½«±íËùÕ¼ÓõĿռäÈ«²¿ÊÍ·Å
truncate Óï¾äȱʡÇé¿öϼû¿Õ¼äÊͷŵ½ minextents¸ö extent,³ý·ÇʹÓÃreuse storage; truncate»á½«¸ßË®Ï߸´Î»(»Øµ½×ʼ).
4.ËÙ¶È,Ò»°ãÀ´Ëµ: drop>; truncate >; delete
5.°²È«ÐÔ:СÐÄʹÓÃdrop ºÍtruncate,ÓÈÆäûÓб¸·ÝµÄʱºò.·ñÔò¿Þ¶¼À´²»¼°
ʹÓÃÉÏ,Ïëɾ³ý²¿·ÖÊý¾ÝÐÐÓÃdelete,×¢Òâ´øÉÏwhere×Ó¾ä. »Ø¹ö¶ÎÒª×ã¹»´ó.
Ïëɾ³ý±í,µ±È»ÓÃdrop
Ïë±£Áô±í¶ø½«ËùÓÐÊý¾Ýɾ³ý. Èç¹ûºÍÊÂÎñÎÞ¹Ø,ÓÃtruncate¼´¿É. Èç¹ûºÍÊÂÎñÓйØ,»òÕßÏë´¥·¢trigger,»¹ÊÇÓÃdelete.
Èç¹ûÊÇÕûÀí±íÄÚ²¿µÄËéƬ,¿ÉÒÔÓÃtruncate¸úÉÏreuse stroage,ÔÙÖØе¼Èë/²åÈëÊý¾Ý
Ïà¹ØÎĵµ£º
1.·ÖÒ³·½°¸Ò»£º(ÀûÓÃNot InºÍSELECT TOP·ÖÒ³)
¡¡¡¡Óï¾äÐÎʽ£º
SELECTTOP10*fromTestTableWHERE(IDNOTIN¡¡¡¡¡¡¡¡¡¡(SELECTTOP20id¡¡¡¡¡¡¡¡fromTestTable¡¡¡¡¡¡¡¡ORDERBYid))ORDERBYIDSELECTTOPÒ³´óС*fromTestTableWHERE(IDNOTIN¡¡¡¡¡¡¡¡¡¡(SELECTTOPÒ³´óС*Ò³Êýid¡¡¡¡¡¡¡¡from±í¡¡¡¡¡¡¡¡ORDERBYid))ORDERBYID
¡¡¡¡2.·Ö ......
×î½üÐÞ¸ÄÊý¾Ý¿âÒѾ´æÔڵıí½á¹¹£¬·¢ÏÖÓÃSQLÓï¾äÄܺܺõÄÐ޸ıíµÄ½á¹¹¡£SQLÊý¾Ý¿â¼Ç¼Êý·Ç³£ÅÓ´ó£¬Èç¹ûÏëÐ޸ıíµÄ×Ö¶ÎÀàÐÍ£¬Ê¹ÓÃÆóÒµ¹ÜÀíÆ÷À´¸ü¸Ä×Ö¶ÎÀàÐÍ£¬ºÜ¶àʱºò»á³¬Ê±£¬¸ü¸Ä²»³É¹¦£¬Õâ¸öʱºò£¬¿ÉÒÔÓÃSQLÓï¾äÀ´¸ü¸Ä£¬ÒÔÏÂΪÏàÓ¦µÄ´úÂëʵÀý£º
1.¸ü¸Ä×Ö¶ÎÀàÐͳ¤¶È
alert table ±í alert column ×Ö¶ÎÃû ÀàÐ͵ij¤¶È
eg: ......
1433¶Ë¿ÚÓ³Éä ¼ÜÉèsql server
¡¾×ª¡¿ http://blog.csdn.net/lee576/archive/2007/11/12/1880001.aspx
¹ØÓÚͨ¹ýÍâÍøÁ¬½ÓÄÚÍøSQL Server·þÎñÆ÷µÄ·½·¨
½üÈÕ£¬ÔÚÂÛ̳ÉÏ¿´µ½µÄÓйØSQL ServerÔ¶³ÌÁ¬½ÓµÄÎÄÕºó£¬ÕæµÄÊÜÒæÁ¼¶à¡£Ò»Ö±ÒÔÀ´À§»óÁËÒѾõĹØÓÚ´ÓÍâÍøÈçºÎÁ¬½Óµ½ÄÚÍøµÄSQL Server·þÎñÆ ......
¹ØÓÚͨ¹ýÍâÍøÁ¬½ÓÄÚÍøSQL Server·þÎñÆ÷µÄ·½·¨
½üÈÕ£¬ÔÚÂÛ̳ÉÏ¿´µ½µÄÓйØSQL ServerÔ¶³ÌÁ¬½ÓµÄÎÄÕºó£¬ÕæµÄÊÜÒæÁ¼¶à¡£Ò»Ö±ÒÔÀ´À§»óÁËÒѾõĹØÓÚ´ÓÍâÍøÈçºÎÁ¬½Óµ½ÄÚÍøµÄSQL Server·þÎñÆ÷Éϵķ½·¨£¬ÏÖÒѽâ¾öÁË£¬ÏàÐÅÒ²Óв»ÉÙÏóÎÒÕâÑùµÄºüÓÑÃÇ¡£¹Ê´Ë£¬ÏÖÔÚ°ÑÎÒʵÏֵķ½·¨Ä ......