Ó°ÏìSQL serverÐÔÄܵĹؼüÈý¸ö·½Ãæ
1 Âß¼Êý¾Ý¿âºÍ±íµÄÉè¼Æ
Êý¾Ý¿âµÄÂß¼Éè¼Æ¡¢°üÀ¨±íÓë±íÖ®¼äµÄ¹ØÏµÊÇÓÅ»¯¹ØÏµÐÍÊý¾Ý¿âÐÔÄܵĺËÐÄ¡£Ò»¸öºÃµÄÂß¼Êý¾Ý¿âÉè¼Æ¿ÉÒÔΪÓÅ»¯Êý¾Ý¿âºÍÓ¦ÓóÌÐò´òÏÂÁ¼ºÃµÄ»ù´¡¡£
±ê×¼»¯µÄÊý¾Ý¿âÂß¼Éè¼Æ°üÀ¨ÓöàµÄ¡¢ÓÐÏ໥¹ØÏµµÄÕ±íÀ´´úÌæºÜ¶àÁеij¤Êý¾Ý±í¡£ÏÂÃæÊÇһЩʹÓñê×¼»¯
±íµÄһЩºÃ´¦¡£
A:ÓÉÓÚ±íÕ£¬Òò´Ë¿ÉÒÔʹÅÅÐòºÍ½¨Á¢Ë÷Òý¸üΪѸËÙ
B:ÓÉÓÚ¶à±í£¬ËùÒÔ¶àïßµÄË÷Òý³ÉΪ¿ÉÄÜ
C:¸üÕ¸ü½ô´ÕµÄË÷Òý
D:ÿ¸ö±íÖпÉÒÔÓÐÉÙһЩµÄË÷Òý£¬Òò´Ë¿ÉÒÔÌá¸ßinsert update deleteµÈµÄËÙ¶È£¬ÒòΪÕâЩ²Ù×÷ÔÚË÷Òý¶àµÄÇé¿öÏ»á¶ÔϵͳÐÔÄܲúÉúºÜ´óµÄÓ°Ïì
E:¸üÉٵĿÕÖµºÍ¸üÉٵĶàÓàÖµ£¬Ôö¼ÓÁËÊý¾Ý¿âµÄ½ô´ÕÐÔ
ÓÉÓÚ±ê×¼»¯£¬ËùÒÔ»áÔö¼ÓÁËÔÚ»ñÈ¡Êý¾ÝʱÒýÓñíµÄÊýÄ¿ºÍÆä¼äµÄÁ¬½Ó¹ØÏµµÄ¸´ÔÓÐÔ¡£Ì«¶àµÄ±íºÍ¸´ÔÓµÄÁ¬½Ó¹ØÏµ»á½µµÍ·þÎñÆ÷µÄÐÔÄÜ£¬Òò´ËÔÚÕâÁ½ÕßÖ®¼äÐèÒª×ۺϿ¼ÂÇ¡£¶¨Òå¾ßÓÐÏà¹Ø¹ØÏµµÄÖ÷¼üºÍÍâÀ´¼üʱӦ¸Ã×¢ÖØµÄÊÂÏîÖ÷ÒªÊÇ£ºÓÃÓÚÁ¬½Ó¶à±íµÄÖ÷¼üºÍ²Î¿¼µÄ¼üÒªÓÐÏàͬµÄÊý¾ÝÀàÐÍ¡£
2 Ë÷ÒýµÄÉè¼Æ
A:¾¡Á¿±ÜÃâ±íɨÃè
¼ì²éÄãµÄ²éѯÓï¾äµÄwhere×Ӿ䣬ÒòΪÕâÊÇÓÅ»¯Æ÷ÖØÒª¹Ø×¢µÄµØ·½¡£°üº¬ÔÚwhereÀïÃæµÄÿһÁУ¨column)¶¼ÊÇ¿ÉÄܵĺîÑ¡Ë÷Òý£¬ÎªÄÜ´ïµ½×îÓŵÄÐÔÄÜ£¬¿¼ÂÇÔÚÏÂÃæ¸ø³öµÄÀý×Ó£º¶ÔÓÚÔÚwhere×Ó¾äÖиø³öÁËcolumn1Õâ¸öÁС£
ÏÂÃæµÄÁ½¸öÌõ¼þ¿ÉÒÔÌá¸ßË÷ÒýµÄÓÅ»¯²éѯÐÔÄÜ£¡
µÚÒ»£ºÔÚ±íÖеÄcolumn1ÁÐÉÏÓÐÒ»¸öµ¥Ë÷Òý
µÚ¶þ£ºÔÚ±íÖÐÓжàË÷Òý£¬µ«ÊÇcolumn1ÊǵÚÒ»¸öË÷ÒýµÄÁÐ
±ÜÃⶨÒå¶àË÷Òý¶øcolumn1Êǵڶþ¸ö»òºóÃæµÄË÷Òý£¬ÕâÑùµÄË÷Òý²»ÄÜÓÅ»¯·þÎñÆ÷ÐÔÄÜ
ÀýÈ磺ÏÂÃæµÄÀý×ÓÓÃÁËpubsÊý¾Ý¿â¡£
SELECT au_id, au_lname, au_fname FROM authors
WHERE au_lname = 'White'
°´ÏÂÃæ¼¸¸öÁÐÉϽ¨Á¢µÄË÷Òý½«»áÊǶÔÓÅ»¯Æ÷ÓÐÓõÄË÷Òý
au_lname
au_lname, au_fname
¶øÔÚÏÂÃæ¼¸¸öÁÐÉϽ¨Á¢µÄË÷Òý½«²»»á¶ÔÓÅ»¯Æ÷Æðµ½ºÃµÄ×÷ÓÃ
au_address
au_fname, au_lname
¿¼ÂÇʹÓÃÕµÄË÷ÒýÔÚÒ»¸ö»òÁ½¸öÁÐÉÏ£¬ÕË÷Òý±È¶àË÷ÒýºÍ¸´ºÏË÷Òý¸üÄÜÓÐЧ¡£ÓÃÕµÄË÷Òý£¬ÔÚÿһҳÉÏ
½«»áÓиü¶àµÄÐк͸üÉÙµÄË÷Òý¼¶±ð£¨Ïà¶ÔÓë¶àË÷ÒýºÍ¸´ºÏË÷Òý¶øÑÔ£©£¬Õâ½«ÍÆ½øÏµÍ³ÐÔÄÜ¡£
¶ÔÓÚ¶àÁÐË÷Òý£¬SQL Serverά³ÖÒ»¸öÔÚËùÓÐÁеÄË÷ÒýÉϵÄÃܶÈͳ¼Æ£¨ÓÃÓÚÁªºÏ£©ºÍÔÚµÚÒ»¸öË÷ÒýÉϵÄ
histogram£¨Öù״ͼ£©Í³¼Æ¡£¸ù¾Ýͳ¼Æ½á¹û£¬¼ÙÈçÔÚ¸´ºÏË÷ÒýÉϵĵÚÒ»¸öË÷ÒýºÜÉÙ±»Ñ¡ÔñʹÓã¬ÄÇôÓÅ»¯Æ÷¶ÔºÜ¶à²éѯÇëÇ󽫲»»áʹÓÃË÷Òý¡£
ÓÐÓõÄË÷Òý»áÌá¸ßselectÓï¾äµÄÐÔÄÜ£¬°üÀ¨insert,uodate,delete¡£
µ«ÊÇ£¬ÓÉÓڸıäÒ»¸ö±íµÄÄÚÈÝ£¬½«»áÓ°ÏìË÷Òý¡£Ã¿Ò»¸öinsert,update,deleteÓï¾ä½«»áʹÐÔÄÜϽµÒ»Ð©¡£ÊµÑé±íÃ÷£¬²»ÒªÔÚÒ»¸öµ¥±íÉÏÓôóÁ¿µÄË÷Òý£¬²»ÒªÔÚ¹²ÏíµÄÁÐÉÏ£¨Ö¸ÔÚ¶à±íÖÐÓÃÁ˲ο¼Ô¼Êø£©Ê¹ÓÃÖØµþµÄË÷Òý¡£
ÔÚijһÁÐÉϼì²éΨһµÄÊý¾ÝµÄ¸öÊý£¬±È½ÏËüÓë±íÖÐÊý¾ÝµÄÐÐÊý×öÒ»¸ö±È½Ï¡£Õâ¾ÍÊÇÊý¾ÝµÄÑ¡ÔñÐÔ£¬Õâ±È½Ï½á¹û½«»á°ïÖúÄã¾ö¶¨ÊÇ·ñ½«Ä³Ò»ÁÐ×÷ΪºîÑ¡µÄË÷ÒýÁУ¬¼ÙÈçÐèÒª£¬½¨ÄÄÒ»ÖÖË÷Òý¡£Äã¿ÉÒÔÓÃÏÂÃæµÄ²éѯÓï¾ä·µ»ØÄ³Ò»ÁеIJ»Í¬ÖµµÄÊýÄ¿¡£
select count(distinct cloumn_name) from table_name
¼ÙÉècolumn_nameÊÇÒ»¸ö10000ÐÐµÄ±í£¬Ôò¿´column_name·µ»ØÖµÀ´¾ö¶¨ÊÇ·ñÓ¦¸ÃʹÓ㬼°Ó¦¸ÃʹÓÃʲôË÷Òý¡£
Unique values Index
5000 Nonclustered index
20 Clustered index
3 No index
ïßË÷ÒýºÍ·ÇïßË÷ÒýµÄÑ¡Ôñ
<1>ïßË÷ÒýÊÇÐеÄÎïÀí˳ÐòºÍË÷ÒýµÄ˳ÐòÊÇÒ»Öµġ£Ò³¼¶£¬µÍ²ãµÈË÷ÒýµÄ¸÷¸ö¼¶±ðÉ϶¼°üº¬Êµ¼ÊµÄÊý¾ÝÒ³¡£Ò»¸ö±íÖ»ÄÜÊÇÓÐÒ»¸öïßË÷Òý¡£ÓÉÓÚupdate,deleteÓï¾äÒªÇóÏà¶Ô¶àһЩµÄ¶Á²Ù×÷£¬Òò´ËïßË÷Òý¾³£ÄܼÓËÙÕâÑùµÄ²Ù×÷¡£ÔÚÖÁÉÙÓÐÒ»¸öË÷ÒýµÄ±íÖУ¬ÄãÓ¦¸ÃÓÐÒ»¸öïßË÷Òý¡£
ÔÚÏÂÃæµÄ¼¸¸öÇé¿öÏ£¬Äã¿ÉÒÔ¿¼ÂÇÓÃïßË÷Òý£º
ÀýÈ磺 ijÁаüÀ¨µÄ²»Í¬ÖµµÄ¸öÊýÊÇÓÐÏ޵썵«ÊDz»ÊǼ«Éٵģ©
¹Ë¿Í±íµÄÖÝÃûÁÐÓÐ50¸ö×óÓҵIJ»Í¬ÖÝÃûµÄËõдֵ£¬¿ÉÒÔʹÓÃïßË÷Òý¡£
ÀýÈ磺 ¶Ô·µ»ØÒ»¶¨·¶Î§ÄÚÖµµÄÁпÉÒÔʹÓÃïßË÷Òý£¬±ÈÈçÓÃbetween,>,>=,<,<=µÈµÈÀ´¶ÔÁнøÐвÙ×÷µÄÁÐÉÏ¡£
select * from sales where ord_date between '5/1/93' and '6/1/93'
ÀýÈ磺 ¶Ô²éѯʱ·µ»Ø´óÁ¿½á¹ûµÄÁпÉÒÔʹÓÃïßË÷Òý¡£
SELECT * FROM phonebook WHERE last_name = 'Smith'
µ±ÓдóÁ¿µÄÐÐÕýÔÚ±»²åÈë±íÖÐʱ£¬Òª±ÜÃâÔÚ±¾±íÒ»¸ö×ÔÈ»Ôö³¤£¨ÀýÈ磬identityÁУ©µÄÁÐÉϽ¨Á¢ïßË÷Òý¡£¼ÙÈçÄ㽨Á¢ÁËïßµÄË÷Òý£¬ÄÇôinsertµÄÐÔÄܾͻá´ó´ó½µµÍ¡£ÒòΪÿһ¸ö²åÈëµÄÐбØÐëµ½±íµÄ×îºó£¬±íµÄ×îºóÒ»¸öÊý¾ÝÒ³¡£
µ±Ò»¸öÊý¾ÝÕýÔÚ±»²åÈ루ÕâʱÕâ¸öÊý¾ÝÒ³ÊDZ»Ëø¶¨µÄ£©£¬ËùÓÐµÄÆäËû²åÈëÐбØÐëµÈ´ýÖ±µ½µ±Ç°µÄ²åÈëÒѾ½áÊø¡£
Ò»¸öË÷ÒýµÄÒ¶¼¶Ò³ÖаüÀ¨Êµ¼ÊµÄÊý¾ÝÒ³£¬²¢ÇÒÔÚÓ²ÅÌÉϵÄÊý¾ÝÒ³µÄ´ÎÐòÊǸúïßË÷ÒýµÄÂß¼´ÎÐòÒ»ÑùµÄ¡£
<2>Ò»¸ö·ÇïßµÄË÷Òý¾ÍÊÇÐеÄÎïÀí´ÎÐòÓëË÷ÒýµÄ´ÎÐòÊDz»Í¬µÄ¡£Ò»¸ö·ÇïßË÷ÒýµÄÒ¶¼¶°üº¬ÁËÖ¸ÏòÐÐÊý¾ÝÒ³µÄÖ¸Õë¡£
ÔÚÒ»¸ö±íÖпÉÒÔÓжà¸ö·ÇïßË÷Òý£¬Äã¿ÉÒÔÔÚÒÔϼ¸¸öÇé¿öÏ¿¼ÂÇʹÓ÷ÇïßË÷Òý¡£
ÔÚÓкܶ಻ֵͬµÄÁÐÉÏ¿ÉÒÔ¿¼ÂÇʹÓ÷ÇïßË÷Òý
ÀýÈ磺һ¸öpart_idÁÐÔÚÒ»¸öpart±íÖÐ
select * from employee where emp_id = 'pcm9809f'
²éѯÓï¾äÖÐÓÃorder by ×Ó¾äµÄÁÐÉÏ¿ÉÒÔ¿¼ÂÇʹÓÃïßË÷Òý
3 ²éѯÓï¾äµÄÉè¼Æ
SQL ServerÓÅ»¯Æ÷ͨ¹ý·ÖÎö²éѯÓï¾ä£¬×Ô¶¯¶Ô²éѯ½øÐÐÓÅ»¯²¢¾ö¶¨×îÓÐЧµÄÖ´Ðз½°¸¡£ÓÅ»¯Æ÷·ÖÎö²éѯÓï¾äÀ´¾ö¶¨ÄǸö×Ó¾ä¿ÉÒÔ±»ÓÅ»¯£¬²¢Õë¶Ô¿ÉÒÔ±»ÓÅ»¯²éѯµÄ×Ó¾äÀ´Ñ¡ÔñÓÐÓõÄË÷Òý¡£×îºóÓÅ»¯Æ÷±È½ÏËùÓпÉÄܵÄÖ´Ðз½°¸²¢Ñ¡Ôñ×îÓÐЧµÄÒ»¸ö·½°¸³öÀ´¡£
ÔÚÖ´ÐÐÒ»¸ö²éѯʱ£¬ÓÃÒ»¸öwhere×Ó¾äÀ´ÏÞÖÆ±ØÐë´¦ÀíµÄÐÐÊý£¬³ý·ÇÍêÈ«ÐèÒª£¬·ñÔòÓ¦¸Ã±ÜÃâÔÚÒ»¸ö±íÖÐÎÞÏÞÖÆµØ¶Á²¢´¦ÀíËùÓеÄÐС£
ÀýÈçÏÂÃæµÄÀý×Ó£¬select qty from sales where stor_id=7131ÊǺÜÓÐЧµÄ±ÈÏÂÃæÕâ¸öÎÞÏÞÖÆµÄ²éѯ
select qty from sales
±ÜÃâ¸ø¿Í»§µÄ×îºóÊý¾ÝÑ¡Ôñ·µ»Ø´óÁ¿µÄ½á¹û¼¯¡£´ðÓ¦SQL ServerÔËÐÐÂú×ãËüÄ¿µÄµÄº¯ÊýÏÞÖÆ½á¹û¼¯µÄ´óСÊǸüÓÐЧµÄ¡£
ÕâÄܼõÉÙÍøÂçI/O²¢ÄÜÌá¸ß¶àÓû§µÄÏà¹Ø²¢·¢Ê±µÄÓ¦ÓóÌÐòÐÔÄÜ¡£ÒòΪÓÅ»¯Æ÷¹Ø×¢µÄ½¹µã¾ÍÊÇwhere×Ó¾äµÄ²éѯ£¬ÒÔÀûÓÃÓÐÓõÄË÷Òý¡£ÔÚ±íÖеÄÿһ¸öË÷Òý¶¼¿ÉÄܳÉΪ°üÀ¨ÔÚwhere×Ó¾äÖеĺîÑ¡Ë÷Òý¡£ÎªÁË×îºÃµÄÐÔÄÜ¿ÉÒÔ×ñÕÕÏÂÃæµÄÓÃÓÚÒ»¸ö¸ø¶¨ÁÐcolumn1µÄË÷Òý¡£
µÚÒ»£ºÔÚ±íÖеÄcolumn1ÁÐÉÏÓÐÒ»¸öµ¥Ë÷Òý
µÚ¶þ£ºÔÚ±íÖÐÓжàË÷Òý£¬µ«ÊÇcolumn1ÊǵÚÒ»¸öË÷ÒýµÄÁв»ÒªÔÚwhere×Ó¾äÖÐʹÓÃûÓÐcolumn1ÁÐË÷ÒýµÄ²éѯÓï¾ä£¬²¢±ÜÃâÔÚwhere×Ó¾äÓÃÒ»¸ö¶àË÷ÒýµÄ·ÇµÚÒ»¸öË÷ÒýµÄË÷Òý¡£Õâʱ¶àË÷ÒýÊÇûÓÐÓõġ£
For example, given a multicolumn index on the au_lname, au_fname columns of the authors table in
the pubs database,
ÏÂÃæÕâ¸öqueryÓï¾äÀûÓÃÁËau_lnameÉϵÄË÷Òý
SELECT au_id, au_lname, au_fname FROM authors
WHERE au_lname = 'White'
AND au_fname = 'Johnson'
SELECT au_id, au_lname, au_fname FROM authors
WHERE au_lname = 'White'
ÏÂÃæÕâ¸ö²éѯûÓÐÀûÓÃË÷Òý£¬ÒòΪËûʹÓÃÁ˶àË÷ÒýµÄ·ÇµÚÒ»¸öË÷ÒýµÄË÷Òý
SELECT au_id, au_lname, au_fname FROM authors
WHERE au_fname = 'Johnson'
(ÔðÈα༣ºÃúÃú [email protected] TEL£º£¨010£©68476636)
Ïà¹ØÐÅÏ¢
- SQL Server¼¯³É·þÎñ±à³Ì»ù´¡
- SQL Server 2005 Êý¾Ýת»»·þÎñÖеÄÄ£ºý
- SQL ServerÊý¾Ý¿âÖд洢ÒýÇæÉîÈë̽ÌÖ(1
- ÊÓÆµ£ºÇáËÉά»¤SQL Server 2005Êý¾Ý¿â
- Ó°ÏìÊý¾Ý¿â¿ÉÓÃÐÔµÄÈý´óÒòËØºÍÏêϸ´ëÊ©
- Êý¾Ý¿âÐÔÄÜÖ®Êý¾Ý¿â¼¯Èº¸ÅÄî
- SQL Server¸ù¾Ý·ÃÎÊÀúÊ·ÈÕÖ¾×Ô¶¯·ÖÎö²¢
- Êý¾Ý¿âµÄ°²È«ÐÔºÍÒì¹¹Êý¾Ý¿â¼äµÄ»¥²Ù×÷
- ÈçºÎÓÅ»¯Êý¾Ý¿âµÄÊý¾Ý²éѯ
- Êý¾Ý¿âÁ½¸öʵÀýÎÊÌâ½âÎö
- ÔõÑù³ÉΪһ¸ö¸ßЧµÄDBA×ܽὨÒé
- Êý¾Ý¿â·À²¡¶¾ÊµÀýÎÊÌâ½âÎö
Ó²¼þ¹ÊÕÏרÌâ
¸ü¶àÈí¼þ¹ÊÕÏάÐÞ
¸ü¶à- Photoshop°ÑÈËÎïÕÕÆ¬´òÔìµÄ¸ü¾ßÓÐ÷ÈÁ¦
- AMD OverDrive ³¬ÆµÈí¼þÄÄÀïÏÂÔØ°¡£¿
- E-AC3¡¢DD plus¡¢DD ÓÐÇø±ðÂð£¿
- ·ÉÄñ(Flyer) v2.3 ¼òÌåÖÐÎÄÕýʽ°æ
- ä¯ÀÀÆ÷±ê×¼²âÊÔ¡ª¡ªAcid3²âÊÔ
- ²éÃ÷ÄãµÄPCÊÇ·ñÊǽ©Ê¬¼ÆËã»ú
- ÈÃWMPÒ²ÏíÊܲ¥·ÅMPEG4µçÓ°µÄÀÖȤ
- ºìñ×Ó³ÆÓ¯ÀûµÚÒ» ·ÅÆú¿ª·¢Ïû·ÑLinux×À
- ÄÇλ´ó´ó½éÉÜÒ»ÏÂÊÓÆµ·Ö¸îºÏ²¢µÄ¹¤¾ß.(
- IE8½«²ÉÓüæÈÝģʽÏÔʾ¾É°æÍøÒ³
ÔĶÁÅÅÐÐ
- ΪʲôҪÉý¼¶µ½SQL Server 2005
- SQL Server 2005£ºÏòϵͳ±í˵ÔÙ¼û
- SQL Server 2000 Óë SQL Server 7.0 °æ
- SQL Server 2005µÄXMLÖ§³ÖÓëÒì³£´¦Àí(1
- SQL Server 2008¶ÔT-SQLÓïÑÔµÄÔöÇ¿(1)
- SQL Server °²È«ÐÔ¹ÜÀíµÄ;¾¶
- SQL Server 2005 Beta2°²×°¼°½çÃæ
- SQL ServerÖ´ÐÐSQLÓï¾äʱÄÚ´æÕ¼ÓÃÌØµã(
- SQLϵÁн̳ÌÖ®»ù±¾SELECTÃüÁî
- SQL Server 2008¼¸ÏîÐÂÌØÐÔ¸ÅÊö
- ¹ØÓÚSQL ServerÖÐË÷ÒýʹÓü°Î¬»¤¼ò½é(1
- SQL Server¸ù¾Ý·ÃÎÊÀúÊ·ÈÕÖ¾×Ô¶¯·ÖÎö²¢