Ҳ̸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»ÊÇҲͬÑùÍê³ÉÁ˹¦ÄÜÄØ£¬ÕâÀï³ýÁËʹÓÃ×ÔÁ¬½ÓÍ⣬»¹Ê¹ÓÃÁË×óÁ¬½Ó£¬ÒòΪʡµçÁ¦Ã»ÓÐÉϼ¶²¿ÃÅ£¬ËûÊÇÀÏ´ó£¬Èç¹ûʹÓÃÄÚÁ¬½Ó£¬¾Í»á°ÑÕâÌõ¼Ç¼¹ýÂ˵ô£¬ÒòΪûÓкÍËûÆ¥ÅäµÄÉϼ¶²¿ÃÅ¡£
×ÔÁ¬½ÓÓõıȽ϶àµÄ¾ÍÊǶÔȨÐνṹµÄ²éѯ£¡ÀàËÆÉÏ±í£¡
Ïà¹ØÎĵµ£º
±íÈçÏÂ
Ò»ÌõÓï¾äÏÔʾËùÓдóÓÚ25ËêºÍϵÄÈË£¬ÒÔÉϵÄÈËÏÔ'´óÁä'
select case when age>25 then '´óÁä' else 'СÁä' end as ÄêÁ伶±ð,count(*) as ÈËÊý from infor group by case when age>25 then '´óÁä' else 'СÁä' end
......
sysaltfiles ÔÚmasterÊý¾Ý¿âÖУ¬°üº¬ÓëÊý¾Ý¿âÎļþÏà¶ÔÓ¦µÄÐÅÏ¢£¬°üº¬ËùÓÐÊý¾Ý¿âµÄÊý¾ÝÎļþÒÔ¼°ÈÕÖ¾Îļþ
ÁÐÃûÊý¾ÝÀàÐÍÃèÊö
fileid
smallint
ÿ¸öÊý¾Ý¿âµÄΨһÎļþ±êʶºÅ¡£1´ú±íÊý¾ÝÎļþ£¬2´ú±íÈÕÖ¾Îļþ
groupid
smallint
Îļþ×é±êʶºÅ¡£
size
int
Îļþ´óС£¨ÒÔ 8 KB ҳΪµ¥Î»£©¡£Ò³µÄÊýÄ¿
maxsize
......
µ³Ô±¹ÜÀíϵͳµÄÊý¾Ý¿âÉè¼Æ
ÐèÒªÒÔÏÂ×ֶΣº
l ѧÉú£º
//ѧÉú»ù±¾ÐÅÏ¢
u ѧÉúѧºÅ[id]£¨char£©Ö÷¼ü
u ѧÉúÉí·ÝÖ¤ºÅ[id_num]£¨char£©
u ѧÉúÐÕÃû[name]£¨char£©
u ѧÉú³öÉúÈÕÆÚ[born_date]£¨date£©
u ѧÉú¼®¹á[native]£¨int£©Íâ¼ü
u ѧÉú¼Òͥסַ[address]£¨char£©
u&nbs ......
ÎÒÃÇÒª×öµ½²»µ«»áдSQL,»¹Òª×öµ½Ð´³öÐÔÄÜÓÅÁ¼µÄSQL,ÒÔÏÂΪ±ÊÕßѧϰ¡¢ÕªÂ¼¡¢²¢»ã×ܲ¿·Ö×ÊÁÏÓë´ó¼Ò·ÖÏí£¡
£¨1£©Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)£º
orACLEµÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬from×Ó¾äÖÐдÔÚ×îºóµÄ±í(»ù´¡±í driving table)½«±»×îÏÈ´¦Àí£¬ÔÚfrom×Ó¾äÖаüº¬¶à¸ö±íµÄÇé¿öÏ ......
ËÄ£ºORACLEµÄÓÅ»¯Æ÷
ÓÅ»¯Æ÷ÓÐʱҲ±»³ÆΪ²éѯÓÅ»¯Æ÷£¬ÕâÊÇÒòΪ²éѯÊÇÓ°ÏìÊý¾Ý¿âÐÔÄÜ×îÖ÷ÒªµÄ²¿·Ö£¬²»ÒªÒÔΪֻÓÐSELECTÓï¾äÊDzéѯ¡£Êµ¼ÊÉÏ£¬´øÓÐÈκÎWHEREÌõ¼þµÄDML(INSERT¡¢UPDATE¡¢DELETE)Óï¾äÖж¼°üº¬²éѯҪÇó£¬ÔÚºóÃæµÄÎÄÕÂÖУ¬µ±Ëµµ½²éѯʱ£¬²»Ò»¶¨Ö»ÊÇÖ¸SELECTÓï¾ä£¬Ò²ÓпÉÄÜÖ¸DMLÓï¾äÖеIJéѯ²¿·Ö¡£ÓÅ»¯Æ÷ÊÇËùÓйØϵ ......