ͨ¹ý·ÖÎöSQLÓï¾äµÄÖ´Ðмƻ®ÓÅ»¯SQL
ͨ¹ý·ÖÎöSQLÓï¾äµÄÖ´Ðмƻ®ÓÅ»¯SQL(×ܽá)
×ö DBA¿ì7ÄêÁË£¬Öмä¸ÐÎòºÜ¶à¡£ÔÚDBAµÄÈÕ³£¹¤×÷ÖУ¬µ÷Õû¸ö±ðÐÔÄܽϲîµÄSQLÓï¾äʱһÏÓÐÌôÕ½ÐԵŤ×÷¡£ÆäÖеĹؼüÔÚÓÚÈçºÎµÃµ½SQLÓï¾äµÄÖ´Ðмƻ®ºÍÈçºÎ´ÓSQLÓï¾äµÄÖ´Ðмƻ®Öз¢ÏÖÎÊÌâ¡£×ÜÊÇÏ뽫ÈÕ³£¾ÑéµÄµãµãµÎµÎ×ܽáһϣ¬µ«ÊÇÖ±µ½×î½ü²Å϶¨¾öÐÄ£¬×ܹ²»¨ÁË3¸öÖÜĩʱ¼ä£¬²Å½«ÆäÕûÀí³É²á£¬±ãÓÚ×Ô¼ºÈÕ³£¹¤×÷¡£²»ºÃÒâ˼¶ÀÏí£¬ËùÒÔ½«ÆäÌù³öÀ´¡£
µÚÒ»Õ¡¢µÚ2Õ ²¢²»ÊǺÜÖØÒª£¬ÊÇ×Ô¼ºµÄһЩÏë·¨£¬¹ØÓÚÈçºÎ×öÒ»¸öÎȶ¨¡¢¸ßЧµÄÓ¦ÓÃϵͳµÄһЩÏë·¨¡£
µÚÈýÕÂÒÔºó¶¼ÊDZȽÏÖØÒªµÄ¡£
¸½Â¼µÄÄÚÈÝÒ²ÊDZȽÏÖØÒªµÄ¡£ÎÒ³£Óøò¿·ÖµÄÄÚÈÝ¡£
ǰÑÔ
±¾ÎĵµÖ÷Òª½éÉÜÓëSQLµ÷ÕûÓйصÄÄÚÈÝ£¬ÄÚÈÝÉæ¼°¶à¸ö·½Ã棺SQLÓï¾äÖ´ÐеĹý³Ì¡¢ORACLEÓÅ»¯Æ÷£¬±íÖ®¼äµÄ¹ØÁª£¬ÈçºÎµÃµ½SQLÖ´Ðмƻ®£¬ÈçºÎ·ÖÎöÖ´Ðмƻ®µÈÄÚÈÝ£¬´Ó¶øÓÉdzµ½ÉîµÄ·½Ê½Á˽âSQLÓÅ»¯µÄ¹ý³Ì£¬Ê¹´ó¼ÒÖð²½²½ÈëSQLµ÷ÕûÖ®ÃÅ£¬È»ºóÄ㽫·¢ÏÖ……¡£
Ŀ¼
µÚ1Õ ÐÔÄܵ÷Õû×ÛÊö
µÚ2Õ ÓÐЧµÄÓ¦ÓÃÉè¼Æ
µÚ3Õ SQLÓï¾ä´¦ÀíµÄ¹ý³Ì
µÚ4Õ ORACLEµÄÓÅ»¯Æ÷
µÚ5Õ ORACLEµÄÖ´Ðмƻ®
·ÃÎÊ·¾¶(·½·¨) -- access path
±íÖ®¼äµÄÁ¬½Ó
ÈçºÎ²úÉúÖ´Ðмƻ®
ÈçºÎ·ÖÎöÖ´Ðмƻ®
ÈçºÎ¸ÉÔ¤Ö´Ðмƻ® - - ʹÓÃhintsÌáʾ
¾ßÌå°¸Àý·ÖÎö
µÚ6Õ ÆäËü×¢ÒâÊÂÏî
¸½Â¼
µÚ1Õ ÐÔÄܵ÷Õû×ÛÊö
OracleÊý¾Ý¿âÊǸ߶ȿɵ÷µÄÊý¾Ý¿â²úÆ·¡£±¾ÕÂÃèÊöµ÷ÕûµÄ¹ý³ÌºÍÄÇЩÈËÔ±Ó¦ÓëOracle·þÎñÆ÷µÄµ÷ÕûÓйأ¬ÒÔ¼°Óëµ÷ÕûÏà¹ØÁªµÄ²Ù×÷ϵͳӲ¼þºÍÈí¼þ¡£±¾Õ°üÀ¨ÒÔÏ·½Ãæ:
l ËÀ´µ÷Õûϵͳ?
l ʲôʱºòµ÷Õû?
l &nbs
Ïà¹ØÎĵµ£º
ÎÒ˵µÄ²»¶¨Ìõ¼þÊÇÖ¸²éѯÌõ¼þµÄ¸öÊý²»¶¨¡£ÓÐʱһ¸ö£¬ÓÐʱÁ½¸ö£¬ÓÐʱºÃ¼¸¸ö¡£
Ê×ÏÈÎÒ·¢ÏÖ
select * from A where a='kkk' Óë
select * from A where a like 'kkk'
ÆäʵЧ¹ûÊÇÒ»Ñù£¬Ö»Òª like ºóÃæµÄ×Ö·û´®²»°üº¬Í¨Åä·û¡£ÕâÑùÒ»À´¾ÍºÜ·½±ãÁË¡£Æ©ÈçÓÐ
select * from A where a='KKK' and b='LLL'
ÓÐÁ½¸ö²éѯÌõ¼þ£¬ ......
SQL¾ÛºÏº¯
±êÇ©£ºsql¾ÛºÏº¯Êý ÔÓ̸
¾ÛºÏº¯Êý£º
1.AVG ·µ»Ø×éÖÐµÄÆ½¾ùÖµ£¬¿ÕÖµ½«±»ºöÂÔ¡£
ÀýÈ磺use northwind // ²Ù×÷northwindÊý¾Ý¿â
Go
Select avg (unitprice) //´Ó±íÖÐÑ¡ÔñÇóunitpriceµÄƽ¾ùÖµ
& ......
sql º¯Êý×ܽá
³£ÓõÄ×Ö·û´®º¯ÊýÓУº
Ò»¡¢×Ö·ûת»»º¯Êý
1¡¢ASCII()
·µ»Ø×Ö·û±í´ïʽ×î×ó¶Ë×Ö·ûµÄASCII ÂëÖµ¡£ÔÚASCII£¨£©º¯ÊýÖУ¬´¿Êý×ÖµÄ×Ö·û´®¿É²»ÓÑ’À¨ÆðÀ´£¬µ«º¬ÆäËü×Ö·ûµÄ×Ö·û´®±ØÐëÓÑ’À¨ÆðÀ´Ê¹Ó㬷ñÔò»á³ö´í¡£
2¡¢CHAR()
½«ASCII Âëת»»Îª×Ö·û¡£Èç¹ûûÓÐÊäÈë0 ~ 255 Ö®¼äµÄASCII ......
ÎÒ¾³£ÎʵÄÒ»¸öÎÊÌâÊÇ£ºÄãÊÇÈçºÎÀ´À©Õ¹SQL DatabaseµÄ£¿ÓкܶàµÄ·½·¨¿ÉÒÔ×öµ½£¬±ÈÈçʹÓûº´æ£¬¹²Ïí»òÕßÓÃÆäËûµÄ¼¼Êõ½«Ñ¹Á¦´ÓÊý¾Ý¿âÊÍ·Å£¬µ«Êµ¼ÊÉÏÎÒÃÇûÓС£SQL Database´Ó¸ù±¾ÉÏÀ´ËµÊDz»¾ßÓÐÀ©Õ¹ÐԵģ¬ÎÒÃÇҲûÓÐħ·¨Ê¹ËüºöÈ»¾ßÓÐÀ©Õ¹ÐÔ¡£
ʲôÊÇÀ©Õ¹ÐÔ£¿
±ØÐë¾ßÓм¸Ï¼¸µã£º
ºáÏòÀ©Õ¹£º¸ü¶àµÄserver¿ÉÒÔ´øÀ´¸ü¸ßµÄÐÔÄÜ ......
ÈçºÎÉú³Éexplain plan?
¡¡¡¡½â´ð:ÔËÐÐutlxplan.sql. ½¨Á¢plan ±í
¡¡¡¡Õë¶ÔÌØ¶¨SQLÓï¾ä£¬Ê¹Óà explain plan set statement_id = 'tst1' into plan_table
¡¡¡¡ÔËÐÐutlxplp.sql »ò utlxpls.sql²ì¿´explain plan
EXPLAIN PLAN ÊÇÒ»¸öºÜºÃµÄ·ÖÎöSQLÓï¾äµÄ¹¤¾ß,ËüÉõÖÁ¿ÉÒÔÔÚ²»Ö´ÐÐSQLµÄÇé¿öÏ·ÖÎöÓï¾ä. ͨ¹ý·ÖÎö,ÎÒÃǾͿÉÒÔÖ ......