SQL Server: Inner Join¼°Outer Join, Natrual Jion
Ò»¡¢NATURAL JOIN£¨×ÔÈ»Á¬½Ó£©
Á½Õűíͨ¹ýNATURAL JOINÁ¬½ÓµÄʱºò£¬Ï൱ÓÚÓиöÒþº¬µÄWHERE×Ӿ䣬¶ÔÁ½ÕűíÖÐͬÃûµÄ¶ÔÓ¦ÁÐÏà±È½Ï¿´ÊÇ·ñÏàµÈ¡£
¶þ¡¢CROSS JOIN£¨´´½¨µÑ¿¨¶û»ý£©
¶ÔÁ½Õűíͨ¹ý½»²æÁªºÏ²úÉúµÚÈýÕÅ·µ»Ø½á¹û¼¯µÄ±í¡£Ï൱ÓÚÆÕͨµÄÁ¬½Ó¡£
Èý¡¢INNER JOIN£¨ÄÚÁ¬½Ó£©
ÄÚÁ¬½Ó¾ÍÏ൱ÓÚÆÕͨµÄCROSS JOIN£¬Ö»ÊǸñʽ²»Ò»Ñù£¬INNER JOINÔÚºóÃæÓÐÒ»¸öON×Ӿ䣨Ï൱ÓÚWHERE£©µÄËÑË÷Ìõ¼þ£¬ÓÃÓÚ¹ýÂË·µ»ØµÄÐС£
ËÄ¡¢OUTER JOIN £¨ÍâÁ¬½Ó£©
select * from ta outer join tb on (ta.c1=tb.c1)
outer join¸æËßDBMSÉú³É½á¹û±í£¬Ôڴ˱íÖв»½ö´øÓÐÏà¹Ø(ta.c1=tb.c1)Ðжԣ¬¶øÇÒ»¹ÓÐÀ´×ÔÁ½¸öÔ´±íÖÐÈÎÒ»±íµÄ²»Æ¥ÅäµÄÐС£
Îå¡¢LEFT OUTER JOIN£¨×óÁ¬½Ó£© RIGHT OUTER JOIN£¨ÓÒÁ¬½Ó£©
select * from ta left outer join tb on (ta.c1=tb.c1)
select * from ta right outer join tb on (ta.c1=tb.c1)
left outer join(left join) ¸æËßDBMSÉú³É°üÀ¨ÁªºÏÐкÍÈκβ»Æ¥ÅäµÄÐеĽá¹û±í£¬µ«ÊDz»Æ¥ÅäµÄÐÐϵÀ´×Ô²éѯµÄfrom×Ó¾äÖÐLEFT OUTER JOIN¹Ø¼ü´Ê×ó±ßµÄ±íÖС£
right outer join(right join)Óëleft outer join(left join)¸ÕºÃÏà·´¡£
Áù¡¢FULL OUTER JOIN£¨È«Á¬½Ó£©
FULL OUTER JOIN ·µ»Ø×éºÏÁËLEFT OUTER JOIN ºÍRIGHT OUTER JOIN½á¹ûµÄ±í¡£
ÒÔÏÂÊÇInner Join¼°Outer JoinµÄ¾ßÌå½éÉÜ£º
ÔÚÒ»¸öÕý¹æ»¯µÄÊý¾Ý¿â»·¾³ÖÐ, ÎÒÃdz£»áÅöµ½Õâ¿îÇéÐÎ: ËùÐèµÄ×ÊÁϲ¢²»ÊÇ·ÅÔÚͬһ¸ö×ÊÁϱíÖÐ, ÔÚÕâ¸öʱºò, Äã¾ÍÒªÓõ½ Join¡£
¡¡¡¡µ±È» Join ÈçºÎ½«²»Í¬µÄÊý¾Ý¿âµÄ×ÊÁϽáºÏ, »¹Òª¿´ÄãÈçºÎʹÓÃËü, Ò»¹²ÓÐËÄÖÖ²»Í¬µÄ Join µÄ·½Ê½, ÔÚÕâƪÎÄÕÂÖÐÎÒÃǽ«ÎªÄã½éÉÜ Inner Join ¼° Outer Join ÒÔ¼°ÆäÓ¦Óá£
¡¡¡¡Inner Join
¡¡¡¡Inner Join Ó¦¸ÃÊÇ×î³£ÓÃµÄ Join ·½Ê½, ËüÖ»»á´«»Ø·ûºÏ Join ¹æÔòµÄ¼Í¼, »¹ÊÇÏÈÀ´¿´¿´Óï·¨£º
Select <ҪѡÔñµÄ×Ö¶Î> from <Ö÷Òª×ÊÁϱí> <Join ·½Ê½> <´ÎÒª×ÊÁϱí> [On <Join ¹æÔò>]
¡¡¡¡ÏÖÔÚÎÒÃÇÀûÓà MS SQL ÄÚ½¨µÄ±±·çÊý¾Ý¿âÀ´Êµ¼ÊÁ·Ï°Ò»ÏÂ! Ïë¶àÁ˽â MS SQL µÄÄÚ½¨Êý¾Ý¿â, Äã¿ÉÒÔ¿´¿´ SQL Server µÄÄÚ½¨Êý¾Ý¿âÕâƪÎÄÕ¡£
¡¡¡¡Çë´ò¿ª
Ïà¹ØÎĵµ£º
CONVERT
½«Ä³ÖÖÊý¾ÝÀàÐ͵ıí´ïʽÏÔʽת»»ÎªÁíÒ»ÖÖÊý¾ÝÀàÐÍ¡£ÓÉÓÚijЩÐèÇó¾³£Óõ½È¡ÈÕÆÚ¸ñʽµÄ²»Í¬.ÏÖÒÔÏ¿ÉÔÚ
SQL ServerÖС¡½«ÈÕÆÚ¸ñʽ»¯.
SQL Server Ö§³ÖʹÓÿÆÍþÌØËã·¨µÄ°¢À²®ÑùʽÖеÄÊý¾Ý¸ñʽ¡£
ÔÚ±íÖУ¬×ó²àµÄÁ½Áбíʾ½« datetime »ò smalldatetime ת»»Îª×Ö·ûÊý¾ÝµÄ style Öµ¡£¸ø style Öµ¼Ó 100£¬¿É»ñµÃ°üÀ¨Ê ......
SQLÃæÊÔÌ⣨1£©
create table testtable1
(
id int IDENTITY,
department varchar(12)
)
select * from testtable1
insert into testtable1 values('Éè¼Æ')
insert into testtable1 values('Êг¡')
insert into testtable1 values('ÊÛºó')
/*
½á¹û
id department
1 Éè¼Æ
2 Êг¡
......
ÉÏÖÜÒ»java µÄʧÀûÁË£¬ËäÈ»»¹Ã»Õýʽ¿ªÕâÃſΣ¬µ«ÎÒÒ²×ÔѧÁË°ëÄ꣬Õâ´ÎµÄʧÀûÈÃÎÒÉî¿ÌÈÏʶÁË×Ô¼ºËù´æÔڵIJ»×㡣ΪÒÔºóѧϰÁôÏÂÁ˱¦¹óµÄ¾Ñé¡£Ê컰˵£º¡°²»È뻢Ѩ£¬Ñɵû¢×Ó¡±¡£
ºóÌ죬ÎÒÒªÔÙÒ»´ÎÈ¥±±´ó¡£Õâ´Î¿¼SQL SERVER2000 £¬Í¬Ñù£¬ÕâÃÅ»¹Ã»Ñ§Í꣬Æäʵ£¬ÊǸտªÊ¼Ñ§¡£Ñ§µ½Á˵ÚÈýÕ¹ØϵģʽÉè¼ÆÔÀí £¬ ËäȻûÓбØʤµÄÐÅÐÄ ......
UNION Ö¸ÁîµÄÄ¿µÄÊǽ«Á½¸ö SQL Óï¾äµÄ½á¹ûºÏ²¢ÆðÀ´¡£´ÓÕâ¸ö½Ç¶ÈÀ´¿´£¬ UNION ¸ú JOIN ÓÐЩÐíÀàËÆ£¬ÒòΪÕâÁ½¸öÖ¸Á¿ÉÒÔÓɶà¸ö±í¸ñÖÐߢȡ×ÊÁÏ¡£ UNION µÄÒ»¸öÏÞÖÆÊÇÁ½¸ö SQL Óï¾äËù²úÉúµÄÀ¸Î»ÐèÒªÊÇͬÑùµÄ×ÊÁÏÖÖÀà¡£ÁíÍ⣬µ±ÎÒÃÇÓà UNIONÕâ¸öÖ¸Áîʱ£¬ÎÒÃÇÖ»»á¿´µ½²»Í¬µÄ×ÊÁÏÖµ (ÀàËÆ SELECT DISTINCT)¡£ unionÖ»Êǽ«Á½¸ö½á¹ ......
SQL JOIN
SQL join ÓÃÓÚ¸ù¾ÝÁ½¸ö»ò¶à¸ö±íÖеÄÁÐÖ®¼äµÄ¹Øϵ£¬´ÓÕâЩ±íÖвéѯÊý¾Ý¡£
Join ºÍ Key
ÓÐʱΪÁ˵õ½ÍêÕûµÄ½á¹û£¬ÎÒÃÇÐèÒª´ÓÁ½¸ö»ò¸ü¶àµÄ±íÖлñÈ¡½á¹û¡£ÎÒÃǾÍÐèÒªÖ´ÐÐ join¡£
Êý¾Ý¿âÖеıí¿Éͨ¹ý¼ü½«±Ë´ËÁªÏµÆðÀ´¡£Ö÷¼ü£¨Primary Key£©ÊÇÒ»¸öÁУ¬ÔÚÕâ¸öÁÐÖеÄÿһÐеÄÖµ¶¼ÊÇΨһµÄ¡£ÔÚ±íÖУ¬Ã¿¸öÖ÷¼üµÄ ......