join ºÍ UnionºÍSQL Union All µÄÇø±ð
SQL UNION ²Ù×÷·û
UNION ²Ù×÷·ûÓÃÓںϲ¢Á½¸ö»ò¶à¸ö SELECT Óï¾äµÄ½á¹û¼¯¡£
Çë×¢Ò⣬UNION ÄÚ²¿µÄ SELECT Óï¾ä±ØÐëÓµÓÐÏàͬÊýÁ¿µÄÁС£ÁÐÒ²±ØÐëÓµÓÐÏàËƵÄÊý¾ÝÀàÐÍ¡£Í¬Ê±£¬Ã¿Ìõ SELECT Óï¾äÖеÄÁеÄ˳Ðò±ØÐëÏàͬ¡£
SQL UNION Óï·¨
SELECT column_name(s) from table_name1
UNION
SELECT column_name(s) from table_name2
×¢ÊÍ£ºÄ¬Èϵأ¬UNION ²Ù×÷·ûÑ¡È¡²»Í¬µÄÖµ¡£Èç¹ûÔÊÐíÖظ´µÄÖµ£¬ÇëʹÓà UNION ALL¡£
SQL UNION ALL Óï·¨
SELECT column_name(s) from table_name1
UNION ALL
SELECT column_name(s) from table_name2
ÁíÍ⣬UNION ½á¹û¼¯ÖеÄÁÐÃû×ÜÊǵÈÓÚ UNION ÖеÚÒ»¸ö SELECT Óï¾äÖеÄÁÐÃû¡£
ÏÂÃæµÄÀý×ÓÖÐʹÓõÄÔʼ±í£º
Employees_China:
E_IDE_Name
01
Zhang, Hua
02
Wang, Wei
03
Carter, Thomas
04
Yang, Ming
Employees_USA:
E_IDE_Name
01
Adams, John
02
Bush, George
03
Carter, Thomas
04
Gates, Bill
ʹÓà UNION ÃüÁî
ʵÀý
ÁгöËùÓÐÔÚÖйúºÍÃÀ¹úµÄ²»Í¬µÄ¹ÍÔ±Ãû£º
SELECT E_Name from Employees_China
UNION
SELECT E_Name from Employees_USA
½á¹û
E_Name
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming
Adams, John
Bush, George
Gates, Bill
×¢ÊÍ£ºÕâ¸öÃüÁîÎÞ·¨ÁгöÔÚÖйúºÍÃÀ¹úµÄËùÓйÍÔ±¡£ÔÚÉÏÃæµÄÀý×ÓÖУ¬ÎÒÃÇÓÐÁ½¸öÃû×ÖÏàͬµÄ¹ÍÔ±£¬ËûÃǵ±ÖÐÖ»ÓÐÒ»¸öÈ˱»ÁгöÀ´ÁË¡£UNION ÃüÁîÖ»»áÑ¡È¡²»Í¬µÄÖµ¡£
UNION ALL
UNION ALL ÃüÁîºÍ UNION ÃüÁºõÊǵÈЧµÄ£¬²»¹ý UNION ALL ÃüÁî»áÁгöËùÓеÄÖµ¡£
SQL Statement 1
UNION ALL
SQL Statement 2
ʹÓà UNION ALL ÃüÁî
ʵÀý£º
ÁгöÔÚÖйúºÍÃÀ¹úµÄËùÓеĹÍÔ±£º
SELECT E_Name from Employees_China
UNION ALL
SELECT E_Name from Employees_USA
½á¹û
E_Name
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming
Adams, John
Bush, George
Carter, Thomas
Gates, Bill
SQL JOIN
Previous Page
Next Page
SQL join ÓÃÓÚ¸ù¾ÝÁ½¸ö»ò¶à¸ö±íÖеÄÁÐÖ®¼äµÄ¹Øϵ£¬´ÓÕâЩ±íÖвéѯÊý¾Ý¡£
Join ºÍ Key
ÓÐʱΪÁ˵õ½ÍêÕûµÄ½á¹û£¬ÎÒÃÇÐèÒª´ÓÁ½¸ö»ò¸ü¶àµÄ±íÖлñÈ¡½á¹û¡£ÎÒÃǾÍÐèÒªÖ´ÐÐ join¡£
Êý¾Ý¿âÖеıí¿Éͨ¹ý¼ü½«±Ë´ËÁªÏµÆðÀ´¡£Ö÷¼ü£¨Primary Key£©ÊÇÒ»¸öÁУ¬ÔÚÕâ¸öÁÐÖеÄÿһÐеÄÖµ¶¼ÊÇΨһµÄ¡£ÔÚ±íÖУ¬Ã¿¸öÖ÷¼üµÄÖµ¶¼ÊÇΨһµÄ¡£ÕâÑù×öµÄÄ¿µÄÊÇÔÚ²»Öظ´Ã¿¸ö±íÖеÄËùÓÐÊý¾ÝµÄÇé¿öÏ£¬°Ñ±í¼äµÄÊý¾Ý½»²æÀ¦°óÔÚÒ»Æð¡£
Çë¿´ "
Ïà¹ØÎĵµ£º
²éѯËÙ¶ÈÂýµÄÔÒòºÜ¶à£¬³£¼ûÈçϼ¸ÖÖ£º
1¡¢Ã»ÓÐË÷Òý»òÕßûÓÐÓõ½Ë÷Òý(ÕâÊDzéѯÂý×î³£¼ûµÄÎÊÌ⣬ÊdzÌÐòÉè¼ÆµÄȱÏÝ)
2¡¢I/OÍÌÍÂÁ¿Ð¡£¬ÐγÉÁËÆ¿¾±Ð§Ó¦¡£
3¡¢Ã»Óд´½¨¼ÆËãÁе¼Ö²éѯ²»ÓÅ»¯¡£
4¡¢ÄÚ´æ²»×ã
5¡¢ÍøÂçËÙ¶ÈÂý
6¡¢²éѯ³öµÄÊý¾ÝÁ¿¹ý´ó£¨¿ÉÒÔ²ÉÓöà´Î²éѯ£¬ÆäËûµÄ·½·¨½µµÍÊý¾ÝÁ¿£©
7¡¢Ëø»òÕßËÀËø(ÕâÒ²ÊDzéѯÂý×î³ ......
JavaÊý¾ÝÀàÐÍ
HibernateÊý¾ÝÀàÐÍ
±ê×¼SQLÊý¾ÝÀàÐÍ
byte
byte
TINYINT
short
short
SMALLINT
int
integer
INGEGER
long
long
BIGINT
float
float
FLOAT
double
double
DOUBLE
java.math.BigDecimal
big_decimal
NUMERIC
char, java.lang.Character
character
CHAR(1)
boolean, java.lang.Boole ......
µ±Ë÷ÒýËùÔÚÒ³ÃæµÄ»ùÓÚÖ÷¹Ø¼ü×ÖµÄÂ߼˳Ðò£¬ºÍÊý¾ÝÎļþÖеÄÎïÀí˳Ðò²»Æ¥Åäʱ£¬ËéƬ¾Í²úÉúÁË¡£ËùÓеÄÒ¶¼¶Ò³°üº¬ÁËÖ¸ÏòÇ°Ò»¸öºÍºóÒ»¸öÒ³µÄÖ¸Õë¡£ÕâÑù¾ÍÐγÉÒ»¸öË«Á´±í¡£ÀíÏëÇé¿öÏ£¬Êý¾ÝÎļþÖÐÒ³µÄÎïÀí˳Ðò»áºÍÂ߼˳ÐòÆ¥Åä¡£Õû¸ö´ÅÅ̵Ť×÷ÐÔÄÜÔÚÎïÀí˳ÐòÆ¥ÅäÂ߼˳Ðòʱ½«ÏÔÖøÌáÉý¡£¶ÔijЩÌض¨µÄ²éѯ¶øÑÔ£¬Õ⽫´øÀ´¼«¼ÑµÄÐÔÄÜ ......
ÒÔÇ°¶ÔÓÚSql ServerÖÐCaseÃüÁîµÄÓ÷¨Ò»Ö±²»ÊǺÜÁ˽⣬½ñÌìͨ¹ýÉÏÍø²éÁËһЩ×ÊÁÏ£¬½«Case»ù±¾µÄÓ÷¨×ܽáһϡ£
CaseÃüÁîÓÐÁ½ÖÖÓï·¨£º
A£©CASE<ÔËËãʽ>
WHEN <ÔËËãʽ> THEN <ÔËËãʽ>
……
WHEN <ÔËËãʽ> THEN <ÔËËãʽ>
[ELSE<ÔËËãʽ> ......
ת×ÔCSDNÂÛ̳£ºchaihuoniu
ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
SQL·ÖÀࣺ
DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
1¡¢ËµÃ÷£º´´½¨Êý¾ ......