¡¡¡¡ ÔÚselect²éѯÓï¾äÀï¿ÉÒÔǶÈëselect²éѯÓï¾ä£¬³ÆΪǶÌײéѯ¡£ÓÐЩÊéÉϽ«ÄÚǶµÄselectÓï¾ä³ÆΪ×Ó²éѯ£¬×Ó²éѯÐγɵĽá¹ûÓÖ³ÉΪ¸¸²éѯµÄÌõ¼þ¡£
×Ó²éѯ¿ÉÒÔǶÌ׶à²ã£¬×Ó²éѯ²Ù×÷µÄÊý¾Ý±í¿ÉÒÔÊǸ¸²éѯ²»²Ù×÷µÄÊý¾Ý±í¡£×Ó²éѯÖв»ÄÜÓÐorder by·Ö×éÓï¾ä¡£
4.4.1 ¼òµ¥Ç¶Ìײéѯ
ÔÚ¡¾ÃüÁî±à¼Çø¡¿Ö´ÐÐÏÂÁÐÓï¾ä¡£
¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal>=(select sal from scott.emp where ename='WARD'); ¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D
µ¥»÷¡¾Ö´ÐС¿°´Å¥£¬³öÏÖÈçͼ4.19ËùʾµÄ½á¹û¡£
¡¾²Î¼û¹âÅÌÎļþ¡¿£º\µÚ4ÕÂ\4.4\441.sql¡£
ÔÚÕâ¶Î´úÂëÖУ¬×Ó²éѯselect sal from scott.emp where ename='WARD'µÄº¬ÒåÊÇ´ÓempÊý¾Ý±íÖвéѯÐÕÃûΪWARDµÄÔ±¹¤µÄнˮ£¬¸¸²éѯµÄº¬ÒåÊÇÒªÕÒ³öempÊý¾Ý±íÖÐнˮ´óÓÚµÈÓÚWARDµÄнˮµÄÔ±¹¤¡£ÉÏÃæµÄ²éѯ¹ý³ÌµÈ¼ÛÓÚÁ½²½µÄÖ´Ðйý³Ì¡£
£¨1£©Ö´ÐС°select sal from scott.emp where ename='WARD'¡±£¬µÃ³ösal=1250£»
£¨2£©Ö´ÐС°select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal>=1250;¡±
4.4.2 ´ø¡¾in¡¿µÄǶÌײéѯ
ÔÚ¡¾ÃüÁî±à¼Çø¡¿Ö´ÐÐÏÂÁÐÓï¾ä¡£
¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal in (select sal from scott.emp where ename='WARD');
¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D
µ¥»÷¡¾Ö´ÐС¿°´Å¥£¬³öÏÖÈçͼ4.20ËùʾµÄ½á¹û¡£
¡¾²Î¼û¹âÅÌÎļþ¡¿£º\µÚ4ÕÂ\4.4\442.sql¡£
ÉÏÊöÓï¾äÍê³ÉµÄÊDzéѯнˮºÍWARDÏàµÈµÄÔ±¹¤£¬Ò²¿ÉÒÔʹÓá¾not in¡¿À´½øÐвéѯ¡£ 4.4.3 ´ø¡¾any¡¿µÄǶÌײéѯ
ÔÚ¡¾ÃüÁî±à¼Çø¡¿Ö´ÐÐÏÂÁÐÓï¾ä¡£
¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal >any(select sal from scott.emp where job='MANAGER');
¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D
µ¥»÷¡¾Ö´ÐС¿°´Å¥£¬³öÏÖÈçͼ4.21ËùʾµÄ½á¹û¡£
¡¾²Î¼û¹âÅÌÎļþ¡¿£º\µÚ4ÕÂ\4.4\443.sql¡£
´øanyµÄ²éѯ¹ý³ÌµÈ¼ÛÓÚÁ½²½µÄÖ´Ðйý³Ì¡£
£¨1£©Ö´ÐС°select sal from scott.emp where job='MANAGER'¡±,Æä½á¹ûÈçͼ4.22Ëùʾ¡£
¡¾²Î¼û¹âÅÌÎļþ¡¿£º\µÚ4ÕÂ\4.4\443-1.sql¡£
£¨2£©²éѯµ½3¸öнˮֵ2975¡¢2850ºÍ2450£¬¸¸²éѯִÐÐÏÂÁÐÓï¾ä¡£
¡¾²Î¼û¹âÅÌÎļþ¡¿£º\µÚ4ÕÂ\4.4\443-2.sql¡£
¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal >2975 or sal>2850 or sal>2450; ¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D
4.4.4 ´ø¡¾some¡¿µÄǶÌײéѯ
ÔÚ¡¾ÃüÁî±à¼Çø¡¿Ö´ÐÐÏÂÁÐÓï¾ä¡£
¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal =some(select sal from scott.emp where job='MANAGER');
¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D
µ¥»÷¡¾Ö´ÐС¿°´Å¥£¬³öÏÖÈçͼ4.23ËùʾµÄ½á¹û¡£
¡¾²Î¼û¹âÅÌÎļþ¡¿£º\µÚ4ÕÂ\4.4\444.sql¡£
´øsomeµÄǶÌײéѯÓëanyµÄ²½ÖèÏàͬ¡£
£¨1£©×Ó²éѯ,Ö´ÐС°select sal from scott.emp where job='MANAGER'¡±,Æä½á¹ûÈçͼ4.22Ëùʾ¡£
£¨2£©¸¸²éѯִÐÐÏÂÁÐÓï¾ä¡£
¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal =2975 or sal=2850 or sal=2450; ¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D
¡¾²Î¼û¹âÅÌÎļþ¡¿£º\µÚ4ÕÂ\4.4\444-2.sql¡£
´ø¡¾any¡¿µÄǶÌײéѯºÍ¡¾some¡¿µÄǶÌײéѯ¹¦ÄÜÊÇÒ»ÑùµÄ¡£ÔçÆÚµÄSQL½ö½öÔÊÐíʹÓá¾any¡¿£¬ºóÀ´µÄ°æ±¾ÎªÁ˺ÍÓ¢ÓïµÄ¡¾any¡¿ÏàÇø·Ö£¬ÒýÈëÁË¡¾some¡¿£¬Í¬Ê±»¹±£ÁôÁË¡¾any¡¿¹Ø¼ü´Ê¡£
4.4.5 ´ø¡¾all¡¿µÄǶÌײéѯ
ÔÚ¡¾ÃüÁî±à¼Çø¡¿Ö´ÐÐÏÂÁÐÓï¾ä¡£
¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal >all(select sal from scott.emp where job='MANAGER');
¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D
µ¥»÷¡¾Ö´ÐС¿°´Å¥£¬³öÏÖÈçͼ4.24ËùʾµÄ½á¹û¡£
¡¾²Î¼û¹âÅÌÎļþ¡¿£º\µÚ4ÕÂ\4.4\445.sql¡£
´øallµÄǶÌײéѯÓ롾some¡¿µÄ²½ÖèÏàͬ¡£
£¨1£©×Ó²éѯ£¬½á¹ûÈçͼ4.22Ëùʾ¡£
£¨2£©¸¸²éѯִÐÐÏÂÁÐÓï¾ä¡£
¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal >2975 and sal>2850 and sal>2450;
¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D
¡¾²Î¼û¹âÅÌÎļþ¡¿£º\µÚ4ÕÂ\4.4\445-2.sql¡£
4.4.6 ´ø¡¾exists¡¿µÄǶÌײéѯ
ÔÚ¡¾ÃüÁî±à¼Çø¡¿Ö´ÐÐÏÂÁÐÓï¾ä¡£
¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp,scott.dept where exists (select * from scott.emp where scott.emp.deptno=scott.dept.deptno);
¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D
µ¥»÷¡¾Ö´ÐС¿°´Å¥£¬³öÏÖÈçͼ4.25ËùʾµÄ½á¹û¡£
¡¾²Î¼û¹âÅÌÎļþ¡¿£º\µÚ4ÕÂ\4.4\446.sql¡£
4.4.7 ²¢²Ù×÷µÄǶÌײéѯ
²¢²Ù×÷¾ÍÊǼ¯ºÏÖв¢¼¯µÄ¸ÅÄî¡£ÊôÓÚ¼¯ºÏA»ò¼¯ºÏBµÄÔªËØ×ܺ;ÍÊDz¢¼¯¡£ ÔÚ¡¾ÃüÁî±à¼Çø¡¿Ö´ÐÐÏÂÁÐÓï¾ä¡£
¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D
(select deptno from scott.emp) union (select deptno from scott.dept); ¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D
µ¥»÷¡¾Ö´ÐС¿°´Å¥£¬³öÏÖÈçͼ4.26ËùʾµÄ½á¹û¡£
¡¾²Î¼û¹âÅÌÎļþ¡¿£º\µÚ4ÕÂ\4.4\447.sql¡£
4.4.8 ½»²Ù×÷µÄǶÌײéѯ
½»²Ù×÷¾ÍÊǼ¯ºÏÖн»¼¯µÄ¸ÅÄî¡£ÊôÓÚ¼¯ºÏAÇÒÊôÓÚ¼¯ºÏBµÄÔªËØ×ܺ;ÍÊǽ»¼¯¡£ ÔÚ¡¾ÃüÁî±à¼Çø¡¿Ö´ÐÐÏÂÁÐÓï¾ä¡£
¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D
(select deptno from scott.emp) intersect (select deptno from scott.dept); ¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D
µ¥»÷¡¾Ö´ÐС¿°´Å¥£¬³öÏÖÈçͼ4.27ËùʾµÄ½á¹û¡£
¡¾²Î¼û¹âÅÌÎļþ¡¿£º\µÚ4ÕÂ\4.4\448.sql¡£
4.4.9 ²î²Ù×÷µÄǶÌײéѯ
²î²Ù×÷¾ÍÊǼ¯ºÏÖвµÄ¸ÅÄî¡£ÊôÓÚ¼¯ºÏAÇÒ²»ÊôÓÚ¼¯ºÏBµÄÔªËØ×ܺ;ÍÊDz¡£
ÔÚ¡¾ÃüÁî±à¼Çø¡¿Ö´ÐÐÏÂÁÐÓï¾ä¡£
¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D
(select deptno from scott.dept) minus (select deptno from scott.emp);
¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D¨D
µ¥»÷¡¾Ö´ÐС¿°´Å¥£¬³öÏÖÈçͼ4.28ËùʾµÄ½á¹û¡£
¡¾²Î¼û¹âÅÌÎļþ¡¿£º\µÚ4ÕÂ\4.4\449.sql¡£
²¢¡¢½»ºÍ²î²Ù×÷µÄǶÌײéѯҪÇóÊôÐÔ¾ßÓÐÏàͬµÄ¶¨Ò壬°üÀ¨ÀàÐͺÍÈ¡Öµ·¶Î§¡£
ÓÃSQL½øÐÐǶÌײéѯ
80¿á¿áÍø 80kuku.com