[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,ÔÙÖØе¼Èë/²åÈëÊý¾Ý
Ïà¹ØÎĵµ£º
Ò»¡¢ ½¨Á¢Ò»¸ö°²È«³éÏó²ã
¡¡¡¡ÎÒÃDz¢²»½¨ÒéÄãÊÖ¹¤µØ°ÑÇ°Ãæ½éÉܵļ¼ÊõÓ¦ÓÃÓÚÿһ¸öÓû§ÊäÈëµÄʵÀýÖУ¬¶øÊÇÇ¿ÁÒÍƼöÄãΪ´Ë´´½¨Ò»¸ö³éÏó²ã¡£Ò»¸ö¼òµ¥µÄ³éÏóÊÇ°ÑÄãµÄУÑé·½°¸¼ÓÈëµ½Ò»¸öº¯ÊýÖУ¬²¢ÇÒÕë¶ÔÓû§ÊäÈëµÄÿһÏîµ÷ÓÃÕâ¸öº¯Êý¡£µ±È»£¬ÎÒÃÇ»¹¿ÉÒÔ´´½¨Ò»ÖÖ¸ü¸´Ôӵĸü¸ßÒ»¼¶µÄ³éÏó-°ÑÒ»¸ö°²È«µÄ²éѯ·â×°µ½Ò»¸öÀàÖУ¬´Ó¶øÓ¦ ......
createdb.batÎļþ
@echo off
echo ╬¡¡╱◥███◣╬╬╬╬╬╬╬╬╬╬╬╬╬╬╬╬╬
echo ╬ ︱Ìï︱Ìï Ìï ︱¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡ ......
¿ª·¢»·¾³£º
windows2003£¬°²×°ÁËvs2008¡¢SQL Server2005£¬ÕâÊÇ»ù´¡£¨µ±È»ÓÃvs2003£¬vs2005£¬SQL Server2000ɶµÄÒ²¿ÉÒÔ£¬²»¹ýеÄ×ÜÊǹ¦ÄÜÇ¿´óЩô£¬ºÇºÇ£©£»
ÒòΪ¿ª·¢Ê±¾ø´ó²¿·Öʱ¼äÊÇÔÚÄ£ÄâÆ÷Éϵ÷ÊÔ£¬¶ø²»ÊÇPPC£¬ËùÒÔÄ£ÄâÆ÷ÊDZØÐë°²×°µÄ¡£Õë¶ÔÄãµÄPPC²Ù×÷ϵͳѡÔñ¶ÔÓ¦µÄÄ£ÄâÆ÷Óësdk°ü£¬ÎÒ°²×°µÄÊÇWM6ÖÐÎÄ°æµÄ¡££¨windows ......
1433¶Ë¿ÚÓ³Éä ¼ÜÉèsql server
¡¾×ª¡¿ http://blog.csdn.net/lee576/archive/2007/11/12/1880001.aspx
¹ØÓÚͨ¹ýÍâÍøÁ¬½ÓÄÚÍøSQL Server·þÎñÆ÷µÄ·½·¨
½üÈÕ£¬ÔÚÂÛ̳ÉÏ¿´µ½µÄÓйØSQL ServerÔ¶³ÌÁ¬½ÓµÄÎÄÕºó£¬ÕæµÄÊÜÒæÁ¼¶à¡£Ò»Ö±ÒÔÀ´À§»óÁËÒѾõĹØÓÚ´ÓÍâÍøÈçºÎÁ¬½Óµ½ÄÚÍøµÄSQL Server·þÎñÆ ......
1¡¢ ÓóÌÐòÖУ¬±£Ö¤ÔÚʵÏÖ¹¦ÄܵĻù´¡ÉÏ£¬¾¡Á¿¼õÉÙ¶ÔÊý¾Ý¿âµÄ·ÃÎÊ´ÎÊý£»Í¨¹ýËÑË÷²ÎÊý£¬¾¡Á¿¼õÉÙ¶Ô±íµÄ·ÃÎÊÐÐÊý,×îС»¯½á¹û¼¯£¬´Ó¶ø¼õÇáÍøÂ縺µ££»Äܹ»·Ö¿ªµÄ ²Ù×÷¾¡Á¿·Ö¿ª´¦Àí£¬Ìá¸ßÿ´ÎµÄÏìÓ¦Ëٶȣ»ÔÚÊý¾Ý´°¿ÚʹÓÃSQLʱ£¬¾¡Á¿°ÑʹÓõÄË÷Òý·ÅÔÚÑ¡ÔñµÄÊ×ÁУ»Ëã·¨µÄ½á¹¹¾¡Á¿¼òµ¥£»ÔÚ²éѯʱ£¬²»Òª¹ý¶àµØʹÓÃͨÅä·û ÈçSELE ......