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µÄ¼Ç¼ÁË¡£
Ïà¹ØÎĵµ£º
createdb.batÎļþ
@echo off
echo ╬¡¡╱◥███◣╬╬╬╬╬╬╬╬╬╬╬╬╬╬╬╬╬
echo ╬ ︱Ìï︱Ìï Ìï ︱¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡ ......
¹ØÓÚͨ¹ýÍâÍøÁ¬½ÓÄÚÍøSQL Server·þÎñÆ÷µÄ·½·¨
½üÈÕ£¬ÔÚÂÛ̳ÉÏ¿´µ½µÄÓйØSQL ServerÔ¶³ÌÁ¬½ÓµÄÎÄÕºó£¬ÕæµÄÊÜÒæÁ¼¶à¡£Ò»Ö±ÒÔÀ´À§»óÁËÒѾõĹØÓÚ´ÓÍâÍøÈçºÎÁ¬½Óµ½ÄÚÍøµÄSQL Server·þÎñÆ÷Éϵķ½·¨£¬ÏÖÒѽâ¾öÁË£¬ÏàÐÅÒ²Óв»ÉÙÏóÎÒÕâÑùµÄºüÓÑÃÇ¡£¹Ê´Ë£¬ÏÖÔÚ°ÑÎÒʵÏֵķ½·¨Ä ......
1¡¢ ÓóÌÐòÖУ¬±£Ö¤ÔÚʵÏÖ¹¦ÄܵĻù´¡ÉÏ£¬¾¡Á¿¼õÉÙ¶ÔÊý¾Ý¿âµÄ·ÃÎÊ´ÎÊý£»Í¨¹ýËÑË÷²ÎÊý£¬¾¡Á¿¼õÉÙ¶Ô±íµÄ·ÃÎÊÐÐÊý,×îС»¯½á¹û¼¯£¬´Ó¶ø¼õÇáÍøÂ縺µ££»Äܹ»·Ö¿ªµÄ ²Ù×÷¾¡Á¿·Ö¿ª´¦Àí£¬Ìá¸ßÿ´ÎµÄÏìÓ¦Ëٶȣ»ÔÚÊý¾Ý´°¿ÚʹÓÃSQLʱ£¬¾¡Á¿°ÑʹÓõÄË÷Òý·ÅÔÚÑ¡ÔñµÄÊ×ÁУ»Ëã·¨µÄ½á¹¹¾¡Á¿¼òµ¥£»ÔÚ²éѯʱ£¬²»Òª¹ý¶àµØʹÓÃͨÅä·û ÈçSELE ......
¡¡¡¡ÔÚÊý¾Ý¿âµÄ¿ª·¢¹ý³ÌÖУ¬¾³£»áÓöµ½¸´ÔÓµÄÒµÎñÂß¼ºÍ¶ÔÊý¾Ý¿âµÄ²Ù×÷£¬Õâ¸öʱºò¾Í»áÓÃSPÀ´·â×°Êý¾Ý¿â²Ù×÷¡£Èç¹ûÏîÄ¿µÄSP½Ï¶à£¬ÊéдÓÖûÓÐÒ»¶¨µÄ¹æ·¶£¬½«»áÓ°ÏìÒÔºóµÄϵͳά»¤À§ÄѺʹóSPÂß¼µÄÄÑÒÔÀí½â£¬ÁíÍâÈç¹ûÊý¾Ý¿âµÄÊý¾ÝÁ¿´ó»òÕßÏîÄ¿¶ÔSPµÄÐÔÄÜÒªÇóºÜ£¬¾Í»áÓöµ½ÓÅ»¯µÄÎÊÌ⣬·ñÔòËÙ¶ÈÓпÉÄܺÜÂý£¬¾¹ýÇ×Éí¾Ñ飬һ¸ö¾ ......
ÐÔÄܼà¿ØÖ®¼à¿ØSQLÓï¾ä
·ÖÎö±í
analyze table tablename compute statistics for all indexes;
analyze table tablename compute statistics for all indexed columns;
analyze table tablename compute statistics for table;
¼à¿ØÊÂÀýµÄµÈ´ý
select event,sum(decode(wait_Tim ......