sql Íâ¼ü ɾ³ý
1¡£ÆóÒµ¹ÜÀíÆ÷
´ò¿ªÄ㽨ÓÐÍâ¼üµÄ±í££ÓÒ»÷±í££Éè¼Æ±í££ÔÚÉÏ·½µã¿ª’¹ÜÀíÔ¼Êø‘££½«¼¶Á¬É¾³ýºÍ¼¶Á¬¸üÐµĹµ´òÉϾͿÉÒÔÁË
2¡£²éѯ·ÖÎöÆ÷
alter table sc
add
constraint forei foreign key(sno)
REFERENCES student(sno)
ON DELETE CASCADE
ON UPDATE CASCADE
ON DELETE {CASCADE | NO ACTION}
Ö¸¶¨µ±±íÖб»¸ü¸ÄµÄÐоßÓÐÒýÓùØϵ£¬²¢ÇÒ¸ÃÐÐËùÒýÓõÄÐдӸ¸±íÖÐɾ³ýʱ£¬Òª¶Ô±»¸ü¸ÄÐвÉÈ¡µÄ²Ù×÷¡£Ä¬ÈÏÉèÖÃΪ NO ACTION¡£
Èç¹ûÖ¸¶¨ CASCADE£¬Ôò´Ó¸¸±íÖÐɾ³ý±»ÒýÓÃÐÐʱ£¬Ò²½«´ÓÒýÓñíÖÐɾ³ýÒýÓÃÐС£Èç¹ûÖ¸¶¨ NO ACTION£¬SQL Server ½«²úÉúÒ»¸ö´íÎ󲢻عö¸¸±íÖеÄÐÐɾ³ý²Ù×÷¡£
Èç¹û±íÖÐÒÑ´æÔÚ ON DELETE µÄ INSTEAD OF ´¥·¢Æ÷£¬ÄÇô¾Í²»Äܶ¨Òå ON DELETE µÄCASCADE ²Ù×÷¡£
ÀýÈ磬ÔÚ Northwind Êý¾Ý¿âÖУ¬Orders ±íºÍ Customers ±íÖ®¼äÓÐÒýÓùØϵ¡£Orders.CustomerID Íâ¼üÒýÓà Customers.CustomerID Ö÷¼ü¡£
Èç¹û¶Ô Customers ±íµÄijÐÐÖ´ÐÐ DELETE Óï¾ä£¬²¢ÇÒΪ Orders.CustomerID Ö¸¶¨ ON DELETE CASCADE ²Ù×÷£¬Ôò SQL Server ½«ÔÚ Orders ±íÖмì²éÊÇ·ñÓÐÓ뱻ɾ³ýµÄÐÐÏà¹ØµÄÒ»Ðлò¶àÐС£Èç¹û´æÔÚÏà¹ØÐУ¬ÄÇô Orders ±íÖеÄÏà¹ØÐн«Ëæ Customers ±íÖеı»ÒýÓÃÐÐһͬɾ³ý¡£
·´Ö®£¬Èç¹ûÖ¸¶¨ NO ACTION£¬ÈôÔÚ Orders ±íÖÐÖÁÉÙÓÐÒ»ÐÐÒýÓà Customers ±íÖÐҪɾ³ýµÄÐУ
Ïà¹ØÎĵµ£º
--select name from sysobjects where type='U' order by name
SELECT
(case when a.colorder=1 then d.name else '' end) ±íÃû,
-- a.colorder ×Ö¶ÎÐòºÅ,
a.name ×Ö¶ÎÃû,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) ±êʶ,
(case when (SELECT count(* ......
¶¨Ò壺 ºÎΪ´¥·¢Æ÷£¿ÔÚSQL ServerÀïÃæÒ²¾ÍÊǶÔijһ¸ö±íµÄÒ»¶¨µÄ²Ù×÷£¬´¥·¢Ä³ÖÖÌõ¼þ£¬´Ó¶øÖ´ÐеÄÒ»¶Î³ÌÐò¡£´¥·¢Æ÷ÊÇÒ»¸öÌØÊâµÄ´æ´¢¹ý³Ì¡£
³£¼ûµÄ´¥·¢Æ÷ÓÐÈýÖÖ£º·Ö±ðÓ¦ÓÃÓÚInsert , Update , Delete ʼþ¡£(SQL Server 2000¶¨ÒåÁËеĴ¥·¢Æ÷£¬Õ ......
SQLServerºÍOracleµÄ³£Óú¯Êý¶Ô±È
¡¡¡¡1.¾ø¶ÔÖµ
¡¡¡¡S:select abs(-1) value
¡¡¡¡O:select abs(-1) value from dual
¡¡¡¡2.È¡Õû(´ó)
¡¡¡¡S:select ceiling(-1.001) value
¡¡¡¡O:select ceil(-1.001) value from dual
¡¡¡¡3.È¡Õû£¨Ð¡£©
¡¡¡¡S:select floor(-1.001) value
¡¡¡¡O:select floor(-1.001) value fr ......
±¾ÎÄÕë¶ÔSQL*Loader¿ØÖÆÎļþ½øÐÐ˵Ã÷¡£
Ò»£ºSQL*Loader¿ØÖÆÎļþµÄÄÚÈÝ
SQL*Loader¿ØÖÆÎļþʹÓÃDDLÃüÁîÀ´¿ØÖÆSQL*Loader»á»°µÄÒÔÏÂÏîÄ¿£º
¡ñʹÓÃSQL*Loaderµ¼ÈëÊý¾ÝµÄλÖÃ
¡ñÊý¾Ý¸ñʽÉ趨·½·¨
¡ñµ¼ÈëÊý¾ÝʱSQL*LoaderµÄÉ趨¡££¨ÄÚ´æ¹ÜÀí¡¢±»¾Ü¾ø¼Ç¼¡¢µ¼Èë´¦ÀíµÄÖжϵȣ©
¡ñµ¼ÈëʱÊý¾ÝµÄ´¦Àí·½·¨
¿ØÖÆÎļþÀý£ºemp.ctl ......
1£©²»Öª´ó¼ÒÊÇ·ñϲ»¶Ê¹ÓÑNOT IN’ÕâÑùµÄ²Ù×÷£¬Èç¹ûÊÇ£¬ÄǾ¡Á¿Ê¹ÓÃ(NOT) EXISTS Ìæ´ú
2£©ÔÚº£Á¿²éѯʱ¾¡Á¿ÉÙÓøñʽת»»¡£
3£©²éѯº£Á¿Êý¾ÝÊÇ£¬¿ÉÒÔʹÓÃoptimizer['ɔptimaizə] hints£¬ÀýÈç/*+ORDERED */
¾¡Á¿ÉÙÓÃIN²Ù×÷·û£¬»ù±¾ÉÏËùÓеÄIN²Ù×÷·û¶¼¿ÉÒÔÓÃEXISTS´úÌæ¡£
²»ÓÃNOT IN²Ù×÷·û£¬¿ÉÒÔ ......