{"id":810,"date":"2025-01-21T16:50:51","date_gmt":"2025-01-21T08:50:51","guid":{"rendered":"https:\/\/eve2333.top\/?p=810"},"modified":"2025-06-21T14:39:52","modified_gmt":"2025-06-21T06:39:52","slug":"mysql%e6%95%b0%e6%8d%ae%e5%ba%93%e5%85%a5%e9%97%a8%e5%88%b0%e5%a4%a7%e8%9b%87%e5%b0%9a%e7%a1%85%e8%b0%b7%e5%ae%8b%e7%ba%a2%e5%ba%b7%e8%80%81%e5%b8%88%e7%ac%94%e8%ae%b0-%e5%9f%ba%e7%a1%80%e7%af%87-pa-3","status":"publish","type":"post","link":"https:\/\/eve2333.top\/?p=810","title":{"rendered":"MySQL\u6570\u636e\u5e93\u5165\u95e8\u5230\u5927\u86c7\u5c1a\u7845\u8c37\u5b8b\u7ea2\u5eb7\u8001\u5e08\u7b14\u8bb0 \u57fa\u7840\u7bc7 part 6"},"content":{"rendered":"\n

\u7b2c06\u7ae0_\u591a\u8868\u67e5\u8be2<\/h1>\n\n\n\n

\u591a\u8868\u67e5\u8be2\uff0c\u4e5f\u79f0\u4e3a\u5173\u8054\u67e5\u8be2\uff0c\u6307\u4e24\u4e2a\u6216\u66f4\u591a\u4e2a\u8868\u4e00\u8d77\u5b8c\u6210\u67e5\u8be2\u64cd\u4f5c\u3002<\/p>\n\n\n\n

\u8fd9\u4e9b\u4e00\u8d77\u67e5\u8be2\u7684\u8868\u4e4b\u95f4\u662f\u6709\u5173\u7cfb\u7684\uff08\u4e00\u5bf9\u4e00\u3001\u4e00\u5bf9\u591a\uff09\uff0c\u5b83\u4eec\u4e4b\u95f4\u4e00\u5b9a\u662f\u6709\u5173\u8054\u5b57\u6bb5\uff0c\u8fd9\u4e2a \u5173\u8054\u5b57\u6bb5\u53ef\u80fd\u5efa\u7acb\u4e86\u5916\u952e\uff0c\u4e5f\u53ef\u80fd\u6ca1\u6709\u5efa\u7acb\u5916\u952e\u3002\u6bd4\u5982\uff1a\u5458\u5de5\u8868\u548c\u90e8\u95e8\u8868\uff0c\u8fd9\u4e24\u4e2a\u8868\u4f9d\u9760\u201c\u90e8\u95e8\u7f16\u53f7\u201d\u8fdb \u884c\u5173\u8054. <\/p>\n\n\n\n

#\u719f\u6089\u5e38\u89c1\u7684\u51e0\u4e2a\u8868\nDESC employees;\nDESC departments;\nDESC locations;\n\n#\u67e5\u8be2\u5458\u5de5\u540d\u4e3a'Abel'\u7684\u4eba\u5728\u54ea\u4e2a\u57ce\u5e02\u5de5\u4f5c\uff1f\nSELECT *\nFROM employees\nWHERE last_name 'Abel';\n\nSELECT *\nFROM departments\nWHERE department_id=80;\n\nSELECT *\nFROM locations\nWHERE location_id =2500;<\/code><\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\u4ece\u591a\u4e2a\u8868\u4e2d\u83b7\u53d6\u6570\u636e\uff1a\u4f1a\u5bfc\u81f4\u51e0\u4e2a\u8868\u6570\u636e\u76f8\u4e58,\u8fd9\u4f1a\u5bfc\u81f4\u5197\u4f59\u7b49\u7b49\u7684\u9519\u8bef : (\u4f1a\u9020\u6210\u6570\u636e\u7684\u5197\u4f59\uff0c\u67e5\u8be2\u4fe1\u606f\u7684\u65f6\u95f4\u4f1a\u53d8\u957f\uff0c\u5360\u7528\u8d44\u6e90\uff0c\u4e0e\u78c1\u76d8IO\u7684\u6b21\u6570\u591a\u6027\u80fd\u4f1a\u53d8\u5dee\uff0c\u7ef4\u62a4\u7684\u6210\u672c\u4e5f\u9ad8.\u6211\u4eec\u628a\u4e0a\u8ff0\u591a\u8868\u67e5\u8be2\u4e2d\u51fa\u73b0\u7684\u95ee\u9898\u79f0\u4e3a\uff1a\u7b1b\u5361\u5c14\u79ef\u7684\u9519\u8bef\u3002<\/p>\n\n\n\n
\u591a\u8868\u67e5\u8be2\u8bb2\u89e3<\/h3>\n\n\n\n
\u5bf9\u4e8e\u5982\u4e0b\u6570\u636e:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
##2.\u591a\u8868\u7684\u67e5\u8be2\u5982\u4f55\u5b9e\u73b0\uff1f\n#\u9519\u8bef\u7684\u5b9e\u73b0\u65b9\u5f0f:\nSELECT employee_id,department_name\nFROM employees,departments;  #\u67e5\u8be2\u51fa2889\u6761\u8bb0\u5f55\n\nSELECT *\nFROM employees; #107\u6761\u8bb0\u5f55\n\nSELECT 2889\/ 107\nFROM DUAL;\n\nSELECT\nFROM departments; #27\u6761\u8bb0\u5f55\n#\u9519\u8bef\u7684\u5b9e\u73b0\u65b9\u5f0f:\u6bcf\u4e2a\u5458\u5de5\u548c\u6bcf\u4e2a\u90e8\u95e8\u5339\u914d\u4e86\u4e00\u904d\n\n#\u9519\u8bef\u539f\u56e0: \u7f3a\u5c11\u4e86\u591a\u8868\u7684\u8fde\u7eed\u6761\u4ef6<\/code><\/pre>\n\n\n\n
\u7b1b\u5361\u5c14\u79ef\uff08\u6216\u4ea4\u53c9\u8fde\u63a5\uff09\u7684\u7406\u89e3 :\u7b1b\u5361\u5c14\u4e58\u79ef\u662f\u4e00\u4e2a\u6570\u5b66\u8fd0\u7b97\u3002\u5047\u8bbe\u6211\u6709\u4e24\u4e2a\u96c6\u5408 X \u548c Y\uff0c\u90a3\u4e48 X \u548c Y \u7684\u7b1b\u5361\u5c14\u79ef\u5c31\u662f X \u548c Y \u7684\u6240\u6709\u53ef\u80fd \u7ec4\u5408\uff0c\u4e5f\u5c31\u662f\u7b2c\u4e00\u4e2a\u5bf9\u8c61\u6765\u81ea\u4e8e X\uff0c\u7b2c\u4e8c\u4e2a\u5bf9\u8c61\u6765\u81ea\u4e8e Y \u7684\u6240\u6709\u53ef\u80fd\u3002\u7ec4\u5408\u7684\u4e2a\u6570\u5373\u4e3a\u4e24\u4e2a\u96c6\u5408\u4e2d\u5143\u7d20 \u4e2a\u6570\u7684\u4e58\u79ef\u6570\u3002<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\u200b<\/p>\n\n\n\n
#3.\u591a\u8868\u67e5\u8be2\u7684\u6b63\u786e\u65b9\u5f0f\uff1a\u9700\u8981\u6709\u8fde\u63a5\u6761\u4ef6\n\nSELECT employee_id,department_name\nFROM employees,departments\n#\u4e24\u4e2a\u8868\u7684\u8fde\u63a5\u6761\u4ef6\nWHERE employees.`department_id` =departments.department_id;<\/code><\/pre>\n\n\n\n
<\/p>\n\n\n\n
\n
\u7b1b\u5361\u5c14\u79ef\u7684\u9519\u8bef\u4f1a\u5728\u4e0b\u9762\u6761\u4ef6\u4e0b\u4ea7\u751f\uff1a<\/p>\n\n\n\n
    \n
  • \u7701\u7565\u591a\u4e2a\u8868\u7684\u8fde\u63a5\u6761\u4ef6\uff08\u6216\u5173\u8054\u6761\u4ef6\uff09<\/li>\n\n\n\n
  • \u8fde\u63a5\u6761\u4ef6\uff08\u6216\u5173\u8054\u6761\u4ef6\uff09\u65e0\u6548<\/li>\n\n\n\n
  • \u6240\u6709\u8868\u4e2d\u7684\u6240\u6709\u884c\u4e92\u76f8\u8fde\u63a5<\/li>\n<\/ul>\n\n\n\n
    \u4e3a\u4e86\u907f\u514d\u7b1b\u5361\u5c14\u79ef\uff0c \u53ef\u4ee5\u5728 WHERE \u52a0\u5165\u6709\u6548\u7684\u8fde\u63a5\u6761\u4ef6\u3002<\/p>\n\n\n\n
    \u52a0\u5165\u8fde\u63a5\u6761\u4ef6\u540e\uff0c\u67e5\u8be2\u8bed\u6cd5\uff1a<\/p>\n\n\n\n
    SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2;  #\u8fde\u63a5\u6761\u4ef6<\/p>\n\n\n\n
    \u5728 WHERE\u5b50\u53e5\u4e2d\u5199\u5165\u8fde\u63a5\u6761\u4ef6\u3002<\/p>\n\n\n\n
    \u6b63\u786e\u5199\u6cd5\uff1a<\/p>\n\n\n\n
    #\u6848\u4f8b\uff1a\u67e5\u8be2\u5458\u5de5\u7684\u59d3\u540d\u53ca\u5176\u90e8\u95e8\u540d\u79f0 SELECT last_name, department_name FROM employees, departments WHERE employees.department_id = departments.department_id;<\/p>\n\n\n\n
    \u5728\u8868\u4e2d\u6709\u76f8\u540c\u5217\u65f6\uff0c\u5728\u5217\u540d\u4e4b\u524d\u52a0\u4e0a\u8868\u540d\u524d\u7f00\u3002<\/p>\n<\/blockquote>\n\n\n\n
    #4\uff0c\u5982\u679c\u67e5\u8be2\u8bed\u53e5\u4e2d\u51fa\u73b0\u4e86\u591a\u4e2a\u8868\u4e2d\u90fd\u5b58\u5728\u7684\u5b57\u6bb5\uff0c\u5219\u5fc5\u987b\u6307\u660e\u6b64\u5b57\u6bb5\u6240\u5728\u7684\u8868\u3002\nSELECT employees.employee_id,departments.department_name,employees.department_id\nFROM employees,departments\nWHERE employees.`department_id`=departments.department_id;\n#\u5efa\u8bae\uff1a\u4ecesql\u4f18\u5316\u7684\u89d2\u5ea6\uff0c\u5efa\u8bae\u591a\u8868\u67e5\u8be2\u65f6\uff0c\u6bcf\u4e2a\u5b57\u6bb5\u524d\u90fd\u6307\u660e\u5176\u6240\u5728\u7684\u8868\u3002<\/code><\/pre>\n\n\n\n
    5.\u53ef\u4ee5\u7ed9\u8868\u8d77\u522b\u540d\uff0c\u5728SELECT\u548cWHERE\u4e2d\u4f7f\u7528\u8868\u7684\u522b\u540d\u3002\nSELECT emp.employee_id,dept.department_name,emp.department_id\nFROMemployees emp,departments dept\nWHERE emp.`department_id`=dept.department_id;\n\n#\u5982\u679c\u7ed9\u8868\u8d77\u4e86\u522b\u540d\uff0c\u4e00\u65e6\u5728SELECT\u6216WHERE\u4e2d\u4f7f\u7528\u8868\u540d\u7684\u8bdd\uff0c\u5219\u5fc5\u987b\u4f7f\u7528\u8868\u7684\u522b\u540d\uff0c\u800c\u4e0d\u80fd\u518d\u4f7f\u7528\u8868\u7684\u539f\u540d\u3002\n#\u5982\u4e0b\u7684\u64cd\u4f5c\u662f\u9519\u8bef\u7684\nSELECT emp.employee_id,departments.department_name,emp.department_id\nFROM employees emp,departments dept\nWHERE emp.`department_id`=dept.department_id;<\/code><\/pre>\n\n\n\n
    \u770b\u6267\u884c\u987a\u5e8f \u5148\u6267\u884cfrom \u5728from\u4e2d\u8d77\u522b\u540d where\u53ef\u4ee5\u7528\u522b\u540d,\u8fd9\u662f\u7ed9\u8868\u8d77\u522b\u540d\uff0c\u53c8\u4e0d\u662f\u7ed9\u5b57\u6bb5\u8d77\u522b\u540d\uff0cFROM\u662f\u6700\u5f00\u59cb\u5c31\u4f1a\u6267\u884c\u7684\u5173\u952e\u5b57\uff0c\u6240\u4ee5\u540e\u9762\u6267\u884c\u7684\u90fd\u53ef\u4ee5\u7528\u5230<\/p>\n\n\n\n
    #\u5fc5\u987b\u5728\u4e0d\u540c\u8868\u4e4b\u95f4\u627e\u4e00\u4e2a\u4ecb\u7ecd\u4eba\uff0c\u5373\u6709\u4e00\u4e2a\u5173\u8054\u7684\u5143\u7d20\n#6\uff0e\u7ed3\u8bba\uff1a\u5982\u679c\u6709n\u4e2a\u8868\u5b9e\u73b0\u591a\u8868\u7684\u67e5\u8be2\uff0c\u5219\u9700\u8981\u81f3\u5c11n-1\u4e2a\u8fde\u63a5\u6761\u4ef6\n#\u7ec3\u4e60\uff1a\u67e5\u8be2\u5458\u5de5\u7684employee_id\uff0clast_name\uff0cdepartment_name\uff0ccity\n\nSELECT e.employee_id,e.last_name,d.department_name,l.city,e.department_id,l.location_id\nFROM employees e,departments d,locations1\nWHERE e.`department_id`=d.`department_id`\nAND d.`location_id`=l.`location_id`;<\/code><\/pre>\n\n\n\n
    \u6f14\u7ece\u5f0f\uff1a\u63d0\u51fa\u95ee\u98981---->\u89e3\u51b3\u95ee\u98981--------->\u63d0\u51fa\u95ee\u98982----->\u89e3\u51b3\u95ee\u98982
    \u5f52\u7eb3\u5f0f\uff1a\u603b--\u5206<\/p>\n\n\n\n
    #7.\u591a\u8868\u67e5\u8be2\u7684\u5206\u7c7b
    \u89d2\u5ea61\uff1a\u7b49\u503c\u8fde\u63a5VS\u975e\u7b49\u503c\u8fde\u63a5
    \u89d2\u5ea62\uff1a\u81ea\u8fde\u63a5VS\u975e\u81ea\u8fde\u63a5
    \u89d2\u5ea63\uff1a\u5185\u8fde\u63a5VS\u5916\u8fde\u63a5<\/p>\n\n\n\n
    \u7b49\u503c\u8fde\u63a5VS\u975e\u7b49\u503c\u8fde\u63a5<\/h3>\n\n\n\n
    SELECT *\nFROM job_grades;\n\nSELECT e.last_name,e.salary,j.grade_level\nFROM employees e,job_grades j\n#where e.`salary` between j.`lowest_saland` and j.`highest_sal`\nWHERE e.`salary` >= j.`lowest_sal` AND e.`salary`<=j.`highest sal`;\n<\/code><\/pre>\n\n\n\n
    \u81ea\u8fde\u63a5VS\u975e\u81ea\u8fde\u63a5<\/h3>\n\n\n\n
    \u81ea\u6211\u5f15\u7528<\/p>\n\n\n\n
    #7.2\u81ea\u8fde\u63a5VS\u975e\u81ea\u8fde\u63a5\nSELECT * FROM employees;\n\n#\u81ea\u8fde\u63a5\u7684\u4f8b\u5b50\uff1a\n#\u7ec3\u4e60\uff1a\u67e5\u8be2\u5458\u5de5id\uff0c\u5458\u5de5\u59d3\u540d\u53ca\u5176\u7ba1\u7406\u8005\u7684id\u548c\u59d3\u540d\n\nSELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name\nFROM employees emp\uff0cemployees mgr\nWHERE emp.`manager_id`=mgr.`employee_id`;<\/code><\/pre>\n\n\n\n
    \u8bb2\u5458\u5de5\u8868\u91cc\u7684\u7ba1\u7406\u5458\u5f53\u505a\u4e00\u4e2a\u8868\u3002\u53ea\u6709\u5f53\u5458\u5de5\u8868\u91cc\u7684\u7ba1\u7406\u5458\u548c\u7ba1\u7406\u8868\u91cc\u7684\u5458\u5de5\u76f8\u7b49\u5c31\u53ef\u4ee5\u4e86\u3002\u7ba1\u7406\u8868\u91cc\u7684\u5458\u5de5\u5176\u5b9e\u5c31\u662f\u5b58\u653e\u7684\u7ba1\u7406\u5458,\u4e0d\u61c2\u76f8\u7b49\u7684\uff0c\u56e0\u4e3a\u7ba1\u7406\u8005\u4e5f\u662f\u516c\u53f8\u7684\u5458\u5de5\uff0c\u5c31\u4f1a\u6709employee_id\u548cmanager_id\uff0c\u975e\u7ba1\u7406\u8005\u7684manger_id\u4e5f\u5c31\u662f\u7ba1\u7406\u8005\u7684employee_id\uff0c\u6240\u4ee5\u7528\u8fd9\u4e2a\u5173\u8054\u8d77\u6765<\/p>\n\n\n\n
    \u5185\u8fde\u63a5VS\u5916\u8fde\u63a5<\/h3>\n\n\n\n
    \u4ea4\u96c6 \u5e76\u96c6 \u5185\u5916\u8fde\u63a5  \u5185\u8fde\u63a5\u5c31\u76f8\u5f53\u4e8e\u4e24\u4e2a\u8868\u7684\u4ea4\u96c6\uff0c\u5916\u8fde\u63a5\u5c31\u76f8\u5f53\u4e8e\u4e24\u4e2a\u8868\u7684\u5e76\u96c6<\/strong><\/p>\n\n\n\n
    #7.3\u5185\u8fde\u63a5Vs\u5916\u8fde\u63a5\n\n#\u5185\u8fde\u63a5\uff1a\u5408\u5e76\u5177\u6709\u540c\u4e00\u5217\u7684\u4e24\u4e2a\u4ee5\u4e0a\u7684\u8868\u7684\u884c\uff0c\u7ed3\u679c\u96c6\u4e2d\u4e0d\u5305\u542b\u4e00\u4e2a\u8868\u4e0e\u53e6\u4e00\u4e2a\u8868\u4e0d\u5339\u914d\u7684\u884c\nSELECT employee_id,department_name\nFROM employees e,departments d\nWHERE e.department_id=d.department_id;#\u53ea\u6709106\u6761\u8bb0\u5f55\n\n#\u5916\u8fde\u63a5\uff1a\u5408\u5e76\u5177\u6709\u540c\u4e00\u5217\u7684\u4e24\u4e2a\u4ee5\u4e0a\u7684\u8868\u7684\u884c\uff0c\u7ed3\u679c\u96c6\u4e2d\u9664\u4e86\u5305\u542b\u4e00\u4e2a\u8868\u4e0e\u53e6\u4e00\u4e2a\u8868\u5339\u914d\u7684\u884c\u4e4b\u5916\uff0c\n#\u8fd8\u67e5\u8be2\u5230\u4e86\u5de6\u8868\u6216\u53f3\u8868\u4e2d\u4e0d\u5339\u914d\u7684\u884c\u3002\n#\u5916\u8fde\u63a5\u7684\u5206\u7c7b\uff1a\u5de6\u5916\u8fde\u63a5\u3001\u53f3\u5916\u8fde\u63a5\u3001\u6ee1\u5916\u8fde\u63a5<\/code><\/pre>\n\n\n\n
      \n
    • \u5185\u8fde\u63a5: \u5408\u5e76\u5177\u6709\u540c\u4e00\u5217\u7684\u4e24\u4e2a\u4ee5\u4e0a\u7684\u8868\u7684\u884c, \u7ed3\u679c\u96c6\u4e2d\u4e0d\u5305\u542b\u4e00\u4e2a\u8868\u4e0e\u53e6\u4e00\u4e2a\u8868\u4e0d\u5339\u914d\u7684\u884c<\/li>\n\n\n\n
    • \u5916\u8fde\u63a5: \u4e24\u4e2a\u8868\u5728\u8fde\u63a5\u8fc7\u7a0b\u4e2d\u9664\u4e86\u8fd4\u56de\u6ee1\u8db3\u8fde\u63a5\u6761\u4ef6\u7684\u884c\u4ee5\u5916\u8fd8\u8fd4\u56de\u5de6\uff08\u6216\u53f3\uff09\u8868\u4e2d\u4e0d\u6ee1\u8db3\u6761\u4ef6\u7684 \u884c \uff0c\u8fd9\u79cd\u8fde\u63a5\u79f0\u4e3a\u5de6\uff08\u6216\u53f3\uff09 \u5916\u8fde\u63a5\u3002\u6ca1\u6709\u5339\u914d\u7684\u884c\u65f6, \u7ed3\u679c\u8868\u4e2d\u76f8\u5e94\u7684\u5217\u4e3a\u7a7a(NULL)\u3002<\/li>\n\n\n\n
    • \u5982\u679c\u662f\u5de6\u5916\u8fde\u63a5\uff0c\u5219\u8fde\u63a5\u6761\u4ef6\u4e2d\u5de6\u8fb9\u7684\u8868\u4e5f\u79f0\u4e3a \u4e3b\u8868\uff0c\u53f3\u8fb9\u7684\u8868\u79f0\u4e3a \u4ece\u8868\u3002<\/li>\n\n\n\n
    • \u5982\u679c\u662f\u53f3\u5916\u8fde\u63a5\uff0c\u5219\u8fde\u63a5\u6761\u4ef6\u4e2d\u53f3\u8fb9\u7684\u8868\u4e5f\u79f0\u4e3a \u4e3b\u8868\uff0c\u5de6\u8fb9\u7684\u8868\u79f0\u4e3a \u4ece\u8868\u3002<\/li>\n<\/ul>\n\n\n\n
      \n
       \u9644\u5f55\uff1a\u5e38\u7528\u7684 SQL \u6807\u51c6\u6709\u54ea\u4e9b<\/h3>\n\n\n\n
      \u5728\u6b63\u5f0f\u5f00\u59cb\u8bb2\u8fde\u63a5\u8868\u7684\u79cd\u7c7b\u65f6\uff0c\u6211\u4eec\u9996\u5148\u9700\u8981\u77e5\u9053 SQL \u5b58\u5728\u4e0d\u540c\u7248\u672c\u7684\u6807\u51c6\u89c4\u8303\uff0c\u56e0\u4e3a\u4e0d\u540c\u89c4\u8303\u4e0b\u7684\u8868 \u8fde\u63a5\u64cd\u4f5c\u662f\u6709\u533a\u522b\u7684\u3002<\/p>\n\n\n\n
      SQL \u6709\u4e24\u4e2a\u4e3b\u8981\u7684\u6807\u51c6\uff0c\u5206\u522b\u662f SQL92 \u548c SQL99 \u300292 \u548c 99 \u4ee3\u8868\u4e86\u6807\u51c6\u63d0\u51fa\u7684\u65f6\u95f4\uff0cSQL92 \u5c31\u662f 92 \u5e74 \u63d0\u51fa\u7684\u6807\u51c6\u89c4\u8303\u3002\u5f53\u7136\u9664\u4e86 SQL92 \u548c SQL99 \u4ee5\u5916\uff0c\u8fd8\u5b58\u5728 SQL-86\u3001SQL-89\u3001SQL:2003\u3001SQL:2008\u3001 SQL:2011 \u548c SQL:2016 \u7b49\u5176\u4ed6\u7684\u6807\u51c6\u3002<\/p>\n\n\n\n
      \u8fd9\u4e48\u591a\u6807\u51c6\uff0c\u5230\u5e95\u8be5\u5b66\u4e60\u54ea\u4e2a\u5462\uff1f\u5b9e\u9645\u4e0a\u6700\u91cd\u8981\u7684 SQL \u6807\u51c6\u5c31\u662f SQL92 \u548c SQL99\u3002<\/strong>\u4e00\u822c\u6765\u8bf4 SQL92 \u7684 \u5f62\u5f0f\u66f4\u7b80\u5355\uff0c\u4f46\u662f\u5199\u7684 SQL \u8bed\u53e5\u4f1a\u6bd4\u8f83\u957f\uff0c\u53ef\u8bfb\u6027\u8f83\u5dee\u3002\u800c SQL99 \u76f8\u6bd4\u4e8e SQL92 \u6765\u8bf4\uff0c\u8bed\u6cd5\u66f4\u52a0\u590d\u6742\uff0c \u4f46\u53ef\u8bfb\u6027\u66f4\u5f3a\u3002\u6211\u4eec\u4ece\u8fd9\u4e24\u4e2a\u6807\u51c6\u53d1\u5e03\u7684\u9875\u6570\u4e5f\u80fd\u770b\u51fa\uff0cSQL92 \u7684\u6807\u51c6\u6709 500 \u9875\uff0c\u800c SQL99 \u6807\u51c6\u8d85\u8fc7\u4e86 1000 \u9875\u3002\u5b9e\u9645\u4e0a\u4ece SQL99 \u4e4b\u540e\uff0c\u5f88\u5c11\u6709\u4eba\u80fd\u638c\u63e1\u6240\u6709\u5185\u5bb9\uff0c\u56e0\u4e3a\u786e\u5b9e\u592a\u591a\u4e86\u3002\u5c31\u597d\u6bd4\u6211\u4eec\u4f7f\u7528 Windows\u3001Linux \u548c Office \u7684\u65f6\u5019\uff0c\u5f88\u5c11\u6709\u4eba\u80fd\u638c\u63e1\u5168\u90e8\u5185\u5bb9\u4e00\u6837\u3002\u6211\u4eec\u53ea\u9700\u8981\u638c\u63e1\u4e00\u4e9b\u6838\u5fc3\u7684\u529f\u80fd\uff0c\u6ee1 \u8db3\u65e5\u5e38\u5de5\u4f5c\u7684\u9700\u6c42\u5373\u53ef\u3002<\/p>\n\n\n\n
      SQL92 \u548c SQL99 \u662f\u7ecf\u5178\u7684 SQL \u6807\u51c6\uff0c\u4e5f\u5206\u522b\u53eb\u505a SQL-2 \u548c SQL-3 \u6807\u51c6\u3002<\/strong>\u4e5f\u6b63\u662f\u5728\u8fd9\u4e24\u4e2a\u6807\u51c6\u53d1\u5e03\u4e4b \u540e\uff0cSQL \u5f71\u54cd\u529b\u8d8a\u6765\u8d8a\u5927\uff0c\u751a\u81f3\u8d85\u8d8a\u4e86\u6570\u636e\u5e93\u9886\u57df\u3002\u73b0\u5982\u4eca SQL \u5df2\u7ecf\u4e0d\u4ec5\u4ec5\u662f\u6570\u636e\u5e93\u9886\u57df\u7684\u4e3b\u6d41\u8bed\u8a00\uff0c \u8fd8\u662f\u4fe1\u606f\u9886\u57df\u4e2d\u4fe1\u606f\u5904\u7406\u7684\u4e3b\u6d41\u8bed\u8a00\u3002\u5728\u56fe\u5f62\u68c0\u7d22\u3001\u56fe\u50cf\u68c0\u7d22\u4ee5\u53ca\u8bed\u97f3\u68c0\u7d22\u4e2d\u90fd\u80fd\u770b\u5230 SQL \u8bed\u8a00\u7684\u4f7f\u7528<\/p>\n<\/blockquote>\n\n\n\n
      #\u7ec3\u4e60\uff1a\u67e5\u8be2\u6240\u6709\u7684\u5458\u5de5\u7684last_name\uff0cdepartment_name\u4fe1\u606f\n\nSELECT employee_id,department_name\nFROM employees e,departments d\nWHERE e.`department_id`=d.department_id;#\u9700\u8981\u4f7f\u7528\u5de6\u5916\u8fde\u63a5\n\n#SQL92\u8bed\u6cd5\u5b9e\u73b0\u5185\u8fde\u63a5\uff1a\u89c1\u4e0a\uff0c\u7565\n#SQL92\u8bed\u6cd5\u5b9e\u73b0\u5916\u8fde\u63a5\uff1a\u4f7f\u7528+   ----------MySQL\u4e0d\u652f\u6301SQL92\u8bed\u6cd5\u4e2d\u5916\u8fde\u63a5\u7684\u5199\u6cd5\uff01\n#+\u597d\u50cf\u628a\u4e24\u6761\u4e0d\u7b49\u7684\u817f\u52a0\u957f\u4e00\u6bb5\n\nSELECT employee_id,department_name\nFROM employees e,departments d\nWHERE e.`department_id`=d.department_id\uff08+);\n\n#SQL99\u8bed\u6cd5\u4e2d\u4f7f\u7528JOIN ...ON\u7684\u65b9\u5f0f\u5b9e\u73b0\u591a\u8868\u7684\u67e5\u8be2\u3002\u8fd9\u79cd\u65b9\u5f0f\u4e5f\u80fd\u89e3\u51b3\u5916\u8fde\u63a5\u7684\u95ee\u9898\u3002MySQL\u662f\u652f\u6301\u6b64\u79cd\u65b9\u5f0f\u7684\n#SQL99\u8bed\u6cd5\u5982\u4f55\u5b9e\u73b0\u591a\u8868\u7684\u67e5\u8be2\u3002\n\n#SQL99\u8bed\u6cd5\u5b9e\u73b0\u5185\u8fde\u63a5\uff1a\nSELECT last_name,department_name\nFROM employees e INNER JOIN departments d\nON e.`department_id`=d.`department_id`;\n\nSELECT last_name,department_name,city\nFROM employeese JOIN departmentsd\nON e.`department_id`=d.`department_id`\nJOIN locations L\nON d.`location_id`=l.`location_id`;\n\n#SQL99\u8bed\u6cd5\u5b9e\u73b0\u5916\u8fde\u63a5\uff1a\n#\u5de6\u5916\u8fde\u63a5\uff1a\n#\u7ec3\u4e60\uff1a\u67e5\u8be2\u6240\u6709\u7684\u5458\u5de5\u7684last_name\uff0cdepartment_name\u4fe1\u606f\nSELECT last_name,department_name\nFROM employees e LEFT JOIN departments d\nON e.`department_id`=d.`department_id`;\n\n#\u53f3\u5916\u8fde\u63a5\uff1a\nSELECT last_name,department_name\nFROM employees e RIGHT OUTER JOIN departments d\nON e.`department_id`=d.`department_id`;\n\n\n\u6ee1\u5916\u8fde\u63a5\uff1amySql\u4e0d\u652f\u6301FULL OUTER JOIN<\/code><\/pre>\n\n\n\n
      \u5185\u8fde\u63a5\u5176\u5b9e\u662fINNER JOIN,\u5e73\u65f6\u662f\u628aINNER\u7701\u7565\u4e86.<\/p>\n\n\n\n
      #\u8fd9\u662foracle\nFROM employees e,departments d\nWHERE e.department_id = d.department_id(+);\n\nSELECT  employee_id,department_name\nFROM employees e,departments d\nWHERE e.department_id(+)=d.department_id;\n\n\n#\u6ee1\u5916\u8fde\u63a5(FULL OUTER JOIN)\nSELECT last_name,department_name\nFROM employees e full OUTER JOIN departments d\nON e.department_id =d.department_id;<\/code><\/pre>\n\n\n\n
      <\/p>\n\n\n\n
      \"\"<\/figure>\n\n\n\n
       \u4f7f\u7528SQL99\u5b9e\u73b07\u4e2dJOIN\u64cd\u4f5c<\/h3>\n\n\n\n
       UNION\u7684\u4f7f\u7528<\/p>\n\n\n\n
      \u5408\u5e76\u67e5\u8be2\u7ed3\u679c \u5229\u7528UNION\u5173\u952e\u5b57\uff0c\u53ef\u4ee5\u7ed9\u51fa\u591a\u6761SELECT\u8bed\u53e5\uff0c\u5e76\u5c06\u5b83\u4eec\u7684\u7ed3\u679c\u7ec4\u5408\u6210\u5355\u4e2a\u7ed3\u679c\u96c6\u3002\u5408\u5e76 \u65f6\uff0c\u4e24\u4e2a\u8868\u5bf9\u5e94\u7684\u5217\u6570\u548c\u6570\u636e\u7c7b\u578b\u5fc5\u987b\u76f8\u540c\uff0c\u5e76\u4e14\u76f8\u4e92\u5bf9\u5e94\u3002\u5404\u4e2aSELECT\u8bed\u53e5\u4e4b\u95f4\u4f7f\u7528UNION\u6216UNION ALL\u5173\u952e\u5b57\u5206\u9694\u3002<\/p>\n\n\n\n
      \u8bed\u6cd5\u683c\u5f0f\uff1a<\/p>\n\n\n\n
      SELECT column,... FROM table1
      UNION [ALL]
      SELECT column,... FROM table2<\/p>\n\n\n\n
      UNION\u64cd\u4f5c\u7b26<\/p>\n\n\n\n
      \"\"<\/figure>\n\n\n\n
      UNION \u64cd\u4f5c\u7b26\u8fd4\u56de\u4e24\u4e2a\u67e5\u8be2\u7684\u7ed3\u679c\u96c6\u7684\u5e76\u96c6\uff0c\u53bb\u9664\u91cd\u590d\u8bb0\u5f55\u3002\u5de6+\u4e2d+\u53f3<\/p>\n\n\n\n
      UNION ALL\u64cd\u4f5c\u7b26<\/p>\n\n\n\n
      \"\"<\/figure>\n\n\n\n
      UNION ALL\u64cd\u4f5c\u7b26\u8fd4\u56de\u4e24\u4e2a\u67e5\u8be2\u7684\u7ed3\u679c\u96c6\u7684\u5e76\u96c6\u3002\u5bf9\u4e8e\u4e24\u4e2a\u7ed3\u679c\u96c6\u7684\u91cd\u590d\u90e8\u5206\uff0c\u4e0d\u53bb\u91cd\u3002\u5de6+\u4e2d+\u4e2d+\u53f3<\/p>\n\n\n\n
      \u6ce8\u610f\uff1a\u6267\u884cUNION ALL\u8bed\u53e5\u65f6\u6240\u9700\u8981\u7684\u8d44\u6e90\u6bd4UNION\u8bed\u53e5\u5c11\u3002\u5982\u679c\u660e\u786e\u77e5\u9053\u5408\u5e76\u6570\u636e\u540e\u7684\u7ed3\u679c\u6570\u636e \u4e0d\u5b58\u5728\u91cd\u590d\u6570\u636e\uff0c\u6216\u8005\u4e0d\u9700\u8981\u53bb\u9664\u91cd\u590d\u7684\u6570\u636e\uff0c\u5219\u5c3d\u91cf\u4f7f\u7528UNION ALL\u8bed\u53e5\uff0c\u4ee5\u63d0\u9ad8\u6570\u636e\u67e5\u8be2\u7684\u6548\u7387\u3002<\/strong><\/p>\n\n\n\n
      \u4e3e\u4f8b\uff1a\u67e5\u8be2\u90e8\u95e8\u7f16\u53f7>90\u6216\u90ae\u7bb1\u5305\u542ba\u7684\u5458\u5de5\u4fe1\u606f<\/p>\n\n\n\n
      #\u65b9\u5f0f1 SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;<\/p>\n\n\n\n
      #\u65b9\u5f0f2 SELECT * FROM employees  WHERE email LIKE '%a%'
      UNION
      SELECT * FROM employees  WHERE department_id>90;<\/p>\n\n\n\n
      \u4e3e\u4f8b\uff1a\u67e5\u8be2\u4e2d\u56fd\u7528\u6237\u4e2d\u7537\u6027\u7684\u4fe1\u606f\u4ee5\u53ca\u7f8e\u56fd\u7528\u6237\u4e2d\u5e74\u7537\u6027\u7684\u7528\u6237\u4fe1\u606f<\/p>\n\n\n\n
      SELECT id,cname FROM t_chinamale WHERE csex='\u7537'
      UNION ALL
      SELECT id,tname FROM t_usmale WHERE tGender='male';<\/p>\n\n\n\n
      #8. UNION  \u548c UNION ALL\u7684\u4f7f\u7528\n# UNION\uff1a\u4f1a\u6267\u884c\u53bb\u91cd\u64cd\u4f5c\n# UNION ALL:\u4e0d\u4f1a\u6267\u884c\u53bb\u91cd\u64cd\u4f5c\n#\u7ed3\u8bba\uff1a\u5982\u679c\u660e\u786e\u77e5\u9053\u5408\u5e76\u6570\u636e\u540e\u7684\u7ed3\u679c\u6570\u636e\u4e0d\u5b58\u5728\u91cd\u590d\u6570\u636e\uff0c\u6216\u8005\u4e0d\u9700\u8981\u53bb\u9664\u91cd\u590d\u7684\u6570\u636e\uff0c\n#\u5219\u5c3d\u91cf\u4f7f\u7528UNION ALL\u8bed\u53e5\uff0c\u4ee5\u63d0\u9ad8\u6570\u636e\u67e5\u8be2\u7684\u6548\u7387\u3002<\/code><\/pre>\n\n\n\n
      \"\"\u200b<\/p>\n\n\n\n
      #9. 7\u79cdJOIN\u7684\u5b9e\u73b0\uff1a\n\n# \u4e2d\u56fe\uff1a\u5185\u8fde\u63a5\nSELECT employee_id,department_name\nFROM employees e JOIN departments d\nON e.`department_id` = d.`department_id`;\n\n# \u5de6\u4e0a\u56fe\uff1a\u5de6\u5916\u8fde\u63a5\nSELECT employee_id,department_name\nFROM employees e LEFT JOIN departments d\nON e.`department_id` = d.`department_id`;\n\n# \u53f3\u4e0a\u56fe\uff1a\u53f3\u5916\u8fde\u63a5\nSELECT employee_id,department_name\nFROM employees e RIGHT JOIN departments d\nON e.`department_id` = d.`department_id`;\n\n# \u5de6\u4e2d\u56fe\uff1a\nSELECT employee_id,department_name\nFROM employees e LEFT JOIN departments d\nON e.`department_id` = d.`department_id`\nWHERE d.`department_id` IS NULL;\n\n# \u53f3\u4e2d\u56fe\uff1a\nSELECT employee_id,department_name\nFROM employees e RIGHT JOIN departments d\nON e.`department_id` = d.`department_id`\nWHERE e.`department_id` IS NULL;\n#\u56e0\u4e3a\u662f\u5de6\u8fde\u63a5\uff0c\u662f\u4fdd\u7559\u8868adepartment_id\u4e3anull\u7684\u6570\u636e\uff0c\u6240\u4ee5\u5982\u679c\u4f60\u5199\u7684\u662f\u8868a\u7684\ndepartment_id\u4e3anull\u7684\u503c\u7684\u8bdd\uff0c\u4f1a\u628a\u8868a\u5de6\u8868\u4e0d\u91cd\u590d\u90e8\u5206\u8fc7\u6ee4\u6389\u4e86\u3002\u800c\u4e0d\u662f\u8fc7\u6ee4\u91cd\u590d\u90e8\u5206\u3002\n\n# \u5de6\u4e0b\u56fe\uff1a\u6ee1\u5916\u8fde\u63a5\n# \u65b9\u5f0f1\uff1a\u5de6\u4e0a\u56fe UNION ALL \u53f3\u4e2d\u56fe\n\nSELECT employee_id,department_name\nFROM employees e LEFT JOIN departments d\nON e.`department_id` = d.`department_id`\nUNION ALL\nSELECT employee_id,department_name\nFROM employees e RIGHT JOIN departments d\nON e.`department_id` = d.`department_id`\nWHERE e.`department_id` IS NULL;\n\n\n# \u65b9\u5f0f2\uff1a\u5de6\u4e2d\u56fe UNION ALL \u53f3\u4e0a\u56fe\n\nSELECT employee_id,department_name\nFROM employees e LEFT JOIN departments d\nON e.`department_id` = d.`department_id`\nWHERE d.`department_id` IS NULL\nUNION ALL\nSELECT employee_id,department_name\nFROM employees e RIGHT JOIN departments d\nON e.`department_id` = d.`department_id`;\n\n# \u53f3\u4e0b\u56fe\uff1a\u5de6\u4e2d\u56fe  UNION ALL \u53f3\u4e2d\u56fe\nSELECT employee_id,department_name\nFROM employees e LEFT JOIN departments d\nON e.`department_id` = d.`department_id`\nWHERE d.`department_id` IS NULL\nUNION ALL\nSELECT employee_id,department_name\nFROM employees e RIGHT JOIN departments d\nON e.`department_id` = d.`department_id`\nWHERE e.`department_id` IS NULL;<\/code><\/pre>\n\n\n\n
      \u5982\u679c\u4f7f\u7528\u4e3b\u8868\u5173\u8054\u5b57\u6bb5 IS NULL\uff0c\u4f1a\u5c06\u4e3b\u8868\u5173\u8054\u5b57\u6bb5\u975eNULL\u4f46\u662f\u548c\u4ece\u8868\u6ca1\u6709\u5173\u8054\u7684\u6570\u636e\u7ed9\u8fc7\u6ee4\u7684<\/p>\n\n\n\n
      \u7528\u7530\u5b57\u66f4\u597d\u7406\u89e3\uff0c\u56e0\u4e3a\u53d1\u4e0d\u4e86\u56fe\u7247\u6211\u7528\u8c61\u9650\u8bf4\u660e\uff0c\u5927\u5bb6\u5728\u7eb8\u4e0a\u82b1\u82b1\uff0c\u5bf9\u6bd4\u7740\u5de6\u63a5\u663e\u793a\u51fa\u7684\u6570\u636e\u90e8\u5206\u4f1a\u660e\u767d\u7684\u3002\u201c\u65e0\u5339\u914d\u6570\u636e\u5219\u586b\u5145\u4e3aNULL\u201d\u8fd9\u4e2a\u7406\u89e3\u4e86\u5c31OK\uff01\u5f15\u7533\uff0c\u65e2\u7136\u662f\u7528null\u6765\u7b5b\u9009\u5bf9\u5e94\u7684\u6570\u636e\uff0c\u5219\u4e0d\u7528\u975e\u5f97\u662fDAPARTMENT_ID IS NULL,\u53ea\u8981\u662f\u5b57\u6bb5\u88ab\u586b\u5145\u4e3aNULL\u7684\u5b57\u6bb5\u90fd\u53ef\u4ee5\uff0c\u6bd4\u5982\u7528EMPLOYEE_ID IS NULL\u4e5f\u6b63\u786e<\/p>\n\n\n\n
      \u591a\u8868\u67e5\u8be2-2<\/h3>\n\n\n\n
      \u50a8\u5907\uff1a\u5efa\u8868\u64cd\u4f5c\uff1a\nCREATE TABLE `t_dept` (\n `id` INT(11) NOT NULL AUTO_INCREMENT,\n `deptName` VARCHAR(30) DEFAULT NULL,\n `address` VARCHAR(40) DEFAULT NULL,\n PRIMARY KEY (`id`)\n ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;\n CREATE TABLE `t_emp` (\n `id` INT(11) NOT NULL AUTO_INCREMENT,\n `name` VARCHAR(20) DEFAULT NULL,\n `age` INT(3) DEFAULT NULL,\n `deptId` INT(11) DEFAULT NULL,\n empno int  not null,\n PRIMARY KEY (`id`),\n KEY `idx_dept_id` (`deptId`)\n #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)\n ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;\nINSERT INTO t_dept(deptName,address) VALUES('\u534e\u5c71','\u534e\u5c71');\n INSERT INTO t_dept(deptName,address) VALUES('\u4e10\u5e2e','\u6d1b\u9633');\n INSERT INTO t_dept(deptName,address) VALUES('\u5ce8\u7709','\u5ce8\u7709\u5c71');\n INSERT INTO t_dept(deptName,address) VALUES('\u6b66\u5f53','\u6b66\u5f53\u5c71');\n INSERT INTO t_dept(deptName,address) VALUES('\u660e\u6559','\u5149\u660e\u9876');\n INSERT INTO t_dept(deptName,address) VALUES('\u5c11\u6797','\u5c11\u6797\u5bfa');\n INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('\u98ce\u6e05\u626c',90,1,100001);\n INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('\u5cb3\u4e0d\u7fa4',50,1,100002);\n INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('\u4ee4\u72d0\u51b2',24,1,100003);\n INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('\u6d2a\u4e03\u516c',70,2,100004);\n INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('\u4e54\u5cf0',35,2,100005);\n INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('\u706d\u7edd\u5e08\u592a',70,3,100006);\n INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('\u5468\u82b7\u82e5',20,3,100007);\n INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('\u5f20\u4e09\u4e30',100,4,100008);\n INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('\u5f20\u65e0\u5fcc',25,5,100009);\n INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('\u97e6\u5c0f\u5b9d',18,null,100010);\n\n\u3010\u9898\u76ee\u3011\n#1.\u6240\u6709\u6709\u95e8\u6d3e\u7684\u4eba\u5458\u4fe1\u606f \n\uff08 A\u3001B\u4e24\u8868\u5171\u6709\uff09\n#2.\u5217\u51fa\u6240\u6709\u7528\u6237\uff0c\u5e76\u663e\u793a\u5176\u673a\u6784\u4fe1\u606f \n\uff08A\u7684\u5168\u96c6\uff09\n#3.\u5217\u51fa\u6240\u6709\u95e8\u6d3e \n\uff08B\u7684\u5168\u96c6\uff09\n#4.\u6240\u6709\u4e0d\u5165\u95e8\u6d3e\u7684\u4eba\u5458 \n\uff08A\u7684\u72ec\u6709\uff09\n#5.\u6240\u6709\u6ca1\u4eba\u5165\u7684\u95e8\u6d3e \n\uff08B\u7684\u72ec\u6709\uff09\n#6.\u5217\u51fa\u6240\u6709\u4eba\u5458\u548c\u673a\u6784\u7684\u5bf9\u7167\u5173\u7cfb\n(AB\u5168\u6709)\n #MySQL Full Join\u7684\u5b9e\u73b0 \u56e0\u4e3aMySQL\u4e0d\u652f\u6301FULL JOIN,\u4e0b\u9762\u662f\u66ff\u4ee3\u65b9\u6cd5 \n#left join + union(\u53ef\u53bb\u9664\u91cd\u590d\u6570\u636e)+ right join\n #7.\u5217\u51fa\u6240\u6709\u6ca1\u5165\u6d3e\u7684\u4eba\u5458\u548c\u6ca1\u4eba\u5165\u7684\u95e8\u6d3e\n\uff08A\u7684\u72ec\u6709+B\u7684\u72ec\u6709\uff09<\/code><\/pre>\n\n\n\n
      <\/p>\n\n\n\n
      1. \u6240\u6709\u6709\u95e8\u6d3e\u7684\u4eba\u5458\u4fe1\u606f \n\uff08 A\u3001B\u4e24\u8868\u5171\u6709\uff09\nselect * \nfrom t_emp a inner join t_dept b \non a.deptId = b.id; \n\n2. \u5217\u51fa\u6240\u6709\u7528\u6237\uff0c\u5e76\u663e\u793a\u5176\u673a\u6784\u4fe1\u606f \n\uff08A\u7684\u5168\u96c6\uff09\nselect * \nfrom t_emp a left join t_dept b \non a.deptId = b.id; \n\n3. \u5217\u51fa\u6240\u6709\u95e8\u6d3e \n\uff08B\u7684\u5168\u96c6\uff09\nselect * \nfrom  t_dept  b;\n\n 4. \u6240\u6709\u4e0d\u5165\u95e8\u6d3e\u7684\u4eba\u5458 \n\uff08A\u7684\u72ec\u6709\uff09\nselect * \nfrom t_emp a left join t_dept b \non a.deptId = b.id \nwhere b.id is null; \n\n5. \u6240\u6709\u6ca1\u4eba\u5165\u7684\u95e8\u6d3e \n\uff08B\u7684\u72ec\u6709\uff09\nselect * \nfrom t_dept b left join  t_emp a \non a.deptId = b.id \nwhere a.deptId is null; \n\n6. \u5217\u51fa\u6240\u6709\u4eba\u5458\u548c\u673a\u6784\u7684\u5bf9\u7167\u5173\u7cfb \n(AB\u5168\u6709)\n #MySQL Full Join\u7684\u5b9e\u73b0 \u56e0\u4e3aMySQL\u4e0d\u652f\u6301FULL JOIN,\u4e0b\u9762\u662f\u66ff\u4ee3\u65b9\u6cd5\n#left join + union(\u53ef\u53bb\u9664\u91cd\u590d\u6570\u636e)+ right join\n SELECT * \nFROM t_emp A LEFT JOIN t_dept B \nON A.deptId = B.id\n UNION\n SELECT * \nFROM t_emp A RIGHT JOIN t_dept B \nON A.deptId = B.id\n\n 7. \u5217\u51fa\u6240\u6709\u6ca1\u5165\u6d3e\u7684\u4eba\u5458\u548c\u6ca1\u4eba\u5165\u7684\u95e8\u6d3e \n\uff08A\u7684\u72ec\u6709+B\u7684\u72ec\u6709\uff09\nSELECT * \nFROM t_emp A LEFT JOIN t_dept B \nON A.deptId = B.id \nWHERE B.`id` IS NULL\n UNION\n SELECT * \nFROM t_emp A RIGHT JOIN t_dept B \nON A.deptId = B.id \nWHERE A.`deptId` IS NULL;<\/code><\/pre>\n\n\n\n
      NATURAL JOIN\u548cUSING\u7684\u4f7f\u7528 <\/p>\n\n\n\n
      #10. SQL99\u8bed\u6cd5\u7684\u65b0\u7279\u60271:\u81ea\u7136\u8fde\u63a5\nSQL99 \u5728 SQL92 \u7684\u57fa\u7840\u4e0a\u63d0\u4f9b\u4e86\u4e00\u4e9b\u7279\u6b8a\u8bed\u6cd5\uff0c\u6bd4\u5982 NATURAL JOIN \u7528\u6765\u8868\u793a\u81ea\u7136\u8fde\u63a5\u3002\u6211\u4eec\u53ef\u4ee5\n\u628a\u81ea\u7136\u8fde\u63a5\u7406\u89e3\u4e3a SQL92 \u4e2d\u7684\u7b49\u503c\u8fde\u63a5\u3002\u5b83\u4f1a\u5e2e\u4f60\u81ea\u52a8\u67e5\u8be2\u4e24\u5f20\u8fde\u63a5\u8868\u4e2d\u6240\u6709\u76f8\u540c\u7684\u5b57\u6bb5\uff0c\u7136\u540e\u8fdb\u884c\n\u7b49\u503c\u8fde\u63a5\u3002\n#92\u5982\u4e0b\nSELECT employee_id,last_name,department_name\nFROM employees e JOIN departments d\nON e.`department_id` = d.`department_id`\nAND e.`manager_id` = d.`manager_id`;\n\n#99\u5982\u4e0b\n# NATURAL JOIN : \u5b83\u4f1a\u5e2e\u4f60\u81ea\u52a8\u67e5\u8be2\u4e24\u5f20\u8fde\u63a5\u8868\u4e2d`\u6240\u6709\u76f8\u540c\u7684\u5b57\u6bb5`\uff0c\u7136\u540e\u8fdb\u884c`\u7b49\u503c\u8fde\u63a5`\u3002\nSELECT employee_id,last_name,department_name\nFROM employees e NATURAL JOIN departments d;\n\n\n#11. SQL99\u8bed\u6cd5\u7684\u65b0\u7279\u60272:USING\nSELECT employee_id,last_name,department_name\nFROM employees e JOIN departments d\nON e.department_id = d.department_id;\n\u4f60\u80fd\u770b\u51fa\u4e0e\u81ea\u7136\u8fde\u63a5 NATURAL JOIN \u4e0d\u540c\u7684\u662f\uff0cUSING \u6307\u5b9a\u4e86\u5177\u4f53\u7684\u76f8\u540c\u7684\u5b57\u6bb5\u540d\u79f0\uff0c\u4f60\u9700\u8981\u5728\nUSING\u7684\u62ec\u53f7 () \u4e2d\u586b\u5165\u8981\u6307\u5b9a\u7684\u540c\u540d\u5b57\u6bb5\u3002\u540c\u65f6\u4f7f\u7528 JOIN...USING \u53ef\u4ee5\u7b80\u5316 JOIN ON \u7684\u7b49\u503c\n\u8fde\u63a5\u3002\u5b83\u4e0e\u4e0b\u9762\u7684 SQL \u67e5\u8be2\u7ed3\u679c\u662f\u76f8\u540c\u7684\uff1a\nSELECT employee_id,last_name,department_name\nFROM employees e JOIN departments d\nUSING (department_id);\n\n\n#\u62d3\u5c55\uff1a\nSELECT last_name,job_title,department_name \nFROM employees INNER JOIN departments INNER JOIN jobs \nON employees.department_id = departments.department_id \nAND employees.job_id = jobs.job_id;<\/code><\/pre>\n\n\n\n
      \n
      \u6ce8\u610f\uff1a<\/p>\n\n\n\n
      \u6211\u4eec\u8981 \u63a7\u5236\u8fde\u63a5\u8868\u7684\u6570\u91cf\u3002\u591a\u8868\u8fde\u63a5\u5c31\u76f8\u5f53\u4e8e\u5d4c\u5957 for \u5faa\u73af\u4e00\u6837\uff0c\u975e\u5e38\u6d88\u8017\u8d44\u6e90\uff0c\u4f1a\u8ba9 SQL \u67e5\u8be2\u6027\u80fd\u4e0b \u964d\u5f97\u5f88\u4e25\u91cd\uff0c\u56e0\u6b64\u4e0d\u8981\u8fde\u63a5\u4e0d\u5fc5\u8981\u7684\u8868\u3002\u5728\u8bb8\u591a DBMS \u4e2d\uff0c\u4e5f\u90fd\u4f1a\u6709\u6700\u5927\u8fde\u63a5\u8868\u7684\u9650\u5236\u3002<\/p>\n\n\n\n
      \u3010\u5f3a\u5236\u3011\u8d85\u8fc7\u4e09\u4e2a\u8868\u7981\u6b62 join\u3002\u9700\u8981 join \u7684\u5b57\u6bb5\uff0c\u6570\u636e\u7c7b\u578b\u4fdd\u6301\u7edd\u5bf9\u4e00\u81f4\uff1b\u591a\u8868\u5173\u8054\u67e5\u8be2\u65f6\uff0c \u4fdd\u8bc1\u88ab\u5173\u8054\u7684\u5b57\u6bb5\u9700\u8981\u6709\u7d22\u5f15\u3002 \u8bf4\u660e\uff1a\u5373\u4f7f\u53cc\u8868 join \u4e5f\u8981\u6ce8\u610f\u8868\u7d22\u5f15\u3001SQL \u6027\u80fd\u3002
      \u6765\u6e90\uff1a\u963f\u91cc\u5df4\u5df4\u300aJava\u5f00\u53d1\u624b\u518c\u300b<\/p>\n<\/blockquote>\n\n\n\n
      \u8bfe\u540e\u7ec3\u4e60<\/h3>\n\n\n\n
      \u3010\u9898\u76ee\u3011<\/p>\n\n\n\n
      # 1.\u663e\u793a\u6240\u6709\u5458\u5de5\u7684\u59d3\u540d\uff0c\u90e8\u95e8\u53f7\u548c\u90e8\u95e8\u540d\u79f0\u3002
      # 2.\u67e5\u8be290\u53f7\u90e8\u95e8\u5458\u5de5\u7684job_id\u548c90\u53f7\u90e8\u95e8\u7684location_id
      # 3.\u9009\u62e9\u6240\u6709\u6709\u5956\u91d1\u7684\u5458\u5de5\u7684 last_name , department_name , location_id , city
      # 4.\u9009\u62e9city\u5728Toronto\u5de5\u4f5c\u7684\u5458\u5de5\u7684 last_name , job_id , department_id , department_name
      # 5.\u67e5\u8be2\u5458\u5de5\u6240\u5728\u7684\u90e8\u95e8\u540d\u79f0\u3001\u90e8\u95e8\u5730\u5740\u3001\u59d3\u540d\u3001\u5de5\u4f5c\u3001\u5de5\u8d44\uff0c\u5176\u4e2d\u5458\u5de5\u6240\u5728\u90e8\u95e8\u7684\u90e8\u95e8\u540d\u79f0\u4e3a\u2019Executive\u2019
      # 6.\u9009\u62e9\u6307\u5b9a\u5458\u5de5\u7684\u59d3\u540d\uff0c\u5458\u5de5\u53f7\uff0c\u4ee5\u53ca\u4ed6\u7684\u7ba1\u7406\u8005\u7684\u59d3\u540d\u548c\u5458\u5de5\u53f7\uff0c\u7ed3\u679c\u7c7b\u4f3c\u4e8e\u4e0b\u9762\u7684\u683c\u5f0f employees Emp#  manager Mgr#
      kochhar      101        king
      # 7.\u67e5\u8be2\u54ea\u4e9b\u90e8\u95e8\u6ca1\u6709\u5458\u5de5 100
      # 8. \u67e5\u8be2\u54ea\u4e2a\u57ce\u5e02\u6ca1\u6709\u90e8\u95e8
      # 9. \u67e5\u8be2\u90e8\u95e8\u540d\u4e3a Sales \u6216 IT \u7684\u5458\u5de5\u4fe1\u606f <\/p>\n\n\n\n
      -- 1. \u663e\u793a\u6240\u6709\u5458\u5de5\u7684\u59d3\u540d\uff0c\u90e8\u95e8\u53f7\u548c\u90e8\u95e8\u540d\u79f0\n SELECT last_name, e.department_id, department_name\n FROM employees e\n LEFT OUTER JOIN departments d\n ON e.`department_id` = d.`department_id`;\n\n-- 2. \u67e5\u8be290\u53f7\u90e8\u95e8\u5458\u5de5\u7684job_id\u548c90\u53f7\u90e8\u95e8\u7684location_id\n SELECT job_id, location_id\n FROM employees e, departments d\n WHERE e.`department_id` = d.`department_id`\n AND e.`department_id` = 90;\n\n\u6216\n SELECT job_id, location_id\n FROM employees e\n JOIN departments d\n ON e.`department_id` = d.`department_id`\n WHERE e.`department_id` = 90;\n\n-- 3. \u9009\u62e9\u6240\u6709\u6709\u5956\u91d1\u7684\u5458\u5de5\u7684 last_name , department_name , location_id , city\n SELECT last_name , department_name , d.location_id , city\n FROM employees e\n LEFT OUTER JOIN departments d\n ON e.`department_id` = d.`department_id`\n LEFT OUTER JOIN locations l\n ON d.`location_id` = l.`location_id`\n WHERE commission_pct IS NOT NULL;\n\n-- 4. \u9009\u62e9city\u5728Toronto\u5de5\u4f5c\u7684\u5458\u5de5\u7684 last_name , job_id , department_id , department_name\n SELECT last_name , job_id , e.department_id , department_name\n FROM employees e, departments d, locations l\n WHERE e.`department_id` = d.`department_id`\n AND d.`location_id` = l.`location_id`\n AND city = 'Toronto';\n\u6216\n SELECT last_name , job_id , e.department_id , department_name\n FROM employees e\n JOIN departments d\n ON e.`department_id` = d.`department_id`\n JOIN locations l\n ON l.`location_id` = d.`location_id`\n WHERE l.`city` = 'Toronto';\n\n-- 5. \u67e5\u8be2\u5458\u5de5\u6240\u5728\u7684\u90e8\u95e8\u540d\u79f0\u3001\u90e8\u95e8\u5730\u5740\u3001\u59d3\u540d\u3001\u5de5\u4f5c\u3001\u5de5\u8d44\uff0c\u5176\u4e2d\u5458\u5de5\u6240\u5728\u90e8\u95e8\u7684\u90e8\u95e8\u540d\u79f0\u4e3a'Executive'\n SELECT department_name, street_address, last_name, job_id, salary\n FROM employees e JOIN departments d\n ON e.department_id = d.department_id\n JOIN locations l\n ON d.`location_id` = l.`location_id`\n WHERE department_name = 'Executive'\n\n-- 6. \u9009\u62e9\u6307\u5b9a\u5458\u5de5\u7684\u59d3\u540d\uff0c\u5458\u5de5\u53f7\uff0c\u4ee5\u53ca\u4ed6\u7684\u7ba1\u7406\u8005\u7684\u59d3\u540d\u548c\u5458\u5de5\u53f7\n SELECT emp.last_name employees, emp.employee_id \"Emp#\", mgr.last_name manager,mgr.employee_id \"Mgr#\"\n FROM employees emp \n LEFT OUTER JOIN employees mgr\n ON emp.manager_id = mgr.employee_id;\n\n-- 7. \u67e5\u8be2\u54ea\u4e9b\u90e8\u95e8\u6ca1\u6709\u5458\u5de5\n#\u65b9\u5f0f1\uff1a\n SELECT d.department_id\n FROM departments d LEFT JOIN employees e\n ON e.department_id = d.`department_id`\n WHERE e.department_id IS NULL\n\n #\u65b9\u5f0f2\uff1a\n SELECT department_id\n FROM departments d\n WHERE NOT EXISTS (\n     SELECT *\n     FROM employees e\n     WHERE e.`department_id` = d.`department_id`\n     )\n\n-- 8. \u67e5\u8be2\u54ea\u4e2a\u57ce\u5e02\u6ca1\u6709\u90e8\u95e8\n SELECT l.location_id,l.city\n FROM locations l LEFT JOIN departments d\n ON l.`location_id` = d.`location_id`\n WHERE d.`location_id` IS NULL\n\n-- 9. \u67e5\u8be2\u90e8\u95e8\u540d\u4e3a Sales \u6216 IT \u7684\u5458\u5de5\u4fe1\u606f\n SELECT employee_id,last_name,department_name\n FROM employees e,departments d\n WHERE e.department_id = d.`department_id`\n AND d.`department_name` IN ('Sales','IT');<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
      \u7b2c06\u7ae0_\u591a\u8868\u67e5\u8be2 \u591a\u8868\u67e5\u8be2\uff0c\u4e5f\u79f0\u4e3a\u5173\u8054\u67e5\u8be2\uff0c\u6307\u4e24\u4e2a\u6216\u66f4\u591a\u4e2a\u8868\u4e00\u8d77\u5b8c\u6210\u67e5\u8be2\u64cd\u4f5c\u3002 \u8fd9\u4e9b\u4e00\u8d77\u67e5\u8be2\u7684\u8868\u4e4b\u95f4\u662f\u6709\u5173\u7cfb\u7684\uff08\u4e00\u5bf9\u4e00\u3001\u4e00\u5bf9\u591a\uff09 …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"emotion":"","emotion_color":"","title_style":"","license":"","footnotes":""},"categories":[1],"tags":[],"class_list":["post-810","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/eve2333.top\/index.php?rest_route=\/wp\/v2\/posts\/810","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/eve2333.top\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/eve2333.top\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/eve2333.top\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/eve2333.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=810"}],"version-history":[{"count":2,"href":"https:\/\/eve2333.top\/index.php?rest_route=\/wp\/v2\/posts\/810\/revisions"}],"predecessor-version":[{"id":1204,"href":"https:\/\/eve2333.top\/index.php?rest_route=\/wp\/v2\/posts\/810\/revisions\/1204"}],"wp:attachment":[{"href":"https:\/\/eve2333.top\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=810"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/eve2333.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=810"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/eve2333.top\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=810"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}