SQLÓï¾ä ´´½¨Ë÷Òý
Óï·¨£º
CREATE [Ë÷ÒýÀàÐÍ] INDEX Ë÷ÒýÃû³Æ
ON ±íÃû(ÁÐÃû)
WITH FILLFACTOR = Ìî³äÒò×ÓÖµ0~100
GO
/*ʵÀý*/
USE ¿âÃû
GO
IF EXISTS
(SELECT * from SYSINDEXES WHERE NAME='IX_TEST_TNAME')--¼ì²âÊÇ·ñÒѾ´æÔÚIX_TEST_TNAMEË÷Òý
DROP INDEX
TEST.IX_TEST_TNAME--Èç¹û´æÔÚÔòɾ³ý
--´´½¨Ë÷Òý
CREATE NONCLUSTERED INDEX
IX_TEST_TNAME --´´½¨Ò»¸ö·Ç¾Û¼¯Ë÷Òý
ON TEST(TNAME) --ΪTEST±íµÄTNAME×ֶδ´½¨Ë÷Òý
WITH FILLFACTOR = 30 --Ìî³äÒò×ÓΪ30%
GO
SELECT * from TEST(INDEX = IX_TEST_TNAME) WHERE TNAME = 'A' --Ö¸¶¨°´‘IX_TEST_TNAME’Ë÷Òý²éѯ
×ܽ᣺
1.ʲôÊÇË÷Òý£º
Êý¾Ý¿âÖеÄË÷ÒýÊÇij¸ö±íÖÐÒ»Áлò¶àÁÐÖµµÄ¼¯ºÏºÍÏàÓ¦µÄÖ¸Ïò±íÖÐÎïÀí±êʶÕâЩֵµÄÊý¾ÝÒ³µÄÂß¼Ö¸ÕëÇåµ¥¡£
2.·ÖÀࣺ
ΨһË÷Òý(UNIQUE)£º
²»ÔÊÐíÁ½ÐоßÓÐÏàͬµÄË÷ÒýÖµ£¨´´½¨ÁËΨһԼÊø£¬ÏµÍ³½«×Ô¶¯´´½¨Î¨Ò»Ë÷Òý£©
Ö÷¼üË÷Òý
£º
Ö÷¼üË÷ÒýÒªÇóÖ÷¼üÖеÄÿ¸öÖµÊÇΨһµÄ£¬£¨´´½¨Ö÷¼ü×Ô¶¯´´½¨Ö÷¼üË÷Òý£©
¾Û¼¯Ë÷Òý(CLUSTERED)£º
±íÖи÷ÐеÄÎïÀí˳ÐòÓë¼üÖµµÄÂß¼£¨Ë÷Òý£©Ë³ÐòÏàͬ£¬±íÖÐÖ»ÄÜ°üº¬Ò»¸ö¾Û¼¯Ë÷Òý£¬Ö÷¼üÁÐĬÈÏΪ¾Û¼¯Ë÷Òý
·Ç¾Û¼¯Ë÷Òý(NONCLUSTERED)£º
±íÖи÷ÐеÄÎïÀí˳ÐòÓë¼üÖµµÄÂß¼£¨Ë÷Òý£©Ë³Ðò²»Æ¥Å䣬±íÖпÉÒÔÓÐ249¸ö·Ç¾Û¼¯Ë÷Òý
3.´´½¨Ë÷ÒýµÄ±ê×¼
£ºÓÃÓÚ
Ƶ·±ËÑË÷µÄÁУ»ÓÃÓÚ¶ÔÊý¾Ý½øÐÐÅÅÐòµÄÁÐ
×¢Ò⣺Èç¹û±íÖнöÓм¸ÐУ¬»òÁÐÖÐÖ»°üº¬¼¸¸ö²»Í¬µÄÖµ£¬²»ÍƼö´´½¨Ë÷Òý£¬ÒòΪSQL Server ÔÚСÐͱíÖÐÓÃË÷ÒýËÑË÷Êý¾ÝËù»¨µÄʱ¼ä±ÈÖðÐÐËÑË÷¸ü³¤¡£
ʵ
¼ÊÉÏ£¬Äú¿ÉÒÔ°ÑË÷ÒýÀí½âΪһÖÖÌØÊâµÄĿ¼¡£Î¢ÈíµÄSQL SERVERÌṩÁËÁ½ÖÖË÷Òý£º¾Û¼¯Ë÷Òý£¨clustered
index£¬Ò²³Æ¾ÛÀàË÷Òý¡¢´Ø¼¯Ë÷Òý£©ºÍ·Ç¾Û¼¯Ë÷Òý£¨nonclustered
index£¬Ò²³Æ·Ç¾ÛÀàË÷Òý¡¢·Ç´Ø¼¯Ë÷Òý£©¡£ÏÂÃ棬ÎÒÃǾÙÀýÀ´ËµÃ÷һϾۼ¯Ë÷ÒýºÍ·Ç¾Û¼¯Ë÷ÒýµÄÇø±ð£º
Æä
ʵ£¬ÎÒÃǵĺºÓï×ÖµäµÄÕýÎı¾Éí¾ÍÊÇÒ»¸ö¾Û¼¯Ë÷Òý¡£±ÈÈ磬ÎÒÃÇÒª²é“°²”×Ö£¬¾Í»áºÜ×ÔÈ»µØ·¿ª×ÖµäµÄÇ°¼¸Ò³£¬ÒòΪ“°²”µÄÆ´ÒôÊÇ“an”£¬¶ø°´ÕÕÆ´ÒôÅÅÐòºº
×ÖµÄ×ÖµäÊÇÒÔÓ¢ÎÄ×Öĸ“a”¿ªÍ·²¢ÒÔ“z”½áβµÄ£¬ÄÇô“°²”×Ö¾Í×ÔÈ»µØÅÅÔÚ×ÖµäµÄÇ°²¿¡£Èç¹ûÄú·
Ïà¹ØÎĵµ£º
SELECT tagid, tagname from uchome_mtag WHERE tagname IN (SELECT tagname from uchome_mtag GROUP BY tagname HAVING (COUNT(tagname) > 1)) ORDER BY tagname ......
Ò»¡¢SQL ServerÖÐÊý¾ÝÐеĴ洢·½Ê½
ÔÚSQL ServerÖдæ·ÅÊý¾ÝµÄÎļþ»áÒÔ8KBµÄ´óС·ÖÒ³¡£Ã¿Ò»Ò³¿ÉÒÔÊÇÊý¾Ý¡¢Ë÷ÒýÒÔ¼°ÆäËûSQL ServerÊý¾Ý¿âÐèҪΪÆäά»¤Êý¾ÝÎļþµÄÊý¾ÝÀàÐÍ¡£´ó¶àÊýÒ³ÊÇÊý¾ÝÒ³»òË÷ÒýÒ³¡£Ò³ÊÇSQL Server¶Á¡¢Ð´Êý¾ÝÎļþµÄµ¥Ôª¡£Ã¿Ò»Ò³Ö»°üÀ¨Ò»¸ö¶ÔÏóµÄÊý¾Ý»òË÷ÒýÐÅÏ¢£¬ËùÒÔÔÚÿһ¸öÊý¾Ý ......
distinctÕâ¸ö¹Ø¼ü×ÖÓÃÀ´¹ýÂ˵ô¶àÓàµÄÖظ´¼Ç¼ֻ±£ÁôÒ»Ìõ£¬µ«ÍùÍùÖ»ÓÃËüÀ´·µ»Ø²»Öظ´¼Ç¼µÄÌõÊý£¬¶ø²»ÊÇÓÃËüÀ´·µ»Ø²»ÖؼǼµÄËùÓÐÖµ¡£ÆäÔÒòÊÇdistinctÖ»ÓÐÓöþÖØÑ»·²éѯÀ´½â¾ö£¬¶øÕâÑù¶ÔÓÚÒ»¸öÊý¾ÝÁ¿·Ç³£´óµÄÕ¾À´Ëµ£¬ÎÞÒÉÊÇ»áÖ±½ÓÓ°Ï쵽ЧÂʵġ£
ÏÂÃæÏÈÀ´¿´¿´Àý×Ó£º
table±í
×Ö¶Î1 ×Ö¶ ......
SQL ½Ì³Ì
1£©SQL ¼ò½é
SQL(Structured Query Language£¬½á¹¹²éѯÓïÑÔ)ÊÇÒ»¸ö¹¦ÄÜÇ¿´óµÄÊý¾Ý¿âÓïÑÔ¡£SQLͨ³£Ê¹ÓÃÓÚÊý¾Ý¿âµÄͨѶ¡£ANSI£¨ÃÀ¹ú¹ú¼Ò±ê׼ѧ»á£©Éù³Æ£¬SQLÊǹØϵÊý¾Ý¿â¹ÜÀíϵͳµÄ±ê×¼ÓïÑÔ¡£SQLÓï¾äͨ³£ÓÃÓÚÍê³ÉһЩÊý¾Ý¿âµÄ²Ù×÷ÈÎÎñ£¬±ÈÈçÔÚÊý¾Ý¿âÖиüÐÂÊý¾Ý£¬»òÕß´ÓÊý¾Ý¿âÖмìË÷Êý¾Ý¡£Ê¹ÓÃSQLµÄ³£¼û¹ØϵÊý¾Ý¿â¹ ......
ÎÒÃÇÒª×öµ½²»µ«»áдSQL,»¹Òª×öµ½Ð´³öÐÔÄÜÓÅÁ¼µÄSQL,ÒÔÏÂΪ±ÊÕßѧϰ¡¢ÕªÂ¼¡¢²¢»ã×ܲ¿·Ö×ÊÁÏÓë´ó¼Ò·ÖÏí£¡
£¨1£© Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)£º
ORACLE µÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬from×Ó¾äÖÐдÔÚ×îºóµÄ±í(»ù´¡±í driving table)½«±»×îÏÈ´¦Àí£¬ÔÚfrom× ......