{"id":290,"date":"2021-11-02T09:57:43","date_gmt":"2021-11-02T01:57:43","guid":{"rendered":"http:\/\/47.98.165.52\/?p=290"},"modified":"2021-11-02T09:57:43","modified_gmt":"2021-11-02T01:57:43","slug":"mysql-day-02","status":"publish","type":"post","link":"\/?p=290","title":{"rendered":"MySQL DAY.02"},"content":{"rendered":"

1.\u5173\u4e8e\u67e5\u8be2\u7ed3\u679c\u96c6\u7684\u53bb\u91cd\uff1f<\/h2>\n

\u5173\u952e\u5b57 distinct<\/strong> <\/p>\n

mysql> select distinct job from emp;\n+-----------+\n| job       |\n+-----------+\n| CLERK     |\n| SALESMAN  |\n| MANAGER   |\n| ANALYST   |\n| PRESIDENT |\n+-----------+<\/code><\/pre>\n
mysql> select ename,distinct job from emp;
\n\u4ee5\u4e0a\u7684sql\u8bed\u53e5\u662f\u9519\u8bef\u7684\u3002
\n\u8bb0\u4f4f\uff1adistinct\u53ea\u80fd\u51fa\u73b0\u5728\u6240\u6709\u5b57\u6bb5\u7684\u6700\u524d\u9762\u3002
\n\u5982\u4e0b\u6848\u4f8b\u6240\u793a\uff1a\u8868\u793a\u540e\u9762\u7684\u5b57\u6bb5\u8054\u5408\u8d77\u6765\u53bb\u9664\u91cd\u590d\u8bb0\u5f55\u3002<\/strong><\/p>\n
mysql> select distinct deptno,job from emp;\n+--------+-----------+\n| deptno | job       |\n+--------+-----------+\n|     20 | CLERK     |\n|     30 | SALESMAN  |\n|     20 | MANAGER   |\n|     30 | MANAGER   |\n|     10 | MANAGER   |\n|     20 | ANALYST   |\n|     10 | PRESIDENT |\n|     30 | CLERK     |\n|     10 | CLERK     |\n+--------+-----------+<\/code><\/pre>\n
\u6848\u4f8b\uff1a\u7edf\u8ba1\u5c97\u4f4d\u7684\u6570\u91cf\uff1f<\/p>\n
mysql>select count(distinct job) from emp;\n\n+---------------------+\n| count(distinct job) |\n+---------------------+\n|                   5 |\n+---------------------+<\/code><\/pre>\n
2.\u8fde\u63a5\u67e5\u8be2<\/h2>\n
2.1\u4ec0\u4e48\u662f\u8fde\u63a5\u67e5\u8be2\uff1f<\/h2>\n
\u5728\u5b9e\u9645\u5f00\u53d1\u4e2d\uff0c\u5927\u90e8\u5206\u60c5\u51b5\u4e0b\u90fd\u4e0d\u662f\u4ece\u5355\u8868\u4e2d\u67e5\u8be2\uff0c\u4e00\u822c\u90fd\u662f\u591a\u5f20\u8868\u8054\u5408\u67e5\u8be2\u53d6\u51fa\u6700\u7ec8\u7ed3\u679c\u3002
\n\u5728\u5b9e\u9645\u5f00\u53d1\u4e2d\uff0c\u4e00\u822c\u4e00\u4e2a\u4e1a\u52a1\u90fd\u4f1a\u5bf9\u5e94\u591a\u5f20\u8868\uff0c\u6bd4\u5982\uff1a\u5b66\u751f\u548c\u73ed\u7ea7\uff0c\u8d77\u7801\u4e24\u5f20\u8868\u3002
\n\u5728\u5b9e\u9645\u5f00\u53d1\u4e2d\uff0c\u5927\u90e8\u5206\u7684\u60c5\u51b5\u4e0b\u90fd\u4e0d\u662f\u4ece\u5355\u8868\u4e2d\u67e5\u8be2\u6570\u636e\uff0c\u4e00\u822c\u90fd\u662f\u591a\u5f20\u8868\u8054\u5408\u67e5\u8be2\u53d6\u51fa\u6700\u7ec8\u7684\u7ed3\u679c\u3002
\n\u5728\u5b9e\u9645\u5f00\u53d1\u4e2d\uff0c\u4e00\u822c\u4e00\u4e2a\u4e1a\u52a1\u90fd\u4f1a\u5bf9\u5e94\u591a\u5f20\u8868\uff0c\u6bd4\u5982\uff1a\u5b66\u751f\u548c\u73ed\u7ea7\uff0c\u8d77\u7801\u4e24\u5f20\u8868\u3002<\/p>\n
    stuno       stuname         classno     classname\n    -----------------------------------------------------------------------------------\n    1                zs                         1               \u5317\u4eac\u5927\u5174\u533a\u4ea6\u5e84\u7ecf\u6d4e\u6280\u672f\u5f00\u53d1\u533a\u7b2c\u4e8c\u4e2d\u5b66\u9ad8\u4e091\u73ed\n    2                ls                         1               \u5317\u4eac\u5927\u5174\u533a\u4ea6\u5e84\u7ecf\u6d4e\u6280\u672f\u5f00\u53d1\u533a\u7b2c\u4e8c\u4e2d\u5b66\u9ad8\u4e091\u73ed<\/code><\/pre>\n
\u5b66\u751f\u548c\u73ed\u7ea7\u4fe1\u606f\u5b58\u50a8\u5230\u4e00\u5f20\u8868\u4e2d\uff0c\u7ed3\u679c\u5c31\u50cf\u4e0a\u9762\u4e00\u6837\uff0c\u6570\u636e\u4f1a\u5b58\u5728\u5927\u91cf\u7684\u91cd\u590d\uff0c\u5bfc\u81f4\u6570\u636e\u7684\u5197\u4f59\u3002<\/p>\n
2.2\u8fde\u63a5\u67e5\u8be2\u7684\u5206\u7c7b<\/h2>\n
\u6839\u636e\u8bed\u6cd5\u51fa\u73b0\u7684\u5e74\u4ee3\u6765\u5212\u5206\u7684\u8bdd\uff0c\u5305\u62ec\uff1a
\nSQL92\uff08\u4e00\u4e9b\u8001\u7684DBA\u53ef\u80fd\u8fd8\u5728\u4f7f\u7528\u8fd9\u79cd\u8bed\u6cd5\u3002DBA\uff1aDataBase Administrator\uff0c\u6570\u636e\u5e93\u7ba1\u7406\u5458\uff09
\nSQL99\uff08\u6bd4\u8f83\u65b0\u7684\u8bed\u6cd5\uff09<\/p>\n
\u6839\u636e\u8868\u7684\u8fde\u63a5\u65b9\u5f0f\u6765\u5212\u5206\uff0c\u5305\u62ec\uff1a
\n\u5185\u8fde\u63a5<\/strong>\uff1a
\n\u7b49\u503c\u8fde\u63a5
\n\u975e\u7b49\u503c\u8fde\u63a5
\n\u81ea\u8fde\u63a5<\/em>
\n\u5916\u8fde\u63a5<\/strong>\uff1a
\n\u5de6\u5916\u8fde\u63a5\uff08\u5de6\u8fde\u63a5\uff09
\n\u53f3\u5916\u8fde\u63a5\uff08\u53f3\u8fde\u63a5\uff09<\/em>
\n\u5168\u8fde\u63a5<\/strong>\uff08\u8fd9\u4e2a\u4e0d\u8bb2\uff0c\u5f88\u5c11\u7528\uff01\uff09<\/p>\n
2.3\u5728\u8868\u7684\u8fde\u63a5\u67e5\u8be2\u65b9\u9762\u6709\u4e00\u79cd\u73b0\u8c61\u88ab\u79f0\u4e3a\u7b1b\u5361\u5c14\u79ef\u73b0\u8c61\u3002\uff08\u7b1b\u5361\u5c14\u4e58\u79ef\u73b0\u8c61\uff09<\/h2>\n
\u6848\u4f8b\uff1a\u627e\u51fa\u6bcf\u4e00\u4e2a\u5458\u5de5\u7684\u90e8\u95e8\u540d\u79f0\uff0c\u8981\u6c42\u663e\u793a\u5458\u5de5\u540d\u548c\u90e8\u95e8\u540d\u3002<\/p>\n
EMP\u8868<\/p>\n
+--------+--------+\n| ename  | deptno |\n+--------+--------+\n| SMITH  |     20 |\n| ALLEN  |     30 |\n| WARD   |     30 |\n| JONES  |     20 |\n| MARTIN |     30 |\n| BLAKE  |     30 |\n| CLARK  |     10 |\n| SCOTT  |     20 |\n| KING   |     10 |\n| TURNER |     30 |\n| ADAMS  |     20 |\n| JAMES  |     30 |\n| FORD   |     20 |\n| MILLER |     10 |\n+--------+--------+<\/code><\/pre>\n
DEPT\u8868<\/p>\n
+--------+------------+----------+\n| DEPTNO | DNAME      | LOC      |\n+--------+------------+----------+\n|     10 | ACCOUNTING | NEW YORK |\n|     20 | RESEARCH   | DALLAS   |\n|     30 | SALES      | CHICAGO  |\n|     40 | OPERATIONS | BOSTON   |\n+--------+------------+----------+<\/code><\/pre>\n
.<\/p>\n
mysql>select ename,dname from emp,dept;\n+--------+------------+\n| ename  | dname      |\n+--------+------------+\n| SMITH  | ACCOUNTING |\n| SMITH  | RESEARCH   |\n| SMITH  | SALES      |\n| SMITH  | OPERATIONS |\n| ALLEN  | ACCOUNTING |\n| ALLEN  | RESEARCH   |\n| ALLEN  | SALES      |\n| ALLEN  | OPERATIONS |\n............\n56 rows in set (0.00 sec)<\/code><\/pre>\n
\u7b1b\u5361\u5c14\u79ef\u73b0\u8c61\uff1a\u5f53\u4e24\u5f20\u8868\u8fdb\u884c\u8fde\u63a5\u67e5\u8be2\u7684\u65f6\u5019\uff0c\u6ca1\u6709\u4efb\u4f55\u6761\u4ef6\u8fdb\u884c\u9650\u5236\uff0c\u6700\u7ec8\u7684\u67e5\u8be2\u7ed3\u679c\u6761\u6570\u662f\u4e24\u5f20\u8868\u8bb0\u5f55\u6761\u6570\u7684\u4e58\u79ef\u3002<\/p>\n
\u5173\u4e8e\u8868\u7684\u522b\u540d\uff1a
\nselect e.ename,d.dname from emp e,dept d;
\n\u8868\u7684\u522b\u540d\u6709\u4ec0\u4e48\u597d\u5904\uff1f
\n\u7b2c\u4e00\uff1a\u6267\u884c\u6548\u7387\u9ad8\u3002
\n\u7b2c\u4e8c\uff1a\u53ef\u8bfb\u6027\u597d\u3002<\/p>\n
2.4\u3001\u600e\u4e48\u907f\u514d\u7b1b\u5361\u5c14\u79ef\u73b0\u8c61\uff1f\u5f53\u7136\u662f\u52a0\u6761\u4ef6\u8fdb\u884c\u8fc7\u6ee4\u3002<\/h2>\n
\u601d\u8003\uff1a\u907f\u514d\u4e86\u7b1b\u5361\u5c14\u79ef\u73b0\u8c61\uff0c\u4f1a\u51cf\u5c11\u8bb0\u5f55\u7684\u5339\u914d\u6b21\u6570\u5417\uff1f
\n\u4e0d\u4f1a\uff0c\u6b21\u6570\u8fd8\u662f56\u6b21\u3002\u53ea\u4e0d\u8fc7\u663e\u793a\u7684\u662f\u6709\u6548\u8bb0\u5f55\u3002<\/p>\n
\u6848\u4f8b\uff1a\u627e\u51fa\u6bcf\u4e00\u4e2a\u5458\u5de5\u7684\u90e8\u95e8\u540d\u79f0\uff0c\u8981\u6c42\u663e\u793a\u5458\u5de5\u540d\u548c\u90e8\u95e8\u540d\u3002<\/p>\n
select  \n    e.ename,d.dname\nfrom\n    emp e , dept d\nwhere\n    e.deptno = d.deptno; \/\/SQL92\uff0c\u4ee5\u540e\u4e0d\u7528\u3002\n\n+--------+------------+\n| ename  | dname      |\n+--------+------------+\n| CLARK  | ACCOUNTING |\n| KING   | ACCOUNTING |\n| MILLER | ACCOUNTING |\n| SMITH  | RESEARCH   |\n| JONES  | RESEARCH   |\n| SCOTT  | RESEARCH   |\n| ADAMS  | RESEARCH   |\n| FORD   | RESEARCH   |\n| ALLEN  | SALES      |\n| WARD   | SALES      |\n| MARTIN | SALES      |\n| BLAKE  | SALES      |\n| TURNER | SALES      |\n| JAMES  | SALES      |\n+--------+------------+<\/code><\/pre>\n
2.5\u5185\u8fde\u63a5\u4e4b\u7b49\u503c\u8fde\u63a5\uff1a\u6700\u5927\u7279\u70b9\u662f\uff1a\u6761\u4ef6\u662f\u7b49\u91cf\u5173\u7cfb\u3002<\/h2>\n
\u6848\u4f8b\uff1a\u67e5\u8be2\u6bcf\u4e2a\u5458\u5de5\u7684\u90e8\u95e8\u540d\u79f0\uff0c\u8981\u6c42\u663e\u793a\u5458\u5de5\u540d\u548c\u90e8\u95e8\u540d\u3002<\/p>\n
SQL92:\uff08\u592a\u8001\uff0c\u4e0d\u7528\u4e86\uff09\nselect \n    e.ename,d.dname\nfrom\n    emp e, dept d\nwhere\n    e.deptno = d.deptno;\n\nSQL99\uff1a\uff08\u5e38\u7528\u7684\uff09\nselect \n    e.ename,d.dname\nfrom\n    emp e\njoin\n    dept d\non\n    e.deptno = d.deptno;\n\n\/\/ inner\u53ef\u4ee5\u7701\u7565\u7684\uff0c\u5e26\u7740inner\u76ee\u7684\u662f\u53ef\u8bfb\u6027\u597d\u4e00\u4e9b\uff0c\u4e00\u770b\u5c31\u662f\u5185\u8fde\u63a5\u3002\nselect \n    e.ename,d.dname\nfrom\n    emp e\ninner join\n    dept d\non\n    e.deptno = d.deptno;\n\n\u8bed\u6cd5\uff1a\n    ...\n        A\n    join\n        B\n    on\n        \u8fde\u63a5\u6761\u4ef6\n    where\n        ...\n\nSQL99\u8bed\u6cd5\u7ed3\u6784\u66f4\u6e05\u6670\u4e00\u4e9b\uff1a\u8868\u7684\u8fde\u63a5\u6761\u4ef6\u548c\u540e\u6765\u7684where\u6761\u4ef6\u5206\u79bb\u4e86\u3002\n\n+--------+------------+\n| ename  | dname      |\n+--------+------------+\n| CLARK  | ACCOUNTING |\n| KING   | ACCOUNTING |\n| MILLER | ACCOUNTING |\n| SMITH  | RESEARCH   |\n| JONES  | RESEARCH   |\n| SCOTT  | RESEARCH   |\n| ADAMS  | RESEARCH   |\n| FORD   | RESEARCH   |\n| ALLEN  | SALES      |\n| WARD   | SALES      |\n| MARTIN | SALES      |\n| BLAKE  | SALES      |\n| TURNER | SALES      |\n| JAMES  | SALES      |\n+--------+------------+<\/code><\/pre>\n
2.6\u3001\u5185\u8fde\u63a5\u4e4b\u975e\u7b49\u503c\u8fde\u63a5\uff1a\u6700\u5927\u7684\u7279\u70b9\u662f\uff1a\u8fde\u63a5\u6761\u4ef6\u4e2d\u7684\u5173\u7cfb\u662f\u975e\u7b49\u91cf\u5173\u7cfb\u3002<\/h2>\n
\u6848\u4f8b\uff1a\u627e\u51fa\u6bcf\u4e2a\u5458\u5de5\u7684\u5de5\u8d44\u7b49\u7ea7\uff0c\u8981\u6c42\u663e\u793a\u5458\u5de5\u540d\u3001\u5de5\u8d44\u3001\u5de5\u8d44\u7b49\u7ea7\u3002<\/p>\n
mysql> select ename,sal from emp; e\n+--------+---------+\n| ename  | sal     |\n+--------+---------+\n| SMITH  |  800.00 |\n| ALLEN  | 1600.00 |\n| WARD   | 1250.00 |\n| JONES  | 2975.00 |\n| MARTIN | 1250.00 |\n| BLAKE  | 2850.00 |\n| CLARK  | 2450.00 |\n| SCOTT  | 3000.00 |\n| KING   | 5000.00 |\n| TURNER | 1500.00 |\n| ADAMS  | 1100.00 |\n| JAMES  |  950.00 |\n| FORD   | 3000.00 |\n| MILLER | 1300.00 |\n+--------+---------+\n\nmysql> select * from salgrade; s\n+-------+-------+-------+ \n| GRADE | LOSAL | HISAL |\n+-------+-------+-------+\n|     1 |   700 |  1200 |\n|     2 |  1201 |  1400 |\n|     3 |  1401 |  2000 |\n|     4 |  2001 |  3000 |\n|     5 |  3001 |  9999 |\n+-------+-------+-------+\n\nselect \n    e.ename,e.sal,s.grade\nfrom\n    emp e\njoin\n    salgrade s\non\n    e.sal between s.losal and s.hisal;\n\n\/\/ inner\u53ef\u4ee5\u7701\u7565\nselect \n    e.ename,e.sal,s.grade\nfrom\n    emp e\ninner join\n    salgrade s\non\n    e.sal between s.losal and s.hisal;\n\n+--------+---------+-------+\n| ename  | sal     | grade |\n+--------+---------+-------+\n| SMITH  |  800.00 |     1 |\n| ALLEN  | 1600.00 |     3 |\n| WARD   | 1250.00 |     2 |\n| JONES  | 2975.00 |     4 |\n| MARTIN | 1250.00 |     2 |\n| BLAKE  | 2850.00 |     4 |\n| CLARK  | 2450.00 |     4 |\n| SCOTT  | 3000.00 |     4 |\n| KING   | 5000.00 |     5 |\n| TURNER | 1500.00 |     3 |\n| ADAMS  | 1100.00 |     1 |\n| JAMES  |  950.00 |     1 |\n| FORD   | 3000.00 |     4 |\n| MILLER | 1300.00 |     2 |\n+--------+---------+-------+<\/code><\/pre>\n
2.7\u3001\u81ea\u8fde\u63a5\uff1a\u6700\u5927\u7684\u7279\u70b9\u662f\uff1a\u4e00\u5f20\u8868\u770b\u505a\u4e24\u5f20\u8868\u3002\u81ea\u5df1\u8fde\u63a5\u81ea\u5df1\u3002<\/h2>\n
\u6848\u4f8b\uff1a\u627e\u51fa\u6bcf\u4e2a\u5458\u5de5\u7684\u4e0a\u7ea7\u9886\u5bfc\uff0c\u8981\u6c42\u663e\u793a\u5458\u5de5\u540d\u548c\u5bf9\u5e94\u7684\u9886\u5bfc\u540d\u3002<\/p>\n
\u5458\u5de5\u7684\u9886\u5bfc\u7f16\u53f7 = \u9886\u5bfc\u7684\u5458\u5de5\u7f16\u53f7\n\nselect \n    a.ename as '\u5458\u5de5\u540d',b.ename as '\u9886\u5bfc\u540d'\nfrom\n    emp a\ninner join\n    emp b\non\n    a.mgr = b.empno;\n\n+--------+--------+\n| \u5458\u5de5\u540d | \u9886\u5bfc\u540d |\n+--------+--------+\n| SMITH  | FORD   |\n| ALLEN  | BLAKE  |\n| WARD   | BLAKE  |\n| JONES  | KING   |\n| MARTIN | BLAKE  |\n| BLAKE  | KING   |\n| CLARK  | KING   |\n| SCOTT  | JONES  |\n| TURNER | BLAKE  |\n| ADAMS  | SCOTT  |\n| JAMES  | BLAKE  |\n| FORD   | JONES  |\n| MILLER | CLARK  |\n+--------+--------+<\/code><\/pre>\n
2.8\u3001\u5916\u8fde\u63a5\uff1f<\/h2>\n
1\uff09\u4ec0\u4e48\u662f\u5916\u8fde\u63a5\uff0c\u548c\u5185\u8fde\u63a5\u6709\u4ec0\u4e48\u533a\u522b\uff1f<\/p>\n
\u5185\u8fde\u63a5\uff1a
\n\u5047\u8bbeA\u548cB\u8868\u8fdb\u884c\u8fde\u63a5\uff0c\u4f7f\u7528\u5185\u8fde\u63a5\u7684\u8bdd\uff0c\u51e1\u662fA\u8868\u548cB\u8868\u80fd\u591f\u5339\u914d\u4e0a\u7684\u8bb0\u5f55\u67e5\u8be2\u51fa\u6765\uff0c\u8fd9\u5c31\u662f\u5185\u8fde\u63a5\u3002
\nAB\u4e24\u5f20\u8868\u6ca1\u6709\u4e3b\u526f\u4e4b\u5206\uff0c\u4e24\u5f20\u8868\u662f\u5e73\u7b49\u7684\u3002<\/p>\n
\u5916\u8fde\u63a5\uff1a
\n\u5047\u8bbeA\u548cB\u8868\u8fdb\u884c\u8fde\u63a5\uff0c\u4f7f\u7528\u5916\u8fde\u63a5\u7684\u8bdd\uff0cAB\u4e24\u5f20\u8868\u4e2d\u6709\u4e00\u5f20\u8868\u662f\u4e3b\u8868\uff0c\u4e00\u5f20\u8868\u662f\u526f\u8868\uff0c\u4e3b\u8981\u67e5\u8be2\u4e3b\u8868\u4e2d\u7684\u6570\u636e\uff0c\u634e\u5e26\u7740\u67e5\u8be2\u526f\u8868\uff0c\u5f53\u526f\u8868\u4e2d\u7684\u6570\u636e\u6ca1\u6709\u548c\u4e3b\u8868\u4e2d\u7684\u6570\u636e\u5339\u914d\u4e0a\uff0c\u526f\u8868\u81ea\u52a8\u6a21\u62df\u51faNULL\u4e0e\u4e4b\u5339\u914d\u3002<\/p>\n
2\uff09\u5916\u8fde\u63a5\u7684\u5206\u7c7b\uff1f
\n\u5de6\u5916\u8fde\u63a5\uff08\u5de6\u8fde\u63a5\uff09\uff1a\u8868\u793a\u5de6\u8fb9\u7684\u8fd9\u5f20\u8868\u662f\u4e3b\u8868\u3002
\n\u53f3\u5916\u8fde\u63a5\uff08\u53f3\u8fde\u63a5\uff09\uff1a\u8868\u793a\u53f3\u8fb9\u7684\u8fd9\u5f20\u8868\u662f\u4e3b\u8868\u3002<\/p>\n
\u5de6\u8fde\u63a5\u6709\u53f3\u8fde\u63a5\u7684\u5199\u6cd5\uff0c\u53f3\u8fde\u63a5\u4e5f\u4f1a\u6709\u5bf9\u5e94\u7684\u5de6\u8fde\u63a5\u7684\u5199\u6cd5\u3002<\/p>\n
\u5916\u8fde\u63a5\uff1a<\/strong>
\n\u6848\u4f8b\uff1a\u627e\u51fa\u6bcf\u4e2a\u5458\u5de5\u7684\u4e0a\u7ea7\u9886\u5bfc\uff1f\uff08\u6240\u6709\u5458\u5de5\u5fc5\u987b\u5168\u90e8\u67e5\u8be2\u51fa\u6765\u3002\uff09<\/p>\n
\/\/ outer\u662f\u53ef\u4ee5\u7701\u7565\u7684\u3002\nselect \n    a.ename '\u5458\u5de5', b.ename '\u9886\u5bfc'\nfrom\n    emp a\nleft outer join\n    emp b\non\n    a.mgr = b.empno;<\/code><\/pre>\n
\u5916\u8fde\u63a5\u6700\u91cd\u8981\u7684\u7279\u70b9\u662f\uff1a\u4e3b\u8868\u7684\u6570\u636e\u65e0\u6761\u4ef6\u7684\u5168\u90e8\u67e5\u8be2\u51fa\u6765\u3002
\n\u6848\u4f8b\uff1a\u627e\u51fa\u54ea\u4e2a\u90e8\u95e8\u6ca1\u6709\u5458\u5de5\uff1f<\/p>\n
mysql> select \n     >d.dname,e.ename \n     >from dept d \n     >left join emp e \n     >on d.deptno=e.deptno \n     >where e.ename is null;\n+------------+-------+\n| dname      | ename |\n+------------+-------+\n| OPERATIONS | NULL  |\n+------------+-------+\n1 row in set (0.00 sec)<\/code><\/pre>\n
2.9\u4e09\u5f20\u8868\u600e\u4e48\u8fde\u63a5\u67e5\u8be2\uff1f<\/h2>\n
\u6ce8\u610f\uff0c\u89e3\u91ca\u4e00\u4e0b\uff1a
\n....
\nA
\njoin
\nB
\njoin
\nC
\non
\n...<\/p>\n
\u8868\u793a\uff1aA\u8868\u548cB\u8868\u5148\u8fdb\u884c\u8868\u8fde\u63a5\uff0c\u8fde\u63a5\u4e4b\u540eA\u8868\u7ee7\u7eed\u548cC\u8868\u8fdb\u884c\u8fde\u63a5\u3002<\/p>\n
\u6848\u4f8b\uff1a\u627e\u51fa\u6bcf\u4e00\u4e2a\u5458\u5de5\u7684\u90e8\u95e8\u540d\u79f0\u4ee5\u53ca\u5de5\u8d44\u7b49\u7ea7\u3002<\/p>\n
mysql> select\n    -> e.ename,d.dname,s.grade\n    -> from\n    -> emp e\n    -> join\n    -> dept d\n    -> on\n    -> e.deptno = d.deptno\n    -> join\n    -> salgrade s\n    -> on\n    -> e.sal between s.losal and s.hisal;\n+--------+------------+-------+\n| ename  | dname      | grade |\n+--------+------------+-------+\n| SMITH  | RESEARCH   |     1 |\n| ALLEN  | SALES      |     3 |\n| WARD   | SALES      |     2 |\n| JONES  | RESEARCH   |     4 |\n| MARTIN | SALES      |     2 |\n| BLAKE  | SALES      |     4 |\n| CLARK  | ACCOUNTING |     4 |\n| SCOTT  | RESEARCH   |     4 |\n| KING   | ACCOUNTING |     5 |\n| TURNER | SALES      |     3 |\n| ADAMS  | RESEARCH   |     1 |\n| JAMES  | SALES      |     1 |\n| FORD   | RESEARCH   |     4 |\n| MILLER | ACCOUNTING |     2 |\n+--------+------------+-------+<\/code><\/pre>\n
\u6848\u4f8b\uff1a\u627e\u51fa\u6bcf\u4e00\u4e2a\u5458\u5de5\u7684\u90e8\u95e8\u540d\u79f0\u3001\u5de5\u8d44\u7b49\u7ea7\u3001\u4ee5\u53ca\u4e0a\u7ea7\u9886\u5bfc\u3002<\/p>\n
mysql> select\n    -> e.ename '\u5458\u5de5',d.dname,s.grade,e1.ename '\u9886\u5bfc'\n    -> from\n    -> emp e\n    -> join\n    -> dept d\n    -> on\n    -> e.deptno = d.deptno\n    -> join\n    -> salgrade s\n    -> on\n    -> e.sal between s.losal and s.hisal\n    -> left join\n    -> emp e1\n    -> on\n    -> e.mgr = e1.empno;\n+--------+------------+-------+-------+\n| \u5458\u5de5   | dname      | grade | \u9886\u5bfc  |\n+--------+------------+-------+-------+\n| SMITH  | RESEARCH   |     1 | FORD  |\n| ALLEN  | SALES      |     3 | BLAKE |\n| WARD   | SALES      |     2 | BLAKE |\n| JONES  | RESEARCH   |     4 | KING  |\n| MARTIN | SALES      |     2 | BLAKE |\n| BLAKE  | SALES      |     4 | KING  |\n| CLARK  | ACCOUNTING |     4 | KING  |\n| SCOTT  | RESEARCH   |     4 | JONES |\n| KING   | ACCOUNTING |     5 | NULL  |\n| TURNER | SALES      |     3 | BLAKE |\n| ADAMS  | RESEARCH   |     1 | SCOTT |\n| JAMES  | SALES      |     1 | BLAKE |\n| FORD   | RESEARCH   |     4 | JONES |\n| MILLER | ACCOUNTING |     2 | CLARK |\n+--------+------------+-------+-------+<\/code><\/pre>\n
3.\u5b50\u67e5\u8be2<\/h2>\n
3.1\u3001\u4ec0\u4e48\u662f\u5b50\u67e5\u8be2\uff1f\u5b50\u67e5\u8be2\u90fd\u53ef\u4ee5\u51fa\u73b0\u5728\u54ea\u91cc\uff1f<\/h2>\n
select\u8bed\u53e5\u5f53\u4e2d\u5d4c\u5957select\u8bed\u53e5\uff0c\u88ab\u5d4c\u5957\u7684select\u8bed\u53e5\u662f\u5b50\u67e5\u8be2\u3002
\n\u5b50\u67e5\u8be2\u53ef\u4ee5\u51fa\u73b0\u5728\u54ea\u91cc\uff1f
\nselect
\n..(select).
\nfrom
\n..(select).
\nwhere
\n..(select).<\/p>\n
3.2\u3001where\u5b50\u53e5\u4e2d\u4f7f\u7528\u5b50\u67e5\u8be2<\/h2>\n
\u6848\u4f8b\uff1a\u627e\u51fa\u9ad8\u4e8e\u5e73\u5747\u85aa\u8d44\u7684\u5458\u5de5\u4fe1\u606f\u3002<\/p>\n
select * from emp where sal > (select avg(sal) from emp);<\/code><\/pre>\n
3.3.from\u540e\u9762\u5d4c\u5957\u5b50\u67e5\u8be2<\/h2>\n
\u6848\u4f8b\uff1a\u627e\u51fa\u6bcf\u4e2a\u90e8\u95e8\u5e73\u5747\u85aa\u6c34\u7684\u7b49\u7ea7\u3002<\/p>\n
\u7b2c\u4e00\u6b65\uff1a\u627e\u51fa\u6bcf\u4e2a\u90e8\u95e8\u5e73\u5747\u85aa\u6c34\uff08\u6309\u7167\u90e8\u95e8\u7f16\u53f7\u5206\u7ec4\uff0c\u6c42sal\u7684\u5e73\u5747\u503c\uff09\nselect deptno,avg(sal) as avgsal from emp group by deptno;\n+--------+-------------+\n| deptno | avgsal      |\n+--------+-------------+\n|     10 | 2916.666667 |\n|     20 | 2175.000000 |\n|     30 | 1566.666667 |\n+--------+-------------+\n\u7b2c\u4e8c\u6b65\uff1a\u5c06\u4ee5\u4e0a\u7684\u67e5\u8be2\u7ed3\u679c\u5f53\u505a\u4e34\u65f6\u8868t\uff0c\u8ba9t\u8868\u548csalgrade s\u8868\u8fde\u63a5\uff0c\u6761\u4ef6\u662f\uff1at.avgsal between s.losal and s.hisal\nselect \n    t.*,s.grade\nfrom\n    (select deptno,avg(sal) as avgsal from emp group by deptno) t\njoin\n    salgrade s\non\n    t.avgsal between s.losal and s.hisal;<\/code><\/pre>\n
\u5c06\u4e0a\u4e2a\u6848\u4f8b\u4e2d\u90e8\u95e8\u5e73\u5747\u85aa\u6c34\u548c\u540d\u79f0\u5bf9\u5e94\u8d77\u6765\u3002\u5e94\u5f53\u6ce8\u610f\u7684\u662f\uff1aavg(sal) as avgsal<\/strong><\/p>\n
mysql> select a.dname,b.avgsal from dept a left join (select deptno,avg(sal) as avgsal from emp group by deptno) as b on a.deptno=b.deptno;\n+------------+-------------+\n| dname      | avgsal      |\n+------------+-------------+\n| ACCOUNTING | 2916.666667 |\n| RESEARCH   | 2175.000000 |\n| SALES      | 1566.666667 |\n| OPERATIONS |        NULL |\n+------------+-------------+<\/code><\/pre>\n
\u6848\u4f8b\uff1a\u627e\u51fa\u6bcf\u4e2a\u90e8\u95e8\u5e73\u5747\u7684\u85aa\u6c34\u7b49\u7ea7\u3002<\/p>\n
\u7b2c\u4e00\u6b65\uff1a\u627e\u51fa\u6bcf\u4e2a\u5458\u5de5\u7684\u85aa\u6c34\u7b49\u7ea7\u3002\nselect e.ename,e.sal,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;\n+--------+---------+--------+-------+\n| ename  | sal     | deptno | grade |\n+--------+---------+--------+-------+\n| SMITH  |  800.00 |     20 |     1 |\n| ALLEN  | 1600.00 |     30 |     3 |\n| WARD   | 1250.00 |     30 |     2 |\n| JONES  | 2975.00 |     20 |     4 |\n| MARTIN | 1250.00 |     30 |     2 |\n| BLAKE  | 2850.00 |     30 |     4 |\n| CLARK  | 2450.00 |     10 |     4 |\n| SCOTT  | 3000.00 |     20 |     4 |\n| KING   | 5000.00 |     10 |     5 |\n| TURNER | 1500.00 |     30 |     3 |\n| ADAMS  | 1100.00 |     20 |     1 |\n| JAMES  |  950.00 |     30 |     1 |\n| FORD   | 3000.00 |     20 |     4 |\n| MILLER | 1300.00 |     10 |     2 |\n+--------+---------+--------+-------+\n\u7b2c\u4e8c\u6b65\uff1a\u57fa\u4e8e\u4ee5\u4e0a\u7ed3\u679c\uff0c\u7ee7\u7eed\u6309\u7167deptno\u5206\u7ec4\uff0c\u6c42grade\u5e73\u5747\u503c\u3002\nselect \n    e.deptno,avg(s.grade)\nfrom \n    emp e \njoin \n    salgrade s \non \n    e.sal between s.losal and s.hisal\ngroup by\n    e.deptno;\n\n+--------+--------------+\n| deptno | avg(s.grade) |\n+--------+--------------+\n|     10 |       3.6667 |\n|     20 |       2.8000 |\n|     30 |       2.5000 |\n+--------+--------------+<\/code><\/pre>\n
3.4\u3001\u5728select\u540e\u9762\u5d4c\u5957\u5b50\u67e5\u8be2\u3002<\/h2>\n
\u6848\u4f8b\uff1a\u627e\u51fa\u6bcf\u4e2a\u5458\u5de5\u6240\u5728\u7684\u90e8\u95e8\u540d\u79f0\uff0c\u8981\u6c42\u663e\u793a\u5458\u5de5\u540d\u548c\u90e8\u95e8\u540d\u3002<\/p>\n
mysql> select\n    -> e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname\n    -> from\n    -> emp e;\n+--------+------------+\n| ename  | dname      |\n+--------+------------+\n| SMITH  | RESEARCH   |\n| ALLEN  | SALES      |\n| WARD   | SALES      |\n| JONES  | RESEARCH   |\n| MARTIN | SALES      |\n| BLAKE  | SALES      |\n| CLARK  | ACCOUNTING |\n| SCOTT  | RESEARCH   |\n| KING   | ACCOUNTING |\n| TURNER | SALES      |\n| ADAMS  | RESEARCH   |\n| JAMES  | SALES      |\n| FORD   | RESEARCH   |\n| MILLER | ACCOUNTING |\n+--------+------------+<\/code><\/pre>\n
4\u3001union \uff08\u53ef\u4ee5\u5c06\u67e5\u8be2\u7ed3\u679c\u96c6\u76f8\u52a0\uff09<\/p>\n
\u6848\u4f8b\uff1a\u627e\u51fa\u5de5\u4f5c\u5c97\u4f4d\u662fSALESMAN\u548cMANAGER\u7684\u5458\u5de5\uff1f<\/p>\n
\u7b2c\u4e00\u79cd\uff1aselect ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';\n\u7b2c\u4e8c\u79cd\uff1aselect ename,job from emp where job in('MANAGER','SALESMAN');\n+--------+----------+\n| ename  | job      |\n+--------+----------+\n| ALLEN  | SALESMAN |\n| WARD   | SALESMAN |\n| JONES  | MANAGER  |\n| MARTIN | SALESMAN |\n| BLAKE  | MANAGER  |\n| CLARK  | MANAGER  |\n| TURNER | SALESMAN |\n+--------+----------+\n\u7b2c\u4e09\u79cd\uff1aunion\nselect ename,job from emp where job = 'MANAGER'\nunion\nselect ename,job from emp where job = 'SALESMAN';\n+--------+----------+\n| ename  | job      |\n+--------+----------+\n| JONES  | MANAGER  |\n| BLAKE  | MANAGER  |\n| CLARK  | MANAGER  |\n| ALLEN  | SALESMAN |\n| WARD   | SALESMAN |\n| MARTIN | SALESMAN |\n| TURNER | SALESMAN |\n+--------+----------+<\/code><\/pre>\n
\u4e24\u5f20\u4e0d\u76f8\u5e72\u7684\u8868\u4e2d\u7684\u6570\u636e\u62fc\u63a5\u5728\u4e00\u8d77\u663e\u793a\uff1f<\/p>\n
select ename from emp\nunion\nselect dname from dept;\n\n+------------+\n| ename      |\n+------------+\n| SMITH      |\n| ALLEN      |\n| WARD       |\n| JONES      |\n| MARTIN     |\n| BLAKE      |\n| CLARK      |\n| SCOTT      |\n| KING       |\n| TURNER     |\n| ADAMS      |\n| JAMES      |\n| FORD       |\n| MILLER     |\n| ACCOUNTING |\n| RESEARCH   |\n| SALES      |\n| OPERATIONS |\n+------------+<\/code><\/pre>\n
\u62fc\u63a5\u65f6\uff0c\u5217\u6570\uff08number of columns\uff09\u8981\u4e00\u81f4<\/p>\n
mysql> select ename,sal from emp\n    -> union\n    -> select dname from dept;\nERROR 1222 (21000): The used SELECT statements have a different number of columns<\/code><\/pre>\n
5\u3001limit (\u91cd\u70b9\u4e2d\u7684\u91cd\u70b9\uff0c\u4ee5\u540e\u5206\u9875\u67e5\u8be2\u5168\u9760\u5b83 \u4e86\u3002)<\/h2>\n
5.1\u3001limit\u662fmysql\u7279\u6709\u7684\uff0c\u5176\u4ed6\u6570\u636e\u5e93\u4e2d\u6ca1\u6709\uff0c\u4e0d\u901a\u7528\u3002\uff08Oracle\u4e2d\u6709\u4e00\u4e2a\u76f8\u540c\u7684\u673a\u5236\uff0c\u53eb\u505arownum\uff09<\/h2>\n
5.2\u3001limit\u53d6\u7ed3\u679c\u96c6\u4e2d\u7684\u90e8\u5206\u6570\u636e\uff0c\u8fd9\u65f6\u5b83\u7684\u4f5c\u7528\u3002<\/h2>\n
5.3\u3001\u8bed\u6cd5\u673a\u5236\uff1a<\/h2>\n
limit startIndex, length<\/strong>
\nstartIndex<\/strong>\u8868\u793a\u8d77\u59cb\u4f4d\u7f6e\uff0c\u4ece0\u5f00\u59cb\uff0c0\u8868\u793a\u7b2c\u4e00\u6761\u6570\u636e\u3002
\nlength<\/strong>\u8868\u793a\u53d6\u51e0\u4e2a<\/p>\n
\u6848\u4f8b\uff1a\u53d6\u51fa\u5de5\u8d44\u524d5\u540d\u7684\u5458\u5de5\uff08\u601d\u8def\uff1a\u964d\u5e8f\u53d6\u524d5\u4e2a\uff09<\/p>\n
select ename,sal from emp order by sal desc;\n\u53d6\u524d5\u4e2a\uff1a\nselect ename,sal from emp order by sal desc limit 0, 5;\nselect ename,sal from emp order by sal desc limit 5;<\/code><\/pre>\n
5.4\u3001limit\u662fsql\u8bed\u53e5\u6700\u540e\u6267\u884c\u7684\u4e00\u4e2a\u73af\u8282\uff1a<\/h2>\n
\u2014\u2014 select      5
\n\u2014\u2014      ...
\n\u2014\u2014  from            1
\n\u2014\u2014      ...
\n\u2014\u2014  where           2
\n\u2014\u2014  ...
\n\u2014\u2014group by      3
\n\u2014\u2014...
\n\u2014\u2014having            4
\n\u2014\u2014...
\n\u2014\u2014order by      6
\n\u2014\u2014  ...
\n\u2014\u2014  limit           7<\/p>\n
5.5\u3001\u6848\u4f8b\uff1a\u627e\u51fa\u5de5\u8d44\u6392\u540d\u5728\u7b2c4\u5230\u7b2c9\u540d\u7684\u5458\u5de5\uff1f<\/h2>\n
select ename,sal from emp order by sal desc limit 3,6;\n+--------+---------+\n| ename  | sal     |\n+--------+---------+\n| JONES  | 2975.00 |\n| BLAKE  | 2850.00 |\n| CLARK  | 2450.00 |\n| ALLEN  | 1600.00 |\n| TURNER | 1500.00 |\n| MILLER | 1300.00 |\n+--------+---------+<\/code><\/pre>\n
5.6\u3001\u901a\u7528\u7684\u6807\u51c6\u5206\u9875sql\uff1f<\/h2>\n
\u6bcf\u9875\u663e\u793a3\u6761\u8bb0\u5f55\uff1a
\n\u7b2c1\u9875\uff1a0, 3
\n\u7b2c2\u9875\uff1a3, 3
\n\u7b2c3\u9875\uff1a6, 3
\n\u7b2c4\u9875\uff1a9, 3
\n\u7b2c5\u9875\uff1a12, 3<\/p>\n
\u6bcf\u9875\u663e\u793apageSize\u6761\u8bb0\u5f55\uff1a
\n\u7b2cpageNo\u9875\uff1a(pageNo - 1) * pageSize, pageSize<\/p>\n
pageSize\u662f\u4ec0\u4e48\uff1f\u662f\u6bcf\u9875\u663e\u793a\u591a\u5c11\u6761\u8bb0\u5f55
\npageNo\u662f\u4ec0\u4e48\uff1f\u663e\u793a\u7b2c\u51e0\u9875<\/p>\n
java\u4ee3\u7801{<\/p>\n
int pageNo = 2; \/\/ \u9875\u7801\u662f2\nint pageSize = 10; \/\/ \u6bcf\u9875\u663e\u793a10\u6761\nlimit (pageNo - 1) * pageSize, pageSize\n}<\/code><\/pre>\n
6\u3001\u521b\u5efa\u8868\uff1a<\/h2>\n
\u5efa\u8868\u8bed\u53e5\u7684\u8bed\u6cd5\u683c\u5f0f\uff1a
\ncreate table \u8868\u540d(
\n\u5b57\u6bb5\u540d1 \u6570\u636e\u7c7b\u578b,
\n\u5b57\u6bb5\u540d2 \u6570\u636e\u7c7b\u578b,
\n\u5b57\u6bb5\u540d3 \u6570\u636e\u7c7b\u578b,
\n....
\n);<\/p>\n
\u5173\u4e8eMySQL\u5f53\u4e2d\u5b57\u6bb5\u7684\u6570\u636e\u7c7b\u578b\uff1f\u4ee5\u4e0b\u53ea\u8bf4\u5e38\u89c1\u7684<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n
<\/th>\n<\/th>\n<\/tr>\n<\/thead>\n
int<\/td>\n\u6574\u6570\u578b(java\u4e2d\u7684int)<\/td>\n<\/tr>\n
bigint<\/td>\n\u957f\u6574\u578b(java\u4e2d\u7684long)<\/td>\n<\/tr>\n
float<\/td>\n\u6d6e\u70b9\u578b(java\u4e2d\u7684float double)<\/td>\n<\/tr>\n
char<\/td>\n\u5b9a\u957f\u5b57\u7b26\u4e32(String)<\/td>\n<\/tr>\n
varchar<\/td>\n\u53ef\u53d8\u957f\u5b57\u7b26\u4e32(StringBuffer\/StringBuilder)<\/td>\n<\/tr>\n
date<\/td>\n\u65e5\u671f\u7c7b\u578b \uff08\u5bf9\u5e94Java\u4e2d\u7684java.sql.Date\u7c7b\u578b\uff09<\/td>\n<\/tr>\n
BLOB<\/td>\n\u4e8c\u8fdb\u5236\u5927\u5bf9\u8c61\uff08\u5b58\u50a8\u56fe\u7247\u3001\u89c6\u9891\u7b49\u6d41\u5a92\u4f53\u4fe1     Binary Large OBject \uff08\u5bf9\u5e94java\u4e2d\u7684Object\uff09<\/td>\n<\/tr>\n
CLOB<\/td>\n\u5b57\u7b26\u5927\u5bf9\u8c61\uff08\u5b58\u50a8\u8f83\u5927\u6587\u672c\uff0c\u6bd4\u5982\uff0c\u53ef\u4ee5\u5b58\u50a84G\u7684\u5b57\u7b26\u4e32\u3002\uff09 Character Large OBject\uff08\u5bf9\u5e94java\u4e2d\u7684Object\uff09<\/td>\n<\/tr>\n
......<\/td>\n....<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
char\u548cvarchar\u600e\u4e48\u9009\u62e9\uff1f<\/strong>
\n\u5728\u5b9e\u9645\u7684\u5f00\u53d1\u4e2d\uff0c\u5f53\u67d0\u4e2a\u5b57\u6bb5\u4e2d\u7684\u6570\u636e\u957f\u5ea6\u4e0d\u53d1\u751f\u6539\u53d8\u7684\u65f6\u5019\uff0c\u662f\u5b9a\u957f\u7684\uff0c\u4f8b\u5982\uff1a\u6027\u522b\u3001\u751f\u65e5\u7b49\u90fd\u662f\u91c7\u7528char\u3002
\n\u5f53\u4e00\u4e2a\u5b57\u6bb5\u7684\u6570\u636e\u957f\u5ea6\u4e0d\u786e\u5b9a\uff0c\u4f8b\u5982\uff1a\u7b80\u4ecb\u3001\u59d3\u540d\u7b49\u90fd\u662f\u91c7\u7528varchar\u3002<\/p>\n
BLOB\u548cCLOB\u7c7b\u578b\u7684\u4f7f\u7528\uff1f<\/strong>
\n\u7535\u5f71\u8868: t_movie
\nid(int) name(varchar)       playtime(date\/char)     haibao(BLOB)        history(CLOB)<\/h2>\n
    1           \u8718\u86db\u4fa0 \n    2\n    3<\/code><\/pre>\n
\u8868\u540d\u5728\u6570\u636e\u5e93\u5f53\u4e2d\u4e00\u822c\u5efa\u8bae\u4ee5\uff1at_\u6216\u8005tbl_\u5f00\u59cb\u3002<\/p>\n
\u521b\u5efa\u5b66\u751f\u8868\uff1a
\n\u5b66\u751f\u4fe1\u606f\u5305\u62ec\uff1a
\n\u5b66\u53f7\u3001\u59d3\u540d\u3001\u6027\u522b\u3001\u73ed\u7ea7\u7f16\u53f7\u3001\u751f\u65e5
\n\u5b66\u53f7\uff1abigint
\n\u59d3\u540d\uff1avarchar
\n\u6027\u522b\uff1achar
\n\u73ed\u7ea7\u7f16\u53f7\uff1aint
\n\u751f\u65e5\uff1achar<\/p>\n
    create table t_student(\n        no bigint,\n        name varchar(255),\n        sex char(1),\n        classno varchar(255),\n        birth char(10)\n    );<\/code><\/pre>\n
7\u3001insert\u8bed\u53e5\u63d2\u5165\u6570\u636e<\/h2>\n
\u8bed\u6cd5\u683c\u5f0f\uff1a\n    insert into \u8868\u540d(\u5b57\u6bb5\u540d1,\u5b57\u6bb5\u540d2,\u5b57\u6bb5\u540d3,....) values(\u503c1,\u503c2,\u503c3,....)\n    \u8981\u6c42\uff1a\u5b57\u6bb5\u7684\u6570\u91cf\u548c\u503c\u7684\u6570\u91cf\u76f8\u540c\uff0c\u5e76\u4e14\u6570\u636e\u7c7b\u578b\u8981\u5bf9\u5e94\u76f8\u540c\u3002\n\ninsert into t_student(no,name,sex,classno,birth) values(1,'zhangsan','1','gaosan1ban');\nERROR 1136 (21S01): Column count doesn't match value count at row 1\n\ninsert into t_student(no,name,sex,classno,birth) values(1,'zhangsan','1','gaosan1ban', '1950-10-12');\n\nmysql> select * from t_student;\n+------+----------+------+------------+------------+\n| no   | name     | sex  | classno    | birth      |\n+------+----------+------+------------+------------+\n|    1 | zhangsan | 1    | gaosan1ban | 1950-10-12 |\n+------+----------+------+------------+------------+\n\ninsert into t_student(name,sex,classno,birth,no) values('lisi','1','gaosan1ban', '1950-10-12',2);\n\nmysql> select * from t_student;\n+------+----------+------+------------+------------+\n| no   | name     | sex  | classno    | birth      |\n+------+----------+------+------------+------------+\n|    1 | zhangsan | 1    | gaosan1ban | 1950-10-12 |\n|    2 | lisi     | 1    | gaosan1ban | 1950-10-12 |\n+------+----------+------+------------+------------+\n\ninsert into t_student(name) values('wangwu'); \/\/ \u9664name\u5b57\u6bb5\u4e4b\u5916\uff0c\u5269\u4e0b\u7684\u6240\u6709\u5b57\u6bb5\u81ea\u52a8\u63d2\u5165NULL\u3002\nmysql> select * from t_student;\n+------+----------+------+------------+------------+\n| no   | name     | sex  | classno    | birth      |\n+------+----------+------+------------+------------+\n|    1 | zhangsan | 1    | gaosan1ban | 1950-10-12 |\n|    2 | lisi     | 1    | gaosan1ban | 1950-10-12 |\n| NULL | wangwu   | NULL | NULL       | NULL       |\n+------+----------+------+------------+------------+\n\ninsert into t_student(no) values(3); \nmysql> select * from t_student;\n+------+----------+------+------------+------------+\n| no   | name     | sex  | classno    | birth      |\n+------+----------+------+------------+------------+\n|    1 | zhangsan | 1    | gaosan1ban | 1950-10-12 |\n|    2 | lisi     | 1    | gaosan1ban | 1950-10-12 |\n| NULL | wangwu   | NULL | NULL       | NULL       |\n|    3 | NULL     | NULL | NULL       | NULL       |\n+------+----------+------+------------+------------+\n\ndrop table if exists t_student; \/\/ \u5f53\u8fd9\u4e2a\u8868\u5b58\u5728\u7684\u8bdd\u5220\u9664\u3002\ncreate table t_student(\n    no bigint,\n    name varchar(255),\n    sex char(1) default 1,\n    classno varchar(255),\n    birth char(10)\n);\n\ninsert into t_student(name) values('zhangsan');\nmysql> select * from t_student;\n+------+----------+------+---------+-------+\n| no   | name     | sex  | classno | birth |\n+------+----------+------+---------+-------+\n| NULL | zhangsan | 1    | NULL    | NULL  |\n+------+----------+------+---------+-------+<\/code><\/pre>\n
\u9700\u8981\u6ce8\u610f\u7684\u5730\u65b9\uff1a
\n\u5f53\u4e00\u6761insert\u8bed\u53e5\u6267\u884c\u6210\u529f\u4e4b\u540e\uff0c\u8868\u683c\u5f53\u4e2d\u5fc5\u7136\u4f1a\u591a\u4e00\u884c\u8bb0\u5f55\u3002
\n\u5373\u4f7f\u591a\u7684\u8fd9\u4e00\u884c\u8bb0\u5f55\u5f53\u4e2d\u67d0\u4e9b\u5b57\u6bb5\u662fNULL\uff0c\u540e\u671f\u4e5f\u6ca1\u6709\u529e\u6cd5\u5728\u6267\u884c
\ninsert\u8bed\u53e5\u63d2\u5165\u6570\u636e\u4e86\uff0c\u53ea\u80fd\u4f7f\u7528update\u8fdb\u884c\u66f4\u65b0\u3002<\/p>\n
\u5b57\u6bb5\u53ef\u4ee5\u7701\u7565\u4e0d\u5199\uff0c\u4f46\u662f\u540e\u9762\u7684value\u5bf9\u6570\u91cf\u548c\u987a\u5e8f\u90fd\u6709\u8981\u6c42\u3002<\/strong>
\ninsert into t_student values(1,'jack','0','gaosan2ban','1986-10-23');
\nmysql> select * from t_student;
\n+------+----------+------+------------+------------+
\n| no   | name     | sex  | classno    | birth      |
\n+------+----------+------+------------+------------+
\n| NULL | zhangsan | 1    | NULL       | NULL       |
\n|    1 | jack     | 0    | gaosan2ban | 1986-10-23 |
\n+------+----------+------+------------+------------+<\/p>\n
insert into t_student values(1,'jack','0','gaosan2ban');\nERROR 1136 (21S01): Column count doesn't match value count at row 1\n\n\/\/ \u4e00\u6b21\u63d2\u5165\u591a\u884c\u6570\u636e\ninsert into t_student\n    (no,name,sex,classno,birth) \nvalues\n    (3,'rose','1','gaosi2ban','1952-12-14'),(4,'laotie','1','gaosi2ban','1955-12-14');\n\nmysql> select * from t_student;\n+------+----------+------+------------+------------+\n| no   | name     | sex  | classno    | birth      |\n+------+----------+------+------------+------------+\n| NULL | zhangsan | 1    | NULL       | NULL       |\n|    1 | jack     | 0    | gaosan2ban | 1986-10-23 |\n|    3 | rose     | 1    | gaosi2ban  | 1952-12-14 |\n|    4 | laotie   | 1    | gaosi2ban  | 1955-12-14 |\n+------+----------+------+------------+------------+<\/code><\/pre>\n
8\u3001\u8868\u7684\u590d\u5236<\/h2>\n
\u8bed\u6cd5\uff1a\n    create table \u8868\u540d as select\u8bed\u53e5;\n\n    mysql ->create table emp2 as select empno,ename from emp;\n    \u5c06\u67e5\u8be2\u7ed3\u679c\u5f53\u505a\u8868\u521b\u5efa\u51fa\u6765\u3002<\/code><\/pre>\n
9\u3001\u5c06\u67e5\u8be2\u7ed3\u679c\u63d2\u5165\u5230\u4e00\u5f20\u8868\u4e2d\uff1f<\/strong><\/h2>\n
mysql> insert into dept1 select * from dept;\nmysql> select * from dept1;\n+--------+------------+----------+\n| DEPTNO | DNAME      | LOC      |\n+--------+------------+----------+\n|     10 | ACCOUNTING | NEW YORK |\n|     20 | RESEARCH   | DALLAS   |\n|     30 | SALES      | CHICAGO  |\n|     40 | OPERATIONS | BOSTON   |\n|     10 | ACCOUNTING | NEW YORK |\n|     20 | RESEARCH   | DALLAS   |\n|     30 | SALES      | CHICAGO  |\n|     40 | OPERATIONS | BOSTON   |\n+--------+------------+----------+<\/code><\/pre>\n
10\u3001\u4fee\u6539\u6570\u636e\uff1aupdate<\/h2>\n
\u8bed\u6cd5\u683c\u5f0f\uff1a
\nupdate \u8868\u540d set \u5b57\u6bb5\u540d1=\u503c1,\u5b57\u6bb5\u540d2=\u503c2... where \u6761\u4ef6;<\/p>\n
\u6ce8\u610f\uff1a\u6ca1\u6709\u6761\u4ef6\u6574\u5f20\u8868\u6570\u636e\u5168\u90e8\u66f4\u65b0\u3002<\/p>\n
\u6848\u4f8b\uff1a\u5c06\u90e8\u95e810\u7684LOC\u4fee\u6539\u4e3aSHANGHAI\uff0c\u5c06\u90e8\u95e8\u540d\u79f0\u4fee\u6539\u4e3aRENSHIBU<\/p>\n
update dept1 set loc = 'SHANGHAI', dname = 'RENSHIBU' where deptno = 10;\nmysql> select * from dept1;\n+--------+------------+----------+\n| DEPTNO | DNAME      | LOC      |\n+--------+------------+----------+\n|     10 | RENSHIBU   | SHANGHAI |\n|     20 | RESEARCH   | DALLAS   |\n|     30 | SALES      | CHICAGO  |\n|     40 | OPERATIONS | BOSTON   |\n|     10 | RENSHIBU   | SHANGHAI |\n|     20 | RESEARCH   | DALLAS   |\n|     30 | SALES      | CHICAGO  |\n|     40 | OPERATIONS | BOSTON   |\n+--------+------------+----------+\n\n\u66f4\u65b0\u6240\u6709\u8bb0\u5f55\n    update dept1 set loc = 'x', dname = 'y';\n    mysql> select * from dept1;\n    +--------+-------+------+\n    | DEPTNO | DNAME | LOC  |\n    +--------+-------+------+\n    |     10 | y     | x    |\n    |     20 | y     | x    |\n    |     30 | y     | x    |\n    |     40 | y     | x    |\n    |     10 | y     | x    |\n    |     20 | y     | x    |\n    |     30 | y     | x    |\n    |     40 | y     | x    |\n    +--------+-------+------+<\/code><\/pre>\n
11\u3001\u5220\u9664\u6570\u636e\uff1f<\/h2>\n
\u8bed\u6cd5\u683c\u5f0f\uff1a
\ndelete from \u8868\u540d where \u6761\u4ef6;
\n\u6ce8\u610f\uff1a\u6ca1\u6709\u6761\u4ef6\u5168\u90e8\u5220\u9664\u3002<\/strong><\/p>\n
eg.\u5220\u966410\u90e8\u95e8\u6570\u636e\uff1f<\/p>\n
    delete from dept1 where deptno = 10;<\/code><\/pre>\n
eg.\u5220\u9664\u6240\u6709\u8bb0\u5f55\uff1f<\/p>\n
    delete from dept1;<\/code><\/pre>\n
eg.<\/strong>\u600e\u4e48\u5220\u9664\u5927\u8868\u4e2d\u7684\u6570\u636e\uff1f<\/strong>\uff08\u91cd\u70b9\uff09<\/p>\n
    truncate table \u8868\u540d; \/\/ \u8868\u88ab\u622a\u65ad\uff0c\u4e0d\u53ef\u56de\u6eda\u3002\u6c38\u4e45\u4e22\u5931\u3002\n\u5220\u9664\u8868\uff1f\n    drop table \u8868\u540d; \/\/ \u8fd9\u4e2a\u901a\u7528\u3002\n    drop table if exists \u8868\u540d; \/\/ oracle\u4e0d\u652f\u6301\u8fd9\u79cd\u5199\u6cd5\u3002<\/code><\/pre>\n
12\u3001\u5bf9\u4e8e\u8868\u7ed3\u6784\u7684\u4fee\u6539<\/h2>\n
\u8fd9\u91cc\u4e0d\u8bb2\u4e86\uff0c\u5927\u5bb6\u4f7f\u7528\u5de5\u5177\u5b8c\u6210\u5373\u53ef\uff0c\u56e0\u4e3a\u5728\u5b9e\u9645\u5f00\u53d1\u4e2d\u8868\u4e00\u65e6
\n\u8bbe\u8ba1\u597d\u4e4b\u540e\uff0c\u5bf9\u8868\u7ed3\u6784\u7684\u4fee\u6539\u662f\u5f88\u5c11\u7684\uff0c\u4fee\u6539\u8868\u7ed3\u6784\u5c31\u662f\u5bf9\u4e4b\u524d\u7684\u8bbe\u8ba1\u8fdb\u884c\u4e86\u5426\u5b9a\uff0c\u5373\u4f7f
\n\u9700\u8981\u4fee\u6539\u8868\u7ed3\u6784\uff0c\u6211\u4eec\u4e5f\u53ef\u4ee5\u76f4\u63a5\u4f7f\u7528\u5de5\u5177\u64cd\u4f5c\u3002\u4fee\u6539\u8868\u7ed3\u6784\u7684\u8bed\u53e5\u4e0d\u4f1a\u51fa\u73b0\u5728Java\u4ee3\u7801\u5f53\u4e2d\u3002
\n\u51fa\u73b0\u5728java\u4ee3\u7801\u5f53\u4e2d\u7684sql\u5305\u62ec\uff1ainsert delete update select\uff08\u8fd9\u4e9b\u90fd\u662f\u8868\u4e2d\u7684\u6570\u636e\u64cd\u4f5c\u3002\uff09<\/p>\n
\u589e\u5220\u6539\u67e5\u6709\u4e00\u4e2a\u672f\u8bed\uff1aCRUD\u64cd\u4f5c
\nCreate\uff08\u589e\uff09 Retrieve\uff08\u68c0\u7d22\uff09 Update\uff08\u4fee\u6539\uff09 Delete\uff08\u5220\u9664\uff09<\/strong><\/p>\n
13\u3001\u7ea6\u675f(Constraint)<\/h2>\n
13.1\u3001\u4ec0\u4e48\u662f\u7ea6\u675f\uff1f\u5e38\u89c1\u7684\u7ea6\u675f\u6709\u54ea\u4e9b\u5462\uff1f<\/h2>\n
\u5728\u521b\u5efa\u8868\u7684\u65f6\u5019\uff0c\u53ef\u4ee5\u7ed9\u8868\u7684\u5b57\u6bb5\u6dfb\u52a0\u76f8\u5e94\u7684\u7ea6\u675f\uff0c\u6dfb\u52a0\u7ea6\u675f\u7684\u76ee\u7684\u662f\u4e3a\u4e86\u4fdd\u8bc1\u8868\u4e2d\u6570\u636e\u7684
\n\u5408\u6cd5\u6027\u3001\u6709\u6548\u6027\u3001\u5b8c\u6574\u6027\u3002
\n\u5e38\u89c1\u7684\u7ea6\u675f\u6709\u54ea\u4e9b\u5462\uff1f
\n\u975e\u7a7a\u7ea6\u675f<\/strong>(not null)\uff1a\u7ea6\u675f\u7684\u5b57\u6bb5\u4e0d\u80fd\u4e3aNULL
\n\u552f\u4e00\u7ea6\u675f<\/strong>(unique)\uff1a\u7ea6\u675f\u7684\u5b57\u6bb5\u4e0d\u80fd\u91cd\u590d
\n\u4e3b\u952e\u7ea6\u675f<\/strong>(primary key)\uff1a\u7ea6\u675f\u7684\u5b57\u6bb5\u65e2\u4e0d\u80fd\u4e3aNULL\uff0c\u4e5f\u4e0d\u80fd\u91cd\u590d\uff08\u7b80\u79f0PK\uff09
\n\u5916\u952e\u7ea6\u675f<\/strong>(foreign key)\uff1a...\uff08\u7b80\u79f0FK\uff09
\n\u68c0\u67e5\u7ea6\u675f<\/strong>(check)\uff1a\u6ce8\u610fOracle\u6570\u636e\u5e93\u6709check\u7ea6\u675f\uff0c\u4f46\u662fmysql\u6ca1\u6709\uff0c\u76ee\u524dmysql\u4e0d\u652f\u6301\u8be5\u7ea6\u675f\u3002<\/p>\n
13.2\u3001\u975e\u7a7a\u7ea6\u675f not null<\/h2>\n
drop table if exists t_user;\ncreate table t_user(\n    id int,\n    username varchar(255) not null,\n    password varchar(255)\n);\ninsert into t_user(id,password) values(1,'123');\nERROR 1364 (HY000): Field 'username' doesn't have a default value\ninsert into t_user(id,username,password) values(1,'lisi','123');<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
1.\u5173\u4e8e\u67e5\u8be2\u7ed3\u679c\u96c6\u7684\u53bb\u91cd\uff1f \u5173\u952e\u5b57 distinct mysql> select distinct job from emp; +—   \u2026 …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0},"categories":[23],"tags":[24],"_links":{"self":[{"href":"\/index.php?rest_route=\/wp\/v2\/posts\/290"}],"collection":[{"href":"\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=290"}],"version-history":[{"count":1,"href":"\/index.php?rest_route=\/wp\/v2\/posts\/290\/revisions"}],"predecessor-version":[{"id":291,"href":"\/index.php?rest_route=\/wp\/v2\/posts\/290\/revisions\/291"}],"wp:attachment":[{"href":"\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=290"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=290"},{"taxonomy":"post_tag","embeddable":true,"href":"\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=290"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}