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 ±íÖÐҪɾ³ýµÄÐУ
Ïà¹ØÎĵµ£º
-- ²é¿´µ±Ç°dbµÄµÇ½
select * from sys.sql_logins
-- ÉóºËµÇ½Êý¾Ý¿âµÄÓû§
sql server managerment studioÖУ¬ÓÒ¼üµã¿ª·þÎñÆ÷µÄÊôÐÔ£¬ÔÚ°²È«ÐÔҳǩÖУ¬ Ñ¡ÖÐÉóºË“³É¹¦ºÍʧ°ÜµÄµÇ½”£¬ËùÓеǽ¶¼»áÔÚ..MSSQL\Log\ERRORLOGÖмǼһÌõ¼Ç¼¡£
Èç¹û¹´Ñ¡“ÆôÓÃC2ÉóºË¸ú×Ù”£¬½«»áÔÚ..MSSQL\Log\Ŀ¼ ......
SQL Select IntoÓï¾ä
The SELECT INTO Statement
SELECT INTO Óï¾ä
The SELECT INTO statement is most often used to create backup copies of tables or for archiving records.
SELECT INTOÓï¾ä³£ÓÃÀ´¸øÊý¾Ý±í½¨Á¢±¸·Ý»òÊÇÀúÊ·µµ°¸¡£
Syntax
Óï·¨
SELECT column_name(s) INTO newtable [IN externaldatabase] ......
±¾ÎÄÕë¶ÔSQL*Loader¿ØÖÆÎļþ½øÐÐ˵Ã÷¡£
Ò»£ºSQL*Loader¿ØÖÆÎļþµÄÄÚÈÝ
SQL*Loader¿ØÖÆÎļþʹÓÃDDLÃüÁîÀ´¿ØÖÆSQL*Loader»á»°µÄÒÔÏÂÏîÄ¿£º
¡ñʹÓÃSQL*Loaderµ¼ÈëÊý¾ÝµÄλÖÃ
¡ñÊý¾Ý¸ñʽÉ趨·½·¨
¡ñµ¼ÈëÊý¾ÝʱSQL*LoaderµÄÉ趨¡££¨ÄÚ´æ¹ÜÀí¡¢±»¾Ü¾ø¼Ç¼¡¢µ¼Èë´¦ÀíµÄÖжϵȣ©
¡ñµ¼ÈëʱÊý¾ÝµÄ´¦Àí·½·¨
¿ØÖÆÎļþÀý£ºemp.ctl ......
ÔÚSQL Server 2008ÖÐÒýÈëÁËhierarchyidÀ´´¦ÀíÊ÷×´½á¹¹¡£ÏÂÃæ¼òµ¥¾ÍÒÔAdventureWorks(ÎÞhierarchyid)ºÍAdventureWorks2008(ÓÐhierarchyid)ÀïµÄHumanResources.EmployeeΪÀý£¬À´ËµÃ÷Ò»ÏÂÔÚеÄhierarchyidÖÐÈçºÎ½øÐÐflat»¯µÄdimension³éÈ¡¡£
AdventureWorksÊÇ΢ÈíΪSQL ServerÌṩµÄÊý¾Ý¿âÐéÄâ°¸Àý¡£
AdventureWorksÖÐËùÒªµ ......