ת×Ô£º°Ù¶È°Ù¿Æ
Ëùν¶Ë¿Ú£¬¾ÍÊÇÏ൱ÓÚ»úÆ÷ÓëÍâ½ç½Ó´¥µÄ´°¿Ú¡£¶Ë¿ÚÆäʵÊÇÈí¼þµÄ´°¿Ú£¬¾ÍÊÇ˵һ¸öÈí¼þÈç¹ûÒªºÍÍâ½çÁªÏµ£¬¾Í±ØÐë´ò¿ªÒ»¸ö¶Ë¿Ú£»1434¶Ë¿ÚÊÇ΢ÈíSQL Serverδ¹«¿ªµÄ¼àÌý¶Ë¿Ú¡£ÄãҪʹÓÃSQL£¬¾Í±ØÈ»´ò¿ª1433ºÍ1434¶Ë¿Ú¡£
ĬÈÏÇé¿öÏ£¬SQL ServerʹÓÃ1433¶Ë¿Ú¼àÌý£¬ºÜ¶àÈ˶¼ËµSQL ServerÅäÖõÄʱºòÒª°ÑÕâ¸ö¶Ë¿Ú¸Ä±ä£¬ÕâÑù±ðÈ˾Ͳ»ÄܺÜÈÝÒ×µØÖªµÀʹÓõÄʲô¶Ë¿ÚÁË¡£¿Éϧ£¬Í¨¹ý΢Èíδ¹«¿ªµÄ1434¶Ë¿ÚµÄUDP̽²â¿ÉÒÔºÜÈÝÒ×ÖªµÀ SQL ServerʹÓõÄʲôTCP/IP¶Ë¿ÚÁË¡£ ÀýÈ磺“2003Èä³æÍõ”ÀûÓÃSQL SERVER 2000µÄ½âÎö¶Ë¿Ú1434µÄ»º³åÇøÒç³ö©¶´£¬¶ÔÍøÂç½øÐй¥»÷¡£
²»¹ý΢Èí»¹ÊÇ¿¼Âǵ½ÁËÕâ¸öÎÊÌ⣬±Ï¾¹¹«¿ª¶øÇÒ¿ª·ÅµÄ¶Ë¿Ú»áÒýÆð²»±ØÒªµÄÂé·³¡£ÔÚʵÀýÊôÐÔÖÐÑ¡ÔñTCP/IPÐÒéµÄÊôÐÔ¡£Ñ¡ÔñÒþ²Ø SQL Server ʵÀý¡£Èç¹ûÒþ²ØÁË SQL Server ʵÀý£¬Ôò½«½ûÖ¹¶ÔÊÔͼö¾ÙÍøÂçÉÏÏÖÓÐµÄ SQL Server ʵÀýµÄ¿Í»§¶ËËù·¢³öµÄ¹ã²¥×÷³öÏìÓ¦¡£ÕâÑù£¬±ðÈ˾Ͳ»ÄÜÓÃ1434À´Ì½²âÄãµÄTCP/IP¶Ë¿ÚÁË£¨³ý·ÇÓÃPort Scan£©
SQL Server 2005²»ÔÙÔÚ1434¶Ë¿ÚÉϽøÐÐ×Ô¶¯ÕìÌýÁË¡£Êµ¼ÊÉÏ£¬ÊÇÍêÈ«²»ÕìÌýÁË¡£ÄãÐèÒª´ò¿ªSQL ä¯ÀÀÆ÷·þÎñ£¬°ÑËü×÷Ϊ½â¾ö¿Í»§¶ËÏò·þÎñÆ÷¶Ë·¢ËÍÇëÇóµÄÖмäý½é¡£SQL ä¯ÀÀÆ÷·þÎñÖ»ÄÜÌ ......
ÉÏÉϸöÐÇÆÚ£¬ÓÐÈË·´À¡£¬CSDNÓÐSQL×¢È붶´£¬º¹ÑÕ£¬¼¸ÄêǰΪSQL×¢È붶´£¬²¿ÃÅרÃŶÔËùÓдúÂë×ö¹ýÒ»´Î·Ç³£´óµÄ¼ì²é£¬¾¹È»ÄǴμì²é»¹ÓÐÒÅ©µÄµØ·½¡£×î½üÕ⼸¸öÐÇÆÚ£¬¾ÍÊÇÒ»Ö±ÔÙ¶Ô´úÂë×öÔٴθ´²é£¬¿´ÓÐûÓÐSQL×¢È붶´¡£
´æÔÚSQL×¢È붶´£¬¾ÍÒòΪÄãµÄSQLÓï¾äÊÇ×Ô¼ºÆ´´ÕµÄ£¬ÔÚÆ´´ÕµÄʱºò£¬Ã»Óп¼ÂÇÓû§¿ÉÄÜÆ´´Õ½øÓÐÎÊÌâµÄÓï¾äÔì³ÉµÄ¡£
֮ǰÎÒÃDzÉÈ¥µÄ±ÜÃâ´ëÊ©ÊÇ£¬¶Ô½ÓÊܵIJÎÊýµÄУÑé½øÐзâ×°£¬ËùÓвÎÊýµÄ»ñµÃ£¬¶¼±ØÐëͨ¹ýÕâ¸ö·â×°µÄº¯ÊýÀ´»ñµÃ¡£µ«ÊÇʵ¼Ê¿ª·¢ÖУ¬×ÜÓÐЩµØ·½£¬ÓÐЩ´úÂëûÓе÷ÓÃÕâ¸ö·â×°µÄº¯ÊýÀ´½øÐÐУÑé¡£
ÉÏÖÜÄ©£¬¹«Ë¾ÄÚ²¿ÌÖÂÛ»áµÄʱºò£¬¾ö¶¨»»ÖÖ˼·¡£´Ó±ÜÃâ×Ô¼ºÆ´´ÕSQLÓï¾äµÄ·½Ê½À´ÊµÏÖ£¬¾ßÌå¾ÍÊÇ¿ª·¢¹æ·¶ÖÐÓÐÒ»Ìõ£¬²»ÔÊÐíÓÃÆ´´ÕµÄSQLÓï¾ä£¬Òª´«²ÎÊý£¬¾ÍÓÃSqlParameter¡£±ÈÈçÏÂÃæµÄ´úÂëÊDz»ÔÊÐíµÄ¡£
string strSQL = "select UserID,UserName,Email from User where UserName = '"+aa.replace("'","''")+"'";
SqlDataReader sdr = SqlHelper.ExecuteReader(strConnStr,CommandType.Text,strSQL)
¶øÏÂ ......
<!--[if !supportLists]-->Ò»¡¢<!--[endif]-->SQL Server 2005Êý¾Ý¿â¹ÜÀíµÄ10¸ö×îÖØÒªÌصã
<!--[if !supportLists]-->1. <!--[endif]-->Êý¾Ý¿â¾µÏñ
ͨ¹ýÐÂÊý¾Ý¿â¾µÏñ·½·¨£¬½«¼Ç¼µµ°¸´«ËÍÐÔÄܽøÐÐÑÓÉì¡£Äú½«¿ÉÒÔʹÓÃÊý¾Ý¿â¾µÏñ£¬Í¨¹ý½«×Ô¶¯Ê§Ð§×ªÒƽ¨Á¢µ½Ò»¸ö´ýÓ÷þÎñÆ÷ÉÏ£¬ÔöÇ¿ÄúSQL·þÎñÆ÷ϵͳµÄ¿ÉÓÃÐÔ¡£
<!--[if !supportLists]-->2. <!--[endif]-->ÔÚÏ߻ָ´
ʹÓÃSQL2005°æ·þÎñÆ÷£¬Êý¾Ý¿â¹ÜÀíÈËÔ±½«¿ÉÒÔÔÚSQL·þÎñÆ÷ÔËÐеÄÇé¿öÏ£¬Ö´Ðлָ´²Ù×÷¡£ÔÚÏ߻ָ´¸Ä½øÁËSQL·þÎñÆ÷µÄ¿ÉÓÃÐÔ£¬ÒòΪֻÓÐÕýÔÚ±»»Ö¸´µÄÊý¾ÝÊÇÎÞ·¨Ê¹Óõģ¬¶øÊý¾Ý¿âµÄÆäËû²¿·ÖÒÀÈ»ÔÚÏß¡¢¿É¹©Ê¹Óá£
<!--[if !supportLists]-->3. <!--[endif]-->ÔÚÏß¼ìË÷²Ù×÷
ÔÚÏß¼ìË÷Ñ¡Ïî¿ÉÒÔÔÚÖ¸ÊýÊý¾Ý¶¨ÒåÓïÑÔ£¨DDL£©Ö´ÐÐÆڼ䣬ÔÊÐí¶Ô»ùµ×±í¸ñ¡¢»ò¼¯´ØË÷ÒýÊý¾ÝºÍÈκÎÓйصļìË÷£¬½øÐÐͬ²½ÐÞÕý¡£ÀýÈ磬µ±Ò»¸ö¼¯´ØË÷ÒýÕýÔÚÖؽ¨µÄʱºò£¬Äú¿ÉÒÔ¶Ô»ùµ×Êý¾Ý¼ÌÐø½øÐиüС¢²¢ÇÒ¶Ô ......
inner join,full outer join,left join,right jion
ÄÚ²¿Á¬½Ó inner join Á½±í¶¼Âú×ãµÄ×éºÏ
full outer È«Á¬ Á½±íÏàͬµÄ×éºÏÔÚÒ»Æð£¬A±íÓУ¬B±íûÓеÄÊý¾Ý£¨ÏÔʾΪnull£©,ͬÑùB±íÓÐ
A±íûÓеÄÏÔʾΪ(null)
A±í left join B±í ×óÁ¬,ÒÔA±íΪ»ù´¡£¬A±íµÄÈ«²¿Êý¾Ý£¬B±íÓеÄ×éºÏ¡£Ã»ÓеÄΪnull
A±í right join B±í ÓÒÁ¬,ÒÔB±íΪ»ù´¡£¬B±íµÄÈ«²¿Êý¾Ý£¬A±íµÄÓеÄ×éºÏ¡£Ã»ÓеÄΪnull
²éѯ·ÖÎöÆ÷ÖÐÖ´ÐУº
Sql´úÂë
--½¨±ítable1,table2£º
create table table1(id int,name varchar(10))
create table table2(id int,score int)
insert into table1 select 1,'lee'
insert into table1 select 2,'zhang'
insert into table1 select 4,'wang'
insert into table2 select 1,90
insert into table2 select 2,100
insert into table2 select 3,70
--½¨±ítable1,table2£º
cr ......
ÔÚ¹«¹²ÐÂÎÅ×éÖУ¬Ò»¸ö¾³£³öÏÖµÄÎÊÌâÊÇ“ÔõÑù²ÅÄܸù¾Ý´«µÝ¸ø´æ´¢¹ý³ÌµÄ²ÎÊý·µ»ØÒ»¸öÅÅÐòµÄÊä³ö£¿”¡£ÔÚһЩ¸ßˮƽר¼ÒµÄ°ïÖú֮ϣ¬ÎÒÕûÀí³öÁËÕâ¸öÎÊÌâµÄ¼¸ÖÖ½â¾ö·½°¸¡£
Ò»¡¢ÓÃIF...ELSEÖ´ÐÐÔ¤ÏȱàдºÃµÄ²éѯ
¡¡¡¡¶ÔÓÚ´ó¶àÊýÈËÀ´Ëµ£¬Ê×ÏÈÏëµ½µÄ×ö·¨Ò²ÐíÊÇ£ºÍ¨¹ýIF...ELSEÓï¾ä£¬Ö´Ðм¸¸öÔ¤ÏȱàдºÃµÄ²éѯÖеÄÒ»¸ö¡£ÀýÈ磬¼ÙÉèÒª´ÓNorthwindÊý¾Ý¿â²éѯµÃµ½Ò»¸ö»õÖ÷£¨Shipper£©µÄÅÅÐòÁÐ±í£¬·¢³öµ÷ÓõĴúÂëÒÔ´æ´¢¹ý³Ì²ÎÊýµÄÐÎʽָ¶¨Ò»¸öÁУ¬´æ´¢¹ý³Ì¸ù¾ÝÕâ¸öÁÐÅÅÐòÊä³ö½á¹û¡£Listing 1ÏÔʾÁËÕâÖÖ´æ´¢¹ý³ÌµÄÒ»¸ö¿ÉÄܵÄʵÏÖ£¨GetSortedShippers´æ´¢¹ý³Ì£©¡£
¡¾Listing 1: ÓÃIF...ELSEÖ´Ðжà¸öÔ¤ÏȱàдºÃµÄ²éѯÖеÄÒ»¸ö¡¿
CREATE PROC GetSortedShippers
@OrdSeq AS int
AS
IF @OrdSeq = 1
SELECT * from Shippers ORDER BY ShipperID
ELSE IF @OrdSeq = 2
SELECT * from Shippers ORDER BY CompanyName
ELSE IF @OrdSeq = 3
SELECT * from Shippers ORDER BY Phone
¡¡¡¡ÕâÖÖ·½·¨µÄÓŵãÊÇ´úÂëºÜ¼òµ¥¡¢ºÜÈÝÒ×Àí½â£¬SQL ServerµÄ²éѯÓÅ»¯Æ÷Äܹ»ÎªÃ¿Ò»¸öSELECT²éѯ´´½¨Ò»¸ö²éѯÓÅ»¯¼Æ»®£¬È·±£´úÂë¾ßÓÐ×îÓŵÄÐÔÄÜ¡£ÕâÖÖ·½·¨×îÖ÷ÒªµÄȱµãÊÇ£¬Èç¹û²éѯµ ......
Oracle Êý¾Ý¿â 10g ÌṩÁË´óÁ¿°ïÖú³ÌÐò£¨»ò“¹ËÎʳÌÐò”£©£¬¿É°ïÖúÄú¾ö¶¨×î¼Ñ²Ù×÷Á÷³Ì¡£ÆäÖÐÒ»¸öʾÀýÊÇ SQL Tuning Advisor£¬Ëü¿ÉÒÔÌṩÓйزéѯµ÷ÕûÒÔ¼°ÔÚÁ÷³ÌÖÐÑÓ³¤Õû¸öÓÅ»¯¹ý³ÌµÄ½¨Òé¡£
µ«Ç뿼ÂÇÒÔϵ÷Õû°¸Àý£º¼ÙÉèÒ»¸öË÷ÒýȷʵÓÐÖúÓÚij¸ö²éѯ£¬µ«¸Ã²éѯִֻÐÐÒ»´Î¡£ÕâÑù£¬¼´Ê¹¸Ã²éѯ¿ÉÒÔµÃÒæÓÚ´ËË÷Òý£¬µ«´´½¨Ë÷ÒýµÄ³É±¾Ò²»á³¬³öÆä´øÀ´µÄºÃ´¦¡£Òª°´ÕâÖÖ·½Ê½·ÖÎö°¸Àý£¬ÄúÐèÒªÁ˽â²éѯµÄ·ÃÎÊƵÂʺÍÔÒò¡£
ÁíÒ»¸ö¹ËÎʳÌÐò (SQL Access Advisor) ¿ÉÖ´ÐÐÕâÖÖÀàÐ͵ķÖÎö¡£³ýÁËÏñÔÚ Oracle Êý¾Ý¿â 10g ÖÐÒ»Ñù¿ÉÒÔ·ÖÎöË÷Òý¡¢ÎﻯÊÓͼµÈ£¬Oracle Êý¾Ý¿â 11g ÖÐµÄ SQL Access Advisor »¹¿ÉÒÔ·ÖÎö±íºÍ²éѯÒÔʶ±ð¿ÉÄܵķÖÇø²ßÂÔ — ÕâÔÚÉè¼Æ×î¼Ñģʽʱ¿ÉÒÔÌṩºÜ´ó°ïÖú¡£ÔÚ Oracle Êý¾Ý¿â 11g ÖУ¬SQL Access Advisor ÏÖÔÚ¿ÉÒÔÌṩÓëÕû¸ö¸ºÔØÏà¹ØµÄ½¨Ò飬°üÀ¨¿¼ÂÇ´´½¨³É±¾ºÍά»¤·ÃÎʽṹ¡£
ÔÚ±¾ÎÄÖУ¬Äú½«Á˽âÐ嵀 SQL Access Advisor ÈçºÎ½â¾ö³£¼ûÎÊÌâ¡££¨×¢£º³öÓÚÑÝʾĿµÄ£¬ÎÒÃǽ«Í¨¹ýÒ»¸öÓï¾äÑÝʾÕâ¸ö¹¦ÄÜ£»µ«ÊÇ£¬Oracle ½¨ÒéʹÓà SQL Access Advisor À´°ïÖúµ÷ÕûÕû¸ö¸ºÔØ£¬¶ø²»Ö»ÊÇÒ»¸ö SQL Óï¾ä¡££©
ÎÊÌâ
ÏÂÃæÊÇÒ»¸öµäÐÍÎÊÌâ¡£Ó¦ÓóÌÐò·¢³öÁËÒÔÏ SQL Óï¾ä¡ ......
Oracle Êý¾Ý¿â 10g ÌṩÁË´óÁ¿°ïÖú³ÌÐò£¨»ò“¹ËÎʳÌÐò”£©£¬¿É°ïÖúÄú¾ö¶¨×î¼Ñ²Ù×÷Á÷³Ì¡£ÆäÖÐÒ»¸öʾÀýÊÇ SQL Tuning Advisor£¬Ëü¿ÉÒÔÌṩÓйزéѯµ÷ÕûÒÔ¼°ÔÚÁ÷³ÌÖÐÑÓ³¤Õû¸öÓÅ»¯¹ý³ÌµÄ½¨Òé¡£
µ«Ç뿼ÂÇÒÔϵ÷Õû°¸Àý£º¼ÙÉèÒ»¸öË÷ÒýȷʵÓÐÖúÓÚij¸ö²éѯ£¬µ«¸Ã²éѯִֻÐÐÒ»´Î¡£ÕâÑù£¬¼´Ê¹¸Ã²éѯ¿ÉÒÔµÃÒæÓÚ´ËË÷Òý£¬µ«´´½¨Ë÷ÒýµÄ³É±¾Ò²»á³¬³öÆä´øÀ´µÄºÃ´¦¡£Òª°´ÕâÖÖ·½Ê½·ÖÎö°¸Àý£¬ÄúÐèÒªÁ˽â²éѯµÄ·ÃÎÊƵÂʺÍÔÒò¡£
ÁíÒ»¸ö¹ËÎʳÌÐò (SQL Access Advisor) ¿ÉÖ´ÐÐÕâÖÖÀàÐ͵ķÖÎö¡£³ýÁËÏñÔÚ Oracle Êý¾Ý¿â 10g ÖÐÒ»Ñù¿ÉÒÔ·ÖÎöË÷Òý¡¢ÎﻯÊÓͼµÈ£¬Oracle Êý¾Ý¿â 11g ÖÐµÄ SQL Access Advisor »¹¿ÉÒÔ·ÖÎö±íºÍ²éѯÒÔʶ±ð¿ÉÄܵķÖÇø²ßÂÔ — ÕâÔÚÉè¼Æ×î¼Ñģʽʱ¿ÉÒÔÌṩºÜ´ó°ïÖú¡£ÔÚ Oracle Êý¾Ý¿â 11g ÖУ¬SQL Access Advisor ÏÖÔÚ¿ÉÒÔÌṩÓëÕû¸ö¸ºÔØÏà¹ØµÄ½¨Ò飬°üÀ¨¿¼ÂÇ´´½¨³É±¾ºÍά»¤·ÃÎʽṹ¡£
ÔÚ±¾ÎÄÖУ¬Äú½«Á˽âÐ嵀 SQL Access Advisor ÈçºÎ½â¾ö³£¼ûÎÊÌâ¡££¨×¢£º³öÓÚÑÝʾĿµÄ£¬ÎÒÃǽ«Í¨¹ýÒ»¸öÓï¾äÑÝʾÕâ¸ö¹¦ÄÜ£»µ«ÊÇ£¬Oracle ½¨ÒéʹÓà SQL Access Advisor À´°ïÖúµ÷ÕûÕû¸ö¸ºÔØ£¬¶ø²»Ö»ÊÇÒ»¸ö SQL Óï¾ä¡££©
ÎÊÌâ
ÏÂÃæÊÇÒ»¸öµäÐÍÎÊÌâ¡£Ó¦ÓóÌÐò·¢³öÁËÒÔÏ SQL Óï¾ä¡ ......