{"id":119100,"date":"2026-10-28T22:40:31","date_gmt":"2026-10-28T14:40:31","guid":{"rendered":"https:\/\/www.wsisp.com\/helps\/119100.html"},"modified":"2026-10-28T22:40:31","modified_gmt":"2026-10-28T14:40:31","slug":"mysql-%e5%a4%8d%e5%90%88%e6%9f%a5%e8%af%a2%ef%bc%9a%e5%a4%9a%e8%a1%a8-%e8%87%aa%e8%bf%9e%e6%8e%a5-%e5%ad%90%e6%9f%a5%e8%af%a2-union%ef%bc%8c%e4%b8%80%e7%af%87%e8%ae%b2%e9%80%8f","status":"publish","type":"post","link":"https:\/\/www.wsisp.com\/helps\/119100.html","title":{"rendered":"MySQL \u590d\u5408\u67e5\u8be2\uff1a\u591a\u8868 \/ \u81ea\u8fde\u63a5 \/ \u5b50\u67e5\u8be2 \/ UNION\uff0c\u4e00\u7bc7\u8bb2\u900f"},"content":{"rendered":"<h2 id=\"mysql-\u590d\u5408\u67e5\u8be2\uff1a\u591a\u8868--\u81ea\u8fde\u63a5--\u5b50\u67e5\u8be2--union\uff0c\u4e00\u7bc7\u8bb2\u900f\">\u76ee\u5f55<\/h2>\n<ul>\n<li>0. \u5f00\u7bc7&#xff1a;\u5355\u8868\u67e5\u8be2\u7684\u5929\u82b1\u677f<\/li>\n<li>1. \u5b9e\u9a8c\u73af\u5883&#xff1a;\u4e09\u5f20\u7ecf\u5178\u8868<\/li>\n<li>2. \u57fa\u672c\u67e5\u8be2\u56de\u987e&#xff08;8 \u9053\u9898&#xff0c;\u6bcf\u9898\u4e00\u4e2a\u7ec6\u8282&#xff09;<\/li>\n<li>3. \u591a\u8868\u67e5\u8be2&#xff1a;\u4ece\u300c\u7b1b\u5361\u5c14\u79ef\u300d\u8bf4\u8d77<\/li>\n<li>4. \u81ea\u8fde\u63a5&#xff1a;\u628a\u4e00\u5f20\u8868\u5f53\u6210\u4e24\u5f20\u7528<\/li>\n<li>5. \u5b50\u67e5\u8be2&#xff1a;SQL \u91cc\u7684\u300c\u5957\u5a03\u300d<\/li>\n<li>6. \u5408\u5e76\u67e5\u8be2&#xff1a;UNION \u4e0e UNION ALL<\/li>\n<li>7. \u8865\u5168&#xff1a;\u591a\u8868\u67e5\u8be2\u7684\u6267\u884c\u987a\u5e8f\u4e0e\u6027\u80fd\u601d\u7ef4<\/li>\n<li>8. \u5b9e\u6218 OJ<\/li>\n<li>9. \u62a5\u9519\u901f\u67e5<\/li>\n<li>10. \u9762\u8bd5\u9ad8\u9891 12 \u95ee<\/li>\n<li>11. \u603b\u7ed3&#xff1a;\u4e00\u5f20\u56fe &#043; \u4e00\u53e5\u53e3\u8bc0<\/li>\n<li>\u9644&#xff1a;\u4e00\u952e\u53ef\u6267\u884c\u5b8c\u6574\u811a\u672c<\/li>\n<\/ul>\n<hr \/>\n<h3 id=\"0-\u5f00\u7bc7\uff1a\u5355\u8868\u67e5\u8be2\u7684\u5929\u82b1\u677f\">0. \u5f00\u7bc7&#xff1a;\u5355\u8868\u67e5\u8be2\u7684\u5929\u82b1\u677f<\/h3>\n<p>\u5148\u770b\u4e00\u4e2a\u9700\u6c42&#xff0c;\u6765\u81ea\u8bfe\u4ef6\u7684\u7b2c\u4e00\u4e2a\u591a\u8868\u6848\u4f8b&#xff1a;<\/p>\n<p>\u663e\u793a\u96c7\u5458\u540d\u3001\u96c7\u5458\u5de5\u8d44&#xff0c;\u4ee5\u53ca\u6240\u5728\u90e8\u95e8\u7684\u540d\u5b57\u3002<\/p>\n<p>\u4f60\u4f1a\u53d1\u73b0\u5355\u8868\u67e5\u8be2\u505a\u4e0d\u4e86&#xff1a;<\/p>\n<ul>\n<li>\u96c7\u5458\u540d\u3001\u5de5\u8d44 \u2192 \u5728 EMP \u8868<\/li>\n<li>\u90e8\u95e8\u540d \u2192 \u5728 DEPT \u8868<\/li>\n<\/ul>\n<p>\u8fd9\u4e0d\u662f\u8bed\u6cd5\u95ee\u9898&#xff0c;\u662f\u6570\u636e\u88ab\u300c\u62c6\u300d\u5f00\u4e86\u3002<\/p>\n<p>\u4e3a\u4ec0\u4e48\u8981\u628a\u6570\u636e\u62c6\u5f00&#xff1f;\u56e0\u4e3a\u6570\u636e\u5e93\u8bbe\u8ba1\u7684\u7b2c\u4e09\u8303\u5f0f&#xff1a;<\/p>\n<ul>\n<li>\u5982\u679c\u6bcf\u6761\u5458\u5de5\u8bb0\u5f55\u91cc\u90fd\u5b58\u4e00\u4efd\u90e8\u95e8\u540d&#xff0c;\u90a3\u300cACCOUNTING\u300d\u8fd9\u4e2a\u8bcd\u8981\u91cd\u590d\u5b58 3 \u904d&#xff08;10 \u53f7\u90e8\u95e8\u6709 3 \u4e2a\u4eba&#xff09;<\/li>\n<li>\u90e8\u95e8\u6539\u540d\u65f6&#xff0c;3 \u6761\u8bb0\u5f55\u90fd\u8981\u6539 \u2192 \u66f4\u65b0\u5f02\u5e38<\/li>\n<li>\u5982\u679c\u67d0\u4e2a\u90e8\u95e8\u8fd8\u6ca1\u4eba&#xff0c;\u90e8\u95e8\u540d\u5c31\u65e0\u5904\u53ef\u5b58 \u2192 \u63d2\u5165\u5f02\u5e38<\/li>\n<\/ul>\n<p>\u6240\u4ee5\u89c4\u8303\u7684\u505a\u6cd5\u662f&#xff1a;\u90e8\u95e8\u4fe1\u606f\u5b58\u4e00\u4efd\u5728 DEPT&#xff0c;\u5458\u5de5\u8868\u53ea\u5b58 deptno \u8fd9\u4e2a\u300c\u5f15\u7528\u300d\u3002<\/p>\n<p>\u8fd9\u5c31\u662f\u300c\u5173\u7cfb\u578b\u6570\u636e\u5e93\u300d\u91cc\u300c\u5173\u7cfb\u300d\u4e8c\u5b57\u7684\u542b\u4e49\u2014\u2014\u6570\u636e\u4e4b\u95f4\u5b58\u5728\u5173\u8054&#xff0c;\u67e5\u8be2\u65f6\u8981\u628a\u5173\u8054\u62fc\u56de\u6765\u3002<\/p>\n<p>\u800c\u62fc\u56de\u6765\u7684\u624b\u6bb5\u53ea\u6709\u4e09\u79cd&#xff1a;<\/p>\n<table>\n<tr>\u624b\u6bb5\u5173\u952e\u8bcd\u4e00\u53e5\u8bdd<\/tr>\n<tbody>\n<tr>\n<td>\u591a\u8868\u67e5\u8be2<\/td>\n<td>from a, b where &#8230; \/ join<\/td>\n<td>\u4e24\u5f20\u8868\u5e76\u5217&#xff0c;\u6309\u6761\u4ef6\u914d\u5bf9<\/td>\n<\/tr>\n<tr>\n<td>\u81ea\u8fde\u63a5<\/td>\n<td>\u540c\u4e00\u5f20\u8868\u8d77\u4e24\u4e2a\u522b\u540d<\/td>\n<td>\u8868\u8ddf\u81ea\u5df1\u914d\u5bf9<\/td>\n<\/tr>\n<tr>\n<td>\u5b50\u67e5\u8be2<\/td>\n<td>select \u91cc\u5957 select<\/td>\n<td>\u5148\u7b97\u4e00\u4e2a\u4e2d\u95f4\u7ed3\u679c&#xff0c;\u518d\u62ff\u5b83\u53bb\u67e5<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u52a0\u4e0a\u6700\u540e\u6536\u5c3e\u7684 UNION&#xff08;\u628a\u4e24\u4e2a\u7ed3\u679c\u96c6\u62fc\u8d77\u6765&#xff09;&#xff0c;\u5c31\u662f\u672c\u7bc7\u7684\u5168\u90e8\u5185\u5bb9\u3002<\/p>\n<p>\u800c\u5b83\u4eec\u80cc\u540e\u6709\u4e00\u4e2a\u5171\u540c\u7684\u5f71\u5b50&#xff0c;\u4f60\u5fc5\u987b\u5148\u8ba4\u8bc6\u5b83\u2014\u2014\u7b1b\u5361\u5c14\u79ef\u3002<\/p>\n<hr \/>\n<h3 id=\"1-\u5b9e\u9a8c\u73af\u5883\uff1a\u4e09\u5f20\u7ecf\u5178\u8868\">1. \u5b9e\u9a8c\u73af\u5883&#xff1a;\u4e09\u5f20\u7ecf\u5178\u8868<\/h3>\n<p>\u8bfe\u4ef6\u7528\u7684\u662f Oracle 9i \u7684\u7ecf\u5178\u6d4b\u8bd5\u8868 EMP \/ DEPT \/ SALGRADE\u3002\u8fd9\u5957\u8868\u4ece 1980 \u5e74\u4ee3\u7528\u5230\u73b0\u5728&#xff0c;\u51e0\u4e4e\u6240\u6709\u6570\u636e\u5e93\u6559\u6750\u90fd\u5728\u7528\u5b83&#xff0c;\u503c\u5f97\u4f60\u4eb2\u624b\u5efa\u4e00\u904d\u3002<\/p>\n<p>create database if not exists multi_demo default character set utf8mb4;<br \/>\nuse multi_demo;<\/p>\n<p>&#8212; &#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061; \u90e8\u95e8\u8868 &#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;<br \/>\ncreate table dept (<br \/>\n    deptno int primary key,<br \/>\n    dname  varchar(14),<br \/>\n    loc    varchar(13)<br \/>\n);<br \/>\ninsert into dept values<br \/>\n(10, &#039;ACCOUNTING&#039;, &#039;NEW YORK&#039;),<br \/>\n(20, &#039;RESEARCH&#039;,   &#039;DALLAS&#039;),<br \/>\n(30, &#039;SALES&#039;,      &#039;CHICAGO&#039;),<br \/>\n(40, &#039;OPERATIONS&#039;, &#039;BOSTON&#039;);<\/p>\n<p>&#8212; &#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061; \u5458\u5de5\u8868 &#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;<br \/>\ncreate table emp (<br \/>\n    empno    int primary key,<br \/>\n    ename    varchar(10),<br \/>\n    job      varchar(9),<br \/>\n    mgr      int,<br \/>\n    hiredate date,<br \/>\n    sal      decimal(7,2),<br \/>\n    comm     decimal(7,2),<br \/>\n    deptno   int<br \/>\n);<br \/>\ninsert into emp values<br \/>\n(7369, &#039;SMITH&#039;,  &#039;CLERK&#039;,     7902, &#039;1980-12-17&#039;,  800.00,    NULL, 20),<br \/>\n(7499, &#039;ALLEN&#039;,  &#039;SALESMAN&#039;,  7698, &#039;1981-02-20&#039;, 1600.00,  300.00, 30),<br \/>\n(7521, &#039;WARD&#039;,   &#039;SALESMAN&#039;,  7698, &#039;1981-02-22&#039;, 1250.00,  500.00, 30),<br \/>\n(7566, &#039;JONES&#039;,  &#039;MANAGER&#039;,   7839, &#039;1981-04-02&#039;, 2975.00,    NULL, 20),<br \/>\n(7654, &#039;MARTIN&#039;, &#039;SALESMAN&#039;,  7698, &#039;1981-09-28&#039;, 1250.00, 1400.00, 30),<br \/>\n(7698, &#039;BLAKE&#039;,  &#039;MANAGER&#039;,   7839, &#039;1981-05-01&#039;, 2850.00,    NULL, 30),<br \/>\n(7782, &#039;CLARK&#039;,  &#039;MANAGER&#039;,   7839, &#039;1981-06-09&#039;, 2450.00,    NULL, 10),<br \/>\n(7788, &#039;SCOTT&#039;,  &#039;ANALYST&#039;,   7566, &#039;1987-04-19&#039;, 3000.00,    NULL, 20),<br \/>\n(7839, &#039;KING&#039;,   &#039;PRESIDENT&#039;, NULL, &#039;1981-11-17&#039;, 5000.00,    NULL, 10),<br \/>\n(7844, &#039;TURNER&#039;, &#039;SALESMAN&#039;,  7698, &#039;1981-09-08&#039;, 1500.00,    0.00, 30),<br \/>\n(7876, &#039;ADAMS&#039;,  &#039;CLERK&#039;,     7788, &#039;1987-05-23&#039;, 1100.00,    NULL, 20),<br \/>\n(7900, &#039;JAMES&#039;,  &#039;CLERK&#039;,     7698, &#039;1981-12-03&#039;,  950.00,    NULL, 30),<br \/>\n(7902, &#039;FORD&#039;,   &#039;ANALYST&#039;,   7566, &#039;1981-12-03&#039;, 3000.00,    NULL, 20),<br \/>\n(7934, &#039;MILLER&#039;, &#039;CLERK&#039;,     7782, &#039;1982-01-23&#039;, 1300.00,    NULL, 10);<\/p>\n<p>&#8212; &#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061; \u5de5\u8d44\u7b49\u7ea7\u8868 &#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;<br \/>\ncreate table salgrade (<br \/>\n    grade int,<br \/>\n    losal int,<br \/>\n    hisal int<br \/>\n);<br \/>\ninsert into salgrade values<br \/>\n(1,  700, 1200),<br \/>\n(2, 1201, 1400),<br \/>\n(3, 1401, 2000),<br \/>\n(4, 2001, 3000),<br \/>\n(5, 3001, 9999);<\/p>\n<p>\u5148\u8bb0\u4f4f\u4e09\u5f20\u8868\u7684\u300c\u89c4\u6a21\u300d&#xff0c;\u540e\u9762\u4f1a\u7528\u5230&#xff1a;<\/p>\n<table>\n<tr>\u8868\u884c\u6570\u8bf4\u660e<\/tr>\n<tbody>\n<tr>\n<td>emp<\/td>\n<td>14<\/td>\n<td>14 \u4e2a\u5458\u5de5<\/td>\n<\/tr>\n<tr>\n<td>dept<\/td>\n<td>4<\/td>\n<td>4 \u4e2a\u90e8\u95e8&#xff08;40 \u53f7 OPERATIONS \u90e8\u95e8\u4e00\u4e2a\u5458\u5de5\u90fd\u6ca1\u6709&#xff01;&#xff09;<\/td>\n<\/tr>\n<tr>\n<td>salgrade<\/td>\n<td>5<\/td>\n<td>5 \u4e2a\u5de5\u8d44\u6863<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u26a0\ufe0f \u8bf7\u7279\u522b\u6ce8\u610f&#xff1a;dept \u6709 4 \u4e2a\u90e8\u95e8&#xff0c;\u4f46 emp \u91cc\u53ea\u6709 10\/20\/30 \u4e09\u4e2a\u90e8\u95e8\u7684\u5458\u5de5\u2014\u201440 \u53f7\u90e8\u95e8\u662f\u300c\u7a7a\u90e8\u95e8\u300d\u3002<\/p>\n<p>\u8fd9\u4e2a 40 \u53f7\u90e8\u95e8\u662f\u6574\u7bc7\u8bfe\u4ef6\u57cb\u4e0b\u7684\u4e00\u4e2a\u4f0f\u7b14&#xff0c;\u4e5f\u662f\u672c\u7bc7\u7ed3\u5c3e&#xff08;\u548c\u4e0b\u4e00\u7bc7&#xff09;\u6700\u91cd\u8981\u7684\u4e00\u4e2a\u5751\u3002 \u5148\u8bb0\u4f4f\u5b83\u3002<\/p>\n<p>&#x1f517; \u524d\u540e\u7167\u5e94&#xff1a;emp.deptno \u5c31\u662f\u7b2c 05 \u7bc7\u8bb2\u7684\u5916\u952e\u5173\u7cfb&#xff08;\u867d\u7136\u8fd9\u91cc\u6ca1\u5efa\u7269\u7406\u5916\u952e&#xff09;\u3002deptno \u4e0a\u6ca1\u6709\u5916\u952e\u7ea6\u675f&#xff0c;\u6240\u4ee5\u7406\u8bba\u4e0a\u4f60\u53ef\u4ee5\u63d2\u5165 deptno &#061; 99 \u7684\u5458\u5de5\u2014\u2014\u8fd9\u6b63\u662f\u7b2c 05 \u7bc7 9.6 \u8282\u8bf4\u7684\u300c\u903b\u8f91\u5916\u952e\u300d\u573a\u666f\u3002<\/p>\n<hr \/>\n<h3 id=\"2-\u57fa\u672c\u67e5\u8be2\u56de\u987e\uff088-\u9053\u9898\uff0c\u6bcf\u9898\u4e00\u4e2a\u7ec6\u8282\uff09\">2. \u57fa\u672c\u67e5\u8be2\u56de\u987e&#xff08;8 \u9053\u9898&#xff0c;\u6bcf\u9898\u4e00\u4e2a\u7ec6\u8282&#xff09;<\/h3>\n<p>\u8bfe\u4ef6\u5148\u7528 8 \u9053\u5355\u8868\u9898\u70ed\u8eab\u3002\u522b\u8df3\u8fc7\u2014\u2014\u8fd9 8 \u9053\u9898\u91cc\u85cf\u7740\u540e\u9762\u591a\u8868\u67e5\u8be2\u8981\u7528\u7684\u5168\u90e8\u96f6\u4ef6\u3002<\/p>\n<h4 id=\"21-\u5de5\u8d44\u9ad8\u4e8e-500-\u6216\u5c97\u4f4d\u4e3a-manager\uff0c\u4e14\u59d3\u540d\u9996\u5b57\u6bcd\u4e3a-j\">2.1 \u5de5\u8d44\u9ad8\u4e8e 500 \u6216\u5c97\u4f4d\u4e3a MANAGER&#xff0c;\u4e14\u59d3\u540d\u9996\u5b57\u6bcd\u4e3a J<\/h4>\n<p>select * from emp where (sal &gt; 500 or job &#061; &#039;MANAGER&#039;) and ename like &#039;J%&#039;;<\/p>\n<p>\u7ed3\u679c&#xff1a;<\/p>\n<p>&#043;&#8212;&#8212;-&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#043;&#8212;&#8212;&#8211;&#043;<br \/>\n| empno | ename | job       | mgr  | hiredate   | sal     | comm | deptno |<br \/>\n&#043;&#8212;&#8212;-&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#043;&#8212;&#8212;&#8211;&#043;<br \/>\n|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |<br \/>\n|  7900 | JAMES | CLERK     | 7698 | 1981-12-03 |  950.00 | NULL |     30 |<br \/>\n&#043;&#8212;&#8212;-&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#043;&#8212;&#8212;&#8211;&#043;<\/p>\n<p>\u26a0\ufe0f \u8fd9\u9053\u9898\u552f\u4e00\u7684\u4ef7\u503c\u5c31\u662f\u90a3\u4e2a\u62ec\u53f7\u3002<\/p>\n<p>AND \u7684\u4f18\u5148\u7ea7\u9ad8\u4e8e OR\u3002\u5982\u679c\u53bb\u6389\u62ec\u53f7&#xff1a;<\/p>\n<p>&#8212; \u274c \u9519\u8bef&#xff1a;\u7b49\u4ef7\u4e8e sal &gt; 500 OR (job&#061;&#039;MANAGER&#039; AND ename like &#039;J%&#039;)<br \/>\nselect * from emp where sal &gt; 500 or job &#061; &#039;MANAGER&#039; and ename like &#039;J%&#039;;<\/p>\n<p>\u56e0\u4e3a\u6240\u6709\u4eba\u5de5\u8d44\u90fd &gt; 500&#xff0c;\u6240\u4ee5\u8fd9\u4e2a\u67e5\u8be2\u4f1a\u8fd4\u56de\u5168\u8868 14 \u884c\u3002<\/p>\n<p>&#x1f4a1; \u51e1\u662f AND \u548c OR \u6df7\u7528&#xff0c;\u4e00\u5f8b\u52a0\u62ec\u53f7\u3002 \u8fd9\u4e0d\u82b1\u94b1&#xff0c;\u53ea\u6551\u547d\u3002 &#x1f517; \u8fd9\u548c\u7b2c 06 \u7bc7 3.4.8 \u8282\u90a3\u9053\u300c\u5b59\u67d0\u540c\u5b66&#xff0c;\u5426\u5219\u8981\u6c42\u603b\u6210\u7ee9 &gt; 200\u2026\u2026\u300d\u662f\u540c\u4e00\u4e2a\u8003\u70b9\u3002<\/p>\n<h4 id=\"22-\u6309\u90e8\u95e8\u53f7\u5347\u5e8f\u3001\u5de5\u8d44\u964d\u5e8f\u6392\u5e8f\">2.2 \u6309\u90e8\u95e8\u53f7\u5347\u5e8f\u3001\u5de5\u8d44\u964d\u5e8f\u6392\u5e8f<\/h4>\n<p>select * from emp order by deptno, sal desc;<\/p>\n<p>\u7ed3\u679c&#xff1a;<\/p>\n<p>&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#043;&#8212;&#8212;&#8211;&#043;<br \/>\n| empno | ename  | job       | mgr  | hiredate   | sal     | comm | deptno |<br \/>\n&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#043;&#8212;&#8212;&#8211;&#043;<br \/>\n|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |<br \/>\n|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |<br \/>\n|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 | NULL |     10 |<br \/>\n|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |<br \/>\n|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |<br \/>\n|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |<br \/>\n|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 | NULL |     20 |<br \/>\n|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 | NULL |     20 |<br \/>\n|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |<br \/>\n|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 | 300.00 |    30 |<br \/>\n|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |   0.00 |    30 |<br \/>\n|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 |1400.00 |    30 |<br \/>\n|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 | 500.00 |    30 |<br \/>\n|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 | NULL |     30 |<br \/>\n&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#043;&#8212;&#8212;&#8211;&#043;<\/p>\n<p>\u4e09\u4e2a\u89c2\u5bdf&#xff1a;<\/p>\n<p>\u2460 \u7b2c\u4e00\u6392\u5e8f\u952e\u76f8\u540c&#xff0c;\u624d\u8f6e\u5230\u7b2c\u4e8c\u6392\u5e8f\u952e \u2014\u2014 20 \u53f7\u90e8\u95e8\u91cc SCOTT \u548c FORD \u90fd\u662f 3000&#xff0c;\u6b64\u65f6\u5b83\u4eec\u7684\u76f8\u5bf9\u987a\u5e8f\u7531 MySQL \u51b3\u5b9a&#xff08;\u4e0d\u4fdd\u8bc1\u7a33\u5b9a&#xff09;\u3002<\/p>\n<p>&#x1f517; \u524d\u540e\u7167\u5e94&#xff1a;\u7b2c 06 \u7bc7 3.5.2 \u8bb2\u8fc7\u300c\u6392\u5e8f\u4e0d\u7a33\u5b9a\u4f1a\u5bfc\u81f4\u5206\u9875\u91cd\u590d\/\u4e22\u6570\u636e\u300d\u3002\u8fd9\u91cc\u5c31\u662f\u6d3b\u4f8b\u5b50&#xff1a;\u52a0\u4e00\u4e2a empno \u4f5c\u4e3a tiebreaker \u624d\u5b89\u5168\u3002<\/p>\n<p> order by deptno, sal desc, empno<\/p>\n<p>\u2461 deptno \u6ca1\u5199 ASC&#xff0c;\u9ed8\u8ba4\u5c31\u662f\u5347\u5e8f\u3002 \u522b\u88ab\u300c\u4e0d\u5199\u5c31\u4e0d\u6392\u300d\u8bef\u5bfc\u3002<\/p>\n<p>\u2462 40 \u53f7\u90e8\u95e8\u6839\u672c\u4e0d\u5728\u7ed3\u679c\u91cc \u2014\u2014 \u56e0\u4e3a\u5b83\u6ca1\u6709\u5458\u5de5\u3002\u8fd9\u4e2a\u7ec6\u8282&#xff0c;\u662f\u5168\u6587\u6700\u91cd\u8981\u7684\u4f0f\u7b14\u3002 \u5982\u679c\u9700\u6c42\u662f\u300c\u663e\u793a\u6240\u6709\u90e8\u95e8&#xff0c;\u5305\u62ec\u6ca1\u6709\u5458\u5de5\u7684\u300d&#xff0c;order by deptno \u662f\u505a\u4e0d\u5230\u7684\u2014\u2014\u4f60\u9700\u8981\u5916\u8fde\u63a5&#xff08;\u4e0b\u4e00\u7bc7&#xff09;\u3002<\/p>\n<h4 id=\"23-\u7528\u5e74\u85aa\u964d\u5e8f\u6392\u5e8f\">2.3 \u7528\u5e74\u85aa\u964d\u5e8f\u6392\u5e8f<\/h4>\n<p>select ename, sal * 12 &#043; ifnull(comm, 0) as &#039;\u5e74\u85aa&#039; from emp order by \u5e74\u85aa desc;<\/p>\n<p>\u8fd9\u4e00\u884c SQL \u91cc\u6709\u4e09\u4e2a\u77e5\u8bc6\u70b9&#xff0c;\u5fc5\u987b\u5168\u90e8\u5403\u900f&#xff1a;<\/p>\n<p>\u2460 ifnull(comm, 0) \u4e3a\u4ec0\u4e48\u5fc5\u987b\u5199&#xff1f;<\/p>\n<p>\u56e0\u4e3a comm&#xff08;\u5956\u91d1&#xff09;\u6709\u5f88\u591a\u884c\u662f NULL\u3002\u800c&#xff1a;<\/p>\n<p>select 2000 * 12 &#043; null;   &#8212; NULL<\/p>\n<p>sal * 12 &#043; comm \u53ea\u8981 comm \u662f NULL&#xff0c;\u6574\u4e2a\u5e74\u85aa\u5c31\u53d8\u6210 NULL\u3002<\/p>\n<p>&#x1f517; \u524d\u540e\u7167\u5e94&#xff1a;\u8fd9\u662f NULL \u7684\u7b2c N \u6b21\u51fa\u573a\u3002\u7b2c 05 \u7bc7\u8bf4\u300c1 &#043; null &#061; null\u300d&#xff0c;\u7b2c 06 \u7bc7\u8bf4\u300cchinese &#043; math &#043; english \u4f1a\u53d8 NULL\u300d&#xff0c;\u7b2c 07 \u7bc7\u8bf4\u300cconcat \u9047 NULL \u5168\u53d8 NULL\u300d&#xff0c;\u8fd9\u91cc\u662f\u7b2c\u56db\u6b21\u2014\u2014\u540c\u4e00\u4e2a\u89c4\u5f8b&#xff0c;\u6362\u4e86\u4e2a\u6218\u573a\u3002<\/p>\n<p>\u51e1\u662f\u8981\u505a\u300c\u8de8\u5217\u7b97\u672f\u300d&#xff0c;\u5148\u95ee\u4e00\u53e5&#xff1a;\u8fd9\u4e00\u5217\u53ef\u80fd\u662f NULL \u5417&#xff1f;<\/p>\n<p>\u2461 as &#039;\u5e74\u85aa&#039; \u7528\u4e86\u4e2d\u6587\u522b\u540d&#xff0c;\u7136\u540e order by \u5e74\u85aa \u76f4\u63a5\u5f15\u7528\u4e86\u5b83\u3002<\/p>\n<p>&#x1f517; \u524d\u540e\u7167\u5e94&#xff1a;\u7b2c 06 \u7bc7 3.4.6 \u8bb2\u8fc7\u300cWHERE \u91cc\u4e0d\u80fd\u7528\u522b\u540d&#xff0c;\u4f46 ORDER BY \u80fd\u7528\u300d&#xff0c;\u539f\u56e0\u5c31\u662f\u6267\u884c\u987a\u5e8f\u3002\u8fd9\u91cc\u6b63\u662f\u90a3\u4e2a\u7ed3\u8bba\u7684\u5b9e\u6218&#xff1a;ORDER BY \u5728 SELECT \u4e4b\u540e\u6267\u884c&#xff0c;\u6240\u4ee5\u522b\u540d\u53ef\u89c1\u3002<\/p>\n<p>\u2462 \u7ed3\u679c\u957f\u8fd9\u6837&#xff1a;<\/p>\n<p>&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;-&#043;<br \/>\n| ename  | \u5e74\u85aa     |<br \/>\n&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;-&#043;<br \/>\n| KING   | 60000.00 |<br \/>\n| SCOTT  | 36000.00 |<br \/>\n| FORD   | 36000.00 |<br \/>\n| JONES  | 35700.00 |<br \/>\n| BLAKE  | 34200.00 |<br \/>\n| CLARK  | 29400.00 |<br \/>\n| ALLEN  | 19500.00 |<br \/>\n| MARTIN | 16400.00 |<br \/>\n| TURNER | 18000.00 |<br \/>\n| WARD   | 15500.00 |<br \/>\n| ADAMS  | 13200.00 |<br \/>\n| MILLER | 15600.00 |<br \/>\n| JAMES  | 11400.00 |<br \/>\n| SMITH  |  9600.00 |<br \/>\n&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;-&#043;<\/p>\n<p>\u6ce8\u610f comm \u4e3a NULL \u7684\u4eba&#xff08;\u5982 KING&#xff09;\u5e74\u85aa\u662f 5000 \u00d7 12 &#061; 60000&#xff0c;\u56e0\u4e3a ifnull(NULL, 0) &#061; 0\u3002 \u5982\u679c\u4e0d\u5199 ifnull&#xff0c;KING \u7684\u5e74\u85aa\u4f1a\u662f NULL \u5e76\u6392\u5728\u6700\u540e&#xff08;\u964d\u5e8f\u65f6 NULL \u6700\u5c0f&#xff09;\u3002<\/p>\n<h4 id=\"24-\u5de5\u8d44\u6700\u9ad8\u7684\u5458\u5de5\u7684\u540d\u5b57\u548c\u5c97\u4f4d\">2.4 \u5de5\u8d44\u6700\u9ad8\u7684\u5458\u5de5\u7684\u540d\u5b57\u548c\u5c97\u4f4d<\/h4>\n<p>select ename, job from emp where sal &#061; (select max(sal) from emp);<\/p>\n<p>\u8fd9\u662f\u672c\u7bc7\u7684\u7b2c\u4e00\u4e2a\u5b50\u67e5\u8be2**&#xff0c;\u975e\u5e38\u5178\u578b\u7684\u300c\u5355\u884c\u5b50\u67e5\u8be2\u300d\u3002**<\/p>\n<p>\u4e3a\u4ec0\u4e48\u4e0d\u80fd\u5199\u6210\u8fd9\u6837&#xff1f;<\/p>\n<p>&#8212; \u274c \u62a5\u9519 ERROR 1111: Invalid use of group function<br \/>\nselect ename, job from emp where sal &#061; max(sal);<\/p>\n<p>\u56e0\u4e3a WHERE \u662f\u300c\u9010\u884c\u8fc7\u6ee4\u300d&#xff0c;\u800c max(sal) \u662f\u300c\u6574\u8868\u805a\u5408\u300d\u2014\u2014\u4e24\u8005\u7684\u4f5c\u7528\u8303\u56f4\u5bf9\u4e0d\u4e0a\u3002<\/p>\n<p>&#x1f517; \u524d\u540e\u7167\u5e94&#xff1a;\u7b2c 06 \u7bc7 7.5 \u8282\u8bb2\u8fc7\u540c\u6837\u7684\u62a5\u9519\u2014\u2014\u805a\u5408\u51fd\u6570\u4e0d\u80fd\u51fa\u73b0\u5728 WHERE \u91cc\u3002\u5f53\u65f6\u7ed9\u7684\u89e3\u6cd5\u662f\u300c\u7528 HAVING\u300d&#xff0c;\u4f46**HAVING \u53ea\u80fd\u914d\u5408 GROUP BY \u7528**\u3002\u50cf\u8fd9\u91cc\u6839\u672c\u6ca1\u6709\u5206\u7ec4\u9700\u6c42&#xff0c;\u600e\u4e48\u529e&#xff1f;<\/p>\n<p>\u7b54\u6848\u5c31\u662f\u5b50\u67e5\u8be2&#xff1a;\u5148\u7528\u5b50\u67e5\u8be2\u8dd1\u51fa\u4e00\u4e2a\u300c\u6807\u91cf\u503c\u300d&#xff08;\u5355\u884c\u5355\u5217&#xff09;&#xff0c;\u518d\u62ff\u8fd9\u4e2a\u503c\u53bb\u548c\u6bcf\u4e00\u884c\u6bd4\u8f83\u3002<\/p>\n<p>\u8fd9\u5c31\u662f\u5b50\u67e5\u8be2\u5b58\u5728\u7684\u7b2c\u4e00\u610f\u4e49&#xff1a;\u628a\u300c\u805a\u5408\u7ed3\u679c\u300d\u53d8\u6210\u300c\u4e00\u4e2a\u53ef\u4ee5\u53c2\u4e0e\u6bd4\u8f83\u7684\u503c\u300d\u3002<\/p>\n<h4 id=\"25-\u5de5\u8d44\u9ad8\u4e8e\u5e73\u5747\u5de5\u8d44\u7684\u5458\u5de5\u4fe1\u606f\">2.5 \u5de5\u8d44\u9ad8\u4e8e\u5e73\u5747\u5de5\u8d44\u7684\u5458\u5de5\u4fe1\u606f<\/h4>\n<p>select ename, sal from emp where sal &gt; (select avg(sal) from emp);<\/p>\n<p>\u7ed3\u679c&#xff1a;<\/p>\n<p>&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;&#043;<br \/>\n| ename | sal     |<br \/>\n&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;&#043;<br \/>\n| JONES | 2975.00 |<br \/>\n| BLAKE | 2850.00 |<br \/>\n| CLARK | 2450.00 |<br \/>\n| SCOTT | 3000.00 |<br \/>\n| KING  | 5000.00 |<br \/>\n| FORD  | 3000.00 |<br \/>\n&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;&#043;<\/p>\n<p>\u5e73\u5747\u5de5\u8d44\u662f 2073.21&#8230;&#xff0c;\u4e0a\u9762 6 \u4e2a\u4eba\u9ad8\u4e8e\u5b83\u3002<\/p>\n<p>\u8fd9\u9053\u9898\u8981\u6ce8\u610f\u4e00\u4e2a\u300c\u76f4\u89c9\u9677\u9631\u300d&#xff1a;<\/p>\n<p>\u5f88\u591a\u4eba\u4f1a\u8bf4\u300c\u7528 join \u66f4\u9ad8\u6548\u300d\u3002\u4e0d\u3002 \u8fd9\u91cc\u4e24\u8868\u90fd\u662f emp&#xff0c;\u7528 join \u53cd\u800c\u7ed5\u3002\u5b50\u67e5\u8be2\u662f\u6700\u76f4\u63a5\u7684\u8868\u8fbe\u3002<\/p>\n<p>\u800c\u8fd9\u9053\u9898\u771f\u6b63\u7684\u9ad8\u9636\u7248\u672c\u5728\u540e\u9762&#xff08;5.4 \u8282&#xff09;&#xff1a;\u4e0d\u662f\u300c\u9ad8\u4e8e\u5168\u516c\u53f8\u5e73\u5747\u300d&#xff0c;\u800c\u662f**\u300c\u9ad8\u4e8e\u81ea\u5df1\u90e8\u95e8\u7684\u5e73\u5747\u300d**\u2014\u2014\u90a3\u65f6\u5019\u5b50\u67e5\u8be2\u5c31\u5fc5\u987b\u53d8\u6210\u300c\u76f8\u5173\u5b50\u67e5\u8be2\u300d\u6216\u300c\u6d3e\u751f\u8868\u300d\u4e86\u3002<\/p>\n<h4 id=\"26-\u6bcf\u4e2a\u90e8\u95e8\u7684\u5e73\u5747\u5de5\u8d44\u548c\u6700\u9ad8\u5de5\u8d44\">2.6 \u6bcf\u4e2a\u90e8\u95e8\u7684\u5e73\u5747\u5de5\u8d44\u548c\u6700\u9ad8\u5de5\u8d44<\/h4>\n<p>select deptno, format(avg(sal), 2), max(sal) from emp group by deptno;<\/p>\n<p>\u7ed3\u679c&#xff1a;<\/p>\n<p>&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#043;<br \/>\n| deptno | format(avg(sal),2) | max(sal)|<br \/>\n&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#043;<br \/>\n|     10 | 2916.67            | 5000.00 |<br \/>\n|     20 | 2175.00            | 3000.00 |<br \/>\n|     30 | 1566.67            | 2850.00 |<br \/>\n&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#043;<\/p>\n<p>format(avg(sal), 2) \u7684\u4f5c\u7528&#xff1a;\u628a\u5e73\u5747\u503c\u4fdd\u7559\u4e24\u4f4d\u5c0f\u6570\u3002<\/p>\n<p>&#x1f517; \u524d\u540e\u7167\u5e94&#xff1a;\u7b2c 07 \u7bc7 4.2 \u8282\u8bb2\u8fc7 round \/ format \/ truncate \u4e09\u5144\u5f1f\u3002\u8fd9\u91cc\u7528\u7684\u662f format\u2014\u2014\u5b83\u8fd4\u56de\u5b57\u7b26\u4e32&#xff0c;\u4f46\u7528\u4e8e\u300c\u5c55\u793a\u300d\u573a\u666f\u6b63\u597d\u5408\u9002\u3002\u5982\u679c\u8981\u505a\u540e\u7eed\u8ba1\u7b97&#xff0c;\u5e94\u8be5\u7528 round\u3002<\/p>\n<p>\u518d\u6ce8\u610f\u4e00\u904d&#xff1a;\u53ea\u6709 3 \u4e2a\u90e8\u95e8\u51fa\u73b0\u5728\u7ed3\u679c\u91cc\u300240 \u53f7\u90e8\u95e8\u53c8\u6d88\u5931\u4e86\u3002<\/p>\n<h4 id=\"27-\u5e73\u5747\u5de5\u8d44\u4f4e\u4e8e-2000-\u7684\u90e8\u95e8\u53f7\u548c\u5e73\u5747\u5de5\u8d44\">2.7 \u5e73\u5747\u5de5\u8d44\u4f4e\u4e8e 2000 \u7684\u90e8\u95e8\u53f7\u548c\u5e73\u5747\u5de5\u8d44<\/h4>\n<p>select deptno, avg(sal) as avg_sal from emp group by deptno having avg_sal &lt; 2000;<\/p>\n<p>\u7ed3\u679c&#xff1a;<\/p>\n<p>&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;-&#043;<br \/>\n| deptno | avg_sal  |<br \/>\n&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;-&#043;<br \/>\n|     30 | 1566.67  |<br \/>\n&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;-&#043;<\/p>\n<p>HAVING &#043; \u522b\u540d \u2014\u2014 \u8fd9\u53c8\u662f\u7b2c 06 \u7bc7 8.6 \u8282\u7684\u5b9e\u6218\u9a8c\u8bc1\u3002<\/p>\n<p>&#8212; \u274c \u8fd9\u6837\u5199\u4f1a\u62a5\u9519 1111<br \/>\nselect deptno, avg(sal) from emp where avg(sal) &lt; 2000 group by deptno;<\/p>\n<p>&#8212; \u2705 \u5fc5\u987b\u7528 having<br \/>\nselect deptno, avg(sal) from emp group by deptno having avg(sal) &lt; 2000;<\/p>\n<h4 id=\"28-\u6bcf\u79cd\u5c97\u4f4d\u7684\u96c7\u5458\u603b\u6570\u3001\u5e73\u5747\u5de5\u8d44\">2.8 \u6bcf\u79cd\u5c97\u4f4d\u7684\u96c7\u5458\u603b\u6570\u3001\u5e73\u5747\u5de5\u8d44<\/h4>\n<p>select job, count(*), format(avg(sal), 2) from emp group by job;<\/p>\n<p>\u7ed3\u679c&#xff1a;<\/p>\n<p>&#043;&#8212;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;&#043;<br \/>\n| job       | count(*) | format(avg(sal),2) |<br \/>\n&#043;&#8212;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;&#043;<br \/>\n| CLERK     |        4 | 1037.50            |<br \/>\n| SALESMAN  |        4 | 1400.00            |<br \/>\n| MANAGER   |        3 | 2758.33            |<br \/>\n| ANALYST   |        2 | 3000.00            |<br \/>\n| PRESIDENT |        1 | 5000.00            |<br \/>\n&#043;&#8212;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;&#043;<\/p>\n<p>\u8fd9\u91cc count(*) \u7528\u7684\u662f\u300c\u884c\u6570\u300d\u7edf\u8ba1\u3002 \u5982\u679c\u7528 count(comm)&#xff0c;SALESMAN \u53ea\u4f1a\u7b97\u51fa 4 \u4e2a\u91cc\u6709\u51e0\u4e2a\u6709\u5956\u91d1\u2014\u2014<\/p>\n<p>select job, count(*), count(comm), format(avg(sal), 2) from emp group by job;<br \/>\n&#8212; SALESMAN: count(*)&#061;4, count(comm)&#061;4&#xff08;4 \u4e2a\u90fd\u6709\u5956\u91d1&#xff0c;\u53ea\u662f TURNER \u662f 0.00 \u4e0d\u662f NULL&#xff09;<br \/>\n&#8212; CLERK:    count(*)&#061;4, count(comm)&#061;0&#xff08;\u5168\u90fd\u662f NULL&#xff09;<\/p>\n<p>&#x1f517; \u524d\u540e\u7167\u5e94&#xff1a;\u7b2c 06 \u7bc7 7.2 \u8282\u90a3\u5f20\u300cCOUNT(*) \/ COUNT(1) \/ COUNT(\u5217)\u300d\u7684\u5bf9\u7167\u8868&#xff0c;\u5728\u8fd9\u91cc\u5f97\u5230\u9a8c\u8bc1\u2014\u2014count(\u5217) \u4e0d\u7edf\u8ba1 NULL\u3002<\/p>\n<p>\u987a\u5e26\u6ce8\u610f&#xff1a;TURNER \u7684 comm \u662f 0.00 \u800c\u4e0d\u662f NULL\u3002\u300c0\u300d\u548c\u300cNULL\u300d\u662f\u4e24\u56de\u4e8b\u2014\u20140 \u662f\u6709\u5956\u91d1\u4f46\u6ca1\u53d1&#xff0c;NULL \u662f\u538b\u6839\u6ca1\u5956\u91d1\u6982\u5ff5\u3002\u8fd9\u4e2a\u533a\u522b\u5728\u505a\u300c\u5e73\u5747\u5956\u91d1\u300d\u65f6\u5f71\u54cd\u5de8\u5927\u3002<\/p>\n<hr \/>\n<h3 id=\"3-\u591a\u8868\u67e5\u8be2\uff1a\u4ece\u300c\u7b1b\u5361\u5c14\u79ef\u300d\u8bf4\u8d77\">3. \u591a\u8868\u67e5\u8be2&#xff1a;\u4ece\u300c\u7b1b\u5361\u5c14\u79ef\u300d\u8bf4\u8d77<\/h3>\n<h4 id=\"31-\u5148\u770b\u4e00\u4e2a\u300c\u6050\u6016\u300d\u7684\u73b0\u8c61\">3.1 \u5148\u770b\u4e00\u4e2a\u300c\u6050\u6016\u300d\u7684\u73b0\u8c61<\/h4>\n<p>select * from emp, dept;<\/p>\n<p>\u7ed3\u679c\u6709\u591a\u5c11\u884c&#xff1f;<\/p>\n<p>14 \u00d7 4 &#061; 56 \u884c<\/p>\n<p>\u662f\u7684&#xff0c;56 \u884c\u3002emp \u7684\u6bcf\u4e00\u884c&#xff0c;\u90fd\u548c dept \u7684\u6bcf\u4e00\u884c\u914d\u4e86\u4e00\u6b21\u5bf9\u3002<\/p>\n<p>\u8fd9\u5c31\u662f\u7b1b\u5361\u5c14\u79ef&#xff08;Cartesian Product&#xff09;&#xff1a;<\/p>\n<p>emp&#xff08;14 \u884c&#xff09;          dept&#xff08;4 \u884c&#xff09;              \u7ed3\u679c&#xff08;56 \u884c&#xff09;<br \/>\n  \u251c SMITH              \u251c 10 ACCOUNTING      \u2192  SMITH &#043; 10 ACCOUNTING<br \/>\n  \u2502                    \u251c 20 RESEARCH        \u2192  SMITH &#043; 20 RESEARCH<br \/>\n  \u2502                    \u251c 30 SALES           \u2192  SMITH &#043; 30 SALES<br \/>\n  \u2502                    \u2514 40 OPERATIONS      \u2192  SMITH &#043; 40 OPERATIONS<br \/>\n  \u251c ALLEN              \u251c 10 &#8230;             \u2192  ALLEN &#043; 10 ACCOUNTING<br \/>\n  &#8230;<\/p>\n<p>\u4ece\u6570\u5b66\u4e0a\u770b&#xff1a;A \u00d7 B &#061; \u6240\u6709\u53ef\u80fd\u7684\u300c\u6709\u5e8f\u5bf9\u300d\u3002<\/p>\n<p>\u4ece\u6570\u636e\u5e93\u89d2\u5ea6\u770b&#xff1a;\u8fd9\u662f\u4e00\u573a\u707e\u96be\u3002<\/p>\n<table>\n<tr>\u8868 A \u884c\u6570\u8868 B \u884c\u6570\u7b1b\u5361\u5c14\u79ef\u884c\u6570<\/tr>\n<tbody>\n<tr>\n<td>14<\/td>\n<td>4<\/td>\n<td>56<\/td>\n<\/tr>\n<tr>\n<td>1,000<\/td>\n<td>1,000<\/td>\n<td>1,000,000<\/td>\n<\/tr>\n<tr>\n<td>100,000<\/td>\n<td>1,000<\/td>\n<td>100,000,000&#xff08;\u4e00\u4ebf&#xff01;&#xff09;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u6240\u4ee5\u300c\u4e0d\u52a0\u8fde\u63a5\u6761\u4ef6\u7684\u591a\u8868\u67e5\u8be2\u300d\u5728\u751f\u4ea7\u73af\u5883\u57fa\u672c\u7b49\u4e8e\u300c\u81ea\u6740\u300d\u3002<\/p>\n<h4 id=\"32-\u52a0\u8fde\u63a5\u6761\u4ef6\uff1a\u628a\u300c\u5bf9\u7684\u914d\u5bf9\u300d\u7b5b\u51fa\u6765\">3.2 \u52a0\u8fde\u63a5\u6761\u4ef6&#xff1a;\u628a\u300c\u5bf9\u7684\u914d\u5bf9\u300d\u7b5b\u51fa\u6765<\/h4>\n<p>select EMP.ename, EMP.sal, DEPT.dname<br \/>\nfrom EMP, DEPT<br \/>\nwhere EMP.deptno &#061; DEPT.deptno;<\/p>\n<p>\u7ed3\u679c&#xff1a;<\/p>\n<p>&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8212;&#043;<br \/>\n| ename  | sal     | dname      |<br \/>\n&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8212;&#043;<br \/>\n| SMITH  |  800.00 | RESEARCH   |<br \/>\n| ALLEN  | 1600.00 | SALES      |<br \/>\n| WARD   | 1250.00 | SALES      |<br \/>\n| JONES  | 2975.00 | RESEARCH   |<br \/>\n| MARTIN | 1250.00 | SALES      |<br \/>\n| BLAKE  | 2850.00 | SALES      |<br \/>\n| CLARK  | 2450.00 | ACCOUNTING |<br \/>\n| SCOTT  | 3000.00 | RESEARCH   |<br \/>\n| KING   | 5000.00 | ACCOUNTING |<br \/>\n| TURNER | 1500.00 | SALES      |<br \/>\n| ADAMS  | 1100.00 | RESEARCH   |<br \/>\n| JAMES  |  950.00 | SALES      |<br \/>\n| FORD   | 3000.00 | RESEARCH   |<br \/>\n| MILLER | 1300.00 | ACCOUNTING |<br \/>\n&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8212;&#043;<\/p>\n<p>14 \u884c\u3002 \u56e0\u4e3a where EMP.deptno &#061; DEPT.deptno \u4ece 56 \u884c\u91cc\u7b5b\u6389\u4e86\u300c\u90e8\u95e8\u53f7\u5bf9\u4e0d\u4e0a\u300d\u7684 42 \u884c\u3002<\/p>\n<p>\u8fd9\u6761 SQL \u7684\u6267\u884c\u8fc7\u7a0b&#xff08;\u975e\u5e38\u91cd\u8981&#xff09;&#xff1a;<\/p>\n<p>\u2460 \u751f\u6210\u7b1b\u5361\u5c14\u79ef&#xff1a;emp \u00d7 dept  \u2192 56 \u884c<br \/>\n\u2461 \u7528 where \u8fc7\u6ee4&#xff1a;\u53ea\u4fdd\u7559 deptno \u76f8\u7b49\u7684 \u2192 14 \u884c<br \/>\n\u2462 \u6295\u5f71&#xff08;select&#xff09;&#xff1a;\u53ea\u53d6 ename, sal, dname \u4e09\u5217<\/p>\n<p>\u8bfe\u4ef6\u90a3\u53e5\u6ce8\u91ca\u8bf4\u5f97\u975e\u5e38\u7cbe\u51c6&#xff1a;<\/p>\n<p>\u5176\u5b9e\u6211\u4eec\u53ea\u8981 emp \u8868\u4e2d\u7684 deptno &#061; dept \u8868\u4e2d\u7684 deptno \u5b57\u6bb5\u7684\u8bb0\u5f55<\/p>\n<p>\u8fd9\u53e5\u8bdd\u7ffb\u8bd1\u6210 SQL \u5c31\u662f where EMP.deptno &#061; DEPT.deptno\u3002\u800c\u300c\u7b1b\u5361\u5c14\u79ef &#043; \u7b5b\u9009\u300d\u8fd9\u4e2a\u6a21\u578b&#xff0c;\u6b63\u662f\u7406\u89e3\u6240\u6709 JOIN \u7684\u94a5\u5319\u3002<\/p>\n<p>&#x1f4a1; \u4e00\u4e2a\u91cd\u8981\u7684\u8ba4\u77e5\u5347\u7ea7&#xff1a; \u5728\u5173\u7cfb\u4ee3\u6570\u91cc&#xff0c;from a, b where \u6761\u4ef6 \u548c a inner join b on \u6761\u4ef6 \u662f\u5b8c\u5168\u7b49\u4ef7\u7684\u4e24\u4ef6\u4e8b\u3002 \u524d\u8005\u53eb\u300c\u9690\u5f0f\u8fde\u63a5\u300d&#xff08;implicit join&#xff09;&#xff0c;\u540e\u8005\u53eb\u300c\u663e\u5f0f\u8fde\u63a5\u300d&#xff08;explicit join&#xff09;\u3002 MySQL \u4f18\u5316\u5668\u4f1a\u628a\u5b83\u4eec\u4f18\u5316\u6210\u540c\u4e00\u4e2a\u6267\u884c\u8ba1\u5212\u3002\u6240\u4ee5\u6027\u80fd\u4e0a\u6ca1\u6709\u533a\u522b&#xff0c;\u533a\u522b\u53ea\u5728\u53ef\u8bfb\u6027\u3002<\/p>\n<p>\u4e0b\u4e00\u7bc7&#xff08;\u5185\u5916\u8fde\u63a5&#xff09;\u4f1a\u8be6\u7ec6\u8bb2\u8fd9\u4e2a\u7b49\u4ef7\u5173\u7cfb&#xff0c;\u4ee5\u53ca\u5b83\u5728\u5916\u8fde\u63a5\u4e0b\u4e0d\u6210\u7acb\u7684\u90a3\u4e2a\u5927\u5751\u3002<\/p>\n<h4 id=\"33-\u6848\u4f8b\u4e8c\uff1a\u663e\u793a-10-\u53f7\u90e8\u95e8\u7684\u90e8\u95e8\u540d\u3001\u5458\u5de5\u540d\u548c\u5de5\u8d44\">3.3 \u6848\u4f8b\u4e8c&#xff1a;\u663e\u793a 10 \u53f7\u90e8\u95e8\u7684\u90e8\u95e8\u540d\u3001\u5458\u5de5\u540d\u548c\u5de5\u8d44<\/h4>\n<p>select ename, sal, dname<br \/>\nfrom EMP, DEPT<br \/>\nwhere EMP.deptno &#061; DEPT.deptno and DEPT.deptno &#061; 10;<\/p>\n<p>\u7ed3\u679c&#xff1a;<\/p>\n<p>&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8212;&#043;<br \/>\n| ename  | sal     | dname      |<br \/>\n&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8212;&#043;<br \/>\n| CLARK  | 2450.00 | ACCOUNTING |<br \/>\n| KING   | 5000.00 | ACCOUNTING |<br \/>\n| MILLER | 1300.00 | ACCOUNTING |<br \/>\n&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8212;&#043;<\/p>\n<p>10 \u53f7\u90e8\u95e8&#xff08;ACCOUNTING&#xff09;\u6709 3 \u4e2a\u4eba&#xff0c;\u6b63\u786e\u3002<\/p>\n<p>\u8fd9\u91cc\u6709\u4e2a\u5c0f\u7ec6\u8282&#xff1a;DEPT.deptno &#061; 10 \u548c EMP.deptno &#061; 10 \u6548\u679c\u4e00\u6837\u5417&#xff1f;<\/p>\n<p>\u4e00\u6837&#xff0c;\u56e0\u4e3a\u8fde\u63a5\u6761\u4ef6\u5df2\u7ecf\u4fdd\u8bc1\u4e86\u4e24\u5f20\u8868\u7684 deptno \u76f8\u7b49\u3002\u4f46\u8bed\u4e49\u4e0a&#xff0c;\u7528 DEPT.deptno &#061; 10 \u66f4\u51c6\u786e\u2014\u2014\u4f60\u60f3\u7b5b\u7684\u662f\u300c\u90e8\u95e8\u300d&#xff0c;\u800c\u4e0d\u662f\u300c\u5458\u5de5\u7684\u90e8\u95e8\u53f7\u5b57\u6bb5\u300d\u3002<\/p>\n<h4 id=\"34-\u6848\u4f8b\u4e09\uff1a\u663e\u793a\u5404\u4e2a\u5458\u5de5\u7684\u59d3\u540d\u3001\u5de5\u8d44\u53ca\u5de5\u8d44\u7ea7\u522b\">3.4 \u6848\u4f8b\u4e09&#xff1a;\u663e\u793a\u5404\u4e2a\u5458\u5de5\u7684\u59d3\u540d\u3001\u5de5\u8d44\u53ca\u5de5\u8d44\u7ea7\u522b<\/h4>\n<p>select ename, sal, grade from EMP, SALGRADE where EMP.sal between losal and hisal;<\/p>\n<p>\u7ed3\u679c&#xff1a;<\/p>\n<p>&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;-&#043;<br \/>\n| ename  | sal     | grade |<br \/>\n&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;-&#043;<br \/>\n| SMITH  |  800.00 |     1 |<br \/>\n| ALLEN  | 1600.00 |     3 |<br \/>\n| WARD   | 1250.00 |     2 |<br \/>\n| JONES  | 2975.00 |     4 |<br \/>\n| MARTIN | 1250.00 |     2 |<br \/>\n| BLAKE  | 2850.00 |     4 |<br \/>\n| CLARK  | 2450.00 |     4 |<br \/>\n| SCOTT  | 3000.00 |     4 |<br \/>\n| KING   | 5000.00 |     5 |<br \/>\n| TURNER | 1500.00 |     3 |<br \/>\n| ADAMS  | 1100.00 |     1 |<br \/>\n| JAMES  |  950.00 |     1 |<br \/>\n| FORD   | 3000.00 |     4 |<br \/>\n| MILLER | 1300.00 |     2 |<br \/>\n&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;-&#043;<\/p>\n<p>\u8fd9\u9053\u9898\u7684\u7cbe\u5999\u4e4b\u5904&#xff1a;\u8fde\u63a5\u6761\u4ef6\u4e0d\u662f &#061;&#xff0c;\u800c\u662f between &#8230; and &#8230;\u3002<\/p>\n<p>where EMP.sal between losal and hisal<\/p>\n<p>\u300c\u8fde\u63a5\u6761\u4ef6\u300d\u4e0d\u4e00\u5b9a\u975e\u8981\u7528\u7b49\u53f7&#xff01; \u53ea\u8981\u662f\u4e00\u4e2a\u300c\u80fd\u5224\u65ad\u4e24\u884c\u662f\u5426\u5339\u914d\u300d\u7684\u5e03\u5c14\u8868\u8fbe\u5f0f&#xff0c;\u90fd\u53ef\u4ee5\u5f53\u8fde\u63a5\u6761\u4ef6\u3002\u8fd9\u5728\u300c\u533a\u95f4\u5339\u914d\u300d\u300c\u65f6\u95f4\u91cd\u53e0\u300d\u8fd9\u7c7b\u573a\u666f\u91cc\u6781\u5176\u5e38\u89c1&#xff1a;<\/p>\n<p>&#8212; \u5224\u65ad\u4e24\u4e2a\u65f6\u95f4\u6bb5\u662f\u5426\u91cd\u53e0&#xff08;\u4f1a\u8bae\u6392\u671f\u3001\u5e93\u5b58\u5360\u7528&#xff09;<br \/>\nwhere a.start_time &lt; b.end_time and a.end_time &gt; b.start_time<\/p>\n<p>\u8fd9\u9053\u9898\u8fd8\u6709\u4e00\u4e2a\u9677\u9631&#xff1a; \u5982\u679c sal \u6070\u597d\u843d\u5728\u4e24\u4e2a\u533a\u95f4\u7684\u8fb9\u754c\u4e0a&#xff08;\u6bd4\u5982 1200 \u540c\u65f6\u5728 grade 1 \u7684 [700,1200] \u548c grade 2 \u7684 [1201,1400] \u4e4b\u5916&#xff09;&#xff0c;\u4e0d\u4f1a\u6709\u91cd\u590d\u3002\u4f46\u5982\u679c\u533a\u95f4\u6709\u91cd\u53e0&#xff0c;\u4e00\u4e2a\u5458\u5de5\u4f1a\u5339\u914d\u51fa\u591a\u4e2a\u7b49\u7ea7&#xff0c;\u7ed3\u679c\u884c\u6570\u4f1a\u591a\u4e8e 14 \u884c\u3002<\/p>\n<p>\u8bbe\u8ba1\u533a\u95f4\u8868\u65f6&#xff0c;\u533a\u95f4\u5fc5\u987b\u300c\u9996\u5c3e\u76f8\u63a5\u3001\u4e92\u4e0d\u91cd\u53e0\u300d&#xff0c;\u5426\u5219 join \u4f1a\u51fa\u73b0\u91cd\u590d\u884c\u3002 \u8fd9\u662f\u6570\u636e\u8d28\u91cf\u95ee\u9898&#xff0c;SQL \u6551\u4e0d\u4e86\u4f60\u3002<\/p>\n<hr \/>\n<h3 id=\"4-\u81ea\u8fde\u63a5\uff1a\u628a\u4e00\u5f20\u8868\u5f53\u6210\u4e24\u5f20\u7528\">4. \u81ea\u8fde\u63a5&#xff1a;\u628a\u4e00\u5f20\u8868\u5f53\u6210\u4e24\u5f20\u7528<\/h3>\n<h4 id=\"41-\u9700\u6c42\">4.1 \u9700\u6c42<\/h4>\n<p>\u663e\u793a\u5458\u5de5 FORD \u7684\u4e0a\u7ea7\u9886\u5bfc\u7684\u7f16\u53f7\u548c\u59d3\u540d\u3002&#xff08;mgr \u662f\u5458\u5de5\u9886\u5bfc\u7684\u7f16\u53f7 \u2192 \u5bf9\u5e94 empno&#xff09;<\/p>\n<p>\u95ee\u9898\u6765\u4e86&#xff1a;FORD \u7684\u4e0a\u7ea7\u4fe1\u606f\u4e5f\u5728\u8fd9\u5f20 emp \u8868\u91cc**&#xff0c;\u53ea\u662f\u6362\u4e86\u4e00\u884c\u3002**<\/p>\n<p>empno | ename | &#8230; | mgr<br \/>\n&#8212;&#8212;&#043;&#8212;&#8212;-&#043;&#8212;&#8211;&#043;&#8212;&#8212;<br \/>\n 7902 | FORD  | &#8230; | 7566   \u2190 FORD \u7684\u7ba1\u7406\u8005\u662f 7566<br \/>\n 7566 | JONES | &#8230; | 7839   \u2190 7566 \u5c31\u662f JONES<\/p>\n<p>\u8981\u62ff\u5230\u300cJONES\u300d\u8fd9\u4e2a\u540d\u5b57&#xff0c;\u5f97\u7528 emp \u8868\u53bb\u67e5 emp \u8868\u3002<\/p>\n<h4 id=\"42-\u89e3\u6cd5\u4e00\uff1a\u5b50\u67e5\u8be2\uff08\u4e24\u6b65\u8d70\uff09\">4.2 \u89e3\u6cd5\u4e00&#xff1a;\u5b50\u67e5\u8be2&#xff08;\u4e24\u6b65\u8d70&#xff09;<\/h4>\n<p>select empno, ename from emp where emp.empno &#061; (select mgr from emp where ename &#061; &#039;FORD&#039;);<\/p>\n<p>\u7ed3\u679c&#xff1a;<\/p>\n<p>&#043;&#8212;&#8212;-&#043;&#8212;&#8212;-&#043;<br \/>\n| empno | ename |<br \/>\n&#043;&#8212;&#8212;-&#043;&#8212;&#8212;-&#043;<br \/>\n|  7566 | JONES |<br \/>\n&#043;&#8212;&#8212;-&#043;&#8212;&#8212;-&#043;<\/p>\n<p>\u6267\u884c\u8fc7\u7a0b&#xff1a;<\/p>\n<p>\u2460 \u5185\u5c42&#xff1a;(select mgr from emp where ename&#061;&#039;FORD&#039;)  \u2192 7566<br \/>\n\u2461 \u5916\u5c42&#xff1a;select &#8230; from emp where empno &#061; 7566    \u2192 JONES<\/p>\n<p>\u5148\u7528\u5b50\u67e5\u8be2\u300c\u67e5\u51fa\u7ebf\u7d22\u300d&#xff0c;\u518d\u7528\u8fd9\u4e2a\u7ebf\u7d22\u300c\u53cd\u67e5\u300d\u3002 \u8fd9\u662f\u5b50\u67e5\u8be2\u6700\u6734\u7d20\u4e5f\u6700\u5e38\u7528\u7684\u7528\u6cd5\u3002<\/p>\n<h4 id=\"43-\u89e3\u6cd5\u4e8c\uff1a\u81ea\u8fde\u63a5\uff08\u4e00\u6b65\u5230\u4f4d\uff09\">4.3 \u89e3\u6cd5\u4e8c&#xff1a;\u81ea\u8fde\u63a5&#xff08;\u4e00\u6b65\u5230\u4f4d&#xff09;<\/h4>\n<p>&#8212; \u4f7f\u7528\u5230\u8868\u7684\u522b\u540d<br \/>\n&#8212; from emp leader, emp worker&#xff0c;\u7ed9\u81ea\u5df1\u7684\u8868\u8d77\u522b\u540d&#xff0c;\u56e0\u4e3a\u8981\u5148\u505a\u7b1b\u5361\u5c14\u79ef&#xff0c;\u6240\u4ee5\u522b\u540d\u53ef\u4ee5\u5148\u8bc6\u522b<br \/>\nselect leader.empno, leader.ename<br \/>\nfrom emp leader, emp worker<br \/>\nwhere leader.empno &#061; worker.mgr and worker.ename &#061; &#039;FORD&#039;;<\/p>\n<p>\u7ed3\u679c\u5b8c\u5168\u4e00\u6837&#xff1a;<\/p>\n<p>&#043;&#8212;&#8212;-&#043;&#8212;&#8212;-&#043;<br \/>\n| empno | ename |<br \/>\n&#043;&#8212;&#8212;-&#043;&#8212;&#8212;-&#043;<br \/>\n|  7566 | JONES |<br \/>\n&#043;&#8212;&#8212;-&#043;&#8212;&#8212;-&#043;<\/p>\n<p>\u8bfe\u4ef6\u90a3\u53e5\u6ce8\u91ca\u6781\u5176\u91cd\u8981&#xff0c;\u6211\u8981\u91cd\u70b9\u8bb2&#xff1a;<\/p>\n<p>from emp leader, emp worker&#xff0c;\u7ed9\u81ea\u5df1\u7684\u8868\u8d77\u522b\u540d&#xff0c;\u56e0\u4e3a\u8981\u5148\u505a\u7b1b\u5361\u5c14\u79ef&#xff0c;\u6240\u4ee5\u522b\u540d\u53ef\u4ee5\u5148\u8bc6\u522b<\/p>\n<p>\u8fd9\u53e5\u8bdd\u63ed\u793a\u4e86\u81ea\u8fde\u63a5\u7684\u672c\u8d28&#xff1a;<\/p>\n<p>\u2460 \u628a emp \u590d\u5236\u6210\u300c\u4e24\u4efd\u89c6\u89d2\u300d&#xff1a;<br \/>\n   leader&#xff08;\u6211\u662f\u9886\u5bfc&#xff09;  worker&#xff08;\u6211\u662f\u4e0b\u5c5e&#xff09;<br \/>\n\u2461 \u505a\u7b1b\u5361\u5c14\u79ef&#xff1a;14 \u00d7 14 &#061; 196 \u884c<br \/>\n\u2462 \u7528\u6761\u4ef6\u7b5b\u9009&#xff1a;leader.empno &#061; worker.mgr  \u2192 \u53ea\u5269\u300c\u771f\u6b63\u7684\u4e0a\u4e0b\u7ea7\u5173\u7cfb\u300d<br \/>\n\u2463 \u518d\u52a0 worker.ename&#061;&#039;FORD&#039;                \u2192 \u53ea\u5269 FORD \u90a3\u4e00\u7ec4<\/p>\n<p>\u5173\u952e\u7406\u89e3&#xff1a;leader \u548c worker \u662f\u540c\u4e00\u5f20\u8868&#xff0c;\u53ea\u662f\u300c\u626e\u6f14\u4e0d\u540c\u89d2\u8272\u300d\u3002<\/p>\n<table>\n<tr>\u522b\u540d\u626e\u6f14\u4ec0\u4e48\u4ece\u54ea\u91cc\u53d6\u6570<\/tr>\n<tbody>\n<tr>\n<td>leader<\/td>\n<td>\u88ab\u67e5\u7684\u90a3\u4e2a\u4eba&#xff08;\u9886\u5bfc&#xff09;<\/td>\n<td>\u4ece leader.* \u53d6 empno\u3001ename<\/td>\n<\/tr>\n<tr>\n<td>worker<\/td>\n<td>\u63d0\u4f9b\u7ebf\u7d22\u7684\u4eba&#xff08;\u4e0b\u5c5e&#xff09;<\/td>\n<td>\u4ece worker.* \u53d6 mgr\u3001ename<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u6240\u4ee5 where leader.empno &#061; worker.mgr \u7684\u542b\u4e49\u662f&#xff1a;<\/p>\n<p>\u300c\u9886\u5bfc\u300d\u7684\u5de5\u53f7 &#061; \u300c\u4e0b\u5c5e\u300d\u8bb0\u5f55\u91cc\u90a3\u4e2a\u300c\u6211\u7684\u9886\u5bfc\u662f\u8c01\u300d\u5b57\u6bb5<\/p>\n<h4 id=\"44-\u4e3a\u4ec0\u4e48\u81ea\u8fde\u63a5\u5fc5\u987b\u8d77\u522b\u540d\uff1f\">4.4 \u4e3a\u4ec0\u4e48\u81ea\u8fde\u63a5\u5fc5\u987b\u8d77\u522b\u540d&#xff1f;<\/h4>\n<p>&#8212; \u274c \u62a5\u9519<br \/>\nselect empno, ename from emp, emp where emp.empno &#061; emp.mgr;<br \/>\n&#8212; ERROR 1066 (42000): Not unique table\/alias: &#039;emp&#039;<\/p>\n<p>\u56e0\u4e3a MySQL \u9700\u8981\u4e00\u4e2a\u300c\u552f\u4e00\u540d\u5b57\u300d\u6765\u533a\u5206\u8fd9\u4e24\u4efd emp\u3002 \u540c\u540d\u7684\u4e24\u5f20\u8868&#xff0c;emp.empno \u5230\u5e95\u6307\u54ea\u4e00\u4efd&#xff1f;\u5b83\u65e0\u6cd5\u5224\u65ad\u3002<\/p>\n<p>\u8d77\u522b\u540d\u540e&#xff1a;<\/p>\n<p>&#8212; \u2705<br \/>\nselect leader.empno, leader.ename from emp leader, emp worker<br \/>\nwhere leader.empno &#061; worker.mgr and worker.ename &#061; &#039;FORD&#039;;<\/p>\n<p>leader \u548c worker \u662f\u4e24\u4e2a\u552f\u4e00\u7684\u522b\u540d&#xff0c;\u65e0\u6b67\u4e49\u3002<\/p>\n<p>&#x1f4a1; \u8fd9\u4e5f\u662f\u300c\u7ed9\u8868\u8d77\u522b\u540d\u300d\u6700\u91cd\u8981\u7684\u7406\u7531\u4e4b\u4e00&#xff08;\u53e6\u4e00\u4e2a\u7406\u7531\u662f\u300c\u8868\u540d\u592a\u957f&#xff0c;\u5199\u8d77\u6765\u75db\u82e6\u300d&#xff09;\u3002 \u5728\u591a\u8868\u67e5\u8be2\u91cc&#xff0c;\u53ea\u8981\u51fa\u73b0\u540c\u540d\u5217&#xff08;empno\u3001deptno\u3001ename \u4e24\u5f20\u8868\u90fd\u6709&#xff09;&#xff0c;\u5c31\u5fc5\u987b\u7528 \u8868\u540d.\u5217\u540d \u6d88\u6b67&#xff0c;\u5426\u5219\u62a5 ERROR 1052: Column &#039;xxx&#039; in field list is ambiguous\u3002<\/p>\n<h4 id=\"45-\u4ec0\u4e48\u65f6\u5019\u300c\u5fc5\u987b\u300d\u7528\u81ea\u8fde\u63a5\uff1f\">4.5 \u4ec0\u4e48\u65f6\u5019\u300c\u5fc5\u987b\u300d\u7528\u81ea\u8fde\u63a5&#xff1f;<\/h4>\n<p>\u81ea\u8fde\u63a5\u4e0d\u662f\u70ab\u6280&#xff0c;\u6709\u4e9b\u9700\u6c42\u53ea\u80fd\u7528\u81ea\u8fde\u63a5&#xff08;\u6216\u76f8\u5173\u5b50\u67e5\u8be2&#xff09;\u89e3\u51b3\u3002 \u5178\u578b\u7684\u4e09\u7c7b&#xff1a;<\/p>\n<p>\u2460 \u540c\u4e00\u5f20\u8868\u5185\u7684\u300c\u5c42\u7ea7\u5173\u7cfb\u300d<\/p>\n<p>&#8212; \u5458\u5de5 \u2192 \u9886\u5bfc \u2192 \u9886\u5bfc\u7684\u9886\u5bfc&#xff08;\u591a\u7ea7&#xff09;<br \/>\nselect e1.ename as \u5458\u5de5, e2.ename as \u76f4\u5c5e\u9886\u5bfc, e3.ename as \u4e0a\u7ea7\u9886\u5bfc<br \/>\nfrom emp e1<br \/>\nleft join emp e2 on e1.mgr &#061; e2.empno<br \/>\nleft join emp e3 on e2.mgr &#061; e3.empno;<\/p>\n<p>\u2461 \u540c\u4e00\u5f20\u8868\u5185\u7684\u300c\u4e24\u884c\u6bd4\u8f83\u300d<\/p>\n<p>&#8212; \u627e\u51fa\u300c\u6bd4\u540c\u90e8\u95e8\u5176\u4ed6\u4eba\u5de5\u8d44\u9ad8\u300d\u7684\u5458\u5de5<br \/>\nselect e1.ename, e1.sal<br \/>\nfrom emp e1, emp e2<br \/>\nwhere e1.deptno &#061; e2.deptno and e1.sal &gt; e2.sal;<br \/>\n&#8212; \u6ce8\u610f&#xff1a;\u8fd9\u4e2a\u5199\u6cd5\u4f1a\u8fd4\u56de\u91cd\u590d\u884c&#xff0c;\u66f4\u597d\u7684\u5199\u6cd5\u89c1 5.4 \u8282<\/p>\n<p>\u2462 \u6811\u5f62\u7ed3\u6784&#xff08;\u5206\u7c7b\u3001\u8bc4\u8bba\u3001\u90e8\u95e8&#xff09;<\/p>\n<p>&#8212; \u5546\u54c1\u5206\u7c7b&#xff1a;\u4e00\u7ea7\u5206\u7c7b \u2192 \u4e8c\u7ea7\u5206\u7c7b<br \/>\nselect c1.name as \u4e00\u7ea7, c2.name as \u4e8c\u7ea7<br \/>\nfrom category c1, category c2<br \/>\nwhere c1.id &#061; c2.parent_id;<\/p>\n<p>\u2463 \u627e\u51fa\u300c\u8fde\u7eed N \u5929\u90fd\u6709\u8bb0\u5f55\u300d\u7684\u6570\u636e&#xff08;\u767b\u5f55\u3001\u6253\u5361&#xff09;<\/p>\n<p>&#8212; \u627e\u8fde\u7eed\u4e24\u5929\u90fd\u767b\u5f55\u7684\u7528\u6237<br \/>\nselect distinct a.user_id<br \/>\nfrom login a, login b<br \/>\nwhere a.user_id &#061; b.user_id<br \/>\n  and b.login_date &#061; date_add(a.login_date, interval 1 day);<\/p>\n<p>\u81ea\u8fde\u63a5\u7684\u672c\u8d28&#xff1a;\u628a\u300c\u4e00\u884c\u5185\u7684\u591a\u4e2a\u89d2\u8272\u300d\u644a\u5e73\u6210\u300c\u591a\u884c\u300d\u3002 \u4e00\u65e6\u4f60\u60f3\u901a\u300c\u4e00\u5f20\u8868\u53ef\u4ee5\u540c\u65f6\u626e\u6f14 A \u548c B\u300d&#xff0c;\u5f88\u591a\u96be\u9898\u5c31\u8fce\u5203\u800c\u89e3\u4e86\u3002<\/p>\n<h4 id=\"46-\u8865\u5168\uff1a\u81ea\u8fde\u63a5\u7684\u6027\u80fd\u9677\u9631\">4.6 \u8865\u5168&#xff1a;\u81ea\u8fde\u63a5\u7684\u6027\u80fd\u9677\u9631<\/h4>\n<p>\u81ea\u8fde\u63a5\u7684\u7b1b\u5361\u5c14\u79ef\u662f\u300c\u884c\u6570\u7684\u5e73\u65b9\u300d&#xff1a;<\/p>\n<table>\n<tr>\u8868\u884c\u6570\u81ea\u8fde\u63a5\u7b1b\u5361\u5c14\u79ef<\/tr>\n<tbody>\n<tr>\n<td>14<\/td>\n<td>196<\/td>\n<\/tr>\n<tr>\n<td>10,000<\/td>\n<td>100,000,000&#xff08;\u4e00\u4ebf&#xff09;<\/td>\n<\/tr>\n<tr>\n<td>1,000,000<\/td>\n<td>\u4e00\u4e07\u4ebf<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u6240\u4ee5&#xff1a;<\/p>\n<li>\u8fde\u63a5\u6761\u4ef6\u4e0a\u7684\u5217\u5fc5\u987b\u6709\u7d22\u5f15&#xff08;\u6bd4\u5982 emp.mgr\u3001category.parent_id&#xff09;<\/li>\n<li>\u5927\u8868\u81ea\u8fde\u63a5\u8981\u6781\u5176\u8c28\u614e&#xff0c;\u5148\u60f3\u6e05\u695a\u80fd\u4e0d\u80fd\u7528\u7a97\u53e3\u51fd\u6570&#xff08;8.0&#043;&#xff09;\u66ff\u4ee3<\/li>\n<p>&#8212; \u7528\u7a97\u53e3\u51fd\u6570\u66ff\u4ee3\u81ea\u8fde\u63a5&#xff08;8.0&#043;&#xff09;&#xff0c;\u6548\u7387\u9ad8\u5f97\u591a<br \/>\nselect ename, sal, deptno,<br \/>\n       rank() over (partition by deptno order by sal desc) as rk<br \/>\nfrom emp;<\/p>\n<hr \/>\n<h3 id=\"5-\u5b50\u67e5\u8be2\uff1asql-\u91cc\u7684\u300c\u5957\u5a03\u300d\">5. \u5b50\u67e5\u8be2&#xff1a;SQL \u91cc\u7684\u300c\u5957\u5a03\u300d<\/h3>\n<h4 id=\"51-\u5b50\u67e5\u8be2\u7684\u5206\u7c7b\u5730\u56fe\">5.1 \u5b50\u67e5\u8be2\u7684\u5206\u7c7b\u5730\u56fe<\/h4>\n<p>\u5148\u7ed9\u4f60\u4e00\u5f20\u5730\u56fe&#xff0c;\u540e\u9762\u5c31\u597d\u5b9a\u4f4d\u4e86&#xff1a;<\/p>\n<p>\u5b50\u67e5\u8be2<br \/>\n\u251c\u2500\u2500 \u6309\u8fd4\u56de\u7ed3\u679c\u5206<br \/>\n\u2502   \u251c\u2500\u2500 \u6807\u91cf\u5b50\u67e5\u8be2&#xff08;1 \u884c 1 \u5217&#xff09;      \u2192 \u53ef\u4ee5\u5f53\u300c\u4e00\u4e2a\u503c\u300d\u7528<br \/>\n\u2502   \u251c\u2500\u2500 \u5217\u5b50\u67e5\u8be2&#xff08;\u591a\u884c 1 \u5217&#xff09;        \u2192 \u914d in \/ any \/ all<br \/>\n\u2502   \u2514\u2500\u2500 \u884c\u5b50\u67e5\u8be2&#xff08;1 \u884c \u591a\u5217&#xff09;        \u2192 \u914d (a, b) &#061; (&#8230;)<br \/>\n\u2502<br \/>\n\u2514\u2500\u2500 \u6309\u51fa\u73b0\u4f4d\u7f6e\u5206<br \/>\n    \u251c\u2500\u2500 WHERE \u5b50\u53e5\u91cc<br \/>\n    \u251c\u2500\u2500 FROM \u5b50\u53e5\u91cc&#xff08;\u6d3e\u751f\u8868 \/ \u4e34\u65f6\u8868&#xff09;<br \/>\n    \u2514\u2500\u2500 SELECT \u5217\u8868\u91cc&#xff08;\u6807\u91cf\u5b50\u67e5\u8be2&#xff09;<\/p>\n<p>\u5b50\u67e5\u8be2\u662f\u6307\u5d4c\u5165\u5728\u5176\u4ed6 sql \u8bed\u53e5\u4e2d\u7684 select \u8bed\u53e5&#xff0c;\u4e5f\u53eb\u5d4c\u5957\u67e5\u8be2\u3002<\/p>\n<h4 id=\"52-\u5355\u884c\u5b50\u67e5\u8be2\">5.2 \u5355\u884c\u5b50\u67e5\u8be2<\/h4>\n<p>\u8fd4\u56de\u4e00\u884c\u8bb0\u5f55\u7684\u5b50\u67e5\u8be2\u3002<\/p>\n<p>&#8212; \u663e\u793a SMITH \u540c\u4e00\u90e8\u95e8\u7684\u5458\u5de5<br \/>\nselect * from EMP where deptno &#061; (select deptno from EMP where ename &#061; &#039;smith&#039;);<\/p>\n<p>\u7ed3\u679c&#xff1a;<\/p>\n<p>&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#043;&#8212;&#8212;&#8211;&#043;<br \/>\n| empno | ename  | job     | mgr  | hiredate   | sal     | comm | deptno |<br \/>\n&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#043;&#8212;&#8212;&#8211;&#043;<br \/>\n|  7369 | SMITH  | CLERK   | 7902 | 1980-12-17 |  800.00 | NULL |     20 |<br \/>\n|  7566 | JONES  | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |<br \/>\n|  7788 | SCOTT  | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |<br \/>\n|  7876 | ADAMS  | CLERK   | 7788 | 1987-05-23 | 1100.00 | NULL |     20 |<br \/>\n|  7902 | FORD   | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |<br \/>\n&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#043;&#8212;&#8212;&#8211;&#043;<\/p>\n<p>\u26a0\ufe0f \u8fd9\u91cc\u6709\u4e2a\u300c\u9690\u6027\u5047\u8bbe\u300d&#xff1a; ename &#061; &#039;smith&#039; \u53ea\u80fd\u8fd4\u56de\u4e00\u884c\u3002<\/p>\n<p>\u5982\u679c SMITH \u662f\u540c\u540d&#xff0c;\u5b50\u67e5\u8be2\u8fd4\u56de\u591a\u884c&#xff0c;\u5c31\u4f1a\u62a5\u9519&#xff1a;<\/p>\n<p>ERROR 1242 (21000): Subquery returns more than 1 row<\/p>\n<p>\u8fd9\u662f\u5355\u884c\u5b50\u67e5\u8be2\u6700\u7ecf\u5178\u7684\u62a5\u9519\u3002 \u89e3\u51b3\u65b9\u6848\u89c1 5.3 \u8282&#xff08;\u6539\u7528 in&#xff09;\u3002<\/p>\n<p>\u8865\u5168&#xff1a;\u5224\u65ad\u5b50\u67e5\u8be2\u7ed3\u679c\u4e3a\u7a7a\u65f6<\/p>\n<p>&#8212; \u5982\u679c\u5b50\u67e5\u8be2\u8fd4\u56de NULL&#xff0c;\u5916\u5c42 &#096;&#061; NULL&#096; \u6c38\u8fdc\u4e3a\u5047&#xff0c;\u7ed3\u679c\u662f\u7a7a\u96c6<br \/>\nselect * from emp where deptno &#061; (select deptno from emp where ename &#061; &#039;\u4e0d\u5b58\u5728\u7684\u4eba&#039;);<br \/>\n&#8212; Empty set<\/p>\n<p>&#8212; \u7528 &lt;&#061;&gt; \u6216 IS NULL \u4e5f\u65e0\u6cd5\u76f4\u63a5\u5957\u7528&#xff08;\u56e0\u4e3a NULL &lt;&#061;&gt; NULL \u4e3a\u771f&#xff0c;\u4f46\u8fd9\u91cc\u7684 NULL \u662f\u5b50\u67e5\u8be2\u7ed3\u679c&#xff09;<\/p>\n<p>&#x1f517; \u524d\u540e\u7167\u5e94&#xff1a;\u7b2c 06 \u7bc7 3.4.9 \u8282\u8bb2 &lt;&#061;&gt; \u65f6\u8bf4\u8fc7\u300c&#061; NULL \u6c38\u8fdc\u67e5\u4e0d\u51fa\u300d\u3002\u8fd9\u91cc\u518d\u52a0\u6df1\u4e00\u5c42&#xff1a;\u4e0d\u53ea\u5b57\u9762 NULL \u4f1a\u8fd9\u6837&#xff0c;\u300c\u5b50\u67e5\u8be2\u8fd4\u56de NULL\u300d\u4e5f\u4f1a\u3002 \u6240\u4ee5\u7528\u5b50\u67e5\u8be2\u505a where \u5217 &#061; (\u5b50\u67e5\u8be2) \u65f6&#xff0c;\u4e00\u5b9a\u8981\u786e\u8ba4\u5b50\u67e5\u8be2\u4e00\u5b9a\u6709\u4e14\u53ea\u6709\u4e00\u884c\u7ed3\u679c\u3002<\/p>\n<h4 id=\"53-\u591a\u884c\u5b50\u67e5\u8be2\uff1ain--all--any\">5.3 \u591a\u884c\u5b50\u67e5\u8be2&#xff1a;in \/ all \/ any<\/h4>\n<h5 id=\"531-in\uff1a\u7b49\u4e8e\u300c\u5217\u8868\u91cc\u7684\u4efb\u610f\u4e00\u4e2a\u300d\">5.3.1 in&#xff1a;\u7b49\u4e8e\u300c\u5217\u8868\u91cc\u7684\u4efb\u610f\u4e00\u4e2a\u300d<\/h5>\n<p>\u67e5\u8be2\u548c 10 \u53f7\u90e8\u95e8\u7684\u5de5\u4f5c\u5c97\u4f4d\u76f8\u540c\u7684\u96c7\u5458\u7684\u540d\u5b57\u3001\u5c97\u4f4d\u3001\u5de5\u8d44\u3001\u90e8\u95e8\u53f7&#xff0c;\u4f46\u662f\u4e0d\u5305\u542b 10 \u53f7\u90e8\u95e8\u81ea\u5df1\u7684\u3002<\/p>\n<p>select ename, job, sal, deptno<br \/>\nfrom emp<br \/>\nwhere job in (select distinct job from emp where deptno &#061; 10)<br \/>\n  and deptno &lt;&gt; 10;<\/p>\n<p>\u7ed3\u679c&#xff1a;<\/p>\n<p>&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8211;&#043;<br \/>\n| ename | job     | sal     | deptno |<br \/>\n&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8211;&#043;<br \/>\n| SMITH | CLERK   |  800.00 |     20 |<br \/>\n| JONES | MANAGER | 2975.00 |     20 |<br \/>\n| BLAKE | MANAGER | 2850.00 |     30 |<br \/>\n| ADAMS | CLERK   | 1100.00 |     20 |<br \/>\n| JAMES | CLERK   |  950.00 |     30 |<br \/>\n| FORD  | ANALYST | 3000.00 |     20 |<br \/>\n&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8211;&#043;<\/p>\n<p>\u62c6\u89e3&#xff1a;<\/p>\n<p>\u2460 \u5b50\u67e5\u8be2\u505a\u4e86\u4ec0\u4e48&#xff1f;<\/p>\n<p>select distinct job from emp where deptno &#061; 10;<br \/>\n&#8212; CLERK, MANAGER, ANALYST, PRESIDENT<\/p>\n<p>10 \u53f7\u90e8\u95e8\u6709 4 \u79cd\u5c97\u4f4d&#xff08;CLARK&#061;MANAGER\u3001KING&#061;PRESIDENT\u3001MILLER&#061;CLERK&#xff0c;\u8fd8\u6709 SCOTT\/FORD \u5728 20 \u53f7&#xff1f;\u5176\u5b9e\u662f 4 \u79cd&#xff1a;CLERK\/MANAGER\/PRESIDENT&#8230; \u5b9e\u9645\u7ed3\u679c\u4f9d\u6570\u636e\u800c\u5b9a&#xff09;\u3002<\/p>\n<p>\u2461 \u4e3a\u4ec0\u4e48\u5b50\u67e5\u8be2\u91cc\u8981\u52a0 distinct&#xff1f;<\/p>\n<p>\u5176\u5b9e\u53ef\u4ee5\u4e0d\u52a0&#xff08;in \u4f1a\u81ea\u52a8\u53bb\u91cd&#xff0c;\u91cd\u590d\u7684\u503c\u5bf9\u7ed3\u679c\u6ca1\u5f71\u54cd&#xff09;&#xff0c;\u4f46\u52a0\u4e0a\u66f4\u6e05\u6670\u3001\u4e5f\u66f4\u7701\u5185\u5b58&#xff08;\u5b50\u67e5\u8be2\u7ed3\u679c\u96c6\u66f4\u5c0f&#xff09;\u3002<\/p>\n<p>\u2462 deptno &lt;&gt; 10 \u4e3a\u4ec0\u4e48\u5fc5\u987b\u52a0&#xff1f;<\/p>\n<p>\u56e0\u4e3a\u9700\u6c42\u660e\u786e\u300c\u4e0d\u5305\u542b 10 \u53f7\u90e8\u95e8\u81ea\u5df1\u300d\u3002\u5982\u679c\u628a 10 \u53f7\u90e8\u95e8\u7684 CLARK\u3001KING\u3001MILLER \u4e5f\u7b97\u8fdb\u6765&#xff0c;\u5c31\u8fdd\u80cc\u9898\u610f\u4e86\u3002<\/p>\n<p>\u26a0\ufe0f \u6ce8\u610f\u4e00\u4e2a\u53cd\u76f4\u89c9\u7684\u70b9&#xff1a;\u8fd9\u6761 SQL \u6ca1\u6709\u6392\u9664 SMITH \u672c\u4eba&#xff08;SMITH \u5728 20 \u53f7\u90e8\u95e8&#xff0c;\u5c97\u4f4d CLERK&#xff0c;\u7b26\u5408\u6761\u4ef6&#xff09;\u3002\u9898\u76ee\u53ea\u8981\u6c42\u300c\u6392\u9664 10 \u53f7\u90e8\u95e8\u300d&#xff0c;\u6ca1\u8981\u6c42\u300c\u6392\u9664\u67d0\u4e2a\u4eba\u300d\u3002\u505a\u9898\u65f6\u4e00\u5b9a\u8981\u9010\u5b57\u8bfb\u9898\u3002<\/p>\n<h5 id=\"532-all\uff1a\u6bd4\u300c\u5168\u90e8\u300d\u90fd\u5927\">5.3.2 all&#xff1a;\u6bd4\u300c\u5168\u90e8\u300d\u90fd\u5927<\/h5>\n<p>\u663e\u793a\u5de5\u8d44\u6bd4 30 \u53f7\u90e8\u95e8\u7684\u6240\u6709\u5458\u5de5 \u7684\u5de5\u8d44\u90fd\u9ad8\u7684\u5458\u5de5\u7684\u59d3\u540d\u3001\u5de5\u8d44\u548c\u90e8\u95e8\u53f7\u3002<\/p>\n<p>select ename, sal, deptno from EMP where sal &gt; all(select sal from EMP where deptno &#061; 30);<\/p>\n<p>\u7ed3\u679c&#xff1a;<\/p>\n<p>&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8211;&#043;<br \/>\n| ename | sal     | deptno |<br \/>\n&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8211;&#043;<br \/>\n| JONES | 2975.00 |     20 |<br \/>\n| SCOTT | 3000.00 |     20 |<br \/>\n| KING  | 5000.00 |     10 |<br \/>\n| FORD  | 3000.00 |     20 |<br \/>\n&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8211;&#043;<\/p>\n<p>&gt; all(\u5b50\u67e5\u8be2) \u7684\u8bed\u4e49&#xff1a;\u6bd4\u5b50\u67e5\u8be2\u8fd4\u56de\u7684\u300c\u6bcf\u4e00\u4e2a\u503c\u300d\u90fd\u5927\u3002<\/p>\n<p>\u7b49\u4ef7\u4e8e\u4ec0\u4e48&#xff1f;<\/p>\n<p>&#8212; \u7b49\u4ef7\u4e8e&#xff1a;\u5927\u4e8e\u6700\u5927\u503c<br \/>\nwhere sal &gt; (select max(sal) from emp where deptno &#061; 30)<br \/>\n&#8212; 30 \u53f7\u90e8\u95e8\u6700\u9ad8\u5de5\u8d44\u662f 2850&#xff08;BLAKE&#xff09;&#xff0c;\u6240\u4ee5\u7ed3\u679c\u90fd\u662f &gt; 2850 \u7684\u4eba<\/p>\n<p>\u26a0\ufe0f \u6ce8\u610f&#xff1a;&gt; all \u7684\u903b\u8f91\u7b49\u4ef7\u4e8e &gt; max&#xff0c;\u4f46\u8981\u6ce8\u610f\u5b50\u67e5\u8be2\u4e3a\u7a7a\u7684\u60c5\u51b5&#xff1a;<\/p>\n<p>&#8212; \u5982\u679c\u5b50\u67e5\u8be2\u8fd4\u56de\u7a7a\u96c6<br \/>\nwhere sal &gt; all(\u7a7a\u96c6)   &#8212; \u6052\u4e3a TRUE&#xff08;\u56e0\u4e3a\u300c\u5bf9\u6240\u6709 x \u90fd\u6210\u7acb\u300d\u5728\u7a7a\u96c6\u4e0a\u662f\u771f\u7a7a\u6210\u7acb\u7684&#xff01;&#xff09;<br \/>\nwhere sal &gt; max(\u7a7a\u96c6)   &#8212; max \u8fd4\u56de NULL&#xff0c;sal &gt; NULL \u6052\u4e3a NULL\u2192FALSE<\/p>\n<p>\u4e24\u8005\u5728\u300c\u5b50\u67e5\u8be2\u4e3a\u7a7a\u300d\u65f6\u884c\u4e3a\u76f8\u53cd&#xff01; \u8fd9\u662f\u4e2a\u6781\u7aef\u7684\u8fb9\u754c\u60c5\u51b5&#xff0c;\u4f46\u9762\u8bd5\u6709\u65f6\u4f1a\u95ee\u3002<\/p>\n<h5 id=\"533-any\uff1a\u6bd4\u300c\u4efb\u610f\u4e00\u4e2a\u300d\u5927\">5.3.3 any&#xff1a;\u6bd4\u300c\u4efb\u610f\u4e00\u4e2a\u300d\u5927<\/h5>\n<p>\u663e\u793a\u5de5\u8d44\u6bd4 30 \u53f7\u90e8\u95e8\u7684\u4efb\u610f\u5458\u5de5 \u7684\u5de5\u8d44\u9ad8\u7684\u5458\u5de5\u7684\u59d3\u540d\u3001\u5de5\u8d44\u548c\u90e8\u95e8\u53f7&#xff08;\u5305\u542b\u81ea\u5df1\u90e8\u95e8\u7684\u5458\u5de5&#xff09;\u3002<\/p>\n<p>select ename, sal, deptno from EMP where sal &gt; any(select sal from EMP where deptno &#061; 30);<\/p>\n<p>\u7ed3\u679c&#xff1a;<\/p>\n<p>&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8211;&#043;<br \/>\n| ename  | sal     | deptno |<br \/>\n&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8211;&#043;<br \/>\n| ALLEN  | 1600.00 |     30 |<br \/>\n| WARD   | 1250.00 |     30 |<br \/>\n| JONES  | 2975.00 |     20 |<br \/>\n| MARTIN | 1250.00 |     30 |<br \/>\n| BLAKE  | 2850.00 |     30 |<br \/>\n| CLARK  | 2450.00 |     10 |<br \/>\n| SCOTT  | 3000.00 |     20 |<br \/>\n| KING   | 5000.00 |     10 |<br \/>\n| TURNER | 1500.00 |     30 |<br \/>\n| ADAMS  | 1100.00 |     20 |<br \/>\n| FORD   | 3000.00 |     20 |<br \/>\n&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8211;&#043;<\/p>\n<p>&gt; any(\u5b50\u67e5\u8be2) \u7684\u8bed\u4e49&#xff1a;\u6bd4\u5b50\u67e5\u8be2\u8fd4\u56de\u7684\u300c\u81f3\u5c11\u4e00\u4e2a\u503c\u300d\u5927\u3002<\/p>\n<p>\u7b49\u4ef7\u4e8e\u4ec0\u4e48&#xff1f;<\/p>\n<p>&#8212; \u7b49\u4ef7\u4e8e&#xff1a;\u5927\u4e8e\u6700\u5c0f\u503c<br \/>\nwhere sal &gt; (select min(sal) from emp where deptno &#061; 30)<br \/>\n&#8212; 30 \u53f7\u90e8\u95e8\u6700\u4f4e\u5de5\u8d44\u662f 950&#xff08;JAMES&#xff09;&#xff0c;\u6240\u4ee5 &gt; 950 \u7684\u4eba\u5168\u8fdb\u6765\u4e86<\/p>\n<h5 id=\"534-\u3010\u6838\u5fc3\u5bf9\u7167\u8868\u3011\u4e09\u8005\u7684\u7b49\u4ef7\u5173\u7cfb\uff08\u80cc\u4e0b\u6765\uff09\">5.3.4 \u3010\u6838\u5fc3\u5bf9\u7167\u8868\u3011\u4e09\u8005\u7684\u7b49\u4ef7\u5173\u7cfb&#xff08;\u80cc\u4e0b\u6765&#xff09;<\/h5>\n<table>\n<tr>\u5199\u6cd5\u8bed\u4e49\u7b49\u4ef7\u4e8e\u8bb0\u5fc6\u6cd5<\/tr>\n<tbody>\n<tr>\n<td>&gt; all<\/td>\n<td>\u6bd4\u5168\u90e8\u90fd\u5927<\/td>\n<td>&gt; max()<\/td>\n<td>all &#061; \u6700\u4e25&#xff0c;\u8981\u8d62\u6240\u6709\u4eba<\/td>\n<\/tr>\n<tr>\n<td>&gt; any<\/td>\n<td>\u6bd4\u67d0\u4e2a\u5927&#xff08;\u22651 \u4e2a&#xff09;<\/td>\n<td>&gt; min()<\/td>\n<td>any &#061; \u6700\u677e&#xff0c;\u8d62\u4e00\u4e2a\u5c31\u884c<\/td>\n<\/tr>\n<tr>\n<td>&#061; any<\/td>\n<td>\u7b49\u4e8e\u4efb\u610f\u4e00\u4e2a<\/td>\n<td>in<\/td>\n<td>&#061; any \u5c31\u662f in<\/td>\n<\/tr>\n<tr>\n<td>&lt;&gt; all<\/td>\n<td>\u4e0d\u7b49\u4e8e\u4efb\u4f55\u4e00\u4e2a<\/td>\n<td>not in<\/td>\n<td>&lt;&gt; all \u5c31\u662f not in<\/td>\n<\/tr>\n<tr>\n<td>&gt; all&#xff08;\u7a7a\u96c6&#xff09;<\/td>\n<td>\u771f\u7a7a\u6210\u7acb<\/td>\n<td>TRUE<\/td>\n<td>\u8fb9\u754c\u5751<\/td>\n<\/tr>\n<tr>\n<td>&gt; any&#xff08;\u7a7a\u96c6&#xff09;<\/td>\n<td>\u4e0d\u6210\u7acb<\/td>\n<td>FALSE<\/td>\n<td>\u8fb9\u754c\u5751<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u518d\u8865\u4e09\u4e2a&#xff1a;<\/p>\n<table>\n<tr>\u5199\u6cd5\u8bed\u4e49\u7b49\u4ef7\u4e8e<\/tr>\n<tbody>\n<tr>\n<td>&lt; all<\/td>\n<td>\u6bd4\u5168\u90e8\u90fd\u5c0f<\/td>\n<td>&lt; min()<\/td>\n<\/tr>\n<tr>\n<td>&lt; any<\/td>\n<td>\u6bd4\u67d0\u4e2a\u5c0f<\/td>\n<td>&lt; max()<\/td>\n<\/tr>\n<tr>\n<td>&gt;&#061; all \/ &lt;&#061; any<\/td>\n<td>\u540c\u7406<\/td>\n<td>&gt;&#061; max() \/ &lt;&#061; min()<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>some \u662f any \u7684\u540c\u4e49\u8bcd&#xff08;\u6807\u51c6 SQL \u91cc\u4e24\u8005\u7b49\u4ef7&#xff0c;MySQL \u90fd\u652f\u6301&#xff09;\u3002<\/p>\n<p>\u26a0\ufe0f \u6700\u91cd\u8981\u7684\u4e00\u4e2a\u5751&#xff1a;any \u548c some \u90fd\u662f\u300c\u53ea\u8981\u6709\u4e00\u4e2a\u6ee1\u8db3\u300d&#xff0c;\u4e0d\u662f\u300c\u5168\u90e8\u6ee1\u8db3\u300d\u3002<\/p>\n<p>\u5f88\u591a\u4eba\u770b\u5230 &#034;any&#034; \u4ee5\u4e3a\u662f\u300c\u4efb\u610f&#xff08;\u968f\u4fbf\u4e00\u4e2a\u90fd\u884c&#xff09;\u300d&#xff0c;\u7406\u89e3\u6210\u4e86 all\u3002\u8bb0\u4f4f&#xff1a;<\/p>\n<p>any &#061; \u5b58\u5728\u4e00\u4e2a&#xff08;\u2203&#xff09;&#xff0c;all &#061; \u6240\u6709&#xff08;\u2200&#xff09;\u3002<\/p>\n<h4 id=\"54-\u591a\u5217\u5b50\u67e5\u8be2\">5.4 \u591a\u5217\u5b50\u67e5\u8be2<\/h4>\n<p>\u5355\u884c\u5b50\u67e5\u8be2\u662f\u6307\u5b50\u67e5\u8be2\u53ea\u8fd4\u56de\u5355\u5217\u3001\u5355\u884c\u6570\u636e&#xff1b;\u591a\u884c\u5b50\u67e5\u8be2\u662f\u6307\u8fd4\u56de\u5355\u5217\u591a\u884c\u6570\u636e&#xff0c;\u90fd\u662f\u9488\u5bf9\u5355\u5217\u800c\u8a00\u7684\u3002\u800c\u591a\u5217\u5b50\u67e5\u8be2\u5219\u662f\u6307\u67e5\u8be2\u8fd4\u56de\u591a\u4e2a\u5217\u6570\u636e\u7684\u5b50\u67e5\u8be2\u8bed\u53e5\u3002<\/p>\n<p>\u6848\u4f8b&#xff1a;\u67e5\u8be2\u548c SMITH \u7684\u90e8\u95e8\u548c\u5c97\u4f4d\u5b8c\u5168\u76f8\u540c\u7684\u6240\u6709\u96c7\u5458&#xff0c;\u4e0d\u542b SMITH \u672c\u4eba<\/p>\n<p>select ename from EMP<br \/>\nwhere (deptno, job) &#061; (select deptno, job from EMP where ename &#061; &#039;SMITH&#039;)<br \/>\n  and ename &lt;&gt; &#039;SMITH&#039;;<\/p>\n<p>\u7ed3\u679c&#xff1a;<\/p>\n<p>&#043;&#8212;&#8212;-&#043;<br \/>\n| ename |<br \/>\n&#043;&#8212;&#8212;-&#043;<br \/>\n| ADAMS |<br \/>\n&#043;&#8212;&#8212;-&#043;<\/p>\n<p>\u8fd9\u662f\u300c\u884c\u503c\u6784\u9020\u5668&#xff08;Row Constructor&#xff09;\u300d\u8bed\u6cd5&#xff1a;<\/p>\n<p>(a, b) &#061; (\u5b50\u67e5\u8be2\u8fd4\u56de\u7684\u4e24\u5217)<\/p>\n<p>\u5b83\u7b49\u4ef7\u4e8e&#xff1a;<\/p>\n<p>where deptno &#061; (select deptno from emp where ename&#061;&#039;SMITH&#039;)<br \/>\n  and job    &#061; (select job    from emp where ename&#061;&#039;SMITH&#039;)<\/p>\n<p>\u4f46\u4e00\u884c\u5199\u6cd5\u66f4\u6e05\u6670\u3001\u4e5f\u53ea\u8dd1\u4e00\u6b21\u5b50\u67e5\u8be2\u3002<\/p>\n<p>\u26a0\ufe0f \u4e09\u4e2a\u8bed\u6cd5\u9650\u5236&#xff1a;<\/p>\n<p>\u2460 \u62ec\u53f7\u91cc\u7684\u5217\u6570\u5fc5\u987b\u4e00\u81f4<\/p>\n<p>&#8212; \u274c \u5217\u6570\u4e0d\u5339\u914d<br \/>\nwhere (deptno, job, sal) &#061; (select deptno, job from emp where ename&#061;&#039;SMITH&#039;);<br \/>\n&#8212; ERROR 1241 (21000): Operand should contain 3 column(s)<\/p>\n<p>\u2461 \u53ea\u80fd\u7528\u5728 &#061; \u548c &lt;&gt; \u4e0a&#xff08;MySQL \u4e0d\u652f\u6301 (a,b) &gt; (c,d) \u8fd9\u79cd\u884c\u6bd4\u8f83&#xff09;<\/p>\n<p>\u2462 in \u4e5f\u53ef\u4ee5\u914d\u591a\u5217&#xff08;8.0 \u652f\u6301\u5f97\u66f4\u597d&#xff09;<\/p>\n<p>&#8212; \u627e\u51fa\u300c\u90e8\u95e8&#043;\u5c97\u4f4d\u300d\u7ec4\u5408\u5728\u67d0\u4e2a\u96c6\u5408\u91cc\u7684\u5458\u5de5<br \/>\nwhere (deptno, job) in ((10,&#039;CLERK&#039;), (20,&#039;ANALYST&#039;))<\/p>\n<h4 id=\"55-\u3010\u91cd\u70b9\u3011\u5728-from-\u5b50\u53e5\u4e2d\u4f7f\u7528\u5b50\u67e5\u8be2\uff08\u6d3e\u751f\u8868\uff09\">5.5 \u3010\u91cd\u70b9\u3011\u5728 FROM \u5b50\u53e5\u4e2d\u4f7f\u7528\u5b50\u67e5\u8be2&#xff08;\u6d3e\u751f\u8868&#xff09;<\/h4>\n<p>\u5b50\u67e5\u8be2\u8bed\u53e5\u51fa\u73b0\u5728 FROM \u5b50\u53e5\u4e2d\u3002\u8fd9\u91cc\u8981\u7528\u5230\u6570\u636e\u67e5\u8be2\u7684\u6280\u5de7&#xff1a;\u628a\u4e00\u4e2a\u5b50\u67e5\u8be2\u5f53\u505a\u4e00\u4e2a\u4e34\u65f6\u8868\u4f7f\u7528\u3002<\/p>\n<p>\u8fd9\u662f\u5168\u7bc7\u6700\u6709\u4ef7\u503c\u7684\u4e00\u8282\u3002 \u56e0\u4e3a\u5b83\u89e3\u51b3\u4e86\u4e00\u6574\u7c7b\u300c\u5355\u8868\u67e5\u8be2\u505a\u4e0d\u5230\u300d\u7684\u95ee\u9898\u3002<\/p>\n<h5 id=\"551-\u6848\u4f8b\u4e00\uff1a\u663e\u793a\u6bcf\u4e2a\u9ad8\u4e8e\u81ea\u5df1\u90e8\u95e8\u5e73\u5747\u5de5\u8d44\u7684\u5458\u5de5\">5.5.1 \u6848\u4f8b\u4e00&#xff1a;\u663e\u793a\u6bcf\u4e2a\u9ad8\u4e8e\u81ea\u5df1\u90e8\u95e8\u5e73\u5747\u5de5\u8d44\u7684\u5458\u5de5<\/h5>\n<p>\u9700\u6c42\u672c\u8d28&#xff1a;<\/p>\n<p>\u5bf9\u4e8e\u6bcf\u4e00\u4e2a\u5458\u5de5&#xff1a;<br \/>\n    \u2460 \u7b97\u51fa\u300c\u4ed6\u6240\u5728\u90e8\u95e8\u300d\u7684\u5e73\u5747\u5de5\u8d44<br \/>\n    \u2461 \u6bd4\u8f83&#xff1a;\u4ed6\u7684\u5de5\u8d44 &gt; \u90e8\u95e8\u5e73\u5747&#xff1f;<\/p>\n<p>\u96be\u70b9&#xff1a;\u7b2c \u2460 \u6b65\u7684\u300c\u90e8\u95e8\u5e73\u5747\u5de5\u8d44\u300d\u662f\u300c\u6309\u90e8\u95e8\u5206\u7ec4\u300d\u7684\u805a\u5408\u7ed3\u679c&#xff0c;\u800c\u7b2c \u2461 \u6b65\u53c8\u8981\u56de\u5230\u300c\u9010\u884c\u300d\u6bd4\u8f83\u3002\u4e24\u6b21\u64cd\u4f5c\u7684\u7c92\u5ea6\u4e0d\u4e00\u6837\u3002<\/p>\n<p>\u89e3\u6cd5&#xff1a;\u628a\u300c\u90e8\u95e8\u5e73\u5747\u5de5\u8d44\u300d\u505a\u6210\u4e00\u5f20\u4e34\u65f6\u8868&#xff0c;\u518d\u548c emp \u8fde\u63a5\u3002<\/p>\n<p>&#8212; \u83b7\u53d6\u5404\u4e2a\u90e8\u95e8\u7684\u5e73\u5747\u5de5\u8d44&#xff0c;\u5c06\u5176\u770b\u4f5c\u4e34\u65f6\u8868<br \/>\nselect ename, deptno, sal, format(asal, 2)<br \/>\nfrom EMP,<br \/>\n     (select avg(sal) asal, deptno dt from EMP group by deptno) tmp<br \/>\nwhere EMP.sal &gt; tmp.asal and EMP.deptno &#061; tmp.dt;<\/p>\n<p>\u7ed3\u679c&#xff1a;<\/p>\n<p>&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#043;<br \/>\n| ename | deptno | sal     | format(asal, 2)     |<br \/>\n&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#043;<br \/>\n| ALLEN |     30 | 1600.00 | 1566.67             |<br \/>\n| JONES |     20 | 2975.00 | 2175.00             |<br \/>\n| BLAKE |     30 | 2850.00 | 1566.67             |<br \/>\n| SCOTT |     20 | 3000.00 | 2175.00             |<br \/>\n| KING  |     10 | 5000.00 | 2916.67             |<br \/>\n| FORD  |     20 | 3000.00 | 2175.00             |<br \/>\n&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#043;<\/p>\n<p>\u62c6\u89e3\u8fd9\u4e2a\u300c\u4e34\u65f6\u8868\u300d&#xff1a;<\/p>\n<p>(select avg(sal) asal, deptno dt from EMP group by deptno) tmp<br \/>\n&#8212; \u8fd9\u4e2a\u5b50\u67e5\u8be2\u7684\u7ed3\u679c&#xff1a;<br \/>\n&#8212; asal&#061;2916.67,  dt&#061;10<br \/>\n&#8212; asal&#061;2175.00,  dt&#061;20<br \/>\n&#8212; asal&#061;1566.67,  dt&#061;30<\/p>\n<p>\u5b83\u662f\u4e00\u5f20\u300c\u53ea\u6709 3 \u884c\u300d\u7684\u8868&#xff1a;\u6bcf\u4e2a\u90e8\u95e8\u4e00\u884c\u3002 \u7136\u540e\u62ff\u5b83\u548c 14 \u884c\u7684 emp \u505a\u8fde\u63a5&#xff0c;emp.deptno &#061; tmp.dt\u3002<\/p>\n<p>\u4e3a\u4ec0\u4e48\u8fd9\u6837\u5c31\u80fd\u89e3\u51b3\u95ee\u9898&#xff1f; \u56e0\u4e3a**\u300c\u6240\u6709\u90e8\u95e8\u5e73\u5747\u5de5\u8d44\u300d\u8fd9\u5f20\u5c0f\u8868&#xff0c;\u88ab\u5e7f\u64ad\u5230\u4e86\u6bcf\u4e00\u884c\u5458\u5de5\u8bb0\u5f55\u4e0a**&#xff1a;<\/p>\n<p>SMITH(20, 800) \u2190\u2192 \u90e8\u95e820\u7684\u5e73\u57472175 \u2192 800 &gt; 2175? \u5426<br \/>\nJONES(20, 2975) \u2190\u2192 \u90e8\u95e820\u7684\u5e73\u57472175 \u2192 2975 &gt; 2175? \u662f \u2705<br \/>\nBLAKE(30, 2850) \u2190\u2192 \u90e8\u95e830\u7684\u5e73\u57471566.67 \u2192 \u662f \u2705<\/p>\n<p>\u2b50 \u8fd9\u662f\u300c\u6d3e\u751f\u8868\u300d\u6700\u91cd\u8981\u7684\u601d\u60f3&#xff1a;<\/p>\n<p>\u5f53\u300c\u805a\u5408\u7ed3\u679c\u300d\u548c\u300c\u660e\u7ec6\u884c\u300d\u9700\u8981\u5728\u540c\u4e00\u884c\u91cc\u6bd4\u8f83\u65f6&#xff0c;\u628a\u805a\u5408\u7ed3\u679c\u505a\u6210\u4e00\u5f20\u4e34\u65f6\u8868&#xff0c;\u518d JOIN \u56de\u539f\u8868\u3002<\/p>\n<h5 id=\"552-\u6848\u4f8b\u4e8c\uff1a\u67e5\u627e\u6bcf\u4e2a\u90e8\u95e8\u5de5\u8d44\u6700\u9ad8\u7684\u4eba\">5.5.2 \u6848\u4f8b\u4e8c&#xff1a;\u67e5\u627e\u6bcf\u4e2a\u90e8\u95e8\u5de5\u8d44\u6700\u9ad8\u7684\u4eba<\/h5>\n<p>select EMP.ename, EMP.sal, EMP.deptno, ms<br \/>\nfrom EMP,<br \/>\n     (select max(sal) ms, deptno from EMP group by deptno) tmp<br \/>\nwhere EMP.deptno &#061; tmp.deptno and EMP.sal &#061; tmp.ms;<\/p>\n<p>\u7ed3\u679c&#xff1a;<\/p>\n<p>&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#043;<br \/>\n| ename | sal     | deptno | ms      |<br \/>\n&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#043;<br \/>\n| KING  | 5000.00 |     10 | 5000.00 |<br \/>\n| SCOTT | 3000.00 |     20 | 3000.00 |<br \/>\n| FORD  | 3000.00 |     20 | 3000.00 |<br \/>\n| BLAKE | 2850.00 |     30 | 2850.00 |<br \/>\n&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#043;<\/p>\n<p>\u6ce8\u610f 20 \u53f7\u90e8\u95e8\u51fa\u73b0\u4e86\u4e24\u6761&#xff08;SCOTT \u548c FORD \u90fd\u662f 3000&#xff09;\u2014\u2014\u56e0\u4e3a\u5b83\u4eec\u5e76\u5217\u6700\u9ad8&#xff0c;\u8fd9\u7b26\u5408\u8bed\u4e49\u3002<\/p>\n<p>\u540c\u6837\u7684\u601d\u60f3&#xff1a;\u628a\u300c\u6bcf\u4e2a\u90e8\u95e8\u6700\u9ad8\u5de5\u8d44\u300d\u505a\u6210 3 \u884c\u7684\u4e34\u65f6\u8868&#xff0c;\u518d join \u56de\u6765\u3002<\/p>\n<h5 id=\"553-\u6848\u4f8b\u4e09\uff1a\u663e\u793a\u6bcf\u4e2a\u90e8\u95e8\u7684\u4fe1\u606f\u548c\u4eba\u5458\u6570\u91cf\">5.5.3 \u6848\u4f8b\u4e09&#xff1a;\u663e\u793a\u6bcf\u4e2a\u90e8\u95e8\u7684\u4fe1\u606f\u548c\u4eba\u5458\u6570\u91cf<\/h5>\n<p>\u8fd9\u662f\u5168\u7bc7\u6700\u91cd\u8981\u7684\u4e00\u9898&#xff0c;\u56e0\u4e3a\u4e24\u79cd\u5199\u6cd5\u4f1a\u5f97\u51fa\u300c\u4e0d\u540c\u7684\u7ed3\u679c\u300d&#xff0c;\u800c\u8bfe\u7a0b\u53ea\u7ed9\u4e86\u524d\u4e24\u79cd\u2014\u2014\u90fd\u4e0d\u662f\u6700\u5168\u7684\u3002<\/p>\n<p>\u65b9\u6cd5 1&#xff1a;\u4f7f\u7528\u591a\u8868\u8fde\u63a5 &#043; group by<\/p>\n<p>select DEPT.dname, DEPT.deptno, DEPT.loc, count(*) &#039;\u90e8\u95e8\u4eba\u6570&#039;<br \/>\nfrom EMP, DEPT<br \/>\nwhere EMP.deptno &#061; DEPT.deptno<br \/>\ngroup by DEPT.deptno, DEPT.dname, DEPT.loc;<\/p>\n<p>\u7ed3\u679c&#xff1a;<\/p>\n<p>&#043;&#8212;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;-&#043;<br \/>\n| dname      | deptno | loc      | \u90e8\u95e8\u4eba\u6570 |<br \/>\n&#043;&#8212;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;-&#043;<br \/>\n| ACCOUNTING |     10 | NEW YORK |        3 |<br \/>\n| RESEARCH   |     20 | DALLAS   |        5 |<br \/>\n| SALES      |     30 | CHICAGO  |        6 |<br \/>\n&#043;&#8212;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;-&#043;<\/p>\n<p>\u53ea\u6709 3 \u4e2a\u90e8\u95e8&#xff01;40 \u53f7 OPERATIONS \u90e8\u95e8\u6d88\u5931\u4e86\u3002<\/p>\n<p>\u4e3a\u4ec0\u4e48&#xff1f; \u56e0\u4e3a where EMP.deptno &#061; DEPT.deptno \u662f\u5185\u8fde\u63a5\u2014\u2014\u6ca1\u6709\u5458\u5de5\u7684\u90e8\u95e8&#xff0c;\u5728 emp \u91cc\u627e\u4e0d\u5230\u5339\u914d\u884c&#xff0c;\u5c31\u88ab\u8fc7\u6ee4\u6389\u4e86\u3002<\/p>\n<p>\u65b9\u6cd5 2&#xff1a;\u4f7f\u7528\u5b50\u67e5\u8be2&#xff08;\u6d3e\u751f\u8868&#xff09;<\/p>\n<p>&#8212; 1. \u5bf9 EMP \u8868\u8fdb\u884c\u4eba\u5458\u7edf\u8ba1<br \/>\nselect count(*), deptno from EMP group by deptno;<\/p>\n<p>&#8212; 2. \u5c06\u4e0a\u9762\u7684\u8868\u770b\u4f5c\u4e34\u65f6\u8868<br \/>\nselect DEPT.deptno, dname, mycnt, loc<br \/>\nfrom DEPT,<br \/>\n     (select count(*) mycnt, deptno from EMP group by deptno) tmp<br \/>\nwhere DEPT.deptno &#061; tmp.deptno;<\/p>\n<p>\u7ed3\u679c\u548c\u65b9\u6cd5 1 \u5b8c\u5168\u4e00\u6837&#xff0c;40 \u53f7\u90e8\u95e8\u8fd8\u662f\u4e0d\u5728\u3002<\/p>\n<p>\u4e3a\u4ec0\u4e48&#xff1f; \u56e0\u4e3a from DEPT, (&#8230;) &#043; where DEPT.deptno &#061; tmp.deptno \u4f9d\u7136\u662f\u5185\u8fde\u63a5\u2014\u2014\u4e34\u65f6\u8868\u91cc\u6ca1\u6709 40 \u53f7\u90e8\u95e8&#xff08;\u56e0\u4e3a emp \u91cc\u6ca1\u4eba\u5728 40 \u53f7&#xff09;&#xff0c;\u6240\u4ee5 40 \u53f7\u5bf9\u4e0d\u4e0a&#xff0c;\u53c8\u88ab\u8fc7\u6ee4\u4e86\u3002<\/p>\n<h5 id=\"\u2b50-554-\u90a3\u6b63\u786e\u7b54\u6848\u662f\u4ec0\u4e48\uff1f\uff08\u8fd9\u662f\u5168\u6587\u7684\u94a9\u5b50\uff09\">\u2b50 5.5.4 \u90a3\u6b63\u786e\u7b54\u6848\u662f\u4ec0\u4e48&#xff1f;&#xff08;\u8fd9\u662f\u5168\u6587\u7684\u94a9\u5b50&#xff09;<\/h5>\n<p>\u8981\u663e\u793a\u300c\u6240\u6709\u90e8\u95e8&#xff08;\u5305\u62ec\u6ca1\u6709\u5458\u5de5\u7684&#xff09;\u300d&#xff0c;\u5fc5\u987b\u7528\u300c\u5916\u8fde\u63a5\u300d&#xff1a;<\/p>\n<p>select d.deptno, d.dname, d.loc, count(e.empno) as \u90e8\u95e8\u4eba\u6570<br \/>\nfrom dept d<br \/>\nleft join emp e on d.deptno &#061; e.deptno<br \/>\ngroup by d.deptno, d.dname, d.loc;<\/p>\n<p>\u7ed3\u679c&#xff08;\u8fd9\u624d\u662f\u4e1a\u52a1\u4e0a\u771f\u6b63\u8981\u7684&#xff09;&#xff1a;<\/p>\n<p>&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;-&#043;<br \/>\n| deptno | dname      | loc      | \u90e8\u95e8\u4eba\u6570 |<br \/>\n&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;-&#043;<br \/>\n|     10 | ACCOUNTING | NEW YORK |        3 |<br \/>\n|     20 | RESEARCH   | DALLAS   |        5 |<br \/>\n|     30 | SALES      | CHICAGO  |        6 |<br \/>\n|     40 | OPERATIONS | BOSTON   |        0 |   \u2190 \u6709\u4e86&#xff01;<br \/>\n&#043;&#8212;&#8212;&#8211;&#043;&#8212;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;-&#043;<\/p>\n<p>\u6ce8\u610f&#xff1a;count(e.empno) \u800c\u4e0d\u662f count(*)&#xff01;<\/p>\n<p>\u56e0\u4e3a left join \u7ed9 40 \u53f7\u90e8\u95e8\u8865\u4e86\u4e00\u884c\u300c\u5168 NULL \u7684 emp \u8bb0\u5f55\u300d&#xff0c;count(*) \u4f1a\u628a\u8fd9\u4e00\u884c\u7b97\u6210 1&#xff08;\u9519&#xff01;&#xff09;&#xff0c;\u800c count(e.empno) \u53ea\u7edf\u8ba1\u975e NULL \u7684 empno&#xff0c;\u5f97\u5230 0&#xff08;\u5bf9&#xff01;&#xff09;\u3002<\/p>\n<p>&#x1f517; \u524d\u540e\u7167\u5e94 &#043; \u9884\u544a&#xff1a; \u8fd9\u91cc\u7b2c\u4e00\u6b21\u51fa\u73b0\u4e86\u300c\u5185\u8fde\u63a5\u4f1a\u4e22\u6570\u636e\u300d\u8fd9\u4e2a\u95ee\u9898&#xff0c;\u800c\u89e3\u6cd5\u662f left join\u3002 \u8fd9\u6b63\u662f\u4e0b\u4e00\u7bc7\u300a\u5185\u5916\u8fde\u63a5\u300b\u8981\u5f7b\u5e95\u8bb2\u900f\u7684\u4e1c\u897f\u3002<\/p>\n<p>\u800c\u4e14\u6211\u628a\u8fd9\u4e00\u9898\u7684\u4e09\u4e2a\u7248\u672c\u653e\u5728\u4e00\u8d77&#xff0c;\u5c31\u662f\u60f3\u8ba9\u4f60\u770b\u6e05\u4e00\u4ef6\u4e8b&#xff1a; \u8bfe\u4ef6\u4e0a\u7684 SQL \u4e0d\u4e00\u5b9a\u662f\u6700\u4f18\u89e3\u3002 \u65b9\u6cd5 1 \u548c\u65b9\u6cd5 2 \u90fd\u5bf9\u300c\u8bed\u6cd5\u300d&#xff0c;\u4f46\u90fd\u4e0d\u7b26\u5408\u300c\u663e\u793a\u6bcf\u4e2a\u90e8\u95e8\u300d\u8fd9\u4e2a\u9700\u6c42\u2014\u2014\u56e0\u4e3a\u5b83\u4eec\u90fd\u6f0f\u6389\u4e86 40 \u53f7\u90e8\u95e8\u3002<\/p>\n<p>\u80fd\u770b\u51fa\u300c\u8bfe\u4ef6\u8fd9\u91cc\u7684\u5199\u6cd5\u4e22\u6570\u636e\u300d&#xff0c;\u624d\u7b97\u771f\u7684\u8bfb\u61c2\u4e86\u3002<\/p>\n<h4 id=\"56-\u8865\u5168\uff1aexists--not-exists\uff08\u8bfe\u4ef6\u6ca1\u8bb2\uff09\">5.6 \u8865\u5168&#xff1a;exists \/ not exists&#xff08;\u8bfe\u4ef6\u6ca1\u8bb2&#xff09;<\/h4>\n<p>\u5b50\u67e5\u8be2\u8fd8\u6709\u4e00\u5927\u7c7b\u7528\u6cd5&#xff1a;exists\u3002<\/p>\n<p>&#8212; \u627e\u51fa\u300c\u6709\u4e0b\u5c5e\u300d\u7684\u7ecf\u7406<br \/>\nselect * from emp e<br \/>\nwhere exists (select 1 from emp w where w.mgr &#061; e.empno);<\/p>\n<p>&#8212; \u627e\u51fa\u300c\u6ca1\u6709\u4e0b\u5c5e\u300d\u7684\u5458\u5de5<br \/>\nselect * from emp e<br \/>\nwhere not exists (select 1 from emp w where w.mgr &#061; e.empno);<\/p>\n<p>exists \u7684\u8bed\u4e49&#xff1a;\u5b50\u67e5\u8be2\u662f\u5426\u300c\u6709\u7ed3\u679c\u300d&#xff08;\u4e0d\u5173\u5fc3\u7ed3\u679c\u662f\u4ec0\u4e48&#xff0c;\u53ea\u770b\u6709\u6ca1\u6709\u884c&#xff09;\u3002<\/p>\n<p>exists vs in \u7684\u5173\u952e\u533a\u522b&#xff1a;<\/p>\n<table>\n<tr>\u5bf9\u6bd4inexists<\/tr>\n<tbody>\n<tr>\n<td>\u5b50\u67e5\u8be2\u8fd4\u56de\u4ec0\u4e48<\/td>\n<td>\u4e00\u4e2a\u503c\u96c6\u5408<\/td>\n<td>\u53ea\u5224\u65ad\u6709\u65e0\u884c<\/td>\n<\/tr>\n<tr>\n<td>\u672c\u8d28<\/td>\n<td>\u5148\u7b97\u51fa\u5b50\u67e5\u8be2\u7ed3\u679c&#xff0c;\u518d\u62ff\u5916\u5c42\u503c\u53bb\u5339\u914d<\/td>\n<td>\u76f8\u5173\u5b50\u67e5\u8be2&#xff1a;\u5916\u5c42\u6bcf\u4e00\u884c\u90fd\u8dd1\u4e00\u6b21\u5b50\u67e5\u8be2<\/td>\n<\/tr>\n<tr>\n<td>\u5b50\u67e5\u8be2\u5927\u65f6<\/td>\n<td>\u6162&#xff08;\u8981\u7269\u5316\u6574\u4e2a\u7ed3\u679c\u96c6&#xff09;<\/td>\n<td>\u5feb&#xff08;\u627e\u5230\u4e00\u4e2a\u5c31\u8fd4\u56de&#xff09;<\/td>\n<\/tr>\n<tr>\n<td>\u5199\u6cd5<\/td>\n<td>where x in (select y from t)<\/td>\n<td>where exists (select 1 from t where t.y &#061; x)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u26a0\ufe0f exists \u662f\u300c\u76f8\u5173\u5b50\u67e5\u8be2&#xff08;correlated subquery&#xff09;\u300d\u2014\u2014\u5b83\u5f15\u7528\u4e86\u5916\u5c42\u7684 e.empno&#xff0c;\u6240\u4ee5\u5916\u5c42\u6bcf\u4e00\u884c\u90fd\u4f1a\u6267\u884c\u4e00\u6b21\u5b50\u67e5\u8be2\u3002\u542c\u8d77\u6765\u5f88\u6162&#xff0c;\u4f46\u56e0\u4e3a\u300c\u627e\u5230\u4e00\u4e2a\u5c31\u505c\u300d&#xff0c;\u5b9e\u9645\u5f80\u5f80\u6bd4 in \u5feb\u3002<\/p>\n<p>\u73b0\u4ee3 MySQL \u4f18\u5316\u5668\u4f1a\u628a\u5f88\u591a in \u548c exists \u4f18\u5316\u6210\u540c\u6837\u7684\u6267\u884c\u8ba1\u5212&#xff08;\u534a\u8fde\u63a5 semi-join \u4f18\u5316&#xff09;&#xff0c;\u6240\u4ee5\u4e0d\u8981\u80cc\u300c\u8c01\u66f4\u5feb\u300d&#xff0c;\u8981\u7528 EXPLAIN \u770b\u5b9e\u9645\u60c5\u51b5\u3002<\/p>\n<p>\u8fd8\u6709 not exists \u7684\u4e00\u4e2a\u91cd\u8981\u4f18\u52bf&#xff1a;\u5b83\u4e0d\u4f1a\u88ab NULL \u5751\u3002<\/p>\n<p>&#x1f517; \u524d\u540e\u7167\u5e94&#xff1a;\u7b2c 06 \u7bc7 3.4.3 \u8282\u8bb2\u8fc7 NOT IN (\u2026, NULL, \u2026) \u4f1a\u6c38\u8fdc\u8fd4\u56de\u7a7a\u3002\u800c not exists \u4e0d\u4f1a\u2014\u2014\u56e0\u4e3a\u5b83\u5224\u65ad\u7684\u662f\u300c\u884c\u662f\u5426\u5b58\u5728\u300d&#xff0c;\u4e0d\u662f\u300c\u503c\u662f\u5426\u76f8\u7b49\u300d\u3002<\/p>\n<p> &#8212; \u274c \u5982\u679c\u5b50\u67e5\u8be2\u91cc\u6709 NULL&#xff0c;\u4e00\u6761\u90fd\u67e5\u4e0d\u51fa\u6765<br \/>\nwhere empno not in (select mgr from emp)<\/p>\n<p>&#8212; \u2705 \u5b89\u5168<br \/>\nwhere not exists (select 1 from emp w where w.mgr &#061; e.empno)<\/p>\n<p>\u8fd9\u662f not exists \u76f8\u5bf9 not in \u6700\u5927\u7684\u5b9e\u7528\u4ef7\u503c\u3002<\/p>\n<hr \/>\n<h3 id=\"6-\u5408\u5e76\u67e5\u8be2\uff1aunion-\u4e0e-union-all\">6. \u5408\u5e76\u67e5\u8be2&#xff1a;UNION \u4e0e UNION ALL<\/h3>\n<p>\u5728\u5b9e\u9645\u5e94\u7528\u4e2d&#xff0c;\u4e3a\u4e86\u5408\u5e76\u591a\u4e2a select \u7684\u6267\u884c\u7ed3\u679c&#xff0c;\u53ef\u4ee5\u4f7f\u7528\u96c6\u5408\u64cd\u4f5c\u7b26 union\u3001union all\u3002<\/p>\n<h4 id=\"61-union\uff1a\u5e76\u96c6--\u81ea\u52a8\u53bb\u91cd\">6.1 union&#xff1a;\u5e76\u96c6 &#043; \u81ea\u52a8\u53bb\u91cd<\/h4>\n<p>\u8be5\u64cd\u4f5c\u7b26\u7528\u4e8e\u53d6\u5f97\u4e24\u4e2a\u7ed3\u679c\u96c6\u7684\u5e76\u96c6\u3002\u5f53\u4f7f\u7528\u8be5\u64cd\u4f5c\u7b26\u65f6&#xff0c;\u4f1a\u81ea\u52a8\u53bb\u6389\u7ed3\u679c\u96c6\u4e2d\u7684\u91cd\u590d\u884c\u3002<\/p>\n<p>select ename, sal, job from EMP where sal &gt; 2500<br \/>\nunion<br \/>\nselect ename, sal, job from EMP where job &#061; &#039;MANAGER&#039;;<\/p>\n<p>\u7ed3\u679c&#xff08;6 \u884c&#xff09;&#xff1a;<\/p>\n<p>&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8211;&#043;<br \/>\n| ename | sal     | job       |<br \/>\n&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8211;&#043;<br \/>\n| JONES | 2975.00 | MANAGER   |<br \/>\n| BLAKE | 2850.00 | MANAGER   |<br \/>\n| SCOTT | 3000.00 | ANALYST   |<br \/>\n| KING  | 5000.00 | PRESIDENT |<br \/>\n| FORD  | 3000.00 | ANALYST   |<br \/>\n| CLARK | 2450.00 | MANAGER   |<br \/>\n&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8211;&#043;<\/p>\n<p>\u5206\u6790&#xff1a;<\/p>\n<ul>\n<li>\u7b2c\u4e00\u4e2a\u67e5\u8be2&#xff08;sal &gt; 2500&#xff09;&#xff1a;JONES\u3001SCOTT\u3001BLAKE\u3001KING\u3001FORD \u2192 5 \u884c<\/li>\n<li>\u7b2c\u4e8c\u4e2a\u67e5\u8be2&#xff08;job &#061; &#039;MANAGER&#039;&#xff09;&#xff1a;JONES\u3001BLAKE\u3001CLARK \u2192 3 \u884c<\/li>\n<li>\u5408\u5e76&#xff1a;5 &#043; 3 &#061; 8 \u884c&#xff0c;\u4f46 JONES \u548c BLAKE \u91cd\u590d\u4e86&#xff0c;\u53bb\u91cd\u540e \u2192 6 \u884c<\/li>\n<\/ul>\n<p>\u6ce8\u610f CLARK \u7684\u51fa\u73b0&#xff1a;\u4ed6\u7684 sal &#061; 2450&#xff0c;\u4e0d\u6ee1\u8db3\u7b2c\u4e00\u4e2a\u6761\u4ef6&#xff0c;\u4f46\u56e0\u4e3a\u4ed6\u662f MANAGER&#xff0c;\u88ab\u7b2c\u4e8c\u4e2a\u67e5\u8be2\u635e\u8fdb\u6765\u4e86\u3002 \u8fd9\u6b63\u662f\u8fd9\u9053\u9898\u7684\u76ee\u7684\u2014\u2014\u7528 union \u8868\u8fbe\u300c\u6216\u300d\u7684\u903b\u8f91&#xff0c;\u4f46\u6bd4 where sal &gt; 2500 or job &#061; &#039;MANAGER&#039; \u66f4\u7075\u6d3b&#xff08;\u6bcf\u6bb5\u53ef\u4ee5\u6709\u81ea\u5df1\u7684\u8868\u3001\u81ea\u5df1\u7684\u6392\u5e8f&#xff09;\u3002<\/p>\n<h4 id=\"62-union-all\uff1a\u5e76\u96c6--\u4fdd\u7559\u91cd\u590d\">6.2 union all&#xff1a;\u5e76\u96c6 &#043; \u4fdd\u7559\u91cd\u590d<\/h4>\n<p>select ename, sal, job from EMP where sal &gt; 2500<br \/>\nunion all<br \/>\nselect ename, sal, job from EMP where job &#061; &#039;MANAGER&#039;;<\/p>\n<p>\u7ed3\u679c&#xff08;8 \u884c&#xff09;&#xff1a;<\/p>\n<p>&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8211;&#043;<br \/>\n| ename | sal     | job       |<br \/>\n&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8211;&#043;<br \/>\n| JONES | 2975.00 | MANAGER   |<br \/>\n| BLAKE | 2850.00 | MANAGER   |<br \/>\n| SCOTT | 3000.00 | ANALYST   |<br \/>\n| KING  | 5000.00 | PRESIDENT |<br \/>\n| FORD  | 3000.00 | ANALYST   |<br \/>\n| JONES | 2975.00 | MANAGER   |   \u2190 \u91cd\u590d<br \/>\n| BLAKE | 2850.00 | MANAGER   |   \u2190 \u91cd\u590d<br \/>\n| CLARK | 2450.00 | MANAGER   |<br \/>\n&#043;&#8212;&#8212;-&#043;&#8212;&#8212;&#8212;&#043;&#8212;&#8212;&#8212;&#8211;&#043;<\/p>\n<p>8 \u884c&#xff0c;\u91cd\u590d\u7684 JONES \u548c BLAKE \u90fd\u4fdd\u7559\u4e86\u3002<\/p>\n<h4 id=\"63-\u3010\u8865\u5168\u3011union-\u7684-6-\u4e2a\u786c\u89c4\u5219\uff08\u8bfe\u4ef6\u6ca1\u8bb2\uff0c\u4f46\u4e00\u5b9a\u4f1a\u8e29\uff09\">6.3 \u3010\u8865\u5168\u3011union \u7684 6 \u4e2a\u786c\u89c4\u5219&#xff08;\u8bfe\u4ef6\u6ca1\u8bb2&#xff0c;\u4f46\u4e00\u5b9a\u4f1a\u8e29&#xff09;<\/h4>\n<p>\u89c4\u5219 1&#xff1a;\u4e24\u4e2a\u67e5\u8be2\u7684\u5217\u6570\u5fc5\u987b\u76f8\u540c<\/p>\n<p>&#8212; \u274c ERROR 1222: The used SELECT statements have a different number of columns<br \/>\nselect empno, ename from emp<br \/>\nunion<br \/>\nselect deptno from dept;<\/p>\n<p>\u89c4\u5219 2&#xff1a;\u5217\u7c7b\u578b\u8981\u517c\u5bb9&#xff0c;\u6700\u7ec8\u4ee5\u300c\u7b2c\u4e00\u4e2a\u67e5\u8be2\u300d\u7684\u5217\u7c7b\u578b\u4e3a\u51c6<\/p>\n<p>select 1, &#039;a&#039;<br \/>\nunion<br \/>\nselect &#039;2&#039;, &#039;b&#039;;<br \/>\n&#8212; \u7ed3\u679c\u91cc &#039;2&#039; \u4f1a\u53d8\u6210 2&#xff08;\u6309\u7b2c\u4e00\u4e2a\u67e5\u8be2\u7684 int \u7c7b\u578b\u8f6c\u6362&#xff09;<\/p>\n<p>\u89c4\u5219 3&#xff1a;\u7ed3\u679c\u96c6\u7684\u5217\u540d\u53d6\u300c\u7b2c\u4e00\u4e2a\u67e5\u8be2\u300d\u7684\u5217\u540d<\/p>\n<p>select ename as n1 from emp where empno &#061; 7369<br \/>\nunion<br \/>\nselect dname as n2 from dept where deptno &#061; 10;<br \/>\n&#8212; \u7ed3\u679c\u5217\u540d\u662f n1&#xff0c;\u4e0d\u662f n2<\/p>\n<p>\u89c4\u5219 4&#xff1a;order by \u53ea\u80fd\u5199\u5728\u6700\u540e&#xff0c;\u4e14\u4f5c\u7528\u4e8e\u6574\u4e2a\u5408\u5e76\u7ed3\u679c<\/p>\n<p>&#8212; \u274c \u62a5\u9519<br \/>\nselect * from emp order by sal<br \/>\nunion<br \/>\nselect * from emp order by sal;<\/p>\n<p>&#8212; \u2705 \u6b63\u786e<br \/>\nselect * from emp where deptno &#061; 10<br \/>\nunion<br \/>\nselect * from emp where deptno &#061; 20<br \/>\norder by sal desc;<\/p>\n<p>\u89c4\u5219 5&#xff1a;\u5355\u4e2a select \u91cc\u7684 order by \/ limit \u8981\u7528\u62ec\u53f7\u5305\u8d77\u6765<\/p>\n<p>(select * from emp order by sal desc limit 3)<br \/>\nunion all<br \/>\n(select * from emp order by sal asc limit 3);<\/p>\n<p>\u26a0\ufe0f \u4f46\u8981\u6ce8\u610f&#xff1a;\u8fd9\u79cd\u5199\u6cd5\u91cc&#xff0c;\u5b50\u53e5\u7684 order by &#043; limit \u5fc5\u987b\u6210\u5957\u51fa\u73b0&#xff0c;\u5426\u5219 MySQL \u53ef\u80fd\u5ffd\u7565 order by&#xff08;\u56e0\u4e3a\u300c\u6ca1 limit \u7684 order by \u5728\u5b50\u67e5\u8be2\u91cc\u65e0\u610f\u4e49\u300d&#xff09;\u3002<\/p>\n<p>\u89c4\u5219 6&#xff1a;union \u4f1a\u9690\u5f0f\u6392\u5e8f&#xff08;\u53bb\u91cd\u7684\u4ee3\u4ef7&#xff09;&#xff0c;union all \u4e0d\u4f1a<\/p>\n<table>\n<tr>\u64cd\u4f5c\u53bb\u91cd\u662f\u5426\u9690\u5f0f\u6392\u5e8f\u6027\u80fd<\/tr>\n<tbody>\n<tr>\n<td>union all<\/td>\n<td>\u274c<\/td>\n<td>\u274c<\/td>\n<td>&#x1f680; \u5feb&#xff08;\u76f4\u63a5\u62fc\u63a5&#xff09;<\/td>\n<\/tr>\n<tr>\n<td>union<\/td>\n<td>\u2705<\/td>\n<td>\u2705&#xff08;\u4e3a\u4e86\u53bb\u91cd&#xff09;<\/td>\n<td>&#x1f422; \u6162&#xff08;\u8981\u5efa\u4e34\u65f6\u8868 &#043; \u6392\u5e8f&#xff09;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u26a0\ufe0f \u8fd9\u662f\u6700\u91cd\u8981\u7684\u4e00\u6761&#xff1a;union \u6bd4 union all \u6162&#xff0c;\u56e0\u4e3a\u5b83\u8981\u505a\u300c\u53bb\u91cd\u300d\u2014\u2014\u800c MySQL \u7684\u53bb\u91cd\u5b9e\u73b0\u65b9\u5f0f\u662f\u300c\u6392\u5e8f\u540e\u76f8\u90bb\u6bd4\u8f83\u300d\u6216\u300c\u5efa\u54c8\u5e0c\u8868\u300d&#xff0c;\u65e0\u8bba\u54ea\u79cd\u90fd\u8981\u989d\u5916\u5f00\u9500\u3002<\/p>\n<p>\u6240\u4ee5&#xff1a;\u5982\u679c\u786e\u5b9a\u4e24\u4e2a\u7ed3\u679c\u96c6\u4e0d\u4f1a\u91cd\u590d&#xff0c;\u6c38\u8fdc\u7528 union all\u3002<\/p>\n<p>&#x1f517; \u524d\u540e\u7167\u5e94&#xff1a;\u8fd9\u548c\u7b2c 06 \u7bc7\u8bb2 DISTINCT \u7684\u4ee3\u4ef7\u662f\u540c\u4e00\u4e2a\u9053\u7406&#xff08;\u53bb\u91cd\u5fc5\u987b\u6392\u5e8f\/\u54c8\u5e0c&#xff09;\u3002union &#061; union all &#043; distinct\u3002<\/p>\n<h4 id=\"64-\u8865\u5168\uff1a\u4ec0\u4e48\u65f6\u5019\u5fc5\u987b\u7528-union\uff0c\u800c\u4e0d\u80fd\u7528-or\uff1f\">6.4 \u8865\u5168&#xff1a;\u4ec0\u4e48\u65f6\u5019\u5fc5\u987b\u7528 union&#xff0c;\u800c\u4e0d\u80fd\u7528 or&#xff1f;<\/h4>\n<p>\u6709\u4e09\u79cd\u573a\u666f&#xff0c;or \u8868\u8fbe\u4e0d\u4e86&#xff0c;\u53ea\u80fd\u7528 union&#xff1a;<\/p>\n<p>\u2460 \u4e24\u4e2a\u67e5\u8be2\u6765\u81ea\u4e0d\u540c\u7684\u8868<\/p>\n<p>select name, &#039;emp&#039; as src from emp where sal &gt; 3000<br \/>\nunion all<br \/>\nselect dname, &#039;dept&#039; as src from dept where loc &#061; &#039;BOSTON&#039;;<\/p>\n<p>\u2461 \u4e24\u4e2a\u67e5\u8be2\u9700\u8981\u5404\u81ea\u7684\u6392\u5e8f\u548c\u6761\u6570<\/p>\n<p>&#8212; \u300c\u5404\u79d1\u76ee\u524d 3 \u540d\u300d\u8fd9\u79cd\u9700\u6c42<br \/>\n(select * from score where subject &#061; &#039;chinese&#039; order by score desc limit 3)<br \/>\nunion all<br \/>\n(select * from score where subject &#061; &#039;math&#039; order by score desc limit 3);<\/p>\n<p>\u2462 \u4e24\u4e2a\u67e5\u8be2\u7684\u8fc7\u6ee4\u6761\u4ef6\u5dee\u5f02\u6781\u5927&#xff0c;or \u4f1a\u8ba9\u7d22\u5f15\u5931\u6548<\/p>\n<p>&#8212; \u274c \u7528 or&#xff1a;\u4e24\u5f20\u8868\u7684\u7d22\u5f15\u90fd\u53ef\u80fd\u7528\u4e0d\u4e0a<br \/>\nselect * from a join b on &#8230; where a.x &#061; 1 or b.y &#061; 2;<\/p>\n<p>&#8212; \u2705 \u7528 union all&#xff1a;\u5404\u81ea\u8d70\u5404\u81ea\u7d22\u5f15<br \/>\nselect * from a join b on &#8230; where a.x &#061; 1<br \/>\nunion all<br \/>\nselect * from a join b on &#8230; where b.y &#061; 2 and a.x &lt;&gt; 1;<\/p>\n<p>&#x1f4a1; union \u662f\u300cSQL \u91cc\u7684\u62c6\u89e3\u672f\u300d&#xff1a;\u628a\u4e00\u4e2a\u5927 or \u62c6\u6210\u4e24\u4e2a\u5404\u8d70\u7d22\u5f15\u7684\u5c0f\u67e5\u8be2&#xff0c;\u518d\u7528 union all \u62fc\u56de\u6765\u3002\u8fd9\u662f\u8001\u7a0b\u5e8f\u5458\u5e38\u7528\u7684\u4f18\u5316\u624b\u6cd5\u3002<\/p>\n<hr \/>\n<h3 id=\"7-\u8865\u5168\uff1a\u591a\u8868\u67e5\u8be2\u7684\u6267\u884c\u987a\u5e8f\u4e0e\u6027\u80fd\u601d\u7ef4\">7. \u8865\u5168&#xff1a;\u591a\u8868\u67e5\u8be2\u7684\u6267\u884c\u987a\u5e8f\u4e0e\u6027\u80fd\u601d\u7ef4<\/h3>\n<h4 id=\"71-\u591a\u8868\u67e5\u8be2\u7684\u6267\u884c\u987a\u5e8f\">7.1 \u591a\u8868\u67e5\u8be2\u7684\u6267\u884c\u987a\u5e8f<\/h4>\n<p>\u628a\u7b2c 06 \u7bc7\u90a3\u5f20\u5355\u8868\u6267\u884c\u987a\u5e8f\u56fe\u6269\u5c55\u4e00\u4e0b&#xff1a;<\/p>\n<p>\u2460 FROM \/ JOIN   \u2190 \u5148\u505a\u7b1b\u5361\u5c14\u79ef&#xff08;\u6216\u6309 JOIN \u6761\u4ef6\u914d\u5bf9&#xff09;<br \/>\n\u2461 ON            \u2190 \u5916\u8fde\u63a5\u7684\u884c\u5339\u914d\u6761\u4ef6<br \/>\n\u2462 WHERE         \u2190 \u9010\u884c\u8fc7\u6ee4<br \/>\n\u2463 GROUP BY<br \/>\n\u2464 HAVING<br \/>\n\u2465 SELECT<br \/>\n\u2466 DISTINCT<br \/>\n\u2467 ORDER BY<br \/>\n\u2468 LIMIT<\/p>\n<p>\u6ce8\u610f FROM \u548c ON \u5728\u6700\u524d\u9762\u2014\u2014\u8fd9\u89e3\u91ca\u4e86\u4e3a\u4ec0\u4e48\u300c\u8fde\u63a5\u6761\u4ef6\u300d\u8981\u5199\u5728 ON \u91cc\u800c\u4e0d\u662f WHERE \u91cc&#xff08;\u9664\u4e86\u5916\u8fde\u63a5\u8bed\u4e49\u4e0d\u540c\u4e4b\u5916&#xff0c;ON \u66f4\u65e9\u6267\u884c&#xff09;\u3002<\/p>\n<h4 id=\"72-\u4e09\u8868\u8fde\u63a5\u600e\u4e48\u5199\uff1f\">7.2 \u4e09\u8868\u8fde\u63a5\u600e\u4e48\u5199&#xff1f;<\/h4>\n<p>\u4ece\u5de6\u5230\u53f3&#xff0c;\u4e00\u5c42\u5c42\u62fc&#xff1a;<\/p>\n<p>&#8212; \u9700\u6c42&#xff1a;\u663e\u793a\u5458\u5de5\u540d\u3001\u90e8\u95e8\u540d\u3001\u5de5\u8d44\u7b49\u7ea7<br \/>\nselect e.ename, d.dname, s.grade<br \/>\nfrom emp e<br \/>\njoin dept d on e.deptno &#061; d.deptno<br \/>\njoin salgrade s on e.sal between s.losal and s.hisal;<\/p>\n<p>\u6267\u884c\u8fc7\u7a0b&#xff1a;<\/p>\n<p>\u2460 emp join dept      \u2192 14 \u884c<br \/>\n\u2461 \u7ed3\u679c join salgrade \u2192 14 \u884c<\/p>\n<p>\u26a0\ufe0f \u987a\u5e8f\u5f88\u91cd\u8981&#xff1a;emp \u5148\u548c dept \u8fde&#xff08;14 \u884c&#xff09;&#xff0c;\u518d\u548c salgrade \u8fde&#xff08;\u8fd8\u662f 14 \u884c&#xff09;\u3002\u5982\u679c\u987a\u5e8f\u53cd\u4e86&#xff08;emp join salgrade \u5148\u8fde&#xff09;&#xff0c;\u4e2d\u95f4\u7ed3\u679c\u4e5f\u662f 14 \u884c\u3002\u5c0f\u8868\u4f18\u5148\u3001\u8fc7\u6ee4\u6027\u5f3a\u7684\u5148\u8fde&#xff0c;\u662f\u4f18\u5316\u7684\u57fa\u672c\u539f\u5219\u3002<\/p>\n<h4 id=\"73-\u6027\u80fd\u4e09\u6761\u94c1\u5f8b\">7.3 \u6027\u80fd\u4e09\u6761\u94c1\u5f8b<\/h4>\n<p>\u94c1\u5f8b\u4e00&#xff1a;\u8fde\u63a5\u6761\u4ef6\u7684\u5b57\u6bb5\u5fc5\u987b\u6709\u7d22\u5f15<\/p>\n<p>&#8212; emp.deptno \u4e0a\u6709\u7d22\u5f15&#xff0c;\u8fde\u63a5\u624d\u4f1a\u5feb<br \/>\nalter table emp add index idx_deptno (deptno);<\/p>\n<p>\u6ca1\u6709\u7d22\u5f15\u4f1a\u600e\u6837&#xff1f; MySQL \u4f1a\u7528\u300c\u5d4c\u5957\u5faa\u73af\u8fde\u63a5&#xff08;Nested-Loop Join&#xff09;\u300d&#xff0c;\u5916\u5c42\u6bcf\u4e00\u884c\u90fd\u8981\u53bb\u5185\u5c42\u5168\u8868\u626b\u4e00\u904d&#xff1a;<\/p>\n<p>14 \u884c&#xff08;emp&#xff09;\u00d7 \u5168\u8868\u626b dept&#xff08;4 \u884c&#xff09;&#061; 56 \u6b21\u6bd4\u8f83  \u2190 \u5c0f\u8868\u6ca1\u4e8b<br \/>\n100 \u4e07\u884c \u00d7 \u5168\u8868\u626b 100 \u4e07\u884c &#061; \u4e00\u4e07\u4ebf\u6b21\u6bd4\u8f83     \u2190 \u76f4\u63a5\u6b7b<\/p>\n<p>\u94c1\u5f8b\u4e8c&#xff1a;\u5c0f\u8868\u9a71\u52a8\u5927\u8868<\/p>\n<p>&#8212; \u5047\u8bbe dept 4 \u884c&#xff0c;emp 100 \u4e07\u884c<br \/>\nselect * from dept d join emp e on d.deptno &#061; e.deptno;<br \/>\n&#8212; \u5916\u5c42 4 \u884c&#xff0c;\u6bcf\u884c\u53bb emp \u7684\u7d22\u5f15\u91cc\u627e \u2192 4 \u6b21\u7d22\u5f15\u67e5\u627e<\/p>\n<p>\u4f18\u5316\u5668\u901a\u5e38\u4f1a\u81ea\u52a8\u9009\u5c0f\u8868\u505a\u300c\u9a71\u52a8\u8868\u300d&#xff0c;\u4f46\u4f60\u53ef\u4ee5\u7528 STRAIGHT_JOIN \u5f3a\u5236\u6307\u5b9a\u3002<\/p>\n<p>\u94c1\u5f8b\u4e09&#xff1a;\u80fd\u65e9\u8fc7\u6ee4\u5c31\u65e9\u8fc7\u6ee4<\/p>\n<p>&#8212; \u274c \u5148\u8fde\u63a5 100 \u4e07\u884c&#xff0c;\u518d\u7b5b 10 \u884c<br \/>\nselect * from emp e join dept d on e.deptno &#061; d.deptno<br \/>\nwhere d.dname &#061; &#039;SALES&#039; and e.sal &gt; 3000;<\/p>\n<p>&#8212; \u2705 \u4f18\u5316\u5668\u4e00\u822c\u4f1a\u91cd\u6392&#xff0c;\u4f46\u663e\u5f0f\u5199\u6e05\u695a\u66f4\u4fdd\u9669<br \/>\nselect * from<br \/>\n  (select * from emp where sal &gt; 3000) e<br \/>\n  join<br \/>\n  (select * from dept where dname &#061; &#039;SALES&#039;) d<br \/>\n  on e.deptno &#061; d.deptno;<\/p>\n<p>&#x1f517; \u524d\u540e\u7167\u5e94&#xff1a;\u7b2c 07 \u7bc7 6.3 \u8282\u8bb2\u7684\u300c\u8ba9\u5b57\u6bb5\u88f8\u5954\u300d\u539f\u5219&#xff0c;\u5728\u591a\u8868\u67e5\u8be2\u91cc\u540c\u6837\u9002\u7528\u2014\u2014on e.deptno &#061; d.deptno \u91cc\u4e24\u8fb9\u90fd\u662f\u88f8\u5b57\u6bb5&#xff0c;\u7d22\u5f15\u624d\u80fd\u7528\u3002<\/p>\n<h4 id=\"74-\u8865\u5168\uff1a\u5b50\u67e5\u8be2-vs-join\uff0c\u8c01\u66f4\u5feb\uff1f\">7.4 \u8865\u5168&#xff1a;\u5b50\u67e5\u8be2 vs JOIN&#xff0c;\u8c01\u66f4\u5feb&#xff1f;<\/h4>\n<p>\u8fd9\u662f\u9762\u8bd5\u9ad8\u9891\u9898&#xff0c;\u4f46\u6ca1\u6709\u7edd\u5bf9\u7b54\u6848\u3002<\/p>\n<table>\n<tr>\u573a\u666f\u63a8\u8350\u539f\u56e0<\/tr>\n<tbody>\n<tr>\n<td>\u53ea\u9700\u8981\u300c\u662f\u5426\u5b58\u5728\u300d<\/td>\n<td>exists<\/td>\n<td>\u627e\u5230\u4e00\u4e2a\u5c31\u505c<\/td>\n<\/tr>\n<tr>\n<td>\u9700\u8981\u5b50\u67e5\u8be2\u7684\u591a\u4e2a\u5217<\/td>\n<td>JOIN<\/td>\n<td>\u5b50\u67e5\u8be2\u53ea\u80fd\u8fd4\u56de\u4e00\u5217&#xff08;\u9664\u884c\u5b50\u67e5\u8be2&#xff09;<\/td>\n<\/tr>\n<tr>\n<td>\u5b50\u67e5\u8be2\u7ed3\u679c\u5f88\u5c0f<\/td>\n<td>\u5b50\u67e5\u8be2<\/td>\n<td>\u5148\u7b97\u51fa\u5c0f\u7ed3\u679c\u96c6&#xff0c;\u518d\u5339\u914d<\/td>\n<\/tr>\n<tr>\n<td>\u5b50\u67e5\u8be2\u7ed3\u679c\u5f88\u5927<\/td>\n<td>JOIN<\/td>\n<td>\u5b50\u67e5\u8be2\u7269\u5316\u5f00\u9500\u5927<\/td>\n<\/tr>\n<tr>\n<td>\u9700\u8981\u4fdd\u7559\u672a\u5339\u914d\u7684\u884c<\/td>\n<td>LEFT JOIN<\/td>\n<td>\u5b50\u67e5\u8be2\u505a\u4e0d\u5230&#xff08;not in \u6709 NULL \u5751&#xff09;<\/td>\n<\/tr>\n<tr>\n<td>\u53ea\u9700\u8981\u805a\u5408\u503c&#xff08;max\/avg&#xff09;<\/td>\n<td>\u5b50\u67e5\u8be2<\/td>\n<td>\u8bed\u4e49\u66f4\u76f4\u63a5<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u73b0\u4ee3 MySQL \u4f18\u5316\u5668&#xff08;5.7&#043;&#xff09;\u4f1a\u628a\u5f88\u591a\u5b50\u67e5\u8be2\u300c\u6539\u5199\u300d\u6210 JOIN&#xff08;\u8fd9\u53eb subquery materialization \/ semi-join \u4f18\u5316&#xff09;\u3002\u6240\u4ee5&#xff1a;<\/p>\n<p>\u4f18\u5148\u8003\u8651\u300c\u54ea\u4e2a\u5199\u6cd5\u66f4\u6e05\u6670\u300d&#xff0c;\u518d\u770b EXPLAIN \u51b3\u5b9a\u8981\u4e0d\u8981\u6539\u3002 \u4e0d\u8981\u4e3a\u4e86\u300c\u7406\u8bba\u4e0a\u66f4\u5feb\u300d\u628a SQL \u5199\u6210\u4eba\u7c7b\u8bfb\u4e0d\u61c2\u7684\u6837\u5b50\u3002<\/p>\n<hr \/>\n<h3 id=\"8-\u5b9e\u6218-oj\">8. \u5b9e\u6218 OJ<\/h3>\n<h4 id=\"81-\u67e5\u627e\u6240\u6709\u5458\u5de5\u5165\u804c\u65f6\u7684\u85aa\u6c34\u60c5\u51b5\">8.1 \u67e5\u627e\u6240\u6709\u5458\u5de5\u5165\u804c\u65f6\u7684\u85aa\u6c34\u60c5\u51b5<\/h4>\n<p>\u7ed9\u51fa emp_no \u4ee5\u53ca salary&#xff0c;\u5e76\u6309\u7167 emp_no \u8fdb\u884c\u9006\u5e8f\u3002<\/p>\n<p>select e.emp_no, s.salary<br \/>\nfrom employees e<br \/>\njoin salaries s on e.emp_no &#061; s.emp_no<br \/>\nwhere e.hire_date &#061; s.from_date<br \/>\norder by e.emp_no desc;<\/p>\n<p>\u8003\u70b9&#xff1a;\u591a\u8868\u8fde\u63a5 &#043; \u65f6\u95f4\u5b57\u6bb5\u7684\u7b49\u503c\u5339\u914d\u3002from_date \u8868\u793a\u300c\u8be5\u85aa\u8d44\u4ece\u54ea\u5929\u5f00\u59cb\u751f\u6548\u300d&#xff0c;\u5165\u804c\u90a3\u5929\u7684\u85aa\u8d44\u5c31\u662f from_date &#061; hire_date \u7684\u90a3\u6761\u8bb0\u5f55\u3002<\/p>\n<h4 id=\"82-\u9488\u5bf9\u5e93\u4e2d\u7684\u6240\u6709\u8868\uff0c\u751f\u6210-select-count-from-tablename\">8.2 \u9488\u5bf9\u5e93\u4e2d\u7684\u6240\u6709\u8868&#xff0c;\u751f\u6210 select count(*) from tableName<\/h4>\n<p>select concat(&#039;select count(*) from &#039;, table_name, &#039;;&#039;) as sql_stmt<br \/>\nfrom information_schema.tables<br \/>\nwhere table_schema &#061; &#039;\u4f60\u7684\u5e93\u540d&#039; and table_type &#061; &#039;BASE TABLE&#039;;<\/p>\n<p>\u8fd9\u9898\u975e\u5e38\u7ecf\u5178&#xff0c;\u8003\u70b9\u6709\u4e09\u5c42&#xff1a;<\/p>\n<p>\u2460 information_schema.tables&#xff1a;MySQL \u7684\u300c\u5143\u6570\u636e\u8868\u300d&#xff0c;\u5b58\u7740\u6240\u6709\u5e93\u3001\u6240\u6709\u8868\u7684\u4fe1\u606f\u3002\u8fd9\u662f\u300c\u7528 SQL \u751f\u6210 SQL\u300d\u7684\u5173\u952e\u3002<\/p>\n<p>\u2461 concat \u62fc\u63a5\u5b57\u7b26\u4e32&#xff08;\u7b2c 07 \u7bc7\u8bb2\u8fc7\u7684\u51fd\u6570&#xff09;\u3002<\/p>\n<p>\u2462 table_type &#061; &#039;BASE TABLE&#039; \u8fc7\u6ee4\u6389\u89c6\u56fe&#xff08;VIEW&#xff09;&#xff0c;\u5426\u5219\u4f1a\u751f\u6210\u5bf9\u89c6\u56fe\u7684 count \u2014\u2014 \u867d\u7136\u4e5f\u80fd\u8dd1&#xff0c;\u4f46\u901a\u5e38\u4e0d\u662f\u4f60\u8981\u7684\u3002<\/p>\n<p>\u8f93\u51fa\u7ed3\u679c\u5927\u6982\u957f\u8fd9\u6837&#xff1a;<\/p>\n<p>select count(*) from emp;<br \/>\nselect count(*) from dept;<br \/>\nselect count(*) from salgrade;<\/p>\n<p>\u7136\u540e\u4f60\u628a\u7ed3\u679c\u590d\u5236\u51fa\u6765\u6267\u884c\u4e00\u904d&#xff0c;\u5c31\u5f97\u5230\u6240\u6709\u8868\u7684\u884c\u6570\u7edf\u8ba1\u3002 \u8fd9\u5728\u300c\u6570\u636e\u8fc1\u79fb\u524d\u8bc4\u4f30\u6570\u636e\u91cf\u300d\u300c\u6392\u67e5\u7a7a\u8868\u300d\u65f6\u975e\u5e38\u5b9e\u7528\u3002<\/p>\n<h4 id=\"83-\u83b7\u53d6\u6240\u6709\u975e-manager-\u7684\u5458\u5de5-emp_no\">8.3 \u83b7\u53d6\u6240\u6709\u975e manager \u7684\u5458\u5de5 emp_no<\/h4>\n<p>&#8212; \u89e3\u6cd5\u4e00&#xff1a;not in<br \/>\nselect emp_no from employees<br \/>\nwhere emp_no not in (select emp_no from dept_manager);<\/p>\n<p>&#8212; \u89e3\u6cd5\u4e8c&#xff1a;left join &#043; is null&#xff08;\u63a8\u8350&#xff0c;\u6027\u80fd\u66f4\u597d&#xff09;<br \/>\nselect e.emp_no<br \/>\nfrom employees e<br \/>\nleft join dept_manager dm on e.emp_no &#061; dm.emp_no<br \/>\nwhere dm.emp_no is null;<\/p>\n<p>&#8212; \u89e3\u6cd5\u4e09&#xff1a;not exists<br \/>\nselect emp_no from employees e<br \/>\nwhere not exists (select 1 from dept_manager dm where dm.emp_no &#061; e.emp_no);<\/p>\n<p>\u26a0\ufe0f \u89e3\u6cd5\u4e00\u7684\u9690\u60a3&#xff1a;dept_manager.emp_no \u5982\u679c\u662f\u53ef\u7a7a\u7684\u4e14\u542b NULL&#xff0c;not in \u4f1a\u8fd4\u56de\u7a7a\u96c6\u3002<\/p>\n<p>&#x1f517; \u524d\u540e\u7167\u5e94&#xff1a;\u7b2c 06 \u7bc7 3.4.3 \u8282\u8bb2 NOT IN &#043; NULL \u7684\u5751&#xff0c;\u8fd9\u91cc\u662f\u5b9e\u6218\u3002dept_manager.emp_no \u901a\u5e38\u662f\u4e3b\u952e&#xff08;\u975e\u7a7a&#xff09;&#xff0c;\u6240\u4ee5\u5b89\u5168&#xff1b;\u4f46\u5982\u679c\u6362\u6210\u4e00\u4e2a\u53ef\u7a7a\u7684\u5217&#xff0c;\u89e3\u6cd5\u4e00\u7acb\u523b\u7ffb\u8f66\u3002<\/p>\n<p>\u6240\u4ee5\u751f\u4ea7\u73af\u5883\u6211\u66f4\u63a8\u8350\u89e3\u6cd5\u4e8c&#xff08;left join &#043; is null&#xff09;\u2014\u2014\u5b83\u5929\u7136\u514d\u75ab NULL \u95ee\u9898\u3002<\/p>\n<h4 id=\"84-\u83b7\u53d6\u6240\u6709\u5458\u5de5\u5f53\u524d\u7684-manager\uff08manager-\u662f\u81ea\u5df1\u5219\u4e0d\u663e\u793a\uff09\">8.4 \u83b7\u53d6\u6240\u6709\u5458\u5de5\u5f53\u524d\u7684 manager&#xff08;manager \u662f\u81ea\u5df1\u5219\u4e0d\u663e\u793a&#xff09;<\/h4>\n<p>select e.emp_no as \u5458\u5de5, m.emp_no as \u7ecf\u7406<br \/>\nfrom dept_emp e<br \/>\njoin dept_manager m on e.dept_no &#061; m.dept_no<br \/>\nwhere e.to_date &#061; &#039;9999-01-01&#039;<br \/>\n  and m.to_date &#061; &#039;9999-01-01&#039;<br \/>\n  and e.emp_no &lt;&gt; m.emp_no;<\/p>\n<p>\u8003\u70b9&#xff1a;<\/p>\n<p>\u2460 to_date &#061; &#039;9999-01-01&#039; \u662f\u8fd9\u5957\u9898\u8868\u7684\u300c\u5f53\u524d\u8bb0\u5f55\u300d\u6807\u8bb0&#xff08;\u5386\u53f2\u8bb0\u5f55\u4f1a\u6709\u7ed3\u675f\u65e5\u671f&#xff09;\u3002<\/p>\n<p>\u2461 e.emp_no &lt;&gt; m.emp_no \u6392\u9664\u300c\u81ea\u5df1\u7ba1\u81ea\u5df1\u300d\u7684\u60c5\u51b5\u3002<\/p>\n<p>\u2462 \u8fd9\u91cc\u5176\u5b9e\u9690\u542b\u4e86\u300c\u540c\u4e00\u90e8\u95e8\u300d\u7684\u8bed\u4e49\u2014\u2014\u300c\u5458\u5de5\u7684\u90e8\u95e8 &#061; \u7ecf\u7406\u7684\u90e8\u95e8\u300d&#xff0c;\u7528 e.dept_no &#061; m.dept_no \u8868\u8fbe\u3002<\/p>\n<h4 id=\"85-\u8865\u5168\uff1a\u518d\u6765\u4e09\u9053\u7ecf\u5178\u300c\u590d\u5408\u67e5\u8be2\u300d\u9898\">8.5 \u8865\u5168&#xff1a;\u518d\u6765\u4e09\u9053\u7ecf\u5178\u300c\u590d\u5408\u67e5\u8be2\u300d\u9898<\/h4>\n<p>\u2460 \u67e5\u51fa\u6bcf\u4e2a\u90e8\u95e8\u5de5\u8d44\u7b2c\u4e8c\u9ad8\u7684\u4eba&#xff08;\u4e0d\u7528\u7a97\u53e3\u51fd\u6570&#xff09;<\/p>\n<p>select e.deptno, e.ename, e.sal<br \/>\nfrom emp e<br \/>\nwhere 2 &#061; (select count(distinct e2.sal) from emp e2<br \/>\n           where e2.deptno &#061; e.deptno and e2.sal &gt; e.sal);<\/p>\n<p>\u8fd9\u662f\u4e00\u4e2a\u300c\u76f8\u5173\u5b50\u67e5\u8be2\u300d\u2014\u2014\u5bf9\u6bcf\u4e00\u884c e&#xff0c;\u53bb\u6570\u300c\u540c\u90e8\u95e8\u91cc\u6bd4\u6211\u5de5\u8d44\u9ad8\u7684\u4eba\u6709\u51e0\u4e2a\u300d&#xff0c;\u7b49\u4e8e 2 \u7684\u5c31\u662f\u7b2c\u4e09\u9ad8&#xff08;\u4ee5\u6b64\u7c7b\u63a8&#xff09;\u3002\u8fd9\u662f\u300c\u76f8\u5173\u5b50\u67e5\u8be2\u300d\u7684\u7ecf\u5178\u7528\u6cd5\u3002<\/p>\n<p>\u2461 \u67e5\u51fa\u6bcf\u4e2a\u90e8\u95e8\u4e2d\u300c\u5de5\u8d44\u5e76\u5217\u6700\u9ad8\u300d\u7684\u6240\u6709\u4eba<\/p>\n<p>select e.ename, e.sal, e.deptno<br \/>\nfrom emp e<br \/>\njoin (select deptno, max(sal) as ms from emp group by deptno) t<br \/>\n  on e.deptno &#061; t.deptno and e.sal &#061; t.ms;<\/p>\n<p>\u8fd9\u5c31\u662f 5.5.2 \u8282\u90a3\u4e2a\u6848\u4f8b&#xff0c;\u6362\u4e2a\u5199\u6cd5\u3002<\/p>\n<p>\u2462 \u67e5\u51fa\u300c\u81f3\u5c11\u6709\u4e00\u4e2a\u5458\u5de5\u300d\u7684\u90e8\u95e8\u7684\u6240\u6709\u4fe1\u606f<\/p>\n<p>select * from dept d<br \/>\nwhere exists (select 1 from emp e where e.deptno &#061; d.deptno);<\/p>\n<p>\u6ce8\u610f\u8fd9\u548c select distinct d.* from dept d join emp e on &#8230; \u7b49\u4ef7&#xff0c;\u4f46 exists \u4e0d\u4f1a\u4ea7\u751f\u91cd\u590d\u884c&#xff08;distinct \u624d\u8981&#xff09;\u3002<\/p>\n<hr \/>\n<h3 id=\"9-\u62a5\u9519\u901f\u67e5\">9. \u62a5\u9519\u901f\u67e5<\/h3>\n<table>\n<tr>\u9519\u8bef\u7801\u62a5\u9519\u6587\u672c\u539f\u56e0\u89e3\u51b3<\/tr>\n<tbody>\n<tr>\n<td>1052<\/td>\n<td>Column &#039;xxx&#039; in field list is ambiguous<\/td>\n<td>\u591a\u8868\u6709\u540c\u540d\u5217&#xff0c;\u6ca1\u52a0\u8868\u540d\u524d\u7f00<\/td>\n<td>\u5199 \u8868\u540d.\u5217\u540d<\/td>\n<\/tr>\n<tr>\n<td>1066<\/td>\n<td>Not unique table\/alias: &#039;emp&#039;<\/td>\n<td>\u81ea\u8fde\u63a5\u6ca1\u8d77\u522b\u540d \/ \u4e24\u4e2a\u522b\u540d\u649e\u4e86<\/td>\n<td>emp leader, emp worker<\/td>\n<\/tr>\n<tr>\n<td>1111<\/td>\n<td>Invalid use of group function<\/td>\n<td>WHERE \u91cc\u7528\u4e86\u805a\u5408\u51fd\u6570<\/td>\n<td>\u6539\u7528\u5b50\u67e5\u8be2\u6216 HAVING<\/td>\n<\/tr>\n<tr>\n<td>1241<\/td>\n<td>Operand should contain N column(s)<\/td>\n<td>\u884c\u5b50\u67e5\u8be2\u5217\u6570\u4e0d\u5339\u914d<\/td>\n<td>\u5bf9\u9f50 (a,b) \u7684\u5217\u6570<\/td>\n<\/tr>\n<tr>\n<td>1242<\/td>\n<td>Subquery returns more than 1 row<\/td>\n<td>\u5355\u884c\u5b50\u67e5\u8be2\u8fd4\u56de\u4e86\u591a\u884c<\/td>\n<td>\u6539 in \/ \u52a0 limit 1 \/ \u52a0\u66f4\u4e25\u7684\u6761\u4ef6<\/td>\n<\/tr>\n<tr>\n<td>1248<\/td>\n<td>Every derived table must have its own alias<\/td>\n<td>from (\u5b50\u67e5\u8be2) \u6ca1\u8d77\u522b\u540d<\/td>\n<td>from (&#8230;) tmp<\/td>\n<\/tr>\n<tr>\n<td>1222<\/td>\n<td>The used SELECT statements have a different number of columns<\/td>\n<td>union \u4e24\u8fb9\u5217\u6570\u4e0d\u4e00\u81f4<\/td>\n<td>\u8865\u5217&#xff08;select null \u5360\u4f4d&#xff09;<\/td>\n<\/tr>\n<tr>\n<td>1093<\/td>\n<td>You can&#039;t specify target table for update in FROM clause<\/td>\n<td>update\/delete \u7684\u5b50\u67e5\u8be2\u5f15\u7528\u4e86\u540c\u4e00\u5f20\u8868<\/td>\n<td>\u5957\u4e00\u5c42\u6d3e\u751f\u8868 (select &#8230; from t) tmp<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&#x1f517; 1248 \u8fd9\u4e2a\u62a5\u9519\u7279\u522b\u5e38\u89c1&#xff1a;from (select &#8230;) \u540e\u9762\u5fc5\u987b\u8ddf\u4e00\u4e2a\u522b\u540d&#xff08;\u54ea\u6015\u4f60\u538b\u6839\u4e0d\u7528\u5b83&#xff09;\u3002<\/p>\n<p> &#8212; \u274c ERROR 1248<br \/>\nselect * from (select deptno, avg(sal) from emp group by deptno);<\/p>\n<p>&#8212; \u2705<br \/>\nselect * from (select deptno, avg(sal) from emp group by deptno) tmp;<\/p>\n<hr \/>\n<h3 id=\"10-\u9762\u8bd5\u9ad8\u9891-12-\u95ee\">10. \u9762\u8bd5\u9ad8\u9891 12 \u95ee<\/h3>\n<p>Q1&#xff1a;\u7b1b\u5361\u5c14\u79ef\u662f\u4ec0\u4e48&#xff1f;\u600e\u4e48\u907f\u514d&#xff1f; \u4e24\u5f20\u8868\u7684\u6240\u6709\u884c\u505a\u300c\u4e24\u4e24\u914d\u5bf9\u300d\u7684\u7ed3\u679c&#xff0c;\u884c\u6570 &#061; \u4e24\u5f20\u8868\u884c\u6570\u4e4b\u79ef\u3002\u901a\u8fc7 where \/ on \u52a0\u8fde\u63a5\u6761\u4ef6\u6765\u8fc7\u6ee4\u6389\u4e0d\u5339\u914d\u7684\u7ec4\u5408\u3002<\/p>\n<p>Q2&#xff1a;from a, b where &#8230; \u548c a join b on &#8230; \u6709\u4ec0\u4e48\u533a\u522b&#xff1f; \u5b8c\u5168\u6ca1\u6709\u533a\u522b&#xff0c;\u524d\u8005\u53eb\u9690\u5f0f\u8fde\u63a5&#xff0c;\u540e\u8005\u53eb\u663e\u5f0f\u8fde\u63a5&#xff0c;\u4f18\u5316\u5668\u4f1a\u751f\u6210\u76f8\u540c\u8ba1\u5212\u3002\u533a\u522b\u53ea\u5728\u53ef\u8bfb\u6027&#xff08;join \u66f4\u597d&#xff09;\u548c\u300c\u5916\u8fde\u63a5\u65f6\u5fc5\u987b\u7528 join &#8230; on\u300d\u3002<\/p>\n<p>Q3&#xff1a;\u4ec0\u4e48\u662f\u81ea\u8fde\u63a5&#xff1f;\u4ec0\u4e48\u65f6\u5019\u5fc5\u987b\u7528&#xff1f; \u628a\u540c\u4e00\u5f20\u8868\u8d77\u4e24\u4e2a\u522b\u540d\u5f53\u4e24\u5f20\u8868\u7528\u3002\u5f53\u4f60\u9700\u8981\u300c\u540c\u4e00\u5f20\u8868\u5185\u7684\u4e24\u884c\u505a\u6bd4\u8f83\u300d\u6216\u300c\u5c42\u7ea7\u5173\u7cfb&#xff08;\u5458\u5de5-\u9886\u5bfc&#xff09;\u300d\u65f6\u5fc5\u987b\u7528\u3002<\/p>\n<p>Q4&#xff1a;in\u3001any\u3001all \u7684\u5173\u7cfb&#xff1f; &#061; any \u2261 in&#xff1b;&lt;&gt; all \u2261 not in&#xff1b;&gt; all \u2261 &gt; max()&#xff1b;&gt; any \u2261 &gt; min()\u3002<\/p>\n<p>Q5&#xff1a;exists \u548c in \u54ea\u4e2a\u5feb&#xff1f; \u4e0d\u7edd\u5bf9\u3002in \u9002\u5408\u300c\u5b50\u67e5\u8be2\u7ed3\u679c\u5c0f\u300d&#xff0c;exists \u9002\u5408\u300c\u5b50\u67e5\u8be2\u7ed3\u679c\u5927 \/ \u5916\u5c42\u5c0f\u300d\u3002\u73b0\u4ee3\u4f18\u5316\u5668\u5e38\u628a\u4e24\u8005\u4f18\u5316\u6210\u540c\u4e00\u8ba1\u5212&#xff0c;\u7528 EXPLAIN \u5224\u65ad\u3002<\/p>\n<p>Q6&#xff1a;not in \u4e3a\u4ec0\u4e48\u53ef\u80fd\u8fd4\u56de\u7a7a\u96c6&#xff1f;\u600e\u4e48\u907f\u514d&#xff1f; \u56e0\u4e3a x NOT IN (1, 2, NULL) \u5c55\u5f00\u540e\u542b x &lt;&gt; NULL&#xff0c;\u7ed3\u679c\u6052\u4e3a NULL&#xff08;\u975e TRUE&#xff09;\u3002\u89e3\u6cd5&#xff1a;\u5b50\u67e5\u8be2\u91cc\u52a0 where \u5217 is not null&#xff0c;\u6216\u6539\u7528 not exists\u3002<\/p>\n<p>Q7&#xff1a;from \u5b50\u53e5\u91cc\u7684\u5b50\u67e5\u8be2&#xff08;\u6d3e\u751f\u8868&#xff09;\u6709\u4ec0\u4e48\u9650\u5236&#xff1f; \u5fc5\u987b\u8d77\u522b\u540d&#xff08;\u5426\u5219 1248&#xff09;&#xff1b;MySQL 5.7 \u4f1a\u7269\u5316\u6210\u4e34\u65f6\u8868&#xff0c;8.0 \u6709 derived merge \u4f18\u5316&#xff08;\u80fd\u5408\u5e76\u7684\u76f4\u63a5\u5408\u5e76&#xff0c;\u4e0d\u80fd\u7684\u624d\u7269\u5316&#xff09;\u3002<\/p>\n<p>Q8&#xff1a;union \u548c union all \u7684\u533a\u522b&#xff1f; union \u53bb\u91cd&#xff08;\u9690\u542b\u6392\u5e8f\/\u54c8\u5e0c&#xff0c;\u6162&#xff09;&#xff0c;union all \u4e0d\u53bb\u91cd&#xff08;\u76f4\u63a5\u62fc\u63a5&#xff0c;\u5feb&#xff09;\u3002\u80fd\u786e\u5b9a\u4e0d\u91cd\u590d\u65f6\u6c38\u8fdc\u7528 union all\u3002<\/p>\n<p>Q9&#xff1a;union \u80fd\u7528 order by \u5417&#xff1f; \u80fd&#xff0c;\u4f46\u53ea\u80fd\u5199\u5728\u6700\u540e\u4e00\u4e2a select \u4e4b\u540e&#xff0c;\u4e14\u4f5c\u7528\u4e8e\u6574\u4e2a\u5408\u5e76\u7ed3\u679c\u3002\u8981\u5bf9\u5355\u4e2a\u5b50\u67e5\u8be2\u6392\u5e8f&#xff0c;\u9700\u7528\u62ec\u53f7\u5305\u8d77\u6765\u5e76\u914d limit\u3002<\/p>\n<p>Q10&#xff1a;\u591a\u5217\u5b50\u67e5\u8be2\u600e\u4e48\u7528&#xff1f; \u7528\u300c\u884c\u503c\u6784\u9020\u5668\u300d&#xff1a;where (a, b) &#061; (select x, y from t)\u3002\u6ce8\u610f\u5217\u6570\u5fc5\u987b\u4e00\u81f4&#xff0c;\u4e14\u53ea\u652f\u6301 &#061; \/ &lt;&gt; \u6bd4\u8f83\u3002<\/p>\n<p>Q11&#xff1a;\u600e\u4e48\u67e5\u300c\u6bcf\u4e2a\u90e8\u95e8\u5de5\u8d44\u9ad8\u4e8e\u672c\u90e8\u95e8\u5e73\u5747\u5de5\u8d44\u7684\u4eba\u300d&#xff1f; \u628a\u300c\u90e8\u95e8\u5e73\u5747\u5de5\u8d44\u300d\u505a\u6210\u6d3e\u751f\u8868&#xff08;select avg(sal), deptno from emp group by deptno&#xff09;&#xff0c;\u518d JOIN \u56de emp&#xff0c;\u6761\u4ef6 emp.sal &gt; tmp.avg AND emp.deptno &#061; tmp.deptno\u3002<\/p>\n<p>Q12&#xff1a;\u4e09\u8868\u8fde\u63a5\u600e\u4e48\u5199&#xff1f;\u6267\u884c\u987a\u5e8f\u662f\u600e\u6837\u7684&#xff1f; \u4ece\u5de6\u5230\u53f3\u9010\u5c42 JOIN&#xff1a;a join b on &#8230; join c on &#8230;\u3002\u6267\u884c\u65f6\u5148 a join b \u5f97\u5230\u4e2d\u95f4\u7ed3\u679c&#xff0c;\u518d\u7528\u8fd9\u4e2a\u7ed3\u679c\u53bb join c\u3002\u8fde\u63a5\u5b57\u6bb5\u5fc5\u987b\u6709\u7d22\u5f15&#xff0c;\u5c0f\u8868\u9a71\u52a8\u5927\u8868&#xff0c;\u80fd\u65e9\u8fc7\u6ee4\u5c31\u65e9\u8fc7\u6ee4\u3002<\/p>\n<hr \/>\n<h3 id=\"11-\u603b\u7ed3\uff1a\u4e00\u5f20\u56fe--\u4e00\u53e5\u53e3\u8bc0\">11. \u603b\u7ed3&#xff1a;\u4e00\u5f20\u56fe &#043; \u4e00\u53e5\u53e3\u8bc0<\/h3>\n<p>                    \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510<br \/>\n                    \u2502      \u590d\u5408\u67e5\u8be2\u7684\u56db\u79cd\u6b66\u5668         \u2502<br \/>\n                    \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<br \/>\n                                  \u2502<br \/>\n    \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510<br \/>\n    \u2502              \u2502              \u2502              \u2502                \u2502<br \/>\n \u3010\u591a\u8868\u67e5\u8be2\u3011   \u3010\u81ea\u8fde\u63a5\u3011     \u3010\u5b50\u67e5\u8be2\u3011     \u3010\u5408\u5e76\u67e5\u8be2\u3011      \u3010\u9690\u85cf boss\u3011<br \/>\n    \u2502              \u2502              \u2502              \u2502                \u2502<br \/>\n from a, b      \u540c\u4e00\u8868\u8d77       WHERE \u91cc      union           \u7b1b\u5361\u5c14\u79ef<br \/>\n join on        \u4e24\u4e2a\u522b\u540d       \u251c \u5355\u884c(&#061; max)  union all       &#061; \u884c\u6570\u4e4b\u79ef<br \/>\n where \u6761\u4ef6                    \u251c \u591a\u884c(in\/any\/all)<br \/>\n                               \u251c \u591a\u5217((a,b)&#061;(x,y))<br \/>\n                               \u2514 FROM \u91cc(\u6d3e\u751f\u8868)<\/p>\n<p>\u4e00\u53e5\u53e3\u8bc0&#xff1a;<\/p>\n<p>\u300c\u5148\u60f3\u7b1b\u5361\u5c14\u79ef&#xff0c;\u518d\u52a0\u8fde\u63a5\u6761\u4ef6&#xff1b;\u540c\u8868\u6bd4\u8f83\u8d77\u522b\u540d&#xff0c;\u805a\u5408\u7ed3\u679c\u505a\u4e34\u65f6\u8868\u3002\u300d<\/p>\n<p>\u5c55\u5f00&#xff1a;<\/p>\n<table>\n<tr>\u534a\u53e5\u542b\u4e49<\/tr>\n<tbody>\n<tr>\n<td>\u5148\u60f3\u7b1b\u5361\u5c14\u79ef<\/td>\n<td>\u4efb\u4f55\u591a\u8868\u67e5\u8be2&#xff0c;\u5148\u5728\u8111\u5b50\u91cc\u753b\u51fa\u300c\u884c\u6570\u4e4b\u79ef\u300d&#xff0c;\u4f60\u5c31\u77e5\u9053\u4e3a\u4ec0\u4e48\u5fc5\u987b\u52a0\u6761\u4ef6<\/td>\n<\/tr>\n<tr>\n<td>\u518d\u52a0\u8fde\u63a5\u6761\u4ef6<\/td>\n<td>where \/ on \u8d1f\u8d23\u4ece\u7b1b\u5361\u5c14\u79ef\u91cc\u7b5b\u51fa\u300c\u5bf9\u7684\u914d\u5bf9\u300d<\/td>\n<\/tr>\n<tr>\n<td>\u540c\u8868\u6bd4\u8f83\u8d77\u522b\u540d<\/td>\n<td>\u81ea\u8fde\u63a5\u5fc5\u987b emp leader, emp worker&#xff0c;\u5426\u5219\u62a5 1066<\/td>\n<\/tr>\n<tr>\n<td>\u805a\u5408\u7ed3\u679c\u505a\u4e34\u65f6\u8868<\/td>\n<td>\u300c\u9ad8\u4e8e\u90e8\u95e8\u5e73\u5747\u300d\u8fd9\u7c7b\u9700\u6c42&#xff0c;\u628a\u805a\u5408\u7ed3\u679c\u653e FROM \u91cc\u5f53\u5c0f\u8868&#xff0c;\u518d JOIN \u56de\u6765<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u6700\u540e&#xff0c;\u628a\u8fd9\u4e00\u7bc7\u548c\u524d\u4e09\u7bc7\u4e32\u8d77\u6765&#xff1a;<\/p>\n<table>\n<tr>\u7bc7\u4e3b\u9898\u4e00\u53e5\u8bdd<\/tr>\n<tbody>\n<tr>\n<td>05<\/td>\n<td>\u8868\u7684\u7ea6\u675f<\/td>\n<td>\u7ea6\u675f\u662f\u95e8\u7981&#xff0c;\u810f\u6570\u636e\u8fdb\u4e0d\u6765<\/td>\n<\/tr>\n<tr>\n<td>06<\/td>\n<td>\u589e\u5220\u6539\u67e5<\/td>\n<td>\u6267\u884c\u987a\u5e8f\u51b3\u5b9a\u4e00\u5207<\/td>\n<\/tr>\n<tr>\n<td>07<\/td>\n<td>\u5185\u7f6e\u51fd\u6570<\/td>\n<td>\u51fd\u6570\u522b\u78b0\u5b57\u6bb5&#xff0c;\u7d22\u5f15\u5c31\u6d3b\u4e86<\/td>\n<\/tr>\n<tr>\n<td>08<\/td>\n<td>\u590d\u5408\u67e5\u8be2<\/td>\n<td>\u591a\u8868 &#061; \u7b1b\u5361\u5c14\u79ef &#043; \u7b5b\u9009&#xff0c;\u805a\u5408\u7ed3\u679c\u505a\u4e34\u65f6\u8868<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u800c\u8fd9\u4e00\u7bc7\u57cb\u4e0b\u7684\u90a3\u9897\u300c40 \u53f7\u90e8\u95e8\u6d88\u5931\u4e86\u300d\u7684\u96f7&#xff0c;\u4e0b\u4e00\u7bc7\u5c31\u8981\u5f15\u7206&#xff1a;<\/p>\n<p>\u5185\u8fde\u63a5\u53ea\u4fdd\u7559\u300c\u4e24\u8fb9\u90fd\u5bf9\u5f97\u4e0a\u300d\u7684\u884c\u3002 \u8981\u7559\u4f4f\u90a3\u4e9b\u300c\u5bf9\u4e0d\u4e0a\u300d\u7684\u884c&#xff08;\u6bd4\u5982\u6ca1\u6709\u5458\u5de5\u7684\u90e8\u95e8\u3001\u6ca1\u6709\u6210\u7ee9\u7684\u5b66\u751f&#xff09;&#xff0c;\u4f60\u5fc5\u987b\u7528\u5916\u8fde\u63a5\u3002<\/p>\n<p>\u2014\u2014\u8fd9\u5c31\u662f\u300a\u5185\u8fde\u63a5\u4e0e\u5916\u8fde\u63a5\u300b\u8981\u89e3\u51b3\u7684\u95ee\u9898\u3002 \u6211\u4eec\u4e0b\u4e00\u7bc7\u89c1\u3002<\/p>\n<hr \/>\n<h3 id=\"\u9644\uff1a\u4e00\u952e\u53ef\u6267\u884c\u5b8c\u6574\u811a\u672c\">\u9644&#xff1a;\u4e00\u952e\u53ef\u6267\u884c\u5b8c\u6574\u811a\u672c<\/h3>\n<p>&#8212; &#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;<br \/>\n&#8212; MySQL \u590d\u5408\u67e5\u8be2 \u00b7 \u5168\u529f\u80fd\u6f14\u793a\u811a\u672c<br \/>\n&#8212; \u73af\u5883&#xff1a;MySQL 5.7 \/ 8.0 \u5747\u53ef<br \/>\n&#8212; &#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;&#061;<br \/>\ndrop database if exists multi_demo;<br \/>\ncreate database multi_demo default character set utf8mb4;<br \/>\nuse multi_demo;<\/p>\n<p>&#8212; &#8212;&#8212;&#8212;- \u5efa\u8868 &#8212;&#8212;&#8212;-<br \/>\ncreate table dept (<br \/>\n    deptno int primary key,<br \/>\n    dname  varchar(14),<br \/>\n    loc    varchar(13)<br \/>\n);<br \/>\ninsert into dept values<br \/>\n(10,&#039;ACCOUNTING&#039;,&#039;NEW YORK&#039;),(20,&#039;RESEARCH&#039;,&#039;DALLAS&#039;),<br \/>\n(30,&#039;SALES&#039;,&#039;CHICAGO&#039;),(40,&#039;OPERATIONS&#039;,&#039;BOSTON&#039;);<\/p>\n<p>create table emp (<br \/>\n    empno    int primary key,<br \/>\n    ename    varchar(10),<br \/>\n    job      varchar(9),<br \/>\n    mgr      int,<br \/>\n    hiredate date,<br \/>\n    sal      decimal(7,2),<br \/>\n    comm     decimal(7,2),<br \/>\n    deptno   int<br \/>\n);<br \/>\ninsert into emp values<br \/>\n(7369,&#039;SMITH&#039;,&#039;CLERK&#039;,7902,&#039;1980-12-17&#039;,800.00,NULL,20),<br \/>\n(7499,&#039;ALLEN&#039;,&#039;SALESMAN&#039;,7698,&#039;1981-02-20&#039;,1600.00,300.00,30),<br \/>\n(7521,&#039;WARD&#039;,&#039;SALESMAN&#039;,7698,&#039;1981-02-22&#039;,1250.00,500.00,30),<br \/>\n(7566,&#039;JONES&#039;,&#039;MANAGER&#039;,7839,&#039;1981-04-02&#039;,2975.00,NULL,20),<br \/>\n(7654,&#039;MARTIN&#039;,&#039;SALESMAN&#039;,7698,&#039;1981-09-28&#039;,1250.00,1400.00,30),<br \/>\n(7698,&#039;BLAKE&#039;,&#039;MANAGER&#039;,7839,&#039;1981-05-01&#039;,2850.00,NULL,30),<br \/>\n(7782,&#039;CLARK&#039;,&#039;MANAGER&#039;,7839,&#039;1981-06-09&#039;,2450.00,NULL,10),<br \/>\n(7788,&#039;SCOTT&#039;,&#039;ANALYST&#039;,7566,&#039;1987-04-19&#039;,3000.00,NULL,20),<br \/>\n(7839,&#039;KING&#039;,&#039;PRESIDENT&#039;,NULL,&#039;1981-11-17&#039;,5000.00,NULL,10),<br \/>\n(7844,&#039;TURNER&#039;,&#039;SALESMAN&#039;,7698,&#039;1981-09-08&#039;,1500.00,0.00,30),<br \/>\n(7876,&#039;ADAMS&#039;,&#039;CLERK&#039;,7788,&#039;1987-05-23&#039;,1100.00,NULL,20),<br \/>\n(7900,&#039;JAMES&#039;,&#039;CLERK&#039;,7698,&#039;1981-12-03&#039;,950.00,NULL,30),<br \/>\n(7902,&#039;FORD&#039;,&#039;ANALYST&#039;,7566,&#039;1981-12-03&#039;,3000.00,NULL,20),<br \/>\n(7934,&#039;MILLER&#039;,&#039;CLERK&#039;,7782,&#039;1982-01-23&#039;,1300.00,NULL,10);<\/p>\n<p>create table salgrade (grade int, losal int, hisal int);<br \/>\ninsert into salgrade values (1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);<\/p>\n<p>&#8212; &#8212;&#8212;&#8212;- 2. \u57fa\u672c\u67e5\u8be2\u56de\u987e &#8212;&#8212;&#8212;-<br \/>\nselect * from emp where (sal &gt; 500 or job &#061; &#039;MANAGER&#039;) and ename like &#039;J%&#039;;<br \/>\nselect * from emp order by deptno, sal desc;<br \/>\nselect ename, sal * 12 &#043; ifnull(comm, 0) as &#039;\u5e74\u85aa&#039; from emp order by \u5e74\u85aa desc;<br \/>\nselect ename, job from emp where sal &#061; (select max(sal) from emp);<br \/>\nselect ename, sal from emp where sal &gt; (select avg(sal) from emp);<br \/>\nselect deptno, format(avg(sal), 2), max(sal) from emp group by deptno;<br \/>\nselect deptno, avg(sal) as avg_sal from emp group by deptno having avg_sal &lt; 2000;<br \/>\nselect job, count(*), format(avg(sal), 2) from emp group by job;<\/p>\n<p>&#8212; &#8212;&#8212;&#8212;- 3. \u591a\u8868\u67e5\u8be2 &#8212;&#8212;&#8212;-<br \/>\nselect count(*) from emp, dept;                          &#8212; 56&#xff08;\u7b1b\u5361\u5c14\u79ef&#xff09;<br \/>\nselect EMP.ename, EMP.sal, DEPT.dname from EMP, DEPT where EMP.deptno &#061; DEPT.deptno;<br \/>\nselect ename, sal, dname from EMP, DEPT where EMP.deptno &#061; DEPT.deptno and DEPT.deptno &#061; 10;<br \/>\nselect ename, sal, grade from EMP, SALGRADE where EMP.sal between losal and hisal;<\/p>\n<p>&#8212; &#8212;&#8212;&#8212;- 4. \u81ea\u8fde\u63a5 &#8212;&#8212;&#8212;-<br \/>\n&#8212; \u5b50\u67e5\u8be2\u89e3\u6cd5<br \/>\nselect empno, ename from emp where emp.empno &#061; (select mgr from emp where ename &#061; &#039;FORD&#039;);<br \/>\n&#8212; \u81ea\u8fde\u63a5\u89e3\u6cd5<br \/>\nselect leader.empno, leader.ename<br \/>\nfrom emp leader, emp worker<br \/>\nwhere leader.empno &#061; worker.mgr and worker.ename &#061; &#039;FORD&#039;;<br \/>\n&#8212; \u591a\u7ea7&#xff1a;\u5458\u5de5-\u9886\u5bfc-\u9886\u5bfc\u7684\u9886\u5bfc<br \/>\nselect e1.ename as \u5458\u5de5, e2.ename as \u9886\u5bfc, e3.ename as \u4e0a\u7ea7<br \/>\nfrom emp e1<br \/>\nleft join emp e2 on e1.mgr &#061; e2.empno<br \/>\nleft join emp e3 on e2.mgr &#061; e3.empno;<\/p>\n<p>&#8212; &#8212;&#8212;&#8212;- 5. \u5b50\u67e5\u8be2 &#8212;&#8212;&#8212;-<br \/>\n&#8212; 5.1 \u5355\u884c<br \/>\nselect * from emp where deptno &#061; (select deptno from emp where ename &#061; &#039;SMITH&#039;);<br \/>\n&#8212; 5.2 \u591a\u884c&#xff1a;in \/ all \/ any<br \/>\nselect ename, job, sal, deptno from emp<br \/>\nwhere job in (select distinct job from emp where deptno &#061; 10) and deptno &lt;&gt; 10;<br \/>\nselect ename, sal, deptno from emp where sal &gt; all(select sal from emp where deptno &#061; 30);<br \/>\nselect ename, sal, deptno from emp where sal &gt; any(select sal from emp where deptno &#061; 30);<br \/>\n&#8212; 5.3 \u591a\u5217<br \/>\nselect ename from emp<br \/>\nwhere (deptno, job) &#061; (select deptno, job from emp where ename &#061; &#039;SMITH&#039;)<br \/>\n  and ename &lt;&gt; &#039;SMITH&#039;;<\/p>\n<p>&#8212; 5.4 FROM \u91cc\u7684\u5b50\u67e5\u8be2&#xff08;\u6d3e\u751f\u8868&#xff09;<br \/>\n&#8212; \u9ad8\u4e8e\u672c\u90e8\u95e8\u5e73\u5747\u5de5\u8d44<br \/>\nselect ename, deptno, sal, format(asal, 2)<br \/>\nfrom emp, (select avg(sal) asal, deptno dt from emp group by deptno) tmp<br \/>\nwhere emp.sal &gt; tmp.asal and emp.deptno &#061; tmp.dt;<\/p>\n<p>&#8212; \u6bcf\u4e2a\u90e8\u95e8\u5de5\u8d44\u6700\u9ad8\u7684\u4eba<br \/>\nselect emp.ename, emp.sal, emp.deptno, ms<br \/>\nfrom emp, (select max(sal) ms, deptno from emp group by deptno) tmp<br \/>\nwhere emp.deptno &#061; tmp.deptno and emp.sal &#061; tmp.ms;<\/p>\n<p>&#8212; \u90e8\u95e8\u4fe1\u606f &#043; \u4eba\u6570&#xff08;\u65b9\u6cd51&#xff1a;\u5185\u8fde\u63a5&#xff0c;\u4f1a\u4e22 40 \u53f7\u90e8\u95e8&#xff09;<br \/>\nselect DEPT.dname, DEPT.deptno, DEPT.loc, count(*) &#039;\u90e8\u95e8\u4eba\u6570&#039;<br \/>\nfrom EMP, DEPT<br \/>\nwhere EMP.deptno &#061; DEPT.deptno<br \/>\ngroup by DEPT.deptno, DEPT.dname, DEPT.loc;<\/p>\n<p>&#8212; \u90e8\u95e8\u4fe1\u606f &#043; \u4eba\u6570&#xff08;\u65b9\u6cd52&#xff1a;\u6d3e\u751f\u8868&#xff0c;\u540c\u6837\u4e22 40 \u53f7\u90e8\u95e8&#xff09;<br \/>\nselect DEPT.deptno, dname, mycnt, loc<br \/>\nfrom DEPT, (select count(*) mycnt, deptno from EMP group by deptno) tmp<br \/>\nwhere DEPT.deptno &#061; tmp.deptno;<\/p>\n<p>&#8212; \u2b50 \u6b63\u786e\u7b54\u6848&#xff1a;\u5916\u8fde\u63a5 &#043; count(\u5217)<br \/>\nselect d.deptno, d.dname, d.loc, count(e.empno) as \u90e8\u95e8\u4eba\u6570<br \/>\nfrom dept d left join emp e on d.deptno &#061; e.deptno<br \/>\ngroup by d.deptno, d.dname, d.loc;<\/p>\n<p>&#8212; 5.6 exists \/ not exists<br \/>\nselect * from emp e where exists (select 1 from emp w where w.mgr &#061; e.empno);<br \/>\nselect * from emp e where not exists (select 1 from emp w where w.mgr &#061; e.empno);<\/p>\n<p>&#8212; &#8212;&#8212;&#8212;- 6. \u5408\u5e76\u67e5\u8be2 &#8212;&#8212;&#8212;-<br \/>\nselect ename, sal, job from EMP where sal &gt; 2500<br \/>\nunion<br \/>\nselect ename, sal, job from EMP where job &#061; &#039;MANAGER&#039;;<\/p>\n<p>select ename, sal, job from EMP where sal &gt; 2500<br \/>\nunion all<br \/>\nselect ename, sal, job from EMP where job &#061; &#039;MANAGER&#039;;<\/p>\n<p>&#8212; union \u914d order by&#xff08;\u53ea\u80fd\u5199\u6700\u540e&#xff09;<br \/>\nselect ename, sal from emp where deptno &#061; 10<br \/>\nunion all<br \/>\nselect ename, sal from emp where deptno &#061; 20<br \/>\norder by sal desc;<\/p>\n<p>&#8212; &#8212;&#8212;&#8212;- 8. \u751f\u6210\u6240\u6709\u8868\u7684 count SQL &#8212;&#8212;&#8212;-<br \/>\nselect concat(&#039;select count(*) from &#039;, table_name, &#039;;&#039;) as sql_stmt<br \/>\nfrom information_schema.tables<br \/>\nwhere table_schema &#061; &#039;multi_demo&#039; and table_type &#061; &#039;BASE TABLE&#039;;<\/p>\n<p>\u5982\u679c\u8fd9\u7bc7\u535a\u5ba2\u5e2e\u4f60\u7406\u6e05\u4e86\u67d0\u4e2a\u70b9&#xff0c;\u6b22\u8fce\u70b9\u8d5e\u6536\u85cf&#xff1b;\u6709\u4efb\u4f55\u7591\u95ee&#xff0c;\u8bc4\u8bba\u533a\u89c1&#xff0c;\u6211\u4f1a\u4e00\u6761\u6761\u56de\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u76ee\u5f55<br \/>\n0. \u5f00\u7bc7&#xff1a;\u5355\u8868\u67e5\u8be2\u7684\u5929\u82b1\u677f1. \u5b9e\u9a8c\u73af\u5883&#xff1a;\u4e09\u5f20\u7ecf\u5178\u88682. \u57fa\u672c\u67e5\u8be2\u56de\u987e&#xff08;8 \u9053\u9898&#xff0c;\u6bcf\u9898\u4e00\u4e2a\u7ec6\u8282&#xff09;3. \u591a\u8868\u67e5\u8be2&#xff1a;\u4ece\u300c\u7b1b\u5361\u5c14\u79ef\u300d\u8bf4\u8d774. \u81ea\u8fde\u63a5&#xff1a;\u628a\u4e00\u5f20\u8868\u5f53\u6210\u4e24\u5f20\u75285. \u5b50\u67e5\u8be2&#xff1a;SQL \u91cc\u7684\u300c\u5957\u5a03\u300d6. \u5408\u5e76\u67e5\u8be2&#xff1a;UNION \u4e0e UNION ALL7. \u8865\u5168&#xff1a;\u591a\u8868\u67e5\u8be2\u7684\u6267\u884c\u987a\u5e8f\u4e0e\u6027\u80fd\u601d\u7ef48. \u5b9e\u6218 OJ9. \u62a5\u9519\u901f\u67e510. \u9762\u8bd5\u9ad8<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[604,100],"topic":[],"class_list":["post-119100","post","type-post","status-publish","format-standard","hentry","category-server","tag-oracle","tag-100"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v20.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>MySQL \u590d\u5408\u67e5\u8be2\uff1a\u591a\u8868 \/ \u81ea\u8fde\u63a5 \/ \u5b50\u67e5\u8be2 \/ UNION\uff0c\u4e00\u7bc7\u8bb2\u900f - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.wsisp.com\/helps\/119100.html\" \/>\n<meta property=\"og:locale\" content=\"zh_CN\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MySQL \u590d\u5408\u67e5\u8be2\uff1a\u591a\u8868 \/ \u81ea\u8fde\u63a5 \/ \u5b50\u67e5\u8be2 \/ UNION\uff0c\u4e00\u7bc7\u8bb2\u900f - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3\" \/>\n<meta property=\"og:description\" content=\"\u76ee\u5f55 0. \u5f00\u7bc7&#xff1a;\u5355\u8868\u67e5\u8be2\u7684\u5929\u82b1\u677f1. \u5b9e\u9a8c\u73af\u5883&#xff1a;\u4e09\u5f20\u7ecf\u5178\u88682. \u57fa\u672c\u67e5\u8be2\u56de\u987e&#xff08;8 \u9053\u9898&#xff0c;\u6bcf\u9898\u4e00\u4e2a\u7ec6\u8282&#xff09;3. \u591a\u8868\u67e5\u8be2&#xff1a;\u4ece\u300c\u7b1b\u5361\u5c14\u79ef\u300d\u8bf4\u8d774. \u81ea\u8fde\u63a5&#xff1a;\u628a\u4e00\u5f20\u8868\u5f53\u6210\u4e24\u5f20\u75285. \u5b50\u67e5\u8be2&#xff1a;SQL \u91cc\u7684\u300c\u5957\u5a03\u300d6. \u5408\u5e76\u67e5\u8be2&#xff1a;UNION \u4e0e UNION ALL7. \u8865\u5168&#xff1a;\u591a\u8868\u67e5\u8be2\u7684\u6267\u884c\u987a\u5e8f\u4e0e\u6027\u80fd\u601d\u7ef48. \u5b9e\u6218 OJ9. \u62a5\u9519\u901f\u67e510. \u9762\u8bd5\u9ad8\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.wsisp.com\/helps\/119100.html\" \/>\n<meta property=\"og:site_name\" content=\"\u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3\" \/>\n<meta property=\"article:published_time\" content=\"2026-10-28T14:40:31+00:00\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u4f5c\u8005\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"\u9884\u8ba1\u9605\u8bfb\u65f6\u95f4\" \/>\n\t<meta name=\"twitter:data2\" content=\"27 \u5206\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/119100.html\",\"url\":\"https:\/\/www.wsisp.com\/helps\/119100.html\",\"name\":\"MySQL \u590d\u5408\u67e5\u8be2\uff1a\u591a\u8868 \/ \u81ea\u8fde\u63a5 \/ \u5b50\u67e5\u8be2 \/ UNION\uff0c\u4e00\u7bc7\u8bb2\u900f - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3\",\"isPartOf\":{\"@id\":\"https:\/\/www.wsisp.com\/helps\/#website\"},\"datePublished\":\"2026-10-28T14:40:31+00:00\",\"dateModified\":\"2026-10-28T14:40:31+00:00\",\"author\":{\"@id\":\"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/358e386c577a3ab51c4493330a20ad41\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.wsisp.com\/helps\/119100.html#breadcrumb\"},\"inLanguage\":\"zh-Hans\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.wsisp.com\/helps\/119100.html\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/119100.html#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"\u9996\u9875\",\"item\":\"https:\/\/www.wsisp.com\/helps\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MySQL \u590d\u5408\u67e5\u8be2\uff1a\u591a\u8868 \/ \u81ea\u8fde\u63a5 \/ \u5b50\u67e5\u8be2 \/ UNION\uff0c\u4e00\u7bc7\u8bb2\u900f\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/#website\",\"url\":\"https:\/\/www.wsisp.com\/helps\/\",\"name\":\"\u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3\",\"description\":\"\u9999\u6e2f\u670d\u52a1\u5668_\u9999\u6e2f\u4e91\u670d\u52a1\u5668\u8d44\u8baf_\u670d\u52a1\u5668\u5e2e\u52a9\u6587\u6863_\u670d\u52a1\u5668\u6559\u7a0b\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.wsisp.com\/helps\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"zh-Hans\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/358e386c577a3ab51c4493330a20ad41\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"zh-Hans\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/gravatar.wp-china-yes.net\/avatar\/?s=96&d=mystery\",\"contentUrl\":\"https:\/\/gravatar.wp-china-yes.net\/avatar\/?s=96&d=mystery\",\"caption\":\"admin\"},\"sameAs\":[\"http:\/\/wp.wsisp.com\"],\"url\":\"https:\/\/www.wsisp.com\/helps\/author\/admin\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"MySQL \u590d\u5408\u67e5\u8be2\uff1a\u591a\u8868 \/ \u81ea\u8fde\u63a5 \/ \u5b50\u67e5\u8be2 \/ UNION\uff0c\u4e00\u7bc7\u8bb2\u900f - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.wsisp.com\/helps\/119100.html","og_locale":"zh_CN","og_type":"article","og_title":"MySQL \u590d\u5408\u67e5\u8be2\uff1a\u591a\u8868 \/ \u81ea\u8fde\u63a5 \/ \u5b50\u67e5\u8be2 \/ UNION\uff0c\u4e00\u7bc7\u8bb2\u900f - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","og_description":"\u76ee\u5f55 0. \u5f00\u7bc7&#xff1a;\u5355\u8868\u67e5\u8be2\u7684\u5929\u82b1\u677f1. \u5b9e\u9a8c\u73af\u5883&#xff1a;\u4e09\u5f20\u7ecf\u5178\u88682. \u57fa\u672c\u67e5\u8be2\u56de\u987e&#xff08;8 \u9053\u9898&#xff0c;\u6bcf\u9898\u4e00\u4e2a\u7ec6\u8282&#xff09;3. \u591a\u8868\u67e5\u8be2&#xff1a;\u4ece\u300c\u7b1b\u5361\u5c14\u79ef\u300d\u8bf4\u8d774. \u81ea\u8fde\u63a5&#xff1a;\u628a\u4e00\u5f20\u8868\u5f53\u6210\u4e24\u5f20\u75285. \u5b50\u67e5\u8be2&#xff1a;SQL \u91cc\u7684\u300c\u5957\u5a03\u300d6. \u5408\u5e76\u67e5\u8be2&#xff1a;UNION \u4e0e UNION ALL7. \u8865\u5168&#xff1a;\u591a\u8868\u67e5\u8be2\u7684\u6267\u884c\u987a\u5e8f\u4e0e\u6027\u80fd\u601d\u7ef48. \u5b9e\u6218 OJ9. \u62a5\u9519\u901f\u67e510. \u9762\u8bd5\u9ad8","og_url":"https:\/\/www.wsisp.com\/helps\/119100.html","og_site_name":"\u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","article_published_time":"2026-10-28T14:40:31+00:00","author":"admin","twitter_card":"summary_large_image","twitter_misc":{"\u4f5c\u8005":"admin","\u9884\u8ba1\u9605\u8bfb\u65f6\u95f4":"27 \u5206"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.wsisp.com\/helps\/119100.html","url":"https:\/\/www.wsisp.com\/helps\/119100.html","name":"MySQL \u590d\u5408\u67e5\u8be2\uff1a\u591a\u8868 \/ \u81ea\u8fde\u63a5 \/ \u5b50\u67e5\u8be2 \/ UNION\uff0c\u4e00\u7bc7\u8bb2\u900f - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","isPartOf":{"@id":"https:\/\/www.wsisp.com\/helps\/#website"},"datePublished":"2026-10-28T14:40:31+00:00","dateModified":"2026-10-28T14:40:31+00:00","author":{"@id":"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/358e386c577a3ab51c4493330a20ad41"},"breadcrumb":{"@id":"https:\/\/www.wsisp.com\/helps\/119100.html#breadcrumb"},"inLanguage":"zh-Hans","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.wsisp.com\/helps\/119100.html"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.wsisp.com\/helps\/119100.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"\u9996\u9875","item":"https:\/\/www.wsisp.com\/helps"},{"@type":"ListItem","position":2,"name":"MySQL \u590d\u5408\u67e5\u8be2\uff1a\u591a\u8868 \/ \u81ea\u8fde\u63a5 \/ \u5b50\u67e5\u8be2 \/ UNION\uff0c\u4e00\u7bc7\u8bb2\u900f"}]},{"@type":"WebSite","@id":"https:\/\/www.wsisp.com\/helps\/#website","url":"https:\/\/www.wsisp.com\/helps\/","name":"\u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","description":"\u9999\u6e2f\u670d\u52a1\u5668_\u9999\u6e2f\u4e91\u670d\u52a1\u5668\u8d44\u8baf_\u670d\u52a1\u5668\u5e2e\u52a9\u6587\u6863_\u670d\u52a1\u5668\u6559\u7a0b","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.wsisp.com\/helps\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"zh-Hans"},{"@type":"Person","@id":"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/358e386c577a3ab51c4493330a20ad41","name":"admin","image":{"@type":"ImageObject","inLanguage":"zh-Hans","@id":"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/image\/","url":"https:\/\/gravatar.wp-china-yes.net\/avatar\/?s=96&d=mystery","contentUrl":"https:\/\/gravatar.wp-china-yes.net\/avatar\/?s=96&d=mystery","caption":"admin"},"sameAs":["http:\/\/wp.wsisp.com"],"url":"https:\/\/www.wsisp.com\/helps\/author\/admin"}]}},"_links":{"self":[{"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/posts\/119100","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/comments?post=119100"}],"version-history":[{"count":0,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/posts\/119100\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/media?parent=119100"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/categories?post=119100"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/tags?post=119100"},{"taxonomy":"topic","embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/topic?post=119100"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}