SQLÓû§È¨ÏÞ¹ÜÀí·þÎñÆ÷
Óû§È¨ÏÞ¹ÜÀí
Ò»¡¢·þÎñÆ÷µÇ¼ÕʺźÍÓû§ÕʺŹÜÀí
1.SQL Server·þÎñÆ÷µÇ¼¹ÜÀí
²»¹ÜʹÓÃÄÄÖÖÈÏ֤ģʽ£¬Óû§¶¼±ØÐëÏȾ߱¸ÓÐЧµÄÓû§µÇ¼Õʺš£SQL ServerÓÐÈý¸öĬÈϵÄÓû§µÇ¼Õʺţº¼´sa¡¢Builtin\administratorsºÍguest¡£saÊÇϵͳ¹ÜÀíÔ±(system administrator)µÄ¼ò³Æ£¬ÊÇÒ»¸öÌØÊâµÄÓû§£¬ÔÚSQL ServerϵͳºÍËùÓÐÊý¾Ý¿âÖÐÓµÓÐËùÓеÄȨÏÞ¡£SQL Server»¹ÎªÃ¿Ò»¸öWindows NTϵͳ¹ÜÀíÔ±ÌṩÁËÒ»¸öĬÈϵÄÓû§ÕʺÅBuiltin\administrators¡£Õâ¸öÕʺÅÔÚϵͳºÍËùÓÐÊý¾Ý¿âÖÐÒ²ÓµÓÐËùÓеÄȨÏÞ¡£¶øguestÕʺÅΪĬÈÏ·ÃÎÊϵͳÓû§Õʺš£ÀûÓÃÆóÒµ¹ÜÀíÆ÷¿ÉÒÔ´´½¨¡¢¹ÜÀíSQL ServerµÇ¼Õʺš£Æä¾ßÌå²½ÖèÈçÏ£º
(1)´ò¿ªÆóÒµÆ÷£¬µ¥»÷·þÎñÆ÷µÄ“+”£¬Õ¹¿ª°²È«ÐÔÎļþ¼Ð£»
(2)ÓÒ»÷µÇ¼ͼ±ê£¬Ñ¡Ôñ“н¨µÇ¼”£»
(3)ÔÚ“Ãû³Æ”Îı¾¿òÖÐÊäÈëµÇ¼Ãû£¬Ñ¡Ôñн¨µÄÓû§ÕʺŵÄÈÏ֤ģʽ£»²¢Ñ¡ÔñĬÈϵÄÊý¾Ý¿âºÍÓïÑÔ¡£
(4)Ñ¡Ôñ“·þÎñÆ÷½ÇÉ«”£¬ÔÚÁгöµÄ¹Ì¶¨·þÎñÆ÷½ÇÉ«µÄ×ó¶Ë¸´Ñ¡¿òÖÐÑ¡ÔñÏàÓ¦µÄ·þÎñÆ÷½ÇÉ«£»
(5)Ñ¡Ôñ“Êý¾Ý¿â·ÃÎÊ”£¬µ¥»÷Êý¾Ý¿â×ó±ßµÄ¸´Ñ¡¿ò£¬±íʾ¸ÃÓû§¿ÉÒÔ·ÃÎÊÏàÓ¦µÄÊý¾Ý¿âÒÔ¼°¸ÃÕʺÅÔÚÊý¾Ý¿âÖеÄÓû§Ãû¡£Ï²¿µÄÁбí¿òÁгöÁËÏàÓ¦µÄÊý¾Ý¿âÖеÄÊý¾Ý¿â½ÇÉ«Çåµ¥£¬¿ÉÒÔÖ¸¶¨¸ÃÕʺÅËùÊôµÄÊý¾Ý¿â½ÇÉ«£»
(6)µ¥»÷“È·¶¨”¼´¿ÉÍê³ÉµÇ¼ÕʺŵĴ´½¨¡£
´ËÍ⣬»¹¿ÉÒÔʹÓô´½¨µÇ¼Ïòµ¼¹¤¾ß´´½¨µÇ¼Õʺš£¾ßÌå¹ý³ÌÈçÏ£º
(1)´ò¿ªÆóÒµ¹ÜÀíÆ÷£¬Ñ¡Ôñ¹¤¾ßÖеēÏòµ¼--´´½¨µÇ¼Ïòµ¼”£»
(2)Ñ¡ÔñÉí·ÝÑé֤ģʽ£»
(3)
(4)
(5)
(6)
2.Óû§ÕʺŹÜÀí
ÔÚÊý¾Ý¿âÖУ¬Ò»¸öÓû§»ò¹¤×÷×éÈ¡µÃºÏ·¨µÄµÇ¼Õʺţ¬Ö»±íÃ÷¸ÃÕʺÅͨ¹ýÁËWindows NTÈÏÖ¤»òÕßSQL ServerÈÏÖ¤£¬µ«²»ÄܱíÃ÷Æä¿ÉÒÔ¶ÔÊý¾Ý¿âÊý¾ÝºÍÊý¾Ý¿â¶ÔÏó½øÐÐijÖÖ»òÕßijЩ²Ù×÷£¬Ö»Óе±ËûͬʱӵÓÐÁËÓû§Õʺź󣬲ÅÄܹ»·ÃÎÊÊý¾Ý¿â¡£ÔÚÒ»¸öÊý¾Ý¿âÖУ¬Óû§ÕʺÅΨһ±êʶһ¸öÓû§£¬Óû§¶ÔÊý¾Ý¿âµÄ·ÃÎÊȨÏÞÒÔ¼°¶ÔÊý¾Ý¿â¶ÔÏóµÄËùÓйØϵ¶¼ÊÇͨ¹ýÕʺÅÀ´¿ØÖƵġ£Óû§ÕʺÅ×ÜÊÇ»ùÓÚÊý¾Ý¿âµÄ£¬¼´Á½¸ö²»Í¬µÄÊý¾Ý¿â¿ÉÒÔÓÐÁ½¸öÏàͬµÄÓû§Õʺţ¬²¢ÇÒÒ»¸öµÇ¼ÕʺÅÒ²×ÜÊÇÓëÒ»¸ö»ò¶à¸öÊý¾Ý¿âÓû§ÕʺÅÏà¶ÔÓ¦µÄ¡£È磬µÇ¼ÕʺÅsa×Ô¶¯Óëÿһ¸öÊý¾Ý¿âÓû§dboÏà¹ØÁª¡£Í¨³£¶øÑÔ£¬Êý¾Ý¿âÓû§ÕʺÅ×ÜÊÇÓëijһµÇ¼ÕʺÅÏà¹ØÁª£¬µ«ÓÐÒ»¸öÀýÍ⣬ÄǾÍÊÇguestÓû§¡£ÔÚ°²×°SQL Serverϵͳʱ£¬guestÓû§±»¼ÓÈëµ½master£¬pubs£¬tempdbºÍnorthwindÊý¾Ý¿âÖУ¬¸ÃÓû§Ö÷ÒªÊÇÈÃÄÇЩûÓÐÊôÓÚ×Ô¼ºµÄÓû§ÕʺŵÄSQL
Ïà¹ØÎĵµ£º
COUNT(*)ÓëCOUNT(COL)
ÍøÉÏËÑË÷ÁËÏ£¬·¢ÏÖ¸÷ÖÖ˵·¨¶¼ÓУº
±ÈÈçÈÏΪCOUNT(COL)±ÈCOUNT(*)¿ìµÄ£»
ÈÏΪCOUNT(*)±ÈCOUNT(COL)¿ìµÄ£»
»¹ÓÐÅóÓѺܸãЦµÄ˵µ½Õâ¸öÆäʵÊÇ¿´ÈËÆ·µÄ¡£
ÔÚ²»¼ÓWHEREÏÞÖÆÌõ¼þµÄÇé¿öÏ£¬COUNT(*)ÓëCOUNT(COL)»ù±¾¿ÉÒÔÈÏΪÊǵȼ۵ģ»
µ«ÊÇÔÚÓÐWHEREÏÞÖÆÌõ¼þµÄÇé¿öÏ£¬COUNT(*)»á±ÈCOUNT(COL)¿ì·Ç³£¶à ......
ÔÚOracle10g֮ǰ£¬ÓÅ»¯SQLÊǸö±È½Ï·ÑÁ¦µÄ¼¼Êõ»î£¬²»Í£µÄ·ÖÎöÖ´Ðмƻ®£¬¼Óhint£¬·ÖÎöͳ¼ÆÐÅÏ¢µÈµÈ¡£ÔÚ10gÖУ¬OracleÍƳöÁË×Ô¼ºµÄSQLÓÅ»¯¸¨Öú¹¤¾ß: SQLÓÅ»¯Æ÷(SQL Tuning Advisor £ºSTA)£¬ËüÊÇеÄDBMS_SQLTUNE°ü¡£Ê¹ÓÃSTAÒ»¶¨Òª±£Ö¤ÓÅ»¯Æ÷ÊÇCBOģʽÏ¡£
Ö´ÐÐDBMS_SQLTUNE°ü½øÐÐsqlÓÅ»¯ÐèÒªÓÐadvisorµ ......
1. ʲôÊÇÃüÃû¹ÜµÀ£¿
ÓëTCP/IP£¨´«Êä¿ØÖÆÐÒé»òinternetÐÒ飩һÑù£¬ÃüÃû¹ÜµÀÊÇÒ»ÖÖͨѶÐÒé¡£ËüÒ»°ãÓÃÓÚ¾ÖÓòÍøÖУ¬ÒòΪËüÒªÇó¿Í»§¶Ë±ØÐë¾ßÓзÃÎÊ·þÎñÆ÷×ÊÔ´µÄȨÏÞ¡£
Òª½âÊÍÕâ¸öÎÊÌ⣬ÎÒ»¹ÊÇժ¼΢Èí¹Ù·½µÄ×ÊÁϱȽϺÃ
http://msdn.microsoft.com/zh-cn/library/ms187892.aspx
ÈôÒªÁ¬½Óµ½ SQL Server Êý¾Ý¿âÒýÇ棬±ØÐëÆô ......
SQL ¸½¼ÓÊý¾Ý¿âʱ³öÏÖ ÎÞ·¨´ò¿ªÎïÀíÎļþ´íÎó
´íÎóÐÅÏ¢:
Ö´ÐÐ Transact-SQL Óï¾ä»òÅú´¦Àíʱ·¢ÉúÁËÒì³£¡£ (Microsoft.SqlServer.ConnectionInfo)
------------------------------
ÎÞ·¨´ò¿ªÎïÀíÎļþ "D:\SqlDataBase\Test.mdf"¡£²Ù×÷ϵͳ´íÎó 5:"5(¾Ü¾ø·ÃÎÊ¡£)"¡£ (Microsoft SQL Server£¬´íÎó: 5120)
½â¾ö°ì·¨:
......
Oracle spool Ó÷¨Ð¡½á[°ëת°ë¼Ó]
¹ØÓÚSPOOL(SPOOLÊÇSQLPLUSµÄÃüÁ²»ÊÇSQLÓï·¨ÀïÃæµÄ¶«Î÷¡£)
¶ÔÓÚSPOOLÊý¾ÝµÄSQL£¬×îºÃÒª×Ô¼º¶¨Òå¸ñʽ£¬ÒÔ·½±ã³ÌÐòÖ±½Óµ¼Èë,SQLÓï¾äÈ磺
select empno||','||ename||','||sal from emp;
spool³£ÓõÄÉèÖÃ
set colsep' ';¡¡¡¡¡¡ //ÓòÊä³ö·Ö¸ô·û
set echo off;¡¡¡¡¡¡¡¡//ÏÔʾstartÆô¶¯µ ......