{"id":1752,"date":"2016-11-03T14:54:45","date_gmt":"2016-11-03T06:54:45","guid":{"rendered":"https:\/\/ai.zhousir.top\/?p=1752"},"modified":"2026-08-18T12:09:36","modified_gmt":"2026-08-18T04:09:36","slug":"mysql%e5%a4%9a%e8%a1%a8%e6%9f%a5%e8%af%a2","status":"publish","type":"post","link":"https:\/\/ai.zhousir.top\/?p=1752","title":{"rendered":"mysql\u591a\u8868\u67e5\u8be2"},"content":{"rendered":"
\n

\u591a\u8868\u67e5\u8be2<\/strong>\u662f\u5728\u591a\u4e2a\u6709\u903b\u8f91\u8054\u7cfb\u7684\u8868\u4e4b\u95f4\u67e5\u8be2\uff0c\u903b\u8f91\u5173\u7cfb\u4e3b\u8981\u6307\u4e3b\u5916\u952e\uff1a\u4e3b\u8868\u67d0\u5b57\u6bb5\u7684\u503c\u53d6\u81ea\u53e6\u4e00\u8868\u7684\u5b57\u6bb5\u96c6\u5408\u3002\u5f80\u4e3b\u8868\u63d2\u6570\u636e\u65f6\uff0c\u5bf9\u5e94\u5b57\u6bb5\u5fc5\u987b\u5728\u5173\u8054\u8868\u4e2d\u5b58\u5728\uff08\u9664\u975e\u8be5\u5b57\u6bb5\u53ef\u7a7a\uff09\uff0c\u5426\u5219\u63d2\u4e0d\u8fdb\u53bb\u3002\u5b9e\u73b0\u65b9\u5f0f\u662f\u8868\u8fde\u63a5<\/strong>\u6216\u5b50\u67e5\u8be2<\/strong>\u3002<\/p>\n

\u5047\u8bbe\u6709\u5458\u5de5\u8868\u548c\u90e8\u95e8\u8868\uff08\u539f\u6587\u8868\u540d\u62fc\u5199\u4e3a deparment \/ employe\uff0c\u793a\u4f8b\u6cbf\u7528\uff09\uff1a<\/p>\n

-- \u90e8\u95e8\u8868\nCREATE TABLE `deparment` (\n  `id` int(11) NOT NULL AUTO_INCREMENT,\n  `deparment_name` varchar(255) NOT NULL,\n  `deparment_num` varchar(255) NOT NULL,\n  `deparment_des` varchar(255) DEFAULT NULL,\n  PRIMARY KEY (`id`)\n);\n\nINSERT INTO `deparment` VALUES ('1', '\u4eba\u4e8b\u90e8', 'A1001', '\u5904\u7406\u4eba\u4e8b\u5173\u7cfb');\nINSERT INTO `deparment` VALUES ('2', '\u8d22\u52a1\u90e8', 'B2001', '\u7ba1\u7406\u516c\u53f8\u8d44\u4ea7');\nINSERT INTO `deparment` VALUES ('3', '\u884c\u653f\u90e8', 'C3001', '\u5236\u8ba2\u89c4\u7ae0\u5236\u5ea6');\n\n-- \u5458\u5de5\u8868\nCREATE TABLE `employe` (\n  `id` int(11) NOT NULL AUTO_INCREMENT,\n  `name` varchar(255) NOT NULL,\n  `d_id` int(11) NOT NULL DEFAULT '1',\n  PRIMARY KEY (`id`),\n  KEY `fk` (`d_id`)\n);\n\nINSERT INTO `employe` VALUES ('5', '\u5f20\u4e09', '1');\nINSERT INTO `employe` VALUES ('6', '\u674e\u56db', '1');\nINSERT INTO `employe` VALUES ('7', '\u738b\u4e8c', '1');\nINSERT INTO `employe` VALUES ('8', '\u9ebb\u5b50', '2');\nINSERT INTO `employe` VALUES ('9', '\u5c0f\u660e', '3');<\/code><\/pre>\n
\u4e00\u3001\u666e\u901a\u591a\u8868\u67e5\u8be2<\/h2>\n
\u8bed\u6cd5\uff1afrom \u540e\u9762\u53ef\u4ee5\u6709\u5f88\u591a\u8868\uff0c\u8868\u540d\u7528\u9017\u53f7\u9694\u5f00\u3002\u8fd9\u4e9b\u8868\u4f1a\u505a\u7b1b\u5361\u5c14\u79ef<\/strong>\uff0c\u518d\u7528 where \u8fc7\u6ee4\u3002<\/p>\n
select \u52171... from \u88681, \u88682... where condition...<\/code><\/pre>\n
mysql> select * from employe,deparment where employe.d_id = deparment.id;\n+----+------+------+----+----------------+---------------+---------------+\n| id | name | d_id | id | deparment_name | deparment_num | deparment_des |\n+----+------+------+----+----------------+---------------+---------------+\n|  5 | \u5f20\u4e09 |    1 |  1 | \u4eba\u4e8b\u90e8         | A1001         | \u5904\u7406\u4eba\u4e8b\u5173\u7cfb  |\n|  6 | \u674e\u56db |    1 |  1 | \u4eba\u4e8b\u90e8         | A1001         | \u5904\u7406\u4eba\u4e8b\u5173\u7cfb  |\n|  7 | \u738b\u4e8c |    1 |  1 | \u4eba\u4e8b\u90e8         | A1001         | \u5904\u7406\u4eba\u4e8b\u5173\u7cfb  |\n|  8 | \u9ebb\u5b50 |    2 |  2 | \u8d22\u52a1\u90e8         | B2001         | \u7ba1\u7406\u516c\u53f8\u8d44\u4ea7  |\n|  9 | \u5c0f\u660e |    3 |  3 | \u884c\u653f\u90e8         | C3001         | \u5236\u8ba2\u89c4\u7ae0\u5236\u5ea6  |\n+----+------+------+----+----------------+---------------+---------------+<\/code><\/pre>\n
\u4e8c\u3001\u5185\u8fde\u63a5<\/h2>\n
select \u5217\u540d1,... from \u88681 inner join \u88682 on \u88681.\u5217=\u88682.\u5217 ... condition...<\/code><\/pre>\n
from \u8d77\u8868 1 \u4e0e\u8868 2 \u505a\u5339\u914d\uff1b\u6bcf\u5339\u914d\u4e00\u884c\uff0c\u7528 on \u540e\u6761\u4ef6\u5224\u65ad\u662f\u5426\u6210\u7acb\uff0c\u6210\u7acb\u624d\u653e\u5165\u4e34\u65f6\u7ed3\u679c\u3002<\/p>\n
mysql> SELECT * FROM employe AS e INNER JOIN deparment AS d ON e.d_id = d.id;\n+----+------+------+----+----------------+---------------+---------------+\n| id | name | d_id | id | deparment_name | deparment_num | deparment_des |\n+----+------+------+----+----------------+---------------+---------------+\n|  5 | \u5f20\u4e09 |    1 |  1 | \u4eba\u4e8b\u90e8         | A1001         | \u5904\u7406\u4eba\u4e8b\u5173\u7cfb  |\n|  6 | \u674e\u56db |    1 |  1 | \u4eba\u4e8b\u90e8         | A1001         | \u5904\u7406\u4eba\u4e8b\u5173\u7cfb  |\n|  7 | \u738b\u4e8c |    1 |  1 | \u4eba\u4e8b\u90e8         | A1001         | \u5904\u7406\u4eba\u4e8b\u5173\u7cfb  |\n|  8 | \u9ebb\u5b50 |    2 |  2 | \u8d22\u52a1\u90e8         | B2001         | \u7ba1\u7406\u516c\u53f8\u8d44\u4ea7  |\n|  9 | \u5c0f\u660e |    3 |  3 | \u884c\u653f\u90e8         | C3001         | \u5236\u8ba2\u89c4\u7ae0\u5236\u5ea6  |\n+----+------+------+----+----------------+---------------+---------------+<\/code><\/pre>\n
\u4e09\u3001\u5916\u8fde\u63a5<\/h2>\n
\u5de6\u5916\u8fde\u63a5<\/strong>\uff1a\u5de6\u8868\u6240\u6709\u8bb0\u5f55\u90fd\u8fdb\u7ed3\u679c\u96c6\uff0c\u65e0\u8bba\u53f3\u8868\u6709\u6ca1\u6709\u5339\u914d\u3002\u8bed\u6cd5\uff1aselect \u5217 from \u88681 left outer join \u88682 on \u88681.\u5217=\u88682.\u5217<\/code>\uff0c\u53ef\u7ee7\u7eed left outer join \u88683\u3002<\/p>\n
mysql> SELECT e.name,d.deparment_name FROM employe AS e\n    -> LEFT OUTER JOIN deparment AS d\n    -> ON e.d_id = d.id;\n+------+----------------+\n| name | deparment_name |\n+------+----------------+\n| \u5f20\u4e09 | \u4eba\u4e8b\u90e8         |\n| \u674e\u56db | \u4eba\u4e8b\u90e8         |\n| \u738b\u4e8c | \u4eba\u4e8b\u90e8         |\n| \u9ebb\u5b50 | \u8d22\u52a1\u90e8         |\n| \u5c0f\u660e | \u884c\u653f\u90e8         |\n| \u5c0f\u4e3d | NULL           |\n+------+----------------+<\/code><\/pre>\n
\u4e0a\u4f8b\u4e2d\u201c\u5c0f\u4e3d\u201d\u5728\u5458\u5de5\u8868\u91cc\uff0c\u90e8\u95e8\u5bf9\u4e0d\u4e0a\uff0c\u90e8\u95e8\u540d\u5c31\u662f NULL\u3002<\/p>\n
\u53f3\u5916\u8fde\u63a5<\/strong>\uff1a\u65e0\u8bba\u662f\u5426\u5339\u914d\uff0c\u90fd\u8fd4\u56de\u53f3\u8868\u5168\u90e8\u8bb0\u5f55\u3002<\/p>\n
mysql> SELECT e.name,d.deparment_name FROM employe AS e\n    -> RIGHT OUTER JOIN deparment AS d\n    -> ON e.d_id = d.id;\n+------+----------------+\n| name | deparment_name |\n+------+----------------+\n| \u5f20\u4e09 | \u4eba\u4e8b\u90e8         |\n| \u674e\u56db | \u4eba\u4e8b\u90e8         |\n| \u738b\u4e8c | \u4eba\u4e8b\u90e8         |\n| \u9ebb\u5b50 | \u8d22\u52a1\u90e8         |\n| \u5c0f\u660e | \u884c\u653f\u90e8         |\n| NULL | \u9500\u552e\u90e8         |\n+------+----------------+<\/code><\/pre>\n
\u201c\u9500\u552e\u90e8\u201d\u5728\u90e8\u95e8\u8868\u91cc\u6ca1\u6709\u5458\u5de5\uff0c\u5458\u5de5\u540d\u5c31\u662f NULL\u3002MySQL \u6ca1\u6709\u5168\u5916\u8fde\u63a5<\/strong>\uff08FULL OUTER JOIN\uff09\u3002\u9700\u8981\u65f6\u53ef\u7528 LEFT JOIN \u4e0e RIGHT JOIN \u518d UNION\u3002<\/p>\n\n\n\n\n\n\n\n\n
\u5199\u6cd5<\/th>\n\u4fdd\u7559\u54ea\u8fb9<\/th>\n\u5339\u914d\u4e0d\u4e0a<\/th>\n<\/tr>\n<\/thead>\n
FROM a, b WHERE a.x = b.x<\/td>\n\u53ea\u4fdd\u7559\u5339\u914d\u884c<\/td>\n\u76f8\u5f53\u4e8e\u5185\u8fde\u63a5<\/td>\n<\/tr>\n
INNER JOIN \u2026 ON<\/td>\n\u53ea\u4fdd\u7559\u5339\u914d\u884c<\/td>\n\u4e0d\u51fa\u73b0<\/td>\n<\/tr>\n
LEFT OUTER JOIN<\/td>\n\u5de6\u8868\u5168\u90e8<\/td>\n\u53f3\u8868\u5217\u4e3a NULL<\/td>\n<\/tr>\n
RIGHT OUTER JOIN<\/td>\n\u53f3\u8868\u5168\u90e8<\/td>\n\u5de6\u8868\u5217\u4e3a NULL<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\u56db\u3001\u81ea\u8fde\u63a5<\/h2>\n
\u4e00\u5f20\u8868\u4e0e\u81ea\u5df1\u8fde\u63a5\u3002\u4f8b\u5982\u67e5\u627e\u6bcf\u4e2a\u5458\u5de5\u7684\u76f4\u63a5\u4e0a\u7ea7\u59d3\u540d\uff1a<\/p>\n
mysql> CREATE TABLE people(\n    -> id int NOT NULL AUTO_INCREMENT PRIMARY KEY,\n    -> name varchar(50) NOT NULL,\n    -> parent_id int DEFAULT 0);\n\nmysql> SELECT * FROM people;\n+----+--------+-----------+\n| id | name   | parent_id |\n+----+--------+-----------+\n|  1 | \u8303\u603b   |         0 |\n|  2 | \u4f55\u7ecf\u7406 |         1 |\n|  3 | \u9ebb\u4e3b\u7ba1 |         2 |\n|  4 | \u5218\u4e3b\u7ba1 |         2 |\n|  5 | \u674e\u9ad8\u7ea7 |         3 |\n|  6 | \u738b\u4e2d\u7ea7 |         3 |\n|  7 | \u5f20\u521d\u7ea7 |         4 |\n+----+--------+-----------+\n\n-- \u67e5\u8be2\u5218\u4e3b\u7ba1\u7684\u4e0a\u7ea7\u9886\u5bfc\nmysql> SELECT s.name AS \u59d3\u540d,f.name AS \u9886\u5bfc FROM people AS s\n    -> INNER JOIN people AS f\n    -> ON s.parent_id = f.id\n    -> AND s.name='\u5218\u4e3b\u7ba1';\n+--------+--------+\n| \u59d3\u540d   | \u9886\u5bfc   |\n+--------+--------+\n| \u5218\u4e3b\u7ba1 | \u4f55\u7ecf\u7406 |\n+--------+--------+<\/code><\/pre>\n
\u5546\u57ce\u5546\u54c1\u5206\u7c7b\u540c\u6837\u7528\u7236\u5b50 id \u81ea\u8fde\u63a5\uff1a<\/p>\n
mysql> select * from product;\n+------------+----------------+-------------------+\n| product_id | product_name   | product_parent_id |\n+------------+----------------+-------------------+\n|          1 | \u5929\u732b\u5546\u54c1       |                 0 |\n|          2 | \u5973\u88c5\/\u5185\u8863      |                 1 |\n|          3 | \u7537\u88c5\/\u8fd0\u52a8\u6237\u5916  |                 1 |\n|          4 | \u5bb6\u5177\u5efa\u6750       |                 1 |\n|          5 | \u6c7d\u8f66\/\u914d\u4ef6\/\u7528\u54c1 |                 1 |\n|          6 | \u56fe\u4e66\u97f3\u50cf       |                 1 |\n|          7 | \u5f53\u5b63\u6d41\u884c       |                 2 |\n|          8 | \u7cbe\u9009\u4e0a\u88c5       |                 2 |\n|          9 | \u6d6a\u6f2b\u88d9\u88c5       |                 2 |\n|         10 | \u5973\u58eb\u4e0b\u88c5       |                 2 |\n|         11 | \u6210\u5957\u5bb6\u5177       |                 4 |\n|         12 | \u5ba2\u5385\u9910\u5385       |                 4 |\n+------------+----------------+-------------------+\n\n-- \u67e5\u8be2\u5973\u58eb\u4e0b\u88c5\u5206\u7c7b\u7684\u4e0a\u7ea7\u5206\u7c7b\nmysql> SELECT s.product_name, f.product_name FROM product AS s\n    -> INNER JOIN product AS f\n    -> ON s.product_parent_id = f.product_id\n    -> AND s.product_name='\u5973\u58eb\u4e0b\u88c5';\n+--------------+--------------+\n| product_name | product_name |\n+--------------+--------------+\n| \u5973\u58eb\u4e0b\u88c5     | \u5973\u88c5\/\u5185\u8863    |\n+--------------+--------------+<\/code><\/pre>\n
\u4e94\u3001\u5b50\u67e5\u8be2<\/h2>\n
\u67e5\u8be2\u91cc\u9762\u8fd8\u53ef\u4ee5\u6709\u67e5\u8be2\u3002\u5916\u5c42\u53eb\u4e3b\u67e5\u8be2\uff0c\u5185\u5c42\u53eb\u5b50\u67e5\u8be2\u3002\u5b50\u67e5\u8be2\u5148\u8fd0\u884c\uff0c\u7ed3\u679c\u5f53\u4f5c\u4e3b\u67e5\u8be2\u7684\u503c\u3002<\/p>\n
    \n
  • \n
    \u5b50\u67e5\u8be2\u8fd4\u56de\u5355\u4e2a\u503c\uff1a\u7528 =<\/code><\/section>\n<\/li>\n
  • \n
    \u8fd4\u56de\u591a\u884c\u4e00\u5217\uff1a\u7528 IN<\/code><\/section>\n<\/li>\n
  • \n
    \u8fd4\u56de\u4e00\u884c\u591a\u5217\uff1a\u7528 (a,b) IN ( \u2026 )<\/code><\/section>\n<\/li>\n
  • \n
    \u8fd4\u56de\u591a\u884c\u591a\u5217\uff1a\u628a\u6761\u4ef6\u62c6\u5f00\uff0c\u5c06\u5b50\u67e5\u8be2\u5f97\u5230\u7684\u8868\u518d\u8fde\u63a5<\/section>\n<\/li>\n<\/ul>\n
    \u6761\u4ef6\u62c6\u5f97\u8d8a\u7b80\u5355\u8d8a\u597d\uff0c\u6761\u4ef6\u4e4b\u95f4\u6700\u597d\u662f\u5305\u542b\u5173\u7cfb\uff0c\u903b\u8f91\u624d\u901a\u3002\u4e5f\u53ef\u4ee5\u5404\u79cd\u5b50\u67e5\u8be2\u5e76\u5217\uff0c\u6216\u4e00\u4e2a\u662f\u53e6\u4e00\u4e2a\u7684\u5b50\u67e5\u8be2\u3002\u67e5\u8be2\u4eba\u4e8b\u90e8\u6240\u6709\u5458\u5de5\u59d3\u540d\uff1a<\/p>\n
    mysql> SELECT name AS \u59d3\u540d FROM employe AS e\n    -> WHERE e.d_id = (SELECT id FROM deparment AS d WHERE d.deparment_name='\u4eba\u4e8b\u90e8');\n+------+\n| \u59d3\u540d |\n+------+\n| \u5f20\u4e09 |\n| \u674e\u56db |\n| \u738b\u4e8c |\n+------+<\/code><\/pre>\n
    \u6ce8\uff1a\u7b1b\u5361\u5c14\u79ef\u53c8\u53eb\u76f4\u79ef\u3002\u96c6\u5408 A={a,b}\uff0cB={0,1,2}\uff0c\u5219 A\u00d7B={(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}\u3002\u53ef\u6269\u5c55\u5230\u591a\u4e2a\u96c6\u5408\u3002\u82e5 A \u662f\u5b66\u751f\u96c6\u5408\u3001B \u662f\u8bfe\u7a0b\u96c6\u5408\uff0cA\u00d7B \u8868\u793a\u6240\u6709\u53ef\u80fd\u7684\u9009\u8bfe\u60c5\u51b5\u3002<\/p>\n
    \n
    \u4e00\u53e5\u8bdd\u603b\u7ed3\uff1a\u591a\u8868\u8981\u4e48\u5148\u7b1b\u5361\u5c14\u79ef\u518d\u7528 WHERE\/ON \u6536\u6210\u5339\u914d\u884c\uff08\u5185\u8fde\u63a5\uff09\uff0c\u8981\u4e48\u7528\u5de6\u53f3\u5916\u8fde\u63a5\u628a\u67d0\u4e00\u4fa7\u6574\u8868\u7559\u4e0b\uff1b\u5c42\u7ea7\u6570\u636e\u7528\u81ea\u8fde\u63a5\uff0c\u5355\u503c\u8fc7\u6ee4\u7528\u5b50\u67e5\u8be2\u3002<\/strong><\/p>\n<\/blockquote>\n<\/section>\n","protected":false},"excerpt":{"rendered":"
    \u591a\u8868\u67e5\u8be2\u662f\u5728\u591a\u4e2a\u6709\u903b\u8f91\u8054\u7cfb\u7684\u8868\u4e4b\u95f4\u67e5\u8be2\uff0c\u903b\u8f91\u5173\u7cfb\u4e3b\u8981\u6307\u4e3b\u5916\u952e\uff1a\u4e3b\u8868\u67d0\u5b57\u6bb5\u7684\u503c\u53d6\u81ea\u53e6\u4e00\u8868\u7684\u5b57\u6bb5\u96c6\u5408\u3002\u5f80\u4e3b\u8868\u63d2\u6570\u636e […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[61],"tags":[192,62],"_links":{"self":[{"href":"https:\/\/ai.zhousir.top\/index.php?rest_route=\/wp\/v2\/posts\/1752"}],"collection":[{"href":"https:\/\/ai.zhousir.top\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ai.zhousir.top\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ai.zhousir.top\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ai.zhousir.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1752"}],"version-history":[{"count":3,"href":"https:\/\/ai.zhousir.top\/index.php?rest_route=\/wp\/v2\/posts\/1752\/revisions"}],"predecessor-version":[{"id":1762,"href":"https:\/\/ai.zhousir.top\/index.php?rest_route=\/wp\/v2\/posts\/1752\/revisions\/1762"}],"wp:attachment":[{"href":"https:\/\/ai.zhousir.top\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1752"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ai.zhousir.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1752"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ai.zhousir.top\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1752"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}