Ҳ̸SQL¸÷ÖÖÁ¬½Ó£¨JOIN£©
×î½ü¹«Ë¾ÔÚÕÐÈË£¬Í¬ÊÂÎÊÁ˼¸¸ö×ÔÈÏΪÊý¾Ý¿â¿ÉÒÔµÄӦƸÕß¹ØÓÚ¿âÁ¬½ÓµÄÎÊÌ⣬»Ø´ð²»¾¡ÀíÏë¡«
ÏÖÔÚÔÚÕâдд¹ØÓÚËüÃǵÄ×÷ÓÃ
¼ÙÉèÓÐÈçÏÂ±í£º
Ò»¸öΪͶƱÖ÷±í£¬Ò»¸öΪͶƱÕßÐÅÏ¢±í¡«¼Ç¼ͶƱÈËIP¼°¶ÔӦͶƱÀàÐÍ£¬×óÓÒÁ¬½Óʵ¼Ê˵ÊÇÎÒÃÇÁªºÏ²éѯµÄ½á¹ûÒÔÄĸö±íΪ׼¡«
1£ºÈçÓÒ½ÓÁ¬ right join »ò right outer join£º
ÎÒÃÇÒÔÓÒ±ßvoter±íΪ׼£¬Ôò×ó±í(voteMaster)ÖеļǼֻÓе±ÆäIDÔÚÓÒ±ß(voter)ÖдæÔÚʱ²Å»áÏÔʾ³öÀ´£¬ÈçÉÏͼ£¬×ó±ßÖÐIDΪ3.4.5.6ÒòΪÕâЩIDÓÒ±íÖÐûÓÐÏàÓ¦¼Ç¼£¬ËùÒÔûÓÐÏÔʾ£¡
2£ºÒò´ËÎÒÃÇ×ÔÈ»ÄÜÀí½â×óÁ¬½Ó left join »òÕß left outer join
¿É¼û£¬ÏÖÔÚÓÒ±ßÖÐIDÔÚÖдæÔÚʱ²Å»áÏÔʾ£¬µ±ÓÒ±ßÖÐûÓÐÏàÓ¦Êý¾ÝʱÔòÓÃNULL´úÌ棡
3£ºÈ«Á¬½Ó full join »òÕß full outer join,Ϊ¶þ¸ö±íÖеÄÊý¾Ý¶¼³öÀ´£¬ÕâÀïÑÝʾЧ¹ûÓëÉÏÒ»Ñù£¡
4£ºÄÚÁ¬½Ó inner join »òÕß join;ËüΪ·µ»Ø×Ö¶ÎIDͬʱ´æÔÚÓÚ±ívoteMaster ºÍ voterÖеļǼ
5£º½»²æÁ¬½Ó£¨ÍêÈ«Á¬½Ó£©cross join ²»´ø where Ìõ¼þµÄ
ûÓÐ WHERE ×Ó¾äµÄ½»²æÁª½Ó½«²úÉúÁª½ÓËùÉæ¼°µÄ±íµÄµÑ¿¨¶û»ý¡£µÚÒ»¸ö±íµÄÐÐÊý³ËÒÔµÚ¶þ¸ö±íµÄÐÐÊýµÈÓڵѿ¨¶û»ý½á¹û¼¯µÄ´óС¡££¨table1ºÍtable2½»²æÁ¬½Ó²úÉú6*3=18Ìõ¼Ç¼£©
µÈ¼Ûselect vm.id,vm.voteTitle,vt.ip from voteMaster as vm,voter as vt
6:×ÔÁ¬½Ó¡£ÔÚÕâÀïÎÒÓÃÎÒÇ°¶Îʱ¼äÒ»¸öµçÁ¦ÏîÄ¿ÖеÄÀý×Ó£¨¸ÄÔì¹ý£©
ÈçÏÂ±í£º
ÕâÊÇÒ»¸ö²¿ÃÅ±í£¬ÀïÃæ´æ·ÅÁ˲¿Ãż°ÆäÉϼ¶²¿ÃÅ£¬µ«¶¼·ÅÔÚͬһÕűíÖУ¬ÎÒÃǼÙÉèÏÖÔÚÐèÒªÓÃSQL²éѯ³ö¸÷²¿Ãż°ÆäÉϼ¶²¿ÃÅ£¡¾ÍÈçºÎ×ö£¬
µ±È»£¬²»ÓÃ×ÔÁ¬½ÓÒ²Ò»Ñù£¬¿ÉÒÔÈçÏ£º
ÎÒÃÇ´ïµ½Ô¤ÆÚÄ¿µÄ£¡ÔÚÕâ¸ö²éѯÖÐʹÓÃÁËÒ»¸ö×Ó²éѯÍê³É¶ÔÉϼ¶²¿ÃÅÃûµÄ²éѯ£¬Èç¹ûʹÓÃ×ÔÁ¬½Ó£¬ÄÇô½á¹¹Éϸоõ»áÇåÎúºÜ¶à¡£
ÊDz»ÊÇҲͬÑùÍê³ÉÁ˹¦ÄÜÄØ£¬ÕâÀï³ýÁËʹÓÃ×ÔÁ¬½ÓÍ⣬»¹Ê¹ÓÃÁË×óÁ¬½Ó£¬ÒòΪʡµçÁ¦Ã»ÓÐÉϼ¶²¿ÃÅ£¬ËûÊÇÀÏ´ó£¬Èç¹ûʹÓÃÄÚÁ¬½Ó£¬¾Í»á°ÑÕâÌõ¼Ç¼¹ýÂ˵ô£¬ÒòΪûÓкÍËûÆ¥ÅäµÄÉϼ¶²¿ÃÅ¡£
×ÔÁ¬½ÓÓõıȽ϶àµÄ¾ÍÊǶÔȨÐνṹµÄ²éѯ£¡ÀàËÆÉÏ±í£¡
Ïà¹ØÎĵµ£º
create PROCEDURE pagelist
@tablename nvarchar(50),
@fieldname nvarchar(50)='*',
@pagesize int output,--ÿҳÏÔʾ¼Ç¼ÌõÊý
@currentpage int output,--µÚ¼¸Ò³
@orderid nvarchar(50),--Ö÷¼üÅÅÐò
@sort int,--ÅÅÐò·½Ê½£¬1±íʾÉýÐò£¬0±íʾ½µÐòÅÅÁÐ
......
sysaltfiles ÔÚmasterÊý¾Ý¿âÖУ¬°üº¬ÓëÊý¾Ý¿âÎļþÏà¶ÔÓ¦µÄÐÅÏ¢£¬°üº¬ËùÓÐÊý¾Ý¿âµÄÊý¾ÝÎļþÒÔ¼°ÈÕÖ¾Îļþ
ÁÐÃûÊý¾ÝÀàÐÍÃèÊö
fileid
smallint
ÿ¸öÊý¾Ý¿âµÄΨһÎļþ±êʶºÅ¡£1´ú±íÊý¾ÝÎļþ£¬2´ú±íÈÕÖ¾Îļþ
groupid
smallint
Îļþ×é±êʶºÅ¡£
size
int
Îļþ´óС£¨ÒÔ 8 KB ҳΪµ¥Î»£©¡£Ò³µÄÊýÄ¿
maxsize
......
×öÊý¾Ý¿â¿ª·¢»ò¹ÜÀíµÄÈ˾³£Òª´´½¨´óÁ¿µÄ²âÊÔÊý¾Ý£¬¶¯²»¶¯¾ÍÐèÒªÉÏÍòÌõ£¬Èç¹ûÒ»ÌõÒ»ÌõµÄ¼È룬ÄÇ»áÀË·Ñ´óÁ¿µÄʱ¼ä£¬±¾ÎĽéÉÜÁËOracleÖÐÈçºÎͨ¹ýÒ»ÌõSQL¿ìËÙÉú³É´óÁ¿µÄ²âÊÔÊý¾ÝµÄ·½·¨¡£
²úÉú²âÊÔÊý¾ÝµÄSQLÈçÏ£º
SQL> select rownum as id,
2 &nb ......
¶þ£ºÓÐЧµÄÓ¦ÓÃÉè¼Æ
ÎÒÃÇͨ³£½«×î³£ÓõÄÓ¦Ó÷ÖΪ2ÖÖÀàÐÍ£ºÁª»úÊÂÎñ´¦ÀíÀàÐÍ(OLTP)£¬¾ö²ßÖ§³Öϵͳ(DSS)¡£
Áª»úÊÂÎñ´¦Àí(OLTP)
¸ÃÀàÐ͵ÄÓ¦ÓÃÊǸßÍÌÍÂÁ¿£¬²åÈë¡¢¸üС¢É¾³ý²Ù×÷±È½Ï¶àµÄϵͳ£¬ÕâЩϵͳÒÔ²»¶ÏÔö³¤µÄ´óÈÝÁ¿Êý¾ÝΪÌØÕ÷£¬ËüÃÇÌṩ¸ø³É°ÙÓû§Í¬Ê±´æÈ¡£¬µäÐ͵ÄOLTPϵͳÊǶ©Æ±ÏµÍ³£¬ÒøÐеÄÒµÎñϵͳ£¬¶©µ¥ÏµÍ³¡£OT ......
ÔÚWin7Ï°²×°SQL2005¿ª·¢°æ£¬°²×°SQL Native ClientʱÌáʾ°²×°Öжϣ¬Ã»ÓÐÔÚÒ⣬ȻºóÔÚ°²×°SQL ServerʱÓÖÌáʾ“[Microsoft][SQL Native Client]¿Í»§¶Ë²»Ö§³Ö¼ÓÃÜ.SQL Server °²×°³ÌÐòÎÞ·¨Á¬½Óµ½Êý¾Ý¿â·þÎñ½øÐзþÎñÆ÷ÅäÖÃ.”жÔØSQL Native ClientÖØа²×°£¬»¹ÊÇÖжϣ¬ÐÞ¸´£¬Ò²Öжϣ¬ÔÙжÔØ£¬ÇåÀí×¢²á±í£¬ÖØ× ......