SQL 2005 ´´½¨Ô¼Êø
²Î¼û¡¶SQL Sever 2005 Êý¾Ý¿â»ù´¡¼°Ó¦Óü¼Êõ½Ì³ÌÓëʵѵ¡· ÖÜÆæ
SQL ServerÖÐÓÐÎåÖÖÔ¼ÊøÀàÐÍ£¬·Ö±ðÊÇCHECKÔ¼Êø¡¢DEFAULTÔ¼Êø¡¢PRIMARY KEYÔ¼Êø¡¢FOREIGN KEYÔ¼ÊøºÍUNIQUEÔ¼Êø¡£
1. CHECKÔ¼Êø£º
CHECKÔ¼ÊøÓÃÓÚÏÞÖÆÊäÈëÒ»Áлò¶àÁеÄÖµµÄ·¶Î§£¬Í¨¹ýÂß¼±í´ïʽÀ´ÅжÏÊý¾ÝµÄÓÐЧÐÔ¡£Ò»¸öÁеÄÊäÈëÄÚÈݱØÐëÂú×ãCHECKÔ¼ÊøµÄÌõ¼þ£¬·ñÔòÊý¾ÝÎÞ·¨Õý³£ÊäÈë¡£
ÏÂÃæÎÒÃÇÒª¶ÔÒÔϵıíÌí¼ÓCHECKÔ¼Êø£¬
Name
Sex
Adams
M
Berry
F
Hebe
f
Jack
M
ÒÔÉϵÄÔ¼Êø¿ÉÒÔÏÞÖÆSexÁеÄÈ¡ÖµÖ»ÄÜΪ’M’»òÕß’F’¡£Èç¹ûÄã²åÈëÐÂÐУ¬»ò¸ü¸ÄijһÐв»·ûºÏ¸ÃÔ¼Êø£¬¾Í»á±¨´í¡£
2. DEFAULTÔ¼Êø
ÈôÔÚ±íÖÐijÁж¨ÒåÁËDEFAULTÔ¼Êø£¬Óû§ÔÚ²åÈëеÄÊý¾ÝÐÐʱ£¬Èç¹û¸ÃÁÐûÓÐÖ¸¶¨Êý¾Ý£¬ÄÇôϵͳ½«Ä¬ÈÏÖµ¸³¸ø¸ÃÁУ¬µ±È»¸ÃĬÈÏÖµÒ²¿ÉÒÔÊÇ¿ÕÖµ(NULL)¡£
´´½¨DEFAULTÔ¼ÊøÐèÒªÔÚ±íÉè¼ÆÆ÷ÖУ¬ÌîдÁÐÊôÐԵĔĬÈÏÖµ”.
3. PRIMARY KEYÔ¼Êø
ÔÚ±íÖг£ÓÐÒ»Áлò¶àÁеÄ×éºÏ£¬ÆäÖµÄÜΨһ±êʶ±íÖеÄÿһÐС£ÕâÑùµÄÒ»Áлò¶àÁгÉΪ±íµÄÖ÷¼ü(Primary Key)¡£Ò»¸ö±íÖ»ÄÜÓÐÒ»¸öÖ÷¼ü£¬¶øÇÒÖ÷¼üÔ¼ÊøÖеÄÁв»ÄÜΪ¿ÕÖµ¡£
4. FOREIGN KEYÔ¼Êø
Íâ¼ü(Foreign Key)ÊÇÓÃÓÚ½¨Á¢ºÍ¼ÓÇ¿Á½¸ö±í(Ö÷±íÓë´Ó±í)µÄÒ»Áлò¶àÁÐÊý¾ÝÖ®¼äµÄÁ¬½ÓµÄ¡£´´½¨Ô¼ÊøµÄ˳ÐòÊÇÏȶ¨ÒåÖ÷±íµÄÖ÷¼ü£¬ÔÙ¶Ô´Ó±í¶¨ÒåÍâ¼üÔ¼Êø¡£
ÏȶÔCountry±íÉèÖÃÖ÷¼ü£¬È»ºó¶ÔAuthor±í´´½¨¹Øϵ¡£
ÉèÖýáÊøÒԺ󣬿ÉÒÔ·¢ÏÖAuthor±íµÄCountryIDÁбäΪÁËÍâ¼ü¡£
5. UNIQUEÔ¼Êø
UNIQUEÔ¼ÊøÓÃÓÚÈ·±£±íÖеÄÁ½¸öÊý¾ÝÐÐÔÚ·ÇÖ÷¼üÖÐûÓÐÏàͬµÄÁÐÖµ¡£ÓëPRIMARY KEYÔ¼ÊøÀàËÆ£¬UNIQUEÔ¼ÊøҲǿÖÆΨһÐÔ£¬µ«UNIQUEÔ¼ÊøÓÃÓÚ·ÇÖ÷¼üµÄÒ»Áлò¶àÁÐ×éºÏ£¬ÇÒÒ»¸ö±í¿ÉÒÔ¶¨Òå¶à¸öUNIQUEÔ¼Êø£¬ÁíÍâUNIQUEÔ¼Êø¿ÉÒÔÓÃÓÚ¶¨Òå¶àÁÐ×éºÏ¡£
°´ÕÕÉÏͼËùʾ²Ù×÷£¬Author±í¾Í¶àÁËÒ»¸ö¶ÔNameÁеÄUNIQUE²Ù×÷¡£ÕâÑù£¬Author±í¾Í²»»áÓÐÁ½ÐоßÓÐÏàͬNameµÄ¼Ç¼ÁË¡£
Ïà¹ØÎĵµ£º
×î½üÐÞ¸ÄÊý¾Ý¿âÒѾ´æÔڵıí½á¹¹£¬·¢ÏÖÓÃSQLÓï¾äÄܺܺõÄÐ޸ıíµÄ½á¹¹¡£SQLÊý¾Ý¿â¼Ç¼Êý·Ç³£ÅÓ´ó£¬Èç¹ûÏëÐ޸ıíµÄ×Ö¶ÎÀàÐÍ£¬Ê¹ÓÃÆóÒµ¹ÜÀíÆ÷À´¸ü¸Ä×Ö¶ÎÀàÐÍ£¬ºÜ¶àʱºò»á³¬Ê±£¬¸ü¸Ä²»³É¹¦£¬Õâ¸öʱºò£¬¿ÉÒÔÓÃSQLÓï¾äÀ´¸ü¸Ä£¬ÒÔÏÂΪÏàÓ¦µÄ´úÂëʵÀý£º
1.¸ü¸Ä×Ö¶ÎÀàÐͳ¤¶È
alert table ±í alert column ×Ö¶ÎÃû ÀàÐ͵ij¤¶È
eg: ......
1¡¢ ÓóÌÐòÖУ¬±£Ö¤ÔÚʵÏÖ¹¦ÄܵĻù´¡ÉÏ£¬¾¡Á¿¼õÉÙ¶ÔÊý¾Ý¿âµÄ·ÃÎÊ´ÎÊý£»Í¨¹ýËÑË÷²ÎÊý£¬¾¡Á¿¼õÉÙ¶Ô±íµÄ·ÃÎÊÐÐÊý,×îС»¯½á¹û¼¯£¬´Ó¶ø¼õÇáÍøÂ縺µ££»Äܹ»·Ö¿ªµÄ ²Ù×÷¾¡Á¿·Ö¿ª´¦Àí£¬Ìá¸ßÿ´ÎµÄÏìÓ¦Ëٶȣ»ÔÚÊý¾Ý´°¿ÚʹÓÃSQLʱ£¬¾¡Á¿°ÑʹÓõÄË÷Òý·ÅÔÚÑ¡ÔñµÄÊ×ÁУ»Ëã·¨µÄ½á¹¹¾¡Á¿¼òµ¥£»ÔÚ²éѯʱ£¬²»Òª¹ý¶àµØʹÓÃͨÅä·û ÈçSELE ......
1¡¢²éÕÒ±íÖжàÓàµÄÖظ´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ýµ¥¸ö×ֶΣ¨peopleId£©À´ÅжÏ
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2¡¢É¾³ý±íÖжàÓàµÄÖظ´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ýµ¥¸ö× ......